数据库知识点汇总尽量详细版（超速更新）

前言

所有知识点总结于《数据库原理及应用技术教程》杨晓光 主编
“[ ]”表示此段代码可选

数据库的特点

数据结构化
数据的共享性高，冗余度低，易扩展
数据独立性高
数据由DBMS统一管理和控制

数据库管理系统的概念和主要功能

概念：

数据库管理系统是位于用户与操作系统之间的一层数据管理软件。

功能：

数据定义功能
数据组织、存储和管理
数据操纵功能
数据库的事务管理和运行管理
数据库的建立和维护功能
其他功能（通信，数据转换）

数据库(DB)、数据库系统(DBS)及数据库管理系统(DBMS)三者之间的关系

数据库是存放数据的仓库，数据库数据具有永久存储、有组织、可共享三个基本特点。

数据库管理系统是位于用户与操作系统之间的一层数据管理软件，是数据库系统一个重要组成部分。

数据库系统是指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员构成。

数据模型三要素

关系数据结构

关系：一个关系对应通常说的一张表
元组：表中的一行即为一个元组
属性：表中的一列即为一个属性，给每一个属性起一个名称即属性名
码：也称码键。表中的某个属性组，它可以唯一确定一个元组
域：属性的取值范围
分量：元组中一个属性值
关系模式：对关系的描述

关系数据操作

关系数据模型的操作主要包括：查询、插入、删除和更新数据。（增，删，改，查）

关系数据的完整性

关系的完整性约束条件包括三大类：实体完整性、参照完整性 和 用户定义的完整性。

数据库的体系结构

三级模式结构

模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。

两级映像

对于同一个模式可以有任意多个外模式。对于一个外模式，数据库系统都有一个外模式/模式映像，它定义了该外模式与模式之间的对应关系。

数据库中只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系。

数据独立性

外模式/模式映象为数据库提供了数据逻辑独立性，可以通过修改 外模式/模式映象 保证数据库的逻辑独立性。

模式/内模式映象为数据库提供了数据物理独立性。可以通过修改 模式/内模式映象 保证数据库的物理独立性。

关系的三类完整性

实体完整性规则:

若属性A是基本关系R的主属性（主码），则A不能取空值。

参照完整性规则:

若属性F是基本关系R的外码，S的主码，则F或者取空值，或者等于S中某个元组的主码值。

用户定义的完整性:

用户定义的完整性就是针对某一具体关系数据库的约束条件

关系代数运算符号及基本的运算

关系R和关系S

并（$R \cap S$）：属于R或属于S的元组组成

差（$R - S$）：属于R而不属于S的所有元组组成

交（$R\cup S$）：既属于R又属于S的元组组成

笛卡尔积（$R\times S$）：R与S的所有元组一一组合

选择（$\sigma_{a=value1,b=value2}(R)$）：在关系R中选择满足给定条件的元组

投影（$\Pi_{a,b}(R)$）：关系R上的投影是从R中选择出若干属性列组成新的关系

连接（$R\bowtie S$）：从R与S的笛卡尔积中选择符合条件符合条件的

除（$R\div S$）：在R中满足S条件的投影（建议看书，这里不作详细介绍）

综合实例（原书P55-56 题 与 表）

查询至少选修了一门其直接先行课为5号课程的学生姓名

$\Pi_{Sname}(\sigma_{Cpno='5'}(Course)\bowtie SC\bowtie \Pi_{Sno,Sname}(Student))$

查询选修了全部课程的学生号码和姓名

$\Pi_{Sno,Cno}(SC)\div \Pi_{Cno}(Course) \bowtie \Pi_{Sno,Cno}(Student)$

关系数据库标准语言

数据查询：SELECT
数据定义：CREATE, DROP, ALTER
数据操纵：INSERT, UPDATE, DELETE
数据控制：GRANT, REVOKE

创建基本表

CREATE TABLE 表名
(
    属性列名 数据类型[列级完整性约束]
    [,属性列名 数据类型[列级完整性约束]
    ...
    [，表级完整性约束]
    [，完整性约束命名字句]]
);

注意：CREATE TABLE 后面是 “( )” 括号，不是大括号。

实现完整性约束

实现完整性约束的的方法

• 列级完整性约束
  只能应用在一个属性列上, 将约束直接写在 属性列名 以及其 数据类型 后。

CREATE TABLE reader
(
    rno CHAR(5) PRIMARY KEY,
    ....
);

创建表时，指定属性列 rno 为主码。

• 表级完整性约束

CREATE TABLE borrow
(
    rno CHAR(5),
    bison CHAR(11),
    ....
    PRIMARY KEY(rno,bison)
);

创建表时，指定属性列 rno 、bison为主码，可以一次指定多个。

• 完整性约束命名字句

CONSTRAINT 完整性约束条件名 [PRIMARY KEY 短语|FOREIGN KEY 短语|CHECK 短语]

CREATE TABLE book
(
    bison CHAR(11),
    ....
    CONSTRANT book_key PRIMARY KEY(bison)
);

那个“book_key”是对约束条件的命名，为了让人更清楚的明白这个约束的意义。

实现实体完整性约束

定义：若属性A是基本关系R的主属性（主码），则A不能取空值。

格式：

PRIMARY KEY(属性列名)

一个基本表只能有一个PRIMARY KEY。

例子就是上面的那三个，列级、表级 和 完整性命名字句。

实现参照完整性约束

定义：若属性F是基本关系R的外码，S的主码，则F或者取空值，或者等于S中某个元组的主码值。

格式：

FOREIGN KEY(属性列名) REFERENCES 被参照表名(属性列名)

例：

CREATE TABLE borrow
(
    rno char(5),
    .....
    FOREIGN KEY(rno) REFERENCES reader(rno)
);

实现用户定义的完整性约束

格式：属性列名 数据类型[约束条件]

NOT NULL

某个属性取值不可以为空。

同理 NULL就是可以为空，默认为NULL。

DEFAULT

若不给此属性赋值，则取给定的默认值。

格式：DEFAULT 默认值

UNIQUE

确保非主码列上的值的唯一性。

CHECK

格式：CHECK(条件表达式)

条件表达式中可以出现属性列名，常量，运算符，函数等。

例：

CREATE TABLE Student
(
    ....
    sgender CHAR(5) CHECK(rgender IN('男','女'))  \\性别只能取“男”或“女”
    sage INT CHECK(sage BETWEEN 0 AND 100)       \\年龄取值在0~100
    sIQ INT CHECK(sIQ > 0)                       \\智商不是负的
    ....
);

修改基本表

增加属性列

格式：ALERT TABLE 基本表名 ADD 新属性列名 数据类型 [完整性约束]

添加一个“联系电话”属性：

ALERT TABLE reader ADD rphone CHAR(13);

增加属性列时，不能使用 NOT NULL，因为之前的元组 在这个新加的属性列上 还没有赋值，确保它们 可以在该列上取空值。

增加完整性约束

格式：ALERT TABLE 基本表名 ADD 完整性约束

设置外码：

ALERT TABLE reader ADD FOREIGN KEY(cno) REFERENCES college(cno);

删除属性列

格式：ALERT TABLE 基本表名 DROP COLUMN 属性列名

DROP COLUMN

删除完整性约束

格式：ALERT TABLE 基本表名 DROP CONSTRAINT 完整性约束名

完整性约束名 就是之前 定义完整性约束时 起的名字，如“book_key”。

例：

ALERT TABLE book DROP CONSTRAINT book_key;

修改属性列

格式：ALERT TABLE 基本表名 ALERT COLUMN 属性列名 数据类型

将“联系电话”的数据类型改为CHAR(20)

ALERT TABLE reader ALERT COLUMN rphone CHAR(20);

ALERT COLUMN

删除基本表

格式：DROP TABLE 基本表名[RESTRICT|CASCADE]

RESTRICT：被删除的表不能被其他的表的约束所引用，否则不能删除。默认为RESTRICT。（启用外键约束）

CASCADE：级联删除，其相关的依赖对象（视图，存储过程等）也一并删除。

基本表一旦被删除，表中数据，以及建立在该表上的索引、视图等都将被删除，无法恢复，谨慎使用此语句。

查询

查询指定列：

SELECT Sno,Sname
    FROM Student;

查询全部列：

SELECT *
    FROM Student;

查询经过计算的值：

SELECT 2004-Sage

消除取值重复的行：

SELECT DISTINCT Sno
    FROM SC;

条件查询

• 比较：

SELECT Sname,Sage
    FROM Student
    WHERE Sage < 20;

• 确定范围：

SELECT Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23; \\20到23岁之间

• 确定集合：

查询指定 系 的学生：

SELECT Sname,Ssex
    FROM Student
    WHERE Sdept IN('CS','MA','IS');

• 多重条件查询：
  AND和OR来连接

SELECT Sname
    FROM Student
    WHERE Sdept='CS'AND Sage < 20;

模糊查询

• 字符匹配：
  “%”代表任意长度的字符串
  “_”代表任意单个字符

SELECT Sname,Sno
    FROM Student
    WHERE Sname LIKE'_阳%';

• 通配符ESCAPE
  “%”，”_”等符号是通配符，ESCAPE的意思是，将其当做其实际值，而不是通配符。

SELECT *
    FROM Course
    WHERE Cname LIKE'DB_%i__'ESCAPE'%';

涉及空值的查询：

SELECT Sno,Cno
    FROM SC
    WHERE Grade IS NULL;

对查询结果排序

格式：ORDER BY [ASC|DESC]

子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排序，默认为升序。

SELECT Sno,Grade
    FROM SC
    WHERE Cno='3'
    ORDER BY Grade DESC;

聚集函数

COUNT(*) 统计元组个数

COUNT（列名）统计一列中值的个数

SUM（列名）计算一列的总和

AVG（列名）计算一列值的平均值

MAX（列名）求一列值的最大值

MIN（列名）求一列值的最小值

聚集函数只能用在 SELECT 和 HAVING 中，不能在WHERE 用中。

分组查询

格式：GROUP BY 列名 [HAVING 条件表达式]

查询结果按某一列或多列的值分组，值相等的为一组，分组后可以用HAVING进行筛选。

查询 选了 三门课 以上的 学生的 学号 以及 所选的课程数：

SELECT Sno,COUNT(Cno)
    FROM SC
    GROUP BY Sno
    HAVING COUNT(*)>3;

连接查询

• 使用FROM 字句表示连接
  格式：FROM 表名1 JOIN 表名2 ON 表名1.列名1 比较运算符 表名2.列名2

查询学生的 学号 和 系名：

SELECT sno,dname
    FROM Student JOIN Dept ON Student.dno = Dept.dno;

若在两个表的 相同名字列 上进行连接时，可以省略 ON 子句。
即：

SELECT sno,dname
    FROM Student JOIN Dept;

• 多表连接
  例：

SELECT r.rno,r.rname,bk.bname
    FROM reader r,borrow b,book bk
    WHERE r.rno=b.rno AND b.bison=bk.bison;

“r.rno=b.rno”为 reader 和 borrow 的连接条件，“b.bison=bk.bison”为 borrow 和 book 的连接条件。为了简化书写，可以为表 取别名，格式如上，你这么聪明，肯定一看就会了。

子查询

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

ANY意为“某个值”，类比数学上的“存在”$\exists$。

ALL意为“全部值”，类比数学上的“全部”$\forall$。

查询至少有一本书的罚金超过14001读者一本书的罚金金额的读者编号：

SELECT DISTINCT rno
    FROM borrow
    WHERE　fine > ANY
        (SELECT fine 
            FROM borrow
            WHERE rno = '14001');

即，所查询的读者 应满足的条件为“在其借阅的书中 存在：一本书 的罚金 大于 14001读者借阅的某一本书的 罚金”。

查询其他专业中比计算机专业所有读者年龄都大的读者姓名和年龄：

SELECT rname,rage
    FROM reader
    WHERE rspecialty <> 'CP'
        AND rage > ALL
            (SELECT rage 
                FROM reader
                WHERE rspecialty = 'CP');

这个好理解，聪明的你一定会。

• 带有EXISTS的子查询

用于判断子查询 是否 存在。

反之，NOT EXISTS 就是判断子查询 是否 不存在。

集合查询

对查询结果进行操作，与数学上的集合运算一致，这里不做解释。

• 并（NUNION）
• 交（INTERSECT）
• 差（EXCEPT）

格式：

SELECT .....
[UNION|INTERSECT|EXCEPT]
SELECT .....

参与集合操作的各个查询结果的 列数 与 对应项的数据类型 也 必须相同。

插入

插入元组：

INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage)
    VALUES('200215128','陈东','男','IS',18);

插入子查询结果:

INSERT INTO Dept_age(Sdept,Avg_age)
    SELECT Sdept,AVG(Sage)
    FROM Student
    GROUP BY Sdept;

更新

修改某一元组的值：

UPDATE Student
    SET Sage =22
    WHERE Sno='200215128';

修改多个元组的值：

UPDATE Student
    SET Sage = Sage+1;

带子查询的修改语句：

UPDATE SC
    SET Grade=0
    WHERE'CS'=
        (SELETE Sdept
            FROM Student
            WHERE Student.Sno = SC.Sno);

删除

删除某一个元组的值：

DELETE
    FROM Student
    WHERE Sno='200215128';

删除多个元组的值：

DELETE
    FROM SC；

带子查询的删除语句：

DELETE
    FROM SC
    WHERE'CS'=
        (SELETE Sdept
            FROM Student
            WHERE Student.Sno = SC.Sno);

视图（View）

建立视图：

CREATE VIEW[(属性列名1[,属性列名,...])]
    AS
        (任意复杂的SELECT语句，但通常不允许有ORDER BY和DISTINCT短语)
    [WITH CHECK POTION]

WITH CHECK POTION表示对视图进行UPDATE,INSERT,DELETE操作时要保证更新、插入、删除的行满足视图定义中的条件

删除视图

DROP VIEW 视图名
    [CASCADE]

如果该视图还导出了其他视图，则使用CASCADE级联删除。

关系数据库理论

函数依赖

概念

函数依赖是关系模式中属性的一种逻辑依赖关系。

例如：$SDC（SNO, SNAME, SAGE, DEPT）$，一个$SNO$ 对应一个学生，一个学生对应一个系。

所以，当 $SNO$ 确立后，$SNAME，SAGE，DEPT$ 的值也就确立了。

即 $SNO$ 决定 $（SNAME, SAGE, DEPT）$或者说$（SNAME, SAGE, DEPT）$ 依赖于 $SNO$。

写作：$SNO\rightarrow（SNAME, SAGE, DEPT）$。

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

当属性集 $Y$是属性集$X$的子集时，则必然存在着函数依赖$X\rightarrow Y$，这种型的函数依赖称为平凡函数依赖。

如果$Y$不是$X$的子集，则称$X\rightarrow Y$为非平凡函数依赖。

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

设关系模式R（U），U是属性全集，X和Y是U的子集。

如果 $X\rightarrow Y$ 并且对于 $X$ 的任何一个真子集$X'$，都有 $X’\nrightarrow Y$ 则称 $Y$ 对 $X$ 完全函数依赖，记作$X \xrightarrow{f} Y$。

如果 $X\rightarrow Y$ 并且对于 $X$ 的任何一个真子集$X'$，都有 $X’\rightarrow Y$ 则称 $Y$ 对 $X$ 部分函数依赖，记作$X \xrightarrow{p} Y$。

简单地说，若有$(A, B)\rightarrow C$。

完全函数依赖 是 $A,B$ 一个都不能少，两个一起才能决定 $C$。

部分函数依赖 是 $A,B$ 一个就行，其中一个就能决定 $C$。

码

设$K为（U,F）$中的属性或属性组合，$K\in U$。

若$K\xrightarrow{f}U$,则K称为$R$的候选码（即可以通过$K决定整个U$）。若候选码有多个，则选择其中一个为主码。

包含在候选码中的属性，称为主属性。其余为非主属性，或非码属性。若整个属性组是码，则称为全码。

候选码，可以为单属性，也可以为多属性。

$R(A,B,C)$ ,若 $(A,B)\xrightarrow fC$则，$(A,B)$为码,$A和B$为主属性。

范式

第一范式

如果关系模式R，其所有的属性均为简单属性，都是不可再分的，则R属于第一范式，简称 $1NF$，记作 $R\in 1NF$。

第二范式

• 定义

若R是 $1NF$，且每一非主属性完全依赖于码，则其为第二范式，简称 $2NF$，记作 $R\in 2NF$。

• 规范化

分解原则：让一个关系只描述一个实体或者实体间的联系，如果一个关系描述的内容多于一个实体或联系，就要进行投影分解，一个关系说明一个问题。

例：将 $SDC(SNO,SNAME,SAGE,DEPT,MN,CNO,GRADE)2NF$ 规范化。

$SNO\rightarrow (SNAME, SAGE, DEPT, MN)$ ， $(SNO,CNO)\xrightarrow{p}GRADE$

可以看出, $CNO$不依赖于 $SNO$ 而且，其中描述了两个实体，学生实体 $（SNO,SNAME,SAGE,DEPT,MN）$ 以及 选课 $（SNO,CNO,GRADE)$ 。

分解：

$SD（SNO,SNAME,SAGE,DEPT,MN）$

$SC（SNO,CNO,GRADE）$

第三范式

• 定义

每一个非主属性既 不部分依赖于码 也 不传递依赖于码。

• 规范化

例：将 $SD（SNO,SNAME,SAGE,DEPT,MN）3NF$ 规范化。

$SNO\rightarrow DEPT \rightarrow MN$存在传递依赖。

分解：

$S（SNO,SNAME,SAGE,DEPT）$

$D（DEPT,MN）$

BC范式

看这个前请先认真学习理解“码”这一部分。

• 定义

属于1NF，对于所有函数依赖$X\rightarrow Y （X\notin Y）$ ,每一个决定因素 $X$ 都包含码。

• 规范

消除任何属性对码的部分函数依赖。

例：设$SNC (SNO,SNAME,CNO,GRADE)$ 并且名字$（SNAME）$不重复。

候选码：$(SNO,CNO),(SNAME,CNO)$

对于函数依赖$SNO\longleftrightarrow SNAME$ 决定因素$SNO 和 SNAME$ 不包含上述的码。

或者说，存在属性对码的部分函数依赖：$(SNO,CNO)\xrightarrow{p} SNAME,(SNAME,CNO)\xrightarrow{p} SNO$

分解：

$S1 (SNO,SNAME)$

$S2(SNO,CNO,GRADE)$

第四范式

• 多值依赖

定义：给定一对 $（X,Z）$ 值，有一组 $Y$ 的值，这组值仅仅决定于x值而与z值无关

关系模式 $WSC（W,S,C）$，$W$ 表示仓库，$S$ 表示保管员，$C$ 表示商品。

每个仓库有若干个保管员，有若干种商品。

每个保管员保管所在仓库的所有商品，每种商品被所有保管员保管。

按照语义，对于 $W$ 的每一个值 $W_i ,S$有一个完整的集合与之对应而不用问C的取值为多少。

所以$W\rightarrow \rightarrow S$ 。

同理，$W\rightarrow \rightarrow C$。

其中$C=U（全集）-W-S，S=U（全集）-W-C$。

• 规范化

分解：

$WS(W,S) \in 4NF$。

$WC(W,C) \in 4NF$。

规范化小结

数据库设计

数据库设计基本步骤

• 需求分析（基础）
• 概念结构设计（关键）
• 逻辑结构设计
• 物理结构设计
• 数据库实施
• 数据库运行和维护

其中，需求分析 和 概念设计 可以独立于任何 数据库管理系统（DBMS）进行。逻辑设计 和 物理设计 以选中的DBMS密切相关。

E-R图的绘制

• 实体型：用矩形表示，框内写明实体名。
• 属性：用椭圆表示，并用无向边将其与相应的实体连接起来。
• 联系：用菱形表示，框内写明联系名，并用无向边将其与相应的实体连接起来，同时在无向边旁 标上联系的类型（1:1、1:n或m:n）。如果 联系本身 也是一种实体，则其也可以有属性。（如 选课实体 中有 “成绩”）。

例：

这个简单聪明的你看一下就会了吧。

E-R图向关系模型的转换

原则为：一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。

对于实体型间的联系则有以下不同的情况：

1. 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应 的关系模式合并。
2. 一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。
3. 一个m：n联系转换为一个关系模型。
4. 3个或3个以上实体间的一个多元联系可以转换为一个关系模式。
5. 具有相同码的关系模式可以合并。

例：

读者（$\underline{读者编号}$，姓名，性别，年龄，专业）

借阅（$\underline{读者编号，图书ISBN号}$，结束日期，还书日期，罚款金额）

图书（$\underline{图书ISBN号}$，图书名称，作者，价格，出版社，图书类别）

事务和并发控制

事务

概念

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，是恢复和并发控制的 基本单位 。

特性

• 原子性
• 一致性
• 隔离性
• 持续性

并发操作带来的数据不一致情况

丢失修改

两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失。

比如，一共有3颗桃子（别问为什么，我喜欢桃子，还有香蕉），事务 $T_1$ 和事务$T_2$ 都是要回一个桃子，设桃子总数为 $A$ 颗。

不可重复读

事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。
比如，有一颗桃子为 $A$ ，事务 $T_1$ 要看这颗桃子，事务$T_2$ 要吃这个桃子。

读脏数据

事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这是T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据就为脏数据，即不正确的数据。

可串行化调度

多个事务的并发执行是正确的，当且仅当其结果与 按某一顺序串行地执行这些事务时的结果相同，则称这种调度为 可串行化调度。

可串行性 是 并发事务正确调整的 准则。

简单地说，有两个事务，$T_1,T_2$，它俩的 串行调度，即上面说的“按某一顺序串行地执行这些事务”为“$T_1T_2$”或“$T_2T_1$”两种情况，各有各的结果。

现在，让两个事务并发执行，若最后的结果，与前面两种 串行调度 得到的的结果的某一个相同，则这个并发是正确的，这种调度（把多个事务变成并发执行）为可串行化调度。

这个一定要好好理解哦。

上图：

冲突可串行化调度

不同的事务对同一个数据的 读写操作 和 写写操作，为冲突操作。

一个太偶读S在保证冲突操作的次序不变的情况下，通过交换两个事务费冲突操作的次序得到另一个调度 $S'$ ，如果 $S'$ 是可串行的，则称调度$S$为冲突可串行化调度。

若一个调度是冲突可串行化的，则其一定是可串行化的调度，反之则不然。

并发控制的技术

封锁，时间戳，乐观控制法。

封锁

1. 申请加锁
2. 获得锁
3. 释放锁

排它锁（X锁）

又称为写锁。若事务T对数据对象加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A

共享锁（S锁）

又称读锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务职能在对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

封锁协议

一级封锁协议

事务 $T$ 在修改数据$R$ 之前先对其加 $X$锁，直到事务结束（COMMMIT 或 ROLLBACK）才释放。

一级封锁协议可以防止丢失修改，并保证事务 $T$ 是可以恢复的。

二级封锁协议

在一级封锁协议的基础上，增加事务 $T$ 要对读取的数据加 $S$ 锁，读完后 立即释放。

缺点：不能保证“可重复读数据”。

三级封锁协议

在一级封锁协议的基础上，增加事务 $T$ 要对读取的数据加 $S$ 锁，事务结束后 才释放。

各级封锁协议的比较

两段锁协议

所有事务必须分两个阶段对数据进行加锁和解锁：

• 在对任何数据进项读、写操作之前，事务首先要获得对数据的封锁；
• 在释放一个封锁之后，事务不在申请和获得任何其他锁。

加锁阶段 : 在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

解锁阶段 : 当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

两段封锁协议可以这样来实现:事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。

活锁

$T_1$ 申请封锁数据 $R$ ，事务$T_2$ 页申请封锁 $R$，则 $T_2$等待数据 $R$上的锁释放。这是，又有$T_3$请求封锁$R$，也进入等待。当$T_1$释放锁后，喜糖首先批准$T_3$对数据$R$的请求，则$T_2$继续等待。然后，又有$T_4$请求封锁数据$R$。若$T_3$释放$R$上的封锁之后，系统又批准$T_4$对数据$R$的请求……则$T_2$有可能永远在等待，这就是活锁。

解决：先来先服务。

死锁

事务 $T_1$ 和事务$T_2$ 都需要数据$R_1 和 R_2$, 它们在操作时：事务$T_1$封锁数据$R_1$,$T_2$ 封锁数据 $R_2$，然后$T_1$ 又请求封锁$R_2$,因$T_2$ 已经封锁 $R_2$，所以$T_1$ 进入等待状态。接着$T_2$申请对数据$R_1$的封锁，同样，$T_2$ 也进入等待状态，两个事物永远不能结束，形成死锁。

死锁的诊断与解除

• 超时法

如果一个事物的等待时间超过规定的时限，就认为发生死锁。（应用不多，看看就好）

• 等待图法

等待事务图是有向图，$T_i$ 代表事务。

若$T_1$ 等待$T_2$ 则两者之间画一条 从$T_1$ 指向$T_2$ 的有向边。

数据库管理系统 的并发控制子系统周期性地生成事务图，检测事务。如果发现图中 存在环路 ，则表示系统中 出现死锁 。

解除：

通常采用的方法是选择一个处理代价最小的事务，将其回滚，并释放此事务持有的全部锁。

封锁粒度

封锁对象的大小称为封锁粒度。封锁对象可以使一些逻辑单元，例如页（数据页、元组、关系、索引页、整个索引，甚至是数据库），也可以是物理单元，块，物理记录等。

封锁粒度越大，数据库所能够封锁的数据单元就越少，并发度越低，系统开销也就越小；反之则效果相反。

多粒度树

根节点是数据库，表示最大的数据粒度。

多粒度封锁协议

多粒度封锁协议允许多粒度树中的每个节点被独立的加锁。

对一个节点加锁意味着这个节点的所有后裔节点也被加以同样的锁。

• 显示封锁：直接加到数据对象上的锁。
• 隐式封锁：该数据对象没有事务对它独立加锁，但是由于其上级节点加锁而使该数据对象加上锁。

两种方法效果相同。因此系统检查时，两种都要查。对某个数据对象加锁，系统要进行时三种检查：

• 检查该数据对象上有无显示封锁与之冲突；
• 检查所有上级节点，看本事务是否与该数据对象上的隐式封锁冲突。
• 检查其所有下级节点，看前面的显示封锁是否与本事务的隐式封锁冲突。

然而，这样的检查效率很低，为了提高DBMS对每个数据对象加锁时系统的检查效率，引入：意向锁。

意向锁

含义：对一个节点加意向锁，这说明该节点的下层节点正在被加锁，对任意节点加锁时，必须先对它的上层节点加意向锁。

意向共享锁(IS锁)

如果对一个数据对象加 $IS$ 锁，表示它的后裔节点(拟意)向加 $S$ 锁。

意向排它锁(IX)

表示它的后裔节点拟（意向）加 $X$ 锁。

共享意向排它锁(SIX)

表示对它加 $SIX$ 锁，再加 $IX$ 锁。

数据备份与恢复

故障种类

能辨别各种故障类型。

事务内部的故障

有的是可以通过事务程序本身发现的，有的是非预期的，不能由事务程序处理的。如运算溢出、并发事务发生死锁而被选中撤销该事务、违反了某些完整性限制等。恢复操作称为事务撤销。

系统故障

是指造成系统停止运转的任何事件，使得系统要重新启动。例如，死机，特定类型的硬件错误（CPU故障）、操作系统故障、DBMS代码错误、系统断电等。恢复子系统必须在系统重启时，让所有非正常终止的事务回滚，强行撤销所有未完成的事务，还需要重做所有已提交的事务，以将数据库真正恢复到一致状态。

会造成内存数据丢失，影响正在运行的所有事务，但不破坏数据库。

介质故障

也叫硬故障。硬故障指外存故障，如磁盘损坏、磁头碰撞，瞬时强磁场干扰等。

会造成硬盘数据丢失，将破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务，破坏性最大。

计算机病毒

是一种人为的故障或破坏，是一些恶作剧者研制的一种计算机程序。

恢复方法（事务故障和系统故障,介质故障只要求静态转储）

静态转储：是在系统中无运行事务时进行的转储操作。即转储操作开始的时刻，数据库处于一致性状态，而转储期间不允许对数据库的任何存取、修改活动。显然，静态转储得到的一定是一个数据一致性的副本。

事务故障的恢复：

           （1）反向扫描日志文件，查找该事务的更新操作
           （2）对该事务的更新操作执行逆操作
           （3）继续反向扫描日志文件，查找该事务的其他更新操作，并做同样处理
           （4）如此处理下去，直至读到此事务的开始标记，事务故障恢复就完成了

系统故障的恢复：

           （1）正向扫描日志文件，找出在故障发生前已经提交的事务，将其事务标识记入重做队列。同时找到故障发生时尚未完成的事务，将其事务标识记入撤销队列。
           （2）对撤销队列中的各个事务进行撤销处理
           （3）对重做队列中的各个事务进行重做处理

介质故障的恢复：

            重装数据库，然后重做已完成的事务。
           （1）装入最新的数据库后备副本，使数据库恢复到最近一次转储时的一致性状态。
           （2）装入相应的日志文件副本，重做已完成的事务

日志文件的作用

• 事务故障恢复和系统故障恢复必须用日志文件
• 在动态转储方式中必须建立日志文件，后备副本和日志文件结合起来才能有效地恢复数据库
• 在静态转储方式中也可以建立日志文件

安全管理

数据库安全性

数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。

包括计算机硬件、操作系统、网络系统等的安全性，是紧密联系，互相支持的。

权限的授予与收回

定义用户存取权限称为授权。

两种类型：系统特权和对象特权。

自助存取控制

用户对不同的对象有不同的存取权限，不同的用户对同一对象也有不同的权限，用户可将自己拥有的存取权限转授给其他用户。比较灵活。

强制存取控制

每一个数据对象被标以一定的密级，每一个用户也被授予某一个级别的许可证，用户可以将自己拥有的存取权限转授给其他用户。比较严格。

授权

授予(GRANT)

格式

GRANT<权限>[,<权限>]...
ON<对象类型><对象名>[,<对象类型><对象名>]
TO<用户>[,<用户>]
[WITH GRANT OPTION]

若指定了WITH GRANT OPTION语句则允许用户把获得的某种权限再授予给其他的用户。
把查询reader表、创建表和修改reader表的权限授权给用户 ser1 和 user2,并允许它们将此权限授权给其他用户。

GRANT SELECT,CREATE TABLE,UPDATE(reader)
ON TABLE reader
TO user1,user2
WITH GRANT OPTION;

把对reader和borrow表的 全部操作权限 授权给 全体用户

GRANT ALL PRIVILEGES 
ON TABLE reader,borrow
TO PUBLIC;

user1可以把表reader的查询操作权限 授予user3并允许user3将此权限授予其他用户。

GRANT SELECT
ON TABLE reader
TO user3
WITH GRANT OPTION

收回(REVOKE)

格式：

REVOKE<权限>[,<权限>]...
ON<对象类型><对象名>[,<对象类型><对象名>]
FROM<用户>[,<用户>]...[CASCADE|RESTRICT]

把user1对reader表的SELECT权限收回

REVOKE SELECT
ON TABLE reader
FROM user1 CASCADE;

将用户user1的SELECT权限收回的时候必须级联（CASCADE）收回，否则系统拒绝（RESTRICT）执行此命令。因为user1将reader表的SELECT权限授予了user3，级联删除，将user1和user3的权限都收回。有的DBMS的默认值为CASCADE。

角色

角色是一组被命名的与数据库操作相关的权限，角色是权限的集合。

创建

CREATE ROLE <角色名>

给角色授权

GRANT <权限>[,<权限>]
ON<对象类型><对象名>
TO<角色>[,<角色>]

将角色分配给其他角色

GRANT <角色>[,<角色>]
TO<角色>[,<用户>]
[WITH ADMIN OPTION]

WITH ADMIN OPTION意思为：获得这种权限的角色或用户可以把这种权限再授予其他角色或用户。

角色权限的收回

REVOKE <角色>[,<角色>]
FROME<角色>[,<用户>]

学了这么久，累了吧。

累了困了，喝东鹏来吸口猫吧！

数据库编程

变量及流程控制语句

略（请自行看书），并不是重点也不是难点。

存储过程

存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。

存储过程的如下优点：

• 有利于实现模块化程序设计
• 提供更高的安全机制
• 减少网络流量
• 分离了客户端服务器端的开发任务

创建

CREATE PROC[EDURE]
[{@parameter data_type}[VARYING][]= default][OUTPUT]][,....n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
    存储过程执行体;

@parameter：过程中的参数。

data_type：参数数据类型。

default：参数默认值。

OUTPUT：表明参数是输出参数。

RECOMPILE：数据引擎不缓存该过程的计划，该过程在运行时编译。

ENCRYPTION：对储存过程的定义加密。

只能在当前和数据库中创建属于当前数据库的存储过程。

例子可以看书。

执行

• EXECUTE

[EXEC[UTE]]
    [@return_status=]{procedure_name|@procedure_name_var}
    [@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,....n]
[WITH RECOMPILE]        

@return_status：可选的整形变量，保存过程的返回状态。

procedure_name：存储过程名。

@parameter：参数。

value：参数值。

OUTPUT：指定存储过程必须返回一个参数。

DEFAULT：根据过程的定义，提供参数的默认值。

• CALL/PERFORM
  CALL/PERFORM 过程名([参数1,参数2……])

查看

Exec sp_help'名字'
Exec sp_helptext'名字'
Exec sp_depends'名字'

修改

ALTER PROC[EDURE]
[{@parameter data_type}[VARYING][]= default][OUTPUT]][,....n]
[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
    存储过程执行体;

删除

DROP PROCEDURE {存储过程名}[,....n]

触发器

触发器是一种特殊类型的存储过程，是一种很有效保证数据库完整性的手段。触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。

触发器的功能如下：

• 当表中的数据发生变化时，使与之相关联的表中的数据进行相应的更新。
• 禁止或回滚违反数据完整性的操作。
• 可以实现比CHECK约束更复杂的约束。
• 区分表在进行数据操作前后的状态差别，并可以完成基于这种差别的特定动作。

创建

CREATE TRIGGER <触发器>
{BEFORE|AFTER}<触发事件>ON<表名>
FOR EACH{ROW|STATEMENT}
[WITH<触发条件>]
<触发动作体>

触发事件：INSERT,DELETE,UPDATE。

触发动作体：可以使一个匿名PL/SQL语句块，或者是已创建的存储过程的调用。

当罚金有变动，打印信息。

CREATE TRIGGER trig1
ON borrow
FOR UPDATE
AS
IF UPDATE(fine)
 print'罚金有变动'

触发器分为行触发器和语句级触发器。

在reader表上创建了一个AFTER UPDATE 触发器。如果表reader有1000行，执行如下语句：

UPDATE reader SET Deptno=5;

• 如果触发器为语句级，则执行一次。
• 如果是行级，执行1000次。
  定义一个BEFORE行级触发器，为借阅表borrow定义完整性规则“罚金不得高于100元，如果高于100元，自动改为100元”。

CREATE TRIGGER Insert_Or_Update_fine
    BEFORE INSERT OR UPDATE ON borrow
    FOR EACH ROW
    AS BEGIN
        IF(new.fine>=100) THEN
        new.fine:=100
        END IF
    END;    

激活和执行触发器

顺序：

• 执行该表上的BEFORE触发器
• 激活触发器上的SQL语句
• 执行该表上的AFTER触发器

修改触发器

ALTER TRIGGERtrigger_name
ON{table|view}
{FOR|AFTER|INSTEAD OF}{[DELETE][,][INSERT][,][UPDATE]}
AS 触发动作体

删除触发器

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

嵌入式SQL

宿主语言

软件赖以生存的软件环境被称作是宿主环境(host environment).宿主环境可以是操作系统,服务器程序,应用程序,而开发这些宿主环境的程序语言(如开发操作系统一般使用c语言,开发WebServer一般使用c或java语言,开发应用程序一般使用C++/java/c#语言)被称作系统开发语言,或用一个更贴切的说法是—宿主语言(Host Language).
比方说你用VC做数据库，那么C++就是宿主语言，它是SQL的宿主.

游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。
概括来讲，SQL的游标是一种临时的数据库对象，即可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。
游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

• 定义

EXEC SQL DECLARE 游标名 [INSENSITIVE]CURSOR
        FOR 
            SQL 查询语句

• 打开

EXEC SQL OPEN 游标名

• 推进游标

EXEC SQL FETCH 游标名 INTO 主变量 

• 关闭游标

EXEC SQL CLOSE 游标名

嵌入式数据库使用

建立数据库连接

EXEC SQL CONNECT 数据库服务器名[AS连接名][USER用户名]

关闭数据库

EXEC SQL DISCONNECT [连接名]

好的，就先告一段落吧。