數(shù)據(jù)庫原理及應(yīng)用第二版第6章索引與視圖的課件

1、第6章 索引與視圖 6.1索引6.1.1基本概念索引用于快速訪問數(shù)據(jù)表中的特定數(shù)據(jù)，它是對數(shù)據(jù)表中一個(gè)列或多個(gè)列值進(jìn)行排序的存儲(chǔ)結(jié)構(gòu)。數(shù)據(jù)庫中的索引與書籍中的目錄類似。索引提供一個(gè)指針用以指向存在在表中指定列的數(shù)據(jù)值。然后根據(jù)指定排序次序排列這些 指針。索引不真實(shí)存儲(chǔ)表中的數(shù)據(jù)只是用來存儲(chǔ)指定源表中的記錄指針。當(dāng)對數(shù)據(jù)表進(jìn)行查詢時(shí)，才需要在表上創(chuàng)建索引，而且索引的使用是自動(dòng)的。另外索引是占用磁盤空間，并且影響添加、刪除和更新記錄的速度，如果需要頻繁的進(jìn)行數(shù)據(jù)更新，則需要考慮建立的索引數(shù)量。索引也是要進(jìn)行維護(hù)。6.1.2索引的存儲(chǔ)結(jié)構(gòu)及分類索引主要有3種類型，聚集索引、非聚集索引和惟一索引。1.

2、聚集索引在聚集索引中，數(shù)據(jù)表中記錄的物理順序與索引順序相同，即索引的順序決定了數(shù)據(jù)表中記錄行的存儲(chǔ)順序，在一個(gè)數(shù)據(jù)表中只能有一個(gè)聚集索引，并且在建立表時(shí)，如果給某列或某幾列指定了主鍵約束，則系統(tǒng)自動(dòng)建立了聚集索引。Create table student(學(xué)號(hào) char(8) primary key,)聚集索引主要用于以下幾種情況：有大量的非重復(fù)值的列，列值不重復(fù)的列建立聚集索引。2)在某列上經(jīng)常要使用between，,=,和=等運(yùn)算符進(jìn)行查詢時(shí)，該列應(yīng)建立聚集索引。3)被連續(xù)訪問的列4)查詢返回大型結(jié)果集的，應(yīng)建立聚集索引。5)經(jīng)常會(huì)被用于分組的列。Group by 列。注意：如果需頻繁更改

3、的列，不適合建立聚集索引。2.非聚集索引一個(gè)數(shù)據(jù)表只能建立一個(gè)聚集索引，而在表中的每列都可以建立自己的非聚集索引。非聚集索引不像聚集索引一個(gè)，聚集索引索引和表的數(shù)據(jù)是存儲(chǔ)在一起的，非聚集索引索引存儲(chǔ)在另一個(gè)地方，非聚集索引帶有指針指向數(shù)據(jù)的存儲(chǔ)位置。一個(gè)數(shù)據(jù)表除了聚集索引之外，其他建立的索引都是非聚集索引。建立非聚集索引一般如下情況：1）不返回大型結(jié)果集的查詢2)返回精確匹配的搜索條件中經(jīng)常使用的列，即經(jīng)常出現(xiàn)在where條件中的列。3)經(jīng)常需要連接和分組的列。3.唯一索引惟一索引也是一種非聚集索引，只是惟一索引也要求索引列的不重復(fù)。只是惟一索引一個(gè)數(shù)據(jù)表能建立多個(gè)。但聚集索引雖然也是索引列的

4、不重復(fù)，但只能建立一個(gè)。聚集索引叫唯一索引。它的功能與主鍵相似，也可以確保索引列不包含重復(fù)的值。如果在建立表時(shí)指定unique的惟一鍵約束，則系統(tǒng)將自動(dòng)建立一個(gè)惟一索引。6.1.3索引的作用1.索引能加快數(shù)據(jù)的查詢速度尤其是用在where子名中的查詢列，要建立索引。學(xué)生（學(xué)號(hào),姓名,性別,出生日期）學(xué)號(hào)，姓名2.索引能保證數(shù)據(jù)的實(shí)體完整性因?yàn)楸碇薪⒌木奂饕?，能保證列的惟一性，即不能出現(xiàn)重復(fù)值，而實(shí)體的完整性就是要求二維表不能出現(xiàn)重復(fù)行。對于索引的使用要注意的問題:1.索引不是建立的越多越好，經(jīng)常查詢的列建立索引。2.索引要建立聚集索引，選擇的都是表中的惟一值的列。且只能建立一個(gè)。3.索引會(huì)

5、影響添加、修改和刪除的速度，因?yàn)楫?dāng)添加數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)時(shí)，要對索引進(jìn)行維護(hù)。即重新建立索引。6.1.4建立索引1.索引的建立有三種方式：1)使用sql server 2005的管理工具來建立，是一種圖形用戶界面2)使用sql命令create index建立索引3)使用sql命令create table建立索引，但只能建立聚集索引和惟一索引。2 .索引的形式 在建立索引時(shí)需要指定以下索引特征 1.建立的索引是聚集還是非聚集，還是惟一索引。 2.索引是單項(xiàng)索引還是組合索引 單項(xiàng)索引：索引的列是1列，即一個(gè)字段。 組合索引：索引的列是多列，即先按第1列索引，第1列相同再按2列索引，以此類推。

6、 3.指定索引是排序類型，即升序還是降序。3.使用create index命令建立索引命令格式為：Create uniqueclusterednonclustered index 索引名 on 表名(列1 asc/desc，列2 asc/desc，)功能是：在指定數(shù)據(jù)表中的列上建立指定類型的索引。1)unique：表示建立的索引是惟一索引。不允許出現(xiàn)重復(fù)值。2)clustered：表示建立的索引是聚集索引，只能是一個(gè)。3)nonclustered：表示建立的索引是非聚集索引，即如果省略了以上三項(xiàng)，表示建立的也是非聚集索引，所以此選項(xiàng)可以省略。4)如果是組合索引，則索引列之間用,間隔。5)排序順

7、序有升序和降序，asc表示升序，可省，desc表示降序不可省。例1:在學(xué)生表上按姓名建立惟一索引，索引名為xm_index，降序。create unique index xm_index on 學(xué)生(姓名desc)例2:在選課表上按學(xué)號(hào)和課程號(hào)建立聚集索引，索引名為xhkh，學(xué)號(hào)升序，課程號(hào)降序。create clustered index xhkh on 選課(學(xué)號(hào),課程號(hào)desc)4.刪除索引Drop index 表名.索引名6.2 視圖6.2.1 試圖的概念 視圖是由從數(shù)據(jù)庫的基本表中選取出來的數(shù)據(jù)組成的邏輯窗口，是基本表的部分行和列數(shù)據(jù)的組合。視圖是一個(gè)虛表。在數(shù)據(jù)庫中只存放視圖的定義

8、，不存放視圖包含的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中。 視圖可以建立在基本表上，也可以建立在其他的視圖上，即可以在一個(gè)視圖之上再定義視圖。但對視圖數(shù)據(jù)的操作最終都會(huì)轉(zhuǎn)換為對基本表的操作。6.2.2 定義視圖定義視圖的一般格式為： CREATE VIEW （視圖列名表） AS 查詢語句 注意以下幾點(diǎn)：子查詢中通常不包含ORDER BY和DISTINCT子句。在定義視圖時(shí)要么指定全部視圖列，要么全部省略不寫。如果省略了視圖的屬性列名，則視圖的列名與子查詢列名相同。但在如下三種情況下必須明確指定組成視圖的所有列名：某個(gè)目標(biāo)列是計(jì)算函數(shù)或列表達(dá)式；多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段需要在視圖中

9、為某個(gè)列選用新的更合適的列名1、定義單源表視圖 單源表視圖指的是數(shù)據(jù)取自一個(gè)基本表的部分行、列。這樣定義的視圖可以進(jìn)行查詢和修改數(shù)據(jù)操作。例建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 信息系 DBMS執(zhí)行CREATE VIEW語句的結(jié)果只是保存視圖的定義，只有在對視圖執(zhí)行查詢時(shí)，才按視圖的定義從相應(yīng)基本表中查詢數(shù)據(jù)。 2、定義多源表視圖 多源表視圖指的是定義視圖的查詢的源表可以有多個(gè)，這樣定義的視圖一般只用于查詢，不用于修改數(shù)據(jù)。 例建立信息系選修了c01號(hào)課程

10、的學(xué)生的視圖。 CREATE VIEW V_IS_S1(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, Sage FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c013、在已有視圖上定義新視圖 在視圖上建立視圖表示視圖的數(shù)據(jù)源中有視圖。作為數(shù)據(jù)源的視圖必須是已經(jīng)建立好的。 例建立信息系選修了c01號(hào)課程且成績在90分以上的學(xué)生的視圖。 CREATE VIEW V_IS_S2 AS SELECT Sno, Sname, Grade FROM

11、V_IS_S1 WHERE Grade = 90 4、定義帶表達(dá)式的視圖 在定義基本表時(shí)，為減少數(shù)據(jù)庫中的冗余數(shù)據(jù)，表中只存放基本數(shù)據(jù)，由基本數(shù)據(jù)經(jīng)過各種計(jì)算派生出的數(shù)據(jù)一般是不存儲(chǔ)的。但由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，所以定義視圖時(shí)可以根據(jù)需要設(shè)置一些派生屬性列，在這些派生屬性列中保存經(jīng)過計(jì)算的值。 例定義一個(gè)反映學(xué)生出生年份的視圖。CREATE VIEW BT_S(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2002-Sage FROM Student5、含分組統(tǒng)計(jì)信息的視圖 含分組統(tǒng)計(jì)信息的視圖是指視圖的子查詢中含有GROUP BY子句，這樣的視圖只能

12、用于查詢，不能用于修改數(shù)據(jù)。 例定義一個(gè)存放每個(gè)學(xué)生的學(xué)號(hào)及平均成績的視圖。 CREATE VIEW S_G(Sno, AverageGrade) AS SELECT Sno, AVG(Grade) FROM SCGROUP BY Sno 注意：如果查詢的選擇列表包含表達(dá)式或統(tǒng)計(jì)函數(shù)，而且在查詢中也沒有為這樣的列指定列標(biāo)題，則在定義視圖的語句中必須要指定視圖屬性列的名字。 6.2.3通過視圖查詢數(shù)據(jù)例利用5.2節(jié)建立的視圖，查詢信息系年齡小于20歲的學(xué)生。 SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage 20 數(shù)據(jù)庫管理系統(tǒng)在對視圖進(jìn)行查詢

13、時(shí)，首先檢查要查詢的視圖是否存在，如果存在，則從數(shù)據(jù)字典中提取視圖的定義，把視圖的定義語句與對視圖的查詢語句結(jié)合起來，轉(zhuǎn)換成等價(jià)的對基本表的查詢，然后執(zhí)行轉(zhuǎn)換后的查詢。例上例查詢最終轉(zhuǎn)換成的實(shí)際查詢?yōu)椋?SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 信息系 AND Sage 201. 修改視圖的SQL語句的格式為：ALTER VIEW （列名，n） as 查詢語句修改視圖與定義視圖語句基本一樣，只是將GREATE VIEW 改成ALTER VIEW6.2.4 修改與刪除視圖2. 刪除視圖的SQL語句的格式為： DROP VIEW 例刪除IS_Student視圖。 DROP VIEW IS_Student 刪除視圖時(shí)需要注意的是，如果被刪除的視圖是作為其他視圖的數(shù)