索引和視圖-課件

第7章索引和視圖第7章索引和視圖本章學習目標掌握索引的創(chuàng)建、管理、維護和刪除等操作掌握視圖的創(chuàng)建、修改、使用和刪除等操作學會使用視圖查詢、修改、更新和刪除數(shù)據(jù)

本章學習目標掌握索引的創(chuàng)建、管理、維護和刪除等操作7.1索引1、索引基礎知識索引的概念和作用

索引是對數(shù)據(jù)庫表中一個或多個列（例如Student表的“學號”列）的值進行排序的結構。 索引提供指針以指向存儲在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。

7.1索引1、索引基礎知識索引的分類

SQLServer中的索引分為3類，分別是：聚集索引非聚集索引唯一索引

索引的分類聚集索引

聚集索引會對表和視圖進行物理排序，所以這種索引對查詢非常有效，在表和視圖中只能有一個聚集索引。當建立主鍵約束時，如果表中沒有聚集索引，SQLServer會用主鍵列作為聚集索引鍵。也可以手動在表的任何列或列的組合上建立索引，但在實際應用中，一般為定義成主鍵約束的列建立聚集索引。返回聚集索引返回非聚集索引 非聚集索引不會對表和視圖進行物理排序。如果表或視圖中不存儲聚集索引，則表或視圖是未排序的。在表和視圖中最多可以建立250個非聚集索引，或者249個非聚集索引和一個聚集索引。

返回非聚集索引返回唯一索引 唯一索引確保索引鍵不包含重復的值，因此，表或視圖中的每一行在某種程度上是唯一的。例如，如果在表中的“姓名”字段上創(chuàng)建了唯一索引，則以后輸入的姓名將不能同名。返回唯一索引返回2、建立索引 創(chuàng)建表或視圖的索引的方法有兩種：使用對象資源管理器和T-SQL語句。使用“對象資源管理器”創(chuàng)建索引

具體的方法請參考本書第7章2、建立索引使用“對象資源管理器”創(chuàng)建索引

使用“對象資源管理器”創(chuàng)建索引

使用“對象資源管理器”創(chuàng)建索引使用“對象資源管理器”創(chuàng)建索引使用“對象資源管理器”創(chuàng)建索引使用“對象資源管理器”創(chuàng)建索引使用T-SQL語句創(chuàng)建索引

T-SQL語句創(chuàng)建索引的基本語法如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED] INDEXindex_name ON<object>(column[ASC|DESC][,...n]) [INCLUDE(column_name[,...n])] [WITH(<relational_index_option>[,...n])]使用T-SQL語句創(chuàng)建索引屬性和參數(shù)說明：

UNIQUE：建立唯一索引。

CLUSTERED：建立聚集索引。

NONCLUSTERED：建立非聚集索引。

index_name：索引名稱，索引名稱在表或視圖中必須唯一，但 在數(shù)據(jù)庫中不必唯一。

object：表名或視圖名。

column：索引所基于的一列或多列。

ASC|DESC：確定特定索引列的升序或降序排序方向。默認值 為ASC（升序）。

INCLUDE(column_name[,...n])：指定要添加到非聚集索引的 葉級別的非鍵列。非聚集索引可以唯一，也可以不 唯一。

relational_index_option：索引屬性。如DROPINDEX ix_Student表示先刪除已經(jīng)存在的索引，因為索引 名稱不能重復，否則會出錯。屬性和參數(shù)說明：【例7.1】使用T-SQL語句在CJGL數(shù)據(jù)庫中的Student表上創(chuàng)建名為IX_Student的唯一、聚集索引，該索引基于表中的“學號”列。

T-SQL腳本語句如下：

USECJGL GO CREATEUNIQUECLUSTERED INDEXIX_Student ONStudent(StuNo) GO

注意：①UNIQUE和CLUSTERED之間不能用“,”號②“學號”列中不能有重復值③關鍵字的大小寫不影響結果④只有表的所有者才能執(zhí)行CREATEINDEX語句⑤更多請參考本書第頁【例7.1】使用T-SQL語句在CJGL數(shù)據(jù)庫中的Stude3、管理索引 管理索引的操作包括：顯示索引信息重命名索引刪除索引其他3、管理索引顯示索引 在索引創(chuàng)建完成后，可以查看索引的相關信息，包括查看索引的名稱、類型、索引鍵列等。查看索引信息有兩種方法：一種是用“對象資源管理器”進行顯示；另一種是用T-SQL的相關語句進行顯示。

顯示索引使用“對象資源管理器”顯示索引 在“對象資源管理器”中找到相應的表，展開“索引”前面的“+”號后，會列出已經(jīng)存在的索引列表，可以用鼠標右鍵單擊其中一個索引名稱，在打開的快捷菜單中選擇“屬性”，即可彈出相應的“索引”屬性窗口，進行查看相關索引的信息。 如下頁圖所示：使用“對象資源管理器”顯示索引索引和視圖-課件使用T-SQL語句顯示索引 使用T-SQL語句顯示相關索引信息，實際是調(diào)用了系統(tǒng)自定義的存儲過程sp_helpindex（存儲過程的相關知識請參看本書第9章的內(nèi)容）?！纠?.2】使用T-SQL語句顯示CJGL數(shù)據(jù)庫中Student表的索引信息。 在查詢窗口中使用的T-SQL語句如下：

USECJGL GO EXECsp_helpindexStudent GO使用T-SQL語句顯示索引

執(zhí)行結果如圖所示返回 執(zhí)行結果如圖所示返回重命名索引 重命名索引和顯示索引信息一樣，同樣有兩種方法：利用“對象資源管理器”和T-SQL命令。

重命名索引使用“對象資源管理器”重命名索引 此種方法重命名索引比較簡單，和重命名表名的操作一樣。使用“對象資源管理器”重命名索引使用T-SQL語句重命名索引 利用T-SQL命令重命名索引，同樣也是調(diào)用了系統(tǒng)自定義的存儲過程sp_rename。其基本語法如下：

EXECsp_rename'table.old_index','new_index'

參數(shù)說明：

table.old_index：當前的索引名稱。

new_index：新的索引名稱。使用T-SQL語句重命名索引【例7.3】用sp_rename存儲過程重命名CJGL數(shù)據(jù)庫中的Student表下的IX_Student索引為IX_new_Student。 在查詢窗口中輸出以下T-SQL命令：

USECJGL GO EXECsp_rename‘student.IX_Student’,’IX_new_Student’ GO返回【例7.3】用sp_rename存儲過程重命名CJGL數(shù)據(jù)庫刪除索引 刪除索引同樣有兩種方法：利用“對象資源管理器”和T-SQL命令。使用“對象資源管理器”重命名索引 右鍵單擊要刪除的索引，在彈出的快捷菜單中執(zhí)行“刪除”命令，此時會彈出“刪除對象”窗口，單擊窗口中的“確定”按鈕即可刪除此索引。

刪除索引索引和視圖-課件使用T-SQL語句刪除索引 使用T-SQL命令的DROPINDEX語句刪除索引的基本語法如下：

DROPINDEXtable_or_view_name.index_name[,…n]

屬性和參數(shù)說明：

table_or_view_name：表或視圖名

index_name：索引名

…n：刪除多個索引使用T-SQL語句刪除索引【例7.5】刪除CJGL數(shù)據(jù)庫下的Student表中的名為IX_new_Student的索引。 在查詢窗口中輸入如下T-SQL語句：

USECJGL GO DROPINDEXStudent.IX_new_Student Go返回【例7.5】刪除CJGL數(shù)據(jù)庫下的Student表中的名為返3、索引分析和維護索引分析

SQLServer提供了分析索引和查詢性能的方法，這里只介紹常用的兩種命令：SHOWPLAN_ALLSTATISTICSIO3、索引分析和維護SHOWPLAN_ALL命令

SQLServer將顯示在執(zhí)行查詢的過程中連接表時所采取的每個步驟，以及是否選擇和選擇了哪個索引，從而達到幫助用戶分析有哪些索引被系統(tǒng)采用的目的。

SHOWPLAN_ALL的基本語法如下：

SETSHOWPLAN_ALL{ON|OFF}SHOWPLAN_ALL命令【例7.6】在CJGL數(shù)據(jù)庫中的Student表中查詢姓為“王”的所有學生信息，并且分析哪些索引被采用。

在本例中，假設已經(jīng)創(chuàng)建了三個索引，一個是基于“學號”PRIMARYKEY約束的索引“PK_Student”，一個是基于“性別”的名為“IX_SSex_Student”的索引，一個是基于“姓名”的名為“IX_SName_Student”的索引。在查詢窗口中輸入如下命令，并查看結果。【例7.6】在CJGL數(shù)據(jù)庫中的Student表中查詢姓為“ USECJGL GO SETSHOWPLAN_ALLON GO SELECT*FROMStudentWHERE姓名LIKE'王%' GO SETSHOWPLAN_ALLOFF GO

執(zhí)行結果和索引的使用情況如下圖所示 USECJGL執(zhí)行結果分析：在執(zhí)行語句SHOWPLAN_ALL后，能發(fā)現(xiàn)在建立的三個索引中，只有基于“學號”PRIMARYKEY約束的索引“PK_Student”被調(diào)用(圖中紅框)，而其余兩個并未出現(xiàn)，則說明其余的兩個索引在這里是毫無用處的。執(zhí)行結果分析：索引維護 判斷索引是否需要維護的標準是索引碎片的大小。數(shù)據(jù)庫上進行很多次的插入、更新和刪除操作，久而久之就會使數(shù)據(jù)變得雜亂無序，造成索引碎片，從而造成性能的下降。索引維護

可以通過DBCCSHOWCONTIG命令來掃描表，同其返回值來確定該索引頁的碎片是否嚴重。

DBCCSHOWCONTIG語句的基本語法是：

DBCCSHOWCONTIG [( {‘table_name|table_id|‘view_name’|view_id} [,’index_name’|index_id] )] 可以通過DBCCSHOWCONTIG命令來掃描表，同【例7.8】利用DBCCSHOWCONTIG命令返回CJGL數(shù)據(jù)庫中Student表的IX_new_Student索引的碎片信息。 在查詢窗口中輸入如下命令：

useCJGL go DBCCSHOWCONTIG(Student,IX_new_Student) go

返回的信息如下圖所示【例7.8】利用DBCCSHOWCONTIG命令返回CJG利用DBCCSHOWCONTIG命令掃描Student表

當碎片較多時就需要整理，碎片整理的命令為：

DBCCINDEXDEFRAG。

DBCCINDEXDEFRAG命令的基本語法格式為：

DBCCINDEXDEFRAG （database_name,table_name|view_name,index_name）參考本書的【例7.9】利用DBCCSHOWCONTIG命令掃描Student表7.2視圖1、視圖基礎知識視圖的概念和作用 視圖是從一個或多個實際表中獲得的，這些表的數(shù)據(jù)存放在數(shù)據(jù)庫中。那些用于產(chǎn)生視圖的表叫做該視圖的基表。一個視圖也可以從另一個視圖中產(chǎn)生。 視圖中存放的是基表中用戶感興趣的數(shù)據(jù)。7.2視圖1、視圖基礎知識

視圖具有以下優(yōu)點：簡單性安全性邏輯數(shù)據(jù)獨立性

視圖也有一些缺點：性能方面修改受限制 視圖具有以下優(yōu)點：2、創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖

使用“對象資源管理器”創(chuàng)建視圖的方法較為復雜，步驟較為繁瑣，具體操作請讀者參考本書第7章相關示例。2、創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖

使用“對象資源管理器”創(chuàng)建視圖

使用“對象資源管理器”創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖使用“對象資源管理器”創(chuàng)建視圖使用T-SQL語句創(chuàng)建視圖

T-SQL語句創(chuàng)建視圖的基本語法如下：

CREATEVIEWview_name[(column[,...n])] [WITH<view_attribute>[,...n]] ASselect_statement[;]

屬性和參數(shù)說明：

view_name：

column：

WITH<view_attribute>：

AS：

select_statement：視圖的名稱。視圖名稱必須符合有關標識符的規(guī)則視圖中的列使用的名稱對視圖進行屬性設置，比如：WITHENCRYPTION對視圖進行加密，防止在SQLServer復制過程中發(fā)布視圖指定視圖要執(zhí)行的操作定義視圖的SELECT語句使用T-SQL語句創(chuàng)建視圖視圖的名稱。視圖名稱必須符合有關標【例7.11】用T-SQL語句在Student表上創(chuàng)建名為v_Student的視圖，該視圖只具有查看所有姓“王”學生信息的功能。 （1）創(chuàng)建視圖 在“新建查詢”窗口中輸入以下SQL語句：

useCJGL go createviewv_Student as select*fromStudentwhere姓名like'王%' go【例7.11】用T-SQL語句在Student表上創(chuàng)建名為

（2）查看該視圖的內(nèi)容 在“新建查詢”窗口中輸入以下SQL語句。

useCJGL go select*fromv_Student go

結果如圖所示 （2）查看該視圖的內(nèi)容

接下來看一個較為復雜的例子（基表是多張表）【例7.12】創(chuàng)建一個名為“v_C語言成績”的視圖，要求該視圖能顯示所有“C語言程序設計”的成績?yōu)楹细竦膶W生的學號、姓名和分數(shù)。 題目分析： 根據(jù)本例的要求可知，所要查詢的列有學號、姓名和分數(shù)，但是由于Score表中并沒有相對應的列，所以這里的操作應該在表Score、Course和Student上進行。 接下來看一個較為復雜的例子（基表是多張表）在“查詢”窗口中輸入以下代碼：useCJGLgocreateviewv_C語言成績asselectdistinctStudent.StuNo,Student.StuName,Score.GradefromStudent,Score,CoursewhereScore.Grade>='60'andStudent.StuNo=Score.StuNoand Score.CouNo=(selectCourse.CouNo fromCourse whereCouName='C語言程序設計')go在“查詢”窗口中輸入以下代碼：

使用Groupby子句、命名新列【例7.13】創(chuàng)建一個名為“v_專業(yè)學生數(shù)”的視圖，要求該視圖能顯示各專業(yè)的學生總數(shù)。 在“查詢”窗口中輸入以下代碼：

useCJGL go createviewv_專業(yè)學生數(shù)

as select專業(yè)班級,count(*)各專業(yè)學生數(shù)fromStudent groupby專業(yè)班級

go 使用Groupby子句、命名新列3、管理視圖 對視圖進行管理，主要有：重命名視圖修改視圖通過視圖修改數(shù)據(jù)刪除視圖查看視圖定義信息3、管理視圖重命名視圖使用“對象資源管理器”重命名視圖 操作比較簡單，如下圖所示：重命名視圖重命名視圖重命名視圖使用T-SQL語句重命名視圖 使用T-SQL命令進行視圖的重命名實際上是使用了系統(tǒng)存儲過程sp_rename，其語法格式為：

sp_renameold_name,new_name

參數(shù)說明：

old_name：原視圖名稱。

new_name：新視圖名稱。使用T-SQL語句重命名視圖【例7.14】用T-SQL語句將例7.13中創(chuàng)建的視圖“v_專業(yè)學生數(shù)”改名為“v_學生數(shù)”。 在“查詢”窗口中輸入以下代碼：

useCJGL go sp_renamev_專業(yè)學生數(shù),v_學生數(shù)

go【例7.14】用T-SQL語句將例7.13中創(chuàng)建的視圖“v_修改視圖使用“對象資源管理器”修改視圖

使用“對象資源管理器”創(chuàng)建視圖的方法較為復雜，步驟較為繁瑣，具體操作請讀者參考本書第7章的【例7.16】。修改視圖使用T-SQL語句修改視圖 修改視圖的T-SQL命令為ALTERVIEW語句，其基本語法為：

ALTERVIEWview_name[(column[,...n])] [WITH<view_attribute>[,...n]] ASselect_statement[;] [WITHCHECKOPTION]

屬性和參數(shù)說明：

view_name：要修改的視圖名稱

column：列名

view_attribute：視圖的屬性

select_statement：定義視圖的select語句

WITHCHECKOPTION：要求對該視圖執(zhí)行的所有數(shù)據(jù)修改語句都必須符合select_statement中所設置的條件使用T-SQL語句修改視圖【例7.16】將【例7.11】創(chuàng)建的視圖v_Student中的篩選條件改為“所有姓王的女生”。

分析：【例7.11】中創(chuàng)建視圖的條件為“姓王的學生”，而本例是將其條件增加一項——“女生”。 在“查詢”窗口中輸入以下T-SQL命令：

useCJGL go alterviewv_Student as select*fromStudent where姓名like'王%'and性別='女' go【例7.16】將【例7.11】創(chuàng)建的視圖v_Student中通過視圖修改數(shù)據(jù) 在使用視圖修改數(shù)據(jù)時，要注意以下一些事項：不能在一個語句中對多個基本表使用數(shù)據(jù)修改語句。如果要修改由兩個或兩個以上基本表得到的視圖，必須實行多次修改，每次修改只能影響一個基表。對于基表中需要更新而又不允許空值的所有列，它們的值在INSERT語句或DEFAULT定義中指定。這將確?；碇兴行枰档牧卸伎梢垣@取值。通過視圖修改數(shù)據(jù)不能修改經(jīng)過計算得到結果的列。在視圖定義中使用了“WITHCHECKOPTION”子句，則所有在視圖上執(zhí)行的數(shù)據(jù)修改語句都必須符合定義視圖的SELECT語句中所設定的條件。在基表的列中修改的數(shù)據(jù)必須符合對這些列的約束條件，如：是否為空、約束、DEFAULT定義等。不能修改經(jīng)過計算得到結果的列。（1）更新視圖數(shù)據(jù)達到更改表中數(shù)據(jù)的目的【例7.18】將“v_Student”視圖中學號為“