• 视图、存储过程、触发器

    视图

    视图(View) 是一种虚拟存在的表。视图中的数据并不在数据库中实际存在, 行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。视图值保存了查询的SQL逻辑，不保存查询结果。

    1. 视图基本操作

    1. -- 创建视图
    2. create view stu_v_1 as select id,name from student where id <=20;
    3.  
    4. -- 查看视图创建语句
    5. show CREATE view  stu_v_1
    6.  
    7. -- 修改视图
    8. CREATE or REPLACE view stu_v_1 as SELECT * from student;
    9. alter view stu_v_1 as SELECT * from student;
    10.  
    11. -- 删除视图
    12. drop view stu_v_1;

    2. 视图检查选项CASCADED

    MySQL中，当使用WITH CHECK OPTION子句创建视图时, MySQL会通过视图检查正在更改的每个行，例如插入,更新，删除,以使其符合视图的定义。MySQL允许基于另一个视图创建视图， 它还会检查依赖视图中的规则以保持一致性。 为了确定检查的范围，mysql提供了两个选项:CASCADED和LOCAL，默认值为CASCADED。

    CASCADE: 不管关联视图的是否开启视图检查,都需要判断是否满足插入条件

    1. SELECT * from student;
    2.  
    3. create view v1 as SELECT id,name from student where id <=20;
    4. insert into v1 VALUES (5,'Tom');
    5. insert into v1 VALUES (25,'Tom');
    6.  
    7. create view v2 as SELECT id,name from v1 where id >=10 with CASCADED check OPTION;
    8. insert into v2 VALUES (6,'Tom');
    9. insert into v2 VALUES (11,'Tom');
    10. -- id为21插入失败的原因是，v2视图是通过v1视图查询出来的，且用到了cascaded（级联）,不仅需要检测v2的条件，还要检测v1的条件
    11. insert into v2 VALUES (21,'Tom');
    12.  
    13. create view v3 as SELECT id,name from v2 where id >=15;
    14. insert into v3 VALUES (16,'Tom');
    15. insert into v3 VALUES (7,'Tom');
    16. -- id为22插入v3失败的原因是，v3视图是通过v2视图查询出来的,需要检测v2的条件,且v2又和v1有级联，所以v1,v2,v3三个视图的条件都要进行检测
    17. insert into v3 VALUES (22,'Tom');

    LOCAL:和CASCADE不同的是，进行视图检查时,会去看关联的表有没有开启视图检查,如果没有开启视图检查，则不需要判断关联表的条件

    1. create view v4 as SELECT id,name from student where id <=20;
    2. insert into v4 VALUES (5,'Tom');
    3. insert into v4 VALUES (25,'Tom');
    4.  
    5. create view v5 as SELECT id,name from v4 where id >=10 with CASCADED LOCAL OPTION;
    6. insert into v5 VALUES (6,'Tom');
    7. -- 这里的id=11,如果是local的话,就可以进行插入,因为虽然关联了v4,但v4没有开启视图检查,所以只需满足v5视图的条件即可
    8. insert into v5 VALUES (11,'Tom');
    9.  
    10. create view v6 as SELECT id,name from v2 where id >=15
    11. insert into v6 VALUES (16,'Tom');
    12. -- 这里的id=22同样可以插入,因为虽然关联了v5,v5虽然有视图检查,但是id满足条件,且v5关联的v4没有开启视图检查,所以只需满足v5,v6视图的条件即可
    13. insert into v6 VALUES (22,'Tom');

    3. 视图的更新

      

     存储过程

    1. 基本语法 

     

    1. -- 创建存储过程
    2. CREATE PROCEDURE p1()
    3. BEGIN
    4. 	
    5. 	SELECT count(*) from tb_user;
    6. 	
    7. END
    8.  
    9. -- 调用
    10. call p1()
    11.  
    12. -- 查看
    13. SELECT * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast' 
    14. show create PROCEDURE p1;
    15.  
    16. -- 删除
    17. DROP PROCEDURE IF EXISTS P1;

    2. 变量 

     变量主要分为三种：系统变量、用户自定义变量和局部变量。

    系统变量

    1. -- 查看系统变量
    2. show session variables ;
    3.  
    4. show session variables like 'auto%';
    5. show global variables like 'auto%';
    6.  
    7. select @@global.autocommit;
    8. select @@session.autocommit;
    9.  
    10.  
    11. -- 设置系统变量
    12. set session autocommit = 1;
    13.  
    14. insert into course(id, name) VALUES (6, 'ES');
    15.  
    16. set global autocommit  = 0;
    17.  
    18. select @@global.autocommit;

    用户自定义变量 

    1. -- 变量: 用户变量
    2. -- 赋值
    3. set @myname = 'itcast';
    4. set @myage := 10;
    5. set @mygender := '男',@myhobby := 'java';
    6.  
    7. select @mycolor := 'red';
    8. select count(*) into @mycount from tb_user;
    9.  
    10. -- 使用
    11. select @myname,@myage,@mygender,@myhobby;
    12.  
    13. select @mycolor , @mycount;
    14.  
    15. select @abc;

    局部变量

    1. -- 变量: 用户变量
    2. -- 赋值
    3. set @myname = 'itcast';
    4. set @myage := 10;
    5. set @mygender := '男',@myhobby := 'java';
    6.  
    7. select @mycolor := 'red';
    8. select count(*) into @mycount from tb_user;
    9.  
    10. -- 使用
    11. select @myname,@myage,@mygender,@myhobby;
    12.  
    13. select @mycolor , @mycount;
    14.  
    15. select @abc;

    3. if 条件判断

    if条件判断需求 

    1. create procedure p3()
    2. begin
    3.     declare score int default 58;
    4.     declare result varchar(10);
    5.  
    6.     if score >= 85 then
    7.         set result := '优秀';
    8.     elseif score >= 60 then
    9.         set result := '及格';
    10.     else
    11.         set result := '不及格';
    12.     end if;
    13.     select result;
    14. end;
    15.  
    16. call p3();

    4. 存储过程-参数（IN/OUT） 

     

    需求： 

     

    1. -- in/out/inout参数
    2.  
    3. -- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。
    4. -- score >= 85分，等级为优秀。
    5. -- score >= 60分 且 score < 85分，等级为及格。
    6. -- score < 60分，等级为不及格。
    7.  
    8. create procedure p4(in score int, out result varchar(10))
    9. begin
    10.     if score >= 85 then
    11.         set result := '优秀';
    12.     elseif score >= 60 then
    13.         set result := '及格';
    14.     else
    15.         set result := '不及格';
    16.     end if;
    17. end;
    18.  
    19. call p4(18, @result);
    20. select @result;
    21.  
    22.  
    23.  
    24.  
    25. -- 将传入的 200分制的分数,进行换算,换算成百分制 , 然后返回分数 ---> inout
    26. create procedure p5(inout score double)
    27. begin
    28.     set score := score * 0.5;
    29. end;
    30.  
    31. set @score = 198;
    32. call p5(@score);
    33. select @score;

    5. 存储过程- 循环 

    while

    while循环是满足条件后，再执行循环体中的SQL语句，对应Java中的while语法

    1. -- while   计算从1累加到n的值，n为传入的参数值。
    2.  
    3. -- A. 定义局部变量, 记录累加之后的值;
    4. -- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
    5. create procedure p7(in n int)
    6. begin
    7.     declare total int default 0;
    8.  
    9.     while n>0 do
    10.          set total := total + n;
    11.          set n := n - 1;
    12.     end while;
    13.  
    14.     select total;
    15. end;
    16.  
    17. call p7(100);

    repeat 

    对应Java中的do while语法

    1. -- repeat   计算从1累加到n的值，n为传入的参数值。
    2. -- A. 定义局部变量, 记录累加之后的值;
    3. -- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
    4. create procedure p8(in n int)
    5. begin
    6.     declare total int default 0;
    7.  
    8.     repeat
    9.         set total := total + n;
    10.         set n := n - 1;
    11.     until  n <= 0
    12.     end repeat;
    13.  
    14.     select total;
    15. end;
    16.  
    17. call p8(10);
    18. call p8(100);

    loop

    对应Java中的死循环，leave对应break，iterate对应continue 

    1. -- loop需求1  计算从1累加到n的值，n为传入的参数值。
    2. -- A. 定义局部变量, 记录累加之后的值;
    3. -- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
    4. create procedure p9(in n int)
    5. begin
    6.     declare total int default 0;
    7.  
    8.     sum:loop
    9.         if n<=0 then
    10.             leave sum;
    11.         end if;
    12.  
    13.         set total := total + n;
    14.         set n := n - 1;
    15.     end loop sum;
    16.  
    17.     select total;
    18. end;
    19.  
    20.  
    21. -- loop需求2  计算从1到n之间的偶数累加的值，n为传入的参数值。
    22. -- A. 定义局部变量, 记录累加之后的值;
    23. -- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
    24. -- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
    25.  
    26. create procedure p10(in n int)
    27. begin
    28.     declare total int default 0;
    29.  
    30.     sum:loop
    31.         if n<=0 then
    32.             leave sum;
    33.         end if;
    34.  
    35.         if n%2 = 1 then
    36.             set n := n - 1;
    37.             iterate sum;
    38.         end if;
    39.  
    40.         set total := total + n;
    41.         set n := n - 1;
    42.     end loop sum;
    43.  
    44.     select total;
    45. end;
    46.  
    47. call p10(100);

    6. 存储过程-游标

    1. -- 游标
    2. -- 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），
    3. -- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
    4.  
    5. -- 逻辑:
    6. -- A. 声明游标, 存储查询结果集
    7. -- B. 准备: 创建表结构
    8. -- C. 开启游标
    9. -- D. 获取游标中的记录
    10. -- E. 插入数据到新表中
    11. -- F. 关闭游标
    12.  
    13. create procedure p11(in uage int)
    14. begin
    15.     declare uname varchar(100);
    16.     declare upro varchar(100);
    17.     declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    18.     declare exit handler for SQLSTATE '02000' close u_cursor;
    19.  
    20.     drop table if exists tb_user_pro;
    21.     create table if not exists tb_user_pro(
    22.         id int primary key auto_increment,
    23.         name varchar(100),
    24.         profession varchar(100)
    25.     );
    26.  
    27.     open u_cursor;
    28.     while true do
    29.         fetch u_cursor into uname,upro;
    30.         insert into tb_user_pro values (null, uname, upro);
    31.     end while;
    32.     close u_cursor;
    33.  
    34. end;
    35.  
    36.  
    37. call p11(30);

    7. 存储函数

    存储函数和存储过程的区别在于，只能有IN参数，且必须有返回值。

    1. -- 存储函数
    2. -- 从1到n的累加
    3.  
    4. create function fun1(n int)
    5. returns int deterministic
    6. begin
    7.     declare total int default 0;
    8.  
    9.     while n>0 do
    10.         set total := total + n;
    11.         set n := n - 1;
    12.     end while;
    13.  
    14.     return total;
    15. end;
    16.  
    17.  
    18. select fun1(50);

     触发器

    1. -- 触发器
    2. -- 需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;
    3.  
    4. -- 准备工作 : 日志表 user_logs
    5. create table user_logs(
    6.   id int(11) not null auto_increment,
    7.   operation varchar(20) not null comment '操作类型, insert/update/delete',
    8.   operate_time datetime not null comment '操作时间',
    9.   operate_id int(11) not null comment '操作的ID',
    10.   operate_params varchar(500) comment '操作参数',
    11.   primary key(`id`)
    12. )engine=innodb default charset=utf8;
    13.  
    14. -- 插入数据触发器
    15. create trigger tb_user_insert_trigger
    16.     after insert on tb_user for each row
    17. begin
    18.     insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    19.     (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
    20. end;
    21.  
    22.  
    23. -- 查看
    24. show triggers ;
    25.  
    26. -- 删除
    27. drop trigger tb_user_insert_trigger;
    28.  
    29. -- 插入数据到tb_user
    30. insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());
    31.  
    32.  
    33.  
    34. -- 修改数据触发器
    35. create trigger tb_user_update_trigger
    36.     after update on tb_user for each row
    37. begin
    38.     insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    39.     (null, 'update', now(), new.id,
    40.         concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
    41.             ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
    42. end;
    43.  
    44. show triggers ;
    45.  
    46. update tb_user set profession = '会计' where id = 23;
    47.  
    48. update tb_user set profession = '会计' where id <= 5;
    49.  
    50.  
    51.  
    52.  
    53.  
    54. -- 删除数据触发器
    55. create trigger tb_user_delete_trigger
    56.     after delete on tb_user for each row
    57. begin
    58.     insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    59.     (null, 'delete', now(), old.id,
    60.         concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
    61. end;
    62.  
    63. show triggers ;
    64.  
    65.  
    66. delete from tb_user where id = 26;

  • 相关阅读:
    dragTabs(vue)
    接口测试工具
    正点原子[第二期]Linux之ARM（MX6U）裸机篇学习笔记-1.3
    一次联合编译器问题排查（备忘）
    淘宝/天猫API：img2text-图片识别商品接口
    FactoryBean解读
    华为机试真题 Java 实现【查找众数及中位数】
    Centos7 离线安装docker
    计算机的应用领域
    spark实验求TOP值
  • 原文地址：https://blog.csdn.net/qq_59212867/article/details/133894440