NoteGit

个人Git学习笔记 —–by BRabbitFan

Git命令汇总

• 基础
• 本地常用
• 远程操作

基本概念

• Git
• Git的本地结构与代码托管中心
• 分支

本地库操作

• 创建本地库
• 将文件提交至本地库
• 查看工作区与暂存区的状态
• 查看提交日志
• 查看文件的修改记录
• 切换文件的版本
• 删除文件
• 对比文件的差异
• 分支操作

远程库操作

• 初始化远程库
• 将远程库的资源获取至本地库
• 将本地库的资源推送至远程库
• 远程库的复制与请求合并
• SSH免密登录

规范化

函数依赖

函数依赖定义

设R(U)是一个属性集U上的关系模式 , X和Y是U的子集 .
若对于R(U)的任意一个可能的关系r :
r中不可能存在两个元组在X上的属性值相等 , 而在Y上的属性值不等.

则称 : “X函数确定Y” 或 “Y函数依赖于X” , 记作 X->Y
X则称为这个函数依赖的决定属性组, 也称为决定因素

若Y不函数依赖于X, 则记作 : X -/> Y

例子1

如下的一张表

其中两个元组违背了Sno->Sname的依赖, 因此这张表不正确

关于函数依赖

• 函数依赖是语义范畴的概念, 只能 根据语义来确定函数依赖
• 数据库设计者可以对现实世界作 强制规定 , 以满足函数依赖
• 函数依赖是指 关系模式R在任何时刻的盥洗室里均要满足的约束条件

平凡函数依赖 与 非平凡函数依赖

在关系模式R(U)中:
若X->Y, Y不是X的一部分, 则X->Y是 非平凡的函数依赖
若X->Y, Y是X的一部分, 则X->Y是 平凡的函数依赖

例如

SC(Sno, Cno, Grade) 中 :
(Sno, Cno)->Grade 是 非平凡函数依赖
(Sno, Cno)->Sno 是 平凡函数依赖

对于任何一个关系模式, 平凡函数依赖是必然陈立的. 因此讨论的总是非平凡的函数依赖

完全函数依赖 与 部分函数依赖

在关系模式R(U)中:
如果X->Y , 且任何一个X真子集X’都有X’-/>Y, 则称 Y完全函数依赖于X , 记作 X -F> Y
如果X->Y , 只要有一个X真子集X’满足X’->Y, 则称 Y部分函数依赖于X , 记作 X -P> Y

例如

STUDENT(Sno, Sdapt, Mname, Cno, Grade) 中 :
(Sno, Cno) -F> Grade 是 完全函数依赖
(Sno, Cno) -P> Sdept 是 部分函数依赖 (因为Sno->Sdept)

传递函数依赖

在关系模式R(U)中:
如果X->Y , X不包含Y , Y-/>X , Y->Z , 则称 Z对X传递函数依赖 , 记作 X -传递> Z
注意 : 若Y->, 即X<–>Y, 则 Z直接依赖于X

例如

STUDENT(Sno, Sdapt, Mname, Cno, Grade) 中 :
Sno->Sdept , Sdept->Mname , Sno -传递> Mname

码(键)

K为R<U,F>中的属性或属性组合 : 若K-F>U , 则K称为R的一个 候选码

• 如果U部分函数依赖于K , 即 K-P>U , 则称K为 超码
• 候选码是最小的超码 , 即K的任意一个真子集都不是候选码
• 若R有多个候选码 , 则选定其中一个作为 主码
• 若整个属性组是一个码 , 则称为 全码
• 若属性组X并非R的码 , 但它是另一个关系模式的码 , 则称 X 是 R 的 外码

例如

S(_Sno_ , Sdept , Sage) 中
Sno->(Sno,Sdept,Sage) , Sno 是码 可以选择Sno为主码
(Sno,Sdept) , (Sno,Sage) , (Sno,Sdept,Sage) 都是超码
SC(_Sno_,_Cno_,Grade)中(Sno,Cno)是码 可以选择(Sno,Cno)为主码

主属性与非主属性

• 包含在任何一个候选码中的属性 , 称为 主属性
• 不包含在任何码中的属性称为 非主属性 或 非码属性

范式 与 第一范式(1NF)

范式是符合某一级别的关系模式的集合 , 关系满足不同程度要求的为不同的范式

范式的种类

• 第一范式 (1NF)
• 第二范式 (2NF)
• 第三范式 (3NF)
• BC范式 (BCNF)
• 第四范式 (4NF)
• 第五范式 (5NF)

各种范式之间是由低到高包含的关系 : 1NF 包含 2NF , 2NF 包含 3NF…
即 : 满足了高等级范式 自然也就满足了 低等级范式

规范化

一个低一级范式的关系模式 , 通过模式分解可以转换为若干个高一级范式的关系模式的集合.
这个过程叫做 规范化

第一范式(1NF)

如果一个关系模式R的所有属性都是不可分的基本数据项 , 则R∈1NF

第一范式是对关系模式最起码的要求, 不满足第一范式的数据库模式不能称为关系数据库模式
即关系数据库模式不能 : 表中有表

例如 : 一个字段在一个基本表中出现多次, 则不符合1NF

例子3-1

SLC(Sno,Cno,Sdept,Sloc,Grade) 关系模式中:(Sloc为住)依赖图如下:

其中 : SLC满足1NF , 码为(Sno,Cno) , 主属性为Sno,Cno , 非主属性为Grade,Daept,Sloc
这里非主属性Sdept和Sloc部分函数依赖与码(Sno,Cno)

这其中存在问题 :

1. 插入异常 : 若某学生已入住但未选课 , 则无法插入其数据 , 因为Cno是主属性
2. 删除异常 : 若某学生只选了3号课程 , 但他又退选了3号课程 , 则删除其选修课程号时将删除他的整条记录
3. 更新复杂 : 若某学生转系 , 则要修改其的Sdept与Sloc , 但若他选了K门课 , 则要在K条记录中操作
4. 数据冗余 : 若某学生选修了8门课程 , 则他的Sdept与Sloc的值被重复存储了8次

分析原因 : 是因为非主属性Sdept和Sloc部分函数依赖与码(Sno,Cno)
解决办法 : 投影分解法 , 将SLC分解为两个关系模式 , 消除这些部分函数依赖

第二范式(2NF)

若关系模式R∈1NF , 且 每个非主属性都完全函数依赖与R的码 , 则 R∈2NF

例子3-1中的SLC∈1NF但SLC/∈2NF

例3-2 : 将例3-1的SLC表改造至满足2NF

将 SLC(Sno,Cno,Sdept,Sloc,Grade) 拆分为如下两个关系模式 :
SC(Sno,Cno,Grade) 与 SL(Sno,Sdept,Sloc) 函数依赖图如下:

SC的码(Sno,Cno) , SL的码Sno
所有非主属性对码都是完全依赖了 , SC和SL都满足2NF
之前存在的四个问题得到了 一定程度 的解决

但这其中仍然存在问题 :

1. 插入异常 : SL中若暂时没有在校学生 , 则无法在中将院系和住处等信息存入数据库
2. 删除异常 : SL中若某个系学生全毕业了 , 则删除这些学生信息同时也删除了院系和住等信息
3. 更新复杂 : SL中当调整一个系的住处时 , 需要调整该系所有N个学生的N个元组
4. 数据冗余 : SL中每个系的学生都住在一个地方 , 但关于系的住处的信息却重复出现

分析原因 ： 在SL中 ：Sno->Sdept , Sdept->Sloc , Sno-传>Sloc . 即存在 非主属性传递函数依赖于码
解决方法 : 投影分解法 , 把SL分解为两个关系模式 , 消除传递函数依赖

第三范式(3NF)

关系模式R<U,F>∈1NF , 若R中不存在这样的码X , 属性组Y 及 为主属性Z (Y不包含Z) :
使得X->Y , Y->Z , Y-/>X 成立, 则称为R<U,F>属于3NF
(即不存在 非主属性对码的部分函数依赖 , 也不存在 非主属性对码的传递函数依赖 )

例3-2中的SC∈3NF , 但SL/∈3NF

例3-3 : 将例3-2中的表改造至满足3NF

SC(Sno,Cno,Grade) 不变 . 将 SL(Sno,Sdept,Sloc) 拆分为两个关系模式 :
SD(Sno,Sdept) 和 DL(Sdept,Sloc) 函数依赖图如下 :

所有非主属性都不存在传递依赖于码 , 所有关系模式都符合3NF .
(本身源自2NF , 也没有非主属性对码的部分依赖)
之前的问题得到了一定的解决

BC范式(BCNF)

设关系模式R<U,F>∈1NF , 如果R的每个函数依赖X->Y , 且 X不包含Y 时 , X必须含有码 , 那么R属于BCNF
(在R<U,F>中 , 若每个决定因素都包含码 , 则R∈BCNF . )
即不存在 任何属性 对码有 部分函数依赖 和 传递函数依赖

BCNF的性质 :

1. 所有非主属性对每一个码都是完全函数依赖的
2. 所有主属性对每一个不包含它的码也是完全函数依赖的
3. 没有任何属性完全函数依赖于非码的任何一组属性

例子3-4

关系模式 STJ(S,T,J) 中 : S是学生 , T是教师 , J是课程 . 语义如下:

• 每一个教师只教一门课 T->J
• 每一门课有若干个教师教 , 但学生选定课后 , 就确定了一个固定教师 (S,J)->T
• 某个学生选修某个教师的课程就确定了所选课的名称 (S,T)->J

函数依赖图如下 :

• 候选码 : (S,J) 和 (S,T)
• S T J 都是主属性 => 不存在非主属性对码的部分函数依赖与传递函数依赖 => STJ∈3NF

该例子存在问题 :

1. 插入异常 : 若某教师开了某门课 , 但还没有学生选这个课 , 则有关信息无法插入
2. 删除异常 : 若选修某门的学生都毕业了 , 在删除这些学生相关元组同时也删除了课程信息
3. 修改复杂 : 某个课程改名了 , 则选修了该课程的学生元组都要更新记录
4. 数据冗余 : 虽然每个教师只教一门课 , 但每个选修该教师的该课程的学生元组都记录了教师信息

分析原因 : 主属性J部分依赖于码(S,T) , 因为 T->J
解决方法 : 采用投影分解法 , 将STJ分解为两个关系模式 , 消除主属性对码的部分函数依赖

改进例3-4

将 STJ(S,T,J) 分解为两个关系模式 : SJ(S,J) 和 TJ(T,J)
SJ的码是(S,J) TJ的码是T , 依赖图为 :

至此没有任何属性对码有 部分函数依赖 和 传递函数依赖

BC模式解决了上述的问题

如果一个关系数据库内的 所有关系模式都属于BCNF , 则 :
在函数依赖范畴内 , 它已经实现了模式的彻底分解 , 达到了最高的规范化程度 .

关系模式规范化的基本步骤

范式	定义
1NF	所有属性都是不可分的基本数据项 ( 不可表中有表 )
2NF	所有非主属性都完全依赖于码 ( 消除非主属性对码的部分依赖 )
3NF	所有非主属性都只完全依赖于码 ( 消除非主属性对码的传递依赖 )
BCNF	所有属性都只完全依赖于码 ( 消除主属性对码的传递依赖 )

关系数据理论

针对一个具体问题, 如何构造适合的数据模型. 即应该构造几个关系, 每个关系的组成属性等.

例子1

现需要开发一个学校教务数据库, 涉及的对象有 :

• 学生学号(Sno), 学生院系(Sdept), 系主任名(Mname), 课程号(Cno), 成绩(Grade)

语义有 :

1. 一个系有若干个学生, 但一个学生只属于一个系;
2. 一个系只有一名主任;
3. 一个学生可以选修多门课程, 每门课程有若干学生选修;
4. 每个学生所学的每门课程都有一个成绩.

设计一个关系模式如下

STUDENT(Sno, Sdept, Mname, Cno, Grade)

这样的设计显然是有问题的 :

1. 数据冗余 , 浪费空间 (例如系主任的名字重复出现多次)
2. 更新异常 (例如要更新系主任的名字, 则要在这个系有关的所有元组都更新)
3. 插入异常 (例如一个新的系暂时没有招生, 则无法插入系和系主任的信息)
4. 删除异常 (例如一个系的学生全毕业了, 删除这些学生信息的同时, 系主任和系的信息也被删除了)

之所以有这些问题是由于模式中某些数据依赖引起的

一个好的模式应该: 不会发生插入异常, 更新异常, 删除异常, 数据冗余度小
解决的半发就是 : 消除其中不合适的数据依赖

设计一个新的关系模式如下

S (Sno, Sdept, Sno -> Sdept)
SC (Sno, Cno, Grade, (Sno, Cno) -> Grade)
DEPT (Sdapt, Mname, Sdept -> Mname)

这种模式设计消除了部分不合适的数据依赖
这样就不会产生插入异常, 更新异常, 删除异常的问题, 数据冗余度也得到了控制

将这种好的经验上升为理论, 就是 : 利用规范化理论改造关系模式, 消除其中不合适的数据依赖.

数据依赖

上例中第一种关系模式 : STUDENT(Sno, Sdept, Mname, Cno, Grade)
其中 : Sdept = f(Sno) , 即Sno以某种函数的方式确定了Sedpt, 记作Sno -> Sdept
其中 : Mname = f(Sdept) , 即Sdept函数确定Mname, Sdept -> Mname
其中 : Grade = f((Sno, Cno)), (Sno, Cno)函数确定Grade, (Sno, Cno) -> Grade
则该关系模式的属性集合 : U = { Sno, Sdept, Mname, Cno, Grade }
该属性组上的函数依赖集合 : F = {Sno->Sdept, Sdept->Mname, (Sno,Cno)->Grade }
上述依赖集合可表示为 :

数据依赖是完整性约束的一种表现形式

• 限定属性取值范围 (例如学生成绩只能是0-100)
• 定义属性值间的关联 (主要体现于值相等与否)
• 数据库模式设计的关键

数据依赖

• 是通过关系中属性值的相等与否体现数据间的相互关系
• 是现实世界属性间相互关系的抽象
• 是数据内在的性质
• 是语义的体现

数据依赖的主要类型

• 函数依赖 (Functional Dependency, FD)
• 多值依赖 (Multivalued Dependeccy. MVD)
• 连接依赖
• …

数据依赖对关系模式的影响

• 不适合的数据依赖,造成插入异常, 删除异常, 更新异常和数据冗余

关系模式的简化表示

关系模式的形式化定义

R ( U , D , DOM , F)
R : 关系名, 是符号化的元组语义
U : 关系的属性集合
D : 属性组U中属性所来自的域
DOM : 属性向域的映像集合
F : 属性间数据的依赖关系集合

关系模式的简化表示

R < U , F >

将关系模式简化为三元组, 影响数据库设计模式的主要是U和F
当且仅当U上的与给关系 r 满足F时, r 称为关系模式R<U,F>的一个关系

NoteLua

个人Lua脚本学习笔记 —-by BRabbitFan

概念

基础概念

• Lua简介
• 安装配置
• 测试

基础

数据类型

• 注释
• 数据类型
• nil (空)
• boolean (布尔)
• number (数字)
• string (字符串)
• table (表)
• function (函数)
• thread (线程)
• userdata (自定义类型)

变量-运算符

• 变量
• 运算符

循环-分支-方法

• 循环
• 分支
• 方法

数据类型进阶

• string进阶
  • 转义字符
  • string成员方法
  • 字符串格式化
  • 匹配模式
• table进阶
  • Array (数组)
  • Map (字典)
  • Module/Package (模块/包)
  • Object (对象)
• iterator (迭代器) (function进阶)

元表-协程

• Metatable (元表)
• Coroutine (协程)

面向对象-文件操作

• 面向对象
  • 利用元表实现OOP
  • 利用表克隆函数实现OOP
  • 利用闭合函数实现OOP
• 文件操作
  • 简单模式
  • 完全模式

进阶

数据库完整性

数据库完整性包括

• 数据的正确性 (数据是符合现实世界语义 , 反应了当前实际情况的)
• 数据的相容性 (数据库同一对象在不同表中的数据是符合逻辑的)

数据库管理系统必须提供:

1. 定义完整性约束条件的机制 (SQL的数据定义语句实现) (实体完整性, 参照完整性, 用户定义的完整性)
2. 完整性检查机制 (INSERT UPDATE DELETE语句后开始检查, 也可以在事务提交的时候检查)
3. 违约处理 (若发现操作违背了完整性约束条件, 采取一定操作)
   • 拒绝执行这条操作 (NO ACTION)
   • 级联执行其他操作 (CASCADE)

实体完整性

实体完整性这项规则要求每个数据表都 必须有主键 , 而作为主键的所有字段，其属性必须是 唯一及非空值 .

实体完整性定义

• 在CREATE TABLE中用PRIMARY KEY定义主键
  • 单属性构成主键
    • 定义为 列级 完整性约束条件
    • 定义为 表级 完整性约束条件
  • 多个属性构成主键
    • 定义为 表级 完整性约束条件

例子1-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 将Student表中的Sno属性定义为主键
  # 方式1, 定义为列级约束
  CREATE TABLE Student (
      Sno CHAR(9) PRIMARY KEY,
      ......
  );
  # 方式2, 定义为表级约束
  CREATE TABLE Student (
      Sno CHAR(9),
      ...,
      PRIMARY KEY(Sno)
  );

# 将SC表中的Sno, Cno属性组定义为主键
  CREATE TABLE SC (
      Sno CHAR(9) NOT NULL,
      Cno CHAR(9) NOT NULL,
      Grade SMALLINT,
      PRIMARY KEY(Sno, Cno) # 只能在表级定义主键
  );

实体完整性检查和违约处理

插入 或对主键进行 更新 操作时, 系统按照实体完整性规则自动进行检查.

检查内容包括:

• 检查主键值是否唯一, 若不唯一则拒绝操作
• 检查主键的各个属性是否为空, 只要有一个为空则拒绝操作

检查的方法:

• 全表扫描 : (一次扫描每一条记录,检查是否违约)
• 建立索引 : (针对主键建立B+树等数据结构,提高效率)

参照完整性

参照的完整性要求关系中不允许引用不存在的实体. (外键的值必须是必须再其他表内的主码里找得到)

参照完整性的定义

在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外键,
用REFERENCES短语指定这些外码参照哪些表的主键.

• 单属性构成主键
  • 定义为 列级 完整性约束条件
  • 定义为 表级 完整性约束条件
• 多个属性构成主键
  • 定义为 表级 完整性约束条件 (一个个指定)

外键定义时只

例子2-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Student表中的Sdept属性是外键,参照DEPT表的主键Deptno
  # 方式1, 定义在列级参照完整性条件中
  CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sdept CHAR(20) FOREIGN KEY REFERENCES DEPT(Deptno)
    ...
  );
  # 方式2, 定义在表级参照完整性条件中
  CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sdept CHAR(20),
    ...
    FOREIGN KEY(Sdept) REFERENCES DEPT(Deptno)
  );

# 关系SC中(Sno, Cno)是主键, Sno Cno分别参照Student表的主键和Course表的主键
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)   # 在表级定义参照完整性
);

参照完整性检查和违约处理

被参照表 和 参照表 进行 增删改 的操作时有可能破坏参照完整性, 必须进行检查.

可能 破坏参照完整性的情况已经违约处理:

违约处理的含义

1. 拒绝执行 (NO ACTION)
   • 不允许该操作, 一般设置为默认策略
2. 级联删除 (CASCADE)
   • 当删除或修改 被参照表 的一个元组造成了 参照表 不一致, 删除或修改 参照表 中造成不一致的元组
3. 设置为空值
   • 原因同上, 将 参照表 中造成不一致的元组对应属性设置空值.

显式指定违约处理方式

例子2-2

1
2
3
4
5
6

...
FOREIGN KEY(Sno) REFERENCES Student(Sno)
  ON DELETE CASCADE,  # 当删除时出错, 级联删除
  ON UPDATE CASCADE,  # 当更新时出错, 级联更新
 #ON DELETE NO ACTION # 当删除时出错, 拒绝执行
...

用户定义的完整性

针对某一具体应用的数据必须满足的语义要求 (比如性别只能是男或女)

当用户定义的完整性被破坏时, 将拒接操作.

属性上的约束条件

CREATE TABLE时定义属性上的约束条件 :

• 列值非空 (NOT NULL)
• 列值唯一 (UNIQUE)
• 检查列值是否满足一个条件表达式 (CHECK)

例子3-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#定义SC表时, 说明 Sno, Cno, Grade属性不允许取空值
CREATE TABLE SC (
  Sno CHAR(9) NOT NULL, # Sno和Cno是主键, 已经不能取空值了, 这里的NOT NULL可以省略
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT NOT NULL,
  PRIMARY KEY(Sno, Cno),
  ...
);

#建立专业表DEPT, 要求专业名Dname列取唯一值且不能为空, 专业编号为Deptno为主键
CREATE TABLE DEPT (
  Deptno NUMERIC(2),
  Dname CHAR(9) UNIQUE NOT NULL,  # 可见 这些关键字可以叠加
  PRIMARY KEY(Deptno)
);

#创建Student表的时候设置Ssex只允许取'男'或 '女'
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL,
  Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),  # CHECK短语 除了IN谓词也可以用其他谓词, 运算符等
  ...
);

属性上的约束条件检查和违约处理:

• 插入元组或修改属性值 时, 检查属性上的约束条件是否被满足
• 如果不满足则操作被 拒绝执行

元组上的约束条件

属性上的约束条件 : 只涉及单个属性
元组上的约束条件 : 约束条件涉及多个属性

在 CREATE TABLE 时, 用 CHECK 子句在 表级约束 上定义元组上的约束条件

例子3-2

1
2
3
4
5
6
7
8

#当学生性别是男时, 其名字不能以Ms.开头
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,
  Sname CHAR(8) NOT NULL,
  Ssex CHAR(2),
  ...
  CHECK (Sex = '女' OR Sname NOT LIKE 'Ms.%') #定义元组上的约束条件, 要么是女的 要么不以Ms.开头
);

元组上的约束条件检查和违约处理

• 插入元组或修改属性的值时, 检查元组上的约束条件是否被满足
• 如果不满足则拒绝执行

关系数据库的三类完整性约束总结

	实体完整性	参照完整性	用户定义的完整性
定义方法	CREATE TABLE	CREATE TABLE	CREATE TABLE
检查时机	执行插入/修改操作	参照表:插入/修改 被参照表:删除/修改	执行插入/修改操作
违约处理	拒绝执行	拒绝执行/级联操作/设置为空值	拒绝执行

完整性约束命名子句

完整性约束的命名, 用于将属性及其完整性约束条件绑定. 当需要删除这个约束条件时, 删除这个命名即可.

完整性约束命名的方式 CONSTRAINT

语法

CONSTRAINT <完整性约束条件名> <完整性约束条件>
完整性约束条件 包括 : NOT NULL , UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK

例子4-1

1
2
3
4
5
6
7
8

# 建立学生表Student ,要求学号在90000-99999之间, 姓名不能为空值, 年龄小于30, 性别只能是男或女
CREATE 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)
);

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

对于删除完整性限制 : 使用 ALTER TABLE ... DROP... 即可.
对于修改完整性限制 : 需要先删除, 再用 ALTER TABLE ... ADD...添加新的约束,

例子4-2

1
2
3
4
5
6
7
8
9

# 删除Student表中对性别的限制
ALTER TABLE Student DROP CONSTRAINT C4;

# 修改Student中的约束条件 : 学号改为900000-999999之间, 年龄改为小于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);
# 先删除旧的, 再添加新的

断言

SQL中 使用CREATE ASSERTION语句, 通过声明断言来指定更具一般性的约束.
通过断言, 可以定义一些复杂的完整性约束. 比如涉及多个表的操作 或 聚集操作.
断言创建后, 任何对断言中涉及的关系的操作都会触发对断言的检查, 任何使断言不为真的操作都拒绝执行.

定义断言 CREATE ASSERTION

语法

CREATE ASSERTION <断言名> <CHECK子句>
每个断言都有一个名字
CHECK子句的约束条件与WHERE的条件表达式类似.

例子5-1

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

# 限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM CHECK (
  60 >= (
    SELECT COUNT(*) FROM Course, SC
    WHERE SC.Cno = Course.Cno AND Course.Cname = '数据库'
  )
);
# 当向SC插入元组时检查, 若违约则拒绝执行

# 限制每个课程最多60个学生选修
CREATE ASSERTION ASSE_SC_CNUM CHECK (
  60 >= ALL (
    SELECT COUNT(*) FROM SC GROUP BY Cno
  )
);

# 限制每个学期每门课程最多60人选修
  # 首先在SC表中增加学期TERM属性
  ALTER TABLE SC ADD TERM DATE;
  # 再用断言定义
  CREATE ASSERTION ASSE_SC_CNUM2 CHECK (
    60 >= ALL (
      SELECT COUNT(*) FROM SC GROUP BY Cno, TERM
    )
  );

如果断言很复杂, 则系统检测维护断言的开销将很高, 使用断言的时候应该注意这一点.

删除断言 DROP ASSERTION

语法

DROP ASSERTION <断言名>;

触发器 (Trigger)

触发器是用户定义再关系表上的一类由事件驱动的特殊过程
任何用户对表的增删改的操作时就自动触发
触发器可以实施更为复杂的检查和操作, 具有更精细和强大的数据控制能力

定义触发器 CREATE TRIGER

语法

语义 : 当<表名>中发生了事件<触发事件>后, 检查<触发条件>. 若满足条件则执行<触发动作体>
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> [OF <属性名>] ON <表名>
REFERENCING NEW | OLD ROW AS <变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>] <触发动作体>
触发器名 : 可包含模式名, 也可以不包含(模式名要和表名在一个模式下). 同模式下, 触发器名必须是唯一的.
BEFORE|AFTER : 触发的时机, 指定再操作执行之后或之前执行触发器
触发事件 : INSERT , DELETE , UPDATE或几个事件的组合 : UPDATE OF<触发列, …>
表名 : 触发器只能定义在基本表上, 不能定义在视图上. 表上的数据变化时,激活对应触发事件.
REFERENCING : 指出表所引用的变量
FOR EACH : 定义触发器类型(行级 / 语句级)
WHEN : 触发器激活时, 只有触发条件为真动作体才执行, 否则动作体不执行
触发动作体 : 如果是行级, 则动作体内可以用NEW OLD 否则不行. 如果动作体执行失败, 事件就终止.

触发器又叫做: 事件-条件-动作 规则

例子6-1

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
28
29
30

# 当修改了SC表中Grade属性时, 若分数增加了10%则此次操作记录到下表:
# SC_U (Sno, Cno, Oldgrade, Newgrade) # (学号, 课程号, 旧分数, 新分数)
CREATE TRIGGER SC_T           # 定义触发器SC_T
AFTER UPDATE OF Grade ON SC   # 当SC.Grade被更新后激活, 触发条件检查
REFERENCING                   # 引用两个变量, 分别记录修改前后的元组值
  OLD ROW AS OldTuple,
  NEW ROW AS NewTuple
FOR EACH ROW                  # 行级触发器(每执行一次更新就触发一次)
WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade)     # 新分数>=旧分数*1.1时
  INSERT INTO SC_U(Sno, Cno, Oldgrade, Newgrade)  # 若满足条件,就添加数据到SC_U表
  VALUES(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade)

# 将每次对Student的插入操作所增加的学生数量记录到标StudentInsertLog中
CREATE TRIGGER Student_Count  # 定义触发器Student_Count
AFTER INSERT ON Student       # 当Student被插入后激活, 触发条件检查
REFERENCING                   # 引用一个变量, 记录修改后的表(里面的元组是被修改过的元组)
  NEW TABLE AS DELTA
FOR EACH STATEMENT            # 语句级触发器 (每次INSERT语句执行完执行一次)
  INSERT INTO StudentInsertLog(Numbers) # 没有WHEN短语, 不做检查每次都插入
  SELECT COUNT(*) FORM DELTA

# 定义一个BEFORE行级触发器, 为教师表Teacher定义完整性规则"教授工资不低于4000,若低于则改为4000"
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher  # 在执行INSERT或UPDATE之前触发
FOR EACH ROW                        # 行级触发器, 没有触发条件
BEGIN
  IF (new.job = '教授') AND (new.Sal < 4000)
  THEN new.Sal := 4000;
  END IF;
END;

激活触发器

当触发器被创建后, 当满足条件时系统会自动执行, 不用显式指定

当一个表中有多个触发器, 它们的执行顺序是 :

1. BEFORE触发器 (多个BEFORE触发器按创建时间先后/字母排序顺序执行, 不同的数据库不一样)
2. 激活触发器的SQL语句
3. AFTER触发器 (多个AFTER触发器的顺序与BEFORE一样)

删除触发器 DROP TRIGGER

语法

DROP TRIGGER <触发器名> ON <表名>;

数据库安全性

问题的提出

• 数据库的一大特点是数据可共享
• 数据共享必然带来数据库安全性问题
• 数据库系统中的数据共享不能是无条件的共享

数据库安全性的目的

• 数据库安全性是指保护数据库以防不合法使用所造成的数据泄露, 更改, 破坏.
• 系统安全保护措施是否有效是数据库系统主要的性能指标之一.

数据库安全性概述

数据库的不安全因素

1. 非授权用户对数据库的恶意存取和破坏 (黑客, 犯罪分子)
   • 黑客假冒合法用户偷取, 修改, 破坏用户数据.
   • 安全措施包括 : 用户身份鉴别, 存取控制, 视图 等.
2. 数据库中重要或敏感的数据被泄露
   • 黑客盗窃数据库中的重要数据, 导致机密信息被暴露.
   • 安全措施包括 : 强制存取控制, 数据加密存储, 加密传输, 审计日志分析
3. 安全环境的脆弱性
   • 数据库的安全性与计算机系统的安全性紧密联系
   • 建立一套可信的计算机系统的概念和标准

安全标准简介

发展历程 : TCSEC -> CC -> ISO15408(CC V2.1)

TCSEC标准

1991年4月由美国NSCS()颁布TDI.
NSCS : 国家计算机安全中心
TDI : <<可信计算机系统评估标准关于可信数据库系统的解释>>

• TDI又称紫皮书. 它将TCSEC扩展到数据库管理系统
• TDI中定义了数据库管理系统的设计与实现中需满足和用以进行安全性级别评估的标准

TCSEC/TDI的安全级别划分 :

安全级别由D到A1逐级递增.

• D : 不符合任何更高标准的
  • DOS系统
• C1 : 初级的自主安全保护, 对用户和数据的分离,进行自主存取控制(DAC)
  • 大多数现有商业系统
• C2 : 安全产品的最低档次, 提供受控的存取保护, 进一步细化DAC.(这一级别产品通常不突出”安全”特色)
  • Windows 2000 , Oracle 11g
• B1 : 标记安全保护, 对标记的主体和客体实施强制存取控制(MAC), 审计等安全机制(安全可信的产品)
  • HP-UX BLS RELEASE 9.09+ , Trusted Oracle , Secure SQL Server version 11.0.6
• B2 : 结构化保护, 对系统内所有主客体试试DAC与MAC
• B3 : 安全域, TCB必须满足访问监控器的要求, 审计跟踪能力更强, 提供系统恢复过程
• A1 : 验证设计, B3级保护的同时给出系统形式化的设计说明和验证实现

CC标准

提出国际公认的表述信息安全性的结构.

把信息产品的安全要求分为 :

• 安全功能要求 : 规范系统产品的安全行为
• 安全保证要求 : 正确有效的实施功能

CC评估保证级(SAL)划分:

保证程度由EAL1到EAL7逐级提升

数据库安全性控制

在计算机系统中, 安全措施逐层设置

• 系统根据用户标识鉴定用户身份, 合法用户才准许进入计算机系统
• 数据库管理系统进行存取控制, 只允许用户执行合法操作
• 操作系统有自己的保护措施(涉及操作系统)
• 数据以密码形式存储到数据库中

数据库安全性控制的常用方法

• 用户身份鉴别
• 存取控制
• 视图
• 审计
• 数据加密

数据库安全保护的控制模型:

• 身份鉴别 : DBMS对提出SQL访问请求的数据库用户进行身份鉴别, 防止不可信用户使用系统.
• SQL层 : 在SQL处理层进行自主存取控制和强制存取控制, 进一步可以进行推理控制.
• 审计 / 入侵检测 : 对用户访问行为和系统关键操作进行审计, 对异常用户行为进行简单入侵检测.

用户身份鉴别 (Identification & Authentication)

系统提供的最外层安全保护措施, 也就是登录的安全保护
用户标识 : 由用户名和用户标识号组成 (用户标识号在系统整个生命周期内唯一)

用户鉴别的方法

1. 静态口令鉴别 (用户自己设定静态口令, 这些口令静态不变. 比如静态密码)
2. 动态口令鉴别 (每次鉴别均使用动态产生的新口令登录, 一次一密. 比如短信登陆)
3. 智能卡鉴别 (智能卡是不可复制的硬件, 内置集成电路的芯片, 具有硬件加密功能. 比如门禁卡)
4. 生物特征鉴别 (通过生物特征进行认证, 指纹, 虹膜, 掌纹等. 比如指纹解锁)

存取控制

用户登录成功不代表可以访问所有数据, 每个用户只能访问其权限内可访问的数据.

存取控制机制组成

• 权限定义机制 : 定义用户权限, 并将用户权限登记到数据字典中
  • 用户对某一数据对象的操作权力称为权限
  • DBMS提供适当的语言来定义用户权限, 存放在数据字典中, 称为安全规则或授权规则
• 合法权限检查
  • 用户发出存取数据库的操作请求.
  • DBMS查找数据字典, 进行合法权限检查

自主存取控制方法

自主存取控制定义 (Discretionary Access Control 检查 DAC)

• 用户对不同数据对象由不同的存取权限
• 不同的用户对同一对象也有不同的权限
• 用户还可将其拥有的存取权限转授给其他用户

关系数据库系统中存取控制对象的及其权限:

对于数据对象的权限管理通过SQL的 GRANT 语句和 REVOKE 语句实现
对于模式对象的权限管理通过SQL的 CREATE USER 语句实现

授权 : 授予与回收

通过SQL的 GRANT 语句和 REVOKE 语句实现权限的授予和回收

权限授予 GRANT

GRANT语句格式

语义 : 将对指定操作对象 指定操作权限授予指定的用户
GRANT <权限1>[(属性名1)] [, <权限2>[(属性名2)]]...
ON <对象类型1> <对象名1> [, <对象类型2> <对象名2>]...
TO <用户1> [, <用户2>]...
[WITH GRANT OPTION];
GRANT : 指定要授予的操作权限
ON子句 : 指定该权限所属的对象 (指定操作对象)
TO子句 : 指定要授予权限的用户
WITH GRANT OPTION子句 : 指定该子句则被授权的用户被允许转授该权限, 否则不可.
注 : SQL中不允许循环授权. 即A给B, B给C, C给A.

有权发出GRANT者:

• 数据库管理员 (Root用户)
• 数据库对象的创建者 (属主Owner)
• 拥有该权限的用户

可指定的接受权限的用户:

• 一个或多个具体用户
• PUBLIC (全体用户)

例子2-1

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

# 将查询Student表的权限授予用户U1
GRANT SELECT
ON TABLE Student
TO U1;

# 将Student表 和 Course表的全部权限授予用户U2 和U3
GRANT ALL PRIVILIGES    # ALL PRIVILIGES 谓词表示相关对象的所有权限
ON TABLE Student, Course
TO U2, U3;

# 把对SC表的查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;  # PUBLIC 谓词表示所有用户

# 把查询Student表和修改学生学号的权限授予用户U4
GRANT UPTDATE(Sno), SELECT  # 对属性列的授权必须显式指出列名
ON TABLE Student
TO U4;

# 把SC表的INSERT权限授予用户U5, 并允许其转授权限
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;  # WITH GRANT OPTION 子句表明目标用户可以转授权限
# U5得到权限后, 还可以授权给U6同时允许转授, U6可以授权给U7同时不允许转授, U7就不能授权给别人了

执行完上述例子后, 数据库中用户权限定义表 如下图:

权限回收 REVOKE

REVOKE语句格式

语义 : 将指定对象的指定操作权限从指定用户处回收
REVOKE <权限>[(属性名)] [, <权限>[(属性名)]]...
ON <对象类型> <对象名> [, <对象类型> <对象名>]...
FROM <用户> [, <用户>]...[CASCADE | RESTRICT];
REVOKE : 指定要回收的权限
ON : 指定要回收权限的对象
FROM : 指定要回收权限的用户
CASCADE | RESTRICT : CASCADE表示级联回收, RESTRICT表示受限回收 (不同系统的缺省值不一样)
(GRANT里提到不允许循环授权, 原因就是回收时将产生歧义)

例子2-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 把用户U4修改学生学号的权限回收
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

# 回收所有用户对SC表的查询权限
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

# 回收用户U5及其转授权限者对于SC表的INSERT权限回收
REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;    # CASCADE 表示级联回收
#这里U5 U6 U7的权限都被回收了, 但如果U6 U7从其他用户那儿获取了该权限, 则它们仍然具有该权限

执行完上述例子后, 数据库中用户权限定义表 如下图:

创建数据库模式的权限 CREATE USER (创建用户)

CREATE USER的语句格式

CREATE USER <用户名>
[WITH] [DBA | RESOURCE | CONNECT]
CREATE USER : 指定要创建用户的用户名
WITH子句 : 指定该用户的初始权限
DBA RESOURCE CONNECT三种权限的权限范围 :

数据库角色

被命名的一组与数据库操作相关的权限

• 角色是权限的集合
• 角色可以为一组具有相同权限的用户创建一个角色
• 简化授权的过程

使用角色管理数据库权限的过程:

方法类似授予权限, 多了授予角色和给角色授权的步骤

1. 角色创建
   • CREATE ROLE <角色名> (新角色的权限是空的)
2. 为角色授权
   • GRANT ... ON ... TO <角色名>[, <角色名>] (与给用户授权一样, 只是用户名换成角色名)
3. 将一个角色授予其他的角色或用户
   • GRANT <角色名>[,<角色名>]... TO <角色名>/<用户名>... [WITH ADMIN OPTION]
   • 指定WITH ADMIN OPTION后,授权的用户有权将权限和角色转授出去

有权授予角色权限的人 : 该角色的创建者, 拥有这个角色的ADMIN OPTION者

角色权限的收回

方法类似回收权限, 只不过目标从用户换成了角色
REVOKE <权限>[,<权限>]
ON <对象类型> <对象名>
FROM <角色名>[,<角色名>]
有权回收角色权限的人 : 该角色的创建者, 拥有这个角色的ADMIN OPTION者

例子2-3

1
2
3
4
5
6
7
8
9
10

# 创建角色 R1
CREATE ROLE R1; # 此时R1没有任何权限
# 授予角色R1 Student表的SELECT UPDATE INSERT权限
GRANT SELECT, UPDATE, INSERT ON TABLE Student TO R1; # R1获得了Student表的SELECT UPDATE INSERT权限
# 将角色R1授予用户U1, U2, U3
GRANT R1 TO U1, U2, U3; # U1, U2, U3获得了R1角色的权限
# 将U1对于Student表的SELECT UPDATE INSERT权限回收
REVOKE R1 FROM U1; # 一次性回收了U1有关R1的所有权限
# 收回角色R1对于Student表的INSERT权限
REVOKE INSERT ON TABLE Student FROM R1; # R1失去了Student表的INSERT权限

强制存取控制 (MAC)

通过GRANT和REVOKE可实现自主存取控制, 但自主存取控制有缺陷:

• 可能存在数据的”无意泄露” (人的原因)

强制存取控制的优势:

• 保证更高的安全性
• 用户不能直接感知或进行控制
• 适用于对数据有严格而固定密级分类的部门 (军政部门)

主体与客体

在强制存取控制中, 数据库管理系统所管理的全部实体被分为主体和客体两类

• 主体 : 系统中活动的实体
  • 数据库管理系统管理的实际用户
  • 代表用户的各进程
• 客体 : 系统中的被动实体, 受主体操控
  • 文件, 基本表, 索引, 视图

敏感度标记

对于主体和客体, DBMS为每个实例指派一个敏感度标记(Label)

• 主体的敏感度标记被称为 : 许可证级别
• 客体的敏感度标记被称为 : 密级

敏感度标记分为若干级别 (TS >= S >= C >= P)

• 绝密 (Top Secret, TS)
• 机密 (Secret, S)
• 可信 (Confidential, C)
• 公开 (Public, P)

强制存取控制规则

1. 仅当主体的许可证级别大于或等于客体的密级时, 主体才能读相应的客体
2. 仅当主体的许可证级别小于或等于客体的密级时, 主体才能写相应的客体
   (之所以不允许主体写敏感度比自己低的客体, 是为了防止其恶意将数据降级造成数据暴露)

强制存取控制是对本身进行密级标记.
无论数据如何复制, 标记与数据是一个不可分的整体.
只有符合密级标记要求的用户才可以操作数据.

DAC与MAC共同构成了DBMS的安全机制

视图机制

视图把要保密的数据对无权操作该数据的用户隐藏, 对数据提供了一定程度的安全保护.

使用GRANT语句可以为用户授权某些属性(列)的权限, 但无法使用其为用户授权某些元组(行)的权限.
借助视图机制则可以实现这一功能.

例子3-1

1
2
3
4
5
6

# 授权王平老师能查询计算机系学生的情况, 授权系主任张明能对计算机系学生的信息进行所有操作
  # 1.先建立计算机系学生的视图CS_Student
  CREATE VIEW CS_Student AS SELECT * FROM Student WHERE Sdept = 'CS';
  # 2.在视图上进一步定义存取权限
  GRANT SELECT ON CS_Student TO 王平;
  GRANT ALL PRIVILIGES ON CS_Student TO 张明;

总结 : 通过视图限制对元组的操作权限, 使用GRANT语句限制对属性的操作权限

审计(Audit)

上述提到的安全性措施包括:

• 用户身份鉴别
• 自主存取控制
• 强制存取控制
• 视图

这些都属于 预防性措施 , 防止出现安全问题.
审计 属于 监控措施 , 是当安全问题出现后的补救措施.
两种措施相互配合, 使得数据库系统安全性更有保障.

审计的工作 :

• 启用一个专用的审计日志 (Audit Log) , 将用户对数据库的所有操作记录在上面.
• 审计员利用审计日志 , 监控数据库中的各种行为 , 发现非法存取与潜在威胁
• C2级别以上的DBMS必须具备审计功能

可以被审计的事件

• 服务器事件
  • 审计数据库服务器发生的事件
• 系统权限
  • 对系统拥有的结构或模式对象进行操作的审计
  • 要求该操作的权限是通过系统权限获得的
• 语句事件
  • 对SQL语句, 如DDL, DML, DQL, DCL语句的审计
• 模式对象事件
  • 对特定模式对象上进行的SELECT或DML操作的审计

审计日志的管理

• 基本功能 : 提供多种审计查阅方式
• 多套审计功能 : 一般在初始化设定
• 提供审计分析和报表功能
• 审计日志管理功能
  • 防止审计员误删审计数据 , 审计日志必须先转储再删除
  • 对转储的审计记录文件提供完整性和保密性保护
  • 只允许审计员查阅和转储审计记录, 不允许任何用户新增和修改审计记录

审计功能是可选性

• 审计很费时间和空间
• DBA可以根据应用对安全性的要求, 灵活地打开或关闭审计功能
• 通常审计功能只用于安全性较高的部门

审计功能设置

• AUDIT语句 : 设置审计功能
• NOAUDIT语句 : 取消审计功能

设置审计功能

审计分为 用户级审计 与 系统级审计.

用户级审计

• 任何用户可设置的审计
• 主要是针对用户子句创建的数据库表和视图进行审计

系统级审计

• 只能由数据库管理员设置
• 监测成功或失败的登录要求, 监测授权和回收操作以及其他数据库权限下的操作

例子4-1

1
2
3
4

# 对修改SC表 结构 或修改SC表 数据 的操作进行审计
AUDIT ALTER, UPDATE ON SC;
# 取消对SC表的一切审计
NOAUDIT ALTER, UPDATE ON SC;    #可见 只能一个个写出来取消

数据加密

防止数据库中数据再 存储 和 传输 中失效的有效手段.

加密方式:

• 存储加密
• 传输加密

存储加密

• 透明存储加密
  • 内核级加密保护方式, 对用户完全透明
  • 将数据卸载磁盘时对数据加密, 授权用户读取数据时再解密
  • 数据库的应用程序不需要修改, 主要在创建表语句中说明需要加密的字段即可
  • 内核级加密方法: 性能好, 安全完备性高
• 非透明存储加密
  • 通过多个加密函数实现

传输加密

• 链路加密
  • 对报头和报文都进行加密
• 端到端加密
  • 只加密报文, 不加密报头

数据库管理系统可信传输示意图:

其他安全性保护

推理控制

• 处理强制存取控制未解决的问题, 避免用户利用能够访问的数据推知更高密级的数据
• 常用方法 : 基于函数依赖的推理控制 , 基于敏感关联的推理控制

隐蔽信道

• 处理强制存取控制未解决的问题
• 高安全等级用户按约定主动向低安全等级用户传输信息, 从而导致敏感信息泄露

小结

实现数据库系统安全性的技术和方法:

• 用户身份鉴别
• 存取控制技术 : 自主存取控制(DAC) 与 强制存取控制(MAC)
• 视图技术
• 审计技术
• 数据加密 : 加密存储 与 加密传输

用户权限管理

• 使用 GRANT 和 REVOKE 语句实现自主存取控制
• 使用 CREATE ROLE 语句创建角色, 使用 GRANT 语句给角色授权
• 掌握视图机制在数据库中安全保护中的作用 (限制元组的访问权限)

难点

• MAC中主体能否存取客体的规则, 以及规定的原因.

视图 (VIEW)

视图的特点 :

• 虚表, 是从一个或几个基本表 (或视图) 导出的表
• 只存放视图的定义, 不存放视图对应的数据
• 基表中的数据发生变化, 从视图中查询出的数据也随之改变

视图相当于建一个方便用户查看的表, 这个表在用户要看的时候临时生成, 平时只记录这个表的构造方法.

定义视图

以建立的方式分类,视图有以下几种 :

1. 基于单个表的视图
2. 基于多个表的视图
3. 基于视图的视图
4. 带表达式的视图
5. 分组视图

语句格式

CREATE VIEW <视图名> [(<列名> [,<列名>])]
AS <子查询>
[WITH CHECK OPTION];

子查询 :
子查询可以是任意的SELECT语句, 是否可以含有 ORDER BY 子句和 DISTINCT 短语, 决定具体系统的实现.

WITH CHECK OPTION :
对视图进行 UPDATE, INSERT, DELETE操作时要保证更新, 插入, 删除的行满足视图定义中的谓词条件.
(满足子查询中的条件表达式)

组成视图的属性列名 : 全部省略或全部指定

• 全部省略:
  • 由子查询中的SELECT目标列中的字段组成
• 全部指定:
  • 某个目标列是聚集函数或列表达式
  • 多表链接时选出了几个同名列作为视图的字段
  • 需要在视图中为某个列启用新定更适合的名字

执行CREATE VIEW语句时, 只是把视图定义存入数据字典. 并不执行其中的SELECT语句.
在对视图查询时候, 按视图的定义从基本表中将数据查出.

基于单个表的视图

例子1-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#建立信息系学生的视图
CREATE VIEW IS_Student #行列子集视图
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS';

#建立信息系学生的视图, 同时呀求进行修改和插入操作时要保证该视图只有信息系学生
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM Student
WHERE Sdept = 'IS'
WITH CHECK OPTION;  #该子句确保只能有信息系学生
#加上WITH CHECK OPTION子句后, 对视图进行增删改操作时, 自动加上Sdept = 'IS'的条件

#将Student中所有女生记录定义为一个视图
CREATE VIEW F_Student (F_Sno, name, sex, age, dept)
AS
SELECT *    # 不指定属性列
FROM Student
WHERE Ssex = '女';
#缺点 : 修改基表Student后, 基表与视图的映像关系被破坏, 导致视图不能正常工作

行列子集视图 :
若一个视图是从单基本表导出的, 并且只是去掉了部分行列, 但保留主键. 我们称这类视图为行列子集视图

基于多个表的视图

例子1-2

1
2
3
4
5
6
7
8
9

#建立信息系选修了1号课程的学生的视图 (包括学号, 姓名, 成绩)
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-3

1
2
3
4
5
6

#建立信息系选修了1号课程且成绩在90以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno, Sname, Grade
FROM IS_S1
WHERE Grade >= 90;

带表达式的视图

例子1-4

1
2
3
4
5

#定义一个反映学生出生年份的视图
CREATE VIEW BT_S (Sno, Sname, Sbirth)
AS
SELECT Sno, Sname, 2020-Sage
FROM Student;

分组视图

例子1-5

1
2
3
4
5
6

#将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G (Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;

删除视图

从数据字典中删除指定的视图定义

语句格式:

DROP VIEW <视图名> [CASCADE];
CASCADE : 使用该关键字可以级联删除这个视图导出的其他视图, 不使用时则只能删除没有导出视图的视图.
删除基表时 : 由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除.

例子2-1

1
2
3
4

#删除视图BT_S和IS_S1 (前者由后者导出)
DROP VIEW IS_S1;         #拒绝执行 : 有其他视图由其导出
DROP VIEW BT_S;          #成功执行 : 无其他视图由其导出
DROP VIEW IS_S1 CASCADE; #成功执行 : 级联删除BT_S和IS_S1

查询视图

从用户的角度来看 : 查询视图与查询基本表的方式相同

从实现的角度来看: 实现视图查询的方法如下 :

• 视图消解法 (View Resoltion)
  • 进行有效性检查
  • 转换成等价的对基本表的查询
  • 执行修正后的查询

例子3-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#在信息系学生的视图中找出年龄小于20岁的学生
  #查询语句如下   (操作者视角)
  SELECT Sno, Sage
  FROM IS_Student
  WHERE Sage < 20;
  #视图消解转换后的查询语句如下:    (系统视角)
  SELECT Sno, Sage
  FROM Student
  WHERE Sdept = 'IS'
    AND Sage < 20;

#查询选修了1号课程的信息系学生
  SELECT IS_Student.Sno, Sname
  FROM IS_Student, SC
  WHERE IS_Student.Sno = SC.Sno
    AND SC.Cno = '1';

视图消解法的局限 : 有时视图消解法不能生成正确的查询

例子3-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#在S_G视图中查询平均成绩在90以上的学生学号和平均成绩
  SELECT *            # S_G的定义如下:
  FROM S_G            # CREATE VIEW S_G (Sno, Gavg) AS
  WHERE Gavg >= 90;   # SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;
  #视图消解法的两种情况
    #错误方式 : WHERE的条件这里不能用聚集函数
    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;
  #还有一个正确方法 : 直接将定义嵌入查询语句 ---- 先把这个表构造出来,再直接在表中查询
    SELECT * FROM (
      SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno # S_G的定义
    ) AS S_G(Sno, Gavg)
    WHERE Gavg >= 90;

更新视图

从用户的角度来看 : 更新视图与查询基本表的方式相同

例子4-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#将信息系学生视图IS_Student中学号201215122的学生名字改为刘晨
  UPDATE IS_Student
  SET Sname = '刘晨'
  WHERE Sno = '201215122';
  #转换后
  UPDATE Student
  SET Sname = '刘晨'
  WHERE Sno = '201215122' AND Sdept = 'IS';

#向信息系学生视图IS_Student中插入一个新学生记录, 其中学号为201215129, 姓名赵新, 年龄20岁
  INSERT INTO IS_Student
  VALUES ('201215129', '赵新', 20);
  #转换后
  INSERT INTO Student(Sno, Sname, Sage, Sdept)
  VALUES ('201215129', '赵新', 20, 'IS');

# 删除信息系学生视图IS_Student中学号为201215129的记录
  DELETE FROM IS_Student
  WHERE Sno = '201215129';
  #转换后
  DELETE FROM Student
  WHERE Sno = '201215129' AND Sdept = 'IS';

更新视图的限制 :
一些视图是不可更新的, 因为对这些视图的更新不能唯一地有意义地转换为对相应的基本表的更新

例子4-2

1
2
3
4
5
6
7
8
9
10

#例1-5中的视图及为一个不可更新的视图
  #例1-5 : 将学生的学号及平均成绩定义为一个视图
  CREATE VIEW S_G (Sno, Gavg) AS
  SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno;
  #对其的更新语句为:
  UPDATE S_G SET Gavg = 90 WHERE Sno = '201215121';
  #这个对视图的更新无法转换成对基本表SC的更新
  #(平均成绩是由SC中成绩计算而成, 逻辑上应该是平均成绩随着成绩更新而更新, 而不是相反.)

视图更新的规则 :

• 允许对行列子集视图进行更新 (依赖于单表构造的视图)
• 对其他类型视图的更新不同系统有不同限制
  • MySQL对视图更新的限制
• 基于一个不可更新的视图之上 所建立的视图 也是不可更新的

视图的作用

• 简化了用户的操作***
• 使用户能够以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
• 适当的利用视图可以更清晰的表达查询

简化用户操作

当视图中的数据不是直接来自一张基本表, 定义视图能够简化用户的操作.

• 基于多张表连接成的视图
• 基于复杂嵌套查询的视图
• 含导出属性的视图

使用户能够以多种角度看待同一数据

• 视图机制能使不同用户以不同方式看待统一数据, 适应数据库共享需要

视图对重构数据库提供了一定程度的逻辑独立性

• 数据库重构
• 视图只能在一定程度上提供数据的逻辑独立性
  • 由于视图的更新是有条件的, 因此应用程序中修改数据的语句可能会因为基本表结构的改变而改变

例子5-1

1
2
3
4
5
6
7
8
9

# 学生关系Student(Sno, Sname, Ssex, Sage, Sdept)"垂直地"分成两个基本表:
# SX(Sno, Sname, Sage) 与SY(Sno, Ssex, Sdept)
  # 通过创建一个Student视图
  CREATE VIEW Student(Sno, Sname, Ssex, Sage, Sdept)
  AS
    SELECT SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept
    FROM SX, SY
    WHERE SX.Sno = SY.Sno
  # 使得用户的外模式不变, 用户的应用程序通过视图仍然能够查找数据

视图能够对机密数据提供安全保护

• 对不同用户定义不同视图, 使得么个用户只能看到他有权看到的数据

适当的利用视图可以更清晰的表达查询

• 经常要查询同一个语句时, 可以针对该语句先建立一个视图, 使得查询语句更清晰

例子5-2

1
2
3
4
5
6
7
8
9

# 对每个同学找出他获得最高成绩的课程号 (经常要查询该语句)
  # 先定义一个视图, 得出每个学生的最高成就
  CREATE VIEW VMGrade AS
  SELECT Sno, MAX(Grade) Mgrade FROM Student
  GROUP BY Sno;
  # 再查询
  SELECT SC.Sno, Cno FROM SC, VMGrade
  WHERE SC.Sno = VMGrade.Sno
    AND SC.Grade = VMGrade.Mgrade;

数据更新

数据更新是针对已经建好的表进行的操作
数据更新的三种方式 :

1. 插入数据
2. 修改数据
3. 删除数据

插入数据

插入数据有两种方式:

• 插入元组
• 插入子查询结果 (可以一次插入多个元组)

插入元组

语句格式

INSERT
INTO <表名> [(<属性列1> [, <属性列2>...])]
VALUES (<常量1> [, <常量2>]...);

功能: 将新元组插入指定表中

INTO子句:

• 指定要插入数据的表名及属性列
• 属性列的顺序可与表定义中的顺序不一致
• 没有指定属性列: 表示要插入的是一条完整的元组, 且属性列属性与表定义中顺序一致
• 指定部分属性列: 插入的元组在其余的属性列上取值NULL

例子1-1

1
2
3
4
5
6

#将一个新学生元组(学号:201215123; 姓名:陈冬; 性别:男; 院系:IS; 年龄:18)插入到Student表中.
INSERT
INTO Student (Sno, Sname, Ssex, Sdept, Sage)
VALUES ('201215128', '陈冬'. '男', 'IS'. 18);
#插入一条选课记录('200215128', '1')
INSERT INTO SC VALUES ('200215128', '1', NULL); #不指定属性列时要将所有属性的值写出来, 这里Grade是NULL

插入子查询结果

语句格式

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

子查询:

• SELECT子句目标列必须与INTO子句匹配
  • 值的个数
  • 值的类型

例子1-2

1
2
3
4
5
6
7
8
9
10
11

#对每一个系, 求学生平均年龄, 并把结果存入数据库
  # 第一步: 建表
  CREATE TABLE Dept_age (
    Sdept CHAR(15),     #系名
    Avg_age SMALLLINT   #平均年龄
  );
  # 第二步: 插入数据
  INSERT INTO Dept_age (Sdept, Avg_age)
    SELECT Sdept, AVG(age)  #子查询语句
    FROM Student
    GROUP BY Sdept;

检查完整性

在执行插入语句时会检查新元组是否破坏表上已经定义的完整性规则:

• 实体完整性
• 参照完整性
• 用户定义的完整性
  • NOT NULL约束
  • UNIQUE约束
  • 值域约束

修改数据

修改数据的三种方式:

1. 修改某一个元组的值
2. 修改多个元组的值
3. 带子查询的修改语句

语句格式

UPDATE <表名>
SET <列名> = <表达式> [, <列名> = <表达式>]...
[WHERE <条件>];

功能:

• SET 子句决定要怎么修改 : 给出<表达式>的值用于取代相应的属性列.
• WHERE 子句决定要修改哪些元组 : 给出条件,只修改满足条件的元组.
  如果不写WHERE子句, 则修改所有元组

修改某一个元组的值

例子2-1

1
2
3
4

#将学生201215121的年龄改为22岁
UPDATE Student
SET Sage = 22
WHERE Sno = '201215121';

修改多个元组的值

例子2-2

1
2
3

#将所有学生的年龄加1岁
UPDATE Student
SET Sage = Sage + 1;

带子查询的修改语句

例子2-3

1
2
3
4
5
6
7
8

# 将计算机系全体学生成绩置零
UPDATE SC
SET Grade = 0
WHERE Sno IN (
  SELECT Sno
  FROM Student
  WHERE Sdapt = 'CS'
);

检查完整性规则

在执行修改语句时会检查操作是否破坏表上已经定义的完整性规则:

• 实体完整性
• 主键不允许修改
• 用户定义的完整性
  • NOT NULL约束
  • UNIQUE约束
  • 值域约束

删除数据

删除数据的三种方式 :

1. 删除某一个元组的值
2. 删除多个元组的值
3. 带子查询的删除语句

语句格式

DELETE FROM <表名>
[WHERE <条件>];

功能: : 删除指定表用满足WHERE子句条件的元组
WHERE子句 :

• 指定要删除的元组
• 无该子句将会删除表中的全部元组

删除某一个元组的值

例子3-1

1
2
3

#删除学号为201215128的学生记录
DELETE FROM Student
WHERE Sno = 201215128;

删除多个元组的值

例子3-2

1
2

#删除所有学生的选课记录
DELETE FROM SC;

带子查询的删除语句

例子3-3

1
2
3
4
5
6
7

#删除计算机系所有学生的选课记录
DELETE FROM SC
WHERE Sno IN (
  SELECT Sno
  FROM Student
  WHERE Sdept = 'CS'
);

数据查询-集合查询

集合操作用于查询结果的再运算.
参加集合操作的各查询结果的列数必须相同; 对应项的数据类型也必须相同.

集合操作的种类

• 并操作 UNION
• 交操作 INTERSECT
• 差操作 EXCEPT

并操作 UNION / UNION ALL

• UNION: 将多个查询结果合并起来, 去掉重复的元组
• UNION ALL: 将多个查询结果合并起来, 保留重复的元组

例子1

1
2
3
4
5
6
7
8
9

#查询计算机系的学生 和 年龄不大于19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
UNION
SELECT * FROM Student WHERE Sage <= 19;
#查询选修了课程1 或 选修了课程2的学生
SELECT * FROM SC WHERE Cno = '1'
UNION ALL
SELECT * FROM SC WHERE Cno = '2';
#可见, 善用UNION / UNION ALL可以让原本一个较为复杂的语句拆分成较为简单的几个语句

交操作 INTERSECT

• INTERSECT: 将多个查询结果相交, 保留在所有结果集中都存在的元组

例子2

1
2
3
4
5
6
7
8
9

#查询计算机系的年龄不大于19的学生
SELECT * FROM Student WHERE Sdept = 'CS'
INTERSECT
SELECT * FROM Student WHERE Sage <= 19;
#查询同时选修了课程1和课程2的学生
SELECT * FROM SC WHERE Cno = '1'
INTERSECT
SELECT * FROM SC WHERE Cno = '2';
#第二个例子可见, 原本需要嵌套查询的功能,用INTERSECT可以不用嵌套实现

差操作 EXCEPT

• EXCEPT: 将左结果集中 在 右结果集中出现的元组去掉

例子3:

1
2
3
4

#查询计算机系学生中 年龄大于 19岁的学生
SELECT * FROM Student WHERE Sdept = 'CS'
EXCEPT
SELECT * FROM Student WHERE Sage < 19;

查询语句小结

语句格式

SELECT [ALL | DEISTINCT] <目标列表达式>[, <目标列表达式>]...
FROM <表名或视图名>[, <表名或视图名>]...|(SELECT 语句) [AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
SELECT: 要查询的内容 (选定列) (ALL不去重(缺省) / DEISTINCT去重) ;
FROM: 从哪个表查询 (选定表) ;
WHERE: 查询的约束条件 (选定行) (条件表达式里面不能使用聚集函数) ;
——前三者为一组,用于查询出结果集——
GROUP BY: 结果集的分组方式 (按照某列值分组,相同值为一组统计数量) ;
HAVING: 只输出满足条件的结果 ;
——中间二者为一组,用于对结果集的输出限制,通常伴随使用聚集函数——
ORDER BY: 结果集的排序方式 (ASC升序(缺省) / DESC降序) .
——最后一条为一组,用于对结果排序——

目标列表达式格式

1. *
2. <表名>.*
3. COUNT ( [DISTINCT | ALL]* )
4. [<表名>.]<属性列名表达式>[,[<表名>.]<属性列名表达式>]...
   其中<属性列名表达式>可以是由属性列, 聚集函数 和 常量的算术运算(+, -, *, /)组成的运算公式

聚集函数的一般格式

• COUNT ( [DISTINCT | ALL] <列名> )
• SUM ( [DISTINCT | ALL] <列名> )
• AVG ( [DISTINCT | ALL] <列名> )
• MAX ( [DISTINCT | ALL] <列名> )
• MIN ( [DISTINCT | ALL] <列名> )

WHERE子句的条件表达式可选格式

1. <属性列名> "关系运算符" <属性列名> / <常量> / [ ANY | ALL ] ( SELECT语句 )
2. <属性列名> [NOT] BETWEEN ... AND ... (...即<属性列名> / <常量> / ( SELECT语句 ))
3. <属性列名> [NOT] IN (<值1>, <值2>...) / (SELECT语句)
4. <属性列名> [NOT] LIKE <匹配串>
5. <属性列名> IS [NOT] NULL
6. [NOT] EXISTS (SELECT语句)
7. <条件表达式> AND/OR <条件表达式>...

空值处理

即NULL

谓词IS NULL / IS NOT NULL用于判断空值

判断空值 IS NULL / IS NOT NULL

1
2

#找出漏填了性别或者年龄信息的记录
SELECT * FROM Student WHERE Ssex IS NULL OR Sage IS NULL;

属性定义(或者域定义)中

• 由NOT NULL约束条件的不能取空值
• 加了UNIQUE限制的属性不能取空值
• 键不能取空值

空值的算术运算, 比较运算, 逻辑运算

• NULL与另一个值的 算术运算 结果为 NULL
• NULL与另一个值的 比较运算 结果为 UNKNOWN
• 有UNKNOWN后, 传统二值(TRUE, FALSE)的逻辑就扩展为三值逻辑

逻辑运算符真值表

例子1

1
2
3
4
5

#找出选修1号课程的不及格的学生
SELECT Sno FROM SC WHERE Grade < 60 AND Cno = '1';
#查询结果不包括缺考学生, 因为它们的Grade值是NULL
#找出选修1号课程的不及格的学生 以及 缺考的学生
SELECT Sno FROM SC WHERE Cno = '1' AND (Grade < 60 OR Grade IS NULL);

数据查询-嵌套查询

嵌套查询概述:

• 一个SELECT-FROM-WHERE语句称为一个查询块
• 将一个查询块嵌套再另一个查询块的 WHERE子句 或 HAVING短语 的条件中的查询被称为嵌套查询.

例如:

1
2
3
4
5
6
7
8

#查询选修了二号课程的学生的姓名
SELECT Sname    #外层查询 / 父查询
FROM Student
WHERE Sno IN (
  SELECT Sno    #内层查询 / 子查询
  FROM SC
  WHERE Cno='2'
);

注意: ORDER BY子句不允许出现在内层查询中, 因为其是对最终结果集的操作.

嵌套查询基本规则/定义:

• 上层查询块被称为外层查询 / 父查询;
• 下层查询块被称为内层查询 / 子查询;
• SQL语言允许多层嵌套查询;
• 子查询中不允许使用ORDER BY子句;

嵌套查询可分为两类: 不相关子查询 与 相关子查询.

不相关子查询:

子查询的查询条件不依赖于父查询

步骤:

• 由里向外逐层处理.
• 每个子查询在上一级查询处理之前求解.
• 子查询的结果用于建立父查询的查找条件.

相关子查询:

子查询的查询条件依赖于父查询

步骤:

• 取外层查询中表的第一个元组, 根据它与内层查询相关的属性值处理内层查询.
• 若WHERE子句返回值为真, 则取此元组放入结果表.
• 然后再取外层表的下一个元组, 重复这一过程, 直到外层表全部被检查完为止.

带有IN谓词的子查询

IN谓词表名集合之间的所属关系
如最上面的例子中, Sno属于子查询的结果集里的Sno.

例子1-1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#查询与刘晨在同一个系学习的学生学号, 姓名
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept IN (      #若这里Sdept确定只有唯一的值, 那么IN也可以用=代替,这涉及到带有比较运算符的子查询
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#此查询为不相关子查询
#将上述例子转变为连接实现,而不是嵌套实现
SELECT S1.Sno, S1.Sname
FROM Student S1, Student S2
WHERE S1.Sdept = S2.Sdept
  AND S2.Sname = '刘晨';

嵌套查询允许多重嵌套(理论上无限套娃):

例子1-2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

#查询选修了课程名为"信息系统"的学生学号和姓名
SELECT Sno, Sname #第三步: 找出这些学号的学生名字
FROM Student
WHERE Sno IN (
  SELECT Sno      #第二步: 找出选修该课程号学生的学号
  FROM SC
  WHERE Cno IN (
    SELECT Cno    #第一步: 找出信息系统课程的课程号
    FROM Course
    WHERE Cname = '信息系统'
  )
);
#此查询为不相关子查询
#将上述例子转变为连接实现,而不是嵌套实现
SELECT Student.Sno, Student.Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno
  AND SC.Cno = Course.Cno
  AND Course.Cname = '信息系统';

带有比较运算符的子查询

• 当能确切直到内层查询返回单值时,可用比较运算符 (>, <, =, >=, <=, !=, <>)

例子2-1:

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

#将例子1-1由 带有IN谓词的子查询 改造为 带有比较运算符的子查询(查询与刘晨在同一个系学习的学生学号, 姓名)
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept = (       #由于刘晨只可能在一个系学习,因此可用用=代替IN
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#找出每个学生超过他所 选修课程的平均成绩 的课程号.(课程平均成绩,不是他的平均成绩)
#-----方式1: 不相关子查询-----
SELECT Sno, Cno
FROM SC
WHERE Grade >= (      #错误, 返回值不唯一
  SELECT AVG(Grade)
  FROM SC
  GROUP BY Cno
);
#-----方式2: 相关子查询-----
SELECT Sno, Cno
FROM SC x
WHERE Grade >= (      #正确, 返回值唯一
  SELECT AVG(Grade)
  FROM SC y
  WHERE y.Cno = x.Cno #子查询的Cno依赖父查询的Cno
)
ORDER BY Sno ASC, Cno ASC;
#方式2中子查询的依赖父查询的一个值Cno,因此是相关子查询

相关子查询可能的执行过程:

• 从外层查询中取出SC的一个元组x, 将元组x的Cno值传给内层查询.
• 在内层查询中得到值, 用该值替代内层查询, 得到外层查询
• 再从外层查询中取一个元组, 如此往复, 直到外层表的所有元组遍历完毕.
• (可以视作一个循环)

带有ANY (SOME) 或 ALL谓词的子查询

在谓词逻辑中, 由存在量词和全称量词的概念, 在SQL中没有对对应的表达, 统一采用”谓词”来表达.

SQL中实现全称量词和存在量词概念的方式:

1. 引入 ANY 和 ALL 谓词, 其对象为某个查询结果, 表示其中任意一个值或者全部值.
2. 引入 EXIST 谓词, 其对象也是某个查询结果, 表示这个结果是否为空, 返回真值.

使用ANY或ALL谓词时必须同时使用比较运算符, 其语义解释如下:

例子3-1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY (  #思路: 年龄要小于任意一个计算机系学生的年龄
  SELECT Sage
  FROM Student
  WHERE Sdept = 'CS'
)
AND Sdept != 'CS';  #父查询块中的条件: 只要查非计算机系的人.
#重构上述SQL语句, 使得不用ANY / ALL谓词实现相同功能
SELECT Sname, Sage
FROM Student
WHERE Sage < (      #思路: 年龄要小于任意一个计算机系学生的年龄 => 年龄小于计算机系年龄最大的学生的年龄
  SELECT MAX(Sage)
  FROM Student
  WHERE Sdept = 'CS'
)
AND Sdept != 'CS';   #父查询块中的条件: 只要查非计算机系的人.

可见, ANY / ALL谓词与运算符相组合处, 通常可以用聚集函数 / IN谓词等价代换.

ANY/ALL谓词 与 聚集函数/IN谓词的等价代换关系:

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据, 只产生逻辑值”true” / “false”.

• 若内层查询结果 非空 , 则外层的WHERE子句返回 true
• 若内层查询结果 为空 , 则外层的WHERE子句返回 false

由EXISTS引出的子查询, 其目标列表达式通常都用*, 因为带EXISTS的子查询只返回true或false, 给出列名无实际意义.

例子4-1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#查询所有选修了1号课程的学生姓名
SELECT Sname
FROM Student
WHERE EXISTS (      #子查询结果返回true或false
  SELECT *
  FROM SC
  WHERE SC.Sno = Student.Sno
    AND SC.Cno = '1';
);
#查询没有选修1号课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS (  #子查询结果返回true或false
  SELECT *
  FROM SC
  WHERE SC.Sno = Student.Sno
    AND SC.Cno = '1';
);

所有带IN谓词, 比较运算符, ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换.
但反过来则不成立, EXISTS可表达的逻辑更多.

EXISTS可以实现全称量词

例子4-2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#将例子1-1的实现由 使用IN谓词 改为 使用 EXISTS谓词 (查询与刘晨在同系学习的学生的学号, 姓名)
#----------使用IN谓词实现----------
SELECT Sno, Sname     #第二步: 根据刘晨的系名查询所有学生的学号, 姓名
FROM Student
WHERE Sdept IN (
  SELECT Sdept        #第一步: 确定刘晨所在系名
  FROM Student
  WHERE Sname = '刘晨'
);
#----------使用EXISTS谓词实现----------
SELECT Sno, Sname
FROM Student S1
WHERE EXISTS (        #思路:针对每个学生查询其所在的系是否有刘晨这个人
  SELECT *
  FROM Student S2
  WHERE S2.Sdept = S1.Sdept
    AND S2.Sname = '刘晨'
);

例子4-3

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#查询选修了所有课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS (    #思路: 对于这个学生, 不存在任何一门课
  SELECT *
  FROM Course
  WHERE NOT EXISTS (  #思路: 对于这个课程, 不被该学生选中
    SELECT *
    FROM SC
    WHERE SC.Sno = Student.Sno
      AND SC.Cno = Course.Cno
  )
);
#关系的代数里使用除法实现, 这里用NOT EXISTS也实现了

用EXISTS实现逻辑蕴涵:
可以把所有带有全称量词的谓词转换为等价的带有存在量词的谓词

例子4-4:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#查询至少选修了学生201215122选修的全部课程的学生号码
SELECT Sno
FROM SC SCX
WHERE NOT EXISTS (    #对于这个学生, 不存在任何一个被201215122选修的课程
  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
  )
);