数据库系统概论笔记(二)

三、SQL语言

3.1 数据库、表、索引、完整性约束

数据库、表、索引的创建、修改、删除

#创建数据库
CREATE DATABASE <数据库名>
#删除数据库
DROP DATABASE <数据库名>
#指定数据库
DATABASE <数据库名>
USE <数据库名>
#创建表
CREATE TABLE [IF NOT EXISTS] <数据表名>
(
    <列定义列表>
    <表约束列表>
)
#修改表
ALTER TABLE <数据表名> <修改子句列表>
#删除表
DROP TABLE <表名>
#创建索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名列表:<列名> [ASC|DESC]>)
#删除索引
DROP INDEX <索引名>

表的列定义

1	<列名> <数据类型> [列级约束列表\|NOT NULL\|AUTO_INCREMENT\|DEFAULT <默认值>]

表的约束

#主码约束
	#单属性主码：列级约束方式
	<列名> <数据类型> PRIMARY KEY
	#单属性主码：表级约束方式
	[CONSTRANIT [约束名]] PRIMARY KEY (<列名>)
	#多属性主码：表级约束方式
	[CONSTRANIT [约束名]] PRIMARY KEY (<列名列表>)
#唯一性约束
	#列级约束方式
	<列名> <数据类型> UNIQUE
	#表级约束方式
	[CONSTRANIT [约束名]] UNIQUE (<列名列表>)
#外码约束
	#列级约束方式
	<列名> <数据类型> REFERENCES <表名>(<列名>)
	#表级约束方式
	[CONSTRANIT [约束名]] FOREIGN KEY (<列名列表>) REFERENCES <表名>(<列名列表>)
#检查约束
	#列级约束方式
	<列名> <数据类型> CHECK (<检查条件>)
	#表级约束方式
	[CONSTRANIT [约束名]] CHECK (<检查条件>)

表的修改子句

#添加约束
ADD <表级约束子句>
#删除约束
DROP {CHECK|CONSTRAINT|FOREIGN KEY} <约束名>
#删除主码
DROP PRIMARY KEY
▲注:等同于语句DROP INDEX "PRIMARY" on <表名>
#删除唯一性约束
▲注:等同于语句DROP INDEX <索引名> on <表名>

断言

#创建断言，其中CHECK子句类似于WHERE子句
CREATE ASSERTION <断言名> <CHECK子句>
#删除断言
DROP ASSERTION <断言名>

3.2 数据查询

查询子句的基本结构
1
2
3
4
5
SELECT <列组合> FROM <数据表列表>
[WHERE <行条件子句>]
[GROUP BY <分组子句>]
[HAVING <组条件子句>]
[ORDER BY <排序子句>]
- 根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表
- 如果有GROUP子句，则将查询结果按照<列名1>相同的值进行分组
- 如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组
- 如果有ORDER子句，查询结果还要按照<列名2>的值进行排序

SELECT子句

目标列形式

#全部列
SELECT * from SC
#列名
SELECT Sno,Score from SC
#算术表达式，可包含+、-、*、/、百分数
SELECT Sno,Score*1.2 from SC
#聚集函数：SUM、AVG
SELECT SUM(Score) from SC

重复元组的处理

缺省或关键字ALL表示保留重复元组

1	SELECT [ALL] <列组合> FROM <数据表列表>

关键字DISTINCT表示去除重复元组

1	SELECT DISTINCT <列组合> FROM <数据表列表>

列的重命名

1 2	#旧目标列可以是列名、表达式、聚集函数名 SELECT <旧目标列> AS <新列名> FROM <数据表列表>

FROM子句

限定：同时指定一个或多个表（或视图）时，如果选择列表中存在同名列，这时应使用对象名限定这些列
1
SELECT username,city.cityid FROM user,city WHERE user.cityid=city.cityid
表的重命名
1
SELECT A.cityid FROM user AS A

子查询

1	SELECT CS.name FROM (SELECT * FROM S WHERE dept="CS") AS CS WHERE CS.age>20

WHERE子句

比较运算符
逻辑运算符：AND、OR、NOT

BETWEEN条件

1	SELECT Sname FROM SC WHERE Score BETWEEN 80 AND 100

模糊查询

1
2
3

#%匹配零个以上字符、_匹配单个字符、\转义字符
SELECT * FROM SC WHERE Sname LIKE '张%'
SELECT * FROM SC WHERE Sname LIKE '张\_%'

ORDER子句

1	SELECT <列组合> FROM <数据表列表> ORDER BY <列名列表:<列名> [ASC\|DESC]>

GROUP与HAVING子句
- GROUP BY：将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值
- HAVING：对分组进行选择，可以针对聚集函数的结果值进行筛选，作用于分组计算的结果集
空值
- 如果null参与算术运算，则该算术表达式的值为null
- 如果null参与比较运算，则结果可视为false
- 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null
1
2
SELECT Sname FROM SC WHERE Score IS NULL
SELECT Sname FROM SC WHERE Score IS NOT NULL
连接查询
- 等值连接：某两张表在某个属性上相等
- 自然连接：在等值连接中去掉重复的属性列
- 自身连接：一个表与其自己进行连接，同一个数据表取不同别名
- 外连接：将悬浮元组包含在连接结果中的连接
嵌套查询
- 子查询是嵌套在另一查询中的 Select-From-Where 表达式
- 子查询中不能使用 Order By 子句，Order By子句只能对最终查询结果进行排序
- 集合成员资格
  1
  2
  #判断表达式的值是否在子查询的结果中
  <表达式> [NOT] IN <子查询>
- 集合之间的比较
  - 当用户能确切知道内层查询返回的是单值时，可以用>、<、＝、>＝、<＝、!＝或<>等比较运算符
  - ANY：表达式的值至少与子查询结果中的一个值相比满足
    1
    SELECT Sname FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept＝’IS’)
  - ALL：表达式的值与子查询结果中的所有的值相比都满足
    1
    SELECT Sname FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept＝’IS’)
- 相关嵌套查询
  - Exists：测试该子查询的结果是否有元组，带有Exists的子查询不返回任何数据，只产生True/False
集合查询
- 集合并：union
- 集合交：intersect
- 集合差：minus
- 集合操作自动去除重复元组，如果要保留重复元组，必须用all关键词指明

3.3 数据的增删改

#插入单个元组
INSERT INTO <表名> [(<列列表>)] VALUES(<值列表>)
#插入子查询结果(要求子查询结果的模式和要插入的模式相同)
INSERT INTO <表名> [(<列列表>)] (<子查询结果集>)
#删除元组(没有WHERE语句时，删除所有元组)
DELETE FROM <表名> [WHERE <条件表达式>]
#更新元组
UPDATE <表名> SET <列修改列表:<列名>=表达式|子查询> [WHERE <条件表达式>]
#跨表更新
UPDATE <表名> SET <列修改列表:<列名>=表达式|子查询> [FROM <表列表>][WHERE <条件表达式>]

3.4 视图

视图是从一个或几个基本表（或视图）导出的一个虚表
数据库中只存放视图的定义而不存放视图的数据

当基表中的数据发生变化时从视图中查出的数据也随之改变

#创建视图
CREATE VIEW <视图名> AS <查询表达式> [WITH CHECK OPTION]
#删除视图
DROP VIEW <视图名>

视图的属性名缺省为子查询结果中的属性名，也可以显式指明
当对视图进行insert，update时，要检查是否满足with check option条件
视图更新
- 对视图的更新，最终要转换为对基表的更新
- SELECT子句中的目标列不能包含聚集函数
- SELECT子句中不能使用UNIQUE或DISTINCT关键字
- 不能包括GROUP BY子句
- 不能包括经算术表达式计算出来的列
- 对于行列子集视图可以更新（视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主码）

3.5 触发器、存储过程、用户自定义函数

触发器

触发器是与某个表绑定的命名存储对象，由一组SQL语句组成

当该表发生某个操作时，触发器将会被触发执行，一般用于维护数据库完整性规则

#创建触发器
CREATE TRIGGER <触发器名>
	{BEFORE|AFTER}
	{<触发事件列表，以OR连接>}
	ON <数据表名>
	[FROM <引用数据表名>]
	[FOR [EACH] {ROW|STATEMENT}]
	[WHEN (<条件>)]
	EXECUTE PROCEDURE <函数名>(<参数列表>)
#删除触发器
DROP TRIGGER <触发器名> ON <表名>

行级触发器：数据每发生一次操作即执行一次触发器动作，如UPDATE多行的某个字段时
语句级触发器（默认）：触发事件语句执行完后才执行一次触发器动作
触发器的激活
- 执行该表上的BEFORE触发器
- 执行激活触发器的SQL语句
- 执行该表上的AFTER触发器

游标

SQL操作都是面向集合的，即操作的对象以及运算的结果均为集合
游标(CURSOR)相当于一个存储于内存的带有指针的表，每次可以存取指针指向的一行数据，并将指针向前推进一行
使用游标可以遍历某个查询语句的结果集

游标不可滚动，即只能依次遍历，不能反向遍历，不能跳跃遍历，不能随机访问，不能修改游标中的数据

#定义变量
DECLARE <变量名> <变量数据类型> [DEFAULT <默认值>]
#定义游标，游标的数据集将为查询语句的结果
CURSOR <游标名> FOR <查询语句>
#打开定义过的游标，并初始化指针
OPEN <游标名>
#读取游标，将读取到的一行数据写入变量列表中
#读取未打开的游标会出错
FETCH [[NEXT] FROM] <游标名> INTO <变量列表>
#关闭游标，关闭未打开的游标会出错
CLOSE <游标名>

存储过程

存储过程是一个SQL语句组合

在创建时进行预编译，首次被调用时进行解析，以后再被调用，则可直接执行

#创建存储过程
CREATE PROCEDURE <存储过程名称>
([@<参数名称> <参数数据类型> [VARYING][=<默认值>][OUT|OUTPUT]])
AS
	[定义变量列表]
BEGIN
	<SQL语句组合>
END
#执行存储过程
[EXECUTE|EXEC] <存储过程名称> <参数列表>
#删除存储过程
DROP PROCEDURE <存储过程名称>

数据传递方式
- 输入参数
- 输出参数使用OUTPUT标识
- RETURN语句返回单个int型数据，如操作过程中受影响的行数，错误码
- RETURN不能返回NULL，若试图返回NULL，将生成警告信息并返回0

用户自定义函数

自定义函数可以像数据库内部函数一样在SQL语句中使用，如WHERE子句、SELECT子句、表达式中

CREATE FUNCTION <函数名>
([参数列表:<参数名称> <参数数据类型>])
RETURNS <返回数据类型>
BEGIN
	<函数体>
	RETURN <返回值:常量/表达式/语句查询结果>
END

3.6 安全性控制

#创建用户
CREATE USER <用户名> with PASSWORD <密码>
#创建角色，角色是权限的集合
CREATE ROLE [IF NOT EXISTS] <角色列表>
#删除角色，拥有该角色的用户失去该角色定义的权限集合
DROP ROLE [IF EXISTS] <角色列表>
#授权，其中WITH GRANT OPTION表示获得权限的用户可以把权限授予他人
GRANT <角色名> TO <用户列表> [WITH GRANT OPTION]
GRANT <权限列表> [ON <对象类型> <对象名>] TO <用户列表> [WITH GRANT OPTION]
#收回，若用户已将权限授予其它用户，则也一并收回
REVOKE <权限列表> [ON <对象类型> <对象名>] FROM <用户列表|PUBLIC>
#权限列表
ALL PRIVILIGES
{SELECT|UPDATE} [(<字段列表>)]
{INSERT|DELETE|ALTER}

3.7 并发控制

3.7.1 并发控制概述

并发控制的必要性
- 事务串行执行：每个时刻只有一个事务运行，其他事务必须等到这个事务结束以后方能运行，不能充分利用系统资源，发挥数据库共享资源的特点
- 交叉并发方式：单处理机系统中，并行事务的并行操作轮流交叉运行
- 同时并发方式：多处理机系统中，可实现多个事务真正的并行运行
并发控制带来的数据不一致性
- 丢失修改
  - 两个事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失
- 不可重复读
  - 事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时，得到与前一次不同的值
  - 事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失了
  - 事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。
- 读脏数据
  - 事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，此时T2读到的数据就为“脏”数据

3.7.2 封锁

封锁概述
- 事务T在对某个数据对象操作之前，先向系统发出请求，对其加锁
- 排它锁（Exclusive Locks，简记为X锁、写锁）
  - 若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁
  - 保证其他事务在T释放A上的锁之前不能再读取和修改A
- 共享锁（Share Locks，简记为S锁、读锁）
  - 若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁
  - 保证其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
- 锁的相容性矩阵（Y表示相容，N表示不相容）

	X	S	-
X	N	N	Y
S	N	Y	Y
-	Y	Y	Y

封锁解决数据不一致性
- 解决丢失修改问题：修改前先对待修改数据对象加X锁
- 解决不可重复读问题：读前先对待读数据对象加S锁
- 解决读脏数据问题：T1修改前先对待修改数据对象加X锁，T2读前先对待读数据对象加S锁
活锁
- T2、T3、…、Tn依次等待事务T1释放锁，而系统依次批准了T3、…、Tn的请求，T2将有可能永远等待，即活锁
- 采用先来先服务的策略避免活锁：当多个事务请求封锁同一数据对象时，按请求封锁的先后次序对这些事务排队，该数据对象上的锁一旦释放，首先批准申请队列中第一个事务获得锁
死锁
- 事务T1封锁了数据R1，而T2封锁了数据R2；T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁；接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁，这样T1在等待T2，而T2又在等待T1，T1和T2两个事务永远不能结束，形成死锁
- 预防死锁
  - 一次封锁法：每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行
  - 顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁
  - 时间戳优先级法：较老时间戳具有更高优先级，当一事务重启时，它的新优先级仍为原先的时间戳
- 死锁的检测与解除
  - 超时法检测：如果一个事务的等待时间超过了规定的时限，就认为发生了死锁；阈值过短则可能误判，过长则死锁不能及时发现
  - 事务等待图法检测
    - 事务等待图是一个有向图G=(T,U)，其中T为结点的集合，每个结点表示正运行的事务，U为边的集合，每条边表示事务等待的情况，若T1等待T2，则T1与T2之间有一条从T1指向T2的有向边
    - 并发控制子系统周期性地（比如每隔数秒）生成事务等待图，检测事务。如果发现图中存在回路，则表示系统中出现了死锁
封锁协议
- 1级封锁协议
  - 事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放
  - 1级封锁协议可防止丢失修改
  - 在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据
- 2级封锁协议
  - 1级封锁协议+事务T在读取数据R前必须先加S锁，读完后即可释放S锁
  - 在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读
- 3级封锁协议
  - 1级封锁协议+事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放
  - 3级封锁协议可防止丢失修改、读脏数据和不可重复读

3.7.3 可串行性

可串行化(Serializable)调度：多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同
冲突可串行化调度
- 冲突可串行化是可串行化调度的充分条件
- 冲突操作指不同事务对同一数据的读写操作和写写操作，冲突操作中两个操作的次序发生改变将导致结果改变
- 某个调度在冲突操作的次序不改变的前提下，通过交换两个事务不冲突操作的次序，转变为可串行的调度，则该调度是冲突可串行化的

3.7.4 两段锁协议

当一个事务满足以下条件时，是满足两段锁协议的
- 在对任何数据进行读写操作前，首先申请并获得对该数据的封锁
- 在释放一个封锁后，事务不再申请和获得任何其他封锁
- 事务被划分为两个阶段
  - 扩展阶段：获得封锁且不释放任何锁
  - 收缩阶段：释放封锁且不申请任何锁
若并发执行的所有事务均满足两段锁协议，则这些事务的任何调度策略都是可串行化的，这是可串行化的充分条件
遵循两段锁协议的事务不要求事务必须一次将所有要使用的数据全部加锁，因此可能发生死锁

3.8 数据库恢复技术

3.8.1 事务

事务概念
- 事务(Transaction)：用户定义的具有交易特性的一个数据库操作序列
- 特性
  - 原子性：事务是数据库的逻辑工作单位，事务中各操作要么都做，要么都不做
  - 一致性：数据库状态与外部状态一致
  - 隔离性：一个事务的执行不能被其他事务干扰
  - 持续性：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

定义事务

显式定义

#提交事务
BEGIN TRANSACTION
	SQL
	...
COMMIT
#回滚事务
BEGIN TRANSACTION
	SQL
	...
ROLLBACK

隐式定义：DBMS按缺省规定自动划分事务

3.8.2 数据库恢复

故障种类
- 事务故障：某个事务在运行过程中由于种种原因未运行至预期的终点便结束
- 系统故障：操作系统或DBMS代码错误等
- 介质故障：磁盘损坏等原因
恢复的实现：建立冗余数据
- 数据转储
  - 静态转储：在系统中无运行事务时进行转储，转储期间不允许对数据库进行任何存取修改活动
  - 动态转储：转储与事务并发执行，但获得一致性副本较麻烦，因此需要将动态转储期间各事务对数据库的修改活动登记下来，建立日志文件
  - 海量转储：定期或不定期将数据库全部数据转储，其转储量大，易造成重复转储
  - 增量转储：每次转储上次转储后更新过的数据，其备份量小，但恢复过程较复杂
- 登录日志文件
  - 用来记录事务对数据库的更新操作的文件
  - 以记录为单位的日志文件内容
    - 各个事务的开始标记(BEGIN TRANSACTION)
    - 各个事务的结束标记(COMMIT或ROLLBACK)
    - 各个事务的所有更新操作
    - 与事务有关的内部更新操作
    - 每条日志记录的内容
      - 事务标识
      - 操作类型（插入、删除或修改）
      - 操作对象（记录ID）
      - 更新前数据的旧值（对插入操作而言，此项为空值）
      - 更新后数据的新值（对删除操作而言, 此项为空值）
  - 以数据块为单位的日志文件
    - 事务标识号
    - 该事务执行更新前的数据块
    - 该事务执行更新后的数据块
  - 每个日志记录在日志中都有一个唯一的码，叫做日志序号（简称LSN）
恢复策略
- 事务故障的恢复：由恢复子系统利用日志文件撤消此事务已对数据库进行的修改
  - 反向扫描文件日志，查找该事务的更新操作，并对该事务的更新操作执行逆操作
  - 如此处理下去，直至读到此事务的开始标记，事务故障恢复即完成
- 系统故障的恢复
  - 正向扫描日志文件，得到Undo队列和Redo队列
  - Undo故障发生时未完成的事务：一些未完成事务对数据库的更新已写入数据库，反向扫描日志文件，对每个UNDO事务的更新操作执行逆操作
  - Redo已完成的事务：一些已提交事务对数据库的更新还留在缓冲区没来得及写入数据库，正向扫描日志文件，对每个REDO事务重新执行登记的操作
  - 通俗方式
    - 在系统发生故障之前已经提交的事务需要重做
    - 在系统发生故障之前开始但没有提交的需要撤销
    - 在系统发生故障之前已经回滚的事务不做操作，相当于没有进行
    - 系统恢复后，回滚和撤销的事务相当于没有执行，只需要考虑重做的事务
- 介质故障的恢复
  - 重装数据库，使数据库恢复到一致性状态
    - 对于静态转储的数据库副本，装入后数据库即处于一致性状态
    - 对于动态转储的数据库副本，还须同时装入转储时刻的日志文件副本，利用与恢复系统故障相同的方法恢复
  - 装入转储结束时刻的日志副本，重做已完成的事务
    - 首先扫描日志文件，找出故障发生时已提交的事务的标识，将其记入重做队列
    - 然后正向扫描日志文件，对重做队列中的所有事务操作进行重做。即将日志记录中“更新后的值”写入数据库

数据库系统概论笔记(二)

三、SQL语言

3.1 数据库、表、索引、完整性约束

3.2 数据查询

3.3 数据的增删改

3.4 视图

3.5 触发器、存储过程、用户自定义函数

3.6 安全性控制

3.7 并发控制

3.7.1 并发控制概述

3.7.2 封锁

3.7.3 可串行性

3.7.4 两段锁协议

3.8 数据库恢复技术

3.8.1 事务

3.8.2 数据库恢复

主题设置

实验特性

播放设置

切换歌单

自定义歌单

默认背景

图片（PC端）

图片（移动端）

渐变色

纯色