一、事务
概念:事务指的是满足ACID特性的一组操作,可以通过commit提交一个事务,也使用rollback进行回滚。一个或一组语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
事务的ACID属性:
- 原子性(Atomicity): 原子性是指事务是一个不可分割的工作单位,事务中的操作要么发生,要么都不发生。
- 一致性(Consistency):事务必须使数据库从一个一致性状态变换到另一个一致性状态。
- 隔离性(Isolation):事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
事务的创建:
- 隐式事务:事务没有明显的开启和结束的标记。如insert,update,delete语句。
- 显式事务:事务具有明显的开启和结束的标记。
前提:必须设置自动提交功能为禁用。 set autocommit=0
- 开启事务
set autocommit=0;
start transaction; # 可选的
- 编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
设置回滚点;
savepoint 节点名; #设置保存点
- 结束事务
commit; #提交事务
rollback; #回滚事务 (相当于操作无效)
(以上二者取其一)
回滚到指定的地方:rollback to回滚点名;
示例:
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a; #设置保存点
delete from account where id=28;
rollback to a;#回滚到保存点
#删了id=25,未删id=28
事务并发问题
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题。
- 脏读:
T1 修改一个数据但未提交,T2 随后读取这个数据。如果 T1 撤销了这次修改,那么 T2 读取的数据是脏数据。
- 不可重复读
T2 读取一个数据,T1 对该数据做了修改。如果 T2 再次读取这个数据,此时读取的结果和第一次读取的结果不同。
- 幻读:
幻读本质上也属于不可重复读的情况,T1 读取某个范围的数据,T2 在这个范围内插入新的数据,T1 再次读取这个范围的数据,此时读取的结果和和第一次读取的结果不同。
事务隔离级别
- 读未提交数据(read uncommitted): 事务中的修改,即使没有提交,对其它事务也是可见的。
- 读已提交数据 (read commited): 一个事务只能读取已经提交的事务所做的修改。换句话说,一个事务所做的修改在提交之前对其它事务是不可见的。
- 可重复读 (repeatable read):保证在同一个事务中多次读取同一数据的结果是一样的。
- 串行化 (serializable): 强制事务串行执行,这样多个事务互不干扰,不会出现并发一致性问题。
该隔离级别需要加锁实现,因为要使用加锁机制保证同一时间只有一个事务执行,也就是保证事务串行执行。
查看隔离级别:
select @@tx_isolation;
设置隔离级别:
set session transaction isolation level 隔离级别;
二、视图
含义:
- 虚拟表,和普通表一样使用
- mysql5.1版本出现的新特性,是通过表动态生成的数据
- 临时的,可重复利用 (领导看舞蹈班)
- 只保存了sql逻辑,不保存查询结果
应用场景
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
好处:
重用sql语句
简化复杂的sql操作,不必知道它的查询细节
保护数据,提高安全性(看不到原始表)
案例:查询姓张的学生名和专业名
#不使用视图
select stuname,majorname
from stuinfo s
inner join major m
on s.majorid=m.id
where s.stuname like '张%';
#将常用的表封装成一个视图
create view v1
as
select stuname,majorname
from stuinfo s
inner join major m on s.majorid = m.id
#从视图中查询
select * from v1 where stuname like '张%';
1. 创建视图
create view 视图名
as
查询语句;
2. 视图修改
方式一
create or replace view 视图名
as
查询语句;
方式二
alter view 视图名
as
查询语句;
3. 删除视图
drop view 视图名,视图名,...;
4. 查看视图
desc myv3;
show create view myv3;
5. 视图的更新
与此同时,原表也会被修改
1.插入
insert into myv1 values('张飞','zf@qq.com');
2.修改
update myv1 set last_name=‘张无忌’ where last_name =‘张飞’;
3.删除
delete from myv1 where last_name = '张无忌';
一般视图不允许修改,只能读
具备以下特点的视图不允许更新:
1.包含以下关键字的SQL语句:分组函数、DISTINCT,GROUP BY,HAVING ,UNION 或者 UNION ALL
2.常量视图
3.select中包含子查询
4.join
5.from 一个不能更新的视图
6.where子句的子查询引用了from子句中的表
6. 视图和表的对比:
创建语法的关键字 是否实际占用物理空间 使用 视图 create view 只是保存了sql逻辑 查,一般不能增删改 表 create table 保存了数据 增删改查
三、存储过程和函数
类似于java中的方法
好处: 提高代码的重用性 ; 简化操作
1. 存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
- 提高代码重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
语法
- 创建
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end
注意:
1.参数列表包含三部分 :参数模式 参数名 参数类型 ex: in stuname varchar(20) 参数模式: in:该参数可以作为输入,也就是改参数需要调用方法传入值 out:该参数可以作为输出,也就是该参数可以作为返回值 inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值 2.如果存储过程体仅仅只有一句话,begin end 可以省略 存储过程体中的每条SQL语句的结尾要求必须加分号。 存储过程的结尾可以使用delimiter重新设置 语法: delimiter 结束标记 案例: delimiter $(用这个标记代替delimiter)
- 调用
call 存储过程名(实参列表);
不同种类
1.
delimiter $
create procedure mypl()
begin
insert into admin(username,password)
values('john','0000');
end $
#调用
call mypl()$
2.创建带in模式参数的存储过程
#案例1:创建存储过程,根据女神名,查询对应的男神信息
create procedure myp2(in beautyName VARCHAR(20))
begin
select bo.*
from boys bo
right join beauty b on bo.id = b.boyfriend_id
where b.name = beautyName
end $
#调用
call myp2('柳岩')$
3.创建存储过程,用户是否登录成功
create procedure myp3(in username varchar(20),in password varchar(20))
begin
begin result int default 0;#声明并初始化变量
select count(*) into result#给变量赋值
from admin
where admin.username = username
and admin.password = password;
select if(result>0,'成功',‘失败’);#使用变量,就是打印变量
end $
#调用
call myp3('张飞',‘8888’)$
4.带Out模式的存储过程
案例1:根据女神名,返回对应的男神名
create procedure myp5(in beautyName varchar(20),out boyName varchar(20))
begin
select bo.boyName in boyName
from boys bo
inner join beauty b
on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
#调用
call myp5(‘小昭’,@bName)$ #@bName是用户变量名
select @bName$
案例2:根据女神名,返回对应的男神名和男神魅力值
create procedure myp6(in beautyName varchar(20),out boyname varchar(20),out userCP int)
begin
select bo.boyName,bo.userCP into boyName,usercp
from boys bo
inner join beauty b
on bo.id=b.boyfriend_id
where b.name=beautyName;
end $
#调用
call myp5(‘小昭’,@bName,@usercp)$
select @bName,@usercp$
5:创建带inout模式参数的存储过程
案例1:传入a和b两个值,最终a和b都翻倍并返回
create procedure myp8(inout a int,inout b int)
begin
set a = a*2;
set b = b*2;
end $
#调用
set @m=10$
set @n=20$
call myp8(@m,@n)$
select @m,@n$
- 删除存储过程
drop procedure 存储过程名
- 查看存储过程的信息
show create procedure 存储过程名;
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/100179.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...