MySQL數(shù)據(jù)庫應(yīng)用與管理實(shí)戰(zhàn) 教案匯 張俊華 模塊6-9 用SQL語句查詢MySQL數(shù)據(jù)表-安全管理與備份MySQL數(shù)據(jù)庫

教學(xué)過程教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與過程（教學(xué)內(nèi)容、教學(xué)方法、組織形式、教學(xué)手段）課前組織做好上課前的各項(xiàng)準(zhǔn)備工作（打開計算機(jī)、打開課件、打開軟件、打開授課計劃、教案等），吸引學(xué)生注意力。課程說明【課前說明】分別從MySQL的常量、變量等知識點(diǎn)進(jìn)行初步的了解。【目的】使學(xué)生從了解本節(jié)課的學(xué)習(xí)目標(biāo)、學(xué)習(xí)重點(diǎn)、考評方式等方面明確課程學(xué)習(xí)的要求和目標(biāo)。課程內(nèi)容描述8.1執(zhí)行多條語句獲取MySQL表數(shù)據(jù)MySQL語句可以包含常量、變量、運(yùn)算符、表達(dá)式、函數(shù)、流程控制語句和注釋等語言元素，每條SQL語句都以半角分號結(jié)束，并且SQL處理器會忽略空格、制表符和回車符等。8.1.1MySQL的常量常量是指在SQL語句或程序運(yùn)行過程中，其值不會改變的量。1．?dāng)?shù)值常量在SQL語言中，數(shù)值常量包括整數(shù)和小數(shù)，并且使用時不需要使用引號，例如3.14、5、-56.7等。正數(shù)可以不加正號“+”表示，例如3.5，也可以添加正號“+”表示，例如+3.5。負(fù)責(zé)必須添加負(fù)6。2．字符串常量在SQL語言中，字符串常量必須使用半角單引號（''）或半角雙引號（""）引起來，可以包括大小寫字母、數(shù)字以及!、@、#等特殊字符。3．日期和時間常量在SQL語言中，日期和時間常量必須使用半角單引號（''）或半角雙引號（""）引起來，例如"2020-10-2511:13:08"。日期是按照年、月、日的順序來表示的，中間使用分隔符“-”，也可以使用“/”。日期和時間常量的值必須符合日期和時間的標(biāo)準(zhǔn)，例如一月沒有32號、二月沒有30號等。4．布爾常量在MySQL中，布爾常量包含兩個值，分別為True和False。其中True表示邏輯真，通常表示一個表達(dá)式或條件成立，對應(yīng)數(shù)字值“1”；False表示邏輯假，通常表示一個表達(dá)式或條件不成立，對應(yīng)數(shù)字值“0”。5．NullNull通常用來表示“值不確定”“無數(shù)據(jù)”等情況，并且不同于數(shù)字類型的“0”或字符串類型的空字符串。8.1.2MySQL的變量變量是指在程序運(yùn)行過程中其值可以改變的量。變量可以保存查詢結(jié)果，也可以在查詢語句中使用，其值還可以被插入數(shù)據(jù)表中，MySQL中變量的使用非常靈活方便。變量名稱不能與MySQL中的命令或已有的函數(shù)名稱相同。1．用戶變量用戶可以在表達(dá)式中使用自己定義的變量，這樣的變量稱為用戶變量。用戶可以先在用戶變量中保存值，然后在以后的語句中引用該值，這樣可以將值從一條語句傳遞到另一條語句，用戶變量在整個會話期都有效。用戶變量在使用前必須定義和初始化。如果使用沒有初始化的變量，其值為Null。用戶變量與當(dāng)前連接有關(guān)，也就是說，一個客戶端定義的變量不能被其他客戶端使用。當(dāng)客戶端退出時，該客戶端連接的所有變量將自動釋放。定義和初始化一個用戶變量可以使用Set語句，其語法格式如下：Set@<變量名稱1>=<表達(dá)式1>[,@<變量名稱2>=<表達(dá)式2>,…];定義和初始化用戶變量的規(guī)則如下。（1）用戶變量以“@”開頭，形式為“@變量名稱”，以便將用戶變量和字段名區(qū)分開。變量名稱必須符合MySQL標(biāo)識符的命名規(guī)則，即變量可以由當(dāng)前字符集的字符、數(shù)字、“.”、“_”和“$”組成。如果變量名稱中需要包含一些特殊字符（例如空格、#等），可以使用半角雙引號或半角單引號將整個變量名稱引起來。（2）“<表達(dá)式>”的值是要給變量賦的值，可以是常量、變量或表達(dá)式。（3）用戶變量的數(shù)據(jù)類型是根據(jù)其所賦予的值的數(shù)據(jù)類型自動定義的，例如:Set@name="admin";此時變量name的數(shù)據(jù)類型也為字符串類型，如果重新給變量name賦值，例如：Set@name=2;此時變量name的數(shù)據(jù)類型則為整型，即變量name的數(shù)據(jù)類型隨所賦的值而改變。（4）定義用戶變量時變量的值可以是一個表達(dá)式，例如：Set@name=@name+3;（5）一條定義語句中可以同時定義多個變量，中間使用半角逗號分隔，例如：Set@name,@number,@sex;（6）對于Set語句，可以使用“=”或“:=”作為賦值符給每個用戶變量賦值，被賦值的類型可以為整型、小數(shù)、字符串或Null。可以用其他SQL語句代替Set語句為用戶變量賦值。在這種情況下，賦值符必須為“:=”，而不能使用“=”，因?yàn)樵诜荢et語句中“=”被視為比較運(yùn)算符。（7）可以使用查詢結(jié)果給用戶變量賦值，例如：Set@name=(Select用戶名稱From用戶注冊信息Where用戶編號='u00003');（8）在一個用戶變量被定義后，它可以以一種特殊形式的表達(dá)式用于其他SQL語句中，變量名稱前面也必須加上符號“@”。例如，使用Select語句查詢前面所定義的變量name的值：Select@name;該語句的執(zhí)行結(jié)果如圖所示。語句“Select@name;”的執(zhí)行結(jié)果例如，從“用戶注冊信息”數(shù)據(jù)表中查詢“用戶名稱”為用戶變量name中所存儲的值的用戶注冊信息，對應(yīng)的語句如下：Select*From用戶注冊信息Where用戶名稱=@name;該語句的執(zhí)行結(jié)果如圖所示。語句“Select*From用戶注冊信息Where用戶名稱=@name;”的執(zhí)行結(jié)果由于在Select語句中，表達(dá)式的值要發(fā)送到客戶端后才能進(jìn)行計算，這說明在Having、GroupBy或OrderBy子句中，不能使用包含用戶變量的表達(dá)式。2．系統(tǒng)變量MySQL有一些特定的設(shè)置，當(dāng)MySQL數(shù)據(jù)庫服務(wù)器啟動的時候，這些設(shè)置會被讀取來決定下一步驟，這些設(shè)置就是系統(tǒng)變量。系統(tǒng)變量在MySQL數(shù)據(jù)庫服務(wù)器啟動時就被引入并初始化為默認(rèn)值。系統(tǒng)變量一般以“@@”為前綴，例如“@@Version”返回MySQL的版本。但某些特定的系統(tǒng)變量可以省略“@@”，例如Current_Date（系統(tǒng)日期）、Current_Time（系統(tǒng)時間）、Current_Timestamp（系統(tǒng)日期和時間）和Current_User（當(dāng)前用戶名）。查看這些系統(tǒng)變量的值的語句如下：Select@@Version,Current_Date,Current_Time,Current_Timestamp,Current_User;該語句的執(zhí)行如果如圖所示。查看多個系統(tǒng)變量值的語句的執(zhí)行結(jié)果在MySQL中，有些系統(tǒng)變量的值是不可改變的，例如Version和系統(tǒng)日期。而有些系統(tǒng)變量的值可以通過Set語句來修改。更改系統(tǒng)變量值的語法格式如下：Set<系統(tǒng)變量名稱>=<表達(dá)式>|[Global|Session]<系統(tǒng)變量名稱>=<表達(dá)式>|@@[Global.|Session.]<系統(tǒng)變量名稱>=<表達(dá)式>;系統(tǒng)變量可以分為全局系統(tǒng)變量和會話系統(tǒng)變量兩種類型。在為系統(tǒng)變量設(shè)定新值的語句中，使用Global或“@@global.”關(guān)鍵字的是全局系統(tǒng)變量，使用Session和“@@session.”關(guān)鍵字的是會話系統(tǒng)變量。Session和“@@session”的同義詞為Local和“@@local.”。如果在使用系統(tǒng)變量時不指定關(guān)鍵字，則默認(rèn)為會話系統(tǒng)變量。只有具有super權(quán)限的用戶才可以設(shè)置全局系統(tǒng)變量。顯示所有系統(tǒng)變量的語句為：ShowVariables;顯示所有全局系統(tǒng)變量的語句為：ShowGlobalVariables;顯示所有會話系統(tǒng)變量的語句為：ShowSessionVariables;要顯示與樣式匹配的變量名稱或名稱列表，需使用Like子句和通配符“%”，例如：ShowVariablesLike'character%';（1）全局系統(tǒng)變量。當(dāng)MySQL數(shù)據(jù)庫服務(wù)器啟動的時候，全局系統(tǒng)變量就被初始化了，并應(yīng)用于每個啟動的會話。全局系統(tǒng)變量對所有客戶端有效，其值能應(yīng)用于當(dāng)前連接，也能應(yīng)用于其他連接，直到服務(wù)器重新啟動為止。（2）會話系統(tǒng)變量。會話系統(tǒng)變量對當(dāng)前連接的客戶端有效，只適用于當(dāng)前的會話。會話系統(tǒng)變量的值是可以改變的，但是其新值僅適用于正在運(yùn)行的會話，不適用于其他會話。例如，對于當(dāng)前會話，把會話系統(tǒng)變量SQL_Select_Limit的值設(shè)置為10。該變量決定了Select語句的結(jié)果集中返回的最大行數(shù)，對應(yīng)的語句如下：Set@@Session.SQL_Select_Limit=10;Select@@Session.SQL_Select_Limit;語句的執(zhí)行結(jié)果如圖所示。改變會話系統(tǒng)變量的值的語句的執(zhí)行結(jié)果這里在系統(tǒng)變量的名稱前面使用了關(guān)鍵字Session（使用Local也可以），明確地表示會話系統(tǒng)變量SQL_Select_Limit和Set語句指定的值保持一致。但是，同名的全局系統(tǒng)變量的值仍然不變。同樣，如果改變了全局系統(tǒng)變量的值，同名的會話系統(tǒng)變量的值也保持不變。MySQL中的大多數(shù)系統(tǒng)變量都有默認(rèn)值，當(dāng)數(shù)據(jù)庫服務(wù)器啟動時，就使用這些默認(rèn)值。如果要將一個系統(tǒng)變量的值設(shè)置為MySQL的默認(rèn)值，可以使用Default關(guān)鍵字。例如，將會話系統(tǒng)變量SQL_Select_Limit的值恢復(fù)為MySQL的默認(rèn)值的語句如下：Set@@Session.SQL_Select_Limit=Default;3．局部變量局部變量是可以保存單個特定類型數(shù)據(jù)值的變量，其有效作用范圍為存儲過程和自定義函數(shù)的Begin到End語句塊之間。在Begin…End語句塊運(yùn)行結(jié)束之后，局部變量就消失了，其他語句塊中不可以使用該局部變量，但Begin…End語句塊內(nèi)所有語句都可以使用該局部變量。MySQL中的局部變量必須先定義后使用。使用Declare語句定義局部變量的語法格式如下：Declare<變量名稱><數(shù)據(jù)類型>[Default<默認(rèn)值>];Default子句用于給變量指定一個默認(rèn)值，如果不指定則默認(rèn)為Null。局部變量的名稱必須符合MySQL標(biāo)識符的命名規(guī)則，局部變量前面不使用“@”符號。該定義語句無法單獨(dú)執(zhí)行，只能在存儲過程和自定義函數(shù)中使用。例如：Declarenamevarchar(30);可以使用一條語句同時聲明多個變量，變量之間使用半角逗號分隔。例如：Declarenamevarchar(20),numberint,sexchar(1);可以使用Set語句為局部變量賦值，Set語句也是SQL本身的一部分，其語法格式如下：Set<局部變量名稱1>=<表達(dá)式1>,<局部變量名稱2>=<表達(dá)式2>,……;例如：Setname='安翔',number=2,sex='男';也可以使用Select…Into語句將獲取的字段值賦給局部變量，并且返回的結(jié)果只能有一條記錄值，其語法格式如下：Select<字段名>[,…]Into<局部變量名稱>[,…][From子句][Where子句];例如：SelectSum(應(yīng)付金額)IntonumberFrom訂單信息;使用Select語句給變量賦值時，如果省略了From子句和Where子句，就等同于使用Set語句給變量賦值。如果有From子句和Where子句，并且Select語句返回多個值，則只將返回的最后一個值賦給局部變量。8.1.3MySQL的運(yùn)算符與表達(dá)式1．運(yùn)算符運(yùn)算符是一種符號，用來指定要在一個或多個表達(dá)式中執(zhí)行的操作，MySQL中的運(yùn)算符主要有如下類型。（1）算術(shù)運(yùn)算符。算術(shù)運(yùn)算符用于對兩個表達(dá)式進(jìn)行數(shù)學(xué)運(yùn)算，這兩個表達(dá)式可以是任何數(shù)值類型。MySQL中的算術(shù)運(yùn)算符有：+（加）、-（減）、*（乘）、/（除）、%（取模）?！?”運(yùn)算符用于獲得兩個或多個值的和，“-”運(yùn)算符用于從一個值中減去另一個值?！?”和“-”運(yùn)算符還可用于對日期時間值進(jìn)行算術(shù)運(yùn)算，例如計算年齡?！?”運(yùn)算符用于獲得兩個或多個值的乘積?！?”運(yùn)算符用于獲得一個值除以另一個值的商，并且除數(shù)不能為零?！?”運(yùn)算符用來獲得一個或多個除法運(yùn)算的余數(shù)，并且除數(shù)不能為零。進(jìn)行算術(shù)運(yùn)算時，用字符串表示的數(shù)字會自動轉(zhuǎn)換為數(shù)值類型。當(dāng)執(zhí)行轉(zhuǎn)換時，如果字符串的前幾個字符或全部字符是數(shù)字，那么它將被轉(zhuǎn)換為對應(yīng)數(shù)字的值，否則被轉(zhuǎn)換為零。（2）賦值運(yùn)算符。=（等號）是MySQL中的賦值運(yùn)算符，可以用于將表達(dá)式的值賦給一個變量。（3）比較運(yùn)算符（又稱為關(guān)系運(yùn)算符）。比較運(yùn)算符用于對兩個表達(dá)式進(jìn)行比較，可以用于比較數(shù)字和字符串。數(shù)字作為浮點(diǎn)值進(jìn)行比較，字符串以不區(qū)分大小寫的方式進(jìn)行比較（除非使用特殊的Binary關(guān)鍵字），例如大寫字母“A”和小寫字母“a”比較，其結(jié)果為相等。比較的結(jié)果為1（True）或0（False），即表達(dá)式成立，結(jié)果為1，表達(dá)式不成立則結(jié)果為0。MySQL中的比較運(yùn)算符有：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等于）、<=>（相等或都等于空，可以用來判斷是否為Null）。（4）邏輯運(yùn)算符。邏輯運(yùn)算符用于對某些條件進(jìn)行測試，以獲得其真假情況。邏輯運(yùn)算符和比較運(yùn)算符一樣，運(yùn)行結(jié)果是1（True）或0（False）。MySQL中的邏輯運(yùn)算符有：And或者&&（如果兩個表達(dá)式都為True，并且不是Null，則結(jié)果為True，否則結(jié)果為False）、Or或者||（如果兩個表達(dá)式中的任何一個為True，并且不是Null，則結(jié)果為True，否則結(jié)果為False）、Not或!（對任何其他運(yùn)算符的結(jié)果取反，True變?yōu)镕alse，F(xiàn)alse變?yōu)門rue）、Xor（如果表達(dá)式一個為True，而另一個為False并且不是Null，則結(jié)果為True，否則結(jié)果為False）。（5）位運(yùn)算符。位運(yùn)算符用于對兩個表達(dá)式進(jìn)行二進(jìn)制位操作，這兩個表達(dá)式可以是整型或與整型兼容的數(shù)據(jù)類型（如字符型，但不能為image類型）。MySQL中的位運(yùn)算符有：&（位與）、|（位或）、^（位異或）、~（位取反）、>>（位右移）、<<（位左移）。（6）一元運(yùn)算符。一元運(yùn)算符只對一個表達(dá)式進(jìn)行操作，該表達(dá)式可以是數(shù)值類型中的任何一種數(shù)據(jù)類型。MySQL中的一元運(yùn)算符有：+（正）、-（負(fù)）和～（位取反）。除了以上的運(yùn)算符，MySQL還提供了其他一些運(yùn)算符，例如All、Any、Some、Between、In、IsNull、IsNotNull、Like、Regexp等運(yùn)算符，這些運(yùn)算符在前面單元已介紹過，這里不贅述。2．表達(dá)式表達(dá)式是常量、變量、字段值、運(yùn)算符和函數(shù)的組合，MySQL可以對表達(dá)式求值以獲取結(jié)果，一個表達(dá)式通常可以得到一個值。與常量和變量一樣，表達(dá)式的值也是某種數(shù)據(jù)類型，例如字符類型、數(shù)值類型、日期時間類型等。根據(jù)表達(dá)式的值的數(shù)據(jù)類型，表達(dá)式可分為字符型表達(dá)式、數(shù)值表達(dá)式和日期表達(dá)式。3．運(yùn)算符的優(yōu)先級當(dāng)一個復(fù)雜的表達(dá)式有多個運(yùn)算符時，運(yùn)算符優(yōu)先級決定運(yùn)算執(zhí)行的先后次序。執(zhí)行的順序有時會影響所得到的運(yùn)算結(jié)果。MySQL運(yùn)算符的優(yōu)先級如表所示，在一個表達(dá)式中，按運(yùn)算符優(yōu)先級先高（優(yōu)先級數(shù)字小的）后低（優(yōu)先級數(shù)字大的）的順序進(jìn)行運(yùn)算。MySQL運(yùn)算符的優(yōu)先級優(yōu)先級運(yùn)算符優(yōu)先級運(yùn)算符1（最高）!8|（位或）2+（正）、-（負(fù)）、～（位取反）9=、<>、!=、<、<=、>、>=、<=>、Is、Like、In（比較運(yùn)算）3^（位異或）10Between、Case、While、Then、Else4*、/、%11Not5+（加）、-（減）12And、&&6<<、>>13Or、||7&（位與）14（最低）=、:=（賦值運(yùn)算）當(dāng)一個表達(dá)式中的兩個運(yùn)算符有相同的優(yōu)先級時，根據(jù)它們在表達(dá)式中的位置進(jìn)行運(yùn)算。一般情況下，一元運(yùn)算符按從右到左（即右結(jié)合性）的順序進(jìn)行運(yùn)算，二元運(yùn)算符按從左到右（即左結(jié)合性）的順序進(jìn)行運(yùn)算。8.1.4MySQL的控制語句1．Begin…End語句MySQL中Begin…End語句用于將多個SQL語句組合為一個語句塊，相當(dāng)于一條語句，達(dá)到一起執(zhí)行的目的。Begin…End語句的語法格式如下：Begin<語句1>;<語句2>;…<語句n>;EndMySQL中允許嵌套使用Begin…End語句。2．If…Then…Else語句If…Then…Else語句用于進(jìn)行條件判斷，實(shí)現(xiàn)程序的選擇結(jié)構(gòu)。根據(jù)是否滿足條件，將執(zhí)行不同的語句，其語法格式如下：If<條件表達(dá)式1>Then<語句塊1>[Elseif<條件表達(dá)式2>Then<語句塊2>][Else<語句塊3>]EndIf;其中，語句塊可以是單條或多條SQL語句。If語句的執(zhí)行過程為：如果條件表達(dá)式的值為True，則執(zhí)行對應(yīng)的語句塊；如果所有的條件表達(dá)式的值為False，并且有Else子句，則執(zhí)行Else子句對應(yīng)的語句塊。在If…Then…Else語句中允許嵌套使用If…Else語句。3．Case語句Case語句用于計算列表并返回多個可能結(jié)果表達(dá)式中的一個，可用于實(shí)現(xiàn)程序的多分支結(jié)構(gòu)。雖然使用If…Then…Else語句也能夠?qū)崿F(xiàn)多分支結(jié)構(gòu)，但是使用Case語句的程序的可讀性更強(qiáng)，一條Case語句經(jīng)?？梢猿洚?dāng)一條If…Then…Else語句。在MySQL中，Case語句有以下兩種形式。（1）簡單Case語句。簡單Case語句用于將某個表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定其返回值，其語法格式如下：Case<條件表達(dá)式>When<表達(dá)式1>Then<SQL語句1>When<表達(dá)式2>Then<SQL語句2>…When<表達(dá)式n>Then<SQL語句n>[Else<其他SQL語句>]EndCase;簡單Case語句的執(zhí)行過程是將“條件表達(dá)式”與各個When子句后面的“表達(dá)式”進(jìn)行比較，如果相等，則執(zhí)行對應(yīng)的“SQL語句”，然后跳出Case語句，不再執(zhí)行后面的When子句；如果When子句中沒有與“條件表達(dá)式”相等的“表達(dá)式”，如果指定了Else子句，則執(zhí)行Else子句后面的“其他SQL語句”；如果沒有指定Else子句，則沒有執(zhí)行Case語句內(nèi)任何一條SQL語句。（2）搜索Case語句。搜索Case語句用于計算一組邏輯表達(dá)式以確定返回結(jié)果，其語法格式如下：CaseWhen<邏輯表達(dá)式1>Then<SQL語句1>When<邏輯表達(dá)式2>Then<SQL語句2>…When<邏輯表達(dá)式n>Then<SQL語句n>[Else<其他SQL語句>]EndCase;搜索Case語句的執(zhí)行過程是先計算第1個When子句后面的“邏輯表達(dá)式1”的值，如果值為True，則Case語句執(zhí)行對應(yīng)的“SQL語句”；如果為False，則按順序計算When子句后面的“邏輯表達(dá)式”的值，且執(zhí)行計算結(jié)果為True的第1個“邏輯表達(dá)式”對應(yīng)的“SQL語句”；在所有的“邏輯表達(dá)式”的值都為False的情況下，如果指定了Else子句，則執(zhí)行Else子句后面的“其他SQL語句”；如果沒有指定Else子句，則沒有執(zhí)行Case語句內(nèi)任何一條SQL語句。4．While循環(huán)語句While循環(huán)語句用于實(shí)現(xiàn)循環(huán)結(jié)構(gòu)，是有條件控制的循環(huán)語句，當(dāng)滿足某種條件時執(zhí)行循環(huán)體內(nèi)的語句。While循環(huán)語句的語法格式如下：[開始標(biāo)注：]While<邏輯表達(dá)式>Do<語句塊>EndWhile[結(jié)束標(biāo)注];While循環(huán)語句的執(zhí)行過程說明如下。首先判斷邏輯表達(dá)式的值是否為True，為True則執(zhí)行“語句塊”中的語句，然后再次進(jìn)行判斷，為True則繼續(xù)循環(huán)，為False則結(jié)束循環(huán)。“開始標(biāo)注：”和“結(jié)束標(biāo)注”是While循環(huán)語句的標(biāo)注，除非“開始標(biāo)注：”存在，否則“結(jié)束標(biāo)注”不能出現(xiàn)，并且如果兩者都出現(xiàn)，它們的名稱必須是相同的。“開始標(biāo)注：”和“結(jié)束標(biāo)注”通常都可以省略。5．Repeat循環(huán)語句Repeat循環(huán)語句是有條件控制的循環(huán)語句，當(dāng)滿足特定條件時，就跳出循環(huán)語句。Repeat循環(huán)語句的語法格式如下：[開始標(biāo)注：]Repeat<語句塊>Until<邏輯表達(dá)式>EndRepeat[結(jié)束標(biāo)注];Repeat循環(huán)語句的執(zhí)行過程說明如下。首先執(zhí)行語句塊中的語句，然后判斷邏輯表達(dá)式的值是否為True，為True則停止循環(huán)，為False則繼續(xù)循環(huán)。Repeat循環(huán)語句也可以被標(biāo)注。Repeat循環(huán)語句與While循環(huán)語句的區(qū)別在于：Repeat循環(huán)語句是先執(zhí)行語句，后進(jìn)行條件判斷；而While循環(huán)語句則是先進(jìn)行條件判斷，條件為True才執(zhí)行語句。6．Loop循環(huán)語句Loop循環(huán)語句可以使某些語句重復(fù)執(zhí)行，實(shí)現(xiàn)一些簡單的循環(huán)。但是Loop循環(huán)語句本身沒有停止循環(huán)的機(jī)制，必須遇到Leave語句才能停止循環(huán)。Loop循環(huán)語句的語法格式如下：[開始標(biāo)注：]Loop<語句塊>EndLoop[結(jié)束標(biāo)注];Loop循環(huán)語句允許某特定語句或語句塊重復(fù)執(zhí)行，以實(shí)現(xiàn)一些簡單的循環(huán)結(jié)構(gòu)。在循環(huán)體內(nèi)的語句一直重復(fù)執(zhí)行直到循環(huán)被強(qiáng)迫終止，通常使用Leave語句終止循環(huán)。7．Leave語句Leave語句主要用于跳出循環(huán)控制，經(jīng)常和循環(huán)語句一起使用，其語法格式如下：Leave<標(biāo)注名>;使用Leave語句可以退出被標(biāo)注的循環(huán)語句，標(biāo)注名是自定義的。8．Iterate語句Iterate語句用于跳出本次循環(huán)，然后直接進(jìn)入下一次循環(huán)，其語法格式如下：Iterate<標(biāo)注名>;Iterate語句與Leave語句都是用來跳出循環(huán)語句的，但兩者的功能不一樣：Leave語句用來跳出整個循環(huán)，然后執(zhí)行循環(huán)語句后面的語句；而Iterate語句用來跳出本次循環(huán)，然后進(jìn)行下一次循環(huán)。8.1.5MySQL的注釋符MySQL的注釋符有以下3種：（1）#<注釋文本>；（2）--<注釋文本>（注意“--”后面有一個空格）；（3）/*<注釋文本>*/?！救蝿?wù)8-1】在【命令提示符】窗口中編輯與執(zhí)行多條SQL語句【任務(wù)描述】在【命令提示符】窗口中編輯與執(zhí)行多條SQL語句，實(shí)現(xiàn)以下功能：（1）為用戶變量name賦值“人民郵電出版社”。（2）從數(shù)據(jù)表“出版社信息”中查詢“人民郵電出版社”的“出版社ID”字段的值，并且將該值存儲在用戶變量id中。（3）從數(shù)據(jù)表“圖書信息”中查詢“人民郵電出版社”出版的圖書種類數(shù)量，并且將圖書種類數(shù)量存儲在用戶變量num中。（4）顯示用戶變量name、id和num的值?！救蝿?wù)實(shí)施】在命令提示符后輸入以下語句：UseMallDB;Set@name="人民郵電出版社";--給變量name賦值Set@id=(Select出版社IDFrom出版社信息Where出版社名稱="人民郵電出版社");--給變量id賦值Set@num=(SelectCount(*)From圖書信息Where出版社=@id);Select@name,@id,@num;語句“Select@name,@id,@num;”的輸出結(jié)果如圖所示。語句“Select@name,@id,@num;”的輸出結(jié)果8.2使用存儲過程和游標(biāo)獲取與處理MySQL表數(shù)據(jù)在MySQL中，存儲過程是一系列為了完成特定功能而編寫的SQL語句組成的程序，經(jīng)過編譯后保存在數(shù)據(jù)庫中。存儲過程要比普通SQL語句的執(zhí)行效率更高，且可以被多次重復(fù)調(diào)用。存儲過程還可以接收輸入、輸出參數(shù)，并可以返回一個或多個查詢結(jié)果集和返回值，以便滿足各種不同需求。8.2.1MySQL的存儲過程1．存儲過程概念存儲過程（StoredProcedure）是一組為了完成特定功能的SQL語句集合。用戶通過存儲過程可以將經(jīng)常使用的SQL語句封裝起來，這樣可以避免重復(fù)編寫相同的SQL語句。存儲過程可以由聲明式SQL語句（如Create、Update、Select等）和過程式SQL語句（如If…Then…Else語句）組成。另外，存儲過程一般是經(jīng)過編譯后存儲在數(shù)據(jù)庫中的，所以執(zhí)行存儲過程要比執(zhí)行存儲過程中封裝的SQL語句效率更高。存儲過程還可以接收輸入?yún)?shù)、輸出參數(shù)等，可以返回單個或多個結(jié)果集。存儲過程可以由程序、觸發(fā)器或者另一個存儲過程來調(diào)用，從而激活它，實(shí)現(xiàn)代碼段中SQL語句的功能。存儲過程主要有以下優(yōu)點(diǎn)。（1）執(zhí)行速度快：存儲過程比普通SQL語句功能更強(qiáng)大，而且能夠?qū)崿F(xiàn)功能性編程。存儲過程執(zhí)行成功后會被存儲在數(shù)據(jù)庫服務(wù)器中，并允許客戶端直接調(diào)用，而且存儲過程可以提高SQL語句的執(zhí)行效率。（2）封裝復(fù)雜的操作：存儲過程中允許包含一條或多條SQL語句，并利用這些SQL語句實(shí)現(xiàn)一個或者多個邏輯功能。對于調(diào)用者來說，存儲過程封裝了SQL語句，調(diào)用者無須考慮邏輯功能的具體實(shí)現(xiàn)過程，直接調(diào)用即可。（3）很強(qiáng)的靈活性：存儲過程可以用流程控制語句編寫，可以完成較復(fù)雜的判斷和運(yùn)算。（4）使數(shù)據(jù)獨(dú)立：程序可以調(diào)用存儲過程來替代執(zhí)行多條SQL語句。這種情況下，存儲過程把數(shù)據(jù)同用戶隔離開來，其優(yōu)點(diǎn)是當(dāng)數(shù)據(jù)表的結(jié)構(gòu)發(fā)生改變時，調(diào)用者不用修改程序，只需要重新編寫存儲過程即可。（5）可以提高安全性：存儲過程可被作為一種安全機(jī)制來充分利用，系統(tǒng)管理員通過限制存儲過程的訪問權(quán)限，可以實(shí)現(xiàn)相應(yīng)數(shù)據(jù)的訪問權(quán)限限制，避免了非授權(quán)用戶對數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全性。（6）可以提高性能：復(fù)雜的功能往往需要多條SQL語句，并且客戶端需要多次連接并發(fā)送SQL語句到服務(wù)器才能實(shí)現(xiàn)。如果利用存儲過程，則可以將這些SQL語句放入存儲過程中，存儲過程被成功編譯后就存儲在數(shù)據(jù)庫服務(wù)器中，以后客戶端可以直接調(diào)用，這樣所有的SQL語句將在服務(wù)器中執(zhí)行。（7）可以減少網(wǎng)絡(luò)流量：針對同一個數(shù)據(jù)庫對象的操作，如果這一操作所涉及的SQL語句被組織成存儲過程，那么當(dāng)在客戶端上調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只是對應(yīng)的調(diào)用語句，大大降低了網(wǎng)絡(luò)負(fù)載。2．Delimiter命令Delimiter命令用于更改MySQL語句的結(jié)束符，例如將默認(rèn)結(jié)束符“;”更改為“$$”，以避免與SQL語句默認(rèn)結(jié)束符相沖突。其語法格式如下：Delimiter<自定義的結(jié)束符>例如：Delimiter$$用戶自定義的結(jié)束符可以是一些特殊的符號，例如“$$”“##”“//”等，但應(yīng)避免使用反斜杠“\”字符，因?yàn)椤癨”是MySQL的轉(zhuǎn)義字符?；謴?fù)使用MySQL的默認(rèn)結(jié)束符“;”的命令如下：Delimiter;3．創(chuàng)建存儲過程創(chuàng)建存儲過程的語法格式如下：CreateProcedure<存儲過程名>([<參數(shù)列表>])[<存儲過程的特征設(shè)置>]<存儲過程體>存儲過程體中可以使用所有類型的SQL語句，包括DDL、DCL和DML語句。當(dāng)然，過程式語句也是被允許的，包括變量的定義和賦值語句。4．查看存儲過程查看存儲過程狀態(tài)的語法格式如下：ShowProcedureStatus[Like<存儲過程名的模式字符>];例如：ShowProcedureStatusLike"proc%";其中，“%”為通配字符，“"proc%"”表示所有名稱以proc開頭的存儲過程。查看存儲過程定義的語法格式如下：ShowCreateProcedure<存儲過程名>;例如：ShowCreateProcedureproc0501;MySQL中存儲過程的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中，可以通過查詢該數(shù)據(jù)表的記錄來查詢存儲過程的信息，例如從Routines表中查詢名稱為“proc0501”的存儲過程的信息的語句如下：Select*Frominformation_schema.RoutinesWhereRoutine_name="proc0501";其中，Routine_name字段中存儲的是存儲過程的名稱。由于Routines數(shù)據(jù)表也存儲了函數(shù)的信息，如果存儲過程和自定義函數(shù)名稱相同，則需要同時指定Routine_Type字段表明查詢的是存儲過程（值為Procedure）還是函數(shù)（值為Function）。5．調(diào)用存儲過程存儲過程創(chuàng)建完成后，可以在程序、觸發(fā)器或者其他存儲過程中被調(diào)用。其語法格式如下：Call<存儲過程名>([<參數(shù)列表>]);如果需要調(diào)用某個特定數(shù)據(jù)庫的存儲過程，則需要在存儲過程名前面加上對應(yīng)數(shù)據(jù)庫的名稱。如果定義存儲過程時使用了參數(shù)，那么調(diào)用存儲過程時也要使用參數(shù)，并且參數(shù)的個數(shù)和順序要與創(chuàng)建存儲過程時的對應(yīng)。6．修改存儲過程可以使用AlterProcedure語句修改存儲過程的某些特征，其語法格式如下：AlterProcedure<存儲過程名>[<存儲過程的特征設(shè)置>];存儲過程的特征設(shè)置與創(chuàng)建存儲過程時類似，這里不贅述。修改存儲過程時，MySQL會覆蓋以前定義的存儲過程。例如，修改存儲過程proc0501的定義，將其讀寫權(quán)限修改為ModifiesSQLData，并指定調(diào)用者有執(zhí)行權(quán)限的語句如下：AlterProcedureproc0501ModifiesSQLDataSQLSecurityInvoker;7．刪除存儲過程在【命令提示符】窗口中刪除存儲過程的語法格式如下：DropProcedure[IfExists]<存儲過程名>;其中，IfExist子句可以防止在存儲過程不存在時出現(xiàn)警告信息。8.2.2MySQL的游標(biāo)為了方便用戶對結(jié)果集中單條記錄進(jìn)行訪問，MySQL提供了一種特殊的訪問機(jī)制：游標(biāo)。游標(biāo)主要包括游標(biāo)結(jié)果集和游標(biāo)位置兩部分。游標(biāo)結(jié)果集是指由定義游標(biāo)的Select語句所返回的記錄集合。游標(biāo)相當(dāng)于指向這個結(jié)果集中某一行的指針。查詢語句可能查詢出多條記錄，在存儲過程和函數(shù)中可以使用游標(biāo)來逐條讀取查詢結(jié)果集中的記錄。游標(biāo)的使用包括聲明游標(biāo)、打開游標(biāo)、使用游標(biāo)和關(guān)閉游標(biāo)。游標(biāo)一定要在存儲過程或函數(shù)中使用，不能單獨(dú)在查詢中使用。1．聲明游標(biāo)在MySQL中，聲明游標(biāo)的語法格式如下：Declare<游標(biāo)名>CursorFor<Select語句>。2．打開游標(biāo)聲明游標(biāo)后，要使用游標(biāo)從游標(biāo)結(jié)果集中提取數(shù)據(jù)，就必須先打開游標(biāo)。在MySQL中，可以使用Open語句打開游標(biāo)，其語法格式如下：Open<游標(biāo)名>;在程序中，一個游標(biāo)可以打開多次。由于其他的用戶或程序本身已經(jīng)更新了數(shù)據(jù)表，所以每次打開的結(jié)果可能不同。3．讀取游標(biāo)游標(biāo)打開后，可以使用Fetch…Into語句從中讀取數(shù)據(jù)，其語法格式如下：Fetch<游標(biāo)名>Into<變量名稱1>[,<變量名稱2>,…];Fetch語句將游標(biāo)指向的一行記錄的一個或多個數(shù)據(jù)賦給一個或多個變量，子句中變量的數(shù)目必須等于聲明游標(biāo)時Select子句中字段的數(shù)目。變量名稱必須在聲明游標(biāo)之前就定義完成。4．關(guān)閉游標(biāo)游標(biāo)使用完以后要及時關(guān)閉，相關(guān)語句的語法格式如下：Close<游標(biāo)名>;【任務(wù)8-2】在【命令提示符】窗口中創(chuàng)建存儲過程查看指定出版社出版的圖書種類【任務(wù)描述】在【命令提示符】窗口中創(chuàng)建存儲過程proc0501，其功能是查看“圖書信息”數(shù)據(jù)表中“人民郵電出版社”出版的圖書種類?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建存儲過程proc0501登錄MySQL數(shù)據(jù)庫服務(wù)器，然后在命令提示符后輸入以下語句：Delimiter$$UseMallDB;CreateProcedureproc0501()BeginDeclarenamevarchar(16);Declareidint;Declarenumint;Setname="人民郵電出版社";--給變量name賦值Setid=(Select出版社IDFrom出版社信息Where出版社名稱=name);SelectCount(*)IntonumFrom圖書信息Where出版社=id;Selectname,id,num;End$$Delimiter;存儲過程創(chuàng)建成功后會顯示如下提示信息：QueryOK,0rowsaffected(0.00sec)2．在【命令提示符】窗口中查看存儲過程在命令提示符后輸入以下語句查看存儲過程proc0501：ShowProcedureStatusLike"proc0501";運(yùn)行結(jié)果的前7列如圖所示。查看存儲過程proc0501的運(yùn)行結(jié)果的前7列3．在【命令提示符】窗口中調(diào)用存儲過程proc0501在命令提示符后輸入以下語句調(diào)用存儲過程proc0501：Callproc0501;調(diào)用存儲過程proc0501的結(jié)果如圖所示。調(diào)用存儲過程proc0501的結(jié)果【任務(wù)8-3】在【命令提示符】窗口中創(chuàng)建有輸入?yún)?shù)的存儲過程【任務(wù)描述】【任務(wù)8-2】出版社名稱存儲在局部變量name中，該存儲過程只能查詢一家出版社所出版的圖書種類。如果需要查詢不同出版社所出版的圖書種類，可以將出版社名稱作為存儲過程的輸入?yún)?shù)，通過輸入?yún)?shù)傳入不同的出版社名稱，從而查詢不同出版社的圖書種類。在【命令提示符】窗口中創(chuàng)建包含輸入?yún)?shù)的存儲過程proc0502，其功能是根據(jù)輸入?yún)?shù)strName的值（存儲“出版社名稱”）查看“圖書信息”數(shù)據(jù)表中對應(yīng)出版社出版的圖書種類?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建存儲過程proc0502在命令提示符后輸入以下語句：Delimiter$$CreateProcedureproc0502(InstrNamevarchar(16))BeginDeclareidint;Declarenumint;If(strNameIsNotNull)ThenSetid=(Select出版社IDFrom出版社信息Where出版社名稱=strName);SelectCount(*)IntonumFrom圖書信息Where出版社=id;EndIf;SelectstrName,id,num;End$$Delimiter;SQL語句輸入過程及結(jié)果如圖所示。存儲過程proc0502中SQL語句的輸入過程及結(jié)果2．在【命令提示符】窗口中調(diào)用存儲過程proc0502在命令提示符后輸入以下語句調(diào)用存儲過程proc0502：Callproc0502("人民郵電出版社");調(diào)用存儲過程proc0502的結(jié)果如圖所示。調(diào)用存儲過程proc0502的結(jié)果【任務(wù)8-4】使用Navicat圖形管理工具創(chuàng)建有輸入?yún)?shù)的存儲過程【任務(wù)描述】使用Navicat圖形管理工具創(chuàng)建包含輸入?yún)?shù)的存儲過程proc0503，其功能是根據(jù)輸入?yún)?shù)strName的值（存儲“出版社名稱”）查看“圖書信息”數(shù)據(jù)表中對應(yīng)出版社出版的圖書種類?！救蝿?wù)實(shí)施】1．查看數(shù)據(jù)庫“MallDB”中已有的存儲過程啟動NavicatforMySQL，在窗口左側(cè)雙擊打開連接“MallConn”，雙擊打開數(shù)據(jù)庫“MallDB”，示。查看數(shù)據(jù)庫“MallDB”中已有的存儲過程2．新建存儲過程在【對象】選項(xiàng)卡的工具欄中單擊【新建函數(shù)】按鈕，打開【函數(shù)向?qū)А繉υ捒虻牡谝粋€界面“請選擇你要創(chuàng)建的例程類型”，在“名：”文本框中輸入存儲過程名稱“proc0503”，在該界面中單擊【過程】單選按鈕，如圖所示?！竞瘮?shù)向?qū)А繉υ捒虻牡谝粋€界面“請選擇你要創(chuàng)建的例程類型”單擊【下一步】按鈕，然后進(jìn)入【函數(shù)向?qū)А繉υ捒虻摹罢堓斎脒@個例程的參數(shù)”界面，在“模式”文本框單擊按鈕，在彈出的下拉列表中選擇模式類型為“IN”，如圖所示。模式類型下拉列表在“名”文本框中輸入“strName”，在“類型”文本框中輸入“varchar(16)”，設(shè)置存儲過程參數(shù)，如圖所示。設(shè)置存儲過程參數(shù)然后單擊【完成】按鈕，彈出存儲過程的定義窗口，其初始狀態(tài)如圖所示。存儲過程的定義窗口的初始狀態(tài)在存儲過程的定義窗口中輸入如下SQL語句：BeginDeclareidint;Declarenumint;If(strNameIsNotNull)ThenSetid=(Select出版社IDFrom出版社信息Where出版社名稱=strName);SelectCount(*)IntonumFrom圖書信息Where出版社=id;EndIf;SelectstrName,id,num;EndSQL語句編輯完成后，單擊工具欄中的【保存】按鈕，按前面步驟指定的存儲過程名稱“proc0503”進(jìn)行保存，存儲過程保存完成后，完整的存儲過程定義如圖所示。完整的存儲過程定義3．運(yùn)行存儲過程在工具欄中單擊【運(yùn)行】按鈕，彈出【輸入?yún)?shù)】對話框，在該對話框的參數(shù)輸入文本框中輸入“人民郵電出版社”，如圖所示?！据斎?yún)?shù)】對話框在【輸入?yún)?shù)】對話框中單擊【確定】按鈕，打開【過程】窗口，并顯示運(yùn)行結(jié)果如圖所示。存儲過程proc0503的運(yùn)行結(jié)果【任務(wù)8-5】使用Navicat圖形管理工具創(chuàng)建有輸入?yún)?shù)和輸出參數(shù)的存儲過程【任務(wù)描述】使用Navicat圖形管理工具創(chuàng)建包含輸入?yún)?shù)和輸出參數(shù)的存儲過程proc0504，其功能是根據(jù)輸入?yún)?shù)strName的值（存儲“出版社名稱”）查看“圖書信息”數(shù)據(jù)表中對應(yīng)出版社出版的圖書種類，并將圖書種類存儲在輸出參數(shù)intNum中?！救蝿?wù)實(shí)施】1．在NavicatforMySQL中新建存儲過程proc0504在【NavicatforMySQL】窗口的【對象】選項(xiàng)卡工具欄中單擊【新建函數(shù)】按鈕，打開【函數(shù)向?qū)А繉υ捒虻牡谝粋€界面“請選擇你要創(chuàng)建的例程類型”，在“名：”文本框中輸入過程名稱“proc0504”，在該界面中單擊【過程】單選按鈕。單擊【下一步】按鈕進(jìn)入【函數(shù)向?qū)А繉υ捒虻摹罢堓斎脒@個例程的參數(shù)”界面，在“模式”文本框單擊按鈕，在彈出的下拉列表中選擇模式類型為“IN”。在“名”文本框中輸入“strName”，在“類型”文本框中輸入“varchar(16)”。單擊左下角的【添加】按鈕，添加一個參數(shù)行，在“模式”文本框選擇或輸入模式類型為“OUT”，在“名”文本框中輸入“intNum”，在“類型”文本框中輸入“int”，如圖所示。在“請輸入這個例程的參數(shù)”界面中設(shè)置兩個參數(shù)然后單擊【完成】按鈕，彈出存儲過程的定義窗口。在存儲過程的定義窗口中輸入如下SQL語句：BeginDeclareidint;If(strNameIsNotNull)ThenSetid=(Select出版社IDFrom出版社信息Where出版社名稱=strName);SelectCount(*)IntointNumFrom圖書信息Where出版社=id;EndIf;SelectstrNameas出版社名稱,idas出版社ID,intNumas圖書種數(shù);EndSQL語句編輯完成后，單擊工具欄中的【保存】按鈕，按前面步驟指定的存儲過程名稱“proc0504”進(jìn)行保存。2．在NavicatforMySQL中運(yùn)行存儲過程proc0504在工具欄中單擊【運(yùn)行】按鈕，彈出【輸入?yún)?shù)】對話框，在該對話框中的參數(shù)輸入文本框中輸入?yún)?shù)的值“人民郵電出版社”。在【輸入?yún)?shù)】對話框中單擊【確定】按鈕，打開【結(jié)果1】選項(xiàng)卡，并顯示運(yùn)行結(jié)果1如圖所示。存儲過程proc0504的運(yùn)行結(jié)果1切換到【結(jié)果2】選項(xiàng)卡，顯示輸出變量intNum的值如圖所示?！窘Y(jié)果2】選項(xiàng)卡顯示輸出變量intNum的值3．在【命令提示符】窗口中調(diào)用存儲過程proc0504在命令提示符后輸入以下語句調(diào)用存儲過程proc0504：Callproc0504('人民郵電出版社',@number);調(diào)用存儲過程proc0504的結(jié)果如圖所示。在【命令提示符】窗口中調(diào)用存儲過程proc0504的結(jié)果使用“Select@number;”語句查看用戶變量number的結(jié)果如圖所示。查看用戶變量number的結(jié)果【任務(wù)8-6】使用Navicat圖形管理工具創(chuàng)建有InOut參數(shù)的存儲過程【任務(wù)描述】使用Navicat圖形管理工具創(chuàng)建有InOut參數(shù)的存儲過程proc0505，其功能是根據(jù)參數(shù)strName的值（存儲“出版社名稱”）查看“圖書信息”數(shù)據(jù)表中對應(yīng)出版社出版的價格最高的圖書名稱，并將圖書名稱存儲在參數(shù)strName中?！救蝿?wù)實(shí)施】1．在NavicatforMySQL中新建存儲過程proc0505在【NavicatforMySQL】窗口的【對象】選項(xiàng)卡工具欄中單擊【新建函數(shù)】按鈕，打開【函數(shù)向?qū)А繉υ捒虻牡谝粋€界面“請選擇你要創(chuàng)建的例程類型”，如圖8-11所示，在“名：”文本框中輸入過程名稱“proc0505”，在該界面中單擊【過程】單選按鈕。單擊【下一步】按鈕進(jìn)入【函數(shù)向?qū)А繉υ捒虻摹罢堓斎脒@個例程的參數(shù)”界面，在“模式”文本框單擊按鈕，在彈出的下拉列表中選擇模式類型為“INOUT”，在“名”文本框中輸入“strName”，在“類型”文本框中輸入“varchar(16)”。然后單擊【完成】按鈕，彈出存儲過程的定義窗口。在存儲過程的定義窗口中輸入如下SQL語句：BeginDeclareidint;DeclaremaxPricedecimal(8,2);If(strNameIsNotNull)ThenSetid=(Select出版社IDFrom出版社信息Where出版社名稱=strName);SelectMax(價格)IntomaxPriceFrom圖書信息Where出版社=id;Select圖書名稱IntostrNameFrom圖書信息Where價格=maxPriceAnd出版社=id;EndIf;SelectstrNameas圖書名稱,idas出版社ID,maxPriceas價格;EndSQL語句編輯完成后，單擊工具欄中的【保存】按鈕，按前面步驟指定的存儲過程名稱“proc0505”進(jìn)行保存。2．在【命令提示符】窗口中調(diào)用存儲過程proc0505在命令提示符后輸入以下語句調(diào)用存儲過程proc0505：Delimiter##Set@name="人民郵電出版社";Callproc0505(@name);##調(diào)用存儲過程proc0505的結(jié)果如圖所示。在【命令提示符】窗口中調(diào)用存儲過程proc0505的結(jié)果使用“Select@name;”語句查看用戶變量name的結(jié)果如圖所示。查看用戶變量name的結(jié)果3．在NavicatforMySQL中調(diào)用存儲過程proc0505創(chuàng)建另一個存儲過程proc050501，在該存儲過程中調(diào)用存儲過程proc0505，代碼如下：BeginSet@name="人民郵電出版社";Callproc0505(@name);End運(yùn)行存儲過程proc050501的結(jié)果如圖所示。運(yùn)行存儲過程proc050501的結(jié)果【任務(wù)8-7】在【命令提示符】窗口中創(chuàng)建應(yīng)用游標(biāo)的存儲過程【任務(wù)描述】在【命令提示符】窗口中創(chuàng)建應(yīng)用游標(biāo)的存儲過程proc0506，其功能是逐行瀏覽“圖書信息”數(shù)據(jù)表的前5條記錄?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建存儲過程proc0506在命令提示符后輸入以下語句：Delimiter$$CreateProcedureproc0506()BeginDeclarestrNamevarchar(100);Declarepricedecimal(8,2);Declarenumberint;DeclarecursorNumCursorForSelect圖書名稱,價格From圖書信息;Setnumber=1;OpencursorNum;Whilenumber<6DoFetchcursorNumIntostrName,price;SelectstrName,price;setnumber=number+1;EndWhile;ClosecursorNum;End$$Delimiter;SQL語句輸入過程及結(jié)果如圖所示。存儲過程proc0506中SQL語句的輸入過程及結(jié)果2．在【命令提示符】窗口中調(diào)用存儲過程proc0506在命令提示符后輸入以下語句調(diào)用存儲過程proc0506：Callproc0506();調(diào)用存儲過程proc0506的運(yùn)行結(jié)果的前兩條數(shù)據(jù)如圖所示。調(diào)用存儲過程proc0506的運(yùn)行結(jié)果的前兩條數(shù)據(jù)8.3使用函數(shù)獲取與處理MySQL表數(shù)據(jù)8.3.1MySQL系統(tǒng)定義的內(nèi)置函數(shù)MySQL包含了100多個內(nèi)置函數(shù)，包括字符串函數(shù)、數(shù)學(xué)函數(shù)等，MySQL系統(tǒng)定義的內(nèi)置函數(shù)如表所示。這些函數(shù)的功能和用法請參考MySQL的幫助系統(tǒng)，這里不做具體介紹。MySQL系統(tǒng)定義的內(nèi)置函數(shù)函數(shù)類型函數(shù)名稱字符串函數(shù)Ascii()、Char()、Left()、Right()、Trim()、Ltrim()、Ttrim()、Rpad()、Lpad()、Replace()、Concat()、Substring()、Strcmp()、Char_Length()、Length()、Insert()數(shù)學(xué)函數(shù)Greatest()、Least()、Floor()、Geiling()、Round()、Truncate()、Abs()、Sign()、PI()、Sqrt()、Pow()、Sin()、Cos()、Tan()、Asin()、Acos()、Atan()、Bin()、Otc()、Hex()日期和時間函數(shù)Now()、Curtime()、Curdate()、Year()、Month()、Monthname()、Dayofyear()、Dayofweek()、Dayofmonth()、Dayname()、Week()、Yearweek()、Hour()、Minute()、Second()、Date_add()、Date_sub()、DateDiff()

系統(tǒng)信息函數(shù)Database()、Benchmark()、Charset()、Connection_ID()、Found_rows()、Get_lock()、Is_free_lock、Last_Insert()、Master_pos_wait()、Release_lock()、User()、System_user()、Version()類型轉(zhuǎn)換函數(shù)Cast()格式化函數(shù)Format()、Date_format()、Time_format()、Inet_ntoa()、Inet_aton()控制流函數(shù)Ifnull()、Nullif()、If()加密函數(shù)Aes_encrypt()、Aes_decrypt()、Encode()、Decode()、Encrypt()、Password()8.3.2MySQL的自定義函數(shù)為了滿足用戶特殊情況下的需要，MySQL允許用戶自定義函數(shù)、補(bǔ)充和擴(kuò)展系統(tǒng)定義的內(nèi)置函數(shù)。用戶自定義函數(shù)可以實(shí)現(xiàn)模塊化程序設(shè)計，并且執(zhí)行速度更快。1．自定義函數(shù)概述MySQL的自定義函數(shù)與存儲過程相似，都是由SQL語句和過程式語句組成的代碼片段，并且可以在應(yīng)用程序中被調(diào)用。但是，它們也有一些區(qū)別。（1）自定義函數(shù)不能擁有輸出參數(shù)，因?yàn)楹瘮?shù)本身就有返回值。（2）不能使用Call語句調(diào)用函數(shù)。（3）函數(shù)必須包含一條Return語句，而存儲過程不允許使用該語句。2．自定義函數(shù)的定義定義自定義函數(shù)的語法格式如下：CreateFunction<函數(shù)名稱>([<輸入?yún)?shù)名><參數(shù)類型>[,…])Returns<函數(shù)返回值類型>[<函數(shù)的特征設(shè)置>]<函數(shù)體>3．查看自定義函數(shù)查看自定義函數(shù)狀態(tài)的語法格式如下：ShowFunctionStatus[Like<函數(shù)名的模式字符>];例如：ShowProcedureStatusLike"func%";其中，“%”為通配字符，“"func%"”表示所有名稱以func開頭的函數(shù)。查看函數(shù)定義的語法格式如下：ShowCreateFunction<函數(shù)名稱>;例如：ShowCreateFunctionfunc0501;4．修改自定義函數(shù)修改自定義函數(shù)的語法格式如下：AlterFunction<自定義函數(shù)名稱>[<函數(shù)的特征設(shè)置>];例如修改自定義函數(shù)func0501的定義，將讀寫權(quán)限修改為“ReadsSQLData”的語句如下：AlterFunctionfunc0501ReadsSQLData;如果要修改自定義函數(shù)的函數(shù)體內(nèi)容，可以采用先刪除后重新定義的方法。5．刪除自定義函數(shù)刪除自定義函數(shù)的語法格式如下：DropFunction[ifexists]<自定義函數(shù)名稱>;例如刪除自定義函數(shù)func0501的語句如下：DropFunctionfunc0501;8.3.3調(diào)用MySQL的函數(shù)在MySQL中，調(diào)用MySQL系統(tǒng)定義的內(nèi)置函數(shù)與調(diào)用自定義函數(shù)的語法格式如下：Select函數(shù)名稱([實(shí)參]);【任務(wù)8-8】在【命令提示符】窗口中創(chuàng)建自定義函數(shù)getTypeName()【任務(wù)描述】在【命令提示符】窗口中創(chuàng)建一個自定義函數(shù)getTypeName()，該函數(shù)的功能是從“商品類型”數(shù)據(jù)表中根據(jù)指定的“類型編號”獲取“類型名稱”?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建自定義函數(shù)getTypeName()在命令提示符后輸入以下語句：Delimiter$$CreateFunctiongetTypeName(strTypeNumberVarchar(9))ReturnsVarchar(10)DeterministicBeginDeclarestrTypeNameVarchar(10);If(strTypeNumberIsNotNull)ThenSelect類型名稱IntostrTypeNameFrom商品類型Where類型編號=strTypeNumber;EndIf;ReturnstrTypeName;End$$Delimiter;SQL語句輸入過程及結(jié)果如圖所示。自定義函數(shù)getTypeName()中SQL語句的輸入過程及結(jié)果解決辦法有兩種。第一種方法是在創(chuàng)建存儲過程、函數(shù)、觸發(fā)器時，聲明為Deterministic、NoSQL或ReadsSQLData中的一個，例如：DeterministicBegin#Routinebodygoeshere…End;第二種方法是信任存儲過程、函數(shù)、觸發(fā)器的創(chuàng)建者，禁止創(chuàng)建、修改子程序時對Super權(quán)限的要求，設(shè)置log_bin_trust_routine_creators全局系統(tǒng)變量的值為1。設(shè)置方法有以下3種。（1）在客戶端【命令提示符】窗口中執(zhí)行語句“SetGloballog_bin_trust_function_creators=1;”。（2）啟動MySQL時，加上“--log-bin-trust-function-creators”選項(xiàng)，將參數(shù)設(shè)置為1。（3）在MySQL配置文件“my.ini”或“f”中的“[mysqld]”段上加上“l(fā)og-bin-trust-function-creators=1”。2．在【命令提示符】窗口中調(diào)用自定義函數(shù)getTypeName()在命令提示符后輸入以下語句調(diào)用自定義函數(shù)getTypeName()：SelectgetTypeName("t1301");調(diào)用自定義函數(shù)getTypeName()的結(jié)果如圖所示。調(diào)用自定義函數(shù)getTypeName()的結(jié)果使用Navicat圖形管理工具創(chuàng)建帶參數(shù)的函數(shù)

getBookNumber()【任務(wù)描述】創(chuàng)建一個自定義函數(shù)getBookNumber()，該函數(shù)的功能是從“圖書信息”數(shù)據(jù)表中根據(jù)指定的“出版社名稱”獲取對應(yīng)的圖書種數(shù)?！救蝿?wù)實(shí)施】1．新建自定義函數(shù)在【對象】選項(xiàng)卡的工具欄中單擊【新建函數(shù)】按鈕，打開【函數(shù)向?qū)А繉υ捒虻牡谝粋€界面“請選擇你要創(chuàng)建的例程類型”，在“名：”文本框中輸入函數(shù)名稱“getBookNumber”，在該界面中單擊【函數(shù)】單選按鈕，如圖所示。輸入函數(shù)名稱與單擊【函數(shù)】單選按鈕單擊【下一步】按鈕進(jìn)入【函數(shù)向?qū)А繉υ捒虻摹罢堓斎脒@個例程的參數(shù)”界面，在“名”文本框中輸入“strBookName”，在“類型”輸入框中輸入“varchar(100)”，設(shè)置函數(shù)參數(shù)如圖所示。設(shè)置函數(shù)參數(shù)然后單擊【下一步】按鈕，進(jìn)入“請選擇這個返回類型的屬性”界面，在“返回類型”列表框中選擇“int”，如圖所示。在“請選擇這個返回類型的屬性”界面中選擇返回類型然后單擊【完成】按鈕，彈出函數(shù)定義窗口，其初始狀態(tài)如圖所示。函數(shù)定義窗口的初始狀態(tài)在函數(shù)定義窗口中輸入如下SQL語句：DeterministicBeginDeclarenumberint;SelectCount(*)IntonumberFrom圖書信息InnerJoin出版社信息On圖書信息.出版社=出版社信息.出版社IDAnd出版社信息.出版社名稱=strBookName;Returnnumber;EndSQL語句編輯完成后，單擊工具欄中的【保存】按鈕，按前面步驟指定的函數(shù)名稱“getBookNumber”進(jìn)行保存。2．調(diào)用自定義函數(shù)在工具欄中單擊【運(yùn)行】按鈕，彈出【輸入?yún)?shù)】對話框，在該對話框的文本框中輸入“人民郵電出版社”，如圖所示。【輸入?yún)?shù)】對話框在【輸入?yún)?shù)】對話框中單擊【確定】按鈕，并顯示函數(shù)的調(diào)用結(jié)果如圖所示。函數(shù)getBookNumber()的調(diào)用結(jié)果8.4使用觸發(fā)器獲取與處理MySQL表數(shù)據(jù)為了保證數(shù)據(jù)的完整性和強(qiáng)制使用業(yè)務(wù)規(guī)則，MySQL除了提供約束之外，還提供了另外一種機(jī)制：觸發(fā)器（Trigger）。當(dāng)對數(shù)據(jù)表執(zhí)行插入、刪除或更新操作時，觸發(fā)器會自動執(zhí)行以檢查數(shù)據(jù)表的完整性和約束性。1．觸發(fā)器概述觸發(fā)器是一種特殊的存儲過程，它與數(shù)據(jù)表緊密相連，可以看作數(shù)據(jù)表定義的一部分，用于數(shù)據(jù)表實(shí)施完整性約束。觸發(fā)器建立在觸發(fā)事件上，例如對數(shù)據(jù)表執(zhí)行Insert、Update或者Delete等操作時，MySQL會自動執(zhí)行建立在這些操作上的觸發(fā)器。觸發(fā)器中包含了一系列用于定義業(yè)務(wù)規(guī)則的SQL語句，用來強(qiáng)制用戶實(shí)現(xiàn)這些規(guī)則，從而確保數(shù)據(jù)的完整性。存儲過程可以使用Call命令調(diào)用，但觸發(fā)器和存儲過程不一樣，觸發(fā)器只能由數(shù)據(jù)庫的特定事件來觸發(fā)，并且不能接收參數(shù)。當(dāng)滿足觸發(fā)器的觸發(fā)條件時，數(shù)據(jù)庫系統(tǒng)就會執(zhí)行觸發(fā)器中定義的程序語句。2．創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語法格式如下：CreateTrigger<觸發(fā)器名>Before|After<觸發(fā)事件>On<數(shù)據(jù)表名稱>ForEachRow<執(zhí)行語句>;在MySQL觸發(fā)器中的SQL語句可以關(guān)聯(lián)數(shù)據(jù)表中的任意字段，但不能直接使用字段名稱，這樣做系統(tǒng)會無法識別，因?yàn)榧せ钣|發(fā)器的語句可能已經(jīng)修改、刪除或添加了新字段名，而字段的原名稱還同時存在。因此必須使用“New.<字段名稱>”或“Old.<字段名稱>”標(biāo)識字段，“New.<字段名稱>”用來引用新記錄的一個字段，“Old.<字段名稱>”用來引用更新或刪除該字段之前原有的字段。對于Insert語句，只有New才可以使用；對于Delete語句，只有Old才可以使用；對Update語句New和Old都可以使用。3．查看觸發(fā)器查看觸發(fā)器是指查看數(shù)據(jù)庫中已存在的觸發(fā)器的定義、狀態(tài)和語法信息等，可以使用以下兩種語句來查看已經(jīng)創(chuàng)建的觸發(fā)器。（1）使用ShowTriggers查看觸發(fā)器。（2）使用Select語句查看Triggers數(shù)據(jù)表中的觸發(fā)器信息，其語法格式如下：Select*FromInformation_Schema.TriggersWhereTrigger_Name=<觸發(fā)器名>;4．刪除觸發(fā)器刪除觸發(fā)器的語法格式如下：DropTrigger[<數(shù)據(jù)庫名>.]<觸發(fā)器名>如果省略了數(shù)據(jù)庫名，則表示在當(dāng)前數(shù)據(jù)庫中刪除指定的觸發(fā)器?！救蝿?wù)8-10】創(chuàng)建Insert觸發(fā)器【任務(wù)描述】創(chuàng)建一個名為“order_insert”的觸發(fā)器，當(dāng)向“訂單信息”數(shù)據(jù)表中插入一條訂單記錄時，將用戶變量strInfo的值設(shè)置為“在訂單信息表中成功插入一條記錄”?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建觸發(fā)器order_insert在命令提示符后輸入以下語句：Delimiter$$CreateTriggerorder_insertAfterInsertOn訂單信息ForEachRowBeginSet@strInfo="在訂單信息表中成功插入一條記錄";End$$Delimiter;SQL語句輸入過程及結(jié)果如圖所示。觸發(fā)器order_insert中SQL語句的輸入過程及結(jié)果2．在Triggers數(shù)據(jù)表中查看觸發(fā)器信息在命令提示符后輸入以下Select語句查看觸發(fā)器信息：SelectTrigger_Name,Event_Manipulation,Event_Object_Schema,Event_Object_TableFromInformation_Schema.TriggersWhereTrigger_Name="order_insert";使用Select語句查看觸發(fā)器信息的結(jié)果如圖所示。使用Select語句查看觸發(fā)器信息的結(jié)果3．應(yīng)用觸發(fā)器order_insert在命令提示符后直接輸入“Select@strInfo;”語句查看用戶變量strInfo的值，此時該變量的初始值為“0x”。接下來，向“訂單信息”數(shù)據(jù)表中插入一條記錄，測試Insert觸發(fā)器“order_insert”是否會被觸發(fā)。對應(yīng)的語句如下：InsertInto訂單信息(訂單編號,提交訂單時間,訂單完成時間,送貨方式,客戶,收貨人,付款方式,商品總額,運(yùn)費(fèi),優(yōu)惠金額,應(yīng)付總額,訂單狀態(tài))Values("132577616584","2020-10-2511:13:08","2020-10-2815:31:12","京東快遞",2,"陳芳","貨到付款",268.80,0.00,10.00,258.80,"已完成");Insert語句成功執(zhí)行后，執(zhí)行“Select@strInfo;”語句再一次查看用戶變量“@strInfo”的值，此時該變量的值如圖所示。查看用戶變量“@strInfo”的值【任務(wù)8-11】創(chuàng)建Delete觸發(fā)器【任務(wù)描述】創(chuàng)建一個名為“commodityType_delete”的觸發(fā)器，該觸發(fā)器需實(shí)現(xiàn)的功能如下：限制用戶刪除“商品類型”數(shù)據(jù)表中的記錄，當(dāng)用戶刪除該類記錄時拋出禁止刪除記錄的錯誤提示信息?！救蝿?wù)實(shí)施】1．在【命令提示符】窗口中創(chuàng)建觸發(fā)器commodityType_delete在命令提示符后輸入以下語句：Delimiter$$CreateTriggercommodityType_deleteBeforeDeleteOn商品類型ForEachRowBeginSet@strDeleteInfo="商品類型數(shù)據(jù)表中的記錄不允許刪除";DeleteFrom商品類型;End$$Delimiter;2．在NavicatforMySQL中查看觸發(fā)器在【NavicatforMySQL】窗口中打開數(shù)據(jù)表“商品類型”的【表設(shè)計器】，切換到【觸發(fā)器】選項(xiàng)卡，該數(shù)據(jù)表中已創(chuàng)建的觸發(fā)器及其定義如圖所示?！吧唐奉愋汀睌?shù)據(jù)表中已創(chuàng)建的觸發(fā)器及其定義3．應(yīng)用觸發(fā)器commodityType_delete在命令提示符后輸入刪除記錄的語句，從“商品類型”數(shù)據(jù)表中刪除一條記錄，測試Delete觸發(fā)器“commodityType_delete”是否會被觸發(fā)。刪除記錄的語句如下：DeleteFrom商品類型Where類型編號="t01";按【Enter】鍵后，可以發(fā)現(xiàn)該SQL語句并不能成功執(zhí)行，會出現(xiàn)如下提示信息：ERROR1442(HY000):Can'tupdatetable'商品類型'instoredfunction/triggerbecauseitisalreadyusedbystatementwhichinvokedthisstoredfunction/trigger.在【NavicatforMySQL】窗口中打開數(shù)據(jù)表“商品類型”的記錄編輯窗口，然后刪除一條記錄。首先會彈出下圖所示的【確認(rèn)刪除】對話框?！敬_認(rèn)刪除】對話框在該對話框中單擊【刪除一條記錄】按鈕，接著會出現(xiàn)圖所示的錯誤提示信息對話框。刪除記錄時出現(xiàn)的錯誤提示信息對話框【任務(wù)8-12】應(yīng)用觸發(fā)器同步更新多張數(shù)據(jù)表中的數(shù)據(jù)【任務(wù)描述】（1）創(chuàng)建“商品庫存”數(shù)據(jù)表和“購物車商品”數(shù)據(jù)表?！吧唐穾齑妗睌?shù)據(jù)表的結(jié)構(gòu)數(shù)據(jù)如表所示?！吧唐穾齑妗睌?shù)據(jù)表的結(jié)構(gòu)數(shù)據(jù)字段名稱數(shù)據(jù)類型字段長度是否允許包含Null商品編號varchar12否商品名稱varchar100是庫存數(shù)量int否最小庫存數(shù)量int是“購物車商品”數(shù)據(jù)表的結(jié)構(gòu)數(shù)據(jù)如表所示?！百徫镘嚿唐贰睌?shù)據(jù)表的結(jié)構(gòu)數(shù)據(jù)字段名稱數(shù)據(jù)類型字段長度是否允許包含Null客戶IDint否商品編號varchar12否購買數(shù)量smallint否優(yōu)惠價格decimal8,2是（2）向“商品庫存”數(shù)據(jù)表中添加5條商品記錄。向“商品庫存”數(shù)據(jù)表中添加的商品記錄如表所示。向“商品庫存”數(shù)據(jù)表中添加的商品記錄商品編號商品名稱庫存數(shù)量最小庫存數(shù)量100009177424華為Mate305G1005100011351676小米10Pro雙模5G1005100013232838海爾LU58J511005100013973228美的KFR-35GW/N8MJA31005100014512520格力KFR-72LW/NhAb3BG1005（3）在“MallDB”數(shù)據(jù)庫的“商品庫存”數(shù)據(jù)表中創(chuàng)建一個觸發(fā)器，當(dāng)客戶選購一件商品時，對應(yīng)的“商品庫存”數(shù)據(jù)表的“庫存數(shù)量”字段值也同步減1?！救蝿?wù)實(shí)施】1．創(chuàng)建“商品庫存”數(shù)據(jù)表在命令提示符后輸入以下語句：CreateTable商品庫存(商品編號varchar(12) NotNull,商品名稱varchar(100) Null,庫存數(shù)量int NotNull,最小庫存數(shù)量int Null);按【Enter】鍵，執(zhí)行以上語句，成功創(chuàng)建“商品庫存”數(shù)據(jù)