MySQL數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程第7章

1、MySQLMySQL數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)例教程 之之視圖觸發(fā)器視圖觸發(fā)器1 12 23 3 本章首先講解了本章首先講解了視圖以及觸發(fā)器的視圖以及觸發(fā)器的管理及使用，然后管理及使用，然后結(jié)合結(jié)合“選課系統(tǒng)選課系統(tǒng)”分別介紹視圖以及分別介紹視圖以及觸發(fā)器在該系統(tǒng)中觸發(fā)器在該系統(tǒng)中的應(yīng)用。的應(yīng)用。 4 4觸發(fā)器觸發(fā)器視圖視圖臨時(shí)表臨時(shí)表派生表（派生表（Derived Table）5 5視圖、子查詢、臨時(shí)表、派生表視圖、子查詢、臨時(shí)表、派生表內(nèi)容一覽內(nèi)容一覽 作業(yè) P213 1、2、3（檢查視圖）、4、6。 復(fù)習(xí) 1、2、3（重點(diǎn)第3章） 視圖與表有很多相似視圖與表有很多相似的地方，的地

2、方，視圖也是由若視圖也是由若干個(gè)字段以及若干條記干個(gè)字段以及若干條記錄構(gòu)成，視圖也可以作錄構(gòu)成，視圖也可以作為為select語(yǔ)句的數(shù)據(jù)源。語(yǔ)句的數(shù)據(jù)源。甚至在某些特定條件下，甚至在某些特定條件下，可以通過(guò)視圖對(duì)表進(jìn)行可以通過(guò)視圖對(duì)表進(jìn)行更新操作。更新操作。7.1 7.1 視圖視圖1 12 23 3 視圖中保存的僅僅視圖中保存的僅僅是一條是一條select語(yǔ)句，語(yǔ)句，視圖中的源數(shù)據(jù)都來(lái)視圖中的源數(shù)據(jù)都來(lái)自于數(shù)據(jù)庫(kù)表，數(shù)據(jù)自于數(shù)據(jù)庫(kù)表，數(shù)據(jù)庫(kù)表稱為基本表或者庫(kù)表稱為基本表或者基表，視圖稱為基表，視圖稱為。 查看視圖的定義查看視圖的定義創(chuàng)建視圖創(chuàng)建視圖視圖在視圖在“選課系統(tǒng)選課系統(tǒng)”中的應(yīng)用中的應(yīng)用

3、45視圖的作用視圖的作用刪除視圖刪除視圖7.1 7.1 視圖視圖6 67 7 視圖中保存的僅僅視圖中保存的僅僅是一條是一條select語(yǔ)句，語(yǔ)句，視圖中的源數(shù)據(jù)都來(lái)視圖中的源數(shù)據(jù)都來(lái)自于數(shù)據(jù)庫(kù)表，數(shù)據(jù)自于數(shù)據(jù)庫(kù)表，數(shù)據(jù)庫(kù)表稱為基本表或者庫(kù)表稱為基本表或者基表，視圖稱為虛表?；恚晥D稱為虛表。 檢查視圖檢查視圖local與與cascade檢查視圖檢查視圖7.1 7.1 視圖視圖創(chuàng)建視圖的語(yǔ)法格式如下。創(chuàng)建視圖的語(yǔ)法格式如下。create view 視圖名視圖名 (視圖字段列表視圖字段列表) asselect語(yǔ)句語(yǔ)句對(duì)于經(jīng)常使用的、結(jié)構(gòu)復(fù)雜的對(duì)于經(jīng)常使用的、結(jié)構(gòu)復(fù)雜的select語(yǔ)句，語(yǔ)句，建議

4、將其封裝為視圖建議將其封裝為視圖 ”.簡(jiǎn)化數(shù)據(jù)查詢操作。簡(jiǎn)化數(shù)據(jù)查詢操作。7.1.1 7.1.1 創(chuàng)建視圖創(chuàng)建視圖7.1.1 創(chuàng)建視圖 視圖視圖(view)為數(shù)據(jù)查詢提供了一條捷徑為數(shù)據(jù)查詢提供了一條捷徑,簡(jiǎn)化數(shù)簡(jiǎn)化數(shù)據(jù)查詢操作據(jù)查詢操作. 創(chuàng)建所有班級(jí)的學(xué)生情況視圖 create view v1_view AS select classes1.class_no,class_name,department_name,student_no,student_name from student1 right join classes1 on student1.class_no=classes1.cl

5、ass_no; select * from v1_view where class_no=1; select * from v1_view where class_no=2;任務(wù)布置任務(wù)布置1： “對(duì)于經(jīng)常使用的、結(jié)構(gòu)復(fù)雜的對(duì)于經(jīng)常使用的、結(jié)構(gòu)復(fù)雜的select語(yǔ)句，建議將其封裝為視圖語(yǔ)句，建議將其封裝為視圖 ”。例如：為統(tǒng)計(jì)每門課程還能供多少同學(xué)選修，例如：為統(tǒng)計(jì)每門課程還能供多少同學(xué)選修，定義名為定義名為 available_course_view的視圖。的視圖。7.1.1 7.1.1 創(chuàng)建視圖創(chuàng)建視圖create view available_course_viewASselect co

6、urse.course_no,course_name,teacher_name,up_limit,count(*) as student_num,up_limit-count(*) as availablefrom teacher inner join courseon teacher.teacher_no=course.teacher_noinner join chooseon choose.course_no=course.course_nogroup by course.course_no;Select * from available_course_view;可以使用下面可以使用下面四

7、種方法查看視圖的定義四種方法查看視圖的定義。 1在在choose數(shù)據(jù)庫(kù)中成功地創(chuàng)建了視圖數(shù)據(jù)庫(kù)中成功地創(chuàng)建了視圖available_course_view后，該視圖的定義默后，該視圖的定義默認(rèn)保存在數(shù)據(jù)庫(kù)目錄（例如認(rèn)保存在數(shù)據(jù)庫(kù)目錄（例如choose目錄）下，目錄）下，文件名為文件名為available_course_view.frm。使用。使用記事本打開(kāi)該文件，即可查看該視圖的定義。記事本打開(kāi)該文件，即可查看該視圖的定義。7.1.2 7.1.2 查看視圖的定義查看視圖的定義可以使用下面四種方法查看視圖的定義。可以使用下面四種方法查看視圖的定義。 2視圖是一個(gè)虛表，也可以使用查看表結(jié)構(gòu)視圖是一

8、個(gè)虛表，也可以使用查看表結(jié)構(gòu)的方式查看視圖的定義的方式查看視圖的定義 desc v1_view。 3MySQL命令命令“show tables;”命令不僅顯命令不僅顯示當(dāng)前數(shù)據(jù)庫(kù)中所有的基表，也會(huì)將所有的示當(dāng)前數(shù)據(jù)庫(kù)中所有的基表，也會(huì)將所有的視圖羅列出來(lái)。視圖羅列出來(lái)。7.1.2 7.1.2 查看視圖的定義查看視圖的定義可以使用下面四種方法查看視圖的定義?？梢允褂孟旅嫠姆N方法查看視圖的定義。 4MySQL系統(tǒng)數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)庫(kù)information_schema的的views表存儲(chǔ)了所有視圖的定義，使用下面表存儲(chǔ)了所有視圖的定義，使用下面的的select語(yǔ)句查詢?cè)摫淼乃杏涗?，也可以語(yǔ)句查詢?cè)摫?/p>

9、的所有記錄，也可以查看所有視圖的詳細(xì)信息。查看所有視圖的詳細(xì)信息。select * from information_schema.viewsG7.1.2 7.1.2 查看視圖的定義查看視圖的定義原來(lái)的原來(lái)的course表的結(jié)構(gòu)表的結(jié)構(gòu): dese course;從現(xiàn)在開(kāi)始，新課程從現(xiàn)在開(kāi)始，新課程course表中比原來(lái)的課程表中比原來(lái)的課程course表表多了一個(gè)多了一個(gè)“剩余的學(xué)生名剩余的學(xué)生名額額”available字段。字段。任務(wù)布置任務(wù)布置2：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述2的的任務(wù)要求。任務(wù)要求。7.1.3 7.1.3 視圖在視圖在“選課系統(tǒng)選課系統(tǒng)”中的應(yīng)

10、用中的應(yīng)用7.1.3 視圖在“選課系統(tǒng)”中的應(yīng)用 1、設(shè)置available字段的默認(rèn)值。 alter table course add available int default 0; 2、查詢新課程course表的信息 desc course;select course_no,course_name,up_limit,available from course;7.1.3 視圖在“選課系統(tǒng)”中的應(yīng)用 3、從視圖中獲取信息修改原表的數(shù)據(jù)從視圖中獲取信息修改原表的數(shù)據(jù). update course set available=up_limit- (select student_num from

11、 available_course_view where course_no=course.course_no); 4、查詢course表的信息 select course_no,course_name,up_limit,available from course;1使操作變得簡(jiǎn)單使操作變得簡(jiǎn)單2避免數(shù)據(jù)冗余避免數(shù)據(jù)冗余3增強(qiáng)數(shù)據(jù)安全性增強(qiáng)數(shù)據(jù)安全性4提高數(shù)據(jù)的邏輯獨(dú)立性提高數(shù)據(jù)的邏輯獨(dú)立性7.1.4 7.1.4 視圖的作用視圖的作用如果某個(gè)視圖不再使用，可以使用如果某個(gè)視圖不再使用，可以使用drop view語(yǔ)句將該視圖刪除，語(yǔ)法格式如下。語(yǔ)句將該視圖刪除，語(yǔ)法格式如下。drop view

12、視圖名視圖名7.1.5 7.1.5 刪除視圖刪除視圖視圖分為普通視圖與檢查視圖視圖分為普通視圖與檢查視圖,沒(méi)有使用沒(méi)有使用with check optiom子句的視圖都是普通試圖子句的視圖都是普通試圖。 通過(guò)檢查視圖更新基表數(shù)據(jù)時(shí)，只有滿足通過(guò)檢查視圖更新基表數(shù)據(jù)時(shí)，只有滿足檢查條件的更新語(yǔ)句才能成功執(zhí)行。創(chuàng)建檢檢查條件的更新語(yǔ)句才能成功執(zhí)行。創(chuàng)建檢查視圖的語(yǔ)法格式如下。查視圖的語(yǔ)法格式如下。create view 視圖名視圖名 (視圖字段列表視圖字段列表) asselect語(yǔ)句語(yǔ)句with local | cascaded check option7.1.6 7.1.6 檢查視圖檢查視圖任務(wù)

13、布置任務(wù)布置3：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述3：普通視圖與更新操作普通視圖與更新操作的任務(wù)要求。的任務(wù)要求。 創(chuàng)建成績(jī)不及格的選修試圖創(chuàng)建成績(jī)不及格的選修試圖choose_1_view create view choose_1_view as select * from choose where score60;7.1.6 7.1.6 檢查視圖檢查視圖一、插入數(shù)據(jù)一、插入數(shù)據(jù) insert into choose_1_view values null,2012003,2,100,now(); select * from choose; Select * from ch

14、oose_1_view;二、刪除數(shù)據(jù)二、刪除數(shù)據(jù) delete from choose where student_no=2012003 and course_no=2; Select * from choose; Select * from choose_1_view; 普通視圖不具備普通視圖不具備”檢查檢查”功能功能.7.1.6 檢查視圖 任務(wù)布置4：上機(jī)操作，完成本書(shū)場(chǎng)景描述4： 檢查視圖與更新操作的任務(wù)要求。 創(chuàng)建成績(jī)不及格的選修試圖choose_2_view create view choose_2_view as select * from choose where score60

15、 with local check option; insert into choose_2_view values (null,2012004,2,100,now(); 然后分析普通視圖與檢查視圖之間的區(qū)別。 定義的格式不同. 更新基表數(shù)據(jù)時(shí),檢查視圖對(duì)檢查數(shù)據(jù)先檢查,而后更新，不滿足檢查視圖定義的檢查條件，則更新失敗。而普通視圖不具備“檢查”功能。如：p 213-3 create view choose_3_view as select * from choose where score between 0 and 100 with local check option; insert i

16、nto choose_3_view values (null,2012005,2,110,now(); insert into choose_3_view values (null,2012005,2,-12,now(); insert into choose_3_view values (null,2012005,2,98,now();select * from choose;select * from choose_3_view;檢查視圖分為檢查視圖分為local檢查視圖與檢查視圖與cascade檢查檢查視圖。視圖。with_check_option的值為的值為1時(shí)表示時(shí)表示local（l

17、ocal視圖），視圖），值為值為2時(shí)表示時(shí)表示cascade（級(jí)聯(lián)視圖，在視圖的（級(jí)聯(lián)視圖，在視圖的基礎(chǔ)上再次創(chuàng)建另一個(gè)視圖）。基礎(chǔ)上再次創(chuàng)建另一個(gè)視圖）。7.1.6 local7.1.6 local與與cascadecascade檢查視圖檢查視圖local 檢查檢查視圖與視圖與cascade檢檢查視圖查視圖7.1.6 local7.1.6 local與與cascadecascade檢查視圖檢查視圖1 12 23 3觸發(fā)器主要用于監(jiān)視某觸發(fā)器主要用于監(jiān)視某個(gè)表的個(gè)表的insert、update以及以及delete等更新操作，等更新操作，這些操作可以分別激活這些操作可以分別激活該表的該表的ins

18、ert、update或者或者delete類型的觸發(fā)類型的觸發(fā)程序運(yùn)行，從而實(shí)現(xiàn)數(shù)程序運(yùn)行，從而實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)維護(hù)。據(jù)的自動(dòng)維護(hù)。 使用觸發(fā)器實(shí)現(xiàn)檢查約束使用觸發(fā)器實(shí)現(xiàn)檢查約束準(zhǔn)備工作準(zhǔn)備工作使用觸發(fā)器維護(hù)冗余數(shù)據(jù)使用觸發(fā)器維護(hù)冗余數(shù)據(jù)45使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)查看觸發(fā)器的定義查看觸發(fā)器的定義7.2 7.2 觸發(fā)器觸發(fā)器6 67 7觸發(fā)器主要用于監(jiān)視某觸發(fā)器主要用于監(jiān)視某個(gè)表的個(gè)表的insert、update以及以及delete等更新操作，等更新操作，這些操作可以分別激活這些操作可以分別激活該表的該表的insert、update或者或者delete類型的觸發(fā)程類型的

19、觸發(fā)程序運(yùn)行，從而實(shí)現(xiàn)數(shù)據(jù)序運(yùn)行，從而實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)維護(hù)。的自動(dòng)維護(hù)。 刪除觸發(fā)器刪除觸發(fā)器使用觸發(fā)器的注意事項(xiàng)使用觸發(fā)器的注意事項(xiàng)7.2 7.2 觸發(fā)器觸發(fā)器7.2 7.2 觸發(fā)器觸發(fā)器使用使用create trigger語(yǔ)句可以創(chuàng)建一個(gè)觸發(fā)器，語(yǔ)句可以創(chuàng)建一個(gè)觸發(fā)器，語(yǔ)法格式如下。語(yǔ)法格式如下。create trigger 觸發(fā)器名觸發(fā)器名 觸發(fā)時(shí)間觸發(fā)時(shí)間 觸發(fā)事件觸發(fā)事件 on 表名表名 for each rowbegin觸發(fā)程序觸發(fā)程序end7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作MySQLMySQL的觸發(fā)事件有三種：的觸發(fā)事件有三種： insert insert：將新記錄插入表時(shí)激

20、活觸發(fā)程序，將新記錄插入表時(shí)激活觸發(fā)程序，例如通過(guò)例如通過(guò)insertinsert、load dataload data和和replacereplace語(yǔ)句，可語(yǔ)句，可以激活觸發(fā)程序運(yùn)行。以激活觸發(fā)程序運(yùn)行。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作MySQLMySQL的觸發(fā)事件有三種：的觸發(fā)事件有三種： updateupdate：更改某一行記錄時(shí)激活觸發(fā)程序，更改某一行記錄時(shí)激活觸發(fā)程序，例如通過(guò)例如通過(guò)updateupdate語(yǔ)句，可以激活觸發(fā)程序運(yùn)行。語(yǔ)句，可以激活觸發(fā)程序運(yùn)行。 deletedelete：從表中刪除某一行記錄時(shí)激活觸發(fā)從表中刪除某一行記錄時(shí)激活觸發(fā)程序，例如通過(guò)程序，例如

21、通過(guò)deletedelete和和replacereplace語(yǔ)句，可以激活語(yǔ)句，可以激活觸發(fā)程序運(yùn)行。觸發(fā)程序運(yùn)行。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作 觸發(fā)器的觸發(fā)時(shí)間有兩種：觸發(fā)器的觸發(fā)時(shí)間有兩種：beforebefore與與afterafter。beforebefore表示在觸發(fā)事件發(fā)生之前執(zhí)行觸發(fā)程表示在觸發(fā)事件發(fā)生之前執(zhí)行觸發(fā)程序。序。afterafter表示在觸發(fā)事件發(fā)生之后執(zhí)行觸發(fā)器表示在觸發(fā)事件發(fā)生之后執(zhí)行觸發(fā)器。因此嚴(yán)格意義上講一個(gè)數(shù)據(jù)庫(kù)表最多可以設(shè)因此嚴(yán)格意義上講一個(gè)數(shù)據(jù)庫(kù)表最多可以設(shè)置六種類型的觸發(fā)器。置六種類型的觸發(fā)器。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作for

22、 each rowfor each row表示行級(jí)觸發(fā)器。表示行級(jí)觸發(fā)器。 目前目前MySQLMySQL僅支持行級(jí)觸發(fā)器，不支持語(yǔ)僅支持行級(jí)觸發(fā)器，不支持語(yǔ)句級(jí)別的觸發(fā)器（例如句級(jí)別的觸發(fā)器（例如create tablecreate table等語(yǔ)等語(yǔ)句）。句）。for each rowfor each row表示更新（表示更新（insertinsert、updateupdate或者或者deletedelete）操作影響的每一條記錄）操作影響的每一條記錄都會(huì)執(zhí)行一次觸發(fā)程序。都會(huì)執(zhí)行一次觸發(fā)程序。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作觸發(fā)程序中可以使用觸發(fā)程序中可以使用old關(guān)鍵字與關(guān)鍵字與

23、new關(guān)鍵字。關(guān)鍵字。 當(dāng)向表插入新記錄時(shí)，在觸發(fā)程序中可當(dāng)向表插入新記錄時(shí)，在觸發(fā)程序中可以使用以使用new關(guān)鍵字表示新記錄，當(dāng)需要訪問(wèn)關(guān)鍵字表示新記錄，當(dāng)需要訪問(wèn)新記錄的某個(gè)字段值時(shí)，可以使用新記錄的某個(gè)字段值時(shí)，可以使用“new.字字段名段名”的方式訪問(wèn)。的方式訪問(wèn)。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作觸發(fā)程序中可以使用觸發(fā)程序中可以使用old關(guān)鍵字與關(guān)鍵字與new關(guān)鍵字。關(guān)鍵字。 當(dāng)從表中刪除某條舊記錄時(shí)，在觸發(fā)程當(dāng)從表中刪除某條舊記錄時(shí)，在觸發(fā)程序中可以使用序中可以使用old關(guān)鍵字表示舊記錄，當(dāng)需要關(guān)鍵字表示舊記錄，當(dāng)需要訪問(wèn)舊記錄的某個(gè)字段值時(shí)，可以使用訪問(wèn)舊記錄的某個(gè)字段值時(shí)

24、，可以使用“old.字段名字段名”的方式訪問(wèn)。的方式訪問(wèn)。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作觸發(fā)程序中可以使用觸發(fā)程序中可以使用oldold關(guān)鍵字與關(guān)鍵字與newnew關(guān)鍵字。關(guān)鍵字。 當(dāng)修改表的某條記錄時(shí)，在觸發(fā)程序中當(dāng)修改表的某條記錄時(shí)，在觸發(fā)程序中可以使用可以使用oldold關(guān)鍵字表示修改前的舊記錄、使關(guān)鍵字表示修改前的舊記錄、使用用newnew關(guān)鍵字表示修改后的新記錄。當(dāng)需要訪關(guān)鍵字表示修改后的新記錄。當(dāng)需要訪問(wèn)舊記錄的某個(gè)字段值時(shí)，可以使用問(wèn)舊記錄的某個(gè)字段值時(shí)，可以使用“old.old.字段名字段名”的方式訪問(wèn)。當(dāng)需要訪問(wèn)修改后的的方式訪問(wèn)。當(dāng)需要訪問(wèn)修改后的新記錄的某個(gè)字段

25、值時(shí)，可以使用新記錄的某個(gè)字段值時(shí)，可以使用“new.new.字字段名段名”的方式訪問(wèn)。的方式訪問(wèn)。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作觸發(fā)程序中可以使用觸發(fā)程序中可以使用old關(guān)鍵字與關(guān)鍵字與new關(guān)鍵字。關(guān)鍵字。 old記錄是只讀的，可以引用它，但不能記錄是只讀的，可以引用它，但不能更改它。在更改它。在before觸發(fā)程序中，可使用觸發(fā)程序中，可使用“set new.col_name = value”更改更改new記錄的值。記錄的值。7.2.1 7.2.1 準(zhǔn)備工作準(zhǔn)備工作 前面曾經(jīng)提到，前面曾經(jīng)提到，MySQL可以使用復(fù)合數(shù)據(jù)可以使用復(fù)合數(shù)據(jù)類型類型set或者或者enum對(duì)字段的取值

26、范圍進(jìn)行檢對(duì)字段的取值范圍進(jìn)行檢查約束，使用復(fù)合數(shù)據(jù)類型可以實(shí)現(xiàn)離散的查約束，使用復(fù)合數(shù)據(jù)類型可以實(shí)現(xiàn)離散的字符串?dāng)?shù)據(jù)的檢查約束，對(duì)于數(shù)值型的數(shù)不字符串?dāng)?shù)據(jù)的檢查約束，對(duì)于數(shù)值型的數(shù)不建議使用建議使用set或者或者enum實(shí)現(xiàn)檢查約束，可以實(shí)現(xiàn)檢查約束，可以使用觸發(fā)器實(shí)現(xiàn)。使用觸發(fā)器實(shí)現(xiàn)。7.2.2 7.2.2 使用觸發(fā)器實(shí)現(xiàn)檢查約束使用觸發(fā)器實(shí)現(xiàn)檢查約束任務(wù)布置任務(wù)布置5 5：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述5 5：使用觸發(fā)器實(shí)現(xiàn)檢查約束使用觸發(fā)器實(shí)現(xiàn)檢查約束的任務(wù)要求。的任務(wù)要求。7.2.2 7.2.2 使用觸發(fā)器實(shí)現(xiàn)檢查約束使用觸發(fā)器實(shí)現(xiàn)檢查約束 冗余的數(shù)據(jù)需要額外

27、的維護(hù)，維護(hù)冗余數(shù)冗余的數(shù)據(jù)需要額外的維護(hù)，維護(hù)冗余數(shù)據(jù)時(shí)，為了避免數(shù)據(jù)不一致問(wèn)題的發(fā)生（例據(jù)時(shí)，為了避免數(shù)據(jù)不一致問(wèn)題的發(fā)生（例如：剩余的學(xué)生名額如：剩余的學(xué)生名額+已選學(xué)生人數(shù)已選學(xué)生人數(shù)課程的課程的人數(shù)上限），冗余的數(shù)據(jù)應(yīng)該盡量避免交由人數(shù)上限），冗余的數(shù)據(jù)應(yīng)該盡量避免交由人工維護(hù)，建議冗余的數(shù)據(jù)交由應(yīng)用系統(tǒng)人工維護(hù)，建議冗余的數(shù)據(jù)交由應(yīng)用系統(tǒng)（例如觸發(fā)器）自動(dòng)維護(hù)。（例如觸發(fā)器）自動(dòng)維護(hù)。7.2.3 7.2.3 使用觸發(fā)器維護(hù)冗余數(shù)據(jù)使用觸發(fā)器維護(hù)冗余數(shù)據(jù)任務(wù)布置任務(wù)布置6：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述6：使用觸發(fā)器自動(dòng)維護(hù)課程使用觸發(fā)器自動(dòng)維護(hù)課程avail

28、able的字段值的字段值的任務(wù)要求。的任務(wù)要求。7.2.3 7.2.3 使用觸發(fā)器維護(hù)冗余數(shù)據(jù)使用觸發(fā)器維護(hù)冗余數(shù)據(jù) 對(duì)于對(duì)于InnoDB存儲(chǔ)引擎的表而言，由于支持存儲(chǔ)引擎的表而言，由于支持外鍵約束，在定義外鍵約束時(shí)，通過(guò)設(shè)置外外鍵約束，在定義外鍵約束時(shí)，通過(guò)設(shè)置外鍵的級(jí)聯(lián)選項(xiàng)鍵的級(jí)聯(lián)選項(xiàng)cascade、set null或者或者no action（restrict），外鍵約束關(guān)系可以交由），外鍵約束關(guān)系可以交由InnoDB存儲(chǔ)引擎自動(dòng)維護(hù)。存儲(chǔ)引擎自動(dòng)維護(hù)。7.2.4 7.2.4 使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)任務(wù)布置任務(wù)布置7：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景

29、描述場(chǎng)景描述7：使用使用InnoDB存儲(chǔ)引擎維護(hù)外鍵約束關(guān)系存儲(chǔ)引擎維護(hù)外鍵約束關(guān)系的任的任務(wù)要求。務(wù)要求。任務(wù)布置任務(wù)布置8：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述8：使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)的任務(wù)要求。的任務(wù)要求。7.2.4 7.2.4 使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)可以使用下面四種方法查看觸發(fā)器的定義?？梢允褂孟旅嫠姆N方法查看觸發(fā)器的定義。1使用使用show triggers命令查看觸發(fā)器的定命令查看觸發(fā)器的定義義7.2.5 7.2.5 查看觸發(fā)器的定義查看觸發(fā)器的定義可以使用下面四種方法查看觸發(fā)器的定義。可以使用下面四種

30、方法查看觸發(fā)器的定義。2查詢查詢information_schema數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)中的triggers表，可以查看觸發(fā)器的定義表，可以查看觸發(fā)器的定義MySQL中所有觸發(fā)器的定義都存放在中所有觸發(fā)器的定義都存放在information_schema數(shù)據(jù)庫(kù)下的數(shù)據(jù)庫(kù)下的triggers表中，表中，查詢查詢triggers表，可以查看所有數(shù)據(jù)庫(kù)中所有觸表，可以查看所有數(shù)據(jù)庫(kù)中所有觸發(fā)器的詳細(xì)信息，查詢語(yǔ)句如下：發(fā)器的詳細(xì)信息，查詢語(yǔ)句如下：select * from information_schema.triggersG7.2.5 7.2.5 查看觸發(fā)器的定義查看觸發(fā)器的定義可以使用下面四種方

31、法查看觸發(fā)器的定義?？梢允褂孟旅嫠姆N方法查看觸發(fā)器的定義。3使用使用“show create trigger”命令可以查看某命令可以查看某一個(gè)觸發(fā)器的定義。一個(gè)觸發(fā)器的定義。例如使用例如使用“show create trigger organization_delete_before_triggerG”命令可命令可以查看觸發(fā)器以查看觸發(fā)器organization_delete_before_trigger的定義。的定義。7.2.5 7.2.5 查看觸發(fā)器的定義查看觸發(fā)器的定義可以使用下面四種方法查看觸發(fā)器的定義?？梢允褂孟旅嫠姆N方法查看觸發(fā)器的定義。4成功創(chuàng)建觸發(fā)器后，成功創(chuàng)建觸發(fā)器后，MyS

32、QL自動(dòng)在數(shù)據(jù)庫(kù)目自動(dòng)在數(shù)據(jù)庫(kù)目錄下創(chuàng)建錄下創(chuàng)建TRN以及以及TRG觸發(fā)器文件，以記事本方觸發(fā)器文件，以記事本方式打開(kāi)這些文件，可以查看觸發(fā)器的定義。式打開(kāi)這些文件，可以查看觸發(fā)器的定義。7.2.5 7.2.5 查看觸發(fā)器的定義查看觸發(fā)器的定義可以使用可以使用drop trigger語(yǔ)句將該觸發(fā)器刪除，語(yǔ)語(yǔ)句將該觸發(fā)器刪除，語(yǔ)法格式如下。法格式如下。drop trigger 觸發(fā)器名觸發(fā)器名7.2.6 7.2.6 刪除觸發(fā)器刪除觸發(fā)器1觸發(fā)程序中如果包含觸發(fā)程序中如果包含select語(yǔ)句，該語(yǔ)句，該select語(yǔ)語(yǔ)句不能返回結(jié)果集。句不能返回結(jié)果集。2同一個(gè)表不能創(chuàng)建兩個(gè)相同觸發(fā)時(shí)間、觸發(fā)同一

33、個(gè)表不能創(chuàng)建兩個(gè)相同觸發(fā)時(shí)間、觸發(fā)事件的觸發(fā)程序。事件的觸發(fā)程序。3觸發(fā)程序中不能使用以顯式或隱式方式打開(kāi)、觸發(fā)程序中不能使用以顯式或隱式方式打開(kāi)、開(kāi)始或結(jié)束事務(wù)的語(yǔ)句，如開(kāi)始或結(jié)束事務(wù)的語(yǔ)句，如start transaction、commit、rollback或者或者set autocommit=0等語(yǔ)等語(yǔ)句。句。7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注意事項(xiàng)條注意事項(xiàng)4MySQL觸發(fā)器針對(duì)記錄進(jìn)行操作，當(dāng)批量更觸發(fā)器針對(duì)記錄進(jìn)行操作，當(dāng)批量更新數(shù)據(jù)時(shí)，引入觸發(fā)器會(huì)導(dǎo)致更新操作性能降低。新數(shù)據(jù)時(shí)，引入觸發(fā)器會(huì)導(dǎo)致更新操作性能降低。5在在MyISAM存儲(chǔ)引擎中，觸發(fā)器不能保

34、證原子存儲(chǔ)引擎中，觸發(fā)器不能保證原子性。性。InnoDB存儲(chǔ)引擎支持事務(wù)，使用觸發(fā)器可存儲(chǔ)引擎支持事務(wù)，使用觸發(fā)器可以保證更新操作與觸發(fā)程序的原子性，此時(shí)觸發(fā)以保證更新操作與觸發(fā)程序的原子性，此時(shí)觸發(fā)程序和更新操作是在同一個(gè)事務(wù)中完成。程序和更新操作是在同一個(gè)事務(wù)中完成。7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注意事項(xiàng)條注意事項(xiàng)6InnoDB存儲(chǔ)引擎實(shí)現(xiàn)外鍵約束關(guān)系時(shí)，建議存儲(chǔ)引擎實(shí)現(xiàn)外鍵約束關(guān)系時(shí)，建議使用級(jí)聯(lián)選項(xiàng)維護(hù)外鍵數(shù)據(jù)；使用級(jí)聯(lián)選項(xiàng)維護(hù)外鍵數(shù)據(jù)；MyISAM存儲(chǔ)引擎存儲(chǔ)引擎雖然不支持外鍵約束關(guān)系時(shí)，但可以使用觸發(fā)器雖然不支持外鍵約束關(guān)系時(shí)，但可以使用觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)修

35、改和級(jí)聯(lián)刪除，進(jìn)而維護(hù)實(shí)現(xiàn)級(jí)聯(lián)修改和級(jí)聯(lián)刪除，進(jìn)而維護(hù)“外鍵外鍵”數(shù)數(shù)據(jù)，模擬實(shí)現(xiàn)外鍵約束關(guān)系。據(jù)，模擬實(shí)現(xiàn)外鍵約束關(guān)系。7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注意事項(xiàng)條注意事項(xiàng)7使用觸發(fā)器維護(hù)使用觸發(fā)器維護(hù)InnoDB外鍵約束的級(jí)聯(lián)選項(xiàng)外鍵約束的級(jí)聯(lián)選項(xiàng)時(shí)，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員究竟應(yīng)該選擇時(shí)，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員究竟應(yīng)該選擇after觸發(fā)器還觸發(fā)器還是是before觸發(fā)器？答案是：應(yīng)該首先維護(hù)子表的觸發(fā)器？答案是：應(yīng)該首先維護(hù)子表的數(shù)據(jù)，然后再維護(hù)父表的數(shù)據(jù)，否則可能出現(xiàn)錯(cuò)數(shù)據(jù)，然后再維護(hù)父表的數(shù)據(jù)，否則可能出現(xiàn)錯(cuò)誤。誤。 7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注

36、意事項(xiàng)條注意事項(xiàng)8MySQL的觸發(fā)程序不能對(duì)本表進(jìn)行更新語(yǔ)句的觸發(fā)程序不能對(duì)本表進(jìn)行更新語(yǔ)句（例如（例如update語(yǔ)句）。觸發(fā)程序中的更新操作可語(yǔ)句）。觸發(fā)程序中的更新操作可以直接使用以直接使用set命令替代，否則可能出現(xiàn)錯(cuò)誤信息，命令替代，否則可能出現(xiàn)錯(cuò)誤信息，甚至陷入死循環(huán)。甚至陷入死循環(huán)。9在在before觸發(fā)程序中，觸發(fā)程序中，auto_increment字段字段的的new值為值為0，不是實(shí)際插入新記錄時(shí)自動(dòng)生成的，不是實(shí)際插入新記錄時(shí)自動(dòng)生成的自增型字段值。自增型字段值。7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注意事項(xiàng)條注意事項(xiàng)10添加觸發(fā)器后，建議對(duì)其進(jìn)行詳細(xì)的

37、測(cè)試，添加觸發(fā)器后，建議對(duì)其進(jìn)行詳細(xì)的測(cè)試，測(cè)試通過(guò)后再?zèng)Q定是否使用觸發(fā)器。測(cè)試通過(guò)后再?zèng)Q定是否使用觸發(fā)器。7.2.7 7.2.7 使用觸發(fā)器的使用觸發(fā)器的1010條注意事項(xiàng)條注意事項(xiàng)1 12 23 3按照按照MySQL臨時(shí)表的存儲(chǔ)臨時(shí)表的存儲(chǔ)位置可以將其分為內(nèi)存臨位置可以將其分為內(nèi)存臨時(shí)表（時(shí)表（in-memory）以及）以及外存臨時(shí)表（外存臨時(shí)表（on-disk）。）。 按照按照MySQL臨時(shí)表的創(chuàng)建臨時(shí)表的創(chuàng)建時(shí)機(jī)可以將其分為自動(dòng)創(chuàng)時(shí)機(jī)可以將其分為自動(dòng)創(chuàng)建的臨時(shí)表以及手動(dòng)創(chuàng)建建的臨時(shí)表以及手動(dòng)創(chuàng)建的臨時(shí)表。的臨時(shí)表。 “選課系統(tǒng)選課系統(tǒng)”中臨時(shí)表的使用中臨時(shí)表的使用臨時(shí)表的創(chuàng)建、查看與刪

38、除臨時(shí)表的創(chuàng)建、查看與刪除使用臨時(shí)表的注意事項(xiàng)使用臨時(shí)表的注意事項(xiàng)7.3 7.3 臨時(shí)表臨時(shí)表1手動(dòng)創(chuàng)建臨時(shí)表手動(dòng)創(chuàng)建臨時(shí)表手動(dòng)創(chuàng)建臨時(shí)表很容易，給正常的手動(dòng)創(chuàng)建臨時(shí)表很容易，給正常的create table語(yǔ)句加上語(yǔ)句加上temporary關(guān)鍵字即可。關(guān)鍵字即可。 2查看臨時(shí)表的定義可以使用查看臨時(shí)表的定義可以使用MySQL語(yǔ)句語(yǔ)句“show create table 臨時(shí)表名臨時(shí)表名;”。 7.3.1 7.3.1 臨時(shí)表的創(chuàng)建、查看與刪除臨時(shí)表的創(chuàng)建、查看與刪除3斷開(kāi)斷開(kāi)MySQL服務(wù)器的連接，臨時(shí)表服務(wù)器的連接，臨時(shí)表frm表結(jié)表結(jié)構(gòu)定義文件以及表記錄將被清除。使用構(gòu)定義文件以及表記錄將

39、被清除。使用drop命令命令也可以刪除臨時(shí)表，語(yǔ)法格式如下。也可以刪除臨時(shí)表，語(yǔ)法格式如下。drop temporary table 臨時(shí)表表名臨時(shí)表表名7.3.1 7.3.1 臨時(shí)表的創(chuàng)建、查看與刪除臨時(shí)表的創(chuàng)建、查看與刪除任務(wù)布置任務(wù)布置9：上機(jī)操作，完成本書(shū)：上機(jī)操作，完成本書(shū)場(chǎng)景描述場(chǎng)景描述9的任的任務(wù)要求。務(wù)要求。7.3.2 “7.3.2 “選課系統(tǒng)選課系統(tǒng)”中臨時(shí)表的使用中臨時(shí)表的使用 使用存儲(chǔ)程序可以實(shí)現(xiàn)表數(shù)據(jù)的復(fù)雜加工處使用存儲(chǔ)程序可以實(shí)現(xiàn)表數(shù)據(jù)的復(fù)雜加工處理，有時(shí)需要將理，有時(shí)需要將select語(yǔ)句的查詢結(jié)果集臨時(shí)地語(yǔ)句的查詢結(jié)果集臨時(shí)地保存到存儲(chǔ)程序（例如函數(shù)、存儲(chǔ)過(guò)程）的

40、變量保存到存儲(chǔ)程序（例如函數(shù)、存儲(chǔ)過(guò)程）的變量中，不過(guò)目前中，不過(guò)目前MySQL并不支持表類型變量。臨時(shí)并不支持表類型變量。臨時(shí)表可以模擬實(shí)現(xiàn)表類型變量的功能。表可以模擬實(shí)現(xiàn)表類型變量的功能。7.3.2 7.3.2 使用臨時(shí)表的注意事項(xiàng)使用臨時(shí)表的注意事項(xiàng) 臨時(shí)表如果與基表重名，那么基表將被隱藏，臨時(shí)表如果與基表重名，那么基表將被隱藏，除非刪除臨時(shí)表，基表才能被訪問(wèn)。除非刪除臨時(shí)表，基表才能被訪問(wèn)。 Memory、MyISAM、Merge或者或者InnoDB存存儲(chǔ)引擎的表都支持臨時(shí)表。儲(chǔ)引擎的表都支持臨時(shí)表。 臨時(shí)表不支持聚簇索引、觸發(fā)器。臨時(shí)表不支持聚簇索引、觸發(fā)器。7.3.2 7.3.2

41、使用臨時(shí)表的注意事項(xiàng)使用臨時(shí)表的注意事項(xiàng) show tables 命令不會(huì)顯示臨時(shí)表的信息。命令不會(huì)顯示臨時(shí)表的信息。 不能用不能用rename來(lái)重命名一個(gè)臨時(shí)表。但可以來(lái)重命名一個(gè)臨時(shí)表。但可以使用使用alter table重命名臨時(shí)表。重命名臨時(shí)表。 在同一條在同一條select語(yǔ)句中，臨時(shí)表只能引用一語(yǔ)句中，臨時(shí)表只能引用一次。例如下面的次。例如下面的select語(yǔ)句將拋出語(yǔ)句將拋出“ERROR 1137 (HY000): Cant reopen table: t1”錯(cuò)誤信錯(cuò)誤信息。息。 select * from temp as t1, temp as t2;7.3.2 7.3.2 使

42、用臨時(shí)表的注意事項(xiàng)使用臨時(shí)表的注意事項(xiàng) 派生表與視圖一樣，一般在派生表與視圖一樣，一般在from子句中使用，子句中使用，其語(yǔ)法格式如下（粗體字代碼為派生表代碼）。其語(yǔ)法格式如下（粗體字代碼為派生表代碼）。.from (select子句子句) 派生表名派生表名.派生表必須是一個(gè)有效的表，因此它必須遵守以派生表必須是一個(gè)有效的表，因此它必須遵守以下規(guī)則：下規(guī)則：每個(gè)派生表必須有自己的別名。每個(gè)派生表必須有自己的別名。派生表中的所有字段必須要有名稱，字段名必須派生表中的所有字段必須要有名稱，字段名必須唯一。唯一。7.4 7.4 派生表（派生表（Derived TableDerived Table）任務(wù)布置任務(wù)布置10：完成本書(shū)：完成本書(shū)場(chǎng)景描述場(chǎng)景