第七章視圖和觸發(fā)器課件

第七章視圖和觸發(fā)器學(xué)習(xí)目標(biāo)掌握索引的創(chuàng)建；視圖的創(chuàng)建；管理視圖；通過(guò)視圖修改數(shù)據(jù)；創(chuàng)建、刪除觸發(fā)器13了解查看索引信息的方式；觸發(fā)器的概念；觸發(fā)器的常見(jiàn)應(yīng)用2理解索引的概念和分類(lèi)掌握了解熟悉視圖是一種數(shù)據(jù)庫(kù)對(duì)象，是從一個(gè)或多個(gè)基表（或視圖）導(dǎo)出的虛表。視圖的結(jié)構(gòu)和數(shù)據(jù)是對(duì)數(shù)據(jù)表進(jìn)行查詢(xún)的結(jié)果。通過(guò)定義SELECT語(yǔ)句檢索將在視圖中顯示的數(shù)據(jù)來(lái)創(chuàng)建視圖。SELECT語(yǔ)句引用的數(shù)據(jù)表稱(chēng)為視圖的基表。視圖可以被看成是虛擬表或存儲(chǔ)查詢(xún)。視圖被定義后便存儲(chǔ)在數(shù)據(jù)庫(kù)中，通過(guò)視圖看到的數(shù)據(jù)只是存放在基表中的數(shù)據(jù)。當(dāng)對(duì)通過(guò)視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基表的數(shù)據(jù)也會(huì)發(fā)生變化，同時(shí)，若基表的數(shù)據(jù)發(fā)生變化，這種變化也會(huì)自動(dòng)地反映到視圖中。7.3視圖7.3.1視圖的基本概念視圖可以是一個(gè)數(shù)據(jù)表的一部分，也可以是多個(gè)基表的聯(lián)合；視圖也可以由一個(gè)或多個(gè)其他視圖產(chǎn)生。視圖通常用來(lái)進(jìn)行以下三種操作：（1）篩選表中的行。（2）防止未經(jīng)許可的用戶(hù)訪(fǎng)問(wèn)敏感數(shù)據(jù)。（3）將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表。視圖上的操作和基表類(lèi)似，但是DBMS對(duì)視圖的更新操作（INSERT、DELETE、UPDATE）往往存在一定的限制。視圖可以提高數(shù)據(jù)的邏輯獨(dú)立性，也可以增加一定的安全性。7.3視圖7.3.1視圖的基本概念（1）視圖能夠簡(jiǎn)化用戶(hù)的操作。（2）視圖使用戶(hù)能從多種角度看待同一數(shù)據(jù)。（3）視圖對(duì)重構(gòu)數(shù)據(jù)庫(kù)提供一定程序的邏輯獨(dú)立性。（4）視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)。7.3視圖7.3.2視圖的優(yōu)點(diǎn)在MySQL中，使用CREATEVIEW語(yǔ)句創(chuàng)建視圖。語(yǔ)法格式如下：CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(字段名列表)]ASselect語(yǔ)句

[WITH[CASCADED|LOCAL]CHECKOPTION]

7.3視圖7.3.3定義視圖

語(yǔ)法格式中相關(guān)子句和參數(shù)說(shuō)明：（1）ORREPLACE：表示當(dāng)已具有同名的視圖時(shí)，將覆蓋原視圖。（2）ALGORITHM子句：可選項(xiàng)，表示視圖選擇的算法。ALGORITHM可取三個(gè)值：MERGE、TEMPTABLE或UNDEFINED。如果沒(méi)有ALGORITHM子句，默認(rèn)算法是UNDEFINED（未定義的）。算法會(huì)影響MySQL處理視圖的方式。7.3視圖7.3.3定義視圖MERGE：會(huì)將引用視圖的語(yǔ)句的文本與視圖定義合并起來(lái)，使得視圖定義的某一部分取代語(yǔ)句的對(duì)應(yīng)部分。TEMPTABLE：視圖的結(jié)果將被置于臨時(shí)表中，然后使用它執(zhí)行語(yǔ)句。UNDEFINED：由MySQL選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因?yàn)镸ERGE通常更有效，而且如果使用了臨時(shí)表，視圖是不可更新的。（3）字段名列表：指定視圖中查詢(xún)結(jié)果的字段名，如果沒(méi)有此可選項(xiàng)，視圖中查詢(xún)結(jié)果的字段名和尚SELECT子句中的字段名一致。（4）WITHCHECKOPTION：對(duì)于可更新視圖，給定WITHCHECKOPTION子句用來(lái)防止插入或更新行，除非作用在行上的select_statement中的WHERE子句為“真”。

在可更新視圖中加入WITHCHECKOPTION子句，當(dāng)視圖是根據(jù)另一個(gè)視圖定義時(shí)，LOCAL和CASCADED關(guān)鍵字決定了檢查測(cè)試的范圍。LOCAL關(guān)鍵字對(duì)CHECKOPTION進(jìn)行了限制，使其僅作用在定義的視圖上，CASCADED會(huì)對(duì)該視圖相關(guān)的所有視圖和基表進(jìn)行檢查。如果未給定任一關(guān)鍵字，默認(rèn)值為CASCADED。7.3視圖7.3.3定義視圖創(chuàng)建視圖時(shí)要求創(chuàng)建者具有針對(duì)視圖的CREATEVIEW權(quán)限，以及針對(duì)由SELECT語(yǔ)句選擇的每一列上的某些權(quán)限。對(duì)于在SELECT語(yǔ)句中其他地方使用的列，必須具有SELECT權(quán)限。如果還有ORREPLACE子句，必須在視圖上具有DROP權(quán)限。

視圖屬于數(shù)據(jù)庫(kù)。在默認(rèn)情況下，將在當(dāng)前數(shù)據(jù)庫(kù)創(chuàng)建新視圖。要想在給定數(shù)據(jù)庫(kù)中創(chuàng)建視圖，創(chuàng)建時(shí)，應(yīng)將名稱(chēng)指定為數(shù)據(jù)庫(kù)名.視圖名。7.3視圖7.3.3定義視圖在“學(xué)生選課”數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)基于teacher表的視圖teacher_view，該視圖要求查詢(xún)輸出所有教師的姓名tname、職稱(chēng)tpro。執(zhí)行以下SQL語(yǔ)句：CREATEVIEWteacher_viewASSELECTtname,tgender,tproFROMteacher;7.3視圖實(shí)例7-19執(zhí)行上述語(yǔ)句，就在“學(xué)生選課”數(shù)據(jù)庫(kù)中創(chuàng)建了視圖teacher_view。使用SELECT語(yǔ)句查詢(xún)teacher_view視圖，可以看到結(jié)果如圖所示。7.3視圖在“學(xué)生選課”數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)基于teacher表的視圖teacher1_view，該視圖要求查詢(xún)輸出所有教師的tname、tgender、tpro，并將視圖中的字段名設(shè)為教師姓名、教師性別和教師職稱(chēng)。SQL語(yǔ)句如下：CREATEVIEWteacher1_view(教師姓名,教師性別,教師職稱(chēng))ASSELECTtname,tgender,tproFROMteacher;7.3視圖實(shí)例7-20在“學(xué)生選課”數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)基于學(xué)生表studentInfo、課程表course和選課表eletive的視圖nopass_view，該視圖要求查詢(xún)輸出所有不及格學(xué)生的學(xué)號(hào)sno，姓名sname，課程名cname，成績(jī)score。執(zhí)行以下SQL語(yǔ)句：CREATEVIEWnopass_viewASSELECTstudentInfo.snoAS學(xué)號(hào),snameAS姓名,cnameAS課程名,scoreAS成績(jī)FROMstudentInfoaINNERJOINelectivebONa.sno=b.snoINNERJOINcoursecONo=oWHEREscore<60;7.3視圖實(shí)例7-211、使用DESCRIBE查看視圖在MySQL中，使用權(quán)DESCRIBE語(yǔ)句可以查看視圖的字段信息，包括字段名、字段類(lèi)型等信息。DESCRIBE語(yǔ)句的語(yǔ)法格式如下所示：DESCRIBE視圖名;或簡(jiǎn)寫(xiě)為：DESC視圖名;7.3視圖7.3.4查看視圖SQL語(yǔ)句為：DESCRIBEteacher_view;查看視圖teacher_view基本信息。7.3視圖實(shí)例7-22左面顯示結(jié)果中各列含義如下：（1）Field：視圖中的字段名。（2）Type:字段的數(shù)據(jù)類(lèi)型。（3）Null:表示該字段是否允許存放空值。（4）Key：表示該字段是否已經(jīng)建有索引。（5）Default:表示該列是否有默認(rèn)值。（6）Extra：表示該列的附加信息。語(yǔ)法格式如下：SHOWTABLESTATUSLIKE'視圖名';LIKE表示后面是匹配字符串，“視圖名”是要查看的視圖名稱(chēng)，可以是一個(gè)具體的視圖名，也可以包含通配符，視圖名稱(chēng)要用單引號(hào)括起來(lái)。7.3視圖2.使用SHOWTABLE

STATUS語(yǔ)句查看視圖SQL語(yǔ)句如下：SHOWTABLESTATUSLIKE'teacher_view'\G使用SHOWTABLESTATUS查看視圖teacher_view的定義信息。

從圖中可以看到，Comment的值為VIEW，說(shuō)明所查看的teacher_view是一個(gè)視圖。存儲(chǔ)引擎(Engine)、數(shù)據(jù)長(zhǎng)度(Data_length)、索引長(zhǎng)度(Index_length)等信息都顯示為NULL，說(shuō)明視圖是虛擬表。7.3視圖實(shí)例7-23使用SHOWCREATEVIEW查看視圖teacher_view。SHOWCREATEVIEW語(yǔ)句的語(yǔ)法格式如下：SHOWCREATEVIEWteacher_view\G7.3視圖實(shí)例7-241.使用CREATEORREPLACEVIEW語(yǔ)句創(chuàng)建視圖

語(yǔ)法格式為：

CREATEORREPLACE[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(字段名列表)]ASselect語(yǔ)句

[WITH[CASCADED|LOCAL]CHECKOPTION]

使用CREATEORREPLACEVIEW語(yǔ)句創(chuàng)建視圖時(shí)，如果視圖已經(jīng)存在，則用語(yǔ)句中的視圖定義修改已存在的視圖。如果視圖不存在，則創(chuàng)建一個(gè)視圖。7.3視圖7.3.5修改視圖2.使用ALTERVIEW語(yǔ)句修改視圖語(yǔ)法格式為：ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(字段名列表)]ASselect語(yǔ)句

[WITH[CASCADED|LOCAL]CHECKOPTION]

7.3視圖使用ALTERVIEW語(yǔ)句，修改例6-19創(chuàng)建的視圖teacher_view，使用視圖查詢(xún)輸出所有職稱(chēng)為“講師”的tname、tpro。SQL語(yǔ)句如下：ALTERVIEWteacher_viewASSELECTtnameas姓名,tproAS職稱(chēng)FROMteacherWHEREtpro='講師';7.3視圖實(shí)例7-25當(dāng)視圖不再需要時(shí)，可以將視圖刪除。刪除視圖只是將視圖的定義刪除，并不會(huì)影響基表中的數(shù)據(jù)。刪除視圖語(yǔ)法格式為：DROPVIEW[IFEXISTS]視圖名1[,視圖名2]...[RESTRICT|CASCADE];

在上述語(yǔ)法格式中，視圖名可以有一或多個(gè)，可同時(shí)刪除一個(gè)或多個(gè)視圖。視圖名之間用逗號(hào)分隔。刪除視圖必須有DROPVIEW權(quán)限。語(yǔ)句中加IFEXISTS可選項(xiàng)，如果存在指定視圖，則將視圖刪除，如果視圖不存在，不會(huì)出在錯(cuò)誤。7.3視圖7.3.6刪除視圖刪除視圖teacher_view。SQL語(yǔ)句如下：DROPVIEWIFEXISTSteacher_view;7.3視圖實(shí)例7-26更新視圖是指通過(guò)視圖來(lái)插入、刪除和更新基表中的數(shù)據(jù)。因?yàn)橐晥D是一個(gè)虛擬表，其中并沒(méi)有數(shù)據(jù)，無(wú)論什么時(shí)候修改視圖的數(shù)據(jù)，實(shí)際上都是通過(guò)視圖在修改基表中的數(shù)據(jù)。一、使用INSERT向視圖中插入數(shù)據(jù)

使用視圖插入數(shù)據(jù)與向基表中插入數(shù)據(jù)一樣，都可以通過(guò)INSERT語(yǔ)句來(lái)實(shí)現(xiàn)。插入數(shù)據(jù)的操作是針對(duì)視圖中的字段的插入操作，而不是針對(duì)基表中的所有的字段的插入操作。由于進(jìn)行插入操作的視圖不同于基表，所以使用視圖插入數(shù)據(jù)要滿(mǎn)足一定的限制條件。7.3視圖7.3.6更新視圖使用INSERT語(yǔ)句進(jìn)行插入操作的視圖必須能夠在基表中插入數(shù)據(jù)，否則插入操作會(huì)失敗。如果視圖上沒(méi)有包括基表中所有屬性為NOTNULL的字段，那么插入操作會(huì)由于那些字段的NULL值而失敗。如果在視圖中使用聚合函數(shù)的結(jié)果，或者是包含表達(dá)式計(jì)算的結(jié)果，則插入操作不成功。不能在使用了DISTINCT,UNION，TOP，GROUPBY或HAVING語(yǔ)句的視圖中插入數(shù)據(jù)。如果在創(chuàng)建視圖的CREATEVIEW語(yǔ)句中使用了WITHCHECKOPTION，那么所有對(duì)視圖進(jìn)行修改的語(yǔ)句必須符合WITHCHECKOPTION中限定條件。對(duì)于由多個(gè)基表聯(lián)接查詢(xún)而生成的視圖來(lái)說(shuō)，一次插入操作只能作用于一個(gè)基表上。7.3視圖向student_view視圖中插入一條數(shù)據(jù)，學(xué)號(hào)sno為“11101004”，姓名sname為“張三”，性別sgender為“女”。使用下面INSERT語(yǔ)句:INSERTINTOstudent_viewVALUES('111010