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

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

一、事务

概念:事务指的是满足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)
blank

相关推荐

  • latex中的括号怎么打_小括号和中括号的简便算法

    latex中的括号怎么打_小括号和中括号的简便算法https://blog.csdn.net/han____shuai/article/details/49679335功能语法显示不好看    (\frac{1}{2})好一点\left(\frac{1}{2}\right)您可以使用\left和\right来显示不同的括号:功能语法显示圆括号,小括号\left( \frac{a}{b}\right)方括号,中括号\left[ \frac…

    2022年10月11日
  • js如何创建数组

    js如何创建数组如何创建数组使用数组之前首先要创建,而且需要把数组本身赋至一个变量。好比我们出游,要组团,并给团定个名字“云南之旅”。创建数组语法:varmyarray=newArray();        我们创建数组的同时,还可以为数组指定长度,长度可任意指定。varmyarray=newArray(8);//创建数组,存储8个数据。 注意:1.创

  • awvs13使用教程_脚本网

    awvs13使用教程_脚本网你可以在以下渠道联系到我,转载请注明文章来源地址~知乎:Sp4rkWGITHUB:Sp4rkWB站:一只技术君博客:https://sp4rkw.blog.csdn.net/联系邮箱:getf_own@163.com文章目录前言核心接口仪表盘接口新增任务接口设置扫描速度启动扫描任务丝滑脚本前言最近在改reaper的awvs互动功能,因为自己的服务器垃圾,一次最多扫四个站,否则就卡死了。所以需要对现有的批量脚本进行修改处理。逻辑比较简单:拿到web资产,django异步启扫描任务从l

  • 罗技k375s怎么连接_罗技g933s蓝牙连接

    罗技k375s怎么连接_罗技g933s蓝牙连接罗技375s

    2022年10月15日
  • ubuntu如何更新_ubuntu更新软件包列表命令

    ubuntu如何更新_ubuntu更新软件包列表命令ubuntu怎么更新?ubuntu更新命令及方法安装Ubuntu系统后,第一件事就是更新系统源。由于系统安装的默认源地址在英国,作为Ubuntu的主源,国内连接速度非常慢,所以我们要将它换成就近的

  • 「Odoo 基础教程系列」第三篇——从 Todo 应用开始(2)

    「Odoo 基础教程系列」第三篇——从 Todo 应用开始(2)

发表回复

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

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