SQL Alter

1. 定义

维基百科：SQLAlter 指令用于已有数据表的修改，增加、修改和删除数据表字段都可以通过 Alter 指令来完成。

慕课解释：Alter 使用户可以修改已创建的数据表，但大多数情况下数据表字段和类型需要在定义的时候就确认，虽然 Alter 可以修改字段的类型和约束，但不能过于依赖；Alter 更多的时候用于索引的添加和删除。

2. 前言

本小节，我们将一起学习 SQL Alter。

我们可以把 Alter 指令理解为一颗后悔药，很多时候因为业务的变更，通过 Create 创建的数据表不能满足现在的需求，这时便吃上一颗后悔药——修改数据表结构。

Alter 常用的操作主要有两大类，分别是：字段操作和索引操作。在小节中，我们也将按照这样的顺序来依次学习。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  username varchar(20),
  age int
);

3. 字段操作

字段操作由添加字段、修改字段和删除字段三部分组成，它们语法类似，下面我们分别介绍。

3.1 添加字段

Alter 添加字段的语法如下：

ALTER TABLE [table_name] ADD ([col] [datatype]);

其中table_name代表待修改的数据表，col表示新增字段名称，datatype为新增字段类型。

3.2 例1、新增 score 字段

请书写 SQL 语句，为imooc_user表新增一个score字段，字段类型为float。

分析：

按照 Alter 添加字段语法，添加 score 字段即可。

语句：

ALTER TABLE imooc_user ADD score float;

修改后，表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | <null>  |       |
| age      | int(11)     | YES  |     | <null>  |       |
| score    | float       | YES  |     | <null>  |       |
+----------+-------------+------+-----+---------+-------+

3.3 修改字段

Alter 修改字段语法如下：

ALTER TABLE [table_name] MODIFY(COLUMN [col] [datatype]);

其中table_name是待修改数据表名称，col是待修改字段名，datatype是将要修改的字段类型。

3.4 例2、修改 username 字段

请书写 SQL 语句，修改上述的imooc_user表，使username字段的类型从varchar(20)变成varchar(30)。

分析：

按照修改字段的语法，修改 username 字段类型修改即可。

语句：

ALTER TABLE imooc_user MODIFY COLUMN username varchar(30);

如果是在 PostgreSQL 中，修改语句稍有不同，如下：

ALTER TABLE imooc_user ALTER COLUMN username TYPE varchar(30);

修改后表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES  |     | <null>  |       |
| age      | int(11)     | YES  |     | <null>  |       |
+----------+-------------+------+-----+---------+-------+

我们也可以通过 ALTER 指令来修改字段的约束，如为 age 字段添加上 NOT NULL 和 DEFAULT约束：

ALTER TABLE imooc_user MODIFY COLUMN age int NOT NULL DEFAULT 18;

如果使用PostgreSQL，则语句如下：

ALTER TABLE imooc_user ALTER age SET NOT NULL;
ALTER TABLE imooc_user ALTER age SET DEFAULT 18;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES  |     | <null>  |       |
| age      | int(11)     | NO   |     | 18      |       |
+----------+-------------+------+-----+---------+-------+

3.5 删除字段

Alter 删除字段语法如下：

ALTER TABLE [table_name] DROP [col];

其中table_name是待修改表的名称，col是待删除字段名。

3.6 例3、删除 age 字段

请书写 SQL 语句，删除imooc_user表中的age字段。

分析：

按照语法删除掉 age 字段即可。

语句：

ALTER TABLE imooc_user DROP age;

删除成功后，表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | <null>  |       |
+----------+-------------+------+-----+---------+-------+

4. 索引操作

索引操作由添加索引和删除索引两部分组成，如果你不了解索引，我们将会在 SQL 索引一节中详细介绍。本小节我们只会介绍索引和 Alter 是如何搭配使用的。

4.1 添加索引

添加索引的语法如下：

ALTER TABLE [table_name] ADD INDEX [index_name] ([col]);

其中index_name代表索引名称，col表示给那一字段添加索引。

在 PostgreSQL 中，添加索引的语法有较大差异，如下：

CREATE INDEX [index_name] ON [table_name]([col]...);

4.2 例4、添加 age 索引

请书写 SQL 语句，给imooc_user表中的age字段添加索引。

分析：

按照语法给 age 字段添加索引即可。

语句：

ALTER TABLE imooc_user ADD INDEX age_index (age);

PostgreSQL 语句如下：

CREATE INDEX age_index ON imooc_user(age);

添加成功后，表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | <null>  |       |
| age      | int(11)     | YES  | MUL | <null>  |       |
+----------+-------------+------+-----+---------+-------+

我们可以为多个字段添加一个索引，即联合索引，如下：

ALTER TABLE imooc_user ADD INDEX username_age_index (username, age);

PostgreSQL 语句如下：

CREATE INDEX username_age_index ON imooc_user(username,age);

添加成功后，表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  | MUL | <null>  |       |
| age      | int(11)     | YES  |     | <null>  |       |
+----------+-------------+------+-----+---------+-------+

4.3 删除索引

删除索引的语法如下：

ALTER TABLE [table_name] DROP INDEX [index_name];

其中index_name代表索引名称。

PostgreSQL 删除索引语法如下：

DROP INDEX [index_name];

4.4 例5、删除 username_age_index 索引

请书写 SQL 语句，删除imooc_user表中的username_age_index索引。

分析：

按照删除索引语法删除 username_age_index 索引即可。

语句：

ALTER TABLE imooc_user DROP INDEX username_age_index;

PostgreSQL 语句如下：

DROP INDEX username_age_index;

删除成功后，表信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES  |     | <null>  |       |
| age      | int(11)     | YES  |     | <null>  |       |
+----------+-------------+------+-----+---------+-------+

5. 个人经验

• Alter 指令给了你吃后悔药的权利，但不要过于依赖它，对于数据表字段的设计最好要在新建时就已经确定。
• Alter 指令修改数据表是十分消耗性能和时间的，请不要在线上工作时使用它。