MySQL学习笔记5_TCL事务控制语言&视图

Transaction Control Language 事务控制语言
事务： 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
案例： A给B转账【A账户减少，B账户增加】为一个执行单元

一、事务

事务的ACID属性：
Atomicity 原子性：一个事务不可分割，要么都发生，要么都不发生；
Consistency 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态；
Isolation 隔离性：各个事物之间不能互相干扰（视隔离级别而定）；
Durability 持久性：事务一旦提交就是永久性的。

事务的创建

• 隐式事务：事务没有明显的开启和结束标记
  比如insert 、update、delete语句

• 显式事务：事务具有明显的开启和结束标记
  前提：必须先设置自动提交功能为禁用
  步骤1：开启事务
  set autocommit=0;
  start transaction; 可选的
  步骤2：编写事务中的sql语句（select insert update delete）
  语句1；
  语句2；
  …
  步骤3：结束事务
  commit; 提交事务
  rollback;回滚事务(二选一)

数据库的隔离级别:
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。

对于两个事务T1,T2
①脏读：T1读取了已经被T2【更新但还没有被提交】的字段，之后若T2回滚则T1所读取的内容就是临时且无效的。
②不可重复读：T1读取了一个字段，然后T2【更新】了该字段，之后T1若再读取该字段值就不同了。
③幻读：T1从一个表中读取了一个字段，然后T2【插入】了新的行，之后T1若再次读取同一个表，就会多出几行。

Oracle支持2种隔离级别：READ COMMITED(默认),SERIALZABLE
Mysql提供的四种事务隔离级别：

• READ UNCOMMITED(读未提交数据)：均不可避免
• READ COMMITED(读已提交数据)：仅能避免脏读
• 默认 REPEATABLE READ(可重复读)：避免脏读、不可重复读
• SERIALIZABLE(串行化)：能避免所有并发问题，但性能低下

查看当前隔离级别： select @@tx_isolation;
	设置当前mysql连接的隔离级别：
		set transaction isolation level 隔离级别;
	设置数据库系统的全局隔离级别：
		set global transaction isolation level 隔离级别;
		
		savepoint 保存点
        SHOW ENGINES; #查看支持的存储引擎

1.1 演示事务的使用步骤

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username, balance)
VALUES('A',1000),('B',1000);

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='A';
UPDATE account SET balance=1500 WHERE username='B';
#结束事务
COMMIT;

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance=1000 WHERE username='A';
UPDATE account SET balance=1000 WHERE username='B';
#结束事务
ROLLBACK;

1.2 演示savepoint的使用

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id= 1;
SAVEPOINT a; #设置保存点a
DELETE FROM account WHERE id= 2;
ROLLBACK TO a; #回滚到保存点

1.3 delete 和 truncate 在事务使用时的区别

#delete支持回滚
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#truncate不支持回滚
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

二、视图

含义： 虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

应用场景： 当多个地方用到同样的查询结果，且使用的sql语句比较复杂时

	创建语法的关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，一般用作查询，不能增删改
表	create table	保存了数据	增删改查

2.1 创建视图

语法：

create view 视图名
	as
	查询语句； 

案例1：查询姓名中包含a字符的员工名、部门名和工种信息

#①创建视图
CREATE VIEW v1
AS
SELECT `last_name`,`department_name`,`job_title` 
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON j.job_id=e.job_id;

#②使用视图
SELECT * FROM v1 WHERE `last_name`LIKE '%a%';

2.2 视图的修改

方式一：
	create or replace view 视图名
	as
	查询语句；
方式二：
	alter view 视图名
	as
	查询语句；

2.3 删除视图

drop view 视图名，视图名，...；

2.4 查看视图

DESC v1;
SHOW CREATE VIEW v1; #cmd适用

2.5 视图的更新

语法和数据的增删改一样
以下类型的视图不能更新：

1. 包含关键字：分组函数，group by , distinct, having, union/union all
2. 常量视图
3. select中包含子查询
4. 有连接 join（可更新，不可插入）
5. from 一个不能更新的视图
6. where 子句的子查询引用了from子句中的表

举例6： 查询领导的信息

CREATE VIEW v2
AS
SELECT `last_name`,`email`,`salary`
FROM employees
WHERE `employee_id` IN(
	SELECT `manager_id`
	FROM employees
	WHERE `manager_id` IS NOT NULL
);
#更新
UPDATE v2 SET salary =10000 WHERE last_name='k_ing';