MYSQL alter 命令详解

ALTER

当我们需要修数据表的名字或者修改数据表的字段时，就需要使用alter命令。

一：删除，添加，或者修改表字段(字段名，字段类型)。

删除表字段语法：alter table 表名 drop 字段名； 下面是示例！

mysql> alter table test drop i;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(50) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

添加表字段语法： alter table 表名 add 字段名 子弹类型； 

mysql> alter table test add age int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(50) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果你想让你添加的字段到最前面或者具体某个字段之后，那么可以用参数，比如first,after，类似如下命令：

mysql> alter table test add level int first; #将新增的字段level放在最前面

mysql> alter table test add num int after level;    #将新增的num字段放在level字段之后

如果需要修改字段类型及名称，可以用modify或者change，

修改表字段类型语法：alter table 表名 modify 字段名 字段类型 (这里是要修改的字段名);

mysql> alter table test modify name char(30); #将name字段类型修改为char(30)
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| level | int(11)  | YES  |     | NULL    |       |
| num   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改字段名称语法：alter table 表名 change 原字段名 新字段名 字段类型;

mysql> alter table test change level jibie int;    #将level字段名修改为jibie，类型为int型；
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11)  | YES  |     | NULL    |       |
| num   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

二：修改字段默认值和表名

修改字段默认值语法： alter talve 表名 alter 字段名 set default 值;

mysql> alter table test  alter jibie  set  Default 1000;     #将test表中的jibie字段的默认值修改为1000
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11)  | YES  |     | 1000    |       |
| num   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

如果不需要默认值，您也可以用drop来删除它

语法：alter table 表名 alter 字段名 drop default;

mysql> ALTER TABLE test ALTER jibie DROP DEFAULT;  #把test表中的jibie子段的默认值删除掉
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show columns from test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| jibie | int(11)  | YES  |     | NULL    |       |
| num   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表名语法：alter table 表名 rename to 新表名;

mysql> alter table test rename to dabai; #修改表名为dabai
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_dabai |
+-----------------+
| dabai           |
+-----------------+
1 row in set (0.00 sec)