【mysql 事务&视图&变量 08】
TCL 事务控制语言（transaction Control language）
事务：事务由单独单元的一个或多个sql语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果
单元中某条sql语句一旦执行失败或产生错误，整个单元将会回滚，所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有
sql语句均执行，则事务被顺口执行
存储引擎
概念：在mysql中的数据用各种不同的技术存储在文件或内存中
通过show engines; 来查看MySQL支持的存储引擎
事务的ACID属性
1、原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2、一致性：事务必须使数据库从一个一致性的状态变换到另外一个一致性状态
3、隔离性：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是格力的，
并发执行的各个事务之间不能互相干扰
4、持久性：指一个事务一旦被提交，它对数据库中的数据的改变就是永久性，接下来的其他操作和数据库故障不应该对其有人格影响
#事务的创建
分类：
1、隐式事务：事务没有明显的开启和结束的标记（自动开始自动结束）
比如：insert update delete语句
2、显示事务：事务具有明显的开始和结束的标记
前提：必须先设置自动提交功能为禁用。(只针对当前回话)
如何设置：
步骤1：
set autocommit=0; start transacation;可选的
步骤2:编写事务中的sql语句（select insert update delete）
语句1；
语句2；
...
步骤3：结束事务
commit;提交事务 rollback;回滚事务 savepoint 节点名。设置保存点
#创建表
DROP TABLE IF EXISTS accout; CREATE TABLE accout( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20), balance DOUBLE ); INSERT INTO accout(username,balance) VALUES ('张无忌',1000),('赵敏',1000); DESC accout; SELECT * FROM accout;
#演示事务的使用步骤
#1、开启事务 SET autocommit=0; #2、编写一组事务的语句 -->转账 UPDATE accout SET balance=1000 WHERE username='张无忌'; UPDATE accout SET balance=1500 WHERE username='赵敏'; #3、结束事务 COMMIT; ROLLBACK; #事务只有提交后才会落在磁盘，没有提交的话是存储在内存里面，如果不提交直接回滚是无效的 SELECT * FROM accout;
# 数据库的隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题
1、脏读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
2、不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了
3、幻读;对于两个事务T1、T2，T1从一个表中读物了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行
数据库提供4中事务隔离级别
隔离级别 描述
READ UNCOMITTED
(读未提交数据) 允许事务读取未被其他事物提交的变更，脏读，不可重复读和幻读的问题就会出现
READ COMMITED
(读已提交数据) 只允许事务读取已经被其他事物提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现
REPEATABLE READ
(可重复读) 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事物对这个字段进行更新，可以避免脏读和不可
重复读，但幻读的问题仍然存在
SERIALIZABLE
(串行化) 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事物对该表执行插入，更新和删除操作，所有冰尜问题
都可以避免，但性能十分低下。
Mysql支持4中事务隔离级别，默认的事务隔离级别
为：REPEATABLE READ
查看数据库隔离级别的命令
select @@tx_isolation;
事务的隔离性级别演示
#1、T1，T2查看隔离级别
mysql> SELECT @@tx_isolation; 显示的级别为：默认级别REPEATABLE READ
#2、设置T1，T2的级别为READ COMMITTED
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#3、开启T1的事务，然后更新T1
SET autocommit=0; mysql> UPDATE accout SET username='张飞' WHERE id =1;
#4、开启T2的事务，然后查看accout表id=1的username是否改成了张飞
mysql> SELECT * FROM accout; +----+----------+---------+ | id | username | balance | +----+----------+---------+ | 1 | 张无忌 | 1000 | | 2 | 赵敏 | 1500 | +----+----------+---------+ 2 ROWS IN SET (0.00 sec) 发现没有被修改，说明级别改成了READ COMMITTED，避免了脏读
#5、提交T1的事务
mysql> COMMIT; QUERY OK, 0 ROWS affected (0.00 sec) #6、T2查看accout表是否被修改为张飞 mysql> SELECT * FROM accout; +----+----------+---------+ | id | username | balance | +----+----------+---------+ | 1 | 张飞 | 1000 | | 2 | 赵敏 | 1500 | +----+----------+---------+ 2 ROWS IN SET (0.02 sec)
#演示savepoint 的使用
SET autocommit=0; START TRANSACTION; DELETE FROM accout WHERE id=1; SAVEPOINT a;#设置保存点,只能搭配rollback 不能搭配commit DELETE FROM accout WHERE id =2; ROLLBACK TO a;# 回滚到保存点 SELECT * FROM accout; id username balance 2 赵敏 1500 -- 只删除了第一行，第二行没有被删除
#二、视图（含义：虚拟表和普通表一样使用）
一种的虚拟的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果
1、应用场景
|-- 多个地方用到同样的查询结果
|-- 该查询结果使用的sql语句较复杂
#案例：查询姓张的学生名和专业名
之前的语句
SELECT stuName,`majorName` FROM major m JOIN `stuinfo` s ON m.id = s.mgjorId WHERE stuName LIKE '张%';
使用视图的方法
CREATE VIEW v1 AS SELECT stuName,`majorName` FROM major m JOIN `stuinfo` s ON m.id = s.mgjorId; SELECT * FROM v1 WHERE stuName LIKE '张%';
一、视图的创建
语法：
create view 视图名
as
查询语句; （一般是比较复杂的语句）
#1、查询姓名中包含a字符的员工名、部门名和工种信息
-- 1) 创建视图 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 e.job_id = j.`job_id`; -- 2) 使用视图 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 my.`ag`,g.`grade_level` FROM myv2 my JOIN job_grades g ON my.`ag` BETWEEN g.lowest_sal AND g.`highest_sal`;
#3、查询平均工资中最低的部门信息
SELECT * FROM myv2 m JOIN departments d ON m.`department_id`=d.`department_id` ORDER BY m.ag LIMIT 1;
# 4、查询平均工资最低的部门名和工资
SELECT m.`ag`,d.department_name FROM myv2 m JOIN departments d ON m.`department_id`=d.`department_id` ORDER BY m.ag LIMIT 1;
2、视图的好处
1）重用sql语句
2）简化复杂的sql操作，不必知道它的查询细节
3）保护数据，提高安全性
二、视图的修改
语法：
方式一：
create or replace view 视图名 as 查询语句；
举例：改变myv2
CREATE OR REPLACE VIEW myv2 AS SELECT AVG(Salary),job_id FROM employees GROUP BY job_id;
方式二：
alter view 视图名 as 查询语句;
举例：myv2
ALTER VIEW myv2 AS SELECT * FROM employees;
三、视图的删除
drop view 视图名，视图名...；
举例：
DROP VIEW myv1,myv2;
四、查看视图
desc 视图名
案例：
#创建视图emp_v1,要求查询电话号码以’011‘开头的员工姓名和工资，邮箱
CREATE VIEW emp_v1 AS SELECT last_name,Salary,email FROM employees WHERE phone_number LIKE '011%'; SELECT * FROM emp_v1;
#创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE VIEW emp_v2 AS SELECT MAX(Salary) ma ,department_id FROM employees GROUP BY department_id HAVING ma>12000; SELECT * FROM departments d JOIN emp_v2 e ON d.`department_id`=e.`department_id`;
五、视图的更新（跟新视图内的数据）
1、视图的可更新性和视图中查询的定义有关系，一下类型的视图是不能更新的
- 1、包含group by ,distinct,分组函数，having，union，union all
- 2、常量视图
- 3、select中包含子查询
- 4、join
- 5、from一个不能更新的视图
- 6、where子句的字查询引用了from子句中的表
2、视图和表的对比
创建语法的关键字 是否实际占用物理空间
视图：create view 没有
表： create table 占用
七、变量
分类：系统变量（全局变量，会话变量），自定义变量（用户变量，局部变量）
1、系统变量（变量由系统提供，不是用户定义，属于服务器层面）
全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话有效，但不能跨重启
2、会话变量
作用域：仅仅针对当前会话（连接）有效
使用语法：
1）查看所有的系统变量/会话变量
show gloabl/【session】 variables
2）查看满足条件的部分系统变量
show global/【session】 variables like '%char%';
3）查看指定的某个系统变量的值
select @@global/[session].系统变量名
4）为某系统变量赋值
方式一：set global/【session】系统变量名= 值； 方式二：set @@global/[session].系统变量名=值；
注意：如果是全局级别，需要加上global，如果是会话级别，需要写session，可以不写，默认情况下是session
3、自定义变量
使用步骤：声明-->赋值-->使用(查看、比较、运算)
1）用户变量
作用域：当前会话有效，同于会话变量的作用域
应用在任何地方，也就是放在begin end里面。或begin end外面
赋值的操作符：=或:=
# 声明并初始化
SET @用户变量名=值;或 SET @用户变量名:=值;或 SELECT @用户变量名:=值;
#赋值
方式一：通过SET或SELECT SET @用户变量名=值;或 SET @用户变量名:=值;或 SELECT @用户变量名:=值; 方式二：通过SELECT INTO SELECT 字段 INTO 变量名 FROM 表；
#使用（查看用户变量的值）
SELECT @用户变量名;
#案例
SET @name='join'; SET @name=100; SET @count =1; SELECT COUNT(*) INTO @count FROM employees; -- 将员工的总个数复制给count SELECT @count;
结果：
@count =107
2）局部变量
作用域：仅仅在定义它的begin end 中有效
应用在begin end中的第一句话
使用步骤：
#声明
DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值;
#赋值
方式一：通过SET或SELECT SET 用户变量名=值;或 SET 用户变量名:=值;或 SELECT @用户变量名:=值; 方式二：通过SELECT INTO SELECT 字段 INTO 变量名 FROM 表；
#使用
SELECT 局部变量名;
#案例:声明两个变量并赋初始值，求和并打印
#1、用户变量
SET @m =1; SET @n = 2; SET @sum = @m+@n; SELECT @sum;
#2、局部变量
DECLARE m INT DEFAULT 1; DECLARE n DEFAULT 2; DECLARE SUM INT; SET SUM = m+n; SELECT SUM;
执行结果会报错,原因在于没有放到BEGIN END 的第一行
