#TCL Transaction Control Language 事务控制语言
/*
事务：
一个或者一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
事务的acid属性
1.原子性(atomicity)：事务是不可分割,要么都执行要么都不执行
2.一致性(consistency):事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性(isolation)：一个事物的执行不能被其他事务干扰
4.持久性(Durability):一个事物一旦提交，则会永久的改变数据库的数据
查看变量：show variables like ‘autocommit’；
事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete
显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
禁用自动提交功能:set autocommit =0；
步骤1：开启事务
set autocommit = 0 ;
start transaction; 可选的
步骤二：编写事务中的sql语句(select insert update delete)
语句1；
语句2；
……
步骤3：结束事务
commit;提交事务
或 rollback；回滚事务
设置保存点：savepoint 节点名;
并发问题
脏读：一个事务读取了其他未提交的数据，读到的是其他事物"更新"的
不可重复读：一个事务多次读取，结果不一致
幻读：一个事务读取了其他未提交的数据，只是读到的是 其他事物“插入”的数据
事物的隔离级别
出现的问题 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed √ √ √
repeatable read × × √
serializable × × ×
mysql中，默认repeated read
oracle中，默认read committed
查看隔离级别：select @@tx_ioslation;
设置隔离级别 set session|global transaction isolation level 隔离级别；
*/
#演示事务的使用步骤 转账
#开启事务
SET autocommit = 0;
#编写一组事务
UPDATE accout SET balance =1000 WHERE username = '张无忌';
UPDATE accout SET balance =1000 WHERE username = '赵敏';
#结束事务
COMMIT;
#演示savepoint的使用
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account WHERE id =25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
delete和truncate
delete支持回滚，truncate不支持回滚
视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态是生成的数据
*/
#案例：查询姓张的同学名和专业
SELECT stuname，majorname
FROM stuinfo s
INNER JOIN major m
ON s.majorid = m.id
WHERE s.stuname = '张%';
CREATE VIEW vi
AS
SELECT stuname，majorname
FROM stuinfo s
INNER JOIN major m
ON s.majorid = m.id;
SELECT * FROM v1
WHERE s.stuname = '张%';
#一、创建视图
/*
语法
create view 视图名
as
查询语句；
*/
#1.查询姓名中包含a字符的员工名、部门名和工种名
#①创建视图
CREATE VIEW v1
AS
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
#②使用视图
SELECT * FROM v1
WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
CREATE VIEW v2
AS
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY department_id;
#使用
SELECT v2.ag, grade_level FROM v2
INNER JOIN job_grades g
ON v2.ag BETWEEN g.lowest_sal AND g.highest_sal
#3.查询平均工资最低的部门信息
CREATE VIEW v3
AS
SELECT department_id
FROM employees e
GROUP BY e.department_id
ORDER BY AVG(e.salary) LIMIT 1;
SELECT d.* FROM v3
INNER JOIN departments d
ON v3.department_id = d.`department_id`
#或者
CREATE VIEW v4
AS
SELECT * FROM v2
ORDER BY ag LIMIT 1;
SELECT d.*,ag FROM v4
INNER JOIN departments d
ON v4.department_id = d.`department_id`;
#二、视图的修改
/*
方式一：
create or replace view 视图名
as
查询语句;
*/
SELECT * FROM v4
CREATE OR REPLACE VIEW v4
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
/*
方式二：
alter view 视图名
as
查询语句;
*/
ALTER VIEW v4
AS
SELECT * FROM employees;
#三、删除视图
/*
drop view 视图名，视图名……；
*/
DROP VIEW v1,v2,v3,v4;
#四、查看视图
DESC v3;
或者
SHOW CREATE VIEW v3;#命令
DROP VIEW v3;
#五、视图的更新
CREATE OR REPLACE VIEW v1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees
CREATE OR REPLACE VIEW v1
AS
SELECT last_name,email
FROM employees
SELECT * FROM v1;
#1.插入
INSERT INTO v1 VALUES('张飞','zf@qq.com');
#2.修改
UPDATE v1 SET last_name = '张无忌'
WHERE last_name ='张飞';
#3.删除
DELETE FROM v1
WHERE last_name ='张无忌';
#具备以下特点的视图不允许更新
/*
①包含关键字的sql语句：分组函数、distinct、group by、having、union或者union all
②常见视图
create or replace view v1
as
select ‘join’ name；
③select中包含子查询
④join
⑤from 一个不能更新的视图
⑥where 子句的子查询引用了from子句中的表
*/
可以通过以下两种方式来删除主表记录
方式一：级联删除
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references maor(id) on delete casade
方式二：级联置空
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references maor(id) on delete set null