MySQL數(shù)據(jù)庫(kù)教程課件

第6章視圖視圖是數(shù)據(jù)庫(kù)中主要的對(duì)象之一，視圖對(duì)應(yīng)于數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部體系結(jié)構(gòu)三級(jí)模式中的外模式，反映的是一張或多張基本表中的局部數(shù)據(jù)，視圖是一種虛擬表。【學(xué)習(xí)目標(biāo)】理解視圖的概念。掌握創(chuàng)建視圖的方法。掌握更新視圖的操作。6.1視圖概述視圖是從一張或多張基本表中導(dǎo)出的虛擬表。視圖與基本表不同，視圖中的內(nèi)容由SELECT語(yǔ)句定義，在數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，不存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)存儲(chǔ)在基本表中，直到用戶使用視圖時(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ù)雜性。用戶不必了解數(shù)據(jù)庫(kù)中詳細(xì)的表結(jié)構(gòu)和復(fù)雜的表間聯(lián)系。用戶可以像使用基本表一樣使用視圖。（2）方便用戶查詢(xún)數(shù)據(jù)。當(dāng)用戶需要查詢(xún)的數(shù)據(jù)來(lái)自于不同表時(shí)，通過(guò)視圖可以將它們集中在一起方便用戶進(jìn)行查詢(xún)。（3）提高安全性。用戶使用視圖只能查詢(xún)和修改視圖中的數(shù)據(jù)，同時(shí)保證了數(shù)據(jù)的邏輯獨(dú)立性。（4）便于數(shù)據(jù)共享。各個(gè)用戶不必重復(fù)定義和存儲(chǔ)自己所需的數(shù)據(jù)，可以共享數(shù)據(jù)庫(kù)中的數(shù)據(jù)，因此同樣的數(shù)據(jù)只需存儲(chǔ)一次。6.2創(chuàng)建和查詢(xún)視圖6.2.1創(chuàng)建視圖創(chuàng)建視圖使用CREATEVIEW語(yǔ)句。語(yǔ)法格式：CREATE[ORREPLACE]VIEW視圖名[(字段名列表)]ASSELECT語(yǔ)句[WITH[CASCADED

LOCAL]CHECKOPTION];參數(shù)說(shuō)明：（1）ORREPLACE：為可選項(xiàng)，在創(chuàng)建視圖時(shí)，如果存在同名的視圖可以用新建的視圖替換已有的視圖。（2）視圖名：指定視圖的名稱(chēng)。（3）字段名列表：為可選項(xiàng)，需要為視圖的各個(gè)字段列定義明確的名稱(chēng)時(shí)，可以使用該選項(xiàng)，各字段名之間用逗號(hào)分隔。字段名列表中的名稱(chēng)數(shù)目必須與SELECT語(yǔ)句中的字段數(shù)目一致。省略時(shí)，視圖的字段名與SELECT語(yǔ)句中的字段相同。（4）SELECT語(yǔ)句：用來(lái)創(chuàng)建視圖的SELECT語(yǔ)句，可以在SELECT語(yǔ)句中查詢(xún)多個(gè)表或視圖。（5）WITHCHECKOPTION：為可選項(xiàng)，指出在視圖上進(jìn)行的修改都要符合SELECT語(yǔ)句指定的WHERE限制條件，這樣在數(shù)據(jù)修改后，仍然可以通過(guò)視圖看到修改的數(shù)據(jù)。當(dāng)一個(gè)視圖是基于另一個(gè)視圖定義時(shí)，可以選擇LOCAL或CASCADED參數(shù)，來(lái)確定檢查測(cè)試的范圍。LOCAL表示只對(duì)定義的視圖進(jìn)行檢查；CASCADED表示對(duì)所有視圖進(jìn)行檢查，是默認(rèn)值。使用視圖需要注意的規(guī)則：（1）新創(chuàng)建的視圖默認(rèn)存儲(chǔ)在當(dāng)前數(shù)據(jù)庫(kù)中。如果需要在指定的數(shù)據(jù)庫(kù)中創(chuàng)建視圖，則使用“數(shù)據(jù)庫(kù)名.視圖名”形式。（2）視圖名必須是唯一的，且不能與表名相同。（3）視圖定義中引用的基本表或視圖必須是存在的，并且定義視圖的用戶對(duì)所涉及的基礎(chǔ)表或視圖具有查詢(xún)的權(quán)限。（4）不能引用系統(tǒng)變量或用戶變量，不能引用預(yù)處理語(yǔ)句參數(shù)。（5）不能包含子查詢(xún)。（6）不能在視圖上建立任何索引?！纠?-1】創(chuàng)建名為v_student的視圖，包含學(xué)生表student中的學(xué)號(hào)sno、姓名sname、性別sex、出生日期birthdate、政治面貌party、班級(jí)classno、入學(xué)總分enterscore和院系表department中的院系名稱(chēng)deptname。mysql>CREATEORREPLACEVIEWv_student->AS->SELECTsno,sname,sex,birthdate,party,classno,deptname,enterscore->FROMdepartmentJOINstudentONdepartment.deptno=student.deptno;【例6-2】創(chuàng)建名為v_avgrade的視圖，包含課程表course中的cno、cname和ROUND(AVG(grade),1)，在視圖中的字段名分別為課程編號(hào)、課程名稱(chēng)和課程平均成績(jī)。mysql>CREATEORREPLACEVIEWv_avgrade(課程編號(hào),課程名稱(chēng),課程平均成績(jī))->AS->SELECTo,cname,ROUND(AVG(grade),1)->FROMcourseJOINscoreONo=o->GROUPBYo,cname;6.2.2查詢(xún)視圖定義視圖后，對(duì)視圖的查詢(xún)?nèi)缤颈淼牟樵?xún)操作，不同的是FROM子句中是視圖名?！纠?-3】查詢(xún)已創(chuàng)建的v_student視圖，顯示所有字段。已經(jīng)創(chuàng)建的v_student視圖中包含了較為復(fù)雜的表連接，用戶直接查詢(xún)?cè)撘晥D，可以不必進(jìn)行表連接，簡(jiǎn)化了查詢(xún)操作。mysql>SELECT*FROMv_student;+--------------+--------+------+------------+-------+----------+-----------------------+------------+|sno|sname|sex|birthdate|party|classno|deptname|enterscore|+--------------+--------+------+------------+-------+----------+-----------------------+------------+|120211010103|宋洪博|男|2003-05-15|黨員|英語(yǔ)2101|外國(guó)語(yǔ)學(xué)院|698||120211010105|劉向志|男|2002-10-08|團(tuán)員|英語(yǔ)2101|外國(guó)語(yǔ)學(xué)院|625||120211010230|李媛媛|女|2003-09-02|團(tuán)員|英語(yǔ)2102|外國(guó)語(yǔ)學(xué)院|596||120211030110|王琦|男|2003-01-23|團(tuán)員|機(jī)械2101|能源動(dòng)力與機(jī)械工程學(xué)院|600||120211030409|張虎|男|2003-07-18|群眾|機(jī)械2104|能源動(dòng)力與機(jī)械工程學(xué)院|650||120211040101|王曉紅|女|2002-09-02|團(tuán)員|電氣2101|電氣與電子工程學(xué)院|630||120211040108|李明|男|2002-12-27|黨員|電氣2101|電氣與電子工程學(xué)院|650||120211041102|李華|女|2003-01-01|團(tuán)員|電氣2111|電氣與電子工程學(xué)院|648||120211041129|侯明斌|男|2002-12-03|黨員|電氣2111|電氣與電子工程學(xué)院|617||120211050101|張函|女|2003-03-07|團(tuán)員|財(cái)務(wù)2101|經(jīng)濟(jì)與管理學(xué)院|663||120211050102|唐明卿|女|2002-10-15|群眾|財(cái)務(wù)2101|經(jīng)濟(jì)與管理學(xué)院|548||120211060104|王剛|男|2004-01-12|團(tuán)員|計(jì)算2101|控制與計(jì)算機(jī)工程學(xué)院|678||120211060206|趙壯|男|2003-03-13|團(tuán)員|計(jì)算2102|控制與計(jì)算機(jī)工程學(xué)院|605||120211070101|李淑子|女|2003-06-14|黨員|物理2101|數(shù)理學(xué)院|589||120211070106|劉麗|女|2002-11-17|團(tuán)員|物理2101|數(shù)理學(xué)院|620|+--------------+--------+------+------------+-------+----------+-----------------------+------------+【例6-4】查詢(xún)v_student視圖，顯示學(xué)號(hào)sno、姓名sname、院系名稱(chēng)deptname和入學(xué)總分enterscore。查詢(xún)視圖v_student。mysql>SELECTsno,sname,deptname,enterscore->FROMv_student;+--------------+--------+------------------------+------------+|sno|sname|deptname|enterscore|+--------------+--------+------------------------+------------+|120211010103|宋洪博|外國(guó)語(yǔ)學(xué)院|698||120211010105|劉向志|外國(guó)語(yǔ)學(xué)院|625||120211010230|李媛媛|外國(guó)語(yǔ)學(xué)院|596||120211030110|王琦|能源動(dòng)力與機(jī)械工程學(xué)院|600||120211030409|張虎|能源動(dòng)力與機(jī)械工程學(xué)院|650||120211040101|王曉紅|電氣與電子工程學(xué)院|630||120211040108|李明|電氣與電子工程學(xué)院|650||120211041102|李華|電氣與電子工程學(xué)院|648||120211041129|侯明斌|電氣與電子工程學(xué)院|617||120211050101|張函|經(jīng)濟(jì)與管理學(xué)院|663||120211050102|唐明卿|經(jīng)濟(jì)與管理學(xué)院|548||120211060104|王剛|控制與計(jì)算機(jī)工程學(xué)院|678||120211060206|趙壯|控制與計(jì)算機(jī)工程學(xué)院|605||120211070101|李淑子|數(shù)理學(xué)院|589||120211070106|劉麗|數(shù)理學(xué)院|620|+--------------+--------+------------------------+------------+【例6-5】查詢(xún)v_student視圖，篩選出“外國(guó)語(yǔ)學(xué)院”的學(xué)號(hào)sno、姓名sname、院系名稱(chēng)deptname和入學(xué)總分enterscore。mysql>SELECTsno,sname,deptname,enterscore->FROMv_studentWHEREdeptname="外國(guó)語(yǔ)學(xué)院";+--------------+--------+------------+------------+|sno|sname|deptname|enterscore|+--------------+--------+------------+------------+|120211010103|宋洪博|外國(guó)語(yǔ)學(xué)院|698||120211010105|劉向志|外國(guó)語(yǔ)學(xué)院|625||120211010230|李媛媛|外國(guó)語(yǔ)學(xué)院|596|+--------------+--------+------------+------------+【例6-6】查詢(xún)v_student視圖，篩選出“外國(guó)語(yǔ)學(xué)院”的學(xué)生記錄，并將字段名顯示為中文的別名。如果希望顯示出字段的明確含義，可以使用AS子句為字段指定別名。mysql>SELECTsnoAS學(xué)號(hào),snameAS姓名,deptnameAS院系名稱(chēng),enterscoreAS入學(xué)總分->FROMv_studentWHEREdeptname="外國(guó)語(yǔ)學(xué)院";+--------------+--------+------------+----------+|學(xué)號(hào)|姓名|院系名稱(chēng)|入學(xué)總分|+--------------+--------+------------+----------+|120211010103|宋洪博|外國(guó)語(yǔ)學(xué)院|698||120211010105|劉向志|外國(guó)語(yǔ)學(xué)院|625||120211010230|李媛媛|外國(guó)語(yǔ)學(xué)院|596|+--------------+--------+------------+----------+如果在定義視圖時(shí)就是用了AS子句，則查詢(xún)視圖時(shí)必須使用AS子句中的別名。例如，定義視圖v_student1。mysql>CREATEORREPLACEVIEWv_student1->AS->SELECTsnoAS學(xué)號(hào),snameAS姓名,sexAS性別,birthdateAS出生日期,partyAS政治面貌,classnoAS班級(jí),deptnameAS院系名稱(chēng),enterscoreAS入學(xué)總分->FROMdepartmentJOINstudentONdepartment.deptno=student.deptnomysql>SELECT學(xué)號(hào),姓名,院系名稱(chēng),入學(xué)總分FROMv_student1;+--------------+--------+------------------------+----------+|學(xué)號(hào)|姓名|院系名稱(chēng)|入學(xué)總分|+--------------+--------+------------------------+----------+|120211010103|宋洪博|外國(guó)語(yǔ)學(xué)院|698||120211010105|劉向志|外國(guó)語(yǔ)學(xué)院|625||120211010230|李媛媛|外國(guó)語(yǔ)學(xué)院|596||120211030110|王琦|能源動(dòng)力與機(jī)械工程學(xué)院|600||120211030409|張虎|能源動(dòng)力與機(jī)械工程學(xué)院|650||120211040101|王曉紅|電氣與電子工程學(xué)院|630||120211040108|李明|電氣與電子工程學(xué)院|650||120211041102|李華|電氣與電子工程學(xué)院|648||120211041129|侯明斌|電氣與電子工程學(xué)院|617||120211050101|張函|經(jīng)濟(jì)與管理學(xué)院|663||120211050102|唐明卿|經(jīng)濟(jì)與管理學(xué)院|548||120211060104|王剛|控制與計(jì)算機(jī)工程學(xué)院|678||120211060206|趙壯|控制與計(jì)算機(jī)工程學(xué)院|605||120211070101|李淑子|數(shù)理學(xué)院|589||120211070106|劉麗|數(shù)理學(xué)院|620|+--------------+--------+------------------------+----------+【例6-7】查詢(xún)v_avgrade視圖，顯示所有記錄。mysql>SELECT*FROMv_avgrade;+----------+------------------+--------------+|課程編號(hào)|課程名稱(chēng)|課程平均成績(jī)|+----------+------------------+--------------+|10101400|學(xué)術(shù)英語(yǔ)|76.0||10500131|證券投資學(xué)|76.8||10600611|數(shù)據(jù)庫(kù)應(yīng)用|88.0||10700140|高等數(shù)學(xué)|81.5||10700053|大學(xué)物理|68.7||10300710|現(xiàn)代控制理論|76.0||10400350|模擬電子技術(shù)基礎(chǔ)|88.0||10600200|高級(jí)語(yǔ)言程序設(shè)計(jì)|65.0||10101410|通用英語(yǔ)|80.0|+----------+------------------+--------------+【例6-8】查詢(xún)v_avgrade視圖，顯示課程平均成績(jī)?cè)?0分以上的記錄。查詢(xún)視圖v_avgrade。mysql>SELECT*FROMv_avgrade->WHERE課程平均成績(jī)>=80;+----------+------------------+--------------+|課程編號(hào)|課程名稱(chēng)|課程平均成績(jī)|+----------+------------------+--------------+|10600611|數(shù)據(jù)庫(kù)應(yīng)用|88.0||10700140|高等數(shù)學(xué)|81.5||10400350|模擬電子技術(shù)基礎(chǔ)|88.0||10101410|通用英語(yǔ)|80.0|+----------+------------------+--------------+【例6-9】查詢(xún)v_avgrade視圖，顯示課程平均成績(jī)?cè)?0分以上的課程名稱(chēng)和課程平均成績(jī)。mysql>SELECT課程名稱(chēng),課程平均成績(jī)FROMv_avgrade->WHERE課程平均成績(jī)>=80;+------------------+--------------+|課程名稱(chēng)|課程平均成績(jī)|+------------------+--------------+|數(shù)據(jù)庫(kù)應(yīng)用|88.0||高等數(shù)學(xué)|81.5||模擬電子技術(shù)基礎(chǔ)|88.0||通用英語(yǔ)|80.0|+------------------+--------------+6.3操作視圖操作視圖包括：更新視圖修改視圖定義刪除視圖6.3.1更新視圖視圖可以為基本表提供保護(hù)，用戶不直接對(duì)基本表進(jìn)行插入、刪除和修改操作，而是針對(duì)視圖來(lái)實(shí)現(xiàn)操作。因?yàn)橐晥D是不存儲(chǔ)數(shù)據(jù)的虛擬表，所以對(duì)視圖的更新就是對(duì)基本表的更新。更新視圖可以實(shí)現(xiàn)對(duì)基本表的插入、刪除、修改等操作。視圖包含下述結(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）SELECT語(yǔ)句中引用了不可更新視圖。（9）WHERE子句中的子查詢(xún)引用了FROM子句中的表?！纠?-10】創(chuàng)建名為v_femalestu的視圖，包含學(xué)生表student中所有女生的sno、sname、sex、enterscore和awards。創(chuàng)建視圖v_femalestu。mysql>CREATEORREPLACEVIEWv_femalestu->AS->SELECTsno,sname,sex,enterscore,awards->FROMstudentWHEREsex="女"->WITHCHECKOPTION;查詢(xún)視圖v_femalestu。mysql>SELECT*FROMv_femalestu;+--------------+--------+------+------------+----------------+|sno|sname|sex|enterscore|awards|+--------------+--------+------+------------+----------------+|120211010230|李媛媛|女|596|||120211040101|王曉紅|女|630|||120211041102|李華|女|648|||120211050101|張函|女|663|||120211050102|唐明卿|女|548|國(guó)家二級(jí)運(yùn)動(dòng)員||120211070101|李淑子|女|589|||120211070106|劉麗|女|620||+--------------+--------+------+------------+----------------+1.插入數(shù)據(jù)使用INSERT語(yǔ)句向視圖插入數(shù)據(jù)時(shí)，如果在創(chuàng)建視圖時(shí)包含WITHCHECKOPTION子句，則在更新數(shù)據(jù)時(shí)檢查新數(shù)據(jù)是否符合視圖定義中WHERE子句的條件。如果視圖是基于多個(gè)基本表創(chuàng)建的，則不能向視圖插入數(shù)據(jù)?！纠?-11】向視圖v_femalestu中插入一條記錄（120211010130，王明明，女，675，NULL）。mysql>INSERTINTOv_femalestu->VALUES("120211010130","王明明","女",675,NULL);查詢(xún)基本表學(xué)生表student。mysql>SELECTsno,sname,sex,enterscore,awards

->FROMstudentWHEREsex="女"ANDsno="120211010130";+--------------+--------+------+------------+--------+|sno|sname|sex|enterscore|awards|+--------------+--------+------+------------+--------+|120211010130|王明明|女|675|NULL|+--------------+--------+------+------------+--------+2.修改數(shù)據(jù)使用UPDATE語(yǔ)句可以實(shí)現(xiàn)通過(guò)視圖修改基本表中的數(shù)據(jù)?！纠?-12】將視圖v_femalestu中的獎(jiǎng)懲情況awards字段中的空白或NULL值更改為“無(wú)”。對(duì)視圖v_femalestu進(jìn)行記錄的修改。mysql>UPDATEv_femalestuSETawards="無(wú)"->WHEREawards=""ORawardsISNULL;查詢(xún)基本表學(xué)生表student。mysql>SELECTsno,sname,sex,enterscore,awards->FROMstudent;+--------------+--------+------+------------+--------------------------+|sno|sname|sex|enterscore|awards|+--------------+--------+------+------------+--------------------------+|120211010103|宋洪博|男|698|三好學(xué)生，一等獎(jiǎng)學(xué)金||120211010105|劉向志|男|625|||120211010119|王建國(guó)|男|670|NULL||120211010130|王明明|女|675|無(wú)||120211010230|李媛媛|女|596|無(wú)||120211030110|王琦|男|600|優(yōu)秀學(xué)生干部，二等獎(jiǎng)學(xué)金||120211030409|張虎|男|650|北京市數(shù)學(xué)建模一等獎(jiǎng)||120211040101|王曉紅|女|630|無(wú)||120211040108|李明|男|650|||120211041102|李華|女|648|無(wú)||120211041129|侯明斌|男|617|||120211050101|張函|女|663|無(wú)||120211050102|唐明卿|女|548|國(guó)家二級(jí)運(yùn)動(dòng)員||120211060104|王剛|男|678|||120211060206|趙壯|男|605|||120211070101|李淑子|女|589|無(wú)||120211070106|劉麗|女|620|無(wú)|+--------------+--------+------+------------+--------------------------+3.刪除數(shù)據(jù)使用DELETE語(yǔ)句實(shí)現(xiàn)通過(guò)視圖刪除基本表中的數(shù)據(jù)?！纠?-13】刪除視圖v_femalestu中姓名sname為“王明明”的記錄。對(duì)視圖v_femalestu進(jìn)行記錄的刪除。mysql>DELETEFROMv_femalestuWHEREsname="王明明";查詢(xún)學(xué)生表student。mysql>SELECTsno,sname,sex,enterscore,awards->FROMstudentWHEREsex="女"ANDsname="王明明";Emptyset(0.00sec)6.3.2修改視圖定義使用ALTERVIEW語(yǔ)句可以修改已有視圖的定義語(yǔ)法格式：ALTERVIEW視圖名[(字段名列表)]ASSELECT語(yǔ)句[WITH[CASCADED

LOCAL]CHECKOPTION];【例6-14】修改視圖v_femalestu，使其只包含學(xué)生表student中所有院系代碼為“101”的女生的學(xué)號(hào)sno、姓名sname、性別sex和入學(xué)總分enterscore。修改視圖v_femalestu。mysql>ALTERVIEWv_femalestu->AS->SELECTsno,sname,sex,enterscore->FROMstudentWHEREsex="女"ANDdeptno=”101”->WITHCHECKOPTION;6.3.3刪除視圖可以刪除不需要的視圖，刪除視圖對(duì)該視圖關(guān)聯(lián)的基本表沒(méi)有任何影響。刪除視圖使用DROPVIEW語(yǔ)句語(yǔ)法格式：DROPVIEW[IFEXISTS]視圖名1[,視圖名2

];說(shuō)明：（1）如果包含IFEXIST，當(dāng)視圖不存在時(shí)，就不會(huì)出現(xiàn)錯(cuò)誤信息。（2）使用DROPVIEW一次可以刪除多個(gè)視圖?！纠?-15】刪除視圖v_student和v_avgrade。批量刪除視圖v_student和v_avgrade。mysql>DROPVIEWv_student,v_avgrade;6.4課堂案例：學(xué)生成績(jī)管理的數(shù)據(jù)視圖1．創(chuàng)建和查看視圖（1）創(chuàng)建名為v_grade的視圖，顯示學(xué)生的學(xué)號(hào)sno、姓名sname、課程名稱(chēng)cname和成績(jī)grade。創(chuàng)建的視圖涉及到3張表：學(xué)生表student、課程表course和選修成績(jī)表score。創(chuàng)建視圖v_grade。mysql>CREATEORREPLACEVIEWv_grade->AS->SELECTstudent.sno,sname,cname,grade->FROMstudentJOINscoreONstudent.sno=score.snoJOINcourseONo=o;+------+------------------+------+|姓名|課程名稱(chēng)|成績(jī)|+------+------------------+------+|李華|現(xiàn)代控制理論|76||李華|模擬電子技術(shù)基礎(chǔ)|91||李華|數(shù)據(jù)庫(kù)應(yīng)用|90||李華|高等數(shù)學(xué)|70|+------+------------------+------+mysql>SELECTsnameAS姓名,cnameAS課程名稱(chēng),gradeAS成績(jī)->FROMv_gradeWHEREsname="李華";（2）創(chuàng)建名為v_count的視圖，查詢(xún)每門(mén)課程的選課人數(shù)。創(chuàng)建視圖v_count的語(yǔ)句。mysql>CREATEORREPLACEVIEWv_count(課程名稱(chēng),選課人數(shù))->AS->SELECTcname,COUNT(sno)->FROMcourseJOINscoreONo=o->GROUPBYcname;查詢(xún)v_count視圖。mysql>SELECT課程名稱(chēng),選課人數(shù)FROMv_count;+------------------+----------+|課程名稱(chēng)|選課人數(shù)|+------------------+----------+|學(xué)術(shù)英語(yǔ)|4|