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

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

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

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

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

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

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

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

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

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

10、達式,表達式就是常量、變量、列名、復雜計算、運算符和函數(shù)的組合。一個表達式通常可以得到一個值。與常量和變量一樣，表達式的值也具有某種數(shù)據(jù)類型，可能的數(shù)據(jù)類型有字符類型、數(shù)值類型、日期時間類型。這樣，根據(jù)表達式的值的類型，表達式可分為字符型表達式、數(shù)值型表達式和日期表達式。 表達式按照形式還可分為單一表達式和復合表達式。 單一表達式就是一個單一的值，如一個常量或列名。 復合表達式是由運算符將多個單一表達式連接而成的表達式 例如：1+2+3，a=b+3，2008-01-20+ INTERVAL 2 MONTH。 表達式一般用在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,請用MySQL函數(shù)完成以下計算： （1）求x和y的最大整數(shù)值和最小整數(shù)值 （2）求x和y四舍五入的整數(shù)值 （3）求y分別保留小數(shù)位數(shù)為2位和保留小數(shù)位數(shù)4位的值 （4）求x的平方根和y的絕對值 3、求字符R的ASCII值 4、求ASCII碼值為91、92、93組成的一個字符串 5、設(shè)有字符串s1=ABCDEFG,s2= yxz ,請用MySQL函數(shù)完成以下運算： （1） 返回s1最左邊的3個字符和最右邊的3個字符。 （2）分別刪除字符串s2首部空格、尾部空格、所有空格。

12、（3）返回字符串s1第3個字符開始的4個字符串 （4）比較s1和s2兩個字符串 6、用MySQL函數(shù)顯示當前日期、當前時間、當前年、當前月的英文、當期星期的英文名，當前日期減10天的日期 7、加密顯示當前數(shù)據(jù)庫名，當前登錄用戶名 和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,請用MySQL函數(shù)完成以下計算： （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的絕對值 SELECT SQRT(x),ABS(y); 3、求字符R的ASCII值 SELECT ASCII(R); 4、求ASCII碼值為91、92、93組成的一個字符串 SELECT CHAR(91,92

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

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

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

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

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

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

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

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

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

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

24、程，實現(xiàn)查詢salary表中收入總額，并調(diào)用該的存儲過程。,刪除存儲過程,存儲過程創(chuàng)建后需要刪除時使用DROP PROCEDURE語句。在此之前，必須確認該存儲過程沒有任何依賴關(guān)系，否則會導致其他與之關(guān)聯(lián)的存儲過程無法運行。 語法格式為： DROP PROCEDURE IF EXISTS 存儲過程名 【例8.22】 刪除存儲過程query_members()。 DROP PROCEDURE IF EXISTS query_members();,流程控制語句,在MySQL中，常見的過程式SQL語句可以用在一個存儲過程體中。例如：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 ; 說明：存儲過程中K1和K2是輸入?yún)?shù)，K3

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

27、okstore數(shù)據(jù)庫的存儲過程，根據(jù)客戶姓名和書名查詢訂單，如果訂購冊數(shù)小于5本不打折，訂購冊數(shù)在5-10本之間，訂購單價打九折，訂購冊數(shù)大于10本，訂購單價打八折。 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 身份證號 INTO sfz FROM Members WHERE 會員姓名=c_name; SELECT 圖書編號 INTO

28、bh FROM Book WHERE 書名=b_name; SELECT 訂購冊數(shù) INTO sl FROM Sell WHERE 身份證號=sfz AND 圖書編號=bh; IF sl=5 AND sl10 THEN UPDATE Sell SET 訂購單價=訂購單價*0.8 WHERE 身份證號=sfz AND 圖書編號=bh; END IF; END IF; END$ DELIMITER ; 調(diào)用存儲過程dj_update： CALL dj_update (張三, 網(wǎng)絡(luò)數(shù)據(jù)庫);,調(diào)用存儲過程舉例,CASE語句,（2）CASE語句 語法格式為： CASE 表達式 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 第一種格式中表達式是要被判斷的值或表達式，接下來是一系列的WHEN-THEN塊，每一塊的值參數(shù)都要與表達式比較的值，如果為真，就執(zhí)行語句序列中的SQL語句。如果前面的每一個塊都不匹配就會執(zhí)行ELSE塊指定的語句。CASE語句最后以END CASE結(jié)束。 第二種格式中CASE關(guān)鍵字后面沒有參數(shù)，在WHEN-THEN塊中，條件指定了一個比較表達式，表達式為真時執(zhí)行THEN后面

30、的語句。 第二種格式與第一種格式相比，更能夠?qū)崿F(xiàn)更為復雜的條件判斷，使用起來更方便。,【例8.25】 創(chuàng)建一個存儲過程，當給定參數(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語句舉例,課堂練習,1、創(chuàng)建存儲過程，輸入員工編號bh ，輸出該員工的性別。 2、創(chuàng)建存儲過程，輸入員工編號bh和級別jb兩個參數(shù)，如果jb為A，該員工收入增加500元， jb為B，該員工收入增加300元，jb為C，該員工收入增加150元，jb為A、B、C以外的其他值，該員工收入增加50元。 3、創(chuàng)建存儲過程，比較兩個員工的實際收入，如前者比

32、后者高就輸出0，否則輸出1。并調(diào)用該存儲過程比較”000001”和”108991”兩員工的收入。,課堂練習參考答案,1、創(chuàng)建存儲過程 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)用存儲過程： call cp(000001,108991,bj) 3、輸出結(jié)果 select bj;,循環(huán)語句,（3）循環(huán)語句 MySQL支持3條用來創(chuàng)建循環(huán)的語句：WHILE、REPEAT和LOOP語句。 WHILE語句語法格式為： 開始標號: WHILE條件 DO 程序段 END WHILE 結(jié)束標號 語句首先判斷條件是否為真，為真則執(zhí)行程序段中的語句，然后再次進行判斷，為真則繼續(xù)循環(huán)，不為真則結(jié)束循環(huán)。 開始標號和結(jié)束標號是WHILE語句的標注。除非開始標號存在，否則結(jié)束標號，并且如果兩者都出現(xiàn)

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

35、 REPEAT; 說明：REPEAT語句和WHILE語句的區(qū)別在于：REPEAT語句先執(zhí)行語句，后進行判斷；而WHILE語句是先判斷，條件為真時才執(zhí)行語句。,LOOP語句, LOOP語句語法格式如下： 開始標號: LOOP 程序段 END LOOP 結(jié)束標號 說明：LOOP允許某特定語句或語句群的重復執(zhí)行，實現(xiàn)一個簡單的循環(huán)構(gòu)造，程序段是需要重復執(zhí)行的語句。在循環(huán)內(nèi)的語句一直重復至循環(huán)被退出，退出時通常伴隨著一個LEAVE 語句。結(jié)構(gòu)如下：LEAVE label 【例8.27】 使用LOOP語句重寫8.26的存儲過程。 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 ;,存儲過程的嵌套,【例8.28】 創(chuàng)建一個存儲過程sell_insert()，作用是向Sell表中插入一行數(shù)據(jù)。創(chuàng)建另外一個存儲過程sell_update，在其中調(diào)用第一個存儲過程，如果給定參數(shù)為0，則修改由第一個存儲過程插入記錄的是否發(fā)貨字段為已發(fā)貨，如果給定參數(shù)為1則刪除第一個存儲過程插入的記錄，并將操作結(jié)果輸出。 第一個存儲過程：向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); 第二個存儲過程：調(diào)用第一個存儲過，并輸出結(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 訂單號=17; SET STR=修改成功; WH

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

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

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

41、系統(tǒng)提供的內(nèi)置函數(shù)（如VERSION()），所以調(diào)用存儲函數(shù)的方法也差不多，都是使用SELECT關(guān)鍵字。 語法格式為： SELECT sp_name (func_parameter,.) 調(diào)用【例8.29】存儲函數(shù): Select NUM_book(),存儲函數(shù)舉例,【例8.30】 創(chuàng)建一個存儲函數(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 ; 此存儲函數(shù)給定書名，返回該書的作者。如要查詢計算機應用基礎(chǔ)的作者，用author_book(計算機應用基礎(chǔ))。,【例8.31】創(chuàng)建一個存儲函數(shù)來刪除Sell表中有但Book表中不存在的記錄。 CREATE FUNCTION del_Sell1(b_bh CHAR(20) RETURNS BOOLEAN BEGIN DECLARE bh CHAR(20); SELECT 圖書編號 INTO bh FROM Book WHERE 圖書編號=b_bh; IF bh IS NULL THEN DELETE FROM Sell WHERE 圖書編號=b_bh; RETURN TRU

43、E; ELSE RETURN FALSE; END IF; END$,存儲函數(shù)舉例,課堂練習,1、創(chuàng)建一個存儲函數(shù)，返回員工的總?cè)藬?shù)EM_NUM() 2、創(chuàng)建一個存儲函數(shù)，判斷員工是否在研發(fā)部工作，若是則返回其學歷，若不是則返回“NO”。,課堂練習參考答案,1、創(chuàng)建一個存儲函數(shù)，返回員工的總?cè)藬?shù)EM_NUM() CREATE FUNCTION EM_NUM() RETURNS Integer RETURN ( SELECT count( * ) FROM Employees); 2、創(chuàng)建一個存儲函數(shù)，判斷員工是否在研發(fā)部工作，若是則返回其學歷，若不是則返回“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ā)時間 觸發(fā)事件 ON 表名 FOR EACH ROW 觸發(fā)器動作 觸發(fā)時間：觸發(fā)器觸發(fā)的時刻，有兩個選項：AFTER和BEF

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

46、BEGIN . END復合語句結(jié)構(gòu)。這樣，就能使用存儲過程中允許的相同語句。,創(chuàng)建觸發(fā)器舉例,【例8.34】 創(chuàng)建一個表table1，其中只有一列a。在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將用戶變量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)表中的任意列。但不能直接使用列的名稱去標志，那會使系統(tǒng)混淆，因為激活觸發(fā)器的語句可能已經(jīng)修改、刪除或添加了新的列名，而列的舊名同時存在。因此必須用這樣的語法來標志：“NEW.column_name”或者“OLD.column_name”。NEW.column_name用來引用新行的一列，OLD.column_name用來引用更新或刪除它之前的已有行的一列。 對于INSERT語句，只有NEW是合法的；對于DELETE語句，只有OLD才合法；而UPDATE語句可以與NEW或OLD同時使用。,創(chuàng)建觸發(fā)器舉例,【例8

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

49、SBN 7-5006-6625; 這時可以發(fā)現(xiàn)，圖書編號為ISBN 7-5006-6625在Sell表中的所有信息已經(jīng)被刪除了。,課堂練習,創(chuàng)建觸發(fā)器，在Employees表中當刪除員工信息的同時將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 ;,課堂練習參考答案,創(chuàng)建觸發(fā)器舉例,【例8.36】 創(chuàng)建一個觸發(fā)器，當修改Sell表中訂購冊數(shù)時，如果修改后的訂購冊數(shù)小于5本，則觸發(fā)器將該對應的折扣修改為1,否則，折扣修改為0.8。 DELI