MySQL數(shù)據(jù)庫(kù)管理與應(yīng)用任務(wù)式教程（微課版）單元6 創(chuàng)建與管理視圖

單元6創(chuàng)建與管理視圖《MySQL數(shù)據(jù)庫(kù)管理與應(yīng)用任務(wù)式教程（慕課版）》學(xué)習(xí)內(nèi)容任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖

2任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖數(shù)據(jù)庫(kù)系統(tǒng)的組成任務(wù)3通過(guò)視圖更新數(shù)據(jù)1.視圖的概念視圖(View)是一種基本的數(shù)據(jù)庫(kù)對(duì)象，它是由基于一個(gè)或多個(gè)數(shù)據(jù)表(或視圖)的一個(gè)查詢生成的虛擬表。視圖中保存著該查詢的定義。同真實(shí)表一樣，視圖也由列和行構(gòu)成，但與真實(shí)表不同，視圖本身并不存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在視圖引用的數(shù)據(jù)表(通常稱為基表)中，視圖的行和列的數(shù)據(jù)來(lái)自基表，并且是在使用視圖時(shí)執(zhí)行查詢語(yǔ)句動(dòng)態(tài)生成的。一旦真實(shí)表中的數(shù)據(jù)發(fā)生改變，顯示在視圖中的數(shù)據(jù)也會(huì)發(fā)生改變。我們可以這樣理解，數(shù)據(jù)庫(kù)中只存放了視圖的定義，視圖是一個(gè)或多個(gè)表(或視圖)查詢的結(jié)果。使用視圖查詢數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)會(huì)從基表中取出對(duì)應(yīng)的數(shù)據(jù)，并以二維表的形式呈現(xiàn)出來(lái)。視圖就像是基表的窗口，它反映了一個(gè)或多個(gè)基表的局部數(shù)據(jù)。6.1.1認(rèn)識(shí)視圖任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖2.視圖常見(jiàn)的應(yīng)用視圖在數(shù)據(jù)庫(kù)中起著非常重要的作用，常用于以下幾個(gè)方面。顯示來(lái)自基表的部分行數(shù)據(jù)。如顯示學(xué)生表xs中所有男同學(xué)的信息。顯示來(lái)自基表的部分列數(shù)據(jù)。如顯示學(xué)生表xs中所有同學(xué)的學(xué)號(hào)、姓名、性別信息。顯示來(lái)自基表的統(tǒng)計(jì)數(shù)據(jù)。如統(tǒng)計(jì)學(xué)生表xs中各專業(yè)的學(xué)生人數(shù)。顯示來(lái)自多個(gè)基表的復(fù)雜查詢數(shù)據(jù)。如顯示學(xué)生表xs中同一個(gè)專業(yè)的學(xué)生成績(jī)信息，包含學(xué)號(hào)、姓名、專業(yè)名、課程名和成績(jī)列。簡(jiǎn)化數(shù)據(jù)交換操作。如可以將學(xué)生成績(jī)信息集中到一個(gè)視圖中，當(dāng)需要交換數(shù)據(jù)時(shí)只需將該視圖中的數(shù)據(jù)導(dǎo)出。任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖3.視圖的優(yōu)點(diǎn)和缺點(diǎn)(1)視圖的優(yōu)點(diǎn)定制用戶數(shù)據(jù)。視圖可以使用戶將注意力集中在其關(guān)心的數(shù)據(jù)上，而不必了解基表的結(jié)構(gòu)，從而屏蔽了數(shù)據(jù)的復(fù)雜性。通過(guò)定義視圖，用戶眼中的數(shù)據(jù)庫(kù)結(jié)構(gòu)變得更簡(jiǎn)單、清晰。簡(jiǎn)化數(shù)據(jù)操作。在使用查詢時(shí)，很多時(shí)候要使用聚合函數(shù)，同時(shí)還要顯示列的信息，可能還需要關(guān)聯(lián)其他表，語(yǔ)句會(huì)很長(zhǎng)，如果這個(gè)動(dòng)作頻繁發(fā)生的話，可以創(chuàng)建視圖來(lái)簡(jiǎn)化操作。共享所需數(shù)據(jù)。使用視圖，不必每個(gè)用戶都定義和存儲(chǔ)自己所需的數(shù)據(jù)，可以共享數(shù)據(jù)庫(kù)中的數(shù)據(jù)，同樣的數(shù)據(jù)只需要存儲(chǔ)一次。視圖提供了一個(gè)簡(jiǎn)單而有效的安全機(jī)制，能夠?qū)?shù)據(jù)提供安全保護(hù)。在設(shè)計(jì)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)時(shí)，可以對(duì)不同的用戶需求定義不同的視圖，使用戶看到的只是他該看到的數(shù)據(jù)，從而達(dá)到保護(hù)基礎(chǔ)數(shù)據(jù)的目的。一般來(lái)說(shuō)，在使用敏感數(shù)據(jù)的企業(yè)里，視圖幾乎是唯一可以用來(lái)面向普通用戶的數(shù)據(jù)庫(kù)對(duì)象。數(shù)據(jù)的完整性。在用戶通過(guò)視圖訪問(wèn)或者更新數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)管理系統(tǒng)的相關(guān)部分會(huì)自動(dòng)地檢查數(shù)據(jù)，確保它滿足預(yù)先設(shè)定的完整性約束。任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖(2)視圖的缺點(diǎn)性能不穩(wěn)定。對(duì)視圖進(jìn)行查詢必須先將其轉(zhuǎn)換為對(duì)底層基表的查詢。若視圖的定義是一個(gè)基于多表的復(fù)雜的查詢語(yǔ)句，可能需要花費(fèi)很長(zhǎng)的時(shí)間來(lái)處理查詢操作。數(shù)據(jù)更新受限。由于視圖是一個(gè)虛擬表，而不是一個(gè)實(shí)際存在的表，對(duì)視圖的更新操作實(shí)際上是對(duì)基表的更新操作?；淼耐暾约s束必將影響視圖，這樣視圖中的數(shù)據(jù)更新將受到限制。數(shù)值格式不同。在不同的數(shù)據(jù)庫(kù)系統(tǒng)中，由于系統(tǒng)使用的數(shù)據(jù)類型和系統(tǒng)顯示數(shù)值的方式有所差別，因此通過(guò)視圖檢索獲得的結(jié)果中可能有一些數(shù)值格式不同的數(shù)據(jù)。任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖在MySQL中，可以用CREATEVIEW語(yǔ)句來(lái)創(chuàng)建視圖，其語(yǔ)法格式如下。CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖6.1.2用命令方式創(chuàng)建視圖1.創(chuàng)建基于一個(gè)基表的視圖默認(rèn)情況下，創(chuàng)建的視圖的列和基表的列是一樣的，可以通過(guò)指定視圖中列的名稱來(lái)創(chuàng)建視圖?！纠}6.1】在成績(jī)管理數(shù)據(jù)庫(kù)cjgl中，創(chuàng)建學(xué)生視圖，查看各學(xué)生的基本信息。打開(kāi)MySQLWorkbench，然后打開(kāi)成績(jī)管理數(shù)據(jù)庫(kù)單擊工具欄上的“CreateanewSQL

tabforexecutingqueries”按鈕，創(chuàng)建用于執(zhí)行查詢的新SQL選項(xiàng)卡，打開(kāi)成績(jī)管理數(shù)據(jù)庫(kù)在代碼編輯區(qū)輸入并執(zhí)行如下語(yǔ)句。USEcjgl;CREATEORREPLACEVIEW學(xué)生ASSELECT*FROMxs;可使用SELECT語(yǔ)句查看該視圖，執(zhí)行如下語(yǔ)句，結(jié)果如圖6-1所示。SELECT*FROM學(xué)生;任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖【例題6.2】創(chuàng)建男生視圖，查看全體男生的學(xué)號(hào)、姓名、專業(yè)名。通過(guò)MySQL命令行客戶端執(zhí)行如下語(yǔ)句。USEcjgl;CREATEVIEW男生ASSELECT學(xué)號(hào),姓名,專業(yè)名FROMxsWHERE性別='男';【例題6.3】創(chuàng)建學(xué)生平均成績(jī)視圖cj_avg，其中包括學(xué)號(hào)和平均成績(jī)。執(zhí)行如下語(yǔ)句。mysql>USEcjgl;mysql>CREATEVIEW平均成績(jī)(學(xué)號(hào),平均成績(jī))ASSELECT學(xué)號(hào),AVG(成績(jī))FROMcjGROUPBY學(xué)號(hào);任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖2.創(chuàng)建基于多個(gè)基表的視圖可以創(chuàng)建基于兩個(gè)以上基表的視圖，在使用這種視圖時(shí)，用戶不需要了解基表的完整結(jié)構(gòu)，更接觸不到基表中的數(shù)據(jù)，從而保護(hù)了數(shù)據(jù)的安全?！纠}6.4】在cjgl數(shù)據(jù)庫(kù)中，創(chuàng)建名為xscj的學(xué)生成績(jī)視圖，視圖中包括學(xué)號(hào)、課程名和成績(jī)列。執(zhí)行如下語(yǔ)句。mysql>USEcjgl;mysql>CREATEVIEWxscjASSELECTcj.學(xué)號(hào),kc.課程名,cj.成績(jī)FROMcj,kcWHEREkc.課程號(hào)=cj.課程號(hào);任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖3.創(chuàng)建視圖時(shí)需要注意的事項(xiàng)創(chuàng)建視圖時(shí)應(yīng)該注意以下幾點(diǎn)。只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建視圖。視圖的創(chuàng)建者必須具有數(shù)據(jù)庫(kù)擁有者授予的創(chuàng)建視圖的權(quán)限，同時(shí)還必須具有對(duì)定義視圖時(shí)引用的表的相應(yīng)權(quán)限。視圖的命名必須符合MySQL中標(biāo)識(shí)符的定義規(guī)則。每個(gè)用戶定義的視圖名稱必須是唯一的，而且不能與用戶的某個(gè)表同名。如果視圖中的某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者視圖中來(lái)自多個(gè)表的列名相同，則必須為列定義名稱。如果視圖引用的基表或者視圖被刪除，則該視圖不能再使用。不能在視圖上創(chuàng)建索引，不能在規(guī)則、默認(rèn)值、觸發(fā)器的定義中引用視圖。任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖打開(kāi)MySQLWorkbench，在SCHEMAS欄中展開(kāi)當(dāng)前默認(rèn)的cjgl數(shù)據(jù)庫(kù)，右擊Views，在彈出式菜單中選擇CreateView...，打開(kāi)編輯視圖的對(duì)話框，如圖6-3所示。在圖6-4所示的編輯區(qū)定義視圖后單擊Apply按鈕，可以預(yù)覽當(dāng)前操作的SQL語(yǔ)句，如圖6-5所示。6.1.3用MySQLWorkbench創(chuàng)建視圖任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖任務(wù)1創(chuàng)建學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖單擊Apply按鈕，在彈出的對(duì)話框中單擊Finish按鈕，即可完成視圖的創(chuàng)建。在SCHEMAS欄中，右擊Views，在彈出式菜單中選擇RefreshAll，可以看到學(xué)生視圖。1.查看視圖的定義可以用DESCRIBE語(yǔ)句來(lái)查看視圖的結(jié)構(gòu)，其語(yǔ)法格式如下。DESCRIBE視圖名;DESCRIBE一般情況下可以簡(jiǎn)寫(xiě)成DESC?！纠}6.8】查看學(xué)生視圖的結(jié)構(gòu)。執(zhí)行如下語(yǔ)句。mysql>USEcjgl;mysql>DESCRIBE學(xué)生;可以使用SHOWTABLESTATUS語(yǔ)句查看視圖的基本信息，其語(yǔ)法格式如下。SHOWTABLESTATUSLIKE'視圖名';【例題6.9】查看學(xué)生視圖的基本信息。執(zhí)行如下語(yǔ)句。mysql>SHOWTABLESTATUSLIKE'學(xué)生'\G6.2.1用命令方式管理視圖任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖可以使用SHOWCREATEVIEW語(yǔ)句查看視圖的定義，其語(yǔ)法格式如下。SHOWCREATEVIEW視圖名;【例題6.10】查看學(xué)生視圖的定義。執(zhí)行如下語(yǔ)句。mysql>SHOWCREATEVIEW學(xué)生;任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖2.修改視圖如果要修改視圖的名稱，可以先將原視圖刪除，然后用CREATEVIEW語(yǔ)句重新創(chuàng)建視圖，將其名稱改為新的視圖名。如果視圖依賴的數(shù)據(jù)表發(fā)生變化，可以通過(guò)ALTERVIEW語(yǔ)句修改視圖來(lái)保持視圖與數(shù)據(jù)表一致。其語(yǔ)法格式如下。ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖【例題6.11】修改【例題6.4】的視圖，使視圖中增加開(kāi)課學(xué)期列。執(zhí)行如下語(yǔ)句。mysql>ALTERVIEWxscjASSELECTcj.學(xué)號(hào),kc.課程名,cj.成績(jī),kc.開(kāi)課學(xué)期FROMcj,kcWHEREkc.課程號(hào)=cj.課程號(hào);3.刪除視圖當(dāng)不再需要視圖或要清除視圖的定義和與之關(guān)聯(lián)的訪問(wèn)權(quán)限定義時(shí)，可以刪除視圖。當(dāng)視圖被刪除之后，該視圖基表中存儲(chǔ)的數(shù)據(jù)并不會(huì)受到影響，但是任何創(chuàng)建在該視圖之上的其他數(shù)據(jù)庫(kù)對(duì)象的查詢將會(huì)發(fā)生錯(cuò)誤。使用DROPVIEW語(yǔ)句可以刪除視圖，其語(yǔ)法格式如下。DROPVIEW[IFEXISTS]view_name[,view_name]...[RESTRICT|CASCADE]可以使用該語(yǔ)句同時(shí)刪除多個(gè)視圖，各視圖名稱之間需用英文逗號(hào)隔開(kāi)?！纠}6.12】刪除學(xué)生視圖。執(zhí)行如下語(yǔ)句。mysql>DROPVIEW學(xué)生;任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖1.查看視圖打開(kāi)MySQLWorkbench，在SCHEMAS欄中展開(kāi)當(dāng)前默認(rèn)的cjgl數(shù)據(jù)庫(kù)，然后展開(kāi)視圖對(duì)象，右擊想查看的學(xué)生視圖，在圖6-6所示的彈出式菜單中選擇SelectRows–Limit200，即可查看視圖內(nèi)容，如圖6-7所示。任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖6.2.2用MySQLWorkbench管理視圖3.刪除視圖打開(kāi)MySQLWorkbench，在SCHEMAS欄中展開(kāi)當(dāng)前默認(rèn)的cjgl數(shù)據(jù)庫(kù)，然后展開(kāi)視圖對(duì)象，右擊需要?jiǎng)h除的視圖，在圖6-6所示的彈出式菜單中選擇DropView...，在彈出的圖6-8所示的對(duì)話框中選擇DropNow，即可直接刪除視圖。若選擇ReviewSQL，則可以顯示刪除操作對(duì)應(yīng)的SQL語(yǔ)句，單擊Execute按鈕就可以執(zhí)行刪除視圖操作。2.修改視圖在MySQLWorkbench中，修改視圖的操作和創(chuàng)建視圖的操作相同。任務(wù)2管理學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中的視圖在MySQL中，通過(guò)視圖不僅可以查詢數(shù)據(jù)，而且可以更新數(shù)據(jù)。在對(duì)視圖的數(shù)據(jù)進(jìn)行操作時(shí)，系統(tǒng)根據(jù)視圖的定義操作與視圖關(guān)聯(lián)的基表。因此，與視圖對(duì)應(yīng)的基表的數(shù)據(jù)也會(huì)發(fā)生變化。更新視圖是指通過(guò)視圖向基表中插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)。由于視圖不是物理存儲(chǔ)的數(shù)據(jù)，因此對(duì)視圖中的數(shù)據(jù)進(jìn)行的插入、修改、刪除操作實(shí)質(zhì)上是作用在基表上的。要通過(guò)視圖更新基表數(shù)據(jù)，必須保證視圖是可更新視圖。如果視圖符合以下的任何一點(diǎn)，它就是不可更新的。（1）定義視圖的SELECT語(yǔ)句的列名列表中有聚合函數(shù)或通過(guò)計(jì)算得到的列或DISTINCT關(guān)鍵字。（2）定義視圖的SELECT語(yǔ)句中有GROUPBY、HAVING子句。（3）定義視圖的SELECT語(yǔ)句中有UNION或UNIONALL運(yùn)算符。（4）FROM子句中有不可更新視圖或包含多個(gè)表。（5）WHERE子句中的子查詢，引用FROM子句中的表。（6）ALGORITHM參數(shù)為T(mén)EMPTABLE（使用臨時(shí)