1. 视图
  2. 触发器
  3. 事务
  4. 存储过程
  5. 流程控制

视图是由查询结果构成的一张虚拟表，和真实的表一样，带有名称的列和行数据

强调：视图是永久存储的，但是视图存储的不是数据，只是一条sql语句

视图的特点：

  1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。 
  2. 视图是由基本表（实表）产生的表（虚表）。
  3. 视图的建立和删除不影响基本表。 
  4. 对视图内容的更新（添加、删除和修改）直接影响基本表。 
  5. 当视图来自多个基本表时，不允许添加和删除数据。

优点：

  1. 可以简化查询（多表查询转换为直接通过视图查询）
  2. 可以进行权限控制（把表的权限封闭，开发对应的视图权限）
  1. create view 视图名称  as sql 查询语句  例子：CREATE view test_view as SELECT * from test;
  1. select * from 视图名 [where 条件]
  1. alter view 视图名称 AS SQL语句； 例子：ALTER view test_view as SELECT * from test_view WHERE salary>10000
  1. drop view 视图名称; 
  2. 例子：drop view test_view

触发器可以监视用户对表的增、删、改操作，并触发某种操作（没有查），自动执行，无法直接调用。

创建触发器语法的四要素：

　　1.监视地点（table）

　　2.监视事件（insert/update/delete）

　　3.触发时间（before/after）

　　4.触发事件（insert/update/delete）

  1. # 插入前
  2. CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
  3. BEGIN
  4.     ...
  5. END
  7. # 插入后
  8. CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
  9. BEGIN
  10.     ...
  11. END
  13. # 删除前
  14. CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
  15. BEGIN
  16.     ...
  17. END
  19. # 删除后
  20. CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
  21. BEGIN
  22.     ...
  23. END
  25. # 更新前
  26. CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
  27. BEGIN
  28.     ...
  29. END
  31. # 更新后
  32. CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
  33. BEGIN
  34.     ...
  35. END

语法 

  1. #准备表
  2. CREATE TABLE cmd (
  3.     id INT PRIMARY KEY auto_increment,
  4.     USER CHAR (32),
  5.     priv CHAR (10),
  6.     cmd CHAR (64),
  7.     sub_time datetime, #提交时间
  8.     success enum ('yes', 'no') #0代表执行失败
  9. );
  11. CREATE TABLE errlog (
  12.     id INT PRIMARY KEY auto_increment,
  13.     err_cmd CHAR (64),
  14.     err_time datetime
  15. );
  17. #创建触发器
  18. delimiter //
  19. CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
  20. BEGIN
  21.     IF NEW.success = 'no' THEN #等值判断只有一个等号
  22.             INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
  23.       END IF ; #必须加分号
  24. END//
  25. delimiter ;
  28. #往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
  29. INSERT INTO cmd (
  30.     USER,
  31.     priv,
  32.     cmd,
  33.     sub_time,
  34.     success
  35. )
  36. VALUES
  37.     ('A','0755','ls -l /etc',NOW(),'yes'),
  38.     ('A','0755','cat /etc/passwd',NOW(),'no'),
  39.     ('A','0755','useradd xxx',NOW(),'no'),
  40.     ('A','0755','ps aux',NOW(),'yes');
  43. #查询错误日志，发现有两条
  44. mysql> select * from errlog;
  45. +----+-----------------+---------------------+
  46. | id | err_cmd         | err_time            |
  47. +----+-----------------+---------------------+
  48. |  1 | cat /etc/passwd | 2018-09-18 20:18:48 |
  49. |  2 | useradd xxx     | 2018-09-18 20:18:48 |
  50. +----+-----------------+---------------------+
  51. 2 rows in set (0.00 sec)

案例

强调：NEW表示即将插入的数据行，OLD表示即将删除的数据行

  1. show triggers
  1. drop trigger 触发器的名称

事务用于将某些操作的多个SQL作为原子性操作，意思就是，事务是一组sql语句集合。

一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。在事务内的语句, 要么全部执行成功, 要么全部执行失败。

事务具有以下四个特性（ACID）

　　1.原子性：事务是一个整体，不可分割，包含在其中的sql操作要么全部成功，要么全部失败回滚，不可能只执行其中一部分操作。

　　2.一致性：当事务执行后 所有的数据都是完整的(外键约束 非空约束)。

　　3.持久性：一旦事务提交，数据永久保存在数据库中

　　4.隔离性：事务之间相互隔离，一个事务的执行不影响其他事务的执行

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

　　1.READ UNCOMMITED（未提交读）：所有事务都可以看到其他未提交事务的执行结果。很少用于实际应用，因为它的性能不比其他级别好多少

　　2.READ COMMITED（提交读）：大部分数据库默认级别，不包括mysql。一个事务从开始到提交之前, 所做的任何修改对其他事务都是不可见的。

　　3.REPEATABLE READ（可重复读）：mysql默认级别，解决了脏读的问题. 该级别保证了在同一个事务中多次读取同样记录的结果时一致的. 无法解决幻读问题

　　4.SERIALIZABLE（可串行化）：是最高的隔离级别，强制事务排序，使之不可能相互冲突，从而解决幻读问题

脏读： 一个事物 读到了 另一个事务未提交的数据 查询 之前要保证 所有的更新都已经完成。

不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读：指的是当某个事务在读取某个范围内的记录时, 另外一个事务又在该范围内插入了新的记录, 当之前的事务再次读取该范围的记录时, 会产生幻行(Phantom Row).

  1. start transaction; 开启一个事物
  2. commit 提交事物
  3. rollback 回滚事务

注：mysql默认开启自动提交事务，pymysql默认是不自动提交，需手动commit

存储过程包含了一系列可执行的sql语句的集合，类似于函数(方法)。

使用存储过程的优点：

  1. #1. 用于替代程序写的SQL语句，实现程序与sql解耦
  3. #2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

缺点：不方便扩展

  1. 创建语法:
  2.     create procedure 过程的名称 ({in,out,inout}  数据类型 参数名称)
  3.     begin
  4.         具体的sql代码
  5.     end
  6.     参数前面需要指定参数的作用
    in 表示该参数用于传入数据
  7.     out 用于返回数据
  8.     inout 即可传入 也可返回
  10.     参数类型是 mysql中的数据类型

    调用语法：
    　　call 存储过程()

  1.          案例:创建一个存储过程 作用是将两个整数相加
  2.             create procedure add_p (in a int,in b int)
  3.             begin
  4.                 select a + b;
  5.             end
  6.  
  7.             //
  8.       调用 call add_p(1,2)
  10.          案例:创建一个存储过程 作用是将两个整数相加 将结果保存在变量中
  11.         定义一个变量
  12.         set @su = 100;
  13.             create procedure add_p2 (in a int,in b int,out su int)
  14.             begin
  15.                 set su = a + b;
  16.             end
  17.  
  18.             //
  19.     
  20.     定义变量 set @su = 100;
  21.     调用过程 call  add_p2(10,20,@su);
  23.           注意  在存储过程中 需要使用分号来结束一行 但是分号有特殊含义
  24.           得将原始的结束符 修改为其他符号
  25.             delimiter // 结束符更换为//
  26.             delimiter;

案列 

  1. 在存储过程中 需要使用分号来结束一行 但是分号有特殊含义
  2. 得将原始的结束符 修改为其他符号
  3.       delimiter // 结束符更换为//
  4.       delimiter;

  1.     create procedure show_p (in a int)
  2.     begin
  3.     if a = 1 then
  4.         select "壹";
  5.     elseif a = 2 then
  6.         select "贰";
  7.     else
  8.         select "other";
  9.     end if;
  10.     end //

使用存储过程 完成 输入 一个 数字 1或2 显示 壹 或 贰 

  1. drop procedure proc_name;

  1. delimiter //
  2. CREATE PROCEDURE proc_if ()
  3. BEGIN
  4.     
  5.     declare i int default 0;
  6.     if i = 1 THEN
  7.         SELECT 1;
  8.     ELSEIF i = 2 THEN
  9.         SELECT 2;
  10.     ELSE
  11.         SELECT 7;
  12.     END IF;
  14. END //
  15. delimiter ;

if 

  1. delimiter //
  2. CREATE PROCEDURE proc_while ()
  3. BEGIN
  4.  
  5.     DECLARE num INT ;
  6.     SET num = 0 ;
  7.     WHILE num < 10 DO
  8.         SELECT
  9.             num ;
  10.         SET num = num + 1 ;
  11.     END WHILE ;
  13. END //
  14. delimiter ;

while 

  1. delimiter //
  2. CREATE PROCEDURE proc_repeat ()
  3. BEGIN
  4.  
  5.     DECLARE i INT ;
  6.     SET i = 0 ;
  7.     repeat
  8.         select i;
  9.         set i = i + 1;
  10.         until i >= 5
  11.     end repeat;
  13. END //
  14. delimiter ;

repeat 

  1. BEGIN
  2.     
  3.     declare i int default 0;
  4.     loop_label: loop
  5.         
  6.         set i=i+1;
  7.         if i<8 then
  8.             iterate loop_label;
  9.         end if;
  10.         if i>=10 then
  11.             leave loop_label;
  12.         end if;
  13.         select i;
  14.     end loop loop_label;
  16. END

loop

 

版权声明：本文为mangM原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/mangM/p/9671363.html