绪论

数据库系统概述

基本概念

数据

数据库(DB)：是长期储存在计算机内、有组织的、可共享的大量数据的集合，数据按一定的数据模型组织、描述和储存

可为各种用户共享
冗余度较小
数据独立性较高
易扩展

数据库管理系统(DBMS)

数据定义功能，数据定义语言：DDL
数据组织、存储和管理
数据操纵功能，数据操纵语言：DML
数据库的事务管理和运行管理
数据库的建立和维护功能
其他功能

数据库系统(DBS)：由数据库（DB）、数据库管理系统（DBMS）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统。

数据管理技术的产生和发展

文件系统：即专门处理数据的应用，程序员要关注记录的结构和不同文件中记录之间的联系，使用文件系统提供的fopen等操作来进行编程

数据库系统：只用一条语句就能实现查询操作

数据库系统特点

数据结构化：实现整体数据的结构化，是数据库系统与文件系统的本质区别。“整体”指的是数据库中的数据不再针对某一个应用，而是面向整个组织
数据的共享性高，冗余度低且易扩充：数据共享可以大大减少数据冗余，节约存储空间，且能避免数据之间的不相容性与不一致性；还容易添加新的应用，使得数据库系统弹性大，易于扩充。
数据独立性高：利用二级映像功能实现，简化应用程序的编制，减少应用程序的维护和修改
- 物理独立性：用户的应用程序与数据库中数据的物理存储是相互独立的
- 逻辑独立性：用户的应用程序与数据库的逻辑结构是相互独立的
数据由数据库管理系统统一管理和控制
- 数据的安全性保护：防止不合法使用造成的数据泄露和破坏
- 数据的完整性检查：保证数据的正确性、有效性和相容性
- 并发控制
- 数据库恢复

数据模型

组成要素

数据结构：描述数据库的组成对象，以及对象之间的联系。数据结构是对系统静态特性的描述

数据的操作：查询，增删改。数据操作是对系统动态特性的描述

数据的完整性约束：给定的数据模型中数据及其联系所具有的制约和依存规则

概念模型

也称信息模型，按用户的观点对数据和信息建模

基本概念

实体：客观存在并可相互区别的事物

属性：实体所具有的某一特性成为属性

码：唯一标识实体的属性

联系：是一支箭的联系通常是指不同实体集之间的联系。有一对一、一对多、多对多的类型

表示方式

实体-联系方法，即E-R模型

逻辑模型

层次模型

网状模型

关系模型

关系模型中数据的逻辑结构是一张二维表，它由行和列组成

关系：一个关系对应通常说的一张表。关系的每一个分量必须是一个不可分的数据项。

元组：表中的一行即为一个元组

属性：表中的一列即为一个属性，给每一个属性起一个名称即属性名

主码：也称码键。表中的某个属性组，它可以唯一确定一个元组

域：一组具有相同数据类型的值的集合

分量：元组中的一个属性值

优点

建立在严格的数学概念的基础上（关系代数等）
概念单一：实体和各类联系都用关系来表示，对数据的检索结果也是关系，数据结构简单清晰、用户易懂易用
关系模型的存取路径对用户透明，具有更高的数据独立性，更好的安全保密性，简化了程序员的工作和数据库开发建立的工作

半结构化数据模型

物理模型

描述数据在系统内部的表示方式和存取方式，或在磁盘或磁带上的存储方式和存取方式

数据库系统结构

三级模式结构

数据库系统通常采用三级模式结构，是数据库系统内部的系统结构

模式：数据库中全体数据的逻辑结构和特征的描述

外模式：数据库用户使用的局部数据的逻辑结构和特征的描述，是与某一应用有关的数据的逻辑表示

内模式：是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式

二级映射功能与数据独立性

外模式/模式映像
模式/内模式映像

保证数据的逻辑独立性和物理独立性

保证数据的逻辑独立性：当模式改变时，数据库管理员对外模式／模式映象作相应改变，使外模式保持不变，从而应用程序不必修改

保证数据的物理独立性：当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式／内模式映象，模式保持不变

关系数据库

关系数据结构及形式化定义

域：是一组具有相同数据类型的值的集合。

关系：$D1×D2×…×Dn$的子集叫作在域$D1，D2，…，Dn$上的关系，表示为 $R（D1，D2，…，Dn)$，（R：关系名，n：关系的目或度）

关系的表示：关系是一个二维表，表的每行对应一个元组，表的每列对应一个域

属性：关系中不同列可以对应相同的域，必须对每列起一个名字，称为属性，n目关系必有n个属性

码

候选码：若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码（特例：候选码只包含一个属性）
主属性：候选码的诸属性称为主属性，其他为非主属性
主码：若一个关系有多个候选码，则选定其中一个为主码
全码：关系模式的所有属性组是这个关系模式的候选码，称为全码

关系的类型：

基本关系：又称基本表或基表，是实际存在的表，是实际存储数据的逻辑表表示
查询表：查询结果对应的表
视图表：是由基本表或其他视图表导出的表，是虚表

关系的性质

列是同质的。每一列中的分量必须来自同一个域，必须是同一类型数据。
不同的列可出自同一个域。不同的属性要给予不同的属性名
列的顺序无所谓，列的次序可以任意交换
任意两个元组的候选码不能相同
行的顺序无所谓，行的次序可以任意交换
属性分量必须取原子值。属性不能是表格，包含多个值的组合。关系的每一个分量必须是一个不可分的数据项。

关系模式

关系模式

对关系的描述
是静态的、稳定的

关系

关系模式在某一时刻的状态或内容
是动态的、随时间不断变化的

关系模式和关系往往笼统称为关系

关系数据库

在一个给定的应用领域中，所有关系的集合构成一个关系数据库

关系数据库的型与值：

关系数据库的型：关系数据库模式，是对关系数据库的描述

关系数据库的值：关系模式在某一时刻对应的关系的集合，通常称为关系数据库

关系操作

集合操作方式。操作的对象和结果都是集合，一次一集合的方式

关系的完整性

实体完整性

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

参照完整性

若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系），则对于R中每个元组在F上的值必须为：

取空值（F的每个属性值均为空值）
等于S中某个元组的主码值

用户定义的完整性

针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求

关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不需由应用程序承担这一功能

关系代数

关系运算的记号

设关系模式为$R(A1，A2，…，An)$，它的一个关系设为R，$t\in R$ 表示t 是R 的一个元组，$t [A_i ]$则表示元组t 中相应于属性Ai 的一个分量
$A，t[A]， \overline {A}$，
- 若$A= \left \{Ai1，Ai2，…，Aik\right \}$，其中$Ai1，Ai2，…，Aik$是$A1，A2，…，An$中的一部分，则A称为属性列或属性组。
- $t[A]=(t[Ai1]，t[A2]，…，t[Ak])$表示元组t在属性列A上诸分量的集合。
- $\overline{A}$则表示$\left\{ A1，A2，…，An\right\}$中去掉$\left\{ Ai1,Ai2…，Aik\right\}$后剩余的属性组。
$\overset{\LARGE{\frown}}{trts}$，R为n目关系，S为m目关系。$tr\in R$，$ts\in S$， $\overset{\LARGE{\frown}}{trts}$称为元组的连接。$\overset{\LARGE{\frown}}{trts}$是一个n + m列的元组，前n个分量为R中的一

个n元组，后m个分量为S中的一个m元组。
象集$Zx$

给定一个关系$R(X，Z)$，X和Z为属性组。当$t[X]=x$时，x在R中的象集为：$x= \left\{t[Z]|t\in R,t[x]=x\right\}$，它表示R 中属性组X上值为x的诸元组在上分量的集合

关系运算

选择

取出行

投影

取出列

连接

扩展列、行

$R\Join S_{A\theta B}=\left\{ \overset{\LARGE{\frown}}{trts}|tr\in R\wedge ts\in S∧tr[A]θts[B]\right\}$

A和B：分别为R和S上度数相等且可比的属性组
θ：比较运算符

等值连接

θ为“＝”的连接运算称为等值连接

自然连接

自然连接是一种特殊的等值连接

两个关系中进行比较的分量必须是相同的属性组
在结果中把重复的属性列去掉

悬浮元组

两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，这些元组称为悬浮元组。

外连接

如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(Null)，就叫做外连接

除运算

给定关系$R (X，Y)$ 和$S(Y，Z)$，其中$X，Y，Z$为属性组。

$R$中的$Y$与$S$中的$Y$可以有不同的属性名，但必须出自相同的域集。

$R$与$S$的除运算得到一个新的关系$P(X)$，$P$是$R$中满足下列条件的元组在 $X$ 属性列上的投影：元组在$X$上分量值$x$的象集$Y_x$包含$S$在$Y$上投影的集合

先对SC关系在$(Sno,Cno)$属性上建立投影，然后逐一求出每一学生$(Sno)$的象集，并检查这些象集是否包含K

k：

${\textstyle \prod_{Sno,Cno}}(SC){\div} K$

SQl

SQL特点

综合统一：SQL语言集DDL，DML，DCL的功能于一体

DDL（数据定义语言，Data Definition Language） DDL 语句用于定义或修改数据库结构，包括数据库、表、视图等。常见的 DDL 命令有：
- CREATE：创建数据库、表、视图等。
- ALTER：修改已有的数据库对象，例如添加、删除或修改表中的列。
- DROP：删除数据库对象，如删除表或数据库。
- TRUNCATE：清空表中所有数据，但保留表结构。
用途：主要用于定义数据库的结构。

DML（数据操纵语言，Data Manipulation Language） DML 语句用于操作数据，包括插入、更新、删除和查询数据。常见的 DML 命令有：
- INSERT：插入数据到表中。
- UPDATE：更新表中的数据。
- DELETE：删除表中的数据。
- SELECT：查询表中的数据。
用途：主要用于增删改查数据库中的数据。

DCL（数据控制语言，Data Control Language） DCL 语句用于管理数据库的权限和安全性，控制用户对数据的访问。常见的 DCL 命令有：
- GRANT：授予用户特定的权限。
- REVOKE：撤销用户的特定权限。
用途：主要用于数据库的访问控制和权限管理。
高度非过程化，无需了解存储路径，存储路径的选择以及SQL的操作过程由系统自动完成
面向集合的操作方式
SQL既是自含式语言又是嵌入式语言
语言简洁，易学易用

数据定义

模式

定义

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

即：在CREATE SCHEMA中可以接受CREATE TABLE,CREATE VIEW,GRANT子句

如果没有指定<模式名>，那么<模式名>隐含为<用户名>

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

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATER TABLE TAB1(COL1 SMALLINT,
                   COL2 INT,
                   COL3 CHAR(20),
                   COL4 NUMERIC(10, 3),-- 共10位，整数部分7位，小数点后3位
                   COL5 DECIMAL(5, 2)-- 整数部分加小数部分总长度是5，小数部分长度是2，如果超过2会发生截断，不足2会补齐
                  );

删除

Drop SCHEMA <模式名><CASCADE|RESTRICT>

CASCADE表示同时把该模式中所有的数据库对象全部删除
RESTRICT表示如果模式中已经定义了下属的数据库对象，则拒绝执行删除该语句

基本表

定义

表：

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

eg：创建一个“课程”表Course

CREATE TABLE Course(
    Cno CHAR(4) PRIMARY KEY, -- 列级完整性约束条件，Cno是主码
    Cname CHAR(40) NOT NULL, -- 列级完整性约束条件，Cname不能取空值
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY(Cpno) REFERENCES Course(Cno) -- 表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno
    /*由此可见参照表和被参照表可以是同一个表*/
);

表所属模式：

法一：在表名中明显给出模式名

eg：CREATE TABLE "S-T".Student(...);表示Student所属的模式是S-T

法二：在创建模式语句同时建立表，例子见模式定义

法三：根据搜索路径来确定对象所属模式

搜索路径默认是$user, PUBLIC，表示先搜索与用户名相同的模式名，如果该模式名不存在，就使用PUBLIC模式

数据库管理员也可以自己设置搜索路径，eg：SET search_path To "S-T", PUBLIC;，然后定义基本表；那么如果S-T存在基本表就定义在了S-T模式中

修改

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

删除

DROP TABLE <表名> [CASCADE|RESTRICT];

索引

建立

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
-- UNIQUE表示每一个索引值只对应唯一的数据记录
-- CLUSTER表示建立的索引是聚簇索引
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

次序：ASC（升序），DESC（降序）；默认ASC

eg：CREATE UNIQUE INDEX Scno ON SC (Sno ASC, Cno DESC)，表示SC表按学号升序和课程号降序建立唯一索引

修改

ALTER INDEX <旧索引名> RENAME TO <新索引名>

删除

DROP INDEX <索引名>

数据查询

单表查询

选择表中的若干列

查询指定列：

1 2	SELECT <目标表达式>[列表题别名][,<目标表达式>[列标题别名]]... -- 目标表达式可以是算术表达式、字符串常量、函数等 FROM <表名或视图名>

eg：

SELECT Sname NAME, 'Year of Birth:'BIRTH, 2014-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT 
-- 字符串常量'Year of Birth:'列的别名是BIRTH
-- 算术表达式2014-Sage列的别名是BIRTHDAY
-- 函数LOWER(Sdept)列的别名是DEPARTMENT 
FROM Student;

查询全部列：

SELECT *

选择表中若干元组

消除取值重复的行

SELECT [ALL|DISTINCT]<目标表达式>[列表题别名][,<目标表达式>[列标题别名]]...
-- DISTINCT消除重复的行
-- ALL保留重复的行
FROM <表名或视图名>

查询满足条件的元组
1
2
3
SELECT [ALL|DISTINCT]<目标表达式>[列表题别名][,<目标表达式>[列标题别名]]...
FROM <表名或视图名>
WHERE <条件表达式> -- WHERE语句实现
- 确定集合：IN/NOT IN
- 字符串匹配：[NOT] LIKE'<匹配串>' [ESCAPE '<换码字符>']
  - <匹配串>中可以含有通配符%（表示任意长度的字符串，长度可以为0）和_（表示任意单个字符）
  - 如果匹配串中不含通配符，LIKE可以用=代替
  - 如果要查询的字符串中本身含有%或_，那么就要用[ESCAPE '<换码字符>']进行转义
  eg：查询以"DB_"开头，且倒数第三个字符为i的课程的详细信息
  1
  2
  3
  SELECT *
  FROM Course
  WHERE Cname LIKE'DB\_%i_ _' ESCAPE'\'
- 空值的查询：IS [NOT] NULL
- 多重条件查询：AND/OR(AND优先级高于OR)

`ORDER BY`

SELECT [ALL|DISTINCT]<目标表达式>[列表题别名][,<目标表达式>[列标题别名]]...
FROM <表名或视图名>
WHERE <条件表达式>
ORDER BY <列名> [ASC|DESC] -- ASC升序，DESC降序；默认为ASC

聚集函数

`GROUP BY`

SELECT [ALL|DISTINCT]<目标表达式>[列表题别名][,<目标表达式>[列标题别名]]...
FROM <表名或视图名>
WHERE <条件表达式>
GROUP BY <列名1> [HAVING<条件表达式>] -- 将查询结果按某一列或多列的值进行分组，HAVING语句指定筛选条件 
ORDER BY <列名2> [ASC|DESC]

$\color{red}{注：}$WHERE作用于基本表或者视图；HAVING作用于组

例：查询每个部门的总的薪水数

1
2
3

SELECT DEPT, sum(SALARY) AS total
FROM STAFF
GROUP BY DEPT -- 是每个部门的而不是整个公司的

连接查询

等值与非等值连接查询

/*连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词*/
-- 连接谓词：
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名3>.]<列名3>

自然连接：把目标列中重复的属性列去掉

自身连接

给同一个表取两个别名，按照不同表的连接方式连接即可

外连接

连接两个表的时候，可能有的元素因为不满足条件，没有出现在最终的表格中；利用外连接可以在不满足条件的地方填上NULL。左外连接，保留左边的表，右边的表不足的时候填上NULL；右外连接同理。

eg：

SELECT <列名1>[,<列名2>]...
FROM <表名1> LEFT OUTER JOIN <表名2> ON([表名1.]<列名1>=[表名2.]<列名2>) -- 左外连接
FROM <表名1> RIGHT OUTER JOIN <表名2> ON([表名1.]<列名1>=[表名2.]<列名2>) -- 右外连接
-- 可以用USING来去掉结果中的重复值：FROM <表名1> RIGHT OUTER JOIN <表名2> USING(列名)

多表连接

1
2
3

SELECT [ALL|DISTINCT]<目标表达式>[,<目标表达式>]...
FROM <表名或视图名>[,<表名或视图名>]...
WHERE <条件表达式>

嵌套查询

$\color{red}{注：}$子查询的SELECT语句中不能使用ORDER BY

带有IN的子查询

不相关子查询：子查询条件不依赖父节点

相关子查询：子查询条件依赖父节点

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

/*Sno--学号 Cno--课程号 SC--存储学号、课程号、成绩的表格 */
SELECT Sno, Cno
FROM SC x
WHERE GRADE >= (SELECT AVG(GRADE)
                FROM SC y
                WHERE x.Sno = y.Sno -- 表示是同一个学生的成绩
               );

带有比较运算符的子查询
带有ANY（SOME）或ALL的子查询
- ANY -- 某个； ALL -- 所有
- 可以用聚集函数+比较运算符代替ANY/ALL，且聚集函数效率更高
  
  eg：<ANY 等价于 <MAX

带有EXISTS/NOT EXISTS的子查询

EXISTS：如果内层查询非空，返回真；否则返回假

NOT EXISTS：如果内层非空，返回假；否则返回真

由于没有全称量词，所以需要将题目转换成等价的用存在量词的形式

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

/*等价于查询 找到学生 没有一门课是ta不选的*/
SELECT Sname
FROM Student
WHERE NOT EXISTS -- 不存在
	(
        SELECT *
        FROM Course
        WHERE NOT EXISTS -- 有一门课程没有ta
        	(
                SELECT *
                FROM SC
                WHERE Sno = Student.Sno AND Cno = Course.Cno
            )
    );

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

/*等价于查询学生x： 不存在这样的课程y 学生201215122选修了y 但是学生x没选修*/
SELECT DISTINCT Sno
FROM SC SCX -- 查询学生x
WHERE NOT EXISTS
	(
        SELECT * -- 查询课程y
        FROM SC SCY
        WHERE SCY.Sno = '201215122' AND NOT EXISTS -- 20121522选修了
                                (
                                    SELECT *
                                    FROM SC SCZ
                                    WHERE SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno -- 就是查找学生x的学号是否在里面，以及201215122是否选修了这门课程；
-- 如果发现结果是后面的判断成立，前面的判断不成立，即“学生201215122选修了y 但是学生x没选修”，就返回空，又因为是NOT EXISTS所以表达式是True；于是第二层判断是True，那么又因为是NOT EXISTS所以第二层整体式False，那么这个就不是要找的学生x
                                )
    );

集合查询

集合操作主要包括：并UNION，交INTERSECT，差EXCEPT

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象

SELECT [ALL|DISTINCT]<目标表达式>[列表题别名][,<目标表达式>[列标题别名]]...
FROM <表名或视图名>[,<表名或视图名>...]|(<SELECT语句>[AS]<别名>)-- 通过FROM生成派生表时AS关键字可以省略，但必须为派生表指定一个别名
WHERE <条件表达式>
GROUP BY <列名1> [HAVING<条件表达式>] 
ORDER BY <列名2> [ASC|DESC]

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

SELECT Sno, Cno
FROM SC, (SELECT Sno, AVG(Grade)
          FROM SC
          GROUP BY Sno
         )AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade;

数据更新

插入数据

插入元组：

1
2
3

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

INTO中没有出现的属性列默认取空值
如果INTO语句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值
VALUES子句对新元组的各属性赋值，字符串常数要用单引号括起来

插入子查询：

1
2
3

INSERT
INTO <表名>[(<属性列1>)[,<属性列2>...]]
子查询；-- SELECT语句

修改数据

1
2
3

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

删除数据

1
2
3

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

视图

1
2
3

CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS<子查询>
[WITH CHECK OPTION] -- 表示在对视图进行操作时保证更新、插入或删除的行满足视图中子查询中的条件表达式

组成视图的属性名或者全省略或者全部指定
以下情况必须明确指定组成视图的所有列名
- 某个列式聚集函数或列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
把对视图的操作都转换成对基本表的操作

优点：

能多视角看待同一数据
简化用户操作
对机密数据提供安全保护
对重构数据库提供了一定程度的逻辑独立性

数据库安全性

数据库安全性主要包括用户身份鉴别、多层存取控制、审计、视图和数据加密等安全技术。

自主存取控制与强制存取控制共同构成数据库管理系统的安全机制

身份鉴别

静态口令鉴别：静态口令一般由用户自己设定，这些口令是静态不变的

动态口令鉴别：口令是动态变化的，每次鉴别时均需使用动态产生的新口令登录数据库管理系统，即采用一次一密的方法

生物特征鉴别：通过生物特征进行认证的技术，生物特征如指纹、虹膜和掌纹等

智能卡鉴别：智能卡是一种不可复制的硬件，内置集成电路的芯片，具有硬件加密功能

自主存取控制DAC

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

通过 SQL 的GRANT 语句和REVOKE 语句实现对数据的操作权限

GRANT

GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]... -- PUBLIC表示所有用户
[WITH GRANT OPTION]; -- 获得该权限的用户可以把权限授予其他用户

具体实例：

全部权限的授予

1	GRANT ALL PRIVILIGES

授予所有用户

1
2
3

GRANT SELECT 
ON TABLE SC 
TO PUBLIC;

对属性列的授权

1
2
3

GRANT UPDATE(Sno), SELECT -- 明确指出列名
ON TABLE Student 
TO U4;

REVOKE

1
2
3

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

具体实例：

收回所有用户对表SC的查询权限

1
2
3

REVOKE SELECT 
ON TABLE SC 
FROM PUBLIC;

把用户U5对SC表的INSERT权限收回

REVOKE INSERT 
ON TABLE SC 
FROM U5 
CASCADE;-- 系统只收回直接或间接从U5处获得的权限，如果U6从U5获得了INSERT权限，但是同时从U4获得了INSERT权限，则U6仍有INSERT权限

对创建数据库模式一类的数据库对象的授权则由数据库管理员在创建用户时实现。

创建用户

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

只有系统的超级用户才有创建一个新的数据库用户的权限
默认拥有CONNECT权限
拥有RESOURCE的用户可以创建基本表和视图，但不能创建模式
拥有DBA的用户时超级用户

角色

角色是权限的集合

角色的创建：

1	CREATE ROLE <角色名>

给角色授权:

1
2
3

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

将一个角色授予其他的角色或用户:

1
2
3

GRANT <角色1>[,<角色2>]… 
TO <角色3>[,<用户1>]… 
[WITH ADMIN OPTION]-- 表示获得某种权限的角色或用户可以把这种权限再授予其他角色

角色权限的收回：

1
2
3

REVOKE <权限>[,<权限>]…
ON <对象类型> <对象名>
FROM <角色>[,<角色>]…

强制存取控制MAC

每一个数据对象被标以一定的密级
每一个用户也被授予某一个级别的许可证

该机制只通过对数据的存储权限来进行安全控制，而数据本身并无安全标记。

全部实体被分成主体和客体：

主体：系统中的活动实体，包括数据库管理系统所管理的实际用户和用户的各种进程

客体：系统中的被动实体，受主体操纵，包括文件、基本表、索引、视图

对于主体和客体，DBMS为它们每个实例指派一个敏感度

敏感度标记分成若干级别：

绝密（Top Secret，TS）
机密（Secret，S）
可信（Confidential，C）
公开（Public，P）

TS>=S>=C>=P

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

TCSEC/TDI安全级别划分：

C1级

能够实现对用户和数据的分离，进行自主存取控制（DAC），保护或限制用户权限的传播。

C2级

安全产品的最低档次
提供受控的存取保护，将C1级的DAC进一步细化，以个人身份注册负责，并实施审计和资源隔离

B1级

标记安全保护。“安全”或“可信的” 产品。
对系统的数据加以标记，对标记的主体和客体实施强制存取控制（MAC）、审计等安全机制

CC评估保证级（EAL）划分：

视图机制

把要保密的数据对无权存取这些数据的用户隐藏起来，对数据提供一定程度的安全保护

审计机制

启用一个专用的审计日志，将用户对数据库的所有操作记录在上面。

审计员利用审计日志，监控数据库中的各种行为，找出非法存取数据的人、时间和内容。

C2以上安全级别的DBMS必须具有审计功能。

审计事件：服务器事件，系统权限，语句事件，模式对象事件

防止审计员误删审计记录，审计日志必须先转储后删除

只允许审计员查阅和转储审计记录，不允许任何用户新增和修改审计记录等

设置和取消审计

AUDIT语句：设置审计功能

NOAUDIT语句：取消审计功能

实例：

对修改SC表结构或修改SC表数据的操作进行审计

1 2	AUDIT ALTER,UPDATE ON SC;

取消对SC表的一切审计

1 2	NOAUDIT ALTER,UPDATE ON SC;

数据加密

数据加密：防止数据库中数据在存储和传输中失密的有效手段

加密的基本思想：根据一定的算法将原始数据—明文变换为不可直接识别的格式—密文

加密方法：

存储加密
- 透明存储加密
  1. 内核级加密保护方式，对用户完全透明
  2. 将数据在写到磁盘时对数据进行加密，授权用户读取数据时再对其进行解密
  3. 数据库的应用程序不需要做任何修改，只需在创建表语句中说明加密的字段即可
  4. 内核级加密方法: 性能较好，安全完备性较高
- 非透明存储加密:通过多个加密函数实现
传输加密
- 链路加密
  1. 在链路层进行加密
  2. 传输信息由报头和报文两部分组成,报文和报头均加密
- 端到端加密
  1. 在发送端加密，接收端解密
  2. 只加密报文不加密报头
  3. 所需密码设备数量相对较少，容易被非法监听者发现并从中获取敏感信息

数据库完整性

DBMS的完整性控制机制具有：定义功能、检查功能、违约处理功能。

实体完整性

关系模型中以主码作为唯一性标识，主属性不能取空值。

定义主码：

如果只有一个属性是码，可以在列后加上PRIMARY KEY，也可以在表的最后表级定义主码，PRIMARY KEY (Sno)
如果多个属性是主码，只能在标的最后表级定义主码，PRIMARY KEY (Sno,Cno)

实体完整性的检查

插入或对主码列进行更新操作时，进行实体完整性规则检查：

检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。
检查主码值是否唯一，如果不唯一则拒绝插入或修改。

检查记录中的主码，如果使用全表扫描，非常耗时，所以数据库管理系统一般会在主码上自动建立一个索引，通过B+树索引查找基本表中是否已经存在新的主码值

参照完整性

外码或者取空值，或者等于对应元组的某个存在的主码值

在表级定义参照完整性：FOREIGN KEY (Cno) REFERENCES Course(Cno)

参照完整性的检查

可能得违规行为：

处理方式：

拒绝执行NO ACTION，该策略为默认策略
级联操作CASCADE，当删除或修改被参照表的一个元组导致参照表的不一致时，删除或修改参照表中的所有导致不一致的元组
设置为空值

显示说明参照完整性的违规处理情况

CREATE TABLE SC
( 
    Sno CHAR(9) NOT NULL，
    Cno CHAR(4) NOT NULL，
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno)，
    FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON DELETE CASCADE /*级联删除SC表中相应的元组*/
    ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
    ON DELETE NO ACTION 
    /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
    ON UPDATE CASCADE 
    /*当更新course表中的cno时，级联更新SC表中相应的元组*/
);

用户定义的完整性

提供定义和检验这类完整性的机制

不允许取空值：

CREATE TABLE SC
( 
     Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,
     Grade SMALLINT NOT NULL,
     PRIMARY KEY (Sno, Cno), 
 …
/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义可以不写*/
);

列值唯一：

CREATE TABLE DEPT 
( 
    Deptno NUMERIC(2),
    Dname CHAR(9) UNIQUE NOT NULL，
    Location CHAR(10), PRIMARY KEY (Deptno) 
);

用CHECK短语指定列值应该满足的条件：

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

CREATE TABLE Student
( 
    Sno CHAR(9), 
    Sname CHAR(8) NOT NULL，
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY (Sno),
    CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
    /*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
);

完整性约束命名

1	CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

实例：

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

修改完整性限制：

先DROP再ADD：

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

断言

任何使断言不为真值的操作都会被拒绝执行

1	CREATE ASSERTION<断言名><CHECK 子句>

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

CREATE ASSERTION ASSE_SC_DB_NUM 
CHECK (60 >=
(
    select count(*) /*此断言的谓词涉及聚集操作count的SQL语句*/
	From Course,SC
	Where SC.Cno=Course.Cno and Course.Cname ='数据库') 
);

修改断言：

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

ALTER TABLE SC 
ADD TERM DATE; -- 首先需要修改SC表，增加一个“TERM”属性，类型为DATE
CREATE ASSERTION ASSE_SC_CNUM2
CHECK(60 >= ALL(
    SELECT count(*) 
    FROM SC
	GROUP by cno,TERM)
);

触发器

触发器保存在数据库服务器中
任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力
表的拥有者才可以在表上创建触发器
触发器只能定义在基本表上，不能定义在视图

定义：

CREATE TRIGGER <触发器名> 
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

详细说明：

FOR EACH ROW是行级触发器，FOR EACH STATEMENT是语句级触发器

eg：假设表TEACHER有1000行，在表上创建了一个触发器，触发事件是UPDATE TEACHER SET Deptno=5;
- 如果是语句级触发器，那么执行完UPDATE后触发动作执行一次
- 如果是行级触发器，触发动作执行1000次
如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行
如果是行级触发器，用户都可以在过程体中使用NEW和OLD引用事件之后的新值和事件之前的旧值；如果是语句级触发器，则不能使用NEW或OLD进行引用

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

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING OLD row AS OldTuple, NEW row AS NewTuple
FOR EACH ROW 
WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

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

CREATE TRIGGER Student_Count
AFTER INSERT ON Student 
/*指明触发器激活的时间是在执行INSERT后*/ 
REFERENCING NEW TABLE AS DELTA
FOR EACH STATEMENT 
/*语句级触发器, 即执行完INSERT语句后下面的触发动作体才执行一次*/
INSERT INTO StudentInsertLog (Numbers)
SELECT COUNT(*)
FROM DELTA

删除触发器：

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

delimiter

默认情况下，delimiter是分号“;”。在命令行客户端中，如果有一行命令以分号结束，那么回车后mysql将会执行该

命令。但有时候，不希望MySQL这么做。因为可能输入较多的语句，且语句中包含有分号。

drop trigger s_t.SC_trigger ;
delimiter //
create trigger SC_trigger after update on sc for each row
begin 
    if 
    	new.grade >= 95 then 
    	update student set scholarship='是’ 
   		where (scholarship='否') AND (student.sno=new.sno) ;
    end if;
end;//

关系数据库理论

数据依赖：一个关系内部属性与属性之间的一种约束关系，主要类型包括函数依赖(FD)和多值依赖(MVD)。

函数依赖

概念：设关系$R(U)$，U是属性集，令$X,Y$是U的两个子集，对于任意R的一个关系r，X的每一个具体值，Y都有唯一值与之对应则称X决定函数Y或Y函数依赖于X，记作$X→Y$（X是自变量，Y是因变量，一个X只能得到一个Y）

非平凡的函数依赖：$X\to Y$，但$Y\not\subset X$

平凡的函数依赖：$X\to Y$，但$Y\subset X$

$X\leftarrow \rightarrow Y$：$X\to Y,Y\to X$

$X\nrightarrow Y$：Y不依赖于函数X

完全函数依赖：若$X\to Y$，且对于X的任何一个真子集X'，都有$X'\not\to Y$，则称Y对X完全函数依赖，记作：$X\stackrel{F}{\longrightarrow} Y$

部分函数依赖：若$X\to Y$，但Y对X不完全函数依赖，则称Y对X部分函数依赖，记作：$X\stackrel{P}{\longrightarrow} Y$

传递函数依赖：如果$X\to Y(Y\not\subset X),Y\not\to X,Y\to Z(Z\not\subset Y)$则称Z对X传递函数依赖

$\color{red}{注：}$ 如果$Y→X$, 即$X←→Y$，则Z直接依赖于X，而不是传递函数依赖。

码

K为属性或属性组合

超码：U函数依赖于K，即$K\to U$，则称K是超码

候选码：若$K $U，则K为R的候选码

主码：若候选码多于一个，选定一个为主码

主属性：包含在任何一个候选码中的属性

非主属性/非码属性：不包含在任何一个候选码中的属性

全码：整个属性组是码

eg： $R(P,W,A)$ ，P：演奏者 W：作品 A：听众。一个演奏者可以演奏多个作品，某一作品可被多个演奏者演奏，听众可以欣赏不同演奏者的不同作品，码为$(P,W,A)$为全码

求候选码：将左部、右部出现的元素分别列出，划去同时出现在两边的属性，剩下的只出现的左部的一定是主属性；出现在右部的一定不是主属性。

多值依赖

概念：设$R(U)$是属性集U上的一个关系模式,$X,Y,Z$ 是U 的子集，并且$Z=U-X-Y$，关系模式$R(U)$中多值依赖$X→→Y$ 成立，当且仅当对$R(U)$的任一关系r，给定的一对$(x,z)$值，有一组Y的值，这组值仅仅决定于x 值而与z 值无关。

eg：

$Teaching（C, T, B）$对于C的每一个值，T有一组值与之对应，而不论B取何值。因此T多值依赖于C，即$C→→T$。

平凡的多值依赖：若$X→→Y$，而$Z=\varnothing$，则称$X→→Y$为平凡的多值连接

性质

对称性：若$X→→Y$，则$X→→Z$，其中$Z=U-X-Y$

传递性：若$X→→Y$，$Y→→Z$，则$X→→Z-Y$

函数依赖可以看做多值依赖的特殊情况。

若$X→→Y$，$X→→Z$，则$X→→YZ$。

若$X→→Y$，$X→→Z$，则$X→→Y\cap Z$。

若$X→→Y$，$X→→Z$，则$X→→Y-Z,X→→Z-Y$。

函数依赖和多值依赖的比较

多值依赖的有效性与属性集的范围有关。即若$X→→Y$在U上成立，则在$W(XY\subset W\subset U)$上一定成立；反之则不然。因为多值依赖的定义中不仅涉及属性组X和Y，而且涉及U中其余属性Z
函数依赖$X→Y$在$R(U)$上成立，则对于任何$Y'\subset Y$均有$X→Y'$成立

多值依赖$X→→Y$在$R(U)$上成立，却不能断言对于任何$Y'\subset Y$均有$X→→Y'$成立

eg：$A\to \to BC$，但是$A\to \to B$不成立

范式

各范式之间的关系：$5NF\subset 4NF\subset BCNF\subset 3NF\subset 2NF\subset 1NF$

规范化：一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式的集合。

1NF：每个列属性都是不可分的数据项。

存在问题：数据冗余、更新异常、插入异常、删除异常

2NF：若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于任何一个候选码，则R∈2NF

单属性码的关系模式必然属于2NF

3NF：设关系模式$R<U,F>∈1NF$,若R中不存在这样的码X、属性组Y及非主属性$Z(Z \not\subset Y)$, 使得$X→Y，Y→Z$成立，$Y\not\to X$不成立，则称$R<U,F > ∈ 3NF$。

双目的关系模式必属于3NF。

eg：

BCNF：设关系模式$R<U,F >∈1NF$，若$X→Y$ 且 $Y \not\subset X$时X 必含有码，则R<U,F >∈BCNF。即如果每一个决定属性集（左部）都包含候选码，则R∈BCNF。

所有非主属性对每一个码都是完全函数依赖
所有主属性对每一个不包含它的码也是完全函数依赖
没有任何属性完全函数依赖于非码的任何一组属性
一个候选码，一定是BCNF；全码一定是BCNF。

如果一个关系数据库中的所有关系模式都属于BCNF，那么在函数依赖范畴内，它已实现了模式的彻底分解，达到了最高的规范化程度，消除了插入异常和删除异常。

4NF：关系模式R<U,F >∈1NF，如果对于R 的每个非平凡多值依赖X→→Y（Y ⊈ X），X 都含有码，则$R<U,F>∈4NF$。

限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。4NF所允许的非平凡多值依赖实际上是函数依赖。

5NF：如果关系模式R中的每一个连接依赖均由R的候选码所隐含，则称此关系模式符合第五范式

判断方式：其中超键表示含有候选码的属性组合

规范化基本步骤：

数据库设计

需求分析

数据字典

数据项：数据项是不可再分的数据单位。

数据项描述={ 数据项名,数据项含义说明,别名,数据类型,长度,取值范围,取值含义,与其他数据项的逻辑关系,数据项之间的联系 }

数据结构：数据结构反映了数据之间的组合关系。

一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成。

数据结构描述= {数据结构名，含义说明，组成:{数据项或数据结构}}

数据流：数据流是数据结构在系统内传输的路径。

数据流描述= {数据流名,说明,数据流来源,数据流去向, 组成:{数据结构}, 平均流量,高峰期流量}

数据存储：数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一。

数据存储描述= {数据存储名,说明,编号,输入的数据流 ,输出的数据流,组成:{数据结构},数据量,存取频度,存取方式}

处理过程：处理过程一般用判定表或判定树来描述。

处理过程描述= {处理过程名,说明,输入:{数据流}, 输出:{数据流},处理:{简要说明}}

概念结构设计

E-R模型

E-R图中所表示的联系是实体之间的联系。

实体：用矩形表示，矩形框内写明实体名

属性：用椭圆形表示，并用无向边将其与相应的实体型连接起来

联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1∶1，1∶n或m∶n）

实例：某个工厂物资管理的概念模型

E-R图的集成

合并：解决各分E-R图之间的冲突，将分E-R图合并起来生成初步E-R图。

冲突主要包括：属性冲突、命名冲突、结构冲突
修改和重构。消除不必要的冗余，生成基本E-R图。

优化

对关系模式进行必要分解，提高数据操作效率和存储空间的利用率。

逻辑结构设计

把概念结构设计阶段设计好的基本E-R图转换为与选用数据库管理系统产品所支持的数据模型相符合的逻辑结构

物理结构设计

数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于选定的数据库管理系统。

数据库管理系统常用存取方法

B+树索引存取方法
Hash索引存取方法
聚簇存取方法

聚簇：为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块中称为聚簇。

聚簇索引：在一个基本表上最多只能建立一个聚簇索引

聚簇索引的适用条件：

很少对基表进行增删操作
很少对其中的变长列进行修改操作

数据库实施、运行和维护

数据库编程

嵌入式SQL

为了区分SQL语句与主语言语句，所有SQL语句必须加前缀EXEC SQL，C语句格式： EXEC SQL <SQL语句>;

与主语言之间的通信

SQL通信区：系统给应用程序若干信息，主要包括系统当前工作状态和运行环境的各种数据。

使用EXEC SQL INCLUDE SQLCA定义通信区

通信区的变量SQLCODE存放每次执行SQL语句后返回的代码；每执行完一条SQL语句都应该测试一下SQLCODE的值

主变量：嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据

在SQL语句中使用主变量时，为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志

-- 主变量和指示变量在中间定义
BEGIN DECLARE SECTION
……
END DECLARE SECTION

游标：为用户开设的一个数据缓冲区

每个游标区都有一个名字，用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

建立和关闭数据库连接：

建立数据库连接：EXEC SQL CONNECT TO target[AS connection-name][USER user-name];

target是要连接的数据库服务器

常见的服务器标识串，如<dbname>@<hostname>:<port>

关闭数据库连接： EXEC SQL DISCONNECT [connection];

实例：

不使用游标的SQL语句

查询结果为单记录的SELECT语句：

EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO:Hsno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno=:givensno; /*使用主变量givensno的值进行查询*/

非CURRENT形式的增删改语句：

在UPDATE的SET字句和WHERE子句中可以使用主变量，SET子句还可以使用指示变量

gradeid=-1； /*gradeid为指示变量，赋为负值*/
EXEC SQL INSERT INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr :gradeid)； /*:stdno,:couno,:gr为主变量*/
-- 由于该学生刚选修课程，成绩应为空，所以要把指示变量赋为负值

指示变量：是一个整型变量，用来“指示”所指主变量的值或条件

一个主变量可以附带一个指示变量指示变量的用途，指示输入主变量是否为空值
检测输出变量是否为空值，值是否被截断

使用游标的SQL语句

查询结果为多条记录的SELECT语句

游标的使用

说明游标：EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>;

打开游标：EXEC SQL OPEN <游标名>;

推进游标指针并取当前记录：EXEC SQL FETCH <游标名> INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...;

关闭游标： EXEC SQL CLOSE <游标名>;

CURRENT形式的UPDATE和DELETE语句

因为UPDATE和DELETE语句都是集合操作，如果只想修改或删除其中的某个数据，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的UPDATE和DELETE语句修改或删除之。即UPDATE语句和DELETE语句中要用子句：WHERE CURRENT OF <游标名>

不能使用CURRENT形式的UPDATE语句和DELETE语句：

当游标定义中的SELECT语句带有UNION或ORDER BY子句
该SELECT语句相当于定义了一个不可更新的视图

动态SQL

允许在程序运行过程中临时“组装”SQL语句
支持动态组装SQL语句和动态参数两种形式

eg：创建基本表TEST

EXEC SQL BEGIN DECLARE SECTION;
	const char *stmt="CREATE TABLE test(a int);"; 
	/*SQL语句主变量，内容是创建表的SQL语句*/
EXEC SQL END DECLARE SECTION;
... 
EXEC SQL EXECUTE IMMEDIATE :stmt;
/*执行动态SQL语句*/

向TEST中插入元组

EXEC SQL BEGIN DECLARE SECTION;
	const char *stmt = "INSERT INTO test VALUES(?);";
	/*声明SQL主变量内容是INSERT语句 */
EXEC SQL END DECLARE SECTION;
... 
EXEC SQL PREPARE mystmt FROM :stmt; /*准备语句*/
... 
EXEC SQL EXECUTE mystmt USING 100; /*设定INSERT语句插入值100 */
EXEC SQL EXECUTE mystmt USING 200; /* 设定INSERT语句插入值200 */

使用参数符号（?）表示该位置的数据在运行时设定
通过 PREPARE语句准备主变量和EXECUTE绑定数据或主变量来完成

过程化SQL

变量和常量定义

变量

1 2	变量名数据类型 [[NOT NULL]:= 初值表达式] 变量名数据类型 [[NOT NULL] 初值表达式]

常量

1	常量名数据类型 CONSTANT:= 常量表达式

流程控制

条件控制

IF condition THEN
	...
ELSE 
	...
END IF;

循环控制

--LOOP
LOOP
	...
END LOOP;
-- WHILE-LOOP
WHILE condition LOOP
	...
END LOOP;
-- FOR-LOOP
FOR count IN[REVERSE]bound1 ... bound2 LOOP
	...
END LOOP;
-- 无REVERSE：count从bound1到bound2
-- 有REVERSE：count从bound2到bound1

存储过程

存储过程的优点

1）运行效率高

2）降低了客户机和服务器之间的通信量

3）方便实施企业规则

存储过程的用户接口

1）创建存储过程

1 2	CREATE OR REPLACE PROCEDURE 过程名([参数1，参数2，...]) AS <过程化SQL块>

2）执行存储过程

1	CALL/PERFORM PROCEDURDE 过程名([参数1，参数2，...])

3）修改存储过程

1 2	ALTER PROCEDURE 过程名1 RENAME TO 过程名2 ALTER PROCEDURE 过程名 COMPILE

4）删除存储过程

1	DROP PROCEDURE 过程名();

函数

1）定义

1 2	CREATE OR REPLACE FUNCTION 函数名([参数1，参数2，...]) RETURNS<类型> AS <过程化SQL块>

2）执行

1	CALL/SELECT 函数名([参数1，参数2，...])

3）修改

1 2	ALTER FUNCTION 过程名1 RENAME TO 过程名2 ALTER FUNCTION 函数名 COMPILE

ODBC编程

用户应用程序
ODBC驱动程序管理器
数据库驱动程序
ODBC数据源管理

ODBC应用程序包括的内容

请求连接数据库
向数据源发送SQL语句
为SQL语句执行结果分配存储空间，定义所读取的数据格式
获取数据库操作结果或处理错误
进行数据处理并向用户提交处理结果
请求事务的提交和回滚操作
断开与数据源的连接

ODBC应用程序不能直接存取数据库

其各种操作请求由驱动提交给关系数据库管理系统的ODBC驱动程序所支持的函数来存取数据库

ODBC 3.0 标准提供了76个函数接口

分配和释放环境句柄、连接句柄、语句句柄(32位整数，指针)
连接函数（SQLDriverconnect等）
与信息相关的函数（SQLGetinfo、SQLGetFuction等）
事务处理函数（如SQLEndTran）
执行相关函数（SQLExecdirect、SQLExecute等）
编目函数，ODBC 3.0提供了11个编目函数，如SQLTables、SQLColumn等。应用程序可以通过对编目函数的调用来获取数据字典的信息，如权限、表结构等

ODBC数据源

指定唯一的数据源名（Data Source Name，简称DSN），并映射到用户名、服务器名、所连接的数据库名等

工作流程

ret=SQLConnect(kinghdbc,“KingbaseES ODBC”, SQL_NTS,“SYSTEM”, SQL_NTS, "MANAGER",SQL_NTS);//配置数据源
ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&kinghenv);//初始化环境
ret=SQLConnect(serverhdbc,"SQLServer", SQL_NTS, "sa“,SQL_NTS, "sa",SQL_NTS);//建立连接
ret=SQLAllocHandle(SQL_HANDLE_STMT,kinghdbc, &kinghstmt);//分配语句句柄
ret=SQLExecDirect(kinghstmt,"SELECT * FROM STUDENT",SQL_NTS);//执行SQL语句
ret=SQLBindCol(kinghstmt,4,SQL_C_LONG,&sAge,0,&cbAge);
while ((ret=SQLFetch(kinghstmt))!=SQL_NO_DATA_FOUND) //结果集处理
{ 
	if(ret==SQL_ERROR) printf("Fetch error\n");
	else ret=SQLExecute(serverhstmt);
}
SQLFreeHandle(SQL_HANDLE_STMT,kinghstmt);//中止处理
SQLDisconnect(serverhdbc);

关系查询优化

查询的开销主要包括：磁盘存取块数（I/O代价），处理机时间（CPU代价）以及查询的内存开销

代数优化

关系代数表达式的优化

选择操作的实现：

全表扫描算法：对于规模较小的表或要查找的元组均匀地分布在查找的表中，该算法简单有效。
索引扫描算法：当选择率较低时，该算法效率更高。

连接操作的实现：

嵌套循环算法
排序-合并算法
索引连接算法
hash join算法

典型的启发式规则

1）选择运算应尽可能先做

2）把投影运算和选择运算同时进行

3）把投影同其前或其后的双目运算结合起来

4）等值连接运算要比同样关系上的笛卡尔积省很多时间

5）找出公共子表达式：构造中间结果文件，视图中构建公共表达式

-- 通过构建公共表达式 PriceCTE，避免了重复计算 TotalPrice，提高了查询效率。
WITH PriceCTE AS (
    SELECT OrderID, ProductID, Quantity * UnitPrice AS TotalPrice 
    FROM OrderDetails
)
SELECT OrderID, TotalPrice FROM PriceCTE WHERE TotalPrice > 100;
SELECT ProductID, TotalPrice FROM PriceCTE WHERE TotalPrice < 50;

查询树

Query tree & Optimization｜查询树和优化查询树

先将SQL语句转换成关系表达式，然后化成初始的查询树
将$\sigma$运算向下移
将$\Pi$运算向下移

物理优化

存取路径和底层操作算法的选择

基于规则的启发式优化：启发式规则是指那些在大多数情况下都适用的规则。

基于代价估算的优化：优化器估算不同执行策略的代价，并选出最小代价的计划。

两者结合的优化方法：

先使用启发式规则，选取若干较优的候选方案
然后分别计算这些候选方案的执行代价，选出最终的优化方案

选择操作的启发式规则

小关系：全表顺序扫描
大关系：
- 选择条件是”主码=值“，查询结果最多是一个元组，利用主码索引
- 选择条件是“非主属性=值”/非等值查询或范围查询，查询结果比例较小利用索引扫描，否则利用全表扫描
- AND连接的合取选择条件，有索引用索引，否则用全表顺序扫描
- OR连接的析取选择条件，全表顺序扫描

连接操作的启发式规则

已按连接属性排序，选用排序-合并算法
连接属性上有索引，利用索引连接算法
一个表较小，使用hash join算法
嵌套循环算法，选择其中较小的表作为外表

代价估算

全表扫描算法的代价估算公式

如果基本表大小为B块，全表扫描算法的代价 $cost＝B$
如果选择条件是“码＝值”，那么平均搜索代价 $cost＝B/2$

索引扫描算法的代价估算公式

如果选择条件是“码＝值”，则采用该表的主索引
- 若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以$cost=L+1$
如果选择条件涉及非码属性，若为B+树索引，选择条件是相等比较，S是索引的选择基数(有S个元组满足条件)
- 满足条件的元组可能会保存在不同的块上，所以(最坏的情况)$cost=L+S$
如果比较条件是＞，＞＝，＜，＜＝，假设有一半的元组满足条件，就要通过索引访问一半的表存储块，$cost=L+Y/2+B/2$

嵌套循环连接算法的代价估算公式

嵌套循环连接算法的代价，$cost＝Br+BrBs/(K-1)$
连接结果写回磁盘，$cost＝Br+Br Bs/(K-1)+(Frs*Nr*Ns)/Mrs$
- 其中Frs为连接选择性，表示连接结果元组数的比例，Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目

排序-合并连接算法的代价估算公式

如果连接表已经按照连接属性排好序，则$cost＝Br+Bs+(Frs*Nr*Ns)/Mrs$
如果必须对文件排序，还需要在代价函数中加上排序的代价
- 对于包含B个块的文件排序的代价大约是 $(2*B)+(2*B*log2B)$

数据库恢复

事务

定义

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是不可分割的工作单位。

在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序，一个程序通常包含多个事务

事务是恢复和并发控制的基本单位

定义事务的语句：

1
2
3

BEGIN TRANSACTION;-- 开始
COMMIT;-- 提交，将事务中所有对数据库的更新写回到磁盘上的物理数据库中
ROLLBACK; -- 回滚，运行中发生了某种故障，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始的状态

ACID特性

原子性(Atomicity)：

事务中包括的诸操作要么都做，要么都不做

一致性(Consistency)：

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态.数据库中只包含成功事务提交的结果

隔离性(Isolation) ：

并发执行的各个事务之间不能互相干扰

持续性(Durability)：

一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

故障

事务内部的故障

软故障
事务没有达到预期的终点(COMMIT或者显式的ROLLBACK)
事务撤消（UNDO）：强行回滚（ROLLBACK）该事务

系统故障

是指造成系统停止运转的任何事件，使得系统要重新启动

软故障
系统重新启动时，恢复程序让所有非正常终止的事务回滚，强行撤消（UNDO）所有未完成事务
恢复策略：系统重新启动时，恢复程序需要重做（REDO）所有已提交的事务

介质故障

硬故障：外存故障。破坏数据库或部分数据库，并影响正在存取这部分数据的所有事务。

计算机病毒

恢复实现技术

建立冗余数据最常用的技术是数据转储和登记日志文件

数据转储

转储是指数据库管理员定期地将整个数据库复制到存储介质上保存起来的过程，备用的数据称为后备副本(backup)

将后备副本重新装入，将数据库恢复到转储时的状态；然后重新运行自转储以后的所有成功的事务

静态转储

在系统中无运行事务时进行的转储操作
转储开始时数据库处于一致性状态
转储期间不允许对数据库的任何存取、修改活动
得到的一定是一个数据一致性的副本
优点：实现简单
缺点：降低了数据库的可用性
- 转储必须等待正运行的用户事务结束
- 新的事务必须等转储结束

动态转储

转储操作与用户事务并发进行
转储期间允许对数据库进行存取或修改
优点
- 不用等待正在运行的用户事务结束
- 不会影响新事务的运行
动态转储的缺点
- 不能保证副本中的数据正确有效
- 例：在转储期间的某时刻Tc，系统把数据A=100转储到磁带上，而在下一时刻Td，某一事务将A改为200。后备副本上的A过时了
把动态转储期间各事务对数据库的修改活动记录到日志文件，后备副本加上日志文件就能把数据库恢复到某一时刻的正确状态

海量转储: 每次转储全部数据库

增量转储: 只转储上次转储后更新过的数据

日志文件

日志的格式和内容：

以记录为单位的日志文件

事务标识（标明是哪个事务）
操作类型（插入、删除或修改）
操作对象（记录ID、Block NO.）
更新前数据的旧值（对插入操作而言，此项为空值）
更新后数据的新值（对删除操作而言，此项为空值）

以数据块为单位的日志文件

事务标识
被更新的数据块

日志的作用：

用来记录事务对数据库的更新操作的文件
进行事务故障恢复
进行系统故障恢复
协助后备副本进行介质故障恢复，不必重新运行那些已完成的事务

登记日志文件：

记录的次序严格按并发事务执行的时间次序
必须先写日志文件，后写数据库
- 写日志文件操作：把本次修改的记录到日志文件中
- 写数据库操作：把对数据的修改写到数据库中

恢复策略

事务故障的恢复

事务故障的恢复由系统自动完成，对用户是透明的

利用日志文件撤消（UNDO）此事务已对数据库进行的修改

从日志最反向向前扫描日志文件，查找该事务的更新操作。
对该事务的更新操作执行逆操作。
继续反向扫描日志文件，并做同样处理，直至读到此事务的开始标记

系统故障的恢复

由系统在重新启动时自动完成，不需要用户干预

Undo 故障发生时未完成的事务；Redo已完成的事务

正向扫描日志文件

（都有）既有BEGIN TRANSACTION也有COMMIT$\to$重做(REDO) 队列

（只有BEGIN）只有BEGIN TRANSACTION无COMMIT记录$\to$撤销 (UNDO)队列

反向扫描日志文件，对UNDO队列事务进行撤销处理，执行逆操作，将“更新前的值”写入数据库
正向扫描日志文件，对REDO队列事务进行重做处理，将“更新后的值”写入数据库

介质故障的恢复

重装数据库，重做已完成的事务

介质故障的恢复需要数据库管理员介入（数据库管理员只需要重装最近转储的数据库副本和有关的各日志文件副本，然后执行系统提供的恢复命令即可，具体操作仍由数据库管理系统完成）

装入最新的后备数据库副本
- 对于静态转储的数据库副本，装入后数据库即处于一致性状态
- 对于动态转储的数据库副本，还须利用恢复系统故障的方法恢复
装入转储结束时刻的日志文件副本，重做已完成的事务
- 扫描日志文件，找出已提交的事务的标识，记入重做队列
- 正向扫描日志文件，对重做队列中的所有事务进行重做处理。即将日志记录中“更新后的值”写入数据库

具有检查点的恢复

将当前日志缓冲区中的所有日志记录写入磁盘的日志文件上
在日志文件中写入一个检查点记录
将当前数据缓冲区的所有数据记录写入磁盘的数据库中
把检查点记录在日志文件中地址写入重新开始文件

镜像

优点

出现介质故障时

可由镜像磁盘继续提供使用
同时数据库管理系统自动利用镜像磁盘数据进行数据库的恢复
不需要关闭系统和重装数据库副本

可用于并发操作

一个用户对数据加排他锁修改数据，其他用户可以读镜像数据库上的数据，而不必等待该用户释放锁频繁地复制数据自然会降低系统运行效率
在实际应用中用户往往只选择对关键数据和日志文件镜像

并发控制

并发控制概述

多用户数据库系统：允许多个用户同时使用

事务并发执行带来的问题：

会产生多个事务同时存取同一数据的情况
可能会破坏事务隔离性和数据库的一致性
数据库管理系统必须提供并发控制机制
并发控制机制是衡量数据库管理系统性能的重要标志

多事务执行方式

事务串行执行：
- 每个时刻只有一个事务运行，其他事务必须等待
- 不能充分利用系统资源，发挥共享资源的特点
交叉并发方式
- 单处理机系统中，并行事务在轮流交叉运行
- 能够减少处理机的空闲时间
同时并发方式
- 多处理机系统中，可以同时运行多个并行事务
- 最理想的并发方式，但受制于硬件环境

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

1.丢失修改

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

2.不可重复读

不可重复读是指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果

3.读“脏”数据

事务T1修改某一数据，事务T2读取同一数据后，T1由于某种原因撤销，T1修改过的数据恢复原值，T2读到的数据就为“脏”数据

封锁

封锁就是，事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁

加锁后事务T就对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象

排它锁（X锁）

若事务T对数据对象A加上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是可恢复的。

事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放

二级封锁协议：

一级封锁协议+事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。

二级封锁协议可以防止丢失修改和读“脏”数据，但是由于读完数据后即可释放S锁，所以它不能保证可重复读。

三级封锁协议

一级封锁协议+事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。

三级封锁协议可防止丢失修改、读脏数据和不可重复读。

活锁和死锁

避免活锁：采用先来先服务的策略

预防死锁：破坏产生死锁的条件

一次封锁法：要求每个事务一次将要使用的数据全部加锁，否则不能继续执行
顺序封锁法：预先对数据对象规定一个封锁顺序，都按这个顺序实行封锁

诊断并解除死锁

超时法诊断死锁

如果一个事务的等待时间超过了时限，就认为死锁了

问题：有可能误判死锁时限若设置得太长，死锁发生后不能及时发现

解除死锁

选择一个处理死锁代价最小的事务，将其撤消

释放此事务持有的所有的锁，使其它事务能继续运行

并发调度的可串行性

对并发事务不同的调度可能会产生不同的结果

串行调度是正确的，执行结果等价于串行调度的调度也是正确的，称为可串行化调度

当且仅当其结果与按某一次序串行地执行这些事务时的结果相同时，对多个事务的并发执行才是正确的。即一个给定的并发调度，当且仅当它是可串行化的，才是正确调度

冲突操作：是指不同的事务对同一数据的读写操作和写写操作

冲突可串行化调度是可串行化调度的充分条件，不是必要条件。即存在不满足冲突可串行化条件的可串行化调度

不可交换：同一事务的两个操作/不同事务的冲突操作

可交换：

不同事务对同一数据的读读操作$R_i(x),R_j(x)$
不同事务对不同数据的各种操作

两段锁协议

指所有事务必须分两个阶段对数据项加锁和解锁

扩展阶段：在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁
收缩阶段：在释放一个封锁之后，事务不再申请和获得任何其他封锁

事务遵守两段锁协议是可串行化调度的充分条件，而不是必要条件。

两段锁协议与防止死锁的一次封锁法

一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议
两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此可能发生死锁

封锁的粒度

封锁粒度：封锁对象的大小

封锁的对象: 逻辑单元，物理单元

例：在关系数据库中，封锁对象：

逻辑单元:属性值、属性值的集合、元组、关系、索引项、整个索引、整个数据库等

物理单元：页（数据页或索引页）、物理记录等

封锁粒度与系统的并发度和并发控制的开销密切相关。

封锁的粒度越大，数据库所能够封锁的数据单元就越少并发度就越小，系统开销也越小
封锁的粒度越小，并发度较高，但系统开销也就越大

多粒度封锁

多粒度封锁：在一个系统中同时支持多种封锁粒度供不同的事务选择

多粒度树：根节点是整个数据库，表示最大的数据粒度，叶节点表示最小的数据粒度。

允许多粒度树中的每个结点被独立地加锁

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

一个数据对象可能以两种方式封锁：显式封锁和隐式封锁

显式封锁: 直接加到数据对象上的封锁

隐式封锁: 由于其上级结点加锁而使该数据对象加上了锁

意向锁

引进意向锁，提高加锁时系统的检查效率

如果对一个结点加意向锁，则说明下层结点正在被加锁；对任一结点加基本锁，必须先对它的上层结点加意向锁

意向共享锁(IS锁)：事务T1要对R1中某个元组加S锁，则要首先对关系R1和数据库加IS锁

意向排它锁(IX锁)：事务T1要对R1中某个元组加X锁，则要首先对关系R1和数据库加IX锁

共享意向排它锁(SIX锁)：对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别元组（所以要对该表加IX锁）

锁的强度

锁的强度是指它对其他锁的排斥程度

申请封锁时以强锁代替弱锁是安全的，反之不然

具有意向锁的多粒度封锁方法

申请封锁时应该按自上而下的次序进行（多粒度树）
释放封锁时则应该按自下而上的次序进行

锁的强度：

数据锁的相容矩阵：