MYSQL（四）

数据库事务

SHOW ENGINES;

#acid
#原子性， 一致性， 隔离性， 持久性

#事务的创建
#隐式 INSERT UPDATE DELETE

SET autocommit= 0;

START TRANSACTION;

DROP TABLE IF EXISTS account;

CREATE TABLE account(
				id INT PRIMARY KEY AUTO_INCREMENT,
				username VARCHAR(20),
				balance DOUBLE
);
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 = '赵敏';

#回滚
ROLLBACK;
#提交
COMMIT;
	
#脏读：  	  对于两个事务 T1 T2， T1读取了T2未提交的字段，若回滚，则无效，就是脏数据
#不可重复读：	对于两个事务 T1 T2， T1读取了一个字段，然后T2【更新】了该字段，T1再次读取数据就不同
#幻读：	  对于两个事务 T1 T2， T1读取了一个字段，T2在该表中【插入】几行数据，T1再次读取数据就多几行

#设置隔离级别 (默认：Repeatable read)
set transaction isolation level read commimtted

#设置全局隔离级别(需重启)
set global transaction isolation level read commimtted

#Read uncommitted 		[读未提交]：最低级别，任何情况都无法保证。
#Read committed 		[读已提交]：可避免脏读的发生。
#Repeatable read 		[可重复读]：可避免脏读、不可重复读的发生。
#Serializable 			[串行化]：可避免脏读、不可重复读、幻读的发生。（会加大量锁，访问效率低）

#设置保存点
SET AUTOCOMMIT = 0;
START TRANSACT
ION;
DELETE FROM account WHERE id = 17;
SAVEPOINT a; #(保存之后不再回滚)
DELETE FROM account WHERE id = 18;
ROLLBACK TO a;
#17删了， 18没删

二、视图

#创建
CREATE VIEW v1 
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 v1 WHERE last_name LIKE '%a%';
#		CREATE OR REPLACE VIEW v2;
CREATE VIEW v2 
AS 
SELECT AVG(salary) ag, department_id 
FROM employees 
GROUP BY department_id;

SELECT * FROM v2;
SELECT * FROM v2 ORDER BY ag LIMIT 1;

#修改
ALTER VIEW v2 
AS 
SELECT * FROM employees;

#删除
DROP VIEW v2;

#查看
DESC v2;

SHOW CREATE VIEW v2;