《數(shù)據(jù)庫原理及應(yīng)用》教學(xué)課件-第五章視圖與索引

數(shù)據(jù)庫原理及應(yīng)用第五章視圖與索引本章導(dǎo)讀為應(yīng)對(duì)數(shù)據(jù)庫中頻繁的查詢操作，SQLServer提供了視圖和索引以提升查詢的效率。視圖是查詢語句的結(jié)果集，它隱藏了底層的表結(jié)構(gòu)，既簡化了數(shù)據(jù)操作語句又保證了數(shù)據(jù)庫的安全性；索引可以將數(shù)據(jù)的邏輯排序存儲(chǔ)在物理空間中，在數(shù)據(jù)量巨大的情況下縮減查詢時(shí)間。本章重點(diǎn)介紹視圖與索引的基本概念與使用方法，包括使用SQL命令和SSMS進(jìn)行創(chuàng)建、刪除與修改等操作。學(xué)習(xí)目標(biāo)理解視圖的基本概念。掌握使用SQL命令創(chuàng)建、刪除、修改、更新及查詢視圖的方法。了解使用SSMS創(chuàng)建、刪除、修改及更新視圖的操作步驟。理解索引的基本概念，包括索引的特點(diǎn)、設(shè)計(jì)索引的注意事項(xiàng)及索引的類型等。掌握使用SQL命令創(chuàng)建、刪除、修改及查看索引的方法。了解使用SSMS創(chuàng)建、刪除、修改及查看索引的操作步驟。目錄5.1視圖5.2索引5.3實(shí)戰(zhàn)訓(xùn)練——視圖與索引的基本操作視圖01視圖是一種數(shù)據(jù)庫對(duì)象，它是從一個(gè)或多個(gè)表中導(dǎo)出的虛擬表。簡單來說，視圖就是一條SELECT語句所對(duì)應(yīng)的結(jié)果集，結(jié)果集的名字就是視圖名。當(dāng)數(shù)據(jù)表中的數(shù)據(jù)更新時(shí)，視圖中的數(shù)據(jù)也會(huì)隨之更新。同樣，視圖中的數(shù)據(jù)更新時(shí)，數(shù)據(jù)表中的數(shù)據(jù)也會(huì)隨之更新。視圖本身不包含任何數(shù)據(jù)，數(shù)據(jù)庫中存儲(chǔ)的是它的定義，通過視圖看到的數(shù)據(jù)是存放在數(shù)據(jù)表中的。視圖的優(yōu)點(diǎn)與作用02（1）視圖可以只提取出用戶需要的數(shù)據(jù)，便于管理和使用。當(dāng)某些查詢的條件比較復(fù)雜時(shí)，視圖可以將表與表之間的連接條件等對(duì)用戶隱藏起來，使用戶只需查詢一個(gè)視圖，就能夠獲取到想要查找的信息。（3）視圖為數(shù)據(jù)提供了一定的邏輯獨(dú)立性。視圖對(duì)應(yīng)了三級(jí)模式中的外模式，當(dāng)數(shù)據(jù)庫的邏輯結(jié)構(gòu)發(fā)生變化時(shí)，只需要修改視圖的定義，就可以保證用戶的外模式不變，對(duì)應(yīng)的應(yīng)用程序也就不必修改了。（2）視圖提供了對(duì)數(shù)據(jù)的安全保護(hù)機(jī)制。在設(shè)計(jì)數(shù)據(jù)庫應(yīng)用系統(tǒng)時(shí)，視圖可以為不同的用戶定制不同的視圖，使重要數(shù)據(jù)只出現(xiàn)在特定的視圖中。創(chuàng)建視圖的SQL命令為CREATEVIEW，具體語法格式如下：下面對(duì)上述命令進(jìn)行說明。用SQL命令創(chuàng)建視圖01CREATEVIEW<視圖名>[(列名)][WITH[ENCRYPTION][,SCHEMABINDING][,VIEW_METADATA]]AS<SELECT語句>[WITHCHECKOPTION]

（1）<視圖名>指視圖的名稱，命名規(guī)則與數(shù)據(jù)表名相同。

（2）(列名)可以全部省略或全部指定。以下情況須指定列名：視圖中的某些列需要另外設(shè)置別名；SELECT語句中含有同名列；SELECT語句中含有算術(shù)表達(dá)式、函數(shù)等調(diào)用的列。

（3）ENCRYPTION表示視圖加密，設(shè)置此選項(xiàng)后，將無法修改視圖。

（4）SCHEMABINDING表示視圖與引用表的定義綁定，設(shè)置此選項(xiàng)后，引用表將不能隨意更改表結(jié)構(gòu)，如列的數(shù)據(jù)類型等，如果必須更改，則需要先刪除與之綁定的視圖。

（5）VIEW_METADATA表示返回視圖自身的元數(shù)據(jù)，設(shè)置此選項(xiàng)后，返回視圖的每一列的定義，而不是所引用數(shù)據(jù)表的列定義。

（6）AS關(guān)鍵字用來指定視圖要執(zhí)行的操作。

（7）<SELECT語句>可以是任意查詢語句，但通常不包含ORDERBY子句和DISTINCT關(guān)鍵字。

（8）WITHCHECKOPTION表示對(duì)視圖執(zhí)行的修改語句必須符合SELECT語句中的條件。設(shè)置此選項(xiàng)可以確保通過視圖修改數(shù)據(jù)時(shí)，會(huì)自動(dòng)加上SELECT語句中的條件?！纠?-1】

創(chuàng)建一個(gè)計(jì)算機(jī)系教師信息的視圖V_T，其中包括教師號(hào)、教師姓名及職稱。CREATEVIEWV_TASSELECTTNo,TN,Prof FROMT WHEREDept='計(jì)算機(jī)'其中，對(duì)教師信息的訪問限制在了“計(jì)算機(jī)系”之內(nèi)，且只能查看TNo，TN，Prof三個(gè)列的內(nèi)容，保證了數(shù)據(jù)的安全性。【例5-2】

創(chuàng)建一個(gè)學(xué)生平均成績的視圖V_SAVG，其中包括學(xué)號(hào)和平均成績。。CREATEVIEWV_SAVG(SNo,AVGGrade)ASSELECTSNo,AVG(Grade) FROMSC

GroupBYSno其中，對(duì)屬性Grade使用了AVG函數(shù)，所以必須在列名處指定別名。用SSMS創(chuàng)建視圖02在SSMS中可按如下步驟創(chuàng)建視圖（以實(shí)現(xiàn)與例5-2同樣功能為例）。步驟1打開SSMS，在“對(duì)象資源管理器”窗口列表中依次雙擊“DESKTOP-UBOLCSC”→“數(shù)據(jù)庫”→“SRS”，在展開的“SRS”列表中右擊“視圖”，在彈出的快捷菜單中選擇“新建視圖”選項(xiàng)，打開“添加表”對(duì)話框（同時(shí)底層會(huì)打開“”窗口），如圖所示。步驟2在“表”選項(xiàng)卡中選中“SC”，單擊“添加”按鈕，然后單擊“關(guān)閉”按鈕，返回“”窗口，如圖所示。步驟3在界面上方表結(jié)構(gòu)中勾選“SNo”及“Grade”復(fù)選框，在界面中間列表中，右擊列前的實(shí)心三角按鈕

，在彈出的列表中選擇“添加分組依據(jù)”選項(xiàng)，如圖所示。步驟4在“Grade”行的“分組依據(jù)”下拉列表中選擇“Avg”選項(xiàng)，在“別名”列對(duì)應(yīng)的文本框中輸入“AVGGrade”，如圖所示。設(shè)置完成后單擊工具欄中的“保存”按鈕

，打開“選擇名稱”對(duì)話框，在“輸入視圖名稱”文本框中輸入“V_SAVG”，單擊“確定”按鈕，完成視圖的創(chuàng)建。步驟5用SQL命令刪除視圖03刪除視圖的SQL命令為DROPVIEW，具體語法格式如下：【例5-3】

刪除視圖V_T。DROPVIEW<視圖名>DROPVIEWV_T刪除視圖只是刪除了視圖的定義，與視圖有關(guān)的數(shù)據(jù)表中的數(shù)據(jù)不會(huì)受到任何影響。用SSMS刪除視圖04在SSMS中可按如下步驟刪除視圖（以實(shí)現(xiàn)與例5-3同樣功能為例）。步驟1在“對(duì)象資源管理器”窗口列表中右擊數(shù)據(jù)庫“SRS”的視圖“V_T”，在彈出的快捷菜單中選擇“刪除”選項(xiàng)。步驟2打開“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕，完成視圖的刪除，如圖所示。用SQL命令修改視圖01修改視圖的SQL命令為ALTERVIEW，具體語法格式如下：此命令的語法格式與CREATEVIEW的語法格式基本類似。ALTERVIEW<視圖名>[(列名)]AS<SELECT語句>

【例5-4】

修改學(xué)生平均成績視圖V_SAVG，在其中添加學(xué)生名稱字段。ALTERVIEWV_SAVG(SNo,SN,AVGGrade)ASSELECTS.SNo,SN,AVG(Grade) GROUPBYS.SNo,SN用SSMS修改視圖02在SSMS中可按以下步驟修改視圖（以實(shí)現(xiàn)與例5-4同樣功能為例）。步驟1在“對(duì)象資源管理器”窗口列表中右擊數(shù)據(jù)庫“SRS”的視圖“V_SAVG”，在彈出的快捷菜單中選擇“設(shè)計(jì)”選項(xiàng)，打開“”窗口。步驟2在界面上方空白處右擊，在彈出的列表中選擇“添加表”選項(xiàng)，如圖所示。步驟3打開“添加表”對(duì)話框，在“表”選項(xiàng)卡中選中“S”，單擊“添加”按鈕，然后單擊“關(guān)閉”按鈕，返回“”窗口。步驟4在界面上方的表結(jié)構(gòu)“S”中勾選“SN”復(fù)選框，如圖所示。在界面中間的列表中，將鼠標(biāo)移到“SN”行上，按住鼠標(biāo)左鍵不放，拖動(dòng)到列表的第二行，調(diào)整屬性列的順序。單擊工具欄中的“保存”按鈕

，完成視圖的修改。用SQL命令更新視圖03由于視圖是一張?zhí)摫?，所以?duì)視圖的更新最終會(huì)變成對(duì)數(shù)據(jù)表的更新，相關(guān)語法格式均與數(shù)據(jù)表的更新操作一致。在完成以下例題前，讀者可按前文步驟創(chuàng)建一個(gè)計(jì)算機(jī)系學(xué)生的視圖V_S，其中包括學(xué)號(hào)、姓名及性別?！纠?-5】

向視圖V_S插入一條記錄（12004，吳艮，女）。INSERTINTOV_S(SNo,SN,Sex)VALUES('12004','吳艮','女')

【例5-6】

將視圖V_S中的學(xué)生“吳艮”的性別改為“男”。

【例5-7】

刪除視圖V_S中同學(xué)“吳艮”的記錄。UPDATEV_SSETSEX='男'WHERESN='吳艮'DELETEFROMV_SWHERESN='吳艮'用SSMS更新視圖04在SSMS中可按如下步驟在視圖中插入數(shù)據(jù)（以實(shí)現(xiàn)與例5-5同樣功能為例）。步驟1在“對(duì)象資源管理器”窗口列表中右擊數(shù)據(jù)庫“SRS”的視圖“V_S”，在彈出的快捷菜單中選擇“編輯前200行”選項(xiàng)，打開“”窗口。步驟2在“”窗口的空白處輸入要插入的數(shù)據(jù)，如圖所示。步驟3單擊工具欄中的“保存”按鈕

，完成視圖數(shù)據(jù)的插入。在視圖中修改數(shù)據(jù)的步驟與插入數(shù)據(jù)類似，在打開對(duì)應(yīng)視圖的數(shù)據(jù)窗口后（如“”），修改對(duì)應(yīng)數(shù)據(jù)即可。在視圖中刪除數(shù)據(jù)的步驟與在數(shù)據(jù)表中刪除數(shù)據(jù)類似。例如，實(shí)現(xiàn)例5-7同樣功能，可打開“”窗口后，右擊學(xué)生“吳艮”的數(shù)據(jù)行，在彈出的快捷菜單中選擇“刪除”選項(xiàng)，完成視圖中數(shù)據(jù)的刪除，如圖所示。在視圖中查詢與在數(shù)據(jù)表中查詢的SQL命令相同，不同之處在于FROM子句中指明的是視圖名。

【例5-8】

在視圖V_SAVG中查詢學(xué)生“馮明”的信息。SELECT*FROMV_SAVGWHERESN='馮明'本例的執(zhí)行過程為：先找到視圖V_SAVG的定義，將這一定義與查詢語句結(jié)合起來，轉(zhuǎn)換成等價(jià)的針對(duì)數(shù)據(jù)表的查詢語句，這一轉(zhuǎn)換過程稱為視圖消解?？蓪⑸享摾愚D(zhuǎn)換為如下語句：SELECTS.SNo,SN,AVG(Grade)ASAVGGradeFROMS,SCWHERESN='馮明GROUPBYS.SNo,SN由此可以看出，當(dāng)對(duì)數(shù)據(jù)表的查詢比較復(fù)雜時(shí)，可以先建立一個(gè)視圖，只對(duì)視圖進(jìn)行查詢，從而簡化查詢過程。索引01索引是一個(gè)單獨(dú)的、存儲(chǔ)在磁盤上的數(shù)據(jù)庫結(jié)構(gòu)，它包含所有記錄的引用指針。簡單來說，索引就是一個(gè)目錄。例如，想要在一本字典中查找某個(gè)漢字，如果沒有目錄就只能一頁頁翻閱查找，效率十分低下。但若擁有目錄，只需要在目錄中查找拼音或是部首，就能夠快速準(zhǔn)確地找到這個(gè)漢字了。視圖的存在無疑簡化了查詢語句，為SQLServer提升了工作效率。但當(dāng)數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)量過于龐大時(shí)，這種程度的簡化就顯得微不足道了，而索引正是解決這一問題的另一種數(shù)據(jù)庫對(duì)象。當(dāng)數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)量十分龐大時(shí)，可以把它看作一本字典，其中的某一條數(shù)據(jù)就是需要查找的某個(gè)漢字，這時(shí)就體現(xiàn)出了索引的重要性。索引的特點(diǎn)02（1）使用索引可以提升數(shù)據(jù)庫的性能，主要體現(xiàn)在以下幾個(gè)方面。①極大地提升了數(shù)據(jù)的查詢速度，這也是其最主要的優(yōu)點(diǎn)；②通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫中的各行數(shù)據(jù)具有唯一性；③建立在外碼上的索引可以加速表與表之間的連接，益于實(shí)現(xiàn)數(shù)據(jù)的參照完整性；④顯著減少分組和排序查詢所使用的時(shí)間。（2）在提升數(shù)據(jù)庫性能的同時(shí)，索引有一些負(fù)面影響。①索引會(huì)占用物理存儲(chǔ)空間，需要一定的額外物理存儲(chǔ)來存放索引文件；②索引的維護(hù)需要耗費(fèi)時(shí)間，對(duì)數(shù)據(jù)表更新數(shù)據(jù)之后，相應(yīng)的索引也需要?jiǎng)討B(tài)維護(hù)。設(shè)計(jì)索引的注意事項(xiàng)03索引設(shè)計(jì)得不合理會(huì)對(duì)數(shù)據(jù)庫及應(yīng)用程序造成不良影響，所以設(shè)計(jì)索引時(shí)應(yīng)注意以下幾個(gè)事項(xiàng)。

（1）索引并非越多越好，一個(gè)數(shù)據(jù)表中如果含有許多索引，不僅占用磁盤空間，也會(huì)造成大量的維護(hù)開銷。

（2）對(duì)經(jīng)常用于查詢的列可以建立索引提高效率，但需要避免插入不必要的數(shù)據(jù)。

（3）數(shù)據(jù)量較小的數(shù)據(jù)表最好不使用索引，因?yàn)榭赡懿粫?huì)產(chǎn)生明顯的優(yōu)化效果。

（4）對(duì)于不同值較少的列，不要建立索引。例如，性別屬性只有“男”和“女”兩個(gè)不同值，在其上建立索引會(huì)嚴(yán)重降低更新數(shù)據(jù)的速度。聚集索引非聚集索引其他索引SQLServer中的索引聚集索引01聚集索引確定了數(shù)據(jù)存儲(chǔ)的順序，數(shù)據(jù)表內(nèi)的數(shù)據(jù)按照聚集索引鍵值的順序存儲(chǔ)。由于物理存儲(chǔ)順序只有一個(gè)，所以一個(gè)數(shù)據(jù)表只能包含一個(gè)聚集索引。

例如，字典中的拼音目錄就是一個(gè)聚集索引，因?yàn)樽值渲兴械臐h字都是按照拼音的順序排列，而拼音目錄也正是按照這一順序指引漢字所在的位置。在默認(rèn)情況下，系統(tǒng)會(huì)對(duì)PRIMARYKEY約束自動(dòng)創(chuàng)建聚集索引，這也是數(shù)據(jù)表中的數(shù)據(jù)行通常按照主碼排列的原因。非聚集索引02非聚集索引與聚集索引有類似的索引結(jié)構(gòu)，但不同的是，非聚集索引的索引排序與物理排序相互獨(dú)立。例如，字典中的部首目錄就是一個(gè)非聚集索引，因?yàn)樗皇菍⒆值渲兴袧h字按照部首分類，并沒有和漢字的排列順序一致。文與聚集索引類似，非聚集索引能夠有效提升數(shù)據(jù)的查詢速度，但同時(shí)也會(huì)降低更新數(shù)據(jù)的速度，并且占用一定的存儲(chǔ)空間。數(shù)據(jù)行的物理存儲(chǔ)順序與索引鍵的邏輯順序不一致。一個(gè)數(shù)據(jù)表可以包含多個(gè)非聚集索引。所以，當(dāng)一個(gè)數(shù)據(jù)表需要頻繁地更新數(shù)據(jù)或存儲(chǔ)空間有限時(shí)，應(yīng)限制非聚集索引的數(shù)量。其他索引03

SQLServer還提供了許多其他類型的索引，下面對(duì)其中幾種進(jìn)行說明。

（1）唯一索引能夠保證索引中不包含重復(fù)值，從而使數(shù)據(jù)表中的每一行在某種條件下具有唯一性。聚集索引和非聚集索引都可以是唯一索引。

（2）視圖索引即是在視圖上建立的索引，它將視圖具體化，并把結(jié)果集永久存儲(chǔ)在聚集索引中。如果在查詢中頻繁引用進(jìn)行復(fù)雜處理的視圖，則可以對(duì)其創(chuàng)建唯一聚集索引。

（3）全文索引是一種特殊類型的基于標(biāo)記的索引，由SQLServer全文引擎生成及維護(hù)，用于快速查詢某個(gè)字符出現(xiàn)的位置。創(chuàng)建索引的SQL命令為CREATEINDEX，具體語法格式如下：下面對(duì)上述命令進(jìn)行說明。用SQL命令創(chuàng)建索引01CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON<表名或視圖名>(<列名>[ASC|DESC][,…N])[WITH<索引選項(xiàng)>[,…N]]

（1）[UNIQUE]表示在數(shù)據(jù)表或視圖上創(chuàng)建唯一索引。

（2）CLUSTERED表示在數(shù)據(jù)表或視圖上創(chuàng)建聚集索引，NONCLUSTERED表示創(chuàng)建非聚集索引，省略時(shí)，表示創(chuàng)建非聚集索引。

（3）<索引名>表示該索引的名稱，命名規(guī)則與數(shù)據(jù)表名相似。

（4）<表名或視圖名>指明索引所屬的數(shù)據(jù)表或視圖。

（5）<列名>指定索引基于的列名，當(dāng)指定兩個(gè)或多個(gè)列名時(shí)，可創(chuàng)建組合索引。

（6）<索引選項(xiàng)>可以指定關(guān)于索引的其他屬性設(shè)定，具體內(nèi)容讀者可查閱相關(guān)資料。

【例5-9】

為數(shù)據(jù)表SC在SNo和CNo兩列上建立名為SC_I的唯一索引。

【例5-10】

為數(shù)據(jù)表T在TN列上建立名為T_I的非聚集索引。CREATEUNIQUEINDEXSC_IONSC(SNo,CNo)CREATENONCLUSTEREDINDEXT_IONT(TN)本例中的索引SC_I存儲(chǔ)了以屬性SNo與CNo為基準(zhǔn)的一種排列順序，即先按SNo遞增排序，SNo相同時(shí)再按CNo遞增排序；因?yàn)橛蠻NIQUE限制，該索引在（SNo，CNo）組合列上的排序具有唯一性。在SSMS中可按如下步驟創(chuàng)建索引（以實(shí)現(xiàn)與例5-9同樣功能為例）。用SSMS創(chuàng)建索引02步驟1在“對(duì)象資源管理器”窗口列表中依次雙擊“DESKTOP-UBOLCSC”→“數(shù)據(jù)庫”→“SRS”→“”，在展開的“”列表中右擊“索引”，在彈出的快捷菜單中選擇“新建索引”→“非聚集索引”選項(xiàng)。步驟2打開“新建索引”對(duì)話框，在“索引名稱”文本框中輸入“SC_I”，然后單擊“添加”按鈕，如下頁圖所示。步驟3打開“從‘’中選擇列”對(duì)話框，在列表中勾選“SNo”和“CNo”復(fù)選框，單擊“確定”按鈕，如圖5-11所示。步驟4返回“新建索引”對(duì)話框，單擊“確定”按鈕，完成索引的創(chuàng)建。刪除索引的SQL命令為DROPINDEX，具體語法格式如下：用SQL命令刪除索引03

【例5-11】

刪除索引T_I。DROPINDEX<數(shù)據(jù)表或視圖.索引名>DROPINDEXT_IONT或DROPINDEX<索引名>ON<數(shù)據(jù)表或視圖>用SSMS刪除索引（以實(shí)現(xiàn)與例5-11同樣功能為例），可在“對(duì)象資源管理器”窗口列表中右擊數(shù)據(jù)表T的索引“T_I”，在彈出的快捷菜單中選擇“刪除”選項(xiàng)，打開“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕，完成索引的刪除。用SSMS刪除索引04通常情況下，索引在建立之后會(huì)由SQLServer自動(dòng)維護(hù)更新，無須手動(dòng)干預(yù)，但有些情況下需要對(duì)索引進(jìn)行修改。例如，向一個(gè)帶有索引的數(shù)據(jù)表插入大量數(shù)據(jù)時(shí)，為了提高插入語句的效率，可以先刪除索引，在數(shù)據(jù)插入后再重新建立索引。用SQL命令修改索引01（1）修改索引的SQL命令為ALTERINDEX，具體語法格式如下：ALTERINDEX<索引名>ON<表名或視圖名>[修改選項(xiàng)]其中，修改選項(xiàng)的具體內(nèi)容讀者可查閱相關(guān)資料。（2）使用SQL命令修改索引名稱的語法格式如下：EXECsp_rename<'表名或視圖名.索引名'>,<'新索引名'>

【例5-12】

將數(shù)據(jù)表SC的索引SC_I改名為SC_I1。EXECsp_rename'SC.SC_I','SC_I1'用SSMS修改索引02用SSMS修改索引（以實(shí)現(xiàn)與例5-12同樣功能為例），可在“對(duì)象資源管理器”窗口列表中右“索引”，在彈出的快捷菜單中選擇“重命名”選項(xiàng)，輸入