第五章 数据库设计和事务 ① 笔记

1.内容回顾

1.1. 课前测试
1.2.上节内容

2.本章重点

2.1.设计数据三大范式
2.2.表间关系
2.3.数据库事务

3.具体内容

3.1:数据库三大范式（设计规则）

范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。一个数据库表之间的所有字段之间的联系的合理性。
学生表，班级表
 范式是离散数学里的概念
 范式目标是在满足组织和存储的前提下使数据结构冗余最小化
 范式级别越高，表的级别就越标准
目前数据库应用到的范式有以下几层
 第一范式：1NF
 第二范式：2NF
 第三范式：3NF
除此之外还有BCNF范式,4NF,5NF
一个数据库表设计的是否合理，要从增删改查的角度去考虑，操作是否方便

 面试题：(重要)

第一范式(1NF)：

确保表中每一列数据的原子性，不可再分！
比如: 外国人的名字可以分为FirstName和LastName，所以设计表的时候要把名字分开

第二范式(2NF)：

在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。

第三范式设计表：

在满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。
当数据库到达第三范式的时候，基本上有关数据冗余，数据插入、删除、更新的异常问
题得到了解决，这也是一个”合法的”数据库最基本的要求，但是效率问题就另当别论
了，因为表越多，连接操作就越多，但是连接是一个比较耗资源的操作。


在设计数据库的时候，要在满足自己需求的前提下，尽可能的满足三大范式。

3.2 表间的关系

需求分析：
 一对一关系： 一个学号对一个姓名
在设计数据库时如果是一对一关系，直接设计成一张表。（如果在字段非常多的情况下，可以做合理的分表）
 一对多（多对一）关系：一个老师多个班级 ，一个班级对多个学生，
一个订单包含多个商品。多个商品属于一个订单。
设计时主要是通过外键关联。
 多对多关系： 学生对课程 ,用户购买商品
设计数据库时，多对多关系，需要一个中间表进行关联！
案例：
创建数据库表的前提是，要先理清楚表间关系。
权限系统设计
员工信息表(包含账号): 工号,姓名,…
部门表: 部门编号,名字…
职位表(是否与部门关联): 职位编号,名字,…
功能菜单表: 菜单编号,菜单名字,菜单级别,父级菜单,url…
职位菜单关联表: 职位编号,菜单编号

3.3 事务管理

3.3.1.为什么需要事务 （一件完整的事情）

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。即一组sql中哪怕有一条失败也会失败
例子: 转账操作,一个账户减钱，一个账户加钱
默认情况下，mysql的事务是自动提交的，也就是执行了增删改语句之后，数据直接持久化到磁盘上，不能撤销。
但是如果改为手动事务之后，更新过的数据，再没有使用commit提交时，可以通rollback进行撤回。

3.3.2.使用事务解决问题

转账案例：张三给李四转账。

#创建银行帐号表
create table bank
(
	bid int primary key auto_increment,
	account varchar(20),
	money int
)

select * from bank;

insert into bank
(account,money)
values
('张三',10000),
('李四',10000)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

事务处理1: 执行过程中遇到错误，回滚事务

#开启事务
start transaction;
update bank
set money = money - 3000
where account='张三'

select * from bank

#在向李四转钱之前，发生错误了，此时回滚事务
rollback;
1
2
3
4
5
6
7
8
9
10

事务处理2: 执行过程正常，提交事务

#开启事务
start transaction;

update bank
set money = money - 3000
where account='张三'

update bank
set money = money + 3000
where account= '李四'

select * from bank

#提交事务
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.3.3.数据库事务的原理

如果不写start transaction/begin;commit; 此时事务默认开启自动提交；
在数据库中 ，事务都是自动提交的。事务的自动提交就是 执行语句完成之后 就立刻持
久化到数据库中。
start transaction/begin;开始事务
rollback;回滚事务
commit;提交事务
当我们添加了start transaction/begin;和commit；后 事务的提交就从自动变成手动。
因为中途出错，所以导致commit不执行，也就是说缓冲区中的数据没有持久化的数据库
中。

3.3.4.什么是数据库事务

从开启到提交为一个事务。
由此可见，一个事务对应一组业务。一个事务中间可以有一条sql,多条sql。
所以 一个业务开始之前 开启事务 一个业务结束之后 提交事务。如果中途出错，可以
回滚事务
我们这个转账案例：需要几个事务？
可以写成两个事务，但是不合适。因为我们的需求 让张三减的同时让李四加钱。只能写
成一个事务。

3.3.5.事务的特征ACID 面试题(重要)

要求概念背会
一、事务的基本要素（ACID）
事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问
并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵
语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如
start transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
在事务开始和结束之间做的所有的事情叫完整事务。
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

1、原子性（Atomicity）：

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：

事务开始前和结束后，数据库的数据完整性约束没有被破坏，事务前后操作数据是一致的 。比如A向B转账，不可能A扣了钱，B却没收到。能量守恒。

3、隔离性（Isolation）：在并发场景下有不同的设置

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
两个事务之间是有隔离级别，隔离级别的不同会导致出现不同的问题。此时产生三种问题：脏读 幻读 不可重复读。

4、持久性（Durability）：

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3.4 事务的并发问题

3.4.1.事务并发问题

数据库事务无非就两种：查询数据的事务（select）、更新数据的事务（update,insert）。
在没有事务隔离控制的时候，多个事务在同一时刻对同一数据的操作可能就会影响到结
果，通常有四种情况：
 两个更新事务同时修改一条数据时，会出现数据丢失，绝对不允许出现
 一个更新事务更新一条数据时，另一个读取事务读取了还没提交的更新，这种情况下会出现读取到脏数据
 一个读取事务读取一条数据时，另一个更新事务修改了这条数据，这时就会出现不可重现的读取
 一个读取事务读取时，另一个插入事务（注意此处是插入）插入了一条新数据，这样就可能多读出一条数据，出现幻读
注意：前三种是对同一条数据的并发操作，对程序的结果可能产生致命影响，尤其是金融等实时性，准确性要求极高的系统，绝不容许这三中情况的出现。
相比第四种情况不会影响数据的真实性，在很多情况下是允许的，如社交论坛等实时性要求不高的系统！

事务的并发问题：

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数
据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数
据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致
3、A事务查询了某个Id的记录，发现为空，准备插入该编号的记录。此时B事务先做了
插入，并做了提交，此时A事务插入同编号的记录时触发了主键重复的异常，对于A事务
而言，第一次读的结果就像发生了幻觉。
注意：不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁
住满足条件的行，解决幻读需要锁表

3.4.2.MySQL事务隔离级别(重要)(用于解决事务并发问题)

隔离级别要求会描述和代码实现过程及描述


对于MySQL的Innodb的默认事务隔离级别是重复读（repeatable read）。可以通过下
面的命令查看：

mysql> SELECT @@tx_isolation;--5.7版本
mysql> SELECT @@transaction_isolation; --8.0版本
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
#设置mysql的隔离级别：
#set session transaction isolation level 设置事务隔离级别

#设置read uncommitted级别：
set session transaction isolation level read uncommitted;

#设置read committed级别：
set session transaction isolation level read committed;

#设置repeatable read级别：
set session transaction isolation level repeatable read;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Read Uncommitted(未提交读):
在该隔离级别下，并发事务可以读取未提交的数据，可能导致 脏读 不可重复读 幻读
等问题。
 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少
用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称
之为脏读

#事务隔离级别:未提交读(read uncommitted)
#未提交读级别会导致脏读的问题
#脏读问题的实现步骤(注意,事务a，事务b在不同的脚本中上运行)
#1.事务a，手动事务，更新数据
#2.事务b，在事务a没有提交的情况下,查询a更新的数据
#3.事务a, 回滚事务
#4.此时，事务b查询的结果和此时数据库中的数据是不一致的，称为脏读

#事务a
#设置事务为未提交读
set session transaction isolation level read uncommitted;
#开始手动事务
start transaction;
#更新数据
update classInfo
set className='111'
where classId=1;
#回滚事务
rollback;

#事务b:在另外一个脚本中运行
set session transaction isolation level read uncommitted;
select * from classInfo where classId=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Read Committed（提交读）：
在该隔离级别下，并发事务只能读取提交过的数据，可以避免脏读问题，但是可能导致
不可重复读 幻读 等问题。
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单
定义：一个事务只能看见已经提交事务所做的改变。
 这种隔离级别 也导致所谓的不可重复读（Nonrepeatable Read），因为同一事务的
其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结
果

#事务隔离级别:read committed(提交读)
#提交读会避免脏读的问题，但是会导致不可重复读问题
#不可重复读问题的实现步骤:
#1.事务a,开启手动事务,先查询一次编号为1的班级数据
#2.事务b,开启手动事务，更新编号为1的班级名称,并提交了事务
#3.事务a,在同一个事务中再查询编号为1的班级数据
#4.此时a事务中的两次查询结果不一致，称为不可重复读

#事务a
#设置事务隔离级别为提交读
set session transaction isolation level read committed;
#开启手动事务
start transaction;
#在事务中第一次查询数据
select * from classInfo where classId = 1;
#在事务中第二次查询数据
select * from classInfo where classId =1;
#提交事务
commit;

#事务b(在另外一个脚本中执行)
set session transaction isolation level read committed;
start transaction;
update classInfo
set className='111'
where classId = 1
commit;

/*
提交读避免了脏读的问题
#事务a
#设置事务为未提交读
#set session transaction isolation level read committed;
#开始手动事务
#start transaction;
#更新数据
update classInfo
set className='111'
where classId=1;
#回滚事务
rollback;

#事务b:在另外一个脚本中上运行
#set session transaction isolation level read committed;
#select * from classInfo where classId=1
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

Repeatable Read（可重复读）:
在该隔离级别下，在同一事务中多次查询数据都能保证一致，可以避免不可重复读问
题，但是可能导致 幻读等问题。
 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，
会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom
Read）

#事务隔离级别:repeatable read(可重复读)
#可重复读解决了不可重复读问题，但是会导致幻读问题
#幻读问题的实现步骤：
#1.事务a,开启手动事务,查询某个班表不存在的班级,例如编号10的班级
#2.事务b,开启手动事务,直接插入编号为10的班级数据，并提交事务
#3.事务a,插入编号为10的班级数据
#4.此时会触发主键重复异常,对于事务a而言，上一次明明查询的10是不存在的
# 但是在插入的时候，却出现错误，就象发生了幻觉一样

#事务a
#设置事务隔离级别为可重复读
set session transaction isolation level repeatable read;
#开启手动事务
start transaction;
#查询编号为10的班级
select * from classInfo where classId=10;
#插入班级编号为10的班级
insert into classInfo
(classid,className)
values
(10,'AAA10');
#提交事务
commit;

#事务b(在另外一个脚本中上运行)
set transaction isolation level repeatable read;
#开启手动事务
start transaction;
#插入编号为10的班级
insert into classInfo
(classId,className)
values
(10,'AAA10');
#提交事务
commit;

/*
解决了不可重复读的问题，在两次读的事务中，两次查询的结果都是一样的
#事务a
#设置事务隔离级别为提交读
set session transaction isolation level repeatable read;
#开启手动事务
start transaction;
#在事务中第一次查询数据
select * from classInfo where classId = 1;
#在事务中第二次查询数据
select * from classInfo where classId =1;
#提交事务
commit;

#事务b(在另外一个脚本中执行)
set session transaction isolation level repeatable read;
start transaction;
update classInfo
set className='111'
where classId = 1
commit;
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

Serializable（可串行化）:
 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读
问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量
的超时现象和锁竞争。

#事务隔离级别:序列化(serializable)
#序列化可以解决事务并发的所有问题，但是事务要排队一个一个执行，
#导致系统的效率非常低

#序列化级别解决幻读问题

#事务a
#设置事务隔离级别为可重复读
set session transaction isolation level serializable;
#开启手动事务
start transaction;
#查询编号为10的班级
select * from classInfo where classId=10;
#插入班级编号为10的班级
insert into classInfo
(classid,className)
values
(10,'AAA10');
#提交事务
commit;

#事务b(在另外一个脚本中上运行)
set transaction isolation level serializable;
#插入编号为10的班级
insert into classInfo
(classId,className)
values
(10,'AAA10');
#提交事务
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3.4.3.案例说明

脏读：

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
场景：公司发工资了，领导把5000元打到Tom的账号上，但是该事务并未提交，而
Tom正好去查看账户，发现工资已经到账，账户多了5000元，非常高兴，可是不幸的
是，领导发现发给Tom的工资金额不对，是2000元，于是迅速回滚了事务，修改金额
后，将事务提交，Tom再次查看账户时发现账户只多了2000元，Tom空欢喜一场，从
此郁郁寡欢，走上了不归路……

不可重复读：

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并
提交，导致事务A多次读取同一数据时，结果 不一致。
场景：Tom拿着工资卡去消费，酒足饭饱后在收银台买单，服务员告诉他本次消费
1000元，Tom将银行卡给服务员，服务员将银行卡插入POS机，POS机读到卡里余额
为3000元，就在Tom磨磨蹭蹭输入密码时，他老婆以迅雷不及掩耳盗铃之势把Tom工
资卡的3000元转到自己账户并提交了事务，当Tom输完密码并点击“确认”按钮后，
POS机检查到Tom的工资卡已经没有钱，扣款失败，Tom十分纳闷，明明卡里有钱，于
是怀疑POS有鬼，和收银小姐姐大打出手，300回合之后终因伤势过重而住进ICU，
Tom从此郁郁寡欢，从此走上了不归路…
分析：上述情况即为不可重复读，两个并发的事务，“事务A：POS机扣款”、“事务B：
Tom的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新数据并提交了事务，
而事务A再次读取该数据扣款

幻读：

已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数
据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围
的记录，但每次读取的纪录数不同，称之为幻象读
场景：Tom跟朋友聚餐完毕后开启事务付账，先付了80元烟钱，Tom的老婆正好在
家里查账发现Tom仅仅有80元的消费记录，非常吃惊，心想“老公真是太节俭了，嫁给
他真好！”，而Tom此时再次支付饭钱1000元，即新增了一条1000元的消费记录并提交
了事务，沉浸在幸福中的老婆不敢相信自己的眼睛，又查询了Tom当天工资卡消费明
细，一探究竟，可查出的结果竟然发现又多了一笔1000元的消费，Tom的老婆瞬间怒气
冲天，外卖订购了一个大号的榴莲，傍晚降临，Tom生活在了水深火热之中，只感到膝
盖针扎的痛…
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增
或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

总结：

三大范式：权限数据库的设计。
事务操作；掌握事务课程中涉及到的概念。

链接：mysql数据库设计理论

作业：

1.建表 权限系统 自己写需求（增删改查）：sql
2.事务概念
3.自己复习mysql所有知识点