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

全栈程序员站长

发布于 2022-06-29 15:08:12

7750

发布于 2022-06-29 15:08:12

文章被收录于专栏：全栈程序员必看

一、事务

概念：事务指的是满足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$