MySQL數(shù)據(jù)庫(kù) 4.1 視圖的開(kāi)發(fā)教案

教案首頁(yè)課程名稱(chēng)MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā)本節(jié)課題任務(wù)4.1視圖的開(kāi)發(fā)授課方式理實(shí)一體化教學(xué)參考及教具姜云橋主編.MySQL數(shù)據(jù)庫(kù)開(kāi)發(fā).臨科院：教務(wù)處,2023計(jì)算機(jī)MySQL數(shù)據(jù)庫(kù)軟件多媒體教學(xué)系統(tǒng)資料包教學(xué)目標(biāo)及基本要求理解視圖了解視圖特性掌握視圖的創(chuàng)建,查詢(xún)、修改和刪除視圖的方法培養(yǎng)自主學(xué)習(xí)能力教學(xué)重點(diǎn)教學(xué)難點(diǎn)創(chuàng)建視圖、查詢(xún)、修改和刪除視圖、通過(guò)視圖操作數(shù)據(jù)通過(guò)視圖操作數(shù)據(jù)教學(xué)小結(jié)作業(yè)及要求按照任務(wù)書(shū)做好預(yù)習(xí)任務(wù)創(chuàng)建視圖、查詢(xún)、修改和刪除視圖教后反思注意：用微課視頻每個(gè)典型操作做示范

教案用紙教學(xué)內(nèi)容、方法和過(guò)程附記任務(wù)4.1視圖的開(kāi)發(fā)【任務(wù)背景】出于安全的原因，有時(shí)要隱藏一些重要的數(shù)據(jù)信息。例如，社會(huì)保險(xiǎn)基金表包含著客戶(hù)的很多重要信息，如果只顯示姓名、地址等基本信息，而不顯示社會(huì)保險(xiǎn)號(hào)和工資數(shù)等重要信息。這時(shí)，可以創(chuàng)建一個(gè)視圖，在原有的表（或者視圖）的基礎(chǔ)上重新定義一張?zhí)摂M表，選取基本的或?qū)τ脩?hù)有用的信息，屏蔽掉那些對(duì)用戶(hù)沒(méi)有用，或者用戶(hù)沒(méi)有權(quán)限了解的信息，保證數(shù)據(jù)的安全。再如，我們?cè)谑褂貌樵?xún)時(shí)，很多時(shí)候要使用聚合函數(shù)，可能還要關(guān)聯(lián)好幾張表，查詢(xún)語(yǔ)句會(huì)顯得比較復(fù)雜，而且經(jīng)常要使用這樣的查詢(xún)。遇到這種情況，數(shù)據(jù)庫(kù)設(shè)計(jì)人員可以預(yù)先通過(guò)視圖創(chuàng)建好查詢(xún)。一方面，屏蔽了復(fù)雜的數(shù)據(jù)關(guān)系，另一方面，用戶(hù)只需從建好的視圖進(jìn)行查詢(xún)，就可以輕松得到想要的信息，使用戶(hù)操作簡(jiǎn)單化?！救蝿?wù)要求】本任務(wù)將從認(rèn)識(shí)視圖著手，學(xué)習(xí)視圖的創(chuàng)建、查詢(xún)、修改和刪除方法，并學(xué)會(huì)通過(guò)視圖對(duì)數(shù)據(jù)進(jìn)行查詢(xún)和統(tǒng)計(jì)，通過(guò)視圖向基表進(jìn)行數(shù)據(jù)更新的操作?！救蝿?wù)必備知識(shí)】4.1.1認(rèn)識(shí)視圖從用戶(hù)角度來(lái)看，視圖是從一個(gè)特定的角度來(lái)查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)。從數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部來(lái)看，視圖是由SELECT語(yǔ)句查詢(xún)定義的虛擬表。從數(shù)據(jù)庫(kù)系統(tǒng)外部來(lái)看，視圖就如同一張表，對(duì)表能夠進(jìn)行的一般操作都可以應(yīng)用于視圖，例如查詢(xún)、插入、修改和刪除操作等。視圖是一個(gè)虛擬表，定義視圖所引用的表稱(chēng)為基本表。視圖的作用類(lèi)似于篩選，定義視圖的篩選可以來(lái)自當(dāng)前或其他數(shù)據(jù)庫(kù)的一個(gè)或多個(gè)表，或者其他視圖。視圖一經(jīng)定義便存儲(chǔ)在數(shù)據(jù)庫(kù)中，與其相對(duì)應(yīng)的數(shù)據(jù)并沒(méi)有像表那樣又在數(shù)據(jù)庫(kù)中再存儲(chǔ)一份，通過(guò)視圖看到的數(shù)據(jù)只是存放在基本表中的數(shù)據(jù)。當(dāng)對(duì)通過(guò)視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本表的數(shù)據(jù)也要發(fā)生變化。同時(shí)，若基本表的數(shù)據(jù)發(fā)生變化，則這種變化也可以自動(dòng)地反映到視圖中。視圖的特性:（1）簡(jiǎn)單性。（2）安全性。（3）邏輯數(shù)據(jù)獨(dú)立性。4.1.2創(chuàng)建視圖語(yǔ)法格式如下。 CREATE[ORREPLACE] VIEWview_name[(column_list)] ASselect_statement說(shuō)明（1）view_name為視圖名。視圖屬于數(shù)據(jù)庫(kù)。在默認(rèn)情況下，將在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建視圖。如果要在其他給定數(shù)據(jù)庫(kù)中創(chuàng)建視圖，應(yīng)將名稱(chēng)指定為db_name.view_name。（2）CREATEVIEW語(yǔ)句能創(chuàng)建新的視圖，如果給定了ORREPLACE子句，該語(yǔ)句還能替換已有的視圖。（3）select_statement是用來(lái)創(chuàng)建視圖的SELECT語(yǔ)句，它給出了視圖的定義。該語(yǔ)句可從基本表（一個(gè)或兩個(gè)以上的表）或其他視圖進(jìn)行選擇。（4）默認(rèn)情況下，由SELECT語(yǔ)句檢索的列名將用作視圖列名。如果想為視圖列定義另外的名稱(chēng)，可使用可選的column_list子句，列出由逗號(hào)隔開(kāi)的列名稱(chēng)即可。但要注意，column_list中的名稱(chēng)數(shù)目必須等于SELECT語(yǔ)句檢索的列數(shù)。（5）視圖是虛表，只存儲(chǔ)對(duì)表的定義，不存儲(chǔ)數(shù)據(jù)?！咀⒁狻恳晥D定義服從下述限制。 （1）要求具有針對(duì)視圖的CREATEVIEW權(quán)限，以及針對(duì)由SELECT語(yǔ)句選擇的每一列上的某些權(quán)限。對(duì)于在SELECT語(yǔ)句中其他地方使用的列，必須具有SELECT權(quán)限。如果還有ORREPLACE子句，必須在視圖上具有DROP權(quán)限。（2）在視圖定義中命名的表必須已存在，視圖必須具有唯一的列名，不得有重復(fù)，就像基本表那樣。（3）視圖名不能與表同名。（4）在視圖的FROM子句中不能使用子查詢(xún)。（5）在視圖的SELECT語(yǔ)句不能引用系統(tǒng)或用戶(hù)變量。（6）在視圖的SELECT語(yǔ)句不能引用預(yù)處理語(yǔ)句參數(shù)。（7）在視圖定義中允許使用ORDERBY，但是，如果從特定視圖進(jìn)行了選擇，而該視圖使用了具有自己ORDERBY的語(yǔ)句，它將被忽略。（8）在定義中引用的表或視圖必須存在。但是，創(chuàng)建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類(lèi)問(wèn)題，可使用CHECKTABLE語(yǔ)句。（9）在定義中不能引用TEMPORARY表，不能創(chuàng)建TEMPORARY視圖。（10）不能將觸發(fā)程序與視圖關(guān)聯(lián)在一起。一、基于單表創(chuàng)建【任務(wù)4.1.1】基于student表創(chuàng)建包含學(xué)號(hào)、姓名、性別、院系的視圖view_student。mysql>createorreplaceviewview_student->as->selects_no,s_name,s_sex,d_nofromstudent;二、基于多表創(chuàng)建【任務(wù)4.1.2】創(chuàng)建視圖view_mark，包括姓名、課程名和成績(jī)字段。mysql>createorreplaceviewview_mark->as->selectst.s_name,cs.c_name,sc.mark->fromstudentstjoinscoresconst.s_no=sc.s_no->joincoursecsonsc.c_no=cs.c_no;2.3.3來(lái)自于視圖三、基于視圖創(chuàng)建【任務(wù)4.1.3】基于視圖view_mark創(chuàng)建新視圖view_mark_statistics，統(tǒng)計(jì)每位學(xué)生的總成績(jī)、平均成績(jī)。mysql>createorreplaceviewview_mark_statistics->as->selects_name,sum(mark)assum_mark,avg(mark)asavg_mark->fromview_mark->groupbys_name;因修改視圖的定義和創(chuàng)建視圖的過(guò)程基本一致，只是將createorreplaceview（該語(yǔ)句也可以修改視圖）換成alterview即可，唯一需要注意的是alterview語(yǔ)句需要視圖名已存在，其他參數(shù)和createview語(yǔ)句完全一致，故不再重復(fù)。分析與討論（1）定義視圖時(shí)基本表可以是當(dāng)前數(shù)據(jù)庫(kù)的表，也可以來(lái)自于另外一數(shù)據(jù)庫(kù)的基本表。例如，任務(wù)3.3.2的視圖VIEW_STU是在TEST庫(kù)創(chuàng)建的，但其基本表卻來(lái)自于JXGL庫(kù)，此時(shí)，基本表前面要加上數(shù)據(jù)庫(kù)名，如JXGL.STUDENTS。（2）定義視圖時(shí)可在視圖后面指明視圖的列名稱(chēng)，名稱(chēng)之間用逗號(hào)分隔，但列數(shù)要與SELECT語(yǔ)句檢索的列數(shù)相等。例如，VIEW_CJ有明確的字段名稱(chēng)（學(xué)號(hào),課程名,成績(jī)）；如果不指明，SELECT語(yǔ)句檢索的列名將用作視圖列名，例如，VIEW_COURSE的列名默認(rèn)為SELECT語(yǔ)句檢索的列名。（3）任務(wù)3.3.3的視圖VIEW_CJ來(lái)源于STUDENTS、COURSE和SCORE第3張表，如同多表查詢(xún)一樣，可用WHERE進(jìn)行連接。（4）任務(wù)3.3.4的視圖VIEW_CJ_TJ的定義來(lái)自于已建好的視圖VIEW_CJ，向最終用戶(hù)隱藏復(fù)雜的表連接，簡(jiǎn)化了用戶(hù)的SQL程序設(shè)計(jì)。（5）使用視圖查詢(xún)時(shí)，若其關(guān)聯(lián)的基本表中添加了新字段，則該視圖將不包含新字段。例如，視圖VIEW_STU中的列關(guān)聯(lián)了STUDENTS表中所有列，若STUDENTS表新增了“nativeplace”字段，那么VIEW_STU視圖中將查詢(xún)不到“nativeplace”字段的數(shù)據(jù)。（6）如果與視圖相關(guān)聯(lián)的表或視圖被刪除，則該視圖將不能再使用。4.1.3查看視圖查看視圖是指查看數(shù)據(jù)庫(kù)中已存在的視圖的定義。查看視圖必須要有SHOWVIEW的權(quán)限，mysql數(shù)據(jù)庫(kù)下的user表中保存著這個(gè)信息。查看視圖的方法包括DESCRIBE語(yǔ)句、SHOWTABLESTATUS語(yǔ)句和SHOWCREATEVIEW語(yǔ)句等。一、查看已創(chuàng)建的視圖視圖是一個(gè)虛表，所以視圖的查詢(xún)還是如同查詢(xún)基本表一樣，用SHOWTABLES命令進(jìn)行查看,可看到新創(chuàng)建的視圖。二、查看視圖結(jié)構(gòu)【任務(wù)4.1.5】使用DESC命令查看創(chuàng)建的視圖的結(jié)構(gòu)。 mysql>DESCVIEW_STU;mysql>DESCVIEW_cj;三、查看視圖的定義MySQL中，SHOWCREATEVIEW語(yǔ)句可以查看視圖的詳細(xì)定義。其語(yǔ)法形式如下。 SHOWCREATEVIEW視圖名;【任務(wù)4.1.6】查看視圖VIEW_CJ的定義。mysql>SHOWCREATEVIEWVIEW_CJ;4.1.4使用視圖一、查詢(xún)數(shù)據(jù)【任務(wù)4.1.7】通過(guò)視圖VIEW_CJ,查詢(xún)選修了MYSQL課程且成績(jī)及格以上的學(xué)生。Mysql>SELECT學(xué)號(hào),課程名,成績(jī) FROMVIEW_CJWHERE課程名='MYSQL' AND成績(jī)>=60二、插入數(shù)據(jù)【任務(wù)4.1.8】往視圖view_student插入下列數(shù)據(jù):+++++|s_no|s_name|s_sex|d_no|+++++|122008|張三|男|D001||122013|李四|男|D002|+++++mysql>insertintoview_student(s_no,s_name,s_sex,d_no)->values('122008','張三','男','D001'),->('122013','李四','男','D002');三、更新數(shù)據(jù)【任務(wù)4.1.9】更新視圖view_student中“張三”的院系編號(hào)為“D003”。mysql>updateview_student->setd_no='D003'->wheres_name='張三';【任務(wù)4.1.10】通過(guò)視圖view_mark修改“張群”的MYSQL課程的成績(jī)?yōu)?6。mysql>updateview_mark->setmark=76->wheres_name='張群'andc_name='MYSQL';說(shuō)明：若一個(gè)視圖依賴(lài)于多個(gè)基本表，則一次更新只能修改一個(gè)基本表的數(shù)據(jù)，不能同時(shí)修改多個(gè)基本表的數(shù)據(jù)。四、刪除數(shù)據(jù)【任務(wù)4.1.11】通過(guò)視圖view_student刪除“章偉峰”的記錄。mysql>deletefromview_student->wheres_name='章偉峰';分析與討論：1)若一個(gè)視圖依賴(lài)于一個(gè)基本表，則可以直接通過(guò)更新視圖來(lái)更新基本表的數(shù)據(jù)。view_stu的定義基于一個(gè)表student，因此，通過(guò)視圖view_student可以向student表插入、更新和刪除數(shù)據(jù)。2)若一個(gè)視圖依賴(lài)于多個(gè)基本表，則一次更新只能修改一個(gè)基本表的數(shù)據(jù)，不能同時(shí)修改多個(gè)基本表的數(shù)據(jù)。3)如果視圖定義包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的:①定義視圖的select語(yǔ)句中的字段列表使用了聚合函數(shù)。②定義視圖的select語(yǔ)句中的字段列表使用了數(shù)學(xué)表達(dá)式。③視圖的字段列表不包含基表中被定義為非空的列。④定義視圖的select語(yǔ)句中使用了distinct、union、groupby、having子句?？傊痪湓?huà)，視圖中的記錄到基本表中的記錄是不可逆的話(huà)，則該視圖就是不可更新的分析與討論（1）若一個(gè)視圖依賴(lài)于一個(gè)基本表，則可以直接通過(guò)更新視圖來(lái)更新基本表的數(shù)據(jù)。view_stu的定義基于一個(gè)表STUDENTS，因此，通過(guò)視圖view_stu可以向STUDENTS表插入、更新和刪除數(shù)據(jù)。（2）若一個(gè)視圖依賴(lài)于多個(gè)基本表，則一次更新只能修改一個(gè)基本表的數(shù)據(jù)，不能同時(shí)修改多個(gè)基本表的數(shù)據(jù)。（3）如果視圖包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的：①聚合函數(shù)（AVG、COUNT、SUM、MIN、MAX）。②通過(guò)表達(dá)式并使用列計(jì)算出其他列。③DISTINCT關(guān)鍵字。④GROUPBY子句、ORDERBY子句、HAVING子句。⑤UNION運(yùn)算符。⑥位于選擇列表中的子查詢(xún)。⑦FROM子句中包含多個(gè)表。⑧SELECT語(yǔ)句中引用了不可更新視圖。⑨WHERE子句中的子查詢(xún)，引用FROM子句中的表。4.1.5刪除視圖dropview[ifexists]view_name1,view_name2,...說(shuō)明：1)聲明了ifexists，視圖不存在的話(huà)，也不會(huì)出現(xiàn)錯(cuò)誤信息。2)一次性刪除多個(gè)視圖時(shí)，視圖之間通過(guò)逗號(hào)（,）分隔?！救蝿?wù)4.1.12】刪除視圖view_mark、view_mark_statistics、view_student。mysql>dropviewifexistsview_mark,view_mark_statistics,view_student;【項(xiàng)目實(shí)踐】在rsgl數(shù)據(jù)庫(kù)中，進(jìn)行如下操作。1.創(chuàng)建視圖，統(tǒng)計(jì)每個(gè)系部博士、碩士、本科的人數(shù)，查詢(xún)?cè)撘晥D運(yùn)行效果如下：+++++|d_id|博士|碩士|本科|+++++|B001|1|2|1||B003|2|2|2||A001|1|2|1||A002|0|1|0||A004|0|2|0||A005|1|0|0||B005|2|1|0||B002|0|1|0|+++++2.創(chuàng)建視圖，統(tǒng)計(jì)系部編號(hào)為“A001”的員工的姓名、編號(hào)、性別、出生日期，查詢(xún)視圖運(yùn)行效果如下：+++++|e_name|e_id|sex|birth|+++++|石小華|100103|女|1978-06-07||張平娜|200103|女|1989-04-23||張白燕|200220|女|1987-05-01||李青青|200221|女|1964-10-06|+++++3.創(chuàng)建視圖，統(tǒng)計(jì)每個(gè)系的最高基本工資，最低基本工資，平均基本工資（保留2位小數(shù)），查詢(xún)視圖，運(yùn)行效果如下：+++++|d_id|最高基本工資|最低基本工資|平均基本工資|+++++|B0