事务

TCL：Transaction Control Language 事务控制语言

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

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

• 概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
• 通过show engines；来查看mysql支持的存储引擎。
• 在mysql中用的最多的存储引擎有：innodb，myisam，memory等。其中innodb支持事务，而myisam、memory等不支持.

转账案例

张三丰转给郭襄500元

# 张三丰  1000
# 郭襄    1000

update 表 set 张三丰的余额=张三丰的余额-500 where name='张三丰'

#　中间发生意外，张三丰的余额少了500，而郭襄的余额并没有增加

update 表 set 郭襄的余额=郭襄的余额+500 where name='郭襄'

事物的特性( ACID )

1. 原子性（Atomicity）

   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）

   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）

   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事务的创建

隐式事务

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

# 执行完表中id列为1的那一行数据就直接被删除了
delete from 表 where id =1;

显式事务

事务具有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用 set autocommit=0;

# 查看自动提交是否开启
SHOW VARIABLES LIKE 'autocommit';

# 查看数据库引擎
SHOW ENGINES;

开启事务步骤

1. 开启事务

   # 关闭自动提交
   set autocommit=0;
   
   #　开启事物（可选）
   start transaction;
   

2. 编写事务中的sql语句（select、insert、update、delete）

3. 可选：savepoint 节点名；设置回滚点

4. 结束事务

   commit;提交事务
   # 或者
   rollback;回滚事务
   # 或者
   rollback to 回滚点名;回滚到指定的地方
   

并发事务

1. 事务的并发问题是如何发生的？

   多个事务 同时 操作 同一个数据库的相同数据时

2. 并发问题都有哪些？

   脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的

   不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了(在一个事物中不管读多少次，读取的数据应该都一样)

   幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

3. 如何解决并发问题

   通过设置隔离级别来解决并发问题

事务隔离级别

√：已解决 ×：未解决

隔离级别	脏读	不可重复读	幻读
read uncommitted:读未提交	×	×	×
read committed：读已提交	√	×	×
repeatable read：可重复读	√	√	×
serializable：串行化	√	√	√

mysql中默认 第三个隔离级别 repeatable read

oracle中默认第二个隔离级别 read committed

查看隔离级别

select @@tx_isolation;

设置隔离级别

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

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

案例

1. 事务的使用步骤

   # 开启事务
   SET autocommit=0;
   START TRANSACTION;
   # 编写一组事务的语句
   UPDATE account SET balance = 1000 WHERE username='张无忌';
   UPDATE account SET balance = 1000 WHERE username='赵敏';
   
   # 结束事务
   ROLLBACK;
   # COMMIT;
   
   SELECT * FROM account;
   

2. 事务对于DELETE和TRUNCATE处理的区别

   SET autocommit=0;
   START TRANSACTION;
   
   DELETE FROM account;
   ROLLBACK;
   

   TRUNCATE无法回滚

   SET autocommit=0;
   START TRANSACTION;
   
   TRUNCATE TABLE account;
   ROLLBACK;
   

3. savepoint的使用

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

视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中 使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

应用场景

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句较复杂

优点

1. 简化sql语句
2. 提高了sql的重用性
3. 保护基表的数据，提高了安全性

创建视图

create view 视图名
as
查询语句;

1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

   CREATE VIEW emp_v1 AS SELECT
       last_name,
       salary,
       email 
   FROM
       employees 
   WHERE
       phone_number LIKE '011%';
   

2. 创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

   # 创建视图，查询每个部门的最高工资,筛选出高于12000的
   DROP VIEW emp_v2;
   CREATE VIEW emp_v2 AS SELECT
   department_id,
   MAX( salary ) max_salary 
   FROM
       employees 
   GROUP BY
       department_id 
   HAVING
       max_salary > 12000;
   
   # 根据创建的视图连接departments表查询部门信息
   SELECT
       d.*,
       ev2.max_salary 
   FROM
       departments d
       JOIN emp_v2 ev2 
       ON d.department_id = ev2.department_id;
   

3. 查询姓名中包含a字符的员工名、部门名和工种信息

   # 查询员工名、部门名和工种信息
   CREATE VIEW emp_v3 AS SELECT
   e.last_name,
   d.department_name,
   j.job_title 
   FROM
       employees e
       LEFT JOIN departments d ON e.department_id = d.department_id
       LEFT JOIN jobs j ON e.job_id = j.job_id;
   
   # 筛选出姓名中包含a字符的员工
   SELECT * FROM emp_v3 WHERE last_name LIKE '%a%';
   

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

   # 查询各部门的平均工资
   CREATE VIEW emp_v4 AS SELECT
   AVG( salary ) avg_salary,
   department_id 
   FROM
       employees 
   GROUP BY
       department_id;
   
   # 查询各部门的平均工资级别
   SELECT
       department_id,
       j.grade_level 
   FROM
       emp_v4 ev4
       LEFT JOIN job_grades j 
       ON ev4.avg_salary BETWEEN j.lowest_sal 
       AND j.highest_sal
   

5. 查询平均工资最低的部门信息

   SELECT
       d.*,
       ev4.avg_salary 
   FROM
       departments d
       JOIN ( SELECT * FROM emp_v4 ORDER BY avg_salary LIMIT 1 ) ev4 
       ON d.department_id = ev4.department_id
   

修改视图

方式一：

create or replace view  视图名
as
查询语句;

方式二：

alter view 视图名
as 
查询语句;

删除视图

drop view 视图名,视图名,...

DROP VIEW emp_v1,emp_v2,emp_v3,emp_v4;

查看视图

DESC 视图名;

SHOW CREATE VIEW 视图名;

CREATE VIEW emp_v1 AS SELECT
* 
FROM
    employees;

DESC emp_v1;

SHOW CREATE VIEW emp_v1;

视图的更新

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。

• 包含以下关键字的sql语句：分组函数、distinct、group by
• having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

案例

1. 张飞能否成功插入？

   不能，原表中没有annual salary那一列

   # 创建视图，查询员工的姓名，邮箱和年薪
   
   CREATE 
       OR REPLACE VIEW myv1 AS SELECT
       last_name,
       email,
       salary * 12 *(1+IFNULL ( commission_pct, 0 )) "annual salary" 
   FROM
       employees;
   
   # 插入一条数据
   INSERT INTO myv1 VALUES('张飞','zf@qq.com',94862.00);
   

2. 张飞能否成功插入？

   能，并且原表中也存在

   # 创建视图，查询员工的姓名和邮箱
   
   CREATE 
       OR REPLACE VIEW myv1 AS SELECT
       last_name,
       email
   FROM
       employees;
   
   # 插入一条数据
   INSERT INTO myv1 VALUES('张飞','zf@qq.com');
   

3. 能否将张飞修改为张无忌？

   能

   UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
   

4. 能否干掉张无忌？

   能

   DELETE FROM myv1 WHERE last_name = '张无忌';
   

5. 能否将10号部门的最高薪水改为9000？

   不能 ，包含group by和分组函数

   #　创建视图，查询每个部门的最高工资
   CREATE 
       OR REPLACE VIEW myv1 AS SELECT
       MAX( salary ) m,
       department_id 
   FROM
       employees 
   GROUP BY
       department_id;
   
   ＃ 将10号部门的最高薪水改为9000
   UPDATE myv1 SET m=9000 WHERE department_id=10;
   

6. 能否更改？

   不能，常量视图

   CREATE OR REPLACE VIEW myv2
   AS
   SELECT 'john' NAME;
   
   #更新
   UPDATE myv2 SET NAME='lucy';
   

7. 能够将最高工资列修改为100000？

   不能，select中包含子查询

   CREATE OR REPLACE VIEW myv3 AS 
   SELECT department_id,( SELECT MAX( salary ) FROM employees ) 最高工资 
   FROM
       departments;
   
   # 修改
   
   UPDATE myv3 SET 最高工资=100000;
   

8. 修改和插入能成功吗？

   可以更新，但是不能插入

   # 创建视图，查询员工名与部门名
   CREATE 
       OR REPLACE VIEW myv4 AS SELECT
       last_name,
       department_name 
   FROM
       employees e
       JOIN departments d ON e.department_id = d.department_id;
   
   # 修改
   UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
   # 插入
   INSERT INTO myv4 VALUES('陈真','Acc');
   

9. 能修改吗？

   不能，from一个不能更新的视图，myv3中select使用了子查询

   CREATE 
       OR REPLACE VIEW myv5 AS SELECT
       * 
   FROM
       myv3;
   
   # 修改
   UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
   

10. 能修改吗？

    不能，where子句的子查询引用了from子句中的表

    # 查询所有的领导信息
    CREATE OR REPLACE VIEW myv6
    AS
    
    SELECT last_name,email,salary
    FROM employees
    WHERE employee_id IN(
        SELECT  manager_id
        FROM employees
        WHERE manager_id IS NOT NULL
    );
    
    # 修改
    UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
    

    查询领导Id（将所有员工的上级Id查出来，这些Id就是领导Id）：

    SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL
    

视图和表的对比

	关键字	是否占用物理空间	使用
视图	create view	占用较小，只保存sql逻辑	一般用于查询
表	create table	保存实际的数据	增删改查