《MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（微課版）》-第7、8章-數(shù)據(jù)庫(kù)編程、數(shù)據(jù)庫(kù)開(kāi)發(fā)體驗(yàn)

項(xiàng)目7數(shù)據(jù)庫(kù)編程目錄任務(wù)1學(xué)習(xí)MySQL編程任務(wù)1學(xué)習(xí)MySQL編程MySQL編程概述【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)【實(shí)訓(xùn)7-2】MySQL流程控制【實(shí)訓(xùn)7-3】使用內(nèi)置函數(shù)【實(shí)訓(xùn)7-4】使用游標(biāo)任務(wù)2存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程任務(wù)4使用觸發(fā)器任務(wù)5了解事務(wù)和鎖7.1.1

MySQL編程概述

編程語(yǔ)言的基礎(chǔ)是關(guān)鍵字、數(shù)據(jù)類(lèi)型、變量和常量、運(yùn)算符、表達(dá)式，以及流程控制，下面分別講解。1．關(guān)鍵字關(guān)鍵字是有特殊含義的單詞，MySQL

5.5的關(guān)鍵字有565個(gè)，其中包括226個(gè)保留字保留字（226個(gè)）：不能用于表名或列名的關(guān)鍵字，例如Select和Delete等非保留關(guān)鍵字（339個(gè)）：雖然有特殊含義，但可以用于表名或列名中7.1.1

MySQL編程概述（續(xù)一）7.1.1

MySQL編程概述（續(xù)二）2．?dāng)?shù)據(jù)類(lèi)型

變量的數(shù)據(jù)類(lèi)型與列的數(shù)據(jù)類(lèi)型相同，見(jiàn)附錄A，并參見(jiàn)項(xiàng)目2的“任務(wù)2理解MySQL的數(shù)據(jù)類(lèi)型”一節(jié)對(duì)數(shù)據(jù)類(lèi)型的講解3．命名需要命名的有表名、列名、變量名，以及函數(shù)名等命名原則不要與保留字以及非保留關(guān)鍵字（共500多個(gè)）相同應(yīng)該從名稱的前綴區(qū)分出名稱的類(lèi)別，如表名、列名、變量名，以及函數(shù)名等名稱全部小寫(xiě)，雖然SQL是大小寫(xiě)不敏感的表7.2是本書(shū)使用的命名規(guī)范7.1.1

MySQL編程概述（續(xù)三）7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)概述1．變量系統(tǒng)變量用戶變量局部變量2．字面常量【實(shí)訓(xùn)7-1】7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)一）--格式一

Case表達(dá)式when等于值1時(shí)then結(jié)果1when等于值2時(shí)then結(jié)果2…else結(jié)果nend

case--格式二

Casewhen表達(dá)式1為真時(shí)then結(jié)果1when表達(dá)式2為真時(shí)then結(jié)果2…else結(jié)果nend

case3．運(yùn)算符和表達(dá)式（1）常用運(yùn)算符算術(shù)運(yùn)算符、關(guān)系運(yùn)算符等，與Java語(yǔ)言、Python以及C語(yǔ)言等基本相同（2）If運(yùn)算符If(條件表達(dá)式,為真時(shí)的結(jié)果,為假時(shí)的結(jié)果)（3）Ifnull運(yùn)算符Ifnull(值,值為空時(shí)的返回值)（4）Case運(yùn)算符7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)二，系統(tǒng)變量）根據(jù)Jitor校驗(yàn)器的要求，在dbForge上完成“【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)”1、系統(tǒng)變量系統(tǒng)變量是MySQL系統(tǒng)內(nèi)部定義的變量，保存了系統(tǒng)的配置參數(shù)，以及軟件和硬件參數(shù)的值系統(tǒng)變量的命名：以@@為前綴（1）查詢指定系統(tǒng)變量的值Select

@@wait_timeout;（2）列出全部系統(tǒng)變量show

variables;結(jié)果有600多個(gè)系統(tǒng)變量，顯示時(shí)沒(méi)有前綴（3）模糊查找系統(tǒng)變量show

variables

like

"version%";查詢以version起始的系統(tǒng)變量，MySQL版本相關(guān)的信息注1：顯示時(shí)沒(méi)有前綴注2：不要修改系統(tǒng)變量的值，少數(shù)系統(tǒng)變量可以重新賦值，但還是不要這樣做注3：系統(tǒng)變量還分為全局(global)變量與會(huì)話(session)變量?jī)煞N，初學(xué)者不必區(qū)分它們7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)三，用戶變量）2、用戶變量用戶變量是用戶定義的變量，用于保存數(shù)據(jù)，具有較長(zhǎng)的生命周期用戶變量的命名：以@為前綴無(wú)需聲明，直接可用（1）例子一：用Set賦值Set

@text

=

"Hello";Select

@text;（2）例子二：用Select語(yǔ)句賦值，將查詢結(jié)果賦給用戶變量Select

sum(col_ammount),

avg(col_ammount)

into

@ammout,

@average

from

shop_order_head;Select

@ammout,

@average;7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)四，局部變量）3、局部變量局部變量是在語(yǔ)句塊（用begin和end定義語(yǔ)句塊）中聲明的變量，用于保存臨時(shí)數(shù)據(jù)，它的生命周期很短局部變量的命名：無(wú)需前綴，但為了與表名列名等混淆，建議加上前綴var_局部變量必須先用Declare聲明，然后才能使用，語(yǔ)法格式如下Declare局部變量名數(shù)據(jù)類(lèi)型default初始值;局部變量的例子局部變量是在語(yǔ)句塊中的，而語(yǔ)句塊又是在存儲(chǔ)程序（下面以存儲(chǔ)過(guò)程為例）中的Drop

procedure

if

exists

p_proc;--丟棄名為p_proc的存儲(chǔ)過(guò)程，如果它存在

Delimiter%%--指定新的分隔符Create

procedure

p_proc()--創(chuàng)建存儲(chǔ)過(guò)程，語(yǔ)句塊放在存儲(chǔ)過(guò)程中beginDeclare

var_tmp

int

default

5;Set

var_tmp

=

12;Select

var_tmp;end%%Delimiter;--恢復(fù)默認(rèn)的分隔符（分號(hào)）運(yùn)行存儲(chǔ)程序（此處為存儲(chǔ)過(guò)程）Call

p_proc();--運(yùn)行存儲(chǔ)過(guò)程7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)五，比較）用戶變量的作用域是這個(gè)用戶，從賦值開(kāi)始，直到用戶退出局部變量的作用域是語(yǔ)句塊，從聲明開(kāi)始，直到語(yǔ)句塊結(jié)束局部變量與用戶變量的比較Drop

procedure

if

exists

p_proc;--丟棄名為p_proc的存儲(chǔ)過(guò)程，如果它存在

Delimiter%%--指定新的分隔符Create

procedure

p_proc()beginDeclare

var_tmp

int

default

12;--用戶變量--局部變量Set

@tmp

=

22;Select

var_tmp;end%%Delimiter

;--恢復(fù)默認(rèn)的分隔符（分號(hào)）Call

p_proc();Select

@tmp;Select

var_tmp;最大的區(qū)別在于作用域--仍然可以訪問(wèn)，并有原來(lái)的值--不能訪問(wèn)，因?yàn)槌隽俗饔糜蚍秶?.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)六，if和ifnull）If運(yùn)算符If運(yùn)算符類(lèi)似于C/C++或Java語(yǔ)言中的三元運(yùn)算符，語(yǔ)法格式如下。If(條件表達(dá)式,為真時(shí)的結(jié)果,為假時(shí)的結(jié)果)例如使用下述語(yǔ)句可以將以M、F表示的性別轉(zhuǎn)換為“男”和“女”。Select

col_name,if

(col_sex="M","男","女")from

shop_staff;Ifnull運(yùn)算符Ifnull運(yùn)算符判斷一個(gè)值是否為空。如果不為空返回該值，如果為空返回另一個(gè)值，語(yǔ)法格式如下。Ifnull(值,值為空時(shí)的返回值)例如下述語(yǔ)句，將列出所有手機(jī)號(hào)。如果手機(jī)號(hào)為空，則顯示“缺手機(jī)號(hào)”。Select

col_name,ifnull

(col_mobile,"缺手機(jī)號(hào)")from

shop_staff;與下述語(yǔ)句等價(jià)Select

col_name,if

(col_mobile

is

not

null,col_mobile,"缺手機(jī)號(hào)")from

shop_staff;7.1.2【實(shí)訓(xùn)7-1】MySQL語(yǔ)言基礎(chǔ)（續(xù)七，case）Case運(yùn)算符比較兩種方式第二種方式更加靈活，例如引用不同的列，有更靈活的條件Select

col_name,

col_inventory,

col_price,casewhen

col_price>=200

and

col_inventory<5

then"需補(bǔ)貨"when

col_price<200

and

col_inventory<10

then"需補(bǔ)貨"

else"庫(kù)存充足"endfrom

shop_goods;Select

col_name,case

col_sexwhen

"M"then"男"when

"F"then"女"

else"未知"endfrom

shop_staff;Select

col_name,casewhen

col_sex="M"then"男"when

col_sex="F"then"女"

else"未知"endfrom

shop_staff;編程概述——小結(jié)與其他編程語(yǔ)言比較，MySQL比較特別的有兩處變量分為系統(tǒng)變量、用戶變量和局部變量三種編程元素必須在語(yǔ)句塊中，而語(yǔ)句塊又必須在存儲(chǔ)程序中，即在下述代碼中Drop

procedure

if

exists

p_proc;--丟棄名為“p_proc”的存儲(chǔ)過(guò)程，如果它存在

Delimiter%%--指定新的分隔符Create

procedure

p_proc()--創(chuàng)建一個(gè)名為p_proc的存儲(chǔ)過(guò)程begin--編程元素（局部變量、流程控制等）的代碼寫(xiě)在這里

end%%Delimiter;--恢復(fù)默認(rèn)的分隔符（分號(hào)）Call

p_proc;--調(diào)用名為“p_proc”的存儲(chǔ)過(guò)程注：存儲(chǔ)程序有存儲(chǔ)函數(shù)、存儲(chǔ)過(guò)程等多種，后面將詳細(xì)講解7.1.3【實(shí)訓(xùn)7-2】MySQL流程控制1．條件分支語(yǔ)句（1）If語(yǔ)句（2）Case語(yǔ)句注1：本節(jié)所有代碼都要放在存儲(chǔ)程序中的begin…end語(yǔ)句塊中才能運(yùn)行

注2：If

運(yùn)算符、Case

運(yùn)算符與If

語(yǔ)句、Case

語(yǔ)句是不同的，前者用于表達(dá)式，后者用于流程控制Set

@id=3;If

@id=1

thenselect"語(yǔ)句1";elseif

@id=2

thenselect"語(yǔ)句2";elseselect"語(yǔ)句n";end

if;Set

@id=2;Casewhen

@id=1

thenselect"語(yǔ)句1";when

@id=2

thenselect"語(yǔ)句2";elseselect"語(yǔ)句n";end

case;【實(shí)訓(xùn)7-2】7.1.3【實(shí)訓(xùn)7-2】MySQL流程控制（續(xù)一，While循環(huán)）2．循環(huán)語(yǔ)句之一：While循環(huán)語(yǔ)句語(yǔ)法格式如下。[標(biāo)簽:]While循環(huán)條件表達(dá)式do語(yǔ)句塊;end

while;可以選擇用leave關(guān)鍵字強(qiáng)制退出當(dāng)前循環(huán)，或用iterate關(guān)鍵字強(qiáng)制進(jìn)入下一次循環(huán)。兩個(gè)例子--計(jì)算1到100的累加和Declare

_i

int

default

0;Set

@sum

=

0;While

_i<100

doset

_i

=

_i

+

1;set

@sum

=

@sum

+

_i;end

while;Select

@sum;--計(jì)算1到100的累加和，用iterate短路，相當(dāng)于計(jì)算1到50的累加和Declare

_i

int

default

0;Set

@sum

=

0;r:While

_i<100

do--用標(biāo)簽r命名這個(gè)循環(huán)set

_i

=

_i

+

1;if

_i>50

theniterate

r;--跳過(guò)后面的循環(huán)體end

if;set

@sum

=

@sum

+

_i;end

while;Select

@sum;7.1.3【實(shí)訓(xùn)7-2】MySQL流程控制（續(xù)二，Repeat循環(huán)）--計(jì)算1到100的累加和Declare

_i

int

default

0;Set

@sum

=

0;Repeatset

_i

=

_i

+

1;set

@sum

=

@sum

+

_i;until

_i>=100--到達(dá)100后，結(jié)束循環(huán)

end

repeat;Select

@sum;2．循環(huán)語(yǔ)句之一：Repeat循環(huán)語(yǔ)句語(yǔ)法格式如下。[標(biāo)簽:]Repeat語(yǔ)句塊;until結(jié)束條件表達(dá)式end

repeat;可以選擇用leave關(guān)鍵字強(qiáng)制退出當(dāng)前循環(huán)，或用iterate關(guān)鍵字強(qiáng)制進(jìn)入下一次循環(huán)。兩個(gè)例子--計(jì)算1到100的累加和，用leave退出，相當(dāng)于計(jì)算1到50的累加和Declare

_i

int

default

0;Set

@sum

=

0;r:Repeat--用標(biāo)簽r命名這個(gè)循環(huán)set

_i

=

_i

+

1;if

_i>50

thenleave

r;--退出標(biāo)簽r指定的循環(huán)end

if;set

@sum

=

@sum

+

_i;until

_i>=100--到達(dá)100后，結(jié)束循環(huán)end

repeat;Select

@sum;7.1.3【實(shí)訓(xùn)7-2】MySQL流程控制（續(xù)三，Loop循環(huán)）2．循環(huán)語(yǔ)句之一：Loop循環(huán)語(yǔ)句語(yǔ)法格式如下。標(biāo)簽:Loop語(yǔ)句塊;end

loop;這是無(wú)限循環(huán)，必須用leave關(guān)鍵字強(qiáng)制退出當(dāng)前循環(huán)，所以標(biāo)簽是不能省略的一個(gè)例子--計(jì)算1到100的累加和Declare

_i

int

default

0;Set

@sum

=

0;r:Loop--用標(biāo)簽r命名這個(gè)循環(huán)set

_i

=

_i

+

1;set

@sum

=

@sum

+

_i;if

_i>=100

thenleave

r;--退出標(biāo)簽r指定的循環(huán)end

if;end

loop;Select

@sum;MySQL流程控制——小結(jié)3種循環(huán)的比較輔助關(guān)鍵字Leave：退出，提前結(jié)束循環(huán)，類(lèi)似于C/Java的breakIterate：短路，提前進(jìn)入下一輪循環(huán)，類(lèi)似于C/Java的continue7.1.4【實(shí)訓(xùn)7-3】使用內(nèi)置函數(shù)注意：字符串連接不能用加號(hào)，而必須用concat函數(shù)，這個(gè)函數(shù)可以連接任意多個(gè)字符串1．統(tǒng)計(jì)函數(shù)在“項(xiàng)目4”的“4.4.4【實(shí)訓(xùn)4-11】統(tǒng)計(jì)與分組統(tǒng)計(jì)”詳細(xì)討論過(guò)2．?dāng)?shù)學(xué)函數(shù)與其他編程語(yǔ)言類(lèi)似Select

sqrt(3);--求平方根Select

round(3.14159,3);--保留3位小數(shù)，四舍五入3．字符串函數(shù)【實(shí)訓(xùn)7-3】7.1.4【實(shí)訓(xùn)7-3】使用內(nèi)置函數(shù)（續(xù)）3．字符串函數(shù)（續(xù)）運(yùn)行下述語(yǔ)句，觀察內(nèi)置函數(shù)的運(yùn)行結(jié)果select

length("abc漢字");--運(yùn)行結(jié)果是9（返回字節(jié)數(shù)）select

char_length("abc漢字");--運(yùn)行結(jié)果是5（返回字符數(shù)）select

substring("12345678",2,3);--234select

substring("12345678",

2);--

2345678select

replace("12345678",

"23",

"abc");

--

1abc45678select

concat("12345678",

"23",

"abc");

--

1234567823abcselect

ascii("abc");

--

97select

char(97);

--

a注意：字符串連接不能用加號(hào)，要用concat函數(shù)，這個(gè)函數(shù)可以連接任意多個(gè)字符串Select

concat("abc",

"xyz");

--

abcxyzSelect

concat("abc",

"xyz",

"123");

--

abcxyz123Select

concat("abc",

"xyz",

"123",

"abc");--

abcxyz123abc加號(hào)只用于算術(shù)加法運(yùn)算，例如Select

12

+

15;

--

27Select

"12"+"15";--27，字符串中的數(shù)字轉(zhuǎn)換為數(shù)字類(lèi)型后相加Select

‘a(chǎn)bc’+15;--15，0+15=15，字符串中的字母無(wú)法轉(zhuǎn)換，轉(zhuǎn)換結(jié)果為0Select

"abc"+"xyz";--0，0+0=07.1.4【實(shí)訓(xùn)7-3】使用內(nèi)置函數(shù)（續(xù)）--加上50天--減去500小4．日期和時(shí)間函數(shù)對(duì)日期、時(shí)間以及日期時(shí)間進(jìn)行操作Select

adddate(‘2020-01-01’,interval

50

day);Select

subdate(‘2020-01-01’,interval

500

hour);時(shí)5．系統(tǒng)函數(shù)Insert

into

shop_province

values

(null,"XX省");Select

last_insert_id();

--返回新插入行的主鍵值Delete

from

shop_province

where

id_shop_province>5;Select

row_count();

--返回實(shí)際刪除的行數(shù)6．轉(zhuǎn)換函數(shù)在不同類(lèi)型之間轉(zhuǎn)換Select

cast("1.234"

as

decimal(5,2));Select

convert("1.234",

decimal(5,2));7.1.4【實(shí)訓(xùn)7-3】使用內(nèi)置函數(shù)（續(xù)）6．轉(zhuǎn)換函數(shù)（續(xù)）日期轉(zhuǎn)換為字符串date_format(date,

format)例如下述代碼Select

date_format(now(),

"%Y-%m-%d

%H:%i:%s");

--

2022-11-08

13:29:41字符串轉(zhuǎn)換為日期str_to_date(date,

format)通常情況下，字符串的日期時(shí)間可以直接用在insert和update語(yǔ)句中但是如果需要對(duì)得到明確的日期時(shí)間值，就需要轉(zhuǎn)換。例如下述代碼Select

str_to_date(‘2022-11-08

13:29:41’,‘%Y-%m-%d

%H:%i:%s’);--返回的是日期時(shí)間類(lèi)型目錄任務(wù)2存儲(chǔ)函數(shù)任務(wù)1學(xué)習(xí)MySQL編程任務(wù)2存儲(chǔ)函數(shù)7.2.1存儲(chǔ)程序7.2.2【實(shí)訓(xùn)7-5】存儲(chǔ)函數(shù)7.2.3【實(shí)訓(xùn)7-6】管理存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程任務(wù)4使用觸發(fā)器任務(wù)5了解事務(wù)和鎖7.2.1存儲(chǔ)程序在學(xué)習(xí)存儲(chǔ)函數(shù)之前，先了解一下存儲(chǔ)程序的概念存儲(chǔ)程序（Stored

programs）

是存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段程序，由單行語(yǔ)句或多行語(yǔ)句組成，并給予一個(gè)命名，通過(guò)該名字來(lái)運(yùn)行或管理這些語(yǔ)句。MySQL的存儲(chǔ)程序有下述4種

存儲(chǔ)函數(shù)和存儲(chǔ)函數(shù)又稱為存儲(chǔ)例程（Stored

routine），僅僅是因?yàn)樵跀?shù)據(jù)庫(kù)備份中有一個(gè)選薦是備份存儲(chǔ)函數(shù)和存儲(chǔ)函數(shù)的，所以為其命名了一個(gè)名稱存儲(chǔ)例程存儲(chǔ)函數(shù)（Stored

function）：本節(jié)講解存儲(chǔ)過(guò)程（Stored

procedure）：下節(jié)講解觸發(fā)器（Trigger）：再下一節(jié)講解事件（Event）：項(xiàng)目11講解存儲(chǔ)程序{}存儲(chǔ)例程（Stored

routine）存儲(chǔ)程序概述4種存儲(chǔ)程序簡(jiǎn)介存儲(chǔ)函數(shù)它返回一個(gè)計(jì)算結(jié)果，該結(jié)果可以用在表達(dá)式里（例如Select語(yǔ)句中的計(jì)算列）就像大多數(shù)語(yǔ)言中的函數(shù)一樣存儲(chǔ)過(guò)程它不直接返回結(jié)果，但可以用來(lái)完成一般的運(yùn)算或是用Select語(yǔ)句生成一個(gè)結(jié)果集并傳遞回調(diào)用方它被call命令調(diào)用觸發(fā)器它與數(shù)據(jù)表相關(guān)聯(lián)，不能被直接運(yùn)行它是在該表執(zhí)行Insert、Delete或Update語(yǔ)句時(shí)觸發(fā)它的執(zhí)行事件它也不能被直接運(yùn)行，而是根據(jù)設(shè)置的時(shí)間，在設(shè)置的預(yù)定時(shí)刻自動(dòng)執(zhí)行存儲(chǔ)程序的優(yōu)點(diǎn)

存儲(chǔ)程序（特別是存儲(chǔ)例程和觸發(fā)器）具有如下優(yōu)點(diǎn)，曾經(jīng)受到廣泛應(yīng)用，有些項(xiàng)目甚至將所有的業(yè)務(wù)邏輯都寫(xiě)在各種存儲(chǔ)程序中。

編譯后執(zhí)行：存儲(chǔ)程序經(jīng)過(guò)編譯之后會(huì)比單獨(dú)的SQL語(yǔ)句一條一條執(zhí)行要快，可以將編譯后的存儲(chǔ)程序緩存起來(lái)，從而提高系統(tǒng)性能。減少網(wǎng)絡(luò)傳輸：存儲(chǔ)程序保存在MySQL服務(wù)器中，距離數(shù)據(jù)最近，可以減少網(wǎng)絡(luò)傳輸，從而提高效率。

代碼復(fù)用：存儲(chǔ)程序被創(chuàng)建后，可以被不同的進(jìn)程甚至是不同的語(yǔ)言調(diào)用，避免開(kāi)發(fā)人員編寫(xiě)相同的SQL語(yǔ)句，從而提高開(kāi)發(fā)效率。流程控制：可以使用流程程控語(yǔ)句實(shí)現(xiàn)復(fù)雜的判斷和運(yùn)算，編寫(xiě)比較通用的存儲(chǔ)程序，從而提高靈活性。

隨著新技術(shù)的出現(xiàn)，存儲(chǔ)程序的優(yōu)勢(shì)正在逐漸喪失，而缺點(diǎn)卻變得日益突出，主要是開(kāi)發(fā)和調(diào)試?yán)щy、移植性差、不適應(yīng)新技術(shù)的應(yīng)用。因此，存儲(chǔ)程序已被許多公司限制使用。語(yǔ)句塊和語(yǔ)句分隔符從語(yǔ)法格式來(lái)說(shuō)，存儲(chǔ)程序有兩種（以存儲(chǔ)程序?yàn)槔┒鄺l語(yǔ)句需要用begin和end將多條語(yǔ)句括起來(lái)，成為一個(gè)語(yǔ)句塊上述代碼出現(xiàn)一個(gè)問(wèn)題，就是語(yǔ)句塊內(nèi)和語(yǔ)句塊外的行末分號(hào)有不同的作用，需要區(qū)分它們?yōu)榱藚^(qū)分語(yǔ)句塊內(nèi)和語(yǔ)句塊外的行末分號(hào)，MySQL特地提供一條命令，用于重新設(shè)定行末分隔符這條命令不能以分號(hào)結(jié)束，甚至行末不能加注釋（加行末注釋，有可能引起出錯(cuò)）單語(yǔ)句存儲(chǔ)程序的語(yǔ)法格式Create

procedure存儲(chǔ)過(guò)程名(參數(shù))

[存儲(chǔ)過(guò)程特征]一條語(yǔ)句;多語(yǔ)句存儲(chǔ)程序的語(yǔ)法格式Delimiter

%%Create

procedure存儲(chǔ)過(guò)程名(參數(shù))

[存儲(chǔ)過(guò)程特征]begin第一條語(yǔ)句;……第n條語(yǔ)句;--這是語(yǔ)句塊內(nèi)的行末分號(hào)end;--這是語(yǔ)句塊外的行末分號(hào)這段代碼有問(wèn)題！語(yǔ)句塊和語(yǔ)句分隔符（續(xù)）因此，多語(yǔ)句存儲(chǔ)程序的正確的語(yǔ)法格式如下（以存儲(chǔ)程序?yàn)槔〥elimiter

%%Create

procedure存儲(chǔ)過(guò)程名(參數(shù))

[存儲(chǔ)過(guò)程特征]begin第1條語(yǔ)句;第2條語(yǔ)句;……第n條語(yǔ)句;

end%%Delimiter

;紅框內(nèi)是一條語(yǔ)句（創(chuàng)建存儲(chǔ)程序，共8行），%%是這條語(yǔ)句的分隔符（標(biāo)記語(yǔ)句結(jié)束）藍(lán)框內(nèi)是語(yǔ)句塊，每行都要用分號(hào)結(jié)束7.2.2【實(shí)訓(xùn)7-5】存儲(chǔ)函數(shù)創(chuàng)建存儲(chǔ)函數(shù)的語(yǔ)法格式如下Create

function存儲(chǔ)函數(shù)名稱(參數(shù)列表)returns返回值類(lèi)型[存儲(chǔ)函數(shù)特征]存儲(chǔ)函數(shù)體return返回值函數(shù)名通常加上前綴f_，避免與關(guān)鍵字沖突每一個(gè)參數(shù)都要指定數(shù)據(jù)類(lèi)型參數(shù)列表后是“returns返回值類(lèi)型”return動(dòng)詞的單數(shù)形式，詞尾加s，主語(yǔ)是函數(shù)名，是單數(shù)的用于說(shuō)明函數(shù)的返回值類(lèi)型在函數(shù)體中必須有至少一條return語(yǔ)句return動(dòng)詞的原型，無(wú)主句的動(dòng)詞用動(dòng)詞原型用于實(shí)際返回函數(shù)的值存儲(chǔ)函數(shù)特征在后面講解【實(shí)訓(xùn)7-5】微課：7-1存儲(chǔ)函數(shù)1．單語(yǔ)句的存儲(chǔ)函數(shù)單語(yǔ)句的存儲(chǔ)函數(shù)的語(yǔ)法格式比較簡(jiǎn)單下述語(yǔ)句用于創(chuàng)建存儲(chǔ)函數(shù)f_add()Create

function

f_add(_a

int,

_b

int)returns

int

no

sqlreturn

_a+_b;--函數(shù)體只有一行語(yǔ)句其中，no

sql是存儲(chǔ)函數(shù)特征，它的含義是這個(gè)存儲(chǔ)函數(shù)不包含SQL語(yǔ)句下述語(yǔ)句使用這個(gè)存儲(chǔ)函數(shù)Select

f_add(3,5);--使用存儲(chǔ)函數(shù)1．單語(yǔ)句的存儲(chǔ)函數(shù)（續(xù)）單語(yǔ)句的存儲(chǔ)函數(shù)的函數(shù)體也可以是單條SQL語(yǔ)句例如下述代碼返回Select的查詢結(jié)果Create

function

f_get_total()returns

float

reads

sql

datareturn

(Select

sum(col_ammount)

from

shop_order_head);其中，reads

sql

data是存儲(chǔ)函數(shù)特征，含義是這個(gè)存儲(chǔ)函數(shù)里有讀數(shù)據(jù)的SQL語(yǔ)句return語(yǔ)句中的Select語(yǔ)句要用圓括號(hào)括起來(lái)，并且只能返回一個(gè)值（單行單列）下述語(yǔ)句使用這個(gè)存儲(chǔ)函數(shù)Select

f_get_total();--使用存儲(chǔ)函數(shù)2．多行語(yǔ)句的存儲(chǔ)函數(shù)如果存儲(chǔ)函數(shù)體有多行語(yǔ)句用begin和end關(guān)鍵字將多條語(yǔ)句括起來(lái)用Delimiter指定新的分隔符例如下述代碼定義了一個(gè)存儲(chǔ)函數(shù)，它返回指定客戶（主鍵）的訂單數(shù)和購(gòu)買(mǎi)商品數(shù)，并將結(jié)果作為一個(gè)字符串返回Delimiter

%%Create

function

f_get_count(_id

int)

returns

varchar(20)

reads

sql

databegindeclare

var_order_count

int;--訂單數(shù)量declare

var_goods_count

int;--購(gòu)買(mǎi)商品件數(shù)select

count(*)

into

var_order_count

from

shop_order_headwhere

id_shop_customer=_id

and

col_status>0;--查詢訂單數(shù)量select

sum(col_quantity)

into

var_goods_countfrom

shop_order_line

inner

join

shop_order_headon

shop_order_line.id_shop_order_head

=

shop_order_head.id_shop_order_headwhere

shop_order_head.id_shop_customer=_id

and

col_status>0;--查詢購(gòu)買(mǎi)商品件數(shù)return

concat("(",

var_goods_count,

"/",

var_order_count,

")");end%%Delimiter

;定義新的分隔符使用新的分隔符恢復(fù)默認(rèn)分隔符{塊中的分號(hào)是存儲(chǔ)過(guò)程內(nèi)部的分隔符號(hào)Select

f_get_count(2);--使用存儲(chǔ)函數(shù)7.2.3【實(shí)訓(xùn)7-6】管理存儲(chǔ)函數(shù)

存儲(chǔ)函數(shù)是一個(gè)數(shù)據(jù)庫(kù)對(duì)象，它與表和視圖一樣，保存在數(shù)據(jù)庫(kù)中，可以創(chuàng)建、修改和丟棄，因此稱為“存儲(chǔ)函數(shù)”所有數(shù)據(jù)庫(kù)對(duì)象用下述語(yǔ)句創(chuàng)建：用Create

語(yǔ)句變更：用Alter

語(yǔ)句丟棄：用Drop

語(yǔ)句列出：用Show

語(yǔ)句1．列出存儲(chǔ)函數(shù)列出數(shù)據(jù)庫(kù)eshop中的所有存儲(chǔ)函數(shù)2．查看存儲(chǔ)函數(shù)的定義查看存儲(chǔ)函數(shù)f_get_count的定義。Show

create

function

f_get_count;3．丟棄存儲(chǔ)函數(shù)丟棄存儲(chǔ)函數(shù)Show

function

status

where

db

=

"eshop";Drop

function

[if

exists]存儲(chǔ)函數(shù)名;

加上if

exists后，可以保證即使存儲(chǔ)函數(shù)不存在也不會(huì)出錯(cuò)【實(shí)訓(xùn)7-6】7.2.3【實(shí)訓(xùn)7-6】管理存儲(chǔ)函數(shù)（續(xù)）4．修改存儲(chǔ)函數(shù)不能修改存儲(chǔ)函數(shù)的定義。如果要修改存儲(chǔ)函數(shù)的定義，要先刪除，后重新創(chuàng)建?？梢孕薷拇鎯?chǔ)函數(shù)的函數(shù)特征，語(yǔ)法格式如下。Alter

function存儲(chǔ)函數(shù)名存儲(chǔ)函數(shù)特征;創(chuàng)建存儲(chǔ)函數(shù)時(shí)可以指定函數(shù)特征，創(chuàng)建存儲(chǔ)函數(shù)后，還能用Alter修改其函數(shù)特征。deterministic或not

deterministic：前者指明執(zhí)行結(jié)果是確定的，相同的參數(shù)必定得到相同的結(jié)果。后者指明執(zhí)行結(jié)果是不確定的，例如含有日期時(shí)間函數(shù)或隨機(jī)數(shù)函數(shù)等，即使參數(shù)相同每次執(zhí)行都會(huì)有不同的結(jié)果no

sql：表示不包含sql語(yǔ)句contains

sql：表示包含sql語(yǔ)句，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句reads

sql

data：表示包含讀（查詢）數(shù)據(jù)的語(yǔ)句modifies

sql

data：表示包含修改（增刪改）數(shù)據(jù)的語(yǔ)句sql

security{definer

|

invoker}：指明誰(shuí)有權(quán)限執(zhí)行這個(gè)存儲(chǔ)函數(shù)comment"注釋內(nèi)容"：指定注釋。例如下述語(yǔ)句修改存儲(chǔ)函數(shù)f_add()的函數(shù)特征為不包含SQL語(yǔ)句，任何人都可以執(zhí)行。function

f_add

no

sql,

sql

security

invoker;目錄任務(wù)3使用存儲(chǔ)過(guò)程任務(wù)1學(xué)習(xí)MySQL編程任務(wù)2存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程【實(shí)訓(xùn)7-7】創(chuàng)建和使用存儲(chǔ)過(guò)程【實(shí)訓(xùn)7-8】存儲(chǔ)過(guò)程的參數(shù)【實(shí)訓(xùn)7-9】管理存儲(chǔ)過(guò)程任務(wù)4使用觸發(fā)器任務(wù)5了解事務(wù)和鎖7.3.1【實(shí)訓(xùn)7-7】創(chuàng)建和使用存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程的格式如下Create

procedure存儲(chǔ)過(guò)程名稱(參數(shù)列表)

[存儲(chǔ)過(guò)程特征]存儲(chǔ)過(guò)程體存儲(chǔ)過(guò)程名通常加上前綴p_，避免與關(guān)鍵字沖突存儲(chǔ)過(guò)程特征與存儲(chǔ)函數(shù)特征完全相同，見(jiàn)“7.2.3【實(shí)訓(xùn)7-6】管理存儲(chǔ)函數(shù)”先舉個(gè)單語(yǔ)句的例子，下述代碼用于創(chuàng)建一個(gè)名為“p_goods_by_catgory”的存儲(chǔ)過(guò)程Create

procedure

p_goods_by_catgory(_id

int)Select

*

from

shop_goods

where

id_shop_category

=

_id;通過(guò)Call關(guān)鍵字調(diào)用這個(gè)存儲(chǔ)過(guò)程的語(yǔ)句如下Call

p_goods_by_catgory(2);執(zhí)行存儲(chǔ)過(guò)程就是執(zhí)行存儲(chǔ)過(guò)程體中的語(yǔ)句，在這個(gè)例子中，與直接使用Select語(yǔ)句的區(qū)別是商品類(lèi)型是通過(guò)存儲(chǔ)過(guò)程的參數(shù)傳入的對(duì)調(diào)用者來(lái)說(shuō)，不需要知道數(shù)據(jù)庫(kù)設(shè)計(jì)的細(xì)節(jié)就可以得到查詢的結(jié)果微課：7-2存儲(chǔ)過(guò)程【實(shí)訓(xùn)7-7】7.3.1【實(shí)訓(xùn)7-7】創(chuàng)建和使用存儲(chǔ)過(guò)程（續(xù)）現(xiàn)在舉一個(gè)多行語(yǔ)句的例子，將上述代碼修改一下，加上if條件，成為多行語(yǔ)句Drop

procedure

if

exists

p_goods_by_catgory;--先丟棄它Delimiter

%%Create

procedure

p_goods_by_catgory(_id

int)beginif

(_id

>

0)

thenselect

*

from

shop_goods

where

id_shop_category

=

_id;elseselect

*

from

shop_goods;end

if;end%%Delimiter

;用大于0的參數(shù)調(diào)用，執(zhí)行查詢id為參數(shù)值的查詢Call

p_goods_by_catgory(2);用參數(shù)0調(diào)用，執(zhí)行沒(méi)有查詢條件的查詢Call

p_goods_by_catgory(0);判斷單行語(yǔ)句還是多行語(yǔ)句的原則單語(yǔ)句：存儲(chǔ)過(guò)程體內(nèi)沒(méi)有分號(hào)

多行語(yǔ)句：存儲(chǔ)過(guò)程體內(nèi)含有分號(hào)7.3.2【實(shí)訓(xùn)7-8】存儲(chǔ)過(guò)程的參數(shù)存儲(chǔ)過(guò)程的參數(shù)無(wú)參有參輸入型：（默認(rèn)）輸出型：用out關(guān)鍵字修飾輸入輸出型：用inout關(guān)鍵字修飾{{1．輸入型參數(shù)Create

procedure存儲(chǔ)過(guò)程名(變量名變量類(lèi)型)存儲(chǔ)過(guò)程體2．輸出型參數(shù)Create

procedure存儲(chǔ)過(guò)程名(out

變量名變量類(lèi)型)存儲(chǔ)過(guò)程體3．輸入輸出型參數(shù)Create

procedure存儲(chǔ)過(guò)程名(inout

變量名變量類(lèi)型)存儲(chǔ)過(guò)程體【實(shí)訓(xùn)7-8】輸出型參數(shù)1．輸入型參數(shù)見(jiàn)前面的講解2．輸出型參數(shù)下述存儲(chǔ)過(guò)程通過(guò)參數(shù)返回有效訂單的總金額Create

procedure

p_total_ammout(out

_ammount

float)select

sum(col_ammount)

into

_ammount

from

shop_order_headwhere

col_status>0;通過(guò)Call關(guān)鍵字調(diào)用這個(gè)存儲(chǔ)過(guò)程時(shí)通過(guò)一個(gè)用戶變量來(lái)接收輸出型參數(shù)的值調(diào)用后再處理這個(gè)變量Set

@ammount

=0;--必須要有一個(gè)變量用于接收輸出的數(shù)據(jù)

Call

p_total_ammout(@ammount

);Select

@ammount;--處理這個(gè)變量輸入輸出型參數(shù)3．輸入輸出型參數(shù)下述代碼的參數(shù)是輸入輸出型的它的輸入是客戶id值它的輸出是該客戶的有效訂單數(shù)輸入和輸出使用同一個(gè)變量_id。Create

procedure

p_total_quantity(inout

_id

int)select

count(*)

into

_id

from

shop_order_headwhere

id_shop_customer

=

_id

and

col_status>0;通過(guò)Call關(guān)鍵字調(diào)用這個(gè)存儲(chǔ)過(guò)程時(shí)通過(guò)一個(gè)用戶變量來(lái)接收輸出型參數(shù)的值同時(shí)這個(gè)用戶變量也用于傳入?yún)?shù)的值Set

@id=1;

--這個(gè)變量的值用于輸入

Call

p_total_quantity(@id);Select

@id;

--調(diào)用后同一個(gè)變量含有輸出值7.3.3【實(shí)訓(xùn)7-9】管理存儲(chǔ)過(guò)程管理存儲(chǔ)過(guò)程與管理存儲(chǔ)函數(shù)完全相同，因?yàn)樗鼈兌紝儆谕活?lèi)“存儲(chǔ)例程”1．列出存儲(chǔ)過(guò)程列出數(shù)據(jù)庫(kù)eshop中的所有存儲(chǔ)過(guò)程Show

procedure

status

where

db

=

"eshop";2．查看存儲(chǔ)過(guò)程的定義查看存儲(chǔ)過(guò)程的定義。Show

create

procedure存儲(chǔ)過(guò)程名;3．丟棄存儲(chǔ)過(guò)程丟棄存儲(chǔ)過(guò)程Drop

procedure

[if

exists]存儲(chǔ)過(guò)程名;4．修改存儲(chǔ)過(guò)程不能修改存儲(chǔ)過(guò)程的定義。如果要修改存儲(chǔ)過(guò)程的定義，要先刪除，后重新創(chuàng)建?？梢孕薷拇鎯?chǔ)過(guò)程的過(guò)程特征，語(yǔ)法格式如下。Alter

procedure存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程特征;【實(shí)訓(xùn)7-9】目錄任務(wù)4使用觸發(fā)器任務(wù)1學(xué)習(xí)MySQL編程任務(wù)2存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程任務(wù)4使用觸發(fā)器觸發(fā)器概述【實(shí)訓(xùn)7-10】before觸發(fā)器【實(shí)訓(xùn)7-11】after觸發(fā)器觸發(fā)器中的新行和舊行【實(shí)訓(xùn)7-12】管理觸發(fā)器數(shù)據(jù)庫(kù)對(duì)象總結(jié)任務(wù)5了解事務(wù)和鎖7.4.1觸發(fā)器概述實(shí)現(xiàn)復(fù)雜約束觸發(fā)器可以實(shí)現(xiàn)復(fù)雜的約束。例如觸發(fā)器可以引用其他表中的列，通過(guò)其他表中的數(shù)據(jù)來(lái)決定如何操作。比較數(shù)據(jù)狀態(tài)觸發(fā)器可以比較數(shù)據(jù)修改前后的差異，并根據(jù)這些差異采取不同的操作。方便統(tǒng)一管理觸發(fā)器集中保存在服務(wù)器端，方便統(tǒng)一管理和維護(hù)。維護(hù)困難觸發(fā)器是不能直接調(diào)用的，從應(yīng)用層面難以覺(jué)察底層觸發(fā)器的運(yùn)行情況，會(huì)造成調(diào)試和排錯(cuò)困難，有時(shí)會(huì)引起莫名其妙的后果。可移植性差不同的數(shù)據(jù)庫(kù)管理系統(tǒng)的觸發(fā)器語(yǔ)法差別較大，因此可移植性很差。占用資源觸發(fā)器占用服務(wù)器端較多的資源，對(duì)服務(wù)器造成較大的壓力，有時(shí)會(huì)嚴(yán)重影響服務(wù)器的性能觸發(fā)器不能被直接調(diào)用，觸發(fā)其執(zhí)行的事件主要有Insert、Update和Delete觸發(fā)器的優(yōu)點(diǎn) ?

觸發(fā)器的缺點(diǎn)觸發(fā)器的功能十分強(qiáng)大，優(yōu)勢(shì)明顯，但缺點(diǎn)也非常突出，越來(lái)越多的公司開(kāi)始限制觸發(fā)器的使用，而改用新的替代技術(shù)7.4.1觸發(fā)器概述（續(xù)）觸發(fā)器的類(lèi)型：根據(jù)被激活的時(shí)機(jī)的不同，分為兩種before觸發(fā)器：在觸發(fā)它的語(yǔ)句之前執(zhí)行，可以進(jìn)行條件判斷，以決定是否執(zhí)行觸發(fā)它的語(yǔ)句after觸發(fā)器：在觸發(fā)它的語(yǔ)句之后執(zhí)行，可以在觸發(fā)語(yǔ)句執(zhí)行之后完成一個(gè)或更多的操作觸發(fā)條件：根據(jù)被激活的條件的不同，分為三種insert觸發(fā)器：在插入行的前或后時(shí)觸發(fā)觸發(fā)器的執(zhí)行update觸發(fā)器：在更新行的前或后時(shí)觸發(fā)觸發(fā)器的執(zhí)行delete觸發(fā)器：在刪除行的前或后時(shí)觸發(fā)觸發(fā)器的執(zhí)行因此，每張表最多定義6個(gè)不同的觸發(fā)器，對(duì)同一張表不能重復(fù)定義相同的觸發(fā)器insert觸發(fā)器update觸發(fā)器delete觸發(fā)器before觸發(fā)器√√√after觸發(fā)器√√√7.4.1觸發(fā)器概述（續(xù)二）創(chuàng)建觸發(fā)器Create

trigger觸發(fā)器名<before

|

after><insert

|

update

|

delete>

on表名for

each

row觸發(fā)器體為避免觸發(fā)器名與關(guān)鍵字沖突，觸發(fā)器名通常加上前綴t_觸發(fā)器類(lèi)型的6種組合before

insertbefore

updatebefore

deleteafter

insertafter

updateafter

deleteon表名：每一張表最多可以定義6個(gè)觸發(fā)器，每種組合一個(gè)

for

each

row：影響多行時(shí)，每一行都會(huì)觸發(fā)一次，例如刪除了3行，就觸發(fā)3次delete觸發(fā)器7.4.2【實(shí)訓(xùn)7-10】before觸發(fā)器先準(zhǔn)備測(cè)試用的表tbl_personUse

test;Drop

table

if

exists

tbl_person;

--

丟棄表時(shí)，同時(shí)丟棄表上的觸發(fā)器Create

table

tbl_person(

--

創(chuàng)建表id

int(11)

not

null

primary

key

auto_increment,name

varchar(20),age

tinyint(4));采用觸發(fā)器技術(shù)，檢查插入或更新數(shù)據(jù)時(shí)，年齡是否超出規(guī)定范圍當(dāng)年齡小于0或大于120時(shí)設(shè)置出錯(cuò)信息，并中止插入或更新數(shù)據(jù)否則正常執(zhí)行插入或更新數(shù)據(jù)【實(shí)訓(xùn)7-10】微課：7-3觸發(fā)器7.4.2【實(shí)訓(xùn)7-10】before觸發(fā)器（續(xù)）編寫(xiě)思路因?yàn)橐獩Q定是否允許插入/更新數(shù)據(jù)，所以要用before觸發(fā)器因?yàn)橐獧z查插入和更新操作，所以要分別寫(xiě)兩個(gè)觸發(fā)器（刪除操作與此無(wú)關(guān)）兩個(gè)觸發(fā)器基本相同，僅僅是提示信息不同當(dāng)new.age<0

or

new.age>120時(shí)設(shè)置出錯(cuò)信息，并中止激發(fā)它的語(yǔ)句繼續(xù)執(zhí)行Delimiter

%%Create

trigger

t_before_insert_person

before

insert

on

tbl_person

for

each

rowbeginif

new.age<0

or

new.age>120

thensignal

sqlstate

"HY000"set

message_text="插入時(shí)，年齡范圍是0～120。";

end

if;end%%Create

trigger

t_before_update_person

before

update

on

tbl_person

for

each

rowbeginif

new.age<0

or

new.age>120

thensignal

sqlstate

"HY000"set

message_text="更新時(shí)，年齡范圍是0～120。";

end

if;end%%Delimiter

;signal

sqlstate

‘HY000’setmessage_text=“出錯(cuò)信息";否則正常執(zhí)行插入或更新數(shù)據(jù)7.4.2【實(shí)訓(xùn)7-10】before觸發(fā)器（續(xù)二）觸發(fā)器的效果

不論是通過(guò)命令行、圖形界面客戶端，還是其他任何途徑，進(jìn)行插入或更新操作，都會(huì)觸發(fā)它的執(zhí)行，從而絕對(duì)保證不會(huì)出現(xiàn)非法的數(shù)據(jù)7.4.3【實(shí)訓(xùn)7-11】after觸發(fā)器先準(zhǔn)備測(cè)試用的表tbl_person（與前一實(shí)訓(xùn)相同）Use

test;Drop

table

if

exists

tbl_person;Create

table

tbl_person(id

int(11)

not

null

primary

key

auto_increment,name

varchar(20),

age

tinyint(4));再準(zhǔn)備一張表tbl_log，用于記錄用戶的操作Drop

table

if

exists

tbl_log;Create

table

tbl_log(id

int(11)

not

null

primary

key

auto_increment,log_text

varchar(500),

log_date

datetime);采用觸發(fā)器技術(shù)，當(dāng)插入或更新數(shù)據(jù)時(shí)，記錄用戶的操作，包括如下數(shù)據(jù)插入時(shí)，記錄插入的時(shí)間，插入的數(shù)據(jù)是什么更新時(shí)，記錄更新的時(shí)間，更新前的數(shù)據(jù)是什么樣的，更新后的數(shù)據(jù)是什么【實(shí)訓(xùn)7-11】7.4.3【實(shí)訓(xùn)7-11】after觸發(fā)器（續(xù)）編寫(xiě)思路因?yàn)橐涗洸僮鞯膬?nèi)容，所以要用after觸發(fā)器因?yàn)橐涗洸迦牒透虏僮?，所以要分別寫(xiě)兩個(gè)觸發(fā)器（沒(méi)有要求記錄刪除操作）兩個(gè)觸發(fā)器基本相同，僅僅是記錄的內(nèi)容不同插入和更新操作激發(fā)它的執(zhí)行，是無(wú)條件的向日志表（

tbl_log）插入一行，包括操作的內(nèi)容的描述和操作發(fā)生的時(shí)間如果是插入記錄插入新行的姓名和年齡如果是更新記錄更新前的姓名和年齡，以及更新后的姓名和年齡Delimiter

%%Create

trigger

t_after_insert_person

after

insert

on

tbl_person

for

each

rowBeginInsert

into

tbl_log

values(null,concat("插入新行：id=",new.id,",姓名=",new.name,",年齡=",new.age),now());End%%Create

trigger

t_after_update_person

after

update

on

tbl_person

for

each

rowBegininsert

into

tbl_log

values(null,concat("更新行：id=",new.id,",姓名=",new.name,"(old=",old.name,"),年齡=",new.age,"(old=",old.age,")"),

now());End%%Delimiter

;7.4.3【實(shí)訓(xùn)7-11】after觸發(fā)器（續(xù)二）觸發(fā)器的效果

不論是通過(guò)命令行、圖形界面客戶端，還是其他任何途徑，進(jìn)行插入或更新操作，都會(huì)觸發(fā)它的執(zhí)行，從而絕對(duì)保證能夠記錄每一步操作，進(jìn)行數(shù)據(jù)跟蹤

從記錄下來(lái)的操作日志中可以看到，“王五”的年齡一開(kāi)始輸入的是22歲，后來(lái)在

12:09:22時(shí)被改為20歲的。如果有身份認(rèn)證系統(tǒng)，還可以記錄是誰(shuí)修改的。如果還要記錄刪除操作，該如何做？7.4.4觸發(fā)器中的新行和舊行

在觸發(fā)器的觸發(fā)體中，有兩個(gè)特殊的對(duì)象new和old。對(duì)象new表示將要插入的新行，對(duì)象old表示將要?jiǎng)h除的舊行。在觸發(fā)器中通過(guò)new和old可以方便地獲取新行或舊行的列的值，并進(jìn)行判斷或記錄。具體的代碼例子見(jiàn)前面講過(guò)的【實(shí)訓(xùn)7-11】after觸發(fā)器

對(duì)于更新操作，可以理解為將舊行替換為新行，因此這時(shí)還可以比較新行和舊行的值，根據(jù)比較的結(jié)果進(jìn)行操作。7.4.5【實(shí)訓(xùn)7-12】管理觸發(fā)器管理觸發(fā)器與管理存儲(chǔ)例程比較相似1．列出觸發(fā)器Show

triggers;2．查看觸發(fā)器的定義Show

create

trigger觸發(fā)器名;3．丟棄觸發(fā)器Drop

trigger

[if

exists]觸發(fā)器名;4．不能修改觸發(fā)器不能修改觸發(fā)器的定義。如果要修改觸發(fā)器的定義，要先刪除，后重新創(chuàng)建觸發(fā)器沒(méi)有觸發(fā)器特征可以修改因此，觸發(fā)器沒(méi)有可以修改的內(nèi)容【實(shí)訓(xùn)7-12】7.4.6數(shù)據(jù)庫(kù)對(duì)象總結(jié)存儲(chǔ)函數(shù)（Stored

function）存儲(chǔ)過(guò)程（Stored

procedure）觸發(fā)器（Trigger）視圖（View）：項(xiàng)目6講解（視圖不是存儲(chǔ)程序）{事件（Event）：項(xiàng)目11講解數(shù)據(jù)庫(kù)對(duì)象}存儲(chǔ)例程（Stored

routine）表（Table)存儲(chǔ)對(duì)象{目錄任務(wù)5了解事務(wù)和鎖任務(wù)1學(xué)習(xí)MySQL編程任務(wù)2存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程任務(wù)4使用觸發(fā)器任務(wù)5了解事務(wù)和鎖事務(wù)【實(shí)訓(xùn)7-13】體驗(yàn)事務(wù)控制語(yǔ)句事務(wù)隔離和鎖機(jī)制7.5.1事務(wù)事務(wù)是什么？

事務(wù)是一個(gè)最小的、不可再分的工作單元，通常它對(duì)應(yīng)一個(gè)完整的業(yè)務(wù)，其中包含了多個(gè)數(shù)據(jù)操縱（如insert、update、delete等）語(yǔ)句，共同完成這個(gè)業(yè)務(wù)。

下面用一個(gè)例子來(lái)說(shuō)明事務(wù)的概念。首先創(chuàng)建一個(gè)簡(jiǎn)單的銀行表（bank），其中有兩個(gè)賬戶A和B，分別向其中存入2

000元和3

000元。Create

table

bank(

--

說(shuō)明概念的代碼，無(wú)須執(zhí)行account

varchar(20)

not

null

primary

key,

--

賬戶，主鍵ammount

decimal(10,2)

--

存款數(shù)目);Insert

into

bank

values("A",

2000);

--

賬戶A有存款2000元Insert

into

bank

values("B",

3000);

--

賬戶B有存款3000元存款總數(shù)是5

000元?，F(xiàn)在賬戶A想轉(zhuǎn)500元給賬戶B，該操作的代碼如下。Set

@transfer=500;

--轉(zhuǎn)賬的金額Update

bank

set

ammount=ammount-@transfer--轉(zhuǎn)賬第一步，從賬戶A中減去500元

where

account="A";--特定時(shí)間點(diǎn)Update

bank

set

ammount=ammount+@transfer--轉(zhuǎn)賬第二步，向賬戶B里加上500元

where

account="B";Select

*

from

bank;兩種可能的意外情況第一種情況在第一條Update語(yǔ)句執(zhí)行后，第二條Update語(yǔ)句還沒(méi)有執(zhí)行時(shí)，由于某種原因（例如停電）在這個(gè)特定的時(shí)間點(diǎn)出現(xiàn)了一個(gè)致命的錯(cuò)誤，而使第二條Update語(yǔ)句無(wú)法執(zhí)行這時(shí)轉(zhuǎn)賬沒(méi)有完成，賬戶A的錢(qián)被扣除了，賬戶B的錢(qián)卻沒(méi)有增加這種情況出現(xiàn)的概率極其微小，但并不是不可能出現(xiàn)，一旦出現(xiàn)，將大大影響銀行的信譽(yù)第二種情況另一個(gè)用戶（例如銀行經(jīng)理）想要查詢銀行的存款總額，如果他的查詢操作是在第一條Update語(yǔ)句執(zhí)行完后的這個(gè)特定時(shí)間點(diǎn)進(jìn)行的，那么查詢到的結(jié)果是存款總額為4

500元，而不是5

000元引起這個(gè)錯(cuò)誤的原因是兩個(gè)用戶（轉(zhuǎn)賬用戶和銀行經(jīng)理用戶）的操作在時(shí)間上極其接近，而系統(tǒng)又沒(méi)有任何防范措施。

因此，一個(gè)完善的數(shù)據(jù)庫(kù)管理系統(tǒng)必須提供一個(gè)妥善的解決方法，以避免上述兩類(lèi)事件對(duì)數(shù)據(jù)庫(kù)的影響，這個(gè)機(jī)制就是事務(wù)。

事務(wù)能夠保證上述兩條語(yǔ)句要么都執(zhí)行，要么都不執(zhí)行，并且一個(gè)事務(wù)的內(nèi)部處理不會(huì)對(duì)其他操作造成影響。事務(wù)的特性原子性（Atomicity）原子性是事務(wù)的最基本的特性，是指一個(gè)事務(wù)中的操作要么全部完成（提交），要么全部撤消（回滾）。一致性（Consistency）若數(shù)據(jù)庫(kù)只包含成功事務(wù)提交的結(jié)果，則稱數(shù)據(jù)庫(kù)處于一致性狀態(tài)。在事務(wù)的執(zhí)行過(guò)程中，數(shù)據(jù)庫(kù)會(huì)從一個(gè)一致性狀態(tài)（執(zhí)行前）變到另一個(gè)一致性狀態(tài)（執(zhí)行后），而不會(huì)處于中間狀態(tài)（不一致的狀態(tài)）。隔離性（Isolation）并發(fā)執(zhí)行的事務(wù)之間不能相互干擾，并發(fā)事務(wù)間保持互斥的特性即為隔離性。持久性（Durability）事務(wù)一旦提交，事務(wù)對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是永久的，接下來(lái)的其他操作或故障不應(yīng)該對(duì)其執(zhí)行結(jié)果有任何影響，這種特性稱為持久性。這4個(gè)特性也簡(jiǎn)稱為ACID特性（

ACID是化學(xué)中“酸”的意思）事務(wù)的特性原子性（Atomicity）原子性是事務(wù)的最基本的特性，是指一個(gè)事務(wù)中的操作要么全部完成（提交），要么全部撤消（回滾）。一致性（Consistency）若數(shù)據(jù)庫(kù)只包含成功事務(wù)提交的結(jié)果，則稱數(shù)據(jù)庫(kù)處于一致性狀態(tài)。在事務(wù)的執(zhí)行過(guò)程中，數(shù)據(jù)庫(kù)會(huì)從一個(gè)一致性狀態(tài)（執(zhí)行前）變到另一個(gè)一致性狀態(tài)（執(zhí)行后），而不會(huì)處于中間狀態(tài)（不一致的狀態(tài)）。隔離性（Isolation）并發(fā)執(zhí)行的事務(wù)之間不能相互干擾，并發(fā)事務(wù)間保持互斥的特性即為隔離性。持久性（Durability）事務(wù)一旦提交，事務(wù)對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是永久的，接下來(lái)的其他操作或故障不應(yīng)該對(duì)其執(zhí)行結(jié)果有任何影響，這種特性稱為持久性。這4個(gè)特性也簡(jiǎn)稱為ACID特性（

ACID是化學(xué)中“酸”的意思）并發(fā)控制并發(fā)是什么？如果只有一個(gè)用戶使用數(shù)據(jù)庫(kù)，那么就沒(méi)有并發(fā)的問(wèn)題，就不需要考慮事務(wù)因?yàn)橐瑫r(shí)為多個(gè)用戶提供服務(wù)，所以就有可能有多個(gè)用戶同時(shí)訪問(wèn)，這就是并發(fā)并發(fā)的概念

兩個(gè)或多個(gè)事務(wù)在同一時(shí)刻（時(shí)間間隔極其短暫）訪問(wèn)同一個(gè)數(shù)據(jù)庫(kù)對(duì)象（例如同一行）的現(xiàn)象稱為并發(fā)（Concurrency）

并發(fā)控制（Concurrency

control）能夠確保當(dāng)多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性、一致性并發(fā)與事務(wù)

事務(wù)是并發(fā)控制的基本單位，事務(wù)應(yīng)該保證下述兩類(lèi)事件發(fā)生時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)能夠正常運(yùn)行事務(wù)在運(yùn)行過(guò)程中被強(qiáng)行停止（例如停電、系統(tǒng)崩潰）。這時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)必須保證被強(qiáng)行終止的事務(wù)對(duì)數(shù)據(jù)庫(kù)和其他事務(wù)沒(méi)有任何影響多個(gè)事務(wù)并發(fā)運(yùn)行時(shí)，不同事務(wù)的操作交叉執(zhí)行。這時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)必須保證多個(gè)事務(wù)交叉運(yùn)行而不會(huì)產(chǎn)生相互影響事務(wù)控制語(yǔ)句

一個(gè)事務(wù)的開(kāi)始、提交與回滾可以用SQL語(yǔ)句來(lái)實(shí)現(xiàn)，在MySQL中，控制事務(wù)的語(yǔ)句主要有3條Begin（或Start

transaction）：顯式地開(kāi)啟一個(gè)事務(wù)

Commit：提交事務(wù)，即提交事務(wù)的所有操作，具體來(lái)說(shuō)就是將事務(wù)中所有對(duì)數(shù)據(jù)庫(kù)的更新寫(xiě)到磁盤(pán)上的物理數(shù)據(jù)庫(kù)中去，事務(wù)正常結(jié)束

Rollback：回滾事務(wù)，當(dāng)事務(wù)運(yùn)行的過(guò)程中發(fā)生了錯(cuò)誤或故障，事務(wù)無(wú)法繼續(xù)執(zhí)行時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)將事務(wù)中對(duì)數(shù)據(jù)庫(kù)的所有已執(zhí)行的操作全部撤銷(xiāo)，回滾到事務(wù)開(kāi)始時(shí)

的狀態(tài)?？梢院?jiǎn)單地理解為“全部反悔”事務(wù)的開(kāi)始與結(jié)束可以由用戶使用上述事務(wù)控制語(yǔ)句顯式控制如果用戶沒(méi)有顯式地定義事務(wù)，數(shù)據(jù)庫(kù)管理系統(tǒng)將按一定的策略自動(dòng)處理事務(wù)MySQL的默認(rèn)事務(wù)處理策略是，將每一條SQL語(yǔ)句作為一個(gè)獨(dú)立的事務(wù)，一旦執(zhí)行完成，立即提交。而使用Start

transaction語(yǔ)句則可以定義一個(gè)事務(wù)，將多條SQL語(yǔ)句作為一個(gè)整體提交，或者在出現(xiàn)故障時(shí)回滾7.5.2【實(shí)訓(xùn)7-13】體驗(yàn)事務(wù)控制語(yǔ)句體驗(yàn)事務(wù)的提交和回滾

前后兩次執(zhí)行代碼，修改倒數(shù)第2行代碼提交：Commit回滾：Rollback

比較結(jié)果的不同，回滾可以理解為“錯(cuò)了，就全部反悔”

每次執(zhí)行代碼都會(huì)刪除數(shù)據(jù)表并重新創(chuàng)建，并初始化數(shù)據(jù)，保證每次執(zhí)行時(shí)的初始數(shù)據(jù)完全相同。Use

test;--在測(cè)試數(shù)據(jù)庫(kù)test上進(jìn)行演示，如果沒(méi)有這個(gè)數(shù)據(jù)庫(kù)，需要?jiǎng)?chuàng)建它

Drop

table

if

exists

bank;Create

table

bank(account

varchar(20)not

null

primary

key,--賬戶，主鍵

ammount

decimal(10,2)--存款數(shù)目);Insert

into

bank

values("A",2000);--賬戶A有存款2000元Insert

into

bank

values("B",3000);--賬戶B有存款3000元--存款總數(shù)是5000元?，F(xiàn)在賬戶A想轉(zhuǎn)500元給賬戶B，這時(shí)的操作應(yīng)該如下。SET

@transfer=500;Start

transaction;Update

bank

set

ammount=ammount-@transfer--轉(zhuǎn)賬第一步，從賬戶A中減去500元

where

account="A";Update

bank

set

ammount=ammount+@transfer--轉(zhuǎn)賬第二步，向賬戶B里加上500元where

account

=

"B";Commit;--把Commit改為Rollback，看看有什么區(qū)別

Select

*

from

bank;【實(shí)訓(xùn)7-13】7.5.3事務(wù)隔離和鎖機(jī)制1、事務(wù)并發(fā)產(chǎn)生的問(wèn)題

如果事務(wù)之間沒(méi)有隔離的話，當(dāng)多個(gè)事務(wù)之間并發(fā)訪問(wèn)時(shí)，可能出現(xiàn)臟讀、不可重復(fù)讀或幻讀等現(xiàn)象，其危害程度由高到低，如表7.10所示。2、鎖機(jī)制MySQL使用鎖機(jī)制來(lái)實(shí)現(xiàn)事務(wù)之間的隔離

例如一個(gè)事務(wù)對(duì)表或表中的某一行進(jìn)行修改操作時(shí)，不允許另一個(gè)事務(wù)對(duì)同一張表或同一行進(jìn)行操作，直到修改操作完成，第二個(gè)事務(wù)才能進(jìn)行操作。這時(shí)第一個(gè)事務(wù)就要對(duì)表或表中的行進(jìn)行鎖定，這就是鎖機(jī)制。MySQL的鎖有多種，例如共享（Share，S）鎖、排他（eXclusive，X）鎖、意向共享（Intent

Share，IS）鎖和意向排他（Intent

eXclusive，IX）鎖等，如表7.11所示。3、隔離級(jí)別MySQL提供了4種隔離級(jí)別來(lái)解決隔離問(wèn)題統(tǒng)管理員只需要指定4種隔離級(jí)別中的某一種，MySQL會(huì)根據(jù)隔離的要系

求自動(dòng)選擇合適的鎖機(jī)制，而不需要用戶進(jìn)行太多的干預(yù)。

MySQL默認(rèn)的隔離級(jí)別是“REPEATABLE-READ”（可重復(fù)讀），這個(gè)默認(rèn)的隔離級(jí)別適合大多數(shù)的需求?？梢杂孟率稣Z(yǔ)句查看隔離級(jí)別。Show

variables

like

"%isolation%";小結(jié)項(xiàng)目7數(shù)據(jù)庫(kù)編程任務(wù)1學(xué)習(xí)MySQL編程MySQL語(yǔ)言基礎(chǔ)MySQL流程控制內(nèi)置函數(shù)任務(wù)2存儲(chǔ)函數(shù)單語(yǔ)句存儲(chǔ)函數(shù)和多語(yǔ)句存儲(chǔ)函數(shù)任務(wù)3使用存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的參數(shù)：無(wú)參數(shù)，輸入型參數(shù)，輸出型參數(shù)，輸入輸出型參數(shù)任務(wù)4使用觸發(fā)器before觸發(fā)器和after觸發(fā)器的區(qū)別

觸發(fā)的條件：insert、update、delete任務(wù)5了解事務(wù)和鎖事務(wù)的提交和回滾，重點(diǎn)是理解，并不需要寫(xiě)代碼看思維導(dǎo)圖，總結(jié)項(xiàng)目7謝謝大家！給授課教師的說(shuō)明（閱畢刪除）：本書(shū)提供的教案詳細(xì)說(shuō)明了教學(xué)內(nèi)容的安排，請(qǐng)下載閱讀（掃碼教材封底二維碼，或訪問(wèn)下載頁(yè)面/MySQLa/?p=2/links）上課時(shí)盡量少講理論，多做演示，可直接在“Jitor校驗(yàn)器”的輔助下進(jìn)行演示，與講授交叉進(jìn)行，包括在教室上課。要求學(xué)生多動(dòng)手操作，充分利用“Jitor校驗(yàn)器”。項(xiàng)目8“在線商店”項(xiàng)目的開(kāi)發(fā)體驗(yàn)?zāi)夸浫蝿?wù)1安裝和認(rèn)識(shí)PHP任務(wù)1安裝和認(rèn)識(shí)PHP安裝開(kāi)發(fā)環(huán)境XAMPP【實(shí)訓(xùn)8-1】PHP基本語(yǔ)法【實(shí)訓(xùn)8-2】PHP數(shù)組（自學(xué)）任務(wù)2用PHP開(kāi)發(fā)數(shù)據(jù)庫(kù)項(xiàng)目（自學(xué)）

任務(wù)3體驗(yàn)“在線商店”應(yīng)用的開(kāi)發(fā)過(guò)程8.1.1安裝開(kāi)發(fā)環(huán)境XAMPP開(kāi)發(fā)環(huán)境XAMPP簡(jiǎn)介XAMPP

=

（Windows/Linux）+

Apache

+

MySQL

+

PHP

+

Perl本項(xiàng)目使用的精簡(jiǎn)版是Apache

+PHP

+自己安裝的MySQL1、下載和安裝從本書(shū)附錄E提供的網(wǎng)盤(pán)地址下載精簡(jiǎn)版的XAMPP

1.8.2，文件名xampp-x.zip，文件大小33MB解壓到任一盤(pán)符的根目錄下，注意必須解壓到根目錄下8.1.1安裝開(kāi)發(fā)環(huán)境XAMPP（續(xù)）2．啟動(dòng)和停止XAMPP雙擊運(yùn)行xampp-control.exe正常情況是自動(dòng)啟動(dòng)的可以從XAMPP控制面板停止Apache需要時(shí)再啟動(dòng)它注：控制面板的版本是3.2.1，實(shí)際運(yùn)行的XAMPP版本則是1.8.2注意觀察Apache的端口號(hào)圖8.1所示的是1443和8082前者是加密的HTTP端口號(hào)后者是普通的HTTP端口號(hào)因此，訪問(wèn)這個(gè)網(wǎng)站的地址有兩個(gè)，這兩個(gè)地址訪問(wèn)的是同一個(gè)網(wǎng)站，內(nèi)容完全相同8.1.1安裝開(kāi)發(fā)環(huán)境XAMPP（續(xù)）3．訪問(wèn)xampp普通的HTTP地址（協(xié)議名為http）http://localhost:8082/加密的HTTP地址（協(xié)議名為https，其中s表示加密）https://localhost:1443/由于加密必需的證書(shū)沒(méi)有通過(guò)第三方的認(rèn)證，這時(shí)反而會(huì)提示“不安全”上述兩個(gè)地址訪問(wèn)的是相同的網(wǎng)站，首頁(yè)如右圖所示看到這個(gè)首頁(yè)，說(shuō)明安裝成功8.1.2【實(shí)訓(xùn)8-1】PHP基本語(yǔ)法1．HTML文件

HTML文件就是通常所說(shuō)的網(wǎng)頁(yè)，是由超鏈接標(biāo)記語(yǔ)言寫(xiě)成的，它的擴(kuò)展名是.html，它的基本元素是標(biāo)簽。例如下述代碼。<html><head><meta

charset="UTF-8"><title>網(wǎng)頁(yè)標(biāo)題</title></head><body>歡迎學(xué)習(xí)PHP語(yǔ)言。</body></html>

將上述內(nèi)容復(fù)制到記事本中，另存為page.html文件（位于htdocs下的test目錄中），在

“保存”或“另存為”時(shí)要將編碼改為UTF-8，否則會(huì)有中文亂碼。【實(shí)訓(xùn)8-1】訪問(wèn)HTML文件文件的路徑是：xampp安裝目錄下的htdocs/test/page.html訪問(wèn)地址是：http://localhost:8082/