关系数据库标准语言 SQL 数据定义

发表于 更新于 Waline： 阅读次数： 本文字数： 1.7k 阅读时长 ≈ 6 分钟

SQL 概述

SQL (Structured Query Language，结构化查询语言) 是关系数据库的标准语言，具有以下特点：

  • 综合统一：集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）于一体。
  • 高度非过程化：只需指定「做什么」，无需说明「怎么做」。
  • 面向集合：操作对象和结果都是元组集合。
  • 两种使用方式：既可作为独立语言交互使用，也可嵌入到宿主语言（如 C, Java）中。
  • 简洁易学：核心功能仅需少量动词。

SQL 与关系模型概念的对应关系：

关系模型 SQL
关系 基本表
子模式 视图
属性 列（column）
元组 行（row）

基本表和视图统称为表（table），视图也被称为虚表（virtual table）。

SQL 基本语言成分

  • 符号：包括 26 个英文字母、阿拉伯数字、括号、四则运算符等。
  • 保留字：具有特定含义的单词或缩写，如 CREATE, SELECT, TABLE, UNIQUE 等。
  • 标识符：用于命名数据库对象，如表、视图、属性、存储过程等。
  • 常量：包括数值常量、字符串常量、日期/时间常量。

SQL 基本表示规范（交互式 SQL）

  • 每条 SQL 语句通常以动词开头，以分号 ; 结尾。
  • 在批处理模式下，分号既是语句结束符，也是语句分隔符。
  • 除常量外，SQL 语句不区分大小写（字母）。
  • 数值常量无需定界符，字符串和日期/时间常量需使用单引号 ' 作为定界符。

SQL 与关系数据库三级模式

graph TB
    %% 定义 SQL 作为顶层节点
    I[SQL（应用层）]:::sql-node

    %% 外模式
    subgraph 外模式[外模式（用户层）]
        direction LR
        A[视图 1]:::view-node
        B[视图 2]:::view-node
    end

    %% 模式
    subgraph 模式 [模式（逻辑层）]
        direction LR
        C[基本表 1]:::table-node
        D[基本表 2]:::table-node
        E[基本表 3]:::table-node
        F[基本表 4]:::table-node
    end

    %% 内模式
    subgraph 内模式 [内模式（物理层）]
        direction LR
        G[存储文件 1]:::storage-node
        H[存储文件 2]:::storage-node
    end

    %% 外模式到模式的关系
    A <--> D
    B <--> E
    B <--> F

    %% 模式到内模式的关系
    C <--> G
    D <--> G
    E <--> G
    F <--> H

    %% SQL 到各层次的关系
    I <--> A
    I <--> B
    I <--> C

    %% 美化样式定义
    classDef sql-node fill:#4CAF50,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef view-node fill:#FF9800,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef table-node fill:#2196F3,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;
    classDef storage-node fill:#9C27B0,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold;

    %% 子图样式定义
    classDef subgraphTitle fill:#333,stroke:#333,stroke-width:2px,color:#fff,font-weight:bold,text-align:center;
  • 基本表（Base Table）：实际存储数据的表，一个关系对应一个基本表。
  • 存储文件：组织数据库内模式的逻辑结构，对用户透明。
  • 视图（View）：从基本表导出的虚表，数据库中只存储视图的定义。

学生-课程数据库

本篇将使用一个「学生-课程」数据库作为示例，包含以下三个基本关系：

  • 学生关系（Student）：记录学生信息
学号（Sno） 姓名（Sname） 性别（Ssex） 年龄（Sage） 所在系（Sdept）
201215121 李勇 男 20 CS
201215122 刘晨 女 19 CS
201215123 王敏 女 18 MA
201215125 张立 男 19 IS
  • 课程关系（Course）：记录课程信息
课程号（Cno） 课程名（Cname） 先行课（Cpno） 学分（Ccredit）
1 数据库 5 4
2 数学 NULL 2
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 NULL 2
7 PASCAL 语言 6 4
  • 选修关系（SC）：记录学生选课信息
学号（Sno） 课程号（Cno） 成绩（Grade）
201215121 1 92
201215121 2 85
201215121 3 88
201215122 2 90
201215122 3 80

SQL 数据定义

SQL 数据定义功能包括：

  • 模式定义：创建、删除模式（CREATE SCHEMA, DROP SCHEMA）
  • 表定义：创建、删除、修改基本表（CREATE TABLE, DROP TABLE, ALTER TABLE）
  • 视图和索引定义：创建、删除视图和索引（CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, ALTER INDEX）

数据库对象命名机制

  • 一个关系数据库管理系统实例可包含多个数据库。
  • 一个数据库可包含多个模式。
  • 一个模式通常包含多个表、视图、索引等数据库对象。
    • 表：存储数据的网格结构（类似 Excel 表格）
    • 视图：保存查询结果的虚拟表
    • 索引：加速数据检索的目录结构
1
2
3
数据库（有的系统称为目录）
└── 模式
    └── 表、视图、索引等

MySQL 中数据库与模式基本是等同的。

模式定义与删除

  • 定义模式：实际上是定义了一个命名空间，可在其中定义数据库对象。 
    1
    2
    CREATE SCHEMA [<模式名>] AUTHORIZATION <用户名>
    [<表定义子句> | <视图定义子句> | <授权定义子句>];
    • 若未指定 <模式名>，则 <模式名> 隐含为 <用户名>。
    • 可在 CREATE SCHEMA 中直接创建表、视图或授权。
  • 删除模式： 
    DROP SCHEMA <模式名> <CASCADE | RESTRICT>;
    • CASCADE（级联）：删除模式的同时删除所有下属对象。
    • RESTRICT（限制）：若模式中存在下属对象，则拒绝删除。

基本表定义、删除与修改

定义基本表

1
2
3
4
5
6
CREATE TABLE <表名> (
    <列名> <数据类型> [<列级完整性约束条件>]
    [, <列名> <数据类型> [<列级完整性约束条件>]]
    ...
    [, <表级完整性约束条件>]
);
  • <表名>：要定义的基本表名称。
  • <列名>：组成表的各个属性（列）。
  • <列级完整性约束条件>：涉及相应属性列的约束。
  • <表级完整性约束条件>：涉及一个或多个属性列的约束。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 学生表 Student
CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY, -- 学号，主码
    Sname CHAR(20) UNIQUE,   -- 姓名，唯一       （吗）
    Ssex CHAR(2),            -- 性别
    Sage SMALLINT,           -- 年龄
    Sdept CHAR(20)           -- 所在系
);

-- 课程表 Course
CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,                  -- 课程号，主码
    Cname CHAR(40) NOT NULL,                  -- 课程名，非空
    Cpno CHAR(4),                             -- 先行课
    Ccredit SMALLINT,                         -- 学分
    FOREIGN KEY (Cpno) REFERENCES Course(Cno) -- 外码，参照 Course 表的 Cno
);

-- 选课表 SC
CREATE TABLE SC (
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno, Cno),                    -- 主码由 Sno 和 Cno 组成
    FOREIGN KEY (Sno) REFERENCES Student(Sno), -- 外码，参照 Student 表的 Sno
    FOREIGN KEY (Cno) REFERENCES Course(Cno)   -- 外码，参照 Course 表的 Cno
);

SQL 数据类型

SQL 使用数据类型来实现域的概念，常用数据类型包括：

数据类型 含义
CHAR(n), CHARACTER(n) 长度为 n 的定长字符串
VARCHAR(n), CHARACTER VARYING(n) 最大长度为 n 的变长字符串
CLOB 字符串大对象（Character Large OBject）
BLOB 二进制大对象（Binary Large OBject）
INT, INTEGER 长整数（4 字节）
SMALLINT 短整数（2 字节）
BIGINT 大整数（8 字节）
NUMERIC(p, d) 定点数，p 位数字（不包括符号、小数点），小数后 d 位
DECIMAL(p, d), DEC(p, d) 同 NUMERIC
REAL 取决于机器精度的单精度浮点数
DOUBLE PRECISION 取决于机器精度的双精度浮点数
FLOAT(n) 可选精度的浮点数，精度至少为 n 位数字
BOOLEAN 逻辑布尔量
DATE 日期，包含年、月、日，格式为 YYYY-MM-DD
TIME 时间，包含一日的时、分、秒，格式为 HH:MM:SS
TIMESTAMP 时间戳类型
INTERVAL 时间间隔类型

修改基本表

1
2
3
4
5
6
ALTER TABLE <表名>
[ADD [COLUMN] <新列名> <数据类型> [<完整性约束>]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE | RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [RESTRICT | CASCADE]]
[ALTER COLUMN <列名> <数据类型>];
  • ADD 子句：用于增加新列、新的列级或表级完整性约束。
  • DROP COLUMN 子句：用于删除表中的列。
    • CASCADE：自动删除引用该列的其他对象。
    • RESTRICT：若该列被其他对象引用，则拒绝删除。
  • DROP CONSTRAINT 子句：用于删除指定的完整性约束。
  • ALTER COLUMN 子句：用于修改原有的列定义。

示例：

1
2
3
4
5
6
7
8
-- 向 Student 表增加「入学时间」列，数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;

-- 将年龄的数据类型由字符型改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;

-- 增加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE (Cname);

删除基本表

DROP TABLE <表名> [RESTRICT | CASCADE];
  • RESTRICT：删除表是有限制的，若表被其他对象引用，则不能删除。
  • CASCADE：删除表没有限制，同时删除相关的依赖对象（如视图、索引等）。

索引

  • 目的：加快查询速度。
  • 建立：由数据库管理员或表的拥有者建立。
  • 维护：由关系数据库管理系统自动完成。
  • 使用：关系数据库管理系统自动选择合适的索引作为存取路径。

常见索引类型：

  • 顺序文件上的索引
  • B+ 树索引
  • 散列索引
  • 位图索引

建立索引

1
2
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名> [<次序>][, <列名> [<次序>]]...);
  • <表名>：要建索引的基本表名称。
  • <列名>：索引可以建立在一列或多列上。
  • <次序>：指定索引值排列次序，升序（ASC，默认）或降序（DESC）。
  • UNIQUE：索引的每一个值只对应唯一的数据记录。
  • CLUSTER：表示要建立的索引是聚簇索引。

示例：

1
2
3
4
5
6
7
8
-- Student 表按学号升序建唯一索引
CREATE UNIQUE INDEX Stusno ON Student (Sno);

-- Course 表按课程号升序建唯一索引
CREATE UNIQUE INDEX Coucno ON Course (Cno);

-- SC 表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX SCno ON SC (Sno ASC, Cno DESC);

修改/删除索引

1
2
3
4
5
-- 修改索引名
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

-- 删除索引
DROP INDEX <索引名>;

数据字典

数据字典是关系数据库管理系统内部的一组系统表，记录了数据库中所有定义信息，包括：

  • 关系模式定义
  • 视图定义
  • 索引定义
  • 完整性约束定义
  • 各类用户对数据库的操作权限
  • 统计信息等

关系数据库管理系统在执行 SQL 数据定义语句时，实际上就是在更新数据字典表中的相应信息。