Mysql学习笔记(10):事务、视图

1. 事务

1.1 事务的定义

事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

1.2 事务的属性

1.3 事务的分类

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

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommint=0

1.4 事务的使用

步骤：

步骤1：开启事务
set autocommit=0;
start transaction;（可选的）
步骤2：编写事务中的sql语句(select insert update delete)
步骤3：结束事务
commit;提交事务
rollback;回滚事务

#开启事务
SET autocommit = 0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 500 WHERE username='张无忌';
UPDATE account SET balance = 1500 WHERE username='赵敏';

#结束事务
COMMIT;
#ROLLBACK;

1.5 事务的隔离级别

1.5.1 脏读、不可重复读、幻读

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

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

1.5.2 隔离级别

-	脏读	不可重复读	幻读
read uncommitted	✔	✔	✔
read committed	×	✔	✔
repeatable read	×	×	✔
serializable	×	×	×

Oracle 支持的 2 种事务隔离级别：READ COMMITED(默认), SERIALIZABLE。
Mysql 支持 4 种事务隔离级别.默认为REPEATABLE READ
一些命令：

#查看当前隔离级别
SELECT @@tx_isolation;

#设置当前 mySQL 连接的隔离级别:
set transaction isolation level read committed;

#设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;

1.6 回滚点的使用

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=1;
SAVEPOINT a;
DELETE FROM account WHERE id=2;
ROLLBACK TO a;

注意：
delete可以回滚，truncate删除表后无法回滚

2. 视图

2.1 视图的定义

含义：虚拟表，和普通表一样使用，是通过表动态生成的数据
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
应用场景：多个地方用到同样的查询结果、该查询结果使用的sql语句较复杂

SELECT stuname, majorname 
FROM stuinfo s
INNER JOIN major m ON s.majorID=m.id
WHERE s.stuName LIKE '张%';

CREATE VIEW v1 
AS 
SELECT stuname, majorname 
FROM stuinfo s
INNER JOIN major m ON s.majorID=m.id
WHERE s.stuName LIKE '张%';

SELECT * FROM v1 WHERE stuname LIKE '张%';

2.2 视图的创建

语法：create view 视图名
as
查询语句;

#1.查询邮箱中包含a字符的员工名、部门名和工种信息

#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#2.查询各部门的平均工资级别

#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag, department_id
FROM employees 
GROUP BY department_id;

#②使用
SELECT myv2.ag, g.grade_level
FROM myv2 
JOIN job_grades g
ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal

#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3 
AS 
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*, m.ag
FROM myv3 m 
JOIN departments d
ON m.department_id = d.department_id;

2.3 视图的修改

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

SELECT * FROM myv3;

CREATE OR REPLACE VIEW myv3 
AS 
SELECT AVG(salary),job_id 
FROM employees
GROUP BY job_id;

方式二：
alter view 视图名
as
查询语句;

ALTER VIEW myv3
AS
SELECT * FROM employees;

2.4 删除与查看视图

删除
语法：drop view 视图名,视图名;
查看
DESC
SHOW CREATE VIEW 视图名;

2.5 视图的更新

如果不进行操作，在增删改视图的情况下就可以更改表中数据

SELECT * FROM employees;
SELECT * FROM empv1;
#1.插入
INSERT INTO empv1 VALUES('张飞','zf@163.com');
#2.修改
UPDATE empv1 SET last_name = '张无忌' WHERE last_name='张飞';
#3.删除
DELETE FROM empv1 WHERE last_name = '张无忌';

具有以下特点的视图不允许更新

#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
CREATE OR REPLACE VIEW empv2
AS 
SELECT MAX(salary), department_id 
FROM employees
GROUP BY department_id;

SELECT * FROM empv2;
#更新
UPDATE empv2 SET m=9000 WHERE department_id=10;


#②常量视图
CREATE OR REPLACE VIEW empv2
AS
SELECT 'jhon' NAME;

SELECT * FROM empv2;
#更新
UPDATE empv2 SET NAME='lucy';


#③SELECT中包含子查询
CREATE OR REPLACE VIEW empv2
AS 
SELECT (SELECT MAX(salary) FROM employees) 最高工资;
#更新
SELECT * FROM empv2;
UPDATE empv2 SET 最高工资=100000;


#④join
CREATE OR REPLACE VIEW empv2
AS 
SELECT last_name, department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;
#更新
SELECT * FROM empv2;
UPDATE empv2 SET last_name='张飞' WHERE last_name='Whalen';
INSERT INTO empv2 VALUES('陈真','xxxxx');


#⑤from一个不能更新的视图
#⑥where子句的子查询引用了from子句中的表
CREATE VIEW myv4
AS
SELECT last_name,email,salary 
FROM employees
WHERE employee_id IN(
	SELECT manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);
#更新
SELECT * FROM myv4;
UPDATE myv6 SET salary=10000 WHERE last_name = 'K_ing';

2.6 视图与表的对比

-	创建语法的关键字	是否占用事积的物理空间
视图	create view	保存了sql逻辑
表	create table	保存了数据