MySQL數(shù)據(jù)庫應(yīng)用項(xiàng)目教程 課件 項(xiàng)目5 數(shù)據(jù)庫編程

MySQL數(shù)據(jù)庫應(yīng)用

項(xiàng)目教程項(xiàng)目五數(shù)據(jù)庫編程5.1SQL編程基礎(chǔ)

5.2存儲過程和存儲函數(shù)5.3觸發(fā)器、事務(wù)和鎖

5.1SQL編程基礎(chǔ)

5.1.1SQL語言基礎(chǔ)SQL語言是一系列操作數(shù)據(jù)庫及數(shù)據(jù)庫對象的命令語句，因此必須了解基本語法和流程語句的構(gòu)成，主要包括常量和變量、表達(dá)式、運(yùn)算符、控制語句等。1.常量和變量MariaDB的體系結(jié)構(gòu)封裝了SQL接口、查詢解析器、查詢優(yōu)化器、查詢執(zhí)行引擎、緩存/緩沖機(jī)制以及一個(gè)插件式存儲引擎。(1)常量。常量也稱為文字值或標(biāo)量值,是指程序運(yùn)行中值始終不變的量。在SQL程序設(shè)計(jì)過程中,常量的格式取決于它所表示的值的數(shù)據(jù)類型。十進(jìn)制整型常量、十六進(jìn)制整型常量、實(shí)型常量、字符串常量、日期常量、布爾常量、位字段值、NULL值5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)1.常量和變量(2)變量。變量就是在程序執(zhí)行過程中,值是可以改變的量①全局變量和會話變量。顯示系統(tǒng)變量可以使用SHOWVARIABLES語句,語法格式:SHOW[GLOBAL|SESSION]VARIABLES[LIKE‘字符串’];【例1】查看系統(tǒng)變量。查詢所連接的MariaDB數(shù)據(jù)庫的所有全局變量。SHOWGLOBALVARIABLES;查詢所連接的MariaDB數(shù)據(jù)庫中以a開頭的會話變量。SHOWVARIABLESLIKE‘a(chǎn)%’;5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)1.常量和變量【例2】修改系統(tǒng)變量的操作。查詢?nèi)肿兞縮ort_buffer_size的值,然后將其值增加5000并再次查看。SELECT@@GLOBAL.sort_buffer_size;SET@@GLOBAL.sort_buffer_size=@@GLOBAL.sort_buffer_size+5000;SELECT@@GLOBAL.sort_buffer_size;當(dāng)前會話變量sql_select_limit決定了SELECT語句的結(jié)果集中的最大行數(shù)。查看當(dāng)前會話變量sql_select_limit的值,并將其值修改為500。SELECT@@SESSION.sql_select_limit;SET@@SESSION.sql_select_limit=500;SELECT@@SESSION.sql_select_limit;SELECT@@GLOBAL.sql_select_limit;5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)1.常量和變量②用戶變量。用戶變量指的是用戶自己定義的變量。定義和初始化一個(gè)用戶變量使用SET

語句或SELECT語句。語法格式:SET<@用戶變量名>[∶]=<表達(dá)式>[,<@用戶變量名>[∶]=<表達(dá)式>,...];SELECT<@用戶變量名>∶=<表達(dá)式>[,<@用戶變量名>∶=<表達(dá)式>,...];5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)1.常量和變量③局部變量。局部變量的作用范圍是在BEGIN...END語句塊中,可以使用DECLARE語句進(jìn)行定義,然后為變量進(jìn)行賦值。在Mar-iaDB中,定義局部變量的語法格式:DECLARE<局部變量名>[,...]<數(shù)據(jù)類型>[DEFAULTvalue];【例3】局部變量的操作。定義局部變量myvar,數(shù)據(jù)類型為INT,默認(rèn)值為5。DECLAREmyvarINTDEFAULT5;為局部變量賦值50。SETmyvar=50;5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)2.運(yùn)算符MariaDB支持使用運(yùn)算符。通過運(yùn)算符,不僅可以使數(shù)據(jù)庫的功能更加強(qiáng)大,而且可以更加靈活地使用數(shù)據(jù)表中的數(shù)據(jù)。MariaDB運(yùn)算符包括四類,分別是算術(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符和位運(yùn)算符。(1)算術(shù)運(yùn)算符。算術(shù)運(yùn)算符是SQL語言中最常用的運(yùn)算符,主要是對數(shù)值運(yùn)算使用的。算術(shù)運(yùn)算符主要包括加、減、乘、除和取余五種。(2)比較運(yùn)算符。比較運(yùn)算符主要用于比較兩個(gè)表達(dá)式的值,其運(yùn)算結(jié)果為邏輯值,有1(真)、0(假)及NULL(不能確定)三種情況。運(yùn)算符<>和!=用來判斷數(shù)字值、字符串、表達(dá)式等是否不相等。如果不相等,結(jié)果返回1;如果相等,結(jié)果返回0。5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)2.運(yùn)算符【例4】算術(shù)運(yùn)算符和比較運(yùn)算符的操作。使用算術(shù)運(yùn)算符對教學(xué)管理數(shù)據(jù)庫teaching_management中的學(xué)生選修課程信息表student_course的學(xué)生成績score字段值進(jìn)行加、減、乘、除和求余運(yùn)算。SELECTscore,score+10,score-10,score?10,score/10,score%10FROMteaching_management.student_course;運(yùn)用運(yùn)算符<>和!=判斷教學(xué)管理數(shù)據(jù)庫teaching_management中的學(xué)生選修課程信息表student_course的學(xué)生成績score字段值是否等于80。SELECTstudent_no,score<>80,score!=80FROMteaching_management.student_course;5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)2.運(yùn)算符(3)邏輯運(yùn)算符。邏輯運(yùn)算符又稱布爾運(yùn)算符,用來判斷表達(dá)式的真假。邏輯運(yùn)算符的返回結(jié)果只有1和0。如果表達(dá)式是真,結(jié)果將返回1;如果表達(dá)式是假,結(jié)果將返回0。MariaDB中支持與、或、非和異或四種邏輯運(yùn)算符。(4)位運(yùn)算符。位運(yùn)算符是使用二進(jìn)制數(shù)進(jìn)行計(jì)算的運(yùn)算符。位運(yùn)算會先將操作數(shù)變成二進(jìn)制數(shù),然后進(jìn)行位運(yùn)算,最后再將計(jì)算結(jié)果從二進(jìn)制數(shù)轉(zhuǎn)換為十進(jìn)制數(shù)。在MariaDB中有六種位運(yùn)算符,分別是按位與、按位或、按位取反、按位異或、按位左移和按位右移。(5)運(yùn)算符的優(yōu)先級。運(yùn)算符的優(yōu)先級決定了不同的運(yùn)算符在表達(dá)式中計(jì)算的先后順序。5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句在SQL語言中,流程控制語句就是用來控制程序執(zhí)行流程的語句,也稱流控制語句或控制流語句。在MariaDB中,這些流程控制語句和局部變量只能在存儲過程或函數(shù)、觸發(fā)器或事務(wù)的定義中出現(xiàn)。(1)BEGIN...END語句塊。語法格式:[begin_label:]BEGIN[statement_list]END[end_label];5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句【例5】編寫一個(gè)簡單代碼塊。

DELIMITERCREATEPROCEDUREexample_begin()BEGINDECLARExTINYINTUNSIGNEDDEFAULT1;BEGIN DECLARExTINYINTUNSIGNEDDEFAULT2;DECLAREyTINYINTUNSIGNEDDEFAULT10;SELECTx,y;/?查詢局部變量x,y的值?/END;SELECTx;END;DELIMITER;CALLexample_begin();5.1SQL編程基礎(chǔ)(2)IF...ELSE條件語句。它可以指定SQL語句的執(zhí)行條件。如果條件為真,則執(zhí)行條件表達(dá)式后面的SQL語句;如果條件為假,則可以用ELSE關(guān)鍵字指定要執(zhí)行的SQL語句。語法格式:IF<表達(dá)式>THENstatement_list[ELSEIF<表達(dá)式>THENstatement_list]...[ELSEstatement_list]ENDIF;5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句【例6】查詢康養(yǎng)學(xué)院的辦公室位置。如果查詢結(jié)果為空,則顯示“辦公地點(diǎn)不詳”,否152則顯示其辦公地點(diǎn)。DELIMITER//CREATEPROCEDUREexample_if()BEGINUSEteaching_management;IF(SELECTofficeFROMdepartment_infoWHEREname=‘康養(yǎng)學(xué)院’)ISNULLTHEN SELECT‘辦公地點(diǎn)不詳’AS辦公地點(diǎn);ELSE SELECTofficeFROMteaching_management.department_infoWHEREname=‘康養(yǎng)學(xué)院’;ENDIF; END; DELIMITER;CALLexample_if();5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句(3)CASE分支語句。CASE關(guān)鍵字可根據(jù)表達(dá)式的真假來確定是否返回某個(gè)值,允許表達(dá)式的任何位置使用這一關(guān)鍵字。使用CASE語句可以進(jìn)行多個(gè)分支的選擇,CASE語句具有如下兩種格式。

①簡單格式。將某個(gè)表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。CASEcase_value WHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE;②搜索格式。計(jì)算一組布爾表達(dá)式以確定結(jié)果。CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE;5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句(4)循環(huán)語句。在SQL語言中主要有三個(gè)標(biāo)準(zhǔn)的循環(huán)方式:LOOP、REPEAT和WHILE。①LOOP循環(huán)。語法格式:begin_label:LOOP [statement_list]; LEAVEbegin_label; [statement_list]; [ITERATEbegin_label;][statement_list];ENDLOOP[end_label];5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句【例7】計(jì)算1+2+3+…+n。通過IF語句判斷退出循環(huán)的條件,達(dá)到退出條件時(shí)使用LEAVE語句退出循環(huán)。DELIMITERCREATEPROCEDUREexample_loop(nint)BEGIN DECLAREsum,iINTDEFAULT0; loop_label:LOOP SETsum=sum+i; IFi=nTHEN LEAVEloop_label; ENDIF;SETi=i+1;5.1SQL編程基礎(chǔ)ENDLOOPloop_label;SELECTsum;END;DELIMITER;CALLexample_loop(100);5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句②REPEAT循環(huán)。語法格式:begin_label:REPEAT[statement_list];LEAVEbegin_label; [statement_list];[ITERATEbegin_label;][statement_list];UNTILsearch_conditionENDREPEAT[end_label];5.1SQL編程基礎(chǔ)③WHILE循環(huán)。語法格式:begin_label:WHILEsearch_conditionDO [statement_list]; LEAVEbegin_label; [statement_list]; [ITERATEbegin_label;] [statement_list];ENDWHILE[end_label];5.1.1SQL語言基礎(chǔ)3.SQL流程控制語句【例8】使用WHILE循環(huán)計(jì)算1到n之間奇數(shù)的和。

DELIMITER//CREATEPROCEDUREexample_while(nint)BEGIN DECLAREsum,iINTDEFAULT0;while_label: whilei<nDO SETi=i+1; IFmod(i,2)=1THEN SETsum=sum+i; ENDIF; ENDwhile_label; SELECTsum;END; DELIMITER;CALLexample_while(10);5.1SQL編程基礎(chǔ)5.1.1SQL語言基礎(chǔ)4.條件和處理程序的定義(1)定義條件。可以使用DECLARE語句定義條件。語法格式:DECLAREcondition_nameCONDITIONFORcondition_value(2)定義處理程序。語法格式:DECLAREhandler_typeHANDLERFORcondition_value[,condition_value]statement5.注釋(1)單行注釋。①在行前使用#。②在行前使用--(2)多行注釋。使用/??/注釋。5.1SQL編程基礎(chǔ)5.1.2常用函數(shù)1.字符串函數(shù)字符串函數(shù)用于控制字符串返回滿足用戶需求的位置,這些功能僅用于字符型數(shù)據(jù)。ASCII(char)、LENGTH(s)、CHAR_LENGTH(s)、CONCAT(s1,s2,...)、UPPER(s)、CONCAT_WS(sep,s1,s2,...)、INSERT(s1,x,len,s2)、LOWER(s)、LEFT(s,n)、TRIM(s)、RIGHT(s,n)等2.數(shù)學(xué)函數(shù)

常用的數(shù)學(xué)函數(shù)有：ABS(x)函數(shù)、FLOOR(x)函數(shù)、CEILING(x)函數(shù)、GREATEST()函數(shù)、LEAST()函數(shù)、ROUND(x)函數(shù)、TRUNCATE(x,y)函數(shù)、RAND()函數(shù)、RAND(x)函數(shù)、SQRT(x)函數(shù)、MOD(x,y)函數(shù)5.1SQL編程基礎(chǔ)5.1.2常用函數(shù)3.日期和時(shí)間函數(shù)

常用日期和時(shí)間函數(shù)的操作有：CURDATE()函數(shù)、CURTIME()函數(shù)、DATE_ADD()函數(shù)、DATE_SUB()函數(shù)、DAYOFWEEK(d)函數(shù)、DAYOFMONTH(d)函數(shù)、DAYOFYEAR(d)函數(shù)、HOUR(t)函數(shù)、MINUTE(t)函數(shù)、SECOND(t)函數(shù)、⑤NOW()函數(shù)、DATEDIFF()函數(shù)、4.系統(tǒng)函數(shù)

常用的系統(tǒng)函數(shù)：DATABASE()函數(shù)、USER()函數(shù)、VERSION()函數(shù)、CHARSET(str)函數(shù)COLLATION(str)函數(shù)5.1SQL編程基礎(chǔ)5.1.3游標(biāo)1.聲明游標(biāo)在MariaDB中,游標(biāo)主要包括游標(biāo)結(jié)果集合和游標(biāo)位置兩部分。語法格式:DECLAREcursor_nameCURSORFORselect_statement;2.游標(biāo)操作(1)打開游標(biāo)。語法格式:OPENcursor_name;例如，打開先前定義的游標(biāo)course_cursor。OPENcourse_cursor;(2)檢索游標(biāo)。語法格式:FETCHcursor_nameINTOvar_name[,var_name,...];(3)關(guān)閉游標(biāo)關(guān)閉游標(biāo)的語法格式:CLOSEcursor_name;5.1SQL編程基礎(chǔ)5.2存儲過程和存儲函數(shù)

5.2.1熟悉存儲過程和存儲函數(shù)1.聲明游標(biāo)存儲過程是一組可以完成特定功能的SQL語句的集合,經(jīng)編譯后以一個(gè)名稱存儲在數(shù)據(jù)庫中。存儲函數(shù)是一個(gè)存儲的程序,用于接受參數(shù)并獲取該程序執(zhí)行后的返回值。存儲過程和存儲函數(shù)的優(yōu)缺點(diǎn)存儲過程和存儲函數(shù)的缺點(diǎn)。5.2存儲過程和存儲函數(shù)5.2.1熟悉存儲過程和存儲函數(shù)2.存儲過程和存儲函數(shù)參數(shù)存儲過程和存儲函數(shù)能接受輸入?yún)?shù),并把參數(shù)返回給調(diào)用方。參數(shù)傳遞信息的關(guān)鍵字5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)1.創(chuàng)建存儲過程(1)使用CREATEPROCEDURE語句創(chuàng)建存儲過程,語法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}]PROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_body使用ORREPLACE子句，判斷將要?jiǎng)?chuàng)建的存儲過程是否存在。DEFINER子句指定存儲過程執(zhí)行時(shí),檢查權(quán)限所使用的上下文。sp_name參數(shù)表示創(chuàng)建的存儲過程的名稱proc_parameter允許使用空參數(shù)列表,如果給定了參數(shù),則參數(shù)名不區(qū)分大小寫。routine_body部分包含有效的SQL語句。5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)1.創(chuàng)建存儲過程【例9】創(chuàng)建存儲過程。創(chuàng)建統(tǒng)計(jì)教學(xué)管理數(shù)據(jù)庫中學(xué)生信息表的學(xué)生數(shù)的簡單存儲過程,并使用OUT參數(shù)輸出學(xué)生人數(shù)。DELIMITER//CREATEPROCEDUREsp_student_num(OUTs_numINT)BEGIN

SELECTCOUNT(?)INTOs_numFROMteaching_management.student_in-fo;END;//DELIMITER;

CALLsp_student_num(@student_num);SELECT@student_num;5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)2.創(chuàng)建存儲函數(shù)(1)使用CREATEFUNCTION語句創(chuàng)建存儲函數(shù)。使用CREATEFUNCTION語句,必須要具備CREATEROUTINE權(quán)限。語法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}][AGGREGATE]FUNCTION[IFNOTEXISTS]func_name([func_parameter[,...]])RETURNStype[characteristic...]RETURNfunc_body

5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)2.創(chuàng)建存儲函數(shù)【例10】通過一個(gè)參數(shù)傳入值,執(zhí)行一個(gè)SQL內(nèi)置函數(shù)CONCAT(),返回一個(gè)新的字符串。DELIMITER//CREATEFUNCTIONteaching_management.func_string(sCHAR(20))

RETURNSCHAR(50)DETERMINISTIC

RETURNCONCAT(‘Hello,’,s,‘!’);//DELIMITER;SELECTfunc_string(‘world’);5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)2.創(chuàng)建存儲函數(shù)【例11】創(chuàng)建一個(gè)計(jì)數(shù)器函數(shù),使用一個(gè)臨時(shí)表來存儲當(dāng)前的值。可以在函數(shù)內(nèi)部使用一個(gè)語句塊來操作數(shù)據(jù)(即使用DML),如INSERT語句和UPDATE語句。CREATETEMPORARYTABLEcounter(cINT);INSERTINTOcounterVALUES(0);DELIMITER//CREATEFUNCTIONfunc_counter()RETURNSINT

BEGIN

UPDATEcounterSETc=c+1;

RETURN(SELECTcFROMcounterLIMIT1);

END;//DELIMITER;SELECTfunc_counter();5.2存儲過程和存儲函數(shù)5.2.2創(chuàng)建存儲過程和存儲函數(shù)2.創(chuàng)建存儲函數(shù)(2)利用HeidiSQL圖形界面創(chuàng)建存儲函數(shù)。利用HeidiSQL圖形界面創(chuàng)建存儲函數(shù)的步驟與創(chuàng)建存儲過程相似。5.2存儲過程和存儲函數(shù)3.調(diào)用存儲過程和存儲函數(shù)存儲函數(shù)的調(diào)用與MariaDB內(nèi)部函數(shù)的調(diào)用方式相同。(1)在HeidiSQL圖形界面調(diào)用存儲過程和存儲函數(shù)。利用HeidiSQL圖形界面調(diào)用存儲過程和存儲函數(shù)的方法非常簡單。(2)使用SQL語句調(diào)用存儲過程和存儲函數(shù)。①調(diào)用存儲過程。語法格式:CALLsp_name([parameter[,...]]);②調(diào)用存儲函數(shù)。如：調(diào)用存儲函數(shù)func_teacher。SELECTfunc_teacher(‘T01’);5.2.3管理存儲過程和存儲函數(shù)1.查看存儲過程和存儲函數(shù)(1)利用SHOWSTATUS語句查看。語法格式:SHOW[PROCEDURE|FUNCTION]STATUS[LIKE‘pattern’];(2)利用SHOWCREATE語句查看。語法格式:SHOWCREATE[PROCEDURE|FUNCTION]<sp_name|func_name>;(3)從information_schema.ROUTINES數(shù)據(jù)表查看存儲過程或存儲函數(shù)的信息。SELECT?FROMinformation_schema.ROUTINES[WHEREROUTINE_NAME{=‘sp_name|func_name’|LIKE‘pattern’}];5.2存儲過程和存儲函數(shù)5.2.3管理存儲過程和存儲函數(shù)1.查看存儲過程和存儲函數(shù)【例12】查看存儲過程或存儲函數(shù)的操作。查看存儲過程sp_count_test的基本信息。SHOWPROCEDURESTATUSsp_count_test;查看存儲函數(shù)func_teacher_department的基本信息。SHOWCREATEFUNCTIONfunc_teacher_department;從information_schema.ROUTINES數(shù)據(jù)表查看存儲函數(shù)func_counter的信息。SELECT?FROMinformation_schema.ROUTINESWHEREROUTINE_NAME=‘func_counter’;5.2存儲過程和存儲函數(shù)5.2.3管理存儲過程和存儲函數(shù)2.修改存儲過程和存儲函數(shù)語法格式:ALTER[PROCEDURE|FUNCTION]<sp_name|func_name>[characteristic...];【例13】修改存儲過程或存儲函數(shù)的操作。查詢information_schema.ROUTINES數(shù)據(jù)表中存儲過程sp_count_test的信息。SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPEFROMinfor-mation_schema.ROUTINESWHEREROUTINE_NAME=‘sp_count_test’;sp_count_test(‘S01’);修改存儲過程sp_count_test。ALTERPROCEDUREsp_count_testModifiesSQLdataSQLSECURITYIN-VOKER;5.2存儲過程和存儲函數(shù)5.2.3管理存儲過程和存儲函數(shù)3.刪除存儲過程和存儲函數(shù)使用DROP語句可以刪除存儲過程或存儲函數(shù)，語法格式:DROP[PROCEDURE|FUNCTION][IFEXISTS]<sp_name|func_name>;例如,刪除存儲過程sp_count_test的語法格式:DROPPROCEDUREIFEXISTSsp_count_test;5.2存儲過程和存儲函數(shù)5.3觸發(fā)器、事務(wù)和鎖

5.3.1觸發(fā)器1.創(chuàng)建觸發(fā)器觸發(fā)器(Trigger)是特殊的存儲過程,它基于一個(gè)數(shù)據(jù)表創(chuàng)建。(1)利用SQL語句創(chuàng)建觸發(fā)器。語法格式:CREATE[ORREPLACE][DEFINER={user|CURRENT_USER|role|CUR-RENT_ROLE}]TRIGGER[IFNOTEXISTS]trigger_nametrigger_timetrigger_eventONtbl_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_stmt5.3觸發(fā)器、事務(wù)和鎖5.3.1觸發(fā)器1.創(chuàng)建觸發(fā)器【例14】觸發(fā)器的創(chuàng)建操作。在teaching_management數(shù)據(jù)庫中創(chuàng)建一個(gè)INSERT觸發(fā)器tr_class_num。USEteaching_management;DELIMITER//CREATEDEFINER=`root`@`localhost`TRIGGERtr_class_num

AFTERINSERTONstudent_infoFOREACHROW

BEGIN

UPDATEclass_infoSETnumber=number+1WHEREno=NEW.class_no;

END;//DELIMITER;5.3觸發(fā)器、事務(wù)和鎖5.3.1觸發(fā)器1.創(chuàng)建觸發(fā)器(2)利用HeidiSQL圖形界面創(chuàng)建觸發(fā)器。【例15】創(chuàng)建一個(gè)觸發(fā)器tr_update_sno,當(dāng)student_info數(shù)據(jù)表中學(xué)生的學(xué)號發(fā)生變更時(shí),同時(shí)更新student_course數(shù)據(jù)表中相應(yīng)的學(xué)生的學(xué)號。5.3觸發(fā)器、事務(wù)和鎖5.3.1觸發(fā)器2.管理觸發(fā)器(1)查看觸發(fā)器。①用SHOWTRIGGERS語句查看觸發(fā)器信息。語法格式:SHOWTRIGGERS;②在information_schema.TRIGGERS數(shù)據(jù)表中查看觸發(fā)器的信息。(2)刪除觸發(fā)器。語法格式:DROPTRIGGER[db_name.]trigger_name;如：刪除觸發(fā)器tr_update_sno。DROPTRIGGERteaching_management.tr_update_sno;(3)利用HeidiSQL圖形界面管理觸發(fā)器。5.3觸發(fā)器、事務(wù)和鎖5.3.2事務(wù)1.事務(wù)的特性事務(wù)是由有限的數(shù)據(jù)庫操作序列組成的,但并不是任意的數(shù)據(jù)庫操作序列都能成為事務(wù),作為一個(gè)邏輯工作單元,必須有4個(gè)屬性,即原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)及持久性(Durability),簡稱事務(wù)的ACID特性。2.事務(wù)分類

扁平事務(wù)、帶有保存點(diǎn)的扁平事務(wù)、鏈?zhǔn)绞聞?wù)、嵌套事務(wù)、分布式事務(wù)。3.事務(wù)控制事務(wù)控制包括開始事務(wù)、提交事務(wù)、回滾事務(wù)等操作。5.3觸發(fā)器、事務(wù)和鎖5.3.2事務(wù)【例16】修改教學(xué)管理系統(tǒng)中學(xué)生所在班級的班級號,成功則提交事務(wù),否則回滾事務(wù)。DELIMITER//CREATEPROCEDUREsp_student_class()BEGIN

DECLAREl_noCHAR(3);

STARTTRANSACTION;UPDATEteaching_management.student_infoSETclass_no=‘L23’WHEREno=‘T01’;SELECTclass_noINTOl_noFROMteaching_management.student_infoWHEREno=‘T01’;IFl_no=‘L23’THEN

BEGIN5.3觸發(fā)器、事務(wù)和鎖5.3.2事務(wù)

SELECT‘修改成功,提交事務(wù)!’;

COMMIT;

END;

ELSE

BEGIN

SELECT‘修改失敗,回滾事務(wù)!’;

ROLLBACK;

END;

ENDIF;

END;//DELIMITER;callsp_student_class;5.3觸發(fā)器、事務(wù)和鎖5.3.3鎖使用事務(wù)可以解決用戶存取數(shù)據(jù)的問題,從而保證數(shù)據(jù)庫的完整性和一致性,為防止其他用戶修改另一個(gè)還沒有完成的事務(wù)中的數(shù)據(jù),就必須在事務(wù)中使用鎖。1.并發(fā)操作引起的問題(1)丟失更新；(2)臟讀；

(3)不可重復(fù)讀；(4)幻象讀2.事務(wù)隔離級別 四個(gè)隔離級別的特性5.3觸發(fā)器、事務(wù)和鎖5.3.3鎖3.鎖機(jī)制(1)表級鎖定。①讀鎖

MariaDB中用于READ(讀)的表級鎖定的實(shí)現(xiàn)機(jī)制。②寫鎖MariaDB中用于WRITE(寫)的表級鎖定的實(shí)現(xiàn)機(jī)制。LOCKTABLES語句可實(shí)現(xiàn)表級鎖的鎖定,鎖數(shù)據(jù)表的時(shí)候可以使用不同的鎖一次性鎖定多張數(shù)據(jù)表,而解鎖的時(shí)候只能一次性解鎖當(dāng)前客戶端會話的所有數(shù)據(jù)表。語法格式:LOCKTABLEStb_name[[AS]alias]lock_type[,tb_name[[AS]alias]lock_type]...;5.3觸發(fā)器、事務(wù)和鎖5.3.3鎖3.鎖機(jī)制【例17】加鎖操作。在test數(shù)據(jù)庫中創(chuàng)建練習(xí)用的t1、t2數(shù)據(jù)表,并插入測試數(shù)據(jù)。USEtest;DROPTABLEIFEXISTSt1,t2;CREATETABLEt1(aINT,bCHAR(5))ENGINE=MYISAM;CREATETAB