項(xiàng)目6

視圖【項(xiàng)目目標(biāo)】視圖是數(shù)據(jù)庫(kù)中主要的對(duì)象之一，視圖對(duì)應(yīng)于數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部體系結(jié)構(gòu)三級(jí)模式中的外模式，反映的是一張或多張基本表中的局部數(shù)據(jù)，視圖是一種虛擬表。理解視圖的概念。掌握創(chuàng)建和查詢(xún)視圖的方法。掌握操作視圖的方法。任務(wù)6.1視圖概述視圖是從一張或多張基本表（或視圖）中導(dǎo)出的虛擬表。視圖與基本表不同，視圖中的內(nèi)容由SELECT語(yǔ)句定義，在數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，不存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)存儲(chǔ)在基本表中，直到用戶(hù)使用視圖時(shí)才查找出對(duì)應(yīng)的數(shù)據(jù)。當(dāng)基本表中數(shù)據(jù)發(fā)生變化時(shí)與之關(guān)聯(lián)的視圖也會(huì)隨之發(fā)生變化。視圖優(yōu)點(diǎn)（1）隱藏了數(shù)據(jù)的復(fù)雜性。用戶(hù)不必了解數(shù)據(jù)庫(kù)中詳細(xì)的表結(jié)構(gòu)和復(fù)雜的表間聯(lián)系。用戶(hù)可以像使用基本表一樣使用視圖。（2）方便用戶(hù)查詢(xún)數(shù)據(jù)。當(dāng)用戶(hù)需要查詢(xún)的數(shù)據(jù)來(lái)自于不同表時(shí)，通過(guò)視圖可以將它們集中在一起方便用戶(hù)進(jìn)行查詢(xún)。（3）提高安全性。用戶(hù)使用視圖只能查詢(xún)和修改視圖中的數(shù)據(jù)，同時(shí)保證了數(shù)據(jù)的邏輯獨(dú)立性。（4）便于數(shù)據(jù)共享。各個(gè)用戶(hù)不必重復(fù)定義和存儲(chǔ)自己所需的數(shù)據(jù)，可以共享數(shù)據(jù)庫(kù)中的數(shù)據(jù)，因此同樣的數(shù)據(jù)只需存儲(chǔ)一次。6.2.1創(chuàng)建視圖CREATE[ORREPLACE]VIEW視圖名[(名稱(chēng)列表)]ASSELECT語(yǔ)句[WITH[CASCADED|LOCAL]CHECKOPTION];說(shuō)明如下。（1）ORREPLACE：可選項(xiàng)，在創(chuàng)建視圖時(shí)，如果存在同名的視圖，則可以用新建的視圖替換已有的視圖。（2）視圖名：指定視圖的名稱(chēng)。（3）名稱(chēng)列表：需要為視圖的各個(gè)列指定名稱(chēng)時(shí)，可使用這個(gè)選項(xiàng)，各名稱(chēng)之間用英文逗號(hào)分隔。名稱(chēng)列表中的名稱(chēng)個(gè)數(shù)必須與SELECT語(yǔ)句中的字段個(gè)數(shù)一致。省略名稱(chēng)列表時(shí)，視圖的列名稱(chēng)與SELECT語(yǔ)句中的字段名稱(chēng)相同。（4）SELECT語(yǔ)句：用來(lái)創(chuàng)建視圖，可以在SELECT語(yǔ)句中查詢(xún)一張表、多張表或視圖中的數(shù)據(jù)。（5）WITHCHECKOPTION：指出在視圖上進(jìn)行的修改要符合SELECT語(yǔ)句指定的WHERE限制條件，這樣在修改數(shù)據(jù)后，仍然可以通過(guò)視圖看到修改的數(shù)據(jù)。使用視圖時(shí)需要注意以下規(guī)則（1）新創(chuàng)建的視圖默認(rèn)存儲(chǔ)在當(dāng)前數(shù)據(jù)庫(kù)中。如果需要在指定的數(shù)據(jù)庫(kù)中創(chuàng)建視圖，則使用“數(shù)據(jù)庫(kù)名.視圖名”形式。（2）視圖名必須是唯一的，且不能與表名相同。（3）視圖定義中引用的基本表或視圖必須是存在的，并且定義視圖的用戶(hù)對(duì)所涉及的基本表或視圖有查詢(xún)的權(quán)限。（4）不能引用系統(tǒng)變量或用戶(hù)變量，不能引用預(yù)處理語(yǔ)句參數(shù)。（5）不能包含子查詢(xún)。（6）不能在視圖上建立任何索引?！纠?-1】創(chuàng)建名為v_reader的視圖，包含讀者表reader中的讀者編號(hào)reader_id、姓名name，讀者類(lèi)型表readertype中的類(lèi)型名稱(chēng)type、最大借閱天數(shù)maxdays。mysql>CREATEORREPLACEVIEWv_reader

->AS

->SELECTreader_id,name,type,maxdays

->FROMreaderJOINreadertypeONreader.type_id=readertype.type_id;reader_idnametypemaxdays1101003091楊麗教師901105010561趙曉麗教師901105071058李俊教師902101240103侯明斌學(xué)生602106240104王剛學(xué)生602106240206王曉紅學(xué)生602107240101李淑子學(xué)生602107240106劉麗華學(xué)生60【例6-2】創(chuàng)建名為v_price的視圖，包含圖書(shū)表book中的book_id、title、publisher和price*0.7，在視圖中，列名稱(chēng)分別為圖書(shū)編號(hào)、書(shū)名、出版社和優(yōu)惠價(jià)格。mysql>CREATEORREPLACEVIEWv_price->AS->SELECTbook_idAS圖書(shū)編號(hào),titleAS書(shū)名,publisherAS出版社,price*0.7AS優(yōu)惠價(jià)格->FROMbook;mysql>CREATEORREPLACEVIEWv_price(圖書(shū)編號(hào),書(shū)名,出版社,優(yōu)惠價(jià)格)->AS->SELECTbook_id,title,publisher,price*0.7FROMbook;圖書(shū)編號(hào)書(shū)名出版社優(yōu)惠價(jià)格100023計(jì)算機(jī)科學(xué)導(dǎo)論博達(dá)教育出版社34.86100582大學(xué)物理博達(dá)教育出版社30.73110001平面設(shè)計(jì)基礎(chǔ)職帆啟航出版社36.40110301導(dǎo)游情景英語(yǔ)職帆啟航出版社31.50110705空間數(shù)據(jù)庫(kù)原理職帆啟航出版社45.50120034學(xué)術(shù)英語(yǔ)寫(xiě)作知行聯(lián)合出版社32.83120062大數(shù)據(jù)技術(shù)基礎(chǔ)知行聯(lián)合出版社38.50120087程序設(shè)計(jì)基礎(chǔ)知行聯(lián)合出版社29.40121006大學(xué)計(jì)算機(jī)基礎(chǔ)知行聯(lián)合出版社26.46123208數(shù)據(jù)庫(kù)系統(tǒng)概論知行聯(lián)合出版社41.86【例6-3】創(chuàng)建名為v_rborrow的視圖，包含讀者編號(hào)reader_id、姓名name、館藏編號(hào)stock_id、書(shū)名title、出版社publisher和歸還時(shí)間returntime。mysql>CREATEORREPLACEVIEWv_rborrow->AS->SELECTreader.reader_id,name,stock.stock_id,title,publisher,returntime->FROMreaderJOINborrowONreader.reader_id=borrow.reader_idJOINstockONborrow.stock_id=stock.stock_idJOINbookONstock.book_id=book.book_id;6.2.2查詢(xún)視圖創(chuàng)建視圖后，對(duì)視圖的查詢(xún)與對(duì)基本表的查詢(xún)類(lèi)似，不同的是FROM子句中用的是視圖名?！纠?-4】查詢(xún)已創(chuàng)建的視圖v_reader，按照下列要求完成查詢(xún)。（1）顯示所有字段。mysql>SELECT*FROMv_reader;（2）顯示出最大借閱天數(shù)maxdays大于等于90天的讀者編號(hào)reader_id和姓名name。mysql>SELECTreader_idAS讀者編號(hào),nameAS姓名

->FROMv_reader

->WHEREmaxdays>=90;【例6-5】查詢(xún)視圖v_price，按照下列要求完成查詢(xún)。（1）顯示知行聯(lián)合出版社的圖書(shū)編號(hào)、書(shū)名和優(yōu)惠價(jià)格。mysql>SELECTbook_id,title,priceFROMv_priceWHEREpublisher='知行聯(lián)合出版社';ERROR1054(42S22):Unknowncolumn'book_id'in'fieldlist'mysql>SELECT圖書(shū)編號(hào),書(shū)名,優(yōu)惠價(jià)格->FROMv_price->WHERE出版社='知行聯(lián)合出版社';（2）統(tǒng)計(jì)各個(gè)出版社的圖書(shū)數(shù)量。mysql>SELECT出版社,COUNT(*)AS圖書(shū)數(shù)量FROMv_priceGROUPBY出版社;【例6-6】查詢(xún)視圖v_rborrow，按照下列要求完成查詢(xún)。（1）顯示楊麗借閱過(guò)的圖書(shū)的館藏編號(hào)stock_id、書(shū)名title和出版社publisher。mysql>SELECTstock_id,title,publisherFROMv_rborrow

WHEREname='楊麗';（2）顯示王曉紅借閱過(guò)的圖書(shū)的館藏編號(hào)stock_id、書(shū)名title和出版社publisher。mysql>SELECTstock_id,title,publisherFROMv_rborrow

WHEREname='王曉紅';（3）顯示楊麗和王曉紅都借閱過(guò)的圖書(shū)的館藏編號(hào)stock_id、書(shū)名title和出版社publishermysql>SELECTa.stock_id,a.title,a.publisherFROMv_rborrowa,v_rborrowb

->WHEREa.stock_id=b.stock_idAND='楊麗'AND='王曉紅';邏輯視圖a和b進(jìn)行等值連接，a視圖的name為楊麗，并且b視圖的name為王曉紅（4）顯示楊麗或王曉紅借閱過(guò)的圖書(shū)的館藏編號(hào)stock_id、書(shū)名title和出版社publishermysql>SELECTstock_id,title,publisherFROMv_rborrow

->WHEREname='楊麗'ORname='王曉紅';（5）顯示楊麗借閱過(guò)但王曉紅沒(méi)借閱過(guò)的圖書(shū)的館藏編號(hào)stock_id、書(shū)名title和出版社publishermysql>SELECTstock_id,title,publisherFROMv_rborrow->WHEREname='楊麗'ANDtitleNOTIN(SELECTtitleFROMv_rborrowWHEREname='王曉紅');（6）顯示尚未還書(shū)的讀者編號(hào)reader_id、姓名name和書(shū)名title。mysql>SELECTreader_id,name,titleFROMv_rborrowWHEREreturntimeISNULL;任務(wù)6.3操作視圖操作視圖包括更新視圖、修改視圖定義和刪除視圖。視圖可以為基本表提供保護(hù)，用戶(hù)不直接對(duì)基本表進(jìn)行插入、刪除和修改操作，而是針對(duì)視圖來(lái)實(shí)現(xiàn)操作。因?yàn)橐晥D是不存儲(chǔ)數(shù)據(jù)的虛擬表，所以對(duì)視圖的更新就是對(duì)基本表的更新。更新視圖可以實(shí)現(xiàn)對(duì)基本表的插入、刪除、修改等操作。6.3.1更新視圖視圖包含下述結(jié)構(gòu)中的任意一種，則該視圖不可以更新。（1）聚合函數(shù)。（2）DISTINCT關(guān)鍵字。（3）GROUPBY子句。（4）ORDERBY子句。（5）HAVING子句。（6）UNION聯(lián)合查詢(xún)。（7）FROM子句中包含多個(gè)表。（8）引用了不可更新視圖。（9）WHERE子句中的子查詢(xún)引用了FROM子句中的表?！纠?-7】創(chuàng)建名為v_femalereader的視圖，包含讀者表reader中所有女性讀者的讀者編號(hào)reader_id、類(lèi)型編號(hào)type_id、姓名name、性別gender和所屬院系dept。mysql>CREATEORREPLACEVIEWv_femalereader

->AS

->SELECTreader_id,type_id,name,gender,deptFROMreaderWHEREgender='女'

->WITHCHECKOPTION;mysql>SELECT*FROMv_femalereader;1.插入數(shù)據(jù)使用INSERT語(yǔ)句向視圖插入數(shù)據(jù)時(shí)，如果在創(chuàng)建視圖時(shí)包含WITHCHECKOPTION子句，則在更新數(shù)據(jù)時(shí)檢查新數(shù)據(jù)是否符合視圖定義中WHERE子句的條件。如果視圖是基于多個(gè)基本表創(chuàng)建的，則不能向視圖插入數(shù)據(jù)?！纠?-8】向視圖v_femalereader中插入一條數(shù)據(jù)（1202110101，1，王明明，女，NULL）。mysql>INSERTINTOv_femalereader

->VALUES('1202110101',1,'王明明','女',NULL);查詢(xún)基本表reader。mysql>SELECT*FROMreaderWHEREreader_id='1202110101';mysql>INSERTINTOv_femalereader

->VALUES('1802110101',1,'王建國(guó)','男',NULL);ERROR1369(HY000):CHECKOPTIONfailed'librarydb.v_femalereader'2．修改數(shù)據(jù)使用UPDATE語(yǔ)句可以實(shí)現(xiàn)通過(guò)視圖修改基本表中的數(shù)據(jù)?！纠?-9】通過(guò)視圖v_femalereader將讀者王明明的所屬院系dept字段修改為人工智能學(xué)院。mysql>UPDATEv_femalereaderSETdept='人工智能學(xué)院'WHEREname='王明明';3．刪除數(shù)據(jù)使用DELETE語(yǔ)句可以實(shí)現(xiàn)通過(guò)視圖刪除基本表中的數(shù)據(jù)?！纠?-10】通過(guò)視圖v_femalereader，刪除姓名name為“王明明”的記錄。mysql>DELETEFROMv_femalereaderWHEREname='王明明';6.3.2修改視圖定義使用ALTERVIEW語(yǔ)句可以修改已有視圖的定義。ALTERVIEW視圖名[(名稱(chēng)列表)]ASSELECT語(yǔ)句[WITH[CASCADED|LOCAL]CHECKOPTION];【例6-11】修改視圖v_femalereader，使其只包含人工智能學(xué)院的女性讀者的讀者編號(hào)reader_id、類(lèi)型編號(hào)type_id、姓名name、性別gender和所屬院系dept。mysql>ALTERVIEWv_femalereader->AS->SELECTreader_id,type_id,name,gender,dept->FROMreaderWHEREgender='女'ANDdept='人工智能學(xué)院'->WITHCHECKOPTION;6.3.3刪除視圖可以刪除不需要的視圖，刪除視圖對(duì)該視圖關(guān)聯(lián)的基本表沒(méi)有任何影響。刪除視圖使用DROPVIEW語(yǔ)句，其語(yǔ)法格式如下。DROPVIEW[IFEXISTS]視圖名1[,視圖名2…];說(shuō)明如下。（1）如果包含IFEXISTS，當(dāng)視圖不存在時(shí)，就不會(huì)出現(xiàn)錯(cuò)誤提示信息。（2）可以一次性刪除多個(gè)視圖?！纠?-12】刪除視圖v_reader和v_price。批量刪除視圖v_reader和v_price。mysql>DROPVIEWv_reader,v_price;【項(xiàng)目案例】圖書(shū)館借還書(shū)管理數(shù)據(jù)庫(kù)的視圖1．創(chuàng)建和查詢(xún)視圖（1）創(chuàng)建名為v_bborrow的視圖，包含書(shū)名title、館藏編號(hào)stock_id、姓名name、存放位置location、圖書(shū)狀態(tài)status、借出時(shí)間borrowtime和歸還時(shí)間returntime。mysql>CREATEORREPLACEVIEWv_bborrow

->AS

->SELECTtitle,stock.stock_id,name,location,status,borrowtime,returntime

->FROMreaderJOINborrowONreader.reader_id=borrow.reader_idJOINstockONborrow.stock_id=stock.stock_idJOINbookONstock.book_id=book.book_id;查詢(xún)讀者李淑子的借閱情況。mysql>SELECTnameAS姓名,titleAS書(shū)名,borrowtimeAS借出時(shí)間,returntimeAS歸還時(shí)間

->FROMv_bborrowWHEREname='李淑子';（2）創(chuàng)建名為v_count的視圖，統(tǒng)計(jì)圖書(shū)表book中各個(gè)出版社的總書(shū)目數(shù)量和總館藏?cái)?shù)量。mysql>CREATEORREPLACEVIEWv_count(出版社,總書(shū)目數(shù)量,總館藏?cái)?shù)量)

->AS

->SELECTpublisher,COUNT(*),SUM(stocknum)FROMbookGROUPBYpublisher;查詢(xún)