MySQL課件：-數(shù)據(jù)庫編程.ppt

1、數(shù)據(jù)庫編程,項(xiàng)目知識(shí)要點(diǎn)與目標(biāo),任務(wù)一 MySQL語言結(jié)構(gòu),1字符串常量 字符串是指用單引號(hào)或雙引號(hào)括起來的字符序列，分為ASCII字符串常量和Unicode 字符串常量。 ASCII字符串常量是用單引號(hào)括起來的，由ASCII字符構(gòu)成的符號(hào)串。舉例：hello，How are you! Unicode 字符串常量與ASCII字符串常量相似，但它前面有一個(gè)N標(biāo)志符（N代表 SQL-92標(biāo)準(zhǔn)中的國際語言（National Language）。N前綴必須為大寫。只能用單引號(hào)括起字符串。 舉例：Nhello，NHow are you! Unicode 數(shù)據(jù)中的每個(gè)字符用兩個(gè)字節(jié)存儲(chǔ)，而每個(gè)ASCII字

2、符用一個(gè)字節(jié)存儲(chǔ)。 在字符串中不僅可以使用普通的字符，也可使用幾個(gè)轉(zhuǎn)義序列，它們用來表示特殊的字符。 【例8.1】 執(zhí)行如下語句： SELECT ThisnIsnFournLines; 其中，“n”表示回車。,常量,2數(shù)值常量 數(shù)值常量可以分為整數(shù)常量和浮點(diǎn)數(shù)常量。 3十六進(jìn)制常量 一個(gè)十六進(jìn)制值通常指定為一個(gè)字符串常量，每對(duì)十六進(jìn)制數(shù)字被轉(zhuǎn)換為一個(gè)字符，其最前面有一個(gè)大寫字母“X”或小寫字“x”。 4日期時(shí)間常量 日期時(shí)間常量：用單引號(hào)將表示日期時(shí)間的字符串括起來構(gòu)成。日期型常量包括年、月、日，數(shù)據(jù)類型為DATE，表示為“1999-06-17”這樣的值。 時(shí)間型常量包括小時(shí)數(shù)、分鐘數(shù)、秒數(shù)及

3、微秒數(shù)，數(shù)據(jù)類型為TIME，如“12:30:43.00013”。 日期/時(shí)間的組合，數(shù)據(jù)類型為DATETIME或TIMESTAMP，如“1999-06-17 12:30:43”。,常量,5位字段值 可以使用bvalue符號(hào)寫位字段值。value是一個(gè)用0和1寫成的二進(jìn)制值。直接顯示bvalue的值可能是一系列特殊的符號(hào)。例如，b0顯示為空白，b1顯示為一個(gè)笑臉圖標(biāo)。 使用BIN函數(shù)可以將位字段常量顯示為二進(jìn)制格式。 6布爾值 布爾值只包含兩個(gè)可能的值：TRUE和FALSE。 FALSE的數(shù)字值為“0”，TRUE的數(shù)字值為“1”。 7NULL值 NULL值可適用于各種列類型，它通常用來表示“沒有

4、值”、“無數(shù)據(jù)”等意義，并且不同于數(shù)字類型的“0”或字符串類型的空字符串。,用戶變量,變量用于臨時(shí)存放數(shù)據(jù)，變量有名字及其數(shù)據(jù)類型兩個(gè)屬性，變量名用于標(biāo)識(shí)該變量，變量的數(shù)據(jù)類型確定了該變量存放值的格式及允許的運(yùn)算。MySQL中根據(jù)變量的定義方式，變量可分為用戶變量和系統(tǒng)變量。 1用戶變量 用戶可以在表達(dá)式中使用自己定義的變量，這樣的變量叫做用戶變量。在使用用戶變量前必須定義和初始化。如果使用沒有初始化的變量，它的值為NULL。 定義和初始化一個(gè)變量可以使用SET語句 語法格式為： SET user_variable1expression1 ,user_variable2= expression

5、2 , 其中，user_variable1、user_variable2為用戶變量名，變量名可以由當(dāng)前字符集的文字?jǐn)?shù)字字符、“.”、“_”和“$”組成。,變量舉例,【例8.2】 創(chuàng)建用戶變量name并賦值為“王林”。 SET name=王林; 還可以同時(shí)定義多個(gè)變量，中間用逗號(hào)隔開。 【例8.3】 創(chuàng)建用戶變量user1并賦值為1，user2賦值為2，user3賦值為3。 SET user1=1, user2=2, user3=3; 定義用戶變量時(shí)變量值可以是一個(gè)表達(dá)式。 【例8.4】 創(chuàng)建用戶變量user4，它的值為user3的值加1。 SET user4=user3+1; 在一個(gè)用戶變量被

6、創(chuàng)建后，它可以以一種特殊形式的表達(dá)式用于其他SQL語句中。變量名前面也必須加上符號(hào)。 【例8.5】 創(chuàng)建并查詢用戶變量name的值。 SET name=王林; SELECT name;,變量舉例,【例8.6】 使用查詢給變量賦值。 SET student=(SELECT 姓名 FROM XS WHERE 學(xué)號(hào)=081101); 【例8.7】 查詢表XS中名字等于例6.11中student值的學(xué)生信息。 SELECT 學(xué)號(hào), 姓名, 專業(yè)名, 出生日期 FROM XS WHERE 姓名=student; 說明：在SELECT語句中，表達(dá)式發(fā)送到客戶端后才進(jìn)行計(jì)算。這說明在HAVING、GROUP

7、 BY或ORDER BY子句中，不能使用包含SELECT列表中所設(shè)的變量的表達(dá)式。 對(duì)于SET語句，可以使用“=”或“:=”作為分配符。分配給每個(gè)變量的值可以為整數(shù)、實(shí)數(shù)、字符串或NULL值。 也可以用其他SQL語句代替SET語句來為用戶變量分配一個(gè)值。在這種情況下，分配符必須為“:=”，而不能用“=”，因?yàn)樵诜荢ET語句中“=”被視為比較操作符。 【例8.8】 執(zhí)行如下語句： SELECT t2:=(t2:=2)+5 AS t2; 結(jié)果t2的值為7。,系統(tǒng)變量,MySQL有一些特定的設(shè)置，當(dāng)MySQL數(shù)據(jù)庫服務(wù)器啟動(dòng)的時(shí)候，這些設(shè)置被讀取來決定下一步驟。例如，有些設(shè)置定義了數(shù)據(jù)如何被存儲(chǔ)，有

8、些設(shè)置則影響到處理速度，還有些與日期有關(guān)，這些設(shè)置就是系統(tǒng)變量。和用戶變量一樣，系統(tǒng)變量也是一個(gè)值和一個(gè)數(shù)據(jù)類型，但不同的是，系統(tǒng)變量在MySQL服務(wù)器啟動(dòng)時(shí)就被引入并初始化為默認(rèn)值。 【例8.9】 獲得現(xiàn)在使用的MySQL版本。 SELECT VERSION ; 說明：在MySQL中，系統(tǒng)變量VERSION的值設(shè)置為版本號(hào)。在變量名前必須加兩個(gè)符號(hào)才能正確返回該變量的值。 大多數(shù)的系統(tǒng)變量應(yīng)用于其他SQL語句中時(shí)，必須在名稱前加兩個(gè)符號(hào)，而為了與其他SQL產(chǎn)品保持一致，某些特定的系統(tǒng)變量是要省略這兩個(gè)符號(hào)的。如CURRENT_DATE（系統(tǒng)日期） 【例8.10】 獲得系統(tǒng)當(dāng)前時(shí)間。 SELE

9、CT CURRENT_TIME;,運(yùn)算符,1算術(shù)運(yùn)算符 算術(shù)運(yùn)算符在兩個(gè)表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是任何數(shù)字?jǐn)?shù)據(jù)類型。算術(shù)運(yùn)算符有：+（加）、（減）、*（乘）、/（除）和%（求模）5種運(yùn)算。 2比較運(yùn)算符 比較運(yùn)算符（又稱關(guān)系運(yùn)算符），用于比較兩個(gè)表達(dá)式的值，其運(yùn)算結(jié)果為邏輯值，可以為三種之一：1（真）、0（假）及 NULL（不能確定）。 3、邏輯運(yùn)算符 邏輯運(yùn)算符用于對(duì)某個(gè)條件進(jìn)行測(cè)試，運(yùn)算結(jié)果為TRUE（1）或FALSE（0）。 4、運(yùn)算符優(yōu)先級(jí) 當(dāng)一個(gè)復(fù)雜的表達(dá)式有多個(gè)運(yùn)算符時(shí)，運(yùn)算符優(yōu)先級(jí)決定執(zhí)行運(yùn)算的先后次序。執(zhí)行的順序會(huì)影響所得到的運(yùn)算結(jié)果。運(yùn)算符優(yōu)先級(jí)如下表所示。,表

10、達(dá)式,表達(dá)式就是常量、變量、列名、復(fù)雜計(jì)算、運(yùn)算符和函數(shù)的組合。一個(gè)表達(dá)式通常可以得到一個(gè)值。與常量和變量一樣，表達(dá)式的值也具有某種數(shù)據(jù)類型，可能的數(shù)據(jù)類型有字符類型、數(shù)值類型、日期時(shí)間類型。這樣，根據(jù)表達(dá)式的值的類型，表達(dá)式可分為字符型表達(dá)式、數(shù)值型表達(dá)式和日期表達(dá)式。 表達(dá)式按照形式還可分為單一表達(dá)式和復(fù)合表達(dá)式。 單一表達(dá)式就是一個(gè)單一的值，如一個(gè)常量或列名。 復(fù)合表達(dá)式是由運(yùn)算符將多個(gè)單一表達(dá)式連接而成的表達(dá)式 例如：1+2+3，a=b+3，2008-01-20+ INTERVAL 2 MONTH。 表達(dá)式一般用在SELECT及SELECT語句的WHERE子句中。,系統(tǒng)內(nèi)置函數(shù),1、有

11、一組數(shù)(12,34,-5.5,103.2,0),求這組數(shù)中的最大值和最小值 2、變量x=12.54,y=-10.63456,請(qǐng)用MySQL函數(shù)完成以下計(jì)算： （1）求x和y的最大整數(shù)值和最小整數(shù)值 （2）求x和y四舍五入的整數(shù)值 （3）求y分別保留小數(shù)位數(shù)為2位和保留小數(shù)位數(shù)4位的值 （4）求x的平方根和y的絕對(duì)值 3、求字符R的ASCII值 4、求ASCII碼值為91、92、93組成的一個(gè)字符串 5、設(shè)有字符串s1=ABCDEFG,s2= yxz ,請(qǐng)用MySQL函數(shù)完成以下運(yùn)算： （1） 返回s1最左邊的3個(gè)字符和最右邊的3個(gè)字符。 （2）分別刪除字符串s2首部空格、尾部空格、所有空格。

12、（3）返回字符串s1第3個(gè)字符開始的4個(gè)字符串 （4）比較s1和s2兩個(gè)字符串 6、用MySQL函數(shù)顯示當(dāng)前日期、當(dāng)前時(shí)間、當(dāng)前年、當(dāng)前月的英文、當(dāng)期星期的英文名，當(dāng)前日期減10天的日期 7、加密顯示當(dāng)前數(shù)據(jù)庫名，當(dāng)前登錄用戶名 和MySQL服務(wù)器的版本,系統(tǒng)內(nèi)置函數(shù),1、有一組數(shù)(12,34,-5.5,103.2,0),求這組數(shù)中的最大值和最小值 select greatest(12,34,-5.5,103.2,0),least(12,34,-5.5,103.2,0); 2、變量x=12.54,y=-10.63456,請(qǐng)用MySQL函數(shù)完成以下計(jì)算： （1）求x和y的最大整數(shù)值和最小整數(shù)值

13、Set x=12.54;Set y=-10.63456; Select floor(x),floor(y),ceiling(x),ceiling(y); （2）求x和y四舍五入的整數(shù)值 SELECT ROUND(x),ROUND(y); （3）求y分別保留小數(shù)位數(shù)為2位和保留小數(shù)位數(shù)4位的值 SELECT TRUNCATE(y, 2),TRUNCATE(y, 4); （4）求x的平方根和y的絕對(duì)值 SELECT SQRT(x),ABS(y); 3、求字符R的ASCII值 SELECT ASCII(R); 4、求ASCII碼值為91、92、93組成的一個(gè)字符串 SELECT CHAR(91,92

14、,93); 5、設(shè)有字符串s1=ABCDEFG,s2= yxz ,請(qǐng)用MySQL函數(shù)完成以下運(yùn)算： （1） 返回s1最左邊的3個(gè)字符和最右邊的3個(gè)字符。 SET s1=ABCDEFG;SETs2= XYZ ; SELECT LEFT(s1, 3) ,right(s1,3); （2）分別刪除字符串s2首部空格、尾部空格、所有空格。 SELECT LTRIM(s2),RTRIM(s2),TRIM(s2); （3）返回字符串s1第3個(gè)字符開始的4個(gè)字符串 select substring(s1,3,4); （4）比較s1和s2兩個(gè)字符串 SELECT STRCMP(s1, s2); 6、用MySQL

15、函數(shù)顯示當(dāng)前日期、當(dāng)前時(shí)間、當(dāng)前年、當(dāng)前月的英文、當(dāng)期星期的英文名，當(dāng)前日期減10天的日期 select now(),CURTIME(),CURDATE(),YEAR(now(),MONTHNAME(now(), DAYNAME(now(),DATE_ADD(now(), INTERVAL -10 DAY); 7、加密顯示當(dāng)前數(shù)據(jù)庫名，當(dāng)前登錄用戶名 和MySQL服務(wù)器的版本 SELECT DATABASE(),USER(),VERSION();,任務(wù)二 存儲(chǔ)過程,使用存儲(chǔ)過程的優(yōu)點(diǎn)有： （1）存儲(chǔ)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。 （2）存儲(chǔ)過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器，在

16、以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，提高了系統(tǒng)性能。 （3）確保數(shù)據(jù)庫的安全。使用存儲(chǔ)過程可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制上述操作對(duì)數(shù)據(jù)庫信息訪問的權(quán)限。,創(chuàng)建存儲(chǔ)過程,創(chuàng)建存儲(chǔ)過程可以使用CREATE PROCEDURE語句 語法格式： CREATE PROCEDURE 存儲(chǔ)過程名 (參數(shù),.)存儲(chǔ)過程體 參數(shù)：存儲(chǔ)過程的參數(shù)，格式如下： IN | OUT | INOUT 參數(shù)名 類型 當(dāng)有多個(gè)參數(shù)的時(shí)候中間用逗號(hào)隔開。存儲(chǔ)過程可以有0個(gè)、1個(gè)或多個(gè)參數(shù)。 MySQL存儲(chǔ)過程支持三種類型的參數(shù)：輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，關(guān)鍵字分別是IN、O

17、UT和INOUT。 輸入?yún)?shù)使數(shù)據(jù)可以傳遞給一個(gè)存儲(chǔ)過程。當(dāng)需要返回一個(gè)答案或結(jié)果的時(shí)候，存儲(chǔ)過程使用輸出參數(shù)。輸入/輸出參數(shù)既可以充當(dāng)輸入?yún)?shù)也可以充當(dāng)輸出參數(shù)。存儲(chǔ)過程也可以不加參數(shù)，但是名稱后面的括號(hào)是不可省略的。 存儲(chǔ)過程體：這是存儲(chǔ)過程的主體部分，也叫做存儲(chǔ)過程體。里面包含了在過程調(diào)用的時(shí)候必須執(zhí)行的語句，這個(gè)部分總是以BEGIN開始，以END結(jié)束。但是，當(dāng)存儲(chǔ)過程體中只有一個(gè)SQL語句時(shí)可以省略BEGIN-END標(biāo)志。,DELIMITER命令,在MySQL中，服務(wù)器處理語句的時(shí)候是以分號(hào)為結(jié)束標(biāo)志的。但是在創(chuàng)建存儲(chǔ)過程的時(shí)候，存儲(chǔ)過程體中可能包含多個(gè)SQL語句，每個(gè)SQL語句都是以

18、分號(hào)為結(jié)尾的，這時(shí)服務(wù)器處理程序的時(shí)候遇到第一個(gè)分號(hào)就會(huì)認(rèn)為程序結(jié)束，這肯定是不行的。所以這里使用DELIMITER命令將MySQL語句的結(jié)束標(biāo)志修改為其他符號(hào)。 DELIMITER語法格式為： DELIMITER $ 說明：$是用戶定義的結(jié)束符，通常這個(gè)符號(hào)可以是一些特殊的符號(hào)，如兩個(gè)“#”，兩個(gè)“￥”等。當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（“”）字符，因?yàn)槟鞘荕ySQL的轉(zhuǎn)義字符。 【例8.29】 將MySQL結(jié)束符修改為兩個(gè)斜杠“/”符號(hào)。 DELIMITER / 說明：執(zhí)行完這條命令后，程序結(jié)束的標(biāo)志就換為雙斜杠符號(hào) “/”了。 要想恢復(fù)使用分號(hào)“;”作為結(jié)束符，運(yùn)行下

19、面命令即可： DELIMITER ;,創(chuàng)建存儲(chǔ)過程舉例,【例8.17】 編寫一個(gè)存儲(chǔ)過程，實(shí)現(xiàn)的功能是刪除一個(gè)特定會(huì)員的信息。 DELIMITER $ CREATE PROCEDURE del_member(IN sfz CHAR(6) BEGIN DELETE FROM Members WHERE 身份證號(hào)=sfz; END $ DELIMITER ; 在關(guān)鍵字BEGIN和END之間指定了存儲(chǔ)過程體，因?yàn)樵诔绦蜷_始用DELIMITER語句轉(zhuǎn)換了語句結(jié)束標(biāo)志為“$”，所以BEGIN和END被看成是一個(gè)整體，在END 后用“$”結(jié)束。當(dāng)然，BEGIN-END復(fù)合語句還可以嵌套使用。 要想查看數(shù)據(jù)

20、庫中有哪些存儲(chǔ)過程，可以使用SHOW PROCEDURE STATUS命令。 SHOW PROCEDURE STATUS 要查看某個(gè)存儲(chǔ)過程的具體信息，可使用SHOW CREATE PROCEDURE sp_name命令，其中sp_name是存儲(chǔ)過程的名稱。 SHOW CREATE PROCEDURE del_member,DECLARE語句,1. 局部變量 在存儲(chǔ)過程中可以聲明局部變量，它們可以用來存儲(chǔ)臨時(shí)結(jié)果。要聲明局部變量必須使用DECLARE語句。在聲明局部變量的同時(shí)也可以對(duì)其賦一個(gè)初始值。 語法格式： DECLARE 變量,. 類型 DEFAULT 值 【例8.18】 聲明一個(gè)整型變

21、量和兩個(gè)字符變量。 DECLARE num INT(4); DECLARE str1, str2 VARCHAR(6); 說明：局部變量只能在BEGINEND語句塊中聲明。 局部變量必須在存儲(chǔ)過程的開頭就聲明，聲明完后，可以在聲明它的BEGINEND語句塊中使用該變量，其他語句塊中不可以使用它。,SET語句,2. 使用SET語句賦值 要給局部變量賦值可以使用SET語句。 語法格式為： SET 變量名1 = 表達(dá)式1 ,變量名2 = 表達(dá)式2 . 【例8.19】 在存儲(chǔ)過程中給局部變量num賦值為1，str1賦值為hello。 SET num=1, str1= hello;,SELECT.INT

22、O語句,3. SELECT.INTO語句 使用這個(gè)SELECTINTO語法可以把選定的列值直接存儲(chǔ)到變量中。因此，返回的結(jié)果只能有一行。 語法格式為： SELECT 列名 ,. INTO 變量名 ,. 數(shù)據(jù)來源表達(dá)式 【例8.20】 在存儲(chǔ)過程體中將Book表中的書名為“計(jì)算機(jī)基礎(chǔ)”的作者姓名和出版社的值分別賦給變量name和publish。 SELECT作者,出版社 INTO name, publish FROM Book WHERE 書名= 計(jì)算機(jī)基礎(chǔ);,調(diào)用存儲(chǔ)過程,存儲(chǔ)過程創(chuàng)建完后，可以在程序、觸發(fā)器或者存儲(chǔ)過程中被調(diào)用，調(diào)用時(shí)都必須使用到CALL語句， 其語法格式： CALL 存儲(chǔ)過

23、程名( 參數(shù) ,.) 【例8.21】 創(chuàng)建存儲(chǔ)過程實(shí)現(xiàn)查詢Members表中會(huì)員人數(shù)的功能,并執(zhí)行它。 首先創(chuàng)建查詢Members表中會(huì)員人數(shù)的存儲(chǔ)過程： CREATE PROCEDURE query_members() SELECT COUNT(*) FROM Members; 這是一個(gè)不帶參數(shù)的非常簡(jiǎn)單的存儲(chǔ)過程，通常SELECT語句不會(huì)被直接用在存儲(chǔ)過程中。 調(diào)用該存儲(chǔ)過程： CALL query_members();,課堂練習(xí),1、創(chuàng)建存儲(chǔ)過程，實(shí)現(xiàn)給出員工編號(hào)，在employees表和salary表中刪除該員工記錄。并調(diào)用該存儲(chǔ)過程，刪除員工編號(hào)為020018的記錄。 2、創(chuàng)建存儲(chǔ)過

24、程，實(shí)現(xiàn)查詢salary表中收入總額，并調(diào)用該的存儲(chǔ)過程。,刪除存儲(chǔ)過程,存儲(chǔ)過程創(chuàng)建后需要?jiǎng)h除時(shí)使用DROP PROCEDURE語句。在此之前，必須確認(rèn)該存儲(chǔ)過程沒有任何依賴關(guān)系，否則會(huì)導(dǎo)致其他與之關(guān)聯(lián)的存儲(chǔ)過程無法運(yùn)行。 語法格式為： DROP PROCEDURE IF EXISTS 存儲(chǔ)過程名 【例8.22】 刪除存儲(chǔ)過程query_members()。 DROP PROCEDURE IF EXISTS query_members();,流程控制語句,在MySQL中，常見的過程式SQL語句可以用在一個(gè)存儲(chǔ)過程體中。例如：IF語句、CASE語句、LOOP語句、WHILE語句、ITERATE

25、語句和LEAVE語句。 （1）IF語句 語法格式為： IF 條件1 THEN 語句序列1 ELSEIF條件2 THEN語句序列2 . ELSE 語句序列e END IF,IF語句舉例,DELIMITER $ CREATE PROCEDURE COMPAR (IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IF K1K2 THEN SET K3= 大于; ELSEIF K1=K2 THEN SET K3= 等于; ELSE SET K3= 小于; END IF; END$ DELIMITER ; 說明：存儲(chǔ)過程中K1和K2是輸入?yún)?shù)，K3

26、是輸出參數(shù)。,【例8.23】 創(chuàng)建存儲(chǔ)過程，判斷兩個(gè)輸入的參數(shù)哪一個(gè)更大。,存儲(chǔ)過程創(chuàng)建完后，可以在程序、觸發(fā)器或者存儲(chǔ)過程中被調(diào)用，但是都必須使用到CALL語句。 語法格式： CALL sp_name(parameter,.) parameter為調(diào)用該存儲(chǔ)過程使用的參數(shù)，這條語句中的參數(shù)個(gè)數(shù)必須總是等于存儲(chǔ)過程的參數(shù)個(gè)數(shù)。 如果是輸出變量，前面加 調(diào)用該存儲(chǔ)過程： CALL COMPAR(3, 6, K); SELECT K; 說明：3和6相當(dāng)于輸入?yún)?shù)K1和K2，用戶變量K相當(dāng)于輸出參數(shù)K3。可以看到，由于36，輸出參數(shù)K的值就為“小于”。,調(diào)用存儲(chǔ)過程舉例,【例8.24】 創(chuàng)建一個(gè)Bo

27、okstore數(shù)據(jù)庫的存儲(chǔ)過程，根據(jù)客戶姓名和書名查詢訂單，如果訂購冊(cè)數(shù)小于5本不打折，訂購冊(cè)數(shù)在5-10本之間，訂購單價(jià)打九折，訂購冊(cè)數(shù)大于10本，訂購單價(jià)打八折。 DELIMITER $ CREATE PROCEDURE dj_update(IN c_name CHAR(8), IN b_name CHAR(20) BEGIN DECLARE bh CHAR(20); DECLARE sfz CHAR(18); DECLARE sl TINYINT; SELECT 身份證號(hào) INTO sfz FROM Members WHERE 會(huì)員姓名=c_name; SELECT 圖書編號(hào) INTO

28、bh FROM Book WHERE 書名=b_name; SELECT 訂購冊(cè)數(shù) INTO sl FROM Sell WHERE 身份證號(hào)=sfz AND 圖書編號(hào)=bh; IF sl=5 AND sl10 THEN UPDATE Sell SET 訂購單價(jià)=訂購單價(jià)*0.8 WHERE 身份證號(hào)=sfz AND 圖書編號(hào)=bh; END IF; END IF; END$ DELIMITER ; 調(diào)用存儲(chǔ)過程dj_update： CALL dj_update (張三, 網(wǎng)絡(luò)數(shù)據(jù)庫);,調(diào)用存儲(chǔ)過程舉例,CASE語句,（2）CASE語句 語法格式為： CASE 表達(dá)式 WHEN 值1 THEN

29、 語句序列1 WHEN值2 THEN語句序列2 . ELSE語句序列e END CASE 或者： CASE WHEN 條件1 THEN 語句序列1 WHEN條件2 THEN語句序列2 . ELSE語句序列e END CASE 第一種格式中表達(dá)式是要被判斷的值或表達(dá)式，接下來是一系列的WHEN-THEN塊，每一塊的值參數(shù)都要與表達(dá)式比較的值，如果為真，就執(zhí)行語句序列中的SQL語句。如果前面的每一個(gè)塊都不匹配就會(huì)執(zhí)行ELSE塊指定的語句。CASE語句最后以END CASE結(jié)束。 第二種格式中CASE關(guān)鍵字后面沒有參數(shù)，在WHEN-THEN塊中，條件指定了一個(gè)比較表達(dá)式，表達(dá)式為真時(shí)執(zhí)行THEN后面

30、的語句。 第二種格式與第一種格式相比，更能夠?qū)崿F(xiàn)更為復(fù)雜的條件判斷，使用起來更方便。,【例8.25】 創(chuàng)建一個(gè)存儲(chǔ)過程，當(dāng)給定參數(shù)為時(shí)返回“上升”，給定參數(shù)為時(shí)返回“下降”，給定其他參數(shù)時(shí)返回“不變”。 DELIMITER $ CREATE PROCEDURE var_cp (IN str VARCHAR(1), OUT direct VARCHAR(4) ) BEGIN CASE str WHEN U THEN SET direct =上升; WHEN D THEN SET direct =下降; ELSE SET direct =不變; END CASE; END$ DELIMITER ;

31、 以上的CASE語句用第二種格式來寫如下： CASE WHEN str= U THEN SET direct =上升; WHEN str= D THEN SET direct =下降; ELSE SET direct =不變; END CASE;,CASE語句舉例,課堂練習(xí),1、創(chuàng)建存儲(chǔ)過程，輸入員工編號(hào)bh ，輸出該員工的性別。 2、創(chuàng)建存儲(chǔ)過程，輸入員工編號(hào)bh和級(jí)別jb兩個(gè)參數(shù)，如果jb為A，該員工收入增加500元， jb為B，該員工收入增加300元，jb為C，該員工收入增加150元，jb為A、B、C以外的其他值，該員工收入增加50元。 3、創(chuàng)建存儲(chǔ)過程，比較兩個(gè)員工的實(shí)際收入，如前者比

32、后者高就輸出0，否則輸出1。并調(diào)用該存儲(chǔ)過程比較”000001”和”108991”兩員工的收入。,課堂練習(xí)參考答案,1、創(chuàng)建存儲(chǔ)過程 create procedure cp(in id1 char(6),in id2 char(6),out bj int) begin declare sr1,sr2 float(8); select income-outcome into sr1 from salary where employeeid=id1; select income-outcome into sr2 from salary where employeeid=id2; if id1id2

33、 then set bj=0; else set bj=1; end if; end 2、 調(diào)用存儲(chǔ)過程： call cp(000001,108991,bj) 3、輸出結(jié)果 select bj;,循環(huán)語句,（3）循環(huán)語句 MySQL支持3條用來創(chuàng)建循環(huán)的語句：WHILE、REPEAT和LOOP語句。 WHILE語句語法格式為： 開始標(biāo)號(hào): WHILE條件 DO 程序段 END WHILE 結(jié)束標(biāo)號(hào) 語句首先判斷條件是否為真，為真則執(zhí)行程序段中的語句，然后再次進(jìn)行判斷，為真則繼續(xù)循環(huán)，不為真則結(jié)束循環(huán)。 開始標(biāo)號(hào)和結(jié)束標(biāo)號(hào)是WHILE語句的標(biāo)注。除非開始標(biāo)號(hào)存在，否則結(jié)束標(biāo)號(hào)，并且如果兩者都出現(xiàn)

34、，它們的名字必須是相同的。,WHILE語句舉例,【例8.26】 創(chuàng)建一個(gè)帶WHILE執(zhí)行5次循環(huán)的存儲(chǔ)過程。 DELIMITER $ CREATE PROCEDURE dowhile() BEGIN DECLARE a INT DEFAULT 5; WHILE a 0 DO SET a = a1; END WHILE; END$ DELIMITER ;,REPEAT語句, REPEAT語句格式如下： 開始標(biāo)號(hào): REPEAT 程序段 UNTIL 條件 END REPEAT 結(jié)束標(biāo)號(hào) 用REPEAT語句替換例8.26的WHILE循環(huán)過程如下： REPEAT a=a1; UNTIL a1; END

35、 REPEAT; 說明：REPEAT語句和WHILE語句的區(qū)別在于：REPEAT語句先執(zhí)行語句，后進(jìn)行判斷；而WHILE語句是先判斷，條件為真時(shí)才執(zhí)行語句。,LOOP語句, LOOP語句語法格式如下： 開始標(biāo)號(hào): LOOP 程序段 END LOOP 結(jié)束標(biāo)號(hào) 說明：LOOP允許某特定語句或語句群的重復(fù)執(zhí)行，實(shí)現(xiàn)一個(gè)簡(jiǎn)單的循環(huán)構(gòu)造，程序段是需要重復(fù)執(zhí)行的語句。在循環(huán)內(nèi)的語句一直重復(fù)至循環(huán)被退出，退出時(shí)通常伴隨著一個(gè)LEAVE 語句。結(jié)構(gòu)如下：LEAVE label 【例8.27】 使用LOOP語句重寫8.26的存儲(chǔ)過程。 DELIMITER $ CREATE PROCEDURE doloop()

36、 BEGIN SET a=5; Label: LOOP SET a=a1; IF a1 THEN LEAVE Label; END IF; END LOOP Label; END$ DELIMITER ;,存儲(chǔ)過程的嵌套,【例8.28】 創(chuàng)建一個(gè)存儲(chǔ)過程sell_insert()，作用是向Sell表中插入一行數(shù)據(jù)。創(chuàng)建另外一個(gè)存儲(chǔ)過程sell_update，在其中調(diào)用第一個(gè)存儲(chǔ)過程，如果給定參數(shù)為0，則修改由第一個(gè)存儲(chǔ)過程插入記錄的是否發(fā)貨字段為已發(fā)貨，如果給定參數(shù)為1則刪除第一個(gè)存儲(chǔ)過程插入的記錄，并將操作結(jié)果輸出。 第一個(gè)存儲(chǔ)過程：向Sell表中插入一行數(shù)據(jù) CREATE PROCEDUR

37、E sell_insert() INSERT INTO Sell VALUES(17 ISBN7-301-06342-3,4, 30, 2013-03-05, NULL, NULL, NULL); 第二個(gè)存儲(chǔ)過程：調(diào)用第一個(gè)存儲(chǔ)過，并輸出結(jié)果 CREATE PROCEDURE sell_update(IN X INT(1), OUT STR CHAR(8) BEGIN CALL sell_insert(); CASE WHEN x=0 THEN UPDATE Sell SET 是否發(fā)貨=已發(fā)貨 WHERE 訂單號(hào)=17; SET STR=修改成功; WH

38、EN X=1 THEN DELETE FROM Sell WHERE 訂單號(hào)=17; SET STR=刪除成功; END CASE; END,存儲(chǔ)過程的嵌套,接下來調(diào)用存儲(chǔ)過程sell_update來查看結(jié)果： CALL sell_update (1, str); SELECT str; 結(jié)果為：刪除成功 CALL sell_update (0, str); SELECT str; 結(jié)果為：修改成功,課堂練習(xí),1、創(chuàng)建存儲(chǔ)過程，輸入一個(gè)數(shù)x ，輸出x!。 2、創(chuàng)建存儲(chǔ)過程DO_hz1，作用是向salary表中插入?yún)R總行（合計(jì)，收入總和，支出總和）。創(chuàng)建另外一個(gè)存儲(chǔ)過程DO_show2，在其中調(diào)

39、用第一個(gè)存儲(chǔ)過程，并輸出匯總結(jié)果。,任務(wù)三 存儲(chǔ)函數(shù),存儲(chǔ)函數(shù)也是過程式對(duì)象之一，與存儲(chǔ)過程很相似。它們都是由SQL和過程式語句組成的代碼片斷，并且可以從應(yīng)用程序和SQL中調(diào)用。然而，它們也有一些區(qū)別： （1）存儲(chǔ)函數(shù)不能擁有輸出參數(shù)，因?yàn)榇鎯?chǔ)函數(shù)本身就是輸出參數(shù)； （2）不能用CALL語句來調(diào)用存儲(chǔ)函數(shù)； （3）存儲(chǔ)函數(shù)必須包含一條RETURN語句，而這條特殊的SQL語句不允許包含于存儲(chǔ)過程中。 創(chuàng)建存儲(chǔ)函數(shù)使用CREATE FUNCTION語句。 語法格式： CREATE FUNCTION 存儲(chǔ)函數(shù)名 (參數(shù),.) RETURNS 類型 函數(shù)體,創(chuàng)建存儲(chǔ)函數(shù),【例8.29】 創(chuàng)建一個(gè)存儲(chǔ)函

40、數(shù)，它返回Book表中圖書數(shù)目作為結(jié)果。 DELIMITER $ CREATE FUNCTION num_book() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM Book); END$ DELIMITER ; RETURN子句中包含SELECT語句時(shí)，SELECT語句的返回結(jié)果只能是一行且只能有一列值。 雖然此存儲(chǔ)函數(shù)沒有參數(shù)，使用時(shí)也要用（），如num_book()。 要查看數(shù)據(jù)庫中有哪些存儲(chǔ)函數(shù)，可以使用SHOW FUNCTION STATUS命令。 SHOW FUNCTION STATUS 調(diào)用存儲(chǔ)函數(shù) 存儲(chǔ)函數(shù)創(chuàng)建完后，就如同

41、系統(tǒng)提供的內(nèi)置函數(shù)（如VERSION()），所以調(diào)用存儲(chǔ)函數(shù)的方法也差不多，都是使用SELECT關(guān)鍵字。 語法格式為： SELECT sp_name (func_parameter,.) 調(diào)用【例8.29】存儲(chǔ)函數(shù): Select NUM_book(),存儲(chǔ)函數(shù)舉例,【例8.30】 創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，返回Book表中某本書的作者姓名。 DELIMITER $ CREATE FUNCTION author_book(b_name CHAR(20) RETURNS CHAR(8) BEGIN RETURN (SELECT 作者 FROM Book WHERE 書名= b_name); END$ D

42、ELIMITER ; 此存儲(chǔ)函數(shù)給定書名，返回該書的作者。如要查詢計(jì)算機(jī)應(yīng)用基礎(chǔ)的作者，用author_book(計(jì)算機(jī)應(yīng)用基礎(chǔ))。,【例8.31】創(chuàng)建一個(gè)存儲(chǔ)函數(shù)來刪除Sell表中有但Book表中不存在的記錄。 CREATE FUNCTION del_Sell1(b_bh CHAR(20) RETURNS BOOLEAN BEGIN DECLARE bh CHAR(20); SELECT 圖書編號(hào) INTO bh FROM Book WHERE 圖書編號(hào)=b_bh; IF bh IS NULL THEN DELETE FROM Sell WHERE 圖書編號(hào)=b_bh; RETURN TRU

43、E; ELSE RETURN FALSE; END IF; END$,存儲(chǔ)函數(shù)舉例,課堂練習(xí),1、創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，返回員工的總?cè)藬?shù)EM_NUM() 2、創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，判斷員工是否在研發(fā)部工作，若是則返回其學(xué)歷，若不是則返回“NO”。,課堂練習(xí)參考答案,1、創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，返回員工的總?cè)藬?shù)EM_NUM() CREATE FUNCTION EM_NUM() RETURNS Integer RETURN ( SELECT count( * ) FROM Employees); 2、創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，判斷員工是否在研發(fā)部工作，若是則返回其學(xué)歷，若不是則返回“NO”。 CREATE FUNCTI

44、ON XL(XH CHAR(6) RETURNS CHAR(10) BEGIN DECLARE BM,ED CHAR(6); SELECT departmentID,education INTO BM,ED FROM employees WHERE employeeID =XH; IF bm=“4” THEN RETURN ED; ELSE RETURN “NO”; END IF; END,任務(wù)四 觸發(fā)器,創(chuàng)建觸發(fā)器語法格式： CREATE TRIGGER 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件 ON 表名 FOR EACH ROW 觸發(fā)器動(dòng)作 觸發(fā)時(shí)間：觸發(fā)器觸發(fā)的時(shí)刻，有兩個(gè)選項(xiàng)：AFTER和BEF

45、ORE，以表示觸發(fā)器是在激活它的語句之前或之后觸發(fā)。如果想要在激活觸發(fā)器的語句執(zhí)行之后執(zhí)行幾個(gè)或更多的改變，通常使用AFTER選項(xiàng)；如果想要驗(yàn)證新數(shù)據(jù)是否滿足使用的限制，則使用BEFORE選項(xiàng)。 觸發(fā)事件：指明了激活觸發(fā)程序的語句的類型。觸發(fā)事件可以是下述值之一： INSERT：將新行插入表時(shí)激活觸發(fā)器。例如，通過INSERT、LOAD DATA和REPLACE語句。 UPDATE：更改某一行時(shí)激活觸發(fā)器。例如，通過UPDATE語句。 DELETE：從表中刪除某一行時(shí)激活觸發(fā)器。例如，通過DELETE和REPLACE語句。 觸發(fā)器動(dòng)作：包含觸發(fā)器激活時(shí)將要執(zhí)行的語句。如果要執(zhí)行多個(gè)語句，可使用

46、BEGIN . END復(fù)合語句結(jié)構(gòu)。這樣，就能使用存儲(chǔ)過程中允許的相同語句。,創(chuàng)建觸發(fā)器舉例,【例8.34】 創(chuàng)建一個(gè)表table1，其中只有一列a。在表上創(chuàng)建一個(gè)觸發(fā)器，每次插入操作時(shí)，將用戶變量str的值設(shè)為“TRIGGER IS WORKING”。 CREATE TABLE table1(a INTEGER); CREATE TRIGGER table1_insert AFTER INSERT ON table1 FOR EACH ROW SET str= TRIGGER IS WORKING ; 向table1中插入一行數(shù)據(jù)： INSERT INTO table1 VALUES(10)

47、; 查看str的值： SELECT str; 在MySQL觸發(fā)器中的SQL語句可以關(guān)聯(lián)表中的任意列。但不能直接使用列的名稱去標(biāo)志，那會(huì)使系統(tǒng)混淆，因?yàn)榧せ钣|發(fā)器的語句可能已經(jīng)修改、刪除或添加了新的列名，而列的舊名同時(shí)存在。因此必須用這樣的語法來標(biāo)志：“NEW.column_name”或者“OLD.column_name”。NEW.column_name用來引用新行的一列，OLD.column_name用來引用更新或刪除它之前的已有行的一列。 對(duì)于INSERT語句，只有NEW是合法的；對(duì)于DELETE語句，只有OLD才合法；而UPDATE語句可以與NEW或OLD同時(shí)使用。,創(chuàng)建觸發(fā)器舉例,【例8

48、.35】 創(chuàng)建一個(gè)觸發(fā)器，當(dāng)刪除表Book表中某圖書的信息時(shí)，同時(shí)將Sell表中與該圖書有關(guān)的數(shù)據(jù)全部刪除。 DELIMITER $ CREATE TRIGGER book_del AFTER DELETE ON Book FOR EACH ROW BEGIN DELETE FROM Sell WHERE 圖書編號(hào)=OLD.圖書編號(hào); END$ DELIMITER ; 現(xiàn)在驗(yàn)證一下觸發(fā)器的功能： DELETE FROM Book WHERE 圖書編號(hào)=ISBN 7-5006-6625; 使用SELECT語句查看Sell表中的情況： SELECT * FROM Sell WHERE 圖書編號(hào)=I

49、SBN 7-5006-6625; 這時(shí)可以發(fā)現(xiàn)，圖書編號(hào)為ISBN 7-5006-6625在Sell表中的所有信息已經(jīng)被刪除了。,課堂練習(xí),創(chuàng)建觸發(fā)器，在Employees表中當(dāng)刪除員工信息的同時(shí)將Salary表中與該員工有關(guān)的數(shù)據(jù)全部刪除。,DELIMITER $ CREATE TRIGGER EM_DELETE AFTER DELETE ON Employees FOR EACH ROW BEGIN DELETE FROM salary WHERE employeeID=OLD.employeeID; END$ DELIMITER ;,課堂練習(xí)參考答案,創(chuàng)建觸發(fā)器舉例,【例8.36】 創(chuàng)建一個(gè)觸發(fā)器，當(dāng)修改Sell表中訂購冊(cè)數(shù)時(shí)，如果修改后的訂購冊(cè)數(shù)小于5本，則觸發(fā)器將該對(duì)應(yīng)的折扣修改為1,否則，折扣修改為0.8。 DELI