MySQL之視圖、儲儲過程、觸發(fā)器使用及說明

更新時(shí)間：2025年09月17日 14:43:26 作者：l_tian_tian_

文章介紹了MySQL中的視圖、存儲過程與觸發(fā)器：視圖是動態(tài)生成的虛擬表,用于簡化查詢和數(shù)據(jù)訪問控制；存儲過程是預(yù)編譯的SQL集合,提升數(shù)據(jù)處理效率；觸發(fā)器在表操作前后自動執(zhí)行,保障數(shù)據(jù)完整性與操作日志記錄

一、視圖

視圖是一種虛擬存在的表。視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中真實(shí)存在，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時(shí)動態(tài)生成的。

通俗的講，視圖只保存了查詢的SQL邏輯，不保存查詢結(jié)果。所以我們在建立視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語句上

創(chuàng)建或修改視圖語句:

查看創(chuàng)建視圖的語句：

show create view 視圖名稱;

查看視圖：

select * from 視圖名稱;

刪除視圖：

drop view if exists 視圖名稱;

with check option:當(dāng)使用with check option子句創(chuàng)建視圖時(shí)，MySQL會通過視圖檢查正在更改的每個(gè)行，例如插入，更新，刪除，以使其符合視圖定義。MySQL允許基另一個(gè)視圖創(chuàng)建視圖，它還會檢查依賴圖中的規(guī)則保持一致性。

為了確定檢查的范圍，mysql提供了兩個(gè)選項(xiàng)：CASCADED和LOCAL,默認(rèn)值為CASCADED。

CASCADED:v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時(shí)候指定了檢查選項(xiàng)為 cascaded，但是v1視圖創(chuàng)建時(shí)未指定檢查選項(xiàng)。則在執(zhí)行檢查時(shí)，不僅會檢查v2，還會級聯(lián)檢查v2的關(guān)聯(lián)視圖v1。
LOCAL：v2視圖是基于v1視圖的，如果在v2視圖創(chuàng)建的時(shí)候指定了檢查選項(xiàng)為 local ，但是v1視圖創(chuàng) 建時(shí)未指定檢查選項(xiàng)。則在執(zhí)行檢查時(shí)，知會檢查v2，不會檢查v2的關(guān)聯(lián)視圖v1。

無法更新的視圖：

作用：

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

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

3.視圖可幫助用戶屏蔽真實(shí)表結(jié)構(gòu)變化帶來的影響

二、存儲過程

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

創(chuàng)建存儲過程：

調(diào)用：

查詢指定數(shù)據(jù)庫的存儲過程及狀態(tài)信息：

select * from information_schema.ROUTINES where ROUTINE_SCHEMA=數(shù)據(jù)庫名稱;

查詢存儲過程定義：

show create procedure 存儲過程名稱;

刪除：

drop procedure if exists 存儲過程名稱;

注意：在命令行中，執(zhí)行創(chuàng)建存儲過程的SQL時(shí)，需要通過關(guān)鍵字delimiter指定SQL語句的結(jié)束符

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

查看所有系統(tǒng)變量：

show global /session variables ;

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

show global variables like 模糊匹配類型;

查看指定變量：

select @@global.autocommit;

設(shè)置系統(tǒng)變量：

set session autocommit=1;

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

用戶定義變量：是用戶根據(jù)需要自己定義的變量，用戶變量不用提前聲明，在用的時(shí)候直接用“@ 變量名”使用就可以。其作用域?yàn)楫?dāng)前連接。

賦值：

set @name:='李天天';

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

查找：

select @name;

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

局部變量：是根據(jù)需要定義的在局部失效的變量，訪問之前，需要DECLARE聲明。可用作存儲過程內(nèi)的局部變量和輸入?yún)?shù)，局部變量的范圍是在其內(nèi)聲明的BEGIN...END塊

聲明：

 declare total int default 0;

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

賦值：

set total:=90;

 select sn  into total from tb_sku where id=1;

if:

create procedure p1()
begin
    declare score int default 44;
    declare result char(10);
    if score>=80 then
        set result:='優(yōu)秀';
    elseif score>=60 then
        set result:='良好';
    else
        set result:='不及格';
    end if;
    select result;
end;

參數(shù)：

示例：

create procedure p2(in score int,out result char(10))
begin
    if score>=80 then
        set result:='優(yōu)秀';
    elseif score>=60 then
        set result:='良好';
    else
        set result:='不及格';
    end if;
end;

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

case:

示例：

begin
    declare season varchar(20);
    case
        when 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('當(dāng)前月份為：', month, ';當(dāng)前季度為:', season);

end;

while:

示例：

create procedure p2(in n int)
begin
    declare total int default 0;

    while n > 0
        do
            set total := total + n;
            set n = n - 1;
        end while;
    select concat('累加值為:',total);
end;

repeat:

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

end;

loop:

loop實(shí)現(xiàn)簡單的循環(huán)，如果不在SQL邏輯中增加退出循環(huán)條件，可以用其來實(shí)現(xiàn)簡單的死循環(huán)。loop可以配合一下兩個(gè)語句使用：

LEAVE:配合循環(huán)使用，退出循環(huán)

ITERATE：必須用在循環(huán)中，作用是跳過當(dāng)前循環(huán)剩下的語句，直接進(jìn)入下一次循環(huán)

create procedure p4(in n int)
begin
    declare total int default 0;
    sum :
    loop
        if n <= 0 then
            leave sum;
        end if;
        set total = total + n;
        set n = n - 1;
    end loop sum;
    select total;
end;

create procedure p6(in n int)
begin
    declare total int default 0;
    sum :
    loop
        if n <= 0 then
            leave sum;
        end if;
        if n % 2 = 1 then
            set n = n - 1;
            iterate sum;

        else
            set total = total + n;
            set n = n - 1;
        end if;

    end loop sum;
    select total;
end;

游標(biāo)：

游標(biāo)是用來存儲查詢結(jié)果集的數(shù)據(jù)類型，在存儲過程和函數(shù)中可以使用游標(biāo)對結(jié)果集進(jìn)行循環(huán)的處理。游標(biāo)的使用包括游標(biāo)的聲明、open、fetch和clode。

聲明游標(biāo)：

打開游標(biāo)：

獲取游標(biāo)記錄：

關(guān)閉游標(biāo)：

條件處理程序：可以用來定義在流程控制結(jié)構(gòu)執(zhí)行過程中遇到問題時(shí)相應(yīng)的處理步驟

mysql異常狀態(tài)碼文檔：

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

示例：

create procedure p7()
begin
    declare 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 true
        do
            fetch 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;

存儲函數(shù)：

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

示例：

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

三、觸發(fā)器

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象，指在insert/update/delete之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合。觸發(fā)器的這種特性可以協(xié)同應(yīng)用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性，日志記錄，數(shù)據(jù)校驗(yàn)等操作。

使用別名OLD和NEW來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，這與其他數(shù)據(jù)庫是相似的?，F(xiàn)在數(shù)據(jù)庫還只支持行級觸發(fā)，不支持語句觸發(fā)。

創(chuàng)建：

查看：

刪除：

insert:

create trigger tb_student_insert_trigger
    after insert on student for each row
    begin
        insert into user_logs (operation, operate_time, operate_id, operate_params) values
        ('insert',now(),new.id,concat('插入的數(shù)據(jù)內(nèi)容為:','id為',new.id,'name為',new.name,'no為',NEW.no));
    end;

deleter:

create trigger tb_student_deleter_trigger
    after delete on student for each row
begin
    insert into user_logs (operation, operate_time, operate_id, operate_params) values
        ('deleter',now(),old.id,concat('刪除的數(shù)據(jù)內(nèi)容為:','id為',old.id,'name為',old.name,'no為',old.no));
end;

update:

create trigger tb_student_update_trigger
    after update on student for each row
begin
    insert into user_logs (operation, operate_time, operate_id, operate_params) values
        ('update',now(),old.id,concat('更新前數(shù)據(jù)內(nèi)容為:','id為',old.id,'name為',old.name,'no為',old.no,'|','更新后的數(shù)據(jù)內(nèi)容為:','id為',new.id,'name為',new.name,'no為',new.no));
end;