MySQL講義第5講——修改表結構

在 MySQL 中可以使用 ALTER TABLE 陳述句來改變原有資料表的結構，修改表結構涉及到的操作主要有：（1）增加和洗掉欄位；（2）修改欄位的資料型別；（3）修改欄位名；（4）修改欄位的排列位置；（5）添加和洗掉完整性約束；（6）修改表名；（7）更改表的存盤引擎等，

語法格式如下：

ALTER TABLE <表名> [修改選項];

[修改選項]的格式為：
ADD COLUMN <列名> <型別>
| CHANGE COLUMN <舊列名> <新列名> <新列型別>
| ALTER COLUMN <列名> [ SET DEFAULT <默認值> | DROP DEFAULT ]
| MODIFY COLUMN <列名> <型別>
| DROP COLUMN <列名>
| RENAME TO <新表名>

以下的例子使用 employee 表，該表的結構如下：

mysql> desc employee;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | char(10)     | YES  |     | NULL    |       |
| addr  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

一、增加和洗掉欄位

1、增加欄位

語法格式如下：

ALTER TABLE <表名> ADD COLUMN <列名> <型別> <完整性約束> [FIRST | AFTER 列名];

說明：使用 [FIRST | AFTER 列名] 選項可以設定新增欄位的位置，

舉例：

（1）添加一個列 phone

mysql> alter table employee add phone char(20);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | char(10)     | YES  |     | NULL    |       |
| addr  | varchar(100) | YES  |     | NULL    |       |
| phone | char(20)     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

（2）添加一個列 salary，默認值為0，不能取空值

 mysql> alter table employee add salary decimal(10,2) default 0 not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | char(10)      | YES  |     | NULL    |       |
| addr   | varchar(100)  | YES  |     | NULL    |       |
| phone  | char(20)      | YES  |     | NULL    |       |
| salary | decimal(10,2) | NO   |     | 0.00    |       |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

（3）新增一個欄位 birth，并放在 name 欄位之后

mysql> ALTER TABLE employee ADD COLUMN birth datetime after name;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | char(10)      | YES  |     | NULL    |       |
| birth  | datetime      | YES  |     | NULL    |       |
| addr   | varchar(100)  | YES  |     | NULL    |       |
| phone  | char(20)      | YES  |     | NULL    |       |
| salary | decimal(10,2) | NO   |     | 0.00    |       |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

2、洗掉欄位

語法格式如下：

ALTER TABLE <表名> DROP COLUMN <列名>;

舉例：洗掉 employee 表中的 salary 欄位

mysql> alter table employee drop column salary;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | char(10)      | YES  |     | NULL    |       |
| birth  | datetime      | YES  |     | NULL    |       |
| addr   | varchar(100)  | YES  |     | NULL    |       |
| phone  | char(20)      | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

二、修改欄位的資料型別、名稱和欄位的位置

1、修改欄位的資料型別

修改欄位的資料型別使用 MODIFY 選項，語法格式如下：

ALTER TABLE <表名> MODIFY COLUMN <列名> <型別>;

說明：使用 MODIFY 選項不能更改欄位的名稱，

舉例：把欄位 name 的型別更為 varchar，長度為20

mysql> alter table employee modify name varchar(20);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc employee;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| birth  | datetime      | YES  |     | NULL    |       |
| addr   | varchar(100)  | YES  |     | NULL    |       |
| phone  | char(20)      | YES  |     | NULL    |       |
| salary | decimal(10,2) | NO   |     | 0.00    |       |
+--------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

2、修改欄位的名稱

修改欄位的名稱使用 CHANGE 選項，語法格式如下：

ALTER TABLE <表名> CHANGE COLUMN <舊列名> <新列名> <新列型別>;

說明：使用 CHANGE 選項時，如果新列名和舊列名相同，則作用和 MODIFY 相同，

舉例：把 addr 列的名稱修改為 address，型別修改為 varchar(200)，放到欄位 phone 之后

mysql> alter table employee change addr address varchar(200) after phone;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| name    | varchar(20)   | YES  |     | NULL    |       |
| birth   | datetime      | YES  |     | NULL    |       |
| phone   | char(20)      | YES  |     | NULL    |       |
| address | varchar(200)  | YES  |     | NULL    |       |
| salary  | decimal(10,2) | NO   |     | 0.00    |       |
+---------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3、更改欄位的位置

在使用 ADD 選項添加欄位、使用 MODIFY 選項修改欄位的型別和長度、使用 CHANGE 選項修改欄位名稱和型別時，可以使用 [FIRST | AFTER 列名] 選項指定欄位的位置，語法格式如下：

--添加欄位同時指定新增欄位的位置
ALTER TABLE <表名> ADD COLUMN <列名> <型別> <完整性約束> [FIRST | AFTER 列名]; 
--修改欄位的型別和長度同時指定欄位的位置
ALTER TABLE <表名> MODIFY COLUMN <列名> <型別> [FIRST | AFTER 列名];
--修改欄位的名稱、型別和長度同時指定欄位的位置
ALTER TABLE <表名> CHANGE COLUMN <舊列名> <新列名> <新列型別> [FIRST | AFTER 列名];

舉例：

（1）添加一個欄位 comm，型別為 varchar(500)，位置放在第一位

mysql> alter table employee add comm varchar(500) first;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| comm    | varchar(500)  | YES  |     | NULL    |       |
| id      | int(11)       | NO   | PRI | NULL    |       |
| name    | varchar(20)   | YES  |     | NULL    |       |
| birth   | datetime      | YES  |     | NULL    |       |
| phone   | char(20)      | YES  |     | NULL    |       |
| address | varchar(200)  | YES  |     | NULL    |       |
| salary  | decimal(10,2) | NO   |     | 0.00    |       |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

（2）把欄位 comm 的長度修改為 1000，并放在欄位 salary 之前

mysql> alter table employee modify comm varchar(1000) after address;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| name    | varchar(20)   | YES  |     | NULL    |       |
| birth   | datetime      | YES  |     | NULL    |       |
| phone   | char(20)      | YES  |     | NULL    |       |
| address | varchar(200)  | YES  |     | NULL    |       |
| comm    | varchar(1000) | YES  |     | NULL    |       |
| salary  | decimal(10,2) | NO   |     | 0.00    |       |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

（3）把欄位 comm 的名稱修改為 comme，并放在表的結尾

mysql> alter table employee change comm comme varchar(1000) after salary;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | NO   | PRI | NULL    |       |
| name    | varchar(20)   | YES  |     | NULL    |       |
| birth   | datetime      | YES  |     | NULL    |       |
| phone   | char(20)      | YES  |     | NULL    |       |
| address | varchar(200)  | YES  |     | NULL    |       |
| salary  | decimal(10,2) | NO   |     | 0.00    |       |
| comme   | varchar(1000) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

三、修改表名

語法格式如下：

ALTER TABLE <表名> RENAME [TO] <新表名>;

舉例：把資料表 employee 名稱修改為 emp

mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+-----------------+
| Tables_in_hist  |
+-----------------+
| dept            |
| emp             |
| stu             |
| student         |
| user_permission |
+-----------------+
5 rows in set (0.00 sec)

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/105582.html

標籤：其他

下一篇：第三十一章 Caché 函式大全 $LISTGET 函式