數(shù)據(jù)庫技術及應用 課件 陳翠松 項目10-12 使用視圖、數(shù)據(jù)庫編程、管理數(shù)據(jù)庫

數(shù)據(jù)庫技術及應用項目10使用視圖通過多表查詢可以實現(xiàn)更大范圍的數(shù)據(jù)整合，滿足復雜的數(shù)據(jù)需求，并且不影響數(shù)據(jù)庫的規(guī)范性。但每次操作都要輸入查詢語句比較麻煩，小王決定把查詢保存下來，將其轉(zhuǎn)變?yōu)橐晥D來提高工作效率。視圖操作方便，提交數(shù)據(jù)安全，并且還能更新數(shù)據(jù)。工作情境目錄01創(chuàng)建和管理視圖02通過視圖更新數(shù)據(jù)03鞏固與小結(jié)04任務訓練01創(chuàng)建和管理視圖一、創(chuàng)建和管理視圖任務分析使用視圖不僅能有效保護數(shù)據(jù)，還能提高數(shù)據(jù)的安全性，因此，小王在粵文創(chuàng)項目中，根據(jù)項目特點設計了不少視圖，以提高數(shù)據(jù)安全性和操作效率。小王對粵文創(chuàng)進行分析后得到的任務清單如下。任務編號任務內(nèi)容任務10-1創(chuàng)建兩個視圖，分別顯示各個城市的城市中文名和電話區(qū)號，以及城市中文名和車牌代碼任務10-2創(chuàng)建視圖，顯示城市民俗信息任務10-3創(chuàng)建視圖，顯示城市名人信息任務10-4創(chuàng)建視圖，顯示城市榮譽信息任務10-5創(chuàng)建視圖，顯示工作計劃參與人員表信息一、創(chuàng)建和管理視圖知識儲備1、初識視圖視圖是數(shù)據(jù)庫中的重要概念。視圖是一個虛擬表，其內(nèi)容由查詢定義。數(shù)據(jù)庫真實表稱為基本表。與基本表一樣，視圖也是由一系列帶有名稱的列和行數(shù)據(jù)組成的。但是，視圖并不在數(shù)據(jù)庫中以存儲數(shù)據(jù)值集的形式存在，而是存儲查詢命令，在引用視圖時才動態(tài)生成數(shù)據(jù)。視圖的建立和刪除只影響視圖本身，不影響對應的基本表。從用戶的角度來看，一個視圖是從一個特定的角度查看數(shù)據(jù)庫中的數(shù)據(jù)。視圖有很多優(yōu)點，主要表現(xiàn)在以下幾方面。1）定制數(shù)據(jù)，提高安全性2）簡化操作，提高效率3）邏輯數(shù)據(jù)獨立性強，方便數(shù)據(jù)合并、分割和共享一、創(chuàng)建和管理視圖知識儲備2、創(chuàng)建視圖創(chuàng)建視圖的語法格式如下如下：CREATEVIEW視圖名[(字段列表)]ASSELECT語句WITHCHECKOPTION;需要說明以下幾點。SELECT語句不能引用系統(tǒng)或用戶變量，不能包含F(xiàn)ROM子句中的子查詢，也不能引用預處理語句參數(shù)。盡管創(chuàng)建的視圖是一個虛擬表，但可以當作表操作，還可以作為其他視圖的數(shù)據(jù)源?？梢允褂肧ELECT語句查詢視圖數(shù)據(jù)，可以使用DESC顯示視圖所包含的字段。如果在創(chuàng)建視圖時設置了參數(shù)“WITHCHECKOPTION”，那么更新數(shù)據(jù)時不能插入或更新不符合視圖限制條件的記錄。一、創(chuàng)建和管理視圖知識儲備示例10-1創(chuàng)建視圖vquerytitle，查詢工作人員表user中的userId、userName和fkTitle。程序代碼如下：CREATEVIEWvquerytitleASSELECTuserId,username,fkTitleFROMuser;示例10-2創(chuàng)建視圖vquerygender，查詢工作人員表user中的userId、userName和gender，并將字段名稱分別改為工號、姓名和性別。程序代碼如下：CREATEVIEWvquerygender(工號,姓名,性別)ASSELECTuserId,username,genderFROMuser;示例10-3創(chuàng)建視圖vqueryhonor，查詢各個城市的榮譽，并顯示編號、城市和榮譽。程序代碼如下：CREATEVIEWvqueryhonor(編號,城市,榮譽)ASSELECTid,chineseName,honoraryTitleFROMhonorJOINareaONhonor.fkAreaNumber=area.areaNumber;一、創(chuàng)建和管理視圖知識儲備3、管理視圖1）打開視圖雖然視圖是虛擬表，但其使用方法與基本表的使用方法完全一致。查看視圖所有數(shù)據(jù)內(nèi)容可以直接使用SELECT語句，語法格式如下：SELECT*FROM視圖名;示例10-4打開視圖vquerygender。程序代碼如下：SELECT*FROMvquerygender;一、創(chuàng)建和管理視圖知識儲備3、管理視圖2）查看視圖（1）查看視圖結(jié)構(gòu)。查看視圖結(jié)構(gòu)可以使用DESCRIBE（可以簡寫為DESC）語句，語法格式如下：DESCRIBE視圖名;（2）查看視圖詳細信息。查看視圖詳細信息可以使用SHOWCREATEVIEW語句，語法格式如下：SHOWCREATEVIEW視圖名;（3）查看所有視圖。查看所有表和視圖可以使用SHOWTABLES語句，語法格式如下：SHOWTABLES;示例10-5查看視圖vquerygender的結(jié)構(gòu)和詳細信息。程序代碼如下：DESCRIBEvquerygender;SHOWCREATEVIEWvquerygender;SHOWTABLES;一、創(chuàng)建和管理視圖知識儲備3、管理視圖3）修改視圖修改視圖可以先刪除原有視圖再新建視圖，也可以使用ALTERVIEW語句，該語句要求登錄用戶除了有SELECT權限，還要有CREATEVIEW和DROPVIEW權限。修改視圖的使用方法如下：ALTERVIEW視圖名ASSELECT語句;4）刪除視圖刪除視圖可以使用DROPVIEW語句，語法格式如下：DROPVIEW視圖名;示例10-6刪除視圖vquerygender。程序代碼如下：DROPVIEWvquerygender;一、創(chuàng)建和管理視圖知識儲備3、使用Navicat操作視圖（1）啟動Navicat，連接MySQL服務器，雙擊指定的數(shù)據(jù)庫，單擊“視圖”圖標，顯示視圖列表，如圖10-2所示。

（2）選中指定的視圖后，單擊視圖列表上方的“打開視圖”選項圖標可以查看選中的視圖的所有數(shù)據(jù)，單擊“刪除視圖”圖標可以刪除選中的視圖；單擊“導出向?qū)А眻D標可以將選中的視圖的內(nèi)容導出為指定格式文件。（3）單擊“新建視圖”圖標，先在工作區(qū)輸入查詢語句并保存，再輸入視圖名，如圖10-3所示，輸入視圖名后單擊“確定”按鈕，系統(tǒng)會對查詢語句進行規(guī)范化處理，如圖10-4所示，單擊代碼界面上方的“美化SQL”圖標，美化結(jié)果如圖10-5所示。一、創(chuàng)建和管理視圖知識儲備3、使用Navicat操作視圖一、創(chuàng)建和管理視圖知識儲備3、使用Navicat操作視圖（4）選中指定的視圖后，單擊視圖列表上方的“設計視圖”圖標可以查看選中的視圖的詳細內(nèi)容。如果要進行修改，那么其操作方法與新建視圖的編輯操作一致。一、創(chuàng)建和管理視圖任務實施任務10-1創(chuàng)建兩個視圖，分別顯示各個城市的城市中文名和電話區(qū)號，以及城市中文名和車牌代碼。任務10-2創(chuàng)建視圖，顯示城市民俗信息。任務10-3創(chuàng)建視圖，顯示城市名人信息。任務10-4創(chuàng)建視圖，顯示城市榮譽信息。任務10-5創(chuàng)建視圖，顯示工作計劃參與人員表信息。02通過視圖更新數(shù)據(jù)二、通過視圖更新數(shù)據(jù)任務分析盡管視圖只是一個虛擬表，只能動態(tài)顯示數(shù)據(jù)，并不能真正保存數(shù)據(jù)，但是能修改數(shù)據(jù)。小王對粵文創(chuàng)進行分析后得到的任務清單如下。任務編號任務內(nèi)容任務10-6通過視圖添加數(shù)據(jù)任務10-7通過視圖修改數(shù)據(jù)任務10-8通過視圖刪除數(shù)據(jù)二、通過視圖更新數(shù)據(jù)知識儲備1、通過視圖更新數(shù)據(jù)表通過視圖可以更新、插入和刪除基本表中的數(shù)據(jù)。視圖和基本表中的數(shù)據(jù)都會更新?？梢允褂肬PDATE語句更新數(shù)據(jù)，使用INSERT語句插入數(shù)據(jù)，使用DELETE語句刪除數(shù)據(jù)。當視圖中包含以下內(nèi)容時，不能通過視圖來修改數(shù)據(jù)。（1）視圖不包含基本表中被定義為非空約束，并且沒有默認值的字段。（2）在定義視圖的SELECT語句中，使用了數(shù)學表達式。（3）在定義視圖的SELECT語句中，使用了聚合函數(shù)。（4）在定義視圖的SELECT語句中，使用了DISTINCT、UNION、TOP、GROUPBY或HAVING子句。（5）在視圖中，可以一次修改多個基本表中的數(shù)據(jù)。二、通過視圖更新數(shù)據(jù)知識儲備示例10-7通過視圖vquerylicensePlateCode，將佛山的車牌代碼改為“粵Y”。程序代碼如下：UPDATEvquerylicensePlateCodeSET車牌代碼="粵Y"WHERE城市中文名="佛山";SELECT*FROMvquerylicensePlateCode;示例10-8通過視圖vqueryparticipant，修改編號為1的記錄，將“2023春惠州行”修改為“2023新春惠州行”，將“李欣”修改為“李小欣”。程序代碼如下：UPDATEvqueryparticipantSET計劃名稱="2023新春惠州行",參與者姓名="李小欣"WHERE編號=1;SELECT*FROMvqueryparticipant;修改失敗，這是為什么呢？運行結(jié)果如圖10-7所示。在一個修改命令中，同時修改了兩個基本表的數(shù)據(jù)。二、通過視圖更新數(shù)據(jù)知識儲備2、通過視圖向數(shù)據(jù)表中插入數(shù)據(jù)示例10-9向視圖vqueryareaCode中添加數(shù)據(jù)

“測試城市”和“999”。程序代碼如下：INSERTINTOvqueryareaCodeVALUES("測試城市","999");DESCvqueryareaCode;DESCarea;運行結(jié)果如圖10-7所示。插入數(shù)據(jù)為什么會失敗呢？因為視圖vqueryareaCode中只有兩個字段，所以只能給這兩個字段輸入值，但其對應的基本表area中有9個字段。除了與視圖相同的2個字段，基本表還有4個非空字段。視圖的數(shù)據(jù)保存在基本表中，因此為視圖vqueryareaCode添加數(shù)據(jù)，其實就是為基本表area添加數(shù)據(jù)，因為還有4個非空字段沒有賦值，所以無法插入數(shù)據(jù)，最終數(shù)據(jù)插入操作失敗。如果成功插入數(shù)據(jù)，那么應檢查是否正確創(chuàng)建了area表。二、通過視圖更新數(shù)據(jù)知識儲備3、通過視圖刪除數(shù)據(jù)表中的數(shù)據(jù)示例10-10通過視圖vquerylicensePlateCode，刪除城市中文名為"佛山"的記錄。程序代碼如下：DELETEFROMvquerylicensePlateCodeWHERE城市中文名="佛山";SELECT*FROMvquerylicensePlateCodeWHERE城市中文名="佛山";SELECT*FROMareaWHEREchineseName="佛山";不僅刪除了視圖vquerylicensePlateCode中的記錄，

還刪除了基本表area中的數(shù)據(jù)，運行結(jié)果如圖10-9所示。測試完后，將刪除的數(shù)據(jù)補回，代碼如下：INSERTINTOareaVALUES("5880","佛山","Foshan，F(xiàn)atshan","禪城","廣東省中部",3797.72,9498900,"0757","粵E");SELECT*FROMareaWHEREchineseName="佛山";二、通過視圖更新數(shù)據(jù)知識儲備3、通過視圖刪除數(shù)據(jù)表中的數(shù)據(jù)示例10-10通過視圖vquerylicensePlateCode，刪除城市中文名為"佛山"的記錄。程序代碼如下：DELETEFROMvquerylicensePlateCodeWHERE城市中文名="佛山";SELECT*FROMvquerylicensePlateCodeWHERE城市中文名="佛山";SELECT*FROMareaWHEREchineseName="佛山";不僅刪除了視圖vquerylicensePlateCode中的記錄，還刪除了基本表area中的數(shù)據(jù)，運行結(jié)果如圖10-9所示。測試完后，將刪除的數(shù)據(jù)補回，代碼如下：INSERTINTOareaVALUES("5880","佛山","Foshan，F(xiàn)atshan","禪城","廣東省中部",3797.72,9498900,"0757","粵E");SELECT*FROMareaWHEREchineseName="佛山";二、通過視圖更新數(shù)據(jù)任務實施任務10-6通過視圖添加數(shù)據(jù)。向視圖vquerylicensePlateCode中添加數(shù)據(jù)"測試城市"和"粵Z"。任務10-7通過視圖修改數(shù)據(jù)。通過視圖vqueryparticipant，先將參與者姓名改為“李小欣”，再修改為原來的內(nèi)容。任務10-8通過視圖刪除數(shù)據(jù)。先向基本表area中添加一條臨時記錄，再通過視圖vquerylicensePlateCode刪除該記錄。03鞏固與小結(jié)三、鞏固與小結(jié)任務分析（1）視圖的內(nèi)涵和優(yōu)點。（2）創(chuàng)建視圖：CREATEVIEW視圖名[(字段列表)]ASSELECT語句WITHCHECKOPTION。（3）管理視圖。 打開視圖：SELECT*FROM視圖名。 查看視圖結(jié)構(gòu)：DESCRIBE視圖名。 查看視圖詳細信息：SHOWCREATEVIEW視圖名。 查看所有視圖：SHOWTABLES。 修改視圖：ALTERVIEW視圖名ASSELECT語句。 刪除視圖：DROPVIEW視圖名。（4）使用Navicat操作視圖。（5）通過視圖更新數(shù)據(jù)：修改數(shù)據(jù)、刪除數(shù)據(jù)和添加數(shù)據(jù)。04任務訓練四、任務訓練任務分析（1）創(chuàng)建視圖vuserlist，顯示點餐系統(tǒng)的用戶名單，如用戶編號、用戶名和用戶類型。（2）創(chuàng)建視圖vcategorylist，顯示點餐系統(tǒng)的菜品分類列表，如分類編號、分類名稱、分類創(chuàng)建時間、創(chuàng)建人姓名和圖標地址。（3）創(chuàng)建視圖vfoodlist，顯示點餐系統(tǒng)的菜品列表，如菜品編號、菜品名稱、菜品標簽、菜品詳情描述、菜品創(chuàng)建時間、創(chuàng)建人姓名、刪除標識、所屬分類名稱、菜品圖片地址和菜品價格。（4）創(chuàng)建視圖vorderlist，顯示點餐系統(tǒng)的訂單列表，如訂單編號、餐桌名稱、訂單創(chuàng)建時間、創(chuàng)建人姓名、訂餐人、聯(lián)系電話、用餐時間、訂單總價和訂單狀態(tài)。（5）創(chuàng)建視圖vpricelist，顯示點餐系統(tǒng)的顧客消費列表，如訂單編號、餐桌名稱和訂餐人訂單總價。（6）創(chuàng)建視圖vorderDetaillist，顯示點餐系統(tǒng)的訂單詳情列表，如編號、訂單名稱、菜品名稱和菜品數(shù)量。四、任務訓練任務分析任務反思（1）記錄在任務完成過程中遇到的問題，應如何解決？（2）是否解決了一些歷史問題，是如何解決的？（3）記錄在任務完成過程中的成功經(jīng)驗。（4）思考任務解決方案還存在哪些漏洞，應如何完善？感謝您的觀看數(shù)據(jù)庫技術及應用項目11數(shù)據(jù)庫編程負責前端開發(fā)工作的老李告訴小王，如果在客戶端實現(xiàn)數(shù)據(jù)處理，那么每個客戶端程序都進行處理，需要重復開發(fā)，工作量比較大，建議小王將某些功能移到數(shù)據(jù)庫服務器上實現(xiàn)，客戶端直接調(diào)用服務器相關資源實現(xiàn)特定的數(shù)據(jù)處理，這樣只需要開發(fā)一次，能有效提高工作效率。通過調(diào)查和學習，小王決定在粵文創(chuàng)項目中，通過函數(shù)來優(yōu)化某些功能實現(xiàn)，通過存儲過程和觸發(fā)器來完善數(shù)據(jù)處理和數(shù)據(jù)一致性，通過事務提升數(shù)據(jù)的安全性和一致性。工作情境目錄01應用函數(shù)02使用存儲過程04應用事務06任務訓練03應用觸發(fā)器05鞏固與小結(jié)01應用函數(shù)一、應用函數(shù)任務分析大家非常佩服程序員，在很短的時間內(nèi)就能編寫出大量的代碼，但是很多代碼其實是重復的。例如，在一個項目中，有10000個比較幾個數(shù)的大小的功能需求，那么比較幾個數(shù)的大小的程序代碼需要出現(xiàn)10000次，除了第1次，后面還要重復9999次?？梢韵劝驯容^幾個數(shù)的大小的程序代碼定義為函數(shù)，再直接調(diào)用這個函數(shù)10000次就可以實現(xiàn)用戶的功能需求，這樣不僅能大大減少代碼量，還方便維護程序。小王對粵文創(chuàng)進行后得到的任務清單如下。任務編號任務內(nèi)容任務11-1設計計算體重指數(shù)BMI值的函數(shù)任務11-2設計根據(jù)不同時間提示不同問候語的函數(shù)任務11-3設計抽獎函數(shù)拓展任務11-1粵文創(chuàng)推出健康咨詢機器人拓展任務11-2粵文創(chuàng)推出生日送生肖禮拓展任務11-3粵文創(chuàng)為用戶昵稱設計加密算法一、應用函數(shù)知識儲備1、SQL函數(shù)的簡介SQL函數(shù)是指能完成特定功能的一組SQL語句。如果沒有函數(shù)，那么所有代碼集中在一起，從上到下按語句執(zhí)行。函數(shù)相當于把特定功能的語句組單獨封裝在一起，變成一個相對獨立的程序，即把原來的程序分為兩部分，分出來的子程序叫作函數(shù)，原來剩下的語句組稱為主程序，主程序需要運行這個函數(shù)稱為函數(shù)調(diào)用，函數(shù)完成特定功能后把結(jié)果告之主程序稱為返回值。假設小王到飯店吃飯時點了白切雞這道菜，那么小王是主程序，酒店是函數(shù)，小王點菜就是函數(shù)調(diào)用，飯店服務員端上的白切雞就是返回值。MySQL本身提供了許多函數(shù)，一般稱為系統(tǒng)函數(shù)或內(nèi)部函數(shù)，如SUM()函數(shù)、MIN()函數(shù)等。常見的系統(tǒng)函數(shù)及其使用方法請參考附錄B。當調(diào)用系統(tǒng)函數(shù)無法解決用戶需求時，需要根據(jù)用戶需求定義新的函數(shù)，即用戶可以自已定義函數(shù)。自定義函數(shù)需要先創(chuàng)建再調(diào)用。一、應用函數(shù)知識儲備2、創(chuàng)建與調(diào)用沒有參數(shù)的函數(shù)1）創(chuàng)建函數(shù)創(chuàng)建函數(shù)使用CREATEFUNCTION語句，語法格式如下：CREATEFUNCTION函數(shù)名()RETURNS返回值類型DETERMINISTIC或NOSQL或READSSQLDATA函數(shù)體;需要說明以下幾點。在創(chuàng)建函數(shù)前，需要設置好當前數(shù)據(jù)庫，函數(shù)名一般由字母、數(shù)字和下畫線組成，建議前面加前綴fun_。函數(shù)體一般以BEGIN開始，以END結(jié)束，兩者之間是函數(shù)功能代碼。關鍵字RETURNS后面已加“S”，在函數(shù)體中用關鍵字RETURN返回指定值。DETERMINISTIC表示確定的，NOSQL表示沒有SQl語句不修改數(shù)據(jù)，READSSQLDATA只讀取數(shù)據(jù)不修改數(shù)據(jù)，一般選擇NOSQL，如果不想選擇，那么可以執(zhí)行語句setgloballog_bin_trust_function_creators=TRUE;。一、應用函數(shù)知識儲備2、創(chuàng)建與調(diào)用沒有參數(shù)的函數(shù)在默認情況下，MySQL的語句以分號結(jié)束，即系統(tǒng)遇到分號時執(zhí)行該語句。如果需要使用定義新的語句結(jié)束符，那么可以使用DELIMITER命令實現(xiàn)。DELIMITER命令的語法格式如下：DELIMITER語句結(jié)束符需要注意的是，DELIMITER和語句結(jié)束符之間至少要有一個空格。示例11-1創(chuàng)建函數(shù)fun_product()，計算123*987的積。一、應用函數(shù)知識儲備2、創(chuàng)建與調(diào)用沒有參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_product()RETURNSINTNOSQLBEGINDECLARExINT;RETURN123*987;END//一、應用函數(shù)知識儲備2、創(chuàng)建與調(diào)用沒有參數(shù)的函數(shù)2）調(diào)用函數(shù)完成函數(shù)創(chuàng)建，相當于廚師學會了做菜，沒有人請他做菜時，他空有一身本事，有人點餐時，廚師才真正工作，函數(shù)也一樣，創(chuàng)建之后需要調(diào)用，調(diào)用函數(shù)才能真正完成特定的功能。函數(shù)可以直接在表達式中使用，具體如下：SELECT函數(shù)名();示例11-2調(diào)用函數(shù)fun_product。程序代碼如下：SELECTfun_product()//DELIMITER;思考：使用fun_product函數(shù)只能計算123*987的積，能否把該函數(shù)擴展為計算兩個整數(shù)的乘積？一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)1）常量常量一般包括字符串常量、數(shù)值常量、日期和時間常量、布爾值、空值等，如表11-1所示。常量的類型說明應用示例字符串常量必須用單引號或雙引號引起來'a'和"a"數(shù)值常量包括整型常量和包含小數(shù)點的浮點型常量123、987和1.2日期和時間常量必須符合日期和時間的標準規(guī)范，必須用單引號或雙引號引起來"2023-2-1413:14:00""2023-5-1"布爾常量包括TRUE和FALSE兩個值TRUE和FALSE空值空值NULL表示“沒有值”，可以使用各種數(shù)據(jù)類型NULL一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)2）變量變量用于臨時存儲數(shù)據(jù)，其值在程序運行過程中可能會發(fā)生變化。變量分為系統(tǒng)變量和用戶變量。MySQL中有一些特定的變量，當MySQL數(shù)據(jù)庫服務器啟動時，其會讀這些設置以決定如何進行下一步，這些設置就是系統(tǒng)變量。有些系統(tǒng)變量以“@@”為前綴，如@@Version等，有些系統(tǒng)變量不以“@@”為前綴，如Current_Date等。示例11-3通過系統(tǒng)變量@@Version、Current_Date、Current_Time及Current_User查看系統(tǒng)版本、當前日期、當前時間和當前用戶。程序代碼如下：SELECT@@Version,Current_Date,Current_Time,Current_User;用戶變量即用戶自已定義的變量。在使用變量前應用DECLARE聲明，語法格式如下：DECLARE變量名數(shù)據(jù)類型[DEFAULT默認值];變量可以使用SET賦值，語法格式如下：SET變量名=值;示例11-4創(chuàng)建函數(shù)fun_productNew，計算123和987的乘積，并調(diào)用該函數(shù)。一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_productNew()RETURNSBIGINTNOSQLBEGINDECLAREnum1,num2INT;DECLAREsBIGINT;SETnum1=123,num2=987;SETs=num1*num2;RETURNs;END//SELECTfun_productNew()//DELIMITER;在一般情況下，盡管一個簡單變量的值可以改變，但任何時刻只能保存一個值。程序所需的變量個數(shù)與數(shù)據(jù)量有關，計算兩個整數(shù)的乘積，需要兩個數(shù)，因此需要兩個變量，兩個數(shù)的乘積也是一個數(shù)值，一般保存在一個新變量中，即需要3個變量。一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)3）參數(shù)盡管函數(shù)fun_productNew通過變量保存數(shù)據(jù)，但仍然無法實現(xiàn)在函數(shù)調(diào)用時才輸入值的功能，因為在定義函數(shù)fun_productNew時，為兩個乘數(shù)賦值，相乘的兩個數(shù)與函數(shù)調(diào)用無關。在定義函數(shù)時，可以設置形式參數(shù)，在調(diào)用函數(shù)時通過實際參數(shù)將值傳給形式參數(shù)，完成數(shù)據(jù)處理。創(chuàng)建帶參數(shù)函數(shù)的語法格式如下：CREATEFUNCTION函數(shù)名(形式參數(shù))RETURNS返回值類型DETERMINISTIC或NOSQL或READSSQLDATA

函數(shù)體;調(diào)用帶參數(shù)函數(shù)的語法格式如下：SELECT函數(shù)名(實際參數(shù));示例11-5創(chuàng)建函數(shù)fun_productExtend，計算兩個整數(shù)的乘積，并調(diào)用該函數(shù)計算123和987的乘積。一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)程序代碼如下：DELIMITER//CREATEFUNCTIONfun_productExtend(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;SETs=num1*num2;RETURNs;END//SELECTfun_productExtend(123,987)//DELIMITER;思考：使用fun_productExtend函數(shù)能計算兩個整數(shù)的乘積，能否實現(xiàn)只計算兩個正整數(shù)的乘積？一、應用函數(shù)知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的函數(shù)4）運算符與表達式MySQL中的運算符主要包括算術運算符、比較運算符、邏輯運算符和位運算符，其中前3類最常用。MySQL中的表達式由數(shù)字、運算符、數(shù)字分組符號、自由變量和約束變量等，以能求得數(shù)值的有意義排列方法所得的組合，如(1+2)*3。表達式中可以使用多種運算符，但不同運算符有對應的優(yōu)先級。關于比較運算符和邏輯運算符的相關內(nèi)容請參考項目5，其它常見運算符及其優(yōu)先級請參考附錄C。一、應用函數(shù)知識儲備4、查看與刪除函數(shù)1）查看函數(shù)創(chuàng)建好的函數(shù)第一次運行時正常執(zhí)行，如果再執(zhí)行一次，那么系統(tǒng)提示函數(shù)已存在，會出錯，如再創(chuàng)建fun_productExtend函數(shù)?？梢允褂肧HOWCREATEFUNCTION語句查看已存在的函數(shù)，了解函數(shù)的定義，語法格式如下：SHOWCREATEFUNCTION函數(shù)名;示例11-6查看fun_productExtend函數(shù)的定義。程序代碼如下：SHOWCREATEFUNCTIONfun_productExtend;2）刪除函數(shù)不需要的函數(shù)可以刪除。刪除函數(shù)使用DROPFUNCTION，語法格式如下：DROPFUNCTION[IFEXISTS]函數(shù)名;說明：在刪除函數(shù)時，如果函數(shù)不存在就會報錯，所以刪除函數(shù)時可以加上關鍵字IFEXISTS。示例11-7刪除fun_productExtend函數(shù)。程序代碼如下：DROPFUNCTIONIFEXISTSfun_productExtend;一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計復雜程序一般包括3種基本結(jié)構(gòu)，分別為順序結(jié)構(gòu)、選擇結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。順序結(jié)構(gòu)就是從上至下逐行執(zhí)行，不需要專門的控制語句；選擇結(jié)構(gòu)根據(jù)判斷結(jié)果執(zhí)行不同的語句，一般用IF和CASE等語句實現(xiàn)；循環(huán)語句在滿足循環(huán)條件時反復執(zhí)行，不滿足循環(huán)條件時結(jié)束循環(huán)，一般用WHILE、REPEAT和LOOP等語句實現(xiàn)。1）簡單IF語句使用IF語句需要解決幾個關鍵問題：先設置一個判斷條件，再確定條件成立時需要做什么，條件不成立時需要做什么。IF語句的語法格式如下：IF條件表達式THEN

條件成立時執(zhí)行的語句ELSE

條件不成立時執(zhí)行的語句ENDIF;示例11-8創(chuàng)建函數(shù)fun_productPositive，計算兩個正整數(shù)的乘積，如果不是兩個正整數(shù)就返回-1，并調(diào)用該函數(shù)計算-123和987的乘積。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計程序代碼如下：DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositive;CREATEFUNCTIONfun_productPositive(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;IFnum1>0ANDnum2>0THENSETs=num1*num2;ELSESETs=-1;ENDIF;RETURNs;END//SELECTfun_productPositive(-123,987)//DELIMITER;num1>0ANDnum2>0是判斷條件，若滿足判斷條件則計算兩個整數(shù)的乘積，若不滿足判斷條件則結(jié)果為-1。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計2）嵌套的IF語句一個簡單的IF語句最多只能表示兩種情況，即條件成立和條件不成立。如果有多種可能的情況，那么可以通過嵌套的IF語句來實現(xiàn)。IF語句可以多層嵌套。嵌套的IF語句的語法格式如下：IF條件表達式1THEN

條件表達式1成立時執(zhí)行的語句ELSEIF條件表達式2THEN

條件表達式1不成立且條件表達式2成立時執(zhí)行的語句ELSE

條件表達式2不成立時執(zhí)行的語句…ENDIF;示例11-9定義函數(shù)fun_productPositiveNew，計算兩個正整數(shù)的積，若兩個都是正整數(shù)則計算，若只有一個正整數(shù)則返回-1，若兩個都不是正整數(shù)則返回-2，并調(diào)用該函數(shù)計算-123和-987的乘積。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositiveNew;CREATEFUNCTIONfun_productPositiveNew(num1INT,num2INT)RETURNSBIGINTNOSQLBEGINDECLAREsBIGINT;IFnum1>0ANDnum2>0THENSETs=num1*num2;ELSEIFnum1<0ANDnum2<0THENSETs=-2;ELSESETs=-1;ENDIF;RETURNs;END//SELECTfun_productPositiveNew(-123,-987)//DELIMITER;一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計如果滿足num1>0ANDnum2>0，那么說明兩個數(shù)都是正整數(shù)，否則有3種情況：兩個整數(shù)都不是正整數(shù)；第1個整數(shù)不是正整數(shù)但第2個整數(shù)是正整數(shù)；第1個整數(shù)是正整數(shù)但第2個整數(shù)不是正整數(shù)。在這3種情況下判斷條件num1<0ANDnum2<0，若滿足條件則說明兩個整數(shù)都不是正整數(shù)，這個條件再否則，就剩下2種情況：第1個整數(shù)不是正整數(shù)但第2個整數(shù)是正整數(shù)；第1個整數(shù)是正整數(shù)但第2個整數(shù)不是正整數(shù)。這兩種情況都只有一個正整數(shù)，不需要再進一步處理。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計3）CASE語句當選擇的分支比較多時，還可以使用CASE語句，語法格式如下：CASE WHEN條件表達式結(jié)果1THEN語句1 WHEN條件表達式結(jié)果2THEN語句2 … WHEN條件表達式結(jié)果nTHEN語句nELSE其他情況執(zhí)行的語句ENDCASE;示例11-10利用CASE語句改寫fun_productPositiveNew函數(shù)。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計DELIMITER//DROPFUNCTIONIFEXISTSfun_productPositiveNew;CREATEFUNCTIONfun_productPositiveNew(num1INT,num2INT)RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINT; CASE WHENnum1>0ANDnum2>0THENSETs=num1*num2; WHENnum1<0ANDnum2<0THENSETs=-2; ELSESETs=-1; ENDCASE; RETURNs;END//SELECTfun_productPositiveNew(-123,-987)//DELIMITER;一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計4）WHILE語句使用WHILE語句需要解決幾個主要問題：首先設置循環(huán)條件，然后確定循環(huán)體（要反復做什么），最后確定循環(huán)控制變量的步長值。在循環(huán)之前一般需要設置控制變量和其他變量的初值。WHILE語句的語法格式如下：循環(huán)控制變量賦初值;WHILE條件表達式DO

循環(huán)體語句ENDWHILE;示例11-11定義fun_customSum函數(shù)，實現(xiàn)1～100所有正整數(shù)之和。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計程序代碼如下：DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; WHILEi<=100DO SETs=s+i; SETi=i+1; ENDWHILE; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計這是一個典型的循環(huán)結(jié)構(gòu)，需要設置一個循環(huán)變量i，其初值為i=1，條件為i<=100，步長值為1，即i=i+1。求1～100共100個數(shù)的和，反復做的工作是兩個數(shù)求和，即每次利用前面的結(jié)果加上當前整數(shù)?？稍O置一個求和變量s，求和可用s=s+i表示，第1個數(shù)求和時，要設置s的初始值s=0。初始值只需要設置1次，因此i=1和s=0應放在循環(huán)之前，而s=s+i和i=i+1要反復執(zhí)行，應放在循環(huán)體之內(nèi)。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計5）REPEAT語句REPEAT語句與WHILE語句的功能相似，只是WHILE語句先檢查循環(huán)條件，滿足條件才執(zhí)行循環(huán)體，而REPEAT語句先執(zhí)行循環(huán)體后進行條件判斷，不滿足循環(huán)條件時繼續(xù)循環(huán)，滿足循環(huán)條件時結(jié)束循環(huán)。使用REPEAT語句實現(xiàn)循環(huán)時，循環(huán)體至少要執(zhí)行1次。REPEAT語句的語法格式如下：循環(huán)控制變量賦初值;REPEAT

循環(huán)體語句UNTIL條件表達式ENDREPEAT;示例11-12使用REPEAT語句改寫fun_customSum函數(shù)。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; REPEAT SETs=s+i; SETi=i+1; UNTILi>100 ENDREPEAT; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計6）LOOP語句LOOP語句的語法格式如下：循環(huán)控制變量賦初值;開始標號:LOOP

循環(huán)體語句ENDLOOP;說明：LOOP語句不能自動結(jié)束循環(huán)，在循環(huán)體中需要設置退出循環(huán)的條件，當滿足循環(huán)結(jié)束條件時，使用LEAVE語句跳出循環(huán)控制。LEAVE語句的語法格式如下：LEAVE標號;示例11-13使用LOOP語句改寫fun_customSum函數(shù)。一、應用函數(shù)知識儲備5、復雜函數(shù)體的設計DELIMITER//DROPFUNCTIONIFEXISTSfun_customSum;CREATEFUNCTIONfun_customSum()RETURNSBIGINTNOSQLBEGIN DECLAREsBIGINTDEFAULT0; DECLAREiINTDEFAULT1; Lsum:LOOP SETs=s+i; SETi=i+1; IFi>100THEN LEAVELsum; ENDIF; ENDLOOP; RETURNs;END//SELECTfun_customSum()//DELIMITER;一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)1）查看函數(shù)（1）啟動Navicat，先選擇指定連接，再選擇指定數(shù)據(jù)庫。（2）單擊“函數(shù)”圖標，顯示數(shù)據(jù)庫中當前所有函數(shù)列表，如圖11-9所示。一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)2）新建函數(shù)示例11-14新建函數(shù)pro_addition，計算兩個整數(shù)之和。（1）選中圖11-9中的一個函數(shù)或在空白處右擊，在快捷菜單中選擇“新建函數(shù)”命令。（2）打開“函數(shù)向?qū)А贝翱?，輸入函?shù)名，選中“函數(shù)”單選按鈕，如圖11-10所示，單擊“完成”按鈕。（3）單擊“下一步”按鈕，設置函數(shù)參數(shù)?！啊北硎驹黾訁?shù)，“”表示刪除參數(shù)，“”和“”分別表示向上和向下移動選中的參數(shù)。設置2個整型參數(shù)num1和num2，但要一個一個地增加，如圖11-11所示。一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)2）新建函數(shù)（4）單擊“下一步”按鈕，設置函數(shù)返回值（可設置返回值的類型、長度、小數(shù)位、字符集等，如圖11-12所示。（5）在完成類型選擇、參數(shù)設置、返回值類型設置后，單擊“完成”按鈕，返回Navicat主界面，已自動生成函數(shù)，再根據(jù)情況補充參數(shù)定義，完善函數(shù)體，如圖11-13所示。單擊代碼框左上方的“保存”按鈕保存函數(shù)。一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)3）運行函數(shù)（1）保存函數(shù)后，單擊圖11-13中代碼框上方的“運行”按鈕，打開“輸入?yún)?shù)”對話框，輸入?yún)?shù)后，單擊“確定”按鈕，如圖11-14所示。（2）運行結(jié)果如圖11-15所示。單擊“信息”選項卡，查看運行信息和運行時間，如圖11-16所示。單擊“定義”選項卡，查看程序代碼。一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)4）關閉函數(shù)窗口將光標移到文檔選項卡上，顯示“關閉”按鈕，如圖11-17所示，單擊該按鈕關閉函數(shù)文檔窗口。需要注意的是，“關閉”按鈕平時是隱藏的。一、應用函數(shù)知識儲備6、使用Navicat工具管理函數(shù)5）其他操作在圖11-13中，單擊“對象”選項卡，顯示函數(shù)列表，選中指定函數(shù)并右擊，快捷菜單中包括如下命令?！霸O計函數(shù)”命令：選擇該命令可以打開“函數(shù)定義”窗口，在該窗口中可以修改和保存函數(shù)。另外，雙擊函數(shù)名也可以打開“函數(shù)定義”窗口?！皠h除函數(shù)”命令：選擇該命令可以刪除指定函數(shù)。“重命名”命令：選擇該命令可以修改函數(shù)名稱。一、應用函數(shù)任務實施任務11-1設計計算體重指數(shù)BMI值的函數(shù)。設計函數(shù)fun_BMI，根據(jù)體重（單位：千克）和身高（單位：米）計算體重指數(shù)BMI值，計算方法為體重（千克）除以身高（米）的平方。任務11-2設計根據(jù)不同時間提示不同問候語的函數(shù)?；浳膭?chuàng)App需要根據(jù)用戶登錄時間提示不同的問候語，登錄時，時間讀取系統(tǒng)當前時間的小時值，問候語生成規(guī)則如下：07:00—12:00顯示“上午好”，13:00—19:00顯示“下午好”，00:00—06:00和20:00—24:00顯示“晚上好”。設計函數(shù)fun_greetings實現(xiàn)以上功能。任務11-3設計抽獎函數(shù)。粵文創(chuàng)想設計一個抽獎函數(shù)fun_prize，抽獎規(guī)則如下：每次隨機產(chǎn)生一個數(shù)字，連續(xù)產(chǎn)生次數(shù)由用戶決定。一、應用函數(shù)任務實施拓展任務11-1粵文創(chuàng)推出健康咨詢機器人。機器人可以根據(jù)體重（單位：千克）和身高（單位：米）判斷用戶的健康情況，并給出建議：如果體重指數(shù)BMI值小于18.5，那么提示“體重過低，可能存在其他健康問題”；如果體重指數(shù)BMI值介于18.5和23.9之間，那么提示“正常體重，請繼續(xù)保持良好的生活方式”；如果體重指數(shù)BMI值介于24.0和27.9之間，那么提示“超重，請通過合理飲食、有效運動達到理想體重”；如果體重指數(shù)BMI值大于或等于28，那么提示“肥胖，請盡快來用合理飲食、運動能量平衡的治療方法來減肥吧”。設計函數(shù)fun_consulting實現(xiàn)以上功能。拓展任務11-2粵文創(chuàng)推出生日送生肖禮。設計函數(shù)fun_zodiac實現(xiàn)根據(jù)出生年份查詢生肖的功能。拓展任務11-3粵文創(chuàng)為用戶昵稱設計加密算法。為了提高系統(tǒng)安全性，粵文創(chuàng)為用戶昵稱（包含英文字母和數(shù)字）設計了加密算法，英文字母改變大小寫，非0數(shù)字轉(zhuǎn)換為對應的補數(shù)，其他字符不變。設計函數(shù)fun_encryption實現(xiàn)以上功能。02使用存儲過程二、使用存儲過程任務分析函數(shù)會向調(diào)用者返回一個結(jié)果值，參數(shù)類型也只有一種，函數(shù)的限制比較多，很多時候不能滿足用戶需求。而存儲過程的參數(shù)類型有3種，包括輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，使用更加靈活。小王對粵文創(chuàng)進行分析后得到的任務清單如下。任務編號任務內(nèi)容任務11-4創(chuàng)建存儲過程pro_cleanname，對粵文創(chuàng)的user表中的userName進行清洗，把姓名只有一個字的用戶刪除，并返回刪除記錄數(shù)任務11-5創(chuàng)建存儲過程pro_cleanage，對粵文創(chuàng)的user表中的birthday進行清洗，合理的年齡范圍為[0,150]，刪除不合理的記錄，并返回刪除記錄數(shù)任務11-6創(chuàng)建存儲過程pro_queryuser，輸入用戶姓名，在粵文創(chuàng)的user表中查詢該用戶的fkTitle、gender、nation、birthday、nativePlace和phone拓展任務11-4創(chuàng)建存儲過程pro_supplementnation，對粵文創(chuàng)的user表中的nation進行清洗，為沒有“族”字的記錄補充“族”字，并返回修改記錄數(shù)拓展任務11-5創(chuàng)建存儲過程pro_cleannation，對粵文創(chuàng)的user表中的nation進行清洗，將有錯誤的記錄顯示出來，并返回輸入錯誤的用戶數(shù)拓展任務11-6創(chuàng)建存儲過程pro_cleanfkTitle，對粵文創(chuàng)的user表中的fkTitle進行清洗，刪除不滿足職稱和年齡關系的記錄二、使用存儲過程知識儲備1、存儲過程簡介存儲過程是一組為了完成特定功能的SQL語句集，是數(shù)據(jù)庫中的一個重要對象。在數(shù)據(jù)量特別大的情況下利用存儲過程可以顯著提升效率。1）存儲過程的優(yōu)點（1）重復使用：存儲過程可以重復使用，這不僅減少了開發(fā)人員的工作量，還提高了效率。（2）減少網(wǎng)絡流量：存儲過程位于服務器上，客戶端通過存儲過程和參數(shù)調(diào)用返回結(jié)果，不需要將大量原始數(shù)據(jù)傳給客戶端，因此降低了網(wǎng)絡傳輸?shù)臄?shù)據(jù)量。（3）安全性：存儲過程的參數(shù)化可以防止SQL注入，可以將Grant、Deny和Revoke權限應用于存儲過程之中。二、使用存儲過程知識儲備1、存儲過程簡介2）存儲過程的缺點（1）存儲過程存儲在數(shù)據(jù)庫服務端上，需要在數(shù)據(jù)庫服務器環(huán)境中調(diào)試，項目上線后，存儲過程的調(diào)試和維護相對來說比較麻煩。（2）存儲過程在數(shù)據(jù)庫服務器環(huán)境中運行，當進行版本差別很大的服務器更新，甚至更換數(shù)據(jù)庫服務器類型時，數(shù)據(jù)庫的遷移比較麻煩，甚至可能需要重寫存儲過程。二、使用存儲過程知識儲備2、創(chuàng)建與調(diào)用無參數(shù)的存儲過程1）創(chuàng)建無參數(shù)的存儲過程使用PROCEDURE創(chuàng)建存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名()存儲過程體需要說明以下幾點。創(chuàng)建函數(shù)之前需要設置好當前數(shù)據(jù)庫，存儲過程名一般由字母、數(shù)字和下畫線組成。存儲過程名建議以pro_作為前綴，從而與其他數(shù)據(jù)庫對象進行區(qū)分。存儲過程體是存儲過程的主體部分，可以充分使用本項目任務1介紹的變量、常量、控制語句和函數(shù)等，存儲過程體一般放在BEGIN…END語句之中。二、使用存儲過程知識儲備2、創(chuàng)建與調(diào)用無參數(shù)的存儲過程2）調(diào)用無參數(shù)的存儲過程使用CALL調(diào)用存儲過程的語法格式如下：CALL存儲過程名();示例11-15創(chuàng)建存儲過程pro_QueryAll()，先查詢user表的前3條記錄，再運行該存儲過程。二、使用存儲過程知識儲備2、創(chuàng)建與調(diào)用無參數(shù)的存儲過程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryAll()BEGIN SELECT*FROMuserLIMIT3;END//CALLpro_QueryAll()//DELIMITER;這個存儲過程其實是將查詢放置在存儲過程體中，有查詢知識和技能的支撐，只要簡單地添加存儲過程固定語法即可完成任務。二、使用存儲過程知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲過程1）創(chuàng)建帶參數(shù)的存儲過程創(chuàng)建帶參數(shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名(形參列表)存儲過程體需要說明以下幾點。形參有3種類型，分別為輸入?yún)?shù)、輸出參數(shù)和輸入/輸出參數(shù)，其對應的關鍵字分別為IN、OUT和INOUT。存儲過程可帶一個或多個參數(shù)，每個參數(shù)必須指明參數(shù)的參數(shù)類型、參數(shù)名稱和數(shù)據(jù)類型。若沒有標明參數(shù)類型，則采用默認值，即輸入?yún)?shù)。二、使用存儲過程知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲過程2）調(diào)用帶參數(shù)的存儲過程調(diào)用帶參數(shù)的存儲過程的語法格式如下：CALL存儲過程名(實參列表);需要說明以下幾點。實參的數(shù)量與順序必須形參的一致。輸入?yún)?shù)需要先賦值，輸出參數(shù)不需要事先賦值。如果沒有聲明直接使用用戶自定義變量，那么可以在變量前面加“@”。示例11-16創(chuàng)建存儲過程pro_QueryN，查詢前n個用戶的信息，n由用戶調(diào)用時輸入。二、使用存儲過程知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲過程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryAllN(nINT)BEGIN SELECT*FROMuserLIMITn;END//CALLpro_QueryAllN(2)//DELIMITER;由用戶控制顯示記錄數(shù)目，并且更具靈活性。二、使用存儲過程知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲過程示例11-17創(chuàng)建存儲過程pro_QueryByName，輸入用戶姓名，查詢其編號、性別、電話，并調(diào)用該存儲過程。二、使用存儲過程知識儲備3、創(chuàng)建與調(diào)用帶參數(shù)的存儲過程程序代碼如下：DELIMITER//CREATEPROCEDUREpro_QueryByName(INiNameVARCHAR(8),OUToIDSMALLINT,OUToGenderVARCHAR(2),OUToPhoneVARCHAR(13))BEGIN SELECTuserId,gender,phoneINTOoid,oGender,oPhoneFROMuserWHEREusername=iName;END//CALLpro_QueryByName(“張宏峰”,@ID,@gender,@phone);SELECT@ID,@gender,@phone;CALLpro_QueryByName(“陳小鋒”,@ID,@gender,@phone);SELECT@ID,@gender,@phone//DELIMITER;二、使用存儲過程知識儲備4、管理存儲過程1）查詢存儲過程查詢當前數(shù)據(jù)庫包括系統(tǒng)存儲過程在內(nèi)的所有存儲過程信息，即存儲過程所屬數(shù)據(jù)庫，以及存儲過程的名稱、類型和各種狀態(tài)值等，語法格式如下：SHOWPROCEDURESTATUS;查詢存儲過程，語法格式如下：SHOWCREATEPROCEDURE存儲過程名;示例11-18查詢存儲過程pro_QueryAllN。程序代碼如下：SHOWCREATEPROCEDUREpro_QueryAllN;2）刪除存儲過程ROPPROCEDURE刪除存儲過程，語法格式如下：可以使用DDROPPROCEDURE[IFEXISTS]存儲過程名;刪除不存在的存儲過程會報錯，在刪除前可以使用[IFEXISTS]判斷存儲過程是否存在。示例11-19刪除存儲過程pro_QueryAll()。程序代碼如下：DROPPROCEDUREIFEXISTSpro_QueryAll;二、使用存儲過程知識儲備4、使用Navicat管理存儲過程在Navicat中，函數(shù)與存儲過程在同一位置顯示。要創(chuàng)建存儲過程，必須在“函數(shù)向?qū)А贝翱谥羞x中“過程”單選按鈕，其他與創(chuàng)建函數(shù)相同。存儲過程的運行、修改、查看、重命名和刪除等操作與函數(shù)的相關操作一樣。二、使用存儲過程知識儲備5、使用游標1）游標的簡介游標是處理數(shù)據(jù)的一種方法。為了查看或處理結(jié)果集中的數(shù)據(jù)，游標提供了在結(jié)果集中一次一行或多行前進或向后瀏覽數(shù)據(jù)的能力。游標在部分資料中也被稱為光標。游標的使用流程為聲明游標、打開游標、讀取游標和關閉游標。2）聲明游標聲明游標的語法格式如下：DECLARE游標名CURSORFOR查詢語句;說明：將游標與指定的查詢結(jié)果關聯(lián)起來，查詢語句不能使用INTO關鍵字。3）打開游標打開游標的語法格式如下：OPEN游標名;二、使用存儲過程知識儲備5、使用游標4）讀取游標讀取游標的語法格式如下：FETCH游標名INTO變量列表說明：將游標指向的一行記錄或多個數(shù)據(jù)賦給對應的變量，變量的個數(shù)必須與游標的數(shù)據(jù)個數(shù)一致。變量列表中的各個變量必須事先定義好，并且變量的類型與必須與對應字段的類型一致。5）關閉游標關閉游標的語法格式如下：CLOSE游標名;示例11-20創(chuàng)建存儲過程pro_QueryName，查詢姓名最長的用戶的姓名。二、使用存儲過程知識儲備5、使用游標DELIMITER//DROPPROCEDUREIFEXISTSpro_QueryName;CREATEPROCEDUREpro_QueryName(OUToNameVARCHAR(10))BEGIN DECLAREtname,lnameVARCHAR(10); DECLAREdoneINTDEFAULT0; DECLAREcur_nameCURSORFORSELECTuserNameFROMuser; DECLAREcontinueHANDLERFORNOTFOUNDSETdone=1; OPENcur_name; FETCHcur_nameINTOtname; SETlname=tname; WHILE(NOTdone)DO IFCHAR_LENGTH(lname)<CHAR_LENGTH(tname)THEN SETlname=tname; ENDIF; FETCHcur_nameINTOtname;

二、使用存儲過程知識儲備5、使用游標 ENDWHILE; CLOSEcur_name; SEToName=lname;END//CALLpro_QueryName(@name);SELECT@name//DELIMITER;二、使用存儲過程任務實施任務11-4創(chuàng)建存儲過程pro_cleanname。對粵文創(chuàng)的user表中的userName進行清洗，把姓名只有一個字的用戶刪除，并返回刪除記錄數(shù)。任務11-5創(chuàng)建存儲過程pro_cleanage。對粵文創(chuàng)的user表中的birthday進行清洗，合理的年齡范圍為[0,150]，刪除不合理的記錄，并返回刪除記錄數(shù)。任務11-6創(chuàng)建存儲過程pro_queryuser。輸入用戶姓名，在粵文創(chuàng)的user表中查詢該用戶的fkTitle、gender、nation、birthday、nativePlace和phone。二、使用存儲過程任務實施拓展任務11-6創(chuàng)建存儲過程pro_cleanfkTitle。對粵文創(chuàng)的user表中的fkTitle進行清洗，刪除不滿足職稱和年齡關系的記錄。拓展任務11-4創(chuàng)建存儲過程pro_supplementnation。對粵文創(chuàng)的user表中的nation進行清洗，為沒有“族”字的記錄補充“族”字，并返回修改記錄數(shù)。拓展任務11-5創(chuàng)建存儲過程pro_cleannation。對粵文創(chuàng)的user表中的nation進行清洗，將有錯誤的記錄顯示出來，并返回輸入錯誤的用戶數(shù)。03應用觸發(fā)器三、應用觸發(fā)器任務分析存儲過程的功能非常強大。利用存儲過程能實現(xiàn)很多功能，但存儲過程需要手動調(diào)用，不能自動執(zhí)行。MySQL提供了觸發(fā)器對象。觸發(fā)器是一種特殊的存儲過程，主要用于強制引用完整性，以便在多個表中添加、更新或刪除行時，保留在這些表之間所定義的關系。小王對粵文創(chuàng)進行分析后得到的任務清單如下。任務編號任務內(nèi)容任務11-7通過INSERT對粵文創(chuàng)的工作計劃表plan進行自動檢查，要求計劃發(fā)布時間、計劃審核時間不能晚于操作當前時間任務11-8通過INSEERT觸發(fā)器tri_checkparticipant，對粵文創(chuàng)的工作計劃參與人員表participant進行自動檢查，在任何計劃中每個人只能分配一項，即在一個計劃中工號是唯一的任務11-9通過INSERT觸發(fā)器tri_chechplanall，對粵文創(chuàng)的工作計劃表plan進行自動檢查，要求計劃開始時間早于計劃結(jié)束時間，計劃制訂者和計劃審核者不是同一個人，計劃發(fā)布時間要晚于計劃審核時間拓展任務11-7將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend，并指出具體錯誤三、應用觸發(fā)器知識儲備1、觸發(fā)器簡介觸發(fā)器由一組SQL語句組成，由事件觸發(fā)，能自動執(zhí)行，無須用戶調(diào)用。觸發(fā)器與表的緊密非常關系，可以作為表的一部分創(chuàng)建，常用于保護表中的數(shù)據(jù)或?qū)崿F(xiàn)數(shù)據(jù)的完整性。1）觸發(fā)器的作用（1）觸發(fā)器可在數(shù)據(jù)處理前，用來強制檢驗或轉(zhuǎn)換數(shù)據(jù)。（2）觸發(fā)器發(fā)生錯誤時，異動的結(jié)果會被撤銷。（3）可依照特定的情況替換異動的指令。三、應用觸發(fā)器知識儲備2、創(chuàng)建觸發(fā)器使用CREATETRIGGER可以創(chuàng)建觸發(fā)器，語法格式如下：CREATETRIGGER觸發(fā)器名觸發(fā)時間觸發(fā)事件ON表名FOREACHROW觸發(fā)器執(zhí)行語句需要說明以下幾點。觸發(fā)時間表示觸發(fā)的時機，有兩個選項：一是AFTER，表示在觸發(fā)觸發(fā)器的執(zhí)行語句之后執(zhí)行；二是BEFORE，表示觸發(fā)觸發(fā)器的執(zhí)行語句之前驗證新數(shù)據(jù)是否滿足使用規(guī)則。觸發(fā)事件用來指明在表中執(zhí)行哪類操作時激活觸發(fā)器，有3個選項：一是INSERT事件，表示向表中插入新行時觸活觸發(fā)器；二是DELETE事件，表示從表中刪除一條記錄時觸活觸發(fā)器；三是UPDATE事件，表示表中更新數(shù)據(jù)時觸發(fā)觸發(fā)器。FOREACHROW表示觸發(fā)器執(zhí)行的間隔，對于受觸發(fā)器事件影響的每行都要觸活觸發(fā)器的執(zhí)行語句。觸發(fā)器執(zhí)行語句是觸發(fā)器的主體，即當觸發(fā)器觸活時，真正要執(zhí)行哪些操作。三、應用觸發(fā)器知識儲備2、創(chuàng)建觸發(fā)器觸發(fā)器不能將任何結(jié)果返回到客戶端，不要在觸發(fā)器定義中包含SELECT語句和將數(shù)據(jù)返回客戶端的存儲過程。在觸發(fā)器執(zhí)行語句中，OLD關聯(lián)被刪除或被更新前的記錄，NEW關聯(lián)被插入或被更新后的記錄，在INSERT事件中可以使用NEW，在DELETE事件中可以使用OLD，在UPDATE事件中可以使用NEW、OLD。觸發(fā)器自動執(zhí)行，所以要執(zhí)行觸發(fā)器需要讓相關數(shù)據(jù)表產(chǎn)生指定的事件。觸發(fā)器名一般由字母、數(shù)字和下畫線組成，建議前面加前綴tri_。示例11-21創(chuàng)建觸發(fā)器tri_CheckAge，user表中只能增加18歲以上的用戶。三、應用觸發(fā)器知識儲備2、創(chuàng)建觸發(fā)器程序代碼如下：DELIMITER//CREATETRIGGERtri_CheckAge2BEFOREINSERTONuserFOREACHROWBEGIN IFYEAR(CURDATE())-YEAR(NEW.birthday)<18THEN SIGNALSQLSTATE'45000'SETmessage_text="用戶年齡小于18歲，無法插入。"; ENDIF;END//INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("張建國","助理研究員","男","漢族","1980-1-29","湖南長沙","132123XX321")//INSERTINTOuser(username,fkTitle,gender,nation,birthday,nativePlace,phone)VALUES("李大為","實習研究員","男","漢族","2020-11-19","廣東惠州","136363XX383")//DELIMITER;三、應用觸發(fā)器知識儲備2、創(chuàng)建觸發(fā)器控制數(shù)據(jù)增加需要使用INSERT觸發(fā)器，在插入前應檢查數(shù)據(jù)有效性，需要選擇BEFORE。在觸發(fā)器中，要檢查NEW的birthday值，判斷用戶是否已滿18歲，若未滿18歲，則設置錯誤阻止數(shù)據(jù)插入。當觸發(fā)觸發(fā)器時，第1條插入語句，年齡滿足要求成功插入，第2條年齡不滿足條件，插入失敗，并提示用戶失敗的原因。三、應用觸發(fā)器知識儲備3、管理觸發(fā)器1）查看觸發(fā)器查看所有觸發(fā)器的語法格式如下：SHOWTRIGGERS;查看指定觸發(fā)器的語法格式如下：SELECT*FROMInformation_Schema.TriggerWHERETrigger_Name=觸發(fā)器名稱;2）刪除觸發(fā)器刪除觸發(fā)器的語法格式如下：DROPTRIGGER數(shù)據(jù)庫名.觸發(fā)器名;說明：如果省略數(shù)據(jù)庫名，就表示在當前數(shù)據(jù)庫中刪除指定的觸發(fā)器。示例11-22刪除觸發(fā)器tri_CheckAge。程序代碼如下：DROPTRIGGERIFEXISTStri_CheckAge;三、應用觸發(fā)器知識儲備4、使用Navicat管理觸發(fā)器1）查看觸發(fā)器選中指定表并右擊，選擇“設計表”命令，進入表的設計模式，單擊“觸發(fā)器”選項卡，可以看到當前表的觸發(fā)器，如圖11-23所示，工作區(qū)上方顯示的是觸發(fā)器列表，下方顯示的是選中觸發(fā)器的內(nèi)容。三、應用觸發(fā)器知識儲備4、使用Navicat管理觸發(fā)器2）添加觸發(fā)器單擊觸發(fā)器列表上方的“添加觸發(fā)器”按鈕，在觸發(fā)器列表中新增一行，輸入觸發(fā)器名稱，選擇觸發(fā)時間，根據(jù)需要勾選后面的“插入”復選框、“更新”復選框和“刪除”復選框，但只能勾選1個，在“定義”選項卡中輸入觸發(fā)器執(zhí)行代碼，如圖11-24所示，單擊“保存”按鈕。三、應用觸發(fā)器知識儲備4、使用Navicat管理觸發(fā)器3）刪除觸發(fā)器先在觸發(fā)器列表中選擇指定的觸發(fā)器，再單擊觸發(fā)器列表上方的“刪除觸發(fā)器”按鈕即可刪除選中的觸發(fā)器。三、應用觸發(fā)器任務實施任務11-7通過INSERT觸發(fā)器tri_checkplandate。對粵文創(chuàng)的工作計劃表plan進行自動檢查，要求計劃發(fā)布時間、計劃審核時間不能晚于操作當前時間。任務11-8通過INSERT觸發(fā)器tri_checkparticipant。對粵文創(chuàng)的工作計劃參與人員表participant進行自動檢查，在任何計劃中每個人只能分配一項，即在任何一個計劃中工號是唯一的。任務11-9通過INSERT觸發(fā)器tri_checkplanall。對粵文創(chuàng)的工作計劃表plan進行自動檢查，要求計劃開始時間早于計劃結(jié)束時間，計劃制訂者和計劃審核者不是同一個人，計劃發(fā)布時間要晚于計劃審核時間。二、使用存儲過程任務實施拓展任務11-7將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend。將觸發(fā)器tri_checkplanall完善為tri_checkplanallextend，并指出具體錯誤。04應用事務四、應用事務任務分析當張三爸爸給張三轉(zhuǎn)當月生活費時，張三爸爸的賬戶已成功扣款2000元，正準備給張三的賬戶增加2000元時突然停電，應該怎么辦呢？不用著急，數(shù)據(jù)庫具有良好的事務處理機制。事務可以把一些相關操作作為一個整體進行處理，要不全部成功完成，要不完全不做，若某一步出錯則撤銷所有操作，回到原點。小王