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 关键字。

赞(2)
未经允许不得转载:极客笔记 » SQL DDL管理数据表
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址