四、修改表结构
语法:1. 修改表名
ALTER TABLE 表名
RENAME 新表名;2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;3. 删除字段
ALTER TABLE 表名
DROP 字段名;4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
修改表名 rename
mysql> create table t2(id int,name char(5));
Query OK,0 rows affected (0.24sec)
mysql>show tables;+---------------+
| Tables_in_db1 |
+---------------+
| innodb__t_t1 |
| innodb__t_t2 |
| innodb__t_t3 |
| innodb__t_t4 |
| t1 |
| t2 |
+---------------+
6 rows in set (0.00sec)
mysql>alter table t2 rename t3;
Query OK,0 rows affected (0.20sec)
mysql>show tables;+---------------+
| Tables_in_db1 |
+---------------+
| innodb__t_t1 |
| innodb__t_t2 |
| innodb__t_t3 |
| innodb__t_t4 |
| t1 |
| t3 |
+---------------+
6 rows in set (0.00 sec)
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
添加字段
mysql> alter table t3 add age int,add sex enum('male','female');
Query OK,0 rows affected (0.21sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.10 sec)
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
指定添加的字段在哪个位置
first 添加到第一个
mysql> alter table t3 add hobby char(50) first;
Query OK,0 rows affected (0.17sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| hobby | char(50) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
放在哪个字段的后面
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
mysql> alter table t3 add hobby char(50) after name;
Query OK,0 rows affected (0.09sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| hobby | char(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
mysql>alter table t3 drop hobby ;
Query OK,0 rows affected (0.06sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…]
mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00sec)
mysql> alter table t3 modify name char(11) ;
Query OK,0 rows affected (0.92sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
改数据类型
mysql> alter table t3 modify name int(11) ;
Query OK,0 rows affected (0.20sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
mysql> alter table t3 change name NAME int(11) ;
Query OK,0 rows affected (0.06sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
mysql> alter table t3 change NAME name char(11) ;
Query OK,0 rows affected (0.25sec)
Records:0 Duplicates: 0 Warnings: 0mysql>desc t3;+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
总结:
如果只想改数据类型 用modify
如果想改字段名 或者 想改 字段名 和数据类型 用change