MySQL笔记4--事务&视图&存储过程&锁

渔舟唱晚

计算机视觉、深度学习、机器学习、大数据、人工智能

前置：渔舟唱晚：MySQL笔记三--多表查询

事务

四大特性：原子性(A)、一致性(c)、隔离性(i)、持久性(D)

1、演示案例

## 事务
-- 创建账户表
create table account(
 id int PRIMARY key auto_increment,
 uname varchar(10) not null,
 balance double
select * from account;
delete from account;
insert into account values(null,"张三",2000),(null,"李四",2000);
update account set balance=balance-200 where id=1;
update account set balance=balance+200 where id=2;
-- 上面的语句是数据操作语言，即DML 每个DML语言都是一个事务，上面有两个事务
-- 但如果操作错误，把id=1的用户扣钱了，但id=2的用户没有增加余额，会出错
-- 所以要把以上两句话放在一个事务里，要么都成功、要么都失败
start TRANSACTION
update account set balance=balance-200 where id=1;
update account set balance=balance+200 where id=2;
-- 手动回滚
rollback
-- 手动提交
commit
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

2、事务的并发

2.1、脏读( 读到了还没提交的数据，比如数据为100，此时A将其改为200，此时B去读数据，发现其为200，然后A进行了回滚，数据仍为100，脏读就是读到了不正确的数据 )

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中,这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是”脏数据”，依据“脏数据”所做的操作可能是不正确的。

2.2、不可重复读

(Unrepeatableread) : 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

如A第一次读到的数据为100，此时B将其修改为200，A再次读数据，数据为200，两次数据不一致。

为了解决该问题，可以使用加锁的方法，对正在读的数据加锁，不允许对其修改。

2.3、幻读

(Phantom read) : 幻读与不可重复读类似。它发生在一个事务(T1) 读取了几行数据，接着另-个并发事务(T2) 插入了一些数据时。

在随后的查询中，第-一个事务(T1) 就会发现多了-一些原本不存在的记录，就好像发生了幻觉-样，所以称为幻读。

为了解决该问题，需要锁住表，不允许插入或删除数据

不可重复读和幻读区别:

不可重复读的重点是修改,幻读的重点在于新增或者删除。

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

3、事务的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE,隔离级别越低，越能支持高并发的数据库操作。

mysql的默认事务隔离级别

--查看默认的事务隔离级别，MySQL默认的是repeatable read

select @@transaction_ isolation;

设置事务的隔离级别(设置当前会话session的隔离级别)

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

对于第一个隔离级别，即 read uncommitted，它可以查看到修改后未提交的数据，即会造成脏读，

而对于第二个隔离级别，即read committed，它只能看到提交后的数据，不会有脏读，其它隔离级别同理。

为了解决事务隔离性的问题，数据库一般会有不同的隔离级别来解决相应的读写影响。

读未提交：一个事务B还没提交，它的修改就被别的事务A读到了。
读已提交：一个事务B提交后，它的修改被其他事务A看到了。
可重复读：一个事物B提交前和提交后，事务A都无法读到事务B的变更。
串行化：对同一行记录，当出现不同事物的读写冲突时，是通过串行化的方式解决的，后一个事务必须等前一个事务完成才能执行。

不同隔离级别能够解决不同的隔离性问题。

参考链接：跟面试官侃半小时MySQL事务隔离性，从基本概念深入到实现 - 阿丸的文章 - 知乎 https:// zhuanlan.zhihu.com/p/11 8658549

视图

概念(可以想象成是从真实存在的基础表中查询得到的中间结果，如select 字段1，字段2 from table,数据库中只存放视图的定义，即该sql语句，而不是该sql查到的数据)

视图(view)是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样, 视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化,视图中相应的数据也会跟着改变。同样对视图插入数据，原来的基础表中也会插入数据。

PS:视图本质上就是:一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

视图的好处：

简化用户操作: 视图可以使用户将注意力集中在所关心地数据上,而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。对机密数据提供安全保护:有了视图,就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据(如,敏感字段"salary" )出现在不应该到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能。

-- 视图
-- 创建或替换视图(已有则替换)，是对下面这个SQL结果的俯瞰，然后可以从这个俯瞰中获取数据
create or replace view myview01 as select empno,ename,job,deptno from emp where deptno=20
with check option;
##带上with check option后，就不能插入部门为非20的员工数据了
select * from myview01;
##对视图插入数据，该数据也会插入到原来的基础表中
insert into myview01(empno,ename,job,deptno) values(9999,"andy","CLERK",20);
##因为有with check option，所以下面的语句不能插入成功
insert into myview01(empno,ename,job,deptno) values(888,"anni","CLERK",30);
##甚至可以对视图做视图，对俯瞰图再做一次俯瞰图

存储过程

1、什么是存储过程？

在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。

这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的- 系列SQL命令(Procedure)的集合。也互之间有关系的SQL命令组织在一起形成的一个小程序。

2、存储过程的优点

1)提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析,编译,执行,而存储过程只是在第一次执行语法分析,编译,执行，以后都是对结果进行调用。

2)可减轻网络负担。使用存储过程,复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行,比起需要多次传递SQL命令本身，这大大减轻了网络负担。

3)可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了。

3、案例

从员工表中查询名字中含有字母“A”的，然后查询名字中含有字母“T”的，这样需要逐个传入参数查询，存储过程类似函数，可以传入参数查询符合不同要求的名字。

-- 存储过程
-- 定义一个没有返回值的存储过程，实现模糊查询
select * from emp where ename like "%A%";
create procedure mypro01(name varchar(10))
begin
  if name is null or name="" then
     select * from emp;
     select * from emp where ename like concat("%",name,"%");
  end if;
-- 删除存储过程
drop producer mypro01;
-- 调用存储过程(相当于调用函数，要传参数)
call mypro01(null);
call mypro01('R');
-- 定义一个有返回值的存储过程，实现模糊查询,返回名字个数
create procedure mypro02(in name varchar(10),out num int(3))
begin
  if name is null or name="" then
     select * from emp;
     select * from emp where ename like concat("%",name,"%");
  end if;
	select found_rows() into num;

前置： 渔舟唱晚：MySQL笔记三--多表查询

事务

视图

存储过程

前置：渔舟唱晚：MySQL笔记三--多表查询