柚子快報(bào)激活碼778899分享：SQL編程

http://yzkb.51969.com/

1.觸發(fā)器

1.1觸發(fā)器簡(jiǎn)介

????????觸發(fā)器（trigger）是一個(gè)特殊的存儲(chǔ)過(guò)程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，比如當(dāng)對(duì)一個(gè)表進(jìn)行操作（ insert，delete， update）時(shí)就會(huì)激活它執(zhí)行。 觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。 例如，當(dāng)學(xué)生表中增加了一個(gè)學(xué)生的信息時(shí)，學(xué)生的總數(shù)就應(yīng)該同時(shí)改變。因此可以針對(duì)學(xué)生表創(chuàng)建一個(gè)觸發(fā)器，每次增加一個(gè)學(xué)生記錄時(shí)，就執(zhí)行一次學(xué)生總數(shù)的計(jì)算操作，從而保證學(xué)生總數(shù)與記錄數(shù)的一致性。

1.2創(chuàng)建觸發(fā)器

1、創(chuàng)建只有一個(gè)執(zhí)行語(yǔ)句的觸發(fā)器

create trigger 觸發(fā)器名 before|after 觸發(fā)事件

on 表名 for each row

執(zhí)行語(yǔ)句;

2、創(chuàng)建有多個(gè)執(zhí)行語(yǔ)句的觸發(fā)器

create trigger 觸發(fā)器名 before|after 觸發(fā)事件

on 表名 for each row

begin

執(zhí)行語(yǔ)句列表

end;

# 說(shuō)明：

<觸發(fā)器名稱> ：最多64個(gè)字符，它和MySQL中其他對(duì)象的命名方式一樣

{ before | after } ：觸發(fā)器時(shí)機(jī)

{ insert | update | delete } ：觸發(fā)的事件

on <表名稱> ：標(biāo)識(shí)建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器

for each row ：觸發(fā)器的執(zhí)行間隔，通知觸發(fā)器每隔一行執(zhí)行一次動(dòng)作，而不是對(duì)整個(gè)表執(zhí)行一次

<觸發(fā)器程序體> ：要觸發(fā)的SQL語(yǔ)句，可用順序，判斷，循環(huán)等語(yǔ)句實(shí)現(xiàn)一般程序需要的邏輯功能

1.3NEW和OLD的區(qū)別

觸發(fā)器類型觸發(fā)器類型NEW 和OLD 的使用 INSERT 型觸發(fā)器：NEW 表示將要或者已經(jīng)新增的數(shù)據(jù) UPDATE 型觸發(fā)器：OLD 表示修改之前的數(shù)據(jù) , NEW 表示將要或已經(jīng)修改后的數(shù)據(jù) DELETE 型觸發(fā)器：OLD 表示將要或者已經(jīng)刪除的數(shù)據(jù)

1.4注意

MYSQL中觸發(fā)器中不能對(duì)本表進(jìn)行 insert ,update ,delete 操作，以免遞歸循環(huán)觸發(fā) 盡量少使用觸發(fā)器，假設(shè)觸發(fā)器觸發(fā)每次執(zhí)行1s，insert table 500條數(shù)據(jù)，那么就需要觸發(fā)500次觸發(fā)器，光是觸發(fā)器執(zhí)行的時(shí)間就花費(fèi)了500s，而insert 500條數(shù)據(jù)一共是1s，那么這個(gè)insert的效率就非常低了。 觸發(fā)器是針對(duì)每一行的；對(duì)增刪改非常頻繁的表上切記不要使用觸發(fā)器，因?yàn)樗鼤?huì)非常消耗資源。

2.存儲(chǔ)過(guò)程

2.1概念

存儲(chǔ)過(guò)程和函數(shù)是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段sql語(yǔ)句集合，調(diào)用存儲(chǔ)過(guò)程可以簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的。 簡(jiǎn)單的說(shuō)，存儲(chǔ)過(guò)程就是一組SQL語(yǔ)句集，功能強(qiáng)大，可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能，類似于JAVA語(yǔ)言中的方法，是SQL 語(yǔ)言層面的代碼封裝與重用。 存儲(chǔ)過(guò)程和函數(shù)的區(qū)別： 函數(shù)必須有返回值，而存儲(chǔ)過(guò)程沒(méi)有。 存儲(chǔ)過(guò)程的參數(shù)可以是IN、OUT、INOUT類型，函數(shù)的參數(shù)只能是IN

2.2優(yōu)點(diǎn)

存儲(chǔ)過(guò)程只在創(chuàng)建時(shí)進(jìn)行編譯；而SQL語(yǔ)句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過(guò)程可以提高數(shù)據(jù)庫(kù)執(zhí)行速度 簡(jiǎn)化復(fù)雜操作，結(jié)合事務(wù)一起封裝 復(fù)用性好 安全性高，可指定存儲(chǔ)過(guò)程的使用權(quán)

2.3存儲(chǔ)過(guò)程的創(chuàng)建與調(diào)用

# 1.定義

delimiter 自定義結(jié)束符號(hào)

create procedure 儲(chǔ)存名([IN | OUT | INOUT]參數(shù)名 類型...)

begin

SQL語(yǔ)句

end 自定義結(jié)束符號(hào)

delimiter ;

# 2.調(diào)用：

call 存儲(chǔ)過(guò)程名(實(shí)參列表)

# 注1：自定義符號(hào)可以用除了分號(hào)的符號(hào)，一般用$$ 或 //

# 注2：存儲(chǔ)過(guò)程的參數(shù)形式：

IN 輸入?yún)?shù)

OUT 輸出參數(shù)

INOUT 輸入輸出參數(shù)

示例

# 使用存儲(chǔ)過(guò)程，插如多條數(shù)據(jù)

mysql> create table passwd(id int,pwds varchar(50));

mysql> delimiter $$

mysql> create procedure proc02()

begin

declare i int default 1;

while(i<10000)

do

insert into passwd values(i, md5(i));

set i=i+1;

end while;

end $$

mysql> delimiter ;

mysql> call proc02();

mysql> select * from passwd;

2.4變量

局部變量: 用戶自定義，在begin/end塊中有效，格式：

# 聲明變量

declare var_name type [default var_value];

# 舉例

declare nickname varchar(32);

MySQL 中還可以使用 SELECT..INTO 語(yǔ)句為變量賦值。其基本語(yǔ)法如下：

# 格式：

select col_name [...] into var_name[,...]

from table_name wehre condition

# 其中：

col_name 參數(shù)表示查詢的字段名稱；

var_name 參數(shù)是變量的名稱；

table_name 參數(shù)指表的名稱；

condition 參數(shù)指查詢條件。

# 注意：當(dāng)將查詢結(jié)果賦值給變量時(shí)，該查詢語(yǔ)句的返回結(jié)果只能是單行單列。

//示例

mysql> delimiter $$

mysql> create procedure proc04()

begin

declare var_name02 varchar(20) ;

select name into var_name02 from student1 where id=1003;

select var_name02;

end $$

mysql> delimiter ;

mysql> call proc04();

+------------+

| var_name02 |

+------------+

| 李四 |

+------------+

?用戶變量：用戶自定義，當(dāng)前會(huì)話（連接）有效。類比java的成員變量，格式

@var_name

# 不需要提前聲明，使用即聲明，即無(wú)declare子句

mysql> delimiter $$

mysql> create procedure proc05() begin set @var_name03 = 'openlab'; end $$

mysql> delimiter ;

mysql> call proc05() ;

mysql> select @var_name01 ;

+-------------+

| @var_name01 |

+-------------+

| openlab |

+-------------+

?系統(tǒng)變量：

語(yǔ)法：

@@global.var_name

# 查看全局變量

show global variables;

# 查看某全局變量

select @@global.auto_increment_increment;

# 修改全局變量的值

set global sort_buffer_size = 40000;

set @@global.sort_buffer_size = 40000;

# 注意：

global 與 @@global 等價(jià)，都是系統(tǒng)全局變量

SET global 用于更改全局系統(tǒng)變量的值，影響所有客戶端連接

SET @@GLOBAL一般用于查看全局變量的當(dāng)前值

# 查看會(huì)話變量

show session variables;

# 查看某會(huì)話變量

select @@session.auto_increment_increment;

# 修改會(huì)話變量的值

set session sort_buffer_size = 50000;

set @@session.sort_buffer_size = 50000 ;

2.5參數(shù)傳遞

IN：表示傳入的參數(shù)， 可以傳入數(shù)值或者變量，即使傳入變量，并不會(huì)更改變量的值，可以內(nèi)部更改，僅僅作用在函數(shù)范圍內(nèi)。

mysql> delimiter $$

mysql> create procedure proc06(in a int) begin declare i int default 1;

while(i<=a) do insert into passwd values(i,md5(i)); set i=i+1; end while;

end $$

mysql> delimiter ;

mysql> set @num=100; # 定義用戶變量

mysql> select @num; # 查看

+------+

| @num |

+------+

| 100 |

+------+

mysql> call proc06(@num); # 執(zhí)行過(guò)程，傳遞變量值到過(guò)程中

mysql> select * from passwd;

out：表示從存儲(chǔ)過(guò)程內(nèi)部傳值給調(diào)用者,in的反向傳遞

mysql> delimiter $$

mysql> create procedure proc07(out cnt1 int) begin select count(*) into cnt1

from passwd; end $$

mysql> delimiter ;

mysql> call proc07(@cnt2);

mysql> select @cnt2;

+-------+

| @cnt2 |

+-------+

| 10099 |

+-------+

inout：

mysql> delimiter $$

mysql> create procedure proc10(inout p1 int) begin if (p1 is not null) then set

p1=p1+1; else select 100 into p1; end if; end$$

mysql> delimiter ;

mysql> select @h;

+------+

| @h |

+------+

| NULL |

+------+

mysql> call proc10(@h);

mysql> select @h;

+------+

| @h |

+------+

| 100 |

+------+

總結(jié)： in ：輸入?yún)?shù)，意思說(shuō)你的參數(shù)要傳到存過(guò)過(guò)程的過(guò)程里面去，在存儲(chǔ)過(guò)程中修改該參數(shù)的值不能被返回 out ：輸出參數(shù):該值可在存儲(chǔ)過(guò)程內(nèi)部被改變，并向外輸出 inout ：輸入輸出參數(shù)，既能輸入一個(gè)值又能傳出來(lái)一個(gè)值)

2.6if判斷

IF語(yǔ)句包含多個(gè)條件判斷，根據(jù)結(jié)果為T(mén)RUE、FALSE執(zhí)行語(yǔ)句，與編程語(yǔ)言中的if、else if、else語(yǔ)法類似，其語(yǔ)法格式如下：

if search_condition_1 then statement_list_1

[elseif search_condition_2 then statement_list_2] ...

[else statement_list_n]

end if

示例

mysql> delimiter $$

mysql> create procedure proc11_if(in score int)

begin

if score < 60

then

select '不及格';

elseif score < 80

then

select '及格' ;

elseif score >= 80 and score < 90

then

select '良好';

elseif score >= 90 and score <= 100

then

select '優(yōu)秀';

else

select '成績(jī)錯(cuò)誤';

end if;

end $$

mysql> delimiter ;

mysql> call proc11_if(120);

mysql> call proc11_if(86);

2.7case判斷

# 語(yǔ)法一（類比java的switch）：

case case_value

when when_value then statement_list

[when when_value then statement_list] ...

[else statement_list]

end case

# 語(yǔ)法二：

case

when search_condition then statement_list

[when search_condition then statement_list] ...

[else statement_list]

end case

mysql> delimiter $$

mysql> create procedure proc13_case(in pay_type int)

begin

case pay_type

when 1 then select '微信支付' ;

when 2 then select '支付寶支付' ;

when 3 then select '銀行卡支付';

else select '其他方式支付';

end case ;

end $$

mysql> delimiter ;

mysql> call proc13_case(2);

mysql> call proc13_case(4);

2.8循環(huán)

1.while循環(huán)格式

[標(biāo)簽:]while 循環(huán)條件 do

循環(huán)體;

end while[標(biāo)簽];

示例：

mysql> create table user (uid int primary key,username varchar(50),password

varchar(50));

# 存儲(chǔ)過(guò)程-while

mysql> delimiter $$

mysql> create procedure proc15_while1(in insertcount int)

begin

declare i int default 1;

label:while i<=insertcount do

insert into user(uid,username,password) values(i,concat('user-',i),'123456');

set i=i+1;

end while label;

end $$

mysql> delimiter ;

mysql> call proc15_while1(10);

mysql> select * from user;

+-----+----------+----------+

| uid | username | password |

+-----+----------+----------+

| 1 | user-1 | 123456 |

| 2 | user-2 | 123456 |

| 3 | user-3 | 123456 |

| 4 | user-4 | 123456 |

| 5 | user-5 | 123456 |

| 6 | user-6 | 123456 |

| 7 | user-7 | 123456 |

| 8 | user-8 | 123456 |

| 9 | user-9 | 123456 |

| 10 | user-10 | 123456 |

+-----+----------+----------+

# 存儲(chǔ)過(guò)程-while + leave

mysql> truncate table user; # 清空表內(nèi)容

mysql> delimiter $$

mysql> create procedure proc16_while2(in insertcount int)

begin

declare i int default 1;

label:while i<=insertcount do

insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');

if i=5

then

leave label;

end if;

set i=i+1;

end while label;

end $$

mysql> delimiter ;

mysql> call proc16_while2(10);

mysql> select * from user;

+-----+----------+----------+

| uid | username | password |

+-----+----------+----------+

| 1 | user-1 | 123456 |

| 2 | user-2 | 123456 |

| 3 | user-3 | 123456 |

| 4 | user-4 | 123456 |

| 5 | user-5 | 123456 |

+-----+----------+----------+

# 存儲(chǔ)過(guò)程-while+iterate

mysql> truncate table user;

mysql> delimiter $$

mysql> create procedure proc17_while3(in insertcount int)

begin

declare i int default 1;

label:while i<=insertcount do

set i=i+1;

if i=5

then

iterate label;

end if;

insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');

end while label;

end $$

mysql> delimiter ;

mysql> call proc17_while3(10);

mysql> select * from user; # 沒(méi)有第五條記錄

+-----+----------+----------+

| uid | username | password |

+-----+----------+----------+

| 2 | user-2 | 123456 |

| 3 | user-3 | 123456 |

| 4 | user-4 | 123456 |

| 6 | user-6 | 123456 |

| 7 | user-7 | 123456 |

| 8 | user-8 | 123456 |

| 9 | user-9 | 123456 |

| 10 | user-10 | 123456 |

| 11 | user-11 | 123456 |

+-----+----------+----------+

2.repeat循環(huán)格式

[標(biāo)簽:]repeat

循環(huán)體;

until 條件表達(dá)式

end repeat [標(biāo)簽];

mysql> create procedure proc18_repeat(in insertCount int)

begin

declare i int default 1;

label:repeat

insert into user(uid, username, password) values(i,concat('user-',i),'123456');

set i = i + 1;

until i > insertCount

end repeat label;

select '循環(huán)結(jié)束';

end $$

mysql> delimiter ;

mysql> call proc18_repeat(100);

?3.loop循環(huán)格式

[標(biāo)簽:] loop

循環(huán)體;

if 條件表達(dá)式

then

leave [標(biāo)簽];

end if;

end loop;

# 存儲(chǔ)過(guò)程-循環(huán)控制-loop

mysql> truncate table user;

mysql> delimiter $$

mysql> create procedure proc19_loop(in insertCount int)

begin

declare i int default 1;

label:loop

insert into user(uid, username, password) values(i,concat('user-',i),'123456');

set i = i + 1;

if i > 5

then

leave label;

end if;

end loop label;

select '循環(huán)結(jié)束';

end $$

mysql> delimiter ;

mysql> call proc19_loop(10);

3.存儲(chǔ)函數(shù)

3.1概念

MySQL存儲(chǔ)函數(shù)（自定義函數(shù)）一般用于計(jì)算和返回一個(gè)值，可以將經(jīng)常需要使用的計(jì)算或功能寫(xiě)成一個(gè)函數(shù)。函數(shù)和存儲(chǔ)過(guò)程類似。

存儲(chǔ)函數(shù)與存儲(chǔ)過(guò)程的區(qū)別

存儲(chǔ)函數(shù)有且只有一個(gè)返回值，而存儲(chǔ)過(guò)程可以有多個(gè)返回值，也可以沒(méi)有返回值。 存儲(chǔ)函數(shù)只能有輸入?yún)?shù)，而且不能帶in, 而存儲(chǔ)過(guò)程可以有多in,out,inout參數(shù)。 存儲(chǔ)過(guò)程中的語(yǔ)句功能更強(qiáng)大，存儲(chǔ)過(guò)程可以實(shí)現(xiàn)很復(fù)雜的業(yè)務(wù)邏輯，而函數(shù)有很多限制，如不能在函數(shù)中使用insert,update,delete,create等語(yǔ)句； 存儲(chǔ)函數(shù)只完成查詢的工作，可接受輸入?yún)?shù)并返回一個(gè)結(jié)果，也就是函數(shù)實(shí)現(xiàn)的功能針對(duì)性比較 強(qiáng)。 存儲(chǔ)過(guò)程可以調(diào)用存儲(chǔ)函數(shù)。但函數(shù)不能調(diào)用存儲(chǔ)過(guò)程。 存儲(chǔ)過(guò)程一般是作為一個(gè)獨(dú)立的部分來(lái)執(zhí)行(call調(diào)用)。而函數(shù)可以作為查詢語(yǔ)句的一個(gè)部分來(lái)調(diào)用.

3.2存儲(chǔ)函數(shù)的創(chuàng)建與調(diào)用

create function func_name ([param_name type[,...]])

returns type

[characteristic ...]

begin

routine_body

END;

# 參數(shù)說(shuō)明：

（1）func_name ：存儲(chǔ)函數(shù)的名稱。

（2）param_name type：可選項(xiàng)，指定存儲(chǔ)函數(shù)的參數(shù)。type參數(shù)用于指定存儲(chǔ)函數(shù)的參數(shù)類型，該類型可

以是MySQL數(shù)據(jù)庫(kù)中所有支持的類型。

（3）returns type：指定返回值的類型。

（4）characteristic：可選項(xiàng)，指定存儲(chǔ)函數(shù)的特性。

（5）routine_body：SQL代碼內(nèi)容。

?示例：

mysql> delimiter $$

mysql> create function myfunc1_emp()

returns int

begin

declare cnt int default 0;

select count(*) into cnt from emp_new;

return cnt;

end $$

mysql> delimiter ;

# 調(diào)用存儲(chǔ)函數(shù)

mysql> select myfunc1_emp();

mysql> create function myfunc2_emp(in_sid int)

returns varchar(50)

begin

declare out_name varchar(50);

select name into out_name from emp_new where sid = in_sid;

return out_name;

end $$

mysql> delimiter ;

mysql> select myfunc2_emp(1776);

?

柚子快報(bào)激活碼778899分享：SQL編程

http://yzkb.51969.com/

精彩鏈接