MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程課件

1、,孔祥盛,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程 之 視圖觸發(fā)器,1,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,1,2,3,本章首先講解了視圖以及觸發(fā)器的管理及使用，然后結(jié)合“選課系統(tǒng)”分別介紹視圖以及觸發(fā)器在該系統(tǒng)中的應(yīng)用。,4,觸發(fā)器,視圖,臨時(shí)表,派生表（Derived Table）,5,視圖、子查詢、臨時(shí)表、派生表,內(nèi)容一覽,2,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,視圖與表有很多相似的地方，視圖也是由若干個(gè)字段以及若干條記錄構(gòu)成，視圖也可以作為select語句的數(shù)據(jù)源。甚至在某些特定條件下，可以通過視圖對(duì)表進(jìn)行更新操作。,7.1 視圖,3,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,1,2,3,視圖中保存的僅僅是一條s

2、elect語句，視圖中的源數(shù)據(jù)都來自于數(shù)據(jù)庫表，數(shù)據(jù)庫表稱為基本表或者基表，視圖稱為虛表。,查看視圖的定義,創(chuàng)建視圖,視圖在“選課系統(tǒng)”中的應(yīng)用,4,5,視圖的作用,刪除視圖,7.1 視圖,4,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,6,7,視圖中保存的僅僅是一條select語句，視圖中的源數(shù)據(jù)都來自于數(shù)據(jù)庫表，數(shù)據(jù)庫表稱為基本表或者基表，視圖稱為虛表。,檢查視圖,local與cascade檢查視圖,7.1 視圖,5,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,創(chuàng)建視圖的語法格式如下。 create view 視圖名 (視圖字段列表) as select語句,7.1.1 創(chuàng)建視圖,任務(wù)布置1：上機(jī)操作，完成本書場

3、景描述1的任務(wù)要求，理解“對(duì)于經(jīng)常使用的、結(jié)構(gòu)復(fù)雜的select語句，建議將其封裝為視圖 ” 。,7.1.1 創(chuàng)建視圖,可以使用下面四種方法查看視圖的定義。 1在choose數(shù)據(jù)庫中成功地創(chuàng)建了視圖available_course_view后，該視圖的定義默認(rèn)保存在數(shù)據(jù)庫目錄（例如choose目錄）下，文件名為available_course_view.frm。使用記事本打開該文件，即可查看該視圖的定義。,7.1.2 查看視圖的定義,可以使用下面四種方法查看視圖的定義。 2視圖是一個(gè)虛表，也可以使用查看表結(jié)構(gòu)的方式查看視圖的定義。 3MySQL命令“show tables;”命令不僅顯示當(dāng)前數(shù)

4、據(jù)庫中所有的基表，也會(huì)將所有的視圖羅列出來。,7.1.2 查看視圖的定義,可以使用下面四種方法查看視圖的定義。 4MySQL系統(tǒng)數(shù)據(jù)庫information_schema的views表存儲(chǔ)了所有視圖的定義，使用下面的select語句查詢?cè)摫淼乃杏涗?，也可以查看所有視圖的詳細(xì)信息。 select * from information_schema.viewsG,7.1.2 查看視圖的定義,從現(xiàn)在開始，本書將選擇“方案一”實(shí)現(xiàn)學(xué)生選課功能。方案一與方案二的唯一區(qū)別在于：方案一中的課程course表比方案二中的課程course表多了一個(gè)“剩余的學(xué)生名額”available字段。 任務(wù)布置2：上機(jī)操

5、作，完成本書場景描述2的任務(wù)要求。,7.1.3 視圖在“選課系統(tǒng)”中的應(yīng)用,1使操作變得簡單 2避免數(shù)據(jù)冗余 3增強(qiáng)數(shù)據(jù)安全性 4提高數(shù)據(jù)的邏輯獨(dú)立性,7.1.4 視圖的作用,如果某個(gè)視圖不再使用，可以使用drop view語句將該視圖刪除，語法格式如下。 drop view 視圖名,7.1.5 刪除視圖,視圖分為普通視圖與檢查視圖。 通過檢查視圖更新基表數(shù)據(jù)時(shí)，只有滿足檢查條件的更新語句才能成功執(zhí)行。創(chuàng)建檢查視圖的語法格式如下。 create view 視圖名 (視圖字段列表) as select語句 with local | cascaded check option,7.1.6 檢查視圖

6、,任務(wù)布置3：上機(jī)操作，完成本書場景描述3：普通視圖與更新操作的任務(wù)要求。 任務(wù)布置4：上機(jī)操作，完成本書場景描述4：檢查視圖與更新操作的任務(wù)要求。 然后分析普通視圖與檢查視圖之間的區(qū)別。,7.1.6 檢查視圖,檢查視圖分為local檢查視圖與cascade檢查視圖。 with_check_option的值為1時(shí)表示local（local視圖）， 值為2時(shí)表示cascade（級(jí)聯(lián)視圖，在視圖的基礎(chǔ)上再次創(chuàng)建另一個(gè)視圖）。,7.1.6 local與cascade檢查視圖,local 檢查視圖與 cascade檢查視圖,7.1.6 local與cascade檢查視圖,1,2,3,觸發(fā)器主要用于監(jiān)視

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

8、 觸發(fā)器,20,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,使用create trigger語句可以創(chuàng)建一個(gè)觸發(fā)器，語法格式如下。 create trigger 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件 on 表名 for each row begin 觸發(fā)程序 end,7.2.1 準(zhǔn)備工作,MySQL的觸發(fā)事件有三種： insert：將新記錄插入表時(shí)激活觸發(fā)程序，例如通過insert、load data和replace語句，可以激活觸發(fā)程序運(yùn)行。,7.2.1 準(zhǔn)備工作,MySQL的觸發(fā)事件有三種： update：更改某一行記錄時(shí)激活觸發(fā)程序，例如通過update語句，可以激活觸發(fā)程序運(yùn)行。 delete：從表中刪除

9、某一行記錄時(shí)激活觸發(fā)程序，例如通過delete和replace語句，可以激活觸發(fā)程序運(yùn)行。,7.2.1 準(zhǔn)備工作,觸發(fā)器的觸發(fā)時(shí)間有兩種：before與after。 before表示在觸發(fā)事件發(fā)生之前執(zhí)行觸發(fā)程序。 after表示在觸發(fā)事件發(fā)生之后執(zhí)行觸發(fā)器。因此嚴(yán)格意義上講一個(gè)數(shù)據(jù)庫表最多可以設(shè)置六種類型的觸發(fā)器。,7.2.1 準(zhǔn)備工作,for each row表示行級(jí)觸發(fā)器。 目前MySQL僅支持行級(jí)觸發(fā)器，不支持語句級(jí)別的觸發(fā)器（例如create table等語句）。for each row表示更新（insert、update或者delete）操作影響的每一條記錄都會(huì)執(zhí)行一次觸發(fā)程序。,

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

11、需要訪問舊記錄的某個(gè)字段值時(shí)，可以使用“old.字段名”的方式訪問。當(dāng)需要訪問修改后的新記錄的某個(gè)字段值時(shí)，可以使用“new.字段名”的方式訪問。,7.2.1 準(zhǔn)備工作,觸發(fā)程序中可以使用old關(guān)鍵字與new關(guān)鍵字。 old記錄是只讀的，可以引用它，但不能更改它。在before觸發(fā)程序中，可使用“set new.col_name = value”更改new記錄的值。,7.2.1 準(zhǔn)備工作,前面曾經(jīng)提到，MySQL可以使用復(fù)合數(shù)據(jù)類型set或者enum對(duì)字段的取值范圍進(jìn)行檢查約束，使用復(fù)合數(shù)據(jù)類型可以實(shí)現(xiàn)離散的字符串?dāng)?shù)據(jù)的檢查約束，對(duì)于數(shù)值型的數(shù)不建議使用set或者enum實(shí)現(xiàn)檢查約束，可以使用

12、觸發(fā)器實(shí)現(xiàn)。,7.2.2 使用觸發(fā)器實(shí)現(xiàn)檢查約束,任務(wù)布置5：上機(jī)操作，完成本書場景描述5：使用觸發(fā)器實(shí)現(xiàn)檢查約束的任務(wù)要求。,7.2.2 使用觸發(fā)器實(shí)現(xiàn)檢查約束,冗余的數(shù)據(jù)需要額外的維護(hù)，維護(hù)冗余數(shù)據(jù)時(shí)，為了避免數(shù)據(jù)不一致問題的發(fā)生（例如：剩余的學(xué)生名額+已選學(xué)生人數(shù)課程的人數(shù)上限），冗余的數(shù)據(jù)應(yīng)該盡量避免交由人工維護(hù)，建議冗余的數(shù)據(jù)交由應(yīng)用系統(tǒng)（例如觸發(fā)器）自動(dòng)維護(hù)。,7.2.3 使用觸發(fā)器維護(hù)冗余數(shù)據(jù),任務(wù)布置6：上機(jī)操作，完成本書場景描述6：使用觸發(fā)器自動(dòng)維護(hù)課程available的字段值的任務(wù)要求。,7.2.3 使用觸發(fā)器維護(hù)冗余數(shù)據(jù),對(duì)于InnoDB存儲(chǔ)引擎的表而言，由于支持外鍵約

13、束，在定義外鍵約束時(shí)，通過設(shè)置外鍵的級(jí)聯(lián)選項(xiàng)cascade、set null或者no action（restrict），外鍵約束關(guān)系可以交由InnoDB存儲(chǔ)引擎自動(dòng)維護(hù)。,7.2.4 使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng),任務(wù)布置7：上機(jī)操作，完成本書場景描述7：使用InnoDB存儲(chǔ)引擎維護(hù)外鍵約束關(guān)系的任務(wù)要求。 任務(wù)布置8：上機(jī)操作，完成本書場景描述8：使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)的任務(wù)要求。,7.2.4 使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng),可以使用下面四種方法查看觸發(fā)器的定義。 1使用show triggers命令查看觸發(fā)器的定義,7.2.5 查看觸發(fā)器的定義,可以使用下面四種方法查看觸發(fā)器的定義。 2查詢

14、information_schema數(shù)據(jù)庫中的triggers表，可以查看觸發(fā)器的定義 MySQL中所有觸發(fā)器的定義都存放在information_schema數(shù)據(jù)庫下的triggers表中，查詢triggers表，可以查看所有數(shù)據(jù)庫中所有觸發(fā)器的詳細(xì)信息，查詢語句如下： select * from information_schema.triggersG,7.2.5 查看觸發(fā)器的定義,可以使用下面四種方法查看觸發(fā)器的定義。 3使用“show create trigger”命令可以查看某一個(gè)觸發(fā)器的定義。 例如使用“show create trigger organization_delete

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

16、發(fā)事件的觸發(fā)程序。 3觸發(fā)程序中不能使用以顯式或隱式方式打開、開始或結(jié)束事務(wù)的語句，如start transaction、commit、rollback或者set autocommit=0等語句。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),4MySQL觸發(fā)器針對(duì)記錄進(jìn)行操作，當(dāng)批量更新數(shù)據(jù)時(shí)，引入觸發(fā)器會(huì)導(dǎo)致更新操作性能降低。 5在MyISAM存儲(chǔ)引擎中，觸發(fā)器不能保證原子性。InnoDB存儲(chǔ)引擎支持事務(wù)，使用觸發(fā)器可以保證更新操作與觸發(fā)程序的原子性，此時(shí)觸發(fā)程序和更新操作是在同一個(gè)事務(wù)中完成。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),6InnoDB存儲(chǔ)引擎實(shí)現(xiàn)外鍵約束關(guān)系時(shí)，建議使用級(jí)聯(lián)選項(xiàng)維

17、護(hù)外鍵數(shù)據(jù)；MyISAM存儲(chǔ)引擎雖然不支持外鍵約束關(guān)系時(shí)，但可以使用觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)修改和級(jí)聯(lián)刪除，進(jìn)而維護(hù)“外鍵”數(shù)據(jù)，模擬實(shí)現(xiàn)外鍵約束關(guān)系。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),7使用觸發(fā)器維護(hù)InnoDB外鍵約束的級(jí)聯(lián)選項(xiàng)時(shí)，數(shù)據(jù)庫開發(fā)人員究竟應(yīng)該選擇after觸發(fā)器還是before觸發(fā)器？答案是：應(yīng)該首先維護(hù)子表的數(shù)據(jù)，然后再維護(hù)父表的數(shù)據(jù)，否則可能出現(xiàn)錯(cuò)誤。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),8MySQL的觸發(fā)程序不能對(duì)本表進(jìn)行更新語句（例如update語句）。觸發(fā)程序中的更新操作可以直接使用set命令替代，否則可能出現(xiàn)錯(cuò)誤信息，甚至陷入死循環(huán)。 9在before觸發(fā)程序中，

18、auto_increment字段的new值為0，不是實(shí)際插入新記錄時(shí)自動(dòng)生成的自增型字段值。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),10添加觸發(fā)器后，建議對(duì)其進(jìn)行詳細(xì)的測試，測試通過后再?zèng)Q定是否使用觸發(fā)器。,7.2.7 使用觸發(fā)器的10條注意事項(xiàng),1,2,3,按照MySQL臨時(shí)表的存儲(chǔ)位置可以將其分為內(nèi)存臨時(shí)表（in-memory）以及外存臨時(shí)表（on-disk）。 按照MySQL臨時(shí)表的創(chuàng)建時(shí)機(jī)可以將其分為自動(dòng)創(chuàng)建的臨時(shí)表以及手動(dòng)創(chuàng)建的臨時(shí)表。,“選課系統(tǒng)”中臨時(shí)表的使用,臨時(shí)表的創(chuàng)建、查看與刪除,使用臨時(shí)表的注意事項(xiàng),7.3 臨時(shí)表,47,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,1手動(dòng)創(chuàng)建臨時(shí)表

19、 手動(dòng)創(chuàng)建臨時(shí)表很容易，給正常的create table語句加上temporary關(guān)鍵字即可。 2查看臨時(shí)表的定義可以使用MySQL語句“show create table 臨時(shí)表名;”。,7.3.1 臨時(shí)表的創(chuàng)建、查看與刪除,3斷開MySQL服務(wù)器的連接，臨時(shí)表frm表結(jié)構(gòu)定義文件以及表記錄將被清除。使用drop命令也可以刪除臨時(shí)表，語法格式如下。 drop temporary table 臨時(shí)表表名,7.3.1 臨時(shí)表的創(chuàng)建、查看與刪除,任務(wù)布置9：上機(jī)操作，完成本書場景描述9的任務(wù)要求。,7.3.2 “選課系統(tǒng)”中臨時(shí)表的使用,使用存儲(chǔ)程序可以實(shí)現(xiàn)表數(shù)據(jù)的復(fù)雜加工處理，有時(shí)需要將sele

20、ct語句的查詢結(jié)果集臨時(shí)地保存到存儲(chǔ)程序（例如函數(shù)、存儲(chǔ)過程）的變量中，不過目前MySQL并不支持表類型變量。臨時(shí)表可以模擬實(shí)現(xiàn)表類型變量的功能。,7.3.2 使用臨時(shí)表的注意事項(xiàng),臨時(shí)表如果與基表重名，那么基表將被隱藏，除非刪除臨時(shí)表，基表才能被訪問。 Memory、MyISAM、Merge或者InnoDB存儲(chǔ)引擎的表都支持臨時(shí)表。 臨時(shí)表不支持聚簇索引、觸發(fā)器。,7.3.2 使用臨時(shí)表的注意事項(xiàng),show tables 命令不會(huì)顯示臨時(shí)表的信息。 不能用rename來重命名一個(gè)臨時(shí)表。但可以使用alter table重命名臨時(shí)表。 在同一條select語句中，臨時(shí)表只能引用一次。例如下面的

21、select語句將拋出“ERROR 1137 (HY000): Cant reopen table: t1”錯(cuò)誤信息。 select * from temp as t1, temp as t2;,7.3.2 使用臨時(shí)表的注意事項(xiàng),派生表與視圖一樣，一般在from子句中使用，其語法格式如下（粗體字代碼為派生表代碼）。 .from (select子句) 派生表名. 派生表必須是一個(gè)有效的表，因此它必須遵守以下規(guī)則： 每個(gè)派生表必須有自己的別名。 派生表中的所有字段必須要有名稱，字段名必須唯一。,7.4 派生表（Derived Table）,54,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,任務(wù)布置10：完成本書場景描述10的任務(wù)要求。,7.4 派生表（Derived Table）,55,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,子查詢一般在主查詢語句中的where子句或者h(yuǎn)aving子句中使用。 視圖通常在主查詢語句中的from子句中使用。,7.5 子查詢、視圖、臨時(shí)表、派生表,56,MySQL數(shù)據(jù)庫基礎(chǔ)與實(shí)例教程,視圖本質(zhì)是一條select語句，執(zhí)行的是某一個(gè)數(shù)據(jù)源某個(gè)字段的查詢操作，如果視圖的“主查詢”語句是update語句、delete語句或者