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