SQL语言：TCL语言的学习

TCL语言的学习

• TCL：（Transaction Control Language）：事务控制语言
• 事务和事务处理

#TCL
/*
Transaction Control language 事务控制语言

事务:
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
案例:转账
张三丰	1000
郭襄		1000

update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
事务的特性:
ACID 
原子性:一个事务不可再分割，要么都执行要么都不执行
一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性:一个事务的执行不受其他事务的干扰
持久性:一个事务一旦提交，则会永久的改变数据库的数据

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句
delete from 表 where id = 1;

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

set autocommit=0;
步骤1：开始事务
set autocommit=0;
start transacation;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

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

并发事务
1、事务的并发问题是如何发生的？
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据

事务的隔离级别：		脏读		不可重复读		幻读
read uncommitted:    √ 			√				√
read committed:		 ×			√ 				√
repeatable read:	 ×			×				√
serializable:		 ×			×				×
mysql中默认 第三个隔离级别 repeatable read
oracle中默认 第二个隔离级别 read committed
查看 自动提交功能：
show variables like 'autocommit';
开启事务的语句：
update 表 set 张三丰的余额=500 where name='张三丰'

update 表 set 郭襄的余额=1500 where name='郭襄'


*/

show engines;

#演示事务的使用步骤

#开始事务
set autocommit=0;
start transaction;
#编写一组事务的语句
update account set balance=1000 where username='张无忌';
update account set balance=1000 where username='赵敏';

#结束事务
rollback;
#commit;

select * from account;

#3，演示savepoint的使用
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;#设置保存点
delete from account where id=28;
rollback to a;#回滚到保存点 （25号删掉了 28号没有删）

• mysql8.0版本之后的查看事务的隔离级别

select @@transaction_isolation;

• 设置当前mysql连接的隔离级别

set transaction isolation level read committed;

• 设置数据库系统的全局的隔离级别

set global transaction isolation level read committed;

视图

• 含义：mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
• 好处：1、简化sql语句，2、提高了sql的重用性，3、保护基表的数据，提高了安全性

#一、视图的创建
create view 视图名
as
查询语句;

#二、视图的修改
#方式一
/*
create pr replace view 视图名 as 查询语句;
*/

#方式二
/*
alter view 视图名 as 查询语句;
*/

#三、删除视图
/*
语法:drop view 视图名,视图名,...;
*/

drop view myv1,myv2;

#四、查看视图
desc myv3;

show create view myv3;

#1、创建视图emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱
create view emp_v1 as select last_name,salary,email from employees where phone_number like '011%';

#2、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
create view emp_v3 as select max(salary) mx,department_id from employees group by department_id;
select * from emp_v3;
create view emp_v2 as select d.* from departments d inner join emp_v3 on d.department_id=emp_v3.department_id where emp_v3.mx>12000;
select * from emp_v2;

#五、视图的更新
create or replace view myv3 as select last_name,email from employees;

select * from myv3;
#1.插入
insert into myv3 values('张飞','zf@qq.com');
select * from employees;#原始表同时也更新

#2.修改
update myv3 set last_name='张无忌' where last_name='张飞';
select * from employees;

#3.删除
delete from myv3 where last_name='张无忌';
select * from employees;
#具备以下特点的视图不允许更新

#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union 或者union all
create or replace view myv1 as select max(salary) mx,department_id from employees group by department_id;

select * from myv1;

#更新
update myv1 SET mx=9000 where department_id=10;#不允许更新 有分组函数和group by


#②常量视图

create or replace view myv2 as select 'john' name;

select * from myv2;

#更新
update myv2 set name='lucy'#不能更新，常量视图

#③select中包含子查询
create or replace view myv3 as select(select max(salary) from employees) 最高工资;

#更新
select * from myv3;

update myv3 set 最高工资=100000;#不能更新 

#join
create or replace view myv4 as select last_name,department_name from employees e join departments d on e.department_id=d.department_id;

#更新
select * from myv4;
update myv4 set last_name='张飞' where last_name='Whalen';
insert into myv4 values('陈真','xxxx');

#⑤from 一个不能更新的视图
create or replace view myv5 as select * from myv3;

#更新
select * from myv5;
update myv5 set 最高工资=10000 where department_id=60;

#⑥where 子句的子查询引用了from 子句中的表
create or replace view myv6 as select last_name,email,salary from employees where employee_id in (select manager_id from employees where manager_id is not null);

select * from myv6;
#更新
update myv6 set salary=10000 where last_name='k_ing';

视图和表的对比
	 创建语法的关键字	 是否实际占用物理空间				使用
视图  create view			没有(只是保存了sql逻辑)		 增删改查，只是一般不能增删改
表    create table		 占用(保存了数据)			   增删改查

#2.delete和truncate再事务使用时的区别

#演示delete
set autocommit=0;
start transaction;
delete from account;
rollback;

select * from account; #支持回滚

#演示truncate
set autocommit=0;
start transaction;
truncate TABLE account;
rollback;

select * from account; #已经没有记录了，是实际的删除