MySQL 系统学习系列 - 事务、视图与存储过程的使用《MySQL系列篇-06》

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

事务

1. 事务简介

• 事务（transaction）是指访问并更新数据库中各种数据的一个程序执行单元（unit） [最小执行单元]

• MySQL事务主要用于处理操作量大。复杂度高的数据

  • 1.MySQL数据库只有InnoDB引擎支持事务
  • 2.事务维护数据的完整性（全执行或全不执行）
  • 3.事务用来管理 INSERT,UPDATE,DELETE 语句

• 事务的事务特性（ACID）▲：

  • 1.原子性（A）：要么全部执行，要么全部不执行
  • 2.一致性（C）：再事务开始之前和结束事务之后，数据库的完整性没有被破坏
  • 3.隔离性（I）：允许多个事务同时执行（并发），隔离性…
  • 4.持久性（D）：事务操作完后，重启不会消失…
  • 以后在重新理解

2. 事务控制语句

• 

• autocommit: 该变量用于设置是否自动提交事务默认为1自动提交，也可以改为0非自动提交

  • 不管autocommit是0还是1：BEGIN/START TRANSACTION后，只有当commit数据才会生效，rollback后就会回滚（默认回滚到最前）
  • 当autocommit为0时，不管有没有 BEGIN/START TRANSACTION，只有当commit数据才会生效，rollback后就会回滚
  • 查看autocommit变量：SHOW VARIABLES LIKE 'autocommit'; 扩 查看变量的语句 show variables like '表量名'
  • 设置autocommit变量：SET autocommit = 0

• # 开启与提交事务
      begin; -- 开启事务
        INSERT INTO stu_sw VALUES("zhangsan","G");
        INSERT INTO stu_sw VALUES("lisi","M");
        -- 事务语句区 ...........
      commit; -- 提交事务
    
  # 回滚事务
      begin; -- 开启事务
        INSERT INTO stu_sw VALUES("zhangsan","G");
        rollback; -- 回滚事务 [会自动提交事务，默认回滚到数据最前面]
        INSERT INTO stu_sw VALUES("lisi","M");
      commit; -- 提交事务
    
  # 事务标记点与回滚事务
      begin; -- 开启事务
        INSERT INTO stu_sw VALUES("zhangsan","G");
        SAVEPOINT p1; -- 创建事务标记点
        INSERT INTO stu_sw VALUES("lisi","M");
        rollback to p1; -- 回滚到p1标记点
      commit; -- 提交事务
    
  /**
      当把事务自动提交关闭后，如果没有commit提交事务，就算添加的数据能通过select中查看，也是无效的数据
      可以理解为该数据只能在本机上的黑窗口中查看 【隔离机制】
   */
  

3. 其它

视图

视图是从一个或多个基本表（或视图）中导出的虚拟的表。视图不可以与表重名。数据多用于查询，一般不会通过视图去修改数据

视图的特点：

• 1.视图能简化用户的操作
• 2.视图能增加安全性
• 3.视图对重构数据库提供了一定程度的逻辑独立性

视图的操作

• 

• # 视图操作的基本语法
    -- 创建视图
      create view 视图名称 as SQL语句
  
    -- 修改视图
      alter view 视图名称 as SQL语句
    
    -- 删除视图
      drop view 视图名称
  

视图练习[理解视图操作]

• 数据表与数据的准备

  • CREATE TABLE stu_view( -- 创建stu——view用于视图的练习
        id INT NOT NULL PRIMARY KEY auto_increment,
        age int,
        sid char(20),
        sex bit,
        name char(20),
        isDelete bit DEFAULT 0
    );
    -- 数据插入
      INSERT INTO stu_view(age,sex,name) VALUES(18,1,"kong");
      INSERT INTO stu_view(age,sid,sex,name) VALUES(18,'',1,"wang");
      INSERT INTO stu_view(age,sex,name) VALUES(36,1,"deng"),(20,1,"gaoPP"),(30,1,"hello"),(30,1,"hahaha");
      INSERT INTO stu_view(age,sid,sex,name) VALUES(34,1,"kong%");
    

• 视图操作部分

  • # 假设：需要经常过滤id大于3的学生中，对年龄过滤20,30,40 【一般写法如下】
        SELECT * FROM (SELECT * FROM stu_view WHERE id>3) as s WHERE age>20;
        SELECT * FROM (SELECT * FROM stu_view WHERE id>3) as s WHERE age>30;
        SELECT * FROM (SELECT * FROM stu_view WHERE id>3) as s WHERE age>40;
      
    # 通过视图实现上面的操作
        CREATE VIEW v_stu as SELECT * FROM stu_view WHERE id>3; -- 将 [SELECT * FROM stu_view WHERE id>3] 创建为一个单独的视图
        -----------------------------该3条语句等价于上面的3条语句--------------------------------------------
          SELECT * FROM v_stu as s WHERE age>20; 
          SELECT * FROM v_stu as s WHERE age>30;
          SELECT * FROM v_stu as s WHERE age>40;
        -- 可以将视图理解为编程中的一个函数，能够大大的减少代码耦合 【使用该查找语句时，就可以直接使用视图名称即可】
      
    # 修改视图：alter view 视图名称 as SQL
        ALTER VIEW v_stu as SELECT * FROM stu_view WHERE id>5; -- 将v_stu视图修改为id>5的select查询语句
      
    # 删除视图：drop view 视图名称
        DROP VIEW v_stu; -- 删除v_stu视图
    

• 视图可以理解为编程中封装一个常用的操作一样，可以直接将比较常用的SQL语句封装成一个视图，需要时直接使用视图名称语句即可

• 视图可以大大的降低比较常用的SQL语句的耦合…

• 总结：视图多用于查询频率比较高的语句或逻辑较为复杂的场景【如网站的首页、网站顶部的筛选过滤…】

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中经过第一次编译后调用不需要再次编译，
用户通过指定存储过程的名字并给出参数（如果存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

语法：

• create procedure 存储过程名()
  BEGIN
      一定功能的sql语句
  END
  
  call 存储过程名 -- 调用存储过程
  
  -- 存储过程多用于数据的检验
  

扩充知识:

• 1.通过 delimiter 语句来修改mysql语句的默认结束符 【注意使用该语句时不需要加结束符】
• 2.通过 declare 变量名 类型 默认值.. 来定义一个变量
• 3.通过 set 语句可以修改变量的值

基本使用【演示_后面自行深入】：

• -- 库与表的准备
     CREATE DATABASE db_sp; -- 数据库的创建
     USE db_sp; -- 使用db_sp库
     CREATE TABLE t_sp( -- 表结构创建
         name varchar(25),
         age INT
     );
  -- ---------------------------------- 问题：向t_sp中插入3条数据[使用存储过程] ----------------------------------
     delimiter $$ -- 修改sql语句的结束符【用于创建存储过程时可以使用;隔开语句而不执行（区分整体）】
     CREATE PROCEDURE add_val() -- 定义存储过程
     BEGIN
     -- ------------------逻辑语句部分【存储过程语句部分】------------------
         DECLARE i INT DEFAULT 1; -- 定义变量 i
         WHILE i<4 DO -- 使用while循环（集体使用后面在学[自行学习]）
             INSERT INTO db_sp.t_sp(name,age) VALUES("kong",1);   
             SET i=i+1; -- 每循环一次i自加1
         END WHILE;
     END$$ -- 结束存储过程
     delimiter ; -- 定义完存储过程后，将sql结束符恢复默认值
    
     call add_val(); -- 调用存储过程
    
  -- 相当于封装了一个函数，也可以使用参数来动态的添加数据............
  -- 其它操作与逻辑操作等，后面找时间再自行学习 [false]
    
  

查看存储过程

• 查看当前存在的存储过程：show procedure status
• 查看特定数据
  • 确定数据库名： show procedure status where db=数据库名
  • 不确定数据库名(模糊查找)： show procedure status where name like 模糊的数据库名

删除存储过程 drop procedure 存储过程名

存储过程可以理解为就是一个编程中的函数，调用存储过程时也可以传参，并且存储过程也可以写一些逻辑判断等（如while循环、if判断…）