MySQL數(shù)據(jù)庫(kù)項(xiàng)目實(shí)戰(zhàn) 課件 項(xiàng)目5“天意書(shū)屋”數(shù)據(jù)庫(kù)中程序的設(shè)計(jì)

項(xiàng)目五“天意書(shū)屋”數(shù)據(jù)庫(kù)中程序的設(shè)計(jì)走進(jìn)程序設(shè)計(jì)1探索觸發(fā)器2Contents目錄處理事務(wù)3任務(wù)一走進(jìn)程序設(shè)計(jì)看情景，明目標(biāo)溫舊知，做準(zhǔn)備

劉老師，如何實(shí)現(xiàn)在“天意書(shū)屋”數(shù)據(jù)庫(kù)中編寫(xiě)SQL程序代碼呢？在MySQL數(shù)據(jù)庫(kù)中編寫(xiě)程序代碼就是存儲(chǔ)過(guò)程，一個(gè)存儲(chǔ)過(guò)程里邊可以包含多條SQL語(yǔ)句，SQL語(yǔ)句之間可以使用流程控制語(yǔ)句進(jìn)行控制，用來(lái)實(shí)現(xiàn)較為復(fù)雜的業(yè)務(wù)邏輯。遇見(jiàn)視圖品味索引

要求：在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名稱(chēng)為proc_mybook的存儲(chǔ)過(guò)程，查詢(xún)圖書(shū)信息表tb_book中的所有圖書(shū)信息。

任務(wù)描述任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施操作步驟如下：

步驟1：在Navicat主窗口中依次單擊“查詢(xún)”—>“新建查詢(xún)”按鈕，創(chuàng)建一個(gè)查詢(xún)窗口。任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施步驟2：在該查詢(xún)窗口中輸入：createprocedureproc_mybook()beginselect*fromtb_book;end;任務(wù)一走進(jìn)程序設(shè)計(jì)步驟3：?jiǎn)螕簟斑\(yùn)行”按鈕，運(yùn)行結(jié)果如圖所示。

任務(wù)實(shí)施步驟4：繼續(xù)在該查詢(xún)窗口中輸入命令，調(diào)用存儲(chǔ)過(guò)程proc_mybook：callproc_mybook();任務(wù)一走進(jìn)程序設(shè)計(jì)步驟5：?jiǎn)螕簟斑\(yùn)行”按鈕，運(yùn)行結(jié)果如圖所示。任務(wù)一走進(jìn)程序設(shè)計(jì)一、存儲(chǔ)過(guò)程概述任務(wù)一走進(jìn)程序設(shè)計(jì)

存儲(chǔ)過(guò)程是一組經(jīng)過(guò)編譯并保存在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句集，可以隨時(shí)被調(diào)用。

存儲(chǔ)過(guò)程具有如下5個(gè)優(yōu)點(diǎn)：

01執(zhí)行速度快02系統(tǒng)性能高03允許標(biāo)準(zhǔn)組件式編程04靈活性強(qiáng)05安全任務(wù)一走進(jìn)程序設(shè)計(jì)二、存儲(chǔ)過(guò)程的創(chuàng)建及調(diào)用1.創(chuàng)建存儲(chǔ)過(guò)程任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CREATEPROCEDURE存儲(chǔ)過(guò)程名稱(chēng)([參數(shù)列表[,...]])

SQL語(yǔ)句集;

語(yǔ)句說(shuō)明：CREATEPROCEDURE：表示創(chuàng)建存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程名稱(chēng)：必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一的。參數(shù)列表：是可選項(xiàng)，不省略為有參數(shù)存儲(chǔ)過(guò)程，省略為無(wú)參數(shù)存儲(chǔ)過(guò)程。SQL語(yǔ)句集：使用BEGIN表示開(kāi)始，使用END表示結(jié)束。提示：在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，設(shè)置的存儲(chǔ)過(guò)程參數(shù)名不要與數(shù)據(jù)表中的字段名重復(fù)，否則系統(tǒng)會(huì)報(bào)錯(cuò)?！纠?-1】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求創(chuàng)建存儲(chǔ)過(guò)程proc_book，查詢(xún)圖書(shū)信息表tb_book中圖書(shū)名稱(chēng)含有“mysql”的圖書(shū)信息。

代碼如下：

任務(wù)實(shí)施createprocedureproc_book()beginselect*fromtb_bookwherebooknamelike'%mysql%';end;任務(wù)一走進(jìn)程序設(shè)計(jì)2.調(diào)用存儲(chǔ)過(guò)程任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CALL存儲(chǔ)過(guò)程名稱(chēng)([參數(shù)列表[,...]]);語(yǔ)句說(shuō)明：CALL：關(guān)鍵字，表示調(diào)用存儲(chǔ)過(guò)程，后面加要調(diào)用的存儲(chǔ)過(guò)程名稱(chēng)。參數(shù)列表：可選項(xiàng)，調(diào)用帶有參數(shù)的存儲(chǔ)過(guò)程，給出參數(shù)的具體的值。【例5-2】要求調(diào)用存儲(chǔ)過(guò)程proc_book，查看圖書(shū)信息表tb_book中含有mysql的圖書(shū)信息。

代碼如下：

任務(wù)實(shí)施callproc_book();任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CREATEPROCEDURE存儲(chǔ)過(guò)程名稱(chēng)([IN|OUT|INOUT]

參數(shù)名稱(chēng)

參數(shù)類(lèi)型)

SQL語(yǔ)句集;語(yǔ)句說(shuō)明：IN：表示輸入?yún)?shù)，可把外界的數(shù)據(jù)傳遞到存儲(chǔ)過(guò)程當(dāng)中。OUT：表示輸出參數(shù)，可把存儲(chǔ)過(guò)程的運(yùn)算結(jié)果傳遞到外界。INOUT：表示輸入輸出參數(shù)，既可以把外界的數(shù)據(jù)傳遞給存儲(chǔ)過(guò)程當(dāng)中，又可以把存儲(chǔ)過(guò)程的運(yùn)算結(jié)果傳遞到外界。在沒(méi)有指定參數(shù)方向的情況下，系統(tǒng)默認(rèn)是輸入?yún)?shù)IN。3.有參數(shù)存儲(chǔ)過(guò)程任務(wù)一走進(jìn)程序設(shè)計(jì)(1)創(chuàng)建和調(diào)用帶輸入?yún)?shù)的存儲(chǔ)過(guò)程3.有參數(shù)存儲(chǔ)過(guò)程

任務(wù)實(shí)施【例5-3】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_getbookbyId，要求在圖書(shū)信息表tb_book中根據(jù)圖書(shū)編號(hào)bookid查詢(xún)指定的圖書(shū)信息，顯示圖書(shū)編號(hào)、圖書(shū)名稱(chēng)、圖書(shū)作者和圖書(shū)價(jià)格。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施【例5-3】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_getbookbyId，要求在圖書(shū)信息表tb_book中根據(jù)圖書(shū)編號(hào)bookid查詢(xún)指定的圖書(shū)信息，顯示圖書(shū)編號(hào)、圖書(shū)名稱(chēng)、圖書(shū)作者和圖書(shū)價(jià)格。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_getbookbyId(inidint)beginselectbookid,bookname,author,bookpricefromtb_bookwherebookid=id;end;

任務(wù)實(shí)施【例5-4】要求調(diào)用存儲(chǔ)過(guò)程proc_getbookbyId，查詢(xún)bookid的值為3的圖書(shū)信息。

代碼如下：callproc_getbookbyId(3);任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)(2)創(chuàng)建和調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程3.有參數(shù)存儲(chǔ)過(guò)程

任務(wù)實(shí)施【例5-5】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_total，要求查詢(xún)圖書(shū)信息表tb_book中圖書(shū)的數(shù)量。

任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施【例5-5】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_total，要求查詢(xún)圖書(shū)信息表tb_book中圖書(shū)的數(shù)量。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_total(outnumint)beginselectcount(*)intonumfromtb_book;end;

任務(wù)實(shí)施【例5-6】要求調(diào)用存儲(chǔ)過(guò)程proc_total，查看圖書(shū)信息表tb_book中的圖書(shū)數(shù)量。

代碼如下：callproc_total(@num);select@num;任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)(3)創(chuàng)建和調(diào)用帶輸入輸出參數(shù)的存儲(chǔ)過(guò)程3.有參數(shù)存儲(chǔ)過(guò)程

任務(wù)實(shí)施【例5-7】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_getuserbyname，要求在客戶信息表tb_user中根據(jù)客戶姓名username查詢(xún)?cè)摽蛻舻目蛻艟幪?hào)。

任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施【例5-7】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc_getuserbyname，要求在客戶信息表tb_user中根據(jù)客戶姓名username查詢(xún)?cè)摽蛻舻目蛻艟幪?hào)。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_getuserbyname(innamevarchar(30),outidint)beginselectuseridintoidfromtb_userwhereusername=name;end;

任務(wù)實(shí)施【例5-8】要求調(diào)用存儲(chǔ)過(guò)程proc_getuserbyname，查詢(xún)用戶名為“王偉”的客戶編號(hào)。

代碼如下：callproc_getuserbyname("王偉",@id);select@id;任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)三、查看存儲(chǔ)過(guò)程1.查看存儲(chǔ)過(guò)程的狀態(tài)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

SHOWPROCEDURESTATUS[LIKE'存儲(chǔ)過(guò)程名稱(chēng)'];

語(yǔ)句說(shuō)明：SHOWPROCEDURESTATUS：表示查看存儲(chǔ)過(guò)程的狀態(tài)。LIKE'存儲(chǔ)過(guò)程名稱(chēng)'：可選項(xiàng)，用來(lái)匹配存儲(chǔ)過(guò)程的名稱(chēng)，LIKE不能省略。

任務(wù)實(shí)施【例5-9】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查看存儲(chǔ)過(guò)程名稱(chēng)中以proc開(kāi)頭的所有存儲(chǔ)過(guò)程的狀態(tài)信息。

代碼如下：showprocedurestatuslike'proc%';任務(wù)一走進(jìn)程序設(shè)計(jì)2.查看存儲(chǔ)過(guò)程的定義任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

SHOWCREATE

PROCEDURE

存儲(chǔ)過(guò)程名稱(chēng);

語(yǔ)句說(shuō)明：SHOWCREATEPROCEDURE：顯示創(chuàng)建的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程名稱(chēng)：要查看的存儲(chǔ)過(guò)程名稱(chēng)。

任務(wù)實(shí)施【例5-10】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查看存儲(chǔ)過(guò)程proc_book的定義語(yǔ)句。

代碼如下：showcreateprocedureproc_book;任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)四、修改存儲(chǔ)過(guò)程任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

ALTERPROCEDURE存儲(chǔ)過(guò)程名稱(chēng)

[

MODIFIESSQLDATA

|SQLSECURITY{DEFINER|INVOKER}];

語(yǔ)句說(shuō)明：ALTERPROCEDURE：關(guān)鍵字，表示修改存儲(chǔ)過(guò)程。MODIFIESSQLDATA：表示子程序中包含寫(xiě)數(shù)據(jù)的語(yǔ)句。DEFINER：表示只有定義者自己才能執(zhí)行。INVOKER：表示調(diào)用者可以執(zhí)行。

任務(wù)實(shí)施【例5-11】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求修改存儲(chǔ)過(guò)程proc_book的讀寫(xiě)權(quán)限為modifiessqldata，安全類(lèi)型為sqlsecurityinvoker。

代碼如下：alterprocedureproc_bookmodifiessqldatasqlsecurityinvoker;任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)五、刪除存儲(chǔ)過(guò)程任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

DROPPROCEDURE[IFEXISTS]存儲(chǔ)過(guò)程名稱(chēng);

語(yǔ)句說(shuō)明：DROPPROCEDURE：表示刪除存儲(chǔ)過(guò)程。IFEXISTS：可選項(xiàng)，用于防止因刪除不存在的存儲(chǔ)過(guò)程而引發(fā)的錯(cuò)誤。使用IFEXISTS在執(zhí)行刪除操作時(shí)，首先判斷存儲(chǔ)過(guò)程是否存在，如果存在，則直接刪除；如果不存在，則刪除不會(huì)報(bào)錯(cuò)；避免了系統(tǒng)錯(cuò)誤。存儲(chǔ)過(guò)程名稱(chēng)：表示要?jiǎng)h除的存儲(chǔ)過(guò)程。

任務(wù)實(shí)施【例5-12】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求刪除存儲(chǔ)過(guò)程proc_book。

代碼如下：dropprocedureproc_book;任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)一走進(jìn)程序設(shè)計(jì)六、變量1.變量的分類(lèi)任務(wù)一走進(jìn)程序設(shè)計(jì)（1）用戶變量（User-DefinedVariables）：帶有前綴@，只能被定義它的用戶使用。（2）局部變量（LocalVariables）：沒(méi)有前綴，一般用于SQL語(yǔ)句塊的BEGIN...END中。（3）系統(tǒng)變量（ServerSystemVariables）：帶有前綴@@，MySQL有許多已經(jīng)設(shè)置默認(rèn)值的系統(tǒng)變量。2.局部變量的定義任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

DECLARE變量名數(shù)據(jù)類(lèi)型

[DEFAULT默認(rèn)值];

語(yǔ)句說(shuō)明：DECLARE：關(guān)鍵字，表示定義局部變量，后面加變量名和數(shù)據(jù)類(lèi)型。DEFAULT：關(guān)鍵字，表示默認(rèn)，后面加默認(rèn)值。

任務(wù)實(shí)施【例如】定義一個(gè)int類(lèi)型的局部變量，名稱(chēng)為var1。

代碼如下：declarevar1int;任務(wù)一走進(jìn)程序設(shè)計(jì)提示：變量的定義必須在復(fù)合語(yǔ)句開(kāi)頭，并且在任何其他語(yǔ)句前面。也就是說(shuō)，declare語(yǔ)句在存儲(chǔ)過(guò)程中使用時(shí)，必須出現(xiàn)在begin...end語(yǔ)句塊的最前面。變量名不區(qū)分大小寫(xiě)，可以一次聲明多個(gè)相同類(lèi)型的變量。3.使用SET關(guān)鍵字為變量賦值任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

SET變量名=變量值;

語(yǔ)句說(shuō)明：SET：關(guān)鍵字，表示設(shè)置。變量值：可以是常量或者表達(dá)式。

任務(wù)實(shí)施【例如】給已經(jīng)定義的局部變量var1賦值。

代碼如下：setvar1=3;任務(wù)一走進(jìn)程序設(shè)計(jì)【例如】給用戶變量var2賦值。代碼如下：set@var2=3;#給用戶變量var2賦值3select@var2;#查看用戶變量var2的值3.使用SELECT...INTO語(yǔ)句為變量賦值任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

SELECT列名INTO變量名FROM表名;

語(yǔ)句說(shuō)明：SELECT…FROM：查詢(xún)語(yǔ)句，變量的數(shù)量必須與列或表達(dá)式的數(shù)量相同。INTO：連接列名和變量名之間的關(guān)鍵字。

任務(wù)實(shí)施【例5-13】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，創(chuàng)建存儲(chǔ)過(guò)程proc1，定義4個(gè)整型局部變量var1、var2、var3和var4，要求實(shí)現(xiàn)var3等于var1和var2相加，var4等于在訂單信息表tb_book中查詢(xún)圖書(shū)編號(hào)為1的客戶編號(hào)，最后將var3和var4的結(jié)果賦值給用戶變量uvar1和uvar2。任務(wù)一走進(jìn)程序設(shè)計(jì)

任務(wù)實(shí)施createprocedureproc1()begindeclarevar1,var2,var3,var4int;#聲明4個(gè)局部變量setvar1=1;#局部變量var1賦值為1setvar2=2;#局部變量var2賦值為2setvar3=var1+var2;#局部變量var3賦值為var1與var2相加#局部變量var4賦值為從訂單信息表中查詢(xún)bookid=1的userid的值selectuseridintovar4fromtb_orderwherebookid=1;set@uvar1=var3;#用戶變量uvar1賦值為var3set@uvar2=var4;#用戶變量uvar2賦值為var4end;任務(wù)一走進(jìn)程序設(shè)計(jì)代碼如下：

任務(wù)實(shí)施任務(wù)一走進(jìn)程序設(shè)計(jì)運(yùn)行結(jié)果：

任務(wù)實(shí)施【例5-14】要求調(diào)用存儲(chǔ)過(guò)程proc1，查詢(xún)uvar1和uvar2的結(jié)果。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)callproc1();select@uvar1;select@uvar2;任務(wù)一走進(jìn)程序設(shè)計(jì)提示：?jiǎn)涡凶⑨尶梢允褂?注釋符，#注釋符后直接加注釋內(nèi)容。單行注釋也可以使用--注釋符，--注釋符后需要加一個(gè)空格，注釋才能生效。多行注釋使用/**/注釋符，/*用于注釋內(nèi)容的開(kāi)頭，*/用于注釋內(nèi)容的結(jié)尾。任務(wù)一走進(jìn)程序設(shè)計(jì)七、流程控制語(yǔ)句1.條件分支語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)

條件分支語(yǔ)句是通過(guò)對(duì)特定條件的判斷，選擇一個(gè)分支的語(yǔ)句執(zhí)行。在MySQL中可以實(shí)現(xiàn)條件分支的語(yǔ)句有IF語(yǔ)句、IFNULL語(yǔ)句、IF...ELSE語(yǔ)句和CASE語(yǔ)句共4種。（1）IF語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

IF(條件表達(dá)式，結(jié)果1，結(jié)果2);

語(yǔ)句說(shuō)明：當(dāng)“條件表達(dá)式”的值為T(mén)RUE時(shí)，返回“結(jié)果1”，否則返回“結(jié)果2”。

任務(wù)實(shí)施【例5-15】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查詢(xún)客戶信息表tb_user中的前5條記錄，輸出username字段和telephone字段的值。當(dāng)telephone字段的值為null時(shí)，輸出字符串“nothing”，否則顯示當(dāng)前字段的值。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)selectusername,if(telephoneisnull,'nothing',telephone)astelephonefromtb_userlimit5;（2）IFNULL語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

IFNULL(結(jié)果1，結(jié)果2);

語(yǔ)句說(shuō)明：若結(jié)果1的值不為空，則返回結(jié)果1，否則返回結(jié)果2。

任務(wù)實(shí)施【例5-16】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查詢(xún)圖書(shū)信息表tb_book中從第7條數(shù)據(jù)開(kāi)始，總條數(shù)為6的數(shù)據(jù)記錄，輸出bookname字段和author字段的值。當(dāng)author字段不為空時(shí)，輸出author字段的值，否則輸出“noauthor”。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)selectbookname,ifnull(author,'noauthor')asauthorfromtb_booklimit6,6;（3）IF...ELSE語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

IF條件表達(dá)式THEN

語(yǔ)句塊1;

ELSE

語(yǔ)句塊2;

ENDIF;

語(yǔ)句說(shuō)明：當(dāng)“條件表達(dá)式”的值為T(mén)RUE時(shí)，“語(yǔ)句塊1”將被執(zhí)行。如果“條件表達(dá)式”的值為FALSE，則執(zhí)行“語(yǔ)句塊2”。每個(gè)語(yǔ)句塊都可以包含一個(gè)或多個(gè)語(yǔ)句。

任務(wù)實(shí)施【例5-17】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求創(chuàng)建存儲(chǔ)過(guò)程proc_myorder，查詢(xún)訂單信息表tb_order中客戶編號(hào)為142258847的用戶是否有訂單。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_myorder()begindeclarenumint;selectcount(*)intonumfromtb_orderwhereuserid=142258847;ifnum>0thenselect'有訂單';elseselect'無(wú)訂單';endif;end;

任務(wù)實(shí)施任務(wù)一走進(jìn)程序設(shè)計(jì)運(yùn)行結(jié)果：

任務(wù)實(shí)施【例5-18】要求調(diào)用存儲(chǔ)過(guò)程proc_myorder，查詢(xún)訂單信息表tb_order中客戶編號(hào)為142258847的用戶是否有訂單。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)callproc_myorder();（4）CASE語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)

CASE語(yǔ)句在MySQL中用于實(shí)現(xiàn)分支處理，能夠根據(jù)表達(dá)式的不同取值，轉(zhuǎn)向不同的計(jì)算或處理，類(lèi)似高級(jí)程序語(yǔ)言中的SWITCH...CASE語(yǔ)句。當(dāng)判斷條件的范圍較大時(shí)，使用CASE會(huì)使得程序的結(jié)構(gòu)更為簡(jiǎn)潔。適用于需要根據(jù)同一個(gè)表達(dá)式的不同取值來(lái)決定將執(zhí)行哪一個(gè)分支的場(chǎng)合。

CASE語(yǔ)句具有簡(jiǎn)單結(jié)構(gòu)和搜索結(jié)構(gòu)兩種語(yǔ)法。

簡(jiǎn)單CASE結(jié)構(gòu)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CASE表達(dá)式

WHEN數(shù)值1THEN

語(yǔ)句1;

[WHEN數(shù)值2THEN

語(yǔ)句2;]

......

[ELSE

語(yǔ)句n+1;]

ENDCASE;

語(yǔ)句說(shuō)明：“表達(dá)式”的值與WHEN子句后的“數(shù)值”比較，找到完全相同的項(xiàng)時(shí)，則執(zhí)行對(duì)應(yīng)的“語(yǔ)句”，若未找到匹配項(xiàng)，則執(zhí)行ELSE后的“語(yǔ)句”。

任務(wù)實(shí)施【例5-19】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查詢(xún)圖書(shū)信息表tb_book中的圖書(shū)名稱(chēng)bookname、圖書(shū)作者author、圖書(shū)價(jià)格bookprice和pricevalue。其中pricevalue的取值為：若bookprice=30為1，否則為0。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)selectbookname,author,bookprice,casebookpricewhen30then1else0endaspricevaluefromtb_book;

CASE搜索結(jié)構(gòu)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CASE

WHEN條件表達(dá)式1THEN

語(yǔ)句1;

[WHEN條件表達(dá)式2THEN

語(yǔ)句2;]

......

[ELSE

語(yǔ)句n+1;]

ENDCASE;

語(yǔ)句說(shuō)明：該結(jié)構(gòu)判斷WHEN子句后的“條件表達(dá)式”的值是否為T(mén)RUE，若為T(mén)RUE，則執(zhí)行對(duì)應(yīng)的“語(yǔ)句”。若所有的“條件表達(dá)式”的值均為FALSE，則執(zhí)行ELSE后的“語(yǔ)句”。若無(wú)ELSE子句，則返回NULL。

任務(wù)實(shí)施【例5-20】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查詢(xún)訂單信息表tb_order中的訂單編號(hào)orderid、圖書(shū)編號(hào)bookid、數(shù)量total和remark。其中remark的取值為若total大于等于4，則為“暢銷(xiāo)書(shū)”，若大于等于2，則為“潛質(zhì)暢銷(xiāo)書(shū)”，其余為“一般暢銷(xiāo)書(shū)”。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)selectorderid,bookid,total, case whentotal>=4then'暢銷(xiāo)書(shū)' whentotal>=2then'潛質(zhì)暢銷(xiāo)書(shū)' else'一般暢銷(xiāo)書(shū)' endasremarkfromtb_order;2.循環(huán)語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)

除了條件語(yǔ)句之外，在MySQL中還經(jīng)常會(huì)用到循環(huán)語(yǔ)句，循環(huán)語(yǔ)句可以在存儲(chǔ)過(guò)程或者觸發(fā)器等內(nèi)容中使用。每一種循環(huán)都是重復(fù)執(zhí)行的一個(gè)語(yǔ)句塊，該語(yǔ)句塊可包括一條或多條語(yǔ)句。循環(huán)語(yǔ)句在其他程序設(shè)計(jì)語(yǔ)言中有多種形式，MySQL中只有WHILE語(yǔ)句、LOOP語(yǔ)句和REPEAT語(yǔ)句三種。（1）WHILE語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

[開(kāi)始標(biāo)簽:]WHILE條件表達(dá)式DO

語(yǔ)句塊;

ENDWHILE[結(jié)束標(biāo)簽];

語(yǔ)句說(shuō)明：WHILE語(yǔ)句是先判斷“條件表達(dá)式”的值是否為T(mén)RUE，當(dāng)“條件表達(dá)式”的值為T(mén)RUE時(shí)，語(yǔ)句塊被重復(fù)執(zhí)行，直至“條件表達(dá)式”的值為FALSE，才會(huì)結(jié)束循環(huán)。只要“開(kāi)始標(biāo)簽”語(yǔ)句存在，則“結(jié)束標(biāo)簽”語(yǔ)句才能被使用；若兩者都存在，它們的名稱(chēng)必須相同。

任務(wù)實(shí)施【例5-21】創(chuàng)建存儲(chǔ)過(guò)程proc_doWhile，要求使用while語(yǔ)句循環(huán)輸出1到100的累加和。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_doWhile()begin set@count=1; set@sum=0; while@count<=100do set@sum=@sum+@count; set@count=@count+1;endwhile;select@sum;end;

任務(wù)實(shí)施【例5-22】要求調(diào)用存儲(chǔ)過(guò)程proc_doWhile，查看1-100的和。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)callproc_doWhile();（2）LOOP語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

[開(kāi)始標(biāo)簽:]LOOP

語(yǔ)句塊;

ENDLOOP[結(jié)束標(biāo)簽];

語(yǔ)句說(shuō)明：“開(kāi)始標(biāo)簽”和“結(jié)束標(biāo)簽”分別表示循環(huán)開(kāi)始和結(jié)束的標(biāo)識(shí)，這兩個(gè)標(biāo)識(shí)必須相同，可以省略?！罢Z(yǔ)句塊”表示需要循環(huán)執(zhí)行的語(yǔ)句。（3）LEAVE語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

LEAVE標(biāo)簽名;語(yǔ)句說(shuō)明：LEAVE語(yǔ)句主要用于跳出循環(huán)控制，與高級(jí)語(yǔ)言中的BREAK語(yǔ)句相似?！皹?biāo)簽名”用于標(biāo)識(shí)跳出循環(huán)的標(biāo)識(shí)符。（4）ITERATE語(yǔ)句任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

ITERATE

標(biāo)簽名;語(yǔ)句說(shuō)明：ITERATE語(yǔ)句只跳出當(dāng)次循環(huán)，然后直接進(jìn)入下一次循環(huán)，與高級(jí)語(yǔ)言中的CONTINUE語(yǔ)句相似?！皹?biāo)簽名”表示用來(lái)跳出的本次循環(huán)的標(biāo)識(shí)符。

任務(wù)實(shí)施【例5-23】創(chuàng)建存儲(chǔ)過(guò)程proc_loop，要求使用loop語(yǔ)句循環(huán)輸出1到100的奇數(shù)和。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedureproc_loop()beginset@count=0;set@sum=0;add_num:loopset@count=@count+1;if@count=100then leaveadd_num;elseifmod(@count,2)=0theniterateadd_num;endif;endif; set@sum=@sum+@count;endloopadd_num;select@sum;end;

任務(wù)實(shí)施任務(wù)一走進(jìn)程序設(shè)計(jì)運(yùn)行結(jié)果：

任務(wù)實(shí)施【例5-24】要求調(diào)用存儲(chǔ)過(guò)程proc_loop，查看1-100的奇數(shù)和。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)callproc_loop();任務(wù)一走進(jìn)程序設(shè)計(jì)提示：leave語(yǔ)句和iterate語(yǔ)句都是用來(lái)跳出循環(huán)語(yǔ)句，但兩者的功能是不一樣的。leave語(yǔ)句是跳出整個(gè)循環(huán)，然后執(zhí)行循環(huán)外的程序語(yǔ)句。iterate語(yǔ)句是跳出本次循環(huán)，進(jìn)入下一次循環(huán)。任務(wù)一走進(jìn)程序設(shè)計(jì)八、游標(biāo)1.聲明游標(biāo)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

DECLARE游標(biāo)名稱(chēng)CURSORFORSELECT語(yǔ)句;語(yǔ)句說(shuō)明：DECLARE：關(guān)鍵字，表示聲明游標(biāo)，后面加游標(biāo)名稱(chēng)。CURSOR：關(guān)鍵字，表示游標(biāo)。FOR：關(guān)鍵字，后接查詢(xún)語(yǔ)句。

任務(wù)實(shí)施【例5-25】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求聲明一個(gè)游標(biāo)cur_user，在客戶信息表tb_user中查詢(xún)客戶姓名。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)declarecur_usercursorforselectusernamefromtb_user;2.打開(kāi)游標(biāo)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

OPEN游標(biāo)名稱(chēng);語(yǔ)句說(shuō)明：OPEN：關(guān)鍵字，表示打開(kāi)游標(biāo)。游標(biāo)名稱(chēng)：已經(jīng)聲明的游標(biāo)名。

任務(wù)實(shí)施【例5-26】要求打開(kāi)聲明的游標(biāo)cur_user。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)opencur_user;提示：在打開(kāi)一個(gè)游標(biāo)時(shí)，游標(biāo)并不指向第一條記錄，而是指向第一條記錄的前邊。3.使用游標(biāo)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

FETCH游標(biāo)名稱(chēng)

INTO變量1[,變量2,.....];語(yǔ)句說(shuō)明：FETCH：關(guān)鍵字，表示提取數(shù)據(jù)，后面加游標(biāo)名稱(chēng)。INTO：關(guān)鍵字，后面加變量。提示：變量的個(gè)數(shù)必須和游標(biāo)返回字段的數(shù)量相同，否則游標(biāo)提取數(shù)據(jù)失敗。

任務(wù)實(shí)施【例5-27】要求將cur_user游標(biāo)查詢(xún)出來(lái)的數(shù)據(jù)存入uname變量中。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)fetchcur_userintouname;提示：MySQL中游標(biāo)是僅向前的且只讀的，也就是說(shuō)，游標(biāo)只能順序的從前往后一條條讀取結(jié)果集。4.關(guān)閉游標(biāo)任務(wù)一走進(jìn)程序設(shè)計(jì)語(yǔ)法格式：

CLOSE游標(biāo)名稱(chēng);語(yǔ)句說(shuō)明：CLOSE：關(guān)鍵字，表示關(guān)閉游標(biāo)。

任務(wù)實(shí)施【例5-28】要求將打開(kāi)的游標(biāo)cur_user關(guān)閉。

代碼如下：任務(wù)一走進(jìn)程序設(shè)計(jì)closecur_user;提示：游標(biāo)關(guān)閉之后，若要繼續(xù)使用游標(biāo)，則需要重新打開(kāi)游標(biāo)。

任務(wù)實(shí)施【例5-29】要求將上述游標(biāo)使用的4個(gè)步驟放到一個(gè)存儲(chǔ)過(guò)程pro_cursor中，然后通過(guò)調(diào)用存儲(chǔ)過(guò)程，查看游標(biāo)的使用方法。

步驟1：創(chuàng)建存儲(chǔ)過(guò)程。任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedurepro_cursor()begindeclareunamevarchar(30);declarecur_user cursorforselectusernamefromtb_user;opencur_user;fetchcur_userintouname;selectuname;closecur_user;end;

任務(wù)實(shí)施步驟2：調(diào)用存儲(chǔ)過(guò)程。任務(wù)一走進(jìn)程序設(shè)計(jì)callpro_cursor();1、在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求創(chuàng)建存儲(chǔ)過(guò)程spUser，在客戶信息表tb_user中查詢(xún)客戶總數(shù)。請(qǐng)根據(jù)題意將代碼補(bǔ)充完整。

動(dòng)手實(shí)踐任務(wù)一走進(jìn)程序設(shè)計(jì)

#創(chuàng)建存儲(chǔ)過(guò)程spUserbegin

#查詢(xún)客戶信息表tb_user中的客戶總數(shù)end;2、要求調(diào)用存儲(chǔ)過(guò)程spUser。請(qǐng)根據(jù)運(yùn)行結(jié)果將代碼補(bǔ)充完整。

動(dòng)手實(shí)踐任務(wù)一走進(jìn)程序設(shè)計(jì)

#調(diào)用存儲(chǔ)過(guò)程spUser3、要求刪除存儲(chǔ)過(guò)程spUser。請(qǐng)根據(jù)題意將代碼補(bǔ)充完整。

#刪除存儲(chǔ)過(guò)程spUser4、要求創(chuàng)建存儲(chǔ)過(guò)程spGetInteger，輸出100以?xún)?nèi)能夠同時(shí)被3和5整除的整數(shù)和。請(qǐng)根據(jù)題意將代碼補(bǔ)充完整。

動(dòng)手實(shí)踐任務(wù)一走進(jìn)程序設(shè)計(jì)createprocedurespGetInteger()begin

;#設(shè)置用戶變量count，初始值為0

;#設(shè)置用戶變量sum，初始值為0add_num:loop

;#設(shè)置用戶變量count加1if@count=101then

;#跳出循環(huán)elseif

then#用戶變量count對(duì)3和5整除#用戶變量sum等于用戶變量sum加上用戶變量count

;

動(dòng)手實(shí)踐任務(wù)一走進(jìn)程序設(shè)計(jì)endif;endif;

;#結(jié)束loop循環(huán)select@sum;end;5、要求調(diào)用存儲(chǔ)過(guò)程spGetInteger，輸出100以?xún)?nèi)同時(shí)被3和5整除的整數(shù)和。請(qǐng)根據(jù)運(yùn)行結(jié)果將代碼補(bǔ)充完整。

#調(diào)用存儲(chǔ)過(guò)程spGetInteger

1+X知識(shí)鞏固

1、關(guān)于MySQL存儲(chǔ)過(guò)程,說(shuō)法錯(cuò)誤的是（）。A、調(diào)用存儲(chǔ)過(guò)程使用關(guān)鍵字CALLB、存儲(chǔ)過(guò)程的參數(shù)在定義時(shí)，有兩種參數(shù)約束，即IN、OUTC、創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法是CREATEPROCEDURED、存儲(chǔ)過(guò)程是一種在數(shù)據(jù)庫(kù)中存儲(chǔ)復(fù)雜程序，以便由外部程序調(diào)用的數(shù)據(jù)庫(kù)對(duì)象2、創(chuàng)建存儲(chǔ)過(guò)程的命令是（）。A、createprocedureB、createruleC、createdureD、createfile任務(wù)一走進(jìn)程序設(shè)計(jì)

1+X知識(shí)鞏固

3、MySQL中使用（）來(lái)調(diào)用存儲(chǔ)過(guò)程。A、execB、callC、executeD、create4、下面的哪個(gè)語(yǔ)句用來(lái)聲明游標(biāo)？（）A、createcursorB、altercursorC、setcursorD、declarecursor5、流程控制語(yǔ)句中的選擇語(yǔ)句有（）。A、ifB、whileC、selectD、switch任務(wù)一走進(jìn)程序設(shè)計(jì)任務(wù)二探索觸發(fā)器看情景，明目標(biāo)溫舊知，做準(zhǔn)備

劉老師，如何實(shí)現(xiàn)當(dāng)插入一條新數(shù)據(jù)時(shí)，這個(gè)新數(shù)據(jù)的某些字段內(nèi)容可以同步到其他表中呢？在MySQL數(shù)據(jù)庫(kù)中觸發(fā)器能實(shí)現(xiàn)這個(gè)功能，觸發(fā)器與存儲(chǔ)過(guò)程類(lèi)似，它也是一段程序代碼。不同的是，觸發(fā)器是由事件激發(fā)某個(gè)操作，當(dāng)表中出現(xiàn)特定事件時(shí)，就會(huì)激發(fā)該對(duì)象。使用觸發(fā)器可以對(duì)表實(shí)施復(fù)雜的完整性約束，保持?jǐn)?shù)據(jù)的一致性。走進(jìn)程序設(shè)計(jì)

要求：在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，購(gòu)買(mǎi)了2本圖書(shū)后，訂單表中將生成一條記錄，這時(shí)要求圖書(shū)信息表（tb_book）表中的數(shù)量做相應(yīng)變化。

任務(wù)描述任務(wù)二探索觸發(fā)器

任務(wù)實(shí)施

提前在tb_book表中添加quantity字段，插入該字段的值為100。

操作步驟如下：

步驟1：在Navicat主窗口中依次單擊“查詢(xún)”—>“新建查詢(xún)”按鈕，創(chuàng)建一個(gè)查詢(xún)窗口。任務(wù)二探索觸發(fā)器

任務(wù)實(shí)施步驟2：在該查詢(xún)窗口中輸入：createtriggerup_inafterinsertontb_orderforeachrowbeginupdatetb_booksetquantity=quantity-new.totalwherebookid=new.bookid;end;任務(wù)二探索觸發(fā)器步驟3：?jiǎn)螕簟斑\(yùn)行”按鈕，運(yùn)行結(jié)果如圖所示。

任務(wù)實(shí)施步驟4：繼續(xù)在該查詢(xún)窗口中輸入insert語(yǔ)句，觸發(fā)up_in觸發(fā)器：insertintotb_ordervalues(198564666,673833825,3,"2022-08-25",2);select*fromtb_book;任務(wù)二探索觸發(fā)器步驟5：?jiǎn)螕簟斑\(yùn)行”按鈕，運(yùn)行結(jié)果如圖所示。任務(wù)二探索觸發(fā)器一、觸發(fā)器概述任務(wù)二探索觸發(fā)器觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，可以用來(lái)對(duì)表實(shí)施復(fù)雜的完整性約束，保持?jǐn)?shù)據(jù)的一致性。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會(huì)自動(dòng)被激活，并執(zhí)行觸發(fā)器中所定義的相關(guān)操作，以保證關(guān)聯(lián)數(shù)據(jù)的完整性。一般激活觸發(fā)器的事件包括INSERT、UPDATE和DELTE事件。在MySQL中，定義在觸發(fā)器中的SQL語(yǔ)句可以關(guān)聯(lián)表中的任意列，但不能直接使用列的名稱(chēng)標(biāo)識(shí)，那會(huì)使系統(tǒng)混淆，所以MySQL提供了兩個(gè)邏輯表NEW和OLD。NEW和OLD的表結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)完全一致，當(dāng)觸發(fā)器的執(zhí)行完成之后，這兩個(gè)表也會(huì)被自動(dòng)刪除。NEW表用來(lái)存放更新后的記錄。對(duì)于INSERT語(yǔ)句，NEW表中存放的是要插入的記錄；對(duì)于UPDATE語(yǔ)句，該表中存放的是要更新的記錄。任務(wù)二探索觸發(fā)器OLD表用來(lái)存放更新前的記錄。對(duì)于UPDATE語(yǔ)句，OLD表中存放的是更新前的記錄（更新完后即被刪除）；對(duì)于DELETE語(yǔ)句，該表中存放的是被刪除的記錄。任務(wù)二探索觸發(fā)器二、創(chuàng)建觸發(fā)器任務(wù)二探索觸發(fā)器語(yǔ)法格式：

CREATETRIGGER觸發(fā)器名稱(chēng)觸發(fā)時(shí)機(jī)

觸發(fā)事件

ON表名FOREACHROW觸發(fā)器激活后執(zhí)行的SQL語(yǔ)句;語(yǔ)句說(shuō)明：CREATETRIGGER：表示創(chuàng)建觸發(fā)器。觸發(fā)時(shí)機(jī)：可以取值BEFORE或AFTER。觸發(fā)事件：可以是INSERT、UPDATE和DELETE。FOREACHROW：表示數(shù)據(jù)表中任意一條記錄滿足觸發(fā)事件都會(huì)激活觸發(fā)器。提示：BEFORE是指觸發(fā)器在激活它的語(yǔ)句之前觸發(fā)。AFTER是指觸發(fā)器在激活它的語(yǔ)句之后觸發(fā)。任務(wù)二探索觸發(fā)器1.創(chuàng)建AFTER觸發(fā)器

AFTER觸發(fā)器是指觸發(fā)器監(jiān)視的觸發(fā)事件執(zhí)行之后，再激活觸發(fā)器，激活后所執(zhí)行的操作無(wú)法影響觸發(fā)器所監(jiān)視的事件。

AFTER觸發(fā)器也可以根據(jù)所監(jiān)視的事件分為三種，分別是INSERT型觸發(fā)器、UPDATE型觸發(fā)器和DELETE型觸發(fā)器?！纠?-30】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為圖書(shū)信息表tb_book創(chuàng)建insert型觸發(fā)器trig_insertbook，同時(shí)向圖書(shū)信息表tb_book中插入數(shù)據(jù)，將typeid插入到類(lèi)型信息表tb_type中的typeid中，最后顯示類(lèi)型信息表tb_type的信息。操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

代碼如下：

任務(wù)實(shí)施createtriggertrig_insertbookafterinsertontb_bookforeachrowbegininsertintotb_typevalues(new.typeid,'開(kāi)發(fā)類(lèi)');end;任務(wù)二探索觸發(fā)器（1）INSERT型觸發(fā)器步驟2：插入一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

任務(wù)實(shí)施insertintotb_bookvalues(100,10,'Java程序設(shè)計(jì)','譚浩強(qiáng)',69,100);任務(wù)二探索觸發(fā)器步驟3：查看插入記錄后tb_type表信息。代碼如下：select*fromtb_type;【例5-31】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為圖書(shū)信息表tb_book創(chuàng)建update型觸發(fā)器trig_updatebook，當(dāng)向圖書(shū)信息表tb_book中更新數(shù)據(jù)時(shí)，同時(shí)更新類(lèi)型信息表tb_type中的typeid數(shù)據(jù)，最后顯示類(lèi)型信息表tb_type的信息。

任務(wù)實(shí)施任務(wù)二探索觸發(fā)器（2）UPDATE型觸發(fā)器【例5-31】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為圖書(shū)信息表tb_book創(chuàng)建update型觸發(fā)器trig_updatebook，當(dāng)向圖書(shū)信息表tb_book中更新數(shù)據(jù)時(shí)，同時(shí)更新類(lèi)型信息表tb_type中的typeid數(shù)據(jù)，最后顯示類(lèi)型信息表tb_type的信息。

操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

代碼如下：

任務(wù)實(shí)施createtriggertrig_updatebookafterupdateontb_bookforeachrowbeginupdatetb_typesettypeid=new.typeidwheretypename='開(kāi)發(fā)類(lèi)';end;任務(wù)二探索觸發(fā)器（2）UPDATE型觸發(fā)器步驟2：更新一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

任務(wù)實(shí)施updatetb_booksettypeid=11wherebookid=100;任務(wù)二探索觸發(fā)器步驟3：查看更新記錄后的tb_type表信息。代碼如下：select*fromtb_type;【例5-32】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為圖書(shū)信息表tb_book創(chuàng)建delete型觸發(fā)器trig_deletebook，當(dāng)向圖書(shū)信息表tb_book中刪除數(shù)據(jù)時(shí)，同時(shí)刪除類(lèi)型信息表tb_type中對(duì)應(yīng)數(shù)據(jù)，最后顯示類(lèi)型信息表tb_type的信息。

任務(wù)實(shí)施任務(wù)二探索觸發(fā)器（3）DELETE型觸發(fā)器【例5-32】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為圖書(shū)信息表tb_book創(chuàng)建delete型觸發(fā)器trig_deletebook，當(dāng)向圖書(shū)信息表tb_book中刪除數(shù)據(jù)時(shí)，同時(shí)刪除類(lèi)型信息表tb_type中對(duì)應(yīng)數(shù)據(jù)，最后顯示類(lèi)型信息表tb_type的信息。

操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

代碼如下：

任務(wù)實(shí)施createtriggertrig_deletebookafterdeleteontb_bookforeachrowbegindeletefromtb_typewheretypeid=old.typeid;end;任務(wù)二探索觸發(fā)器（3）DELETE型觸發(fā)器步驟2：刪除一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

任務(wù)實(shí)施deletefromtb_bookwherebookid=100;任務(wù)二探索觸發(fā)器步驟3：查看刪除記錄后tb_type表信息。代碼如下：select*fromtb_type;任務(wù)二探索觸發(fā)器2.創(chuàng)建BEFORE觸發(fā)器

BEFORE觸發(fā)器是指觸發(fā)器在所監(jiān)視的觸發(fā)事件執(zhí)行之前激活，激活后執(zhí)行的操作先于監(jiān)視的事件，這樣就有機(jī)會(huì)進(jìn)行一些判斷或修改即將發(fā)生的操作。BEFORE觸發(fā)器可以根據(jù)所監(jiān)視的事件分為三種，分別是INSERT型觸發(fā)器、UPDATE型觸發(fā)器和DELETE型觸發(fā)器?！纠?-33】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求為訂單信息表tb_order創(chuàng)建insert型觸發(fā)器tg1，當(dāng)tb_order表中圖書(shū)數(shù)量大于tb_book表中所對(duì)應(yīng)圖書(shū)的總量，tb_book表中的圖書(shū)庫(kù)會(huì)出現(xiàn)負(fù)數(shù)，為避免這類(lèi)問(wèn)題，可以創(chuàng)建before觸發(fā)器，系統(tǒng)會(huì)先判斷訂單中圖書(shū)的購(gòu)買(mǎi)數(shù)量，如果大于庫(kù)存，則拋出異常，終止操作。

操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

運(yùn)行結(jié)果如下：

代碼如下：

任務(wù)實(shí)施任務(wù)二探索觸發(fā)器任務(wù)二探索觸發(fā)器提示：row_count()：用于記錄更新操作影響的行數(shù)，如果其值不等于1，就說(shuō)明訂單中圖書(shū)數(shù)量大于庫(kù)存的數(shù)量，tb_book表不能進(jìn)行更新，此時(shí)就會(huì)把“商品名稱(chēng)+庫(kù)存不足”賦值給變量msg。signal：用于在存儲(chǔ)過(guò)程中向調(diào)用者返回錯(cuò)誤或警告條件。此外，它還提供對(duì)錯(cuò)誤特征（錯(cuò)誤編號(hào)、sqlstate值、消息）的控制。

任務(wù)實(shí)施createtriggertg1beforeinsertontb_orderforeachrowbegindeclaremsgvarchar(200);updatetb_booksetquantity=quantity-new.totalwherebookid=new.bookidandquantity>=new.total;ifrow_count()<>1thenselectconcat(bookname,'庫(kù)存不足')intomsgfromtb_bookwherebookid=new.bookid; signalsqlstate'TX000'setmessage_text=msg;endif;end;任務(wù)二探索觸發(fā)器步驟2：插入一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

任務(wù)實(shí)施insertintotb_ordervalues(184514955,100101255,13,'2022-8-20',200);任務(wù)二探索觸發(fā)器步驟3：查看tb_book表信息。代碼如下：

select*fromtb_book;任務(wù)二探索觸發(fā)器三、查看觸發(fā)器任務(wù)二探索觸發(fā)器語(yǔ)法格式：

SHOWTRIGGERS;語(yǔ)句說(shuō)明：SHOWTRIGGERS：查看觸發(fā)器的基本信息?！纠?-34】要求在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，查看所有觸發(fā)器。

代碼如下：

任務(wù)實(shí)施showtriggers;任務(wù)二探索觸發(fā)器任務(wù)二探索觸發(fā)器四、刪除觸發(fā)器任務(wù)二探索觸發(fā)器語(yǔ)法格式：

DROPTRIGGER[數(shù)據(jù)庫(kù)名].觸發(fā)器名稱(chēng);語(yǔ)句說(shuō)明：DROPTRIGGER：刪除已經(jīng)存在的觸發(fā)器。數(shù)據(jù)庫(kù)名：可選項(xiàng)，如果刪除不是當(dāng)前數(shù)據(jù)庫(kù)中的觸發(fā)器，則不可以省略。觸發(fā)器名稱(chēng)：表示要?jiǎng)h除的觸發(fā)器名稱(chēng)?！纠?-35】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求刪除trig_insertbook觸發(fā)器。

代碼如下：

任務(wù)實(shí)施droptriggertrig_insertbook;任務(wù)二探索觸發(fā)器1、在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求創(chuàng)建insert型觸發(fā)器trigInsertOrder，觸發(fā)時(shí)機(jī)是before，當(dāng)向訂單信息表tb_order中插入數(shù)據(jù)時(shí)，同時(shí)也在圖書(shū)信息表tb_book中插入對(duì)應(yīng)的數(shù)據(jù)，最后顯示圖書(shū)信息表tb_book的信息。請(qǐng)根據(jù)運(yùn)行結(jié)果將代碼補(bǔ)充完整。

操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

代碼如下：

動(dòng)手實(shí)踐

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

#設(shè)置觸發(fā)器時(shí)機(jī)、觸發(fā)器事件和觸發(fā)器作用的表foreachrowbegininsertintotb_book(bookid,typeid,bookprice)values(

.bookid,1,120);end;任務(wù)二探索觸發(fā)器步驟2：插入一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

insertintotb_ordervalues(111111112,100000001,99,'2021-08-19',10);任務(wù)二探索觸發(fā)器步驟3：查看插入記錄后tb_book表信息。代碼如下：select*fromtb_bookwherebookid=99;動(dòng)手實(shí)踐2、在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求創(chuàng)建update型觸發(fā)器trigUpdatebook，觸發(fā)時(shí)機(jī)是after，當(dāng)向圖書(shū)信息表tb_book中更新數(shù)據(jù)時(shí)，同時(shí)也在訂單信息表tb_order中更新對(duì)應(yīng)的數(shù)據(jù)，最后顯示訂單信息表tb_order的信息。請(qǐng)根據(jù)運(yùn)行結(jié)果將代碼補(bǔ)充完整。

操作步驟如下：

步驟1：創(chuàng)建觸發(fā)器。

代碼如下：

動(dòng)手實(shí)踐

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

#設(shè)置觸發(fā)器時(shí)機(jī)、觸發(fā)器事件和觸發(fā)器作用的表

#對(duì)表中每一行都生效beginupdatetb_ordersetbookid=new.bookidwhereorderid=111111112;end;任務(wù)二探索觸發(fā)器步驟2：更新一條記錄實(shí)現(xiàn)觸發(fā)觸發(fā)器。

代碼如下：

updatetb_booksetbookid=9999wherebookprice=120;任務(wù)二探索觸發(fā)器步驟3：查看更新記錄后tb_order表信息。代碼如下：select*fromtb_orderwhereorderid=111111112;動(dòng)手實(shí)踐3、在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求查看觸發(fā)器信息。請(qǐng)根據(jù)題意將代碼補(bǔ)充完整。

動(dòng)手實(shí)踐

#查看觸發(fā)器信息任務(wù)二探索觸發(fā)器4、在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求刪除觸發(fā)器trigUpdatebook。請(qǐng)根據(jù)題意將代碼補(bǔ)充完整。

#刪除觸發(fā)器trigUpdatebook

1+X知識(shí)鞏固

1、創(chuàng)建觸發(fā)器的關(guān)鍵語(yǔ)句是（）。A、createfunctionB、createprocedureC、createtriggerD、createevent2、一般激活觸發(fā)器的事件包括insert、update和（）事件。A、createB、alterC、dropD、delete3、下列說(shuō)法中錯(cuò)誤的是（）。A、常用的觸發(fā)器有insert、update、delete三種B、對(duì)于同一張數(shù)據(jù)表，可以同時(shí)有兩個(gè)beforeupdate觸發(fā)器C、new表在insert觸發(fā)器中用來(lái)訪問(wèn)被插入的行D、old表中值只讀不能被更新任務(wù)二探索觸發(fā)器任務(wù)三處理事務(wù)看情景，明目標(biāo)溫舊知，做準(zhǔn)備

劉老師，我想試驗(yàn)一種極端情況，比如買(mǎi)家購(gòu)買(mǎi)圖書(shū)信息后結(jié)賬的瞬間，斷電了或者其他意外情況，買(mǎi)家的錢(qián)已經(jīng)扣除，顯示余額變少，但是賣(mài)家的錢(qián)沒(méi)有增加，怎么辦？為了預(yù)防這種情況發(fā)生，數(shù)據(jù)庫(kù)設(shè)計(jì)了事務(wù)機(jī)制。事務(wù)是一組有著內(nèi)在邏輯聯(lián)系的SQL語(yǔ)句，這些SQL語(yǔ)句執(zhí)行的數(shù)據(jù)結(jié)果存在一定的關(guān)聯(lián)，要么都執(zhí)行成功，要么都不執(zhí)行，且事務(wù)必須同時(shí)滿足ACID特征。通過(guò)使用事務(wù)可以大大提高數(shù)據(jù)安全性和執(zhí)行效率。走進(jìn)程序設(shè)計(jì)探索觸發(fā)器

要求：在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，采用事務(wù)提交的方法，設(shè)置訂單信息表tb_order中的下單日期orderdate為當(dāng)前系統(tǒng)時(shí)間。

任務(wù)描述任務(wù)三處理事務(wù)

任務(wù)實(shí)施操作步驟如下：

步驟1：在Navicat主窗口中依次單擊“查詢(xún)”—>“新建查詢(xún)”按鈕，創(chuàng)建一個(gè)查詢(xún)窗口。任務(wù)三處理事務(wù)

任務(wù)實(shí)施步驟2：在該查詢(xún)窗口中輸入：starttransaction;updatetb_ordersetorderdate=date_format(now(),'%Y-%m-%d');commit;任務(wù)三處理事務(wù)步驟3：?jiǎn)螕簟斑\(yùn)行”按鈕,運(yùn)行結(jié)果如圖所示。

任務(wù)實(shí)施步驟4：繼續(xù)在該查詢(xún)窗口中輸入查詢(xún)語(yǔ)句，查看orderdate的時(shí)間。select*fromtb_order;任務(wù)三處理事務(wù)步驟5：?jiǎn)螕簟斑\(yùn)行”按鈕,運(yùn)行結(jié)果如圖所示。任務(wù)三處理事務(wù)一、事務(wù)概述任務(wù)三處理事務(wù)事務(wù)是一組有著內(nèi)在邏輯聯(lián)系的SQL語(yǔ)句，可以是一條非常簡(jiǎn)單的SQL語(yǔ)句組成，也可以由一組復(fù)雜的SQL語(yǔ)句組成。支持事務(wù)的數(shù)據(jù)庫(kù)系統(tǒng)要么正確執(zhí)行事務(wù)里的所有SQL語(yǔ)句，要么把它們當(dāng)做整體全部放棄，也就是說(shuō)事務(wù)永遠(yuǎn)不會(huì)只完成一部分。在事務(wù)中的操作，要么都執(zhí)行，要么都不執(zhí)行，這就是事務(wù)的目的，也是事務(wù)的重要特征之一。使用事務(wù)可以大大提高數(shù)據(jù)安全性和執(zhí)行效率。1.事務(wù)的定義任務(wù)三處理事務(wù)1324原子性是指數(shù)據(jù)庫(kù)事務(wù)是不可分割的操作單位。只有使事務(wù)中所有的數(shù)據(jù)庫(kù)操作都執(zhí)行成功，整個(gè)事務(wù)的執(zhí)行才算成功。原子性（Atomicity）

一致性是指事務(wù)將數(shù)據(jù)庫(kù)從一種狀態(tài)變成另一種狀態(tài)。在事務(wù)開(kāi)始之前和事務(wù)結(jié)束之后，數(shù)據(jù)的完整性約束沒(méi)有被破壞。一致性（Consistency）隔離性要求每個(gè)讀寫(xiě)事務(wù)的對(duì)象與其他事務(wù)的操作對(duì)象能相互分離，即該事務(wù)提交前對(duì)其他事務(wù)都不可見(jiàn)。隔離性（Isolation）事務(wù)一旦提交，其結(jié)果就是永久性的，即使發(fā)生死機(jī)等故障，數(shù)據(jù)庫(kù)也能將數(shù)據(jù)恢復(fù)。持久性（Durability）2.事務(wù)的特征任務(wù)三處理事務(wù)二、事務(wù)的基本操作任務(wù)三處理事務(wù)語(yǔ)法格式：

第1步：START

TRANSACTION;

第2步：SQL語(yǔ)句集;

第3步：COMMIT/

ROLLBACK;語(yǔ)句說(shuō)明：STARTTRANSACTION：表示開(kāi)始事務(wù)。COMMIT：關(guān)鍵字，表示提交事務(wù)。ROLLBACK：關(guān)鍵字，表示回滾事務(wù)。提示：ROLLBACK只能針對(duì)未提交的事務(wù)回滾，已提交的事務(wù)無(wú)法回滾?！纠?-36】在“天意書(shū)屋”數(shù)據(jù)庫(kù)中，要求采用事務(wù)提交的方法，設(shè)置圖書(shū)信息表tb_book中“mysql數(shù)據(jù)庫(kù)程序設(shè)計(jì)”的圖書(shū)作者為空，同時(shí)價(jià)格在原來(lái)價(jià)格的基礎(chǔ)上增加20元。

操作步驟如下：

步驟1：查看“mysql數(shù)據(jù)庫(kù)程序設(shè)計(jì)”圖書(shū)信息。

代碼如下：

任務(wù)實(shí)施select*fromtb_bookwherebookname='mysql數(shù)據(jù)庫(kù)程序設(shè)計(jì)';任務(wù)三處理事務(wù)步驟2：事務(wù)提交設(shè)置圖書(shū)作者為空，同時(shí)圖書(shū)價(jià)格在原來(lái)基礎(chǔ)上增加20元。

代碼如下：

任務(wù)實(shí)施starttransaction;updatetb_booksetauthor=''wherebookname='mysql數(shù)據(jù)庫(kù)程序設(shè)計(jì)';updatetb_booksetbookprice=bookprice+20wherebooknam