MySQL学习第三天事务和视图

事务的概念和特性

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

MySQL 中的存储引擎

在mysql中的数据用各种不同的技术存储在文件（或内存）中。

通过show engines来查看mysql支持的存储引擎。

在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务

事务的ACID(acid)属性

1. 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3. 隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事务的创建

隐式事务：事务没有明显的开启和结束的标记

比如insert、update、delete语句

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

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

显式事务开启步骤

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点

事务的隔离级别

一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

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

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

脏读:

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

1.演示脏读

开启两个连接客户端
将事务隔离级别修改为read uncommitted 默认为 REPEATABLE-READ

 set session transaction isolation level read uncommitted;

分别在两个客户端开启两个不同的事务T1，T2


原始数据

此时另一个客户端读取到的是刚刚修改的数据发生了脏读。

解决脏读的办法：将事务隔离级别改为READ COMMITED

不可重复读:

对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.

若事务T1回滚 rollback了
此时客户端2便读到了原始的数据与之前数据不一致，该现象称为不可重复读。

解决不可重复读的办法：将事务隔离级别改为REPEATABLE READ

幻读:

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

将两个客户端的隔离级别改为 repeatable read来演示幻读

set session transaction isolation level repeatable read;

事务T1在表中插入了一行

而此时事务T2之前查询到的是

然后在查询时会多出一行。T2感觉像是出现了幻觉一样刚刚读到的是两行突然变成了三行。此现象称为幻读。

解决幻读的办法：将事务隔离级别改为SERIALIZABLE

数据库提供的 4 种事务隔离级别:

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。

Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别
为: REPEATABLE READ

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量@@transaction_isolation; , 表示当前的事务隔离级别.

查看当前的隔离级别:

 SELECT @@transaction_isolation;
 或
 show VARIABLES LIKE '%ISOLATION%'

设置当前 mySQL 连接的隔离级别:

set transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed

事务的案例演示

用如下表来演示事务

1.演示事务的使用步骤

曹操向刘备转账500元

SET autocommit=0;
start TRANSACTION;
UPDATE t_account SET a_balance=1000 WHERE a_name='曹操';
UPDATE t_account SET a_balance=1000 WHERE a_name='刘备';
COMMIT;

2.演示事务对于delete和truncate的处理的区别

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

SET autocommit=0;
START TRANSACTION;
TRUNCATE FROM t_account;
ROLLBACK;

使用truncate会报错 不支持事务回滚

• You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near ‘FROM t_account’ at line 1

3.演示savepoint 的使用

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

刘备的信息被删除了。

视图的概念和特性

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

应用场景：

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

视图的好处

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

视图的增删改查

一、创建视图

语法：

create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

#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;

二、修改视图

修改视图的语法：
第一种：

alter view view_name
As select_statement
[with|cascaded|local|check option]

ALTER VIEW myv3
AS
SELECT * FROM employees;

第二种：

create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

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

三.删除视图

删除视图语法：

drop view [if exists] view_name,view_name …[restrict|cascade]

DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图

查看视图语法：

DESC myv3;

SHOW CREATE VIEW myv3;

五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * 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 all

• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

视图的例题

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

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

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

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;

表和视图的对比

1、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改。

2、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

3、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

联系：视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。