項(xiàng)目六創(chuàng)建和使用程序MySQL數(shù)據(jù)庫任務(wù)驅(qū)動(dòng)式教程（第4版）（微課版|AIGC拓展版）任務(wù)背景銀行經(jīng)常需要計(jì)算用戶的利息，但不同類別的用戶的利率是不一樣的。這就可以將計(jì)算利率的SQL代碼寫成一個(gè)程序存放起來，用指定的用戶類別作參數(shù)、這樣的程序叫作存儲過程或者存儲函數(shù)。使用時(shí)只要調(diào)用這個(gè)存儲過程或者存儲函數(shù)，根據(jù)指定的用戶類別，就可以將不同類別用戶的利息計(jì)算出來。再如，在編制學(xué)生管理系統(tǒng)時(shí)，當(dāng)某個(gè)學(xué)生某門課程的成績修改后，根據(jù)成績r(jià)eport是否及格更新credit表，將符合條件的學(xué)生某門課的學(xué)分累加到該學(xué)生的總學(xué)分里。這是一組重復(fù)使用的SQL語句，可以將其寫成存儲過程或存儲函數(shù)存儲在MySQL服務(wù)器中，需要時(shí)再調(diào)用，就可以多次執(zhí)行重復(fù)的操作。任務(wù)要求本任務(wù)將從認(rèn)識存儲過程著手，學(xué)習(xí)創(chuàng)建、執(zhí)行、調(diào)用、修改和刪除存儲過程的方法。重點(diǎn)掌握創(chuàng)建基本的存儲過程、創(chuàng)建帶變量的存儲過程和創(chuàng)建帶有流程控制語句的存儲過程。任務(wù)

13

建立和使用存儲過程任務(wù)目標(biāo)1．知識目標(biāo)（1）理解存儲過程的概念及作用；（2）了解存儲過程的基本語法。2．能力目標(biāo)（1）掌握創(chuàng)建、執(zhí)行、調(diào)用、修改和刪除存儲過程的方法；（2）學(xué)會創(chuàng)建基本的存儲過程、創(chuàng)建使用變量的存儲過程；（3）學(xué)會創(chuàng)建帶有流程控制語句的存儲過程。3．素養(yǎng)目標(biāo)（1）增強(qiáng)安全意識，學(xué)會編寫安全的存儲過程，防止SQL注入等安全威脅。（2）培養(yǎng)團(tuán)隊(duì)協(xié)作能力，在項(xiàng)目中，學(xué)會與團(tuán)隊(duì)成員協(xié)作，共同開發(fā)和維護(hù)存儲過程。（3）培養(yǎng)邏輯思維能力，通過編寫存儲過程，鍛煉學(xué)生的邏輯思維和問題解決能力，學(xué)會如何將復(fù)雜問題分解為可管理的代碼塊。任務(wù)

13

建立和使用存儲過程認(rèn)識存儲過程使用存儲過程的優(yōu)點(diǎn)有如下幾個(gè)方面。存儲過程有助于確保數(shù)據(jù)庫的安全性和完整性。存儲過程在被創(chuàng)建后，可以在程序中多次被調(diào)用而不必重新編寫，避免開發(fā)人員重復(fù)地編寫相同的SQL語句。而且，開發(fā)人員可以隨時(shí)對存儲過程進(jìn)行修改，對應(yīng)用程序源代碼無影響。存儲過程可以用流程控制語句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器中，在以后的操作中，只需從高速緩沖存儲器中調(diào)用已編譯好的二進(jìn)制代碼即可，提高了系統(tǒng)性能。存儲過程和存儲函數(shù)都是在MySQL服務(wù)器中存儲和執(zhí)行的，可以減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸，可以利用服務(wù)器的計(jì)算能力，執(zhí)行速度快。創(chuàng)建基本的存儲過程

關(guān)于DELIMITER命令在MySQL命令行的客戶端中，服務(wù)器處理語句默認(rèn)是以分號（;）為結(jié)束標(biāo)志的，如果有一行命令以分號（;）結(jié)束，那么按Enter鍵后，MySQL將會執(zhí)行該命令。但是在存儲過程中，可能要輸入較多的語句，且語句中包含有分號。如果還以分號作為結(jié)束標(biāo)志，那么執(zhí)行完第一個(gè)帶有分號的語句后，就會認(rèn)為程序結(jié)束，不能再往下執(zhí)行其他語句，必須將MySQL語句的結(jié)束標(biāo)志修改為其他符號。這時(shí)，可以使用DELIMITER命令來改變默認(rèn)結(jié)束標(biāo)志。DELIMITER命令的語法格式如下。DELIMITER$$輸入如下命令就是告訴MySQL解釋器，只有碰到“//”時(shí)才執(zhí)行命令。mySQL>delimiter//例如，要查看students表的信息。mySQL>Select*fromstudent//要想將命令結(jié)束符重新設(shè)定為分號，運(yùn)行下面的命令即可。DELIMITER;創(chuàng)建基本的存儲過程創(chuàng)建基本存儲過程創(chuàng)建存儲過程可以使用CREATEPROCEDURE語句。要在MySQL8.0中創(chuàng)建存儲過程，必須具有CREATEROUTINE權(quán)限。CREATEPROCEDURE的語法格式如下。CREATEPROCEDUREsp_name([proc_parameter[,...]])

[characteristic...]routine_body【任務(wù)13.1】創(chuàng)建存儲過程，用指定的學(xué)號作為參數(shù)刪除某一學(xué)生的記錄。mysql>DELIMITER$$CREATEPROCEDUREDELETE_student(INXHCHAR（6）)BEGINDELETEFROMstudentWHERES_NO=XH;END$$DELIMITER;創(chuàng)建基本的存儲過程【任務(wù)13.3】創(chuàng)建帶多個(gè)輸入?yún)?shù)的存儲過程，用指定的學(xué)號和課程號作為參數(shù)查詢學(xué)生成績。mysql>DELIMITER$$CREATEPROCEDUREselect_score(INXHCHAR(6),KCHCHAR(6))BEGINSELECT*FROMscoreWHEREs_no=XHandc_no=KCH;END$$DELIMITER;【任務(wù)13.2】創(chuàng)建存儲過程，用指定的課程號作為參數(shù)統(tǒng)計(jì)該課程的平均成績。mysql>DELIMITER$$CREATEPROCEDUREAVG_SCORE(INKCHCHAR(6))BEGINSELECTc_no,AVG(report)FROMSCOREWHEREc_no=KCH;END$$DELIMITER;創(chuàng)建基本的存儲過程【任務(wù)13.4】創(chuàng)建帶輸出參數(shù)的存儲過程，求學(xué)生人數(shù)。mysql>DELIMITER$$CREATEPROCEDUREsimpleproc(OUTXSINT)BEGINSELECTCOUNT(*)INTOXSFROMstudents;END$$DELIMITER;【任務(wù)13.6】以指定的系別號作為參數(shù)，查找某學(xué)院的老師姓名、所在院系名稱。mysql>DELIMITER$$CREATEPROCEDUREIS_teacher(INXBCHAR(8))BEGINSELECTT_NAME,D_NAMEFROMdepartments,teachers

WHEREteachers.D_NO=departments.D_NOANDdepartments.D_NO=XB;END$$DELIMITER;【任務(wù)13.5】創(chuàng)建不帶參數(shù)的存儲過程，統(tǒng)計(jì)已開設(shè)的專業(yè)基礎(chǔ)課總學(xué)分。mysql>DELIMITER$$CREATEPROCEDURESUM_CREDIT()BEGINSELECTSUM(credit)AS'專業(yè)基礎(chǔ)課總學(xué)分'FROMcourseWHEREtype='專業(yè)基礎(chǔ)課';END$$DELIMITER;創(chuàng)建基本的存儲過程查看存儲過程要想查看數(shù)據(jù)庫中有哪些存儲過程，可以使用SHOWPROCEDURESTATUS命令，如圖13.2所示。圖13.2查看存儲過程執(zhí)行存儲過程在創(chuàng)建存儲過程之后，可以在程序、觸發(fā)器或者存儲過程中調(diào)用已經(jīng)創(chuàng)建好的存儲過程，調(diào)用時(shí)都會使用到CALL語句。語法格式：CALLsp_name([parameter[,...]])【任務(wù)13.7】執(zhí)行存儲過程，刪除學(xué)號為122001的學(xué)生記錄。mysql>CallDELETE_STUDENT('122001');可以通過SELECT*FROMstudents查看一下學(xué)號為122001的學(xué)生記錄是否已被刪除。調(diào)用【任務(wù)13.2】存儲過程。mysql>CallAVG_SCORE('A002')運(yùn)行結(jié)果如圖13.3所示。調(diào)用【任務(wù)13.3】存儲過程。mysql>CallSELECT_SCORE('122001','A001');運(yùn)行結(jié)果如圖13.4所示。調(diào)用【任務(wù)13.5】存儲過程。mysql>CallSUM_CREDIT()運(yùn)行結(jié)果如圖13.5所示。調(diào)用【任務(wù)13.4】的存儲過程。mysql>Callsimpleproc(@xs)select@xs;結(jié)果如圖13.6所示。圖13.3圖13.4圖13.5圖13.6創(chuàng)建帶變量的存儲過程DECLARE語法格式如下。DECLAREvar_name[,...]type[DEFAULTvalue]局部變量聲明與賦值01OPTION用DECLARE語句聲明局部變量【任務(wù)13.8】聲明一個(gè)整型變量和兩個(gè)字符變量。DECLAREnumINT(4);DECLAREstr1,str2VARCHAR(6);語法格式如下。SETvar_name=expr[,var_name=expr]...02OPTION用SET語句給變量賦值【任務(wù)13.9】在存儲過程中給局部變量賦值。SETnum=100,str1='lenovo',str2='聯(lián)想';創(chuàng)建帶變量的存儲過程語法格式如下。SELECTcol_name[,...]INTOvar_name[,...]table_expr03OPTION用SELECT語句給變量賦值【任務(wù)13.【任務(wù)13.10】在存儲過程中將學(xué)號為“122001”的學(xué)生的“MYSQL”成績的值賦給變量CJ。8】聲明一個(gè)整型變量和兩個(gè)字符變量。DECLARECJINT(4);SELECTreportINTOCJFROMcourseJOINscoreUSING(c_no)WHEREc_name='MYSQL'ANDs_no='122001';創(chuàng)建帶變量的存儲過程【任務(wù)13.11】創(chuàng)建一個(gè)存儲過程，根據(jù)指定的參數(shù)（學(xué)號）查看某位學(xué)生的不及格科目數(shù)，如果不及格科目數(shù)超過2門（含2門），則輸出“啟動(dòng)成績預(yù)警”并輸出該生的成績單，否則輸出“成績在可控范圍”。創(chuàng)建使用局部變量的存儲過程mysql>DELIMITER$$CREATEPROCEDUREDO_QUERY(INXHCHAR(6),OUTSTRCHAR(8))BEGINDECLAREKMTINYINT;SELECTCOUNT(*)INTOKMFROMscoreWHEREs_no=XHANDreport<60;IFKM>=2THENSETSTR='啟動(dòng)成績預(yù)警';SELECT*FROMSCOREWHEREs_no=XH;ELSEIFKM<2THENSETSTR='成績在可控范圍';ENDIF;END$$DELIMITER;調(diào)用存儲過程。CALLDO_QUERY('122001',@str);運(yùn)行結(jié)果如圖13.7所示。SELECT@str;mysql>CALLDO_QUERY('123003',@str);運(yùn)行結(jié)果如圖13.8所示。mysql>SELECT@str;運(yùn)行結(jié)果如圖13.9所示。創(chuàng)建帶變量的存儲過程圖13.7【任務(wù)13.11】運(yùn)行結(jié)果1圖13.8【任務(wù)13.11】運(yùn)行結(jié)果2圖13.9【任務(wù)13.11】運(yùn)行結(jié)果3創(chuàng)建帶有流程控制語句的存儲過程IF…THEN…ELSE語句可根據(jù)不同的條件執(zhí)行不同的操作。語法格式如下。使用IF…THEN…ELSE語句IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF【任務(wù)13.12】創(chuàng)建一個(gè)存儲過程，有兩個(gè)輸入?yún)?shù)：XH和KCH。如果成績大于或等于60分，就將該課程的學(xué)分累加計(jì)入該學(xué)生的總學(xué)分；否則，總學(xué)分不變。mysql>DELIMITER$$CREATEPROCEDUREDO_UPDATE(INXHCHAR(6),INKCHCHAR(16))BEGINDECLAREXFTINYINT;DECLARECJFLOAT;SELECTcreditINTOXFFROMcourseWHEREc_no=KCH;SELECTreportINTOCJFROMscoreWHEREs_no=XHANDc_no=KCH;IFCJ<60THENUPDATEcreditSETcredit=credit+0WHEREs_no=XH;ELSEUPDATEcreditSETcredit=credit+XFWHEREs_no=XH;ENDIF;END$$DELIMITER;創(chuàng)建帶有流程控制語句的存儲過程向score表中輸入一行數(shù)據(jù)。mysql>INSERTINTOscoreVALUES('122004','A001',80);接下來，調(diào)用存儲過程并查詢調(diào)用結(jié)果。mysql>callDO_UPDATE('122004','A001');查看credit表的學(xué)分情況。mysql>select*fromcredit；可以看到在credit表中，由于該學(xué)生該課程成績大于60分，因此已將該門課的學(xué)分累加到該學(xué)生的總學(xué)分里，運(yùn)行結(jié)果如圖13.10所示。圖13.10【任務(wù)13.12】運(yùn)行結(jié)果再向score表中輸入一行數(shù)據(jù)。mysql>INSERTINTOscoreVALUES('122004','A003',50);接下來,調(diào)用存儲過程并查詢調(diào)用結(jié)果。mysql>callDO_UPDATE('122004','A003')查看credit表的學(xué)分情況。mysql>select*fromcredit；當(dāng)調(diào)用存儲過程時(shí)，credit表已更新，但由于成績小于60分，學(xué)號為“122004”的學(xué)生的總學(xué)分不變。圖13.10【任務(wù)13.12】運(yùn)行結(jié)果創(chuàng)建帶有流程控制語句的存儲過程一個(gè)CASE語句經(jīng)?？梢猿洚?dāng)一個(gè)IF…THEN…ELSE語句。語法格式如下（第一種）。使用CASE語句CASEcase_valueWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE語法格式如下（第二種）。CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE【任務(wù)13.13】用CASE的第二種語法格式創(chuàng)建【任務(wù)13.12】要求創(chuàng)建的存儲過程。mysql>DELIMITER$$CREATEPROCEDUREDO_UPDATE(INXHCHAR(6),INKCHCHAR(16))BEGINDECLAREXFTINYINT;DECLARECJFLOAT;SELECTcreditINTOXFFROMcourseWHEREc_no=KCH;SELECTreportINTOCJFROMscoreWHEREs_no=XHANDc_no=KCH;CASEWHENCJ<60THENUPDATEcreditSETcredit=credit+0WHEREs_no=XH;ELSEUPDATEcreditSETcredit=credit+XFWHEREs_no=XH;ENDCASE;END$$DELIMITER;在存儲過程調(diào)用其他存儲過程【任務(wù)13.14】創(chuàng)建一個(gè)存儲過程DO_INSERT(

)，向score表中插入一行記錄。創(chuàng)建另一個(gè)存儲過程DO_query，調(diào)用已經(jīng)建好的存儲過程DO_INSERT(

)，并查詢輸出score表的記錄?？梢栽诖鎯^程中調(diào)用其他存儲過程。先創(chuàng)建存儲過程DO_INSERT(

)。mysql>CREATEPROCEDUREDO_INSERT()INSERTINTOscoreVALUES('122001','A003',85);創(chuàng)建第二個(gè)存儲過程DO_query(

)，調(diào)用DO_INSERT(

)。DELIMITER$$CREATEPROCEDUREDO__query()BEGINCALLDO_INSERT();SELECT*FROMSCORE;END$$DELIMITER;調(diào)用存儲過程DO_query()如下。mysql>CALLDO_query運(yùn)行結(jié)果如圖13.11所示。圖13.11【任務(wù)13.14】運(yùn)行結(jié)果修改存儲過程有兩種方法可以修改存儲過程，一種方法是使用ALTERPROCEDURE語句進(jìn)行修改；另一種方法是先刪除再重新定義存儲過程。使用ALTERPROCEDURE語句修改存儲過程的語法格式如下。ALTERPROCEDUREsp_name[characteristic...]其中，characteristic的語法格式如下。{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'01OPTION使用ALTERPROCEDURE語句修改存儲過程的某些特征【任務(wù)13.15】修改存儲過程num_from_employee(

)的定義。將讀寫權(quán)限改為MODIFIESSQLDATA，并指明調(diào)用者可以執(zhí)行。ALTER

PROCEDURE

num_from_employee

MODIFIES

SQL

DATA

SQL

SECURITY

INVOKER

;

修改存儲過程02OPTION先刪除再重新定義存儲過程的方法【任務(wù)13.16】使用先刪除再重新定義的辦法創(chuàng)建【任務(wù)13.5】中的存儲過程SUM_CREDIT()，修改為統(tǒng)計(jì)已開設(shè)的專業(yè)課總學(xué)分。mysql>DELIMITER$$DROPPROCEDUREIFEXISTSSUM_CREDIT()；CREATEPROCEDURESUM_CREDIT()BEGINSELECTSUM(credit)AS'專業(yè)課總學(xué)分'FROMcourseWHEREtype='專業(yè)課';END$$DELIMITER;查看存儲過程的定義可以用SHOW命令查看創(chuàng)建的存儲過程的語句塊。mysql>showcreatePROCEDUREsimpleproc;刪除存儲過程可以使用DROPPROCEDURE語句刪除已經(jīng)存在的存儲過程。語法格式如下。DROPPROCEDURE[IFEXISTS]sp_name【任務(wù)13.17】刪除存儲過程DO_UPDATE(

)。mySQL>DROPPROCEDUREIFEXISTSDO_UPDATE；項(xiàng)目實(shí)踐在YSGL數(shù)據(jù)庫中進(jìn)行如下操作。創(chuàng)建不帶參數(shù)的存儲過程count_procedure(

)，統(tǒng)計(jì)工作10年以上的員工人數(shù)。創(chuàng)建帶一個(gè)輸入?yún)?shù)的存儲過程salary_procedure(

)，根據(jù)員工E_ID查詢該員工的實(shí)際收入。創(chuàng)建一個(gè)存儲過程zhicheng_procedure(

)，用參數(shù)指定的職稱的值查詢具有該職稱的所有老師。創(chuàng)建一個(gè)存儲過程salary_avg_procedure(

)，用參數(shù)指定的部門名稱查詢屬于該部門的老師的平均基本工資?；贓mployees表創(chuàng)建存儲過程EMPLOYEES_info_procedure(

)。該存儲過程的輸入?yún)?shù)是type，輸出參數(shù)是info。當(dāng)type的值是1時(shí)，計(jì)算employees表中所有男性員工的人數(shù)，然后通過參數(shù)info輸出；當(dāng)type的值是2時(shí)，計(jì)算employees表中所有女性員工的人數(shù)，然后通過info輸出；當(dāng)type為1和2以外的任何值時(shí)，將字符串“ErrorInput!”賦值給info。刪除存儲過程salary_procedure(

)。創(chuàng)建一個(gè)帶輸入?yún)?shù)的存儲過程，根據(jù)指定的學(xué)號判斷該學(xué)生是否為女生，若為女生則加一個(gè)學(xué)分，若為男生則不加。習(xí)題一、編程與應(yīng)用題在數(shù)據(jù)庫bookdb中創(chuàng)建一個(gè)存儲過程count_procedure(

)，用參數(shù)給定的留言人的姓名統(tǒng)計(jì)該留言人留言的記錄數(shù)。二、簡答題1．請解釋什么是存儲過程。2．請列舉使用存儲過程的益處。3．請簡述存儲過程的優(yōu)點(diǎn)。任務(wù)背景存儲函數(shù)與存儲過程的功能類似，任務(wù)背景也相似。存儲過程實(shí)現(xiàn)的功能要復(fù)雜一些，而存儲函數(shù)實(shí)現(xiàn)的功能針對性比較強(qiáng)。存儲函數(shù)與存儲過程有什么區(qū)別和聯(lián)系呢？該怎樣創(chuàng)建和使用存儲函數(shù)呢？任務(wù)要求本任務(wù)將從認(rèn)識存儲函數(shù)著手，學(xué)習(xí)創(chuàng)建、調(diào)用、查看、修改和刪除存儲函數(shù)的方法。重點(diǎn)掌握創(chuàng)建基本的存儲函數(shù)、創(chuàng)建帶變量的存儲函數(shù)、在存儲函數(shù)中調(diào)用其他存儲過程或存儲函數(shù)的方法。任務(wù)

14建立和使用存儲函數(shù)任務(wù)目標(biāo)1．知識目標(biāo)（1）理解存儲函數(shù)的概念及作用；（2）了解存儲函數(shù)的基本語法；（3）掌握存儲函數(shù)和存儲過程的異同。2．能力目標(biāo)（1）掌握創(chuàng)建、調(diào)用、查看、修改和刪除存儲函數(shù)的方法；（2）學(xué)會創(chuàng)建基本的存儲函數(shù)、創(chuàng)建帶變量的存儲函數(shù)；（3）學(xué)會在存儲函數(shù)中調(diào)用其他存儲函數(shù)或存儲過程。3．素養(yǎng)目標(biāo)（1）培養(yǎng)創(chuàng)新思維，不斷探索和嘗試新的技術(shù)方法，以提高存儲函數(shù)的效率和性能，解決實(shí)際問題。（2）對數(shù)據(jù)安全和隱私保護(hù)有深刻的認(rèn)識，了解在數(shù)據(jù)庫操作中可能遇到的安全威脅，并學(xué)會如何在編寫存儲函數(shù)時(shí)實(shí)施安全措施。（3）培養(yǎng)良好的倫理意識，遵守職業(yè)道德規(guī)范，確保在數(shù)據(jù)庫管理和存儲函數(shù)開發(fā)過程中，尊重?cái)?shù)據(jù)的所有權(quán)和隱私權(quán)，遵守相關(guān)法律法規(guī)。任務(wù)

14建立和使用存儲函數(shù)認(rèn)識存儲函數(shù)存儲函數(shù)和存儲過程類似，是在數(shù)據(jù)庫中定義一些SQL語句的集合。一旦它被存儲，客戶端不需要再重新發(fā)布單獨(dú)的語句，直接調(diào)用這些存儲函數(shù)即可，可以避免開發(fā)人員重復(fù)地編寫相同的SQL語句。而且，存儲函數(shù)和存儲過程一樣，是在MySQL服務(wù)器中存儲和執(zhí)行的，可以減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸。創(chuàng)建存儲函數(shù)MySQL中創(chuàng)建存儲函數(shù)的語法格式如下。CREATEFUNCTIONsp_name([func_parameter[,...]])RETURNStype[characteristic...]routine_body創(chuàng)建基本的存儲函數(shù)mysql>DELIMITER$$CREATEFUNCTIONNUM_OF_COURSE()RETURNSINTEGERBEGINRETURN(SELECTCOUNT(*)FROMcourseWHEREtype='專業(yè)基礎(chǔ)課');END$$DELIMITER;【任務(wù)14.1】創(chuàng)建一個(gè)存儲函數(shù)，它返回course表中已開設(shè)的專業(yè)基礎(chǔ)課門數(shù)。創(chuàng)建存儲函數(shù)存儲函數(shù)與存儲過程一樣，也可以定義和使用變量，它們可以用來存儲臨時(shí)結(jié)果。聲明局部變量和賦值方法請參照任務(wù)13相關(guān)部分。創(chuàng)建帶變量的存儲函數(shù)【任務(wù)14.2】創(chuàng)建一個(gè)存儲函數(shù)，根據(jù)指定的參數(shù)KCH來刪除在score表中存在但course表中不存在的成績記錄。mysql>DELIMITER$$CREATEFUNCTIONDELETE_KCH(KCHCHAR（6）)RETURNSCHAR（5）BEGINDECLAREKCMCHAR（6）;SELECTc_nameINTOKCMFROMcourseWHEREc_no=KCH;IFKCMISNULLTHENDELETEFROMscoreWHEREc_no=KCH;RETURN'YES';ELSERETURN'NO';ENDIF;END$$DELIMITER;調(diào)用存儲函數(shù)使用SELECT關(guān)鍵字調(diào)用存儲函數(shù)語法格式如下。SELECTsp_name([func_parameter[,...]])【任務(wù)14.3】調(diào)用存儲函數(shù)DELETE_KCH(

)。mysql>SELECTDELETE_KCH('A001');運(yùn)行結(jié)果如圖14.1所示。【任務(wù)14.4】調(diào)用存儲函數(shù)NUM_OF_COURSE()。mysql>SELECTNUM_OF_COURSE();運(yùn)行結(jié)果如圖14.2所示。圖14.2【任務(wù)14.4】運(yùn)行結(jié)果圖14.1【任務(wù)14.3】運(yùn)行結(jié)果調(diào)用存儲函數(shù)調(diào)用另外一個(gè)存儲函數(shù)或者存儲過程【任務(wù)14.5】創(chuàng)建一個(gè)存儲函數(shù)，通過調(diào)用存儲函數(shù)NUM_OF_COURSE()獲得專業(yè)基礎(chǔ)課開設(shè)的門數(shù)。如果專業(yè)基礎(chǔ)課開設(shè)門數(shù)超過3門，則返回專業(yè)基礎(chǔ)課的總學(xué)時(shí)；否則返回專業(yè)基礎(chǔ)課的平均總學(xué)時(shí)。mysql>DELIMITER$$CREATEFUNCTIONIS_KCXS()RETURNSFLOAT(5,0)BEGINDECLAREKCMSINT;SELECTNUM_OF_COURSE()INTOKCMS;IFKCMS>=3THENRETURN(SELECTSUM(hours)FROMcourseWHEREtype='專業(yè)基礎(chǔ)課');ELSERETURN(SELECTAVG(hours)FROMcourseWHEREtype='專業(yè)基礎(chǔ)課');ENDIF;END$$DELIMITER;調(diào)用存儲函數(shù)IS_KCXS()。mysql>SELECTIS_KCXS();運(yùn)行結(jié)果如圖14.3所示。圖14.3【任務(wù)14.5】運(yùn)行結(jié)果查看存儲函數(shù)用戶可以通過SHOWSTATUS語句來查看函數(shù)的狀態(tài)。mysql>SHOWFUNCTIONSTATUS;也可以通過SHOWCREATE語句來查看函數(shù)的定義。mysql>SHOWCREATEFUNCTIONSP_NAME;SP_NAME參數(shù)表示存儲函數(shù)的名稱。【任務(wù)14.6】查看創(chuàng)建存儲函數(shù)NUM_OF_COURSE()的定義。mysql>SHOWCREATEFUNCTIONNUM_OF_COURSE;可以查看到存儲函數(shù)名稱、創(chuàng)建存儲函數(shù)的語句塊、字符集和校對原則，WAMP下的運(yùn)行結(jié)果如圖14.4所示。圖14.4WAMP下的運(yùn)行結(jié)果修改存儲函數(shù)通過ALTERFUNCTION語句來修改存儲函數(shù)，一種方法是用ALTERFUNCTION語句進(jìn)行修改，另一種方法是刪除再重新定義存儲函數(shù)。語法格式與修改存儲過程的相同。詳情請參照任務(wù)13中的修改存儲過程相關(guān)內(nèi)容。刪除存儲函數(shù)刪除存儲函數(shù)指刪除數(shù)據(jù)庫中已經(jīng)存在的存儲函數(shù)，可以通過DROPFUNCTION語句來刪除。語法格式如下。DROPFUNCTION[IFEXISTS]sp_name;【任務(wù)14.7】刪除存儲函數(shù)NUM_OF_COURSE()。圖14.5【任務(wù)14.7】運(yùn)行結(jié)果mysql>DROPFUNCTIONIFEXISTSNUM_OF_COURSE;運(yùn)行結(jié)果如圖14.5所示。項(xiàng)目實(shí)踐在YSGL數(shù)據(jù)庫中進(jìn)行如下操作。創(chuàng)建存儲函數(shù)income_function(

)，根據(jù)指定的E_ID參數(shù)統(tǒng)計(jì)每個(gè)員工的實(shí)際收入。創(chuàng)建存儲函數(shù)student_info_function(

)，實(shí)現(xiàn)任務(wù)13中【項(xiàng)目實(shí)踐】（5）的功能，該函數(shù)只有一個(gè)參數(shù)type。通過RETURN語句返回查詢結(jié)果。創(chuàng)建存儲函數(shù)count_function(

)，統(tǒng)計(jì)工作10年以上的員工人數(shù)。創(chuàng)建存儲函數(shù)DELETE_function(

)，根據(jù)指定的參數(shù)E_ID來刪除Salary表中存在，但Employees表中不存在的工資記錄。刪除存儲函數(shù)income_function(

)。習(xí)題一、編程與應(yīng)用題在數(shù)據(jù)庫bookdb中創(chuàng)建一個(gè)存儲函數(shù)count_function(

)，用參數(shù)給定的留言人的姓名統(tǒng)計(jì)該留言人留言的記錄數(shù)。二、簡答題請簡述存儲過程與存儲函數(shù)的區(qū)別。任務(wù)背景當(dāng)學(xué)生表中增加了一個(gè)學(xué)生的信息時(shí)，學(xué)生的總數(shù)同時(shí)改變。當(dāng)錄入（更新）某位學(xué)生某門課的成績時(shí)，如果成績合格，應(yīng)該將這門課的學(xué)分加到他的總學(xué)分里。當(dāng)刪除學(xué)生表中某個(gè)學(xué)生的信息時(shí)，同時(shí)將成績表中與該學(xué)生有關(guān)的數(shù)據(jù)全部刪除。編寫程序，監(jiān)控教師職稱變化。教師職稱升為教授時(shí)，工資加1000元；教師職稱升為副教授時(shí)，工資加500元。更新員工記錄（如員工編號更改）時(shí)，也要同時(shí)更新銷售表相應(yīng)的記錄。類似這樣的情況，當(dāng)插入、更新或刪除某個(gè)數(shù)據(jù)時(shí)，要觸發(fā)一個(gè)動(dòng)作，更新另一張表（或同一張表）中相應(yīng)的數(shù)據(jù)。這個(gè)功能可以通過觸發(fā)器（Trigger）來實(shí)現(xiàn)。任務(wù)要求本任務(wù)將從認(rèn)識觸發(fā)器著手，學(xué)習(xí)觸發(fā)器的創(chuàng)建、查看和刪除的基本方法，掌握觸發(fā)器激發(fā)它表數(shù)據(jù)更新、激發(fā)自表數(shù)據(jù)更新，以及調(diào)用存儲過程進(jìn)行數(shù)據(jù)操作的實(shí)際應(yīng)用任務(wù)

15創(chuàng)建和使用觸發(fā)器任務(wù)目標(biāo)1．知識目標(biāo)（1）認(rèn)識觸發(fā)器；（2）學(xué)習(xí)創(chuàng)建、查看和刪除觸發(fā)器的基本方法。2．能力目標(biāo)（1）掌握通過觸發(fā)器激發(fā)它表數(shù)據(jù)更新、激發(fā)自表數(shù)據(jù)更新的方法；（2）掌握通過觸發(fā)器調(diào)用存儲過程進(jìn)行數(shù)據(jù)操作的實(shí)際應(yīng)用。3．素養(yǎng)目標(biāo)（1）培養(yǎng)系統(tǒng)思維與問題解決能力，從整個(gè)數(shù)據(jù)庫管理系統(tǒng)的角度出發(fā)，理解觸發(fā)器如何與其他數(shù)據(jù)庫對象協(xié)同工作，以及它們對系統(tǒng)性能的影響。（2）增強(qiáng)倫理意識與職業(yè)道德，遵守?cái)?shù)據(jù)所有權(quán)和隱私權(quán)的倫理規(guī)范。遵守職業(yè)道德，不利用觸發(fā)器進(jìn)行不正當(dāng)?shù)臄?shù)據(jù)操作，如數(shù)據(jù)篡改或非法訪問。（3）提高法律意識與合規(guī)操作能力，在實(shí)際應(yīng)用中，確保觸發(fā)器的設(shè)計(jì)和實(shí)現(xiàn)符合行業(yè)標(biāo)準(zhǔn)和法規(guī)要求。任務(wù)

15創(chuàng)建和使用觸發(fā)器認(rèn)識觸發(fā)器觸發(fā)器是一種特殊的存儲過程，只要滿足一定的條件，對數(shù)據(jù)進(jìn)行INSERT、UPDATE和DELETE操作時(shí)，數(shù)據(jù)庫系統(tǒng)就會自動(dòng)執(zhí)行觸發(fā)器中定義的程序語句，以維護(hù)數(shù)據(jù)完整性或完成其他特殊的任務(wù)。語法格式如下。CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROWtrigger_stmt創(chuàng)建觸發(fā)器激發(fā)它表數(shù)據(jù)更新【任務(wù)15.1】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)更改course表中某門課的課程編號時(shí)，同時(shí)將score表中相應(yīng)課程編號全部更新。mysql>DELIMITER$$CREATETRIGGERCNO_UPDATEAFTERUPDATEONcourseFOREACHROWBEGINUPDATEscoreSETc_no=NEW.c_noWHEREc_no=OLD.c_no;END$$DELIMITER;現(xiàn)在驗(yàn)證一下觸發(fā)器的功能，代碼如下。mysql>UPDATE

course

SET

c_no='A100'

WHEREc_no='A001';使用SELECT語句查看score表中的情況，發(fā)現(xiàn)所有原A001課程編號的記錄已更新為A100，運(yùn)行結(jié)果如圖15.1所示。圖15.1【任務(wù)15.1】運(yùn)行結(jié)果創(chuàng)建觸發(fā)器【任務(wù)15.2】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向score表中插入數(shù)據(jù)時(shí)，如果成績大于或等于60分，則利用觸發(fā)器將credit表中該學(xué)生的總學(xué)分加上該門課程的學(xué)分；否則總學(xué)分不變。mysql>DELIMITER$$CREATETRIGGERCREDIT_ADDAFTERINSERTONscoreFOREACHROWBEGINDECLAREXFINT（1）;SELECTcreditINTOXFFROMcourseWHEREc_no=NEW.c_no;IFNEW.REPORT>=60THENUPDATEcreditSETCREDIT=CREDIT+XFWHEREs_no=NEW.s_no;ENDIF;END$$DELIMITER;圖15.2【任務(wù)15.2】運(yùn)行結(jié)果現(xiàn)在驗(yàn)證一下觸發(fā)器的功能。INSERT

INTO

SCOREVALUES

('123004','A002',60);使用SELECT語句查看credit表中的情況，運(yùn)行結(jié)果如圖15.2所示?？梢钥吹剑褜002課程的學(xué)分累加給了123004學(xué)生。創(chuàng)建觸發(fā)器【任務(wù)15.3】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)刪除students表中某個(gè)學(xué)生的記錄時(shí)，刪除score表中相應(yīng)的成績記錄。mysql>DELIMITER$$CREATETRIGGERSCO_DELETEAFTERDELETE

ONstudentsFOREACHROWBEGINDELETEFROMscoreWHEREs_no=OLD.s_no;END$$DELIMITER;現(xiàn)在驗(yàn)證一下觸發(fā)器的功能。DELETEFROMstudentsWHEREs_no='122001';使用SELECT語句查看score表中的情況，可以看到已沒有122001學(xué)生的成績記錄。創(chuàng)建觸發(fā)器激發(fā)自表數(shù)據(jù)更新【任務(wù)15.4】創(chuàng)建一個(gè)觸發(fā)器，修改course表中相應(yīng)課程的學(xué)時(shí)之后，每增加18學(xué)時(shí)，將該門課的學(xué)分增加1學(xué)分。mysql>DELIMITER$$CREATETRIGGERCREDIT_UPDATEBEFOREUPDATEONcourseFOREACHROWBEGINIFnew.hours-old.hours=18THENSETnew.credit=old.credit+1;ENDIF;END$$DELIMITER;可以看出，A002課程的學(xué)分已從3學(xué)分更新為4學(xué)分。圖15.3【任務(wù)15.4】運(yùn)行結(jié)果更新course表，將A002課程學(xué)時(shí)增加18。UPDATEJXGL.courseSEThours=82WHEREcourse.c_no='A002';現(xiàn)在查看course表的credit的更新情況。SELECT*FROMcourse;運(yùn)行結(jié)果如圖15.3所示。創(chuàng)建觸發(fā)器觸發(fā)器調(diào)用存儲過程【任務(wù)15.5】備份students表并命名為“學(xué)生表”,當(dāng)students表數(shù)據(jù)更新時(shí)，通過觸發(fā)器調(diào)用存儲過程，保證學(xué)生表數(shù)據(jù)的同步更新。定義存儲過程。mysql>DELIMITER$$CREATEPROCEDURECHANGES()BEGINTRUNCATETABLE學(xué)生表;REPLACEINTO學(xué)生表SELECT*FROMstudents;END$$DELIMITER;查看觸發(fā)器MySQL可以執(zhí)行SHOWTRIGGERS語句來查看觸發(fā)器的基本信息。語法格式如下。mysql>SHOWTRIGGERS;在WAMP下運(yùn)行結(jié)果如圖15.4所示。圖15.4在WAMP下運(yùn)行結(jié)果在MySQL中，所有觸發(fā)器的定義都存儲在information_schema數(shù)據(jù)庫下的triggers表中。查詢triggers表，可以查看數(shù)據(jù)庫中所有觸發(fā)器的詳細(xì)信息。查詢語句如下。mysql>SELECT*FROMinformation_schema.triggers;刪除觸發(fā)器刪除觸發(fā)器指刪除數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器。MySQL使用DROPTRIGGER語句來刪除觸發(fā)器。語法格式如下。DROPTRIGGER[schema_name.]trigger_name【任務(wù)15.6】刪除觸發(fā)器STU_CHANGE3。mysql>DROPTRIGGERSTU_CHANGE3;項(xiàng)目實(shí)踐在YSGL數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，當(dāng)向Employees表中增加一個(gè)員工信息時(shí)，員工的總數(shù)同時(shí)改變。在YSGL數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，當(dāng)更改Departments表中的部門編號時(shí)，同時(shí)將Employees表的部門編號也全部更新。在Salary表上建立一個(gè)BEFORE類型的觸發(fā)器，監(jiān)控對員工工資的更新，當(dāng)更新后的工資比更新前低時(shí)，取消操作，并給出提示信息；否則允許工資的更新。習(xí)題一、填空題1．在實(shí)際使用中，MySQL所支持的觸發(fā)器有、和3種。2．假設(shè)之前創(chuàng)建的score表沒有設(shè)置外鍵級聯(lián)策略，設(shè)置觸發(fā)器，實(shí)現(xiàn)在students表中修改課程s_no時(shí)，可自動(dòng)修改score表中的課程s_no。Createtriggertrigger_updateupdateonforeachrow;二、編程與應(yīng)用題在數(shù)據(jù)庫bookdb的contentinfo表中創(chuàng)建一個(gè)觸發(fā)器delete_trigger，用于每次刪除contentinfo表中一行數(shù)據(jù)時(shí)將用戶變量str的值設(shè)置為“舊信息已刪除！”。三、簡答題什么是觸發(fā)器？觸發(fā)器有哪幾種？觸發(fā)器有什么優(yōu)點(diǎn)?

任務(wù)背景MySQL5.1.6中引入了一項(xiàng)新特性——EVENT，顧名思義EVENT就是事件、定時(shí)任務(wù)機(jī)制，即在指定的時(shí)間單元內(nèi)執(zhí)行特定的任務(wù)。引入EVENT以后，一些對數(shù)據(jù)的定時(shí)性操作不再依賴外部程序，直接使用數(shù)據(jù)庫本身提供的功能即可。例如，定時(shí)使數(shù)據(jù)庫中的數(shù)據(jù)在某個(gè)間隔后刷新、定時(shí)關(guān)閉賬戶、定時(shí)打開或關(guān)閉數(shù)據(jù)庫指示器等。這些特定任務(wù)可以由事件調(diào)度器來完成。任務(wù)要求本任務(wù)將從認(rèn)識事件開始，學(xué)習(xí)創(chuàng)建、查看、修改和刪除事件的基本方法。重點(diǎn)掌握創(chuàng)建某個(gè)時(shí)刻發(fā)生的事件、創(chuàng)建在指定區(qū)間周期性發(fā)生的事件，以及在事件中調(diào)用存儲過程或存儲函數(shù)的實(shí)際應(yīng)用。任務(wù)

16創(chuàng)建和使用事件任務(wù)目標(biāo)1．知識目標(biāo)（1）掌握事件調(diào)度器的相關(guān)知識。（2）學(xué)習(xí)如何創(chuàng)建、修改、刪除和查看事件，包括事件的定義語法和參數(shù)設(shè)置。（3）了解事件的觸發(fā)條件，包括時(shí)間間隔、特定時(shí)間點(diǎn)以及數(shù)據(jù)庫操作觸發(fā)。2．能力目標(biāo)（1）掌握創(chuàng)建、修改的刪除不同類型事件的方法。（2）掌握如何將SQL語句和存儲過程嵌入到事件中，實(shí)現(xiàn)自動(dòng)化的數(shù)據(jù)庫操作。（3）學(xué)會分析和優(yōu)化事件執(zhí)行對數(shù)據(jù)庫性能的影響，確保事件的高效運(yùn)行。3．素養(yǎng)目標(biāo)（1）培養(yǎng)對數(shù)據(jù)隱私和安全的尊重，確保在自動(dòng)化任務(wù)中遵守?cái)?shù)據(jù)保護(hù)法規(guī)和倫理標(biāo)準(zhǔn)。（2）強(qiáng)化對數(shù)據(jù)庫操作的責(zé)任意識，確保事件任務(wù)的執(zhí)行不會導(dǎo)致數(shù)據(jù)丟失或泄露。（3）了解并遵守相關(guān)的法律法規(guī)，確保事件任務(wù)的合法合規(guī)執(zhí)行。任務(wù)

16創(chuàng)建和使用事件認(rèn)識事件事件調(diào)度器有時(shí)也可稱為臨時(shí)觸發(fā)器(TemporalTrigger)，因?yàn)槭录{(diào)度器是基于特定時(shí)間周期觸發(fā)來執(zhí)行某些任務(wù)，而觸發(fā)器是基于某張表所產(chǎn)生的事件觸發(fā)的，兩者的區(qū)別就在這里。MySQL事件調(diào)度器負(fù)責(zé)調(diào)用事件，這個(gè)模塊是MySQL數(shù)據(jù)庫服務(wù)器的一部分，它不斷地監(jiān)視一個(gè)事件是否需要調(diào)用。要?jiǎng)?chuàng)建事件，必須打開調(diào)度器?？梢允褂孟到y(tǒng)變量EVENT_SCHEDULER來打開事件調(diào)度器，TRUE（或1、ON）為打開，F(xiàn)ALSE（或0、OFF）為關(guān)閉。要開啟EVENT_SCHEDULER，可執(zhí)行下面的語句。SET@@GLOBAL.EVENT_SCHEDULER=TRUE;也可以在MySQL的配置文件my.ini中加上下面代碼，然后重啟MySQL服務(wù)器。event_scheduler=1要查看當(dāng)前是否已開啟事件調(diào)度器，可執(zhí)行如下SQL語句。SHOWVARIABLESLIKE'event_scheduler';運(yùn)行結(jié)果如圖16.1所示。也可以執(zhí)行：SELECT@@event_scheduler;運(yùn)行結(jié)果如圖16.2所示。圖16.1查看事件調(diào)度器開啟狀況1圖16.2查看事件調(diào)度器開啟狀況2創(chuàng)建事件創(chuàng)建事件可以使用CREATEEVENT語句。語法格式如下。CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE|DISABLEONSLAVE][COMMENT'comment']DOsql_statement;其中，schedule具體內(nèi)容如下。ATtimestamp[+INTERVALinterval]

|EVERYinterval[STARTStimestamp[+INTERVALinterval]][ENDStimestamp[+INTERVALinterval]]interval：

count{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}創(chuàng)建事件創(chuàng)建某個(gè)時(shí)刻發(fā)生的事件【任務(wù)16.1】創(chuàng)建現(xiàn)在立刻執(zhí)行的事件，創(chuàng)建表test。mysql>USEJXGL;CREATEEVENTDIRECTONSCHEDULEATNOW()DOCREATETABLEtest(timelineTIMESTAMP);查看是否創(chuàng)建了表test。SHOWTABLES;運(yùn)行結(jié)果如圖16.3所示。圖16.3【任務(wù)16.1】運(yùn)行結(jié)果查看test表。mysql>SELECT*FROMtest;圖16.3【任務(wù)16.1】運(yùn)行結(jié)果【任務(wù)16.2】創(chuàng)建現(xiàn)在立刻執(zhí)行的事件。5秒后創(chuàng)建表test1。mysql>USEJXGL;CREATEEVENTDIRECTONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL5SECONDDOCREATETABLEtest1(timelineTIMESTAMP);創(chuàng)建事件創(chuàng)建在指定區(qū)間周期性發(fā)生的事件【任務(wù)16.3】每秒插入一條記錄到數(shù)據(jù)表。mysql>CREATEEVENTtest_insertONSCHEDULEEVERY1SECONDDOINSERTINTOtestVALUES(CURRENT_TIMESTAMP);等待5秒后，再執(zhí)行查詢。MySQL>SELECT*FROMtest;運(yùn)行結(jié)果如圖所示。【任務(wù)16.4】每天定時(shí)清空test表。mysql>CREATEEVENTe_test

ONSCHEDULEEVERY1DAYDODELETEFROMtest;【任務(wù)16.5】創(chuàng)建一個(gè)事件，從下一個(gè)星期開始,每個(gè)星期都清空test表,并且在2019年的12月31日12時(shí)結(jié)束。mysql>DELIMITER$$CREATEEVENTSTARTMONTHONSCHEDULEEVERY1WEEKSTARTSCURDATE()+INTERVAL1WEEKENDS'2019-12-3112:00:00'DOBEGINTRUNCATETABLEtest;END$$DELIMITER;創(chuàng)建事件在事件中調(diào)用存儲過程或存儲函數(shù)【任務(wù)16.6】假設(shè)COUNT_STU(

)函數(shù)是用來統(tǒng)計(jì)學(xué)生考勤情況的存儲過程，創(chuàng)建一個(gè)事件，每星期查看一次學(xué)生的考勤情況，供有關(guān)部門參考。mysql>DELIMITER$$CREATEEVENTSTARTWEEKONSCHEDULEEVERY1WEEKDOBEGINCallCOUNT_STU;END$$DELIMITER;查看事件簡要列出所有的事件。語法格式如下。SHOWEVENTS[FROMschema_name]

[LIKE'pattern'|WHEREexpr]【任務(wù)16.7】查看JXGL數(shù)據(jù)庫的事件。mysql>UseJXGL;mysql>SHOWEVENTS;【任務(wù)16.8】格式化顯示所有事件。mysql>SHOWEVENTS\G運(yùn)行結(jié)果如圖16.5所示。查看事件的創(chuàng)建信息。語法格式如下。mysql>SHOWCREATEEVENTEVENT_NAME【任務(wù)16.9】查看STARTMONTH的創(chuàng)建信息。mysql>SHOWCREATEEVENTSTARTMONTH;圖16.5【任務(wù)16.8】運(yùn)行結(jié)果修改事件在MySQL中可以通過ALTEREVENT語句來修改事件的定義和相關(guān)屬性，例如，臨時(shí)關(guān)閉事件或再次讓它活動(dòng)、修改事件的名稱并加上注釋等。ALTEREVENTevent_name[ONSCHEDULEschedule][RENAMETOnew_event_name][ONCOMPLETION[NOT]PRESERVE][COMMENT'comment'][ENABLE|DISABLE][DOsql_statement]【任務(wù)16.10】臨時(shí)關(guān)閉e_test事件。mysql>ALTEREVENTe_testDISABLE;【任務(wù)16.11】開啟e_test事件。mysql>ALTEREVENTe_testENABLE;【任務(wù)16.12】將每天清空test表改為5天清空一次。mysql>ALTEREVENTtestONSCHEDULEEVERY5DAY;【任務(wù)16.13】重命名事件并加上注釋。mysql>ELTEREVENTSTARTMONTH_INERTRENAMETOSTARTWEEK_INERTCOMMENT'表數(shù)據(jù)操作';刪除事件在MySQL中用DROPEVENT語句刪除事件。語法格式如下。DROPEVENT[IFEXISTS][databasename.]eventname【任務(wù)16.14】刪除事件e_test。mysql>DROPEVENTe_test；項(xiàng)目實(shí)踐創(chuàng)建一個(gè)事件,在2020年5月23日9點(diǎn)30分20秒整清空test表。創(chuàng)建一個(gè)事件,從下個(gè)月開始,每月執(zhí)行一次,并于2020年7月1日結(jié)束。習(xí)題一、編程與應(yīng)用題在數(shù)據(jù)庫bookdb中創(chuàng)建一個(gè)事件，要求每個(gè)星期刪除一次姓名為“探險(xiǎn)者”的用戶所發(fā)的全部留言信息，該事件開始于下個(gè)月并且在2020年12月31日結(jié)束。二、簡答題1．請解釋什么是事件。2．請簡述事件的作用。3．請簡述事件與觸發(fā)器的區(qū)別。THANKS項(xiàng)目七數(shù)據(jù)庫安全與性能優(yōu)化MySQL數(shù)據(jù)庫任務(wù)驅(qū)動(dòng)式教程（第4版）（微課版|AIGC拓展版）任務(wù)17用戶與權(quán)限【任務(wù)背景】MySQL用戶包括root用戶和普通用戶。這兩種用戶的權(quán)限是不一樣的。root用戶是管理員，擁有所有的權(quán)限，包括創(chuàng)建用戶、刪除用戶和修改普通用戶的密碼等管理權(quán)限；普通用戶只擁有創(chuàng)建該用戶時(shí)賦予它的權(quán)限。某校的教學(xué)管理系統(tǒng)，對用戶權(quán)限的要求如下：教務(wù)處管理員有對課程、學(xué)生表和成績表的所有權(quán)限（INSERT、UPDATE、DELETE等）；任課教師可以錄入成績，但不能修改學(xué)生表、課程表數(shù)據(jù)；學(xué)生只能查看（SELECT）相關(guān)表數(shù)據(jù)，而不能更新、刪除。那么，該怎樣建立這些用戶并設(shè)置相應(yīng)的權(quán)限呢？數(shù)據(jù)庫的安全性是指，只允許合法用戶進(jìn)行其權(quán)限范圍內(nèi)的數(shù)據(jù)庫相關(guān)操作，保護(hù)數(shù)據(jù)庫以防止任何不合法的使用所造成的數(shù)據(jù)泄露、更改或破壞。數(shù)據(jù)庫安全性措施主要涉及以下兩個(gè)方面的問題。（1）用戶認(rèn)證問題。（2）訪問權(quán)限問題。【任務(wù)背景】MySQL8.0新加了很多功能。其中在用戶管理中增加了角色的管理；默認(rèn)的密碼加密方式也做了調(diào)整，由之前的SHA1改為了SHA2；同時(shí)，增加了MySQL5.7的禁用用戶和用戶過期的功能設(shè)置，提高了數(shù)據(jù)庫的安全性?！救蝿?wù)要求】本任務(wù)將學(xué)習(xí)用CREATEUSER語句來創(chuàng)建用戶，用ALTER語句設(shè)置用戶密碼，用GRANT語句授予權(quán)限，以及使用REVOKE語句收回權(quán)限，通過修改MySQL授權(quán)表來創(chuàng)建用戶、設(shè)置密碼和授予權(quán)限，學(xué)習(xí)掌握權(quán)限轉(zhuǎn)移、權(quán)限限制以及密碼管理策略、角色管理等方面的知識和技能。任務(wù)17用戶與權(quán)限續(xù)【任務(wù)目標(biāo)】1．知識目標(biāo)（1）理解MySQL用戶包括root用戶和普通用戶，以及它們權(quán)限的差異。（2）掌握數(shù)據(jù)庫安全性的概念，包括用戶認(rèn)證和訪問權(quán)限。（3）理解MySQL中角色的概念，以及角色與權(quán)限的關(guān)系。（4）區(qū)分權(quán)限直接授予用戶和通過角色授予用戶的差異。2．能力目標(biāo)（1）掌握使用命令創(chuàng)建用戶、修改用戶密碼的方法；（2）掌握使用命令授予權(quán)限和收回權(quán)限的方法；（3）通過修改MySQL授權(quán)表來創(chuàng)建用戶、設(shè)置密碼和授予權(quán)限。3．素養(yǎng)目標(biāo)（1）正確使用用戶、角色、權(quán)限，培養(yǎng)良好的職業(yè)道德和合作精神；（2）樹立正確的世界觀、價(jià)值觀、人生觀；（3）有效管理用戶、權(quán)限和角色，以確保數(shù)據(jù)的安全性和合規(guī)性。任務(wù)17用戶與權(quán)限續(xù)創(chuàng)建用戶賬戶用CREATEUSER分別創(chuàng)建能在本地主機(jī)、任意主機(jī)連接數(shù)據(jù)庫的用戶，并設(shè)置密碼。語法格式如下。用CREATEUSER創(chuàng)建用戶CREATEUSERuser[IDENTIFIEDBY[PASSWORD]'password'][,user[IDENTIFIEDBY[PASSWORD]'password']]...【任務(wù)17.1】創(chuàng)建用戶KING，從本地主機(jī)連接MySQL服務(wù)器。mysql>CREATEUSER'KING'@'localhost';【任務(wù)17.2】創(chuàng)建兩個(gè)用戶，用戶名為palo，分別從任意主機(jī)和本地主機(jī)連接MySQL服務(wù)器，指定用戶密碼為“123456”。mysql>CREATEUSER'palo'@'%'

IDENTIFIEDBY'123456','palo'@'localhost'

IDENTIFIEDBY'123456';創(chuàng)建的用戶信息將保存在USER表中。如下命令可以查看創(chuàng)建的用戶情況。MySQL>SELECTUSER,HOST,AUTHENTICATION_STRINGFROMUSER;運(yùn)行結(jié)果如圖17.1所示。圖17.1【任務(wù)17.2】運(yùn)行結(jié)果創(chuàng)建用戶賬戶創(chuàng)建用戶賬戶只有root用戶才可以設(shè)置或修改當(dāng)前用戶或其他特定用戶的密碼。修改用戶密碼【任務(wù)17.3】查看MySQL8.0中用戶表默認(rèn)的身份驗(yàn)證插件。mysql>selectuser,host,pluginfrommysql.user;運(yùn)行結(jié)果如圖所示。圖17.2【任務(wù)17.3】運(yùn)行結(jié)果【任務(wù)17.4】修改king的密碼為queen。mysql>ALTERUSERking@localhostIDENTIFIEDWITHmysql_native_password

BY'queen';mysql>flushprivileges;【任務(wù)17.5】修改密碼時(shí)效為永不過期。mysql>ALTERUSER'root'@'%'IDENTIFIEDBY'123456'PASSWORDEXPIRENEVER;創(chuàng)建用戶賬戶重命名用戶名的語法格式如下。RENAMEUSERold_userTOnew_user,

[,old_userTOnew_user]...其中，old_user為已經(jīng)存在的SQL用戶，new_user為新的SQL用戶。重命名用戶名【任務(wù)17.6】修改king用戶名為ken。mysql>RENAMEUSERking@localhosttoken@localhost;授予用戶權(quán)限新的SQL用戶不允許訪問屬于其他SQL用戶的表，也不能立即創(chuàng)建自己的表，它必須被授權(quán)。在DOS終端運(yùn)行如下命令，用剛才創(chuàng)建的king用戶登錄MySQL服務(wù)器。cdC:\ProgramFiles\MySQL\MySQLServer8.0\binMySQL–uking–p123456嘗試使用USEXSCJ語句進(jìn)入XSCJ數(shù)據(jù)庫，將出現(xiàn)圖17.3所示的錯(cuò)誤。因?yàn)閗ing用戶尚未被授權(quán)，所以不能進(jìn)入XSCJ數(shù)據(jù)庫。圖17.3用戶未授權(quán)錯(cuò)誤提示授予用戶權(quán)限MySQL的權(quán)限可以分為多個(gè)層級。關(guān)于MySQL的權(quán)限數(shù)據(jù)庫層級全局層級列層級表層級便用ON*.*語法賦予權(quán)限。便用ONdb_name.*語法賦予權(quán)限。使用ONdb_name.tbl_name語法賦予權(quán)限。語法格式采用SELECT(col1,col2…)、INSERT(col1,col2…)和UPDATE(col1,col2…)。授予用戶權(quán)限新創(chuàng)建的用戶還沒有任何權(quán)限，不能訪問數(shù)據(jù)庫，不能做任何事情。針對不同用戶對數(shù)據(jù)庫的實(shí)際操作要求，分別授予用戶對特定表的特定字段、特定表、數(shù)據(jù)庫的特定權(quán)限。語法格式如下。用GRANT授權(quán)GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]{tbl_name|*|.|db_name.*}TOuser[IDENTIFIEDBY[PASSWORD]'password'][,user[IDENTIFIEDBY[PASSWORD]'password']]...[WITHwith_option[with_option]...]授予用戶權(quán)限授予對字段或表的權(quán)限字段或表的權(quán)限與說明見表17.1。01OPTION權(quán)限說明SELECT給予用戶使用SELECT語句訪問特定表的權(quán)限INSERT給予用戶使用INSERT語句向一個(gè)特定表中添加行的權(quán)限D(zhuǎn)ELETE給予用戶使用DELETE語句從一個(gè)特定表中刪除行的權(quán)限UPDATE給予用戶使用UPDATE語句修改特定表中值的權(quán)限REFERENCES給予用戶創(chuàng)建一個(gè)外鍵來參照特定表的權(quán)限CREATE給予用戶使用特定的名字創(chuàng)建一個(gè)表的權(quán)限ALTER給予用戶使用ALTERTABLE語句修改表的權(quán)限INDEX給予用戶在表上定義索引的權(quán)限D(zhuǎn)ROP給予用戶刪除表的權(quán)限ALL或ALLPRIVILEGES給予用戶對表所有的權(quán)限授予用戶權(quán)限【任務(wù)17.7】授予用戶king對students表的S_NO列和S_NAME列的UPDATE權(quán)限。mysql>GRANTUPDATE(S_NO,S_NAME)ONstudentsTOking@localhost;【任務(wù)17.8】授予用戶peter、king查看、更新JXGL庫STUDENTS表的權(quán)限。mysql>GRANTSELECT,UPDATEONJXGL.studentsTOpeter@localhost,king@localhost;【任務(wù)17.9】授予用戶peter在students表上定義索引的權(quán)限。mysql>GRANTINDEXONJXGL.STUDENTSTOpeter@localhost;授予用戶權(quán)限授予對庫的權(quán)限數(shù)據(jù)庫的權(quán)限與說明見表17.2。01OPTION權(quán)限說明SELECT給予用戶使用SELECT語句訪問所有表的權(quán)限INSERT給予用戶使用INSERT語句向所有表中添加行的權(quán)限D(zhuǎn)ELETE給予用戶使用DELETE語句從所有表中刪除行的權(quán)限UPDATE給予用戶使用UPDATE語句修改所有表中值的權(quán)限REFERENCES給予用戶創(chuàng)建一個(gè)外鍵來參照所有的表的權(quán)限CREATE給予用戶使用特定的名字創(chuàng)建一個(gè)表的權(quán)限ALTER給予用戶使用ALTERTABLE語句修改表的權(quán)限INDEX給予用戶在所有表上定義索引的權(quán)限D(zhuǎn)ROP給予用戶刪除所有表和視圖的權(quán)限CREATETEMPORARYTABLES給予用戶在特定數(shù)據(jù)庫中創(chuàng)建臨時(shí)表的權(quán)限CREATEVIEW給予用戶在特定數(shù)據(jù)庫中創(chuàng)建新的視圖的權(quán)限SHOWVIEW給予用戶查看特定數(shù)據(jù)庫中已有視圖的視圖定義的權(quán)限CREATEROUTINE給予用戶為特定的數(shù)據(jù)庫創(chuàng)建存儲過程和存儲函數(shù)等權(quán)限ALTERROUTINE給予用戶更新和刪除數(shù)據(jù)庫中已有的存儲過程和存儲函數(shù)等權(quán)限EXECUTEROUTINE給予用戶調(diào)用特定數(shù)據(jù)庫的存儲過程和存儲函數(shù)的權(quán)限LOCKTABLES給予用戶鎖定特定數(shù)據(jù)庫的已有表的權(quán)限ALL或ALLPRIVILEGES表示所有權(quán)限授予用戶權(quán)限【任務(wù)17.10】授予用戶king對JXGL數(shù)據(jù)庫中所有表SELECT、INSERT、UPDATE、DELETE、CREATE、DROP的權(quán)限。mysql>GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROPONJXGL.*TOking@localhost;【任務(wù)17.11】授予用戶david對JXGL數(shù)據(jù)庫中所有表所有的權(quán)限。mysql>GRANTALLONJXGL.*TOdavid@localhost;【任務(wù)17.12】授予用戶stone為JXGL數(shù)據(jù)庫創(chuàng)建存儲過程和存儲函數(shù)權(quán)限。mysql>GRANTCREATEROUTINEONJXGL.*TOstone@localhost;授予對所有庫的權(quán)限【任務(wù)17.13】授予用戶stone操作所有數(shù)據(jù)庫的權(quán)限。03OPTIONmysql>GRANTCREATEUSERON*.*TOstone@localhost;用REVOKE收回權(quán)限根據(jù)實(shí)際情況需要，可以使用REVOKE語句收回用戶的部分或所有權(quán)限。語法格式如下（第一種）。REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON{tbl_name|*|.|db_name.*}FROMuser[,user]...語法格式如下（第二種）。REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser[,user]...其中，第一種格式用來收回某些特定的權(quán)限，第二種格式用來收回某用戶的所有權(quán)限。【任務(wù)17.14】收回用戶king在JXGL庫的SELECT權(quán)限。mysql>REVOKEselectonJXGL.*FROMking@localhost;【任務(wù)17.15】收回用戶king在JXGL庫的所有權(quán)限。mysql>REVOKEallonJXGL.*FROMking@localhost;權(quán)限限制WITH子句也可以對一個(gè)用戶的權(quán)限進(jìn)行限制，語句如下。MAX_QUERIES_PER_HOURcount表示每小時(shí)可以查詢數(shù)據(jù)庫的最大次數(shù)。MAX_CONNECTIONS_PER_HOURcount表示每小時(shí)可以連接數(shù)據(jù)庫的最大次數(shù)。MAX_UPDATES_PER_HOURcount表示每小時(shí)可以修改數(shù)據(jù)庫的最大次數(shù)。

其中，count表示次數(shù)?！救蝿?wù)17.17】授予用戶Jim每小時(shí)只能處理一條SELECT語句的權(quán)限。mysql>GRANTSELECTONXSTOJim@localhostWITHMAX_QUERIES_PER_HOUR1;【任務(wù)17.18】授予用戶king每小時(shí)可以發(fā)出查詢20次、每小時(shí)可以發(fā)出更新10次、每小時(shí)可以連接數(shù)據(jù)庫5次的權(quán)限。mysql>GRANTALLON*.*TO'king'@'localhost'IDENTIFIEDBY'frank'WITHMAX_QUERIES_PER_HOUR20MAX_UPDATES_PER_HOUR10MAX_CONNECTIONS_PER_HOUR5；密碼管理策略要全局建立自動(dòng)密碼到期策略，請使用default_password_lifetime系統(tǒng)變量。其默認(rèn)值為0，表示禁用自動(dòng)密碼到期。如果default_password_lifetime的值為正整數(shù)N，則表示允許的密碼生存期為N天，以便密碼必須每N天更改。該變量可以加在配置文件中。過期時(shí)間管理（1）要建立全局策略，密碼的使用期限大約為6個(gè)月，可在服務(wù)器f文件中使用以下命令啟動(dòng)服務(wù)器。[mysqld]default_password_lifetime=180（2）要建立全局策略，以便密碼永不過期，請將default_password_lifetime設(shè)置為0。[mysqld]default_password_lifetime=0這個(gè)參數(shù)是可以動(dòng)態(tài)設(shè)置并保存的，示例代碼如下。SETPERSISTdefault_password_lifetime=180;SETPERSISTdefault_password_lifetime=0;密碼管理策略（3）創(chuàng)建和修改帶有密碼過期的用戶，賬戶特定的到期時(shí)間設(shè)置示例如下。①

要求每60天更換密碼的代碼如下。CREATEUSER'jack'@'localhost'PASSWORDEXPIREINTERVAL60DAY;ALTERUSER'jack'@'localhost'PASSWORDEXPIREINTERVAL60DAY;②

禁用密碼到期的代碼如下。CREATEUSER'jack'@'localhost'PASSWORDEXPIRE