MySQL——視圖、存儲(chǔ)過程豺鼻、觸發(fā)器

視圖

視圖概述

視圖（View）是一種虛擬存在的表综液。視圖并不在數(shù)據(jù)庫中實(shí)際存在，行和列數(shù)據(jù)來自定義視圖的查詢中使用的表儒飒，并且是在使用視圖時(shí)動(dòng)態(tài)生成的谬莹。通俗的講，視圖就是一條SELECT語句執(zhí)行后返回的結(jié)果集约素。所以我們?cè)趧?chuàng)建視圖的時(shí)候届良，主要的工作就落在創(chuàng)建這條SQL查詢語句上。

視圖相對(duì)于普通的表的優(yōu)勢(shì)主要包括以下幾項(xiàng)圣猎。

簡(jiǎn)單：使用視圖的用戶完全不需要關(guān)心后面對(duì)應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件乞而，對(duì)用戶來說已經(jīng)是過濾好的復(fù)合條件的結(jié)果集送悔。

安全：使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集，對(duì)表的權(quán)限管理并不能限制到某個(gè)行某個(gè)列爪模，但是通過視圖就可以簡(jiǎn)單的實(shí)現(xiàn)欠啤。

數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對(duì)用戶的影響屋灌，源表增加列對(duì)視圖沒有影響洁段；源表修改列名，則可以通過修改視圖來解決共郭，不會(huì)造成對(duì)訪問者的影響祠丝。

創(chuàng)建或者修改視圖

創(chuàng)建視圖的語法為：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改視圖的語法為：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

選項(xiàng) :

WITH [CASCADED | LOCAL] CHECK OPTION 決定了是否允許更新數(shù)據(jù)使記錄不再滿足視圖的條件疾呻。

LOCAL ：只要滿足本視圖的條件就可以更新。

CASCADED ：必須滿足所有針對(duì)該視圖的所有視圖的條件才可以更新写半。默認(rèn)值.

示例 , 創(chuàng)建city_country_view視圖 , 執(zhí)行如下SQL :

create or replace view city_country_view

select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查詢視圖：

select * from city_country_view;

查看視圖

從 MySQL 5.1 版本開始岸蜗，使用 SHOW TABLES 命令的時(shí)候不僅顯示表的名字，同時(shí)也會(huì)顯示視圖的名字叠蝇，而不存在單獨(dú)顯示視圖的 SHOW VIEWS 命令璃岳。

同樣，在使用 SHOW TABLE STATUS 命令的時(shí)候悔捶，不但可以顯示表的信息铃慷，同時(shí)也可以顯示視圖的信息。

如果需要查詢某個(gè)視圖的定義蜕该，可以使用 SHOW CREATE VIEW 命令進(jìn)行查看：

刪除視圖

語法：

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

示例 , 刪除視圖city_country_view :

DROP VIEW city_country_view ;

存儲(chǔ)過程和函數(shù)

存儲(chǔ)過程和函數(shù)概述

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

存儲(chǔ)過程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲(chǔ)過程沒有淤齐。

函數(shù)：是一個(gè)有返回值的過程股囊。

過程：是一個(gè)沒有返回值的函數(shù)。

創(chuàng)建存儲(chǔ)過程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])

begin

? ? -- SQL語句

end ;

示例：

delimiter $

create procedure pro_test1()

begin

? ? select 'Hello Mysql' ;

end$

delimiter ;

DELIMITER

該關(guān)鍵字用來聲明SQL語句的分隔符 , 告訴 MySQL 解釋器更啄，該段命令是否已經(jīng)結(jié)束了稚疹，mysql是否可以執(zhí)行了。默認(rèn)情況下祭务，delimiter是分號(hào);内狗。在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束义锥，那么回車后柳沙，mysql將會(huì)執(zhí)行該命令。

調(diào)用存儲(chǔ)過程

call procedure_name() ;

查看存儲(chǔ)過程

-- 查詢db_name數(shù)據(jù)庫中的所有的存儲(chǔ)過程

select name from mysql.proc where db='db_name';

-- 查詢存儲(chǔ)過程的狀態(tài)信息

show procedure status;

-- 查詢某個(gè)存儲(chǔ)過程的定義

show create procedure test.pro_test1 \G;

刪除存儲(chǔ)過程

DROP PROCEDURE [IF EXISTS] sp_name 拌倍；

語法

存儲(chǔ)過程是可以編程的赂鲤，意味著可以使用變量，表達(dá)式柱恤，控制結(jié)構(gòu) 数初，來完成比較復(fù)雜的功能。

變量

DECLARE

通過 DECLARE 可以定義一個(gè)局部變量梗顺，該變量的作用范圍只能在 BEGIN…END 塊中泡孩。

DECLARE var_name[,...] type [DEFAULT value]

示例：

delimiter $

create procedure pro_test2()

begin

? ? declare num int default 5;

? ? select num+ 10;

end$

delimiter ;

SET

直接賦值使用 SET，可以賦常量或者賦表達(dá)式寺谤，具體語法如下：

SET var_name = expr [, var_name = expr] ...

示例 :

DELIMITER $

CREATE PROCEDURE pro_test3()

BEGIN

? ? DECLARE NAME VARCHAR(20);

? ? SET NAME = 'MYSQL';

? ? SELECT NAME ;

END$

DELIMITER ;

也可以通過select ... into 方式進(jìn)行賦值操作

DELIMITER $

CREATE PROCEDURE pro_test5()

BEGIN

? ? declare countnum int;

? ? select count(*) into countnum from city;

? ? select countnum;

END$

DELIMITER ;

if條件判斷

語法結(jié)構(gòu) :

if search_condition then statement_list

? ? [elseif search_condition then statement_list] ...

? ? [else statement_list]

end if;

需求：

根據(jù)定義的身高變量仑鸥，判定當(dāng)前身高的所屬的身材類型

? ? 180 及以上 ----------> 身材高挑

? ? 170 - 180 ---------> 標(biāo)準(zhǔn)身材

? ? 170 以下 ----------> 一般身材

示例 :

delimiter $

create procedure pro_test6()

begin

? ? declare height int default 175;

? ? declare description varchar(50);

? ?

? ? if height >= 180 then

? ? ? ? set description = '身材高挑';

? ? elseif height >= 170 and height < 180 then

? ? ? ? set description = '標(biāo)準(zhǔn)身材';

? ? else

? ? ? ? set description = '一般身材';

? ? end if;

? ?

? ? select description ;

end$

delimiter ;

傳遞參數(shù)

語法格式 :

create procedure procedure_name([in/out/inout] 參數(shù)名參數(shù)類型)

...

IN : 該參數(shù)可以作為輸入吮播，也就是需要調(diào)用方傳入值 , 默認(rèn)

OUT: 該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值

INOUT: 既可以作為輸入?yún)?shù)锈候，也可以作為輸出參數(shù)

IN - 輸入

需求 :

根據(jù)定義的身高變量薄料，判定當(dāng)前身高的所屬的身材類型

示例 :

delimiter $

create procedure pro_test5(in height int)

begin

? ? declare description varchar(50) default '';

? ? if height >= 180 then

? ? ? ? set description='身材高挑';

? ? elseif height >= 170 and height < 180 then

? ? ? ? set description='標(biāo)準(zhǔn)身材';

? ? else

? ? ? ? set description='一般身材';

? ? end if;

? ? select concat('身高 ', height , '對(duì)應(yīng)的身材類型為:',description);

end$

delimiter ;

OUT-輸出

需求 :

根據(jù)傳入的身高變量，獲取當(dāng)前身高的所屬的身材類型

示例:

create procedure pro_test5(in height int , out description varchar(100))

begin

? ? if height >= 180 then

? ? ? ? set description='身材高挑';

? ? elseif height >= 170 and height < 180 then

? ? ? ? set description='標(biāo)準(zhǔn)身材';

? ? else

? ? ? ? set description='一般身材';

? ? end if;

end$

調(diào)用:

call pro_test5(168, @description)$

select @description$

@description：這種變量要在變量名稱前面加上“@”符號(hào)泵琳，叫做用戶會(huì)話變量摄职，代表整個(gè)會(huì)話過程他都是有作用的，這個(gè)類似于全局變量一樣获列。

@@global.sort_buffer_size：這種在變量前加上 "@@" 符號(hào), 叫做系統(tǒng)變量谷市。

case結(jié)構(gòu)

語法結(jié)構(gòu) :

方式一 :

CASE case_value

? ? WHEN when_value THEN statement_list

? ? [WHEN when_value THEN statement_list] ...

? ? [ELSE statement_list]

END CASE;

方式二 :

CASE

? ? WHEN search_condition THEN statement_list

? ? [WHEN search_condition THEN statement_list] ...

? ? [ELSE statement_list]

END CASE;

需求:

給定一個(gè)月份, 然后計(jì)算出所在的季度

示例 :

delimiter $

create procedure pro_test9(month int)

begin

? ? declare result varchar(20);

? ? case

? ? ? ? when month >= 1 and month <=3 then

? ? ? ? ? ? set result = '第一季度';

? ? ? ? when month >= 4 and month <=6 then

? ? ? ? ? ? set result = '第二季度';

? ? ? ? when month >= 7 and month <=9 then

? ? ? ? ? ? set result = '第三季度';

? ? ? ? when month >= 10 and month <=12 then

? ? ? ? ? ? set result = '第四季度';

? ? end case;

? ?

? ? select concat('您輸入的月份為 :', month , ' , 該月份為 : ' , result) as content ;

end$

delimiter ;

while循環(huán)

語法結(jié)構(gòu):

while search_condition do

? ? statement_list

end while;

需求:

計(jì)算從1加到n的值

示例 :

delimiter $

create procedure pro_test8(n int)

begin

? ? declare total int default 0;

? ? declare num int default 1;

? ?

? ? while num<=n do

? ? ? ? set total = total + num;

? ? ? ? set num = num + 1;

? ? end while;

? ?

? ? select total;

end$

delimiter ;

repeat結(jié)構(gòu)

有條件的循環(huán)控制語句, 當(dāng)滿足條件的時(shí)候退出循環(huán) 。while 是滿足條件才執(zhí)行击孩，repeat 是滿足條件就退出循環(huán)迫悠。

語法結(jié)構(gòu) :

REPEAT

? ? statement_list

? ? UNTIL search_condition

END REPEAT;

需求:

計(jì)算從1加到n的

示例 :

delimiter $

create procedure pro_test10(n int)

begin

? ? declare total int default 0;

? ?

? ? repeat

? ? ? ? set total = total + n;

? ? ? ? set n = n - 1;

? ? ? ? until n=0

? ? end repeat;

? ?

? ? select total ;

end$

delimiter ;

loop語句

LOOP 實(shí)現(xiàn)簡(jiǎn)單的循環(huán)，退出循環(huán)的條件需要使用其他的語句定義巩梢，通炒葱梗可以使用 LEAVE 語句實(shí)現(xiàn)，具體語法如下：

[begin_label:] LOOP

? ? statement_list

END LOOP [end_label]

如果不在 statement_list 中增加退出循環(huán)的語句括蝠，那么 LOOP 語句可以用來實(shí)現(xiàn)簡(jiǎn)單的死循環(huán)鞠抑。

leave語句

用來從標(biāo)注的流程構(gòu)造中退出，通常和 BEGIN ... END 或者循環(huán)一起使用忌警。下面是一個(gè)使用 LOOP 和 LEAVE 的簡(jiǎn)單例子 , 退出循環(huán)：

delimiter $

CREATE PROCEDURE pro_test11(n int)

BEGIN

? ? declare total int default 0;

? ?

? ? ins: LOOP

? ? ? ? IF n <= 0 then

? ? ? ? ? ? leave ins;

? ? ? ? END IF;

? ? ? ?

? ? ? ? set total = total + n;

? ? ? ? set n = n - 1;

? ? END LOOP ins;

? ?

? ? select total;

END$

delimiter ;

游標(biāo)/光標(biāo)

游標(biāo)是用來存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型 , 在存儲(chǔ)過程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理搁拙。光標(biāo)的使用包括光標(biāo)的聲明、OPEN法绵、FETCH 和 CLOSE箕速，其語法分別如下。

聲明光標(biāo)：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光標(biāo)：

OPEN cursor_name ;

FETCH 光標(biāo)：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光標(biāo)：

CLOSE cursor_name ;

示例 :

初始化腳本:

create table emp(

? ? id int(11) not null auto_increment ,

? ? name varchar(50) not null comment '姓名',

? ? age int(11) comment '年齡',

? ? salary int(11) comment '薪水',

? ? primary key(`id`)

)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹

王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);

-- 查詢emp表中數(shù)據(jù), 并逐行獲取進(jìn)行展示

create procedure pro_test11()

begin

? ? declare e_id int(11);

? ? declare e_name varchar(50);

? ? declare e_age int(11);

? ? declare e_salary int(11);

? ? declare emp_result cursor for select * from emp;

? ?

? ? open emp_result;

? ?