SQL ALTER TABLE修改数据表的用法（非常详细）

假如已经创建好了一张数据表，但是由于各种原因需要修改这张表，那就需要用到增加/删除字段的操作。这种情况在企业中很常见，比如企业开发的系统需要增加一个功能，那就需要在目前已创建好的数据表中增加一个字段来为该功能提供数据存储支持。

在 MySQL/MariaDB 数据库中，可以使用 ALTER TABLE 命令来改变现有数据表的结构，例如，增加或删除列（字段）、更改列的数据类型、重新命名列或表等。

ALTER TABLE 命令修改数据表的知识点较多，可以将修改数据表分成两类来学习掌握：

• 一类是增加或删除字段；
• 另一类是修改表以及字段目前的属性。

ALTER TABLE增加/删除字段

在一张表中增加新的列（字段），可以在开头、中间、末尾这 3 个位置增加，在这 3 个位置增加列的语句略微会有一些差异。

1) 表末尾添加字段

在表的末尾增加列（字段），其语法格式为：

ALTER TABLE 表名 ADD 新字段名称 数据类型[(宽度)] [字段属性|约束] [索引] [注释];

各个字段的含义如下：

• ALTER TABLE：修改表，关键字；
• 表名：要对哪个表进行修改；
• ADD：增加新的列（字段），关键字；
• 新字段名称：新增加的字段的名称；
• 数据类型[(宽度)]：指定新字段要存储的数据类型，宽度可选；
• [字段属性|约束][索引][注释]：可选。

【实例】在表的末尾增加列（字段）。

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| mathematics| char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
5 rows in set (0.001 sec)

MariaDB [test01]> alter table stu_score add sports char(3) not null;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| mathematics| char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
6 rows in set (0.001 sec)

2) 表开头添加字段

如果想在表的开头位置添加新的列（字段），就需要使用 FIRST 关键字，其语法格式为：

ALTER TABLE 表名 ADD 新字段名称 数据类型[(宽度)] [字段属性|约束] [索引] [注释] FIRST;

【实例】在表的开头位置添加新的列（字段）。

MariaDB [test01]> alter table stu_score add id int(5) not null first;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(5)       | NO   |     | NULL              |                |
| stu_id      | int(11)      | NO   |     | NULL              |                |
| name        | varchar(20)  | NO   | PRI | NULL              |                |
| english     | char(3)      | NO   |     | NULL              |                |
| mathematics | char(3)      | YES  |     | NULL              |                |
| geography   | char(3)      | NO   |     | NULL              |                |
| sports      | char(3)      | NO   |     | NULL              |                |
+-------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [test01]>

3) 表中间添加字段

如果想在表的中间某个指定的位置添加新的列（字段），就需要用到 AFTER 关键字。需要注意的是，AFTER 关键字只能在指定字段的后面添加新字段，不能在它的前面添加新字段。其语法格式为：

ALTER TABLE 表名 ADD 新字段名称 数据类型[(宽度)] [字段属性|约束] [索引] [注释] AFTER 指定字段；

指定字段表示要在该字段后插入新的字段。

【实例】在“english”字段后面插入一个新字段。

MariaDB [test01]> alter table stu_score add computer char(5) not null after english;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| computer   | char(5)      | NO   |     | NULL              |                |
| mathematics| char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
8 rows in set (0.001 sec)

MariaDB [test01]>

可以看到，在“english”字段后面插入了一个“computer”字段。

4) 删除字段

删除字段的操作十分简单，使用 DROP关 键字就可以删除指定的字段，其语法格式为：

ALTER TABLE 表名 DROP 字段名；

这里的字段名表示将要删除的字段的名称。

【实例】删除刚刚新添加的“computer”字段。

MariaDB [test01]> alter table stu_score drop computer;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| mathematics| char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [test01]>

ALTER TABLE修改数据表及其属性

修改数据表及表的某项属性还是得通过 ALTER TABLE 命令，例如修改表名、表中某个字段的名称、数据类型或字符集等。

1) 修改表的名称

如果想要修改表的名称，可以通过 RENAME 关键字实现。修改表的名称并不会涉及表的结构，仅仅是将表的名称换一下而已。其语法格式为：

ALTER TABLE 旧表名 RENAME TO 新表名；

【实例】将现有的表名 stu_score 改成 stu_chengji，再将名称换回来。

MariaDB [test01]> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| stu_score        |
+------------------+
1 row in set (0.000 sec)

MariaDB [test01]> alter table stu_score rename to stu_chengji;
Query OK, 0 rows affected (0.003 sec)

MariaDB [test01]> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| stu_chengji      |
+------------------+
1 row in set (0.000 sec)

MariaDB [test01]> alter table stu_chengji rename to stu_score;
Query OK, 0 rows affected (0.003 sec)

MariaDB [test01]> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| stu_score        |
+------------------+
1 row in set (0.001 sec)

MariaDB [test01]>

一般在企业中，每个表的名称设计好后就不会再变更了，因为表的名称更改后，之前代码中与数据库对接的那部分代码也需要随之变更。

2) 修改表的字符集

修改表的字符集也可以通过 ALTER TABLE 命令实现，其语法格式为：

ALTER TABLE表名DEFAULT CHARACTER SET字符集名DEFAULT COLLATE校对规则名；

【实例】将表 stu_score 的 utf8 字符集改成 gb2312 字符集。

MariaDB [test01]> show create table stu_score \G;
*************************** 1. row ***************************
Table: stu_score
Create Table: CREATE TABLE `stu_score` (
  `id` int(5) NOT NULL,
  `stu_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `english` varchar(10) DEFAULT NULL,
  `shuxue` char(3) DEFAULT NULL,
  `geography` char(3) NOT NULL,
  `sports` char(3) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.000 sec)

MariaDB [test01]> alter table stu_score default character set gb2312 default collate gb2312_chinese_ci;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> show create table stu_score \G;
*************************** 1. row ***************************
Table: stu_score
Create Table: CREATE TABLE `stu_score` (
  `id` int(5) NOT NULL,
  `stu_id` int(11) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb3 NOT NULL,
  `english` varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
  `shuxue` char(3) CHARACTER SET utf8mb3 DEFAULT NULL,
  `geography` char(3) CHARACTER SET utf8mb3 NOT NULL,
  `sports` char(3) CHARACTER SET utf8mb3 NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.000 sec)

MariaDB [test01]>

3) 修改表的字段名

若想修改表中某个字段的名称，需要用到 CHANGE 关键字，通过 CHANGE 关键字不仅可以修改字段的名称，还可以修改字段的数据类型，其语法格式为：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型；

语法格式中的所有部分都不能省略，如果不想更改数据类型，则将其设置成原数据类型即可。

【实例】将表中 mathematics 字段的名称修改为 shuxue。

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| English    | char(3)      | NO   |     | NULL              |                |
| mathematics| char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [test01]> alter table stu_score change mathematics shuxue char(3);
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| shuxue     | char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [test01]>

笔者不建议大家修改字段的数据类型，因为如果表中已经有数据，修改数据类型可能会影响到现有的数据。

4) 修改字段的数据类型

如果只想修改某个字段的数据类型，可以通过 MODIFY 关键字实现，其语法格式为：

ALTER TABLE 表名 MODIFY 字段名 数据类型；

【实例】将 english 字段的 char 数据类型修改为 varchar 数据类型，并将存储宽度提高。

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | char(3)      | NO   |     | NULL              |                |
| shuxue     | char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [test01]> alter table stu_score modify english varchar(10);
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test01]> desc stu_score;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(5)       | NO   |     | NULL              |                |
| stu_id     | int(11)      | NO   |     | NULL              |                |
| name       | varchar(20)  | NO   | PRI | NULL              |                |
| english    | varchar(10)  | YES  |     | NULL              |                |
| shuxue     | char(3)      | YES  |     | NULL              |                |
| geography  | char(3)      | NO   |     | NULL              |                |
| sports     | char(3)      | NO   |     | NULL              |                |
+------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.002 sec)

MariaDB [test01]>