一、绪论

文件系统的问题

ACID

• 原子性(atomicity)

• 一致性(consistency)

• 隔离性(isolation)

• 持久性(durability)

不同类型的DB

分层数据库系统

• 树状结构，用指针连接，存在一对多

• 缺点
• 查询复杂（递归）
• 难保证数据一致性
• 难扩展
• 缺乏标准
• 权限不好分配

网状数据库系统

• 无环图

• 缺点
• 复杂
• 难维护

关系型数据库系统

• 组织为表

数据库系统DBS

• 组成
• Database
• Data model
• DBMS

DB语言

• 定义db格式

• 增删改查
• 分类
• 过程化DML
• 用户需同事知道要什么和怎么要数据
• 非过程化DML
• 用户只需要知道要什么数据
• SQL

数据库模式DB Schema

• 数据库的逻辑结构
• 概念模式
• 定义db所有数据
• 物理模式
• 描述如何存储数据
• 用户视图
• 只描述用户可访问的数据

• 三层模式结构
• 外部层
• 概念层
• 内部层

• 提供访问控制机制

二、关系型数据库管理系统RDBMS

• 表就是关系

• 属性：列

• 元组，记录：行

• 表头：属性名

模式Schema

• 定义：属性的集合，就是表的结构
• 一旦确定，改动的代价很大

• State：具体的所有数据
• 就是数据条目，经常被修改

关系型数据库

关系的性质

• 无序性

• 无重复行

• 无多值属性

空值Null

• 使用条件
• 未知
• 后续将确定
• 不适用

• 不同于0和空格

• 数值计算中null = 0

• 排序中null为无穷大

超键sk

• 可唯一确定的字段集合

• 一个关系至少有一个sk（所有字段的集合）

• 包含sk的集合也是sk

• 只给一个关系的State，只能证明一个字段的集合不是sk，不能证明它就是sk

键

• 极小化的sk
1. 它能唯一标识一行（它是 SK）。
2. 不能再减了。你要是再从这个集合里踢掉一个字段，它就没法唯一标识了。

• 一个关系的key可唯一确定关系表的一行。

• 一个关系至少有一个key，即所有字段的集合极小化的结果。

候选键ck

• 一个关系的 Key 也称为 Candidate Key

• 有资格当主键pk的候选

主键pk

• 包含字段个数最少的ck之一

• pk被选定之后不能更换

• 唯一且非空
• pk的值不能重复，且不能有空值

外键fk

• 规则 ①：门当户对。 FK 和它引用的 PK 必须有相同数量的字段，而且数据类型得兼容。

• 规则 ②：活要见人，死要见尸（参照完整性）。 对于 R1 里的每一行，它的外键值只有两条路走：
1. 要么是 null：代表你现在是个断线的风筝，还没跟谁搞上关系。
2. 要么就必须在 R2 的主键里能找着。 这叫参照完整性约束。

• fk需要显式指定

三、MySQL

SQL概览

• Structural Query Language

• 核心组件
• DDL：数据定义语言 (Data Definition Language)
• 定义数据库对象（数据库，表，字段）
• DML：数据操纵语言 (Data Manipulation Language)
• 增删改
• DQL：数据查询语言 (Data Query Language)
• 查
• DCL：数据控制语言 (Data Control Language)
• 创建DB用户，管理权限

• 语句不区分大小写

DDL

• DB的操作
• 查询
• 创建
• CREATE DATABASE [IF NOT EXISTS] DB名 [DEFAULT CHARSET 字符集];
• 加IF NOT EXISTS 避免因为已存在该DB而报错
• 一般用utf8mb4可以每个数据4bytes
• 删
• DROP DATABASE [IF EXISTS] DB名;
• 切换到指定DB
• USE DB名

• 数据表
• SHOW TABLES 查询当前db所有表
• DESC 表名 查询结构（Describe）
• SHOW CREATE TABLE 查询指定表的建表语句
• 创建
• 如

• 数据类型
• 数

类型	描述	大小 / Byte(s)
TINYINT	小整数	1
SMALLINT	大整数	2
MEDIUMINT	大整数	3
INT 或 INTERGER	大整数	4
BIGINT	极大整数	8
FLOAT	单精度浮点数	4
DOUBLE	双精度浮点数	8
DECIMAL	小数值(精确定点数)	/

• 文本（使用时需指定长度）

类型	描述	备注
CHAR	定长字符串	长度不足用空格补足，也占固定空间，适用于性别等长度基本确定的字符串
VARCHAR	变长字符串	按实际长度分配空间，适用于用户名等长度不确定的字符串
TINYTEXT	短文本字符串	/
TEXT	长文本数据	/
MEDIUMTEXT	中等长文本数据	/
LONGTEXT	极大文本数据	/

• 日期时间

类型	描述	格式	大小
DATE	日期值	YYYY-MM-DD	3
TIME	时间值或持续时间	HH:MM:SS	3
YAER	年份值	YYYY	1
DATETIME	混合日期和时间值	YYYY-MM-DD HH:MM:SS	8
TIMESTAMP	混合日期和时间值、时间戳	YYYY-MM-DD HH:MM:SS	4

• 表的修改
• 增
ALTER TABLE 表名 ADD 字段名 类型(长度) ［COMMENT注释］［约束］;
• 改
AFTER TABLE 表名 MODIFY 字段名 新类型(长度)
AFTER TABLE 表名 CHANGE 旧名 新名 类型(长度) [COMMENT] [长度]
• 删
AFTER TABLE 表名 DROP 字段名
• 改表名
ALTER TABLE 表名 RENAME TO 新表名
• 删表
DROP TABLE [IF EXISTS］表名；#删除表
TRUNCATE TABLE 表名；#删除指定表，并重创建该表 clear操作

DML

• 添加数据
• 字段顺序要对应
• 字符串和日期要用引号
• 不要超界

• 修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件]; # 修改数据
• 一定加where，不然所有值都会改变

• 删除数据
DELETE FROM 表名 [WHERE 条件]; # 删除数据
• 同上
• 如果只是将值置空，用update+set null

DQL

• 基础查询
• 基本：SELECT，FROM
• 条件：WHERE
条件
• 单条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN 最小值 AND 最大值	在某范围内的值(含最小值、最大值)
IN(...)	任一在列表中的值
LIKE 占位符	模糊匹配('_' 匹配单个字符, '%' 匹配多个字符)
IS NULL	是 NULL

• 多条件

逻辑运算符	功能
AND 或 &&	且
OR 或 ||	或
NOT 或 !	非, 否

有以下的等价性：
① = SOME(…)等价于 IN(…) .
② <> SOME(…) 不等价于 NOT IN(…) .
③ <> ALL(…) 等级与 NOT IN(…) .
• 聚合函数：COUNT，MAX，MIN，AVG，SUM

聚合函数	功能
COUNT	统计数量
MAX	最大值
MIN	最小值
AVG	平均值
SUM	求和

• SELECT AVG(age) FROM agetable; 统计平均年龄（输出数字）
• Null不参与运算
• 分组：GROUP BY
• 过滤分组后的数据：HAVING
• HAVING可用聚合函数作为条件
• 分组后，除了分组字段，其他字段会被折叠，无法单独查询。所以只能查询聚合函数或者分组字段。
• 分页：LIMIT
• 排序：ORDER BY
• ASC：非降序（默认），从小到大
• DESC：非升序，从大到小

• 编写顺序：

• 执行顺序：

• 所以别名（AS后的）只能在SELECT后用，不能在WHERE和GROUP BY用

DCL

• 创建用户 (CREATE USER)
• 主机名
• localhost为只能本地连接
• 可以为’%’，即可以在任意ip连接

• 赋予权限（GRANT）
• 准许 caixukun 在 company 这个库里，干所有的脏活累活（增删改查）。
• company.*：意思是 company 库下面的所有表，他都能碰。
• 但是不能建表（CREATE），也不能删表（DROP）。
• 将所有库，所有表的所有权限给richardliu 。
• 可以在权限列表中加可以操作的具体属性：
• GRANT UPDATE(stock_code, stock_price) ON stock TO brown;
• 只能操作stock_code, stock_price 属性

• 移除权限（REVOKE）
• 大体跟GRANT写法类似
• 更加根本，删除用户：DROP USER 'caixukun'@'localhost';

• 更新缓存（FLUSH PRIVILEGES）
• 防止数据库没反应过来

约束

外键约束

• 建立表与表之间的关系，确保引用完整性

• 例
• 主表：部门信息
• 从表：员工信息，其中一个属性是部门，需要引用部门的信息。这个属性必须填主表上主键有的项

• 从表插入属性要检查主表主键是否存在

• 主表不能随便删改

DQL多表查询

• 一对多/多对一
• 多的一方建立外键，指向另一方主键

• 多对多
• 建立中间表，至少包含两个外键，分别关联两表的主键
• 案例：
• 一个 学生 可以选 多门课。
• 一门 课 也可以被 多个学生 选。
1. 建一张 Student 表。
2. 建一张 Course 表。
3. 再建一张 Student_Course_Relation 表（中间表/关联表）！
• 中间表里存啥？ 就存 student_id 和 course_id。
• 一行记录表示：“张三 选了 数据库原理”。
• 另一行记录表示：“李四 也选了 数据库原理”。

• 一对一
• 多用于单表拆分，基础字段一张表，详情字段一张表，提升性能，隔离隐私
• 外键建在哪边都行，关联另一边的主键，但是外键要加上 UNIQUE（唯一） 约束！

• 实际上是求多张表的笛卡尔积，强制两两匹配

• 需要消除冗余
• WHERE + 条件：SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;
• 老标准
• 连接查询
• 内连接
• 查询集合A，B交集的数据
• SELECT * FROM emp e [INNER] JOIN dept d ON e.dept_id = d.id;
• 外连接
• 查询左表/右表所有数据和交集部分，常用左外连接，因为两表顺序可以反过来
• SELECT * FROM 表1 LEFT JOIN 表2 ON 条件...
• 返回左表的所有条目和右表的部分条目，如果左表的条目在右表没有，则返回null
例
先建两个表，一个是 beauty（女神表），一个是 car（豪车表）。
beauty 表（女神）：
id	name (名字)
1	冰冰
2	圆圆
3	凤姐
car 表（豪车）：
owner_id	car_name (车名)
1	劳斯莱斯
2	法拉利
4	五菱宏光
注：你看好了，凤姐（id 3）没车，五菱宏光（owner_id 4）的主人不在女神表里。
---
1. 内连接 (INNER JOIN) —— 返回的是“门当户对”
返回结果： 只有两边都能匹配上的数据。
SQL语句：
返回什么？
name	car_name
冰冰	劳斯莱斯
圆圆	法拉利
• 凤姐呢？ 凤姐没车，滚蛋了。
• 五菱宏光呢？ 没主人的破车，数据库理都不理。
---
2. 左外连接 (LEFT JOIN) —— 返回的是“女神及其挂件”
返回结果： 左表（beauty）的所有人，加上右表能匹配上的数据。匹配不上的，右边填 NULL。
SQL语句：
返回什么？
name	car_name
冰冰	劳斯莱斯
圆圆	法拉利
凤姐	NULL
• 凤姐怎么出来了？ 因为她是左表的人，她是主子。虽然她没车，但数据库得给她留个位置，没车就给她分配个 NULL（空气）。
• 五菱宏光呢？ 依然没戏，因为它在右表，没人领它，它就得滚。
---
3. 右外连接 (RIGHT JOIN) —— 返回的是“名车博览会”
返回结果： 右表（car）的所有车，加上左表能匹配上的主人。匹配不上的，左边填 NULL。
SQL语句：
返回什么？
name	car_name
冰冰	劳斯莱斯
圆圆	法拉利
NULL	五菱宏光
• 五菱宏光怎么出来了？ 因为它是右表的，主子必须全部露脸。虽然它没主人，但数据库会在主人那一栏给它写个 NULL。
• 凤姐呢？ 凤姐没车，在这种“名车博览会”的逻辑下，没车的女人连进门扫码的资格都没有！
• 自连接
• 自连接可以是内连接也可以是外连接
• 解决的就是那种“老子表里某个人是我表里另一个人他爹”这种狗屁倒灶的层级关系
• 一张表拆成两张虚表 + 起两个不一样的外号 + 按逻辑连起来
例
有一张员工表 emp：
id	name (员工名)	manager_id (领导的ID)
1	老王 (大BOSS)	NULL
2	张三	1
3	李四	1
4	王五	2
查询谁是谁的领导。
---
返回结果：
员工	领导
张三	老王
李四	老王
王五	张三
• 老王因为是顶级BOSS，他没有领导（manager_id是NULL），所以在 INNER JOIN 下，老王直接被踢出名单了。你要是想把老王也查出来，就得用 LEFT JOIN！
• 联合查询
• 合并多次查询结果形成新表。
• 要求行属性一致，列数相等
• UNION：删掉重复项
• UNION ALL：直接拼合
• 子查询（嵌套查询）
• SQL语句中嵌套 SELECT 语句称为嵌套查询或子查询.
1. 子查询外部的语句可以是 INSERT、UPDATE、DELETE、SELECT 等.
1. 按子查询的结果分类:
① 标量子查询: 子查询的结果是一个值.
② 列子查询: 子查询结果为一列.
③ 行子查询: 子查询结果为一行.
④ 表子查询: 子查询结果为多行多列的表.
1. 按子查询的位置分类:
① WHERE 之后.
② FROM 之后.
③ SELECT 之后.
• 存在判定EXIST()
• 可以替换掉子查询操作，性能更优
• 子查询：以“结果集”为中心，把表作为单位查询
• 存在判定：以“逻辑判定”为中心，把条目作为单位查询
例
在学生表 "students" 和修课表 "enrollment" 中进行如下查询:
(1) 查询至少修了一门课的学生的信息.
(2) 查询没有修编号 "21003001" 的课程的学生的信息.

视图

• 虚拟的表，数据存在，但不是真实存在库里的表，实际上是一个动态生成的查询结果

增删改查

• 创建
• OR REPLACE：要是视图已经存在了，数据库不会给你报错，而是覆盖

• 查询: 同查询数据表.

• 修改.
或

• 删除.
• 删视图不会影响原始数据

检查选项

• WITH ... CHECK OPTION ：过滤网
• 检查更改的每一行是否符合视图定义
• 对视图进行修改的时候，如果修改的项不符合视图的条件，则报错
• 直接对源表进行修改，不生效

• 两种选项
• CASCADED 级联（默认）
• 逻辑： 如果视图 B 是基于视图 A 建的，而视图 B 加了 WITH CASCADED CHECK OPTION。
• 连坐： 当你往 B 里塞数据时，它不仅要检查 B 的条件，还会强行检查 A 的条件，不管 A 当初建的时候有没有加检查选项！
• LOCAL 本地
• 如果只有B加了检查选项，只会检查B，不会检查A

视图的更新

• 只有当视图的条目和基础表中的条目一一对应才能改，不然数据库不知道要改什么

• 如果包含下面这些项之一都不能更新：
• 聚合函数或窗口函数, 如 SUM、MIN、MAX、COUNT.
• DISTINCT.
• GROUP BY.
• HAVING.
• UNION 或 UNION ALL.

• 能更新的： SELECT * FROM table WHERE ...（原封不动，只是加了过滤）。

视图的优缺点

• 优点
• 简化查询语句
• 安全
• 数据独立性
• 提高编程效率
• 存储占用小

• 确定
• 引用视图花时间
• 可能不被直接更新（LOCAL）

其他存储对象

• 存储过程
• 存放在数据库中的完成特定功能的SQL语句集

• 存储函数
• 返回计算结果，可以直接被SQL表达式调用

• 触发器
• 自动检测某表的改变

• 事件
• 定时任务

• 游标
• 用于遍历结果集，每次返回一行

四、关系代数

—— 选择 (Select)

• 取行

• 对应WHERE

π —— 投影 (Projection)

• 取列

• 对应SELECT

——连接（Join）

• 即原本条关系中，真实的n条一一对应的关系

• 先乱交，再筛选

• θ 连接
• 只要你的配对条件里用的是 >、<、>=、<=、!= 这些玩意儿，它都叫 Theta 连接。

• 等值连接
• 条件里必须且只能是等号（=）。

• 自然连接
• 两表中的相同属性对应的值也相等的条目会自动连接
• 本质上是内连接
• 你来个 NATURAL JOIN，数据库会默默在后台给你加上：WHERE A.id = B.id AND A.name = B.name AND A.status = B.status。两表属性连一起且自动去重
• 省略join-condition的join成为自然连接

• 外连接
• 左右连接
• 全连接
• 同时保留左右表的全部数据

除法

• 核心逻辑： 给你两张表 A 和 B，查出 A 表里哪些货色“完全涵盖了” B 表里的所有要求。

五、关系型数据库理论

确定性

三大症状

• 更新异常：漏

• 删除异常：删多了

• 插入异常：没主键

决定因素

函数依赖

• 自反律：你包含我，那我肯定能确定你

• 扩充率：，那给两边都加个 Z，逻辑依然成立。

• 传递率： 且 ，那 A 就能跨过 B 直接搞定 C

闭包

• 闭包（Closure）： 给你一个属性集 X，让你去推导在这个表里，X 到底能间接确定多少个属性。推出来的那个大集合就叫 。
• 搜索

• 超级键（Superkey）： 只要你的闭包能覆盖整张表的所有属性，你就是超级键。

• 候选键（Candidate Key）： 如果你是超级键，而且你还没法再精简了（去掉任何一个字段就罩不住全场了），那你就是候选键。

• 起点：，只有向外的箭头

• 终点：，只有箭头指向它

• 任何候选键中必须含有的所有项，但不能有的任何项

关系分解

• 数据库里的无损是指“没产生幻觉”。如果拆得不对，拼回来的时候会多出一些原本不存在的、莫名其妙的“幽灵行”（Wrong tuples）。这就叫有损。
• Join的共同属性不能是多对多，需要是某个表的候选键。

• 判别绝招（Theorem 5.1.3.1）： 如果你把一张表拆成 和 ，只要满足：
• （公共属性是 的键）
• 或者 （公共属性是 的键）
• 只要满足其中一个，你这次拆迁就是“无损”的。

• 后果： 如果没保持依赖，你以后往表里塞数据的时候，数据库就没法自动帮你检查逻辑错误了。这简直就是给未来的程序 Bug 留了个后门。

• 算法： XYGP 算法。就是让你在拆开的小表里反复求闭包，看看能不能把原表所有的 FD 都给推导出来。

1. 拆表是为了治病（去冗余）。

2. 无损连接（LLJD）是底线。如果你拆完连不回去了，那你就直接回炉重造吧，别tm当程序员了。

3. 保持依赖（DPD）是理想。有些时候（比如为了达到 BCNF），我们不得不牺牲一点点依赖保持，这是权衡。

范式

1NF

• 表中的每一个格子（属性），都必须是原子性 (Atomic) 的

• 一个格子里只能放一个东西

• 非主键必须依赖主键

2NF

• 只有组合主键才要考虑是不是2NF，如果主键就一列，一定是2NF

• 其他属性必须同时依赖主键的所有属性
• 假设你建了一张选课成绩表，主键是 (学号, 课程号)。
• 成绩 同时依赖 (学号, 课程号)。这没问题
• 学生姓名 只依赖主键的一半 (学号)，把另一半 (课程号) 当空气。这不行。
• 所以不符合2NF

• 将只依赖部分主键属性的分出去。

3NF

• 只有两个属性的一定满足

• 非主键之间也存在依赖，造成传递依赖

• 将非主键的依赖分成小表

BCNF

• 所有的决定者都必须是候选键

• 3NF：非主键的小弟不能互相搞基（传递依赖）。

• BCNF：哪怕你是主键的一部分，只要你敢决定别人，你就必须是候选键！ 

算法

LLJD-BCNF（保持无损连接的 BCNF 分解）

• Input（原料）： 一张可能充满病毒（非 BCNF）的大表 R，和一堆规则 F。

• Process（屠宰）：
1. 检查这张表是不是 BCNF？
2. 如果是，收工。
3. 如果不是（比如发现 X→Y 这个二五仔），那就一刀切下去！把表拆成两半。
4. 对拆出来的两半，重复步骤 1。直到所有的表都干净为止。

• Output（成品）： 一堆小表 D，每一张都必须是 BCNF，而且拼起来还能还原。

Cover覆盖

• F 说：只要你有钱，就能买车；只要有车，就能载妹。

• G 说：只要你有钱，就能载妹。

• 虽然 F 没直接说有钱能载妹，但通过 F 的逻辑能推导出 G。所以 F 覆盖了 G。

等价

• F 能推导出 G 的所有屁话。

• G 也能推导出 F 的所有废话。

• FG等价

最小覆盖

• 能够覆盖原fd

• fd一一对应

• 不要有多余的语句
• 如果一条规矩删了，剩下的规矩还是能推导出它，那这条规矩就是多余的废话。

• 左边不准带拖油瓶 (No extraneous)： 如果 AB→C，而老子发现光靠 A→C 就够了，那 B 就没用。

LLJD-DPD-3NF

• LLJD (无损连接)： 拼回去的时候别给我多出一块肉，也别少个腰子。

• DPD (依赖保持)： 以前的帮规（函数依赖）在新表里还得管用，别拆完之后没人守规矩了。

• 3NF (第三范式)： 别有那些小弟勾搭小弟的破事（传递依赖）。

1. 去废话： 先把函数依赖变成

2. 拉小圈子： 对于 Fmin 里的每一条规矩 X→Y，直接建一张新表，把 X 和 Y 关在一起。

3. 找老大： 看看这些新表里，有没有哪张表包含了原表的老大（候选键 CK）？
1. 如果有，恭喜，你已经完事了。
2. 如果没有，硬塞一个！ 专门建一张只包含候选键的表。

4. 删多余： 如果一张小表是另一张表的“子集”，直接把它踢了。

ER图

• 矩形（方块）： 实体（Entity）。就是表里的“主角”

• 椭圆（圆圈）： 属性（Attribute）。就是主角身上的“零件”

• 菱形： 关系（Relationship）。就是主角们是怎么“勾搭”在一起的

多对多/三元关系

• 要R表中转，其他的分别建表

二元关系

• 方块 E 和方块 F 通过 R 勾搭。

• x 和 y 是基数（Cardinality），表示一个人能勾搭几个。

1. 一对多： 众多的“小弟”兜里揣着“大哥”的身份证（1端主键做n端外键）。

2. 一对一： 别让表里出现太多空位（NULL），谁必须参与就让谁带着对方的 ID。

3. 多对多： 别瞎折腾，直接建个“案发现场”表（中介表），把双方的 ID 都拽过来。

孩子复习不完了……就这样吧