數(shù)據(jù)庫原理及應用課件2005版第10章視圖和索引

第10章視圖和索引教學內(nèi)容1、詳細介紹視圖的基本概念和操作2、詳細介紹索引的基本概念和操作第10章視圖與索引教學目的與要求理解視圖的基本概念掌握視圖的基本操作理解索引的基本概念掌握索引的基本操作第10章視圖與索引重點理解視圖的基本概念掌握視圖的創(chuàng)建、修改、使用、刪除基本操作理解索引的基本概念掌握索引的分類、創(chuàng)建、查看、刪除基本操作難點視圖、索引的操作第10章視圖與索引課程引入：視圖索引1視圖——視圖概述視圖概述數(shù)據(jù)庫的基本表是按照數(shù)據(jù)庫設(shè)計人員的觀點設(shè)計的，并不一定符合用戶的需求。SQLServer2005可以根據(jù)用戶需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖。視圖是關(guān)系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中數(shù)據(jù)的重要機制，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上的。視圖不是真實存在的基本表而是一張?zhí)摫?，視圖所對應的數(shù)據(jù)并不實際地以視圖結(jié)構(gòu)存儲在數(shù)據(jù)庫中，而是存儲在視圖所引用的表中。1視圖——視圖概述視圖優(yōu)點(1)為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。(2)保證數(shù)據(jù)的邏輯獨立性。(3)重新定制數(shù)據(jù)，使得數(shù)據(jù)便于共享；合并分割數(shù)據(jù)，有利于數(shù)據(jù)輸出到應用程序中。(4)數(shù)據(jù)保密。1視圖——創(chuàng)建視圖視圖創(chuàng)建視圖在數(shù)據(jù)庫中是作為一個獨立的對象存儲的，必須遵循以下原則。①只能在當前數(shù)據(jù)庫中創(chuàng)建視圖。②視圖名稱必須遵循標識符的規(guī)則，且對每個用戶必須唯一。此外，該名稱不得與該用戶擁有的任何表的名稱相同。③用戶可以在其他視圖之上建立視圖。④如果視圖中的某一列是一個算術(shù)表達式、內(nèi)置函數(shù)或常量派生而來，而且視圖中兩個或者更多的不同列擁有一個相同的名字(這種情況通常是因為在視圖的定義中有一個連接，而且這兩個或者多個來自不同表的列擁有相同的名字)，此時，用戶需要為視圖的每一列指定特定的名稱。⑤定義視圖的查詢不可以包含ORDERBY,COMPUTE或COMPUTEBY子句或INTO關(guān)鍵字。⑥不能在視圖上定義全文索引定義。⑦不能創(chuàng)建臨時視圖，也不能在臨時表上創(chuàng)建視圖。⑧不能對視圖執(zhí)行全文查詢，但是如果查詢所引用的表支持全文索引，就可以在視圖定義中包含全文查詢。⑨不能將規(guī)則或者DEFAULT定義關(guān)聯(lián)于視圖。1視圖——創(chuàng)建視圖1．在SQLServerManagementStudio中創(chuàng)建視圖在SQLServerManagementStudio使用向?qū)?chuàng)建視圖是一種圖形界面環(huán)境下最快捷的創(chuàng)建方式，其步驟如下：（1）在“對象資源管理器”中展開要創(chuàng)建視圖的數(shù)據(jù)庫如“教學庫”，展開“視圖”選項，可以看到視圖列表中系統(tǒng)自動為數(shù)據(jù)庫創(chuàng)建的系統(tǒng)視圖。右鍵單擊“視圖”選項，選擇“新建視圖”菜單，打開“添加表”對話框，在此對話框中，可以選擇表、視圖或者函數(shù)，然后單擊“添加”按鈕，就可以將其添加到視圖查詢中。（2）以創(chuàng)建學生表中所有男生信息的視圖為例。選擇“學生”表后，單擊“添加”按鈕，單擊“關(guān)閉”按鈕，返回“新建視圖”對話框。（3）在對話框上半部分，可看到添加進來的“學生”表，選擇視圖所用的列；在對話框中間網(wǎng)格窗格部分，可看到在上半部分的復選框中所選擇的對應表的列，在“性別”列的篩選器中寫出篩選條件“=‘男’”；在對話框下半部分，可看到系統(tǒng)同時生成的T-SQL語句。然后，單擊工具欄上的“保存”按鈕，視圖取名為“男生視圖”。在“對象資源管理器”中展開創(chuàng)建了視圖的數(shù)據(jù)庫如“教學庫”，展開“視圖”選項，就可以看到視圖列表中剛創(chuàng)建好的“男生視圖”。如果沒有看到，單擊“刷新”按鈕，刷新一次即可。1視圖——創(chuàng)建視圖2．使用T-SQL語句創(chuàng)建視圖SQLServer2005提供了CREATEVIEW語句創(chuàng)建視圖，語法格式如下：CREATEVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement[;][WITHCHECKOPTION]1視圖——創(chuàng)建視圖2．使用T-SQL語句創(chuàng)建視圖[例10-1]創(chuàng)建學生_課程_成績視圖，包括計算機專業(yè)的學生的學號、姓名，和他們選修的課程號、課程名和成績。USE教學庫GOCREATEVIEW學生_課程_成績ASSELECT學生.學生號,姓名,課程.課程號,課程名,成績FROM學生,選課,課程WHERE學生.學生號=選課.學生號AND課程.課程號=選課.課程號AND專業(yè)='計算機'GO1視圖——創(chuàng)建視圖2．使用T-SQL語句創(chuàng)建視圖[例10-2]創(chuàng)建庫存統(tǒng)計視圖，求每種商品的總庫存數(shù)量，要求包括商品編號和商品名稱。USE倉庫庫存GOCREATEVIEW庫存統(tǒng)計視圖ASSELECT商品.商品編號,商品名稱,sum(數(shù)量)AS總庫存數(shù)量FROM商品,庫存情況WHERE商品.商品編號=庫存情況.商品編號GROUPBY商品.商品編號,商品名稱GO1視圖——修改視圖在SQLServerManagementStudio中修改視圖使用SQLServerManagementStudio修改視圖的操作步驟如下：(1)打開SQLServerManagementStudio中的對象資源管理器，展開相應數(shù)據(jù)庫文件夾。(2)展開“視圖”選項，右擊要修改的視圖，選擇“修改”命令，打開的對話框可用來修改視圖的定義。(3)如果要向視圖中再添加表，則可以在窗格中右擊鼠標，選擇“添加表”。同樣，如果要移除表，則右擊要被移除的表，選擇“移除”命令。1視圖——修改視圖在SQLServerManagementStudio中修改視圖(4)如果要修改其他屬性，則在對話框上半部分，可重新選擇視圖所用的列；在中間的網(wǎng)格窗格部分，對視圖對每一列進行屬性設(shè)置。最后，單擊工具欄上的“保存”按鈕保存修改后的視圖。(5)例如，修改“男生視圖”，用于查詢所有男生選課情況的信息。首先添加“選課”表，然后選擇其中的“課程號”和“成績”列，其它屬性不變，在對話框下半部分，可看到系統(tǒng)同時對T-SQL語句的修改。最后，單擊工具欄上的“保存”按鈕保存修改后的“男生視圖”。1視圖——修改視圖2．使用T-SQL語句修改視圖T-SQL提供了ALTERVIEW語句修改視圖，語法格式如下：ALTERVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement[;][WITHCHECKOPTION]注：語句中的參數(shù)與CREATEVIEW語句中的參數(shù)相同。1視圖——修改視圖2．使用T-SQL語句修改視圖[例10-3]修改庫存統(tǒng)計視圖，求每種商品的總庫存數(shù)量和總價錢，要求包括商品編號和商品名稱。USE倉庫庫存GOALTERVIEW庫存統(tǒng)計視圖ASSELECT商品.商品編號,商品名稱,sum(數(shù)量)AS總庫存數(shù)量,單價*sum(數(shù)量)AS總價FROM商品,庫存情況WHERE商品.商品編號=庫存情況.商品編號GROUPBY商品.商品名稱,商品.商品編號,商品.單價GO1視圖——使用視圖1．使用視圖進行數(shù)據(jù)查詢[例10-4]在查詢窗口中查詢學生_課程_成績視圖，統(tǒng)計“C++語言”課程的總分和平均分。USE教學庫GOSELECT總分=SUM(成績),平均分=AVG(成績)FROM學生_課程_成績WHERE課程名='C++語言'GO本例的執(zhí)行結(jié)果為：總分平均分----------------------154771視圖——使用視圖1．使用視圖進行數(shù)據(jù)查詢[例10-5]在查詢窗口中查詢“冰箱”的庫存統(tǒng)計信息。USE倉庫庫存GOSELECT*FROM庫存統(tǒng)計視圖WHERE商品名稱='冰箱'本例的執(zhí)行結(jié)果為：商品編號商品名稱總庫存數(shù)量總價-----------------------------------------------------bx-179冰箱26400bx-340冰箱125681視圖——使用視圖2．使用視圖修改基本表中數(shù)據(jù)修改視圖的數(shù)據(jù)，其實就是對基本表進行修改，真正插入數(shù)據(jù)的地方是基本表，而不是視圖，同樣使用INSERT、UPDATE、DELETE語句來完成。但是在對視圖進行修改的時候也要注意一些事項，并不是所有的視圖都可以更新，只有對滿足以下可更新條件的視圖才能進行更新。1視圖——使用視圖2．使用視圖修改基本表中數(shù)據(jù)(1)任何修改（包括UPDATE、INSERT和DELETE語句）都只能引用一個基本表的列。(2)視圖中被修改的列必須直接引用表列中的基礎(chǔ)數(shù)據(jù)。不能通過任何其他方式對這些列進行派生，如通過聚合函數(shù)、計算（如表達式計算）、集合運算等。(3)被修改的列不受GROUPBY、HAVING、DISTINCT或TOP子句的影響。1視圖——使用視圖2．使用視圖修改基本表中數(shù)據(jù)即使是可更新視圖，也不能隨意更新數(shù)據(jù)。(1)通常有可能插入并不滿足視圖查詢的WHERE子句條件中的一行。為了進行限制此操作，可以在創(chuàng)建視圖時使用WITHCHECKOPTION選項。(2)如果視圖所依賴的基本表有多個時，不能向該視圖添加數(shù)據(jù)，因為這將影響多個基本表。(3)修改數(shù)據(jù)時，若視圖依賴于多個基本表，那么一次修改只能修改一個基本表中的數(shù)據(jù)。(4)刪除數(shù)據(jù)時，若視圖依賴于多個基本表，那么不能通過視圖刪除數(shù)據(jù)。1視圖——使用視圖2．使用視圖修改基本表中數(shù)據(jù)使用視圖修改基本表中數(shù)據(jù)[例10-6]通過“男生視圖”向“學生”表中插入一個“男”生。INSERTINTO男生視圖VALUES('0501001','張三','男','計算機')如果通過“男生視圖”向“學生”表中插入一個女生，也可以完成插入，如果不想通過“男生視圖”插入“女”生，在創(chuàng)建“男生視圖”應該使用WITHCHECKOPTIDN選項。1視圖——刪除視圖在不需要該視圖的時候或想清除視圖定義及與之相關(guān)聯(lián)的權(quán)限時，可以刪除該視圖。視圖的刪除不會影響所依附的基表的數(shù)據(jù)，定義在系統(tǒng)表sysahjects、syscolumns、ments、sysdepends和sysprotects中的視圖信息也會被刪除。1．在SQLServerManagementStudio刪除視圖在SQLServerManagementStudio中選中要刪除的視圖，右鍵單擊選擇“刪除”命令，進入“刪除對象”窗口，單擊“確定”按鈕就能刪除視圖。1視圖——刪除視圖2．在查詢窗口中執(zhí)行T-SQL語句刪除視圖T-SQL提供了視圖刪除語句DROPVIEW。其語法格式如下：DROPVIEWview_name[例10-7]刪除例10-1創(chuàng)建的學生_課程_成績視圖。USE教學庫GODROPVIEW學生_課程_成績GO

2索引——索引簡介索引(Index)是對數(shù)據(jù)庫表中一個或多個列的值進行排序的結(jié)構(gòu)，其主要目的是提高SQLServer系統(tǒng)的性能，加快數(shù)據(jù)的查詢速度和減少系統(tǒng)的響應時間。所以，索引就是加快檢索表中數(shù)據(jù)的方法。索引簡介數(shù)據(jù)庫的索引就類似于書籍的目錄，如果想快速查找而不是逐頁查找指定的內(nèi)容，可以通過目錄中章節(jié)的頁號找到其對應的內(nèi)容。類似地，索引通過記錄表中的關(guān)鍵值指向表中的記錄，這樣數(shù)據(jù)庫引擎就不用掃描整個表而定位到相關(guān)的記錄。相反，如果沒有索引，則會導致SQLServer搜索表中的所有記錄，以獲取匹配結(jié)果。2索引——索引簡介10.2.1索引簡介索引的優(yōu)點：（1）大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。（2）創(chuàng)建唯一性索引，保證表中每一行數(shù)據(jù)的唯一性。（3）加速表和表之間的連接。（4）在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。（5）查詢優(yōu)化器可以提高系統(tǒng)的性能，但它是依靠索引起作用的。2索引——索引類型10.2.1索引類型SQLServer2005支持在表中任何列（包括計算列）上定義索引。索引可以是唯一的，即索引列不會有兩行記錄相同，這樣的索引稱為唯一索引。例如，如果在表中的“姓名”列上創(chuàng)建了唯一索引，則以后輸入的姓名將不能同名。索引也可以是不唯一的，即索引列上可以有多行記錄相同。如果索引是根據(jù)單列創(chuàng)建的，這樣的索引稱為單列索引，根據(jù)多列組合創(chuàng)建的索引則稱為復合索引。索引的組織方式的不同，可以將索引分為聚集索引和非聚集索引。2索引——索引類型10.2.1索引類型1.聚集索引聚集索引會對表和視圖進行物理排序，所以這種索引對查詢非常有效，在表和視圖中只能有一個聚集索引。當建立主鍵約束時，如果表中沒有聚集索引，SQLServer會用主鍵列作為聚集索引鍵?？梢栽诒淼娜嗡帕谢蛄械慕M合上建立索引，實際應用中一般為定義成主鍵約束的列建立聚集索引。例如，漢語字典的正文就是一個聚集索引的順序結(jié)構(gòu)2索引——索引類型10.2.1索引類型2.非聚集索引非聚集索引不會對表和視圖進行物理排序。如果表中不存在聚集索引，則表是未排序的。在表或視圖中，最多可以建立250個非聚集索引，或者249個非聚集索引和1個聚集索引。例如，可以根據(jù)“偏旁部首”來查。比如查“張”字，在查部首之后的檢字表中“張”的頁碼是622頁，檢字表中“張”的上面是“弛”字，但頁碼卻是60頁，“張”的下面是“弟”字，頁碼是95頁，正文中這些字并不是真正的分別位于“張”字的上下方。2索引——索引類型10.2.1索引類型聚集索引和非聚集索引都可以是唯一的索引。因此，只要列中數(shù)據(jù)是唯一的，就可在同一個表上創(chuàng)建一個唯一的聚集索引。如果必須實施唯一性以確保數(shù)據(jù)的完整性，則應在列上創(chuàng)建UNIQUE或PRIMARYKEY約束，而不要創(chuàng)建唯一索引。創(chuàng)建PRIMARYKEY或UNIQUE約束會在表中指定的列上自動創(chuàng)建唯一索引。創(chuàng)建UNIQUE約束與手動創(chuàng)建唯一索引沒有明顯的區(qū)別，進行數(shù)據(jù)查詢時，查詢方式相同，而且查詢優(yōu)化器不區(qū)分唯一索引是由約束創(chuàng)建還是手動創(chuàng)建的。如果存在重復的鍵值，則無法創(chuàng)建唯一索引和PRIMARYKEY或UNIQUE約束。如果是復合的唯一索引，則該索引可以確保索引列中每個組合都是唯一的，創(chuàng)建復合唯一索引可為查詢優(yōu)化器提供附加信息，所以對多列創(chuàng)建復合索引時最好是唯一索引。2索引——創(chuàng)建索引我們已經(jīng)知道，創(chuàng)建索引雖然可以提高查詢速度，但是它需要犧牲一定的系統(tǒng)性能。因此，在創(chuàng)建時，哪些列適合創(chuàng)建索引，哪些列不適合創(chuàng)建索引，需要進行詳細的考察。1．創(chuàng)建索引時應考慮的問題（1）對一個表中建大量的索引，應進行權(quán)衡。（2）對于小型表（行數(shù)較少）進行索引可能不會產(chǎn)生優(yōu)化效果。（3）對于主鍵和外鍵列應考慮建索引，因為經(jīng)常通過主鍵查詢數(shù)據(jù)，而外鍵用于表間的連接。（4）很少在查詢中使用的列以及值很少的列不應考慮建索引。（5）視圖中如果包含聚集函數(shù)或連接時，創(chuàng)建視圖的索引可以顯著提升查詢性能。2索引——創(chuàng)建索引通過SQLServerManagementStudio創(chuàng)建索引在SQLServerManagementStudio中使用向?qū)?chuàng)建索引是一種圖形界面環(huán)境下最快捷的創(chuàng)建方式，其步驟如下：1）在SQLServerManagementStudio的“對象資源管理器”面板中，選擇要創(chuàng)建索引的表(如教學庫中的學生表)，然后展開學生表前面的“>”號，選中“索引”選頂右擊，在彈出的快捷菜單中選擇“新建索引”命令，如圖所示：。2索引——創(chuàng)建索引通過SQLServerManagementStudio創(chuàng)建索引（2）選擇“新建索引”命令，進入如圖所示的“新建索引”窗口。（3）在“常規(guī)”選項窗口中，可以創(chuàng)建索引，在“索引名稱”文本框中輸入索引名稱，在“索引類型”下拉列表中選擇是不是聚集索引，在“唯一索引”單選框中選擇是否設(shè)置唯一索引等。例如，輸入索引名稱為“index_姓名”，選擇“非聚集”選項。（4）通過選擇索引設(shè)置按鈕，可以為新建的索引添加、刪除、移動索引列。例如，選擇“添加”按鈕，進入如圖所示的“添加索引列”窗口，選中“姓名”列前的多選按鈕，單擊“確定”按鈕即可添加一個按“姓名”列升序排序的非聚集索引。再選擇“確定”按鈕，索引創(chuàng)建完成。（5）索引創(chuàng)建完成后，在SQLServerManagementStudio的“對象資源管理器”面板中，選擇創(chuàng)建了索引的表(學生表)，展開學生表前面的“>”號，再展開“索引”選頂前面的“>”號，就會出現(xiàn)新建的索引——index_姓名，如圖所示。2索引——創(chuàng)建索引3．利用T-SQL語句創(chuàng)建索引使用T-SQL語句創(chuàng)建索引的語法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ON{table_name|view_name}(column[ASC|DESC][,...n])[WITH<index_option>[,...n]] [ONfilegroup]

<index_option>::=

{PAD_INDEX|

FILLFACTOR=fillfactor|

IGNORE_DUP_KEY|

DROP_EXISTING|

STATISTICS_NORECOMPUTE}2索引——創(chuàng)建索引3．利用T-SQL語句創(chuàng)建索引[例10-8]根據(jù)教學庫中學生表的姓名列的升序創(chuàng)建一個名為index_學生姓名的普通索引。

USE教學庫GOCREATEINDEXindex_學生姓名ON學生(姓名)GO[例10-9]根據(jù)倉庫庫存數(shù)據(jù)庫中商品表的商品名稱、生產(chǎn)商創(chuàng)建一個名為商品_生產(chǎn)商的唯一性復合索引，其中商品名稱為升序，生產(chǎn)商為降序。USE倉庫庫存GOCREATEUNIQUEINDEX商品_生產(chǎn)商ON商品(商品名稱ASC,生產(chǎn)商DESC)GO2索引——創(chuàng)建索引4．間接創(chuàng)建索引在定義表結(jié)構(gòu)或修改表結(jié)構(gòu)時，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創(chuàng)建索引。[例10-10]創(chuàng)建一個學生1表，并定義了主鍵約束。USE教學庫GOCREATETABLE學生1(snochar(6)PRIMARYKEY,snamechar(8))此例中，就按sno升序創(chuàng)建了一個聚集索引。2索引——創(chuàng)建索引[例10-11]創(chuàng)建一個教師表，并定義了主鍵約束和唯一性約束。USE教學庫GOCREATETABLE教師(tnochar(6)PRIMARYKEY,tamechar(8)UNIQUE)此n例中，創(chuàng)建了兩個索引，按tno升序創(chuàng)建了一個聚集索引，按tname升序創(chuàng)建了一個非聚集唯一索引。2索引——創(chuàng)建索引5．創(chuàng)建索引視圖視圖也稱為虛擬表，這是因為由視圖返回的結(jié)果集其一般格式與由列和行組成的表相似，并且，在SQL語句中引用視圖的方式也與引用表的方式相同。標準視圖的結(jié)果集不是永久地存儲在數(shù)據(jù)庫中。對于標準視圖而言，為每個引用視圖的查詢動態(tài)生成結(jié)果集的開銷很大，特別是對于那些涉及對大量行進行復雜處理（如聚合大量數(shù)據(jù)或連接許多行）的視圖更為可觀。若經(jīng)常在查詢中引用這類視圖，可通過在視圖上創(chuàng)建唯一聚集索引來提高性能。在視圖上創(chuàng)建唯一聚集索引時將執(zhí)行該視圖，并且結(jié)果集在數(shù)據(jù)庫中的存儲方式與帶聚集索引的表的存儲方式相同。2索引——創(chuàng)建索引5．創(chuàng)建索引視圖

可以在SQLServerManagementStudio的對象資源管理器，展開相應數(shù)據(jù)庫文件夾，展開“視圖”選項，再展開要創(chuàng)建索引的視圖，右擊“索引”選項選擇“新建索引”命令。例如，按成績的升序為學生_課程_成績視圖創(chuàng)建一個唯一的聚集索引，在“新建索引”對話框內(nèi)輸入索引名，設(shè)置索引類型，單擊“確定”按鈕即可新建一個索引視圖。2索引——創(chuàng)建索引5．創(chuàng)建索引視圖

[例10-12]創(chuàng)建一個女學生的視圖，并為該視圖按學生號升序創(chuàng)建一個具有唯一性的聚集索引。創(chuàng)建視圖：CREATEVIEW女學生WITHSCHEMABINDINGASSELECT學生號,姓名,性別,專業(yè)FROMdbo.學生WHERE性別='女'GO創(chuàng)建索引：CREATEUNIQUECLUSTEREDINDEX女學生索引ON女學生(學生號)GO2索引——查看索引信息查看索引信息在實際使用索引的過程中，有時需要對表的索引信息進行查詢，了解在表中曾經(jīng)建立的索引?？梢允褂肧QLServerManagementStudio進行查詢；也可以在查詢分析器中使用T-SQL語言進行查詢。1.在SQLServerManagementSt