mysql---事务与视图

事务

什么是事务

一个或多个sql组成一个执行单元

事务由单独单元的一个或多个SQL语句组成，在这
个单元中，每个MySQL语句是相互依赖的。而整个单独单
元作为一个不可分割的整体，如果单元中某条SQL语句一
旦执行失败或产生错误，整个单元将会回滚。所有受到影
响的数据将返回到事物开始以前的状态；如果单元中的所
有SQL语句均执行成功，则事物被顺利执行

事务的四大属性

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

事务特性的解析

原子性： 事务中的语句不可以分割，是一起执行的
一致性：事务会使sql的总状态使不变的使一一致的，比如你的事务时改变两个人的工资额度，但是在改变后的总额度和改变前的总额度一致，也就是其中一个增加另外一个必须减少
隔离性： 多个事务之间互相隔离开
持久性： 一个事务一旦提交就永久改变不可改变，比如你删除一个语句也是一个事务，然后

为什么要使用事务

通过确保成批的SQL操作要么完全执行，要么完 全不执行，来维护数据库的完整性

事务术语

事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布 回退（与回退整个事务处理不同）。

事务的创建

• 隐式事务： 事务没有明显的开启和结束的标记，比如insert、update、delete语句
• 显式事务：事务具有明显的开启和结束的标记
  前提是必须先设置自动提交功能为禁用: set autocommit = 0

创建显示事务的步骤

1 : 开启事务 set autocommit=0; start transaction;
2： 编写事务中的sql语句(select insert update delete)
3: 结束事务：commit提交事务/rollback 结束事务

• eg

show VARIABLES like 'autocommit'  --  首先查看事务的自动提交功能是否被禁用

set autocommit = 0;  -- 开启禁止事务提交功能
-- rollback;
update account set balance = 500 where username = '张无忌';

update account set balance = 1500 where username = '赵敏';
-- rollback;
commit ;  -- 提交事务

rollback 回滚语句

• 也可以理解为和roollback这一次搭配执行的事务时不会执行的

set autocommit=0;
– start acuOne;
update account set balance=1000 where username = ‘张无忌’;
update account set balance=1000 where username = ‘赵敏’; – 这里是不执行的 因为下面的rollback给回滚了，也就相当于这一步 执行后下面的rollback又给还原到这一步的上面了

rollback; – 可以理解位这一次的事务不执行了

事务的隔离级别

		  脏读		不可重复读	幻读
read uncommitted：√		√		√
read committed：  ×		√		√
repeatable read： ×		×		√
serializable	  ×             ×     




mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

删除事务

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

事务的回滚点

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

---

视图

什么是视图

视图就是临时表虚拟表，例如学校组建的乐器组是学校的学生组建的，因为是临时组建的为了表演来的，所以这个乐器组就是视图
然后可以重复利用这个乐器组 这就是视图 ，临时虚拟表重复利用

视图的创建 create view

视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改


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

视图的使用

#1.查询姓名中包含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%';



#2.查询各部门的平均工资级别

#①创建视图查看每个部门的平均工资
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`;



#3.查询平均工资最低的部门信息

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资

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`;

**视图的修改 **

• 第一种 replace

create or replace view 视图名
as
如果视图存在就修改 不存在就创建
查询语句;

SELECT * FROM myv3 

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

• 第二种修改 alter

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

ALTER VIEW myv3
AS
SELECT * FROM employees;

删除视图

drop view 视图名,视图名,…;

DROP VIEW emp_v1,emp_v2,myv3;

**查看视图 desc **

DESC myv3;

SHOW CREATE VIEW myv3;

视图的更新

视图的更新是更新视图内部的数据，视图就是一个临时表，update就是我们只能对表内的数据进行更改
视图的更新可以使用insert update对视图临时表内部的数据进行修改

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

#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
②常量视图不能更新
③Select中包含子查询
④join
⑤from一个不能更新的视图
⑥where子句的子查询引用了from子句中的表

-- 创建视图
select * from myv1
create or replace view myv1 as 
select last_name,email from employees;

show create view myv1

desc myv1
-- 插入数据
insert into myv1 values('老王', '67546@163.com')

-- 修改
update myv1 set last_name = '隔壁老王' where email = '67546@163.com'



------------

create or replace view myv1 
as 
select max(salary)m, department_id from employees GROUP BY department_id 

select * from myv1 

-- 更新数据 这种是不可以的 因为视图内使用了分组函数
update myv1 set m = 7001 where department_id = null


-- 创建常量视图
create or replace view myv2 
as 
select 'john' name ;

select * from myv2 

-- 因为是常量视图所以也是不能更新的
update myv2 set name = 'lucy'


# 3：SELECT中包含子查询的也不能更新
create or replace view myv3 as

select department_id, (select max(salary) FROM employees) 最高薪资 FROM departments
select * from myv3 

-- 更新 是不可以的因为视图中select中包含了子查询
update myv3 set 最高薪资  = 10000


# 4 join 连接查询的视图

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

select * from myv4 

-- 更新   因为视图使用了join所以是可以update 然后不可以insert 插入

update myv4 set last_name = '老王' where last_name = 'Whalen'; 可以修改
insert into myv4 values('老刘','sddsffs')  -- 不可以插入





# from 一个不能更新的视图

create or REPLACE view myv5 
as 
select * from myv3 

select * from myv5

-- 更新是不可以使用的
update myv5 set 最高薪资 = 10000 where department_id = 60

视图和表的对比

	创建语法的关键字	  是否实际占用物理空间	    使用

视图	create view		   只是保存了sql逻辑	  增删改查，只是一般不能增删改

表	create table		保存了数据		  增删改查

视图Practices

# 一、创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

create or replace view emp_v1 as 
select last_name, salary, email from  employees  where phone_number like '011%';

select * from emp_v1


二、要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员
工姓名和邮箱、电话号码



create or replace view emp_v1 as

SELECT last_name, email,phone_number from employees where phone_number like '011%' and email like '%e%';


select * from emp_v1
三、向 emp_v1 插入一条记录，是否可以？
可以插入
insert into emp_v1 values('tt','sdfsfa',011442399782390)


四、修改刚才记录中的电话号码为‘0119’
update emp_v1 set phone_number = '0119'

五、删除刚才记录

drop view emp_v1

六、创建视图 emp_v2，要求查询部门的最高工资高于 12000 的部门信息

create or replace view emp_v2 
as 

select  max(salary) 最高薪资, department_id from 
employees 
GROUP BY department_id having MAX(salary) > 12000


select d.* from departments d join emp_v2 on d.department_id = emp_v2.department_id 


select * from  emp_v2


七、向 emp_v2 中插入一条记录，是否可以？

不可以，因为使用了分组

八、删除刚才的 emp_v2 和 emp

drop view emp_v2

Practices

/* 

1 创建Book表字段如下
	bid 整型 要求主键
	bname 字符型 要求设置唯一主键，并非空
	price 浮点型 要求有默认值10 
	btypeid  类型编号，要求引用bookType表的id字段

*/


create table Book (

bid int auto_increment primary key,

bname char(32) unique  not null,
price DOUBLE default 10.00,
btypeid int ,
foreign key(btypeid) reference bokType(id )
)


2 : 开启事务向表中插入一行数据

set autocommit = 0;
insert into (bid ,bname price btypeid)
values (1,'老王在隔壁',700,3);
commit 

3: 创建视图 实现查询价格大于100的书名和类型名
create view v1 
as 
select bname, name from Book join bookType on Book.btypeid = bookType.id where price > 1000

4: 修改视图实现查询价格在90---120的书名和价格
create or replace view v2 
as 
select bname,price from Book where price between 90 and 120

5 :删除视图
drop view v2 

变量

变量由系统定义，不是用户定义，属于服务器层面

• 变量的分类

系统变量：
     全局变量
     会话变量

自定义变量：
     用户变量
     局部变量