1、制作：荊州職業(yè)技術學院 彭嵐制作：荊州職業(yè)技術學院 彭嵐本章學習目標1、熟練掌握視圖的分類、創(chuàng)建方法及各種操作方法2、熟練掌握索引的創(chuàng)建、管理及維護的各種操作方法學習要點1、查詢視圖的創(chuàng)建、修改與刪除；2、視圖數(shù)據(jù)的查詢、插入、修改與刪除；3、創(chuàng)建、更改、刪除索引對象；4、索引對象的管理及維護制作：荊州職業(yè)技術學院 彭嵐制作：荊州職業(yè)技術學院 彭嵐項目要求 能根據(jù)實際工作需求完成對視圖的創(chuàng)建、管理及維護，以簡化對電腦銷售管理系統(tǒng)中的數(shù)據(jù)查詢操作。創(chuàng)建視圖創(chuàng)建視圖管理視圖管理視圖操作視圖數(shù)據(jù)操作視圖數(shù)據(jù)實際工作任務制作：荊州職業(yè)技術學院 彭嵐 在在CPMS數(shù)據(jù)庫的數(shù)據(jù)庫的WORKER表上創(chuàng)建一個

2、名為表上創(chuàng)建一個名為Worker_view1的視圖，視圖的數(shù)據(jù)包括職位為的視圖，視圖的數(shù)據(jù)包括職位為“業(yè)務業(yè)務員員”的所有職員信息。的所有職員信息。任務一：創(chuàng)建視圖任務一：創(chuàng)建視圖 創(chuàng)建視圖的兩種方法：創(chuàng)建視圖的兩種方法：方法一：利用管理工具方法一：利用管理工具方法二：利用方法二：利用T-SQLT-SQL語句語句制作：荊州職業(yè)技術學院 彭嵐建立視圖建立視圖利用對象資源管理器利用對象資源管理器任務一：在任務一：在CPMS數(shù)據(jù)庫的數(shù)據(jù)庫的WORKER表上創(chuàng)建一個名表上創(chuàng)建一個名為為Worker_view1的視圖，視圖的數(shù)據(jù)包括職位為的視圖，視圖的數(shù)據(jù)包括職位為“業(yè)業(yè)務員務員”的所有職員信息。的所有

3、職員信息。關系圖窗格關系圖窗格條件窗格條件窗格SQL窗格窗格結果窗格結果窗格制作：荊州職業(yè)技術學院 彭嵐建立視圖建立視圖利用利用SQL語句語句1、格式：、格式： Create View 視圖名視圖名 (字段名字段名 , n) With Encryption AS Select 語句語句 With check option 2、說明：、說明：1）With Encryption：對視圖的定義加密：對視圖的定義加密2） With check option：指出對視圖進行的修改要符合：指出對視圖進行的修改要符合Select語句所指定的限制條件。語句所指定的限制條件。3） （字段名）：在視圖中包含的列名

4、，如果未指定則（字段名）：在視圖中包含的列名，如果未指定則與與SELECT語句中的列相同的名稱。語句中的列相同的名稱。制作：荊州職業(yè)技術學院 彭嵐在在users表中創(chuàng)建一個視圖表中創(chuàng)建一個視圖users_view1，視圖中包含三個用戶，視圖中包含三個用戶名和密碼，要求不能查看視圖定義語句。名和密碼，要求不能查看視圖定義語句。create view users_view1with encryptionasselect username,pwd from usersGO此時用此時用SP_HelpText users_view1系統(tǒng)提示：此對象備注已加密系統(tǒng)提示：此對象備注已加密制作：荊州職業(yè)技術學

5、院 彭嵐3、創(chuàng)建時應考慮的因素：、創(chuàng)建時應考慮的因素：1）語句中不能包括）語句中不能包括COMPUTE、COMPUTE BY字句和字句和INTO關鍵字；關鍵字；2）創(chuàng)建時參考的基礎的列數(shù)最多為）創(chuàng)建時參考的基礎的列數(shù)最多為1024列列3）創(chuàng)建視圖不能參考臨時表）創(chuàng)建視圖不能參考臨時表4）CREATE VIEW和其他和其他T-SQL語句不能同時出語句不能同時出現(xiàn)在一個批處理句中現(xiàn)在一個批處理句中制作：荊州職業(yè)技術學院 彭嵐概念：概念：視圖是一個由視圖是一個由SELECT語句指定，用以檢索數(shù)據(jù)庫表語句指定，用以檢索數(shù)據(jù)庫表中某些行或列數(shù)據(jù)的語句存儲定義。中某些行或列數(shù)據(jù)的語句存儲定義。說明：說明：

6、1）視圖是一個基于選擇查詢的虛擬表。視圖與真實的數(shù)據(jù)庫）視圖是一個基于選擇查詢的虛擬表。視圖與真實的數(shù)據(jù)庫表有很多類似的地方，視圖也是由若干字段（列）和一些記表有很多類似的地方，視圖也是由若干字段（列）和一些記錄（行）組成的；錄（行）組成的；2）視圖本身并不存儲數(shù)據(jù)，視圖中的字段和記錄都是來自數(shù)）視圖本身并不存儲數(shù)據(jù)，視圖中的字段和記錄都是來自數(shù)據(jù)庫表（稱為基表）據(jù)庫表（稱為基表） 3）視圖也可像表那樣作為）視圖也可像表那樣作為Select語句的數(shù)據(jù)來源使用；語句的數(shù)據(jù)來源使用；4）視圖可以用來更新其中的數(shù)據(jù)，但更新的是視圖所對應的）視圖可以用來更新其中的數(shù)據(jù)，但更新的是視圖所對應的基表中的數(shù)

7、據(jù)?；碇械臄?shù)據(jù)。什么是視圖什么是視圖制作：荊州職業(yè)技術學院 彭嵐2、視圖的用途、視圖的用途使用視圖可以集中、簡化和定制用戶的數(shù)據(jù)庫顯示。使用視圖可以集中、簡化和定制用戶的數(shù)據(jù)庫顯示。視圖通常應用兩種場合：視圖通常應用兩種場合：簡化數(shù)據(jù)操作簡化數(shù)據(jù)操作。如果在查詢中數(shù)據(jù)分散在兩個或多如果在查詢中數(shù)據(jù)分散在兩個或多個表中，或者所用的搜索條件比較復雜時，需要編寫個表中，或者所用的搜索條件比較復雜時，需要編寫很長的很長的Select語句，并且要多次執(zhí)行此相同的任務，語句，并且要多次執(zhí)行此相同的任務，則可考慮建立視圖。則可考慮建立視圖。提高數(shù)據(jù)庫的安全性。提高數(shù)據(jù)庫的安全性。使用視圖還可定制允許用戶使

8、用視圖還可定制允許用戶查看哪些數(shù)據(jù)，讓用戶通過視圖來訪問表中的特定字查看哪些數(shù)據(jù)，讓用戶通過視圖來訪問表中的特定字段和記錄，而不是授予他們直接訪問基表的權限。段和記錄，而不是授予他們直接訪問基表的權限。制作：荊州職業(yè)技術學院 彭嵐創(chuàng)建視圖示例創(chuàng)建視圖示例創(chuàng)建聯(lián)合視圖創(chuàng)建聯(lián)合視圖將多表查詢中的記錄匯總為一個虛擬表將多表查詢中的記錄匯總為一個虛擬表例例:在在cpms的的worker表和表和sell表上創(chuàng)建一個包括所有女性職員的職員編表上創(chuàng)建一個包括所有女性職員的職員編號、姓名、性別、職位、所銷售貨物編號及銷售價格的視圖號、姓名、性別、職位、所銷售貨物編號及銷售價格的視圖sex_VIEW3 。例三：

9、例三：create view sex_view3asselect a.work_id,work_name,sex,position,ware_id,sell_price from worker a,sell bwhere a.work_id=b.work_id and sex=1goselect * from sex_view3制作：荊州職業(yè)技術學院 彭嵐創(chuàng)建包含集合函數(shù)的視圖創(chuàng)建包含集合函數(shù)的視圖例例:創(chuàng)建一個能顯示職員編號及該職員的銷售總價格的視圖創(chuàng)建一個能顯示職員編號及該職員的銷售總價格的視圖price_VIEW1。create view price_view1asselect work

10、_id,sum(sell_price) from sell group by work_id創(chuàng)建視圖的視圖創(chuàng)建視圖的視圖例例:在在sex_view3視圖中創(chuàng)建一個能顯示職員編號、姓名及性別的視視圖中創(chuàng)建一個能顯示職員編號、姓名及性別的視圖圖sex_view4。create view sex_view4asselect work_id,work_name,sex from sex_view3制作：荊州職業(yè)技術學院 彭嵐兩種方法修改：一是使用企業(yè)管理器；二是使用兩種方法修改：一是使用企業(yè)管理器；二是使用ALTER VIEW語語句。句。1、使用、使用ALTER View修改視圖修改視圖 ALTER

11、View 視圖名視圖名 (字段名字段名 , n) With Encryption AS Select 語句語句 With check option 例：例：修改名為修改名為sex_view1的視圖的視圖,使其能顯示所有男性職員信息使其能顯示所有男性職員信息alter view sex_view1asselect * from worker where sex=0go任務二：管理視圖任務二：管理視圖-修改視圖修改視圖制作：荊州職業(yè)技術學院 彭嵐2、查看視圖信息、查看視圖信息1）查看視圖名稱、擁有者及創(chuàng)建日期）查看視圖名稱、擁有者及創(chuàng)建日期SP_HELP 視圖名視圖名2）查看視圖的定義腳本）查看視

12、圖的定義腳本SP_HELPTEXT 視圖名視圖名3、更改視圖名字、更改視圖名字SP_RENAME 舊視圖名，新視圖名舊視圖名，新視圖名4、刪除視圖、刪除視圖DROP VIEW 視圖名視圖名制作：荊州職業(yè)技術學院 彭嵐任務三：操作視圖數(shù)據(jù)任務三：操作視圖數(shù)據(jù) 視圖數(shù)據(jù)的查詢、插入、修改與刪除與表中數(shù)據(jù)視圖數(shù)據(jù)的查詢、插入、修改與刪除與表中數(shù)據(jù)的操作幾乎一樣。語句均為：的操作幾乎一樣。語句均為：1、查詢：、查詢：SELECT2、插入：、插入：INSERT3、更新：、更新：UPDATE4、刪除：、刪除：DELETE制作：荊州職業(yè)技術學院 彭嵐現(xiàn)場操作在CPMS數(shù)據(jù)庫的Worker表上創(chuàng)建一個名為Wo

13、rker_view5的視圖，該視圖要求包括職員的所有信息。創(chuàng)建成功以后，從視圖中查詢出職位為“業(yè)務員”的職員信息。制作：荊州職業(yè)技術學院 彭嵐現(xiàn)場操作向視圖worker_view5中插入一行數(shù)據(jù)，信息為“9901”、”王方平”、”男”、”1980-3-2”、、”紫禁城45號”、”業(yè)務員”將視圖worker_view5中“王方平”的家庭住址改為”紫陽路45號”將視圖worker_view5中“王方平”的職員信息刪除制作：荊州職業(yè)技術學院 彭嵐七七-2 -2 索引索引制作：荊州職業(yè)技術學院 彭嵐數(shù)據(jù)庫的物理順序數(shù)據(jù)庫的物理順序索引字段索引字段案例一：在案例一：在work

14、er表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index1的唯一索引，要求檢索時能快速的唯一索引，要求檢索時能快速的按照職員姓名字段定位。的按照職員姓名字段定位。Create unique Index work_index1 onworker(work_name asc)制作：荊州職業(yè)技術學院 彭嵐索引的概念索引的概念 索引是一種特殊類型的數(shù)據(jù)庫對象，它與表有著密切的關系。索引是一種特殊類型的數(shù)據(jù)庫對象，它與表有著密切的關系。1、概念：、概念：索引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散索引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散的存儲結構。的存儲結構。2、說明：索引通過記錄表中的關鍵

15、值來指向表中的記錄。、說明：索引通過記錄表中的關鍵值來指向表中的記錄。在一個在一個表中建立一個索引，執(zhí)行查詢時不必掃描整個表，就能快速地找表中建立一個索引，執(zhí)行查詢時不必掃描整個表，就能快速地找到所需的數(shù)據(jù)。一個索引就是一個列表，其中列出一些值和指向到所需的數(shù)據(jù)。一個索引就是一個列表，其中列出一些值和指向每個值在表中的實際存儲位置的指針，這些索引信息放在索引頁每個值在表中的實際存儲位置的指針，這些索引信息放在索引頁中，表中的數(shù)據(jù)則放在數(shù)據(jù)頁中。中，表中的數(shù)據(jù)則放在數(shù)據(jù)頁中。 制作：荊州職業(yè)技術學院 彭嵐 基于某個字段（如基于某個字段（如“姓名姓名”）建立索引的，這樣的字段通）建立索引的，這樣的

16、字段通常稱為常稱為索引鍵索引鍵。索引鍵可以是表中的單個字段，也可以由多個。索引鍵可以是表中的單個字段，也可以由多個字段組合而成。一個索引就是一組索引鍵值的列表，這些值來字段組合而成。一個索引就是一組索引鍵值的列表，這些值來自于表中的各個記錄。索引鍵值可以是惟一的，例如選擇表中自于表中的各個記錄。索引鍵值可以是惟一的，例如選擇表中的主鍵，但索引鍵值也可以有重復值。的主鍵，但索引鍵值也可以有重復值。制作：荊州職業(yè)技術學院 彭嵐案例二：在案例二：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index2的聚集索引，要求檢索時能快速的聚集索引，要求檢索時能快速的按照職工編號字段查詢信息。的按

17、照職工編號字段查詢信息。案例三：在案例三：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index3的唯一索引，要求檢索時能快速的唯一索引，要求檢索時能快速的按照姓名字段定位。的按照姓名字段定位。案例四：在案例四：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index4的非聚集索引，要求檢索時能快的非聚集索引，要求檢索時能快速的按照出生日期字段定位。速的按照出生日期字段定位。制作：荊州職業(yè)技術學院 彭嵐2）說明：）說明：1、PAD_Index 指定每個頁保持開放的空間，只有在指定指定每個頁保持開放的空間，只有在指定FillFactor時才能使用。時才能使用。2、 Fill

18、Factor子句通過參數(shù)指定各索引頁級的填滿程度。子句通過參數(shù)指定各索引頁級的填滿程度。3、Drop_Existing刪除已存在的同名索引。只有在指定的索引名存刪除已存在的同名索引。只有在指定的索引名存在時才能使用在時才能使用制作：荊州職業(yè)技術學院 彭嵐案例二：在案例二：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index2的聚集的聚集索引，要求檢索時能快速的按照職工編號字段查詢信息。索引，要求檢索時能快速的按照職工編號字段查詢信息。 聚集索引表示表中的聚集索引表示表中的，在執(zhí)行，在執(zhí)行Insert語句時，新記錄將按照該索語句時，新記錄將按照該索引鍵值指定的順序插入適當?shù)奈恢蒙稀?/p>

19、引鍵值指定的順序插入適當?shù)奈恢蒙稀?注意注意：（1）在一個表中只能建立在一個表中只能建立一一個聚集索引個聚集索引。（2）必須要先創(chuàng)建聚集索引才能在創(chuàng)建非聚集索引。）必須要先創(chuàng)建聚集索引才能在創(chuàng)建非聚集索引。（3）主鍵創(chuàng)建后，自動創(chuàng)建聚集索引）主鍵創(chuàng)建后，自動創(chuàng)建聚集索引制作：荊州職業(yè)技術學院 彭嵐添加惟一索引時添加惟一索引時使用使用Create Table語句在表中的某個字段上添加惟一語句在表中的某個字段上添加惟一性約束時，則系統(tǒng)會在該字段上自動建立惟一性索引。性約束時，則系統(tǒng)會在該字段上自動建立惟一性索引。案例三：在案例三：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index3

20、的唯的唯一索引，要求檢索時能快速的按照姓名字段定位。一索引，要求檢索時能快速的按照姓名字段定位。制作：荊州職業(yè)技術學院 彭嵐什么是惟一性索引與復合索引什么是惟一性索引與復合索引惟一性索引（惟一性索引（Unique index） 如果表中任意兩行被索引的列不允許出現(xiàn)重復值如果表中任意兩行被索引的列不允許出現(xiàn)重復值,那那么這種索引稱為惟一索引。么這種索引稱為惟一索引。例如，在一個表中包含有身份證號碼字段，則最適合于例如，在一個表中包含有身份證號碼字段，則最適合于建立惟一性索引，因為不可能有兩個人擁有相同的身份建立惟一性索引，因為不可能有兩個人擁有相同的身份證號碼。證號碼。復合索引（復合索引（Com

21、posite index） 基于兩列或多列組合而建立的索引稱為復合索引。基于兩列或多列組合而建立的索引稱為復合索引。在一個復合索引中最多可以使用在一個復合索引中最多可以使用16個字段。在個字段。在Select語語句的句的Where子句中使用多個字段時，復合索引顯得格外子句中使用多個字段時，復合索引顯得格外有用。有用。制作：荊州職業(yè)技術學院 彭嵐案例四：在案例四：在worker表上創(chuàng)建一個名為表上創(chuàng)建一個名為work_index4的非聚的非聚集索引，要求檢索時能快速的按照出生日期字段定位。集索引，要求檢索時能快速的按照出生日期字段定位。Create nonclustered index work

22、_index4On worker (birth asc)制作：荊州職業(yè)技術學院 彭嵐知識點：什么是知識點：什么是非聚集索引（非聚集索引（NonClustered index） 非聚集索引用不會對表和視圖進行物理排序非聚集索引用不會對表和視圖進行物理排序,其用其用于于，其索引頁中包含著索引鍵值，其索引頁中包含著索引鍵值和指向對應數(shù)據(jù)頁的指針，該指針用于確定記錄在表和指向對應數(shù)據(jù)頁的指針，該指針用于確定記錄在表中的實際位置。不過，表中的數(shù)據(jù)按照插入記錄的順中的實際位置。不過，表中的數(shù)據(jù)按照插入記錄的順序存放，并沒有按照索引鍵值指定的順序來存儲。序存放，并沒有按照索引鍵值指定的順序來存儲。 在一個表中最多可以建立在一個表中最多可以建立249個非聚集索引個非聚集索引。這。這樣，就有可能在以不同方式訪問表時使用不同的索引，樣，就有可能在以不同方式訪問表時使用不同的索引，這也是非聚集索引的一個優(yōu)點。這也是非聚集索引的一個優(yōu)點。 缺省創(chuàng)建非聚集索引缺省創(chuàng)建非