SQL DDL管理数据表

使用DDL创建好数据库和创建好模式后，我们就可以在数据库/模式中创建表了。

DDL 创建表

在 SQL 中，使用 CREATE TABLE 语句创建一个表：

CREATE TABLE table_name
(
  column1 data_type column_constraint,
  column2 data_type,
  ...,
  table_constraint
);

该语句包含以下内容：

• table_name 指定了表的名称。
• 括号内是字段的定义；columnN 是字段的名称，data*type* *是它的数据类型；column*****constraint 是可选的字段约束；多个字段使用逗号进行分隔。
• table_constraint 是可选的表级约束。

其中，常见的约束包括主键、外键、唯一、非空、检查约束以及默认值。

以下语句用于创建一个新表 dept：

CREATE TABLE dept
    ( dept_id    INTEGER NOT NULL PRIMARY KEY
    , dept_name  VARCHAR(50) NOT NULL
    ) ;

dept 表包含两个字段，部门编号（deptid）是一个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）；部门编号（deptname）是一个可变长度的字符串，不允许为空。

如果想要创建一个自定义名称的主键约束，可以使用表级约束：

CREATE TABLE dept
    ( dept_id    INTEGER NOT NULL
    , dept_name  VARCHAR(50) NOT NULL
    , CONSTRAINT pk_dept PRIMARY KEY (dept_id)
    ) ;

其中，pk_dept 是自定义的主键名称。

标识列

标识列（identity column），也称为自增长列（auto increment），可以自动生成一个唯一的数字；它的主要用途就是为主键提供默认的数据。首先来看一下 SQL 标准中的定义：

column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY;

其中，data_type 必须是数字类型（INTEGER、NUMERIC 等）；GENERATED ALWAYS 表示总是由系统自动生成数据值，不接受用户提供的值；GENERATED BY DEFAULT 表示如果用户提供了输入值就使用该值，否则系统会自动生成一个数据值。

以下语句为表 emp_identity 创建了一个标识列 emp_id，它也是该表的主键：

-- Oracle 以及 PostgreSQL 实现
CREATE TABLE emp_identity(
  emp_id   INT GENERATED ALWAYS AS IDENTITY, 
  emp_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

目前，只有 Oracle 和 PostgreSQL 支持这种语法。让我们尝试往该表中插入一些数据：

INSERT INTO emp_identity(emp_name) VALUES ('张三');
INSERT INTO emp_identity(emp_name) VALUES ('李四');
INSERT INTO emp_identity(emp_name) VALUES ('王五');

SELECT emp_id, emp_name
  FROM emp_identity;

EMP_ID|EMP_NAME|
------|--------|
     1|张三     |
     2|李四     |
     3|王五     |

在上面的插入语句中，我们没有为 emp_id 字段提供数据，而是由系统自动生成序列值。

除了标准 SQL 语法之外，其他数据库通过专有的语法实现了类似的功能：

-- MySQL
CREATE TABLE emp_identity(
  emp_id   INT AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

-- SQL Server
CREATE TABLE emp_identity(
  emp_id   INT IDENTITY,
  emp_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

-- PostgreSQL
CREATE TABLE emp_identity(
  emp_id   INT SERIAL,
  emp_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

其中，AUTO_INCREMENT、IDENTITY 和 SERIAL 都是表示自动增长的整数。

复制表

除了手动创建表之外，也可以基于其他表或者查询的结果创建一个表：

CREATE TABLE table_name
    AS
SELECT ...;

其中的 SELECT 语句定义了新表的结构和数据。以下示例基于查询的结果创建了一个新表：emp_devp，表中包含了研发部的所有员工。

-- Oracle、MySQL 以及 PostgreSQL 实现
CREATE TABLE emp_devp
    AS
SELECT e.*
  FROM employee e
  JOIN department d
    ON (d.dept_id = e.dept_id AND d.dept_name = '研发部');

另外，还有一些数据库专用的语法形式：

-- SQL Server 和 PostgreSQL 实现
SELECT e.*
  INTO emp_devp
  FROM employee e
  JOIN department d
    ON (d.dept_id = e.dept_id AND d.dept_name = '研发部');

-- MySQL 实现
CREATE TABLE emp_devp
  LIKE employee;

其中，MySQL 的 LIKE 语法只复制表结构，不复制数据。

DDL 修改表

对于已经存在的表，可能会由于业务变更或者代码重构需要修改它的结构。因此，SQL 定义了修改表的语句：

ALTER TABLE table_name action;

其中的 action 表示执行的操作，常见的操作包括增加列，修改列，删除列；增加约束，修改约束，删除约束等。以下语句用于增加一个新的字段：

ALTER TABLE table_name 
  ADD [COLUMN] column_name data_type column_constraint;

添加字段的内容和创建表时类似，包括字段名、数据类型以及可选的列约束。Oracle 和 SQL Server 不支持 COLUMN 关键字，省略即可。

以下语句为表 emp_identity 新增一个字段 commission_pct：

ALTER TABLE emp_identity
  ADD bonus NUMERIC(8,2);

如果某个字段不再需要，可以使用 DROP COLUMN 操作删除：

ALTER TABLE emp_identity
  DROP COLUMN bonus;

以上语句将会删除 emp_identity 表中的 bonus 字段。

DDL 删除表

DROP TABLE 语句用于删除一个表。以下示例将会删除 emp_identity 表：

DROP TABLE emp_identity;

DDL 截断表

SQL 还提供了一种特殊的操作：截断表（TRUNCATE），可以用于快速删除表中的所有数据。以下语句将会删除 emp_devp 中的所有数据。

TRUNCATE TABLE emp_devp;

TRUNCATE 用于快速删除数据，回收表占用的空间，但会保留表的结构。MySQL 和 PostgreSQL 可以省略 TABLE 关键字。