使用SQL語句創(chuàng)建視圖1

1、任務(wù)任務(wù)5：創(chuàng)建視圖和更新視圖數(shù)據(jù)：創(chuàng)建視圖和更新視圖數(shù)據(jù)使用使用management studio創(chuàng)建視圖；創(chuàng)建視圖；使用使用CREATE VIEW語句創(chuàng)建視圖；語句創(chuàng)建視圖；視圖的管理：查看視圖信息、修改視圖、刪除視圖、重命視圖的管理：查看視圖信息、修改視圖、刪除視圖、重命名視圖等；名視圖等；使用視圖對表數(shù)據(jù)操作：查詢、添加、修改、刪除等。使用視圖對表數(shù)據(jù)操作：查詢、添加、修改、刪除等。第一部分第一部分創(chuàng)建視圖創(chuàng)建視圖1.視圖概述視圖概述視圖是一種常用的數(shù)據(jù)庫對象，是從視圖是一種常用的數(shù)據(jù)庫對象，是從一個(gè)或多個(gè)基表中導(dǎo)出的表，其結(jié)構(gòu)一個(gè)或多個(gè)基表中導(dǎo)出的表，其結(jié)構(gòu)和數(shù)據(jù)是對數(shù)據(jù)表進(jìn)行查詢的

2、結(jié)果。和數(shù)據(jù)是對數(shù)據(jù)表進(jìn)行查詢的結(jié)果。視圖被定義后便存儲在數(shù)據(jù)庫中，通視圖被定義后便存儲在數(shù)據(jù)庫中，通過視圖看到的數(shù)據(jù)只是存放在基表中過視圖看到的數(shù)據(jù)只是存放在基表中的數(shù)據(jù)。的數(shù)據(jù)。視圖的概念視圖的概念視圖不是真實(shí)存在的基礎(chǔ)表（基表），視圖不是真實(shí)存在的基礎(chǔ)表（基表），而是一張而是一張?zhí)摫怼Ｌ摫怼?通過視圖看到的數(shù)據(jù)只是存放在基表通過視圖看到的數(shù)據(jù)只是存放在基表中的數(shù)據(jù)中的數(shù)據(jù) 對視圖的操作與對表的操作一樣對視圖的操作與對表的操作一樣可以對其進(jìn)行查詢、修改（有一定的可以對其進(jìn)行查詢、修改（有一定的限制）和刪除；限制）和刪除； 可以在視圖的基礎(chǔ)上繼續(xù)創(chuàng)建視圖。可以在視圖的基礎(chǔ)上繼續(xù)創(chuàng)建視圖。2.

3、視圖的用途視圖的用途1簡化查詢操作簡化查詢操作用戶可以將多個(gè)表用戶可以將多個(gè)表中感興趣的數(shù)據(jù)集中感興趣的數(shù)據(jù)集中在一個(gè)視圖中，中在一個(gè)視圖中，通過查詢視圖查看通過查詢視圖查看多個(gè)表中的數(shù)據(jù)。多個(gè)表中的數(shù)據(jù)。 2提高安全性提高安全性 為用戶創(chuàng)建一個(gè)視為用戶創(chuàng)建一個(gè)視圖，只將允許該用圖，只將允許該用戶查看的數(shù)據(jù)加入戶查看的數(shù)據(jù)加入視圖。再通過權(quán)限視圖。再通過權(quán)限的設(shè)置，使該用戶的設(shè)置，使該用戶允許訪問視圖而不允許訪問視圖而不允許訪問表。允許訪問表。3便于數(shù)據(jù)的交換便于數(shù)據(jù)的交換 先將多個(gè)表中的數(shù)先將多個(gè)表中的數(shù)據(jù)集中到一個(gè)視圖據(jù)集中到一個(gè)視圖中，再將視圖中的中，再將視圖中的數(shù)據(jù)和其他類型數(shù)數(shù)據(jù)和其

4、他類型數(shù)據(jù)庫中數(shù)據(jù)交換。據(jù)庫中數(shù)據(jù)交換。 3.使用使用management studio創(chuàng)建視圖創(chuàng)建視圖“視圖視圖”-”新建視圖新建視圖”-“添加表添加表”1創(chuàng)建視圖，查詢庫存不足的商品基本信息，結(jié)果包括創(chuàng)建視圖，查詢庫存不足的商品基本信息，結(jié)果包括條形碼、商品名稱、庫存數(shù)量。庫存不足指庫存數(shù)量少于條形碼、商品名稱、庫存數(shù)量。庫存不足指庫存數(shù)量少于30。視圖名為。視圖名為庫存不足商品庫存不足商品。4.使用使用CREATE VIEW語句創(chuàng)建視圖語句創(chuàng)建視圖CREATE VIEW 視圖名視圖名 ( 視圖列名視圖列名1 ,.視圖列名視圖列名n ) WITH ENCRYPTION AS SELECT

5、語句語句WITH CHECK OPTIONWITH ENCRYPTION：對視圖的定義進(jìn)行加密。對視圖的定義進(jìn)行加密。WITH CHECK OPTION：迫使通過視圖執(zhí)行的所有數(shù)據(jù)迫使通過視圖執(zhí)行的所有數(shù)據(jù)修改語句必須符合視圖定義中設(shè)置的條件。修改語句必須符合視圖定義中設(shè)置的條件。視圖列名視圖列名列名如未在列名如未在CREATE VIEW后指明，則由查詢語句中列字后指明，則由查詢語句中列字段組成。段組成。必須明確指明組成視圖的所有列名的三種情況必須明確指明組成視圖的所有列名的三種情況：其中某個(gè)目標(biāo)列不是單純的列名，而是列表達(dá)式或聚其中某個(gè)目標(biāo)列不是單純的列名，而是列表達(dá)式或聚合函數(shù)；合函數(shù)；多

6、表連接時(shí)，選出了幾個(gè)同名的列作為視圖的字段；多表連接時(shí)，選出了幾個(gè)同名的列作為視圖的字段；需要在視圖中為某個(gè)列啟用新的名字需要在視圖中為某個(gè)列啟用新的名字 。-1創(chuàng)建視圖，查詢庫存不足的商品基本信息，結(jié)果包創(chuàng)建視圖，查詢庫存不足的商品基本信息，結(jié)果包括條形碼、商品名稱、庫存數(shù)量。庫存不足指庫存數(shù)量少括條形碼、商品名稱、庫存數(shù)量。庫存不足指庫存數(shù)量少于于30。視圖名為。視圖名為庫存不足商品庫存不足商品。create view 庫存不足商品庫存不足商品asselect 條形碼條形碼,商品名稱商品名稱,庫存數(shù)量庫存數(shù)量from 商品商品where 庫存數(shù)量庫存數(shù)量30go-2創(chuàng)建視圖，查詢商品的銷售

7、詳細(xì)信息，結(jié)果包括銷售號、銷售創(chuàng)建視圖，查詢商品的銷售詳細(xì)信息，結(jié)果包括銷售號、銷售時(shí)間、是否會員、支付方式、會員號、條形碼、銷售數(shù)量。視圖名為時(shí)間、是否會員、支付方式、會員號、條形碼、銷售數(shù)量。視圖名為商品銷售詳細(xì)信息商品銷售詳細(xì)信息。create view 商品銷售詳細(xì)信息商品銷售詳細(xì)信息asselect 銷售銷售.銷售號銷售號,銷售時(shí)間銷售時(shí)間,是否會員是否會員,支付方式支付方式,會員號會員號,條形碼條形碼,銷售銷售數(shù)量數(shù)量from 銷售銷售,進(jìn)行銷售進(jìn)行銷售where 進(jìn)行銷售進(jìn)行銷售.銷售號銷售號=銷售銷售.銷售號銷售號go5.查看視圖的定義腳本查看視圖的定義腳本EXECUTE SP

8、_HELPTEXT 視圖名視圖名注意：如果視圖定義中使用注意：如果視圖定義中使用WITH ENCRYPTION，則無，則無法查看定義腳本。法查看定義腳本。7. 重命名視圖重命名視圖EXECUTE SP_RENAME 舊視圖名舊視圖名,新視圖名新視圖名8.刪除視圖刪除視圖刪除一個(gè)視圖，就是刪除視圖的定義及其賦予的全部權(quán)限刪除一個(gè)視圖，就是刪除視圖的定義及其賦予的全部權(quán)限,而原先通過視圖獲得的而原先通過視圖獲得的數(shù)據(jù)并沒有被刪除數(shù)據(jù)并沒有被刪除。使用使用DROP VIEW語句刪除：語句刪除：DROP VIEW 視圖名視圖名第二部分第二部分更新視圖數(shù)據(jù)更新視圖數(shù)據(jù)1.通過視圖查詢表數(shù)據(jù)通過視圖查詢表

9、數(shù)據(jù)當(dāng)定義視圖以后，用戶就當(dāng)定義視圖以后，用戶就可以像對基礎(chǔ)表進(jìn)行查詢一樣對可以像對基礎(chǔ)表進(jìn)行查詢一樣對視圖進(jìn)行查詢視圖進(jìn)行查詢了。了。 可像基礎(chǔ)表一樣用在可像基礎(chǔ)表一樣用在FROM子句中作為數(shù)據(jù)來源。子句中作為數(shù)據(jù)來源。 SELECT 列名或表達(dá)式列名或表達(dá)式 FROM 表名或視圖名表名或視圖名 WHERE 行條件表達(dá)式行條件表達(dá)式 GROUP BY 列名列名 HAVING 組條件表達(dá)式組條件表達(dá)式 ORDER BY 列名列名 ASC|DESC1重新創(chuàng)建視圖重新創(chuàng)建視圖目前商品價(jià)格目前商品價(jià)格2，查詢各商品目前的零，查詢各商品目前的零售價(jià)和會員價(jià)，結(jié)果包括條形碼、商品名稱、零售價(jià)、會員售價(jià)和

10、會員價(jià)，結(jié)果包括條形碼、商品名稱、零售價(jià)、會員價(jià)。價(jià)。提示：通過視圖提示：通過視圖目前商品價(jià)格目前商品價(jià)格1和商品表創(chuàng)建。和商品表創(chuàng)建。將目前商品價(jià)格將目前商品價(jià)格1當(dāng)成一張表來使用，當(dāng)成一張表來使用，要查找條形碼、商品名稱、零售價(jià)、會員價(jià)要查找條形碼、商品名稱、零售價(jià)、會員價(jià)將目前商品價(jià)格將目前商品價(jià)格1和商品表連接起來和商品表連接起來select dbo.商品商品.條形碼條形碼,商品名稱商品名稱,零售價(jià)零售價(jià),會員價(jià)會員價(jià)from dbo.目前商品價(jià)格目前商品價(jià)格,dbo.商品商品where 目前商品價(jià)格目前商品價(jià)格.條形碼條形碼=商品商品.條形碼條形碼create view 目前商品價(jià)格

11、目前商品價(jià)格2 asselect 商品商品.條形碼條形碼,商品名稱商品名稱,零售價(jià)零售價(jià),會員價(jià)會員價(jià)from 目前商品價(jià)格目前商品價(jià)格1,商品商品where 目前商品價(jià)格目前商品價(jià)格1.條形碼條形碼=商品商品.條形碼條形碼2.通過視圖更新表數(shù)據(jù)通過視圖更新表數(shù)據(jù)實(shí)質(zhì)實(shí)質(zhì)對視圖所引用的基礎(chǔ)表執(zhí)行數(shù)據(jù)的插入和更新對視圖所引用的基礎(chǔ)表執(zhí)行數(shù)據(jù)的插入和更新 。視圖中本沒有數(shù)據(jù)，往視圖中添加、刪除、修改數(shù)據(jù)視圖中本沒有數(shù)據(jù)，往視圖中添加、刪除、修改數(shù)據(jù)，都要轉(zhuǎn)化為表中的數(shù)據(jù)的修改，如果不能轉(zhuǎn)化，就，都要轉(zhuǎn)化為表中的數(shù)據(jù)的修改，如果不能轉(zhuǎn)化，就不能修改。所以是受限制的。不能修改。所以是受限制的。相關(guān)限制

12、相關(guān)限制在一個(gè)語句中，一次不能修改一個(gè)以上的視圖基表；在一個(gè)語句中，一次不能修改一個(gè)以上的視圖基表；對視圖中所有列的修改必須遵守視圖基表中所定義對視圖中所有列的修改必須遵守視圖基表中所定義的各種數(shù)據(jù)約束條件；的各種數(shù)據(jù)約束條件；不允許對視圖中的計(jì)算列進(jìn)行修改，也不允許對不允許對視圖中的計(jì)算列進(jìn)行修改，也不允許對視圖定義中包含有統(tǒng)計(jì)函數(shù)或視圖定義中包含有統(tǒng)計(jì)函數(shù)或GROUP BY子句的子句的視圖進(jìn)行插入或修改操作。視圖進(jìn)行插入或修改操作。2往往水果類商品信息水果類商品信息視圖中添加一條記錄：條形碼視圖中添加一條記錄：條形碼為：為：907992101279，商品名稱為：，商品名稱為：伊利紅棗酸牛奶

13、伊利紅棗酸牛奶，庫存數(shù)量為：，庫存數(shù)量為：0，商品類型為：，商品類型為：牛奶牛奶。思考：是否能添加成功？如果出錯，為什么？思考：是否能添加成功？如果出錯，為什么？消息消息550，級別，級別16，狀態(tài)，狀態(tài)1，第，第1 行行試圖進(jìn)行的插入或更新已失敗，原因是目標(biāo)視圖或者目標(biāo)試圖進(jìn)行的插入或更新已失敗，原因是目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了視圖所跨越的某一視圖指定了WITH CHECK OPTION，而該操作的一個(gè)或多個(gè)結(jié)果行又不符合而該操作的一個(gè)或多個(gè)結(jié)果行又不符合CHECK OPTION 約束。約束。語句已終止。語句已終止。-不能添加成功，不能添加成功，因?yàn)閯?chuàng)建視圖時(shí)使用因?yàn)閯?chuàng)建視圖

14、時(shí)使用With check option,通過視圖執(zhí)行的所有數(shù)據(jù)修改語句通過視圖執(zhí)行的所有數(shù)據(jù)修改語句必須符合視圖定義中設(shè)置的條件必須符合視圖定義中設(shè)置的條件,即商品類型即商品類型=水果水果。如果如果水果類商品信息水果類商品信息視圖定義時(shí)沒有視圖定義時(shí)沒有With check option，能添加成功，只是加在商品表上。，能添加成功，只是加在商品表上。水果類商品信息水果類商品信息視圖顯示的是水果類，所以不顯示伊視圖顯示的是水果類，所以不顯示伊利紅棗酸牛奶的記錄利紅棗酸牛奶的記錄3往往商品銷售詳細(xì)信息商品銷售詳細(xì)信息視圖中添加一條記錄，銷售號：視圖中添加一條記錄，銷售號：101-106-45，銷售時(shí)間：，銷售時(shí)間：2010-4-18，是否會員：，是否會員：0，支付，支付方式：方式：現(xiàn)金現(xiàn)金 ，條形碼：，條形碼：2000000341316，銷售數(shù)量：，銷售數(shù)量：5。思考：能否添加成功？如果出錯，為什么？思考：能否添加成功