《SQL Server數(shù)據(jù)庫應用技術實例教程》課件任務六

任務：了解視圖及索引的作用，分析“高職院校教學管理系統(tǒng)”中索引及視圖的應用，使用SSMS和T-SQL語句創(chuàng)建、管理“高職院校教學管理系統(tǒng)”中的索引及視圖。6.1創(chuàng)建索引6.2創(chuàng)建視圖6.3知識進階項目實訓任務六優(yōu)化數(shù)據(jù)查詢和數(shù)據(jù)管理

—在SQLServer2005中應用索引及視圖理解索引的概念、分類、作用及使用原則使用SSMS創(chuàng)建、管理索引使用CREATEINDEX語句創(chuàng)建、管理索引理解視圖的概念、作用及特點使用SSMS創(chuàng)建、管理視圖使用CREATEVIEW語句創(chuàng)建、管理視圖學習目標在SQLServer2005中應用索引及視圖6.1創(chuàng)建索引任務：理解索引的概念、優(yōu)缺點及分類；掌握索引的應用原則；分析“高職院校教學管理系統(tǒng)”中索引的應用，使用SSMS和T-SQL創(chuàng)建、管理“高職院校教學管理系統(tǒng)”中的索引。6.1.1認識索引6.1.2使用SSMS創(chuàng)建和管理索引6.1.3使用T-SQL創(chuàng)建和管理索引6.1.4在“高職院校教學管理系統(tǒng)”中應用索引在SQLServer2005中應用索引及視圖索引的概念：數(shù)據(jù)庫中的索引是一個數(shù)據(jù)列表，這個列表包含某個表中的一列或若干列的值的有序集合，并記錄與這些值相對應的數(shù)據(jù)在表中存儲的物理地址。索引的分類：

（1）聚集索引（ClusteredIndex，也稱聚類索引、簇集索引）。聚集索引是表數(shù)據(jù)的物理排序。（2）非聚集索引（NonclusteredIndex，也稱非聚類索引、非簇集索引）。非聚集索引具有與表數(shù)據(jù)完全分離的存儲結構。索引的優(yōu)缺點：索引的應用原則：6.1.1認識索引在SQLServer2005中應用索引及視圖創(chuàng)建索引時需要注意以下幾點：（1）必須是表或視圖的所有者才能創(chuàng)建索引，并且無論表中是否已經(jīng)存在數(shù)據(jù)。（2）只有定義時使用了參數(shù)SCHEMABINDING的視圖上才可以創(chuàng)建索引。（3）創(chuàng)建聚集索引時系統(tǒng)會首先對表進行復制，然后對表中的數(shù)據(jù)進行排序后存儲，最后再刪除原始的表。因此，創(chuàng)建聚集索引時數(shù)據(jù)庫上必須有足夠的空閑空間來容納數(shù)據(jù)副本。（4）一個表中最多可以創(chuàng)建249個非聚集索引。（5）復合索引的列最大數(shù)目為16個，而且各列組合后的最大長度為900B。創(chuàng)建步驟略。6.1.2使用SSMS創(chuàng)建和管理索引提示：在一個表上，主鍵和唯一索引都可以限制數(shù)據(jù)的唯一性。但是通過PRIMARYKEY約束指定的主鍵只能有一個，而唯一索引卻可以有多個在SQLServer2005中應用索引及視圖1．使用T-SQL語句創(chuàng)建索引可以使用T-SQL的CREATEINDEX語句創(chuàng)建索引。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])[WITH<index_option>[,...n]][ONfilegroup][;]<index_option>::=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT__IN__TEMPDB6.1.3使用T-SQL創(chuàng)建和管理索引在SQLServer2005中應用索引及視圖其中：UNIQUE：指明創(chuàng)建的是唯一索引。CLUSTERED|NONCLUSTERED：指明創(chuàng)建的索引為聚集索引還是非聚集索引。系統(tǒng)默認創(chuàng)建的索引為非聚集索引。ASC|DESC：指定特定的索引列的排序方式，默認為升序（ASC）。Column：指定被索引的列。如果使用兩個或兩個以上的列組成一個索引，則稱為復合索引。PAD_INDEX：指定填充索引的內(nèi)部節(jié)點的行數(shù)，至少應大于或等于兩行。PAD_INDEX選項只有在FILLFACTOR選項指定后才起作用。IGNORE_DUP_KEY：指定往包含在唯一約束中的列中插入重復數(shù)據(jù)時SQLServer所作的反應。DROP_EXISTING：指定要刪除并重新創(chuàng)建聚集索引。ONfilegroup：指定存放索引的文件組。2．使用系統(tǒng)存儲過程查看索引信息通過執(zhí)行系統(tǒng)存儲過程sp_helpindex或sp_help，可以查看數(shù)據(jù)表的索引信息。基本語法如下：

[EXEC]{sp_helpindex|sp_help}table_name【例6-2】查看表student_info中的索引信息。代碼如下：sp_helpindexstudent_infoGOsp_helpindex'student_info'GO6.1.3使用T-SQL創(chuàng)建和管理索引在SQLServer2005中應用索引及視圖3．使用系統(tǒng)存儲過程更改索引名稱使用系統(tǒng)存儲過程sp_rename可以更改索引的名稱，基本語法如下：

[EXEC]sp_rename'old_table_name','new_table_name'【例6-3】將索引idx_student的名稱修改為idx_class,然后再重新修改回來。代碼如下：sp_rename'student_info.idx_student','student_info.idx_class'GOsp_rename'student_info.idx_class','student_info.idx_student'GO6.1.3使用T-SQL創(chuàng)建和管理索引在SQLServer2005中應用索引及視圖4．使用T-SQL語句刪除索引當不再需要某個索引時，要及時把索引從數(shù)據(jù)庫中刪除掉。對因為設置PRIMARYKEY約束或UNIQUE約束時由系統(tǒng)創(chuàng)建的索引，可以通過刪除約束的方法刪除索引；對于用戶手工創(chuàng)建的其他索引，可以使用DROPINDEX語句刪除，基本語法如下：

DROPINDEXtable_name.index_name[,…n]【例6-4】將表student_info中的索引idx_student_class刪除。代碼如下：DROPINDEXstudent_info.idx_student_classGO6.1.3使用T-SQL創(chuàng)建和管理索引在SQLServer2005中應用索引及視圖在主鍵列上建立索引在外鍵列上建立索引在經(jīng)常查詢的字段上建立索引數(shù)據(jù)行數(shù)很少的表不建立索引很少或從來不在查詢中作為條件引用的列不要建立索引只有很少取值的列不建立索引定義為text、image和bit數(shù)據(jù)類型的列不建立索引6.1.4在“高職院校教學管理系統(tǒng)”中應用索引在SQLServer2005中應用索引及視圖6.2創(chuàng)建視圖任務：理解視圖的概念和作用；了解視圖的分類和通過視圖進行數(shù)據(jù)修改的限制。分析“高職院校教學管理系統(tǒng)”中視圖的應用，使用SSMS和T-SQL創(chuàng)建、管理“高職院校教學管理系統(tǒng)”中的視圖。

6.1.1認識視圖6.1.2使用SSMS創(chuàng)建和管理視圖6.1.3使用T-SQL創(chuàng)建和管理視圖6.1.4在“高職院校教學管理系統(tǒng)”中應用視圖在SQLServer2005中應用索引及視圖視圖的概念：視圖是一張從一個或多個數(shù)據(jù)表或視圖中導出的虛擬表，生成視圖的表稱為視圖的基表。視圖的分類：

（1）標準視圖。標準視圖是最常用的普通視圖。（2）分區(qū)視圖。分區(qū)視圖與標準視圖相似，只是分區(qū)視圖在一個或多個服務器間水平連接一組成員表中的分區(qū)數(shù)據(jù)，使數(shù)據(jù)看起來就像來自一個表。（3）索引視圖。在SQLServer2005中，可以在視圖上創(chuàng)建唯一的聚集索引和多個非聚集索引，改進最復雜查詢的數(shù)據(jù)訪問性能。這種擁有一個聚集索引的視圖被稱為索引視圖。視圖的作用：簡單性；安全性；邏輯數(shù)據(jù)獨立性。6.2.1認識視圖在SQLServer2005中應用索引及視圖創(chuàng)建步驟略。6.2.2使用SSMS創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖使用T-SQL語句創(chuàng)建視圖：基本語法如下：CREATEVIEW[database_name.][owner.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION][;]

<view_attribute>::={ENCRYPTION|SCHEMABINDING}6.2.3使用T-SQL創(chuàng)建和管理視圖其中：view_name：視圖的名稱。column：視圖中各個列的字段名稱。AS：指定視圖要執(zhí)行的操作。select_statement：視圖定義的SELECT語句。WITHCHECKOPTION：強制針對視圖執(zhí)行的所有數(shù)據(jù)修改都必須符合在select_statement中設置的條件。ENCRYPTION：對sys.syscomments

表中包含CREATEVIEW語句文本的項進行加密。使用WITHENCRYPTION可防止在SQLServer復制過程中發(fā)布視圖。SCHEMABINDING：將視圖綁定到基礎表的架構chema。在SQLServer2005中應用索引及視圖【例6-6】創(chuàng)建一個新視圖v_student_score，該視圖包含“學號”、“姓名”、“班級”、“課程號”和“考試成績”。數(shù)據(jù)來源于表student_info和表student_grade的連接查詢結果集。代碼如下：CREATEVIEWv_student_score(stud_id,stud_name,class_id,course_id,grade)AS(SELECTa.stud_id,a.stud_name,a.class_id,b.course_id,b.gradeFROMstudent_infoajoinstudent_gradebONa.stud_id=b.stud_id)GO6.2.3使用T-SQL創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖【例6-7】創(chuàng)建一個視圖v_person_list，使用戶可以看到所有學生和老師的“編號”、“姓名”和人員類型（“學生”或“教師”）。數(shù)據(jù)來源于表student_info和表teacher_info。代碼如下：CREATEVIEWv_person_listAS(SELECTstud_idASid,stud_nameASname,N'學生'ASperson_typeFROMstudent_infoUNIONSELECTteac_idASid,teac_nameASname,N'教師'ASperson_typeFROMteacher_info)GO6.2.3使用T-SQL創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖2．使用存儲過程查看視圖定義對于已經(jīng)創(chuàng)建的視圖，為了修改視圖的定義或了解視圖是從哪些基表中按照什么條件得到數(shù)據(jù)時，需要查看視圖的定義。除了使用SSMS外，可以使用系統(tǒng)存儲過程sp_helptext獲得視圖定義的有關信息?；菊Z法如下：[EXEC]sp_helptextobject_name其中，object_name為需要查看定義的視圖名稱?！纠?-8】使用存儲過程查看v_person_list的詳細定義信息。代碼如下：EXECsp_helptextv_person_listGO6.2.3使用T-SQL創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖3．使用T-SQL修改視圖使用T-SQL中的ALTERVIEW語句修改視圖定義，基本語法如下：ALTERVIEW[database_name.][owner.]view_name[(column[,...n])]ASselect_statement其中各參數(shù)的含義與CREATEVIEW語句中的參數(shù)含義一致。6.2.3使用T-SQL創(chuàng)建和管理視圖提示：對于沒有指定WITHENCRYPTION參數(shù)加密定義的視圖，除了可以通過SSMS修改外，還可以使用T-SQL語句中的ALTERVIEW命令進行修改；而對于加密的視圖則只能通過T-SQL語句修改。如果只是需要對視圖的名稱進行修改，可以直接通過系統(tǒng)存儲過程sp_rename對視圖重新命名。在SQLServer2005中應用索引及視圖4．使用T-SQL刪除視圖使用T-SQL的DROPVIEW語句可以刪除不再使用的視圖。刪除視圖后，其對應的基表中的數(shù)據(jù)并不受任何影響。基本語法：

DROPVIEW[database_name.][owner.]view_name[...,n][;]6.2.3使用T-SQL創(chuàng)建和管理視圖提示：使用DROPVIEW語句可以同時刪除多個視圖，視圖名稱之間用逗號“，”隔開即可。在SQLServer2005中應用索引及視圖5．通過視圖查詢數(shù)據(jù)定義視圖后，可以通過視圖進行數(shù)據(jù)查詢。【例6-11】通過視圖v_student_list查詢學生的基本信息。SELECT*FROMv_student_listGO【例6-12】通過視圖v_student_list和表student_grade關聯(lián)查詢得到學生的“學號”、“姓名”、“課程編號”和“考試成績”，并且只顯示成績在80分以上的記錄。代碼如下：SELECTa.stud_id,a.stud_name,b.course_id,b.gradeFROMv_student_listajoinstudent_gradebONa.stud_id=b.stud_idWHEREb.grade>=80GO6.2.3使用T-SQL創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖6．通過視圖修改數(shù)據(jù)（1）只能引用一個基表的列，不能在一個語句中同時對分屬不同基表的列數(shù)據(jù)進行修改。如果要修改由多個基表定義的視圖數(shù)據(jù)，則必須進行多次修改，每次修改只能影響一個基表。（2）通過視圖中修改的列必須是直接引用基表中的列。對于通過使用集合函數(shù)得到，或使用表達式由多個字段得到的列，不能進行修改操作。（3）如果在視圖定義中使用了WITHCHECKOPTION子句，則所有在視圖上執(zhí)行的UPDATE操作都必須符合定義視圖的SELECT語句中所設置的條件。（4）通過視圖修改數(shù)據(jù)時，必須符合基表中對這些列的約束條件，如數(shù)據(jù)類型、是否為空、外鍵約束等。6.2.3使用T-SQL創(chuàng)建和管理視圖在SQLServer2005中應用索引及視圖通過視圖屏蔽對所有數(shù)據(jù)的查看通過修改視圖數(shù)據(jù)，簡化數(shù)據(jù)庫中數(shù)據(jù)的修改通過查詢視圖來查看需要的數(shù)據(jù)通過視圖修改基本表中的數(shù)據(jù)并保證基表中的數(shù)據(jù)與視圖中要求的數(shù)據(jù)一致6.2.4在“高職院校教學管理系統(tǒng)”中應用視圖在SQLServer2005中應用索引及視圖6.3知識進階——全文索引任務：了解全文索引的概念，區(qū)分全文索引和普通索引，創(chuàng)建和使用全文索引。在SQLServer2005中應用索引及視圖全文索引1．全文索引的概念全文索引為在字符串數(shù)據(jù)中進行復雜的詞搜索提供有效支持。全文索引存儲關于重要詞和這些詞在特定列中的位置的信息。全文索引利用這些信息，可快速搜索包含具體某個詞或一組詞的行。全文索引包含在全文目錄中；全文目錄和索引不存儲在它們所屬的數(shù)據(jù)庫中。2．全文索引和普通索引的區(qū)別普通索引全文索引存儲時受定義它們所在的數(shù)據(jù)庫的控制存儲在文件系統(tǒng)中，但通過數(shù)據(jù)庫管理每個表允許有若干個普通索引每個表只允許有一個全文索引當對作為其基礎的數(shù)據(jù)進行插入、更新或刪除時，它們會自動更新將數(shù)據(jù)添加到全文索引稱為填充，全文索引可通過調度或特定請求來更新，也可以在添加新數(shù)據(jù)時自動發(fā)生不分組在同一個數(shù)據(jù)庫內(nèi)分組為一個或多個全文目錄使用SSMS、向導或T-S