数据库技术：期末复习

Last updated on July 1, 2026 pm

本文为 SJTU-CS3321 数据库技术课程的期末复习.

第一章数据库系统概论

1. 数据库系统概述

基本概念：了解关系即可（包含的范围越来越大）
- 数据(Data)：数据库中存储的基本对象
- 数据库(DataBase, DB)：长期储存在计算机内、有组织、可共享的大量数据集合
- 数据库管理系统 (DataBase Management System, DBMS)：位于用户与操作系统之间的一层数据管理软件
- 数据库系统 (DataBase System, DBS)：在计算机系统中引入数据库和 DBMS 后的系统构成
DBMS 的主要功能：
- 数据定义功能：
  - 提供数据定义语言(Data Definition Language, DDL)
  - 定义数据库中的数据对象的组成与结构
- 数据组织、存储、管理功能：
  - 文件结构和存取方式
  - 数据如何联系
  - 提高存储空间利用率、方便存取
- 数据操纵功能：
  - 提供数据操纵语言(Data Manipulation Language, DML)
  - 操纵数据实现基本操作，如查询、插入、删除和修改
- 数据库的事务管理和运行管理：
  - 保证数据的安全性、完整性
  - 多用户对数据的并发使用
  - 发生故障后的系统恢复
- 数据库的建立和维护功能：
  - 数据库数据批量装载和转储
  - 介质故障恢复
  - 数据库的重组织
  - 性能监视、分析
- 其他功能：
  - 数据库管理系统与网络中其它软件系统的通信
  - 数据库管理系统各系统之间的数据转换
  - 异构数据库之间的互访和互操作
数据库系统的特点：
- 数据的结构化：数据的结构用数据模型描述，无需程序定义和解释
- 数据的独立性：
  - 物理独立性：用户的应用程序与存储在物理磁盘上的数据库中数据是相互独立的，即当数据的物理存储改变了，应用程序不用改变
  - 逻辑独立性：用户的应用程序与数据库的逻辑结构是相互独立的，即数据的逻辑结构改变了，用户程序也可以不变
- 数据的高共享性：数据面向整个系统，可以被多个用户、多个应用共享使用
- 数据由 DBMS 统一管理和控制：数据的安全性保护、数据的完整性检查、并发控制、数据库恢复

2. 数据模型

数据建模的步骤：
- 建立概念模型：将现实世界抽象为信息世界
  - 概念模型是按用户的观点来对数据和信息建模，用于数据库设计
- 将概念模型转换为数据模型：将信息世界转换为机器世界
  - 数据模型是按计算机系统的观点对数据建模，用于 DMBS 的实现
E-R 模型：用 E-R 图来描述现实世界的概念模型
- 实体型：用矩形表示，矩形框内写明实体名
- 属性：用椭圆形表示，并用无向边将其与相应的实体连接起来
- 联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1:1、1:n 或 m:n）
  - 联系的属性：用无向边与该联系连起来
  - 两个实体型之间的联系：
    - 一对一联系：如果对于实体集 $A$ 中的每一个实体，实体集 $B$ 中至多有一个实体与之联系，反之亦然，则称实体集 $A$ 与实体集 $B$ 具有一对一联系，记为 $1:1$
    - 一对多联系：如果对于实体集 $A$ 中的每一个实体，实体集 $B$ 中有 $n$ 个实体 $(n\ge 0)$ 与之联系，反之，对于实体集 $B$ 中的每一个实体，实体集 $A$ 中至多只有一个实体与之联系，则称实体集 $A$ 与实体集 $B$ 有一对多联系，记为 $1:n$
    - 多对多联系：如果对于实体集 $A$ 中的每一个实体，实体集 $B$ 中有 $n$ 个实体 $(n \ge 0)$ 与之联系，反之，对于实体集 $B$ 中的每一个实体，实体集 $A$ 中也有 $m$ 个实体 $(m \ge 0)$ 与之联系，则称实体集 $A$ 与实体 $B$ 具有多对多联系，记为 $m: n$
  - 多个实体型之间的联系：
    - 一对多联系：若实体型 $E_1, E_2, \dots，E_n$ 存在联系，对于实体型 $E_j$ （ $j=1, 2, \dots, i-1, i+1, \dots, n$ ）中的给定实体，最多只和 $E_i$ 中的一个实体相联系，则我们说 $E_i$ 与 $E_1, E_2, \dots, E_{i-1}, E_{i+1}, \dots, E_n$ 之间的联系是一对多的
    - 多对多联系、一对一联系
  - 同一实体集内各实体间的联系：一对多联系、一对一联系、多对多联系

此处需要会建立简单的 E-R 模型，例题参考 Canvas 第 02 讲 41:50-47:50 及第 03 讲 16:50-31:30.

数据模型的三要素
- 数据结构：对系统静态特性的描述
  - 内容：数据库的组成对象及对象之间的联系
- 数据操作：对系统动态特性的描述
  - 内容：对数据库中各种对象的实例允许执行的操作的集合，包括操作及有关的操作规则
- 数据的完整性约束条件：一组完整性规则的集合
  - 完整性规则：给定的数据模型中数据及其联系所具有的制约和储存规则

3. 数据库系统结构

三级模式二级映象

三级模式与二级映象：
- 三级模式：对数据的三个抽象级别
  - 模式：数据库中全体数据的逻辑结构和特征的描述，是数据库模式结构的中心(首先确定)
    - 独立于数据库的其它层次，一个应用数据库只有一个模式
  - 外模式：局部数据的逻辑结构和特征的描述
    - 面向具体的应用程序，定义在逻辑模式之上，但独立于存储模式和存储设备
    - 模式与外模式、外模式与应用的关系都是一对多
  - 内模式：数据物理结构和存储方式的描述
    - 依赖于全局逻辑结构，但独立于外模式，也独立于具体的存储设备
    - 一个数据库只有一个内模式
- 二级映象：在 DBMS 内部实现这三个抽象层次的联系和转换
  - 外模式／模式映象：保证数据的逻辑独立性
  - 模式／内模式映象：保证数据的物理独立性

4. 数据库系统的组成

数据库系统的组成：数据库、数据库管理系统（及其应用开发工具）、应用系统、数据库管理员(DataBase Administrator, DBA)
DBA 的职责：了解即可
- 设计与定义数据库：
  - 参与数据库设计的全过程
  - 与用户、应用开发人员、系统分析员密切结合
  - 设计概念模式、数据库模式以及各个应用的外模式
  - 熟悉 DBMS 产品，决定数据库的存储结构和存取策略，设计数据库的内模式
- 帮助最终用户使用数据库系统
- 负责数据库系统的运维工作：
  - 负责监视数据库系统的运行情况
  - 及时处理运行过程中出现的问题
  - 控制不同用户访问数据库的权限
  - 收集数据库的审计信息，保证数据库的安全性和完整性
- 改进和重组数据库系统，调优数据库系统的性能：
  - 负责监视、分析数据库系统的性能，包括空间利用率和处理效率；根据实际应用环境不断改进数据库设计
  - 数据库运行过程中不断地插入、删除、修改数据，DBA 要定期地或按一定的策略对数据库进行重组织
- 转储与恢复数据库：
  - 为减少硬件、软件或人为故障对数据库系统的破坏，DBA 必须定义和实施适当的后援和恢复策略
  - 一旦系统故障，DBA 必须能够在最短时间内把数据库恢复到某一正确状态
- 重构数据库：
  - 用户应用需求改变时，DBA 需要重新构造数据库，包括修改内模式或模式

第二章关系模型和关系运算理论

2. 关系数据结构

基本概念：
- 基数(Cardinal number)：一个域允许的不同取值个数
- 码：
  - 候选码(Candidate key)：若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码
  - 主码（Primary key）：若一个关系有多个候选码，则选定其中一个为主码
  - 主属性(Prime attribute)：候选码的诸属性
- 外码(Foreign Key)：设 $F$ $F$ 是基本关系 $R$ $R$ 的一个或一组属性，但不是关系 $R$ $R$ 的码，如果 $F$ $F$ 与基本关系 $S$ $S$ 的主码 $K_s$ $K_{s}$ 相对应，则称 $F$ $F$ 是基本关系 $R$ $R$ 的外码
  - $R$ 称为参照关系(Referencing Relation)， $S$ 称为被参照关系(Referenced Relation)
- 其他概念比较简单，在此不罗列

4. 关系的完整性

关系模型中三类完整性约束：
- 实体完整性：若属性(组) $A$ 是基本关系 $R$ 的主属性，则属性 $A$ 不能取空值
- 参照完整性：若属性(组) $F$ $F$ 是基本关系 $R$ $R$ 的外码，它与基本关系 $S$ $S$ 的主码 $K_s$ $K_{s}$ 相对应（基本关系 $R$ $R$ 和 $S$ $S$ 不一定是不同的关系），则对于 $R$ $R$ 中每个元组在 $F$ $F$ 上的值必须为：或者取空值，或者等于 $S$ $S$ 中某个元组的主码值
  - $R$ 称为参照关系， $S$ 称为被参照关系
- 用户定义的完整性：针对某一具体关系数据库的约束条件，反映所涉及的数据必须满足的语义要求

5. 关系代数

关系代数：
- 五种基本运算：选择、投影、并、差、笛卡儿积
- 广义笛卡儿积(Extended Cartesian Product)：
  $R \times S=\{\overset{\frown}{t_r t_s} \mid t_{\mathrm{r}} \in R \wedge t_{\mathrm{s}} \in S\}$
- 选择(Selection)：在关系 $R$ 中选择满足给定条件的诸元组
  
  $\sigma_{\mathrm{F}}(R)=\left\{t \mid t \in R \wedge F(t)= \text { “真” }\right\}$
  
  其中 $F$ 是选择条件
  ▶
  例：
  例：查询信息系(IS 系)全体学生 $\sigma_{\text {Sdept = 'IS'}}(\text {Student})$
  例：查询年龄小于 20 岁的学生 $\sigma_{\text {Sage < 20}}(\text {Student})$
- 投影(Projection)：从 $R$ 中选择出若干属性列组成新的关系
  
  $\pi_A(R)=\{t[A] \mid t \in R\}$
  
  其中 $A$ 是 $R$ 中的属性列
  ▶
  例：
  例：查询学生的姓名和所在系 $\pi_{\text {Sname, Sdept}} (\text{Student})$
  例：查询学生关系 Student 中都有哪些系 $\pi_{\text {Sdept}} (\text{Student})$
  例：查询选修了 2 号课程的学生的学号 $\pi_{\text {Sno}}\left(\sigma_{\text{Cno='2'}}(\text{SC})\right)$
- 连接(Join)：从两个关系的笛卡儿积中选取属性间满足一定条件的元组
  
  $R \underset{A\theta B}{\bowtie} S = \{ \overset{\frown}{t_r t_s} \mid t_r \in R \land t_s \in S \land t_r[A] \theta t_s[B] \}$
  
  其中 $A$ 和 $B$ 分别为 $R$ 和 $S$ 上度数相等且可比的属性组， $\theta$ 是比较运算符
  - 等值连接(equijoin)： $\theta$ 为 “ $＝$ ” 的连接运算 $R \underset{A = B}{\bowtie} S = \{ \overset{\frown}{t_r t_s} \mid t_r \in R \land t_s \in S \land t_r[A] = t_s[B] \}$
  - 自然连接(Natural join)：一种特殊的等值连接，两个关系中进行比较的分量必须是相同的属性组，在结果中把重复的属性列去掉（只有自然连接不用写连接条件） $R \bowtie S = \{ \overset{\frown}{t_r t_s}[U-B] \mid t_r \in R \land t_s \in S \land t_r[B] = t_s[B] \}$ 其中 $R$ 和 $S$ 具有相同的属性组 $B$ ， $U$ 为 $R$ 和 $S$ 的全体属性集合
  - 外连接(Outer Join)：把舍弃的元组也保存在结果关系中，而在其他属性上填空值
  - 左/右外连接(LEFT/RIGHT OUTER JOIN)：只把左/右边关系 $R$ 中要舍弃的元组（即悬浮元组）保留
  ▶
  PPT 例题：
  例：查询至少选修了一门其直接先修课为 5 号课程的课程的学生姓名 $\pi_{\text {Sname}}\left(\pi_{\text {Cno}}\left(\sigma_{\text {Cpno='5'}}(\text {Course})\right) \bowtie \pi_{\text {Sno, Cno}}(\text{SC}) \bowtie \pi_{\text{Sno, Sname}}(\text {Student})\right)$
- 除(Division)：给定关系 $R (X,Y)$ 和 $S (Y,Z)$ ，其中 $X, Y, Z$ 为属性组
  
  $R \div S=\left\{t_r[X] \mid t_r \in R \wedge \pi_Y(S) \subseteq Y_x\right\}$
  
  其中 $Y_x$ 是 $x$ 在 $R$ 中的象集， $x=t_r[X]$
  - 象集(Images Set)：给定一个关系 $R(X, Z)$ ， $X$ 和 $Z$ 为属性组，当 $t[X]=x$ 时， $x$ 在 $R$ 中的象集 $Z_x=\{t[Z] \mid t \in R, t[X]=x \}$
  ▶
  例：
  例：查询至少选修 1 号课程和 3 号课程的学生号码 $\pi_\text{Sno, Cno}(\text{SC}) \div K$ 其中 $K$ 为临时关系：
  Cno
  1
  3
- 运算顺序：为了减少关系运算的时间复杂度，通常先做选择运算，再做投影运算，最后做连接运算

Cno
1
3

▶

PPT 例题：

例：查询选修某一门课程在 95 分或以上的学生姓名 $\pi_{\text {Sname }}\left(\pi_{\text {Sno}}\left(\sigma_{\text {Grade}>95}(\text{SC})\right) \bowtie \pi_{\text {Sno, Sname}}(\text{Student})\right)$ 或只用基本运算： $\pi_{\text {Sname}}\left(\sigma_{\text {S.Sno }=\text {SC.Sno}}\left(\pi_{\text {Sno}}\left(\sigma_{\text {Grade}>=95}(\text{Student})\right) \times S\right)\right)$

此处需要会用关系代数表示查询，例题参考 Canvas 第 08 讲 19:30-23:05、第 09 讲 23:35-26:35 及第 11 讲 14:45-54:55.

第三章关系规范化基础

2. 数据依赖

数据依赖导致的问题：
- 数据冗余(Data redundancy)：浪费大量的存储空间
- 更新异常(Update anomaly)：更新数据时，维护数据完整性代价大
- 插入异常(Insert anomaly)：该插的数据插不进去
- 删除异常(Deletion anomaly)：不该删除的数据不得不删
函数依赖的基本概念：
- 函数依赖：设 $R(U,F)$ 是一个属性集 $U$ 上的关系模式， $X$ 和 $Y$ 是 $U$ 的子集，若对于 $R(U,F)$ 的任意一个可能的关系 $r$ ， $r$ 中不可能存在两个元组在 $X$ 上的属性值相等，而在 $Y$ 上的属性值不等，则称 “ $X$ 函数确定 $Y$ ” 或 “ $Y$ 函数依赖于 $X$ ”，记作 $X \to Y$
- 完全/部分函数依赖：在关系模式 $R(U,F)$ $R (U, F)$ 中，
  - 如果 $X \rightarrow Y$ ，并且对于 $X$ 的任何一个真子集 $\mathrm{X}^{\prime}$ ，都有 $\mathrm{X}^{\prime} \nrightarrow Y$ ，则称 $Y$ 完全函数依赖于 $X$ ，记作 $X \xrightarrow{F} Y$
  - 如果 $X \rightarrow Y$ ，但 $Y$ 不完全函数依赖于 $X$ ，则称 $Y$ 部分函数依赖于 $X$ ，记作 $X \xrightarrow{P} Y$
- 传递函数依赖：在关系模式 $R(U,F)$ 中，如果 $X \rightarrow Y, Y \nrightarrow X, Y \rightarrow Z$ ，则称 $Z$ 对 $X$ 传递函数依赖，记为 $X \xrightarrow{T} Z$
- 如果没有特别说明，都不考虑平凡的函数依赖

3. 关系规范化

范式的判断：
- 1NF(第一范式)：如果一个关系模式 $R$ 的所有属性都是不可分的基本数据项，则 $R \in 1NF$
- 2NF(第二范式)：若关系模式 $R \in 1NF$ $R \in 1 NF$ ，并且每一个非主属性都完全函数依赖于 $R$ 的任何一个候选码，则 $R \in 2NF$ $R \in 2 NF$
  - 2NF 消除了非主属性对码的部分函数依赖
- 3NF (第三范式)：若关系模式 $R \in 1NF$ $R \in 1 NF$ ，若不存在这样的码 $X$ $X$ 、属性组 $Y$ $Y$ 及非主属性 $Z(Z \nsubseteq X, Z \nsubseteq Y)$ $Z (Z ⊈ X, Z ⊈ Y)$ ，使得 $X \rightarrow Y, Y \nrightarrow X, Y \rightarrow Z$ $X \to Y, Y ↛ X, Y \to Z$ 成立，则称 $R \in 3 N F$ $R \in 3 NF$
  - 3NF 消除了非主属性对码的传递函数依赖
- BCNF(BC 范式)：设关系模式 $R \in 1 N F$ $R \in 1 NF$ ，如果对于 $R$ $R$ 的每个函数依赖 $X \rightarrow Y$ $X \to Y$ ，若 $Y \nsubseteq X$ $Y ⊈ X$ ，则 $X$ $X$ 必含有码，那么 $R \in B C N F$ $R \in BCNF$
  - BCNF 消除了主属性对码的部分和传递函数依赖，且每一个决定属性集都包含码

▶

PPT 中的一些例题：

SLC(Sno, Sdept, Sloc, Cno, Grade)，其中 Sloc 为学生住处，假设每个系的学生住在同一个地方，那么 SLC $\in 1NF$ ，因为存在非主属性 Sdept 和 Sloc 对码 (Sno, Cno) 的部分函数依赖
SL(Sno, Sdept, Sloc) $\in 2NF$ ，因为存在非主属性 Sloc 对码 Sno 的传递函数依赖
STJ(S, T, J)，其中 T $\rightarrow$ J，(S, J) $\rightarrow$ T，(S, T) $\rightarrow$ J，那么 STJ $\in 3NF$ ，因为存在主属性 J 对码 (S, T) 的部分函数依赖
C(Cno, Cname, Pcno) $\in BCNF$
邮编(城市, 街道, 邮政编码)，其中 (城市, 街道) $\rightarrow$ 邮政编码，邮政编码 $\rightarrow$ 城市，那么该关系模式 $\in 3NF$ ，因为存在主属性“街道”对码 (街道, 邮政编码) 的部分函数依赖
SJP(S, J, P)，其中 (S, J) $\rightarrow$ P，(J, P) $\rightarrow$ S，那么 SJP $\in BCNF$

关系模式的规范化：
- 定义：将低一级范式的关系模式，通过模式分解(schema decomposition) 转换为若干个高一级范式的关系模式集合
- 分解标准：
  - 保持依赖性：每个关系的最小函数依赖集是原关系的最小函数依赖集的子集，并且所有子集的并等于原关系的最小函数依赖集
  - 无损连接性：进行关系分解后得到的关系按照外码自然连接能够得到原来的关系

此处需要会判断给定关系模式的范式以及规范化，例题参考 Canvas 第 15 讲 06:20-13:45 及第 15 讲 22:25-24:20.

4. 数据依赖的公理系统

基本概念：
- 逻辑蕴含：对于满足一组函数依赖 $F$ 的关系模式 $R\langle U, F\rangle$ ，对其任何一个关系 $r$ ，若函数依赖 $X \rightarrow Y$ 都成立，则称 $F$ 逻辑蕴含 $X \rightarrow Y$
- 函数依赖闭包：在关系模式 $R\langle U, F \rangle$ 中为 $F$ 所逻辑蕴含的函数依赖的全体，叫作 $F$ 的闭包，记为 $F^+$
- 属性(集)闭包：设 $F$ 为属性集 $U$ 上的一组函数依赖， $X \subseteq U$ ， $U = \{A_1, \dots, A_n\}$ ，则称 $X_F{}^+ =\{A_i \mid X \rightarrow A_i \text{ 能由 } F \text{ 根据 Armstrong 公理导出 } \}$ 为属性集 $X$ 关于函数依赖集 $F$ 的闭包
- 函数依赖集等价：如果 $G^{+}=F^{+}$ ，就说函数依赖集 $F$ 与 $G$ 等价
- 最小依赖集：如果函数依赖集 $F$ $F$ 满足下列条件，则称 $F$ $F$ 为一个最小依赖集
  - $F$ 中任一函数依赖的右部仅含有一个属性
  - $F$ 中不存在这样的函数依赖 $X \rightarrow A$ ，使得 $F$ 与 $F-\{X \rightarrow A\}$ 等价
  - $F$ 中不存在这样的函数依赖 $X \rightarrow A$ ， $X$ 有真子集 $Z$ 使得 $F-\{X \rightarrow A\} \cup\{Z \rightarrow A\}$ 与 $F$ 等价
求闭包的算法：求属性集 $X(X \subseteq U)$ 关于 $U$ 上的函数依赖集 $F$ 的闭包 $X_F{ }^{+}$
- 第一步：令 $X^{(0)}=X, i=0$
- 第二步：求 $B = \{A \mid(\exists V)(\exists W)(V \rightarrow W \in F \left.\left.\wedge V \subseteq X^{(i)} \wedge A \in W\right)\right\}$ ，即对 $X^{(i)}$ 中的每个元素，依次检查相应的函数依赖，将依赖它的属性加入 $B$
- 第三步： $X^{(\mathrm{i}+1)}=B \cup X^{(\mathrm{i})}$
- 第四步：判断 $X^{(i+1)}=X{ }^{(i)}$ $X^{(i + 1)} = X^{(i)}$
  - 若 $X^{(i+1)}$ 与 $X{ }^{(i)}$ 相等或 $X^{(i)}=U$ ，则 $X^{(i)}$ 就是 $X_F{ }^{+}$ ，算法终止
  - 若否，则 $i=i+1$ ，返回第二步
候选码的求解算法：
- 第一步：列出 L、R、N、LR 属性包含的元素
  - L 类属性：只出现在函数依赖集左边的属性
  - R 类属性：只出现在函数依赖集右边的属性
  - N 类属性：没有出现在函数依赖集里的属性
  - LR 类属性：出现在函数依赖集左、右两边的属性
- 第二步：设 $X$ 代表 L 与 N 类属性， $Y$ 代表 LR 类属性
- 第三步：求 $X$ 的闭包
- 第四步：
  - 如果 $X$ 的闭包包含 $U$ 的全部属性， $X$ 即为该关系的唯一候选码，结束
  - 若 $X^+$ 不全包含 $U$ 的全部属性
- 第五步：
  - 从 $Y$ 中依次取出一个元素，设该元素为 $A$ ，求 $(XA)^+$ ，若取出元素和 $X$ 组合求得的闭包包含关系模式中的所有属性，则为候选码，继续直到试完 $Y$ 中全部的元素
  - 若 $Y$ 中的元素取出与 $X$ 组合均包含 $U$ 全部属性，此时所有候选码被找出
  - 若 $Y$ 中还有元素与 $X$ 组合求得的闭包不包含全部属性，则从这些属性中依次取出两个开始继续与 $X$ 组合
求最小依赖集的算法：
- 第一步：逐一检查 $F$ 中各函数依赖 $FD_i:X \to Y$ ，若 $Y=A_1A_2\dots A_k,k>2$ ，则用 $\{X \to A_j \mid j=1,2,\dots,k\}$ 来取代 $X \to Y$
- 第二步：逐一检查 $F$ 中各函数依赖 $FD_i:X \to A$ ，令 $G=F-\{X \to A\}$ ，若 $A \in {X_G}^+$ ，则从 $F$ 中去掉此函数依赖
- 第三步：逐一取出 $F$ 中各函数依赖 $FD_i: X \to A$ ，设 $X=B_1B_2\dots B_m$ ，逐一考查 $B_i(i=1,2,\dots,m)$ ，若 $A \in {(X-B_i)_F}^+$ ，则以 $X-B_i$ 取代 $X$

此处需要会求属性集的闭包、求候选码、求最小依赖集，例题参考 Canvas：

求闭包：第 13 讲 28:20-34:15；
求候选码：第 13 讲 38:15-42:45 及第 15 讲 13:50-16:55；
求最小依赖集：第 15 讲 16:55-22:25.

第四章结构查询语言 SQL

1. SQL 概述

SQL 功能	动词
数据定义	CREATE, DROP, ALTER
数据查询	SELECT
数据操纵	INSERT, DELETE, UPDATE
数据控制	GRANT, REVOKE

2. 数据定义

模式(SCHEMA)：

定义(CREATE)：CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等

▶

例：

例：为用户 WANG 定义一个"学生-课程"模式 S-T
1
CREATE SCHEMA “S-T” AUTHORIZATION WANG;

例：为用户 ZHANG 创建了一个模式 TEST，并在其中定义了一个表 TAB1

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT,
                  COL2 INT,
                  COL3 CHAR(20),
                  COL4 NUMERIC(10, 3),
                  COL5 DECIMAL(5, 2)
                  );

删除(DROP)：DROP SCHEMA <模式名> <CASCADE|RESTRICT>
- CASCADE(级联)：删除模式的同时把该模式中所有的数据库对象全部删除
- RESTRICT(限制)：如果该模式中定义了下属的数据库对象，则拒绝该删除语句的执行
▶
例：
- 例：删除模式 TEST，同时该模式中定义的表 TAB1 也被删除
  1
  DROP SCHEMA TEST CASCADE;

基本表(TABLE)：

定义(CREATE)：这是重点

CREATE TABLE <表名>
        (<列名> <数据类型>[<列级完整性约束条件>]
        [, <列名> <数据类型>[<列级完整性约束条件>]] 
        ...
        [, <表级完整性约束条件>]);

常见的完整性约束：

约束条件	说明
PRIMARY KEY	标识该字段为该表的主码，等价于 UNIQUE + NOT NULL
FOREIGN KEY	标识该字段为该表的外码
NOT NULL	标识该字段不能为空
UNIQUE KEY	标识该字段的值是唯一的

▶

例：

例：建立“学生”表 Student，学号是主码，姓名取值唯一

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件，主码*/ 
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ 
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);

例：建立一个“课程”表 Course

CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY, /* 列级完整性约束条件，主码*/ 
Cname CHAR(40) NOT NULL, /* Cname不能取空值*/ 
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);

例：建立一个“学生选课”表 SC，学生成绩在 0~100

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT CHECK (Grade>=0 and Grade<=100),
PRIMARY KEY (Sno, Cno),
/* 主码由两个属性构成，必须作为表级完整性进行定义*/ 
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/* 表级完整性约束条件，Sno是外码，被参照表是Student*/ 
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/* 表级完整性约束条件，Cno是外码，被参照表是Course*/
);

修改(ALTER)：

ALTER TABLE <表名>
[ ADD [COLUMN] <新列名> <数据类型> [完整性约束] ]
[ ADD <表级完整性约束>]
[ DROP [COLUMN] <列名> [CASCADE|RESTRICT] ]
[ DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT] ]
[ RENAME COLUMN <列名> TO <新列名> ]
[ ALTER COLUMN <列名> TYPE <数据类型> ];

▶

例：

例：向 Student 表增加“入学时间”列，其数据类型为日期型
1
ALTER TABLE Student ADD S_entrance DATE;
例：将年龄的数据类型由字符型改为整数
1
ALTER TABLE Student ALTER COLUMN Sage TYPE INT;
例：增加课程名称必须取唯一值的约束条件
1
ALTER TABLE Course ADD UNIQUE(Cname);

删除(DROP)：DROP TABLE <表名> [RESTRICT|CASCADE];
▶
例：
- 例：删除 Student 表，同时删除表上建立的对象
  1
  DROP TABLE Student CASCADE;

索引(INDEX)：
- 建立(CREATE)：
  1
  2
  CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]...);
  - <次序>：升序 ASC，降序 DESC（缺省值：ASC）
  ▶
  例：
  例：在 Student 表的 Sname（姓名）列上建立一个聚簇索引
  1
  CREATE CLUSTER INDEX Stusname ON Student(Sname);
  例：为学生-课程数据库中的 Student，Course，SC 三个表建立索引，其中 Student 表按学号升序建唯一索引，Course 表按课程号升序建唯一索引，SC 表按学号升序和课程号降序建唯一索引
  1
  2
  3
  CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
- 修改(ALTER)：ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  ▶
  例：
  例：将 SC 表的 SCno 索引名改为 SCSno
  1
  ALTER INDEX Scno RENAME TO SCSno;
- 删除(DROP)：DROP INDEX <索引名>;
  ▶
  例：
  例：删除 Student 表的 Stusname 索引
  1
  DROP INDEX Stusname;

3. 数据查询

基本语法：

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]...
FROM <表名或视图名>[,<表名或视图名>...]|(<SELECT语句>)[AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]]
[LIMIT <行数1> [OFFSET <行数2>]];

执行过程：
- 读取 FROM 子句中的基本表、视图的数据，执行笛卡尔积操作
- 选择满足 WHERE 子句中给出的条件表达式的元组
- 按 GROUP 子句中指定列的值分组，同时提取满足 HAVING 子句中组条件表达式的那些组
- 按 SELECT 子句中给出的列名或列表达式求值输出（投影）
- ORDER 子句对输出的目标表进行排序（按 ASC 升序排列，按 DESC 降序排列）
- LIMIT 子句限制 SELECT 语句查询结果的数量为 <行数1> 行，OFFSET <行数2>，表示在计算 <行数1> 行前忽略 <行数2> 行

3.1 单表查询

选择表中的若干列：
- 查询指定列：
  ▶
  例：
  例：查询全体学生的学号与姓名
  1
  2
  SELECT Sno, Sname FROM Student;
  例：查询全体学生的姓名、学号、所在系
  1
  2
  SELECT Sname, Sno, Sdept FROM Student;
- 查询全部列：
  ▶
  例：
  例：查询全体学生的详细记录
  1
  2
  SELECT * FROM Student;
- 查询经过计算的值：SELECT 子句的 <目标列表达式> 为表达式（算术表达式、字符串常量、函数、列别名）
  ▶
  例：
  例：查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名
  1
  2
  SELECT Sname, 'Year of Birth: ', 2026-Sage, LOWER(Sdept) FROM Student;
  例：使用列别名改变查询结果的列标题
  1
  2
  SELECT Sname NAME, 'Year of Birth: ' BIRTH, 2014-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;
选择表中的若干元组：
- 消除取值重复的行：两个不同的元组投影到指定列后，可能会变成相同的行；在 SELECT 子句中使用 DISTINCT 短语消除重复行，缺省为 ALL
  ▶
  例：
  例：查询选修了课程的学生学号
  1
  2
  SELECT DISTINCT Sno FROM SC;
- 查询满足条件的元组：
  - 比较大小：
    ▶
    例：
    例：查询计算机科学系全体学生的姓名
    1
    2
    3
    SELECT Sname FROM Student WHERE Sdept='CS';
    例：查询考试成绩有不及格的学生学号
    1
    2
    3
    SELECT DISTINCT Sno FROM SC WHERE Grade<60;
  - 确定范围：使用谓词 BETWEEN ... AND ... 或 NOT BETWEEN ... AND ...
    ▶
    例：
    例：查询年龄在 20-23 岁（包括 20 岁和 23 岁）之间的学生的姓名、系别和年龄
    1
    2
    3
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
    例：查询年龄不在 20-23 岁之间的学生姓名、系别和年龄
    1
    2
    3
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
  - 确定集合：使用谓词 IN <值表> 或 NOT IN <值表>，其中 <值表> 是用逗号分隔的一组取值
    ▶
    例：
    例：查询信息系(IS)、数学系(MA) 和计算机科学系(CS) 学生的姓名和性别
    1
    2
    3
    SELECT Sname, Ssex FROM Student WHERE Sdept IN ('IS', 'MA', 'CS');
    例：查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别
    1
    2
    3
    SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('IS', 'MA', 'CS');
  - 字符串匹配：[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']
    - 匹配串为固定字符串：
      ▶
      例：
      
      例：查询学号为 201215121 的学生的详细情况
      1
      2
      3
      SELECT * FROM Student WHERE Sno LIKE '201215121';
      1
      2
      3
      SELECT * FROM Student WHERE Sno = '201215121';
    - 通配符：
      - % (百分号)：代表任意长度（长度可以为 0） 的字符串
        
        例：a%b 表示以 a 开头，以 b 结尾的任意长度的字符串，如 acb、addgb、ab 等都满足该匹配串
      - _ (下横线)：代表任意单个字符
        
        例：a_b 表示以 a 开头，以 b 结尾的长度为 3 的任意字符串，如 acb，afb 等都满足该匹配串
    - 匹配串为含通配符的字符串：
      ▶
      例：
      
      例：查询所有姓刘学生的姓名、学号和性别
      1
      2
      3
      SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%'; /*%表示任意长度*/
      例：查询姓"欧阳"且全名为三个汉字的学生的姓名
      1
      2
      3
      SELECT Sname FROM Student WHERE Sname LIKE '欧阳_ _'; /*_ _代表任意单个字符*/
      例：查询名字中第2个字为"阳"字的学生的姓名和学号
      1
      2
      3
      SELECT Sname, Sno FROM Student WHERE Sname LIKE '_ _阳%';
      例：查询所有不姓刘的学生姓名
      1
      2
      3
      SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE '刘%';
    - 使用换码字符将通配符转义为普通字符：
      ▶
      例：
      
      例：查询 DB_Design 课程的课程号和学分
      1
      2
      3
      SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
      例：查询以 “DB_” 开头，且倒数第 3 个字符为 i 的课程情况
      1
      2
      3
      SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE '\';
  - 涉及空值的查询：使用谓词 IS NULL 或 IS NOT NULL；IS NULL 不能用 = NULL 代替
    ▶
    例：
    例：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩查询缺少成绩的学生学号和相应课程号
    1
    2
    3
    SELECT Sno，Cno FROM SC WHERE Grade IS NULL;
    例：查所有有成绩的学生学号和课程号
    1
    2
    3
    SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL;
  - 多重条件查询：用逻辑运算符 AND 和 OR 来联结多个查询条件（AND 的优先级高于 OR）
    ▶
    例：
    例：查询计算机系年龄在 20 岁以下的学生姓名
    1
    2
    3
    SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20;
    例：查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
    1
    2
    3
    SELECT Sname, Ssex FROM Student WHERE Sdept= 'IS' OR Sdept= 'MA' OR Sdept= 'CS';
    例：查询 1 号课程中考试成绩小于 60 或大于 90 的学生学号
    1
    2
    3
    SELECT Sno FROM SC WHERE (Grade<60 or Grade>90) and Cno=1;
对查询结果排序：使用 ORDER BY子句，可以按一个或多个属性列排序；升序 ASC，降序 DESC，缺省值为升序
▶
例：
- 例：查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数降序排列
  1
  2
  3
  4
  SELECT Sno, Grade FROM SC WHERE Cno= '3' ORDER BY Grade DESC;
- 例：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
  1
  2
  3
  SELECT * FROM Student ORDER BY Sdept, Sage DESC;
使用聚集函数：
- 5 类主要聚集函数：除 COUNT (*) 外，都只处理非空值，空值自动忽略
  - 计数：
    - 统计元组个数：COUNT ([DISTINCT|ALL] *)
    - 统计一列中值的个数：COUNT ([DISTINCT|ALL] <列名>)
  - 计算一列值的总和：SUM ([DISTINCT|ALL] <列名>)
  - 计算一列值的平均值：AVG ([DISTINCT|ALL] <列名>)
  - 求一列值中的最大最小值：MAX/MIN ([DISTINCT|ALL] <列名>)
▶
例：
- 例：查询学生总人数
  1
  2
  SELECT COUNT(*) FROM Student;
- 例：查询选修了课程的学生人数
  1
  2
  SELECT COUNT(DISTINCT Sno) FROM SC;
- 例：计算 1 号课程的学生平均成绩
  1
  2
  3
  SELECT AVG(Grade) FROM SC WHERE Cno= '1';
- 例：查询选修 1 号课程的学生最高分数
  1
  2
  3
  SELECT MAX(Grade) FROM SC WHERE Cno= '1';
- 例：查询学生 201215012 选修课程的总学分数
  1
  2
  3
  SELECT SUM(Ccredit) FROM SC, Course WHERE Sno='201215012' AND SC.Cno=Course.Cno;
- WHERE 子句中不能用聚集函数作为条件表达式，因为 WHERE 子句对每一个元组进行条件过滤，而不是对集合进行条件过滤
对查询结果分组：使用 GROUP BY 子句分组，按某一列或多列的值分组，值相等的为一组
- 聚集函数的作用对象:
  - 未对查询结果分组，聚集函数将作用于整个查询结果
  - 对查询结果分组后，聚集函数将分别作用于每个组
- 使用 GROUP BY 子句分组：
  ▶
  例：
  例：求各个课程号及相应的选课人数
  1
  2
  3
  SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;
- 使用 HAVING 短语筛选最终输出结果：
  ▶
  例：
  例：查询选修了 3 门以上课程的学生学号
  1
  2
  3
  4
  SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;
  例：查询平均成绩大于等于 90 分的学生学号和平均成绩
  1
  2
  3
  4
  SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;
  例：查询有两门及以上不及格课同学的学号和其平均成绩
  1
  2
  3
  4
  5
  6
  7
  8
  9
  SELECT Sno, AVG(GRADE) FROM SC WHERE Sno in (SELECT Sno FROM SC WHERE Grade<60 GROUP BY Sno HAVING COUNT(*)>2) GROUP BY Sno;
- 注意：
  - 聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句
  - WHERE 子句作用于基本表或视图，选择满足条件的元组
  - HAVING 短语作用于分组，从分好的组中选择满足条件的组
LIMIT 子句：LIMIT <行数1> [OFFSET <行数2>]; 表示忽略前 <行数2> 行，然后取 <行数1> 作为查询结果数据
▶
例：
- 例：查询选修了数据库课程的成绩排名前 10 名的学生学号
  1
  2
  3
  4
  5
  SELECT Sno FROM SC, Course WHERE Course.Cname='数据库' AND SC.Cno=Course.Cno ORDER BY Grade DESC LIMIT 10; /*取前10行数据为查询结果*/
  ORDER BY 可以使用不在 SELECT 列表中的列进行排序，这是 SQL 标准允许的
- 例：查询平均成绩排名在 3-7 名的学生学号和平均成绩
  1
  2
  3
  4
  5
  SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno ORDER BY AVG(Grade) DESC LIMIT 5 OFFSET 2; /*取5行数据，忽略前2行，之后为查询结果数据*/

3.2 连接查询

连接查询：同时涉及多个表的查询，又可以通过广义笛卡尔积后再进行选择运算来实现
- [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
- [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
等值与非等值连接查询 (INNER JOIN)：
- 等值连接：连接运算符为 =
  ▶
  例：
  例：查询每个学生及其选修课程的情况
  1
  2
  3
  SELECT Student.*, SC.* FROM Student，SC WHERE Student.Sno = SC.Sno;
  - 任何子句中引用表 1 和表 2 中同名属性时，都必须加表名前缀
  - 引用唯一属性名时可以加也可以省略表名前缀
自然连接：等值连接的一种特殊情况，把目标列中重复的属性列去掉
▶
例：
- 例：查询每个学生及其选修课程的情况
  1
  2
  3
  SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno = SC.Sno;
自身连接：需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀
▶
例：
- 例：查询每一门课的间接先修课（即先修课的先修课）
  1
  2
  3
  SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno AND SECOND.Cpno IS NOT NULL;
外连接(OUTER JOIN)：
- 外连接(FULL OUTER JOIN)：列出左边关系和右边关系中所有元组（包括左边和右边关系的悬浮元组）
- 左外连接(LEFT OUTER JOIN)：列出左边关系中所有元组（包括左边关系的悬浮元组）
- 右外连接(RIGHT OUTER JOIN)：列出右边关系中所有元组（包括右边关系的悬浮元组）
▶
例：
- 例：查询每个学生及其选修课程的情况，包括没有选修课程的学生
  1
  2
  SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno = SC.Sno);

复合条件连接：WHERE 子句中含多个连接条件

▶

例：

例：查询选修 2 号课程且成绩在 90 分以上的所有学生的学号和姓名

SELECT Student.Sno, Student.Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno AND /* 连接谓词 */
      SC.Cno= '2' AND /* 其他限定条件 */
      SC.Grade > 90;  /* 其他限定条件 */

多表连接：

▶

例：

例：查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND 
      SC.Cno = Course.Cno;

3.3 嵌套查询

嵌套查询：将一个查询块(子查询)嵌套在另一个查询块(父查询)的 WHERE 子句或 HAVING 短语的条件中的查询
- 不相关子查询：子查询的查询条件不依赖于父查询
- 相关子查询：子查询的查询条件依赖于父查询

带有 IN 谓词的子查询：

▶

例：

例：查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno, Sname FROM Student 
WHERE Sno IN
  (SELECT Sno FROM SC 
  WHERE Cno IN
    (SELECT Cno FROM Course 
    WHERE Cname= '信息系统'));

带有比较运算符的子查询：当能确切知道内层查询返回单值时，可用比较运算符；与 ANY 或 ALL 谓词配合使用
▶
例：
- 例：查询与“刘晨”在同一个系学习的学生
  1
  2
  3
  4
  5
  6
  SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= '刘晨');
  子查询一定要跟在比较符之后
- 例：找出每个学生超过他选修课程平均成绩的课程号
  1
  2
  3
  4
  5
  SELECT Sno, Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);

带有 ANY(SOME) 或 ALL 谓词的子查询：ANY/SOME 表示某一个值，ALL 表示所有值；必须同时使用比较运算符

▶

例：

例：查询其他系中比计算机系某一个（任意一个）学生年龄小的学生姓名和年龄

SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
                  FROM Student
                  WHERE Sdept= 'CS')
      AND Sdept <> 'CS';

SELECT Sname, Sage
FROM Student
WHERE Sage < (SELECT MAX(Sage)
              FROM Student
              WHERE Sdept= 'CS')
      AND Sdept <> 'CS';

例：查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄

SELECT Sname, Sage
FROM Student
WHERE Sage < ALL (SELECT Sage
                  FROM Student
                  WHERE Sdept= 'CS')
      AND Sdept <> 'CS';

SELECT Sname, Sage
FROM Student
WHERE Sage < (SELECT MIN(Sage)
              FROM Student
              WHERE Sdept= 'CS')
      AND Sdept <> 'CS';

带有 EXISTS 谓词的子查询：

EXISTS 谓词：
- 子查询不返回任何数据，若内层查询结果非空，则返回真值“true”；若内层查询结果为空，则返回假值“false”
- 子查询的目标列表达式通常都用 *，因为带 EXISTS 的子查询只返回真值或假值，给出列名无实际意义
▶
例：
- 例：查询所有选修了 1 号课程的学生姓名
  1
  2
  3
  4
  5
  6
  7
  SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');
  1
  2
  3
  4
  SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= '1';
NOT EXISTS 谓词：
- 若内层查询结果非空，则外层的 WHERE 子句返回假值
- 若内层查询结果为空，则外层的 WHERE 子句返回真值
▶
例：
- 例：查询没有选修 1 号课程的学生姓名
  1
  2
  3
  4
  5
  6
  7
  SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno= '1');
不同形式的查询间的替换：
- 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换（查询包含所有的、查询没有的）
- 所有带 IN 谓词、比较运算符、ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换
▶
例：
- 例：查询与“刘晨”在同一个系学习的学生，可以用带 EXISTS 谓词的子查询替换
  1
  2
  3
  4
  5
  6
  7
  SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS 　(SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨');

用 EXISTS/NOT EXISTS 实现全称量词：

(\forall \mathrm{x}) \mathrm{P} \equiv \neg(\exists \mathrm{x}(\neg \mathrm{P}))

▶

例：

例：查询选修了全部课程的学生姓名

SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
    FROM Course
    WHERE NOT EXISTS
          (SELECT *
          FROM SC
          WHERE Sno = Student.Sno
          AND Cno = Course.Cno));

用 EXISTS/NOT EXISTS 实现逻辑蕴函：

\mathrm{p} \rightarrow \mathrm{q} \equiv \neg \mathrm{p} \vee \mathrm{q}

▶

例：

例：查询至少选修了学生 201215122 选修的全部课程的学生号码

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
    (SELECT *
    FROM SC SCY
    WHERE SCY.Sno = '201215122' AND
    NOT EXISTS
        (SELECT *
        FROM SC SCZ
        WHERE SCZ.Sno=SCX.Sno AND
        SCZ.Cno=SCY.Cno));

3.4 集合查询

集合查询：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
并操作：UNION
- UNION：将多个查询结果合并起来时，系统自动去掉重复元组
- UNION ALL：将多个查询结果合并起来时，保留重复元组
▶
例：
- 例：查询计算机科学系的学生或年龄不大于 19 岁的学生
  1
  2
  3
  4
  5
  6
  7
  SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19;
  1
  2
  3
  SELECT DISTINCT * FROM Student WHERE Sdept= 'CS' OR Sage<=19;

交操作：INTERSECT

▶

例：

例：查询选修课程 1 的学生集合与选修课程 2 的学生集合的交集

SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';

SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
                  (SELECT Sno
                  FROM SC
                  WHERE Cno='2');

1
2
3

SELECT S1.Sno
FROM SC S1, SC S2
WHERE S1.Sno=S2.Sno AND S1.Cno='1' AND S2.Cno='2';

差操作：EXCEPT

▶

例：

例：查询计算机科学系的学生与年龄不大于 19 岁的学生的差集

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19;

1
2
3

SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage>19;

例：查询没有选修 1 号课程的学生学号。

SELECT Sno
FROM Student
WHERE NOT EXISTS
      (SELECT *
      FROM SC
      WHERE SC.Sno = Student.Sno AND Cno='1');

SELECT Sno
FROM Student
EXCEPT
SELECT Sno
FROM SC
WHERE Cno='1';

3.5 派生查询

派生查询：子查询出现在 FROM 子句中，即子查询生成的临时派生表(derived table) 成为主查询的查询对象

基于派生表的查询：

▶

例：

例：找出每个学生超过自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
          AS Avg_sc (avg_sno, avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade

例：查询所有选修了 1 号课程的学生姓名

SELECT Sname
FROM Student, (SELECT Sno FROM SC WHERE Cno='1')
              AS SC1 
WHERE Student.Sno = SC1.Sno

如果子查询没有聚集函数，派生表可以不指定属性列，子查询 SELECT 子句后面的列名为其默认属性

4. 数据更新

插入(INSERT)：

插入单个元组：
1
2
3
INSERT INTO <表名> [(<属性列1>[, <属性列2>]...)] VALUES (<常量1>[, <常量2>]...)
▶
例：
- 例：将一个新学生记录（学号：201215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到 Student 表中
  1
  2
  3
  INSERT INTO Student (Sno, Sname, Ssex, Sdept, Sage) VALUES ('201215128', '陈冬', '男', 'IS', 18);
- 例：在表 SC 插入一条选课记录
  1
  2
  INSERT INTO SC (Sno, Cno) VALUES ('201215128', '2');
  1
  2
  INSERT INTO SC VALUES('201215128', '2', NULL);
- VALUES 子句提供的值必须与 INTO 子句匹配（值的个数、值的类型）
- 如果指定属性名称，属性列的顺序可与表定义中的顺序不一致；如果仅指定部分属性列，则新元组在没有出现的属性列上取空值
- 如果不指定任何属性列，则新插入的元组必须在每个属性列上均有值，顺序也必须相同

插入子查询结果：可以一次插入多个元组

1
2
3

INSERT 
INTO <表名> [(<属性列1>[, <属性列2>]...)]
子查询;

▶

例：

例：对每一个系，求学生的平均年龄，并把结果存入数据库

/* 第一步：建表 */
CREATE TABLE Deptage
      (Sdept CHAR(15)
      Avgage SMALLINT);

/* 第二步：插入数据 */
INSERT
  INTO Deptage(Sdept, Avgage)
  SELECT Sdept, AVG(Sage)
  FROM Student
  GROUP BY Sdept;

修改(UPDATE)：
1
2
3
UPDATE <表名> SET <列名>=<表达式>[, <列名>=<表达式>]... [WHERE <条件>];
- WHERE 子句：指定要修改的元组，缺省表示要修改表中的所有元组
- 修改元组的值：
  ▶
  例：
  例：将学生 201215121 的年龄改为 22 岁
  1
  2
  3
  UPDATE Student SET Sage=22 WHERE Sno='201215121';
  例：将所有学生的年龄增加 1 岁
  1
  2
  UPDATE Student SET Sage=Sage+1;
- 带子查询的修改语句：
  ▶
  例：
  例：将计算机科学系全体学生的成绩置零
  1
  2
  3
  4
  5
  6
  UPDATE SC SET Grade=0 WHERE sno IN (SELETE Sno FROM Student WHERE Sdept= 'CS');
删除(DELETE)：
1
2
3
DELETE FROM <表名> [WHERE <条件>]
- WHERE 子句：指定要删除的元组，缺省表示要删除表中的所有元组（表的定义仍在数据字典中）
- 删除元组的值：
  ▶
  例：
  例：删除学号为 201215128 的学生记录
  1
  2
  3
  DELETE FROM Student WHERE Sno='201215128';
  例：删除 2 号课程的所有选课记录
  1
  2
  3
  DELETE FROM SC WHERE Cno='2';
  例：删除所有的学生选课记录
  1
  2
  DELETE FROM SC;
- 带子查询的删除语句：
  ▶
  例：
  例：删除计算机科学系所有学生的选课记录
  1
  2
  3
  4
  5
  6
  DELETE FROM SC WHERE Sno IN (SELETE Sno FROM Student WHERE Sdept= 'CS');
  例：删除有四门不及格课程的所有同学
  1
  2
  3
  4
  5
  6
  7
  8
  DELETE FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Grade<60 GROUP BY SnO HAVING COUNT(*)>=4);

5. 空值

空值的算术运算、比较运算和逻辑运算：

空值与另一个值(包括空值)的算术运算结果为空值
空值与另一个值(包括空值)的比较运算结果为 UNKNOWN

▶

例：

例：找出选修 1 号课程的不及格的学生

SELECT Sno
FROM SC
WHERE Grade<60 AND Cno= '1';
/* 选出了参加考试不及格的学生，不包括缺考的学生 */

例：找出选修 1 号课程的不及格的学生及缺考的学生

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1'
UNION
SELECT Sno
FROM SC
WHERE Grade IS NULL AND Cno= '1';

1
2
3

SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);

6. 视图

视图的特点：
- 不仅包含外模式，而且包含外模式/模式映像
- 是虚表，是从基本表(或视图)导出的表
- 数据字典只存放视图的定义
- 对视图的更改，最终反映在对基本表的更改上
建立视图(CREATE)：
1
2
3
CREATE VIEW <视图名> [(<列名> [, <列名>]...)] AS <子查询> [WITH CHECK OPTION];
- 说明：
  - WITH CHECK OPTION 表示对视图进行更新、插入或删除操作时，要保证满足视图定义中的谓词条件(子查询条件表达式)
  - 组成视图的属性列名：全部省略或全部指定，但在下面三种情况必须明确指定组成视图的所有列名：
    - 某个目标列不是单纯的属性名，而是聚集函数或列表达式
    - 多表连接时选出了几个同名列作为视图的字段
    - 需要在视图中为某个列启用新的更合适的名字
  - DBMS 执行 CREATE VIEW 语句时只是把视图的定义存入数据字典，并不执行其中的 SELECT 语句
- 行列子集视图：由单个基本表导出，只是去掉某些行列，但保留主码
  ▶
  例：
  例：建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生
  1
  2
  3
  4
  5
  6
  CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION;
- 基于多个基表的视图：
  ▶
  例：
  例：建立信息系选修了 1 号课程的学生视图
  1
  2
  3
  4
  5
  6
  7
  CREATE VIEW IS_S1(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1';
- 基于视图的视图：
  ▶
  例：
  例：建立信息系选修了 1 号课程且成绩在 90 分以上的学生的视图
  1
  2
  3
  4
  5
  CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade>=90;
- 带表达式的视图：
  ▶
  例：
  例：定义一个反映学生出生年份的视图
  1
  2
  3
  4
  CREATE VIEW BT_S(Sno, Sname, Sbirth) AS SELECT Sno, Sname, 2014-Sage FROM Student
- 建立分组视图：
  ▶
  例：
  例：将学生的学号及他的平均成绩定义为一个视图
  1
  2
  3
  4
  5
  CREATE VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno;
- 不指定属性列：
  ▶
  例：
  例：将 Student 表中所有女生记录定义为一个视图
  1
  2
  3
  4
  5
  CREATE VIEW F_Student1(F_sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex='女';
删除视图(DROP)：DROP VIEW <视图名> [CASCADE];
- 从数据字典中删除指定的视图定义
▶
例：
- 例：
  删除视图 BT_S：DROP VIEW BT_S;
  删除视图 IS_S1：DROP VIEW IS_S1; 拒绝执行
  级联删除：DROP VIEW IS_S1 CASCADE;

查询视图(SELECT)：从用户角度，查询视图与查询基本表相同

DBMS 实现视图查询的方法：把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询，这称为视图消解法(View Resolution)

▶

例：

例：在信息系学生的视图中找出年龄小于 20 岁的学生

1
2
3

SELECT Sno，Sage
FROM IS_Student
WHERE Sage<20;

/* 转换后的查询语句 */
SELECT Sno，Sage 
FROM Student
WHERE Sdept= 'IS' AND Sage<20;

例（多表查询）：查询信息系选修了 1 号课程的学生

1
2
3

SELECT IS_Student.Sno，Sname
FROM IS_Student，SC
WHERE IS_Student.Sno = SC.Sno AND SC.Cno= '1';

例：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

1
2
3

SELECT *
FROM S_G
WHERE Gavg>=90;

/* 转换后的查询语句（错误） */
SELECT Sno，AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90 /* 查询出现语法错误 */
GROUP BY Sno;
/* 转换后的查询语句（正确） */
SELECT Sno，AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

更新视图(UPDATE)：从用户角度，更新视图与更新基本表相同
▶
例：
- 例：将信息系学生视图 IS_Student 中学号 201215122 的学生姓名改为“刘辰”
  1
  2
  3
  UPDATE IS_Student SET Sname= '刘辰' WHERE Sno= '201215122';
  1
  2
  3
  4
  /* 转换后的语句 */ UPDATE Student SET Sname= '刘辰' WHERE Sno= '201215122' AND Sdept= 'IS';
- 例：向信息系学生视图 IS_S 中插入一个新的学生记录：201215129，赵新，20岁
  1
  2
  3
  INSERT INTO IS_Student VALUES('201215129'，'赵新'，20);
  1
  2
  3
  4
  /* 转换为对基本表的更新 */ INSERT INTO Student(Sno，Sname，Sage，Sdept) VALUES('201215129'，'赵新'，20，'IS');
- 例：删除视图 CS_S 中学号为 201215129 的记录
  1
  2
  3
  DELETE FROM IS_Student WHERE Sno= '201215129';
  1
  2
  3
  4
  /* 转换为对基本表的更新 */ DELETE FROM Student WHERE Sno= '201215129' AND Sdept= 'IS';
- 更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
  - 允许对行列子集视图进行更新（保留基表的主码）
  - 如果视图的 SELECT 目标列包含聚集函数，不能更新
  - 如果视图的 SELECT 子句使用 UNIQUE 或 DISTINCT，不能更新
  - 如果视图包括 GROUP BY 子句，不能更新
  - 如果视图包括经算术表达式计算出来的列，不能更新

本章需要掌握 SQL 的基本语法，例题参考第四章作业.

第六章数据库设计

1. 数据库设计概述

数据库设计的基本步骤：
- 需求分析：整个设计的基础，最困难、最耗时
- 概念结构设计：对用户需求进行综合、归纳与抽象，形成一个独立于具体 DBMS 的概念模型
- 逻辑结构设计：将概念结构转换为某个 DBMS 所支持的数据模型，并对其进行优化
- 物理结构设计：为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）
- 数据库实施：运用 DBMS 提供的数据语言、工具及宿主语言，根据逻辑设计和物理设计的结果，建立数据库、编制与调试应用程序、组织数据入库、并进行试运行
- 数据库运行和维护：在数据库系统运行过程中必须不断地对其进行评价、调整与修改

2. 需求分析

需求分析：分析用户的需要与要求，是设计数据库的起点
- 重点：数据需求的理解 和 处理规则需求的理解
- 分析和表达需求的方法：结构化分析方法(Structured Analysis)，从最上层的系统组织机构入手，自顶向下逐层分解，并用数据流图和数据字典描述系统
数据字典：各类数据描述(元数据)的集合，是进行详细的数据收集和数据分析所获得的主要结果
- 组成：DBMS 内部的一组系统表，它记录了数据库中所有定义信息：关系模式/表定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等
- 内容：数据项、数据结构、数据流、数据存储、处理过程

3. 概念结构设计

概念结构设计：
- 描述概念模型的工具：E-R 模型
- 常用策略：自顶向下地进行需求分析，自底向上地设计概念结构
- 步骤：(1) 抽象数据并设计局部视图；(2) 集成局部视图，得到全局概念结构
局部视图设计：
- 选择局部应用：通常以中层数据流图作为设计分 E-R 图的依据
- 逐一设计分 E-R 图：凡能够作为属性对待的事物，应尽量作为属性
视图的集成：合并为总 E-R 图时，可能出现三种冲突
- 属性冲突：分为两类
  - 属性域冲突：属性值的类型、取值范围或取值集合不同
    - 例：某些部门(即局部应用)以出生日期形式表示学生的年龄，而另一些部门(即局部应用)用整数形式表示学生的年龄
  - 属性取值单位冲突：
    - 例：学生的身高，有的以米为单位，有的以厘米为单位，有的以尺为单位
- 命名冲突：分为两类
  - 同名异义：不同意义的对象在不同的局部应用中具有相同的名字
    - 例：局部应用 A 中将教室称为房间，局部应用 B 中将学生宿舍称为房间
  - 异名同义(一义多名)：同一意义的对象在不同的局部应用中具有不同的名字
    - 例：有的部门把教科书称为课本，有的部门则把教科书称为教材
- 结构冲突：分为三类
  - 同一对象在不同应用中具有不同的抽象（某应用是实体，某应用是属性）
  - 同一实体在不同分 E-R 图中所包含的属性个数和属性排列次序不完全相同
  - 实体之间的联系在不同局部视图中呈现不同的类型
修改与重构：
- 基本任务：消除不必要的冗余数据和冗余的实体间联系，得到基本 E-R 图
- 冗余的问题：容易破坏数据库的完整性，给数据库维护增加困难
- 消除冗余的方法：确定分 E-R 图实体之间的数据依赖 $F_L$ ；求 $F_L$ 的最小依赖集 $G_L$ ，差集为 $D = F_L-G_L$ ，逐一考察 $D$ 中的函数依赖，确定是否是冗余的联系

此处需要会合并 E-R 图及判断冲突类型，例题参考 Canvas 第 25 讲 18:35-25:30 及第 33 讲 05:45-17:25.

4. 逻辑结构设计

E-R 图向关系模型的转换：
- 一个实体型转换为一个关系模式
  - 关系的属性：实体型的属性
  - 关系的码：实体型的码
- 一个 m:n 联系转换为一个关系模式
  - 关系的属性：与该联系相连的各实体的码以及联系本身的属性
  - 关系的码：各实体码的组合
- 一个 1:n 联系与 n 端对应的关系模式合并
  - 关系的属性：在 n 端关系中加入 1 端关系的码和联系本身的属性
  - 关系的码：不变
- 一个 1:1 联系与任意一端对应的关系模式合并
  - 关系的属性：加入对应关系的码和联系本身的属性
  - 关系的码：不变
- 三个或三个以上实体间的一个多元联系转换为一个关系模式
  - 关系的属性：与该多元联系相连的各实体的码以及联系本身的属性
  - 关系的码：各实体码的组合
- 具有相同码的关系模式可合并
数据模型的优化：确定数据依赖，消除冗余的联系，确定所属范式，进行必要的分解

此处需要会将 E-R 图转换为关系模型，例题参考 Canvas 第 25 讲 39:00-50:25 及第 33 讲 17:25-21:10.

5. 数据库物理设计

物理结构的内容：数据库在物理设备上的存储结构与存取方法

6. 数据库实施与维护

重组织：重新安排存储位置、回收垃圾等，不会改变原设计的数据逻辑结构和物理结构
重构造：调整模式和内模式、增改数据项等，会改变原设计的数据逻辑结构和物理结构

第七章数据库安全

2. 数据库安全性控制

数据库安全性控制的层次：
- 应用：用户标识和鉴定
- DMBS：存取控制、审计、视图、推断控制机制
- OS：操作系统安全保护
- DB：加密机制

存取控制：用户权限定义和合法权限检查机制一起组成了 DBMS 的存取控制子系统
- 自主存取控制(Discretionary Access Control, DAC)：通过授权机制实现，更灵活
- 强制存取控制(Mandatory Access Control, 简称 MAC)：更严格

自主存取控制(DAC)

自主存取控制的对象和操作类型：
授权(GRANT)：将对指定操作对象的指定操作权限授予指定的用户
1
2
3
4
GRANT <权限>[,<权限>]... [ON <对象类型> <对象名>] TO <用户>[,<用户>]... [WITH GRANT OPTION];
- 说明：
  - 接受权限的用户可以是 PUBLIC(全体用户)
  - 如果有 WITH GRANT OPTION 子句，则获得权限的用户可以再授予其他用户；如果没有，则不能传播
▶
例：
- 例：把查询 Student 表的权限授给用户 U1
  1
  2
  3
  GRANT SELECT ON TABLE Student TO U1;
- 例：把对 Student 表和 Course 表的全部操作权限授予用户 U2 和 U3
  1
  2
  3
  GRANT ALL PRIVILIGES ON TABLE Student, Course TO U2, U3;
- 例：把对表 SC 的查询权限授予所有用户
  1
  2
  3
  GRANT SELECT ON TABLE SC TO PUBLIC;
- 例：把查询 Student 表和修改学生学号的权限授给用户 U4
  1
  2
  3
  GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;
- 例：把对表 SC 的 INSERT 权限授予 U5 用户，并允许他再将此权限授予其他用户
  1
  2
  3
  4
  GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION;
回收(REVOKE)：
1
2
3
REVOKE <权限>[,<权限>]... ON <对象类型> <对象名> FROM <用户>[,<用户>]...;
▶
例：
- 例：把用户 U4 修改学生学号的权限收回
  1
  2
  3
  REVOKE UPDATE(Sno) ON TABLE Student FROM U4;
- 例：收回所有用户对表 SC 的查询权限
  1
  2
  3
  REVOKE SELECT ON TABLE SC FROM PUBLIC;
- 例：把用户 U5 对 SC 表的 INSERT 权限收回
  1
  2
  3
  REVOKE INSERT ON TABLE SC FROM U5 CASCADE;
  若 U5 授权过其他用户 INSERT 权限，将用户 U5 的该权限收回的时候必须级联(CASCADE)收回，不然系统将拒绝执行该命令

创建权限：DBA 在创建用户时实现

1 2	`CREATE USER <username> [WITH] [DBA \| RESOURCE \| CONNECT]`

拥有的权限	CREATE USER	CREATE SCHEMA	CREATE TABLE	登录数据库，执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，须有相应权限

数据库角色：角色是权限的集合，可以为一组具有相同权限的用户创建一个角色，简化授权的过程
- 角色的创建：
  1
  CREATE ROLE <角色名>
- 给角色授权：
  1
  2
  3
  GRANT <权限> [,<权限>] ... ON <对象类型> <对象名> TO <角色> [,<角色>] ...
- 将一个角色授予其他的角色或用户：
  1
  2
  3
  GRANT <角色1> [,<角色2>] ... TO <角色3> [,<用户1>] ... [WITH ADMIN OPTION]
- 角色权限的收回：
  1
  2
  3
  REVOKE <权限> [,<权限>] ... ON <对象类型> <对象名> FROM <角色> [,<角色>] ...
▶
例：
- 例：通过角色来实现将一组权限授予一个用户，步骤如下：
  首先创建一个角色 R1
  1
  CREATE ROLE R1;
  然后使用 GRANT 语句，使角色 R1 拥有 Student 表的 SELECT、UPDATE、INSERT 权限
  1
  2
  3
  GRANT SELECT, UPDATE, INSERT ON TABLE Student TO R1;
  将这个角色授予王平、张明、赵玲，使他们具有角色 R1 所包含的全部权限
  1
  2
  GRANT R1 TO 王平, 张明, 赵玲;
  可以一次性通过 R1 来回收王平的这 3 个权限
  1
  2
  REVOKE R1 FROM 王平;
- 例：角色的权限修改
  1
  2
  3
  GRANT DELETE ON TABLE Student TO R1;
- 例：角色的权限修改
  1
  2
  3
  REVOKE SELECT ON TABLE Student FROM R1;
授权粒度：授权粒度越细，授权子系统就越灵活
缺点：由于数据本身并无安全性标记，可能存在数据的无意泄露

强制存取控制(MAC)

基本概念：
- 主体：系统中的活动实体，包括：DBMS 所管理的实际用户、代表用户的各进程
- 客体：系统中的被动实体，包括：文件、基表、索引、视图
- 敏感度标记：对于主体和客体，DBMS 为它们每个实例(值)指派一个敏感度标记(Label)
  - 主体的敏感度标记称为许可证级别(Clearance Level)
  - 客体的敏感度标记称为密级(Classification Level)
MAC 规则：
- 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体
- 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
MAC 的特点：对数据本身进行密级标记，提供了更高级别的安全性，与 DAC 共同构成 DBMS 的安全机制

3. 视图机制

视图机制：
- 可以把要保密的数据对无权存取这些数据的用户隐藏起来，对数据提供一定程度的安全保护
- 通常与授权机制配合使用
▶
例：
- 例：建立计算机系学生的视图，把对该视图的 SELECT 权限授于王平，把该视图上的所有操作权限授于张明
  先建立计算机系学生的视图 CS_Student
  1
  2
  3
  4
  5
  CREATE VIEW CS_Student AS SELECT * FROM Student WHERE Sdept='CS';
  在视图上进一步定义存取权限
  1
  2
  3
  4
  5
  6
  7
  GRANT SELECT ON CS_Student TO 王平; GRANT ALL PRIVILIGES ON CS_Student TO 张明;

4. 审计

审计：
- 启用一个专用的审计日志(Audit Log)，记录用户对数据库的所有操作
- DBA 可以利用审计日志中的追踪信息，找出非法存取数据的人、时间和内容
基本语句：
- AUDIT 语句：设置审计功能
  ▶
  例：
  例：对修改 SC 表结构或修改 SC 表数据的操作进行审计
  1
  2
  AUDIT ALTER, UPDATE ON SC;
- NOAUDIT 语句：取消审计功能
  ▶
  例：
  例：取消对 SC 表的一切审计
  1
  2
  NOAUDIT ALTER, UPDATE ON SC;

第八章数据库完整性

1. 数据库完整性概述

DBMS 的完整性控制机制：定义完整性约束条件的机制、提供完整性检查的方法、进行违约处理

2. 实体完整性

实体完整性的定义：PRIMARY KEY 子句

▶

例：

例：在学生选课数据库中，要定义 Student 表的 Sno 属性为主码

CREATE TABLE Student
  (Sno NUMBER(8),
  Sname VARCHAR(20) NOT NULL,
  Sage NUMBER(20),
  PRIMARY KEY (Sno)); 　/* 在表级定义主码 */

CREATE TABLE Student
  (Sno NUMBER(8) PRIMARY KEY ,　/* 在列级定义主码 */
  Sname VARCHAR(20) NOT NULL,
  Sage NUMBER(20));

例：要在 SC 表中定义 (Sno, Cno) 为主码

CREATE TABLE SC
  (Sno NUMBER(8) NOT NULL,
  Cno NUMBER(2) NOT NULL,
  Grade NUMBER(2),
  PRIMARY KEY (Sno, Cno)　/* 只能在表级定义主码 */
  FOREIGN KEY (Sno) REFERENCES Student(Sno) /* 在表级定义参照完整性 */
  FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性 */
  );

实体完整性检查：
- 检查主码值是否唯一，否则拒绝插入或修改
- 检查主码的各个属性是否为空，否则拒绝插入或修改
违约处理：系统拒绝此操作

3. 参照完整性

参照完整性的定义：FOREIGN KEY 子句及 REFERENCES 子句

▶

例：

例：建立表 SC

CREATE TABLE SC
  (Sno CHAR(9)　NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY (Sno, Cno), /* 表级定义实体完整性 */
  FOREIGN KEY (Sno) REFERENCES Student(Sno) /* 在表级定义参照完整性 */
  FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性 */
  );

参照完整性检查：对被参照表和参照表进行以下的增删改操作时，有可能破坏参照完整性，必须进行检查
- 在参照关系中插入元组
- 在参照关系中修改外码值
- 在被参照关系中删除元组
- 在被参照关系中修改主码

违约处理：

拒绝(NO ACTION)：不允许该操作执行（默认策略）
级联(CASCADE)：当删除或修改被参照表的一个元组导致与参照表不一致时，删除或修改参照表中所有导致不一致的元组
设置为空值(SET NULL)：当删除或修改被参照表的一个元组导致与参照表不一致时，将参照表中所有造成不一致的元组的对应属性设置为空值

▶

例：

例：建立表 SC

CREATE TABLE SC
  (Sno CHAR(9)　NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY (Sno, Cno), /* 表级定义实体完整性，Sno、Con 都不能取空值 */ 
  FOREIGN KEY (Sno) REFERENCES Student(Sno) /* 在表级定义参照完整性 */
    ON DELETE CASCADE 　 /* 删除 Student 元组时，级联删除 SC 中相应元组 */
    ON UPDATE CASCADE, 　/* 更新 Student 表中 Sno 时，级联更新 SC 中相应元组 */
  FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性*/
    ON DELETE NO ACTION /* 删除 Course 元组造成与 SC 表不一致时，拒绝删除 */
    ON UPDATE CASCADE, 　 /* 更新 Course 表中 Cno 时，级联更新 SC 中相应元组 */
  );

4. 用户定义的完整性

两类定义方法：建表时定义、通过触发器定义

属性上的约束条件：

列值非空(NOT NULL 短语)：

▶

例：

例：建立表 SC，说明 Sno、Con、Grade 属性不允许取空值

CREATE TABLE SC
  (Sno CHAR(9)　NOT NULL,　/* Sno 属性不允许取空值 */
  Cno CHAR(4) NOT NULL,　/* Cno 属性不允许取空值 */
  Grade SMALLINT NOT NULL,　/* Grade 属性不允许取空值 */
  PRIMARY KEY (Sno, Cno), /* 表级定义实体完整性 */ 
  FOREIGN KEY (Sno) REFERENCES Student(Sno) /* 在表级定义参照完整性 */
  FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 在表级定义参照完整性 */
  );

列值唯一(UNIQUE 短语)：

▶

例：

例：建立部门表 DEPT，要求部门名称 Dname 列取值唯一，部门编号 Deptno 列为主码

CREATE TABLE DEPT 
  (Deptno NUMERIC(2), 　　 
  Dname CHAR(9) UNIQUE NOT NULL, /* 要求 Dname 列值唯一，且不能取空值 */ 　　 
  Loc VARCHAR(10), 　　 
  PRIMARY KEY (Deptno)
  );

检查列值是否满足一个条件表达式(CHECK 短语)：

▶

例：

例：建立学生登记表 Student，要求年龄 <29，性别只能是‘男’或‘女’，姓名非空

CREATE TABLE Student 　　 
  (Sno NUMBER(5) PRIMARY KEY, 　/* 在列级定义主码 */
  Sname CHAR(20) NOT NULL,　/* Sname 属性不允许取空值 */
  Sage SMALLINT CHECK (Sage < 29),　/* Sage属性小于 29 */
  Ssex CHAR(2) CHECK (Ssex IN ('男','女')) /* Ssex 属性只允许取‘男’或‘女’ */
  );

例：建立表 SC，Grade 的值在 0 和 100 之间

CREATE TABLE SC
  (Sno CHAR(9),　　　　　　　　　
  Cno CHAR(4),　　　　　　　　　　
  Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),　/* Grade取值范围 0-100 */
  PRIMARY KEY (Sno, Cno), 　
  FOREIGN KEY (Sno) REFERENCES Student(Sno),
  FOREIGN KEY (Cno) REFERENCES Course(Cno));

元组上的约束条件：用 CHECK 短语设置不同属性之间取值的相互约束条件

▶

例：

例：当学生的性别是男时，其名字不能以 Ms. 打头

CREATE TABLE Student
  (Sno CHAR(9), 
  Sname CHAR(20) NOT NULL， /* Sname 非空值 */
  Ssex CHAR(2),
  Sage SMALLINT，
  Sdept CHAR(20),
  PRIMARY KEY (Sno),
  CHECK (Ssex='女' OR Sname NOT LIKE 'MS.%')
  );

5. 完整性约束命名子句

完整性约束命名子句：CONSTRAINT <完整性约束条件名> <完整性约束条件>

<完整性约束条件> 包括 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 短语等
用于建表时对完整性约束条件命名，可以灵活增加、删除一个完整性约束条件

▶

例：

例：建立学生登记表 Student，要求学号在 90000～99999 之间，年龄 < 30，性别只能是‘男’或‘女’，姓名非空

CREATE TABLE Student
  (Sno NUMERIC(6)
    CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
  Sname CHAR(20) 
    CONSTRAINT C2 NOT NULL,
  Sage NUMERIC(3) 
    CONSTRAINT C3 CHECK (Sage < 30),
  Ssex CHAR(2)
    CONSTRAINT C4 CHECK (Ssex IN ('男', '女')),
    CONSTRAINT StudentKey PRIMARY KEY(Sno));

例：建立职工表 EMP，要求每个职工的应发工资不低于 3000 元，应发工资实际上就是实发工资列 Sal 与扣除项 Deduct 之和

CREATE TABLE EMP
  (Eno NUMERIC(4) PRIMARY KEY, /* 在列级定义主码 */
  Ename CHAR(10),
  Job CHAR(8),
  Sal NUMERIC(7,2),
  Deduct NUMERIC(7,2)
  Deptno NUMERIC(2),
  CONSTRAINT TeacherKey FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno),
  CONSTRAINT C1 CHECK (Sal + Deduct >=3000));

修改表中的完整性限制：ALTER TABLE 子句

▶

例：

例：去掉 Student 表中对性别的限制

1 2	`ALTER TABLE Student DROP CONSTRAINT C4;`

例：修改 Student 表中的约束条件，要求学号改为 900000～999999之间，年龄由小于 30 改为小于 40

ALTER TABLE Student
  DROP CONSTRAINT C1;
ALTER TABLE Student
  ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
  DROP CONSTRAINT C3;
ALTER TABLE Student
  ADD CONSTRAINT C3 CHECK(Sage < 40);

6. 断言

创建断言：CREATE ASSERTION <断言名> <CHECK 子句>

<CHECK 子句> 中的约束条件与 WHERE 子句的条件表达式类似
用于指定更具一般性的约束，可以涉及多个表或聚集操作

▶

例：

例：限制数据库课程最多 60 名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
  CHECK (60 >= (SELECT count (*) /* 此断言的谓词涉及聚集操作 count */
    FROM Course, SC
    WHERE SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库'));

例：限制每一门课程最多 60 名学生选修

CREATE ASSERTION ASSE_SC_CNUM1
  CHECK (60 >= ALL(SELECT count (*) /* 此断言的谓词涉及聚集操作 count */
    FROM SC /* 和分组函数 group by 的 SQL 语句 */
    GROUP by Cno));

例：限制每个学期每一门课程最多 60 名学生选修

首先修改 SC 表的模式，增加一个‘学期(TERM)’的属性

1 2	`ALTER TABLE SC /* 先修改 SC 表，增加 TERM 属性，类型是DATE */ ADD TERM DATE;`

然后定义断言

CREATE ASSERTION ASSE_SC_CNUM2
  CHECK (60 >= ALL(SELECT count (*) /* 此断言的谓词涉及聚集操作 count */
    FROM SC /* 和分组函数 group by 的 SQL 语句*/
    GROUP by Cno, TERM));

删除断言：DROP ASSERTION <断言名>

7. 触发器

定义触发器：触发器(trigger) 是用户定义在关系表上的一类由事件驱动的特殊过程

CREATE TRIGGER <触发器名> /* 每当触发事件发生时，该触发器被激活 */
{BEFORE | AFTER} <触发事件> ON <表名> 　/* 指明触发器激活时间是在执行触发事件前或后 */
REFERENCING NEW | OLD ROW AS <变量> 　/* REFERENCING 指出引用的变量 */
FOR EACH {ROW | STATEMENT} 　/* 定义触发器的类型，指明动作体执行的频率 */
[WHEN <触发条件>] <触发动作> 　/* 仅当触发条件为真时，才执行触发动作体 */

说明：
- 触发器只能定义在基本表上，不能定义在视图上
- 触发事件可以是 INSERT、DELETE、UPDATE，也可以是 INSERT OR DELETE 或 UPDATE OF <触发列,...> 等
- BEFORE/AFTER 分别表示在触发事件的操作执行之前/之后激活触发器
- 按照所触发动作的间隔尺寸可分为行级触发器(FOR EACH ROW)和语句触发器(FOR EACH STATEMENT)，默认是语句级触发器
  - 如果是行级触发器，用户可在过程体中使用 NEW 和 OLD 引用 UPDATE/INSERT 事件之后的新值和 UPDATE/DELETE 事件之前的旧值
  - 如果是语句级触发器，则不能在触发动作体中使用 NEW 或 OLD 进行引用

▶

例：

例：当对表 SC 的 Grade 属性进行修改时，若分数增加了 10%，则将此次操作记录到另一个表 SC_U (Sno、Cno、Oldgrade、Newgrade) 中，其中 Oldgrade 是修改前的分数，Newgrade 是修改后的分数

CREATE TRIGGER SC_T /* SC_T 是触发器的名字 */
AFTER UPDATE OF Grade ON SC 　　/* UPDATE OF Grade ON SC 是触发事件 */
  /* AFTER 是触发的时机，表示当对 SC 的 Grade 属性修改完后再触发下面的规则 */
REFERENCING 　　　
  OLDROW AS OldTuple
  NEWROW AS NewTuple
FOR EACH ROW /* 行级触发器，每执行一次 Grade 更新，下面的规则就执行一次 */
WHEN (NewTuple.Grade>=1.1*OldTuple.Grade) /* 触发条件，只有该条件为真时才执行 */
BEGIN
  INSERT INTO SC_U (Sno, Cno, OldGrade, NewGrade) /* 下面的 insert 操作 */
  VALUES(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade)
END

例：将每次对表 Student 的插入操作所增加的学生个数记录到表 StudentInsertLog 中

CREATE TRIGGER Student_Count /* Student_Count 是触发器的名字 */
AFTER INSERT ON Student /* 指明触发器激活的时间是在执行 INSERT 之后*/
REFERENCING 　　　
  NEW TABLE AS DELTA /* DELTA 是一个关系名，模式与 Student 相同，包含的元组是 INSERT 语句增加的元组 */
FOR EACH STATEMENT
  /* 语句级触发器，即执行完 INSERT 语句后下面的触发动作体才执行一次*/
BEGIN
  INSERT INTO StudentInsertLog (Numbers)
  SELECT COUNT(*) FROM DELTA
END

例：定义一个 BEFORE 行级触发器，为教师表 Teacher 定义完整性规则 “教授的工资不得低于 4000 元，如果低于 4000 元，自动改为 4000 元”

CREATE TRIGGER Insert_OR_Update_Sal /* 对教师表插入或更新时激活触发器 */
BEFORE INSERT OR UPDATE ON Teacher /* BEFORE 触发事件 */
REFERENCING
  NEW row AS newtuple
FOR EACH ROW /* 行级触发器 */
BEGIN /* 定义触发动作，这是一个 PL/SQL 过程块 */
  IF (newtuple.Job = '教授' AND (newtuple.Sal < 4000))
    THEN newtuple.Sal = 4000;
    /* 因为是行级触发器，可在过程体使用插入或更新操作后的新值 */
  END IF;
END; /* 触发动作体结束 */

删除触发器：DROP TRIGGER <触发器> ON <表名>;

第九章数据库存储管理

2. 数据组织

2.1 数据库的逻辑组织方式与物理组织方式

数据库的逻辑组织：表空间 – 段 – 分区 – 数据块
数据库的物理组织：文件 – 块 – 记录
逻辑组织与物理组织的对应关系：

2.2 记录表示

定长记录存储：
- 关系表中的每条记录占据相同大小的空间
- 变长字段以定长形式存储，预留最大长度空间
变长记录存储：
- 方式一：
  - 在每条记录的头部记录该条记录的长度
  - 在记录的每个变长字段前记录该字段的长度
- 方式二：
  - 先存放定长字段，再存放变长字段
  - 第一个变长字段紧随定长字段，从第二个变长字段开始，在记录首部用指针(偏移量)指向变长字段
- 方式三：
  - 将定长字段与变长字段分开存储在不同的块中

2.3 块的组织

定长记录存储的块组织：
- 块组织：
  - 表中元组依次存放在块中
  - 块的结构：首部+记录+空闲空间
    - 首部：块头信息、块 ID、最后一次修改和访问该块的时间戳、每条记录在块内的偏移量、空闲空间头指针
- 块维护：
  - 增：在空闲空间直接插入新元组
  - 改：直接在原位置修改
  - 删：回收空间，将空闲空间加入空闲空间链表，不需要移动
变长记录存储的块组织：
- 块组织：
  - 表中元组从块的尾部连续存放
  - 块的结构：首部+空闲空间+记录
    - 首部：各记录的指针(块内偏移量)、空闲空间尾指针(块内偏移量)
- 块维护：
  - 增：从空闲空间尾部分配空间；在偏移量表中记录该元组的起始位置；调整空闲空间尾指针
  - 删：在偏移量表中为该元组指针置删除标记；释放元组空间，移动物理位置在其前面的元组（保证空闲空间连续）；修改被移动元组在偏移量表中的指针；修改空闲空间尾指针
  - 改：在原位置修改；若修改后记录在原位置放不下，会带来记录的迁移

2.4 关系表的组织

五种存放方式：
- 堆存储：记录可存储于任意有空间的位置，磁盘上存储的记录是无序的
- 顺序存储：记录按某属性或属性组值的顺序插入，磁盘上存储的记录是有序的
- 多表聚簇存储：不同表的元组聚簇存放在同一组块中，减少连接操作
- B+ 树存储：以 B+ 树索引的方式确定记录存放在哪个数据块中
- 哈希存储：用哈希函数计算表中指定属性的哈希值，以此确定相应记录放在哪个块中

3. 索引结构

索引：定义在存储表(Table)基础之上，无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项(Index Entries)组成，每一索引项又包括索引字段和行指针
顺序表索引：在顺序表的排序属性(组)上建立索引，也称作主索引(Primary Index) 或聚簇索引(Clustering Index)
- 稠密索引(Dense Index)：索引块中存放每条记录的索引属性值以及指向相应记录的指针
- 稀疏索引(Sparse Index)：基本表的每个物理存储块只对应一个索引项，每个索引项存放每个物理块的第一条记录的索引属性值及指向该物理块的指针
- 多级索引(Multilevel Index)：对索引再建立索引，形成多级索引；第一级索引是稠密或稀疏索引，第二级及以上为建立在上一级索引上的稀疏索引
辅助索引(Secondary Index)：建立在表的非排序属性上的索引
- 一个表最多只能建立一个主索引，但可以在不同属性上建立多个辅助索引；辅助索引必须是稠密索引
- 可以引入指针桶去除重复索引项：索引项指针 $\to$ 指针桶相应位置 $\to$ 相应的元组
聚簇索引：索引中邻近的记录在主文件中也邻近存储
- 一个主文件只能有一个聚簇索引文件，但可有多个非聚簇索引文件
- 主索引和聚簇索引是能决定存储位置的索引
B+ 树索引：本质上是一个多级索引，将索引块组织成一棵 M 叉平衡树，索引字段值在叶结点中按顺序排列，且所有叶结点可覆盖所有键值的索引

哈希索引：将记录的索引属性值映射到哈希桶，每个桶存放放一条或多条哈希值相同的索引项，每个索引项包括属性值和指向相应记录的指针
位图索引：对每条记录用一个位向量标识索引属性的取值，每一个位向量对应于索引属性的一个可能的取值

第十章查询处理和优化

1. RDBMS 的查询处理

查询处理的步骤：查询分析、查询检查、查询优化、查询执行
查询优化分类：
- 代数优化：通过对关系代数表达式的等价变换来提高查询效率
- 物理优化：选择高效合理的操作算法或存取路径

2. RDBMS 的查询优化

查询优化的目标：使数据库查询的执行时间最短
查询执行的代价：最主要的是从磁盘访问数据的 I/O 代价

3. 代数优化

基本思想：改变关系代数的操作次序，尽可能早做选择和投影运算
语法树的启发式优化：
- 方法：尽可能把选择和投影操作移动到树的叶端
▶
PPT 例题：
- 例：
  1
  2
  3
  4
  SELECT Student.Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno='81003'
  把 SQL 语句转换成查询树
  为了使用关系代数表达式的优化法，假设内部表示是关系代数语法树
  对语法树进行优化，把选择 $\sigma_\text{SC.Cno=‘81003’}$ 移到叶端

第十一章数据库恢复技术

1. 事务的基本概念

事务(Transaction)：用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位
- 事务是数据库恢复和并发控制的基本单位
事务的结束：COMMIT(提交) 或 ROLLBACK(回滚)
事务的 ACID 特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持续性(Durability)

2. 故障的种类

故障的种类：
- 事务内部的故障：某个事务在运行过程中由于种种原因未运行至正常终止点就夭折了，只影响该事务本身
- 系统故障：造成系统停止运转的任何事件，所有正在运行的事务都非正常终止
- 介质故障：硬件故障使存储在外存中的数据部分丢失或全部丢失，并影响正在存取这部分数据的所有事务
  - 发生可能性最小，但破坏性最大

▶

PPT 例题：

问题：若事务中有表达式 a/b，如果 b=0 时会产生的故障属于什么故障？
答案：事务故障.

3. 恢复的实现技术

3.1 数据转储

数据转储：DBA 定期将整个数据库复制到磁盘或其他存储介质上保存起来的过程，这些备用的数据文本称为后备副本
转储方法：
- 静态转储：转储期间不允许对数据库的任何存取、修改活动
- 动态转储：转储期间允许对数据库进行存取或修改
  - 需要把转储期间各事务对数据库的修改活动登记下来，建立日志文件(log file)
  - 后备副本加上日志文件才能把数据库恢复到某一时刻的正确状态
- 海量转储：每次转储全部数据库
- 增量转储：只转储上次转储后更新过的数据

3.2 登记日志文件

日志文件(log file)：用来记录事务对数据库的更新操作的文件，运行日志直接写入介质存储
登记日志文件的原则：
- 登记的次序严格按并行事务执行的时间次序
- 必须先写日志文件，后写数据库

4. 恢复策略

事务故障的恢复：
- 恢复方法：恢复子系统应利用日志文件撤消(UNDO) 此事务已对数据库进行的修改
- 恢复步骤：
  - 反向扫描日志文件，查找该事务的更新操作
  - 对该事务的更新操作执行逆操作，即将日志记录中“更新前的值” (Before Image, BI) 写入数据库：
    - 插入操作，“更新前的值”为空，则相当于做删除操作
    - 删除操作，“更新后的值”为空，则相当于做插入操作
    - 若是修改操作，则用 BI 代替 AI(After Image)
  - 继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理
  - 如此处理下去，直至读到此事务的开始标记
- 事务故障的恢复由系统自动完成，不需要用户干预
系统故障的恢复：
- 恢复方法：撤销(Undo) 故障发生时未完成的事务，重做(Redo) 已完成的事务
- 恢复步骤：
  - 正向扫描日志文件，建立两个队列：
    - REDO-LIST 重做队列：在故障发生前已经提交的事务，即既有 BEGIN TRANSACTION 记录也有 COMMIT 记录的事务
    - UNDO-LIST 撤销队列：故障发生时尚未完成的事务，即只有 BEGIN TRANSACTION 记录但无相应的 COMMIT 记录的事务
  - 对 Undo 撤销队列事务进行 UNDO 处理
    - 反向扫描日志文件，对每个 UNDO 事务的更新操作执行逆操作，即将日志记录中“更新前的值”写入数据库
  - 对 Redo 重做队列事务进行 Redo 处理
    - 正向扫描日志文件，对每个 Redo 事务重新执行日志文件登记的操作，即将日志记录中“更新后的值”写入数据库
- 系统故障的恢复由系统在重新启动时自动完成，不需要用户干预

▶

PPT 例题：

问题：假设日志文件尾部为：<T1 start>, <T1 A, 1000, 950>, <T2 start>, <T2 C, 700, 600>, <T1 B, 2000, 2500>, <T1 commit>，则恢复时应执行什么操作？
答案：Undo T2, Redo T1.

介质故障的恢复：
- 恢复方法：先装入发生介质故障前某个时刻的数据副本，再重做(Redo) 自此时开始的所有成功事务，撤销(Undo) 未完成的事务
- 恢复步骤：
  - 装入最新的后备数据库副本，使数据库恢复到最近一次转储时的一致性状态
    - 对于静态转储的数据库副本，装入后数据库即处于一致性状态
    - 对于动态转储的数据库副本，还须同时装入转储时刻的日志文件副本，利用与恢复系统故障相同的方法(即 REDO+UNDO)，才能将数据库恢复到一致性状态
  - 装入有关的日志文件副本(即从转储结束点到故障发生点的日志)，重做已提交的事务，撤销未提交的事务
- 介质故障的恢复需要 DBA 介入

▶

PPT 例题：

问题：事务故障、系统故障、介质故障的恢复分别需要什么？
答案：事务故障、系统故障仅需要日志文件，介质故障需要日志文件和备份.

5. 具有检查点的恢复技术

检查点(checkpoint)技术：
- 在检查点时刻，DBMS 强制内存中内容和物理介质中内容保持一致，即将内存中更新的所有内容写入磁盘 DB
- 在日志文件中增加检查点记录，增加重新开始文件
  - 检查点记录：记录检查点时刻所有正在执行的事务清单，以及这些事务最近一个日志记录的地址
  - 重新开始文件：记录各个检查点记录在日志文件中的地址
利用检查点的恢复策略：
- 从重新开始文件中找到最后一个检查点记录在日志文件中的地址
- 由该地址在日志文件中找到最后一个检查点记录
- 由该检查点记录得到检查点建立时刻所有正在执行的事务清单 ACTIVE-LIST
  - 建立两个事务队列
    - UNDO-LIST：需要执行 UNDO 操作的事务集合
    - REDO-LIST：需要执行 REDO 操作的事务集合
  - 把 ACTIVE-LIST 暂时放入 UNDO-LIST 队列，REDO 队列暂为空
- 从检查点开始正向扫描日志文件，直到日志文件结束
  - 如有新开始的事务 $T_i$ ，把 $T_i$ 暂时放入 UNDO-LIST 队列
  - 如有提交的事务 $T_j$ ，把 $T_j$ 从 UNDO-LIST 队列移到 REDO-LIST 队列
- 对 UNDO-LIST 中的每个事务执行 UNDO 操作，对 REDO-LIST 中的每个事务执行 REDO 操作

此处需要会利用检查点机制的进行恢复，例题参考 Canvas 第 38 讲 45:50-49:25.

第十二章并发控制

1. 并发控制概述

并发操作带来的数据不一致性：
- 丢失修改(lost update)：指事务 1 与事务 2 从数据库中读入同一数据并修改
  - 事务 2 的提交结果破坏了事务 1 提交的结果，导致事务 1 的修改被丢失
- 不可重复读(non-repeatable read)：指事务 1 读取数据后，事务 2 执行更新操作，使事务 1 无法再现前一次读取结果
- 读“脏”数据(dirty read)：
  - 事务 1 修改某一数据，并将其写回磁盘
  - 事务 2 读取同一数据后
  - 事务 1 由于某种原因被撤消，这时事务 1 已修改过的数据恢复原值
  - 事务 2 读到的数据就与数据库中的数据不一致，即 “脏”数据

2. 封锁

基本封锁类型：
- 排它锁(eXclusive lock, X 锁)：又称为写锁
  - 若事务 T 对数据对象 A 加上 X 锁，则只允许 T 读取和修改 A，其它任何事务都不能再对 A 加任何类型的锁，直到 T 释放 A 上的锁
  - 保证其他事务在释放 A 上的锁之前，不能读取和修改 A
- 共享锁(Share lock, S 锁)：又称为读锁
  - 若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改 A
  - 其它事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁
  - 保证其他事务可以读 A，但在 T 释放 A 上的 S 锁之前，不能修改 A
封锁类型的相容矩阵：

3. 封锁协议

一级封锁协议：
- 内容：事务 T 在修改数据 R 之前必须先对其加 X 锁，直到事务结束才释放
  - 如果仅仅是读数据而不对其修改，是不需要加锁的
- 效果：可防止丢失修改，但不能保证可重复读和不读“脏”数据
二级封锁协议：
- 内容：在一级封锁协议基础上，增加事务 T 在读取数据 R 前必须先加 S 锁，读完后即可释放 S 锁
- 效果：可防止丢失修改和读“脏”数据，但不能保证可重复读
三级封锁协议：
- 内容：在一级封锁协议基础上，增加事务 T 在读取数据 R 前必须先加 S 锁，直到事务结束才释放
- 效果：可防止丢失修改、读脏数据和不可重复读

5. 并发调度的可串行性

可串行化调度：多个事务的并行执行是正确的，当且仅当其结果与按某一次序串行地执行它们时的结果相同，这种并行调度策略称为可串行化(Serializable)调度
冲突操作：指不同的事务对同一个数据的读写操作和写写操作
- $R_i(x)$ 与 $W_j(x)$ ，事务 $T_i$ 读 $x$ ，事务 $T_j$ 写 $x$ ，其中 $i\neq j$
- $W_i(x)$ 与 $W_j(x)$ ，事务 $T_i$ 写 $x$ ，事务 $T_j$ 写 $x$ ，其中 $i \neq j$
- 其他操作是不冲突操作
冲突可串行化调度：一个调度 Sc 在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度 Sc’，如果 Sc’ 是串行的，称调度 Sc 为冲突可串行化的调度
- 不同事务的冲突操作和同一事务的两个操作是不能交换的
- 若一个调度是冲突可串行化调度，则一定是可串行化的调度
▶
PPT 例题：
- 例：今有调度 $Sc_1=r_1(A)w_1(A)r_2(A)w_2(A)r_1(B)w_1(B)r_2(B)w_2(B)$
  可以把 $w_2(A)$ 与 $r_1(B)w_1(B)$ 交换，得到： $r_1(A)w_1(A)r_2(A)r_1(B)w_1(B)w_2(A)r_2(B)w_2(B)$
  再把 $r_2(A)$ 与 $r_1(B)w_1(B)$ 交换，得到： $Sc_2=r_1(A)w_1(A)r_1(B)w_1(B)r_2(A)w_2(A)r_2(B)w_2(B)$
  $Sc_2$ 等价于一个串行调度 $T_1$ 、 $T_2$ ，所以 $Sc_1$ 为冲突可串行化调度
- 例：有三个事务 $T_1=W_1(Y)W_1(X)$ ， $T_2=W_2(Y)W_2(X)$ ， $T_3=W_3(X)$
  调度 $L_1=W_1(Y)W_1(X)W_2(Y)W_2(X)W_3(X)$ 是一个串行调度
  调度 $L_2=W_1(Y)W_2(Y)W_2(X)W_1(X)W_3(X)$ 不满足冲突可串行化
  调度 $L_2$ 不满足冲突可串行化，但是调度 $L_2$ 是可串行化的
  调度 $L_2$ 执行的结果与调度 $L_1$ 相同， $Y$ 的值都等于 $T_2$ 的值， $X$ 的值都等于 $T_3$ 的值
- 冲突可串行性的判别：
  - 构造一个前驱图(有向图)，结点是每一个事务 $T_i$
  - 如果 $T_i$ 的一个操作与 $T_j$ 一个操作发生冲突，且 $T_i$ 在 $T_j$ 前执行，则绘制一条边，由 $T_i$ 指向 $T_j$
  - 如果此有向图没有环，则是冲突可串行化的

此处需要会判断冲突可串行性，例题参考 Canvas 第 40 讲 22:05-28:45 及 29:40-41:10.

6. 两段锁协议

两段锁协议(TwoPhase Locking，简称 2PL)：
- 内容：所有的事务必须分两个阶段对数据项加锁和解锁
  - 第一阶段是获得封锁，事务可以申请获得任何数据项上的任何类型的锁，但是不能释放任何锁
  - 第二阶段是释放封锁，事务可以释放任何数据项上的任何类型的锁，但是不能再申请任何锁
- 效果：如果并行执行的所有事务都遵守两段锁协议，则对这些事务的所有并行调度策略都是冲突可串行化的
  - 事务遵守两段锁协议是可串行化调度的充分条件

7. 封锁的粒度

7.1 封锁粒度

封锁的粒度(Granularity)：封锁对象的大小
- 常见的封锁粒度：数据库、关系、元组

封锁的粒度越	大	小
系统被封锁的对象	少	多
并发度	小	高
系统开销	小	大

7.2 多粒度封锁

多粒度树：以树形结构来表示多级封锁粒度
- 例：三级粒度树，根结点为数据库，数据库的子结点为关系，关系的子结点为元组
多粒度封锁协议：
- 允许多粒度树中的每个结点被独立地加锁
- 对一个结点加锁意味着这个结点的所有后裔结点也被加以同样类型的锁
- 两种封锁方式：显式封锁(直接加到数据对象上的封锁)、隐式封锁(由于其上级结点加锁而使该数据对象加上了锁)

7.3 意向锁

意向锁(intention lock)：
- 如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁
- 对任一结点加锁时，必须先对它的上层结点加意向锁
常用意向锁：
- 意向共享锁(Intent Share Lock，简称 IS 锁)：
  - 如果对一个数据对象加 IS 锁，表示它的后裔结点拟(意向)加 S 锁
  - 例：事务 T1 要对某个元组加 S 锁，则要首先对关系和数据库加 IS 锁
- 意向排它锁(Intent Exclusive Lock，简称 IX 锁)：
  - 如果对一个数据对象加 IX 锁，表示它的后裔结点拟(意向)加 X 锁
  - 例：要对某个元组加 X 锁，则要首先对关系和数据库加 IX 锁
- 共享意向排它锁(Share Intent Exclusive Lock，简称 SIX 锁)：
  - 如果对一个数据对象加 SIX 锁，表示对它加 S 锁，再加 IX 锁，即 SIX = S + IX
  - 例：对某个表加 SIX 锁，则表示该事务要读整个表(所以要对该表加 S 锁)，同时会更新个别元组(所以要对该表加 IX 锁)
意向锁的相容矩阵：
- 同级别的意向锁之间是兼容的，冲突交由更低的粒度进行判断
- 兼容性矩阵比较的是同层粒度不同类型锁之间的兼容性
此处讲解在 Canvas 第 41 讲 22:50-30:40.
- 锁的强度：指它对其他锁的排斥程度
具有意向锁的多粒度封锁方法：
- 申请封锁时应该按自上而下的次序进行
- 释放封锁时则应该按自下而上的次序进行

希望大家考试取得好成绩！

参考资料

本文参考上海交通大学《数据库技术》课程 CS3321 郭捷老师的 PPT 课件整理.

数据库技术

#期末复习 #数据库技术

数据库技术：期末复习

https://cny123222.github.io/2026/06/18/数据库技术：期末复习/

Author

Nuoyan Chen

Posted on

June 18, 2026

Licensed under

机器学习：期末复习 Previous

感知与认知：期末复习 Next

数据库技术：期末复习

第一章 数据库系统概论

1. 数据库系统概述

2. 数据模型

3. 数据库系统结构

4. 数据库系统的组成

第二章 关系模型和关系运算理论

2. 关系数据结构

4. 关系的完整性

5. 关系代数

第三章 关系规范化基础

2. 数据依赖

3. 关系规范化

4. 数据依赖的公理系统

第四章 结构查询语言 SQL

1. SQL 概述

2. 数据定义

3. 数据查询

3.1 单表查询

3.2 连接查询

3.3 嵌套查询

3.4 集合查询

3.5 派生查询

4. 数据更新

5. 空值

6. 视图

第六章 数据库设计

1. 数据库设计概述

2. 需求分析

3. 概念结构设计

4. 逻辑结构设计

5. 数据库物理设计

6. 数据库实施与维护

第七章 数据库安全

2. 数据库安全性控制

自主存取控制(DAC)

强制存取控制(MAC)

3. 视图机制

4. 审计

第八章 数据库完整性

1. 数据库完整性概述

2. 实体完整性

3. 参照完整性

4. 用户定义的完整性

5. 完整性约束命名子句

6. 断言

7. 触发器

第九章 数据库存储管理

2. 数据组织

2.1 数据库的逻辑组织方式与物理组织方式

2.2 记录表示

2.3 块的组织

2.4 关系表的组织

3. 索引结构

第十章 查询处理和优化

1. RDBMS 的查询处理

2. RDBMS 的查询优化

3. 代数优化

第十一章 数据库恢复技术

1. 事务的基本概念

2. 故障的种类

3. 恢复的实现技术

3.1 数据转储

3.2 登记日志文件

4. 恢复策略

5. 具有检查点的恢复技术

第十二章 并发控制

1. 并发控制概述

2. 封锁

3. 封锁协议

5. 并发调度的可串行性

6. 两段锁协议

7. 封锁的粒度

7.1 封锁粒度

7.2 多粒度封锁

7.3 意向锁

希望大家考试取得好成绩！

参考资料

第一章数据库系统概论

第二章关系模型和关系运算理论

第三章关系规范化基础

第四章结构查询语言 SQL

第六章数据库设计

第七章数据库安全

第八章数据库完整性

第九章数据库存储管理

第十章查询处理和优化

第十一章数据库恢复技术

第十二章并发控制