第6章 索引和視圖,6.1 索引 6.2 視圖,6.1 索引,6.1.1 索引基本概念 6.1.2 索引的存儲結構及分類 6.1.3 創(chuàng)建和刪除索引,2020年7月29日3時6分,2,6.1 索引基本概念,索引與書籍中的目錄類似。 索引使對數(shù)據的查找不需要對整個表進行掃描，就可以在其中找到所需數(shù)據。 可以為表中的單個列建立索引，也可以為一組列（索引項）建立索引。 索引一般采用B樹結構。,2020年7月29日3時6分,3,索引及數(shù)據間的對應關系示意圖,索引的組織方式,索引項按數(shù)據頁(一塊固定大小的連續(xù)存儲空間)存儲。 表中的全部索引連在一起。,6.1.2 索引的存儲結構及分類,聚集索引（Clustered Index，也稱為聚簇索引） 將數(shù)據按照索引項的順序進行物理排序。 非聚集索引（Non-clustered Index，也稱為非聚簇索引）。 不對數(shù)據進行物理排序。,兩類索引相同點,聚集索引和非聚集索引一般都使用B-樹結構來存儲索引項， 都包含數(shù)據頁和索引頁， 索引頁用來存放索引項和指向下一層的指針， 數(shù)據頁用來存放數(shù)據。,B-樹結構,聚集索引,聚集索引的B-樹按自下而上建立，最下層的葉級節(jié)點存放數(shù)據，它同時也是數(shù)據頁。 多個數(shù)據頁生成一個中間層節(jié)點的索引頁，然后再由數(shù)個中間層的節(jié)點的索引頁合成更上層的索引頁， 如此上推，直到生成頂層的根節(jié)點的索引頁。,9,建有聚集索引的表的存儲結構示意圖,數(shù)據示例,聚集索引,非聚集索引,聚集索引示例,12,數(shù)據示例,查找過程,當在建有聚集索引的列上查找數(shù)據時 首先從聚集索引樹的入口（根節(jié)點）開始逐層向下查找， 直到達到B-樹索引的葉級，也就是達到了要找的數(shù)據所在的數(shù)據頁， 最后只在這個數(shù)據頁中查找所需數(shù)據,查找示例,SELECT * FROM employee WHERE eno=E08,說明,在聚集索引的葉節(jié)點中，數(shù)據按聚集索引項的值進行物理排序。 因此，聚集索引很類似于電話號碼簿。 一個表只能包含一個聚集索引。 但一個索引可以由多個列（組合索引）組成。,下列情況可考慮創(chuàng)建聚集索,包含大量非重復值的列。 使用下列運算符返回一個范圍值的查詢：BETWEEN AND、=、< 和 <=。 被連續(xù)訪問的列。 不返回大型結果集的查詢。 經常被用作連接的列。 ORDER BY或GROUP BY子句中指定的列。,下列情況不適于建立聚集索引,頻繁更改的列。 字節(jié)長的列。因為聚集索引的索引項的值將被所有非聚集索引作為查找關鍵字使用，并被存儲在每個非聚集索引的B樹的葉級索引項中。,非聚集索引,非聚集索引與圖書后邊的術語表類似。數(shù)據存儲在一個地方，術語表存儲在另一個地方。而且數(shù)據并不按術語表的順序存放，但術語表中的每個詞在書中都有確切的位置。 非聚集索引就類似于術語表，而數(shù)據就類似于一本書的內容。,非聚集索引的存儲示意圖,非聚集索引與聚集索引的差別,數(shù)據不按非聚集索引關鍵字值的順序排序和存儲。 葉級節(jié)點不是存放數(shù)據的數(shù)據頁。 非聚集索引B樹的葉級節(jié)點是索引行。每個索引行包含非聚集索引關鍵字值以及一個或多個行定位器，這些行定位器指向該關鍵字值對應的數(shù)據行（如果索引不唯一，則可能是多行）,在eno列上建有非聚集索引的情形,數(shù)據示例,下述情況可考慮建立非聚集索引,包含大量非重復值的列。 不返回大型結果集的查詢。 經常作為查詢條件使用的列。 經常作為連接和分組條件的列。,唯一索引,確保索引列不包含重復值。 在組合唯一索引的情況下，可以確保索引列中每個值的組合都是唯一的。 例如，如果在last_name、first_name和middle_initial列的組合上創(chuàng)建了唯一索引full_name，則該表中任何兩個人都不可以具有完全相同的名字。 聚集索引和非聚集索引都可以是唯一的。,說明,如果必須要實施唯一性來確保數(shù)據的完整性，則應在列上創(chuàng)建UNIQUE約束或PRIMARY KEY約束，而不要創(chuàng)建唯一索引。 例如，如果限制身份證號碼（sid）列的取值不重復，則可在sid列上創(chuàng)建UNIQUE約束。 實際上，當在表上創(chuàng)建PRIMARY KEY約束或UNIQUE約束時，系統(tǒng)會自動在這些列上創(chuàng)建唯一索引。,創(chuàng)建索引,CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名(列名 ,.n) UNIQUE：創(chuàng)建唯一索引。 CLUSTERED：創(chuàng)建聚集索引。 NONCLUSTERED：創(chuàng)建非聚集索引。 如果沒有指定索引類型，則默認是創(chuàng)建非聚集索引。,示例,例1 為Student表的Sname列創(chuàng)建非聚集索引。 CREATE INDEX Sname_ind ON Stuent (Sname) 例2 為Student表的Sid列創(chuàng)建唯一聚集索引。 CREATE UNIQUE CLUSTERED INDEX Sid_ind ON Stuent (Sid ),示例,例3 為Employee表的FirstName和LastName列創(chuàng)建一個聚集索引。 CREATE CLUSTERED INDEX EName_ind ON Employee(FirstName,LastName),刪除索引,刪除索引的基本語法格式為： DROP INDEX 例4 刪除Student表的Sname_ind索引 DROP INDEX Sname_ind,6.2 視圖,6.2.1 概念概念 6.2.2 定義視圖 6.2.3 通過視圖查詢數(shù)據 6.2.4 修改和刪除視圖 6.2.5 視圖的作用,6.2.1 基本概念,視圖是由從數(shù)據庫的基本表中選取出來的數(shù)據組成的邏輯窗口， 是基本表的部分行和列數(shù)據的組合。 視圖是一個虛表。 數(shù)據庫中只存儲視圖的定義，而不存儲視圖所包含的數(shù)據。,視圖與基本表關系,6.2.2 定義視圖,CREATE VIEW (視圖列名表) AS 查詢語句 查詢語句中通常不包含ORDER BY和DISTINCT子句。 在定義視圖時要么指定視圖的全部列名，要么全部省略不寫。,說明,如果省略了視圖的“列名表”部分，則視圖的列名與查詢語句結果顯示的列名相同。 如下三種情況下必須明確指定視圖列名： 某個目標列是函數(shù)或表達式，并且沒有為這樣的列起別名。 多表連接時選出了幾個同名列作為視圖的字段。 需要在視圖中為列選用新的更合適的列名。,定義單源表視圖,視圖取自一個基本表的部分行、列，視圖行列與基本表行列對應， 一般可看可改。,示例,CREATE VIEW IS_Student AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系,例1建立信息系學生的的學號、姓名、性別和年齡視圖。,定義多源表視圖,子查詢源表多于一個， 一般可看不可改。,36,示例,CREATE VIEW V_IS_S1（Sno, Sname, Grade） AS SELECT Student.Sno, Sname, Sage FROM Student, SC WHERE Sdept = 信息系 AND Student.Sno = SC.Sno AND SC.Cno = c01,例2建立信息系選修了c01課程的學生的學號、姓名和成績的視圖。,在已有視圖上定義新視圖,視圖的數(shù)據源可以來自其它的視圖。,示例,CREATE VIEW IS_Student_Sage AS SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage < 20,例3利用例1建立的視圖，建立查詢信息管理系年齡小于20的學生的學號、姓名和年齡的視圖 。,示例,例4 在例1所建的視圖基礎上，例2的視圖定義可改為： CREATE VIEW V_IS_S2(Sno,Sname,Grade) AS SELECT SC.Sno, Sname, Grade FROM IS_Student JOIN SC ON IS_Student.Sno = SC.Sno WHERE Cno = c01,2020年7月29日3時6分,40,定義帶表達式的視圖,定義基本表時，為減少數(shù)據冗余，表中只存放基本數(shù)據。 由基本數(shù)據經過各種計算派生出的數(shù)據一般不存儲。 由于視圖中的數(shù)據并不實際存儲，因此，可以在視圖中設置一些附加列來保存這些派生的數(shù)據。 由于這些附加列在基本表中并不實際存在，因此稱這些列為虛擬列。 稱包含虛擬列的視圖為帶表達式的視圖。,示例,例5.定義一個查詢學生出生年份的視圖，內容包括學號，姓名和出生年份。 CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno, Sname, 2010 - Sage FROM Student,含分組統(tǒng)計信息的視圖,子查詢中含GROUP BY子句，視圖行列由基本表行列得到， 數(shù)據只可看不可改。,示例,CREATE VIEW S_G AS SELECT Sno,AVG(Grade) AverageGrade FROM SC GROUP BY Sno,例6定義一個查詢每個學生的學號及平均成績的視圖。,6.2.3 通過視圖查詢數(shù)據,視圖定義好后，可以對其進行查詢， 通過視圖查詢數(shù)據同基本表一樣。,示例,例7：利用例1建立的視圖，查詢信息系男生的信息。 SELECT * FROM IS_Student WHERE Ssex = 男,轉換成相關基本表的等價查詢,SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系 AND Ssex = 男,示例,例8 查詢信息系選修了“cC01”號課程且成績大于等于60的學生的學號、姓名和成績。 這個查詢可以利用例2的視圖實現(xiàn)。 SELECT * FROM V_IS_S1 WHERE Grade = 60 此查詢轉換成的對最終基本表的查詢： SELECT S.Sno, Sname, Grade FROM SC JOIN Student S ON S.Sno = SC.Sno WHERE Sdept = 信息系 AND SC.Cno = c01 AND Grade = 60,示例,例9.查詢信息系學生的學號、姓名、所選課程名 SELECT v.Sno, Sname, Cname FROM IS_Student v JOIN SC ON v.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno 此查詢轉換成的對最終基本表的查詢： SELECT S.Sno, Sname, Cname FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Sdept = 信息系,示例,例10 利用例6建立的視圖，查詢平均成績大于等于80分的學生的學號和平均成績。 SELECT * FROM S_G WHERE AverageGrade = 80 此查詢轉換成的對最終基本表的查詢： SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) = 80,注意,若將例10的查詢轉換為如下形式： SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade) 80 GROUP BY Sno,x,視圖到基本表的轉換有些并不是直接進行的。 目前大多數(shù)DBMS對這種含有統(tǒng)計函數(shù)的視圖的查詢均能進行正確的轉換。,通過視圖修改數(shù)據,也可以通過視圖修改基本表中的數(shù)據， 但并不是所有的視圖都可以用于修改數(shù)據。如經過統(tǒng)計或表達式計算得到的視圖。 能否通過視圖修改數(shù)據的基本原則： 如果這個操作能夠最終落實到基本表上，并成為對基本表的正確操作，則可以 否則不行。,6.2.4 修改和刪除視圖,1.修改視圖 ALTER VIEW 視圖名( 列名 ,.n ) AS 查詢語句,示例,例11.修改S_G視圖，使其統(tǒng)計每個學生的考試平均成績和修課總門數(shù)。 ALTER VIEW S_G(Sno, AverageGrade,Count_Cno) AS SELECT Sno, AVG(Grade), Count(*) FROM SC GROUP BY Sno,2. 刪除視圖,格式： DROP VIEW 例刪除IS_Student視圖。 DROP VIEW IS_Student,6.2.5 視圖的作用,簡化數(shù)據查詢語句 使用戶能從多角度看待同一數(shù)據 提高了數(shù)據的安全性 提供了一定程度的邏輯獨立性,