05 SQL Alter

优质

小牛编辑

128浏览

2023-12-01

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 指令修改数据表是十分消耗性能和时间的，请不要在线上工作时使用它。