SQL Alter

1. 定義

維基百科：SQLAlter 指令用于已有數(shù)據(jù)表的修改，增加、修改和刪除數(shù)據(jù)表字段都可以通過 Alter 指令來完成。

慕課解釋：Alter 使用戶可以修改已創(chuàng)建的數(shù)據(jù)表，但大多數(shù)情況下數(shù)據(jù)表字段和類型需要在定義的時候就確認，雖然 Alter 可以修改字段的類型和約束，但不能過于依賴；Alter 更多的時候用于索引的添加和刪除。

2. 前言

本小節(jié)，我們將一起學(xué)習(xí) SQL Alter。

我們可以把 Alter 指令理解為一顆后悔藥，很多時候因為業(yè)務(wù)的變更，通過 Create 創(chuàng)建的數(shù)據(jù)表不能滿足現(xiàn)在的需求，這時便吃上一顆后悔藥——修改數(shù)據(jù)表結(jié)構(gòu)。

Alter 常用的操作主要有兩大類，分別是：字段操作和索引操作。在小節(jié)中，我們也將按照這樣的順序來依次學(xué)習(xí)。

本小節(jié)測試數(shù)據(jù)如下，請先在數(shù)據(jù)庫中執(zhí)行：

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代表待修改的數(shù)據(jù)表，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是待修改數(shù)據(jù)表名稱，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 索引一節(jié)中詳細介紹。本小節(jié)我們只會介紹索引和 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>  |       |
+----------+-------------+------+-----+---------+-------+

我們可以為多個字段添加一個索引，即聯(lián)合索引，如下：

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. 個人經(jīng)驗

• Alter 指令給了你吃后悔藥的權(quán)利，但不要過于依賴它，對于數(shù)據(jù)表字段的設(shè)計最好要在新建時就已經(jīng)確定。
• Alter 指令修改數(shù)據(jù)表是十分消耗性能和時間的，請不要在線上工作時使用它。