小白菜学Mysql笔记（四）--TCL--事务和视图

TCL
Transaction Control Language 事务控制语言

一.事务:
	一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
	每个sql语句是相互依赖的,某条sql语句执行失败,整个单元将会回滚.

	show engines;查看mysql支持的存储引擎

	innodb支持事务,myisam,memory不支持事务.

	1.※事务的acid属性(特点):
		1.原子性atomicity:不可分割的工作单位
		2.一致性consistency:从一个一致性状态变换到另外一个一致性状态
		3.隔离性lsolation:不能被其他事务干扰
		4.持久性durability:对数据的改变是永久性的
	
	
	2.事务的创建
		1.隐式事务:事务没有明显的开启和结束的标记
		比如insert,update,delete语句

		delete from 表 where id =1;

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

		set autocommit=0;关闭,默认为关闭
		
		步骤1:开启事务
		set autocommit=0;
		start transaction;可选的
		
		步骤2:编写事务中的sql语句(insert,update,select,delete)
		语句1;
		语句2;
		.....
		
		步骤3:结束事务
		commit;提交事务
		rollback;滚回事务
		savepoint 节点名;设置节点/保存点
		
		
		CREATE TABLE IF NOT EXISTS account(
			id INT PRIMARY KEY AUTO_INCREMENT,
			username VARCHAR(20),
			balance DOUBLE
		);
		TRUNCATE TABLE account;
		SELECT *FROM account;
		INSERT INTO account(username,balance)
		VALUES('张无忌',1000),('赵敏',1000);

		SET autocommit=0;
		START TRANSACTION;
		UPDATE account SET balance =500 WHERE username ='张无忌';
		UPDATE account SET balance=1500 WHERE username = '赵敏';
		COMMIT;
		
		#3.演示savepoint
		SET autocommit=0;
		START TRANSACTION;
		DELETE FROM account WHERE id=25;
		SAVEPOINT a;#设置保存点
		DELETE FROM account WHERE id=28;
		ROLLBACK TO a;
		
		

	3.事务并发管理
	多个事务同时运行,必须采取必要的隔离机制,否则导致并发问题
		1.脏读,T1读取了T2还么被提交的字段,如事务2回滚,事务1的内容是无效
		2.不可重复读:对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 
			之后, T1再次读取同一个字段, 值就不同了. 
		3.幻读:对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 
			之后, 如果 T1 再次读取同一个表, 就会多出几行

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

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

	mysql支持4中事务隔离级别
									脏读		不可重复读		幻读
	read uncommitted:读未提交		×			×				×
	read COMMITTED:读已提交			√			×				×
	repeatable READ:可重复读		√			√				×		mysql默认
	SERIALIZABLE:串行化				√			√				√


	4.DELETE 和truncate在事务使用的区别
		#演示delete

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

		#演示truncate
		#实际的删除,不支持回滚
		SET autocommit=0;
		START TRANSACTION;
		TRUNCATE TABLE account;
		ROLLBACK;	


二.视图
	含义:虚拟表,和普通标一样使用
	通过表动态生成的数据
	只保存sql逻辑,不保存查询结果
	简化sql操作,保护数据,提高安全性

	#查询姓张的学生名和专业名
	CREATE VIEW v1
	AS
	SELECT stuname,majorname
	FROM stuinfo s
	INNER JOIN major m ON s.`majorid`=m.`id`;


	SELECT * FROM WHERE stuname LIKE '张%';
		
		
	视图	CREATE VIEW		占用较少物理空间,保存了sql逻辑语句
	表		CREATE TABLE	较多,保存了数据


	1.创建视图
	语法:
	CREATE VIEW 视图名
	AS
	查询语句;

		#查询姓名中包含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%';

		#查询各部门平均工资级别
		#创建视图查看每个部门的平均工资
		CREATE VIEW myv2
		AS 
		SELECT AVG(salary) ag,department_id
		FROM employees e
		GROUP BY e.department_id;

		SELECT myv2.`avg(salary)` ,g.grade_level
		FROM myv2
		JOIN job_grades g
		ON myv2.`avg(salary)` BETWEEN g.`lowest_sal`AND g.`highest_sal`;


		#查询平均部门最低的部门信息

		SELECT * FROM myv2 ORDER BY myv2.`avg(salary)` LIMIT 1;

		#查询平均最低的部门名和工资
		CREATE VIEW myv3
		AS
		SELECT * FROM myv2 
		ORDER BY myv2.`avg(salary)` LIMIT 1;

		SELECT d.*,myv3.`avg(salary)`
		FROM myv3
		JOIN departments d
		ON myv3.`department_id`=d.`department_id`;
		
	2.视图修改
		方式一:
		CREATE OR REPLACE VIEW
		AS 
		查询语句;
		ALTER VIEW myv3
		AS
		SELECT * FROM employees;
	
	3.删除视图
		语法:
		DROP view 视图名,视图名...;
		
		DROP VIEW myv1,myv2....;
	
	4.查看视图
	
		DESC myv3;
		
		show CREATE VIEW myv3;#显示视图创建过程
	
#创建视图emp_v1,查询电话号码为011开头的员工姓名和工资,邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;


#创建爱你视图emp_v2,查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

SELECT d.*,emp_v2.`max(salary)`
FROM emp_v2
JOIN departments d
ON emp_v2.department_id = d.`department_id`;

	5.视图的更新
	具备以下特点的视图不允许更新:
	• 包含以下关键字的sql语句：分组函数、distinct、group  by 、having、union或者union all 
	• 常量视图 
	• Select中包含子查询 
	• join 
	• from一个不能更新的视图 
	• where子句的子查询引用了from子句中的表
	
		DROP VIEW myv1;
		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 myv2
		AS
		SELECT last_name,email
		FROM employees;

		插入数据
		#某些数据不能插入
		INSERT INTO myv1 
		VALUES ('张飞','zf@qq.com',10000);
		#成功插入后,原始表也会生成更新
		INSERT INTO myv2 
		VALUES ('张飞','zf@qq.com');

		修改数据
		UPDATE myv2 
		SET last_name='张无忌' 
		WHERE last_name ='张飞';

		删除数据
		DELETE FROM myv2
		WHERE last_name ='张无忌';