SQL ALTER TABLE 语句

在本教程中,你将学习如何使用 SQL 更改或修改现有表。

修改现有表

很有可能在创建表后,当你开始使用它时,你可能会发现你忘记提及任何列或约束或为列指定了错误的名称。

在这种情况下,你可以使用 ALTER TABLE 语句通过添加、更改或删除表中的列来更改现有表。

考虑我们的数据库中有一个 *shippers * 表,其结构如下:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

我们将把这个 shippers 表用于我们的所有 ALTER TABLE 语句例子。

现在假设我们想通过再添加一列来扩展现有的 shippers 表。但问题是我们如何使用 SQL 命令来做到这一点? 好吧,让我们找出答案。

添加新列

可以使用以下方法给出将新列添加到现有表的基本语法:

ALTER TABLE table_name ADD column_name data_type constraints ; 

以下语句将新的列 fax 添加到 shippers 表。

ALTER TABLE shippers ADD fax VARCHAR(20);

现在,你执行上述语句后如果你在 MySQL 命令行上使用 DESCRIBE shippers; 命令看到表结构,它看起来如下:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
| fax          | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

注意: 如果要添加 NOT NULL 列到现有表,则必须指定显式默认值。此默认值用于为表中已存在的每一行填充新列。

提示: 向表中添加新列时,如果既未指定也 NULLNOT NULL 指定,则将列视为已指定 NULL

MySQL 默认在末尾添加新列。但是,如果要在特定列之后添加新列,则可以使用 AFTER 子句,如下所示:

mysql> ALTER TABLE shippers ADD fax VARCHAR ( 20 ) AFTER shipper_name; 

MySQL 提供了另一个子句 FIRST ,你可以使用该子句在表中的第一个位置添加新列。只需替换上一个示例中的子句 AFTERFIRST ,即可在 shippers 表的开头添加列 *fax*。

更改列位置

在 MySQL 中,如果你已经创建了一个表但对表中的现有列位置不满意,则可以使用以下语法随时更改它:

ALTER TABLE table_name MODIFY column_name column_definition AFTER column_name ; 

以下语句将 shipper_name 列之后的列 fax 放在 shippers 表中。

mysql> ALTER TABLE shippers MODIFY fax VARCHAR ( 20 ) AFTER shipper_name; 

添加约束

我们目前的 shippers 表有一个主要问题。如果你插入带有重复电话号码的记录,它不会阻止你这样做,这不好,它应该是唯一的。

你可以通过向*电话*列添加约束 UNIQUE 来解决此问题。将此约束添加到现有表列的基本语法可以通过以下方式给出:

ALTER TABLE table_name ADD UNIQUE ( column_name ,...); 

以下语句向 phone 列添加 UNIQUE 约束。

mysql> ALTER TABLE shippers ADD UNIQUE (phone); 

如果你尝试插入重复的电话号码,执行此语句后,你将收到错误消息。

同样,如果你创建了一个没有 PRIMARY KEY 的表,你可以添加一个:

ALTER TABLE table_name ADD PRIMARY KEY ( column_name ,...); 

如果未定义,则以下语句将向 shipper_id 列添加 PRIMARY KEY 约束。

mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id); 

删除列

从现有表中删除列的基本语法可以通过以下方式给出:

ALTER TABLE table_name DROP COLUMN column_name ; 

以下语句从 shippers 表中删除我们新添加的列 *fax*。

mysql> ALTER TABLE shippers DROP COLUMN fax; 

现在,如果你看到表结构,执行上述语句后,它看起来如下:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(20) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

更改列的数据类型

你可以使用以下 ALTER 子句修改 SQL Server 中列的数据类型,如下所示:

ALTER TABLE table_name ALTER COLUMN column_name new_data_type ; 

但是 MySQL 数据库服务器不支持 ALTER COLUMN 语法,但它支持 MODIFY 子句,你可以使用它来修改列,如下所示:

ALTER TABLE table_name MODIFY column_name new_data_type ; 

下面的语句改变 shippers 表中 phone 列的数据类型从 VARCHAR 更改到 CHAR,长度从 20 变为 15。

mysql> ALTER TABLE shippers MODIFY phone CHAR ( 15 ); 

类似地,你可以使用 MODIFY 子句通过重新指定现有列定义并在末尾添加 NULLNOT NULL 约束来切换 MySQL 表中的列是否应该允许空值,如下所示:

mysql> ALTER TABLE shippers MODIFY shipper_name CHAR ( 15 ) NOT NULL ; 

重命名表

在 MySQL 中重命名现有表的基本语法可以通过以下方式给出:

ALTER TABLE current_table_name RENAME new_column_name ; 

以下语句重命名我们的 shippers 表为 *shipper*。

mysql> ALTER TABLE shippers RENAME shipper; 

你也可以使用 RENAME TABLE 语句在 MySQL 中实现相同的功能,如下所示:

mysql> RENAME TABLE shippers TO shipper;