MySQL之修改表中的列，mysql

MySQL之修改表中的列，mysql

MySQL之修改表中的列

修改表中列的文法：

一張表，建立完畢，有了N列。之後還可以增加或刪除或修改列

alter table 表名 add 列名稱 列類型 列參數;    [這樣加的列在表的最後]
例:alter table m1 add username char(20) not null default '';

alter table 表名 add 列名稱 列類型 列參數 after 某列;    [新列加在某列後]
例:alter table m1 add username char(20) not null default '';


如果想增加一列，並位於表的最前面，用first
alter table 表名 add 列名稱 列類型 列參數 first;
例:alter table m1 add pid int not null first;


-------------------------------------------------------------------------------------------------------------------
刪除表中列的文法：

alter table 表名 drop 列名;
例:alter table m1 drop pid;


------------------------------------------------------------------------------------------------------------------
修改表中列類型的文法：

alter table 表名 modify 列名 新類型 新參數;
例:alter table m1 modify gender char(4) not null default '';


-------------------------------------------------------------------------------------------------------------------
修改表中列名及類型的文法：

alter table 表名 change 舊列名 新列名 新類型 新參數;
例:alter table m1 change id uid int unsigned;

---------------------------------------------------------------------------------------------------------------

下面是在mysql中的操作：

mysql> create table m1(
    -> id int unsigned auto_increment primary key
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> desc m1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.14 sec)

mysql> alter table m1 add username char(20) not null default '';
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | char(20)         | NO   |     |         |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table m1 add birth date not null default '0000-00-00';
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
3 rows in set (0.01 sec)

mysql> alter table m1 add gender char(1) not null default '' after username;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| gender   | char(1)          | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.01 sec)

mysql> #如果想增加一列，並位於表的最前面，用first
mysql> alter table m1 add pid int not null default '' first;
ERROR 1067 (42000): Invalid default value for 'pid'
mysql> alter table m1 add pid int not null first;
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| pid      | int(11)          | NO   |     | NULL       |                |
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| gender   | char(1)          | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
5 rows in set (0.01 sec)

mysql> #刪除列
mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| pid      | int(11)          | NO   |     | NULL       |                |
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| gender   | char(1)          | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
5 rows in set (0.01 sec)

mysql> alter table m1 drop pid;
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| gender   | char(1)          | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.01 sec)

mysql> alter table m1 modify gender char(4) not null default '';
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+----------------+
| Field    | Type             | Null | Key | Default    | Extra          |
+----------+------------------+------+-----+------------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| username | char(20)         | NO   |     |            |                |
| gender   | char(4)          | NO   |     |            |                |
| birth    | date             | NO   |     | 0000-00-00 |                |
+----------+------------------+------+-----+------------+----------------+
4 rows in set (0.05 sec)

mysql> alter table m1 change id uid int unsigned;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc m1;
+----------+------------------+------+-----+------------+-------+
| Field    | Type             | Null | Key | Default    | Extra |
+----------+------------------+------+-----+------------+-------+
| uid      | int(10) unsigned | NO   | PRI | 0          |       |
| username | char(20)         | NO   |     |            |       |
| gender   | char(4)          | NO   |     |            |       |
| birth    | date             | NO   |     | 0000-00-00 |       |
+----------+------------------+------+-----+------------+-------+
4 rows in set (0.01 sec)

mysql> exit;

----------------------------------------------------------------------------------

歡迎探討與學習。。。。。。