MySQL数据库设计概念及多表查询和事物操作(2)-木庄网络博客

一条查询语句结果作为另一条查询语法一部分。

SELECT 查询字段 FROM 表 WHERE 条件;
举例：
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

说明：子查询需要放在（）中

三种子查询情况：单行单列、多行单列、多行多列。

2.单行单列

子查询结果是单列，在WHERE后面作为条件

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

通常使用比较运算符: > 、>= 、<、<=、=等

3.多行单列

子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符

-- IN表示在数值中
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

4.多行多列

子查询结果是多列，在FROM后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

注意：子查询作为表需要取别名，使用as，可以省略，否则这张表没用名称无法访问表中的字段

事务操作

事务的概念

什么是事务

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

简而言之，事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

事务的应用场景声明

关于事务在实际中的应用场景：

假设我在淘宝买了一部手机，然后当我付完款，钱已经从我的账户中扣除。正当此时，淘宝转账系统宕机了，那么此时淘宝还没有收到钱，而我的账户的钱已经减少了，这样就会导致我作为买家钱已经付过，而卖家还没有收到钱，他们不会发货物给我。这样做显然是不合理。实际生活中是如果淘宝出问题，作为用户的账户中钱是不应该减少的。这样用户就不会损失钱。

还有种情况，就是当我付完款之后，卖家看到我付款成功，然后直接发货了，我如果有权限操作，我可以撤销，这样就会导致我的钱没有减少，但是卖家已经发货，同样这种问题在实际生活中也是不允许出现的。

MySQL中可以有两种方式进行事务的操作：

手动提交事物：先开启，在提交
自动提交事物（默认的）：即执行一条sql语句提交一次事物

数据准备

# 创建账号表
create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);

手动提交事务

手动提交事务有关的sql语句

SQL语句	描述
`start transaction`	开启手动控制事物
`commit`	提交事物
`rollback`	回滚事物

手动提交事务使用步骤

开启事务–>执行SQL语句–>成功–>提交事务
开启事务–>执行SQL语句–>失败–>回滚事务

演示案例：演示提交事务，a给b转账100元

-- 1.开启事务
start transaction;
-- 2.执行sql语句
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3.提交事务
commit;

案例演示：演示回滚事务，a给b转账100元

-- 1.开启事务
start transaction;
-- 2.执行sql语句
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3.回滚事务
rollback;

注意：

提交事务(commit) ：事务提交之后，sql语句对数据库产生的操作才会被永久的保存
事务的回滚(rollback)：撤销已经成功执行的sql语句，回到开启事务之前的状态
只要提交事务，那么数据就会长久保存了，就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作

自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。自动提交，通过修改mysql全局变量autocommit进行控制。

通过以下命令可以查看当前autocommit模式

show variables like '%commit%';

设置自动提交的参数为OFF

set autocommit = 0;  -- 0:OFF  1:ON

案例演示

-- 自动提交事务：每条sql语句就是一个事务，那么执行一条sql语句就会提交一次事务
-- mysql数据库就是自动提交事务
-- a给b转账100元
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';

-- 查看mysql是否自动提交事务
-- autocommit的值是on表示自动提交事务，值是off表示关闭自动提交事务
show variables like '%commit%';
-- 我们可以使用命令临时设置mysql变为手动提交事务，即将自动提交事务关闭
-- 下次重新连接mysql依然是自动提交事务
set autocommit = 0; -- 0 表示关闭自动提交事务 1表示开启自动事务
update account set money=money-100 where name='a'

注意：

1）MySql默认自动提交。即执行一条sql语句提交一次事务。

2）设置autocommit为off状态，只是临时性的，下次重新连接mysql，autocommit依然变为on状态。

3）如果设置autocommit为off状态，那么当我们执行一条sql语句，就不会自动提交事务，重新启动可视化工具，数据并没有改变。

4）如果设置autocommit为on状态，如果我们先执行start transaction 然后在执行修改数据库的语句：

update account set money = money-100 where name='a';
update account set money = money+100 where name='b';

那么此时就表示上述修改数据库的sql语句都在同一个事务中，此时必须手动提交事务，即commit；

换句话说，如果我们手动开启事务start transaction那么此时mysql就不会自动提交事务，必须手动提交事务。

5）如果设置autocommit为on状态，如果我们不执行start transaction 直接执行修改数据库的语句：

update account set money = money-100 where name='a';
update account set money = money+100 where name='b';

那么此时mysql就会自动提交事务，即上述每条sql语句就是一个事务

事务原理和四大特征

事务原理

原理说明

一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
如果开启事务，将所有的写操作写到日志文件中。
如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。

事务的四大特征

事务的四大特征（ACID）

数据库的事务必须具备ACID特征，ACID是指Atomicity（原子性）、Consistensy（一致性）、Isolation（隔离性）和Durabiliyt（持久性）

隔离性（Isolation）

多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事物干扰，多个并发的事务之间相互隔离

持久性（Durability）

指一个事务一旦被提交，它对数据库的改变是永久性的，哪怕数据库发生异常，重启之后数据依然会存在

原子性（Atomicity）

指事务包装的一组sql语句（一组业务逻辑）是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

一致性（Consistency）

一致性是指数据处于一种语义上有意义且正确的状态；

事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。

事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性

事务的并发访问引发的三个问题(面试)

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据。最严重，杜绝发生。
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是不一致的，这是事务update时引发的问题
幻读(虚读)	一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。这是insert或delete时引发的问题

1.脏读：指一个事务读取了另外一个事务未提交的数据。(非常危险)

2.不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。

3.幻读（虚读）:一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同

4.总结

赃读：一个事务读取另一个事务还没有提交的数据,一定避免。
不可重复读：一个事务读取多次数据内容不一样，主要是update语句。事务已经提交了。可以发生的。
幻读（虚读）：一个事务读取多次数量不一样，主要是delete或者insert语句。事务已经提交了。可以发生的。

事务的隔离级别

通过以上问题演示，我们发现如果不考虑事务的隔离性，会遇到脏读、不可重复读和虚读等问题。所以在数据库中我们要对上述三种问题进行解决。MySQL数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

事物隔离级别

上面的级别最低，下面的级别最高。是表示会出现这种问题，否表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否