6視圖的創(chuàng)建與使用匯總課件

1、第6章 視圖的創(chuàng)建與使用 2022/7/141用戶對(duì)數(shù)據(jù)源進(jìn)行查詢時(shí)，常常會(huì)對(duì)數(shù)據(jù)進(jìn)行更新、刪除及添加新記錄等操作，前面介紹的查詢得到的結(jié)果是只讀的，當(dāng)需要對(duì)查找結(jié)果進(jìn)行修改并將其返回?cái)?shù)據(jù)源表時(shí)，就需要用到視圖。2022/7/142主要內(nèi)容 視圖概述1創(chuàng)建視圖2管理視圖3通過視圖修改數(shù)據(jù)452022/7/1436.1 視圖概述視圖是從一張或多張表中導(dǎo)出的表，所以稱為虛擬表，沒有自己的真實(shí)數(shù)據(jù)，是一些SQL查詢語句的集合，其結(jié)構(gòu)和內(nèi)容是建立在對(duì)表的查詢基礎(chǔ)之上的，和表一樣包括行和列。在SQL語句中引用視圖的方式也與引用表的方式相同。 視圖是一個(gè)虛擬表，其內(nèi)容由查詢語句定義生成。表是視圖的基礎(chǔ)。數(shù)

2、據(jù)庫中只存儲(chǔ)了視圖定義，而不存放視圖所對(duì)應(yīng)的數(shù)據(jù)，視圖所對(duì)應(yīng)的數(shù)據(jù)仍存放在視圖所引用的基表中。 視圖中的數(shù)據(jù)是視圖在被使用時(shí)動(dòng)態(tài)生成的,它隨著基表數(shù)據(jù)的變化而發(fā)生變化。2022/7/144視圖的形成 2022/7/145視圖為數(shù)據(jù)的呈現(xiàn)提供了多種多樣的表現(xiàn)形式，用戶可以通過它瀏覽表中感興趣的數(shù)據(jù)，用戶利用視圖對(duì)數(shù)據(jù)進(jìn)行操作比用戶直接對(duì)數(shù)據(jù)源表操作有較多的優(yōu)勢(shì)，其主要優(yōu)點(diǎn)如下： 簡(jiǎn)化數(shù)據(jù)操作 數(shù)據(jù)安全訪問機(jī)制 自定義所需數(shù)據(jù) 從多個(gè)表中匯總數(shù)據(jù) 通過視圖可以修改數(shù)據(jù) 2022/7/146在SQL Server 2005中視圖分為以下3類。 （1）標(biāo)準(zhǔn)視圖 （2）索引視圖 （3）分區(qū)視圖 視圖分類

3、2022/7/147創(chuàng)建視圖時(shí)的注意事項(xiàng) 創(chuàng)建者必須擁有創(chuàng)建視圖的權(quán)限，對(duì)視圖中引用的基表或視圖有許可權(quán)；只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖；但是，如果使用分布式查詢定義視圖，則新視圖所引用的表和視圖可以存在于其他數(shù)據(jù)庫甚至其他服務(wù)器中。在一個(gè)視圖中最多引用1024列，視圖中記錄的行數(shù)限制由基表中記錄數(shù)目決定； 視圖的名稱必須遵循標(biāo)識(shí)符的定義規(guī)則，對(duì)于每個(gè)用戶來說也必須唯一，而且視圖名稱不允許與該用戶擁有的表重名；視圖中列的名稱一般繼承其基表中列的名稱，如果視圖中某一列是算術(shù)表達(dá)式、函數(shù)、常量或者來自多個(gè)表的列名相同，必須要為視圖中的列重新定義名稱；2022/7/148可以將視圖創(chuàng)建在其他視圖上，SQ

4、L Server 2005中允許32層的視圖嵌套。不能在視圖上創(chuàng)建全文索引，不能將規(guī)則、默認(rèn)綁定在視圖上。不能在臨時(shí)表上創(chuàng)建視圖，也不能創(chuàng)建臨時(shí)視圖。 定義視圖的查詢語句中不能包含計(jì)算子句COMPUTE、COMPUTE BY子句和INTO關(guān)鍵字。 2022/7/1496.2 創(chuàng)建視圖用戶可以利用SSMS對(duì)象資源管理器和CREATE VIEW命令創(chuàng)建視圖。利用SSMS創(chuàng)建視圖的具體操作步驟：進(jìn)入新建視圖對(duì)話框：將要?jiǎng)?chuàng)建視圖的數(shù)據(jù)庫展開，右擊視圖圖標(biāo)，從彈出的快捷菜單中單擊“新建視圖”選項(xiàng)，進(jìn)入新建視圖對(duì)話框，如圖6-2所示。2022/7/14106.2.1 使用SQL Server Manage

5、ment Studio創(chuàng)建視圖 2022/7/1411為視圖選擇數(shù)據(jù)源 ：在打開的新建視圖對(duì)話框中，在其上部的空白處單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“添加表”選項(xiàng)，或者點(diǎn)擊“查詢?cè)O(shè)計(jì)器”菜單中的“添加表”菜單項(xiàng)，將出現(xiàn)“添加表”對(duì)話框，如圖6-3所示。2022/7/1412選擇視圖的輸出字段（表達(dá)式）：通過單擊字段左邊的復(fù)選框選擇需要的字段 。2022/7/1413單擊工具欄中的“執(zhí)行”按鈕，或者右擊視圖設(shè)計(jì)窗口的空白區(qū)域，在彈出的快捷菜單中選擇“執(zhí)行SQL”菜單項(xiàng)，則可以運(yùn)行視圖，在窗口的下面將顯示查詢結(jié)果。單擊工具欄菜單中的“保存”按鈕，或者點(diǎn)擊“文件”菜單中的“保存視圖”。 2022

6、/7/14146.2.2 使用Transact-SQL語句創(chuàng)建視圖使用CREATE VIEW語句創(chuàng)建視圖，其語法格式如下：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; := ENCRYPTION SCHEMABINDING 指定視圖所屬架構(gòu)的名稱 用于指定新建視圖的名稱 用于指定視圖中的字段的名稱 ，可省略用于創(chuàng)建視圖的SELECT語句 強(qiáng)制視圖上執(zhí)行的所有數(shù)據(jù)修改語句都必須符合由select_statement設(shè)置的準(zhǔn)則 將新建的視圖加密 2

7、022/7/1415例6.1 利用Seller表查詢銷售員的編號(hào)、姓名、性別、地址。Use sales GOCREATE VIEW dbo.V_Seller (編號(hào),姓名,性別,地址)ASSELECT SaleID, Salename, Sex, AddressFROM Seller2022/7/1416例6.2 在sales數(shù)據(jù)庫中創(chuàng)建如下視圖：利用Custmoer、Orders、OrderDetail三個(gè)表來查詢定單數(shù)量在50和100之間的客戶編號(hào)、公司名稱、產(chǎn)品編號(hào)、定單編號(hào)，訂單日期，訂單數(shù)量。CREATE VIEW V_customer ASSELECT Customer.Custo

8、merID, Customer.CompanyName, OrderDetail.ProductID, Orders.OrderID AS Order_ID, Orders.OrderDate, OrderDetail.QuantityFROM Customer INNER JOIN Orders ONCustomer.CustomerID = Orders.CustomerID INNER JOIN OrderDetail ON Orders.OrderID = OrderDetail.OrderIDWHERE (OrderDetail.Quantity 50) AND(OrderDetai

9、l.Quantity 100)2022/7/1417例6.3 在sales數(shù)據(jù)庫中創(chuàng)建如下視圖：利用OrderDetail表和Product表查詢每種產(chǎn)品銷售的總價(jià)值。USE salesGOCREATE VIEW V_Sale_Total (定單編號(hào),產(chǎn)品名稱,銷售總價(jià)值) WITH ENCRYPTIONASSELECT OrderID, ProductName, Price *QuantityFROM OrderDetail INNER JOIN Product ON OrderDetail.ProductID =Product.ProductID2022/7/14186.2.3 創(chuàng)建索引視

10、圖對(duì)于標(biāo)準(zhǔn)視圖而言，為每個(gè)引用視圖的查詢動(dòng)態(tài)生成結(jié)果集的開銷很大，特別是對(duì)于那些涉及對(duì)大量行進(jìn)行復(fù)雜處理（如聚合大量數(shù)據(jù)或聯(lián)接許多行）的視圖。如果在查詢中頻繁地引用這類視圖，可通過對(duì)視圖創(chuàng)建唯一聚集索引來提高性能。對(duì)視圖創(chuàng)建唯一聚集索引后，結(jié)果集將存儲(chǔ)在數(shù)據(jù)庫中，就像帶有聚集索引的表一樣。2022/7/1419創(chuàng)建索引視圖有許多限制條件：創(chuàng)建索引視圖的前后必須確認(rèn)會(huì)話的 SET 選項(xiàng)的設(shè)置是否正確；必須使用 WITH SCHEMABINDING 選項(xiàng)創(chuàng)建視圖，語句中引用到的對(duì)象必須由架構(gòu)名和對(duì)象名兩部分組成；在視圖上必須先創(chuàng)建唯一的聚集索引等。 2022/7/1420例6.4 本例中創(chuàng)建了數(shù)據(jù)

11、源表student,以及基于該表的索引視圖view_stu。CREATE TABLE student(num int PRIMARY KEY, name varchar(8),class_name varchar(20)GOCREATE VIEW view_stu WITH SCHEMABINDING ASSELECT num, name, class_nameFROM dbo.student WHERE num BETWEEN 1 AND 100GOCREATE UNIQUE CLUSTERED INDEX index_num ON view_stu(num)2022/7/14216.2.4

12、 創(chuàng)建分區(qū)視圖分區(qū)視圖是通過對(duì)成員表使用 UNION ALL 所定義的視圖，這些成員表的結(jié)構(gòu)相同，但作為多個(gè)表分別存儲(chǔ)在同一個(gè) SQL Server 實(shí)例中，或存儲(chǔ)在稱為聯(lián)合數(shù)據(jù)庫服務(wù)器的自主 SQL Server 服務(wù)器實(shí)例組中。 本地分區(qū)視圖：本地分區(qū)視圖中的所有參與表和視圖都位于同一個(gè) SQL Server 實(shí)例上。分布式分區(qū)視圖：分布式分區(qū)視圖中，至少有一個(gè)參與表位于不同的（遠(yuǎn)程）服務(wù)器上。2022/7/1422例：Customer表的數(shù)據(jù)分布在三個(gè)服務(wù)器位置的三個(gè)成員表中：Server1 上的 Customer_33、Server2 上的 Customer_66 和 Server3

13、上的 Customer_99。 那么在Server1 的分區(qū)視圖是通過以下方式定義的：CREATE VIEW view_CustomerASSELECT * FROM CompanyData.dbo.Customer_33UNION ALLSELECT * FROM Server2.CompanyData.dbo.Customer_66UNION ALLSELECT * FROM Server3.CompanyData.dbo.Customer_99服務(wù)器名數(shù)據(jù)庫名表名2022/7/14236.3 管理視圖查看視圖信息 使用sp_help顯示視圖的特征 例6.5 顯示視圖view_stu的特征

14、信息。USE studentGO sp_help view_stu2022/7/14242022/7/1425使用sp_helptext顯示視圖在系統(tǒng)表中的定義 例6.6 顯示視圖view_stu的在系統(tǒng)表中的定義。Use studentGo sp_helptext view_stu2022/7/1426使用sp_depends顯示視圖對(duì)表的依賴關(guān)系和引用的字段例6.7 顯示視圖view-stu的表依賴關(guān)系和引用的字段情況。Use studentGo sp_depends view_stu 2022/7/1427修改視圖ALTER VIEW schema_name . view_name (c

15、olumn ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION ; 2022/7/1428例6.8 利用ALTER命令去除視圖V_Sale_Total的加密屬性。Use sales GOAlter view V_Sale_Total (定單編號(hào), 產(chǎn)品名稱,銷售總價(jià)值)ASSELECT OrderID,ProductName, Price * Quantity FROM OrderDetail INNER JOIN Product ON OrderDetail.ProductID = Product.ProductID2022/7/1429

16、重命名視圖和刪除視圖重命名視圖sp_rename old_name, new_name例6.9 將視圖V_customer重命名為V_customer1sp_rename V_customer, V_customer1 刪除視圖 ：DROP VIEW view ,.n 例6.10 刪除視圖V_Customer。DROP VIEW V_Customer刪除視圖時(shí)，底層數(shù)據(jù)表不受影響，但會(huì)造成與該視圖關(guān)聯(lián)的權(quán)限丟失。2022/7/1430先判斷，再刪除，避免重復(fù)刪除出錯(cuò)use studentgoif EXISTS(select name from sys.views where name=view

17、_stu) 先判斷如果系統(tǒng)中有名為view_stu 的視圖，再刪除DROP view view_stu2022/7/14316.4 通過視圖修改數(shù)據(jù)可以利用視圖對(duì)創(chuàng)建它的數(shù)據(jù)源（表或其他視圖）進(jìn)行一定的修改，但使用視圖修改數(shù)據(jù)源時(shí)需要注意以下幾點(diǎn)： 對(duì)視圖中的數(shù)據(jù)進(jìn)行修改（包括 UPDATE、INSERT 和 DELETE ），不能同時(shí)修改兩個(gè)或多個(gè)基表中的數(shù)據(jù)。不能修改那些通過表達(dá)式計(jì)算得到的字段值，例如包含計(jì)算值或者聚合函數(shù)的字段。若用戶在創(chuàng)建視圖時(shí)，指定了WITH CHECK OPTION 選項(xiàng)，那么對(duì)視圖進(jìn)行UPDAT或INSERT操作時(shí)，要保證更新或插入的數(shù)據(jù)滿足視圖定義的范圍。用戶

18、如果想通過視圖執(zhí)行更新和刪除命令時(shí)，則要操作的數(shù)據(jù)必須包含在視圖的結(jié)果集中，否則不能完成該操作。2022/7/14326.4.1 利用視圖插入新記錄插入新記錄時(shí)須注意以下內(nèi)容：1）如果新插入的記錄不符合視圖定義的查詢條件，但是視圖定義時(shí)沒有使用WITH CHECK OPTION選項(xiàng)，那么該記錄仍然可以通過視圖插入到基表中，但再次運(yùn)行視圖時(shí)，新記錄不會(huì)在視圖中顯示。2）如果在視圖的定義中使用了WITH CHECK OPTION選項(xiàng)，則當(dāng)向視圖中插入不符合視圖查詢條件的記錄時(shí)，則系統(tǒng)報(bào)錯(cuò)。3）如果基表中定義了某些約束條件或觸發(fā)器，當(dāng)插入的記錄違反這些限制時(shí)，則不能將該記錄插入到視圖和基表中。202

19、2/7/1433例6.11 首先基于Product表創(chuàng)建一個(gè)新視圖文件V_stocks，要求包含庫存stocks值在300以上的記錄。CREATE VIEW V_stocks ASSELECT *FROM Product WHERE stocks300創(chuàng)建視圖后，輸入以下命令，向視圖中插入一條新數(shù)據(jù)。INSERT INTO V_stocks VALUES (P03008,冰糖,3,6.0,200) 2022/7/1434給 使用了WITH CHECK OPTION選項(xiàng)的視圖 添加數(shù)據(jù)例6.12 CREATE VIEW V_stocks ASSELECT *FROM Product WHERE stocks300WITH CHECK OPTION創(chuàng)建視圖后，輸入以下命令，向視圖中插入一條新數(shù)據(jù)。INSERT INTO V_stockSVALUES (P03010,雪餅,3,15.0,200)SELECT * FROM V_stocks查看是否有剛插入的記錄2022/7/14356.4.2 利用視圖更新記錄使用視圖可以更新數(shù)據(jù)，但更新的只是數(shù)據(jù)庫中基表的數(shù)據(jù)記錄。更新的列應(yīng)屬于