視圖

視圖（View）是一種虛擬存在的表怜珍，視圖中的數(shù)據(jù)并不在數(shù)據(jù)庫中實際存在端蛆，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表，并且是在使用視圖時自動生成的酥泛。

• 創(chuàng)建視圖：create [or replace] view 視圖名稱[(列名列表)] as select查詢語句;

• 查詢視圖：
  1今豆、查看創(chuàng)建視圖的語句：show create view 視圖名稱;
  2、查看視圖數(shù)據(jù)：select * from 視圖名稱;

• 修改視圖：
  1柔袁、create [or replace] view 視圖名稱[(列名列表)] as select查詢語句;
  2呆躲、alter view 視圖名稱[(列名列表)] as select查詢語句;

• 刪除視圖：drop view [if exist] 視圖名稱 [,視圖名稱...];

• 視圖檢查選項：當(dāng)使用with [cascaded / local] check option字句創(chuàng)建視圖時，在對視圖進行插入捶索、更新插掂、刪除這些操作時，MySQL 會通過視圖檢查來判斷要操作的數(shù)據(jù)是否符合創(chuàng)建視圖時select查詢語句中的條件腥例，不符合則無法插入辅甥。MySQL 允許基于另一個視圖創(chuàng)建視圖，它還會檢查依賴視圖中的規(guī)則來保持一致性燎竖，cascaded（默認(rèn)）璃弄、local 是 MySQL 用來確定檢查范圍的兩個選項：
  1、cascaded：如果創(chuàng)建視圖指定了該選項构回，那么在操作當(dāng)前視圖時會檢查當(dāng)前視圖的條件夏块、以及遞歸檢查當(dāng)前視圖依賴的所有視圖的條件，如果依賴的視圖沒有添加檢查選項纤掸，則會給強制添加上拨扶，保證所有視圖的檢查條件生效。
  2茁肠、local：會檢查當(dāng)前視圖的條件患民、以及嘗試遞歸檢查當(dāng)前視圖依賴的所有視圖的條件，但是如果依賴的視圖沒有添加檢查選項則不檢查它的視圖條件，也就是不會給其強制添加視圖檢查選項淹朋，只保證有檢查選項的視圖條件生效。

• 視圖更新：要讓視圖可以更新碧磅，視圖中的行與基礎(chǔ)表中的行之間必須存在一對一關(guān)系印蓖，如果視圖包含如下內(nèi)容辽慕，則不能更新：
  1、聚合函數(shù)
  2赦肃、distinct
  3溅蛉、group by
  4、having
  5他宛、union船侧、union all

• 視圖的作用：
  1、視圖不僅可以簡化用戶對數(shù)據(jù)的理解厅各，還可以簡化操作镜撩。那些經(jīng)常使用的查詢可以被定義為視圖，這樣不用為以后相同的操作每次指定全部查詢條件队塘，而是直接從視圖查詢袁梗。
  2、數(shù)據(jù)庫可以通過權(quán)限管理來指定用戶訪問那些庫那些表憔古，但不能授權(quán)用戶只訪問特定的行數(shù)據(jù)以及列遮怜，這些數(shù)據(jù)可以通過視圖來提供給用戶。
  3鸿市、視圖可以幫助用戶屏蔽真實的表結(jié)構(gòu)锯梁，用戶只關(guān)心需要的數(shù)據(jù)即可。

存儲過程

存儲過程是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段 SQL 語句的集合灸芳，調(diào)用存儲過程可以傳遞參數(shù)涝桅、以及返回結(jié)果數(shù)據(jù)，簡化了開發(fā)烙样、減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸冯遂，進而提高數(shù)據(jù)的處理效率。核心思想就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用谒获。

基本操作

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

create procedure 存儲過程名稱([參數(shù)列表])
begin
    -- sql 語句;
    -- sql 語句;
end;

上邊語句在命令行無法成功執(zhí)行蛤肌，因為 end 之前還有;，導(dǎo)致錯誤的識別 SQL 結(jié)束批狱，可以用如下方式：

-- 指定結(jié)束限定符裸准，這樣遇到 $$ 才認(rèn)為 SQL 結(jié)束
delimiter $$
create procedure 存儲過程名稱([in/out/inout 參數(shù)名 參數(shù)類型, ...])
begin
    -- sql 語句;
    -- sql 語句;
end$$
-- 指定 SQL 結(jié)束符為默認(rèn)的分號
delimiter ; 

• 調(diào)用存儲過程

call 存儲過程名稱([參數(shù)列表]);

• 查看存儲過程

-- 查詢指定數(shù)據(jù)庫的存儲過程以及狀態(tài)信息
select * from information_schema.routines where routine_schema = '數(shù)據(jù)庫名稱';

-- 查詢某個存儲過程的定義
show create procedure 存儲過程名稱;

• 刪除存儲過程

drop procedure [if exists] 存儲過程名稱;

變量

• 系統(tǒng)變量

-- 查看所有系統(tǒng)變量
show [global | session] variables;
-- 模糊查找系統(tǒng)變量
show [global | session] variables like '';
-- 查看指定系統(tǒng)變量，select @@session.autocommit; select @@autocommit;
select @@[global | session].變量名;

-- 設(shè)置系統(tǒng)變量
set  [global | session] 變量名 = 值;
-- set @@session.autocommit = 0; set @@autocommit = 0;
set  @@[global | session].變量名 = 值;

如果不指定 global赔硫、session炒俱，默認(rèn)是 session；設(shè)置系統(tǒng)變量指定為 global，MySQL 服務(wù)重啟后也會失效权悟。

• 用戶自定義變量
  用戶自定義變量不用提前聲明砸王，直接用@變量名使用即可，作用域為當(dāng)前連接峦阁。

-- 給變量賦值谦铃，推薦使用 :=
set @var_name = expr [, @var_name = expr]...;
set @var_name := expr [, @var_name := expr]...;
select @var_name := expr [, @var_name := expr]...;
select 字段名 into @var_name from 表名;

-- 使用變量，沒賦值的變量到的值是 null
select @var_name [, @var_name]...;

• 局部變量
  局部變量在訪問之前榔昔，需要使用declare聲明驹闰，可以作為存儲過程內(nèi)的局部變量和輸入?yún)?shù)，在begin end塊之內(nèi)有效撒会。

-- 聲明局部變量嘹朗，類型就是數(shù)據(jù)庫支持的類型
declare 變量名 變量類型 [default 默認(rèn)值];

-- 賦值
set 變量名 = 值;
set 變量名 := 值;
select 字段名 into 變量名 from 表名;

-- 取值
select 變量名;

條件判斷

• if

if 條件1 then
-- 可選
elseif 條件2 then     
-- 可選
else                  
end if;

• 參數(shù)
  1、in茧彤，默認(rèn)的類型骡显，標(biāo)記參數(shù)為輸入類型的疆栏，可以作為輸入?yún)?shù)
  2曾掂、out，標(biāo)記參數(shù)為輸出類型的壁顶，可以作為返回值
  3珠洗、inout，可以作為輸入?yún)?shù)也可以作為返回值

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

-- 調(diào)用存儲過程若专，使用用戶自定義變量接收結(jié)果
call p(70, @result);
select @result;

• case

create procedure p(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 && month <= 3 then set result = '第一季度';
        when month >= 4 && month <= 6 then set result = '第二季度';
        else set result = '其它';
    end case;
end;

循環(huán)

• while

while 條件 do
    sql 邏輯...
end while;

• repeat

repeat
    sql 邏輯...
    until 條件
end repeat;

• loop

[label:]loop
    sql 邏輯...
    [leave [label]; 退出循環(huán) | iterate [lable]; 直接進入下一次循環(huán)]
end loop [label];

條件處理程序

用來定義在流程控制結(jié)構(gòu)執(zhí)行過程中遇到問題時的處理辦法许蓖。
declare handler_action handler for condition_value [, condition_value...] statement;

• handler_action
  1、continue：繼續(xù)執(zhí)行當(dāng)前程序
  2调衰、exit：終止執(zhí)行當(dāng)前程序
• condition_value
  1膊爪、sqlstate 狀態(tài)碼
  2、sqlwarning：所有以01開頭的 sqlstate 代碼的簡寫
  3嚎莉、not found：所有以02開頭的 sqlstate 代碼的簡寫
  4米酬、sqlexception：所有沒被 sqlwarning、not found 捕獲的 sqlstate 代碼的簡寫

游標(biāo)

游標(biāo)（cursor）趋箩，用來存儲查詢結(jié)果集類型的數(shù)據(jù)赃额，在存儲過程和函數(shù)中可以使用游標(biāo)對結(jié)果集進行循環(huán)處理。

• 聲明游標(biāo)：declare 游標(biāo)名稱 cursor for 查詢語句;
• 打開游標(biāo)：open 游標(biāo)名稱;
• 獲取游標(biāo)記錄：fetch 游標(biāo)名稱 into 變量[, 變量...];
• 關(guān)閉游標(biāo)：close 游標(biāo)名稱;

create procedure p(in user_age int)
begin
    declare _name varchar(100);
    declare _profession varchar(100);
    -- 聲明游標(biāo)叫确，存儲查詢的結(jié)果集
    declare u_cursor cursor for select name, profession from user where age >= user_age;
    -- 聲明一個條件處理程序跳芳，當(dāng)狀態(tài)碼為0200時執(zhí)行退出操作、關(guān)閉游標(biāo)
    declare exit handler for sqlstate '0200' close u_cursor;
    -- 創(chuàng)建新表
    drop table if exists user2;
    create table user2(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100));
    -- 打開游標(biāo)
    open u_cursor;
    while true do
        -- 獲取游標(biāo)記錄
        fetch u_cursor into _name, _profession;
        -- 插入記錄到新表
        insert into user2 (name, profession) values (_name, _profession);
    end while;
    -- 關(guān)閉游標(biāo)
    close u_cursor;
end;

存儲函數(shù)

存儲函數(shù)是有返回值的存儲過程竹勉，存儲函數(shù)的參數(shù)只能是in類型的飞盆。

create function 存儲函數(shù)名稱([參數(shù)列表])
returns type [characteristic...]
begin
    sql語句;
    return ...;
end;

• type：返回值類型
• characteristic：
  1、deterministic：相同的輸入?yún)?shù)總是產(chǎn)生相同的結(jié)果
  2、no sql：不包含 sql 語句
  3吓歇、reads sql data：包含讀取數(shù)據(jù)的語句车胡，但不包含寫入數(shù)據(jù)的語句

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

select fum_sum(100);

觸發(fā)器

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象，指在 insert照瘾、update匈棘、delete 之前（before）或之后（after），觸發(fā)并執(zhí)行觸發(fā)器中定義的 SQL 語句集合析命。觸發(fā)器的這種特性可以幫助應(yīng)用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性主卫、記錄日志、數(shù)據(jù)校驗等操作鹃愤。

使用別名 old簇搅、new 來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，目前只支持行級觸發(fā)器（修改多少行記錄就觸發(fā)幾次）不支持語句級觸發(fā)器软吐。

• 創(chuàng)建

create trigger trigger_name
before/after insert/update/delete
on table_name for each row
begin
    trigger_statement;
end;

• 查看

show triggers;

• 刪除

-- 如果沒指定schema_name凹耙，默認(rèn)為當(dāng)前數(shù)據(jù)庫
drop trigger [schema_name.]trigger_name;

• 實例

-- 給 tb_user 表添加數(shù)據(jù)后姿现，自動給日志表添加日志：
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('insert', '張三', now(), new.content);
end;

-- 更新 tb_user 表數(shù)據(jù)后，自動給日志表添加日志：
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('update', '張三', now(), concat('更新前的數(shù)據(jù)', old.content, '肖抱；', '更新后的數(shù)據(jù)', new.content));
end;

-- 從 tb_user 表刪除數(shù)據(jù)后备典，自動給日志表添加日志：
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
    insert into tb_user_logs(operation, operator, time, content) values ('delete', '張三', now(), old.content);
end;