SQL20052008開發(fā)實(shí)戰(zhàn)：第2章視圖的操作與管理

1、第第2章章 視圖的操作與管理視圖的操作與管理視圖概述 視圖是個(gè)虛表，是從一個(gè)或者多個(gè)表或視圖中導(dǎo)出的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上的。 使用視圖的優(yōu)點(diǎn)和作用主要有：（1）視圖可以使用戶只關(guān)心他感興趣的某些特定數(shù)據(jù)和他們所負(fù)責(zé)的特定任務(wù)，而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示。（2）視圖大大地簡化了用戶對數(shù)據(jù)的操作。（3）視圖可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集。（4）在某些情況下，由于表中數(shù)據(jù)量太大，因此在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平或者垂直分割，但表的結(jié)構(gòu)的變化對應(yīng)用程序產(chǎn)生不良的影響。而使用視圖可以重新組織數(shù)據(jù)，從而使外模式保持不變，原有的應(yīng)用程序仍可以通過視圖

2、來重載數(shù)據(jù)。（5）視圖提供了一個(gè)簡單而有效的安全機(jī)制。3.1創(chuàng)建視圖 SQL Server 2005提供了如下幾種創(chuàng)建視圖的方法：（1）用SQL SERVER管理平臺創(chuàng)建視圖；（2）用Transact-SQL語句中的CREATE VIEW命令創(chuàng)建視圖；（3）利用SQL SERVER管理平臺的視圖模板來創(chuàng)建視圖。 創(chuàng)建視圖時(shí)應(yīng)該注意以下情況：（1）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖，在視圖中最多只能引用1024列，視圖中記錄的數(shù)目限制只由其基表中的記錄數(shù)決定。（2）如果視圖引用的基表或者視圖被刪除，則該視圖不能再被使用，直到創(chuàng)建新的基表或者視圖。（3）如果視圖中某一列是函數(shù)、數(shù)學(xué)表達(dá)式、常量或者來自多個(gè)

3、表的列名相同，則必須為列定義名稱。（4）不能在視圖上創(chuàng)建索引，不能在規(guī)則、默認(rèn)、觸發(fā)器的定義中引用視圖。（5）當(dāng)通過視圖查詢數(shù)據(jù)時(shí)，SQL Server要檢查以確保語句中涉及的所有數(shù)據(jù)庫對象存在，每個(gè)數(shù)據(jù)庫對象在語句的上下文中有效，而且數(shù)據(jù)修改語句不能違反數(shù)據(jù)完整性規(guī)則。（6）視圖的名稱必須遵循標(biāo)識符的規(guī)則，且對每個(gè)用戶必須是惟一的。此外，該名稱不得與該用戶擁有的任何表的名稱相同。 3.1.1利用SQL SERVER管理平臺創(chuàng)建視圖 利用SQL SERVER管理平臺創(chuàng)建視圖的具體操作步驟如下：（1）在SQL SERVER管理平臺中，展開指定的服務(wù)器，打開要創(chuàng)建視圖的數(shù)據(jù)庫文件夾，選擇指定的數(shù)據(jù)

4、庫，右擊該數(shù)據(jù)庫圖標(biāo)，從彈出的快捷菜單中依次選擇“新建（New）視圖”選項(xiàng)，如圖3-1所示。接著就出現(xiàn)添加表、視圖、函數(shù)對話框。如圖3-2所示。圖3-1 選擇新建視圖選項(xiàng)對話框 圖3-2 添加表、視圖、函數(shù)對話框 3.1.1利用SQL SERVER管理平臺創(chuàng)建視圖 （2）選擇好創(chuàng)建視圖所需的表、視圖、函數(shù)后，通過單擊字段左邊的復(fù)選框選擇需要的字段，如圖3-3所示。單擊工具欄中的“保存”按鈕，或者單擊鼠標(biāo)右鍵，從快捷菜單中選擇保存選項(xiàng)保存視圖，輸入視圖名，即可完成視圖的創(chuàng)建。 圖3-3 選擇視圖字段對話框 3.1.2利用Transact-SQL語句中的CREATE VIEW命令創(chuàng)建視圖 使用Tr

5、ansact-SQL語句中的CREATE VIEW創(chuàng)建視圖，其語法形式如下：CREATE VIEW schema_name. view_name (column ,.n) WITH ,.nAS select_statement WITH CHECK OPTION := ENCRYPTION|SCHEMABINDING|VIEW_METADATA 3.1.2利用Transact-SQL語句中的CREATE VIEW命令創(chuàng)建視圖 例3-1選擇表s和sc中的部分字段和記錄來創(chuàng)建一個(gè)視圖，并且限制表s中的記錄只能是計(jì)算機(jī)系的記錄集合，視圖定義為view_s。程序清單如下：create view vie

6、w_sas select ,s.age,s.sex,o,sc.score from s,scwhere s.sno=sc.sno and s.dept=計(jì)算機(jī)例3-2創(chuàng)建一個(gè)視圖，使之包含復(fù)雜的查詢。程序清單如下：CREATE VIEW ExampleView WITH SCHEMABINDINGAS SELECT sno, SUM(score) AS Sumscore, COUNT(*) AS CountCol FROM sc GROUP BY sno 3.1.2利用Transact-SQL語句中的CREATE VIEW命令創(chuàng)建視圖 例3-3創(chuàng)建一個(gè)視圖，使之包含字符串的運(yùn)算。程

7、序清單如下：CREATE VIEW v_shyjl(shyxh, shj, xm, jglb, phr, bzh) ASSelectdistinct shyxh,substring(shj,1,10)+zhi, t01_shbshy.xm,t012_shyjl.jglb,t012_shyjl.phr,t012_shyjl.bzhfrom t01_shbshy, t012_shyjlwhere t012_shyjl.xm=t01_shbshy.xm其中shj列是從基表中取出兩列并和一空字符串相加而得到的。這些都為基表中字段間的靈活運(yùn)算提供了極大方便，從而使我們可以隨心所欲地定制符合自己要求的數(shù)據(jù)

8、。 3.1.3 利用模板創(chuàng)建視圖 使用視圖模板可以很容易地創(chuàng)建視圖，其具體操作步驟如下；（1）在SQL SERVER管理平臺中，選擇view菜單中的“模板資源管理器 ”選項(xiàng)，如圖3-4所示。（2）在出現(xiàn)的“模板資源管理器”選項(xiàng)中選擇“創(chuàng)建視圖”選項(xiàng)，如圖3-5所示。 （3）按照提示輸入視圖名稱，select語句后，執(zhí)行此語句，即可創(chuàng)建視圖。 圖3-4 選擇工具菜單中的向?qū)?圖3-5 創(chuàng)建視圖模板 3.2 修改、刪除和重命名視圖 3.2.1 修改視圖 3.2.2 重命名視圖3.2.3 查看視圖信息、刪除視圖 3.2.1 修改視圖 修改視圖的方法有以下兩種方法： （1）在SQL SERVER管

9、理平臺中，右擊要修改的視圖，從彈出的快捷菜單中選擇“設(shè)計(jì)視圖”選項(xiàng)，出現(xiàn)視圖修改對話框。該對話框與創(chuàng)建視圖時(shí)的對話框相同，可以按照創(chuàng)建視圖的方法修改視圖。（2）使用ALTER VIEW語句修改視圖，但首先必須擁有使用視圖的權(quán)限，然后才能使用ALTER VIEW語句，該語句的語法形式如下： ALTER VIEW view_name （column,.n） WITH ENCRYPTION AS select_statement WITH CHECK OPTION 3.2.1 修改視圖 例3-4修改了視圖V_employees，在該視圖中增加了新的字段employees.salary，并且定義一個(gè)新

10、的字段名稱e_salary。程序清單如下：alter view dbo.employees（number,name,age,e_salary）asselect number,name,age,salaryfrom employeeswhere name=張三 3.2.2 重命名視圖 重命名視圖方法有以下兩種：1. 在SQL SERVER管理平臺中，選擇要修改名稱的視圖，并右擊該視圖，從彈出的快捷菜單中選擇“重命名”選項(xiàng)?；蛘咴谝晥D上再次單擊，也可以修改視圖的名稱。接著該視圖的名稱變成可輸入狀態(tài)，可以直接輸入新的視圖名稱。2. 使用系統(tǒng)存儲過程sp_rename來修改視圖的名稱，該過程的語法形式

11、如下： sp_rename old_name,new_name例3-5把視圖v_all重命名為v_part。程序清單如下： sp_rename v_all,v_part 3.2.3 查看視圖信息、刪除視圖 1. 查看視圖信息可以使用系統(tǒng)存儲過程sp_help顯示視圖特征，使用sp_helptext顯示視圖在系統(tǒng)表中的定義，使用sp_depends顯示該視圖所依賴的對象。使用SQL Server 查詢分析器可以方便地顯示視圖屬性信息，如圖3-7所示。圖3-8顯示了使用sp_helptext存儲過程顯示視圖的創(chuàng)建語句。 圖3-7 顯示視圖屬性對話框 圖3-8 顯示視圖創(chuàng)建語句 3.2.3 查看視圖

12、信息、刪除視圖 1. 查看視圖信息利用select語句或SQL SERVER管理平臺可以查看視圖的輸出數(shù)據(jù)。在SQL SERVER管理平臺中，右擊某個(gè)視圖的名稱，從彈出的快捷菜單中選擇“打開視圖”選項(xiàng)，在SQL SERVER管理平臺中就會顯示該視圖的輸出數(shù)據(jù)，如圖3-9所示。 圖圖3-9 視圖輸出數(shù)據(jù)窗口視圖輸出數(shù)據(jù)窗口 3.2.3 查看視圖信息、刪除視圖 2. 刪除視圖對于不再使用的視圖，可以使用SQL SERVER管理平臺或者Transact-SQL語句中的DROP VIEW命令刪除它。 使用Transact-SQL語句DROP VIEW刪除視圖，其語法形式如下：DROP VIEW vie

13、w_name ,n可以使用該命令同時(shí)刪除多個(gè)視圖，只需在要刪除的各視圖名稱之間用逗號隔開即可。例3-6同時(shí)刪除視圖v_student和v_teacher。程序清單如下：drop view v_student,v_teacher3.3 通過視圖修改記錄 使用視圖修改數(shù)據(jù)時(shí)，需要注意以下幾點(diǎn)：（1）修改視圖中的數(shù)據(jù)時(shí)，不能同時(shí)修改兩個(gè)或者多個(gè)基表，可以對基于兩個(gè)或多個(gè)基表或者視圖的視圖進(jìn)行修改，但是每次修改都只能影響一個(gè)基表。（2）不能修改那些通過計(jì)算得到的字段，例如包含計(jì)算值或者合計(jì)函數(shù)的字段。（3）如果在創(chuàng)建視圖時(shí)指定了WITH CHECK OPTION選項(xiàng)，那么使用視圖修改數(shù)據(jù)庫信息時(shí)，必須

14、保證修改后的數(shù)據(jù)滿足視圖定義的范圍。（4）執(zhí)行UPDATE、DELETE命令時(shí)，所刪除與更新的數(shù)據(jù)必須包含在視圖的結(jié)果集中。（5）如果視圖引用多個(gè)表時(shí)，無法用DELETE命令刪除數(shù)據(jù)，若使用UPDATE命令則應(yīng)與INSERT操作一樣，被更新的列必須屬于同一個(gè)表。 3.3.1 插入數(shù)據(jù)記錄 例3-7創(chuàng)建一個(gè)基于表employees的新視圖v_employees。程序清單如下：create view v_employees（number, name, age, sex, salary）asselect number, name, age, sex, salary from employeeswhe

15、re name=張三執(zhí)行以下語句可向表employees中添加一條新的數(shù)據(jù)記錄：Insert into v_employeesValues（001,李力,22,m,2000） 3.3.1 插入數(shù)據(jù)記錄 例3-8首先創(chuàng)建一個(gè)包含限制條件的視圖v_employee2，限制條件為工資2000，然后插入了一條不滿足限制條件的記錄，再用SELECT語句檢索視圖和表。程序清單如下：create view v_employee2asselect * from employeewhere 工資2000goinsert into v_employee2values（002,王則,30,f,1000）gosele

16、ct * from employeegoselect * from v_employee2go3.3.1 插入數(shù)據(jù)記錄 例3-9在例子3-8的基礎(chǔ)上添加WITH CHECK OPTION選項(xiàng)。程序清單如下：create view v_employee3asselect * from employeewhere 工資2000with check optiongoinsert into v_employee3values（002,王則,30,f,1000）goselect * from v_employee3go運(yùn)行該程序?qū)@示如下出錯(cuò)信息：Server: Msg 550, Level 16, S

17、tate 1, Line 1The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.The statement has been terminated. 3.3.2 更新和刪除數(shù)據(jù)記錄 使用視圖可以更新數(shù)據(jù)記錄，但應(yīng)該注意的是，更新的只是數(shù)據(jù)庫中的基表。使用視圖刪除記錄，可以刪除任何基表中的記錄，直接利用DELETE語句刪除記錄即可。但應(yīng)該注意，必