MySQL強化關鍵_015

目? 錄

一、概述

1.說明

2.優點

3.缺點?

二、存儲過程的操作

1.創建

2.調用

3.查看

4.刪除

三、變量

1.系統變量

（1）說明

（2）查看系統變量?

（3）設置系統變量

2.用戶變量

（1）說明

（2）用戶變量賦值

（3）讀取用戶變量

?3.局部變量

（1）說明

（2）聲明、賦值、讀取與調用

?四、IF

1.語法格式

2.實例

五、參數

1.三種類型

2.in、out 實例?

3.inout 實例

?六、CASE

1.語法格式1

2.語法格式2

3.實例

七、循環

1.WHILE 循環

（1）語法格式

（2）實例

2.REPEAT 循環

（1）語法格式

（2）實例?

3.?LOOP 循環

（1）語法格式

（2）leave 實例?

（3）iterate 實例

八、游標 cursor

1.說明

2.步驟

（1）聲明

（2）開啟

（3）獲取

（4）關閉

3.實例?

?九、捕捉并處理異常

1.說明

2.實例?

十、存儲函數

1.說明

2.語法格式

3.實例

十一、觸發器

1.說明

2.語法規則?

3.關鍵字 new、old

4.實例

一、概述

1.說明

存儲過程即過程化 SQL 語言，是在普通 SQL 語句上增加了編程語言的特點，將 DML 和 DQL 組織在過程化代碼中，通過邏輯判斷、循環等實現復雜計算的程序語言；
存儲過程有自己的變量、條件判斷、循環語句等，一個存儲過程中，可以將多條 SQL 語句以邏輯代碼的方式將其串聯，所以一個存儲過程可以看作是為了完成特定任務的 SQL 語句集合；
每一個存儲過程就是一個數據庫對象，和 table、view一樣存儲在數據庫當中，一次編譯永久有效。每個存儲過程都有自己的名稱，可以通過存儲過程的名稱調用；
在數據量龐大的情況下，利用存儲過程可以提升效率；
實際開發中，只有在需要進行性能優化時考慮使用。

2.優點

? ? ? ? 速度快。降低了應用服務器和數據庫服務器之間的網絡通訊開銷。

3.缺點?

? ? ? ? 移植性差、編寫難度大、維護性差。每一個數據庫管理系統都有獨特的存儲過程語法規則，一旦使用了存儲過程，較難更換數據庫產品。且對于數據庫存儲過程語法，沒有專業的 IDE，編碼效率較低，維護成本較高。

二、存儲過程的操作

# 為了演示存儲過程的一系列操作，首先初始化
drop table if exists human;
create table human(id int not null auto_increment,name varchar(10),gender char(2) default '未知',age int,phone varchar(20),primary key (id)
);
insert into human(name, gender, age, phone) values('王磊', '男', 18, '13328345217'),('劉穎', '女', 23, '17728589999'),('周子恩', '女', 21, '11253467846');

? ? ? ? 在 dos 命令窗口下，MySQL 遇到【;】將結束輸入，?所以在創建存儲過程時會報錯。

? ? ? ? 此時，需要使用【delimiter <符號>】更改結束符。為方便操作，以下操作均在?Navicat 里進行。

1.創建

create procedure p()
beginselect name, age, gender, phone from human;
end;

2.調用

call p();

3.查看

系統表 information_schema.routines 存儲了存儲過程、函數對象、觸發器對象等狀態信息；
SPECIFIC_NAME：存儲過程的具體名稱，包括名稱和參數列表；
ROUTINE_SCHEMA：存儲過程所在的數據庫名稱；
ROUTINE_NAME：存儲過程名稱；
ROUTINE_TYPE：PROCEDURE 表示存儲過程，FUNCTION 表示一個函數；
ROUTINE_DEFINITION：存儲過程定義語言；
CREATED：存儲過程創建時間；
LAST_ALTERED：存儲過程最后修改時間；
DATA_TYPE：存儲過程返回值類型、參數類型等。

select SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION, CREATED, LAST_ALTERED, DATA_TYPE 
from information_schema.ROUTINES 
where ROUTINE_NAME = 'p';

4.刪除

drop procedure if exists p;

三、變量

1.系統變量

（1）說明

MySQL 系統變量是 MySQL 服務器運行時控制行為的參數，可以被設置為特定值從而改變服務器的默認設置；
系統變量有全局作用域和會話作用域。全局作用域對所有連接和所有數據庫都適用，會話作用域只對當前連接和當前數據庫適用。

（2）查看系統變量?

語法格式：
【show?[ global | session ] variables;】；
【show [ global | session ] variables like ' ';】；
【select @@[ global | session ].系統變量名;】。

未指定 global 和 session 時，默認是 session。

（3）設置系統變量

語法格式：
【set [ global | session ] 系統變量名 = 值;】；
【set @@[ global | session ].系統變量名 = 值;】。

無論是全局設置還是會話設置，MySQL 服務器重啟之后，之前的配置都會失效。可以通過修改 MySQL 安裝根目錄下的 my.ini?配置文件實現永久修改；
my.ini 默認不存在，需要新建。在 Windows 系統下，文件后綴是【.ini】，在 Linux 系統下，文件后綴是【.cnf】。

2.用戶變量

（1）說明

用戶自定義的變量；
只對當前會話有效；
所有用戶變量以【@】開始。

（2）用戶變量賦值

語法格式：
【set @用戶變量名 = 值;】；
【set @用戶變量名 := 值;】（推薦使用“:”形式）；
【set @用戶變量名 := 值, @用戶變量名 := 值;】；
【select @用戶變量名 := 值;】；
【select 字段名 into @用戶變量名 from 表名 where 條件;】。

MySQL 中用戶變量無需聲明，可直接賦值。若未聲明直接讀取，則返回 NULL。

set @id = 1;
set @`name` := '張鵬';
set @age := 23;
set @gender = '男', @phone = '13325678910';

（3）讀取用戶變量

? ? ? ? 語法格式：【select @變量名1,??@變量名2, …;】。

select @id, @`name`, @age, @gender, @phone;

?3.局部變量

（1）說明

存儲過程可以使用局部變量，使用【declare】聲明，在 begin 和 end 之間有效；
局部變量只在存儲過程中有效。

（2）聲明、賦值、讀取與調用

語法格式：【declare 變量名數據類型 [default …];】；
declare 通常出現在 begin 和 end 之間。

drop procedure if exists p1;
create procedure p1()
begin-- 1.聲明declare name varchar(10) default '佚名';declare age int default 0;-- 2.賦值set name := '徐佳禾';set age := 22;-- 3.讀取select name, age;
end;-- 4.調用
call p1();

?四、IF

1.語法格式

? ? ? ? if [條件1] then [分支1]

? ? ? ? elseif [條件2] then [分支2]

????????elseif [條件3] then [分支3]

? ? ? ? else [分支4]

? ? ? ? end if；

2.實例

????????員工月薪高于 10000 屬于“高收入”，6000 ~ 10000 屬于中收入，低于 6000 屬于低收入。查詢員工薪資收入級別。

drop procedure if exists p2;create procedure p2()
begin -- 聲明一個局部變量，存儲月薪declare sal int default 0;-- 聲明一個局部變量，存儲薪資等級declare gra varchar(3);-- 賦值月薪set sal := 6000;if sal > 10000 then set gra := '高收入';elseif sal between 6000 and 10000 thenset gra := '中收入';elseset gra := '低收入';end if;select gra;
end;call p2();

五、參數

1.三種類型

in：入參，接收調用者傳入數據。未指定時默認為 in；
out：出參，保存存儲過程執行結果；
inout：即是入參也是出餐。

2.in、out 實例?

? ? ? ? 上方使用 if 語句判斷薪資等級，每次修改月薪需要在存儲過程中進行，如此不利于維護。可以使用參數改進。

drop procedure if exists p3;create procedure p3(in sal int, out gra varchar(3))
beginifsal > 10000thenset gra := '高收入';elseifsal < 6000thenset gra := '低收入';elseset gra := '中收入';end if;
end;call p3(10001, @grade);select @grade;

3.inout 實例

? ? ? ? 將傳入的薪資上調 10%。?

drop procedure if exists p4;create procedure p4(inout sal int)
begin set sal := sal * 1.1;
end;set @sal := 100;
call p4(@sal);
select @sal;

?六、CASE

1.語法格式1

????????case 匹配條件

? ? ? ? ????????when [值1] then [處理1]

????????????????when [值2] then [處理2]

? ? ? ? ????????else [處理3]

? ? ? ? end case;

2.語法格式2

? ? ? ? case

? ? ? ? ? ? ? ? when [條件1]?then [處理1]

????????????????when [條件1]?then [處理1]

????????????????else [處理3]

? ? ? ? end case;

3.實例

? ? ? ? 3、4、5 月為春季，6、7、8 月為夏季，9，10，11 月為秋季，12，1，2 月為冬季。根據月份輸出季節，其他輸入為非法輸入。?

-- 語法1
drop procedure if exists p5;
create procedure p5(in month int, out result char(2))
begincase monthwhen 3 then set result := '春季';when 4 then set result := '春季';when 5 then set result := '春季';when 6 then set result := '夏季';when 7 then set result := '夏季';when 8 then set result := '夏季';when 9 then set result := '秋季';when 10 then set result := '秋季';when 11 then set result := '秋季';when 12 then set result := '冬季';when 1 then set result := '冬季';when 2 then set result := '冬季';else set result := '非法';end case;
end;call p5(1, @season);
select @season;-- 語法2
drop procedure if exists p6;
create procedure p6(in month int, out result char(2))
begincasewhen month between 3 and 5 then set result := '春季';when month between 6 and 8 then set result := '夏季';when month between 9 and 11 then set result := '秋季';when month = 12 or month between 1 and 2 then set result := '冬季';else set result := '非法';end case;
end;call p6(6, @season);
select @season;

七、循環

1.WHILE 循環

（1）語法格式

? ? ? ? while [條件] do

????????????????-- 循環體

? ? ? ? end while;

（2）實例

? ? ? ? 傳入整數 n，計算 1 ~ n 中所有偶數和。?

drop procedure if exists p7;create procedure p7(in n int)
begindeclare sum int default 0;while n > 0doif n % 2 = 0then set sum := sum + n;end if;set n := n - 1;end while;select sum;
end;call p7(10);

2.REPEAT 循環

（1）語法格式

? ? ? ? ?repeat

? ? ? ? ? ? ? ? -- 循環體

? ? ? ? ? ? ? ? until 條件

? ? ? ? end repeat;

（2）實例?

????????傳入整數 n，計算 1 ~ n 中所有偶數和。?

drop procedure if exists p8;create procedure p8(in n int)
begindeclare sum int default 0;repeatif n % 2 = 0then set sum := sum + n;end if;set n := n - 1;until n <= 0end repeat;select sum;
end;call p8(10);

3.?LOOP 循環

（1）語法格式

? ? ? ? 循環名:loop

? ? ? ? ? ? ? ? -- 循環體

? ? ? ? ? ? ? ? [ leave \ iterate ]?循環名;

? ? ? ? end loop;

# leave：類似于 Java 中的 break，結束當前循環；

# iterate：類似于 Java 中的 continue，結束本次循環。

（2）leave 實例?

? ? ? ? 輸出 1 ~ 6。

drop procedure if exists p9;create procedure p9()
begindeclare i int default 0;num:loopset i := i + 1;if i = 7then leave num;end if;select i;end loop;
end;call p9();

（3）iterate 實例

????????輸出 1、2、3、4、6、7、8、9。

drop procedure if exists p10;create procedure p10()
begindeclare i int default 0;num:loopset i := i + 1;if i = 11then leave num;elseif i = 5then iterate num;elseif i = 10then iterate num;end if;select i;end loop;
end;call p10();

八、游標 cursor

1.說明

游標是指向結果集中某條記錄的指針，允許程序逐個訪問結果集中的每條記錄，并進行逐行操作；
使用游標需要在存儲過程或函數中定義一個游標變量，并通過【declare】進行聲明和初始化；
使用【open】開啟游標；
使用【fetch】逐行獲取游標指向的記錄并處理；
最后使用【close】關閉游標，釋放資源；
聲明游標的語句必須在聲明普通變量的下方。

2.步驟

（1）聲明

? ? ? ? declare 游標名稱 cursor for 查詢語句;

（2）開啟

? ? ? ? ?open 游標名稱;

（3）獲取

? ? ? ? fetch 游標名稱 into 變量1, 變量2, …;?

（4）關閉

? ? ? ? close 游標名稱;?

3.實例?

? ? ? ? 從二中的 human 表中查詢 name 和 gender，并插入一張新表 human_summary。

drop procedure if exists p11;create procedure p11()
begin-- 聲明變量declare human_name varchar(10);declare human_gender char(2);-- 聲明游標declare human_cursor cursor for select `name`, gender from human;-- 新建 human_summary 表drop table if exists human_summary;create table human_summary(id int primary key auto_increment,`name` varchar(10),gender char(2) default '未知');-- 開啟游標open human_cursor;-- 循環獲取數據while true dofetch human_cursor into human_name, human_gender;-- 插入數據insert into human_summary(`name`, gender) values(human_name, human_gender);end while;-- 關閉游標close human_cursor;
end;call p11();

?九、捕捉并處理異常

1.說明

語法格式：【declare [ 異常處理程序名 ] handler for [ SQL狀態碼 ] [ 執行語句 ];】；
異常處理程序：
continue：發生異常后，程序不終止，正常執行后續過程。即捕捉；
exit：發生異常后，終止存儲過程的執行。即上拋。

SQL 狀態碼：
可以填具體的數字狀態碼；
SQLWARNING：代表所有 01 開始的 SQL 狀態碼；
NOT FOUND：代表所有 02 開始的 SQL 狀態碼；
SQLEXCEPTION：代表所有除 01、02 開始的 SQL 狀態碼。

執行語句：異常發生時執行的語句。

2.實例?

? ? ? ? 上述游標中的實例，執行時會報錯，因為循環是死循環。

????????那么，如何改進呢？

drop procedure if exists p11;create procedure p11()
begindeclare human_name varchar(10);declare human_gender char(2);declare human_cursor cursor for select `name`, gender from human;-- 異常處理，在發生未發現異常時終止并關閉游標declare exit handler for not found close human_cursor;drop table if exists human_summary;create table human_summary(id int primary key auto_increment,`name` varchar(10),gender char(2) default '未知');open human_cursor;while true dofetch human_cursor into human_name, human_gender;insert into human_summary(`name`, gender) values(human_name, human_gender);end while;close human_cursor;
end;call p11();

十、存儲函數

1.說明

存儲函數是帶有返回值的存儲過程；
參數只能是 in，但不能顯示地寫 in。沒有 out 和 inout。

2.語法格式

????????create function 存儲函數名稱(參數列表) returns 數據類型 [ 特征 ]

? ? ? ? begin

? ? ? ? ? ? ? ? -- 函數體

? ? ? ? return …;

? ? ? ? end;

# 特征重要取值：

deterministic：標記該函數為確定性函數，即每次調用函數時，傳入相同的參數，返回值是固定的。這是一種優化策略，此情況下全部函數體的執行會省略，直接返回之前緩存的結果，提高函數的執行效率；
no sql：標記該函數執行過程不會查詢數據庫，以向 MySQL 優化器表示不需要使用查詢緩存和優化器緩存優化。避免不必要的查詢消耗；
reads sql data：標記該函數會進行查詢操作，以向 MySQL 優化器表示該函數需要查詢數據庫，可以使用查詢緩存優化。同時 MySQL 還會進行優化器緩存處理。

3.實例

? ? ? ? 傳入整數 n，計算 1 ~ n 中所有偶數和。

drop function if exists f;create function f(n int) returns int deterministic
begindeclare sum int default 0;while n > 0 doif n % 2 = 0 then set sum := sum + n;end if;set n := n - 1;end while;return sum;
end;set @result := f(10);
select @result;

十一、觸發器

1.說明

MySQL 觸發器是一種數據庫對象，是與表相關聯的特殊程序；

可以在插入、更新、刪除觸發時自動執行；

作用：

強制實施業務規則：可以確保表中業務規則強制執行；

數據審計：可以聲明在執行數據修改時自動記錄日志或審計數據變化的操作；

執行特定業務操作：可以自動執行特定業務操作。

分類：

before：在執行 insert、update、delete 語句之前執行；

after：在執行 insert、update、delete 語句之后執行。

觸發器是一種數據庫高級功能，只在必要條件下使用。過量的觸發器和復雜的觸發器邏輯可能會影響查詢性能和擴展性。

2.語法規則?

? ? ? ? create trigger 觸發器名稱 [ before \ after ] [ insert \ update \ delete ] on 表名 for each row

? ? ? ? begin

? ? ? ? ? ? ? ? -- 觸發器執行的 SQL 語句

? ? ? ? end;

3.關鍵字 new、old

new 和 old 是兩個特殊的關鍵字，用于引用在觸發器中修改前后的舊值、新值。
new：觸發 insert 或 update 操作期間，new 用于引用將要插入或更新到表中新行的值；
old：出發 update 和 delete 操作期間，old 用于引用更新或刪除之前在表中舊行的值。

可以像引用其他列一樣引用 new 和 old。

4.實例

# 1.首先，初始化一個日志表 log
drop table if exists log;
create table log(id bigint primary key auto_increment,table_name varchar(255) not null,operate varchar(10) not null,time datetime not null,operate_id bigint not null,description text 
);

# 2.向 human 表插入的觸發器
drop trigger if exists trigger_human_insert;
create trigger trigger_human_insert after insert on human for each row
begininsert into log(table_name, operate, time, operate_id, description) values('human', '新增', now(), new.id, concat('插入：id = ', new.id, ',name = ', new.name, ',gender = ', new.gender, ',age = ', new.age, ',phone = ', new.phone));
end;-- 查詢 log 日志表
select * from log;-- 向 human 表插入一條新數據
insert into human values(null, '柳梓熙', '女', '24', '18333256677');-- 查詢 log 日志表
select * from log;

# 3.向 human 表更新的觸發器
drop trigger if exists trigger_human_update;
create trigger trigger_human_update after update on human for each row
begininsert into log values(null, 'human', '更新', now(), new.id, concat('更新：[原數據：name = ', old.name, ',gender = ', old.gender, ',age = ', old.age, ',phone = ', old.phone, '];[新數據：name = ', new.name, ',gender = ', new.gender, ',age = ', new.age, ',phone = ', new.phone, ']'));
end;-- 查詢 log 日志表
select * from log;-- 向 human 表修改一條新數據
update human set name = '劉梓琪' where id = 4;-- 查詢 log 日志表
select * from log;

# 4.向 human 表刪除的觸發器
drop trigger if exists trigger_human_delete;
create trigger trigger_human_delete after delete on human for each row
begininsert into log values(null, 'human', '刪除', now(), old.id, concat('刪除：name = ', old.name, ',gender = ', old.gender, ',age = ', old.age, ',phone = ', old.phone));
end;-- 查詢 log 日志表
select * from log;-- 向 human 表刪除一條新數據
delete from human where name = '劉梓琪';-- 查詢 log 日志表
select * from log;