在本教程中,您将学习如何使用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;