MySQL——事务和视图
2023.9.17
本章开始介绍TCL语言（Transaction Control Language 事务控制语言）。
事务
事务的概念：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
事务的特性：（ACID）
- 原子性：一个事务不可再分割，要么都执行要么都不执行。
- 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态。
- 隔离性：一个事务的执行不受其他事务的干扰。
- 持久性：一个事务一旦提交，则会永久的改变数据库的数据。
事务的创建及使用步骤：
隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句。
显式事务：事务具有明显的开启和结束的标记。前提：必须先设置自动提交功能为禁用。
步骤1：开启事务
set autocommit=0;
start transaction;（可选）
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
savepoint 的使用案例：
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点数据库的隔离级别：
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
- 脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段。之后,若T2回滚,T1读取的内容就是临时且无效的。
- 不可重复读:对于两个事务T1, T2,T1读取了一个字段,然后T2更新了该字段。之后, T1再次读取同一个字段,值就不同了。
- 幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。之后,如果T1再次读取同一个表,就会多出几行。
各个隔离级别：
脏读 不可重复读 幻读
read uncommitted： √ √ √
read committed： × √ √
repeatable read： × × √
serializable： × × ×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认 第二个隔离级别 read committed
查看隔离级别：
select @@tx_isolation;
设置隔离级别：
set session|global transaction isolation level 隔离级别;
视图：
含义：可以理解为一张虚拟表，和普通表一样使用，用于保存一些临时表数据，方便再次使用。
mysql5.1版本出现的新特性，是通过表动态生成的数据。
视图与表的对比：
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑（数据没有保存） 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
视图的好处：
①重用sql语句。
②简化复杂的sql操作，不必知道它的查询细节。
③保护数据，提高安全性。
相关笔记：
#一、创建视图
/*
语法：
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.查询各部门的平均工资级别
#创建视图查看每个部门的平均工资
CREATE VIEW v2
AS
SELECT AVG(`salary`) ag ,`department_id`
FROM `employees`
GROUP BY `department_id`;
#使用视图
SELECT v2.ag,g.`grade_level`
FROM v2
INNER JOIN `job_grades` g
ON v2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;#3.查询平均工资最低的部门信息
#利用刚刚生成的视图v2
SELECT * FROM v2 ORDER BY ag ASC LIMIT 1; #4.查询平均工资最低的部门名和工资
#视图可以套娃
CREATE VIEW v3
AS
SELECT * FROM v2 ORDER BY ag ASC LIMIT 1; SELECT d.`department_name`,v.ag
FROM v3 v
INNER JOIN `departments` d
ON v.`department_id`=d.`department_id`;#二、视图的修改#方式一：
/*
create or replace view 视图名
as
查询语句;
*/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;#三、删除视图/*
语法：drop view 视图名,视图名,...;
*/DROP VIEW v1,v2,v3;#四、查看视图
DESC myv3;SHOW CREATE VIEW myv3;#五、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;#1.插入INSERT INTO myv1 VALUES('张飞','zf@qq.com');#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';#具备以下特点的视图不允许更新#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union allCREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;SELECT * FROM myv1;#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;#②常量视图
CREATE OR REPLACE VIEW myv2
ASSELECT 'john' NAME;SELECT * FROM myv2;#更新
UPDATE myv2 SET NAME='lucy';#③Select中包含子查询CREATE OR REPLACE VIEW myv3
ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;#④join
CREATE OR REPLACE VIEW myv4
ASSELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;#更新SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;#⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6
ASSELECT last_name,email,salary
FROM employees
WHERE employee_id IN(SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL
);#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';