数据库原理——事务、视图、存储过程

数据库原理——事务、视图、存储过程

一、事务

概念:事务指的是满足ACID特性的一组操作,可以通过commit提交一个事务,也使用rollback进行回滚。一个或一组语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
事务的ACID属性:

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

事务的创建:

  • 隐式事务:事务没有明显的开启和结束的标记。如insert,update,delete语句。
  • 显式事务:事务具有明显的开启和结束的标记。
    前提:必须设置自动提交功能为禁用。 set autocommit=0
  1. 开启事务
set autocommit=0;
start transaction;  # 可选的
  1. 编写事务中的sql语句(select insert update delete)
语句1;
语句2...
设置回滚点;
savepoint 节点名; #设置保存点
  1. 结束事务
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$
  1. 删除存储过程
drop procedure 存储过程名
  1. 查看存储过程的信息
 show create procedure 存储过程名;
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/100179.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • python mmap_python mmap对象[通俗易懂]

    python mmap_python mmap对象[通俗易懂]—-使用内存映射的原因为了随机访问文件的内容,使用mmap将文件映射到内存中是一个高效和优雅的方法。例如,无需打开一个文件并执行大量的seek(),read(),write()调用,只需要简单的映射文件并使用切片操作访问数据即可。内存映射一个文件并不会导致这个文件被读取到内存中。也就是说,文件并没有被复制到内存缓存或数组中。相反,操作系统仅仅为文件内容保留了一段虚拟内存。当访问文件的不同区域时…

  • 中高级Java开发面试题,最难的几道Java面试题,看看你跪在第几个

    中高级Java开发面试题,最难的几道Java面试题,看看你跪在第几个5.为什么char数组比Java中的String更适合存储密码?另一个基于String的棘手Java问题,相信我只有很少的Java程序员可以正确回答这个问题。这是一个真正艰难的核心Java面试问题,并且需要对String的扎实知识才能回答这个问题。这是最近在Java面试中向我的一位朋友询问的问题。他正在接受技术主管职位的面试,并且有超过6年的经验。如果你还没有遇到过这种情况,那么字符数组和字符串可以用来存储文本数据,但是选择一个而不是另一个很难。但正如我的朋友所说,任何

  • 图形渲染管线简介_渲染流水线和渲染管线

    图形渲染管线简介_渲染流水线和渲染管线TheGraphicsRenderingPipeline渲染管线,这章主要讲光栅化渲染管线。毕业前实习时,也实现过一个简单的软光栅化渲染管线,再复习一下。在计算机图形学领域,shading

  • [精选]详细介绍MySQL中常见的锁

    [精选]详细介绍MySQL中常见的锁

  • 狂神说Linux_狂神说docker笔记

    狂神说Linux_狂神说docker笔记Linux在服务器端,很多大型项目都是部署在Linux服务器上利用VM + Centos7搭建本地Linux系统你可以使用 man [命令]来查看各个命令的使用文档,如 :man cp。概念云服务器就是一个远程电脑Linux中一切皆文件根目录/,所有的文件都挂载在这个节点下/bin:bin是Binary的缩写, 这个目录存放着最经常使用的命令。/boot: 这里存放的是启动Linux时使用的一些核心文件,包括一些连接文件以及镜像文件。/dev : dev是Device(设备

  • HTML embed 标签「建议收藏」

    HTML embed 标签「建议收藏」HTMLembed标签embed标签–定义网页中嵌入除图片外的多媒体不符合标准网页设计的理念,不赞成使用.embed标签是单独出现的,以开始,结束使用embed标签可以在网页中嵌入Flash,Mid,MP3等嵌入式内容embed标签已经被符合标准的object标签代替。属性Common–一般属性align–对齐方式autostart–是否

    2022年10月21日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号