MySQL數(shù)據(jù)庫應(yīng)用與管理實戰(zhàn) 教案 模塊7 用視圖方式操作MySQL數(shù)據(jù)表

模塊7用視圖方式操作MySQL數(shù)據(jù)表課程名稱MySQL數(shù)據(jù)庫應(yīng)用、設(shè)計與管理任務(wù)驅(qū)動教程章名用視圖方式操作MySQL數(shù)據(jù)表教學(xué)內(nèi)容用視圖方式操作MySQL數(shù)據(jù)表課時4項目性質(zhì)□演性 驗性 □計性 √合性授課班級授課日期授課地點教學(xué)目標(biāo)了解視圖的基本概念掌握創(chuàng)建視圖的方法掌握查看視圖相關(guān)信息的方法教學(xué)內(nèi)容認(rèn)知視圖創(chuàng)建視圖查看視圖相關(guān)信息教學(xué)重點視圖的創(chuàng)建與查看教學(xué)難點視圖的創(chuàng)建與查看教學(xué)準(zhǔn)備裝有MySQL、NavicatforMySQL等軟件的計算機(jī)教學(xué)課件PPT教材：《MySQL數(shù)據(jù)庫應(yīng)用、設(shè)計與管理任務(wù)驅(qū)動教程（微課版）》作業(yè)設(shè)計教學(xué)過程教學(xué)環(huán)節(jié)教學(xué)內(nèi)容與過程（教學(xué)內(nèi)容、教學(xué)方法、組織形式、教學(xué)手段）課前組織做好上課前的各項準(zhǔn)備工作（打開計算機(jī)、打開課件、打開軟件、打開授課計劃、教案等），吸引學(xué)生注意力。課程說明【課前說明】分別從視圖的定義、視圖的優(yōu)缺點等知識點進(jìn)行初步的了解。【目的】使學(xué)生從了解本節(jié)課的學(xué)習(xí)目標(biāo)、學(xué)習(xí)重點、考評方式等方面明確課程學(xué)習(xí)的要求和目標(biāo)。課程內(nèi)容描述7.1認(rèn)知視圖7.1.1視圖的定義視圖是一種常用的數(shù)據(jù)庫對象，可以把它看成從一張或幾張源表導(dǎo)出的虛表或存儲在數(shù)據(jù)庫中的查詢。對于視圖所引用的源表來說，視圖的作用類似于篩選。篩選的數(shù)據(jù)可以來自當(dāng)前或其他數(shù)據(jù)庫的一張或多張表，也可以來自其他視圖。視圖與數(shù)據(jù)表不同，數(shù)據(jù)庫中只存放視圖的定義，即SQL語句，而不存放視圖對應(yīng)的數(shù)據(jù)，數(shù)據(jù)存放在源表中。當(dāng)源表中的數(shù)據(jù)發(fā)生變化時，從視圖中查詢出的數(shù)據(jù)也會隨之改變。對視圖進(jìn)行操作時，系統(tǒng)會根據(jù)視圖的定義去操作與視圖相關(guān)聯(lián)的數(shù)據(jù)表。視圖一經(jīng)定義后，就可以像源表一樣被查詢、修改和刪除。視圖為查看和存取數(shù)據(jù)提供了另外一種途徑，對于直接查詢數(shù)據(jù)表能完成的大多數(shù)操作，使用視圖一樣可以完成；使用視圖還可以簡化數(shù)據(jù)操作；當(dāng)通過視圖修改數(shù)據(jù)時，相應(yīng)的源表的數(shù)據(jù)也會發(fā)生變化；同時，若源表的數(shù)據(jù)發(fā)生變化，則這種變化也會自動地同步反映到視圖中。7.1.2視圖的優(yōu)點視圖是在源表或者視圖基礎(chǔ)上重新定義的虛擬表，可以從源表中選取用戶所需的數(shù)據(jù)，屏蔽那些對用戶沒有用或者用戶沒有權(quán)限了解的數(shù)據(jù)，這樣做既使應(yīng)用簡單化，也保證了數(shù)據(jù)安全。視圖具有以下優(yōu)點。（1）簡化操作。視圖大大簡化了用戶對數(shù)據(jù)的操作。如果一個查詢非常復(fù)雜，需要跨越多張數(shù)據(jù)表，那么可以將這個復(fù)雜查詢定義為視圖，這樣在每一次執(zhí)行相同的查詢操作時，只要一條簡單的查詢視圖語句就可以了。視圖向用戶隱藏了表與表之間復(fù)雜的連接操作。（2）提高數(shù)據(jù)安全性。視圖能創(chuàng)建一種可以控制的環(huán)境，為不同的用戶定義不同的視圖，使每個用戶只能看到他有權(quán)看到的部分?jǐn)?shù)據(jù)。那些沒有必要的、敏感的或不合適的數(shù)據(jù)都從視圖中排除了，用戶只能查詢和修改視圖中顯示的數(shù)據(jù)。（3）屏蔽數(shù)據(jù)庫的復(fù)雜性。用戶不必了解數(shù)據(jù)庫中復(fù)雜的表結(jié)構(gòu)，視圖將數(shù)據(jù)庫設(shè)計的復(fù)雜性和用戶的使用方式屏蔽了。數(shù)據(jù)庫管理員可以在視圖中將那些難以理解的字段名稱替換成數(shù)據(jù)庫用戶容易理解和接受的名稱，從而為用戶的使用提供極大便利，并且數(shù)據(jù)庫中表的更改也不會影響用戶對數(shù)據(jù)庫的使用。（4）數(shù)據(jù)即時更新。當(dāng)視圖所基于的數(shù)據(jù)表發(fā)生變化時，視圖能夠即時更新，提供與數(shù)據(jù)表一致的數(shù)據(jù)。（5）便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次。7.2創(chuàng)建視圖7.2.1創(chuàng)建視圖的語法格式創(chuàng)建視圖可以使用CreateView語句，該語句完整的語法格式如下：Create[OrReplace][<算法選項>][<視圖定義者>][<安全性選項>]View<視圖名>[<視圖的字段名稱列表>]As<Select語句>[檢查選項]視圖在數(shù)據(jù)庫中是作為一個對象來存儲的。用戶創(chuàng)建視圖前，要保證自己已被數(shù)據(jù)庫所有者授權(quán)可以使用CreateView語句，并且有權(quán)操作視圖所涉及的數(shù)據(jù)表或其他視圖。7.2.2創(chuàng)建視圖的注意事項創(chuàng)建視圖的注意事項如下。（1）定義視圖的用戶必須對所參照的源表或視圖有查詢的權(quán)限（即可執(zhí)行Select語句），運行創(chuàng)建視圖的語句需要用戶具有創(chuàng)建視圖（CrateView）的權(quán)限，若添加了“OrReplace”選項，還需要用戶具有刪除視圖（DropView）的權(quán)限。（2）Select語句不能包含F(xiàn)rom子句中的子查詢。（3）Select語句不能引用系統(tǒng)或用戶變量。（4）Select語句不能引用預(yù)處理語句參數(shù)。（5）在存儲子程序內(nèi)，不能引用子程序參數(shù)或局部變量。（6）在定義中引用的數(shù)據(jù)表或視圖必須存在。但是在創(chuàng)建了視圖后，能夠舍棄定義引用的數(shù)據(jù)表或視圖。要想檢查視圖定義時引用的數(shù)據(jù)表或視圖是否存在這類問題，可使用CheckTable語句。（7）在定義中不能引用臨時表，不能創(chuàng)建臨時視圖。（8）在視圖定義中命名的數(shù)據(jù)表必須已存在，如果引用的不是當(dāng)前數(shù)據(jù)庫的數(shù)據(jù)表或視圖，要在數(shù)據(jù)表或視圖前加上數(shù)據(jù)庫的名稱。（9）不能將默認(rèn)值或觸發(fā)器與視圖關(guān)聯(lián)在一起。（10）在視圖定義中允許使用OrderBy，但是，如果從特定視圖進(jìn)行選擇，而該視圖使用了具有自己OrderBy的語句，則它將被忽略。（11）不能在視圖上建立任何索引，包括全文索引。7.3查看視圖相關(guān)信息1．使用Describe語句查看視圖的結(jié)構(gòu)定義如果只需要了解視圖各個字段的簡單信息，可以使用Describe語句查看視圖的結(jié)構(gòu)定義，與查詢數(shù)據(jù)表的結(jié)構(gòu)一樣。通常情況下，可以使用Desc代替Describe關(guān)鍵字。語法格式如下：Describe<視圖名稱>;2．使用ShowTableStatus語句查看視圖的基本信息在MySQL中，可以使用ShowTableStatus語句查看視圖的基本信息，其語法格式如下：ShowTableStatusLike<視圖名稱>;。3．使用ShowCreateView語句查看視圖的定義信息在MySQL中，可以使用ShowCreateView語句查看視圖的定義信息，其語法格式如下：ShowCreateView<視圖名稱>;【任務(wù)7-1】使用CreateView語句創(chuàng)建單源表視圖【任務(wù)描述】創(chuàng)建一個名為“view_人郵社0701”的視圖，該視圖包括“人民郵電出版社”出版的所有圖書信息，視圖中包括數(shù)據(jù)表“圖書信息”中的商品編號、圖書名稱、出版社、商品類型等數(shù)據(jù)，已知“人民郵電出版社”的“出版社ID”字段的值為1?！救蝿?wù)實施】1．創(chuàng)建視圖創(chuàng)建視圖對應(yīng)的SQL語句如下：CreateOrReplaceViewview_人郵社0701AsSelect商品編號,圖書名稱,出版社,商品類型From圖書信息Where出版社=1;2．使用Select語句查看視圖的記錄數(shù)據(jù)使用Select語句查看視圖的記錄數(shù)據(jù)的語句如下：Select*Fromview_人郵社0701;查看視圖的記錄數(shù)據(jù)的部分結(jié)果如圖所示。查看視圖的記錄數(shù)據(jù)的部分結(jié)果3．使用Desc語句查看視圖的結(jié)構(gòu)定義使用Desc語句查看視圖的結(jié)構(gòu)定義的語句如下：Descview_人郵社0701;查看視圖的結(jié)構(gòu)定義的結(jié)果如圖所示。查看視圖的結(jié)構(gòu)定義的結(jié)果圖7-2所示的視圖的結(jié)構(gòu)定義顯示了視圖的字段名稱、數(shù)據(jù)類型、是否允許包含Null、是否為主/外鍵、默認(rèn)值和其他信息。4．使用ShowTableStatus語句查看視圖的基本信息使用ShowTableStatus語句查看視圖的基本信息的語句如下：ShowTableStatusLike'view_人郵社0701';5．使用ShowCreateView語句查看視圖的定義信息使用ShowCreateView語句查看視圖的定義信息的語句如下：ShowCreateViewview_人郵社0701;按【Enter】鍵，在該語句的執(zhí)行結(jié)果中，對應(yīng)的CreateView語句如下:CreateAlgorithm=UndefinedDefiner='Root'@'Localhost'SqlSecurityDefinerView'View_人郵社0701'AsSelect'圖書信息'.'商品編號'As'商品編號','圖書信息'.'圖書名稱'As'圖書名稱','圖書信息'.'價格'As'價格','圖書信息'.'出版社'As'出版社','圖書信息'.'商品類型'As'商品類型'From'圖書信息'Where(('圖書信息'.'出版社'=1)And('圖書信息'.'價格'>40))【任務(wù)7-2】使用Navicat圖形管理工具創(chuàng)建多源表視圖多源表視圖指的是視圖的數(shù)據(jù)來源有兩張或多張數(shù)據(jù)表，這種視圖在實際應(yīng)用中使用得更多一些?！救蝿?wù)描述】創(chuàng)建一個名為“view_人郵社0702”的視圖，該視圖包括“人民郵電出版社”出版的所有圖書信息，視圖中包括數(shù)據(jù)表“圖書信息”中的商品編號、圖書名稱，數(shù)據(jù)表“出版社信息”中的出版社名稱，數(shù)據(jù)表“商品類型”中的類型名稱等數(shù)據(jù)?！救蝿?wù)實施】（1）啟動圖形管理工具NavicatforMySQL，打開連接“MallConn”，打開數(shù)據(jù)庫“MallDB”。（2）單擊【NavicatforMySQL】窗口工具欄中的【視圖】按鈕，顯示“視圖”對象，如圖所示。在【NavicatforMySQL】窗口中顯示“視圖”對象（3）單擊【新建視圖】按鈕，顯示【定義】、【高級】和【SQL預(yù)覽】多個選項卡，如圖所示。在【NavicatforMySQL】窗口中新建視圖在【視圖】工具欄中單擊【視圖創(chuàng)建工具】按鈕，打開【視圖創(chuàng)建工具】窗口，窗口左側(cè)為數(shù)據(jù)庫“MallDB”中的數(shù)據(jù)表列表，中部提供了查詢語句的生成模板，右側(cè)為顯示SQL語句的區(qū)域，如圖所示?！疽晥D創(chuàng)建工具】窗口（4）選擇創(chuàng)建視圖的數(shù)據(jù)表與創(chuàng)建關(guān)聯(lián)關(guān)系。在【視圖創(chuàng)建工具】窗口左側(cè)數(shù)據(jù)表列表中雙擊數(shù)據(jù)表“圖書信息”“出版社信息”“商品類型”，在中部上方彈出“圖書信息”“出版社信息”和“商品類型”數(shù)據(jù)表可供選擇的字段。在“出版社信息”字段列表中單擊字段名“出版社ID”，并按住鼠標(biāo)左鍵將其拖曳到“圖書信息”數(shù)據(jù)表的“出版社”字段位置，松開鼠標(biāo)左鍵，即完成“出版社信息”與“圖書信息”數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系的創(chuàng)建。以同樣的方法，創(chuàng)建“商品類型”與“圖書信息”數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系。（5）從已選的數(shù)據(jù)表中選擇所需的字段。分別從“圖書信息”字段列表中選擇“商品編號”和“圖書名稱”，從“出版社信息”字段列表中選擇“出版社名稱”，從“商品類型”字段列表中選擇“類型名稱”，同時SQL語句區(qū)域會自動生成對應(yīng)的SQL語句，如圖所示。在【視圖創(chuàng)建工具】窗口中選擇要查詢的數(shù)據(jù)表和字段（6）設(shè)置查詢條件。在【視圖創(chuàng)建工具】窗口中切換到【W(wǎng)HERE】選項卡，單擊【+】按鈕，出現(xiàn)“<值>=<值>”的條件輸入標(biāo)識，單擊“=”左側(cè)的“<值>”，在彈出的對話框中切換到【標(biāo)識符】選項卡，然后在3張數(shù)據(jù)表的字段列表中單擊字段“出版社名稱”，如圖所示。在字段列表中選擇所需的字段名“出版社名稱”單擊“=”右側(cè)的“<值>”，在彈出的對話框的【編輯】文本框中輸入“'人民郵電出版社'”，如圖所示。在【編輯】文本框中輸入“'人民郵電出版社'”在【視圖創(chuàng)建工具】窗口中單擊【構(gòu)建】按鈕，關(guān)閉該窗口并返回NavicatforMySQL的“視圖”定義區(qū)域。設(shè)置好字段、數(shù)據(jù)表及關(guān)聯(lián)條件、Where條件的查詢語句如下：Select'圖書信息'.'商品編號','圖書信息'.'圖書名稱','出版社信息'.'出版社名稱','商品類型'.'類型名稱'From'圖書信息'InnerJoin'出版社信息'On'圖書信息'.'出版社'='出版社信息'.'出版社ID'InnerJoin'商品類型'On'圖書信息'.'商品類型'='商品類型'.'類型編號'Where'出版社信息'.'出版社名稱'='人民郵電出版社'在【視圖】工具欄中單擊【保存】按鈕，在彈出的【視圖名】對話框中輸入視圖名“view_人郵社0702”，如圖所示，然后單擊【確定】按鈕保存創(chuàng)建的視圖?！疽晥D名】對話框切換到【高級】選項卡，查看高級選項設(shè)置，如圖所示?！八惴ā睘椤癠NDEFINED”，即MySQL自動選擇算法；“定義者”為“root@localhost”；“安全性”為“DEFINER”；“檢查選項”這里未設(shè)置。查看視圖的高級選項設(shè)置在【視圖】工具欄中單擊【預(yù)覽】按鈕，切換到【定義】選項卡中查看視圖對應(yīng)的Select語句和運行結(jié)果，如圖所示。查看視圖對應(yīng)的Select語句和運行結(jié)果【任務(wù)7-3】修改視圖當(dāng)視圖不符合使用需求時，可以使用AlterView語句對其進(jìn)行修改，視圖的修改與創(chuàng)建相似，其語法格式如下：Alter[<算法選項>][<視圖定義者>][<安全性選項>]View<視圖名>As<Select語句>[檢查選項]AlterView語句的語法與CreateView語句類似，相關(guān)參數(shù)的作用和含義詳見前面介紹的CreateView語句?！救蝿?wù)描述】（1）修改視圖“view_人郵社0701”，使該視圖包括“人民郵電出版社”出版的價格高于40元的所有圖書信息，視圖中包括數(shù)據(jù)表“圖書信息”中的商品編號、圖書名稱、價格、出版社、商品類型等數(shù)據(jù)。（2）修改視圖“view_人郵社0702”，使該視圖包括“人民郵電出版社”出版的價格高于40元的所有圖書信息，視圖中包括數(shù)據(jù)表“圖書信息”中的商品編號、圖書名稱、價格，數(shù)據(jù)表“出版社信息”中的出版社名稱，數(shù)據(jù)表“商品類型”中的類型名稱等數(shù)據(jù)?！救蝿?wù)實施】1．修改視圖“view_人郵社0701”使用AlterView語句修改視圖“view_人郵社0701”的語句如下：AlterAlgorithm=UndefinedDefiner=root@localhostSqlSecurityDefinerViewview_人郵社0701AsSelect圖書信息.商品編號,圖書信息.圖書名稱,圖書信息.價格,圖書信息.出版社,圖書信息.商品類型From圖書信息Where圖書信息.出版社=1And圖書信息.價格>40;查看視圖的記錄數(shù)據(jù)的語句如下：Select*Fromview_人郵社0701;查看視圖“view_人郵社0701”修改后的記錄數(shù)據(jù)的全部結(jié)果如圖所示。查看視圖“view_人郵社0701”修改后的記錄數(shù)據(jù)的全部結(jié)果2．修改視圖“view_人郵社07使用AlterView語句修改視圖“view_人郵社0702”的語句如下：AlterAlgorithm=UndefinedDefiner=root@localhostSqlSecurityDefinerViewview_人郵社0702AsSelect圖書信息.商品編號,圖書信息.圖書名稱,圖書信息.價格,出版社信息.出版社名稱,商品類型.類型名稱From圖書信息InnerJoin出版社信息On圖書信息.出版社=出版社信息.出版社IDInnerJoin商品類型On圖書信息.商品類型=商品類型.類型編號Where出版社信息.出版社名稱='人民郵電出版社'And圖書信息.價格>40;查看視圖的記錄數(shù)據(jù)的語句如下：Select*Fromview_人郵社0702;查看視圖“view_人郵社0702”修改后的記錄數(shù)據(jù)的全部結(jié)果如圖所示。查看視圖“view_人郵社0702”修改后的記錄數(shù)據(jù)的全部結(jié)果【任務(wù)7-4】利用視圖查詢與更新數(shù)據(jù)表中的數(shù)據(jù)更新視圖是指通過視圖來插入（Insert）、更新（Update）和刪除（Delete）數(shù)據(jù)表中的數(shù)據(jù)。因為視圖是一張?zhí)摂M表，其中沒有數(shù)據(jù)，所以視圖進(jìn)行更新時，都是轉(zhuǎn)換到源表來更新的。更新視圖時，只能更新權(quán)限范圍內(nèi)可以更新的數(shù)據(jù)，超出權(quán)限范圍則無法更新?！救蝿?wù)描述】（1）創(chuàng)建一個名為“view_用戶注冊0703”的視圖，該視圖包括所有的用戶注冊信息。（2）利用視圖“view_用戶注冊0703”查詢“權(quán)限等級”為“C”的用戶注冊信息。（3）利用視圖“view_用戶注冊0703”新增一個注冊用戶，“用戶ID”為“7”，“用戶編號”為“u00007”，“用戶名稱”為“測試用戶”，“密碼”為“todayBetter”，“權(quán)限等級”為“A”，“手機(jī)號碼”為，“用戶類型”為“2”。（4）利用戶類型修改為“1”。（5）利用視圖“view_用戶注冊0703”刪除前面新增的用戶“測試用戶”?！救蝿?wù)實施】1．創(chuàng)建視圖創(chuàng)建視圖“view_用戶注冊0703”對應(yīng)的語句如下：CreateOrReplaceViewview_用戶注冊0703AsSelect用戶ID,用戶編號,