MySQL 11 ALTER 命令

当需要修改数据表名或者修改数据表字段时，就需要使用到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;