数据库技术复习笔记

第一章 SQL基本语法

1. 基本表的定义、删除、修改

1. 定义基本表:

CREATE TABLE <table_name> 
(<col_name> <data_type> [col_constraint]
[, ...]
[, <table_constraint>])

eg:

CREATE TABLE Students
(Sno  CHAR(5) PRIMARY KEY,
 Sname CHAR(20) NOT NULL,
 Ssex CHAR(2),
 Birthday DATE,
 Dno CHAR(5),
 CONSTRAINT FK_Dno FOREIGN KEY(Dno) REFERENCES Depts
)

2. 向表中添加元组:

INSERT
INTO <table_name> [(<col_1>) [, <col_2> ...]]
VALUES(<const_1> [, <const2>]...)

eg:

INSERT
INTO Depts
VALUES('D01', 'Computer')

3. 修改基本表:

ALTER TABLE <table_name>
[ADD <new_col_name> <data_type> [col_constraint]]
[DROP CONSTRAINT [constraint_name]]
[ALTER COLUMN <col_name> <data_type>]

eg:

ALTER TABLE Students ADD Sentrancedate DATETIME;
ALTER TABLE Students ALTER COLUMN Birthday DATETIME;
ALTER TABLE Students DROP CONSTRAINT un_Sname1;

4. 删除基本表：

DROP TABLE <table_name>

2. 建立与删除索引

1. 建立索引：

CREATE [UNIQUE] [CLUSTERED] INDEX <index_name>
ON <table_name> (<col_name> [<sort_order>] ...)

eg:

CREATE CLUSTERED INDEX Stu_Sno ON Students(Sno);
CREATE UNIQUE INDEX Stu_Sname ON Students(Sname DESC);
CREATE INDEX Stu_Sage ON Students(Sage ASC, Dno DESC)

2. 删除索引：

DROP INDEX <table_name.index_name>

eg:

DROP INDEX Students.Stu_Sage

3. SQL数据查询

SELECT [ALL|DISTINCT] <target_col_expression> [,...]
FROM <table_name_or_view_name> [,...]
[WHERE <condition_expression>]
[GROUP BY <col_1> [HAVING <condition_expression>]]
[ORDER BY <col_2> [ASC|DESC]]

GROUP子句按col_1的值进行分组。
HAVING短语只输出满足指定条件的组。

1. 简单的选择与投影查询

(1) 无条件查询：

SELECT * 
FROM Students

(2) 条件查询:

• 比较条件

SELECT Sno, Sname
FROM Students
WHERE Dno = 'D03'

• 谓词条件

SELECT Sname, DATENAME(yyyy, GETDATE()) - YEAR(Birthdau) Sage
FROM Students
WHERE DATENAME(yyyy, GETDATE()) - YEAR(Birthday) BETWEEN 18 AND 22

• 查询结果排序

SELECT Sno, Grade
FROM Reports
WHERE Cno = 'C03'
ORDER BY Grade DESC

• 集函数的使用

SELECT COUNT(*)
FROM Students

集函数：COUNT(*)， COUNT(col_name), SUM(col_name), AVG(col_name), MAX(col_name), MIN(col_name)

• 查询结果分组

SELECT Cno, COUNT(Sno) CntSno
FROM Reports
GROUP BY Cno
HAVING COUNT(Cno) >= 3

2. 连接查询

连接查询分为：

• 等值连接查询
• 自然连接查询
• 非等值连接查询
• 自身连接查询
• 外连接查询
• 符合条件查询
  等值连接中把目标列重复属性去掉则为自然连接。

(1) 不同表之间的连接查询

• 等值连接

SELECT Students.*, Reports.*
FROM Students, Reports
WHERE Students.Sno = Reports.Sno

• 自然连接

SELECT Students.Sno, Sname, Ssex, Birthday, Dno, Cno, Grade
FROM Students, Reports
WHERE Students.Sno = Reports.Sno

(2) 自身连接

SELECT A.Cno, A.Cname, B.Pre_Cno
FROM Courses A, Courses B
WHERE A.Pre_Cno = B.Cno

(3) 外连接

外连接分为：

• 左连接
• 右连接

SELECT Students.Sno, Sname, Ssex, Birthday, Dno, Cno, Grade
FROM Students, Reports
WHERE Students.Sno = Reports.Sno(*)

上述命令实现的是左连接。
左连接指的是从左表中返回所有行，即使右表中无匹配。

3. 嵌套查询
   (1) 带谓词IN的嵌套查询

SELECT Sname, Sno
FROM Students
WHERE Sno IN
(SELECT Sno 
 FROM Reports
 WHERE Cno = 'C02')

SQL支持多层嵌套查询，但在子查询的SELECT语句中不能使用ORDER BY子句。
嵌套查询的一般求解方法是由里到外处理，即先执行子查询，后执行父查询。
子查询的结果用于建立父查询的查找条件。
不相关子查询指: 子查询的查询条件不依赖于父查询。

(2) 带有比较运算符的嵌套查询

SELECT Sno, Sname, Dno
FROM Students
WHERE Dno = 
(SELECT Dno
 FROM Students
 WHERE Sname = 'LiWei')

(3) 带谓词ANY或ALL的嵌套查询

SELECT Sno, Sname, Ssex, Birthday, Dno
FROM Students
WHERE Dno <> 'D01'
AND YEAR(Birthday) <= ALL(SELECT YEAR(Birthday)
	FROM Students
	WHERE Dno = 'D01')

一般来说，用集函数实现子查询比直接用ANY或ALL查询效率要高。

(4) 带谓词EXISTS的嵌套查询

SELECT Sname, Dno
FROM Students
WHERE EXISTS
(SELECT *
 FROM Reports
 WHERE Sno = Students.Sno AND Cno = 'C01')

• 上述查询为相关子查询，即子查询的查询条件依赖于外层父查询的某个属性值(本例中为Student.Sno)。
• 相关子查询的处理过程：先去外层表中第一个元组，根据它与内层相关的属性值处理内存子查询，若WHERE子句返回为真，则将该元组放入结果表；然后在取下一个元组重复该过程。
• 由EXISTS引出的子查询，其目标属性列一般用*表示，因为带EXISTS的子查询只返回逻辑值True或False,不需给出具体列名。若内层子查询结果非空则外层WHERE子句条件为True,否则为False。
• 一些带EXISTS或NOT EXISTS的子查询不能被其他形式的子查询等级替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能被带EXISTS谓词的子查询等价替换。

eg：下面的SQL命令实现：查询选修了所有课程的学生姓名(Sname)和所在系别编号(Dno)

SELECT Sname, Dno
FROM Students
WHERE NOT EXISTS
(SELECT * 
 FROM Courses
 WHERE NOT EXISTS
 (SELECT * 
  FROM Reports
  WHERE Sno = Students.Sno
  AND Cno = Courses.Cno))

由于不存在全称量词，可将题目意思转成等价的存在量词形式：查询这样的学生，没有一门课是他不修的。

也可以简单的嵌套查询实现：

SELECT Sname, Dno
FROM Students
WHERE Sno IN
(SELECT Sno
 FROM Reports
 GROUP BY Sno
 HAVING COUNT(*)=
 (SELECT COUNT(*) FROM Courses))

4. 集合查询

集合查询分为：

• 并集，SQL提供了UNION运算符
• 差集，可以通过求补的思想等价转换实现

SELECT * 
FROM Students
WHERE Dno = 'D02'
UNION
SELECT *
FROM Students
WHERE DATENAME(yyyy, GETDATE()) - YEAR(Birthday) <= 20

4. SQL数据更新

1. 插入数据

INSERT
INTO <table_name> [(<col_1>)[, <col_2>...]]
SELECT sub_clause

eg:

INSERT
INTO History_Student
SELECT *
FROM Students

2. 修改数据

UPDATE <table_name>
SET <col_1> = <expression_1>[, <col_2> = <expression_2>...]
[WHERE <condition>]

eg:

UPDATE Reports
SET Grade = 80
WHERE Sno = 'S03' AND Cno = 'C01';

3. 删除数据

DELETE 
FROM <table_name>
[WHERE <condition>]

5. SQL视图

视图是由一个或几个基本表(或视图)导出的虚拟表。
只有 行列子集视图 可以被更新。
行列子集视图满足以下三个条件：

• 从单表导出
• 只使用选择和投影操作
• 视图属性集中包含一个候选键

1. 视图定义
   (1) 建立视图

CREATE VIEW <view_name> [(<col_name> [, <col_name>]...)]
AS <SELECT sub_clause>
[WITH CHECK OPTION]

WITH CHECK OPTION表示用视图进行更新、插入和删除操作时要满足视图定义的谓词条件
eg:

CREATE VIEW C_Student
AS
SELECT Sno, Sname, Birthday, Dno
FROM Students
WHERE Dno = 'D03'
WITH CHECK OPTION

DBMS执行CREATE VIEW语句并不执行其中的SELECT语句，只是把视图的定义放入数据字典。

(2) 删除视图

DROP VIEW <view_name>

2. 视图查询

视图定义后，可像查询基本表一样查询视图
eg:

SELECT Sname, DATENAME(yyyy, GETDATE()) - YEAR(Birthday) Sage
FROM C_Student
WHERE DATENAME(yyyy, GETDATE()) - YEAR(Birthday) < 20

视图消解：DBMS在执行视图查询时，首先检测表和视图是否存在，若存在则从数据字典中取出视图定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询的转换过程。

3. 视图更新

只有行列子集视图可被更新
(1) 插入

INSERT
INTO C_Student
VALUES('S09', 'WangHai', '1998-05-16', 'D03')

(2) 删除

DELETE
FROM C_Student
WHERE Sno = 'S09'

(3) 修改

UPDATE C_Student
SET Sname = 'HangHai'
WHERE Sno = 'S05'

6. SQL数据控制

SQL数据控制包括：

• 创建用户
• 授权
• 收回权限

1. 授权

GRANT <rights>
[ON <object_type> <object_name>]
TO <user_name>
[WITH GRANT OPTION]

• 不同对象类型允许的操作权限:

对象	对象类型	操作权限
属性列与视图	TABLE	SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES
基本表	TABLE	SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALL PRIVILEGES
数据库	DATABASE	CREATETAB

eg:

GRANT ALL PRIVILEGES
ON TABLE Students
TO User1, User2

2. 收回权限

REVOKE <rights>
[ON <object_type> <object_name>]
FROM <user_name>

eg:

REVOKE UPDATE(Sname)
ON Students
FROM User3 CASCADE

CASCADE表示级联收回。

第二章 数据管理与数据库

数据管理是对数据进行收集、整理、组织、编码、存储、检索和传输等一系列操作的总称，它是数据处理的前提和中心问题。

信息 = 数据 + 数据处理

数据独立性：

• 数据的物理独立性: 当数据的物理存储位置或物理结构改变时，用户程序不用改变
• 数据的逻辑独立性：当数据库的全局逻辑结构改变时，用户程序不用改变

数据库系统：

• 数据库：是长期存储在计算机内、有组织的、可共享的数据集合。
• 数据库管理系统(DBMS)：是位于用户和OS之间的一层数据管理软件。DBMS是数据库系统的核心组成部分。
• 数据库系统：是计算机系统、DB、DBMS、应用软件、数据库管理员和用户的集合

DBMS调用OS。

数据模型三要素：

数据模型是DBMS的核心。

1. 数据结构：数据库静态特性的描述

• 层次结构
• 网状结构
• 关系结构

2. 数据操作：数据库动态特性的描述

• 检索
• 更新

3. 完整性约束：关于数据状态和状态变化的一组完整性约束条件

• 实体完整性
• 参照完整性

数据模型分类：

• 概念数据模型

将现实世界转换为信息世界
常用模型：E-R模型

• 结构数据模型(逻辑数据模型)

用户从数据库中看到的模型

• 网状数据模型
• 层次数据模型
• 关系数据模型: 逻辑结构是一种二维表
• 面向对象数据模型

• 物理数据模型

描述数据在存储介质上的组织结构

• 索引
• 聚集

数据库的三级模式：

• 内模式(存储模式)：物理数据模型：存储文件
• (逻辑)模式: 结构(逻辑)数据模型：基本表
• 外模式(子模式、用户模式): 概念数据模型：视图

数据库的二级映像：

• 外模式/模式映像：逻辑独立性
• 模式/内模式映像：物理独立性

数据库只有一个模式、也只有一个内模式。

DBMS的功能：

• 数据库的定义功能
• 数据操作功能
• 数据库控制语言功能

第三章 关系数据库模型

关系模型：

• 数据结构: 关系(二维表)
• 关系操作：
  • 查询：选择(select)、投影(project)、连接(join)、除(division)、并(union)、交(intersection)、差(difference)
  • 更新：增加(insert)、删除(delete)、修改(update)
• 完整性约束
  • 实体完整性：主键属性对应的各个分量不能为空值
  • 参照完整性：外键在被参照表中的值必须为空值或等于参照表中某元组主键值
  • 用户定义的完整性

关系数据语言：

• 关系代数语言: ISBL
  • 传统集合运算：并、交、差、笛卡尔积
  • 专门关系运算：投影、选择、连接、除法
• 关系演算语言
  • 元组关系演算语言： ALPHA、QUEL
  • 域关系演算语言：QBE
• 具有代数和演算双重特性的语言：SQL

关系完备性：以关系代数运算为基础的数据语言课实现人们对数据库的所有查询和更新操作。

查询优化策略：

• 选择和投影尽早执行
• 投影和选择同时进行
• 把投影和前后的一个双目运算结合起来
• 执行连接运算前，对关系进行预处理，如索引和排序
• 把笛卡尔积和前后的选择运算合并成连接运算
• 存储公用子表达式

第四章 关系模式的规范化设计理论

关系模式： 对一类实体特征的结构性描述，也是对关系的结构性描述。
关系模式的规范化设计理论(关系数据库的规范化理论)是数据库逻辑结构设计的有力工具

1. 问题的提出以及关系模式的函数依赖

关系模式可能存在的异常：

• 插入异常
• 删除异常
• 冗余过多

异常的原因：属性之间存在过多的数据依赖。

数据依赖：一个关系中属性值的相互联系，是数据之间的内在性质

• 函数依赖

X->Y:X函数确定Y指的是对关系r中的任一元组，如果它在属性集X上值确定，则它在属性值Y上的值随之确定。

• 完全函数依赖：X的任何真子集无法确定Y
  - 部分函数依赖：存在X的真子集可以确定Y
  - 传递函数依赖 ：X->Y, Y->Z都是非平凡函数依赖，且Y无法确定X，则X->Z

• 多值依赖

异常问题的解决：关系模式的分解

Armstrong公理系统：函数依赖的有效的、完备的推理规则集

• 自反律: 若X包含Y,则X->Y
• 增广律：若X->Y，则XZ->YZ
• 传递律：若X->Y, Y->Z则X->Z

Armstrong的推导规则：

• 合并律：X->Y,X->Z则X->YZ
• 伪传递律:X->Y, WY->Z则WX->Z
• 分解律:X->Y,且Y包含Z,则X->Z

最小函数依赖集Fmin满足：

• 右部都是单属性
• F中无冗余函数依赖
• 左部无冗余属性

2. 关系模式的规范化

1NF ⊇ 2NF ⊇ 3NF ⊇ BCNF ⊇ 4NF ⊇ 5NF

• 1NF: 所有属性不可再分
• 2NF: ∈1NF,且非主属性完全函数依赖于某个候选键
• 3NF: ∈2NF,且非主属性不传递依赖于候选键
• BCNF: ∈1NF,且每个属性不传递依赖于任一候选键，即只有候选键才能使决定因素

各范式的规范化过程：

• 1NF 消除非主属性对候选键的部分依赖 -> 2NF
• 2NF消除非主属性对候选键的传递依赖 -> 3NF
• 3NF消除主属性对候选键的部分依赖和传递依赖 -> BCNF
• BCNF消除非平凡且非函数依赖的多值依赖 -> 4NF
• 4NF消除不是由候选键蕴含的连接依赖 -> 5NF

3. 关系模式的分解特性

关系模式分解的评判标准：

• 无损连接性

无损连接的测试（略*P139）

• 保持函数依赖
• 既保持函数依赖，又具有无损连接性

关于模式分解的几个事实：

• 分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。
• 若要求分解具有无损连接性，那么模式分解一定可以达到BCNF。
• 若要求分解保持函数依赖，那么模式分解可以达到3NF，但不一定能达到BCNF。
• 若要求分解既具有无损连接性，又保持函数数依赖，则模式分解可以达到3NF，但不一定能达到BCNF

第五章 数据库的安全与保护

（1）如何保证数据安全可靠、正确可用
（2）当数据库遭破坏后如何迅速恢复

DBMS的数据库安全与保护功能：

• 安全性保护
  • 用户鉴别
  • 存取权限控制
  • 视图机制：最主要功能是数据独立性
  • 跟踪审查
  • 数据加密存储
• 完整性保护
  • 完整性包括：正确性、一致性、相容性
  • 完整性约束：(实体、参照、用户定义完整性约束)
    • 静态列级约束
    • 静态元组约束
    • 静态关系约束
    • 动态列级约束
    • 动态元组约束
    • 动态关系约束
  • 完整性控制：
    • 定义功能
    • 检查功能
    • 保护功能
  • 触发器：建立在某个关系(基本表)上的一系列SQL语句的集合(程序)，预先编译后存储在数据库中。
    • 主动完整性约束机制
    • 还具有安全保护功能
• 并发控制
  • 事务：用户定义的一组操作序列集合，是数据恢复和并发控制的基本单位。

  事务的特性(ACID性质)：

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

  • 事务处理技术：
    • 并发控制技术：保障并发执行时事务满足ACID性质
    • 数据库恢复技术：保障系统发送故障时事务满足ACID性质
  • 并发的目的：
    • 改善系统资源利用率
    • 改善短事务的响应时间
  • 并发引起的问题：
    • 丢失修改(写-写冲突)
    • 脏读(读-写冲突)
    • 不能重读(幻影现象)
  • 并发控制方法：
    • 封锁技术
      • 排他锁(X锁)
      • 共享锁(S锁)
    • 封锁协议
      • 三级封锁协议
        • 一级封锁协议：对要修改数据对象先加X锁，防止丢失修改
        • 二级封锁协议：一级 + 读取数据对象先加S锁，读完后释放，防止脏读
        • 三级封锁协议：一级 + 读取数据对象先加S锁，且直到该事务结束才可释放S锁，防止不可重读
      • 两端封锁协议：可串行化的充分条件，保证并行调度结果可串行性
    • 死锁和活锁：
      • 解决活锁的方法：先来先服务策略
      • 解决死锁的方法：
        • 预防法：一次封锁法、顺序封锁法
        • 诊断解除法
    • 时标技术
    • 版本更新技术
• 数据库恢复
  • 故障种类：
    • 事务故障
    • 系统故障
    • 介质故障
    • 病毒破坏
  • 恢复技术
    • 数据转储
    • 日志文件
    • 恢复策略
      • 事务故障的恢复：UNDO
      • 系统故障的恢复：UNDO撤销队列、REDO重做队列
      • 介质故障的恢复：重装数据库
  • 检查点机制

第六章 数据库设计与实施

数据库设计的步骤：

• 数据库规划时期 （可行性分析报告）
• 数据库设计时期
  • 需求分析阶段
    • 需求调查
    • 分析整理
    • 评审
  • 概念结构设计阶段
    • ER方法的步骤：
      • 设计局部E-R图
      • 设计全局E-R图
      • 解决属性冲突、结构冲突、命名冲突
      • 全局E-R图的优化
  • 逻辑结构设计阶段
  • 物理结构设计阶段
    • 聚簇设计
    • 索引设计
    • 分区设计
• 数据库实施时期
• 数据库运行与维护时期

数据字典的内容：

• 数据项
• 数据结构
• 数据流
• 数据存储
• 处理过程

逻辑结构设计中ER模式转换为关系模式时联系的转换：

• 1:1联系：将联系的属性和其中一个实体型的主键加入另一关系模式中即可。
• 1:n联系：将联系的属性和1端对应关系模式的主键属性加入n端即可。
• m:n联系：将联系转换为一个关系型，把联系的属性和前面两个关系模式的主键属性加入其中。

数据库应用系统的结构

• 单用户结构
• 集中式结构
• 二层客户机/服务器结构
• 三层客户机/服务器结构
  • 数据层：数据管理和事务逻辑
  • 功能层：应用逻辑
  • 表示层：表达逻辑
• 多层结构