SQL Alter

1. 定義

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

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

2. 前言

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

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

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

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

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

3. 字段操作

字段操作由添加字段、修改字段和刪除字段三部分組成，它們語(yǔ)法類(lèi)似，下面我們分別介紹。

3.1 添加字段

Alter 添加字段的語(yǔ)法如下：

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

其中table_name代表待修改的數(shù)據(jù)表，col表示新增字段名稱(chēng)，datatype為新增字段類(lèi)型。

3.2 例1、新增 score 字段

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，為imooc_user表新增一個(gè)score字段，字段類(lèi)型為float。

分析：

按照 Alter 添加字段語(yǔ)法，添加 score 字段即可。

語(yǔ)句：

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 修改字段語(yǔ)法如下：

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

其中table_name是待修改數(shù)據(jù)表名稱(chēng)，col是待修改字段名，datatype是將要修改的字段類(lèi)型。

3.4 例2、修改 username 字段

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，修改上述的imooc_user表，使username字段的類(lèi)型從varchar(20)變成varchar(30)。

分析：

按照修改字段的語(yǔ)法，修改 username 字段類(lèi)型修改即可。

語(yǔ)句：

ALTER TABLE imooc_user MODIFY COLUMN username varchar(30);

如果是在 PostgreSQL 中，修改語(yǔ)句稍有不同，如下：

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>  |       |
+----------+-------------+------+-----+---------+-------+

我們也可以通過(guò) ALTER 指令來(lái)修改字段的約束，如為 age 字段添加上 NOT NULL 和 DEFAULT約束：

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

如果使用PostgreSQL，則語(yǔ)句如下：

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 刪除字段語(yǔ)法如下：

ALTER TABLE [table_name] DROP [col];

其中table_name是待修改表的名稱(chēng)，col是待刪除字段名。

3.6 例3、刪除 age 字段

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，刪除imooc_user表中的age字段。

分析：

按照語(yǔ)法刪除掉 age 字段即可。

語(yǔ)句：

ALTER TABLE imooc_user DROP age;

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

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

4. 索引操作

索引操作由添加索引和刪除索引兩部分組成，如果你不了解索引，我們將會(huì)在 SQL 索引一節(jié)中詳細(xì)介紹。本小節(jié)我們只會(huì)介紹索引和 Alter 是如何搭配使用的。

4.1 添加索引

添加索引的語(yǔ)法如下：

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

其中index_name代表索引名稱(chēng)，col表示給那一字段添加索引。

在 PostgreSQL 中，添加索引的語(yǔ)法有較大差異，如下：

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

4.2 例4、添加 age 索引

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，給imooc_user表中的age字段添加索引。

分析：

按照語(yǔ)法給 age 字段添加索引即可。

語(yǔ)句：

ALTER TABLE imooc_user ADD INDEX age_index (age);

PostgreSQL 語(yǔ)句如下：

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>  |       |
+----------+-------------+------+-----+---------+-------+

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

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

PostgreSQL 語(yǔ)句如下：

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 刪除索引

刪除索引的語(yǔ)法如下：

ALTER TABLE [table_name] DROP INDEX [index_name];

其中index_name代表索引名稱(chēng)。

PostgreSQL 刪除索引語(yǔ)法如下：

DROP INDEX [index_name];

4.4 例5、刪除 username_age_index 索引

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，刪除imooc_user表中的username_age_index索引。

分析：

按照刪除索引語(yǔ)法刪除 username_age_index 索引即可。

語(yǔ)句：

ALTER TABLE imooc_user DROP INDEX username_age_index;

PostgreSQL 語(yǔ)句如下：

DROP INDEX username_age_index;

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

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

5. 個(gè)人經(jīng)驗(yàn)

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