《數(shù)據(jù)庫(kù)原理及應(yīng)用教程-MySQL8.0》課件 第8章 MySQL編程

數(shù)據(jù)庫(kù)原理與應(yīng)用教程-MySQL8.0第8章MySQL編程第8章MySQL編程與程序設(shè)計(jì)語(yǔ)言相比，SQL語(yǔ)言具有高度非過程化的特點(diǎn)，而為了完成較復(fù)雜的操作，就需要進(jìn)行編程，比如使用常量、變量、表達(dá)式、關(guān)鍵字等編寫程序。MySQL提供稱為流程控制的特殊關(guān)鍵字，用于控制SQL語(yǔ)句、語(yǔ)句塊和存儲(chǔ)過程的執(zhí)行流。在數(shù)據(jù)庫(kù)開發(fā)過程中，函數(shù)和存儲(chǔ)過程起著很重要的作用，函數(shù)和存儲(chǔ)過程都是由一個(gè)或多個(gè)SQL語(yǔ)句組成的子程序，可用于封裝代碼以便重復(fù)使用。8.1MySQL編程基礎(chǔ)8.1.1標(biāo)識(shí)符標(biāo)識(shí)符是用來標(biāo)識(shí)事物的符號(hào)，其作用類似于給事物取的名稱。標(biāo)識(shí)符分為合法標(biāo)識(shí)符和引用標(biāo)識(shí)符。1.合法標(biāo)識(shí)符(1)可以由大小寫形式的字母a-z、數(shù)字0-9、美元符號(hào)、下劃線、以及范圍在U+0080到U+FFFF之間的Unicode擴(kuò)展字符構(gòu)成。(2)可以是以上任何字符開頭，但不允許全部是數(shù)字。(3)不能是MySQL關(guān)鍵字，關(guān)鍵字不區(qū)分大小寫。8.1.1標(biāo)識(shí)符2.引用標(biāo)識(shí)符不符合合法標(biāo)識(shí)符格式規(guī)則的標(biāo)識(shí)符必須使用引用標(biāo)識(shí)符，比如使用空格等特殊符號(hào)，使用MySQL關(guān)鍵字以及全部是數(shù)字。引用標(biāo)識(shí)符括在反引號(hào)``中。8.1.2變量變量可以分為兩大類：系統(tǒng)變量和自定義變量。1.系統(tǒng)變量

由系統(tǒng)定義，非用戶定義，屬于服務(wù)器層面。分為全局變量和會(huì)話變量。(1)全局變量全局變量由系統(tǒng)提供且預(yù)先聲明，名稱前加兩個(gè)“@”符號(hào)。用戶不能定義但可以使用全局變量，具有super權(quán)限的用戶還可以對(duì)它們進(jìn)行修改。(2)會(huì)話變量客戶機(jī)連接MySQL服務(wù)器后，都會(huì)產(chǎn)生與之對(duì)應(yīng)的會(huì)話。會(huì)話期間，MySQL服務(wù)實(shí)例會(huì)生成與該會(huì)話對(duì)應(yīng)的會(huì)話變量。用戶可修改，連接斷開失效。8.1.2變量2.自定義變量(1)用戶變量用戶變量與數(shù)據(jù)庫(kù)連接有關(guān)，如果數(shù)據(jù)庫(kù)實(shí)例連接斷開，變量就會(huì)消失。用戶變量以”@”開始，形式為”@變量名”，只對(duì)當(dāng)前用戶使用的客戶端生效。用戶變量在定義和使用時(shí)要在其名稱前加上標(biāo)志“@”，使用SET或SELECT語(yǔ)句定義并賦值。8.1.2變量【例8-1】定義一個(gè)用戶變量@CurrentDateTime，賦值為CURDATE()函數(shù)的值，最后輸出變量@CurrentDateTime的值。SET@CurrentDateTime=CURDATE();SELECT@CurrentDateTimeAS'當(dāng)前的日期和時(shí)間';8.1.2變量(2)局部變量局部變量是根據(jù)需要在局部生效的變量，訪問之前，需要DECLARE聲明。可用作存儲(chǔ)過程或函數(shù)內(nèi)的局部變量和輸入?yún)?shù)局部變量的作用范圍是聲明它的BEGIN...END語(yǔ)句塊內(nèi)。為了與用戶變量進(jìn)行區(qū)分，局部變量不以”@”開頭，它采用普通的標(biāo)識(shí)符名稱。8.1.2變量(2)局部變量局部變量是根據(jù)需要在局部生效的變量，訪問之前，需要DECLARE聲明?？捎米鞔鎯?chǔ)過程或函數(shù)內(nèi)的局部變量和輸入?yún)?shù)局部變量的作用范圍是聲明它的BEGIN...END語(yǔ)句塊內(nèi)。為了與用戶變量進(jìn)行區(qū)分，局部變量不以”@”開頭，它采用普通的標(biāo)識(shí)符名稱。8.1.3運(yùn)算符運(yùn)算符是一種符號(hào)，用來指定要在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作。在MySQL系統(tǒng)中，可以使用的運(yùn)算符可以分為算術(shù)運(yùn)算符、邏輯運(yùn)算符、賦值運(yùn)算符、字符串串聯(lián)運(yùn)算符、按位運(yùn)算符、一元運(yùn)算符及比較運(yùn)算符等。8.1.4注釋兩類注釋符：(1)注釋符“#”用于單行注釋；(2)多行注釋符“/*……*/”。USEteaching;#查詢學(xué)生的學(xué)號(hào)和姓名SELECTsno,snameFROMstudent;/*查詢所有男同學(xué)的學(xué)號(hào)、姓名和專業(yè)*/SELECTsno,sname,majorFROMstudentWHEREssex=’男’;8.2流程控制語(yǔ)句與所有的計(jì)算機(jī)編程語(yǔ)言一樣，MySQL也提供了用于編寫過程性代碼的語(yǔ)法結(jié)構(gòu)，可用來進(jìn)行順序、分支、循環(huán)、函數(shù)、存儲(chǔ)過程等程序設(shè)計(jì)，編寫結(jié)構(gòu)化的模塊代碼，從而提高編程語(yǔ)言的處理能力。8.2.1SET語(yǔ)句一個(gè)SET語(yǔ)句可以定義并初始化多個(gè)變量。SET語(yǔ)句是順序執(zhí)行的，將一個(gè)表達(dá)式賦值給聲明的變量，變量的類型就是表達(dá)式的數(shù)據(jù)類型。8.2.2BEGIN…END語(yǔ)句BEGIN…END語(yǔ)句能夠?qū)⒍鄠€(gè)SQL語(yǔ)句組合成一個(gè)語(yǔ)句塊，并將它們視為一個(gè)單元處理。語(yǔ)法格式：BEGIN

{sql_statement

|statement_block

}END其中，參數(shù){sql_statement|statement_block}為任何有效的SQL語(yǔ)句或語(yǔ)句塊。8.2.3DELIMITER語(yǔ)句在MySQL命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，回車后，MySQL將會(huì)執(zhí)行該命令。但有時(shí)候，一段命令可能輸入較多的語(yǔ)句，且語(yǔ)句中包含有分號(hào)，默認(rèn)情況下，不可能等到用戶把這些語(yǔ)句全部輸入完之后，再執(zhí)行整段語(yǔ)句，因?yàn)镸ySQL一遇到分號(hào)，就要自動(dòng)執(zhí)行?？梢允褂肈ELIMITER語(yǔ)句，把DELIMITER后面換成其它符號(hào)，如//、$$、;;等等，限制整段程序遇到其換成的符號(hào)時(shí)才執(zhí)行。此語(yǔ)句多用在定義子程序、觸發(fā)器等MySQL自己內(nèi)嵌小程序中。8.2.4IF…ELSE語(yǔ)句語(yǔ)法格式：IFBoolean_expression/*條件表達(dá)式，可含有SELECT語(yǔ)句*/THEN{sql_statement}/*條件表達(dá)式為真時(shí)執(zhí)行的語(yǔ)句*/[ELSE{sql_statement}]/*條件表達(dá)式為假時(shí)執(zhí)行的語(yǔ)句*/ENDIF;IF…ELSE語(yǔ)句只能應(yīng)用于MySQL子程序中，所以在后面的函數(shù)、存儲(chǔ)過程、觸發(fā)器等中再進(jìn)行舉例。8.2.5CASE語(yǔ)句使用CASE語(yǔ)句可以進(jìn)行多個(gè)分支的選擇。簡(jiǎn)單CASE格式：將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較（等于），以確定結(jié)果。CASEinput_expressionWHENwhen_expression

THENresult_expression[...n

][

ELSEelse_result_expression]

END8.2.5CASE語(yǔ)句【例8-6】以簡(jiǎn)單CASE格式查詢所有學(xué)生的專業(yè)情況，包括學(xué)號(hào)、姓名以及專業(yè)的英文名。USEteaching;SELECTsno,sname,CASEmajorWHEN'計(jì)算機(jī)科學(xué)與技術(shù)'THEN'ComputerScienceandTechnology'WHEN'信息安全'THEN'InformationSecurity'WHEN'通信工程'THEN'CommunicationEngineering'WHEN'軟件工程'THEN'SoftwareEngineering'ELSE'ElectronicCommerce'ENDASmajorFROMstudent;8.2.5CASE語(yǔ)句搜索CASE格式：計(jì)算一組布爾表達(dá)式，以確定結(jié)果。CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

END8.2.5CASE語(yǔ)句【例8-7】以搜索CASE格式查詢所有學(xué)生的考試等級(jí)，包括學(xué)號(hào)、課程號(hào)以及成績(jī)級(jí)別(a、b、c、d、e)。USEteaching;SELECTsno,cno,

CASEWHENscore>=90then'a'WHENscore>=80then'b'WHENscore>=70then'c'WHENscore>=60then'd'WHENscore<60then'e'ENDASscore_levelFROMsc;8.2.6WHILE語(yǔ)句如果需要重復(fù)執(zhí)行程序中的一部分語(yǔ)句，可使用WHILE循環(huán)語(yǔ)句實(shí)現(xiàn)?？墒褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制循環(huán)中語(yǔ)句的執(zhí)行。語(yǔ)法格式為：WHILEBoolean_expressionessionDO/*條件表達(dá)式*/sql_statement|statement_block/*SQL語(yǔ)句或語(yǔ)句塊構(gòu)成的循環(huán)體*/ENDWHILE;8.3函數(shù)函數(shù)是由一個(gè)或多個(gè)SQL語(yǔ)句組成的子程序，可用于封裝代碼以便重復(fù)使用。SQL語(yǔ)言提供了豐富的數(shù)據(jù)操作函數(shù)，用以完成各種數(shù)據(jù)管理工作。MySQL允許用戶創(chuàng)建自己的用戶定義函數(shù)。8.3.1系統(tǒng)內(nèi)置函數(shù)MySQL系統(tǒng)內(nèi)置函數(shù)按函數(shù)的功能分類可分為系統(tǒng)信息函數(shù)、聚合函數(shù)、數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)等類型。8.3.1系統(tǒng)內(nèi)置函數(shù)1.數(shù)學(xué)函數(shù)可以滿足數(shù)據(jù)庫(kù)維護(hù)人員日常的數(shù)值計(jì)算需要【例8-8】求下列語(yǔ)句的執(zhí)行結(jié)果。SELECTFLOOR(10.9),FLOOR(-10.9);執(zhí)行結(jié)果為：10,-112.日期和時(shí)間函數(shù)用于進(jìn)行時(shí)間方面的處理工作【例8-9】計(jì)算現(xiàn)在是幾月。SELECTMONTH(CURDATE());8.3.1系統(tǒng)內(nèi)置函數(shù)3．聚合函數(shù)聚合函數(shù)在結(jié)果集中通過對(duì)被選列值的收集處理并返回一個(gè)數(shù)值型的計(jì)算結(jié)果。4．字符串函數(shù)主要用來處理二進(jìn)制類型的數(shù)據(jù)和文本類型的數(shù)據(jù)。【例8-10】將字符串“Iamastudent”以大寫字母顯示。SELECTUPPER('Iamastudent');8.3.1系統(tǒng)內(nèi)置函數(shù)5.IF函數(shù)用于條件判斷的函數(shù)，包括IF()和IFNULL()函數(shù)。（1）IF()函數(shù)IF(condition_expression,result_expression1,result_expression2)【例8-11】查詢teaching數(shù)據(jù)庫(kù)中的sc表，如果成績(jī)小于60，則輸出“不及格”，否則輸出“成績(jī)合格或?yàn)榭铡薄SEteaching;SELECTsno,cno,score,IF(score<60,'不及格','成績(jī)合格或?yàn)榭?)ASlevelFROMsc;8.3.1系統(tǒng)內(nèi)置函數(shù)（2）IFNULL()函數(shù)IFNULL(expression1,experssion2)【例8-12】查詢teaching數(shù)據(jù)庫(kù)中的sc表，如果成績(jī)不為為空，則顯示成績(jī)值，否則顯示“缺考”。USEteaching;SELECTsno,cno,IFNULL(score,'缺考')AS成績(jī)FROMsc;8.3.2用戶定義函數(shù)用戶定義函數(shù)可以針對(duì)特定應(yīng)用程序問題提供解決方案。用戶定義函數(shù)都是經(jīng)過封裝的SQL子程序，其他SQL代碼可以調(diào)用這些子程序來返回單一的值。1.函數(shù)的創(chuàng)建CREATEFUNCTIONfunction_name/*函數(shù)名部分*/

([{parameter_namepar_data_type}[,...n]])/*形參定義部分*/RETURNSreturn_data_type/*返回參數(shù)的類型*/BEGINfunction_body;/*函數(shù)體部分*/

RETURNexpression;/*返回語(yǔ)句*/END8.3.2用戶定義函數(shù)【例8-13】求sc表中某門課的平均成績(jī)。USEteaching;DELIMITER//CREATEFUNCTIONaverage(cnchar(4))RETURNSfloatBEGINDECLAREaverfloat;SETaver=(SELECTavg(score)FROMscWHEREcno=cn);RETURNaver;END//8.3.2用戶定義函數(shù)2.查看函數(shù)可以通過可視化管理工具的菜單界面、也可以通過SQL語(yǔ)句查看用戶自定義函數(shù)。3.調(diào)用函數(shù)在其它程序模塊中可以調(diào)用函數(shù)，語(yǔ)法格式：SELECTfunction_name(parameter_expression1…parameter_expressionn);【例8-15】求C001號(hào)課的平均成績(jī)。USEteaching;SELECTaverage('C001')AS'C001號(hào)課程的平均成績(jī)';8.3.2用戶定義函數(shù)【例8-16】創(chuàng)建函數(shù)，顯示兩門課中平均成績(jī)高的那門課的課程號(hào)。USEteaching;DELIMITER//CREATEFUNCTIONaverage1(cn1char(4),cn2char(4))RETURNSchar(4)BEGINDECLAREaver1,aver2float;SETaver1=(SELECTavg(score)FROMscWHEREcno=cn1);SETaver2=(SELECTavg(score)FROMscWHEREcno=cn2);8.3.2用戶定義函數(shù)IFaver1>=aver2/*IF…ELSE語(yǔ)句的應(yīng)用*/THENRETURNcn1;ELSERETURNcn2;ENDIF;END//【例8-17】判斷C001和C002號(hào)課哪個(gè)平均成績(jī)高。USEteaching;SELECTaverage1('C001','C002')AS'平均成績(jī)高的課程';8.3.2用戶定義函數(shù)4.刪除函數(shù)可以通過可視化管理工具的菜單界面、也可以通過SQL語(yǔ)句刪除用戶自定義函數(shù)?！纠?-18】通過DROPFUNCTION語(yǔ)句刪除average函數(shù)。USEteaching;DROPFUNCTIONaverage;8.4存儲(chǔ)過程存儲(chǔ)過程就是一個(gè)具有獨(dú)立功能的子程序，以特定的名稱存儲(chǔ)在數(shù)據(jù)庫(kù)中。8.4.1存儲(chǔ)過程概述存儲(chǔ)過程是SQL語(yǔ)句和可選流程控制語(yǔ)句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，能夠提高系統(tǒng)的應(yīng)用效率和執(zhí)行速度。存儲(chǔ)過程是一種獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)的對(duì)象，可以接受輸入?yún)?shù)、輸出參數(shù)，可以返回單個(gè)值或多個(gè)結(jié)果集，也可以完成數(shù)據(jù)庫(kù)中數(shù)據(jù)更新，由應(yīng)用程序通過調(diào)用執(zhí)行。8.4.1存儲(chǔ)過程概述存儲(chǔ)過程的優(yōu)點(diǎn)：(1)模塊化程序設(shè)計(jì)。只需創(chuàng)建一次，可在程序中調(diào)用任意次?？捎稍跀?shù)據(jù)庫(kù)編程方面有專長(zhǎng)的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。

(2)加快SQL語(yǔ)句的執(zhí)行速度。創(chuàng)建存儲(chǔ)過程時(shí)對(duì)其進(jìn)行分析和優(yōu)化并預(yù)先編譯好放在數(shù)據(jù)庫(kù)內(nèi)，減少編譯語(yǔ)句所花的時(shí)間。(3)減少網(wǎng)絡(luò)流量。一條執(zhí)行過程代碼的單獨(dú)語(yǔ)句。(4)更高地安全性?？梢允谟栌脩魣?zhí)行存儲(chǔ)過程的權(quán)限，而不授予其直接訪問數(shù)據(jù)庫(kù)對(duì)象（表、視圖等）的權(quán)限。8.4.2創(chuàng)建存儲(chǔ)過程MySQL創(chuàng)建存儲(chǔ)過程的SQL語(yǔ)句語(yǔ)法格式如下：CREATEPROCEDUREprocedure_name([IN|OUT|INOUT][parameter_namedata_type][,…n])BEGINprocedure_body;END8.4.2創(chuàng)建存儲(chǔ)過程【例8-19】在teaching庫(kù)創(chuàng)建無參存儲(chǔ)過程，查詢每個(gè)學(xué)生的平均成績(jī)。USEteaching;DELIMITER//CREATEPROCEDUREstudent_avg()BEGINSELECTsno,avg(score)as'avgscore'FROMscGROUPBYsno;END//8.4.2創(chuàng)建存儲(chǔ)過程【例8-20】在teaching庫(kù)創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程，查詢某個(gè)學(xué)生的基本信息。USEteaching;DELIMITER//CREATEPROCEDUREGetStudent(numberchar(10))BEGINSELECT*FROMstudentWHEREsno=number；END//8.4.2創(chuàng)建存儲(chǔ)過程【例8-21】在teaching庫(kù)創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程，修改某個(gè)學(xué)生某門課的成績(jī)。USEteaching;DELIMITER//CREATEPROCEDUREUpdate_score(numberchar(10),cnchar(4),sc1int)BEGINUPDATEscSETscore=sc1WHEREsno=numberandcno=cn;END//8.4.2創(chuàng)建存儲(chǔ)過程【例8-22】在bankcard數(shù)據(jù)庫(kù)中使用流程控制語(yǔ)句創(chuàng)建存儲(chǔ)過程。假設(shè)今天銀行有活動(dòng)，如果今天某賬號(hào)交易支出總金額超過3000元，則獎(jiǎng)勵(lì)其10元。USEbankcard;DELIMITER//CREATEPROCEDUREadd_10(ANOchar(20))BEGINIF(SELECTSUM(Expense)FROMTrecordWHERETDate=CURDATE()ANDAccNO=ANO)>=3000THENUPDATEAccountSETBalance=Balance+10WHEREAccNO=ANO;INSERTINTOTrecord(TDate,AccNO,Income,Abstract)VALUES(CURDATE(),ANO,10,'銀行活動(dòng)獎(jiǎng)勵(lì)');ENDIF;END//8.4.2創(chuàng)建存儲(chǔ)過程【例8-23】在bankcard數(shù)據(jù)庫(kù)創(chuàng)建帶OUT參數(shù)的存儲(chǔ)過程，用于計(jì)算指定的儲(chǔ)戶的總余額，存儲(chǔ)過程中使用一個(gè)輸入?yún)?shù)（身份證號(hào)）和兩個(gè)輸出參數(shù)（儲(chǔ)戶姓名和總余額）。USEbankcard;DELIMITER//CREATEPROCEDUREs_balance(INidchar(18),OUTdnvarchar(10),OUTsbalafloat)BEGINSELECTDnameINTOdnFROMdepositorWHEREIDNO=id;SELECTSUM(Balance)INTOsbalaFROMaccount WHEREIDNO=id;END//8.4.2創(chuàng)建存儲(chǔ)過程【例8-24】創(chuàng)建存儲(chǔ)過程，返回兩門課中平均成績(jī)高的那門課的課程號(hào)和平均成績(jī)。USEteaching;DELIMITER//CREATEPROCEDUREaverage2(INOUTcn1char(4),INcn2char(4),OUTavgscoredecimal(3,1))BEGINDECLAREaver2decimal(3,1);SETavgscore=(SELECTavg(score)FROMscWHEREcno=cn1);SETaver2=(SELECTavg(score)FROMscWHEREcno=cn2);IFaver2>avgscoreTHENSETcn1=cn2;SETavgscore=aver2;ENDIF;END//8.4.3調(diào)用存儲(chǔ)過程調(diào)用即執(zhí)行存儲(chǔ)過程，可以使用CALL命令。對(duì)于存儲(chǔ)過程的所有者或任何一名對(duì)此過程擁有CALL權(quán)限的用戶，都可以執(zhí)行此存儲(chǔ)過程。如果被調(diào)用的存儲(chǔ)過程需要參數(shù)輸入時(shí)，在存儲(chǔ)過程名后逐一給定，每一個(gè)參數(shù)用逗號(hào)隔開，參數(shù)的排列必須和建立存儲(chǔ)過程所定義的次序?qū)?yīng)。(1)無參存儲(chǔ)過程的調(diào)用語(yǔ)句CALL

procedure_name[()]；(2)有參存儲(chǔ)過程的調(diào)用語(yǔ)句CALL

procedure_name(parameter[,...]);8.4.3調(diào)用存儲(chǔ)過程【例8-25