事务
什么是事务
一个或多个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
变量
变量由系统定义,不是用户定义,属于服务器层面
- 变量的分类
系统变量:
全局变量
会话变量自定义变量:
用户变量
局部变量