山东大学数据库系统复习-CSDN博客

本文链接：https://blog.csdn.net/2303_80811725/article/details/149172201

数据库系统复习

第一章引言

**1.1.**基本概念
- **1.1.1.**数据（ Data）
  - 数据(Data)是数据库中存储的基本对象
  - 数据的定义：描述事物的符号记录
  - 数据的种类：文字、图形、图像、声音
  - 数据的特点：数据与其语义是不可分的
- 1.1.2.数据库(Database，简称DB)
  - 数据库是长期储存在计算机内、有组织的、可共享的大量数据集合
  - 数据库的特征：
    - 数据按一定的数据模型组织、描述和储存；
    - 可为各种用户共享；
    - 冗余度较小；
    - 数据独立性较高；
    - 易扩展
- 1.1.3.数据库管理系统(Database Management System，简称DBMS)
  - DBMS由一个互相关联的数据的集合和一组用以访问这些数据的程序组成，是位于用户与操作系统之间的一层数据管理软件
  - 数据集合一般被称为数据库
  - DBMS的用途：科学地组织和存储数据、高效地获取和维护数据
  - DBMS的主要功能：
    - 数据定义功能：提供数据定义语言(DDL)，定义数据库中的数据对象
    - 数据操纵功能：提供数据操纵语言(DML)，操纵数据实现对数据库的基本操作
      (查询、插入、删除和修改)
    - 数据库的运行管理：保证数据的安全性、完整性，多用户对数据的并发使用，发生故障后的系统恢复
    - 数据库的建立和维护功能
- 1.1.4.数据库系统(Database System，简称DBS)
  - 数据库系统是指在计算机系统中引入数据库后的系统。在不引起混淆的情况下常常把数据库系统简称为数据库
  - 数据库系统的构成：数据库、数据库管理系统、应用系统（及其开发工具）、数据库管理员（和用户）
1.2.数据视图
- **1.2.1.**数据抽象三层次
  - 物理层:最低层次的抽象，描述数据存储
  - 逻辑层:描述存储在数据库中的数据，以及数据之间的关系
  - 视图层:最高层次的抽象，只描述整个数据库的某部分数据
- **1.2.2.**实例与模式
  - 实例–特定时刻存储在数据库中的信息的集合
  - 模式–数据库的总体设计
  - 数据独立性 –物理数据独立性/逻辑数据独立性
  - 型与值的区别
    - 型是对数据的结构和属性的说明----模式
    - 值是型的一个具体赋值 ----实例
    - 型是相对稳定的，值是随时间不断变化的
- 三级模式（schema）
  - 物理模式（存储模式、内模式）：数据库的物理架构，有且只能有一个
  - 逻辑模式：数据库逻辑结构
  - 子模式（外模式）：逻辑模式的子集
- 两级映像（map）：可看作两模式间的函数
  - 外模式/模式映像：定义某一个外模式和模式之间的对应关系，映像定义通常包含在各外模式中（模式改变时，修改这个来保持外模式不变，即是逻辑数据独立性）
  - 模式/内模式映像：定义数据逻辑结构和存储结构之间的对应关系（存储结构改变时，修改这个来保持模式不变，即是物理数据独立性）
**1.2.3.**数据模型
- 数据模型分四类：
  - 关系模型：用二维表来表示实体及其相互联系
  - 实体-联系模型
  - 基于对象的数据模型
  - 半结构话数据模型
常见题目
- 三级模式
  - 内模式：数据的存储结构和存取⽅法，是数据库的最底层抽象。定义数据在存储
    介质上的实际存储⽅式，管理数据的物理访问路径。
  - 模式：描述数据库的全局逻辑结构，是数据库的中间层抽象。定义所有⽤⼾数据
    的逻辑结构，描述数据的完整性和安全性，建⽴数据之间的逻辑关系。
  - 外模式：⽤⼾可⻅的局部逻辑视图，是数据库的最⾼层抽象，为不同⽤⼾提供定
    制化数据⼦集，简化复杂查询，增强数据安全性。
- 两级映像与数据独立性（逻辑与物理）

第二章关系模型介绍

2.1关系数据库的结构
- **2.1.1.**几个概念
  - 域——每个属性可能的取值范围（集合）叫属性的域空值——表示值为止或不存在
  - 关系——指代“表” 关系是无序的，元组的顺序是无关紧要的。
  - 笛卡尔积D1×D2× …×Dn的子集叫做在域D1 , D2 , … , Dn上的关系，用R(D1 , D2 , … , Dn )表示 R是关系的名字，n是关系的度或目
  - 关系是笛卡尔积中有意义的子集关系也可以表示为二维表
  - 元组——指代行（是一组值的序列）
  - 属性——指代列属性的值（通常）要求为原子的，也就是说，不可再分关系实例——表示一个关系的特定实例，也就是所包含的一组特定的行
- 关系的性质
  - 有限性：无限关系无意义
  - 属性无序性
  - 列同质：列的分量来源于一个域，数据类型相同
  - 属性名区分相同数据类型不同列
  - 列次序可交换
  - 元组相异
  - 分量不可再分（原子性，1NF）
2.2关系模式
- 关系模式：关系的描述，对应于程序设计语言中类型定义的概念，对于A1, A2, … , An 属性，R = (A1, A2, … , An ) 是一个关系模式
- 关系实例——对应于程序设计语言中变量的值的概念
2.3关系模型（码）
- 超码：属性的集合，可以在一个关系中唯一地标识一个元组
- 候选码：任意真子集都不能成为超码的超码
- 主码：从一个关系的多个候选码中选定一个作为主码
- 外码：一个关系模式r1可能在它的属性中包含另一个关系模式r2的主码，这个属性在上称作在r1上参照r2的外码（ r1和r2可以是同一个关系）。
2.4模式图
- 一个含有主码和外码依赖的数据库模式可以用模式图来表示。
- 每一个关系用一个矩形来表示，关系的名字显示在矩形上方，矩形内列出各属性。
- 主码属性用下划线标注。
- 外码依赖用参照关系的外码属性到被参照关系的主码属性之间的箭头来表示。
2.5完整性
- 实体完整性（NULL）
  - 定义：主码不可为空值NULL
  - 空值：不知道或无意义的值
  - NULL计算方面
    - 算术运算：结果NULL
    - 比较运算：结果NULL
    - 逻辑运算（除了不需要使用null判断的，结果都是null）：
      - null or true = true
      - null and false = false
      - 其余为null
- 参照完整性
  - 若存在外码导致的参照关系，每个元组中外码必须对应主码的某个存在值，或者在没有非空约束的情况下为null
能否插入元组
- 主码不可为null或重复
- 外码不可无对应的关系的已存在主码，但可以为null
- 注意插入时会不会出现 ‘null’ ，插入的变成字符串而不是null

关系代数

基本运算
- 一元运算
  - 选择（σ，Selection）
    - 只针对行
```
σ_{条件表达式}(关系名)
（例：σ_{年龄>20}(学生表) 表示选取年龄大于20的学生）
```
  - 投影（π，Projection）
    - 只针对列
    - 结果去掉相同元组
```
π_{属性1, 属性2, ...}(关系名)
（例：π_{姓名, 专业}(学生表) 表示选取学生的姓名和专业列）
```
  - 更名（ρ，Rename）
- 多元运算
  - 笛卡尔积（×，Cartesian Product）
    - 属性重复命名：关系名 . 属性名
```
关系1 × 关系2
（例：学生表 × 课程表 生成所有学生与课程的组合）
```
  - 集合并（∪，Union）
    - 关系属性数必须相同
    - 去除重复元组
    - 必须相容，不要求名字相同，但A第二列对应B第二列属性必须相容
  - 集合差（−，Difference）
    - 关系必须相容
- 其他运算
  - 集合交（∩，Intersection）
    - 关系必须相容
  - ɵ连接（θ 连接，Theta Join）
    - 连接后同名属性前有关系名
```
关系1 ⋈_{条件表达式} 关系2
（例：学生表 ⋈_{学生.学号=选课.学号} 选课表）
```
  - 自然连接（⋈，Natural Join）
    - 同名属性做连接，结果无需重名
    - 多个同名属性都需相等
    - 无同名属性时，结果是笛卡尔积
    - 同名属性无相同元组时，得到空集
```
关系1 ⋈ 关系2
（例：学生表 ⋈ 选课表，自动按“学号”属性连接并去重）
```
  - 除（÷，Division）
    - 象集：
    - 适用于全部满足情况
    - 等价（如果存在其他属性，减R前投影到“X，Y”上）
  - 赋值（←，Assignment）
    - 可以赋给临时关系变量用于当作中间结果
    - 赋给永久关系变量就会改变数据库

扩展

外连接
- 适用于统计数据，比如根据SC（sno,cno,score）得到每个学生在每一科上的分数，就需要分别统计学生在每科上的分数，然后外连接到一起，使得一些学生没选的课的分数变成null
1. 左外连接（Left Outer Join，⟕）
  - 定义：保留左侧关系R的所有元组，右侧关系S中无匹配的元组对应属性填NULL。
  - 关系代数表示：（解释：左半部分为内连接结果，右半部分为R中未匹配元组与NULL的笛卡尔积）
    
    R ⟕ S = (R ⋈ S) ∪ (R - π_{R.attrs}(R ⋈ S)) × {NULL}
2. 右外连接（Right Outer Join，⟖）
  - 定义：保留右侧关系S的所有元组，左侧关系R中无匹配的元组对应属性填NULL。
  - 关系代数表示：
    
    R ⟖ S = (R ⋈ S) ∪ ({NULL} × (S - π_{S.attrs}(R ⋈ S)))
3. 全外连接（Full Outer Join，⟗）
  - 定义：保留两侧关系R和S的所有元组，无匹配的元组对应属性填NULL。
  - 关系代数表示：
    
    R ⟗ S = (R ⟕ S) ∪ (R ⟖ S)

聚集函数

输入是关系中的一组元组（或分组后的元组集合），输出是标量值。
支持多重集（存在重复元组），可用distinct去重
自动忽略null值（除非明确处理），但count(*)例外（统计所有元组，包括含null的行）。

常见聚集函数的定义、注意事项及示例

函数名称	定义	注意事项
sum(expr)	计算`expr`的数值和（仅适用于数值型数据）	忽略`null`值- 非数值型数据会报错
avg(expr)	计算`expr`的平均值（仅适用于数值型数据，不能对字符串）	等价于`sum(expr)/count(expr)`- 除数为 0 时（无有效数据）返回`null`
max(expr)	返回`expr`的最大值（支持数值、字符、日期类型）	字符型按字典序比较（可对字符串进行首字母排序）
日期型按时间顺序比较
min(expr)	返回`expr`的最小值（支持数值、字符、日期类型）	同`max(expr)`
count(expr)	统计`expr`非`null`值的数量	`expr`可为列名或表达式`count(distinct expr)`去重统计
*count()**	统计关系中元组的总数（包括所有行，无论是否含`null`）	唯一不忽略`null`的聚集函数，常用于获取表行数

结果列为聚集函数名（注意95是关系，而不是int类型数据）：
聚集函数分组运算
- 存在多个属性时，全部相等为同一元组
- 示例：对关系SC(sno, cno, score)按sno分组，统计每个学生总分和平均分：
- 添加的分组属性也会在结果中列出
除count(*)外，所有聚集函数自动忽略输入中的null值。
- 示例：avg({1, null, 3})等价于avg({1, 3}) = 2。
未分组的属性不能直接出现在查询结果中（除非用聚集函数包裹），否则违反 “关系代数完整性”。
用having过滤分组后的结果（如having count(sid) > 10），不能用where（因where先于分组执行）。
大多数数据库不支持直接嵌套聚集函数（如max(avg(score))），需通过子查询实现：
```
select max(avg_score) from (select avg(score) as avg_score from student group by grade)
```

性质
- 分配律

书写注意
- 最后结果的元组属性前关系名要不要加
- 使用自然连接查找时，注意会不会把原来的主体包括（Smith也会被包括在结果里）
- θ连接上同名属性有关系名，自然连接没有
- 除运算的结果观察一下是否还需要投影（结果已经是选修所有课程的学生的学号了），同时要观察被除数是否存在干扰属性（分数会使得将（学生，分数）成为一个独立元组）
- 计算时观察两边关系的元组属性数是否有要求，比如减法
- 注意不等于写做“<>”
- 有些题目要求输出指定结果，需要对最后结果进行更名才能把列名对应上
- 聚集函数分组时需考虑后续需要哪些属性，如果分组时没有提出就没办法在结果中选择或者投影
- 聚集函数除了count能够正常给出空集对应的值以外，其他聚集函数使用时遇到只会产生结果null，比如max（score）=null，因此需要提前判断（注意空集，和含null元组的区别）
- 空值
  - σ选择表达式为真的式子，可以看看null的逻辑运算法则（null=任何值或者null<>任何值，结果都是false）
  - ∪、∩、π运算把null当作一个奇特的值，仍会去重
常见题目
- 关系模式与关系示例的定义与区别
  - 定义：
    - 关系模式：描述关系的逻辑结构，即表的结构
    - 关系实例：某时刻关系中存储的具体数据
  - 区别
    - 相同点：
      - 它们都是关系型数据库模型的核心组成部分，共同描述和存储数据，关系
        模式定义了数据的结构，关系实例则是这些结构的具体数据填充
    - 不同点：
      - 关系模式是静态的，定义后不随数据变换⽽改变，类似于数据库的“模板”
        或蓝图
      - 关系实例是动态的，随数据的增删改查⽽变化
      - 关系实例的变化必须满⾜关系模式中定义的约束。
- null的定义及各种计算
  - 空值表⽰某个属性的值未知，缺失或不适⽤，它不是⼀个具体的值，⽽是表⽰
    数据的不确定性或不存在性
- 为什么主码不能为空
  - 破坏实体完整性
  - 破坏参照完整性
- ⽐较关系型数据库与⾮关系型数据库的优缺点
  - 逻辑结构：
    - 关系型数据库定义了严格的表结构，数据插⼊之前必须定义结构，使⽤结构化查询语⾔，预定义结构带来可靠性和稳定性，但修改这些数据结构⽐较⿇烦
    - ⾮关系型数据库基于动态结构，使⽤⾮结构化数据，因为Nosql数据库是动态结构，可以很容易适应数据类型和结构变化。
  - 物理结构：
    - 关系型数据库存储结构统⼀，稳定性强，但横向拓展困难
    - ⾮关系型数据库天然⽀持分布式，适合⼤规模数据存储与处理
  - 数据处理能⼒：
    - 关系型数据库为了维护数据的⼀致性付出了巨⼤的代价，读写性能较差，⾯对⾼并发读写性能差，在海量数据⾯前性能差，但处理事务，联表查询能⼒强
    - ⾮关系型数据库更适合⾼并发，⼤数据，灵活场景

第三章 SQL

主要按照这个表格结构介绍

SQL 功能	操作符
数据定义	CREATE，ALTER，DROP
数据查询	SELECT
数据操纵	INSERT，UPDATE，DELETE
数据控制	GRANT，REVOKE

3.1SQL概述
- SQL组成：
  - 数据定义语言(DDL)
  - 数据操纵语言 (DML)
  - 数据控制语言(DCL)
  - 事务控制(Transaction Control)
  - 嵌入式SQL和动态SQL(Embedded SQL and Dynamic SQL)

数据定义（data definition）

主要用于定义数据库对象（如数据库、表、视图、索引等）的结构
表空间：
- 表空间是数据库的逻辑划分，一个表空间只能属于一个数据库，一个数据库可以拥有多个表空间，表空间分为系统表空间和用户定义的表空间
- 一个表空间可以包含多个表，一个表只能属于一个表空间

create

创建新的数据库对象，比如创建数据库、表、视图、索引等。

在这里插入图片描述

示例（创建表）：

CREATE   TABLE   S (
	 sno    CHAR(4),
	 sname  CHAR(8)  NOT NULL,
	 age   SMALLINT,
	 gender   CHAR(1),
	 constraint pk_s PRIMARY KEY (sno), //pk_表名
	 CHECK (gender=‘0’ or gender=‘1’)
	 );

create domain  person_name  char(20)
create   table   T
(
tno    char(10),
tname person_name not null,
sal  int,
dno   char(10),
primary key (tno),
foreign key (dno) references D(dno),
check (sal > 0)
);

完整性约束
- 主码约束： PRIMARY KEY（约束数据唯一且非空）
- 唯一性约束：unique（约束数据唯一，比如邮箱账号）
```
CREATE   TABLE   S (
	 sno    CHAR(4),
	 sname  CHAR(8)  NOT NULL,
	 email varchar(100) UNIQUE
	 );
```
- 非空值约束：NOT NULL
- 参照完整性约束：FOREIGN KEY

alter

修改已存在数据库对象的结构，像给表添加列、修改列的数据类型、删除列等。
格式：

示例（给表添加列）：

alter table student add column gender varchar(10);

//注意：修改列的长度时，只能改长，不能改短)
alter  table S MODIFY sname  varchar2(30)；

//空表可以，非空表不行（因为对于已有元组，添加的列是以null补足的）
alter table T add location char(30) not null；

//CASCADE表示：在基本表中删除某列时，所有引用到该列的视图和约束也要一起自动地被删除。
//RESTRICT表示在没有视图或约束引用该属性时，才能在基本表中删除该列，否则拒绝删除操作。
alter table S  drop  column Scome CASCADE；

//删除约束
alter table S drop constraint pk_s;

drop
- 删除数据库对象，例如删除数据库、表、视图、索引等，删除后相关对象及数据会被移除。
- 风险：撤消基本表后，基本表的定义、表中数据、索引都被删除，由此表导出的视图将无法继续使用
```
drop table student;
```

数据查询（data query）

索引
- 格式：
- unique(distinct)：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新
- cluster：聚集索引，表中元组物理顺序与索引的逻辑(索引)顺序相同。一个基本表上只能建一个聚集索引(该索引可以包含多个列)
- asc /desc：索引表中索引值的排序次序，缺省为asc
```
create  cluster  index   s_sno_clu  on  S(sno);
create  unique  index   s_sno  on  S(sno);
create index   s_sname  on  S(sname);

//当前无重复id时可建立
create  distinct index s_id on S(id);

//注意：删除唯一索引后，唯一索引对于数据的约束就消失了
drop  index  【索引名】
```
select：从一个或多个表中选取数据，可结合 from（指定数据源表）、where（筛选行条件）、group by（分组）、having（分组后筛选）、order by（排序结果）等子句使用。

//标准查询语句顺序
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

select

可以为列名，* ，算术表达式，聚集函数

“ * ”：所有属性

//可以是带+，-，*，/的算术表达式
select sal*0.95
from T

//结果默认保留重复元组，若要去掉重复元组，可用关键字distinct或unique指明
select distinct sno
from SC

//更名运算
select sal*0.05 as tax
from T

标量子查询：只要子查询只返回一个包含单个属性的元组（保证只返回单个值），允许它出现在返回单个值的表达式能够出现的任何地方

select dept_name,       
(select count(*)           
 from instructor           
 where department.dept_name = instructor.dept_name)        
as num_instructors       
from department

from

相当于笛卡尔积

//在from子句中使用as子句定义元组变量
//作用：1、简化关系名称；2、比较同一关系的两个元组
//as可省略
select    a.sno, sname   //注意查出来的属性是通过更名后的关系做前缀
from    s as a, sc as b   
where    a.sno = b.sno and cno = ‘c1’;

复杂查询

select       sname ， avg_score   
from      (select  sname ， avg(score)		     
					from        s，sc	 	    
					where      sc.sno = s.sno		    
					group by   s.sno,sname)         
					result (sname ，avg_score )   //更名，不写as，写了会报错
where  avg_score >=  60;

//lateral允许访问同一from子句的前面的表或子查询的属性
select name, salary, avg_salary       
from instructor I1,               
lateral (select avg(salary) as avg_salary                             
					from instructor I2                             
					where I2.dept_name = I1.dept_name)

with子句

//提供临时视图，只对出现with的语句有效
with  max_score (mscore) as    
	select  max(score)      
	from sc
select   sno    
	from   sc, max_score    
	where   sc.score = max_score.mscore;

dual：一行一列，通常只有一个名为dummy或x的列，值为1
- sys用户下的一张内部表，所有用户都可访问

where

常见查询符号

查询条件	谓词	作用与示例说明
比较	>, <, =, <>, >=, <=, some，all等	用于数值、日期、字符串等类型数据的比较示例：
`where score > 60`
多条件	and, or	组合多个查询条件，`and`要求同时满足，`or`满足其一即可；注意`and`优先级高于`or`，可用括号调整示例：
`where score > 60 and age < 20`
确定范围	between … and …, not between …and …	筛选值在 / 不在某连续范围的数据示例：
`where age between 18 and 25`
集合成员资格	in, not in	筛选值在 / 不在指定集合内的数据，集合元素用逗号分隔示例：`where city in ('北京','上海')`
判定空集合	exists, not exists	结合子查询，判断子查询结果是否存在记录，常用于关联查询示例：`where exists (select * from order where user_id = 1)`
字符匹配	like, not like	基于通配符（`%`匹配任意字符，`_`匹配单个字符）做模糊查询示例：`where name like '张%'`（查询姓张的姓名）
空值	is null, is not null	筛选字段值为空 / 非空的数据示例：
`where email is null`
重复	unique	如果子查询没有重复元组，则返回true，例：
`select sname
from s
where not unique
(select sno
from sc
where sc.sno = s.sno);
（查询至少选修了两门课的同学）`

比较

可用于子查询
子查询一定要在比较符号后

//查询与刘晨在同一个学院学习的学生(假设全校只有一位刘晨同学)
SELECT   sno，sname，dno    
FROM   S    
WHERE   dno  =          
(SELECT   dno            
 FROM    S             
 WHERE   sname =  ‘ 刘晨’)；

陷阱：当子查询的值不止一项时，‘=’的执行结果会报错，可以通过distinct对子查询结果进行修正，但是可能没办法修正，谨慎使用
some/all子查询
some：表达式的值至少与子查询结果中的一个值相比满足比较运算符
all：表达式的值与子查询结果中的所有的值相比都满足比较运算符

SELECT   sname，age       
FROM    S       
WHERE  age < some 
(SELECT  age                                             
FROM    S                                             
WHERE  dno= ' d1’)            AND dno <> ' d1' ;

集合成员资格

select    *       
from     S       
where   sname  in (’张军’，’王红’);

select   sno,cno    
from   s,c   
where   (sno,cno) not in		  	
(select   sno,cno                                  
from   sc)；

select    sno，sname       //外层查询/父查询
from     S       
where   sno   in				   
(select    sno				      //内部查询/子查询
from     SC     		      
where   cno = ‘c1’);

陷阱：in和not in其实等价于=的反复or操作和<>的反复and操作，因此当子查询查出null时，会使得结果变成null

解决方法，进行判断

select dno 
from d    
where dno not in 
(select dno                                         
from s                                         
where dno is not null);

子查询不可使用order by

判定空集合

select    sno，sname   
from     S   
where   exists 	
(select     *		   
from      SC   		   
where    cno = ‘c1’                 
and  sc.sno = s.sno);//注意！！！（相关子查询）

字符匹配

where 【列名】（not） like ‘字符串’
- like单向性，例如‘济南市%’ like ‘济南市山大路’ ，结果false
- “%”：匹配零个或多个字符
- “_”：匹配任意单个字符
- Escape：转义字符，便于匹配特殊字符（如用escape’\’定义\为转义字符，就可用‘_’匹配‘_’）
```
//查询名称中含有4个字符以上，且倒数第3个字符是’d’，倒数第2个字符是’_’的
//课程的所有信息
select    *
from     C
where   cname like  ‘% ＿d \＿＿’ escape’\’;
```

正则表达式：

REGEXP_LIKE(匹配)

//查询手机号码是以 1开头接着是3或5再加9位的数字
select *        
from table1       
where REGEXP_LIKE(SJHM, ‘^[1]{1}[35]{1}[[:digit:]]{9}$‘)

符号	作用	示例（匹配`abc`）
`^`	匹配字符串的开始位置。	`^ab` → 匹配以`ab`开头的字符串。
`$`	匹配字符串的结束位置。	`c$` → 匹配以`c`结尾的字符串。
`[]`	内部为匹配范围	`[1]{1}`表示有一位`1`
`[:digit:]`代表数字
`{}`	表示个数，有几位	`[1]{1}`表示有一位`1`
`[]`	匹配方括号内的任意单个字符。	`[abc]` → 匹配`a`、`b`或`c`。
`[^...]`	取反，匹配不在方括号内的任意单个字符。	`[^0-9]` → 匹配非数字字符。 `[^0-9]` → 匹配非数字字符。
`[a-z]`	匹配指定范围内的任意单个字符（连字符表示范围）。	`[a-z]+` → 匹配小写字母组成的字符串。
`[a-zA-Z]`	组合范围：匹配大小写字母中的任意一个。	`[a-zA-Z0-9]` → 匹配字母或数字。

REGEXP_INSTR (包含)
REGEXP_REPLACE(替换)
REGEXP_SUBSTR(提取)

空值
- 除了is （not） null，聚集函数count（*）外，其他查不出null

group by
- 聚集函数返回的是一个关系
- 不允许在count（*）中使用distinct
- 不使用分组的聚集函数，在select子句中，只能出现聚集函数，不能出现其他属性名；
```
//error
select tname， max(sal)
from T
```
- 除了count(*)以外的聚集函数，均忽略null值。如果应用的需求不希望忽略null值，可以使用nvl函数进行处理；
```
//把score中null变成0
nvl(score,0);
```
- 聚集函数在输入为空集合的情况下也返回一个关系(一个元组的关系)，结果集中返回值为null。count函数例外，在输入为空集合的情况下，count函数返回0。
having（聚集函数相关查询不能出现在where，在having）
- 对分组之后进行的选择运算

order by（不出现于子查询）

asc升序（不写时默认，从小到大）：空值最后显示
desc降序（从大到小）：空值最先显示

//按学院名称升序列出老师姓名，所在学院，同一学院中老师按姓名降序排列   
select    dname，tname      
from     T，D      
where   T.dno = D.dno  		 
order by    dname  asc，
						tname  desc;

集合操作
- 集合并：union
- 集合交：intersect
- 集合差： except(minus)
- 集合操作结果集自动去除重复元组，如果要保留重复元组，必须用all关键词指明
  union all ，intersect all， except all
- 优先级：交集最高，差并相同（从左到右执行）

where、having异同

相同：二者均是选择运算

不同：

二者的作用对象不同，where的作用对象是元组，having的作用对象是分组

相同逻辑下，两者效率可能有差异

//低效: 
select   count(sno)
from   s,dwhere   s.dno = d.dno
group by   dname
having   dname = ‘软件学院’;

//高效：
select   count(sno)	
from   s,d      
where   s.dno = d.dno and dname = ‘软件学院’;

相关子查询VS不相关子查询
- 不相关子查询：子查询的查询条件不依赖于父查询
- 相关子查询：子查询的查询条件依赖于父查询
- 二者的区别在于：元组变量的作用域不同，in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。相关子查询本身不完整，需要包括外层查询参数
子查询、临时视图VS基本表
- 相同：均为关系，可以作为SQL的查询对象
- 不同：子查询和临时视图在一次查询结束后立即消失；基本表永久保存在数据库中

数据操纵（data manipulation）

对表中的数据进行增、删、改操作，操作符及说明如下：

insert

格式

//插入一条指定了值的元组
insert  into   表名  [(列名[，列名]…]
values    (值 [，值]…)

//插入子查询结果中的若干条元组
insert  into   表名  [(列名[，列名]…]
(子查询)

示例：

insert into student (id, name, age) 
values (1, 'zhangsan', 20);

//error（定义列时，sal被用not null约束）
insert  into  T (tno, tname, dno)		 
values (‘t123’, ‘王明’, ‘d08’);//插入元组的sal被自动取空值

insert  into  EXCELLENT ( sno, score)		
select   sno, avg(score)		   
from    sc		   
group  by (sno)		   
having  avg(score) > 90;

建议写列名

若没写：
- 每列都有值
- 或按默认顺序赋值

update
- 修改表中已存在的数据行，通过 where 子句指定要修改的行。
- 示例：
```
update student 
set age = 21 
where id = 1;
```
- 多个update语句需考虑顺序
delete
- 删除表中的数据行，借助 where 子句确定删除范围，若不写 where 会删除表中所有行（谨慎操作）。
- 示例：
```
delete from student 

delete from student 
where age < 18;
```
- 删除是找到所有符合的元组后一起删除（避免条件不断改变）
```
delete   from    T	   
where  sal  <   				
(select   avg(sal) 		   		    
from     T);
```
- 参照完整性：先删除SC，再删S（SC中有外码）
DBMS 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则

数据控制（data control）

用于管理数据库的访问权限，涉及用户对数据库对象的操作权限设置与回收：
- grant：授予用户或角色对数据库对象（如表、视图等）的操作权限，比如查询、插入、修改等权限。
  - 示例（授予用户查询学生表的权限）：
    
    sql
    
    grant select on student to 'user1'@'localhost';
- revoke：收回之前授予用户或角色的权限，取消其对特定数据库对象的操作能力。
  - 示例（收回用户查询学生表的权限）：
    
    sql
    
    revoke select on student from 'user1'@'localhost';
常见SQL书写点
- select后属性是否要加关系名做前缀，是否要更名
- 根据已知表和结果表写SQL时，需要考虑
  - 目标表的属性列需要重命名
  - 目标表的属性前有对应关系表的前缀，因此需要考虑连接方式，是自然连接还是正常连接
  - 考虑聚集函数的细节，比如是count（*）还是count（sno）
  - 考虑表的排序问题，比如表会按照性别，学号等这种常被忽略的地方，当然也可能使用常见的分数等排序
  - 注意会出现那种性别等只有一个字的查询条件，别忘了（0是男，1是女）
  - 注意是否需要在结果加上distinct，注意多想想，有些时候写上总没错
- 常见变量类型
  - char(n)：固定长度的字符串，用户指定长度 n 。也可使用全称 character。
  - varchar(n)：可变长度的字符串，用户指定最大长度，等价于全称 character varying 。（作者注：在实际使用中，也可以写成 varchar2） . int：整数类型，等价于全称 integer 。(-2^31~~231-1)
  - numeric( p, d)：定点数，精度由用户制定。这个数有p位数字（加上一个符号位），其中d位数字在小数点后面。
  - date：日期(年、月、日) 。输入日期类型数据的格式是：date ‘ xxxx-xx-xx’ 或者to_date(‘xxxxxxxx’,’yyyymmdd’)其中 x 为数字，其它部分直接引用即可。
- 查日期
```
SELECT TRUNC(SYSDATE) FROM DUAL;  -- 输出：2025-06-15 00:00:00

-- 提取当前时间的年、月、日、时、分、秒
SELECT
    EXTRACT(YEAR FROM SYSDATE) AS 年,
    EXTRACT(MONTH FROM SYSDATE) AS 月,
    EXTRACT(DAY FROM SYSDATE) AS 日,
    EXTRACT(HOUR FROM SYSDATE) AS 时,
    EXTRACT(MINUTE FROM SYSDATE) AS 分,
    EXTRACT(SECOND FROM SYSDATE) AS 秒
FROM DUAL;

-- 等价于使用日期函数（传统方法）
SELECT
    TO_CHAR(SYSDATE, 'YYYY') AS 年,
    TO_CHAR(SYSDATE, 'MM') AS 月,
    TO_CHAR(SYSDATE, 'DD') AS 日,
    TO_CHAR(SYSDATE, 'HH24') AS 时,
    TO_CHAR(SYSDATE, 'MI') AS 分,
    TO_CHAR(SYSDATE, 'SS') AS 秒
FROM DUAL;
```

第四章中级SQL

**4.1.**连接表达式

连接操作作用于两个关系并返回一个关系作为结果。
基本分类
- 连接成分：包括两个输入关系、连接条件、连接类型
- 连接条件：决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性
- 连接类型：决定如何处理与连接条件不匹配的元组
**4.1.1.**连接条件
- on条件允许在参与有连接的关系的关系上设置通用谓词，该谓词的写法与where子句谓词类似
- 但在外连接中，on 条件的表现与where条件是不同的。
- on优点：
  - 对被称作外连接的这类连接来说，on条件的表现与where条件是不同的。
  - 如果在on子句中指定连接条件，并在where子句中出现其余的条件，这样的SQL查询通常更容易让人读懂。
**4.1.2.**连接
- 内连接：舍弃不匹配的元组（注意此处表格属性列命名方式有误，应当依然是同名属性，使用关系表做前缀命名）
- 外连接（outer join）运算与我们已经学过的连接运算类似，但通过在结果中创建包含空值元组的方式，保留了那些在连接中丢失的元组。内连接：舍弃不匹配的元组
  - 左外连接：内连接+左边失配的元组（缺少的右边关系属性用 null）
  - 右外连接：内连接+右边失配的元组（缺少的左边关系属性用 null）
  - 全外连接：内连接 + 左边失配的元组（缺少的右边关系属性用 null） + 右边失配的元组（缺少的左边关系属性用 null）
- on和where的不同：
  
  外连接只为那些对应内连接没有贡献的元组补上空值并加入结果。on条件是外连接声明的一部分（只有不需要进行补充空值时才考虑 on 条件）。但 where 子句是在外连接完成之后才进行的，这就会导致部分元组因为使用了空值填充而不满足 where 条件因而被排除了。
- 在表名后面加外连接操作符(*)或(+)指定非主体表(oracle数据库中使用(+))
  - 非主体表有一“万能”的虚行，该行全部由空值组成
  - 虚行可以和主体表中所有不满足连接条件的元组进行连接
  - 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值
```
-- 左外连接
//标准 SQL 语法（使用 LEFT JOIN）
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d
LEFT JOIN EMPLOYEES e ON d.DEPT_ID = e.DEPT_ID;
//Oracle 特有语法（使用 (+)）
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d, EMPLOYEES e
WHERE d.DEPT_ID = e.DEPT_ID(+);  -- 非主体表（员工表）后加(+)

-- 右外连接
//标准 SQL 语法（使用 RIGHT JOIN）
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d
RIGHT JOIN EMPLOYEES e ON d.DEPT_ID = e.DEPT_ID;
//Oracle 特有语法（使用 (+)）
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d, EMPLOYEES e
WHERE d.DEPT_ID(+) = e.DEPT_ID;  -- 非主体表（部门表）后加(+)

//全外连接通过union实现
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d, EMPLOYEES e
WHERE d.DEPT_ID = e.DEPT_ID(+)
UNION
SELECT d.DEPT_NAME, e.EMP_NAME
FROM DEPARTMENTS d, EMPLOYEES e
WHERE d.DEPT_ID(+) = e.DEPT_ID;
```

**4.1.3.**连接类型和条件

任意的连接形式可以和任意的连接条件进行组合。

连接类型	标准语法	连接条件	结果特点	示例 SQL（表 A 连接表 B）
内连接	`INNER JOIN`	通过`ON`子句指定关联字段的匹配条件（如`A.id = B.id`）	仅返回两表中匹配条件的记录	`SELECT * FROM A INNER JOIN B ON A.id = B.id;`
左外连接	`LEFT JOIN` 或 `LEFT OUTER JOIN`	同内连接	返回左表所有记录 + 右表匹配记录（右表无匹配时补`NULL`）	`SELECT * FROM A LEFT JOIN B ON A.id = B.id;`
右外连接	`RIGHT JOIN` 或 `RIGHT OUTER JOIN`	同内连接	返回右表所有记录 + 左表匹配记录（左表无匹配时补`NULL`）	`SELECT * FROM A RIGHT JOIN B ON A.id = B.id;`
全外连接	`FULL JOIN` 或 `FULL OUTER JOIN`	同内连接	返回左右两表所有记录（无匹配时补`NULL`）	`SELECT * FROM A FULL JOIN B ON A.id = B.id;`
自然连接	`NATURAL JOIN`（隐式匹配所有同名属性）	自动匹配两表中名称和数据类型完全相同的字段（无需显式`ON`子句）	结果中同名属性只保留一列	`SELECT * FROM A NATURAL JOIN B;`
使用 USING 子句	`JOIN ... USING (共同字段)`	通过`USING`指定一个或多个共同字段（如`USING (id)`）	等价于`ON A.id = B.id`，但结果中共同字段只保留一列	`SELECT * FROM A JOIN B USING (id);`

**4.2.**视图
- **4.2.1.**视图定义
  - 定义：
```
create view视图名 [(属性名 …)] as （查询表达式） 
```
  - as是必须的
  - 以下情况必须在视图定义时显式指定属性名：
    - 1. 列名重复或冲突
      当 SELECT 列表包含多个同名列（如多表连接）时，必须为重复列指定别名。
```
- ❌ 错误：未命名的重复列
CREATE VIEW OrderView AS
SELECT o.OrderID, c.CustomerID, CustomerID -- 两个CustomerID会冲突
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

-- ✅ 正确：显式命名重复列
//直接创建视图时命名属性列
CREATE VIEW OrderView (OrderID, CustomerID, CustomerName) AS
SELECT o.OrderID, c.CustomerID, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;
//select语句中更名
CREATE VIEW OrderView AS
SELECT o.OrderID as OrderID, 
				c.CustomerID as CustomerID, 
				c.CustomerName as CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;
```
    - 1. 包含表达式或函数
      当 SELECT 列表包含未命名的表达式、函数或计算结果时，必须为其指定别名。
```
- ❌ 错误：未命名的表达式
CREATE VIEW EmployeeInfo AS
SELECT EmployeeID, FirstName || ' ' || LastName, //拼接表达式未命名计算结果未命名
FROM Employees;
-- ✅ 正确：显式命名表达式
CREATE VIEW EmployeeInfo (ID, FullName, AdjustedSalary) AS
SELECT EmployeeID, FirstName || ' ' || LastName, Salary * 1.1
FROM Employees;
```
    - 1. 聚合函数结果
      使用聚合函数（如SUM、AVG、COUNT）时，必须为结果指定别名。
```
- ❌ 错误：未命名的聚合函数
CREATE VIEW DepartmentSummary AS
SELECT DepartmentID, COUNT(*) -- 未命名的COUNT SUM(Salary) -- 未命名的SUM
FROM Employees
GROUP BY DepartmentID;
-- ✅ 正确：显式命名聚合结果
CREATE VIEW DepartmentSummary (DeptID, EmployeeCount, TotalSalary) AS
SELECT DepartmentID, COUNT(*), SUM(Salary)
FROM Employees
GROUP BY DepartmentID;
```
    - 1. 提升可读性
      即使语法允许缺省，为复杂视图显式命名属性可提高代码可读性。
  - 删除视图：
    view本身不存在数据，删除不影响数据
```
drop view view_name
```
  - 视图不会要求分配存储空间，视图中也不会包含实际的数据。
  - 视图只保存视图定义，视图中的数据是从基表中获取。
  - 视图中的数据在视图被引用时动态的生成（相当于最新数据）。
- 4.2.2.SQL查询中使用视图
  - 一旦定义了一个视图，我们就可以用视图名指代该视图生成的虚关系。 create view 视图名（视图中属性名）as
  - 格式：select 属性名 from 关系名
- 视图作用
  - 定义视图能够简化用户的操作
    - 基于多张表连接形成的视图
    - 基于复杂嵌套查询的视图
    - 包含导出属性的视图
  - 视图机制能使不同用户以不同方式看待同一数据集，适应数据库共享的需要
  - 视图在一定程度上提高了数据的逻辑独立性，但也只能一定程度（对视图的更新是有条件的）
    如：建立视图使用户的外模式保持不变，从而对原表的查询程序无需更改，但仍可能因为基本表结构改变而改变
  - 视图能够对数据提供安全保护，对不同用户定义不同视图，使每个用户只能看到他有权看到的数据
- **4.2.3.**物化视图
  - 即实体化视图，它确实存放有物理数据。物化视图包含定义视图的查询时所选择的基表中的行。
  - 对物化视图的查询就是直接从该视图中取出数据，不需要从基本表中获得数据。
  - 如果查询中使用的关系发生了更新，则物化视图中的结果就会过期。因此每当视图的底层关系进行更新时要更新视图，以此来维护视图。
  - 使用物化视图的目的：提高查询性能，是以空间换时间的一种有效手段，更少的物理读/写，更少的cpu时间，更快的响应速度；规模较大的报表适合使用物化视图来提高查询性能。
  - 对应用透明，需占用存储空间，适用规模较大的数据查询来提高查询性能……
- **4.2.4.**视图更新
  - 一般来说，如果定义视图的查询对下列条件都满足，则称SQL视图是可更新的。
    - from子句中只有一个数据库关系
    - 不能包括group by子句
    - select中不能使用unique，distinct关键字
    - select 子句中只包含关系的属性名，不包含任何算术表达式、聚集或distinct
    - 任何没有出现在 select 子句中的属性可以取空值；它们也不构成主码的一部分（在这种情况下，插入元组时没有声明的属性值用 null 代替） . 查询中不含有 group by 或 having 子句
  - 对视图更新语句就是把对基本表更新语句中表名改为视图名，会被自动转化成对应基本表的更新
  - 一些视图没法插入数据
```
//error（转化为往S中插入信息时，发现没有主码sno，sno不能为null）
insert  into SN_AGE(sname, age )	
values ( “张立” ,23 )
```
  - 视图with check option
  - 如果向视图中插入一条不满足视图的where子句条件的元组，数据库系统将拒绝该插入操作。
**4.3.**事务
- 一个事务由查询和更新的语句的序列组成。
- 一个 SQL 语句开始执行隐含一个事务的开始。
- 以下列语句之一表示结束一个事务：
  - commit [work]：提交当前事务，即将该事务所做的更新在数据库中永久保存。
  - rollback [work]：回滚当前事务，即撤销该事务中所有 SQL 对数据库的更新，数据库恢复到执行该事务的第一条语句之前的状态。
  - 注： DDL 和 DCL 与事务无关，所有drop操作需要小心
**4.4.**完整性约束
- 完整性约束保证授权用户对数据库进行修改时不会破坏数据的一致性。防止对数据的意外破坏。
- 完整性约束通常被看成是数据库模式设计过程的一部分，它作为用于创建关系的create table命令的一部分被声明。
- 4.4.1.单个关系上的约束
  - not null：声明禁止该属性取值为空值。（注意插入更新）
  - primary key
  - unique
    - unique ( A1, A2, …, Am)
    - Unique声明指明下列属性A1, A2, … Am 形成了一个候选码；即在关系中没有两个元组能在所有列出的属性上取值相同。候选码属性可以为null（主码不可为空）。空值不等于其他的任何值。
  - check § ，P是一个谓词
    - 关系上的每一个元组，都必须满足P。
    - 如果S中删除元组，不会触发CHECK子句，只有对SC表的更新才会触发。主码约束：主码值不允许空，也不允许出现重复
```
CREATE   TABLE   S		
( sno    CHAR (4),		  
sname  CHAR (8)  NOT NULL,		  
age   SMALLINT,		  
gender   CHAR (1),		  
constraint pk_s PRIMARY KEY (sno),		  
CHECK  (gender=‘0’ OR gender=‘‘1’)		
)
```
- 4.4.2.主码约束
  - 主码值不允许空，也不允许出现重复
  - 主码定义形式
    主码子句：PRIMARY KEY(sno)
    主码短语：SNO CHAR(4) PRIMARY KEY
  - 对于constraint pk_s PRIMARY KEY (sno)
    1. CONSTRAINT 关键字：用于为约束命名，使约束具有可读性和可管理性
    2. pk_s：约束名称（Constraint Name），通常采用 “pk_表名” 的命名约定
    3. PRIMARY KEY：约束类型，声明该列（或列组）为主键
    4. ( sno )：主键列，指定sno列作为主键
- 4.4.5.（外码约束）参照完整性
  - 保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现。这种情况称为参照完整性。
  - A是一个属性的集合，R和S是两个包含属性A关系，并且A是S的主码，如果对于每个在R中出现的A在S中也出现，则A被称为R的外码。
  - 例如关系S在dno上的取值有两种可能
    - 空值，表示该学生尚未分到任何系中；
    - 若非空值，则必须是D关系中某个元组的dno值，表示该学生不可能分到一个不存在的系中
  - 作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系
  - 删除基本关系元组
    FOREIGN KEY (sno) REFERENCES S(sno) [ON DELETE [RESTRICT |CASCADE | SET NULL] ]
    - RESTRICT方式(默认)（ restpict ）：只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作
    - CASCADE方式（cascade）：将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
    - SET NULL方式：删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值
  - 修改基本关系主码
    FOREIGN KEY (sno) REFERENCES S(sno) [ON UPDATE [CASCADE | SET NULL] ]
  - RESTRICT方式(默认)：只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改
  - CASCADE方式：将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值
  - SET NULL方式：修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值
- 4.4.6.全局约束
  - 全局约束涉及多个属性间的或多个关系间的联系
```
CREATE TABLE SC
(sno  CHAR(4), 
cno  CHAR(4), 
score   SMALLINT, 
PRIMARY KEY (sno, cno), 
CHECK(sno IN (SELECT sno FROM S)), 
CHECK(cno IN (SELECT cno FROM C))
)
```
- 4.4.7.约束的命名、撤销和添加
  - 命名：CONSTRAINT 约束名 <约束条件>
```
sno CHAR(4) CONSTRAINT S_PK PRIAMRY KEY

age SAMLLINT CONSTRAINT AGE_VAL
CHECK(age >= 15 AND age <= 25)
```
  - 关系上约束的撤消与添加
    - 撤消用alter …drop…
    - 添加用alter …add…
```
alter table S drop constraint S_PK
alter table SC add constraint  SC_CHECK
check(sno in select sno from S)
```
4.5.SQL的数据类型与模式
- 关于如何在SQL中创建基本的用户定义类型。
- 4.5.1.SQL中的日期和时间类型
  - date: 日期，包括年（四位）、月和日
    - 示例：date ‘2014-3-10’
  - time: 时间，包括小时, 分和秒。time§可以表示秒的小数点后的数字位数（默认值为0）
    - 示例：time‘09 :00 :30’
  - timestamp: date和time 的组合
    - 示例：timestamp‘2014-3-10 09 :00 :30’
  - interval: 时间段
    - 示例：interval‘1’day
  - SQL允许在上面列出的所有类型上进行比较运算，也允许在各种数字类型上进行算术运算和比较运算。两个 date/time/timestamp 类型值相减产生一个 interval 类型值。也可以在 date/time/timestamp 类型的值上加减interval 类型的值。例如x、y都是data类型，x-y的值是日期x到日期y间隔的天数。
- **4.5.2.**默认值
  - 给某一属性指定默认值后，当一元组插入关系时没有给定该属性的值，就会被设置为默认值。
```
create table s 
(sno char (5), 
sname varchar (20) not null, 
dno char (20), 
sex char(1) default ‘1’ , //当没有给出性别的值时，默认为‘ 1’
primary key (sno))
```
- **4.5.3.**创建索引
  - 格式：create index studentsno_index on s(sno)
  - 作用：索引是一种数据结构，它允许数据库高效地找到关系中那些在索引属性上取给定值的元组，而不用扫描关系中的所有元组。用于加快查询在索引属性上取给定值的元组的速度。
- **4.5.4.**大对象类型
  - 大对象（照片，视频，CAD文件等）以large object （lob）类型存储：
  - blob：二进制数据的大对象数据类型–对象是没有被解释的二进制数据的大集合（对二进制数据的解释由数据库系统以外的应用程序完成）
  - clob：字符数据的大对象数据类型–对象是字符数据的大集合
  - LOB存储实现：指针+文件
  - 当查询结果是一个大对象时，返回的是指向这个大对象的指针，而不是大对象本身
- **4.5.5.**用户定义的类型
  - 格式：create type 类型名 as 数据类型 [final根据系统自身决定]
  - 示例：create type person-name as char（20） [final]
    使用时：create table t(a1 char (10)，a2 person-name);
  - SQL提供了drop type和alter type子句里删除或修改以前创建过的类型
  - 域定义：可以在基本类型上施加完整性约束
  - 格式：create domain 域名 as 数据类型
  - 示例：create domain person-name as char（20）
  - 类型定义与域定义的区别：域上可以声明约束，例如not null，也可以为域类型变量定义默认值，然而在用户定义类型上不能声明约束或默认值。域并不是强类型的。因此一个域类型的值可以被赋给另一个域类型，只要它们基本类型是相容的。
  - 当把check子句应用到域上时，允许模式设计者指定一个谓词，被声明为来自该域的任何变量都必须满足这个谓词。
**4.6.**授权
- **4.6.1.**权限的授予与收回
  - 定义：允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来
  - 权限类型： select、insert、update、delete和all privileges（所有权限）
- 权限图
  - 结点是用户，根结点是DBA，有向边Ui→Uj，表示用户Ui把某权限授给用户Uj
  - 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径
- 授权命令
  - 格式：grant 表级权限on {表名 | 视图名}to {用户 [，用户]… | public} [with grant option]
  - 表级权限包括：select, update, insert, delete, index, alter, drop, resource以及它们的总和all，其中对select, update可指定列名
  - with grant option表示获得权限的用户可以把权限再授予其它用户
  - 示例：
```
grant select,insert on S to Liming
							with grant option
grant all on S to public//表S的全部权限
grant UPDATE(sno),SELECT ON TABLE S to U4 
grant ALL PRIVILIGES to public//数据库的全部权限
```
- **4.6.2.**角色
  - 角色：为了指明一类人应有的授权，提出了角色概念。在数据库中建立角色集，并授予每个角色一定的权限，然后将角色分配给用户。
```
create role teller;
grant select on account to teller;
grant insert,delete on customer to teller;
grant teller to john;
```
- 回收权限
  - revoke <权限列表>on <关系名或视图名> from <用户/角色列表>
  - 收回权限时，若该用户已将权限授予其它用户，则也一并收回。授权路径的起点一定是DBA（数据库管理员）
  - 示例：revoke insert on S from Liming;
- 特别：支持多库的数据库系统中授权对象可以是数据库
  - 数据库级权限包括：
    - connect：允许用户在database语句中指定数据库
    - resource：connect权限+建表、删除表及索引权利
    - dba：resource权限+授予或撤消其他用户的connect、resource、dba权限
  - 不允许dba撤消自己的dba权限，只能让别的dba撤销
常见题目
- 表和视图的定义与区别
  - 表（table）：数据库中实际存储数据的基本结构，由⾏和列组成，对应物理存储⽂件
  - 视图（View）：虚拟表，由⼀个或多个表查询结果动态⽣成，不存储实际数据，仅保存查询逻辑
  - 区别：
    - 表是数据的物理载体，直接存储数据⽂件；视图本质是预定义查询，仅存储查询语句，不保留实际数据
    - 表的数据是静态的，视图数据是动态⽣成的，每次访问视图，都要重新执⾏其底层查询
    - 表允许完整的增删改查，可直接修改数据；视图的修改能⼒有限
    - 表的数据访问速度更快，视图的查询性能取决于底层查询复杂度

第五章高级SQL

概述
- 一个应用程序包含很多功能，SQL无法一一完成，需要与高级程序设计语言合作，满足用户需求
- 动态SQL：动态SQL允许运行时以字符串的形式构建SQL，提交查询和更新
- 嵌入式SQL：嵌入式SQL必须在编译时全部确定，并交给预处理器
- API (应用程序调用，application-program interface)用于高级程序设计语言和数据库服务器之间的交互
  - 与数据库服务器连接
  - 向数据库服务器发送SQL命令
  - 逐个取结果集元组到程序变量
- ODBC (Open Database Connectivity) 用于C，C++，C# ，Visual Basic……
- JDBC (Java Database Connectivity) 用于Java

JDBC

与数据库的通信模型：
1. 打开一个数据库连接
2. 创建一个“statement”对象
3. 使用statement对象发送SQL并取回结果
4. 处理错误的异常处理机制

public static void JDBCexample(String dbid, String userid, String passwd) {
    try {
        // 加载Oracle JDBC驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        // 建立数据库连接
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@db.yale.edu:2000:univdb", 
            userid, 
            passwd
        );
        
        //_______________________________________________________
        //实际工作（示例）
        // 创建Statement对象
        Statement stmt = conn.createStatement();
        
        // 执行实际的数据库操作
        ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");
        while (rs.next()) {
            // 处理查询结果
            System.out.println(rs.getString(1));
        }
        rs.close(); // 关闭结果集
        //__________________________________________________________________________
        
        // 关闭资源
        stmt.close();
        conn.close();
    } catch (SQLException sqle) {
        // 处理SQL异常
        System.out.println("SQLException: " + sqle.getMessage());
        sqle.printStackTrace(); // 打印详细堆栈信息
    } catch (ClassNotFoundException cnfe) {
        // 处理驱动类未找到异常
        System.out.println("ClassNotFoundException: " + cnfe.getMessage());
        cnfe.printStackTrace();
    }
}

//更新数据库
try {
    // 执行插入操作
    stmt.executeUpdate(
        "INSERT INTO t(tno, tname, dno, age, salary) " +
        "VALUES('77987', 'Kim', 'd01', 32, 9800)"
    );
    System.out.println("数据插入成功");
} catch (SQLException sqle) {
    System.out.println("插入数据失败: " + sqle.getMessage());
}

// 执行查询并获取结果
    ResultSet rset = stmt.executeQuery(     //结果集（游标）
        "SELECT dno, AVG(salary) " +
        "FROM t " +
        "GROUP BY dno"
    );
    
    // 遍历结果集并打印
    while (rset.next()) {       
    system.out.println(rset.getString("dno") + " " +rset.getFloat(2));
    }
    
//得到结果字段：
//rs.getString(“dno”) 和 rs.getString(1) 
//如果dno是选择查询结果的第一个字段，上述两个操作功能相同

//处理空值
//int a = rs.getInt(“a”);    
//if (rs.wasNull()) 
//Systems.out.println(“Got null value”);
//如果不希望DB返回空值，可以在SQL使用nvl函数将空值处理成其他值返回

//示例
try {
    Class.forName("oracle.jdbc.driver.oracledriver"); //定义驱动程序
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@202.194.7.x:1000:student", "u1", "pw1"
    );
    Statement stmt = conn.createStatement(); //定义statement
    
    try { //插入s(s1,甲)
        stmt.executeUpdate("insert into s(sno,sname) values (‘s1’, ‘甲’)");
    } catch (SQLException sqle) { //错误处理
        System.out.println("could not insert:" + sqle);
    }
    
    try { //显示所有学生sno,sname
        ResultSet rset = stmt.executeQuery("select sno,sname from s");
        while (rset.next()) {
            System.out.println(rset.getString("sno") + ":" + rset.getString("sname"));
        }
        rset.close(); //释放rset
    } catch (SQLException sqle) { //错误处理
        System.out.println("select sno,sname err:" + sqle);
    }
    
    stmt.close(); //释放statement
    conn.close(); //释放连接
} catch (SQLException sqle) {
    System.out.println("SQLException:" + sqle);
}

PreparedStatement接口继承Statement， PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于 Statement 对象
PreparedStatement能够提高应用程序的安全性，防止SQL注入
当有用户的输入作为参数时，应当适用预备语句

预备语句支持占位符（不带引号的问号）

PreparedStatement pStmt = conn.createprepareStatement(                             
"insert into t(tno,tname,dno,sal)  
values(?,?,?,?)");
pStmt.setString(1, "88877");          
pStmt.setString(2, "Perry");
pStmt.setString(3, "d1");        
pStmt.setInt(4, 125000);
pStmt.executeUpdate();    
//可用变量代替常量，更具一般性

ResultSet元数据
元数据：描述数据的数据
将一个ResultSet看做关系，元数据即是ResultSet的关系模式

示例：

//当执行完一个查询后，得到一个ResultSet rs:
ResultSetMetaData rsmd = rs.getMetaData();
for(int i=1; i<=rsmd.getColumnCount(); i++) {
System.out.println(rsmd.getColumnName(i));              
System.out.println(rsmd.getColumnTypeName(i)); 	       
}

ResultSet元数据的作用：对程序中不能确定结果集模式时非常有用

//自由查询功能示例：用户自行输入查询sql语句，显示结果
String vsqlstring=getSqlStringfromUser()
//假设用户输入：select sno,sname,age from s where dno='d1';
PreparedStatement stmt=conn.createprepareStatement(); //创建Preparedstatement
ResultSet rset = stmt.executeQuery(vsqlstring);
ResultSetMetaData rsmd=rset.getMetaData() ;//rest的元数据
for(int i=1;i<=rsmd.getColumnCount();i++)
{rsmd.getColumnName(i);
rsmd.getColumnTypeName(i);
…//根据结果集模式，建立显示结果的表格
}
while (rset.next())
{…}//根据结果集元数据，显示一行结果数据
… //其它工作，释放Statement等

数据库也有元数据

//查找列名及类型
DataBaseMetaData dbmd;
dbmd = conn.getMetaData();
ResultSet rset; rset=dbmd.getColumns(null,"student","s","%") ;
// The value “%” has the same meaning as SQL like clause所有列
while (rset.next())
{rset.getString("COLUMN_NAME");
//读取列名
[//rset.getString](https://rset.getstring/)("COLUMN_TYPE");
//读取列类型
…}

//查找主码
DatabaseMetaData dmd = connection.getMetaData()
ResultSet rs = dmd.getPrimaryKeys(“”, “”, tableName);
while(rs.next()){          
system.out.println(rs.getString(“KEY_SEQ”),  //属性在主码的位置（如果主码有多个属性）		                        
rs.getString("COLUMN_NAME"));}

在默认情况下，每个SQL语句都被作为一个被自动提交的独立的事务，建议将自动提交关闭
conn.setAutoCommit(false);
事务必须被显式的提交或回滚conn.commit();或conn.rollback();

batch批量执行SQL语句（不包括select，一个stmt接受不了两个select的结果）

public void executeBatch(String[] sqls)throws Exception{
try{
Class.forName(driver);
conn=DriverManager.getConnection(url,user,pass);
conn.setAutoCommit(false);
System.out.println("开启了事务!");
stmt=conn.createStatement();
for (String sql : sqls) {
stmt.addBatch(sql);
}
int[] nums=stmt.executeBatch();  //可以执行 DDL 和 DML，不包括 select
for (int i = 0; i < nums.length; i++) {
System.out.println((i+1)+"行sql执行的结果:"+nums[i]);
}
conn.commit();
System.out.println("提交了事务!");
conn.setAutoCommit(true);
}finally{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
}

SQL支持定义函数

可返回数值

//定义一个函数，输入一个学院的编号，返回该学院的教师的数量
create function dept_count (dno  varchar(20))       
returns integer      
begin           
declare d_count  integer;           
select count (* ) into d_count           
from t
where t.dno = dno           
return d_count;       
end

//找出教师数大于12的所有学院的名称
select dname
from d
where dept_count (dno ) > 12;

也可返回表示例：

//返回一个包含特定系的所有教师的表
create function teacher_of (dno char(20)
returns table (tno varchar(5),			
								name varchar(20),			
								salary numeric(8,2))
return table	
						(select  tno, tname, salary	  
						from t	  
						where t.dno = teacher_of.dno)
						
//使用
select *	  
from table (teacher_of (‘d01’))

题目
- 在嵌入式 SQL 中，什么情况下的 DML 语句不必涉及到游标操作？
  - INSERT、DELETE 和 UPDATE 语句（无结果返回）
  - 对于 SELECT 语句，如果已知查询结果肯定是单值时（用变量来接收）

第七章数据库设计和E-R模型

E-R模型基本概念
- 实体（entity）：客观存在可相互区分的事物（唯一标识）
- 实体集（entity set）：是具有相同类型及共享相同性质（属性）的实体集合，组成实体集的各实体称为实体集的外延（Extension），实体集可相交
- 域（domain）：属性的取值范围
- 举例：性别的域为（’0‘，’1‘）（0女1男）
- 属性（attribute）：实体集中每个成员具有的描述性性质，是将实体集映射到域的函数
  - 简单属性：不可再分的属性
  - 复合属性：可以划分为更小属性（如将姓名分为姓与名）
  - 单值属性：每个实体在该属性上取值唯一
  - 多值属性：某个实体在该属性上取多个值（一个人可能有多个电话号码），为表示多值属性，使用花括号将属性名括住如 { phone_ number }
  - 派生属性：可以由其他相关属性派生出来（如年龄可以由生日计算），数据库中通常只存储派生属性的定义或依赖关系，用到时再从基属性中计算出来（为了效率，也可以保存）
  - 基属性：基础属性
  - 实体可表达为<属性，数据值>的集合
    - 例如，一个customer实体可以用集合{(customer-id, 677-89-9011),
      (customer-name, Hayes),
      (customer-street, Main),
      (customer-city, Harrison)}
- 码：主码的属性用下划线表示
- 联系(relationship)：是多个实体之间的相互关联，这些实体不必互异
- 联系集(relationship set)：同类联系的集合
- 元或度(degree)：参与联系集的实体集的个数
- 参与(participation)：实体集之间的关联称为参与，即实体参与联系
  - 示例：如王军选修“数据库系统”，表示实体“王军”与实体“数据库系统”参与了联系“选修”
  - 全部参与：实体集E中的每个实体都参与到联系集R中的至少一个联系（双线表示全部参与）
  - 部分参与：实体集E中只有部分实体参与到联系集R的联系中（单线表示部分参与）
- 角色(role)：实体在联系中的作用称为实体的角色,当需要显示区分角色时,在连接菱形和矩形的线上加上说明性的标注以区别不同的角色
映射基数（ Mapping Cardinalities）
- 实体之间的联系的数量，即一个实体通过一个联系集能与另一实体集相关联的实体的数目
- 二元联系集的映射基数
  - 只要记住一个点：“一”是至多一个，”多“是至少一个
  - 一对一
    - 两个实体集E1和E2, E1中的一个实体与E2中至多一个实体相联系，并且E2中的一个实体与E1中至多一个实体相联系
    - 不是一一对应
  - 一对多
    - 两个实体集E1和E2, E1中的一个实体与E2中n(n≥0)个实体相联系，并且E2中的一个实体与E1中至多一个实体相联系
  - 多对一
    - 两个实体集E1和E2, E1中的一个实体与E2中n(n≥0)个实体相联系，并且E2中的一个实体与E1中至多一个实体相联系
  - 多对多
    - 两个实体集E1和E2, E1中的一个实体与E2中n(n≥0)个实体相联系，并且E2中的一个实体与E1中m(m≥0)个实体相联系
- 实体集内的二元联系
- 多实体集间联系
  - 一对多
    - 设有n个实体集E1 , E2 , … , En ,若对于 E1 , … , Ei-1, Ei+1 , … , En ，分别给定实体e1 , … , ei-1 , ei+1 , … , en 时，至多有一个实体ei ∈ Ei与之相联系，则称有一个从Ei到E1 , … , Ei-1 , Ei+1 , … , En的一对多联系
    - 如“课程”，“教师”，“参考书”之间的“讲课”联系
  - 多对多
    - 设有n个实体集E1 , E2 , … , En ,若对于 E1 , … , Ei-1, Ei+1 , … , En ，分别给定实体e1 , … , ei-1 , ei+1 , … , en 时，至多有n个实体ei ∈ Ei与之相联系，则称有一个从Ei到E1 , … , Ei-1 , Ei+1 , … , En的多对多联系
- 基数约束
  - 基本的基数约束：m:1，m:n，1:1
  - 更精确的基数约束：给出上下界约束 l…h
  - 0…*等价于“多”
  - 0…1、1…1等价于“一”
弱实体
- 定义：一个实体的所有属性合在一起都不足以形成主码，则称这样的实体为弱实体；同一类型的弱实体形成弱实体集，反之称为强实体。
- 弱实体需要依赖属主实体而存在，不能独立存在
- 分辨符：用于区别依赖于某个特定强实体集的属性集合，也称作部分码。
- 弱实体集的主码由其依赖的强实体集主码和它的分辨符组成
- 弱实体集与其拥有者之间的联系称作标识性联系(identifying relationship)
- 弱实体集与强实体集之间是多对一的联系
- 弱实体集与存在依赖
  - 弱实体集必然存在依赖于强实体集(Strong Entity Set)
  - 存在依赖并不总会导致一个弱实体集，从属实体集可以有自己的主码
- E-R图：标示性联系是双边框菱形；弱实体集必须双线全部参与；分辨符用下划虚线表示
- 使用弱实体集原因
  - 避免数据冗余(强实体集码重复)，以及因此带来的数据的不一致性
  - 弱实体集反映了一个实体对其它实体依赖的逻辑结构，这是现实世界存在的现象
  - 弱实体集可以随它们的强实体集的删除而自动删除
E-R模型转换为关系模式
- 实体→关系
  - 属性→关系的属性
  - 弱实体→用包含标识性强实体集的主键作为列构成的表来表示
  - 复合属性→将每个组合属性作为复合属性所在实体的属性
  - 多值属性→新的关系+所在实体主码（独立建表）
- 联系转换为关系模式
  - 一个联系化成一个表
  - 表的属性：参与联系的实体主码+联系的属性
  - 表的超码：参与联系的实体集的主码的集合
  - 在联系转化成的表中：
    - 实体主码形成的属性pk(e1)∪pk(e2)∪… ∪pk(en)均应not null
    - 只有在联系转化成的表与其他表合并后，才可能允许为null
  - 如果在不同的实体集中出现了主码属性名称不唯一的情况，应该以实体的名字加上属性的名字形成唯一的属性名；
关系模式的合并
- 在实体和联系各自转化为关系模式以后，需要进行合并
- 二元一对一联系
  - 联系转化的表可以与任一端实体转化成的表进行合并
  - 二元一对一联系不能导致相关实体转化成的表合并
```
Dept(dno,dname)
President(pid， name)
Manage(dno,pid)//dno,pid均可作主码，假设选dno作主码

表的合并:
Dept+Manage∪Dept(dno,dname,pid)
&
President+Manage∪President(pid,name,dno)
不能进行下述合并：
Dept+Manage+President→ ?(不能接受的合并)
```
- 二元多对一联系
  - 联系转化的表可以和“多端”实体转化成的表进行合并
```
转化成的表
dept(dno, dname)
student(sno, sname)
sd(sno, dno, stime)  //dno非空值

表的合并
student+sd∪student(sno, sname, dno, stime)//dno可以为空值
```
- 二元多对多联系
  - 将联系定义为新的关系，属性为参与双方的主码和联系的描述性属性，不能进行合并
- 多元联系
  - 联系转化的表和实体转化的表不能进行合并
  - 即便是m:n:1,其转化的表和也不能进行合并
- 实体转化成的表，相互之间不能机械合并
- 联系转化成的表，相互之间不能机械合并
扩展E-R特性
- 特殊化(specialization)
  - 自顶而下的设计过程实体集可能包含一些子集，子集中的实体在某些方面区别于实体集中其他实体。
  - 在E-R模型中，特殊化用从特殊化实体指向另一个实体的空心箭头来表示，我们称这种关系为ISA关系
  - 对于属性继承，子类可以继承其超类的属性及超类所参与的实体集，如下图：
- 概括(generalization)
  - 自底而上的设计过程，多个实体集根据共同的特征综合成一个较高层的实体集,是特化的逆过程，二者在E-R图中不作区分
- 特殊化/概括上的约束
  - 限定实体成为低层实体集的成员：
    - 条件定义（如cloth实体集，根据sex属性，决定低层是男装还是女装）
    - 用户定义
  - 一个实体是否可以属于多个底层实体集：
    - 不相交特化：实体集必须属于至多一个特化实体集，使用一个箭头（如instructor和secretary）
    - 重叠特化：实体集可能属于多个特化实体集，分开使用多个箭头（如employee和student）
  - 完全性约束：
    - 全部概化：每个高层必须属于一个低层实体集
    - 部分概化：允许高层不属于任何低层实体集
- 聚集
  - 将关系作为抽象实体使用,可以减少冗余
  - 聚集的模式表示：聚集关系的主码、相关实体集外码和任何描述属性
  - 可以将联系和参与联系的实体聚集为高层实体(abstract entity)
  - 高层实体可以和其它实体产生联系
  - 核心是被聚集的“联系”，聚集成的高层实体本身不转化成表。
  - 高层实体参与的联系进行正常的表转化，高层实体的主码使用聚集的“核心联系”的主码代替
```
custom(…),bank(…),project(…)
order(cid,pid…)
guarantee(cid,pid,bid…)
```
设计要点
- 实体VS联系：描述实体间行为时采用联系集
- 实体VS属性：
  - 属性可以简化E-R模型（一个员工一个电话）
  - 实体有多方面性质，属性没有（一个员工多个电话，且电话具有属性）
- 属性VS联系：属性无法限制唯一性
- 二元VS多元：多元到二元转化可能出现信息缺失，难以表达参与联系的各方实体，浪费存储空间
设计过程
- 属性冲突
  - 属性域的冲突：属性的类型、取值范围不同（学号的域是整型？字符串？）
  - 属性取值单位冲突，如重量分别采用磅、千克
- 命名冲突
  - 同名异义：不同意义的对象具有相同的名字
  - 异名同义：同一意义的对象具有不同的名字
- 结构冲突
  - 同一对象在不同E-R模型中的抽象结果不同(实体、联系或者属性)
  - 同一实体在不同E-R模型中属性组成不同
  - 同一实体在不同E-R模型中抽象层次不同
  - 同一联系在不同E-R模型中呈现不同的类型
E-R模型符号体系

或者
题目
- ER画图例题：
  - 注意领导与被领导关系，保存联系所储存的相关属性
- 弱实体集
  - 一个实体的所有属性合在一起都不足以形成主码，则称这样的实体为弱实体；同一类型的弱实体形成弱实体集，反之称为强实体。

第八章关系数据库设计

模式规范化（normal form ，NF）
- 1NF：原子性（关系中每一变量不可再分）
  - 所有属性域是原子的
  - 原子域：如果某个域的元素被认为是不可再分的单元
- 2NF：属性完全依赖或不部分依赖于候选码（消除非主属性对码的部分依赖）
  - 若R∈1NF，且每个属性满足下列准则之一：
    - 它出现在一个候选码中
    - 它没有部分依赖于一个候选码
- 3NF：消除非主属性对码的传递依赖
  - 1、关系模式R(U, F)中，F+中所有函数依赖α→β ，至少有以下之一成立：
    - ① α→β 是平凡的函数依赖；
    - ② α是超码；
    - ③ β - α的每一个属性A都包含在R的候选码中
  - 2、关系模式R<U,F>中，若不存在这样的码X，属性组Y及非主属性Z(Z ⊄Y)，使得下式成立：X → Y , Y → Z , Y\→X，也就是非主属性对码没有传递依赖
- BCNF：3NF基础上减去条件3（一个关系模式只描述一件事）
  - 关系模式R<U,F>中，F中所有函数依赖 α → β , 至少有以下之一成立：
    - α → β是平凡的函数依赖
    - α是R的一个超码
- 4NF：BCNF条件1变成平凡多值依赖
  - 函数依赖和多值依赖集为D的关系模式R属于4NF的条件是：
    对于所有D+中形如： α→→β的多值依赖（其中α ⊆R∧β ⊆R），至少有以下条件之一成立：
    - α→→β是一个平凡的多值依赖；
    - α是模式R的超码。
  - 判断4NF：
```
D在Ri上的投影(限定)是集合Di，它包含以下内容：
1、D+中所有只含Ri中属性的函数依赖；
2、所有形如α  β∩Ri的多值依赖，其中α⊆Ri并且α→→β属于D+
```
  - 判断无损分解：
    令R为一关系模式，D为R上的函数依赖和多值依赖集合。令R1和R2是R的一个分解，该分解是R的无损分解，当且仅当下面的多值依赖中至少有一个属于D+：
    R1 ∩ R2 →→R1
    R1 ∩ R2→→R2
  - 分解4NF算法（BCNF的算法，把函数依赖改成多值依赖）

函数依赖：

定义：R（U，F）→关系模式（属性集，函数依赖）

设R(U)是属性集U上的关系模式，如学生（学号，姓名）
r是R(U) 上的任意一个关系实例，如（001，’张三‘）

在这里插入图片描述

概念	定义与说明	符号 / 记法
函数依赖（α→β）	关系模式中，若元组 α 属性值相同则 β 属性值必相同，α 是决定因素，β 是被决定因素	α→β

平凡函数依赖	α→β 且 β⊆α，一定成立	-
完全函数依赖	α→β 且 α 的任意真子集都无法决定 β	f
a → b
部分函数依赖	α→β 且存在 α 的真子集能决定 β	p
a → b
传递函数依赖	α→β、β→γ、β⊄α 且 β↛α（即非相互决定），则 γ 对 α 传递函数依赖	-

码

概念	定义与说明
超码	关系模式 R（U,F）中，属性 / 属性组 K 满足 K→U（能决定所有属性）
候选码	K 为超码，且 U 完全依赖于 K（U 不能由 K 的真子集决定）
主码	多个候选码中选定的一个，作为关系的主要标识
主属性	包含在任意一个候选码中的属性

逻辑蕴涵（logical imply）
- 定义：
  关系模式R，F是其函数依赖集，如果从F的函数依赖能够推出 α → β , 则称F逻辑蕴涵 α → β , 记作F ├ α→ β
- 函数依赖闭包：被F所逻辑蕴涵的所有函数依赖的集合，记作F+ = { α → β | F ├ α→ β}
  - 不要忘记空集和平凡函数依赖
  - 函数依赖闭包的推导理论基础（Armstrong’s Axiom）
```
- Armstrong公理系统
-- 自反律：若β⊆α，则α→β
-- 增广律：若α→β，则αγ→βγ
-- 传递律：若α→β、β→γ，则α→γ

-- Armstrong公理推导规则
-- 合并律：若α→β、α→γ，则α→βγ
-- 分解律：若α→βγ，则α→β、α→γ
-- 伪传递律：若α→β、γβ→δ，则γα→δ
```
  - 函数依赖闭包推导算法（使用Armstrong’s Axiom由F计算F+是NP完全问题，常用的是属性集闭包的计算）
- 属性集的闭包
  - 定义：
    令 α为属性集，将函数依赖集F下被 α函数确定的所有属性的集合称作F下 α的闭包，记作 α+
    记作： α+= {A | α →A能由F根据Armstrong公理导出}
  - 计算F下属性集 α 闭包的算法：
    对任意函数依赖β → γ , 如果β在当前的结果集中，就把 γ也放入结果集里面，循环直到没有新的元素加入
```
输入：属性集α、函数依赖集F 
输出：α+ 
步骤：
1. 初始化结果集result = α 
2. 循环：
   遍历F中每个函数依赖β→γ 
   若β⊆result 且 γ不在result中：
       result = result ∪ γ 
3. 直到result不再变化，最终result即为α+
```
  - 用途：
    - 判断属性集是否为超码 ( α+最终的结果集是否包含了所有的属性集）
      →判断属性集是否是候选码（ α+=U，且 α任意真子集的闭包不等于U）
    - 通过检验β ⊆ α+是否成立，可以验证函数依赖 α → β是否成立
    - 是另一种计算F+的方法：对任意 γ ⊆R，找出 γ+，对于任意的S∈ γ+，得到 γ→S
正则覆盖（Canonical cover）
- 函数依赖集F，G，若F+= G+，则称F与G等价。
  若F与G等价，则称F是G的一个覆盖，G是F的一个覆盖。
- 无关属性**(extraneous attribute)**：去除一个函数依赖中的属性，不会改变该函数依赖集的闭包
  - 形式化定义，考虑函数依赖 α → β：
    A在α中无关：如果A∈ α , 并且F ├(F-{ α → β})∪{( α-A)→ β}
    A在β 中无关：如果A∈β , 并且(F-{ α → β})∪{ α→(β-A)}├F
  - 核心：能够被函数依赖集F逻辑蕴涵的函数依赖不必在F中写明
  - 检验无关属性方法，考虑函数依赖 α → β:
    - 如果A∈ α , 令 γ= α-{A}，并计算 γ→ β是否可以由F推出，即计算在F下的 γ+，如果 γ+包含β的所有属性，则A在 α中是无关的
    - 如果A∈β , F’=(F-{ α → β}∪{ α →(β-A)} ,检验 α→A是否能由F’推出，即计算F’下的 α+，如果 α+包含A，则A在β中是无关的
```
检验α→β中属性A是否无关
-- 情况1：A∈α（检验α中是否无关）
γ = α - {A}
计算γ+（用属性集闭包算法）
若γ+包含β所有属性 → A在α中无关
-- 情况2：A∈β（检验β中是否无关）
F’ = (F - {α→β}) ∪ {α→(β - A)}
计算α+（基于F’ ，用属性集闭包算法）
若α+包含A → A在β中无关`
```
- 正则覆盖要求：（ F的正则覆盖记作Fc）
  - Fc与F等价
  - Fc中任何函数依赖都不含无关属性
  - Fc中函数依赖的左半部都是唯一的，即不存在两个依赖 α 1→ β1， α2→ β2满足 α 1= α2
- 求解正则覆盖方式：
  删除无关属性的函数依赖，合并左边相同的依赖，直至无法继续
  - 检查无关属性是在当前Fc中的函数依赖，而不是F。
  - 不能同时讨论F中的两个属性的无关性，一次只能讨论一个属性。
  - 正则覆盖未必唯一
- 最小覆盖Fm：（把正则覆盖中函数依赖右端拆成一个）
  不含无关属性且函数依赖右端属性只有一个
分解
- 关系模式R<U,F>的一个分解是指ρ= {R1/sub><U1,F1>,R2<U2,F2>,…,Rn<Un , F n >} 其中U=U1∪U2…∪Un，并且没有Ui ⊆U j ，1≤i，j ≤n
- 关系模式的分解是将R所有的属性分解到不同的子关系里面
- 分解的基本代数运算：投影和自然连接
- 分解的要求：
- 无损连接分解
  - 无损分解：模式分解以后进行自然连接，得到的关系依旧是原来的关系
  - 判断无损连接分解（假设关系模式R(U，F)，U = {A1,…,An}一个分解是ρ{R1,…,Rk}）
    - 快速法（充分条件）（分解后的关系模式只有两个）：
      - R1∩R2→R1或 R1∩R2→R2或 R1∩R2→R1-R2或 R1∩R2→R2-R1一个成立即可
    - 表格法（充分必要条件）：
      - 构造一张k行n列的表格，每列对应一个属性Aj(1≤j≤n)，每行对应一个模式Ri(1≤i≤k)。
      - 如果Aj在Ri中，那么在表格的第i行第j列处填上符号aj，否则填上符号bij。
      - 修改方法如下：对于F中函数依赖：a→b，如果表格中有多行在a分量上相等，在b分量上不相等，那么把这些行在b分量上改成相等。如果b的分量中有一个是aj，那么另一个也改成aj；如果没有aj，那么用其中的一个bij替换另一个(尽量把ij改成较小的数，亦即取i值较小的那个)。
      - 若在修改的过程中，发现表格中有一行全是a，即a1,a2,…,an，那么可立即断定分解相对于F是无损连接分解，此时不必再继续修改。若经过多次修改直到表格不能修改时，发现表格中不存在有一行全是a的情况，那么分解就是有损的。
- 保持函数依赖
  - 保持函数依赖：原有的函数依赖依旧保持
  - 判断是否保持：
```
Input： ρ = {R1,...,Rn}，F中的一个函数依赖α->β
Output：属性集
result = α
while (result 发生变化) do
for each 分解后的Ri
{
a = (result ∩ Ri)+  ∩ Ri//闭包求解依赖于全体函数依赖闭包
result = result ∪ a
}
//如果β⊆result，则保持函数依赖
```

多值依赖α, β, γ, δ
- 描述型定义：
  关系模式R(U)， α 、β 、γ⊆U，并且γ=U– α–β , 多值依赖 α→→β成立当且仅当对R(U)的任一关系r，给定的一对 ( α 1，β1）值，有一组β的值，这组值仅仅决定于 α值而与β值无关
- 如在关系模式TEACH中，对(c1, b1)有一组tno值(t1, t2)，对(c1, b2)也有一组tno值(t1, t2)，这组值仅取决于cno的取值，而与bno的取值无关。因此，tno多值依赖于cno，记作cno→→tno，同样有cno →→ b no
- 形式化定义：
  关系模式R(U)， α 、β 、γ⊆ U，并且γ=U– α–β , 对于R(U)的任一关系r，若存在元组t1，t2，使得t1[ α] = t2[ α]，那么就必然存在元组t3，t4，使得：
  t3[ α] = t4[ α] = t1[ α] = t2[ α] t3[β] = t1[β]， t3[ γ] = t2[ γ] t4[β] = t2[β]， t4[ γ] = t1[ γ]
- 性质：
  - 对称性：若 α→→β , 则 α→→ γ , 其中 γ=U– α–β
  - 函数依赖是多值依赖的特例，即：若 α→β , 则 α→→β
  - 平凡的多值依赖：若 α→→β且U– α–β=∅或β⊆ α
  - 传递性：若 α→→β , β→→ γ , 则 α→→ γ-β
  - 其他：若 α→→β , α→→ γ , 则 α→→β ∪ γ 、 α→→β ∩ γ 、 α→→β- γ , α→→γ-β
常见题目类型
- 判断属性集是否为超码： α+最终的结果集是否包含了所有的属性集，还有看看属性集到底几个元素，不要漏元素了
- 判断属性集是否是候选码：
  1. α+=U，且验证 α任意真子集的闭包不等于U
  2. （非万能方法）对于给定的关系R(U,F) ，可将其属性分为4类：
    - L类：仅出现在F的函数依赖左部的属性
    - R类：仅出现在F的函数依赖右部的属性
    - N类：在F的函数依赖两边均未出现的属性
    - LR类：在F的函数依赖两边均出现的属性
    - L和N类都一定是候选码一部分，R类一定不是
    - 推论：对于给定的关系模式R及其函数依赖集F，若 α( α⊆U)是L类和N类属性集，且 α+包含了U中的全部属性，则 α一定是R的唯一候选码
- 判断属性是否为无关属性
  - 把这个属性删去，看原本所在的函数依赖a→b
  - 如果属性在a，则看a+能不能推出b
  - 如果属性在b，则看a+是否包括该属性
- 正则覆盖的定义
- 计算正则覆盖
  - 删除无关属性的函数依赖，合并左边相同的依赖，直至无法继续
- 判断函数依赖集是否等价
  - 判断F+是否相等
  - 判断一个函数的正则覆盖是不是等于另一个函数的正则覆盖
- 判断无损连接分解
  
  →
- 判断保持函数依赖
  - 先分别看看分解的属性集各自支持了哪些函数依赖
  - 剩余不支持的，按照以下方法验证其是否成立
```
Input： ρ = {R1,...,Rn}，F中的一个函数依赖α->β
Output：属性集
result = α
while (result 发生变化) do
for each 分解后的Ri
{
a = (result ∩ Ri)+  ∩ Ri
result = result ∪ a
}
//如果β⊆result，则保持函数依赖
```
- 分解前先看看要不要分解
- 分解成3NF且保持函数依赖
```
输入：关系模式R(U,F)
输出：达到3NF且保持函数依赖的分解ρ
⒈计算F的正则覆盖FC
⒉若有α->β->FC ，且αβ = U，则算法终止
3.对FC按具有相同左部的原则进行分组(设为k组)，
每一组函数依赖所涉及的属性全体为Ui，
令Fi为FC在Ui上的投影，
则ρ = {R1<U1, F1> , … , R k <U k , F k>}是R<U , F>的一个保持函数依赖的分解，
并且每个Ri<Ui , Fi> ∈3NF
返回ρ
```
- 分解成3NF且保持函数依赖与无损连接分解
```
算法：3NF 合成算法（达到 3NF 且保持无损连接与函数依赖的分解）
输入：关系模式 R(U, F)
输出：达到 3NF 且同时保持无损连接与函数依赖的分解 τ

1. 生成保持函数依赖的 3NF 分解  
   得到 R(U, F) 的保持函数依赖的 3NF 分解：  
   ρ = { R₁(U₁, F₁), …, Rₖ(Uₖ, Fₖ) }  

2. 处理候选码，确保无损连接  
   设 α 为 R(U, F) 的任意候选码  
   检查是否存在分解后的 Uᵢ 满足 α ⊆ Uᵢ：  
   - 若存在：ρ 即为结果，τ = ρ  
   - 若不存在：补充包含候选码的关系模式 R*(α, Fα)，则 τ = ρ ∪ { R*(α, Fα) }  
																													   //Fα是F在α上的投影

3. 返回结果  
   输出最终分解 τ  
```
- 判断BCNF
  - 对于Ri上属性的所有子集α，检查α+，判断检查结果是否不包含Ri – α的任一属性(平凡的函数依赖)或者包含Ri的所有属性(超码)
  - 如果某个属性集α不满足条件，考虑如下的函数依赖，可以证明它出现在F+中：
    α→(α+ - α) ∩ Ri
    此函数依赖说明Ri违反了BCNF
- 分解成BCNF（只能产生无损连接分解，不一定保持函数依赖）
  - 拆分后，左右两侧有可能还能再拆
```
算法：(BCNF分解算法)
result := {R}；//初始时全体就是R
done := false；
while  (not done) do
if (result 中存在不属于BCNF的关系模式Ri)
then {
令α->β是Ri上的非平凡函数依赖，满足α->Ri不在F+中，//Ri不属于BCNF
且α∩β=空集；//无共同属性
result :=(result – Ri) ∪ (Ri - β) ∪ (α,β);
}
else done := true;
```
- 函数依赖和多值依赖的区别
  - 函数依赖规定某些元组不能出现在关系中，也称为相等产生依赖；多值依赖要求某种形
    式的其它元组必须在关系中，称为元组产生依赖。
  - X→Y 的有效性仅决定于 X、Y 属性集上的值；X→→Y 的有效性与属性集范围有关

第十二章物理存储系统

常见的存储介质
- ①高速缓冲存储器(Cache)
- ②主存储器(Main memory)
- ③快闪存储器 (Flash memory)
- ④光学存储器(CD-ROM/DVD)
- ⑤磁盘
- ⑥磁带存储器
- 注：①②都为易失性存储介质，cache和主存配合工作。 ③④⑤⑥都为非易失性存储介质，磁盘是主要的辅存，磁带主要用来脱机备份。
存储层次
- 基本存储：访问速度最快的存储介质，但是易失(cache, 主存) ，可以直接被cpu访问。
- 辅助存储：层次结构中基本存储介质的下一层介质, 非易失, 访问速度较快。如：闪存, 磁盘
- 第三级存储：层次结构中最底层的介质, 非易失，访问速度慢。如：磁带, 光学存储器

第十三章数据存储结构

文件组织和记录组织
- 逻辑层面：
  - 数据库被映射到多个不同的文件；一个文件在逻辑上组织成为记录的一个序列；一个记录是多个字段的序列；
    - 文件——基本表
    - 记录——元组
    - 字段——属性
- 物理层面：
  - 每个文件分成定长的存储单元，称作块（block），块是存储分配和数据传输的基本单元。（大多数数据库默认使用4-8KB的块，数据库允许修改块的大小）
  - 一个块包含很多记录，一个块包含的确切的记录集合是由使用的物理数据组织形式所决定的。
  - 一般假定没有记录比块更大，这个假定对于大多数数据处理应用都是现实的。
  - 要求每条记录包含在单个块中，这个限定简化并加速数据项访问。
  - 实例解读：其中每一行是一条记录，每个记录包含4个字段，这些记录组成一个文件。
- 定长记录
  - 实现方法：从字节n*(i–1)开始存储记录i, n是每个记录的长度
  - 缺点：访问记录很容易，但是记录可能会分布在不同的块上。
  - 解决方案：修改约束——不允许记录跨越块的边界 + 删除记录困难，删除记录所占的空间必须由文件的其他记录来填充，或者我们自己必须用一种方法标记删除的记录，使得它可以被忽略。核心思想是，使有效记录在逻辑上连续。
  - 具体方案：
    - 1、移动记录i + 1, . . ., n 到 i, . . . , n–1；
    - 2、移动记录n到i；
    - 3、不移动记录, 但是链接所有的空闲记录到一个 free list；
- 变长记录
  - 可变长度记录的几种方式
  - ①存储在一个文件中的记录有多个记录类型
  - ②记录类型允许记录中某些字段值的长度可变(如：varchar) 分槽的页结构
    - 分槽的页结构一般用于在块中组织记录。
    - 分槽页页头在每个块的块头（此处“ 页”=“块”）
      它的作用：记录条目的个数；记录块中空闲空间的末尾；维护一个包含每条记录位置和大小的条目组成的数组。
    - 可以将记录在一页内移动以保证记录之间没有空闲的空间，则数组中信息也要更新。
  - 实现：
    - 1、实际记录从块的尾部开始排列。
    - 2、块中空闲空间是连续的，在块头数组的最后一个条目和第一条记录之间。
    - 3、如果插入一条记录，在空闲的尾部给这条记录分配空间，并且将包含这条记录大小和位置的条目添加到块头中。
    - 4、如果一条记录被删除，它所占用的空间被释放，并且它的条目被设置成删除状态，块中被删除记录之前的记录被移动，是的由此删除产生的空闲空间被重用，并且所有的空闲空间在块头数组的最后一个条目和第一条记录之间。
- 大记录
  - 对于图片、音频等数据，这些数据比块大很多，可以使用blob和clob数据类型，大对象一般存储到一个特殊文件中，而不是与记录的其他属性存储在一起，然后一个指向该对象的指针存储到包含该大对象的记录中。
- 文件中记录的组织
  - 堆文件：一个记录可以放在文件中任何地方只要有有足够的空间。
  - 顺序文件：记录根据“搜索码”的值顺序存储。
  - 哈希文件：在每条记录的某些属性上计算一个哈希函数，哈希函数的结果确定了记录应放到文件的哪一块中。
  - 特殊的：在多表聚簇文件组织中一个文件可以存储多个不同关系的记录。
    动机：将相关记录存储在同一个块上，在做多表查询时减少I/O。
- 数据字典存储
  - 数据字典包含：
    - 关系的有关信息
      比如：关系的名字，每个关系中属性的名字、类型和长度，视图的名字和视图的定义，完整性约束。
    - 用户和账号信息，包括密码 + 统计和描述数据 + 文件的组织信息
      比如：关系的存储组织、关系的存储位置
    - 索引信息
- 数据缓冲区
  - 数据缓冲区设计的目的：数据库系统尽量减少磁盘和内存之间的数据块传输数量。可以在主存中保留尽可能多的块来减少磁盘访问次数。
  - 缓冲区：部分主存用于存储磁盘块的副本。
  - 缓冲区管理：负责在主存中分配缓冲区空间的子系统。
  - 缓冲区管理程序
    - 基本功能：
      - 当程序需要从磁盘中得到一个块时，调用缓冲区管理程序。
      - 如果这个块已经在缓冲区里，缓冲区管理程序返回这个块在主存中的地址。
      - 如果这个块不在缓冲区中，缓冲区管理程序为这个块在缓冲区中分配空间。如果缓冲区满了，按照某种算法替换（抛出）某些块，替换出的块如果被修改则需要写回磁盘。然后将这个块从磁盘中读到缓冲区中，并将这个块在主存中的地址返回给请求者。
    - 缓冲区替换策略
      - LRU策略——系统替换掉那些最近最少使用的块
        LRU的思想：用过去块访问模式来预测未来的访问查询已经是定义良好的访问模式（例：顺序扫描），数据库可以使用用户查询的信息来预测未来的访问。
        缺点，比如重复扫描例如: 通过嵌套循环计算 2 个关系 r 和 s 的连接
      - MRU策略——替换时替换最近最常使用的块
        被钉住的块：不允许写回磁盘的块。
        立即丢弃策略：一旦一个块中最后一个元组处理完毕，就命令缓冲区管理器释放这个块所占用的空间。
        块的强制写出：有些时候，尽管不需要一个块所占用的存储空间，但是也必须把这个块写回磁盘，这样的写操作称为块的强制写出。
        作用在于：主存的内容在系统崩溃时将丢失，而磁盘上的内容在系统崩溃时得以保留，块的强制写出能够保护数据。
        最近最常使用策略：
        系统必须把当前正在处理的块钉住。在块中最后一个元组处理完毕后，这个块就不再被钉住，成为最近最常使用的块，替换时替换最近最常使用的块。

第十四章索引

基本概念
- 搜索码 -用于在文件中查找记录的属性或属性集
  - 每个索引有一个搜索码(一个搜索码包含多个属性)
  - 索引文件由如下形式的记录（被称为索引项）组成 search-key(搜索码)+pointer
  - 一个表中如果有多个索引，就需要多个搜索码
- 索引文件通常远小于原始文件
- 两种基本的索引类型
  - 顺序索引(ordered index)：基于搜索码值的顺序排序
  - 散列索引(hash index)：基于将搜索码值平均分布到若干散列桶中，一个值所属的散列桶是由一个函数决定，该函数称为散列函数
- 索引的作用：加快访问所需数据的速度
- 索引评价指标
  - 能有效支持的访问类型
  - 访问时间
  - 插入时间
  - 删除时间
  - 空间开销
顺序索引
- 主索引（聚集索引）：
  - 包含记录的文件按照某个搜索码指定的顺序排序，那么该搜索码对应的索引称为主索引
  - 只能有一个主索引，不然无法排序
  - 主索引的搜索码常常是主码，但可以建立在非主码属性上
- 辅助索引（非聚集索引、二级索引）：、
  - 搜索码指定的顺序与文件中记录的物理顺序不同的索引被称为辅助索引
- 索引顺序文件：在搜索码上有聚集索引的文件
- 稠密索引：在稠密索引中，文件中的每个搜索码值都有一个索引项。
- 稀疏索引：在稀疏索引中，只为搜索码的某些值建立索引项。
  - 只有索引是聚集索引时才能使用稀疏索引。
  - 为了定位一个搜索码值为 K 的记录，我们需要:
    - 找到搜索码值 <K 的最大索引项
    - 从该索引项所指向的记录开始，沿着文件中的指针查找，直到找到所需
- 稀疏索引与稠密索引比较
  - 稀疏索引的优点：所占空间较小，插入和删除时所需的维护开销也较小
  - 稀疏索引的缺点：定位一条记录时，通常比稠密索引更慢
  - 为文件中的每个块建一个索引项的稀疏索引是一个很好的折中
多级索引
- 解决问题：如果主索引比较大，不能放在内存中，访问效率将变低。
- 解决方案：
  把主索引当做一个连续的文件保留在磁盘上，创建一个它之上的稀疏索引。
  - 外层索引–主索引上的稀疏索引
  - 内索层引–主索引文件
- 缺点：对文件进行更新删除操作时，各级索引都必须全部更新
索引更新
- 单级索引插入操作
  - 核心流程：先用插入记录的搜索码值检索索引，再根据索引类型（稠密 / 稀疏）执行不同逻辑。
  - 1. 稠密索引插入
    - 情况 1：搜索码值未在索引中→ 直接插入该搜索码值的索引项。
    - 情况 2：搜索码值已在索引中
      - 若索引项存储所有同搜索码值记录的指针：在索引项中新增一个指向新记录的指针。
      - 若索引项仅存储首记录指针：将新记录添加到同搜索码值记录的末尾，索引项指针不变。
  - 1. 稀疏索引插入
    - 前提：索引按 “每个数据块存储 1 个索引项” 策略构建。
    - 情况 1：插入操作未创建新数据块→ 索引不做任何修改。
    - 情况 2：插入操作创建了新数据块→ 将新块中第一个搜索码值插入索引项。
- 单级索引删除操作
  - 根据索引类型（稠密 / 稀疏）及记录唯一性，执行不同删除逻辑。
  - 1. 稠密索引删除
    - 情况 1：待删除记录是同搜索码值的唯一记录→ 从索引中删除该搜索码值的索引项。
    - 情况 2：待删除记录非唯一记录
      - 若索引项存储所有同搜索码值记录的指针：从索引项中删除指向该记录的指针。
      - 若索引项仅存储首记录指针：
        若待删除记录是首记录：索引项更新为指向下一条同搜索码值的记录。
        若待删除记录非首记录：索引项不变（仅删除数据记录）。
  - 1. 稀疏索引删除
    - 情况 1：索引中无待删除记录的搜索码值索引项→ 索引不做修改。
    - 情况 2：索引中存在对应索引项
      - 若待删除记录是同搜索码值的唯一记录：
        用下一个搜索码值的索引项替换当前索引项；
        若下一个搜索码值已有索引项，则直接删除当前索引项。
      - 若待删除记录非唯一记录：
        若索引项指向待删除记录（如首记录）：更新索引项指向同搜索码值的下一条记录。
- 多级索引的插入 / 删除
  - 核心逻辑：递归应用单级索引的插入 / 删除算法。
  - 示例：
    - 插入时，从顶层索引开始检索，若需修改某层索引项，同步触发下层索引的插入逻辑。
    - 删除时，从顶层索引定位到数据记录，删除后若某层索引项变为冗余（如稀疏索引中块首记录被删），则递归更新上层索引。
复合索引（多码索引、组合索引）
- 一个包含多个属性的搜索码称为复合搜索码。
- 这个索引结构和其他结构不同的是搜索码是一个列表，这个搜索码可以表示为形如（a1, … ,an）的一组值，其中a1, … ,an是索引属性。索引码值按照字典顺序排序。
- 最左匹配原则
  - 即最左优先，在检索数据时从复合索引的最左边开始匹配
  - 示例：对列col1、列col2和列col3建一个复合索引，相当于建立了(col1)、(col1,col2)、(col,col2,col3)三个索引
```
select  *
from test
where col1 = ‘1’ and col2 = ‘2’ and col4 =  ‘4’
//上述查询会用到索引(col1,col2)进行数据匹配
```
**B+**树索引文件
- B+树索引文件是索引顺序文件的一种替代
- 为什么替代？
  - 索引顺序文件的缺点（主要在结构方面）：
    - 随着文件的增大，由于许多溢出块会被创建，索引查找性能和数据顺序扫描性能都会下降。
    - 插入和删除时，频繁重组整个文件。
  - **+B+**树索引文件的优点：
    - 在数据插入和删除时，能够通过小的自动调整来保持平衡。
    - 不需要重组文件来维持性能。
  - **B+**树索引文件的缺点：
    - 增加文件插入和删除的时间开销，同时会增加空间开销。这是因为插入和删除可能会引发B+树的调整，并且树形结构比线性存储需要更大的空间。
  - 总结：相较于顺序存储时频繁地调整整个文件，B+树只需要在局部调整以维持平衡，并且B+树查找每个叶节点的性能非常稳定。所以我们可以接受一定的时间和空间开销，使用这种数据结构。
- **B+**树的结构
  - 基本特征
  - 从根结点到叶结点的所有路径长度是一致的。
  - 每一个非根且非叶结点有n/2到n个孩子结点。
  - 一个叶结点有(n–1)/2到n–1 个值。
  - 特殊的：如果根结点是非叶结点，它至少有两个孩子结点；如果根结点是一个叶结点(也就是说，树中没有其他结点) ，它可以有0到(n - 1)个值
- **B+**树结点结构
  - 结构特征：
    - Ki 是搜索码值。
    - Pi是指向孩子结点的指针(对于非叶结点)或者指向记录或记录桶的指针(对于叶子结点) 。
    - 顺序特征：结点中的搜索码是有序的。 K1 < K2 < K3 < . . . < K n–1(假设目前没有重复的码值)
  - **B+**树的叶子结点
    - 指针Pi（ i = 1, 2, . . ., n–1）指向搜索码值为Ki的文件记录
    - 如果 Li ，L j 是叶结点并且i < j，Li的搜索码值小于或等于Lj的搜索码值
    - P n 指向按搜索码排序的下一个叶结点（因此有Pn，没有Kn）
  - **B+**树的非叶结点
    - 非叶结点形成叶结点上的一个多级稀疏索引，对于一个包含m个指针的非叶结点：
      - P1指针所指子树上的所有搜索码值小于K1
      - 对2 <= i <= n–1，Pi指针所指子树上的所有搜索码值大于或等于Ki–1且小于Ki
      - Pn指针所指子树上的所有搜索码值大于或等于kn–1
- **B+**树的特性
  - 由于结点间通过指针进行连接，逻辑上邻近的块在物理上不一定邻近。
  - B+树的一层非叶结点形成一级稀疏索引。
  - B+树每层的数值的个数有如下特点：如果在文件中有K个搜索码值，树的高度不超过log┌n/2┐(K) 从而可以有效地进行检索，可以高效地对主文件进行插入和删除操作，并且索引可以在对数时间内重构。
- **B+**树的查询、插入和删除
  - 找到搜索码值将要插入的叶结点
  - 如果在此叶结点中搜索码值已经存在
    - 向文件中添加新记录
    - 如果必要的话，添加一个指针到桶中
  - 如果搜索码值不存在
    - 将记录添加到主文件 (如果必要的话，创建一个新桶)
    - 如果在此叶结点中有空间，插入键值对 (key-value, pointer) 到叶结点中
    - 否则，分裂结点
散列索引
- 散列不仅可以用于文件的组织，还可以用于索引结构的创建
- 散列索引将搜索码及其相应的指针组织成散列文件结构
- 散列索引只是一种辅助索引结构
  - 如果一个文件自身是按散列组织的，就不必在其上另外建立一个独立的索引结构
  - 使用散列索引来表示散列文件结构，同时也用它表示辅助散列索引
- 动态散列：能够适应数据库增长和收缩的需要，允许散列函数动态改变
  - 可扩充散列——动态散列的一种形式

SQL

索引创建

操作类型 语法 注意事项 示例
普通索引（单字段） CREATE INDEX idx_name ON table_name (column_name); - 字段值非唯一时创建普通索引
- 避免在频繁更新的字段创建索引（影响写入性能） | CREATE INDEX idx_emp_sal ON employees (salary); |
  | 唯一索引（UNIQUE） | CREATE UNIQUE INDEX idx_name ON table_name (column_name); | - 确保字段值唯一，重复值无法插入
- 主键自动创建唯一索引 | CREATE UNIQUE INDEX idx_emp_email ON employees (email); |
  | 组合索引（复合） | CREATE INDEX idx_name ON table_name (col1, col2, col3); | - 遵循 “最左前缀” 原则，查询条件需包含 col1 才会命中索引
- 组合字段顺序影响效率 | CREATE INDEX idx_emp_dept_sal ON employees (dept_id, salary); |

操作类型	语法	注意事项	示例
普通索引（单字段）	`CREATE INDEX idx_name ON table_name (column_name);`	- 字段值非唯一时创建普通索引

索引删除

操作类型	语法	注意事项	示例
删除普通索引	`DROP INDEX idx_name ON table_name;`	- 删除后查询若依赖该索引，性能可能下降- 释放索引占用的存储空间	`DROP INDEX idx_emp_sal ON employees;`
删除唯一索引	`DROP INDEX idx_name ON table_name;`	- 唯一约束需通过 ALTER TABLE 删除（如`ALTER TABLE table_name DROP UNIQUE idx_name;`）	`ALTER TABLE employees DROP UNIQUE idx_emp_email;`

位图索引(bitmap index)
- 位图索引主要针对列中大量相同值的列而创建，比如“性别”列，“职称”列等
  - 为了使用位图索引，关系中的记录必须按顺序编号，比如说从 0 开始。
  - 对于给定的一个n值，必须能很简单地检索到编号为n的记录
- 位图就是位的一个简单数组
- 关系r的属性A上的位图索引是由A能取的每个值建立的位图构成的
- 每个位图都有和关系中的记录数相等数目的位
- 作用
  - 查询：针对多个码上的选择操作，通过位图的逻辑交运算等逻辑运算快速找到
  - 统计元组数量：只需要访问位图，不需要访问关系

题目

对于给定SQL语句创建什么样的索引

SQL 场景	最优索引方案	说明
`WHERE a=1`	`CREATE INDEX idx_a ON table(a);`	单列索引，直接匹配条件列。
`WHERE a=1 AND b=1`	`CREATE INDEX idx_a_b ON table(a, b);`	复合索引，顺序为查询条件的列顺序。
`WHERE a=1 AND b>10 AND c=1`	`CREATE INDEX idx_a_c_b ON table(a, c, b);`	将范围查询的列（`b`）放在最后，确保前两列可利用索引。
`WHERE a IN (1,2,3) AND b=1`	`CREATE INDEX idx_a_b ON table(a, b);`	复合索引，`IN` 操作符视为等值查询。
`ORDER BY a ASC`	`CREATE INDEX idx_a ON table(a);`	索引顺序与排序顺序一致，避免文件排序。
`ORDER BY a ASC, b ASC`	`CREATE INDEX idx_a_b ON table(a, b);`	复合索引顺序与排序顺序一致。
`WHERE a=1 ORDER BY b`	`CREATE INDEX idx_a_b ON table(a, b);`	复合索引同时支持查询条件和排序。

给定查询条件，计算查询代价

第十五章查询处理

基本步骤
- 解析与翻译
  - 语法分析器检查语法，验证关系。
  - 把查询语句翻译成系统的内部表示形式，也就是翻译成关系代数。
- 执行
  - 查询执行引擎接收一个查询执行计划，执行该计划并把结果返回给查询。
- 优化
  - 关于优化的几个重要概念
    - 一个关系代数表达式可能有许多等价的表达式。
    - 可以用多种不同的算法来执行每个关系代数运算。
    - 用于执行一个查询的原语操作序列（不可分割的一次操作）称为查询执行计划。
  - 查询优化
    - 使用来自数据库目录的统计信息来评估代价。
    - 在所有等效执行计划中选择具有最小查询执行代价的计划。
查询代价的度量
- 在磁盘上存取数据的代价通常是主要代价。
  通过以下指标来对其进行度量：
  - 搜索磁盘次数 * 平均寻道时间
  - 读取的块数 * 平均块读取时间
  - 写入的块数 * 平均块写入时间
- 只用传输磁盘块数以及搜索磁盘次数来度量查询计算计划的代价：
  - t T ：传输一个块的时间
  - t S ：磁盘平均访问时间（磁盘搜索时间+旋转延迟）
  - 传输b个块以及执行s次磁盘搜索的操作代价： b * t T + s * t S
- 忽略 CPU 时间。
- 没有包括将操作的最终结果写回磁盘的代价。
关系代数运算的执行
- 选择运算
  - A1 线性搜索
    - 搜索方法：系统扫描每一个文件块，对所有记录都进行测试，看它们是否满足选择条件。
    - 线性搜索可以普遍应用于各种情况，不论记录是否有序，不论是否存在索引。
    - 时间代价分析：
      - 开始时需要做一次磁盘搜索来访问文件的第一个块，如果文件的块不是顺序存放的，也许需要更多的磁盘搜索，为了简化起见，我们忽略了这种情况。因此时间代价由br次磁盘块传输和1次磁盘搜索产生。
        时间代价：Cost=b r *t T + t S
      - 特别的：对作用在候选码属性上的选择操作来说，系统在找到所需记录以后可以立即停止。
        因此时间代价的期望为 [(b r /2) 次磁盘块传输 + 1 次磁盘搜索] 。
  - 索引扫描：使用索引的搜索算法
    - 选择条件必须是建立索引的搜索码。
    - A2主索引，码属性等值比较
      - 对于具有主索引的码属性的等值比较，我们可以使用索引检索到满足相应等值条件的唯一一条记录。
      - 时间代价分析：
        索引使用B+树结构（hi是B+树的高度），索引查找需要从树根到叶节点，再加一次I/O取记录，每个这样的I/O操作需要一次搜索和一次块传输。
        时间代价：Cost = (hi + 1) * (t T + t S)
    - A3 主索引，非码属性等值比较
      - 因为是非码属性，所以允许重复，有可能检索多条记录。
      - 时间代价：
      - 时间代价分析：
        因为我们为搜索码建立了主索引，所以它们在文件中的记录是有序的，我们搜索的目标在文件中一定是连续存储的。B+树的每层都有一次搜索和传输，在B+树中找到满足条件的第一个索引后，需要在磁盘上根据这个索引搜索第一个块，b是包含具有指定搜索码的块数（假定这些块是顺序存储的叶子块，并且不需要额外搜索），因此我们需要传输b个块。
        Cost = hi * (t T + t S) + t S + t T * b
    - A4 辅助索引，等值比较
      - 如果等值条件是码属性上的，该策略可以检索到满足条件的一条记录
      - 时间代价分析：
        索引查找穿越树的高度，再加一次I/O取记录，每个这样的I/O操作需要一次搜索和一次块传输。
        时间代价：Cost = (hi + 1) * (t T + t S)
      - 若索引字段是非码属性，则可检索到多条记录
      - 时间代价分析：
        因为在非码属性上建立辅助索引，文件中记录的顺序和搜索码指定的顺序不同，所以满足条件的n个匹配的记录可能在不同的磁盘块中，这需要每条记录一次搜索和传输。索引查找穿越树的高度，再加n次I/O取记录。
        时间代价：Cost = (hi + n) * (t T +t S)
    - A5 主索引，比较
      - 建立主索引的文件记录是按搜索码的顺序排序的，对于σ A>=V® ，使用索引找到>=v的第一个元组，从这里开始顺序扫描关系。
      - 时间代价分析：
        树的每层一次搜索，第一个块的搜索，b是包含具有指定搜索码的块数，假定这些块是顺序存储的叶子块，并且不需要额外搜索。
        时间代价：Cost = hi * (t T + t S) + t S + t T * b
      - 特别的：对于σA<=V® ，只是顺序扫描关系找到>v的第一个元组，因为文件记录按搜索码的顺序排列，所以不使用索引，以节省B+树搜索的开销。
    - A6 辅助索引，比较
      - 对于σA>=V® ，使用索引找到第一个>=v的索引项，从这里开始依次扫描索引，找到指向记录的指针。对于σA<=V® ，只需要扫描索引的叶子页来找到指针，直到找到第一个>v的索引项。因为辅助索引文件记录无序，所以必须在B+树的叶节点中搜索，不能直接在文件中搜索。
      - 时间代价分析：
        n 是所取记录数，但是每条记录可能在不同的块上，这需要每条记录一次搜索。如果n比较大，查询代价非常大。
        时间代价：Cost = (hi + n) * (t T + t S)
- 连接运算
  - 在实际应用中，我们要根据代价估算来选择合适的连接。
  - 使用下面的信息作为例子：
    记录数（n）: Student-5,000 s c - 10,000
    磁盘块数（b）: Student-100 sc-400
  - 嵌套循环连接
```
for each 元组 tr in r do begin
 for each 元组 ts in s do begin
  测试元组对 (tr,ts) 是否满足连接条件θ
   如果满足，把 tr • ts 加到结果中 
 end 
end
```
    - r称为连接的外层关系，而s称为连接的内层关系。
    - 无需索引，并且不管连接条件是什么。
    - 代价很大，因为算法逐个检查两个关系中的每一对元组AθB
    - 代价分析：
    - 在最坏的情况下，缓冲区只能容纳每个关系的一个数据块，这时共需 n r *b s + b r 次块传输nr+ b r 次磁盘搜索
    - 如果较小的关系能被放入内存 中，使用它作为内层关系，这时共需br+ bs 次块传输、2 次磁盘搜索
      - *最坏的可用内存情况下的成本估算，用student 作为外层关系:
        5000**400 + 100 =2,000,100 次块传输
        5000 + 100 = 5100 次磁盘搜索
      - 最坏的可用内存情况下的成本估算，用 s c 作为外层关系:
        10000*100 + 400 = 1,000,400 次块传输
        10,400 次磁盘搜索
  - 块嵌套循环连接
```
for each 块 Br of r do begin
 for each 块 Bs of s do begin
  for each 元组 tr in Br do begin
   for each 元组 ts in Bs do begin
    测试元组对 (tr,ts) 是否满足连接条件θ
     如果满足，把 tr • ts 加入到结果中
   end 
  end 
 end 
end
```
    - 嵌套循环连接的优化
    - 其中内层关系的每一块与外层关系的每一块对应，形成块对，在每一个块对中，一个块的每一个元组与另一个块的每一个元组形成组对，从而得到全体组对。
    - 分析：在块嵌套循环连接中外层关系中的一个元组与内层关系的当前块的每一个元组比较完之后，并没有像嵌套循环连接一样立即将内层关系的当前块置换出去，而是用外层关系的当前块的每一个元组都与它比较之后在将其置换出去。这样对于外层关系中的每一个块，内层关系的每一块只需读取一次，不需要对每一个元组读一次，明显减少了缓冲区置换的次数
    - 代价分析：
      - 最坏情况 b r*bs + br次块传输，2 * b r 次磁盘搜索
      - 最好情况 b r + bs 次块传输，2 次磁盘搜索
      - 最坏的可用内存情况下的成本估算，用student作为外层关系:
        块传输：100400+100=40100
        块搜索：2100=200
  - 补充：改进嵌套循环与块嵌套循环算法
    - 在块嵌套循环中，如果内存中有M块，使用M - 2个磁盘块作为外层关系的块单元；使用剩余的两个块作为内层关系和输出的缓冲区。
    - Cost = ┌b r/ (M-2)┐*bs + br次块传输 + 2┌br / (M-2)┐次磁盘搜索
    - 如果等值连接中的连接属性是内层关系的码，则对每个外层关系元组，内层循环一旦找到了首条匹配元组就可以终止。
    - 使用缓冲区的剩余块，对内层循环轮流做向前、向后的扫描（使用 LRU 替换策略)。
    - 若内层循环连接属性上有索引，可以用更有效的索引查找法替代文件扫描法。
  - 索引循环嵌套连接
    - 适用场景：当连接是自然连接或等值连接，并且内层关系的连接属性上存在可用索引时，索引查找法可以替代文件扫描法。
    - 对于外层关系r的每一个元组tr，可以利用索引查找满足与tr的连接条件的s中的元组。
    - 代价分析：
      - 最坏的情况：缓冲区只能容纳关系r的一块和索引的一块，对于外层关系r的每一个元组，需要对关系s进行索引查找。
      - 连接的时间代价：br (t T + t S) + nr* c （c 是使用连接条件对关系 s 进行单次选择操作的代价）
    - 如果两个关系r和s上均有索引时，一般把元组较少的关系作外层关系时效果较好。因为外层关系决定了搜索次数。
  - 归并连接
    - 在连接属性上对全部关系进行排序(如果之前并非有序的)
    - 可用于计算自然连接和等值连接
    - 每个块只需被读取一次 (假设所有连接属性是给定值的元组都被装入内存）
    - 归并连接的代价
      - b r + bs 次块传输 + ┌b r / bb┐ + ┌bs / bb┐ 次磁盘搜索 + 排序代价（如果关系未被排序），bb是为每个关系分配的缓冲块数量
    - 混合归并-连接: 如果一个关系已排序，并且另一关系有一个连接属性上的 B+ 树辅助索引
      - 把已排序关系和另一个关系的 B+ 树辅助索引叶结点进行归并
      - 将该文件按照未排序关系元组的地址进行排序
      - 从而能够对相关元组按照物理存储顺序进行有效的检索，最终完成连接操作
      - 顺序扫描比随机查找更有效
  - 散列连接
    - 适用于等值连接和自然连接
    - 用同一个散列函数 h 来划分两个关系的元组
    - 关系 r i 中的元组 r 只需要与关系 s i 中的元组 s 相比较，而没有必要与其他任何划分里的元组 s 相比较
      - 如果一个 r 元组和一个 s 元组满足连接条件，那么它们在连接属性上就会有相同的值
      - 如果该值经散列函数映射到 i ,则关系 r 的那个元组必在 r i 中，而关系 s 的那个元组必在 s i 中
两种计算完整表达式树的方法
- 目前只研究了单个关系运算如何执行，下面讨论如何计算包括多个运算的表达式。计算一个完整表达式树的两种方法：
- 物化计算：
  - 输入一个关系或者已完成的计算，产生一个表达式的结果，在磁盘中物化它，重复该过程。（可以把物化理解为创建一个确实存在的临时关系）
  - 概述：从最底层开始，执行树中的运算，对运算的每个中间结果创建文件，然后用于下一层运算。
  - 特点：
    - 任何情况下，物化计算都是永远适用的。
    - 将结果写入磁盘和读取它们的代价是非常大的。
- 流水线：
  - 一个正在执行的操作的部分结果传送到流水线的下一个操作，使得两操作可同时进行。
  - 概述：同时执行多个操作，一个操作的结果传递到下一个，不储存中间结果。
  - 特点：
    - 流水线并不总是可行的，比如需要排序的归并连接和产生配对的散列链接。
    - 比实体化代价小很多。
    - 对于有效流水线，当作为输入的元组被接收时，立即使用计算算法得到输出元组。

第十六章查询优化

概述
- 查询优化就是从多个可能的策略中，找出最有效的查询执行计划的一种处理过程。
  - 优化一方面可以在关系代数级别发生；
  - 另一方面是为处理查询选择一个详细的策略，比如执行算法、选择索引等。
- 实例：
- ∏name ,title(σdname =‘music’(instructor⋈ (teaches ⋈ ∏ course id ,title(course))))左侧的表达式树将产生很大的中间关系，instructor ⋈ (teaches ⋈ ∏ course id ,title(course) ，但是我们只对music学院的教师感兴趣，因此，优化后的表达式树变成右侧的。
- 基于代价的优化步骤
  - 使用等价规则产生逻辑上的等价表达式
  - 注解结果表达式来得到替代查询计划
  - 基于代价估计选择代价最小的计划（估计的根据是系统中的各种统计信息）
关系表达式的转换
- 等价关系表达式：如果两个关系代数表达式在所有有效数据库实例中都会产生相同的元组集，则称它们是等价的。（元组顺序无关紧要）
- 等价规则
  - 合取选择运算可以被分解为单个选择运算的序列
  - 选择运算满足交换律
  - 一系列投影中只有最后一个运算是必需的，其余的可省略
  - 选择操作可与笛卡尔积以及θ连接相结合 σθ(E1 XE2) = E1 ⋈θE2 θ连接运算满足交换律 E1 ⋈θE2 = E2 ⋈θE1
  - 自然连接运算满足结合律 (E1 ⋈E2) ⋈E3 = E1 ⋈(E2 ⋈E3)
  - θ连接具有以下方式的结合律 (E1 ⋈θ1 E2) ⋈θ2^θ3 E3 = E1 ⋈θ1^θ3 (E2 ⋈θ2 E3) 选择运算在下面两个条件下对θ连接运算具有分配率
    - 当选择条件θ0的所有属性只涉及参与连接运算的表达式之一 (比如 E1)时，满足分配率： σθ0(E1 ⋈θE2) = (σθ0E1)) ⋈θE2
    - 当选择条件θ1只涉及E1的属性，选择条件θ2只涉及E2的属性时，满足分配率： σθ1^θ2(E1 ⋈θE2) = (σθ1(E1)) ⋈θ(σθ2(E2)) 投影运算在下列条件下对θ连接运算具有分配率
  - 假设连接条件θ只涉及L1∪L2中的属性 ∏L1∪L2(E1 ⋈θE2) = (∏L1(E1)) ⋈θ(∏L2(E2))
  - 考虑连接 E1 ⋈θE2 令L1和L2分别代表 E1 和 E2 的属性集令L3是E1中出现在连接条件θ中但不在L1∪L2中的属性令L4是E2中出现在连接条件θ中但不在L1∪L2中的属性 ∏L1∪L2(E1 ⋈θE2) = ∏L1 ∪L2((∏L1 ∪L3( E1)) ⋈θ(∏L2 ∪L4(E2)))
  - 集合的并与交满足交换律 E1∪E2 = E2∪E1 E1∩E2 = E2∩E1
  - 集合的并与交满足结合律 (E1∪E2) ∪E3 = E1∪(E2∪E3) (E1∩E2)∩E3 = E1∩(E2∩E3)
  - 选择运算对∪, ∩和–运算具有分配率 σθ(E1 – E2) =σθ(E1)–σθ(E2) 上述规则将“–”替换成∪或∩时也成立。 σθ(E1 – E2) = σθ(E1) – E2 上述规则将“–”替换成∩时成立，替换成∪时不成立
  - 投影运算对并运算具有分配率 ∏L(E1∪E2) = (∏L(E1))∪(∏L(E2)) 。
- 等价规则使用的原则
  - 尽可能早地执行选择操作以减小被连接的关系的大小。尽可能早地执行投影操作以减小被连接的关系的大小。
  - 在多重连接中，尽量把产生较小结果的连接放在前面以产生较小的临时关系。
表达式结果集统计大小的估计
- 一个操作的代价依赖于它的输入的大小和其他统计信息。
- 统计信息 + 目录信息：
  - nr:关系r的元组数
  - br:包含关系r中元组的磁盘块数
  - lr:关系r中每个元组的字节数
  - fr:关系r的块因子，一个磁盘块能容纳的关系r中元组的个数
  - V(A, r):关系r中属性A中出现的非重复值个数，该值与A®的大小相同
  - 假设关系r的元组物理上存储于一个文件中，则下面的等式成立：
- 直方图
  - 大多数数据库将将每个属性的取值分布另存为一张直方图，如果没有直方图信息，优化器将假设数据分布是均匀的。
  - 注：一个直方图只占用很少的空间，因此不同的属性上的直方图可以存储在系统目录里。
  - 等宽直方图：把取值范围分成相等大小的区间。
  - 等深直方图：调整区间分解，以使落入每个区间的取值个数相等。
- 估计方法
  - σA=v®
    - nr / V(A ，r) : 满足选择的记录数
  - σA<=V® + c 表示满足条件的元组的估计数
    - 如果 min(A ,r) 和 max(A ,r) 可存储到目录上，当v小于记录的最小值时，c为0；当v大于记录的最大值时，c为nr 。否则c均匀分布
    - 注：如果存在直方图，可以得到更精确的估计。
    - 不存在统计信息时，c 被假设为 nr / 2
- 选中率
  - 条件θi的选中率是关系r上一个元组满足θi的概率。
  - 合取σθ1∧θ2∧ . . . ∧θn ®
  - 析取σθ1∨θ2∨ … ∨θn ®
  - 取反σ┐θ®
    - nr–size(σθ®)
- 连接运算
  - 笛卡尔积r x s包含nr .ns个元组，每个元组占用sr + ss个字节。
  - 若R∩S =∅ , 则r ⋈s 与r x s结果一样。
  - 若R∩S是R的码，则可知s的一个元组至多与r的一个元组相连接。
    - 因此，r ⋈s的元组数不会超过s 元组的数目。
  - 若R∩S构成了S中参照 R的外码，r ⋈ 中的元组数正好与s中的元组数相等。
  - 若R∩S既不是R的码也不是S的码，假定每个值等概率出现。
  - 注：上述估计是在各个值等概率出现的这一假设前提下做出的，如果这个假设不成立，则必须使用更发杂的估算方法。直方图可以改善上述结果，如果两个直方图有相似的区间，可以在每个区间中使用上述估计方法。
执行计划选择
- 理论与实际结合
  - 当选择执行计划时，必须考虑执行技术的相互作用。为每个操作独立地选择代价最小的算法可能不会产生最佳的整体算法。
  - 实际的查询优化器合并了以下两大方法中的元素：
    - 搜索所有的计划，基于代价选择最佳的计划
      - 基本理念：从给定查询等价的所有查询计划执行空间进行搜索，并选择估计代价最小的一个。
      - 缺点
        对于复杂查询来说，搜索整个可能的空间代价太高。考虑为表达式 r1 ⋈r2 ⋈ . . . ⋈ rn寻找最佳连接顺序，该表达式有 (2(n–1))!/(n–1)! 个不同的连接顺序，对于n = 7, 此数变为 665280, 对于 n = 10, 此数大于 176 亿!
    - 使用启发式方法选择计划基于代价的优化
      - 基本理念：
      - 启发式优化通过使用一系列规则转化查询树，这通常能改善执行性能。
        尽早执行选择运算 (减少元组数目) 。
        尽早执行投影运算 (减少属性数目) 。
        在其他类似运算之前，执行能对关系进行最大限制的选择和投影运算（例如，能得到最少的结果的运算）
- 查询优化器
  - 许多优化器只考虑左深连接树顺序，使用启发式规则在查询树中对选择和投影进行下推，减少优化的复杂性，生成适合流水线执行的计划。
  - 一些查询优化器整合启发式选择和替代访问方法的生成。
  - 常用方法：
    - 启发式重写嵌套块结构和聚集
    - 对每个块通过基于代价的连接顺序进行优化
    - 如果优化待价甚至比计划代价还要高，提早停止优化的优化代价预算。
    - 重用以前的计算计划的计划缓存，查询在短时间内被重新提交，节省反复优化的开销。

第十七章事务

事务定义
- 事务是访问并可能更新各种数据项的一个程序执行单元。简单讲，这些操作要么都做，要么都不做，是一个不可分割的工作单位。
- 比如现实生活中的银行转账，要么转账成功，要么没有转账，不可能出现转出去但对方没收到的情况。
- 注： SQL中事务的定义：
  - Commit work表示提交，事务正常结束。
  - Rollback work表示事务非正常结束，撤消事务已完成的操作，回滚到事务开始时状态。
事务特性（ACID）
- 原子性：
  - 事务中包含的所有操作要么全做，要么全不做。
  - 注：破坏一致性的情况往往发生在系统故障时，会出现事务执行到一半被中断的情况，所以原子性由恢复系统实现。
- 一致性：
  - 事务的隔离执行必须保证数据库的一致性。
  - 一致性是指，事务开始前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态；事务的执行过程中可以暂时的不一致。
  - 在串行调度的情况下，事务一个接一个地执行，每条事务都按照顺序不受干扰的执行完。然而在并发调度时，很可能两个事务交替访问一个数据项，此时如不加控制，极有可能破坏一致性。
  - 因此，数据库的一致性状态由用户来负责，由并发控制系统实现。
- 隔离性：
  - 系统必须保证事务不受其它并发执行事务的影响。
  - 对任何一对事务T1，T2，在T1看来，T2要么在T1开始之前已经结束，要么在T1完成之后再开始执行。
  - 隔离性的强弱可以根据需要变动，隔离性通过并发控制系统实现。
- 持久性：
  - 一个事务一旦提交之后，它对数据库的影响必须是永久的。
  - 要保证系统发生故障不能改变事务的持久性，因此持久性通过恢复系统实现。
- 注：可见并发控制系统和恢复系统是保证事务特性的两大重要工具，随后会有针对这两个系统的详细介绍。
事务的读和写
- read(X)：从数据库把数据项X传送到执行read操作的事务的局部缓冲区(缓冲区在主存中)
- Write(X)：从执行write操作的事务的局部缓冲区把数据项X传回数据库（可能暂时存在内存，批量写入磁盘）
并行与串行
- 基本比较
  - 并行事务会破坏数据库的一致性。
  - 串行事务效率低。
- 并行的优点
  - 一个事务由不同的步骤组成，所涉及的系统资源也不同。这些步骤可以并发执行，以提高系统的吞吐量(throughput) 。
  - 系统中存在着周期不等的各种事务，串行会导致难于预测的延迟。如果各个事务所涉及的是数据库的不同部分，采用并行会减少平均响应时间(average response time)。
- 核心问题：在保证一致性的前提下最大限度地提高并发度。
- 并发操作面临的问题
  - 丢失修改（lost update）：丢失修改是指事务1与事务2从数据库中读入同一数据并修改，事务2的提交结果破坏了事务1提交的结果，导致事务1的修改被丢失。
  - 不可重复读（non-repeatable read）：不可重复读是指事务1读取数据后，事务2执行更新操作，使事务1无法再现前一次读取结果。
    - 注：事务1读取某一数据后，可能的三类不可重复读：
      - 事务2对其做了修改，当事务1再次读该数据时，得到与前一次不同的值。
      - 事务2删除了其中部分记录，当事务1再次读取数据时，发现某些记录神密地消失了。
      - 事务2插入了一些记录，当事务1再次按相同条件读取数据时，发现多了一些记录。
      - 注：后两种不可重复读有时也称为幻影现象。
  - 读“脏”数据（dirty read）：事务1修改某一数据，并将其写回磁盘，事务2读取同一数据后，事务1由于某种原因被撤消，这时事务1已修改过的数据恢复原值，事务2读到的数据就与数据库中的数据不一致，是不正确的数据，又称为“脏”数据。
事务调度(Transaction schedule)
- 事务的执行顺序称为一个调度，表示事务的指令在系统中执行的时间顺序。
- 一组事务的调度必须保证：
  - 包含所有事务的操作指令；
  - 一个事务中指令的顺序必须保持不变。（完整且有序）
- 事务调度的两种模式
  - 串行调度
    - 在串行调度中，属于同一事务的指令紧挨在一起。
    - 能保证事务的特性，但是极大的牺牲了系统的效率。
  - 并行调度
    - 在并行调度中，来自不同事务的指令可以交叉执行。
    - 不一定能保证事务的特性，当并行调度等价于某个串行调度时，则称它是正确的。
- 数据库系统的调度应该保证任何调度执行后数据库总处于一致状态。
- 通过保证任何调度执行的效果与没有并发执行的调度执行效果一样，可以保证数据库的一致性。
- 并行调度的原则：并行调度应该在某种意义上等价于一个串行调度。
冲突可串行化
- 指令顺序：
- 考虑一个调S中的两条连续指令（仅限read与 write操作）Ii与Ij，分别属于事务Ti与Tj
```
Ii = read(Q), Ij = read(Q); //①
Ii = read(Q), Ij = write(Q); //②
Ii = write(Q), Ij = read(Q); //③
Ii = write(Q), Ij = write(Q);//④
```
  - 除了①的情况下，事务Ii与Ij顺序无关紧要。其余情况下，Ii与Ij的次序不同，其执行结果也不同，数据库最终状态也不同。
- 冲突指令：当两条指令是不同事务在相同数据项上的操作，并且其中至少有一个是write指令时，则称这两条指令是冲突的。
  - 如在②、③、④情况下，Ii与Ij 是冲突的。
- 特别的：非冲突指令交换次序不会影响调度的最终结果。
- 冲突等价：如果调度S可以经过一系列非冲突指令交换转换成调度S’ ，则称调度S与S’是冲突等价的(conflict equivalent) 。
  - 存在结果相同，但非冲突等价的调度
- 冲突可串行化：当一个调度S 与一个串行调度冲突等价时，则称该调度S是冲突可串行化的(conflict serializable)。
- 冲突可串行化的判定
  - 优先图构造方法：
    一个调度S的优先图是这样构造的：它是一个有向图G =（V，E），V是顶点集，E是边集。顶点集由所有参与调度的事务组成，边集由满足下述条件之一的边Ti→Tj组成：
    - ①在Tj执行read(Q)之前，Ti执行write(Q)
    - ②在Tj执行write(Q)之前，Ti执行read(Q)
    - ③在Tj执行write(Q)之前，Ti执行write(Q)
    - 非两个事务都执行read，否则都形成一条边。
  - 并行转串行准则：如果优先图中存在边Ti→Tj ，则在任何等价于S的串行调度S’ 中，Ti都必须出现在Tj之前。
  - 冲突可串行化判定准则：如果调度S的优先图中有环，则调度S是非冲突可串行化的。如果图中无环，则调度S是冲突可串行化的。
  - 可通过拓扑排序检验是否有环
可恢复性
- 可恢复调度：对于每对事务T1与T2，如果T2读取了T1所写的数据，则T1必须先于T2提交。
- 注：事务的恢复：一个事务失败了，应该能够撤消该事务对数据库的影响。如果有其它事务读取了失败事务写入的数据，则该事务也应该撤消。
- 级联调度：由于一个事务故障，导致了一系列事务的回滚
- 无级联调度：对于每对事务T1与T2，如果T2读取了T1所写的数据，则T1必须在T2读取之前提交。
- 注：无级联调度比可恢复调度的要求更高，它不仅是可恢复的，而且还避免了写数据回滚可能造成的一系列事务的回滚。
事务隔离性级别
- 事务隔离性的实质：数据库的并发性与一致性的函数。
  - 随着事务隔离级别的上升，数据库的一致性随之上升，而并发性反而下降。
- 事务隔离级别，按照隔离级别从低到高的顺序：
  - ①未提交读：允许读取未提交数据。
    - 当事务A更新某条数据时，不容许其他事务来更新该数据，但可以读取。
    - 事务A中的更新操作完全没有被隔离。
    - 如果事务A因为异常回滚，那么事务B中读取的数据就是脏数据。
    - 这一隔离级别违反了最基本的ACID特性
  - ②已提交读：只允许读取已提交数据，但不要求可重复读。
    - 当事务A更新某条数据时，不容许其他事务进行任何操作包括读取，但事务A读取时，其他事务可以进行读取、更新
    - 这是一种使用较多的隔离级别，它既允许了事务B获取数据（支持并发性），同时又隐藏了其它事务（事务A）对该数据的更新，直到（事务A）提交的那一刻为止。
    - 几乎所有的数据库都支持“已提交读”的隔离级别，并且大部分将其作为默认的隔离级别。
  - ③可重复读：只允许读取已提交数据，而且一个事务两次读取一个数据项期间，其他事务不得更新该数据，但是该事务不要求与其他事务可串行化。
    - 尽管在事务B执行期间，事务A插入了一条数据（QRS），但是在事务B在t2时刻查询所得的结果依然和t0时刻一样（不包含QRS），即使到了t4时刻，事务A已经提交了也是如此（这一点不同于“读已提交”）
    - 该隔离级别下，会在被查询或修改的数据上加上读写锁，因此任何想要修改该数据的其它事务会等待（或失败），直到“可重复读”的事务提交为止。
  - ④可串行化：保证可串行化调度
    - 在该隔离级别下，所有同时到达的事务将会“排队进入”，保证每次只允许一个事务操作数据。
    - 使用“ 串行化”的隔离级别，应用的并发性明显下降，而数据完整性则显著提高。
  隔离级别 肮脏读取 不可重复读取 幻影读取
  未提交读可能发生可能发生可能发生
  已提交读 - 可能发生可能发生
  可重复读 - - 可能发生
  可串行化 - - -
常见题目
- 事务的定义、特性
- 说明串行调度和可串行化调度的区别
  - 执行方式上，一个是串行执行，另一个是并发执行但达到某一串行执行顺序下串行执行的结果
  - 可串行化调度允许事务中操作在满足可串行化条件下进行交叉
  - 可串行化下天然避免并发冲突从未保证了一致性，而可串行化调度需要通过锁的协议等保证一致性
  - 可串行化调度提高了系统资源利用率
  - 串行化调度因为无并发竞争避免了死锁的情况，但可能因为某一事务执行时间过长导致系统资源的浪费与饥饿现象的发生，而可串行化调度可能出现死锁饥饿等现象，需要通过牺牲策略等方式进行解决

隔离级别	肮脏读取	不可重复读取	幻影读取
未提交读	可能发生	可能发生	可能发生
已提交读	-	可能发生	可能发生
可重复读	-	-	可能发生
可串行化	-	-	-

第十八章并行控制

基本要点

封锁：两阶段封锁保证可串行化
时间戳：通过时间戳确定事务应该执行还是回滚
多版本和快照隔离：多版本允许事务读取数据项的旧版本，快照隔离让每个事务开始时拥有自己的数据库版本或者快照

基于锁的协议
- 锁
  - 排它锁（exclusive lock，简记为X锁）
    - 又称为写锁。
    - 若事务T对数据对象Q加上X锁，则事务T既可以读又可以写Q，其它任何事务都不能再对Q加任何类型的锁，直到T释放A上的锁。
  - 共享锁（Share lock，简记为S锁）
    - 又称为读锁。
    - 若事务T对数据对象Q加上S锁，事务T可读但不能写Q，其它事务只能再对Q加S锁，而不能加X锁，直到T释放Q上的S锁。
  - 注：可以看出排他锁的封锁级别更高，共享锁允许多个事务同时读取。如果这两种锁的优先级不加约束，会出现严重的后果。
  - 注意有S锁就不能加X锁，只能再加S锁
- 饥饿
  - 不断出现的申请并获得S锁的事务，使申请X锁的事务一直处在等待状态。
  - 饥饿的防止
    - 规定两种锁的优先级，排他锁的优先级高于共享锁。对申请S锁的事务，如果有先于该事务且等待的加X锁的事务，令申请S锁的事务等待。
- 封锁协议（Locking Protocol）
  - 何时申请X锁或S锁，持锁时间、何时释放
  - 不同的封锁协议，在不同的程度上为并发操作的正确调度提供一定的保证。
  - 封锁协议限制了可能的调度数目，这些调度组成的集合是所有可能的可串行化调度一个真子集。
- 两阶段封锁协议
  - 定义：每个事务分两个阶段提出加锁和解锁申请。
  - 增长阶段(growing phase)：事务可以获得锁，但不能释放锁。
  - 缩减阶段(shrinking phase) ：事务可以释放锁，但不能获得新锁。
  - 封锁点：事务最后加锁的位置，称为事务的封锁点，记作Lp（T）
  - 两阶段封锁协议的特性：
    - 并行执行的所有事务均遵守两段锁协议，则对这些事务的所有并行调度策略都是可串行化的。
    - 事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件。即可串行化的调度中，不一定所有事务都必须符合两段锁协议。
  - 定理：两阶段封锁协议保证调度冲突可串行化
  - 两阶段封锁协议下不能避免死锁。
  - 两阶段封锁协议下不能避免级联回滚。
  - 为了避免级联
    - 严格两阶段封锁协议
      - 事务持有的所有排他锁必须在事务结束后，方可释放。
    - 强两阶段封锁协议
      - 事务提交之前，不得释放任何锁。
- 多粒度封锁协议
  - 概念
    - 单一粒度的缺点
      - 封锁粒度大：并发性低
      - 封锁粒度小：访问大粒度数据加锁量巨大
    - 多粒度的优点
      - 根据访问数据的粒度，确定封锁的粒度。以求加锁量有限，并可获得最大的并发性
    - 多粒度封锁的基本原则
      - 大粒度数据由小粒度数据组成。
      - 允许对不同粒度数据进行封锁。
      - 事务对大粒度数据加锁，隐含地对组成大粒度数据的所有小粒度数据加锁。
    - 多粒度层级结构：
      - 多粒度层次树：子节点表示的数据是父节点表示的数据的一部分
    - 多粒度封锁判定授予锁
    - 申请小粒度锁的判定
      - 判定在申请数据上有没有不相容锁。
      - 判定在申请数据相关大粒度数据上，有没有不相容锁。
      - 粒度的层次有限，本判定不困难
    - 申请大粒度锁的判定
      - 判定在申请数据上有没有不相容锁。
      - 判定在申请数据相关小粒度数据上，有没有不相容锁；
      - 如：封锁表，要判定每个元组上有没有不相容锁
      - 小粒度的数据量可能巨大，本判定困难。
    - 意向锁：
      - 如果一个节点加上了意向锁，则意味着要在树的较低层进行显示加锁。
      - 意向锁添加时机：在一个节点显式加锁之前，该结点的全部祖先均加上了意向锁。
      - 意向锁的作用：事务判定是否能够成功地给一个结点加锁时，不必搜索整棵树。相当于一个标志，当节点上有意向锁时，表明它的下一级数据有一个或多个正在被其他事务访问。此时，只用观察当前节点有无意向锁，即可知道能否对当前节点的所有后代加锁，不用逐一检测下一级数据的锁。
      - 三种意向锁：共享意向锁（IS）/排他意向锁（IX）/共享排他意向锁（SIX）
  - 多粒度封锁协议
    - 遵从锁的相容矩阵
    - 根结点必须首先加锁，可以加任何类型的锁
    - 仅当Ti 对Q的父结点持有IX或IS锁时，Ti对于结点Q加S或者Is锁
    - 仅当Ti对Q的父结点持有IX或SIX锁时， Ti对于结点Q加X、SIX、IX锁
    - 仅当Ti未曾对任何结点解锁时，Ti可以对结点加锁（两阶段的）
    - 仅当Ti当前不持有Q的子节点的锁时，Ti可以对节点Q解锁
死锁处理
- 死锁预防
  - 预防死锁的发生就是要破坏产生死锁的条件。
  - 一次封锁法：
    - 要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。
    - 问题：
      - 降低并发度，将以后要用到的全部数据加锁，势必扩大了封锁的范围，从而降低了系统的并发度。
      - 难以事先确认封锁对象，事先全部加锁，则更加降低并发度
  - 顺序封锁法：
    - 顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁。
    - 问题：维护成本高数据库系统中可封锁的数据对象极其众多，并且随数据的插入、删除等操作而不断地变化，要维护这样极多而且变化的资源的封锁顺序非常困难。
  - 抢占与事务回滚
    - 在抢占机制中，当事务Ti所申请的锁被事务Tj所持有时，授予Tj的锁可能通过回滚事务Tj被抢占，并将锁授予Ti。
    - 通过时间戳确定事务等待还是回滚，事务重启时，保持原有的时间戳。
    - 注：为何保持原有时间戳？因为一个被反复回滚的事务很可能处于饥饿状态，让它保持原有时间戳相当于一种老化机制。早产生的事务在不断回滚时一定会成为最“老”的那个，此时它在抢占锁时一定有最高的优先级，破除了饥饿。
    - 时间戳越小，说明这个事务越古老
    - 两种技术：
      - Wait-die(非抢占技术)：当事务Ti申请的数据项当前被事务Tj持有时，仅当Ti的时间戳小于Tj的时间戳时，允许Ti等待，否则Ti回滚。
      - Wound-die (抢占技术) ：当事务Ti申请的数据项当前被事务Tj持有时，仅当Ti的时间戳大于Tj的时间戳时，允许Ti等待，否则Tj回滚。
    - 注：上述两种机制均避免“饿死”：任何时候均存在一个时间戳最小的事务。在这两种机制中，这个事务都不允许回滚。由于时间戳总是增长，并且回滚的事务不被赋予新的时间戳，被回滚的事务最终变成最小时间戳事务，从而不会再次回滚。
    - 二者的共同问题是：发生不必要的回滚
- 检测死锁
  - 超时法
    - 如果一个事务的等待时间超过了规定的时限，就认为发生了死锁。
    - 优点：实现简单。
    - 缺点：时限若设置得太短，有可能误判死锁；时限若设置得太长，死锁发生后不能及时发现。
  - 等待图法
    - 用事务等待图动态反映所有事务的等待情况，并发控制子系统周期性地（比如每隔1 min）检测事务等待图，如果发现图中存在回路，则表示系统中出现了死锁。
    - 注：事务等待图是一个有向图G=(V，E)，V为结点的集合，每个结点表示正运行的事务，E为边的集合，每条边表示事务等待的情况
    - 若Ti等待Tj，则Ti，Tj之间划一条有向边，从Ti指向Tj
    - 事务Tj不再持有事务Ti所需要的数据项时，边从等待图中删除
    - 如果图中存在回路，就说明系统中出现死锁
- 解除死锁：选择牺牲者，回滚事务。
  - 部分回滚：只回滚到可以解除死锁的地方
  - 饿死：同一事务被选为牺牲者多次
    - 解决方法：在代价因素中增加回滚次数，减少饿死
基于时间戳的协议
- 事先选定事务的次序。
- 时间戳排序协议的目标：令调度冲突等价于按照事务开始早晚次序排序的串行调度。
- 时间戳排序协议的基本思想：
  - 开始早的事务不能读开始晚的事务写的数据。
  - 开始早的事务不能写开始晚的事务已经读过或写过的数据。
- 事务的时间戳：对于系统中的每一个事务Ti，将唯一的时间戳与它相联系，记为TS(T i) 。
- 时间戳的两种简单方法：系统时钟&逻辑计数器
- 事务的时间戳决定了串行化顺序。
- 时间戳的大小标志着事务发生的早晚。（时间戳越大，事务越晚发生）
- 数据项时间戳
  - W-timestamp(Q)：表示成功执行write(Q)的所有事务的最大的时间戳。
  - R-timestamp(Q)：表示成功执行read(Q)的所有事务的最大的时间戳。
  - 注：不是最后执行Read(Q)的事务的时间戳。
  - 例如：
```
TS(T1)=1;TS(T2)=2; 
T2:read(Q) //r-ts(Q)=2 
T1:read(Q) //r-ts(Q)=2 (≠1!)
```
- 时间戳排序协议
  - 假设事务Ti发出read(Q)
    - 如果TS(T i)< W-timestamp(Q) ，则Ti需读入的Q值已被覆盖。因此，read操作被拒绝，Ti回滚。
    - 如果TS(T i)>= W-timestamp(Q) ，则执行read操作，R-timestamp(Q)被设为R-timestamp(Q)和TS(T i)两者的最大值。
  - 假设事务Ti发出write(Q)
    - 如果TS(T i)< R- timestamp(Q) ，则Ti产生的Q值是先前所需要的值，且系统已假定该值不会被产生。因此，write操作被拒绝，Ti回滚。
    - 如果TS(T i)< W-timestamp(Q) ，则Ti试图写入的Q值已过时。因此，write操作被拒绝，Ti回滚。
    - 否则，执行write操作，将W-timestamp(Q)设为TS(T i) 。
- 注意时间戳排序协议回滚时，系统赋予事务新的时间戳，为了避免出现重复的“重启——回滚——重启”现象
- 时间戳排序协议的特性
  - 保证冲突可串行化，冲突可串行化的调度不一定能被时间戳排序协议调度出来。
  - 无死锁。事物如不满足协议即回滚，不会出现事务间相互等待的情况。
  - 存在饥饿现象。事务可能被反复回滚、重启。
  - 不能保证可恢复性。可以扩展协议以保证可恢复性，如跟踪提交依赖等。
- 调度可恢复方法：（下列之一）
  - 所有的写操作都在事务末尾执行，在写操作正在执行时，任何事务都不允许访问已写好的任何数据项。
  - 对未提交数据项的读操作，被推迟到更新该数据项的事务提交之后
  - 事务Ti读取了其他事务所写的数据，只有在其他事务提交之后，Ti才能提交
- Thomas写规则
  - 假如事务Ti发出write(Q)
  - 如果TS(T i)<R-timestamp(Q) ，则Ti产生的Q值是先前所需要的值，且系统已假定该值不会被产生，因此，write操作被拒绝，Ti回滚。
  - 如果TS(T i)<W-timestamp(Q) ，则T1试图写入的值已过时，因此，忽略这个写操作
  - 否则，执行write操作，将W- timestamp(Q)设为TS（Ti）
  - 注：Thomas写规则尽量减少数据被反复修改，注重保护当前有效的数据。因此在第二种情况下，它会选择忽略这个老的写操作。这样做减少了回滚。 Thomas写规则通过删除事务发出的过时的write操作产生视图等价于串行调度。
- 插入和删除
  - 删除
    - 在两阶段封锁协议下，在一个数据项可以被删除之前，必须请求在该数据项上加排他锁；
    - 在时间戳排序协议下，必须执行类似于write操作的约束，假如事务Ti发出delete(Q)：
      - a.如果TS(T i)<R-timestamp(Q)，则Ti将要删除的Q值已被满足TS(T j)>TS(T i)的事务Tj读取，因此delete操作被拒绝，Ti回滚；
      - b.如果TS(T i)<W-timestamp(Q)，则满足TS(T j)>TS(T i)的事务Tj已经写过Q，因此delete操作被拒绝，Ti回滚；
      - c.执行delete操作
  - 插入
    - 在两阶段封锁协议下，如果事务Ti执行insert(Q)，Ti在新创建的数据项Q上被赋予排他锁
    - 在时间戳排序协议下，如果事务Ti执行insert(Q)操作， R-timestamp(Q)和W-timestamp(Q)的值被设成TS(T i)
  - 谓词读和幻象现象
    - 幻象(phantom phenomenon)会伴随着插入、更新和删除操作而出现
    - 幻象现象的根源在于谓词读取(Predicate Reads)与插入更新相冲突，从而导致新/更新的元组满足谓词
    - 防止幻影的索引锁定协议
      - 每个关系必须至少有一个索引.
      - 事务只能在通过关系上的一个或多个索引找到元组后才能访问元组
      - 执行查找的事务Ti必须以S模式锁定其访问的所有索引叶节点，即使叶节点不包含任何满足索引查找的元组(例如，对于范围查询，叶中没有元组在范围内)
      - 在关系r中插入、更新或删除元组Ti的事务Ti
        Must update all indices to r
        必须在受insert/update/delete影响的所有索引叶节点上获得独占锁
        必须遵守两阶段锁定协议的规则
基于有效性检查的协议
- 每个事务Ti在其生存期中按两个或三个阶段执行：
  - 读阶段：各数据项值被读入，并保存在事物Ti的局部变量中。
  - 有效性检查阶段：判断是否可以将write操作所更新的临时局部变量值复制到数据库而不违反可串行性。
  - 写阶段：若事务Ti已经通过有效性检查，进行实际的数据库更新，否则，回滚。
- 按照阶段设置时间戳
  - Start(T i)：事务Ti开始执行的时间。
  - Validation(T i)：事务Ti完成读阶段并开始其有效性检查阶段的时间。
  - Finish(T i)：事务Ti完成写阶段的时间。
- 有效性检查协议
- 利用时间戳Validation(T i)的值，通过时间戳排序技术决定可串行化顺序。
- 即：TS(T i)=Validation(T i) ，事务完成读之后即更改其时间戳的值。
- 之所以选择Validation(T i)的值作为事务Ti的时间戳，而不使用Start(T i) ，是为了在冲突频度低的情况下，可以拥有更快的响应时间。
- 事务Tj的有效性测试：要求任何满足TS(T i) < TS(T j)的事务Ti必须满足下列条件之一：
  - Finish(T i)< Start(T j)
  - Ti所写的数据项集与Tj所读数据项集不相交，并且Ti的写阶段在Tj开始其有效性。检查阶段之前完成(start(T j)<finish(T i)<validation(T j)) ，此条件保证Ti和Tj的写不重叠。
多版本机制
- 多版本时间戳排序协议
  - 对于每个数据项Q，有一个版本序列<Q1, Q2, …, Qm>，每个版本Qi包括三部分内容：
    - Content：版本Qi的值；
    - W-timestamp(Qi) ：创建版本Qi的事务的时间戳
    - R-timestamp(Qi) ：所有成功读取Qi版本的事务的最大的时间戳
  - 假设事务Ti发出read(Q)或者write(Q)操作，令Qk表示Q的版本，它具有小于或等于TS(T i)的最大的写时间戳
    - 如果事务Ti发出read(Q)，则返回版本Qk的内容
    - 如果事务Ti发出write(Q)，且TS(T i)< R-timestamp(Q k)，则系统回滚Ti；若TS(T i) = W-timestamp(Q k)，则系统覆盖Qk的内容；否则创建一个Q的新版本
  - 多版本时间戳排序协议的良好特性：读请求从不失败且不必等待。在数据库系统中，读操作比写操作频繁，因此，此特性对于实践来说至关重要。
  - 缺点：读取数据项可能更新R-timestamp字段，增加了一次磁盘访问机会；事务间的冲突通过回滚解决而不是等待，这种做法开销大
  - 版本删除：假设某数据项的两个版本Qk与Qj，这两个版本的W-timestamp都小于当前系统中最老的事务的时间戳，则Qk和Qj中较旧的的版本将不再用到，可以删除
- 多版本两阶段封锁协议
  - 该协议对只读事务和更新事务加以区分。
  - 更新事务执行强两阶段封锁协议
  - 数据项的每一个版本有一个时间戳，时间戳由计数器(t s-counter)实现
  - 只读事务执行读操作时遵从多版本时间戳排序协议
  - 当更新事务读取一个数据项时，首先获得该数据项上的排他锁，然后为该数据项创建一个新版本，写操作在新版本上进行，新版本的时间戳最初置为∞
  - 更新事务Ti完成任务后，按照如下方式提交：首先，Ti将它创建的每一个版本的时间戳设为当前ts-counter的值加1；然后Ti将ts-counter的值加1
  - 版本删除：假设某数据项的两个版本Qk与Qj，这两个版本的时间戳都小于或等于当前系统中最老的事务的时间戳，则Qk和Qj中较旧的的版本将不再用到，可以删除
快照（snapshot）隔离
- 快照隔离在事务开始执行时给它一份数据库快照，事务在该快照上以与其他事物完全隔离的方式操作。
- 对于只读事务非常理想
- 更新事务：提交之前必须对事务进行有效性检查；更新被控制在事务的私有工作空间中，通过有效性检查后，更新被写入数据库
- 更新事务提交要保障原子性
- 事务被赋予两个时间戳：
  - Start TS(T i)事务开始时间
  - Commit TS(T i)事务请求有效性检查时间
  - 每个数据项的时间戳被赋予更新该数据项的事务的CommitTS(T i)
  - 当事务Ti读取一个数据项时，具有<= Start TS(T i)的数据库的最新版本将返回给Ti
- 更新事务的有效性检查，事务Ti与事务Tj是并发的：StartTS(Tj)<= StartTS(Ti)<= CommitTS(Tj)或者StartTS(Ti)<= StartTS(Tj)<= CommitTS(Ti)
- 更新丢失：两个更新事务都被允许写入数据库，第一个更新的写操作将被第二个覆盖。
- 幻影现象：可能出现两个事务执行的不一样，但是结果生成相同
- 快照隔离两个变种
  - 先提交者胜：仅当没有其他并发事务已写入T1打算写入的数据时提交.
  - 先更新者胜
常见题目
- 给定事务时间戳，执行时序
  - 是否满足两阶段封锁协议：是否有上锁、解锁两阶段的解可以使得事务执行
  - 是否满足时间戳封锁协议：不断更新各数据项读写时间戳，观察是否有冲突
  - 是否满足冲突可串行化：画出事务的优先图
- 给定步骤，求查询结果
  - 对客户端，检查事务是否已经提交，注意事务上锁（若一个客户端上锁，会导致其他客户端一直等待）
- 为什么要赋予新的新的时间戳：
  - 避免事务重复冲突
  - 防止发生饥饿现象
- 什么是时间戳排序协议？特性
  - 定义
    - 令调度冲突等价于事务开始早晚顺序的串行调度
    - 开始早的事务不能读取开始晚的事务的写的数据
    - 开始早的事务不能写开始晚的事务读取过或写过的数据
  - 特性
    - 保证冲突可串行化，冲突可串行化的调度不一定能被时间戳排序协议调度出来。
    - 无死锁。事物如不满足协议即回滚，不会出现事务间相互等待的情况。
    - 存在饥饿现象。事务可能被反复回滚、重启。
    - 不能保证可恢复性。可以扩展协议以保证可恢复性，如跟踪提交依赖等。
- 什么是两阶段封锁协议？特性
  - 定义
    - 每个事务分两个阶段提出加锁和解锁申请
    - 增长阶段：事务能获得锁，不能释放锁
    - 缩减阶段：事务能释放锁，不能获得新锁
  - 特性
    - 若并发执行的所有事物遵守两阶段封锁协议，则冲突可串行化
    - 冲突可串行化的事务不一定遵守两阶段封锁协议
    - 不能避免死锁
    - 不能避免级联回滚（严格两阶段封锁协议，强两阶段封锁协议）

第十九章恢复系统

故障分类
- 事务故障
  - 逻辑故障：由于某些内部条件而不能继续正常执行（如非法输入，溢出等）
  - 系统错误：系统进入不良状态（如死锁）
- 系统崩溃（硬件故障、DBMS故障、OS故障）
- 磁盘故障
基于日志的恢复
- 日志概念
  - 日志是日志记录的序列,记录数据库中所有的更新活动。
  - 先写日志，后写数据库。
  - 日志的组成：事务标识符、数据项标识符、旧值、新值
  - 下面是几种常见的日志： <T i, X j, V1,V2>；<T i, commit>；<T i, abort>
- 延迟的数据库修改：事务中所有的write操作，在事务部分提交时才修改数据库的执行，日志中只记录新值。
  - 恢复机制
    - Redo(T i): 将事务Ti更新的所有数据项的值设为新值。
    - Redo操作必须是幂等的，操作次数不影响最后的结果
    - 事务Ti需要Redo操作，当且仅当日志中既包含记录<T i, start>又包含记录<T i, commit>。
    - 注：当且仅当，事务完整提交后，数据库中的数据才被修改了。因此必须有commit标志的事务才可以被redo。
- 立即的数据库修改：允许数据库修改在事务处于活动状态时就输出到数据库中。
  - 恢复机制
    - Undo(T i)：将事务Ti所有更新的所有数据项的值恢复成旧值。
    - Redo(T i)：将事务Ti所有更新的所有数据项的值置为新值。
    - 操作原则
      - 事务Ti需要Redo操作，当且仅当日志中既包含记录<T i, start>又包含记录<T i, commit>
      - 事务Ti需要Undo操作，当且仅当日志中既包含记录<T i, start>不包含记录<T i, commit>
      - 注：只有commit的事务才是有效的。因此即使事务修改了数据库，但是它没有commit，它新写的数据也不能具有持久性。
- undo和redo的本质：根据日志恢复数据库丢失的数据，因此redo已经提交的事务，undo未提交的事务
- 检查点
  - 由系统周期性地执行检查点，需要执行下列操作：
    - 将当前位于主存的所有日志记录输出到稳定存储器上。
    - 将所有修改了的缓冲块输出到磁盘上。
    - 将一个日志记录输出到稳定存储器。
  - 检查点执行过程中，不允许事务执行更新操作。
  - 基本原则：
    - 在检查点之前提交的事务，不予考虑。因为记录<T i, commit>在日志中，出现在之前，这表示系统故障前Ti已经提交，Ti的操作有效。
    - 确定最近的检查点发生前开始执行的最近的一个事务Ti，对于Ti和Ti之后的开始执行的事务Tj执行redo和undo操作。
  - 具体过程
    - 系统由后向前（因为事务可能多次更新一个数据项）扫描日志，直至发现第一个：
      - Redo-list：对每一个形如<T i, commit>的记录，将Ti加入Redo-list
      - Undo-list：对每一个形如<T i, start>的记录，如果Ti不属于Redo-list，将Ti加入undo-list
    - Redo-list和undo-list构造完毕后：
    - 从最后一个记录开始由后至前从新扫描日志，并且对undo-list中的每一个日志记录执行Undo操作。忽略redo-list中的事务。
    - 找到最近一条记录。
    - 系统由最近一条记录由前向后扫描日志，并且对redo-list中事务Ti的每一个日志记录执行redo操作。
    - 注：从后向前undo，从前往后redo 。因为undo是还原数据的操作，应该从最后一个无效数据逐步还原回最近记录的有效数据。而redo是重写操作，有可能多个事务对同一数据单元进行过更新，应该按照这些事务写的顺序来重写数据，否则会出现最后有效的数据反而是原先“最老”的事务提交的结果。

2025押题

一、

调度相关概念
优化语法树
- B+树
索引
并发控制
关系模型介绍这一章的某个知识点
恢复系统/多值依赖

二、

ER图并转化为关系模式
3NF/BCNF分解（正则覆盖，候选码）
SQL/关系代数书写

补充1：英汉双语

（后面发现考试没有英语题）

补充2：其余资料

资料链接：
通过网盘分享的文件：相关资料.zip
链接: https://pan.baidu.com/s/1J7NM7-JiE2BOe–kTQ577g?pwd=SJKX 提取码: SJKX

资料目录：
数据库系统—事务.pdf
2018 数据库.docx
2019 数据库.docx
2021 数据库.docx
2022 数据库.docx
2023 数据库.docx
2024 数据库.docx
数据库原理试题-A试卷.pdf
数据库原理试题-A试卷答案.pdf
数据库原理试题-B试卷.pdf
数据库原理试题-B试卷答案.pdf