MySQL基础_事务_视图

事务

#TCL 事务控制 
/*
TRANSACTION Control LANGUAGE 

事务 ：一个或一组sql语句组成一个执行单元 这个单元要么全部执行 要么全部不执行
InnoDB 支持事务 
MYISAM MEMORY 不支持事务

1.原子性:一条sql语句要么全部执行 全部执行
2.一致性：事务必须使数据库从一个一致性状态变换到到另一个一致性状态
3.隔离性：多个事务同时执行 互不干扰 [隔离级别 ]
4.持久性：事务一旦被提交就是永久性的改变数据库数据

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如 INSERT ,UPDATE,DELETE语句 相当于默认开启了事务

显式事务: 事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用

设置事务为禁用  只针对当前事务|当前的会话
set autocommit=1;

步骤1：开启事务
set autocommit=0;
start TRANSACTION;可选的
步骤2：编写事务中的sql语句(SELECT,INSERT,UPDATE,DELETE)
语句1；
语句2；
......

步骤3：结束事务
commit;提交事务
rollback;回滚事务


无法合并成一条语句(UPDATE本身是开启事务的) 开启事务
开启事务的语句
UPDATE 表 SET 张三丰的余额=500 where name='张三丰'
UPDATE 表 set 郭襄的余额=1500 WHERE name ='郭襄'
关闭事务的语句
*/
#查看事务状态
SHOW VARIABLES LIKE 'autocommit'
#查看存储引擎
SHOW ENGINES;

SELECT *FROM account;

#演示事务的使用步骤
#1.开启事务
set autocommit=0;
START TRANSACTION;
#2.编写sql语句
UPDATE account set balance = 1000 WHERE username='张无忌';
UPDATE account set balance = 1000 WHERE username='赵敏';

#以上只是保存到了内存 
#步骤三 结束事务
#COMMIT;
ROLLBACK 
 
# 同时运行多个事务 访问数据库相同的数据时 没有采取隔离机制 就会并发问题 
/**
1.脏读 ：对于两个事务T1,T2，T1读取了已经被T2[更新]但还没有提交的字段 
之后 若T2回滚 T1读取的内容就是临时且无效的
2.不可重复读：对于两个事务T1,T2,T1读取了一个字段 然后T2 [更新]了这个字段
T1再次读取同一个字段 值就不同了|同一个事务中读取的值是不一样的
3.幻读：对于两个事务T1,T2，T1从一个表中读取了一个字段 然后T2在该表中【插入】新的一行 T1在次去读就多出几行(针对插入)
设置隔离级别 避免以上情况
(READ UNCOMMITTED)
读未提交数据 :允许事务读取未被其他事务提交的变更 脏读 不可重复读 幻读都会发生 

READ COMMIT 
读已提交：只允许事务读取已经被其他事务提交的变更  不可重复，幻读会发生 避免脏读

REPEATABLE  READ
可重复读：确保事务可以多次从一个字段中读取相同的值 这个事务期间 禁止其他事物对这个字段进行更新
避免脏读 不可重复读 幻读依然存在

SERIALIZABLE
串行化 ：确保事务可以从一个表中读取相同的行 在事务期间 禁止其他事务对该表执行插入更新 删除 操作
避免所有并发问题 性能十分低下

Oracle支持的2种事务 ：READ COMMIT  | SERIALIZABLE 默认 READ COMMIT

Mysql 支持4种事务隔离级别 默认 REPEATABLE READ 

*/

设置隔离级别语句

/*
停止mysql
net  stop mysql8 

启动mysql
net start mysql8 

登录
mysql -uroot -p123456

查看隔离级别 8.0版本
select @@transaction_isolation;

# 设置隔离级别  只针对当前的连接
 set session transaction isolation level [隔离级别]; 

#设置数据库系统的全局的隔离级别
set GLOBAL TRANSACTION ISOLATION LEVEL [隔离级别]
 
*/

savepoint 保存点

# SAVEPOINT
SAVEPOINT 节点名 设置保存点 
/**
设置保存点 搭配rollback
*/

# 删掉id=1的数据 id=2不会删除
# 演示SAVEPOINT的使用 
set autocommit=0;
START TRANSACTION;
DELETE FROM student WHERE id =1;
SAVEPOINT a;#设置保存点
DELETE FROM student WHERE id =2;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM student;

视图的创建

#视图
/*
 临时性表 重复利用
含义：虚拟表 
数据来源于 我们查询时使用的表 使用视图会【动态生成的】只保存了sql逻辑 不保存查询结果 

应用场景：
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
*/
/**
# 创建视图
语法：
CREATE VIEW 视图名
as 
查询语句(一般是复杂语句);

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


*/

# 1.查询邮箱中包含a字符的员工名、部门名和工种信息

SELECT * FROM email_info WHERE last_name LIKE '%a'

CREATE VIEW email_info AS 
SELECT
	last_name,
	department_name,
	job_title 
FROM
	employees es
	INNER JOIN departments ds ON es.department_id = ds.department_id
	INNER JOIN jobs jb ON jb.job_id = es.job_id;

#2.查询各部门的平均工资级别
DROP VIEW avg_grades

CREATE VIEW avg_grades AS SELECT
AVG( salary ) avg,
department_id 
FROM
	employees 
GROUP BY
	department_id
# 查询视图
SELECT
	grade_level,
	department_id 
FROM
	avg_grades de_avg,
	job_grades jo 
WHERE
	de_avg.avg BETWEEN jo.lowest_sal 
	AND jo.highest_sal;

# 查询平均工资最低的部门信息
DROP VIEW min_info
SELECT * FROM min_info
CREATE VIEW min_info AS SELECT
d.* 
FROM
	departments d,
	( SELECT * FROM avg_grades ORDER BY avg asc LIMIT 1  ) min 
WHERE
	d.department_id = min.department_id

# 4.查询平均工资最低的部门名和工资
DROP VIEW dept_as

CREATE VIEW dept_as AS 
 SELECT * FROM avg_grades ORDER BY avg asc LIMIT 1   

SELECT
	department_name,
	avg
FROM
	dept_as de,
	departments ds 
WHERE
	de.department_id = ds.department_id

#查询出部门Pur 的人员信息 部门名称
#普通写法
SELECT
	es.*,
	department_name 
FROM
	employees es
	INNER JOIN departments ds ON es.department_id = ds.department_id 
WHERE
	department_name = 'Pur'

#视图
CREATE VIEW v1
AS
SELECT
	es.*,
	department_name 
FROM
	employees es
	INNER JOIN departments ds ON es.department_id = ds.department_id 

#查询结果
SELECT * FROM v1 WHERE 	department_name = 'Pur'

视图修改 删除 查看

视图添加只读权限
# 方式一
/**
视图存在则替换|不存在则创建
CREATE OR REPLACE VIEW 视图名
as 
查询语句;
*/

SELECT * FROM  v1;
# 存在替换为新的查询条件
CREATE 
	OR REPLACE VIEW v1 AS SELECT
	avg( salary ),
	department_id 
FROM
	employees 
GROUP BY
	department_id;

#方式二 
/**
ALTER VIEW 视图名
as 
查询语句;
*/

SELECT * FROM min_info;

ALTER VIEW min_info AS SELECT
* 
FROM
	employees;


#三 删除视图 
/*
语法：DROP VIEW 视图名,视图二...
*/

DROP VIEW avg_grades,dept_as,email_info,min_info;
 
#四 查看视图|查看结构
DESC v1;
SHOW CREATE VIEW v1;

视图更新|表区别

/*包含下列语句视图不能更新
分组函数, DISTINCT ,GROUP BY ,HAVING ,UNION ,UNION ALL
常量视图
SELECT中包含子查询
JOIN 
FROM 一个不能更新的视图
WHERE 子句的子查询引用了 FROM 子句中的表

#表和视图区别
视图  CREATE VIEW 			保存了sql逻辑				增删改查 ,一般不支持增删改

表 		CREATE TABLE 			保存了数据					增删改查
*/