MySQL 学习笔记（13）— 创建表、修改表、删除表、清空表

1. 创建表

在 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 是可选的表级约束。

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

CREATE TABLE product
(
product_id                CHAR(4)              NOT NULL,
product_name              VARCHAR(100)         NOT NULL,
product_type              VARCHAR(32)         NOT NULL,
sale_price                INTEGER				      ,
purchase_price            INTEGER                     ,
regist_date               DATE                         ,
PRIMARY KEY (product_id)
);


CREATE TABLE addressbook
(
regist_no       INTEGER           NOT NULL,
name            VARCHAR(128)      NOT NULL,
address         VARCHAR(259)      NOT NULL,
tel_no          CHAR(10)                  ,
mail_address    CHAR(20)                  ,
PRIMARY KEY (regist_no)
);

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

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

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

1.1 标识列

标识列（ 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)
);

除了标准 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 都是表示自动增长的整数。

2. 修改表

对于已经存在的表，可能会由于业务变更或者代码重构需要修改它的结构。因此， 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;

3. 删除表

DROP TABLE 语句用于删除一个表。以下语句用于删除名称为 table_name 的表。

DROP TABLE table_name

4. 截断表

截断表（ TRUNCATE ），可以用于快速删除表中的所有数据。以下语句将会删除 emp_devp 中的所有数据。

TRUNCATE TABLE emp_devp;

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