《數(shù)據(jù)庫原理與應(yīng)用教程》第5版 課件 第5章 視圖和索引

數(shù)據(jù)庫原理與應(yīng)用教程(第5版)第5章視圖和索引5.1視圖5.2索引5.1視圖5.1視圖概念5.2定義視圖5.3通過視圖查詢數(shù)據(jù)5.4修改和刪除視圖5.5視圖的作用5.1視圖概念視圖：由基本表構(gòu)成的虛表（滿足用戶需求的表結(jié)構(gòu)）

5.2定義視圖CREATEVIEW<視圖名>[(視圖列名表)] AS查詢語句說明：查詢中通常不含ORDERBY和DISTINCT語句。缺省時(shí)視圖列名與查詢列名相同。查詢的源表可以是已定義的視圖。說明下列三種情況下不能省略視圖列名：某個(gè)目標(biāo)列是聚集函數(shù)或表達(dá)式列；多表連接查詢時(shí)，在查詢列表中有同名列；希望用新的更合適的列名。視圖的列名序列或者全部省略，或者全部指定。定義單源表視圖視圖取自一個(gè)基本表的部分行、列，視圖行列與基本表行列對(duì)應(yīng)，一般可看可改。示例例1．建立信息系學(xué)生的視圖。

CREATEVIEWIS_Student AS SELECTSno,Sname,Sage FROMStudentWHERESdept=’信息’

定義多源表視圖子查詢?cè)幢矶嘤谝粋€(gè)，一般可看不可改。示例例2．建立查詢信息系選了C001課程的學(xué)生的視圖，列出學(xué)號(hào)，姓名和年齡。CREATEVIEWV_IS_S1（Sno,Sname,Grade）

AS SELECTStudent.Sno,Sname,Sage FROMStudent,SC WHERESdept=‘信息系’ ANDStudent.Sno=SC.SnoANDSC.Cno=‘C001’在已有視圖上定義新視圖視圖的數(shù)據(jù)源可以來自其它的視圖。示例例3．利用例2建立的視圖，建立信息系選了C001課程且成績?cè)?0分以上的學(xué)生的視圖。

CREATEVIEWV_IS_S2（Sno,Sname,Grade）

AS SELECTStudent.Sno,Sname,Sage FROMV_IS_S1

WHEREGrade>=90示例例4．利用例1所建的視圖，建立查詢信息系“計(jì)算機(jī)文化學(xué)”考試成績大于等于80分的學(xué)生的姓名和成績的視圖。

CREATEVIEWV_IS_VBASSELECTSname,GradeFROMIS_StudentVJOINSCONV.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='計(jì)算機(jī)文化學(xué)'

ANDGrade>=80定義帶表達(dá)式的視圖定義基本表時(shí)，為減少數(shù)據(jù)冗余，表中只存放基本數(shù)據(jù)。由基本數(shù)據(jù)經(jīng)過各種計(jì)算派生出的數(shù)據(jù)一般不存儲(chǔ)。由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，因此，可以在在視圖中設(shè)置一些附加列來保存這些派生的數(shù)據(jù)。由于這些附加列在基本表中并不實(shí)際存在，因此稱這些列為虛擬列。稱包含虛擬列的視圖為帶表達(dá)式的視圖。示例例5．定義一個(gè)查詢學(xué)生學(xué)號(hào)、姓名和出生年份的視圖。

CREATEVIEWV_BirthYear(Sno,Sname,BirthYear)ASSELECTSno,Sname,2020-SageFROMStudent含分組統(tǒng)計(jì)信息的視圖子查詢中含GROUPBY子句，視圖行列由基本表行列得到，數(shù)據(jù)只看不可改。示例例6．定義一個(gè)查詢每個(gè)學(xué)生的學(xué)號(hào)及考試平均成績的視圖。

CREATEVIEWS_G(Sno,AvgGrade)ASSELECTSno,AVG(Grade)FROMSC GROUPBYSno5.3通過視圖查詢數(shù)據(jù)視圖定義好后，可以對(duì)其進(jìn)行查詢，通過視圖查詢數(shù)據(jù)同基本表一樣。示例例7．利用5.2節(jié)例1建立的視圖，查詢信息系年齡小于等于20歲的學(xué)生。

SELECTSno,Sname,SageFROMIS_StudentWHERESage<=20轉(zhuǎn)換成相關(guān)基本表的等價(jià)查詢SELECTSno,Sname,SageFROMStudentWHERESdept='信息系'

ANDSage<=20示例例8．查詢信息系選修了“C001”的學(xué)生學(xué)號(hào)、姓名和年齡。SELECTSno,Sname,SageFROMIS_StudentJOINSCONIS.Student.Sno=SC.SnoWHERECno='C01'示例例9.查詢信息系學(xué)生的學(xué)號(hào)、姓名、所選課程的課程名。

SELECTv.Sno,Sname,CnameFROMIS_StudentvJOINSCONv.Sno=SC.SnoJOINCourseCONC.Cno=SC.Cno

轉(zhuǎn)換成相關(guān)基本表的等價(jià)查詢SELECTS.Sno,Sname,CnameFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERESdept='信息系'示例例10.利用5.2節(jié)例6建立的視圖，查詢考試平均成績80分以上的學(xué)生的學(xué)號(hào)和平均成績。

SELECT*FROMS_GWHEREAvgGrade>80不能直接轉(zhuǎn)換為：SELECTSno,AVG(Grade)FROMSCWHEREAVG(Grade)>80GROUPBYSno而應(yīng)該轉(zhuǎn)換為：SELECTSno,AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>90修改和刪除視圖修改格式：ALTERVIEW視圖名

[(列名[,...n])]AS

查詢語句示例例11．修改例6定義的視圖，使其統(tǒng)計(jì)每個(gè)學(xué)生的考試平均成績和修課總門數(shù)。ALTERVIEWS_G(Sno,AvgGrade,Count_Cno)ASSELECTSno,AVG(Grade),Count(*)FROMSCGROUPBYSno刪除視圖格式：

DROPVIEW<視圖名>

例．刪除例1定義的IS_Student視圖。

DROPVIEWIS_Student5.5視圖的作用簡化數(shù)據(jù)查詢語句使用戶能從多角度看待同一數(shù)據(jù)提高了數(shù)據(jù)的安全性提供了一定程度的邏輯獨(dú)立性5.2索引5.2.1索引基本概念5.2.2索引分類5.2.3唯一索引5.2.4創(chuàng)建和刪除索引5.2.1基本概念索引與圖書中的目錄類似。在數(shù)據(jù)庫中，索引使對(duì)數(shù)據(jù)的查找不需要對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)。圖書的目錄注明了各部分內(nèi)容所對(duì)應(yīng)的頁碼，而數(shù)據(jù)庫中的索引是一個(gè)表中所包含的值的列表，其中注明了表中的各行數(shù)據(jù)所在的存儲(chǔ)位置?？梢詾楸碇械膯蝹€(gè)列建立索引，也可以為一組列建立索引。索引由索引項(xiàng)組成，索引項(xiàng)由來自表中每一行的一個(gè)或多個(gè)列（稱為索引關(guān)鍵字）組成。當(dāng)在多個(gè)列上建立索引時(shí)，系統(tǒng)按索引列出現(xiàn)的先后順序?qū)λ饕羞M(jìn)行排序。

索引及數(shù)據(jù)間對(duì)應(yīng)關(guān)系索引利弊合適的索引可以提高查詢效率。索引為查找所帶來的性能好處是有代價(jià)的：索引在數(shù)據(jù)庫中會(huì)占用一定的存儲(chǔ)空間。在對(duì)數(shù)據(jù)進(jìn)行插入、更改和刪除操作時(shí)，為使索引與數(shù)據(jù)保持一致，還需要對(duì)索引進(jìn)行相應(yīng)維護(hù)。對(duì)索引的維護(hù)是需要花費(fèi)時(shí)間。5.2.2索引的分類索引分為兩大類聚集索引（ClusteredIndex，也稱為聚簇索引）非聚集索引（Non-clusteredIndex，也稱為非聚簇索引）聚集索引對(duì)數(shù)據(jù)按聚集索引關(guān)鍵字值進(jìn)行物理的排序。下列情況可考慮創(chuàng)建聚集索引：包含大量非重復(fù)值的列。使用下列運(yùn)算符返回一個(gè)范圍值的查詢：

BETWEENAND、>、>=、<和<=。不返回大型結(jié)果集的查詢。經(jīng)常被用作連接的列，一般來說，這些列是外鍵列。經(jīng)常用在ORDERBY或GROUPBY子句中的列。非聚集索引非聚集索引與圖書后邊的術(shù)語表類似。數(shù)據(jù)存儲(chǔ)在一個(gè)地方，術(shù)語表存儲(chǔ)在另一個(gè)地方。而且數(shù)據(jù)并不按術(shù)語表的順序存放，但術(shù)語表中的每個(gè)詞在書中都有確切的位置。非聚集索引就類似于術(shù)語表，而數(shù)據(jù)就類似于一本書的內(nèi)容。下述情況可考慮建立非聚集索引包含大量非重復(fù)值的列。不返回大型結(jié)果集的查詢。經(jīng)常作為查詢條件使用的列。經(jīng)常作為連接和分組條件的列。唯一索引確保索引列不包含重復(fù)值。在組合唯一索引的情況下，可以確保索引列中每個(gè)值的組合都是唯一的。例如，如果在last_name、first_name和middle_initial列的組合上創(chuàng)建了唯一索引full_name，則該表中任何兩個(gè)人都不可以具有完全相同的名字。聚集索引和非聚集索引都可以是唯一的。說明如果必須要實(shí)施唯一性來確保數(shù)據(jù)的完整性，則應(yīng)在列上創(chuàng)建UNIQUE約束或PRIMARYKEY約束，而不要?jiǎng)?chuàng)建唯一索引。例如，如果限制身份證號(hào)碼（sid）列的取值不重復(fù)，則可在sid列上創(chuàng)建UNIQUE約束。實(shí)際上，當(dāng)在表上創(chuàng)建PRIMARYKEY約束或UNIQUE約束時(shí)，系統(tǒng)會(huì)自動(dòng)在這些列上創(chuàng)建唯一索引。5.2.3創(chuàng)建和刪除索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON<表名>(<列名>[<順序>[,<列名>[<順序>]…]]UNIQUE：創(chuàng)建唯一索引。CLUSTERED：創(chuàng)建聚集索引。NONCLUSTERED：創(chuàng)建非聚集索引。[ASC|DESC]：指定索引列的升序或降序排序方式。默認(rèn)值為ASC。默認(rèn)是創(chuàng)建非聚集索引。示例例1在Student表的Sname列上創(chuàng)建非聚集索引。CREATEINDEXSname_indONStudent(Sname)例2在Student表的Sid列上創(chuàng)建一個(gè)唯一聚集索引。CREATEUNIQUECLUSTEREDINDEXSid_indONStudent(Sid)示例（續(xù)