【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 的第一行