第6章索引和視圖數據庫原理與應用1本章內容6.1索引6.2視圖26.1索引6.1.1索引基本概念6.1.2索引的存儲結構及分類6.1.3創建和刪除索引36.1.1索引基本概念建立索引是為了加快數據的查詢速度。可以為表中的單個列建立索引，也可以為一組列建立索引。索引由索引項組成，索引項由來自表中一個或多個列（稱為搜索關鍵字或索引關鍵字）組成。如：對于一個由A、B、C三個列組成的索引，可以在A以及A、B和A、B、C上對其進行高效搜索。4索引及數據間的對應關系示意5使用索引的代價利用索引提高查詢效率是以占用空間和增加數據更改的時間為代價的。索引在數據庫中會占用一定的存儲空間來存儲索引信息。對數據進行插入、更改和刪除操作時，需要對索引進行相應維護，需要花費時間的。6數據頁在數據庫管理系統中，數據一般是按數據頁存儲的，數據頁是一塊固定大小的連續存儲空間。有的數據庫管理系統數據頁的大小是固定的MySQL的數據頁就固定為16KB；有些數據庫管理系統的數據頁大小可由用戶設定DB2。索引項也按數據頁存儲，大小與存放數據的數據頁相同。7數據頁組織方式示意8存放數據的數據頁與存放索引項的數據頁采用的都是通過指針鏈接在一起的方式連接各數據頁。在頁頭包含指向下一頁及前面頁的指針，這樣就可以將表中的全部數據或者索引鏈在一起。6.1.2索引的存儲結構及分類索引的存儲結構索引的分類聚集索引非聚集索引9索引的B樹存儲結構示意圖10聚集索引聚集索引（也稱為聚簇索引）的B樹是自下而上建立的，最下層的葉級節點存放的是數據，因此它既是索引頁，同時也是數據頁。多個數據頁生成一個中間層節點的索引頁，然后再由數個中間層節點的索引頁合成更上層的索引頁，如此上推，直到生成頂層的根節點的索引頁。11說明在聚集索引的葉節點中，數據按聚集索引關鍵字的值進行物理排序。當在建有聚集索引的列上查找數據時，系統首先從聚集索引樹的入口（根節點）開始逐層向下查找，直到達到B樹索引的葉級，最后只在這個數據頁中查找所需數據即可。12建有聚集索引的表的存儲結構示意13例子:employee表14EnoEnameDeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS在Eno列上建有聚集索引的B樹15適合建立聚集索引的情況包含大量非重復值的列。使用下列運算符返回一個范圍值的查詢：BETWEENAND、>、>=、<和<=。經常被用作連接的列。ORDERBY或GROUPBY子句中指定的列。注意：對于頻繁進行更改操作的列則不適合建立聚集索引。16非聚集索引非聚集索引也稱為非聚簇索引。非聚集索引就類似于術語表，而數據就類似于一本書的內容。17非聚集索引的B樹存儲示意圖18非聚集索引與聚集索引的差別數據不按非聚集索引關鍵字值的順序排序和存儲。非聚集索引的葉級節點不是存放數據的數據頁。非聚集索引B樹的葉級節點是索引行。每個索引行包含非聚集索引關鍵字值以及一個或多個行定位器（如果索引不唯一，則可能是多行）。19在Eno列上建有非聚集索引的情形20適合建立非聚集索引的情況包含大量非重復值的列。經常作為查詢條件使用的列。經常作為連接和分組條件的列。216.1.3創建和刪除索引創建索引查看索引刪除索引22創建索引在MySQL中，建立索引的三種方法：在已建好的表上建立索引；通過更改表結構建立索引；在建表的同時建立索引。23在已建好的表上建立索引CREATE[UNIQUE]INDEX<索引名>ON<表名>(<列名>[(長度)][ASC|DESC][,...n])UNIQUE：表示要創建的索引是唯一索引。ASC|DESC：指定索引項的排序方式。ASC為升序，DESC為降序默認為ASC24通過更改表結構建立索引ALTERTABLE<表名>ADDINDEX<索引名>

(<列名>[(長度)][ASC|DESC])25在建表的同時建立索引CREATETABLE<表名>(<列名><數據類型>[完整性約束]，

…<列名><數據類型>[完整性約束]，INDEX<索引名>(<列名>[ASC|DESC]))26唯一索引建有唯一索引的列中的數據不允許有重復值。例：若在(LastName,FirstName,MiddleInitial)三個列上創建了一個唯一索引FullName，則表中任何兩個人都不可以有完全相同的名字。如果必須要實施唯一性來確保數據的完整性，比較好的做法是在列上創建UNIQUE約束或PRIMARYKEY約束，而不是創建唯一索引。27創建唯一索引和普通索引的例子例6-1為students表的phone列創建唯一索引。CREATEUNIQUEINDEXPhone_indONstudents(phone);例6-2為students表的Sname列創建普通索引。CREATEINDEXSname_indONstudents(Sname);或ALTERTABLEstudentsADDINDEXSname_ind(Sname);28創建多列索引（組合索引）索引可以由多個列組成——組合索引。例6-3創建Employee表，同時在FirstName和LastName列上創建一個多列的普通索引。CREATETABLEEmployee(IdINTPRIMARYKEY,FirstNamevarchar(20),LastNamevarchar(20),INDEXEName_ind(FirstName,LastName));29查看索引在MySQL中，可以使用SHOWINDEX語句查看表中創建的索引。其一般語法格式為：SHOWINDEXFROM<表名>[FROM<數據庫名>]或者SHOWINDEXFROM<數據庫名>.<表名>例6-4查看students表的索引。SHOWINDEXFROMstudents;30刪除索引在MySQL中，刪除索引使用DROPINDEX語句或AlTERTABLE語句實現。其一般語法格式為：DROPINDEX<索引名>on<表名>或：AlTERTABLE<表名>DROPINDEX<索引名>例6-5刪除students表中的Sname_ind索引。DROPINDEXSname_indonstudents;31本章內容6.1索引6.2視圖326.2視圖6.2.1視圖基本概念6.2.2定義視圖6.2.3通過視圖查詢數據6.2.4修改視圖定義6.2.5更新視圖數據6.2.6刪除視圖6.2.7視圖的作用336.2.1視圖基本概念視圖（view）是數據庫中的一個對象。視圖對應關系數據庫中的外模式。視圖是由從數據庫的基本表中選取出來的數據組成的邏輯窗口，是基本表的部分行和列數據的組合。視圖是一個虛表。數據庫中只存儲視圖的定義，而不存儲視圖所包含的數據。對視圖數據的操作最終都會轉換為對基本表的操作。34視圖機制的好處第一，視圖數據始終與基本表數據保持一致。第二，節省存儲空間。35視圖與基本表的關系示意圖366.2.2定義視圖MySQL定義視圖的一般格式：CREATE[ORREPLACE]VIEW<視圖名>[(列名[,...n])]ASSELECT語句37說明定義視圖中引用的表或視圖必須存在。在視圖定義中允許使用ORDERBY子句，但如果從該視圖查詢數據時也使用了ORDERBY子句，則系統將忽略視圖定義中使用的ORDERBY子句。在定義視圖時要么指定視圖的全部列名，要么全部省略不寫，不能只寫視圖的部分列名。如果與視圖相關聯的表或視圖被刪除，則該視圖將不能使用。38定義單源表視圖單源表的行列子集視圖指視圖的數據取自一個基本表的部分行和列視圖行列與基本表行列對應。一般支持通過視圖對數據進行查詢和修改操作。39定義單源表視圖的例子例6-5建立查詢“計算機學院”學生的學號、姓名、性別和所在學院的視圖CS_Student。CREATEVIEWCS_StudentAS SELECTSID,sname,gender,college FROMstudents

WHEREcollege='計算機學院';40定義多源表視圖多源表視圖指定義視圖的查詢語句涉及多張表。例6-6建立查詢“計算機學院”借了“零基礎入門學習C語言”圖書的學生學號、姓名和借書時間的視圖。CREATEVIEWV_CS_S1(SID,sname,borrow_time)AS

SELECTs.SID,sname,borrow_timeFROMstudentssJOINborrowbsONs.SID=bs.SID

JOINBooksbonbs.ISBN=b.ISBNWHEREcollege='計算機學院'ANDbname='零基礎入門學習C語言';41在已有視圖上定義新視圖視圖的來源可以是基本表，也可以是已經建立好的視圖例6-7利用例6-5建立的視圖，建立查詢“計算機學院”女生的學號和姓名的視圖。CREATEVIEWCS_Student_femaleASSELECTSID,SnameFROMCS_StudentWHEREgender='女';42視圖來源是視圖和基本表視圖的來源還可以是視圖和基本表的組合。例6-8利用例6-5，例6-6的視圖定義可改為：CREATEVIEWV_CS_S2(SID,sname,borrow_time)ASSELECTs.SID,sname,borrow_time

FROMCS_studentsJOINborrowbsONs.SID=bs.SIDJOINBooksbonbs.ISBN=b.ISBNWHEREbname='零基礎入門學習C語言';43定義帶表達式的視圖在定義基本表時，基本數據經過各種計算派生出的數據一般是不存儲的。定義視圖時可以根據需要設置一些派生屬性列，在這些派生屬性列中保存經過計算的值。派生屬性由于在基本表中并不實際存在，因此，也稱它們為虛擬列。包含虛擬列的視圖也稱為帶表達式的視圖。44定義帶表達式的視圖的例子例6-9定義一個查詢圖書出版的年數的視圖，內容包括ISBN、書名和距今已出版年數。CREATEVIEWBT_P(ISBN,bname,PubYear)ASSELECTISBN,bname,YEAR(NOW())-YEAR(pub_date)

FROMbooks;NOW()：MySQL系統函數，獲取系統當前日期和時間；YEAR(日期)：MySQL系統函數，獲取日期的年份部分。45含分組統計信息的視圖含分組統計信息的視圖是指定義視圖的查詢語句中含有GROUPBY子句和聚合函數這種視圖只能用于查詢。例6-10定義查詢每種圖書分類的圖書總數量的視圖。CREATEVIEWB_SAS

SELECTcategory,SUM(quantity)SumQuantityFROMbooksGROUPBYcategory466.2.3通過視圖查詢數據通過視圖查詢數據同通過基本表查詢數據一樣。例6-11利用例6-5建立的視圖，查詢計算機學院男生的信息。SELECT*FROMCS_StudentWHEREgender='男'47視圖查詢過程首先檢查要查詢的視圖是否存在。如果存在，則從數據字典中提取視圖的定義。根據定義視圖的查詢語句，將對視圖的查詢轉換成等價的對基本表的查詢，然后再執行轉換后的查詢操作。例6-11的查詢最終轉換成的實際查詢語句如下：

SELECTSID,Sname,gender,collegeFROMstudentsWHEREcollege='計算機學院'ANDgender='男'48轉換不能直接進行的情況有些情況下，通過視圖查詢數據轉換成對基本表查詢的這種轉換不能直接進行。例6-14利用例6-10建立的視圖，查詢相同圖書分類的圖書中，總數量大于等于10的圖書分類和總數量。

SELECT*FROMB_SWHERESumQuantity>=1049例6-14轉換后的查詢語句錯誤的轉換語句：

SELECTcategory,SUM(quantity)FROMbooksWHERESUM(quantity)>=10GROUPBYcategory;正確的轉換語句：

SELECTcategory,SUM(quantity)FROMbooksGROUPBYcategoryHAVINGSUM(quantity)>=10;50通過建立視圖進行分步驟查詢例6-15查詢圖書分類為“TP”的圖書名、出版社和總數量。步驟1：建立統計每種圖書分類的總數量的視圖。CREATEVIEWB_S_SASSELECTcategoryas圖書分類,SUM(quantity)as總數量FROMbooks

GROUPBYcategory;步驟2：利用該視圖和books表查詢“TP”類圖書的書名、出版社和總數量。SELECTbnameAS書名,pressAS出版社,總數量FROMB_S_SJOINbooks

ONB_S_S.圖書分類

=books.CategoryWHERE圖書分類='TP';516.2.4修改視圖定義1.使用CREATEORREPLACEVIEW語句修改視圖定義：CREATEORREPLACEVIEW語句的語法格式為：CREATEORREPLACEVIEW<視圖名>[(列名[,...n])]ASSELECT語句52CREATEORREPLACEVIEW語句例子例6-16修改視圖CS_Student，使視圖的列名為：學號、姓名、性別和學院。CREATEORREPLACEVIEW

CS_Student(學號,姓名,性別,學院)ASSELECTSID,sname,gender,collegeFROMstudentsWHEREcollege='計算機學院';532.使用ALTERVIEW語句修改視圖定義ALTERVIEW語句的語法格式為：

ALTERVIEW<視圖名>[(<列名>[,...n])]