項目六公司管理數(shù)據(jù)庫系統(tǒng)中視圖和索引的應(yīng)用課件.pptx

1、會利用視圖操作數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)；會利用索引提高數(shù)據(jù)庫系統(tǒng)的檢索速度，改善數(shù)據(jù)庫的性能 。模塊1 公司數(shù)據(jù)庫系統(tǒng)中視圖的 應(yīng)用教學(xué)目標(biāo)：1、能正確理解視圖的含義和作用。2、會使用企業(yè)管理器和TransactSQL語句管理視圖。3、會利用視圖操作數(shù)據(jù)表的數(shù)據(jù)。工作任務(wù)1、創(chuàng)建有關(guān)女職工的信息數(shù)據(jù)表，包括雇員ID、姓名、性別、出生年月和特長等信息。2、創(chuàng)建有關(guān)客戶訂購產(chǎn)品信息數(shù)據(jù)表，包括客戶名稱、產(chǎn)品名、訂購數(shù)量等信息。3、創(chuàng)建有關(guān)雇員接受訂單信息的數(shù)據(jù)表。4、創(chuàng)建有關(guān)訂購“鼠標(biāo)”的客戶信息的視圖。一、視圖的概述 視圖是一個虛表，它可以訪問來自一個或多個表的列的子集。視圖是從一個或多個表中派生出的數(shù)

2、據(jù)對象。派生出視圖的表稱為基本表。 視圖一經(jīng)定義后，就可以像表一樣被查詢、修改、刪除和更新。二、 創(chuàng)建視圖創(chuàng)建視圖時應(yīng)該注意以下情況： 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。如果視圖中某一列是函數(shù)、數(shù)學(xué)表達式、常量或者來自多個表的列名相同，則必須為列定義名稱。不能在視圖上創(chuàng)建索引，不能在規(guī)則、缺省、觸發(fā)器的定義中引用視圖。當(dāng)通過視圖查詢數(shù)據(jù)時，SQL Server要檢查以確保語句中涉及的所有數(shù)據(jù)庫對象存在，而且數(shù)據(jù)修改語句不能違反數(shù)據(jù)完整性規(guī)則。視圖的名稱必須遵循標(biāo)識符的規(guī)則。1. 利用向?qū)?chuàng)建視圖【例1】創(chuàng)建有關(guān)產(chǎn)品的類

3、別、產(chǎn)品名、單價和庫存量有關(guān)的視圖。操作步驟：在“工具”菜單上單擊“向?qū)А泵?。展開“數(shù)據(jù)庫”文件夾。雙擊“創(chuàng)建視圖向?qū)А?。完成向?qū)е械牟襟E。 2. 利用企業(yè)管理器創(chuàng)建視圖（1） 查詢設(shè)計器和視圖設(shè)計器都由四個窗格組成：關(guān)系圖窗格、網(wǎng)格窗格、SQL 窗格以及結(jié)果窗格。 關(guān)系圖窗格：顯示正在查詢的表和其它表結(jié)構(gòu)化對象。每個矩形代表一個表或表結(jié)構(gòu)化對象，并顯示可用的數(shù)據(jù)列以及表示每列如何用于查詢的圖標(biāo)。網(wǎng)格窗格：包含一個類似電子表格的網(wǎng)格，用戶可以在其中指定選項，比如要顯示哪些數(shù)據(jù)列、要選擇什么行、如何對各行進行分組等等。2. 利用企業(yè)管理器創(chuàng)建視圖（2）2. 利用企業(yè)管理器創(chuàng)建視圖（3）SQL

4、窗格：顯示用于查詢或視圖的 SQL 語句。可以對設(shè)計器所創(chuàng)建的 SQL 語句進行編輯，也可以輸入自己的 SQL 語句。結(jié)果窗格：顯示含有由查詢或視圖檢索的數(shù)據(jù)的網(wǎng)格。在查詢設(shè)計器中，該窗格顯示最近執(zhí)行的選擇查詢的結(jié)果。任務(wù)1 創(chuàng)建女職工的視圖（girl_view），包括雇員ID、姓名、性別、出生年月和特長等信息。操作步驟：單擊要創(chuàng)建視圖的companyinfo數(shù)據(jù)庫?！拘陆ā縷【視圖】【添加表】命令，將打開“添加表”對話框。在 “添加表”對話框中，選中要創(chuàng)建視圖的基表，選擇所需要的字段。設(shè)置字段限制。單擊“運行”按鈕。 單擊工具欄中的“保存”按鈕。3. 利用CREATE VIEW命令創(chuàng)建視圖語

5、法： CREATE VIEW view_name ( column ,.n ) WITH ENCRYPTION AS select_statement WITH CHECK OPTION 參數(shù)說明：view_name:視圖的名稱Column:用于指定視圖中的字段名稱。如果未指定 column，則視圖列將獲得與 SELECT 語句中的列相同的名稱。WITH ENCRYPTION：在syscomments系統(tǒng)表對視圖的文本進行加密。select_statement：用于創(chuàng)建視圖的SELECT語句。WITH CHECK OPTION:用于強制執(zhí)行準(zhǔn)則。【例2】在pubs數(shù)據(jù)庫，創(chuàng)建有關(guān)書的標(biāo)題、類型

6、、單價和出版日期的視圖。 USE pubs GO CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GO 【例3】創(chuàng)建書的平均價格視圖B_TITLEs_AVG。 CREATE VIEW b_titles_avg (price_avg) AS SELECT avg(price) FROM titles_view 說明：創(chuàng)建視圖時，源表可以是基本表，也可以是視圖。【例4】顯示名為 CAonly 的視圖，該視圖使得只對加利福尼亞州（CA）的作者應(yīng)用數(shù)據(jù)修改。CREATE VIEW CAonly AS S

7、ELECT au_lname, au_fname, city, state FROM authors WHERE state = CA任務(wù) 2 創(chuàng)建客戶訂購產(chǎn)品信息視圖，包括客戶名稱、產(chǎn)品名、訂購數(shù)量等信息。程序清單：CREATE VIEW cu_orderasSELECT customer.公司名稱, p_order.數(shù)量, product.產(chǎn)品名FROM customer INNER JOIN p_order ON customer.客戶ID = p_order.客戶ID INNER JOIN product ON p_order.產(chǎn)品ID = product.產(chǎn)品ID任務(wù) 3 創(chuàng)建有關(guān)雇

8、員接受訂單信息的視圖.程序清單：CREATE VIEW em_orderasSELECT em.姓名 , cu.公司名稱, pd.產(chǎn)品名 , po.數(shù)量FROM customer AS cu JOIN p_order AS po ON cu.客戶ID = po.客戶ID JOIN product AS pd ON po.產(chǎn)品ID = pd.產(chǎn)品ID JOIN employee AS em ON po.雇員ID = em.雇員ID任務(wù)4 創(chuàng)建有關(guān)訂購“鼠標(biāo)”的客戶信息的視圖。CREATE VIEW mouse_orderASSELECT customer.公司名稱, customer. 聯(lián)系人姓

9、名, p_order.數(shù)量FROM customer JOIN p_order ON customer.客戶ID = p_order.客戶ID JOIN productON p_order.產(chǎn)品ID = product.產(chǎn)品IDWHERE (product.產(chǎn)品名 = 鼠標(biāo))三、 修改和重命名視圖 1. 修改視圖（1）利用企業(yè)管理器修改視圖。（2）使用ALTER VIEW語句修改視圖。語法： ALTER VIEW view_name (column,.n) WITH ENCRYPTION ASselect_statement WITH CHECK OPTION 【例5】將任務(wù)4中創(chuàng)建的有關(guān)訂購

10、“鼠標(biāo)”的客戶信息的視圖改為有關(guān)“打印紙”的信息。ALTER VIEW mouse_orderASSELECT customer.公司名稱, customer. 聯(lián)系人姓名, p_order.數(shù)量FROM customer JOIN p_order ON customer.客戶ID = p_order.客戶ID JOIN productON p_order.產(chǎn)品ID = product.產(chǎn)品IDWHERE (product.產(chǎn)品名 = 打印紙)2. 重命名視圖 （1）利用企業(yè)管理器重命名視圖 。（2）可以使用系統(tǒng)存儲過程sp_rename修改視圖的名稱，該過程的語法形式如下： sp_renam

11、e old_name,new_name【例6】把視圖MOUSE_ORDER重命名為PAPER_ORDER。其程序清單如下： sp_rename mouse_order,paper_order3、查看視圖信息 可以使用系統(tǒng)存儲過程sp_help顯示視圖特征，使用sp_helptext顯示視圖在系統(tǒng)表中的定義。語法：（1）sp_help 數(shù)據(jù)庫對象名稱（2）sp_helptext 視圖（觸發(fā)器、存儲過程）四. 刪除視圖 （1）使用企業(yè)管理器刪除視圖。 （2）使用Transact-SQL語句DROP VIEW刪除視圖的語法形式如下： DROP VIEW view_name ,n 【例7】同時刪除pu

12、bs數(shù)據(jù)庫中的視圖titleview和CAonly 。 drop view titleview , CAonly五、通過視圖修改記錄 需要注意以下幾點： 不能同時修改兩個或者多個基表。不能修改那些通過計算得到的字段。如果在創(chuàng)建視圖時指定了WITH CHECK OPTION選項，那么所有使用視圖修改數(shù)據(jù)庫信息時，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。執(zhí)行UPDATE、DELETE命令時，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。如果視圖引用多個表時，無法用DELETE命令刪除數(shù)據(jù)。1、數(shù)據(jù)查詢【例8】查詢訂購打印紙的客戶信息。 select 公司名稱，數(shù)量 from paper_order

13、2插入數(shù)據(jù)記錄【例9】利用已創(chuàng)建的girl_view視圖，向表中添加一條新的數(shù)據(jù)記錄。該記錄對應(yīng)的值為：(29, 王玉, 1980-9-9，, 乒乓球)。INSERT INTO girl_viewVALUES(29, 王玉,1980-9-9,乒乓球)【例10】向paper_view視圖中插入一條記錄，查看運行結(jié)果。 INSERT INTO paper_view VALUES (中通,張小平,56) 說明：當(dāng)視圖所依賴的基本表有多個時，不能向該視圖插入數(shù)據(jù)，因為這將會影響多個基本表。2更新數(shù)據(jù)記錄注意：更新的只是數(shù)據(jù)庫中的基表。不能同時修改兩個或者多個基表，可以對基于兩個或多個基表或者視圖的視圖

14、進行修改，但是每次修改都只能影響一個基表。不能修改那些通過計算得到的字段如果指定了WITH CHECK OPTION選項，必須保證修改后的數(shù)據(jù)滿足視圖定義的范圍。【例12】利用已創(chuàng)建的視圖girl_view，修改employee表中李立三的姓名為張宏明。UPDATE girl_view SET 姓名 =張宏明WHERE 姓名=李立三3 刪除數(shù)據(jù)記錄 如果視圖引用多個表時，無法用DELETE命令刪除數(shù)據(jù)?！纠?3】利用視圖gril_view,刪除表employee中姓名為姜玲娜的記錄。DELETE FROM girl_viewWHERE 姓名= 姜玲娜模塊2 數(shù)據(jù)庫系統(tǒng)中索引的應(yīng)用一、教學(xué)目標(biāo)1

15、能正確理解索引的概念、作用和分類。2會使用企業(yè)管理器和TransactSQL語句創(chuàng)建和管理索引。二、工作任務(wù) 通過在數(shù)據(jù)庫系統(tǒng)中創(chuàng)建索引，達到提高數(shù)據(jù)檢索速度，改善數(shù)據(jù)庫性能的目的。1為employee表創(chuàng)建立一個姓名的非聚集索引文件。2為表employee創(chuàng)建立一個姓名和出生日期的復(fù)合索引。3為表employee創(chuàng)建立一個雇員ID的唯一聚集索引。（一）索引的概述 索引是SQL Server使用的內(nèi)部表結(jié)構(gòu)，它是基于表中的一個或多個列的值，提供對表中行的快速存取。1、使用索引的優(yōu)點：加快數(shù)據(jù)檢索速度。保證數(shù)據(jù)記錄的唯一性。加速了表之間的連接。2、使用所有的缺點：創(chuàng)建索引要花費時間每個創(chuàng)建的索引

16、連同原先的數(shù)據(jù)源（表）都需要磁盤空間來存放數(shù)據(jù)。每次修改數(shù)據(jù)時索引都需要更新。3、索引分類簇索引非簇索引1）簇索引（也可稱為聚集索引、簇集索引） 在簇索引中，表中的行的物理存儲順序和索引順序完全相同。每個表只允許建立一個簇索引。數(shù)據(jù)按列進行排序，然后再重新存儲到磁盤上。建立簇索引改變表中的數(shù)據(jù)行的物理順序。聚集索引的結(jié)構(gòu)注意：以姓名為關(guān)鍵字2）非聚集索引 非聚集索引具有完全獨立于數(shù)據(jù)行的結(jié)構(gòu)，使用非聚集索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非聚集索引的葉節(jié)點存儲了組成非聚集索引的關(guān)鍵字值和一個指針，指針指向數(shù)據(jù)頁中的數(shù)據(jù)行，該行具有與索引鍵值相同的列值。 在默認(rèn)情況下，CREATE INDEX

17、建立的索引為非聚集索引。從理論上，一個表可以建立最多249個非聚集索引。非聚集索引的結(jié)構(gòu)3、用戶創(chuàng)建索引的原則在經(jīng)常需要搜索的列上；在作為主鍵的列上；在經(jīng)常用在連接的列上；在經(jīng)常使用在WHERE子句中的列。在經(jīng)常需要排序的列上；在經(jīng)常需要根據(jù)范圍進行搜索的列上；（二）索引的創(chuàng)建和管理 數(shù)據(jù)庫系統(tǒng)的索引有兩種渠道生成：系統(tǒng)自動生成的索引；用戶創(chuàng)建的索引利用索引向?qū)?chuàng)建索引 利用Transact-SQL語句中的CREATE INDEX命令創(chuàng)建索引。任務(wù)1 利用企業(yè)管理器為employee表創(chuàng)建立一個姓名的非聚集索引文件。1在企業(yè)管理器中，選擇要創(chuàng)建索引的表，用右鍵的快捷菜單中選擇【所有任務(wù)】|【管

18、理索引】選項，將打開“管理索引”對話框。2在打開的“管理索引”對話框中，單擊“新建”按鈕，將打開 “新建索引”對話框。3在“新建索引”對話框中的“索引名稱”文本框中輸入新建索引的名稱，這里輸入“i_xm_enployee”。（三）利用TransactSQL語句創(chuàng)建和管理索引 使用T-SQL語句中的 CREATE INDEX，既可以創(chuàng)建聚集索引或非聚集索引。語法：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名 ON 表名|視圖名 列名 ASC | DESC ,.n)提示：在SQL Server 2000數(shù)據(jù)庫中，為表定義一個主鍵，將會自動在主鍵所在列上創(chuàng)建一個唯一索引，稱之為主鍵索引。主鍵索引是唯一索引的特殊類型。參數(shù)說明如下： UNIQUE：用于指定為表或視圖創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。 CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。 NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。任務(wù) 2 創(chuàng)建姓名和出生日期的復(fù)合索引文件。程序清單：CREATE INDEX i_xmrq_employeeON employee (姓名,出生年月)任務(wù)3 為表employe