数据库 / 05 SQL Alter

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