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

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

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

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