MySQL——視圖、儲儲過程、觸發器

一、視圖

二、存儲過程

三、觸發器

一、視圖

視圖是一種虛擬存在的表。視圖中的數據并不在數據庫中真實存在，行和列數據來自定義視圖的查詢中使用的表，并且是在使用視圖時動態生成的。通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結果。所以我們在建立視圖的時候，主要的工作就落在創建這條SQL查詢語句上

創建或修改視圖語句:

查看創建視圖的語句：

show create view 視圖名稱;

查看視圖：

select * from 視圖名稱;

刪除視圖：

drop view if exists 視圖名稱;

with check option:當使用with check option子句創建視圖時，MySQL會通過視圖檢查正在更改的每個行，例如插入，更新，刪除，以使其符合視圖定義。MySQL允許基另一個視圖創建視圖，它還會檢查依賴圖中的規則保持一致性。為了確定檢查的范圍，mysql提供了兩個選項：CASCADED和LOCAL,默認值為CASCADED。

CASCADED:v2視圖是基于v1視圖的，如果在v2視圖創建的時候指定了檢查選項為 cascaded，但是v1視圖創建時未指定檢查選項。則在執行檢查時，不僅會檢查v2，還會級聯檢查v2的關聯視圖v1。

LOCAL：v2視圖是基于v1視圖的，如果在v2視圖創建的時候指定了檢查選項為 local ，但是v1視圖創建時未指定檢查選項。則在執行檢查時，知會檢查v2，不會檢查v2的關聯視圖v1。

無法更新的視圖：

作用：

1.視圖不僅可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖，從而使用戶不必以后操作每次指定全部條件

2.數據庫可以授權，但不能授權到數據庫特定行和特定的列上。通過視圖用戶只能查詢和修改他們所能見到的數據

3.視圖可幫助用戶屏蔽真實表結構變化帶來的影響

二、存儲過程

存儲過程是事先經過編譯并存儲在數據庫中的一段SQL語句的集合，調用存儲過程可以簡化應用開發人員的很多工作，減少數據在數據庫和應用服務器之間的傳輸，對于提高數據處理的效率是有好處的

創建存儲過程：

調用：

查詢指定數據庫的存儲過程及狀態信息：

select * from information_schema.ROUTINES where ROUTINE_SCHEMA=數據庫名稱;

查詢存儲過程定義：

show create procedure 存儲過程名稱;

刪除：

drop procedure if exists 存儲過程名稱;

注意：在命令行中，執行創建存儲過程的SQL時，需要通過關鍵字delimiter指定SQL語句的結束符

系統變量：是MySQL服務器提供，不是用戶定義的，屬于服務器層面。分為全局變量(GLOBAL)、會話變量(SESSION)

查看所有系統變量：

show global /session variables ;

通過like模糊匹配方式查找變量：

show global variables like 模糊匹配類型;

查看指定變量：

select @@global.autocommit;

設置系統變量：

set session autocommit=1;

注意：如果沒有指定SESSION/GLOBAL，默認是SESSION，會話變量。mysql服務重新啟動之后，所設置的全局參數會失效，想要不失效，可在/etc/my.cnf中配置

用戶定義變量：是用戶根據需要自己定義的變量，用戶變量不用提前聲明，在用的時候直接用“@ 變量名”使用就可以。其作用域為當前連接。

賦值：
?

set @name:='李天天';

select sn  into @sn_1 from tb_sku where id=1;

查找：

select @name;

注意：用戶定義的變量無需對其進行聲明或初始化，只不過獲取到地值未NULL

局部變量：是根據需要定義的在局部失效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程內的局部變量和輸入參數，局部變量的范圍是在其內聲明的BEGIN...END塊

聲明：

 declare total int default 0;

注意：變量類型就是數據庫字段類型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

賦值：

set total:=90;

 select sn  into total from tb_sku where id=1;

if:

create procedure p1()
begindeclare score int default 44;declare result char(10);if score>=80 thenset result:='優秀';elseif score>=60 thenset result:='良好';elseset result:='不及格';end if;select result;
end;

參數：

示例：

create procedure p2(in score int,out result char(10))
beginif score>=80 thenset result:='優秀';elseif score>=60 thenset result:='良好';elseset result:='不及格';end if;
end;


create procedure p3(inout score double)
beginset score:=score*0.5;
end;
set @result=89;
call p3(@result);
select @result;

case:

示例：

begindeclare season varchar(20);casewhen month >= 1 and month <= 3 then set season = '一季度';when month >= 4 and month <= 6 then set season = '二季度';when month >= 7 and month <= 9 then set season = '三季度';when month >= 10 and month <= 12 then set season = '四季度';else set season = '輸入錯誤';end case;select concat('當前月份為：', month, ';當前季度為:', season);end;

while:

示例：

create procedure p2(in n int)
begindeclare total int default 0;while n > 0doset total := total + n;set n = n - 1;end while;select concat('累加值為:',total);
end;

repeat:

create procedure p3(in n int)
begindeclare total int default 0;repeatset total := total + n;set n = n - 1;until n <= 0end repeat;select concat('累加值為:', total);end;

loop:

loop實現簡單的循環，如果不在SQL邏輯中增加退出循環條件，可以用其來實現簡單的死循環。loop可以配合一下兩個語句使用：

LEAVE:配合循環使用，退出循環

ITERATE：必須用在循環中，作用是跳過當前循環剩下的語句，直接進入下一次循環

create procedure p4(in n int)
begindeclare total int default 0;sum :loopif n <= 0 thenleave sum;end if;set total = total + n;set n = n - 1;end loop sum;select total;
end;

create procedure p6(in n int)
begindeclare total int default 0;sum :loopif n <= 0 thenleave sum;end if;if n % 2 = 1 thenset n = n - 1;iterate sum;elseset total = total + n;set n = n - 1;end if;end loop sum;select total;
end;

游標：

游標是用來存儲查詢結果集的數據類型，在存儲過程和函數中可以使用游標對結果集進行循環的處理。游標的使用包括游標的聲明、open、fetch和clode。

聲明游標：

打開游標：

獲取游標記錄：

關閉游標：

條件處理程序：可以用來定義在流程控制結構執行過程中遇到問題時相應的處理步驟

mysql異常狀態碼文檔：

https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

示例：

create procedure p7()
begindeclare new_name varchar(10) default null;declare new_no varchar(10) default null;declare student_name cursor for select no,name from student;declare exit  handler for SQLSTATE '02000' close student_name;open student_name;create table if not exists tb_sku_name_price_100(id   int primary key auto_increment,no  varchar(10),name varchar(10));while truedofetch student_name into new_no ,new_name ;insert into tb_sku_name_price_100 values (null,new_no,new_name);end while;close student_name;
end;

存儲函數：

存儲函數是有返回值的存儲過程，存儲函數的參數只能是IN類型

示例：

create function sun(n int)
returns int deterministic
begindeclare total int default 0;while n>0 doset total:=n+total;set n:=n-1;end while;return total;
end;

三、觸發器

觸發器是與表有關的數據庫對象，指在insert/update/delete之前或之后，觸發并執行觸發器中定義的SQL語句集合。觸發器的這種特性可以協同應用在數據庫端確保數據的完整性，日志記錄，數據校驗等操作。

使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他數據庫是相似的。現在數據庫還只支持行級觸發，不支持語句觸發。

創建：

查看：

刪除：

insert:

create trigger tb_student_insert_triggerafter insert on student for each rowbegininsert into user_logs (operation, operate_time, operate_id, operate_params) values('insert',now(),new.id,concat('插入的數據內容為:','id為',new.id,'name為',new.name,'no為',NEW.no));end;

deleter:

create trigger tb_student_deleter_triggerafter delete on student for each row
begininsert into user_logs (operation, operate_time, operate_id, operate_params) values('deleter',now(),old.id,concat('刪除的數據內容為:','id為',old.id,'name為',old.name,'no為',old.no));
end;

update:

create trigger tb_student_update_triggerafter update on student for each row
begininsert into user_logs (operation, operate_time, operate_id, operate_params) values('update',now(),old.id,concat('更新前數據內容為:','id為',old.id,'name為',old.name,'no為',old.no,'|','更新后的數據內容為:','id為',new.id,'name為',new.name,'no為',new.no));
end;