如何使用 SQL CREATE TABLE 创建新表

本文介绍如何使用 SQL CREATE TABLE 创建新表,DROP TABLE 用来完整地删除一个表,ALTER TABLE 用来更改表列或其他诸如约束或索引等对象。

一、创建表

SQL 不仅用于表数据操纵,而且还用来执行数据库和表的所有操作,包括表本身的创建和处理。

一般有两种创建表的方法:

  • 多数 DBMS 都具有交互式创建和管理数据库表的工具;
  • 表也可以直接用 SQL 语句操纵。

用程序创建表,可以使用 SQL 的 CREATE TABLE 语句。

需要注意的是,使用交互式工具时实际上就是使用 SQL 语句。这些语句不是用户编写的,界面工具会自动生成并执行相应的 SQL 语句(更改已有的表时也是这样)。

本文不会介绍创建表时可以使用的所有选项,那超出了本文的范围,我只给出一些基本选项。详细的信息说明,请参阅具体的 DBMS 文档。

1.1 表创建基础

利用 CREATE TABLE 创建表,必须给出下列信息:

  • 新表的名字,在关键字 CREATE TABLE 之后给出;
  • 表列的名字和定义,用逗号分隔;
  • 有的 DBMS 还要求指定表的位置。

下面的 SQL 语句创建 Products 表:

1
2
3
4
5
6
7
8
CREATE TABLE Products
(
    prod_id       CHAR(10)          NOT NULL,
    vend_id       CHAR(10)          NOT NULL,
    prod_name     CHAR(254)         NOT NULL,
    prod_price    DECIMAL(8,2)      NOT NULL,
    prod_desc     VARCHAR(1000)     NULL
);

从上面的例子可以看到,表名紧跟 CREATE TABLE 关键字。

实际的表定义(所有列)括在圆括号之中,各列之间用逗号分隔。

这个表由 5 列组成。每列的定义以列名(它在表中必须是唯一的)开始,后跟列的数据类型(关于数据类型的解释,请参阅 学习 SQL 之前需要了解的基础知识。此外,SQL 常用的数据类型 列出了常见的数据类型及兼容性)。

整条语句以圆括号后的分号结束。

前面提到,不同 DBMS 的 CREATE TABLE 的语法有所不同,这个简单脚本也说明了这一点。

这条语句在绝大多数 DBMS 中有效,但对于 DB2,必须从最后一列中去掉 NULL。这就是对于不同的 DBMS,要编写不同的表创建脚本的原因(参见 学习 SQL 所用到的样例表脚本)。

1.2 使用 NULL 值

如何使用 SQL WHERE 过滤返回的数据 中提到,NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受没有列值的行,换句话说,在插入或更新行时,该列必须有值。

每个表列要么是 NULL 列,要么是 NOT NULL 列,这种状态在创建时由表的定义规定。请看下面的例子:

1
2
3
4
5
6
CREATE TABLE Orders
(
    order_num      INTEGER      NOT NULL,
    order_date     DATETIME     NOT NULL,
    cust_id        CHAR(10)     NOT NULL
);

这条语句创建本文中所用的 Orders 表。Orders 包含三列:订单号、订单日期和顾客 ID。这三列都需要,因此每一列的定义都含有关键字 NOT NULL

这就会阻止插入没有值的列。如果插入没有值的列,将返回错误,且插入失败。

下一个例子将创建混合了 NULLNOT NULL 列的表:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE Vendors
(
    vend_id          CHAR(10)     NOT NULL,
    vend_name        CHAR(50)     NOT NULL,
    vend_address     CHAR(50)     ,
    vend_city        CHAR(50)     ,
    vend_state       CHAR(5)      ,
    vend_zip         CHAR(10)     ,
    vend_country     CHAR(50)
);

这条语句创建 Vendors 表。供应商 ID 和供应商名字列是必需的,因此指定为 NOT NULL

其余五列全都允许 NULL 值,所以不指定 NOT NULLNULL 为默认设置,如果不指定 NOT NULL,就认为指定的是 NULL

1.3 指定默认值

SQL 允许指定默认值,在插入行时如果不给出值,DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

请看下面的例子:

1
2
3
4
5
6
7
8
CREATE TABLE OrderItems
(
    order_num      INTEGER          NOT NULL,
    order_item     INTEGER          NOT NULL,
    prod_id        CHAR(10)         NOT NULL,
    quantity       INTEGER          NOT NULL      DEFAULT 1,
    item_price     DECIMAL(8,2)     NOT NULL
);

这条语句创建 OrderItems 表,包含构成订单的各项(订单本身存储在 Orders 表中)。quantity 列为订单中每个物品的数量。

在这个例子中,这一列的描述增加了 DEFAULT 1,指示 DBMS,如果不给出数量则使用数量 1

默认值经常用于日期或时间戳列。

例如,通过指定引用系统日期的函数或变量,将系统日期用作默认日期。

MySQL 用户指定 DEFAULT CURRENT_DATE(),Oracle 用户指定 DEFAULT SYSDATE,而 SQL Server 用户指定 DEFAULT GETDATE()

遗憾的是,这条获得系统日期的命令在不同的 DBMS 中大多是不同的。

表 1 列出了这条命令在某些 DBMS 中的语法。这里若未列出某个 DBMS,请参阅相应的文档。

表 1 获得系统日期

DBMS函数/变量
DB2CURRENT_DATE
MySQLCURRENT_DATE()
OracleSYSDATE
PostgreSQLCURRENT_DATE
SQL ServerGETDATE()
SQLitedate('now')

二、更新表

更新表定义,可以使用 ALTER TABLE 语句。虽然所有的 DBMS 都支持 ALTER TABLE,但它们所允许更新的内容差别很大。

以下是使用 ALTER TABLE 时需要考虑的事情。

  • 理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动。
  • 所有的 DBMS 都允许给现有的表增加列,不过对所增加列的数据类型(以及 NULLDEFAULT 的使用)有所限制。
  • 许多 DBMS 不允许删除或更改表中的列。
  • 多数 DBMS 允许重新命名表中的列。
  • 许多 DBMS 限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制。

可以看出,对已有表做更改既复杂又不统一。对表的结构能进行何种更改,请参阅具体的 DBMS 文档。

使用 ALTER TABLE 更改表结构,必须给出下面的信息:

  • ALTER TABLE 之后给出要更改的表名(该表必须存在,否则将出错);
  • 列出要做哪些更改。

因为给已有表增加列可能是所有 DBMS 都支持的唯一操作,所以我们举个这样的例子:

1
2
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

这条语句给 Vendors 表增加一个名为 vend_phone 的列,其数据类型为 CHAR

更改或删除列、增加约束或增加键,这些操作也使用类似的语法。

注意,下面的例子并非对所有 DBMS 都有效:

1
2
ALTER TABLE Vendors
DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:

(1) 用新的列布局创建一个新表;

(2) 使用 INSERT SELECT 语句(关于这条语句的详细介绍,请参阅 如何使用 SQL INSERT 语句将数据插入到表中)从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;

(3) 检验包含所需数据的新表;

(4) 重命名旧表(如果确定,可以删除它);

(5) 用旧表原来的名字重命名新表;

(6) 根据需要,重新创建触发器、存储过程、索引和外键。

三、删除表

删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可:

1
DROP TABLE CustCopy;

这条语句删除 CustCopy 表(如何使用 SQL INSERT 语句将数据插入到表中 中创建的)。删除表没有确认步骤,也不能撤销,执行这条语句将永久删除该表。

四、重命名表

每个 DBMS 对表重命名的支持有所不同。对于这个操作,不存在严格的标准。

DB2、MariaDB、MySQL、Oracle 和 PostgreSQL 用户使用 RENAME 语句,SQL Server 用户使用 sp_rename 存储过程,SQLite 用户使用 ALTER TABLE 语句。

所有重命名操作的基本语法都要求指定旧表名和新表名。不过,存在 DBMS 实现差异。关于具体的语法,请参阅相应的 DBMS 文档。

五、小结

本文介绍了几条新的 SQL 语句。

CREATE TABLE 用来创建新表,ALTER TABLE 用来更改表列(或其他诸如约束或索引等对象),而 DROP TABLE 用来完整地删除一个表。

这些语句必须小心使用,并且应该在备份后使用。由于这些语句的语法在不同的 DBMS 中有所不同,所以更详细的信息请参阅相应的 DBMS 文档。

(完)