数据库笔记

大约 17 分钟

数据库笔记

基于数据库课程

绪论

基本概念 P4

数据

描述事物的符号记录称为数据
数据的含义称为数据的语义
数据与其语义不可分(2000 -> 年份/工资)

数据库

数据库是长期储存在计算机内, 有组织的, 可共享的大量数据的集合

数据库管理系统

Database Management System，位于用户与操作系统之间的一层数据管理软件

DBMS的用途

科学地组织和存储数据、高效地获取和维护数据

数据库管理系统的功能 ▲

数据库定义定义数据库中的数据对象(DDL)
数据的运行管理保证数据的安全性、完整性多用户对数据的并发使用发生故障后的系统恢复
数据操纵功能查询,插入,删除,修改
数据库的建立和维护数据库数据批量装载数据库转储介质故障恢复数据库的重组织性能监视等

数据库系统 ▲

数据库系统（Database System，简称DBS）是指在计算机系统中引入数据库后的系统构成

数据库系统的构成 ▲

由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员（和用户）构成

数据库系统三级模式

模式数据库中全体数据逻辑结构和特征的描述
外模式用户看见与使用的局部数据的逻辑结构和特征的描述
内模式一个数据库只有一个内模式, 是数据物理结构和存储方式的描述

数据管理三阶段 ▲

人工管理
文件系统管理
数据库系统阶段

数据模型 ▲

层次模型
网状模型
关系模型
面向对象模型
对象关系模型

数据库系统阶段的特点 ▲

数据结构化：这是数据库与文件系统的根本区别
数据共享性高、冗余度小、易扩充
数据独立性高：包括数据的物理独立性和数据的逻辑独立性
数据由DBMS统一管理和控制

概念模型 P16

实体

客观存在且可相互区别的事务 ER图中使用矩形带文字说明表示

属性

实体具有的某一特性 ER图中使用椭圆形带文字说明表示, 与实体或联系连接

码

唯一标识实体的属性集

外码

来自其他实体中的主码可以作为主码

实体型

用实体名与实体属性名的集合来刻画同类实体 e.g. 学生(学号,姓名)

实体集

同一类实体的集合

联系

ER图中使用菱形带文字说明表示, 连接多个实体第i个实体与联系的连接上可以有数字 x_i = 1 或 n 实体i, 对应x₁个实体1, ... 对应x_m个实体m

一对一联系
一对多联系
多对多联系

无论那种联系, 都允许1个A对应0个B

关系数据库

关系数据库只包含单一数据结构关系

基本概念 P38

域

具有相同数据类型的集合 e.g. D=

笛卡尔积

D_1\times D_2

域 $D_1,D_2$ 中所有元素各取一个组成元组, 结果为所有可能的元组

关系

R(D_1,D_2,...D_n)

$R$ 为 $D_1\times D_2\times ... \times D_n$ 的一个有实际意义的子集 n 为关系的度

关系类型

基本关系
查询表
视图表

关系的性质

每一列的分量来自同一个域
每一列具有不同的列名(属性名)
列的次序可以交换(纯笛卡尔积有顺序, 没有列名)
任意两个元组(行)的候选码不能有相同的值
行的顺序可以交换
每一个分量(列)的值不可分(不允许有子表)

关系完整性 P45

实体完整性实体的主属性不可取空值
参照完整性实体中的外码只能取空值或依赖关系S主码中存在的取值
用户定义的完整性取值要有意义(e.g. 分数不可小于0)

关系代数

集合运算 P49

要求参与运算的两个集合列数相同, 对应列取自相同的域(数据类型相同)

并 UNION

R\cup S

差 EXCEPT

R-S

选出属于R而不属于S的元素

并 INTERSECTION

R\cap S

笛卡尔积

D_1\times D_2

关系运算

选择 P50

\sigma_F(R)

从R中选出使F为真的元组(行)

投影 P53

\Pi_{A_1,A_2,...,A_n}(R)

从 $R$ 中选出列 $A_1,A_2,...,A_n$ 投影为集合运算, 要去除重复列

连接 JOIN P53

A和B分别为R和S上列数相等且可比的属性组, $\theta$ 为比较符号将A与B做笛卡尔积, 再筛选出满足的元组

等值连接

比较符号 $\theta$ 为等于号保留所有列

自然连接

特殊的等值连接, 比较的两个关系中所有同名的属性组合并所有同名属性组

特殊的自然连接

假设

R(A,B)\;S(B,C)\\ (b_5,c_1)\in S\;(a_1,b_4)\in R\\ b_4\notin S.B\;b_5\notin R.B

外连接

R\text{⟗}S

保证结果中被连接列 B 的在 R 与 S 中所有的取值会出现在结果中例子中 $b_4$ 与 $b_5$ 不同时存在于 $R,S$ 中, 但也会在结果中保留, 不能确定的列取空值

左/右连接

以左连接为例

R\text{⟕}S

保证结果中被连接列 B 的在 R(左侧) 中所有的取值会出现在结果中, 不能确定的列取空值

除运算 P55

R\div S

去除 $S$ 中所有 $R$ 中不存在的列
将 $R$ 中不在 $S,R$ 的公共列的列作为参照组
以参照组的各个元组 $a_i$ , 选出对应的所公共列元组(象集) $\{b|(a_i+b)\in R\}$
当 $S$ 包含于参照组中的元组 $a_i$ 对应的象集, $a_i$ 即为符合的元组
结果为所有符合条件的 $a_i$

意义: R 为 A 与 B 的一对多关系, S 为一个 B 的集合象集 $A'_i$ 为 $a_i$ 对应的所有 $b_i$ 的集合除法的结果即 $a_n$ 至少对应了 S 中所有的元素

SQL语言

SQL的特点

综合统一
高度非过程化
面向集合的操作方式
以同一种语法结构提供两种使用方法
语言简洁, 易学易用

操作对象 P80

模式 SCHEMA(DATABASE)
表 TABLE
视图 VIEW
索引 INDEX

数据类型 P83

数据类型	含义
CHAR(N)	固定长度的字符串
VARCHAR(N)	可变长度的字符串
SMALLINT	2字节整型
INT	4字节整型
BIGINT	8字节整型
BOOLEAN	布尔型
BLOB	二进制数据
CLOB(TEXT)	文本
FLOAT(n)	精度为n的浮点型
REAL	单精度浮点
DOUBLE PRECISION	双精度浮点
NUMERIC(p,d)	由p位数组成, 小数点后有d位
DECIMAL(p,d)	同NUMERIC
DATE	日期, 包括年月日
TIME	时间, 包括时分秒
TIMESTAMP	UNIX时间戳
INTERVAL	时间间隔

定义表 P82

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

表级完整性约束

主键约束 PRIMARY KEY(列1, 列2, ...)
外键约束 FOREIGN KEY(表中的列) REFERENCES 参照表(参照列) 参照列必须是参照表的主键(外键定义)

修改表 P85

新增列 ALTER TABLE <表名> ADD COLUMN <新列名> <数据类型> [完整性约束];
添加约束 ALTER TABLE <表名> ADD <完整性约束>(被约束列);
删除列 ALTER TABLE <表名> DROP COLUMN <列名>;
修改列 ALTER TABLE ALTER COLUMN <列名> <数据类型>;

删除表

DROP TABLE <表名>;

索引操作 P88

建立索引

CREATE [UNIQUE(唯一,默认)/CLUSTER(聚簇)] INDEX <索引名> ON <表名>(列1 [ASC/DESC], ...);

重命名索引

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

删除索引

DROP INDEX <索引名>;

查询

基本格式

SELECT [DISTINCT/ALL] <列1, 列2,...>
FROM <表1, 表2, ...>
WHERE <条件>
GROUP BY <列>
HAVING <聚簇函数条件>
ORDER BY <排序方法>

选择列 P90

选择的列可以是表达式 e.g. <列>+1024
可以对选择的列重命名 e.g. <列> "<结果中的名称>"
通配符 * 表示所有列
<选择的表>.* 表示选择表下所有的列
DISTINCT 将去除重复的结果(可用于模拟集合操作)
ALL 默认, 保留所有结果

选择表 FROM

可以对选择的表重命名(用于自身连接P101)
可以选择子查询作为表 (<子查询>) AS <表名>(子查询结果各列的名称)
选择多个表本质为将各个选择的表做笛卡尔积
外连接 <表1> [LEFT/RIGHT] OUTER JOIN <表2> ON (条件)
自然连接排除重复的列的多表查询

条件查询 WHERE P93

比较参数为NULL时, 结果必定为FALSE 相等 = / 不相等 != <> 被比较值取空值时, 结果为FALSE, 需要先使用 IS NULL 识别空值
范围 BETWEEN AND
空值 IS NULL / IS NOT NULL
逻辑 AND(优先级最高) OR NOT 当空值在逻辑运算中的取值对结果有影响, 则结果为 UNKNOWN P120
字符串匹配 LIKE <匹配字符串> 多字符通配符 % / 单字符通配符 _ / 转义符使用ESCAPE <字符> 指定注意匹配字符串第一个字符对应文本第一个字符, 最后一个字符对应最后一个即全文匹配
集合参数为NULL时, 结果必定为FALSE IN (取值1, 取值2, ...)/(子查询)

排序 ORDER BY P96

ORDER BY <列1> [ASC/DESC], ... 用于最外层的查询, 默认升序(位置越高值越大)

聚集函数 P97

配合GROUP BY 与 HAVING 使用, 可作为列表达式在 SELECT 中使用, 或作为 HAVING 的条件
形式 <函数名>([DISTINCT/ALL] 列名)
DISTINCT 排除重复, 查找种数
ALL 默认, 查找个数
COUNT(*) 特殊, 用于查找元组总数(行数)
MYSQL中, 列名可为表达式, 但需要注意 NULL 的问题

名称	作用
COUNT	统计元组个数
SUM	统计目标列所有值之和
AVG	统计目标列所有值的平均值
MAX	统计目标列所有值的最大值
MIN	统计目标列所有值的最小值

分组 GROUP BY P98

GROUP BY <列名> 表中各行以目标列取值分组, 用于聚集函数使用 HAVING 以聚集函数为条件, 进一步筛选

嵌套查询

不相关子查询 P104

子查询的条件不依赖于父查询即无论父查询检查的行改变, 子查询不会改变可使用多表查询优化 e.g.

SELECT * FROM A
WHERE A.a IN (
    SELECT a FROM B
    WHERE B.b = 10 #查询条件与父查询无关
);

子查询方法

a IN(子查询) P104 返回结果要是单独的一列当子查询中存在取值与 a 相同的行, 则为真 a 为NULL时, 结果必定为FALSE
a <比较符> (子查询) P106 返回结果只能有一个值与返回的值比较
a <比较符>[ANY/ALL] (子查询) P107 返回结果要是单独的一列与所有返回的值比较, [任一/所有]比较结果为真, 返回真可使用聚集函数MAX/MIN优化
EXISTS (子查询) P109 子查询结果不为空时, 结果为真
选择子查询作为表 P113

集合查询 P111

MYSQL 中不可用

交集 UNION
并集 INTERSECT
差集 EXCEPT 格式 <查询1> UNION <查询2> 不保留重复的值(可在命令后使用 ALL 参数保留)

数据更新

插入数据 P115

插入值 INSERT INTO <表>(列1, ...) VALUES(列1的值, ...), ...;
插入子查询的值 INSERT INTO <表>(列1, ...) 子查询;

修改数据 P117

UPDATE <表> 
SET <列>=<新值, 可以是列表达式>
WHERE <条件>;

删除数据 P118

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

视图 P121

创建视图

CREATE VIEW <视图名>(列名1, ...)
AS <子查询>
[WITH CHECK OPTION]

WITH CHECK OPTION

修改视图中的数据时, 会对数据使用子查询中的 WHERE 进行检查

行列子集视图

建立在基本表的部分列上的视图可以进行数据更行

带表达式视图

含有虚拟列(列为表达式)的视图

分组视图

带有聚集函数与GROUP BY 的视图

规范化

函数依赖 P180

定义

$R<U,F>$ 关系模型 $R$ , 属性集 $U$ , 函数依赖集 $F$ $F=\{u_a\rightarrow(u_b,u_c),...\}$ $X,Y=\{u_i,u_j,..\}$ 为属性集 $U=\{u_1,u_2,...u_n\}$ 的子集属性集内部的属性有无函数依赖均可

函数依赖

在 $R$ 的所有元组, 不存在两个元组在 $X$ 上的值相等时, $Y$ 上的值不相等则称 $Y$ 函数依赖于 $X$ 或 $X$ 函数确定 $Y$

X\rightarrow Y

$X$ 为决定因素

平凡函数依赖

Y\subseteq X\;X\rightarrow Y

由于当 $Y\subseteq X$ , 必然有函数依赖, 因此此类函数依赖没有意义, 不做讨论 e.g. $${姓名,学号}\rightarrow 学号$$

完全函数依赖

设 $X\rightarrow Y$ $X$ 的所有真子集 $X'\nrightarrow Y$ 则称 $Y$ 完全函数依赖于 $X$

X\mathop{\rightarrow}\limits^FY

部分函数依赖

设 $X\rightarrow Y$ $X$ 存在真子集 $X'\nrightarrow Y$ 则称 $Y$ 部分函数依赖于 $X$

X\mathop{\rightarrow}\limits^PY

传递函数依赖

是一种特殊的函数依赖假设

X\rightarrow Y\;Y\nrightarrow X\;Y\rightarrow Z

则

X\mathop{\rightarrow}\limits^{\text{传递}}Z

码 P181

定义

R<U,F>

$R$ 关系 $U$ 属性集 $F$ 属性间的依赖关系

候选码

K\mathop{\rightarrow}\limits^FU

$K$ 中不可有任何额外属性, 否则无法达到完全函数依赖的要求且 $K$ 函数确定的是全体属性 $U$

超码

S\mathop{\rightarrow}\limits U

$S$ 中可以有额外属性, 且可以完全或部分函数依赖于 $U$ 因此 $K\subseteq U$

主码

任意一个候选码都可作为主码

主属性

一个或多个包含在任何一个候选码中的属性即主属性

范式 P182

高级的范式必须建立在符合低级范式的基础上

第一范式 1NF

实体中的某个属性不能有多个值或者不能有重复的属性

第二范式 2NF P182

非主属性完全函数依赖于任何一个候选码 e.g.

R(\text{学号}, \text{课程}, \text{成绩}, \text{姓名})

候选码 $$K={\text{学号}, \text{课程}};K\mathop{\rightarrow}\limits^P姓名$$ 不符合第二范式

数据冗余姓名重复
修改复杂当姓名修改, 需要额外修改整张表
插入异常学生未选课时, 无法将学号-姓名信息插入表中(课程为主键)
删除异常如果删除所有选课信息, 学生的学号-姓名信息将被删去/无法删去所有课程信息

需要添加额外的关系以达到第二范式对于有多个主属性的关系要特别注意

第三范式 3NF P184

每一个非主属性不传递依赖于码 (主属性内可能有传递依赖关系) e.g.

R(\text{学号},\text{系},\text{系名})

\text{学号}\rightarrow\text{系}\rightarrow\text{系名}

不符合第三范式, 需要拆分传递函数依赖的部分

修改复杂当系名修改, 需要额外修改整张表
数据冗余系名重复

BCNF P184

消除所有属性之间的部分与传递函数依赖 @import "./SPJ.png" 对于关系 $R(S,P,J)$ 有候选码 $K_1=\{S,J\}\;K_2=\{S,T\}$ $K_2$ 解释: 仅有 $S$ 无法推出 $T,J$ (属性组 $S, J$ 函数确定 $T$ ), 仅有 $T$ 无法推出 $S$ 可得主属性 $S$ 非主属性 $T,J$ 对于两个候选码, 均能直接函数确定非主属性, 符合第三范式但是存在 $(S,J)\mathop{\rightarrow}\limits^{\text{传递}}J$ 不符合BCNF, 需要拆分

数据库设计 P209

需求分析需要实现的功能
概念结构分析 ER图
逻辑结构分析 mysql语句
物理结构分析部署到硬盘
数据库实施交互界面
数据库运行和维护备份

数据库恢复与备份

事务 P293

事务的概念

用户定义的一个数据库操作序列, 这些操作要么全做, 要么全不做, 是一个不可分割的工作单元
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序
事务是恢复和并发控制的基本单位

SQL语句

BEGIN TRANSACTION 开始事务操作暂时生效
COMMIT 提交事务事务的操作永久生效
ROLLBACK 回滚事务撤销所有操作

事务特性

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

数据库备份 P297

任意转储状态与转储方式的组合成为一种备份方式

转储状态

静态转储

通过复制所有数据以备份静态转储时不可有任何事务运行改变数据

动态转储

转储期间允许事务运行通过建立日志文件, 保证转储时修改的数据的正确性

转储方式

海量转储

备份数据库中所有的数据

增量转储

在原备份的基础上, 只备份与原先备份不同的数据

数据库的并发控制

并发控制

并发控制概述

对并发操作进行正确调度
保证事务的隔离性
保证数据库的一致性

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

修改失效
读脏数据
不可重复读

封锁 P312

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

排他锁/写锁

eXclusive locks 简称X锁事务T对数据A加上排他锁后, 只允许T读取与修改A, 其他任何事物都不能对A加任何类型的锁

共享锁/读锁

Share locks 简称S锁事务T对数据A加上共享锁后, 只允T与许其他事务读取A, 不能修改, 其他事务也可加共享锁, 但不能加排他锁

封锁协议 P313

一级封锁协议

事务T在修改R之前, 对其添加X锁, 直到事务结束后解除可以防止修改丢失

二级封锁协议

事务T在读取R之前, 对其添加S锁, 直到读取结束后解除可以防止读脏数据(读取时被修改, 但修改最后被回滚)

三级封锁协议

事务T在读取R之前, 对其添加S锁, 直到事务结束后解除保证可重复读

封锁问题 P315

活锁

当事务T不能优先对数据加锁时, 可能会长时间的等待, 直到没有事务对数据加锁, 即活锁通过先到先得机制解决

死锁

多个事务对数据加锁的请求形成闭环, 导致无限的等待可通过牺牲闭环中代价最小的事务解决

数据库笔记

# 数据库笔记

# 绪论

# 基本概念 P4

# 数据

# 数据库

# 数据库管理系统

# DBMS的用途

# 数据库管理系统的功能 ▲

# 数据库系统 ▲

# 数据库系统的构成 ▲

# 数据库系统三级模式

# 数据管理三阶段 ▲

# 数据模型 ▲

# 数据库系统阶段的特点 ▲

# 概念模型 P16

# 实体

# 属性

# 码

# 外码

# 实体型

# 实体集

# 联系

# 关系数据库

# 基本概念 P38

# 域

# 笛卡尔积

# 关系

# 关系类型

# 关系的性质

# 关系完整性 P45

# 关系代数

# 集合运算 P49

# 并 UNION

# 差 EXCEPT

# 并 INTERSECTION

# 笛卡尔积

# 关系运算

# 选择 P50

# 投影 P53

# 连接 JOIN P53

# 等值连接

# 自然连接

# 特殊的自然连接

# 外连接

# 左/右连接

# 除运算 P55

# SQL语言

# SQL的特点

# 操作对象 P80

# 数据类型 P83

# 定义表 P82

# 表级完整性约束

# 修改表 P85

# 删除表

# 索引操作 P88

# 建立索引

# 重命名索引

# 删除索引

# 查询

# 选择列 P90

# 选择表 FROM

# 条件查询 WHERE P93

# 排序 ORDER BY P96

# 聚集函数 P97

# 分组 GROUP BY P98

# 嵌套查询

# 不相关子查询 P104

# 相关子查询 P105

# 子查询方法

# 集合查询 P111

# 数据更新

# 插入数据 P115

# 修改数据 P117

# 删除数据 P118

# 视图 P121

# 创建视图

# WITH CHECK OPTION

# 行列子集视图

# 带表达式视图

数据库笔记

绪论

基本概念 P4

数据

数据库

数据库管理系统

DBMS的用途

数据库管理系统的功能 ▲

数据库系统 ▲

数据库系统的构成 ▲

数据库系统三级模式

数据管理三阶段 ▲

数据模型 ▲

数据库系统阶段的特点 ▲

概念模型 P16

实体

属性

码

外码

实体型

实体集

联系

关系数据库

基本概念 P38

域

笛卡尔积

关系

关系类型

关系的性质

关系完整性 P45

关系代数

集合运算 P49

并 UNION

差 EXCEPT

并 INTERSECTION

笛卡尔积

关系运算

选择 P50

投影 P53

连接 JOIN P53

等值连接

自然连接

特殊的自然连接

外连接

左/右连接

除运算 P55

SQL语言

SQL的特点

操作对象 P80

数据类型 P83

定义表 P82

表级完整性约束

修改表 P85

删除表

索引操作 P88

建立索引

重命名索引

删除索引

查询

选择列 P90

选择表 FROM

条件查询 WHERE P93

排序 ORDER BY P96

聚集函数 P97

分组 GROUP BY P98

嵌套查询

不相关子查询 P104

相关子查询 P105

子查询方法

集合查询 P111

数据更新

插入数据 P115

修改数据 P117

删除数据 P118

视图 P121

创建视图

WITH CHECK OPTION

行列子集视图

带表达式视图

分组视图