一、事务
#事务 一个或一组SQL要么全部执行，要么全部不执行
1、事务的基本要素（ACID）
#事务的ACID属性 #原子性（Atomicity） 一个事务是一个整体 不可拆分 要么都成功 要么都失败 #一致性（Consistency） 事务必须使数据库从一个一致性状态，变换到另一个一致性状态 也就是本来两个人金额总和为200，转账后也得是200 #隔离性（Isolation） 两个事务相互隔离，并发执行的各个事务不能相互干扰 #持久性（Durability） 一个事务一但被提交，它对数据库中的数据的改变是永久性的
2、事务的并发问题
#1、脏读： 事务A更新了数据，但没有commit，此时事务B读取到了事务A的更新，就叫脏读 #2、不可重复读：（一个事务范围内两个相同的查询却返回了不同数据） 事务 A读取一个数据，事务 B 在对该数据更新并提交，导致事务A两次读取结果不一致，（B想问，我又没有提交事务，怎么就改变了呢） #3、幻读： 事务A想要把所有人的工资加1000，事务A查看表中一共有3个人，准备执行更新工作，此时事务B在表中增加了一个人，事务A执行更新工作，发现4个人受影响，莫名多出一个人 #小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
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)