05.TCL语言和视图

2022-06-17 约 1939 字预计阅读 4 分钟

TCL语言

Transaction control language 事务控制语言

1 事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

1.1 存储引擎(表类型)

在 mysql 中的数据用各种不同的技术存储在文件(或内存)中
在 mysql 中用的最多的存储引擎有：innodb, myisam, memory等
innodb 支持事务，而myisam、memory 等不支持事务

show engines;

1.1.1 事务的ACID属性

原子性(Atomicity)
- 事务是一个不可分割的工作单位，事务中的操作要么全都发生，要么都不发生
一致性(consisitency)
- 事务必须使数据库从一个一致性状态变换到另一个一致性状态
隔离性(isolation)
- 一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性(Durability)
- 一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

1.1.2 事务的创建

隐式(自动)事务：没有明显的开启和结束的标记
- insert, update, delete语句
显式事务：事务具有明显的开启和结束的标记
- 前提：必须先设置自动提交功能为禁用

show variables like 'autocommit';

# 开启事务
set autocommit=0;
start transaction; #可选
# 编写事务中的sql语句 (select, insert, update, delete)
# 语句1;
# 语句2;
# 
# 结束事务
commit; #提交事务
rollback; #回滚事务

delete 和 truncate 在事务中的区别

truncate不支持回滚

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

# truncate
set autocommit=0;
start transaction;
truncate from account;
rollback;

2 数据库的隔离级别

read uncommitted(读未提交): 出现脏读、幻读、不可重复读
read committed(读已提交): 避免脏读
repeatable read(可重复读): 避免脏读、不可重复读
serializable(串行化): 避免脏读、幻读、不可重复读

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
- 对于两个事务T1, T2.
- T1读取了已经被T2更新但还没有被提交的字段.
- 之后, 若T2回滚, T1读取的内容就是临时且无效的.
不可重复读：一个事务多次读取，结果不一样
- 对于两个事务T1, T2.
- T1读取了一个字段，然后T2更新了该字段.
- 之后, T1再次读取同一个字段, 值就不同了.
幻读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“插入”的数据
- 对于两个事务T1, T2.
- T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行
- 之后, 如果T1再次读取同一个表, 就会多出几行.

Oracle支持的2种事务隔离级别：READ COMMITED(默认), SERIALIZABLE。

Mysql支持4种事务隔离级别，Mysql默认的事务隔离级别为：REPEATABLE READ.

# 查看隔离级别
select @@tx_isolation;
# 设置当前mySQL连接的隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
# 设置数据库系统的全局的隔离级别
set global transaction isolation level read committed;

回滚点

# 设置保存点
savepoint 节点名;

set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;
delete from account where id=28;
rollback to a;  #回滚到保存点

3 视图

含义：虚拟表，和普通表一样使用

mysql5.1出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成

视图的好处：

重用sql语句
简化复杂的sql操作
保护数据，提高安全性

3.1 创建视图

语法：create view 视图名 as 查询语句;

use myemployees;
# 查询姓名中包含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
    GROUP BY department_id;
# 使用
SELECT
    myv2.ag, g.grade_level
FROM
    myv2
        JOIN
    job_grades g ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;

# 查询平均工资最低的部门信息
select * from myv2 order by ag limit 1;
# 查询平均工资最低的部门名和工资
create view myv3
as
select * from myv2 order by ag limit 1;

select d.*, m.ag from myv3 m join departments d on m.department_id = d.department_id;

4 修改视图

语法：

方式一：create or replace view 视图名 as 查询语句;
方式二：alter view 视图名 as 查询语句;

# 方式一
SELECT
    *
FROM
    myv3;
CREATE OR REPLACE VIEW myv3 AS
    SELECT
        AVG(salary), job_id
    FROM
        employees
    GROUP BY job_id;
# 方式二
alter view myv3
as
select * from employees;

5 删除视图

语法：drop view 视图名,视图名,...

drop view myv1,myv2,myv3;

5.1 查看视图

desc myv2;
show create view myv2;

5.2 视图的更新

create or replace view myv1
as
select last_name, email from employees;
# 插入
insert into myv1 values('zhangfei','zf@qq.com');
# 修改
update myv1 set last_name='zhangwuji' where last_name='zhangfei';
# 删除
delete from myv1 where last_name='zhangwuji';

具备以下特点的视图不允许更新

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

update myv1 set m=9000 where department_id=10;
# 常量视图
create or replace view myv2
as
select 'john' name;

update myv2 set name='luvy';
# select中包含子查询
create or replace view myv3
as
select (select max(salary) from employees) 'max_salary';

update myv3 set max_salary=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;

update myv4 set last_name='zhangfei' where last_name='Whalen';
insert into myv4 values('chenzhen','xxx');
# from 一个不能更新的视图
create or replace view myv5
as
select * from myv3;

update myv5 set max_salary=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;
);
update myv6 set salary=10000 where last_name='K_ing';

目录