SQL奇遇记：构筑数据世界的基石

SQL DDL（数据定义语言）介绍

SQL 分为几个部分，其中 DDL（数据定义语言）是非常重要的一部分。DDL 包括了用于定义或修改数据库结构的命令，例如创建、修改、删除数据库中的表格和其他对象。DDL 主要包含以下几种类型的语句：

1. CREATE：用于创建新的数据库、表格、视图或其他数据库对象。
2. ALTER：用于修改现有数据库对象的结构。
3. DROP：用于删除数据库中的对象。
4. TRUNCATE：用于删除表中的所有行，但保留表结构。

接下来我们详细介绍一下各种语法的具体用法

CREATE

CREATE 可以用于创建数据库、数据表、索引以及视图。

创建数据库

CREATE DATABASE 语句用于创建数据库，这里的数据库并非创建一个数据库系统，而是在数据库系统中创建一个 schema， schema 你可以理解为是数据表的分组，例如不同的系统，会创建不同的 schema，然后对应系统的数据表创建在自身系统所在的 schema 下。我们要创建新的数据表，也是需要先创建一个 schema。

下面我来看一下具体语法

语法

CREATE DATABASE dbname;

现在我们创建一个名为 “study_db”，对应的语法如下

CREATE DATABASE study_db;

创建数据表

CREATE TABLE 语句用来创建一张数据表，数据库中的表由行和列组成，跟你平时见到的 Excel 表格很相似，那么创建一张数据库表，一般需要我们指定 “表名”、“列名”，“数据类型”，“约束条件”等等。下面我们来看一下具体语法

语法

CREATE TABLE 表名称 (
    列名称1 数据类型 [约束],
    列名称2 数据类型 [约束],
    ...
);

比如我们想创建一个图书表，记录图书馆中图书的名称和作者，就可以这样定义。
表名是 book，里面有三个字段，第一个是 id，它是 bigint 整数类型，第二个是 name，它是 varchar(90) 字符串类型，最后一个字段是 author，它是 varchar(30)字符串类型。

三个字段都是不能为空，具体作用如下

• id 字段作为表的主键。
• name 用来记录图书名。
• author 用来记录图书作者姓名。

对应的 DDL 语句如下

CREATE TABLE book
(
    id               bigint AUTO_INCREMENT COMMENT '主键'
        PRIMARY KEY,
    name             varchar(90) NOT NULL COMMENT '图书名',
    author           varchar(30) NOT NULL COMMENT '作者'
)
    COMMENT '图书表';

现在我们来逐句分析上述 DDL 语句

• CREATE TABLE book：CREATE TABLE 是固定写法，后面 book 就是我们要创建的表名。

• id bigint AUTO_INCREMENT COMMENT '主键'

  • id：字段名
  • bigint：字段类型为长整数类型
  • AUTO_INCREMENT：表示这个字段是子增长的，后续我们会详细讲作用
  • COMMENT ‘主键’：字段的注释

• name varchar(90) NOT NULL COMMENT '图书名'

  • name 字段名
  • varchar(90) 字段类型为字符串类型，并且最长显示 90 个字符
  • NOT NULL：字段约束，表示这个字段不能为空，也就是一定要有值
  • COMMENT ‘图书名’：字段的注释

• author varchar(30) NOT NULL COMMENT '作者'

  • author 字段名
  • varchar(30) 字段类型为字符串类型，并且最长显示 30 个字符
  • NOT NULL：字段约束，表示这个字段不能为空，也就是一定要有值
  • COMMENT ‘作者’：字段的注释

• COMMENT '图书表'：数据表的注释

• ； 最后的分号表示 DDL 语句的结束。

关于约束条件，现在看不懂没关系，后续会有专门一个章节去学习约束条件，这里大家有个印象就好了。

创建索引

在 SQL 中，索引是用来提高数据库查询效率的数据结构，后续会有专门的章节去学习索引，在 DDL 章节中我们只需要学习如何创建和删除索引即可。

语法

CREATE INDEX 索引名 ON 表名(列名1, 列名2, ..., 列名N);

假设现在给 book 的 name 字段添加一个索引，对应的语句如下

CREATE index idx_name
              ON book (name)

索引的命名也是有规范的，以及阿里的开发规范来讲，普通索引名为 idx_字段名 、主键索引名为 pk_主键字段名、外键索引名为 pk_字段名、唯一索引名为 uk_字段名。

创建视图

视图是基于 SQL 语句的结果集的可视化的表。简单来说就是用其他表格查询出来的数据拼凑成一张虚拟表，同样你可以以查询正常表一样去查询视图。视图的特点如下：

• 简化复杂的查询：视图可以将复杂的查询封装起来，使用户在使用时只需通过简单的 SELECT 查询视图即可。
• 保护数据：通过视图，可以限制用户访问表中的特定数据，而不是整个表。
• 重用 SQL 语句：如果某个查询操作频繁使用，可以将其保存为视图，这样就不需要每次都重新编写相同的 SQL 语句了。

不过在实际场景中，视图一般用作 “保护数据”

语法

-- 基础语法
CREATE VIEW 视图名称 AS
SELECT 列名称
FROM 表名称
WHERE 条件;

假设我们有一张 employee 员工表，包含 id、name、department 等字段。

1. 创建单表视图

如果我们经常需要查看 IT 部门的员工，可以创建如下视图

CREATE VIEW it_department AS
SELECT id, name
FROM employee
WHERE department = 'IT';

这样后续查询 IT 部门的员工，只需要通过 it_department 视图即可，即 SELECT id, name FROM it_department

1. 创建多表关联视图

假设除了员工表 employee，我们还有一个薪资表 salary，包含了 employee_code 和 salary 两个字段。我们可以创建一个视图来展示每个员工以及薪资：

CREATE VIEW employee_salary AS
SELECT e.id, e.name, s.salary
FROM employee e
JOIN salary s ON e.id = s.employee_id;

ALTER

ALTER 语句用于修改数据表、字段、索引等等。因为在表创建好之后，我们有时还需要对表结构进行修改，例如添加新字段、删除旧字段、修改旧字段以及修改视图的操作。

添加字段

语法

ALTER TABLE 表名称
    ADD 列名称 数据类型 [约束]

假设我们给 book 表添加一个新字段来记录出版社，字段名 publisher，类型 verchar(64)，并且设置为不能为 NULL，对应的 ALTER 语句如下

ALTER TABLE book
    ADD publisher varchar(64) NOT NULL COMMENT '出版社';

修改字段

语法

ALTER TABLE 表名称
    ALTER COLUMN 列名称 数据类型 [约束]

比如我们可以修改字段名和修改字段类型，以及字段约束，对应的语法如下

-- 修改 publisher 字段名 为 publisher_name
ALTER TABLE book
    CHANGE publisher publisher_name varchar(64) NOT NULL COMMENT '出版社';
    
-- 修改 publisher 字段类型为 char(64)
ALTER TABLE book
    MODIFY publisher char(64) NOT NULL COMMENT '出版社';

-- 删除 publisher 字段 NOT NULL 约束 
ALTER TABLE book
    MODIFY publisher varchar(64) NULL COMMENT '出版社';

删除字段

语法

ALTER TABLE 数据表名
    DROP COLUMN 字段名

假设我们现在删除 book 表中的字段 publisher，对应的语法如下

ALTER TABLE book
    DROP COLUMN publisher;

修改视图

修改视图的操作比较特殊，每个数据库之间定义可能都不一样，我们以 MySQL 为例，在 MySQL 不支持 ALTER VIEW 修改视图，但是提供了 CREATE OR REPLACE VIEW 语法，你可以理解为是删除旧视图，然后创建了一个新视图。

语法

CREATE OR REPLACE VIEW 视图名称 AS
SELECT 语句;

现在我们给 it_department 视图添加一个员工年龄字段，对应的语句如下

CREATE VIEW it_department AS
SELECT id, name, age
FROM employee
WHERE department = 'IT';

需要注意的是，在修改视图定义时，应该考虑到该视图修改后的兼容性，例如现在已经有程序查询使用了 it_department 视图中的 name 字段数据，如果我们在修改视图时，将 name 字段删除，就会导致该程序因查询不到该字段数据而报错。

DROP

DROP 语法用于删除数据库、数据表、索引、视图等操作。在执行 DROP 操作前应该确保已经做好了充分的备份，以防止数据不小心丢失。

删除数据库

开发中，这种操作都是禁止的，因为无异于“删库跑路”了，并且一般咱们开发也没有这种操作权限。大家知道有这种操作语法就好了。

语法

DROP SCHEMA 数据库名;

假设我们要删除在 CREATE DATABASE 中创建的 study_db 数据库，对应的语法如下

DROP SCHEMA study_db;

删除数据表

跟删除数据库同理，这种操作在开发中也很少会执行。

语法

DROP TABLE 数据表名;

删除 book 表对应的语法如下

DROP TABLE book;

删除索引

语法

DROP INDEX 索引名;

删除 book 表中 name 字段的索引，索引名为 idx_ name，那么对应的删除语法如下

DROP INDEX idx_name ON book;

删除视图

语法

DROP VIEW 视图名;

现在我们来删除在 CREATE VIEW 中创建的 it_department 视图

DROP VIEW it_department;

TRUNCATE

语法

TRUNCATE TABLE 表名称;

比如说现在要删除 book 的所有数据，对应的语句如下

TRUNCATE TABLE book;

截断数据表的作用是把数据表中的数据全部清空。到后面学习 DML 的时候，你会发现 TRUNCATE 实现的效果跟 DELETE 语法差不多，但实际在数据处理效率上以及语法上还是有不同的，这个我们后续学习 DELETE 语法的时候会进行对比。