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

视图的增删改查

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

存储过程的练习

创建存储过程：

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

查询存储过程：

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

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

运行结果：

删除存储过程：

drop PROCEDURE pr_test;

运行结果：

存储过程的练习：

-- 存储过程的练习 无参数
create PROCEDURE pr_test()
BEGIN
	select * from dept;
END;

-- 使用存储过程
call pr_test();

-- 存储过程 带有输入参数
-- 如果只有一个输入参数 关键字in 可以省略
create PROCEDURE pr_test1(_deptno int)
BEGIN
	SELECT * from emp WHERE deptno = _deptno;
END;

call pr_test1(10);
call pr_test1(20);
call pr_test1(30);

-- 存储过程  带有输出参数
-- 输出参数的out关键字不可以省略 

create PROCEDURE pr_test2(out _ename VARCHAR(50))
BEGIN
	SELECT ename into _ename from emp;
END;

-- 测试
set @ename = '';

-- 测试 如果包含多条数据 是不能直接进行赋值的
call pr_test2(@ename) ;

SELECT @ename;

-- 测试存储过程 单个输出参数
CREATE PROCEDURE pr_test3(out _ename VARCHAR(50))
BEGIN
	SELECT ename INTO _ename from emp WHERE emp.deptno = 10;
END;

-- 输出参数
set @deptno = '';

-- 执行存储过程
call pr_test3(@deptno);

-- 输出结果
SELECT @deptno;


-- 测试存储过程 有输入参数 也有输出参数
-- 关键字in 可以省略 out 不可以省略
create PROCEDURE pr_test5(_id int,out _ename varchar(20))
BEGIN
	SELECT ename into _ename from emp WHERE deptno = _id;
END;

-- 测试数据
set @result = '';

-- 
call pr_test5(10,@result);

-- 查询结果
SELECT @result;


-- 测试存储过程 即使输入参数 也是输出参数
CREATE PROCEDURE pr_test6(INOUT res VARCHAR(20) )
BEGIN
	SELECT emp.deptno into res from emp where emp.empno = res;
END;

SELECT * from emp;

-- 
set @res = '7876';

-- 
call pr_test6(@res);

-- 
SELECT @res;

函数的使用、查看、删除

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

-- 使用函数
SELECT fun_show_detail()
-- 删除函数
drop FUNCTION fun_show_detail;

触发器

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

show TRIGGERS from emp;

删除触发器：

drop TRIGGER tr_emp_update

触发器练习：

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

执行结果：

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

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

执行sql:

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

再次执行：

所以如果是before xxx，不会直接进行增删改，会执行触发器里面的代码。

关于触发器的new虚拟表格和old虚拟表格

当进行insert的时候，new表格可以获取插入的数据。

当进行delete的时候，old表格可以获取被删除行的数据。

当进行update的时候，new表格中是修改后的数据，old表格中是被修改行的数据。

定时任务event

定时任务 执行一次

语法：

CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE  AT timestamp [+ INTERVAL interval] ...
    
DO 
begin
 # 要执行的sql语句
end;

间隔任务 多次执行

语法：

CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE  EVERY  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
    
DO 
begin
 # 要执行的sql语句
end;

查询定时任务

show EVENTS FROM emp;

删除定时任务

drop event event_name

流程控制

case

给我整懵了，怎么测试怎么出错。

出错原因：

​ 1.case语句不能直接使用，需要搭配存储过程或者函数使用，或者搭配select语句使用。

​ 2.搭配select语句，里面每个语句结束后不能添加分号，结尾语句必须为end，不能为end case;

SELECT case 10
when 10 then  '10' -- 这里不能加分号
when 9 then  '9' -- 这里不能加分号
else  '8'
end; -- 这里不能用end case;结尾

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

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

​

语法1：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

语法2：

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

区别：

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

语法2：匹配的是表达式，when里面是表达式。

if

语法：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

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

create PROCEDURE test_if()
BEGIN
	IF 3>2 THEN
	SELECT '正确';
ELSE
	SELECT '错误';
END IF;

end;

call test_if();

while

语法：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

示例代码：阶乘

CREATE FUNCTION get_jc(num int)
RETURNS int
BEGIN
	DECLARE i int DEFAULT 1;
	DECLARE res int DEFAULT 1;
	
	WHILE i <= num DO
			set res = res * i;
			set i = i + 1;
	END WHILE;

RETURN res;
end;

SELECT get_jc(3);

运行结果：

loop

语法：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

示例代码：阶乘

create FUNCTION test_jc(num int)
RETURNS int
BEGIN
	DECLARE i int DEFAULT 1;
	DECLARE res int DEFAULT 1;
	
	lab: LOOP

	IF i > num THEN
		LEAVE lab; 
	END IF; 
	
	set res = res * i;
	set i = i + 1;
	
	END LOOP;

	RETURN res;
END;

select test_jc(4);

运行结果：

[CURSOR]游标

这个东西，有点意思。

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