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;