十、修改数据表 alter

MySQL 中 alter 语句更新表定义即改变原有表的结构。

1、修改数据表名或者修改数据表字段

在已有的表中添加、修改或删除列字段，创建或者取消索引，更改字段类型，重命名字段或者表名等。

 语法格式：

alter table 表名 [修改选项]

修改选项语法如下：

1）添加一个新字段

可以指定添加到哪个字段之后。

add 字段名 类型(长度) [COMMENT 注释] [约束] [after 已经存在的字段名]；

例如：

MariaDB [test]> show columns from w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| name  | char(4) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)MariaDB [test]> alter table w1 add num int(4) unique after name;
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

2）删除字段

drop 字段1，drop 字段2，...；

删除多个字段得写多个drop，每个字段之间用逗号（，）隔开

例如：

MariaDB [test]> alter table w1 drop name,drop num;
Query OK, 3 rows affected (0.07 sec)               
Records: 3  Duplicates: 0  Warnings: 0

3）只修改字段的数据类型和属性

不能修改字段名。

modify 字段 新数据类型 约束条件；

例如：

MariaDB [test]> alter table w1 add name int(4) not null ;
Query OK, 3 rows affected (0.06 sec)               
Records: 3  Duplicates: 0  Warnings: 0MariaDB [test]> desc w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| num   | char(4) | YES  |     | NULL    |       |
| name  | int(4)  | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)MariaDB [test]> alter table w1 modify name char(4) ;
Query OK, 3 rows affected (0.08 sec)               
Records: 3  Duplicates: 0  Warnings: 0MariaDB [test]> desc w1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| addr  | char(4) | YES  |     | NULL    |       |
| num   | char(4) | YES  |     | NULL    |       |
| name  | char(4) | YES  |     | NULL    |       |                -- null在定义数据时为默认设置
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4）修改字段名和数据类型

可以修改字段名和数据类型

CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

注意： 修改字段名必须在新字段名后面添加数据类型，就算数据类型还是原先的类型，也得再写一遍，无数据类型则修改不成功,报错。

MariaDB [test]> alter table w1 change name Name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1MariaDB [test]> alter table w1 change name Name char(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5）修改表名称

rename  [to]  新的表名；

例如：

MariaDB [test]> alter table w1 rename test1;
Query OK, 0 rows affected (0.02 sec)MariaDB [test]> alter table  test1 rename to w1;
Query OK, 0 rows affected (0.02 sec)

注： to 为可选参数，使用与否不影响结果

6）修改字段默认值

alter 字段 set default 值；    -- 设置字段默认值
alter 字段 drop default；      -- 删除默认值

注：若字段有默认值，需要先删除默认值，再进行设置新值。

例如：

MariaDB [test]> alter table test1 alter Name drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0MariaDB [test]> alter table test1 alter Name set default 'shan';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、另一种修改表名的语句

 

rename table 原表名1 to 新表名1，原表名1 to 新表名1，原表名2 to 新表名2，...；

 

例如：

MariaDB [test]> rename table w1 to test1;
Query OK, 0 rows affected (0.02 sec)

3、删除表

drop table 表名；