SQL ALTER TABLE簡單使用

本次環境使用的是MySQL5.7版

1.新增一個欄位至該表
ALTER TABLE tableName
ADD column_name datatype;

mysql> SELECT * FROM Persons_copy;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|       11 | Lester   | Mark      | No      | No   |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE Persons_copy ADD column_name varchar(255);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Persons_copy;
+----------+----------+-----------+---------+------+-------------+
| PersonID | LastName | FirstName | Address | City | column_name |
+----------+----------+-----------+---------+------+-------------+
|       11 | Lester   | Mark      | No      | No   | NULL        |
+----------+----------+-----------+---------+------+-------------+
1 row in set (0.00 sec)

2.刪除一個欄位至該表
ALTER TABLE tableName
DROP column_name;

mysql> ALTER TABLE Persons_copy DROP COLUMN column_name;
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Persons_copy;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|       11 | Lester   | Mark      | No      | No   |
+----------+----------+-----------+---------+------+
1 row in set (0.00 sec)

3.修改一個欄位類型

在SQL Server / MS Access：
ALTER TABLE tableName
ALTER COLUMN columnName datatype;

在My SQL / Oracle (prior version 10G)：
ALTER TABLE tableName
MODIFY COLUMN columnName datatype;

在Oracle 10G and later：
ALTER TABLE tableName
MODIFY columnName datatype;

mysql> SELECT COLUMN_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'Persons_copy';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| PersonID    | int       |
| LastName    | varchar   |
| FirstName   | varchar   |
| Address     | varchar   |
| City        | varchar   |
+-------------+-----------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE Persons_copy
    -> MODIFY COLUMN City char(50);
Query OK, 1 row affected (0.48 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT COLUMN_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'Persons_copy';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| PersonID    | int       |
| LastName    | varchar   |
| FirstName   | varchar   |
| Address     | varchar   |
| City        | char      |
+-------------+-----------+
5 rows in set (0.00 sec)

4.修改欄位名稱與欄位類型

在MYSQL
ALTER TABLE tableName
CHANGE oldColumn newColumn datatype;
在Oracle
ALTER TABLE tableName
RENAME COLUMN oldColumn TO newColumn;

mysql> SELECT COLUMNS_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'Persons_copy';
ERROR 1054 (42S22): Unknown column 'COLUMNS_NAME' in 'field list'
mysql> SELECT COLUMN_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'Persons_copy';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| PersonID    | int       |
| LastName    | varchar   |
| FirstName   | varchar   |
| Address     | varchar   |
| City        | char      |
+-------------+-----------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE Persons_copy CHANGE City Test varchar(255);
Query OK, 4 rows affected (0.54 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT COLUMN_NAME, DATA_TYPE
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_NAME = 'Persons_copy';
+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
+-------------+-----------+
| PersonID    | int       |
| LastName    | varchar   |
| FirstName   | varchar   |
| Address     | varchar   |
| Test        | varchar   |
+-------------+-----------+
5 rows in set (0.00 sec)