上一篇我们讨论了如何为字段选择合适的数据类型。选定了字段的数据类型之后，我们就可以开始创建和管理数据库中的表了。
数据库对象
数据库（Database）由一组相关的对象组成，主要包括表、索引、视图、存储过程等。为了方便对象的管理和访问，数据库通常使用模式（Schema）来组织这些对象；模式是一个逻辑单元，或者一个存储对象的容器；它们之间的关系如下图所示：
一个数据库由多个模式组成，一个模式由许多对象组成；在不同模式中可以创建同名的对象。
MySQL 中的模式和数据库是相同的概念，一个数据库对应一个同名的模式。
管理数据库
创建数据库
当我们连接到数据库服务器时，需要指定一个目标数据库。如果需要创建一个新的数据库，可以使用 CREATE DATABASE 语句：
CREATE DATABASE mydb; 复制
以上语句将会创建一个名为 mydb 的数据库。对于 Oracle 而言，通常只有一个数据库；因此很少手动创建数据库。
Oracle 12c 以及更高版本，如果采用容器数据库（Container Database）模式，可以创建多个可插拔的数据库（Pluggable Database）。本专栏未涉及这一部分内容。
我们可以使用以下命令或语句查看 MySQL 中已有的数据库：
— MySQL 实现 SHOW DATABASES; SELECT schemaname AS database_name FROM information_schema.schemata; Database | —————————| hrdb | information_schema| mydb | mysql | performance_schema| sys | 复制
其中，hrdb 是我们使用的数据库；mydb 是刚刚创建的数据库；其他都是系统数据库。information_schema 系统数据库存储了 MySQL 服务器中所有数据库的信息，例如数据库名称、表的结构以及访问权限等。
对于 SQL Server 可以使用以下命令：
— SQL Server 实现 SELECT name AS database_name FROM sys.databases; database_name| ——————-| master | tempdb | model | msdb | mydb | hrdb | 复制
其中，hrdb 是我们使用的数据库；mydb 是刚刚创建的数据库；其他都是系统数据库。sys.databases 是 SQL Server 中的一个系统表，存储了关于数据库的信息。
PostgreSQL 数据库使用以下命令：
— PostgreSQL 实现 SELECT datname AS database_name FROM pg_database; database_name| ——————-| postgres | hrdb | template1 | template0 | mydb | 复制
其中，hrdb 是我们使用的数据库；mydb 是刚刚创建的数据库；其他都是系统数据库。pg_database 是 PostgreSQL 中的一个系统表，存储了关于数据库的信息。
最后，Oracle 使用以下命令：
— Oracle 实现 SELECT name AS database_name FROM v$database; DATABASE_NAME| ——————-|_ ORCL | 复制
其中，ORCL 是我们使用的数据库。v$database 是 Oracle 中的一个系统视图，提供了关于数据库的信息。
删除数据库
如果确认不再需要，可以使用 DROP DATABASE 语句删除数据库：
DROP DATABASE mydb; 复制
DROP DATABASE 命令将会删除该数据库中的所有对象，而且操作无法恢复，使用时千万小心！
如果有用户正在连接，无法删除数据库；可以等待用户断开连接，或者强制断开连接后删除。
管理模式
创建模式
CREATE SCHEMA 命令用于创建一个新的模式：
— SQL Server 以及 PostgreSQL 实现 CREATE SCHEMA hr [AUTHORIZATION user1]; 复制
以上语句创建一个名为 hr 的模式，可选的 AUTHORIZATION 表示为该模式指定一个拥有者 user1，拥有者是一个已经存在的数据库用户。
SQL Server 创建数据库时会自动创建一个名为 dbo 的模式，PostgreSQL 创建数据库时会自动创建一个名为 public 的模式。
MySQL 中的模式等价于数据库，因此 CREATE SCHEMA 等价于 CREATE DATABASE。
Oracle 中的模式等价于用户，因此使用 CREATE USER 命令创建用户时就相当于创建一个同名的模式：
CREATE USER hr IDENTIFIED BY xxx; 复制
Oracle 也提供了 CREATE SCHEMA 命令，但不是用于创建模式，而是用于在模式中创建表、视图以及执行授权操作。
删除模式
不需要的模式可以使用 DROP SCHEMA 命令删除：
— SQL Server 以及 PostgreSQL 实现 DROP SCHEMA hr; 复制
MySQL 中的模式等价于数据库，因此 DROP SCHEMA 等价于 DROP DATABASE。
Oracle 中的模式等价于用户，因此使用 DROP USER 命令创建用户时就相当于创建一个同名的模式：
— Oracle 实现 DROP USER hr; 复制
如果模式中存在对象，则无法删除该模式；可以先删除其中的对象，再删除模式。某些数据库支持级联删除：
— PostgreSQL 实现 DROP SCHEMA hr CASCADE; — Oracle 实现 DROP USER hr CASCADE; 复制
CASCADE 表示删除模式的同时，删除该模式中的所有对象。
管理数据表
创建表
在 SQL 中，使用 CREATE TABLE 语句创建一个表：
CREATE TABLE table_name ( column1 data_type column_constraint, column2 data_type, …, table_constraint ); 复制
该语句包含以下内容：
- table_name 指定了表的名称。
- 括号内是字段的定义；columnN 是字段的名称，datatype 是它的数据类型；columnconstraint 是可选的字段约束；多个字段使用逗号进行分隔。
- table_constraint 是可选的表级约束。
其中，常见的约束包括主键、外键、唯一、非空、检查约束以及默认值。
以下语句用于创建一个新表 dept：
CREATE TABLE dept ( deptid INTEGER NOT NULL PRIMARY KEY , dept_name VARCHAR(50) NOT NULL ) ; 复制
dept 表包含两个字段，部门编号（dept_id）是一个整数类型（INTEGER），不可以为空（NOT NULL），同时它还是这个表的主键（PRIMARY KEY）；部门编号（dept_name）是一个可变长度的字符串，不允许为空。
如果想要创建一个自定义名称的主键约束，可以使用表级约束：
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 语法只复制表结构，不复制数据。
修改表
对于已经存在的表，可能会由于业务变更或者代码重构需要修改它的结构。因此，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 字段。
删除表
DROP TABLE 语句用于删除一个表。以下示例将会删除 emp_identity 表：
DROP TABLE emp_identity; 复制
截断表
SQL 还提供了一种特殊的操作：截断表（TRUNCATE），可以用于快速删除表中的所有数据。以下语句将会删除 emp_devp 中的所有数据。
TRUNCATE TABLE emp_devp; 复制
TRUNCATE 用于快速删除数据，回收表占用的空间，但会保留表的结构。MySQL 和 PostgreSQL 可以省略 TABLE 关键字。
小结
数据定义语言（Data Definition Language）用于定义数据库中各种对象的结构，例如表、视图、索引等。常见的 DDL 语句包括创建（CREATE）、修改（ALTER）和删除（DROP）。虽然各种对象的具体语法细节不同，但都遵循相同的模式；例如，创建索引可以使用 CREATE INDEX 语句；在后续文章中我们还会介绍其他对象的管理。
思考题：使用 DROP TABLE 语句删除 department 表，能不能成功执行？为什么？
若有收获，就点个赞吧
0 人点赞
