第5章-索引與視圖

數(shù)據(jù)庫技術與應用數(shù)據(jù)庫技術與應用教材編寫組第5章索引與視圖索引視圖視圖的創(chuàng)建與使用2問題提出什么是索引？為什么需要索引？索引與排序有什么區(qū)別？什么是視圖？為什么要建立視圖？3？5.1索引5.1.1索引的概念5.1.2索引的分類5.1.3索引的管理

45.1.1索引的概念索引是對數(shù)據(jù)庫表中一個或多個字段的值進行排序而創(chuàng)建的一種分散存儲結構（即通過指針來建立元素間的聯(lián)系和存取路徑）。索引也是對數(shù)據(jù)表中一個或多個關鍵字段的值與表中的行之間的對照表。5院系代碼5.1.1索引的概念建立索引的目的有以下幾點：（1）可以大大加快數(shù)據(jù)檢索速度；（2）可以加速排序、分組、連接等操作（3）實現(xiàn)表與表之間的參照完整性（強制實施唯一性）（4）通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)記錄的唯一性例如，查詢Sales數(shù)據(jù)庫employee表中編號為“E002”的員工的信息，可以執(zhí)行如下SQL語句：SELECT*FROMemployeeWHEREemployee_id='E002'若在employee_id列上沒建索引，SQL按順序一行一行地查詢；若在該列上建了索引，SQL先搜索該索引，找到要求的值(E002)，再按照索引中的信息位置，確定表中的行。65.1.2索引的分類SQLServer2008中提供的索引類型主要有：聚集索引、非聚集索引、唯一索引、索引視圖、全文索引、包含性列索引、空間索引、篩選索引、XML（即可擴展標記語言）索引按存儲結構分聚集索引和非聚集索引按數(shù)據(jù)的唯一性分唯一索引和非唯一索引按鍵列的個數(shù)分單列索引和多列索引75.1.2索引的分類按索引的存儲結構分為1.聚集索引將數(shù)據(jù)行的鍵值在表內排序并存儲對應的數(shù)據(jù)記錄，使得數(shù)據(jù)表物理順序與索引順序一致（即通過物理上的順序進行排列而建立索引。一個表只能建立一個聚集索引，該索引可以包含多個列（組合索引）85.1.2索引的分類2.非聚集索引非聚集索引完全獨立于數(shù)據(jù)行的結構。數(shù)據(jù)存儲在一個地方，索引存儲在一個地方。非聚集索引中的數(shù)據(jù)排列順序并不是中數(shù)據(jù)的排列順序。最多能創(chuàng)建249個；創(chuàng)建非聚集索引前要先創(chuàng)建聚集索引。如：可以創(chuàng)建非聚集索引用于檢索學號，創(chuàng)建非聚集索引用于檢索性別。95.1.2索引的分類聚集索引與非聚集索引的比較10索引類型存取速度索引的數(shù)量所需空間聚集索引快一表一個少非聚集索引慢一表可以多個多5.1.2索引的分類SQLSever中數(shù)據(jù)的訪問方法：表掃描法當訪問未建索引的表內數(shù)據(jù)時，從表的起始處逐行查找，直到符合條件為止。使用索引當使用索引訪問建有索引的表內數(shù)據(jù)時，系統(tǒng)會通過遍歷索引樹結構來查找行的存儲位置，效率非常高。115.1.2索引的分類唯一索引確保在索引鍵上不存在相同的值。索引也可以不是唯一的，非唯一索引的多行可以共享同一鍵值。聚集索引和非聚集索引都可以是唯一的。創(chuàng)建主鍵（PRIMARYKEY）或唯一性（UNIQUE）約束時，數(shù)據(jù)庫引擎會自動為指定的列創(chuàng)建唯一索引。索引視圖對索引創(chuàng)建唯一聚集索引后，結果集將存儲在數(shù)據(jù)庫中，就像帶有聚集索引的表一樣，這樣的視圖稱為索引視圖，即是為了實現(xiàn)快速訪問而將其結果持續(xù)存放于數(shù)據(jù)庫內并創(chuàng)建索引的視圖。125.1.3索引的管理

索引的管理包括創(chuàng)建索引、查看索引、修改索引、刪除索引。創(chuàng)建索引的方法系統(tǒng)自動創(chuàng)建索引在創(chuàng)建UNIQUE約束或PRIMARYKEY約束時，系統(tǒng)自動為這些列創(chuàng)建聚集索引。135.1.3索引的管理

2.用戶創(chuàng)建索引（1）使用圖形化界面向導創(chuàng)建索引145.1.3索引的管理

索引的創(chuàng)建（2）使用“表設計器”創(chuàng)建索引15“索引/鍵”窗口5.1.3索引的管理

索引的查看在SQLServer的對象資源管理器可以直觀地查看索引信息索引的重建索引的修改可通過“對象資源管理器”實現(xiàn)16為什么要重建？重建的方法？5.1.3索引的管理

索引的修改可通過“對象資源管理器”實現(xiàn)注意：不能通過此方法修改作為PRIMARYKEY或UNIQUE約束的結果而創(chuàng)建的索引，必須修改約束。索引的刪除索引會減慢插入（INSERT）、更新（UPDATE）和刪除（DELETE）語句的執(zhí)行速度。如果發(fā)現(xiàn)索引阻礙整體性能或不再需要索引，則可將其刪除。17如何刪除索引？？5.2視圖5.2.1視圖的概念5.2.2視圖的創(chuàng)建5.2.3視圖的查詢5.2.4視圖的修改5.2.5視圖的刪除185.2.1視圖的概念索引是為了提升數(shù)據(jù)查詢性能，視圖一般用于給用戶提供一個統(tǒng)一的接口而屏蔽掉無用或需要保密的信息。視圖是一個虛擬表，并不包含任何的物理數(shù)據(jù)視圖中的數(shù)據(jù)不物理地存儲在數(shù)據(jù)庫內。SELECT語句的結果集構成了視圖的內容。19為什么要提供視圖？什么是視圖？5.2.1視圖的概念視圖的優(yōu)點（1）為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。（2）屏蔽數(shù)據(jù)庫的復雜性。（3）簡化用戶權限的管理。（4）便于數(shù)據(jù)共享。（5）可以重新組織數(shù)據(jù)以便輸出到其他應用程序中。20視圖的優(yōu)點有什么？5.2.1視圖的概念視圖有系統(tǒng)視圖和用戶創(chuàng)建的視圖系統(tǒng)視圖（用于返回系統(tǒng)相關信息的視圖）目錄視圖、兼容性視圖、動態(tài)管理視圖、信息架構視圖、復制視圖215.2.2視圖的創(chuàng)建用戶創(chuàng)建的視圖使用圖形工具創(chuàng)建視圖在SSMS中創(chuàng)建視圖的方法主要在視圖設計器中完成。22在顯示的表列表中選擇需要添加的表名，單擊“添加”按鈕。5.2.2視圖的創(chuàng)建使用圖形工具創(chuàng)建視圖新建視圖窗口235.2.2視圖的創(chuàng)建使用CREATEVIEW語句創(chuàng)建視圖語法格式：CREATEVIEW[schema_name.]view_nameAS

select_statement[WITHCHECKOPTION]24查詢語句指出在視圖上所進行的修改都要符合查詢語句所指定的限制條件，這樣可以確保數(shù)據(jù)修改后仍可通過視圖看到修改的數(shù)據(jù)。5.2.2視圖的創(chuàng)建查詢語句用來創(chuàng)建視圖的SELECT語句。對SELECT語句有以下的限制：定義視圖的用戶必須對所參照的表或視圖有查詢權限，即可執(zhí)行SELECT語句。不能使用COMPUTE或COMPUTEBY子句。不能使用ORDERBY子句。不能使用INTO子句。不能在臨時表或表變量上創(chuàng)建視圖。255.2.2視圖的創(chuàng)建操作方法在SSMS窗口右鍵單擊“新建查詢”命令，在彈出的快捷菜單中選中“視圖設計器”命令，打開“視圖設計器”，然后輸入相應命令進行操作。265.2.2視圖的創(chuàng)建例5.1：在Student數(shù)據(jù)庫中創(chuàng)建St_view視圖，該視圖選擇學生信息表St_Info中的所有女學生。創(chuàng)建St_view視圖的語句：CREATEVIEWSt_viewASSELECT*FROMSt_InfoWHERESt_Sex='女'27查詢語句創(chuàng)建基于單表的視圖5.2.2視圖的創(chuàng)建例5.2：創(chuàng)建v_Stu1視圖，包括“法學1601”班各學生的學號、姓名、選修的課程號及成績。要保證對該視圖的修改都符合“Cl_Name為法學1601”這一條件。創(chuàng)建v_Stu1視圖的語句：CREATEVIEWv_Stu1AS

SELECTSt_Info.St_ID,St_Name,S_C_Info.C_No,ScoreFROMSt_Info,S_C_InfoWHERESt_Info.Cl_Name='法學1601'ANDSt_Info.St_ID=S_C_Info.St_IDWITHCHECKOPTION28對v_Stu1視圖的更新操作：修改操作：自動加上Cl_Name='法學0501'的條件刪除操作：自動加上Cl_Name='法學0501'的條件插入操作：自動檢查Cl_Name屬性值是否為'法學0501'

如果不是，則拒絕該插入操作如果沒有提供Sdept屬性值，則自動定義Cl_Name為'法學0501'創(chuàng)建基于多表的視圖5.2.2視圖的創(chuàng)建例5.4：創(chuàng)建學生的平均成績視圖v_stu_avg，包括St_ID（在視圖中列名為學號）和平均成績。創(chuàng)建v_stu_avg視圖的語句：CREATEVIEWv_stu_avgASSELECTSt_IDAS學號,AVG(Score)AS平均成績

FROMS_C_InfoGROUPBYSt_ID29創(chuàng)建分組視圖5.2.2視圖的創(chuàng)建補充例子：例1：在視圖文件v_Stu1中創(chuàng)建選修了課程且成績在85分以上的學生的視圖。CREATEVIEWstu_sc_ScoreASSELECTSt_ID,St_Name,ScoreFROMv_stu1WHEREScore>=8530基于視圖創(chuàng)建的視圖5.2.2視圖的創(chuàng)建例2：創(chuàng)建一個反映學生出生年份的視圖;CREATEVIEWstu_year(St_id,St_name,birthdate)ASSELECTSt_id,St_name,出生年份=year(birthdate)FROMSt_Info31有表達式的視圖5.2.3視圖的查詢視圖創(chuàng)建后，就可以像查詢基本表那樣對視圖進行查詢。例5.5：使用視圖v_Stu查找法學1601班的學生的St_ID、姓名、St_Sex。 SELECTSt_id,St_nameAS姓名,St_SexFROMv_Stu例5.6：查找平均成績在80分以上的學生的學號和平均成績。SELECT*FROMv_stu_avgWHERE平均成績>80325.2.4視圖的修改在視圖使用過程中，可能經(jīng)常會發(fā)生基表改變，視圖無法正常工作的情況，則需修改視圖的定義。1.使用圖形工具修改視圖335.2.4視圖的修改2.使用ALTERVIEW語句修改視圖語法格式：ALTERVIEW[schema_name.]view_nameASselect_statement[WITHCHECKOPTION]345.2.4視圖的修改例5.7：修改例5.1中創(chuàng)建的St_view視圖。將視圖中選擇學生信息表St_Info中的所有女學生修改為選擇所有男學生。修改St_view視圖的語句：ALTERVIEWSt_viewASSELECT*FROMSt_InfoWHERESt_Sex='男'355.2.5視圖的刪除當不再需要某個存在的視圖時，可以刪除它。以釋放存儲空間。刪除視圖后，表和視圖所基于的數(shù)據(jù)并不受影響。1．使用圖形工具刪除視圖2.使用DROPVIEW語句刪除視圖語法格式：DROPVIEW[schema_name.]view_name[,...n]例5.8：刪除St_view視圖。語句為：DROPVIEWst_view36本章小結（1）索引索引是一個單獨的、物理的數(shù)據(jù)庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。根據(jù)索引的存儲結構不同，將其分為聚集索引和非聚集索引兩類。（2)視圖視圖是一個虛擬表，并不包含任何的物理數(shù)據(jù)，即視圖所對應的數(shù)據(jù)不進行實際存儲，數(shù)據(jù)庫中只存放視圖的