MySql事务，视图
一、事务
#事务 一个或一组SQL要么全部执行，要么全部不执行
1、事务的基本要素（ACID）
#事务的ACID属性 #原子性（Atomicity） 一个事务是一个整体 不可拆分 要么都成功 要么都失败 #一致性（Consistency） 事务必须使数据库从一个一致性状态，变换到另一个一致性状态 也就是本来两个人金额总和为200，转账后也得是200 #隔离性（Isolation） 两个事务相互隔离，并发执行的各个事务不能相互干扰 #持久性（Durability） 一个事务一但被提交，它对数据库中的数据的改变是永久性的
2、事务的并发问题
【1】脏读（读取未提交数据）
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。
这种情况常发生于转账与取款操作中
|
时间顺序 |
转账事务 |
取款事务 |
|
1 |
|
开始事务 |
|
2 |
开始事务 |
|
|
3 |
|
查询账户余额为2000元 |
|
4 |
|
取款1000元，余额被更改为1000元 |
|
5 |
查询账户余额为1000元（产生脏读） |
|
|
6 |
|
取款操作发生未知错误，事务回滚，余额变更为2000元 |
|
7 |
转入2000元，余额被更改为3000元（脏读的1000+2000） |
|
|
8 |
提交事务 |
|
|
备注 |
按照正确逻辑，此时账户余额应该为4000元 |
|
【2】不可重复读（前后多次读取，数据内容不一致）
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
|
时间顺序 |
事务A |
事务B |
|
1 |
开始事务 |
|
|
2 |
第一次查询，小明的年龄为20岁 |
|
|
3 |
|
开始事务 |
|
4 |
其他操作 |
|
|
5 |
|
更改小明的年龄为30岁 |
|
6 |
|
提交事务 |
|
7 |
第二次查询，小明的年龄为30岁 |
|
|
备注 |
按照正确逻辑，事务A前后两次读取到的数据应该一致 |
|
【3】幻读（前后多次读取，数据总量不一致）
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。
|
时间顺序 |
事务A |
事务B |
|
1 |
开始事务 |
|
|
2 |
第一次查询，数据总量为100条 |
|
|
3 |
|
开始事务 |
|
4 |
其他操作 |
|
|
5 |
|
新增100条数据 |
|
6 |
|
提交事务 |
|
7 |
第二次查询，数据总量为200条 |
|
|
备注 |
按照正确逻辑，事务A前后两次读取到的数据总量应该一致 |
|
不可重复读和幻读到底有什么区别呢？
（1）不可重复读是读取了其他事务更改的数据，针对insert与update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
（2）幻读是读取了其他事务新增的数据，针对insert与delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。
3、事务的简单使用
#start transaction; 开启事务 update user set balance = balance-100 where name = 'hby'; update user set balance = balance-100 where name = 'pdun'; #rollback; #全部撤销（回滚） #commit; 注意：commit前可以回滚，一旦执行了commit，将无法回滚 ------------------------------------------------------------------------ #start transaction; update user set balance = balance-100 where name = 'hby'; update user set balance = balance+100 where name = 'pdun'; #savepoint p; #创建保存点，可以选择回滚到此处，注意：一定取名字 update user set balance = balance+100 where name = 'pdun'; #rollback to p;
4、事务的隔离级别
#脏读，幻读，不可重复读都有 mysql> select @@tx_isolation; #查看隔离级别 +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) #设置隔离级别set session transaction isolation level mysql> set session transaction isolation level read uncommitted; #读不提交 mysql> select * from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1000 | | 2 | Moker | 1000 | +----+-------+---------+ 2 rows in set (0.00 sec) mysql> start transaction; #开启事务， Query OK, 0 rows affected (0.00 sec) #修改但未commit mysql> update user set balance = balance+100 where name = 'pdun'; ------------------------------------------- #此时打开另一个终端2，同样把隔离基别设置为最轻等级 mysql> start transaction; #开启事务， mysql> select * from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1100 | #发现数据已经改了，但是终端1并未提交，这就是脏读 | 2 | Moker | 1000 | +----+-------+---------+ #如果终端1 rollback，回滚了 mysql> select * from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1000 | #数据又变回来了，这就称为不可重复读和幻读 | 2 | Moker | 1000 | +----+-------+---------+ 2 rows in set (0.00 sec)
#没有脏读，存在幻读与不可重复读 #开启终端1 #设置隔离级别 mysql> set session transaction isolation level read committed; mysql> select *from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1000 | | 2 | Moker | 1000 | +----+-------+---------+ 2 rows in set (0.00 sec) mysql> start transaction; #开启事务 Query OK, 0 rows affected (0.00 sec) mysql> update user set balance = balance+100 where name = 'Moker'; #没有commit提交 ---------------------------------- #开启终端2 mysql> select * from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1000 | #没有脏读 | 2 | Moker | 1000 | +----+-------+---------+ #如果终端1此时提交了 mysql> select * from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1000 | | 2 | Moker | 1100 | #终端2事务并没提交，却改变了，出现了幻读 +----+-------+---------+ 2 rows in set (0.00 sec)
不存在脏读和不可重复读，存在脏读 开启终端1 #修改隔离等级 mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ mysql> select *from user; +----+-------+---------+ | id | name | balance | +----+-------+---------+ | 1 | pdun | 1100 | #查看的一共两条数据，按理说应该两行收影响 | 2 | Moker | 1100 | +----+-------+---------+ 2 rows in set (0.00 sec) mysql> start transaction; mysql> update user set balance = balance+100; #这一步先不运行 --------------------------------- #开启终端2 mysql> set session transaction isolation level repeatable read; mysql> start transaction; Query OK, 0 rows affected (0.12 sec) mysql> insert into user(name,balance) -> values -> ('Bingo',1000); Query OK, 1 row affected (0.00 sec) mysql> commit; #插入一个字段并提交 Query OK, 0 rows affected (0.13 sec) ----------------------------------------------------- mysql> update user set balance = balance+100; #运行这一步 Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 #3行受影响，这就是幻读
二、视图
1、视图简介
#视图是什么 视图是由一条sql语句的查询结果构成的虚拟表 其不是物理存在的 使用方式与普通表相同 #视图的作用 简化sql语句的编写 限制可以查看的数据 #语法： CREATE VIEW 视图名称 AS SQL语句 CREATE [or repalce] VIEW 视图名称 [(column_list)] AS SQL语句 加上or repiale 时如果已经视图存在相同视图则替换原有视图 column_list指定哪些字段要出现在视图中
2、视图的增删改查（会同步到原始表）
视图是一张虚拟表 所以使用方式与普通表没有任何区别 #查看视图 1.desc view_name; //查看数据结构 索引信息不会体现在视图中 2.show create view view_name;//查看 创建语句 #修改视图 alter view view_name as select_statement #删除视图 drop view view_name
3、使用
案例一、简化SQL
create database db02 charset utf8; use db02 create table student( s_id int(3), name varchar(20), math float, chinese float ); insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75); create table stu_info( s_id int(3), class varchar(50), addr varchar(100) ); insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');
#不使用视图，每次查询信息，都需要连接查询 select student.s_id,student.name ,stu_info.class from student,stu_info where student.s_id=stu_info.s_id; #创建视图 create view view_student as select student.s_id,student.name ,stu_info.class from student,stu_info where student.s_id=stu_info.s_id; #以后再查询，只需要select *from view_student，不需要再每次连接查询，简化代码
案例二、隔离数据，设置权限
create table salarys( id int primary key, name char(10), salary double, dept char(10) ); insert into salarys values (1,"刘强东",900000,"市场"), (2,"马云",800090,"市场"), (3,"李彦宏",989090,"财务"), (4,"马化腾",87879999,"财务");
#设置一个部门都可观看 mysql> create view dept_view as select *from salarys where dept = "财务"; Query OK, 0 rows affected (0.13 sec) mysql> select *from dept_view; +----+-----------+----------+--------+ | id | name | salary | dept | +----+-----------+----------+--------+ | 3 | 李彦宏 | 989090 | 财务 | | 4 | 马化腾 | 87879999 | 财务 | +----+-----------+----------+--------+ 2 rows in set (0.00 sec) #设置仅可自己看自己的薪资 mysql> create view self_view as select *from salarys where name = "李彦宏"; Query OK, 0 rows affected (1.85 sec) mysql> select *from self_view; +----+-----------+--------+--------+ | id | name | salary | dept | +----+-----------+--------+--------+ | 3 | 李彦宏 | 989090 | 财务 | +----+-----------+--------+--------+ 1 row in set (0.01 sec)
浙公网安备 33010602011771号