MySQL视图、存储过程、函数、触发器、定时任务、流程控制总结 ...

视图的增删改查

视图相当于一张只能读的表,不可以修改。当组成视图的表发生数据变化的时候,视图会相对应的进行改变。

存储过程的练习

创建存储过程：

1. create [if not exists] procedure 名字 ([in | out | inout] 参数名称 参数类型)
2. begin
3. # sql语句
4. end;

复制代码

查询存储过程：

存储过程存放于infomation_schema数据库，routines表e

1. -- 使用sql查询
2. SELECT *
3. from information_schema.ROUTINES
4. where information_schema.routines.routine_schema != 'sys'

复制代码

运行结果：

删除存储过程：

1. drop PROCEDURE pr_test;

复制代码

运行结果：

存储过程的练习：

1. -- 存储过程的练习 无参数
2. create PROCEDURE pr_test()
3. BEGIN
4.         select * from dept;
5. END;
7. -- 使用存储过程
8. call pr_test();
10. -- 存储过程 带有输入参数
11. -- 如果只有一个输入参数 关键字in 可以省略
12. create PROCEDURE pr_test1(_deptno int)
13. BEGIN
14.         SELECT * from emp WHERE deptno = _deptno;
15. END;
17. call pr_test1(10);
18. call pr_test1(20);
19. call pr_test1(30);
21. -- 存储过程  带有输出参数
22. -- 输出参数的out关键字不可以省略
24. create PROCEDURE pr_test2(out _ename VARCHAR(50))
25. BEGIN
26.         SELECT ename into _ename from emp;
27. END;
29. -- 测试
30. set @ename = '';
32. -- 测试 如果包含多条数据 是不能直接进行赋值的
33. call pr_test2(@ename) ;
35. SELECT @ename;
37. -- 测试存储过程 单个输出参数
38. CREATE PROCEDURE pr_test3(out _ename VARCHAR(50))
39. BEGIN
40.         SELECT ename INTO _ename from emp WHERE emp.deptno = 10;
41. END;
43. -- 输出参数
44. set @deptno = '';
46. -- 执行存储过程
47. call pr_test3(@deptno);
49. -- 输出结果
50. SELECT @deptno;
53. -- 测试存储过程 有输入参数 也有输出参数
54. -- 关键字in 可以省略 out 不可以省略
55. create PROCEDURE pr_test5(_id int,out _ename varchar(20))
56. BEGIN
57.         SELECT ename into _ename from emp WHERE deptno = _id;
58. END;
60. -- 测试数据
61. set @result = '';
63. --
64. call pr_test5(10,@result);
66. -- 查询结果
67. SELECT @result;
70. -- 测试存储过程 即使输入参数 也是输出参数
71. CREATE PROCEDURE pr_test6(INOUT res VARCHAR(20) )
72. BEGIN
73.         SELECT emp.deptno into res from emp where emp.empno = res;
74. END;
76. SELECT * from emp;
78. --
79. set @res = '7876';
81. --
82. call pr_test6(@res);
84. --
85. SELECT @res;

复制代码

函数的使用、查看、删除

1. -- 函数的练习
2. -- 创建函数
3. create FUNCTION fun_show_detail() # 注意事项1： 这里的括号一定不能丢
4. returns varchar(30) # 注意事项2： 这里是rerurns
5. BEGIN
6.         declare _res VARCHAR(50); # 注意事项3： Delare 需要搭配存储过程和函数使用 不能单独使用
7.         SELECT 1 INTO _res ;
8.         return _res;
9. END;
11. -- 使用函数
12. SELECT fun_show_detail()
13. -- 删除函数
14. drop FUNCTION fun_show_detail;

复制代码

触发器


查询当前数据库中所有的触发器：

1. show TRIGGERS from emp;

复制代码

删除触发器：

1. drop TRIGGER tr_emp_update

复制代码

触发器练习：

1. -- 创建触发器
2. create trigger tr_emp_insert
3. BEFORE INSERT
4. on emp FOR EACH ROW
5. BEGIN
6.         insert into emp(empno,ename) values(1,'测试');
7. END;
8. -- 触发触发器
9. insert into emp(empno,ename) values(2,'外面测试');

复制代码

执行结果：

因为这样会循环的触发触发器，所以不能直接这样进行数据的插入。可以通过set的方式进行赋值。

1. create trigger tr_emp_insert
2. BEFORE INSERT
3. on emp FOR EACH ROW
4. BEGIN
5.         -- insert into emp set empno = 1,ename ='测试';
6.         set new.empno = 2,new.ename ='测试';
7. END;

复制代码

执行sql:

1. insert into emp(empno,ename) values(2,'外面测试');

复制代码

再次执行：

所以如果是before xxx，不会直接进行增删改，会执行触发器里面的代码。
关于触发器的new虚拟表格和old虚拟表格
当进行insert的时候，new表格可以获取插入的数据。
当进行delete的时候，old表格可以获取被删除行的数据。
当进行update的时候，new表格中是修改后的数据，old表格中是被修改行的数据。
定时任务event

定时任务 执行一次

语法：

1. CREATE
2. [DEFINER = user]
3. EVENT
4. [IF NOT EXISTS]
5. event_name
6. ON SCHEDULE  AT timestamp [+ INTERVAL interval] ...
7.    
8. DO
9. begin
10. # 要执行的sql语句
11. end;

复制代码

间隔任务 多次执行

语法：

1. CREATE
2. [DEFINER = user]
3. EVENT
4. [IF NOT EXISTS]
5. event_name
6. ON SCHEDULE  EVERY  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
7.               WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
8.               DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
9.    
10. DO
11. begin
12. # 要执行的sql语句
13. end;

复制代码

查询定时任务

1. show EVENTS FROM emp;

复制代码

删除定时任务

1. drop event event_name

复制代码

流程控制

case

给我整懵了，怎么测试怎么出错。
出错原因：
​        1.case语句不能直接使用，需要搭配存储过程或者函数使用，或者搭配select语句使用。
​        2.搭配select语句，里面每个语句结束后不能添加分号，结尾语句必须为end，不能为end case;

1. SELECT case 10
2. when 10 then  '10' -- 这里不能加分号
3. when 9 then  '9' -- 这里不能加分号
4. else  '8'
5. end; -- 这里不能用end case;结尾

复制代码

​        3.搭配存储过程或者函数使用，结尾必须为end case，中间的比较语句也必须加分号，then 后面必须跟着语句使用。

1. create PROCEDURE test1111()
2. begin
3. case 10
4. when 10 then SELECT '10'; -- 这里必须为sql语句，而且要加分号
5. when 9 then SELECT '9'; -- 这里必须为sql语句，而且要加分号
6. else SELECT '8'; -- 这里必须为sql语句，而且要加分号
7. end case; -- 这里必须以end case;结尾
8. end;

复制代码

​

语法1：

1. CASE case_value
2.     WHEN when_value THEN statement_list
3.     [WHEN when_value THEN statement_list] ...
4.     [ELSE statement_list]
5. END CASE

复制代码

语法2：

1. CASE
2.     WHEN search_condition THEN statement_list
3.     [WHEN search_condition THEN statement_list] ...
4.     [ELSE statement_list]
5. END CASE

复制代码

区别：
语法1：匹配的是值，所以when里面不能是表达式。
语法2：匹配的是表达式，when里面是表达式。
if

语法：

1. IF search_condition THEN statement_list
2.     [ELSEIF search_condition THEN statement_list] ...
3.     [ELSE statement_list]
4. END IF

复制代码

突然发现这些流程控制语句好像大多数都得搭配函数/存储过程使用。

1. create PROCEDURE test_if()
2. BEGIN
3.         IF 3>2 THEN
4.         SELECT '正确';
5. ELSE
6.         SELECT '错误';
7. END IF;
9. end;
11. call test_if();

复制代码

while

语法：

1. [begin_label:] WHILE search_condition DO
2.     statement_list
3. END WHILE [end_label]

复制代码

示例代码：阶乘

1. CREATE FUNCTION get_jc(num int)
2. RETURNS int
3. BEGIN
4.         DECLARE i int DEFAULT 1;
5.         DECLARE res int DEFAULT 1;
6.        
7.         WHILE i <= num DO
8.                         set res = res * i;
9.                         set i = i + 1;
10.         END WHILE;
12. RETURN res;
13. end;
15. SELECT get_jc(3);

复制代码

运行结果：

[CURSOR]游标

这个东西，有点意思。

https://dev.mysql.com/doc/refman/8.0/en/cursors.html

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！