本文共 4287 字，大约阅读时间需要 14 分钟。

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 sINNER JOIN major m ON s.majorID=m.idWHERE s.stuName LIKE '张%';CREATE VIEW v1 AS SELECT stuname, majorname FROM stuinfo sINNER JOIN major m ON s.majorID=m.idWHERE s.stuName LIKE '张%';SELECT * FROM v1 WHERE stuname LIKE '张%';

2.2 视图的创建

语法：create view 视图名

as 查询语句;

#1.查询邮箱中包含a字符的员工名、部门名和工种信息#①创建CREATE VIEW myv1ASSELECT last_name,department_name,job_titleFROM employees eJOIN departments d ON e.department_id = d.department_idJOIN jobs j ON j.job_id = e.job_id;#②使用SELECT * FROM myv1 WHERE last_name LIKE '%a%';#2.查询各部门的平均工资级别#①创建视图查看每个部门的平均工资CREATE VIEW myv2ASSELECT AVG(salary) ag, department_idFROM employees GROUP BY department_id;#②使用SELECT myv2.ag, g.grade_levelFROM myv2 JOIN job_grades gON 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.agFROM myv3 m JOIN departments dON 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 employeesGROUP BY job_id;

方式二：

alter view 视图名 as 查询语句;

ALTER VIEW myv3ASSELECT * 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 allCREATE OR REPLACE VIEW empv2AS SELECT MAX(salary), department_id FROM employeesGROUP BY department_id;SELECT * FROM empv2;#更新UPDATE empv2 SET m=9000 WHERE department_id=10;#②常量视图CREATE OR REPLACE VIEW empv2ASSELECT 'jhon' NAME;SELECT * FROM empv2;#更新UPDATE empv2 SET NAME='lucy';#③SELECT中包含子查询CREATE OR REPLACE VIEW empv2AS SELECT (SELECT MAX(salary) FROM employees) 最高工资;#更新SELECT * FROM empv2;UPDATE empv2 SET 最高工资=100000;#④joinCREATE OR REPLACE VIEW empv2AS SELECT last_name, department_nameFROM employees e JOIN departments dON 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 myv4ASSELECT last_name,email,salary FROM employeesWHERE 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	保存了数据

转载地址：http://mkgwb.baihongyu.com/