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

標籤：MySQL

上一篇：智商狂飆，問了ChatGPT幾個資料庫問題后，我的眼鏡掉了

下一篇：一文詳解數倉GaussDB(DWS) 函式出參帶出方式