java全栈学习之路——mysql(4)

Mysql

表

为了便于解释，因此作出以下两个表便于直观可见。

• table1:

id	name	pid
1	小明	1
2	小芳	1
3	小强	2
4	小李	3

• table2:

id	class
1	一班
2	二班

子查询

1. 单行单列子查询。
   意思就是对对查询出来的单个结果进行判断。
   判断符号>、=、<、<>

SELECT id FROM table2 WHERE class = "一班"; #1
SELECT * FROM table1 WHERE pid = 1;
查询结果等效于：
SELECT * FROM table1 WHERE pid = (SELECT id FROM table2 WHERE class = "一班");

id	name	pid
1	小明	1
2	小芳	1

2. 多行单列子查询。
   对查询的list集合进行判断。
   
   in、(符号)any、(符号)all
   
   1. in: 和其中任意一项相同。
   
   2. =any: 和in相同。
   
   3. >any: 大于最小。
   
   4. <any： 小于最大。
   
   5. >all: 大于最大。
   
   6. <all： 小于最小。

SELECT * FROM table1 WHERE pid IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE pid >ANY (SELECT id FROM table2);
SELECT * FROM table1 WHERE pid <ALL (SELECT id FROM table2);

id	name	pid
1	小明	1
2	小芳	1
3	小强	2

id	name	pid
3	小强	2
4	小李	3

id	name	pid

3. 多列子查询。
   其实就是为了减少笛卡尔积。

SELECT * FROM table1 LEFT JOIN (SELECT * FROM table2 WHERE class = "一班") tab ON tab.id = table1.pid;

id	name	pid	id	class
1	小明	1	1	一班
2	小芳	1	1	一班
3	小强	2	NULL	NULL
4	小李	3	NULL	NULL

事物的ACID

1. 原子性（Atomicity）：事务中包含的操作集合，要么全部操作执行完成，要么全部都不执行。即当事务执行过程中，发生了某些异常情况，如系统崩溃、执行出错，则需要对已执行的操作进行回滚，清除所有执行痕迹。
2. 一致性（Consistency）：事务执行前和事务执行后，数据库的完整性约束不被破坏。即事务的执行是从一个有效状态转移到另一个有效状态。
3. 隔离性（Isolation）：多个事务并发执行时，彼此之间不应该存在相互影响。隔离程度不是绝对的，每个数据库都提供有自己的隔离级别，每个数据库的默认隔离级别也不尽相同。
4. 持久性（Durability）：事务正常执行完毕后，对数据库的修改是永久性的。即事务的修改操作已经记录到了存储介质中。

事物的控制语音

1. begin: 开启一个事物，开启一个新的事物空间。
2. commit：提交事物。
3. rollback: 回滚事物。

事物的隔离级别

事务隔离级别	脏读	不可重复读	幻读	第一类丢失更新	第二类丢失更新
读未提交(read uncommitted)	√	√	√	×	√
读已提交{read committed)	×	√	√	√	√
可重复读(repeatable read)	×	×	√	√	√
串行化(serializable)	×	×	×	×	×

mysql推荐使用repeatable read。

默认情况：mysql不支持幻读。
除非：select * from 表名 lock in share mode。

mysql中锁基于索引机制，也不会出现第一类丢失更新。

所以针对mysql而言我们只需考虑第二类更新。

解决办法:

使用锁机制来解决。

1. 悲观锁：select * from 表名 for update.
2. 乐观锁：利用版本控制。

事务隔离级别详解：

1. 脏读

a事务读取到b事务尚未提交的更改数据，并在这个数据的基础上操作，如果恰巧b事务回滚，那么a事务读到的数据根本不被承认。

时间	取款事务a	转账事务b
T1		开启事务
T2	开启事务
T3		查询账户余额1000
T4		取出1000，账户余额为0
T5	查询账户余额为0.(脏读)
T6		撤销事务，账户余额恢复1000
T7	存入500，账户余额修改为500
T8	提交事务

2. 不可重复读

a事务读取到b事务已经提交的更改数据。

时间	取款事务a	转账事务b
T1	开启事务
T2		开启事务
T3	查询账户余额1000
T4		查询账户余额1000
T5	取出1000，账户余额为0
T6	提交事务
T7		查询账户为0.(不可重复读)
T8	提交事务

3. 幻读

a事务读取到b事务新增数据，这是a事务将出现幻读的原因。

时间	取款事务a	转账事务b
T1		开启事务
T2	开启事务
T3	统计总存款1000
T4		新增加一个账户，存款500
T5		提交事务
T6	再次统计账户余额为1500（幻读）

4. 事务第一类丢失更新（回滚丢失）

a事务撤销时对已提交的b事务覆盖。

时间	取款事务a	转账事务b
T1	开启事务
T2		开启事务
T3	查询账户余额1000
T4		查询账户余额1000
T5		转入500，账户余额修改为1500
T6		提交事务
T7	取出200，账户余额修改为800
T8	撤销事务（回滚）
T9	账户余额恢复1000（丢失更新）

5. 事务第二类丢失更新（覆盖丢失）

b事务覆盖a事务已经提交的数据，造成a事务操作数据丢失。

时间	取款事务a	转账事务b
T1	开启事务
T2		开启事务
T3	查询账户余额1000
T4		查询账户余额1000
T5	取出500，账户余额500
T6	提交事务
T7		转入500
T8		提交事务
T9		余额修改为1500.（丢失更新）