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

我们将对所有ALTER table语句使用这个shippers表。

现在假设我们想要通过增加一列来扩展现有的shippers表。但是,问题是我们如何使用SQL命令来做到这一点?让我们来看看。

添加新列

 将新列添加到现有表的基本语法:

ALTER TABLE table_name ADD column_name data_type constraints;					

下面的语句向shippers表添加了一个新的列fax。

ALTER TABLE shippers ADD fax VARCHAR(20);

现在,在执行以上语句之后,如果您使用命令DESCRIBE shippers看到了表结构; 在MySQL命令行上,如下所示:

+--------------+-------------+------+-----+---------+----------------+
| 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列,则必须指定显式的默认值。此默认值用于为表中已经存在的每一行填充新列。

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

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

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

MySQL首先提供了另一个子句,您可以使用该子句在表的第一个位置添加新列。只需将前面示例中的AFTER子句替换为FIRST,以便在shippers表的开头添加列fax。

更改列位置

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

ALTER TABLE table_name
  MODIFY column_name column_definition AFTER column_name;			

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

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

添加约束

我们当前的shippers表存在一个主要问题。即使您插入具有重复电话号码的记录,也不会阻止您这样做,这不好,电话号码应该是唯一的。

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

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

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

mysql> ALTER TABLE shippers ADD UNIQUE (phone);					

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

同样,如果您创建的表中没有PRIMARY KEY,则可以使用以下内容:

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

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

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语法。它支持可用于修改列的备选修改子句,如下所示:

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子句在MySQL表中的列是否允许空值之间进行切换,通过重新指定现有的列定义,并在最后添加null或not null约束,如下所示:

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;