当需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。
1)删除、添加或修改表字段
mysql> select * from tb1;
+----+--------------+----------+------------+
| id | title | author | date |
+----+--------------+----------+------------+
| 1 | 学习mysql | lihaogn | 2018-07-13 |
| 2 | 学习java | zhangsan | 2018-06-30 |
| 3 | 学习scala | wanger | NULL |
| 4 | 学习python | caliu | 2018-08-29 |
| 5 | 学习spark | wusan | 2018-08-29 |
| 6 | 学习hadoop | gaowu | 2018-08-29 |
| 7 | 学习storm | songba | 2018-08-31 |
| 8 | 学习php | zhaosi | 2018-08-31 |
+----+--------------+----------+------------+
8 rows in set (0.00 sec)
// 查看表中有哪些字段
mysql> show columns from tb1;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| author | varchar(40) | NO | | NULL | |
| date | date | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
// 删除一个字段
mysql> ALTER TABLE tb1 DROP author;
// 添加一个字段
mysql> ALTER TABLE tb1 ADD name varchar(20);
mysql> show columns from tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| date | date | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
// 在指定位置添加字段
mysql> ALTER TABLE tb1 ADD type varchar(10) AFTER title;
mysql> show columns from tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| type | varchar(10) | YES | | NULL | |
| date | date | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
注意:
- 如果数据表中只剩余一个字段则无法使用DROP来删除字段。
2)修改字段类型与名称
// 使用MODIFY
mysql> ALTER TABLE tb1 MODIFY type INT;
// 使用CHANGE
mysql> ALTER TABLE tb1 CHANGE name name varchar(10);
mysql> show columns from tb1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| type | int(11) | YES | | NULL | |
| date | date | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
// 使用CHANGE,指定新的字段名和类型
mysql> ALTER TABLE tb1 CHANGE name author_name varchar(15);
mysql> show columns from tb1;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| type | int(11) | YES | | NULL | |
| date | date | YES | | NULL | |
| author_name | varchar(15) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
3)修改字段默认值
// 修改字段的默认值
mysql> ALTER TABLE tb1 ALTER author_name SET DEFAULT 'UNKNOWN';
mysql> ALTER TABLE tb1 ALTER type SET DEFAULT 0;
mysql> show columns from tb1;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| type | int(11) | YES | | 0 | |
| date | date | YES | | NULL | |
| author_name | varchar(10) | NO | | UNKNOWN | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE tb1 MODIFY title varchar(100) NOT NULL DEFAULT 'UNKNOWN';
mysql> show columns from tb1;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | UNKNOWN | |
| type | int(11) | YES | | 0 | |
| date | date | YES | | NULL | |
| author_name | varchar(10) | NO | | UNKNOWN | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
// 删除字段的默认值
mysql> ALTER TABLE tb1 ALTER author_name DROP DEFAULT;
mysql> show columns from tb1;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | UNKNOWN | |
| type | int(11) | YES | | 0 | |
| date | date | YES | | NULL | |
| author_name | varchar(10) | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
// 修改数据表类型
ALTER TABLE tb1 ENGINE=XXXX;
-- 修改not null 为 null
ALTER TABLE 表名 MODIFY 字段名 VARCHAR(20) DEFAULT NULL
4)修改表名
mysql> ALTER TABLE tb1 RENAME TO tb1_study;