數(shù)據(jù)庫(kù) 第9章視圖、索引和游標(biāo)

1、河北工程大學(xué)河北工程大學(xué) 信電學(xué)院信電學(xué)院 崔冬崔冬 數(shù)據(jù)庫(kù)原理與數(shù)據(jù)庫(kù)原理與SQL Server 2005SQL Server 2005應(yīng)用應(yīng)用 SQL Server 2005SQL Server 2005 1 2 3 4 1 SQL Server 2005SQL Server 2005 基本表基本表1 基本表基本表2 視圖視圖 圖圖9-1 視圖概念示意圖視圖概念示意圖 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 通常在以下情況下使用視圖：通常在以下情況下使用視圖： 1) 著重于特定數(shù)據(jù)。著重于特定數(shù)據(jù)。 2)

2、簡(jiǎn)化數(shù)據(jù)操作。簡(jiǎn)化數(shù)據(jù)操作。 3) 自定義數(shù)據(jù)。自定義數(shù)據(jù)。 4) 數(shù)據(jù)的導(dǎo)入與導(dǎo)出。數(shù)據(jù)的導(dǎo)入與導(dǎo)出。 5) 跨服務(wù)器組合分區(qū)數(shù)據(jù)庫(kù)跨服務(wù)器組合分區(qū)數(shù)據(jù)庫(kù) SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 CREATE VIEW CREATE VIEW （視圖列名表）（視圖列名表） AS AS 子查詢(xún)語(yǔ)句子查詢(xún)語(yǔ)句 SQL Server 2005SQL Server 2005 定義視圖 注意：注意

3、： 子查詢(xún)中通常不包含子查詢(xún)中通常不包含ORDER BYORDER BY和和DISTINCTDISTINCT子句。子句。 在定義視圖時(shí)要么指定全部視圖列，要么全部省在定義視圖時(shí)要么指定全部視圖列，要么全部省 略不寫(xiě)。如果省略了視圖的屬性列名，則視圖的略不寫(xiě)。如果省略了視圖的屬性列名，則視圖的 列名與子查詢(xún)列名相同。但在如下三種情況下必列名與子查詢(xún)列名相同。但在如下三種情況下必 須明確指定組成視圖的所有列名：須明確指定組成視圖的所有列名： u某個(gè)目標(biāo)列是計(jì)算函數(shù)或列表達(dá)式；某個(gè)目標(biāo)列是計(jì)算函數(shù)或列表達(dá)式； u多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段；多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段； u

4、需要在視圖中為某個(gè)列選用新的更合適的列名。需要在視圖中為某個(gè)列選用新的更合適的列名。 SQL Server 2005SQL Server 2005 1.定義單源表視圖 l單源表視圖指的是數(shù)據(jù)取自一個(gè)基本表的部分行、列。這樣單源表視圖指的是數(shù)據(jù)取自一個(gè)基本表的部分行、列。這樣 定義的視圖可以進(jìn)行查詢(xún)和修改數(shù)據(jù)操作。定義的視圖可以進(jìn)行查詢(xún)和修改數(shù)據(jù)操作。 例例1 1建立信息系學(xué)生的視圖。建立信息系學(xué)生的視圖。 CREATE VIEW IS_StudentCREATE VIEW IS_Student ASAS SELECT SELECT SnoSno, , SnameSname, Sage, Sag

5、e FROM Student WHERE FROM Student WHERE SdeptSdept = = 信息系信息系 DBMSDBMS執(zhí)行執(zhí)行CREATE VIEWCREATE VIEW語(yǔ)句的結(jié)果只是保存視圖的定義，語(yǔ)句的結(jié)果只是保存視圖的定義， 只有在對(duì)視圖執(zhí)行查詢(xún)時(shí)，才按視圖的定義從相應(yīng)基本表中只有在對(duì)視圖執(zhí)行查詢(xún)時(shí)，才按視圖的定義從相應(yīng)基本表中 查詢(xún)數(shù)據(jù)。查詢(xún)數(shù)據(jù)。 SQL Server 2005SQL Server 2005 2定義多源表視圖定義多源表視圖 多源表視圖指的是定義視圖的子查詢(xún)的源表可以有多源表視圖指的是定義視圖的子查詢(xún)的源表可以有 多個(gè)，這樣定義的視圖一般只用于查詢(xún)

6、，不用于修改數(shù)多個(gè)，這樣定義的視圖一般只用于查詢(xún)，不用于修改數(shù) 據(jù)。據(jù)。 例例2 2建立信息系選修了建立信息系選修了c01c01號(hào)課程的學(xué)生的視圖。號(hào)課程的學(xué)生的視圖。 CREATE VIEW V_IS_S1(CREATE VIEW V_IS_S1(SnoSno, , SnameSname, Sage, Grade), Sage, Grade) AS AS SELECT Student. SELECT Student.SnoSno, , SnameSname, Sage, Grade, Sage, Grade FROM Student JOIN SC ON Student. FROM Stud

7、ent JOIN SC ON Student.SnoSno = SC. = SC.SnoSno WHERE WHERE SdeptSdept = = 信息系信息系 AND SC.AND SC.CnoCno = c01 = c01 SQL Server 2005SQL Server 2005 3在已有視圖上定義新視圖 在視圖上建立視圖表示視圖的數(shù)據(jù)源中有視圖。作為數(shù)在視圖上建立視圖表示視圖的數(shù)據(jù)源中有視圖。作為數(shù) 據(jù)源的視圖必須是已經(jīng)建立好的。據(jù)源的視圖必須是已經(jīng)建立好的。 例例3 3建立信息系選修了建立信息系選修了c01c01號(hào)課程且成績(jī)?cè)谔?hào)課程且成績(jī)?cè)?090分以上的分以上的 學(xué)生的視圖。學(xué)

8、生的視圖。 CREATE VIEW V_IS_S2CREATE VIEW V_IS_S2 AS AS SELECT SELECT SnoSno, , SnameSname, Grade, Grade FROM V_IS_S1 FROM V_IS_S1 WHERE Grade = 90WHERE Grade = 90 SQL Server 2005SQL Server 2005 4定義帶表達(dá)式的視圖 在定義基本表時(shí)，為減少數(shù)據(jù)庫(kù)中的冗余數(shù)據(jù)，表中只在定義基本表時(shí)，為減少數(shù)據(jù)庫(kù)中的冗余數(shù)據(jù)，表中只 存放基本數(shù)據(jù)，由基本數(shù)據(jù)經(jīng)過(guò)各種計(jì)算派生出的數(shù)據(jù)一般存放基本數(shù)據(jù)，由基本數(shù)據(jù)經(jīng)過(guò)各種計(jì)算派生出的數(shù)據(jù)

9、一般 是不存儲(chǔ)的。但由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，所以定義是不存儲(chǔ)的。但由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，所以定義 視圖時(shí)可以根據(jù)需要設(shè)置一些派生屬性列，在這些派生屬性視圖時(shí)可以根據(jù)需要設(shè)置一些派生屬性列，在這些派生屬性 列中保存經(jīng)過(guò)計(jì)算的值。列中保存經(jīng)過(guò)計(jì)算的值。 例例4 4定義一個(gè)反映學(xué)生出生年份的視圖。定義一個(gè)反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S(CREATE VIEW BT_S(SnoSno, , SnameSname, , SbirthSbirth) ) AS AS SELECT SELECT SnoSno, , SnameSname, 2002-Sage , 20

10、02-Sage FROM Student FROM Student SQL Server 2005SQL Server 2005 5含分組統(tǒng)計(jì)信息的視圖 含分組統(tǒng)計(jì)信息的視圖是指視圖的子查詢(xún)中含有含分組統(tǒng)計(jì)信息的視圖是指視圖的子查詢(xún)中含有GROUP GROUP BYBY子句，這樣的視圖只能用于查詢(xún)，不能用于修改數(shù)據(jù)。子句，這樣的視圖只能用于查詢(xún)，不能用于修改數(shù)據(jù)。 例例5 5定義一個(gè)存放每個(gè)學(xué)生的學(xué)號(hào)及平均成績(jī)的視圖。定義一個(gè)存放每個(gè)學(xué)生的學(xué)號(hào)及平均成績(jī)的視圖。 CREATE VIEW S_G(CREATE VIEW S_G(SnoSno, , AverageGradeAverageGrade

11、) ) AS SELECT AS SELECT SnoSno, AVG(Grade) FROM SC, AVG(Grade) FROM SC GROUP BY GROUP BY Sno Sno 注意：如果子查詢(xún)的選擇列表包含表達(dá)式或統(tǒng)計(jì)函數(shù)，而且注意：如果子查詢(xún)的選擇列表包含表達(dá)式或統(tǒng)計(jì)函數(shù)，而且 在子查詢(xún)中也沒(méi)有為這樣的列指定列標(biāo)題，則在定義視圖的在子查詢(xún)中也沒(méi)有為這樣的列指定列標(biāo)題，則在定義視圖的 語(yǔ)句中必須要指定視圖屬性列的名字。語(yǔ)句中必須要指定視圖屬性列的名字。 SQL Server 2005SQL Server 2005 CREATE VIEW CREATE VIEW ( (Snam

12、eSname, , CnameCname, Grade), Grade) AS SELECT AS SELECT SnameSname, , CnameCname, Grade, Grade FROM Student JOIN SC ON Student. FROM Student JOIN SC ON Student.SnoSno = SC. = SC.SnoSno JOIN Course ON Course.JOIN Course ON Course.CnoCno = SC. = SC.CnoCno SQL Server 2005SQL Server 2005 修改修改 數(shù)據(jù)時(shí)，若視圖依賴(lài)

13、于多個(gè)基本表，那么一次修改只能修數(shù)據(jù)時(shí)，若視圖依賴(lài)于多個(gè)基本表，那么一次修改只能修 改一個(gè)基本表中的數(shù)據(jù)。刪除數(shù)據(jù)時(shí)，若視圖依賴(lài)于多個(gè)改一個(gè)基本表中的數(shù)據(jù)。刪除數(shù)據(jù)時(shí)，若視圖依賴(lài)于多個(gè) 基本表，那么不能通過(guò)視圖刪除數(shù)據(jù)基本表，那么不能通過(guò)視圖刪除數(shù)據(jù)。 SQL Server 2005SQL Server 2005 常見(jiàn)錯(cuò)誤常見(jiàn)錯(cuò)誤 例例1 1： INSERT INTO V_IS_S1 (Sno,Sname,Grade) VALUES (9512107,張星張星,18) 例例2 2： SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server

14、 2005 例例1 1建立信息系學(xué)生的視圖。建立信息系學(xué)生的視圖。 CREATE VIEW IS_StudentCREATE VIEW IS_Student ASAS SELECT SELECT SnoSno, , SnameSname, Sage, Sage FROM Student WHERE FROM Student WHERE SdeptSdept = = 信息系信息系 ALTER VIEW IS_StudentALTER VIEW IS_Student AS AS SELECT SELECT SnoSno, , SnameSname, Sage, Sage FROM Student

15、WHERE FROM Student WHERE SdeptSdept=信息系信息系 AND AND ssexssex=男男 SQL Server 2005SQL Server 2005 例例8 8刪除刪除IS_StudentIS_Student視圖。視圖。 DROP VIEW IS_StudentDROP VIEW IS_Student 注意注意: :如果被刪除的視圖是作為其他視圖的數(shù)據(jù)源，則導(dǎo)出視如果被刪除的視圖是作為其他視圖的數(shù)據(jù)源，則導(dǎo)出視 圖將無(wú)法再使用了。同樣，如果作為視圖的基本表被刪除圖將無(wú)法再使用了。同樣，如果作為視圖的基本表被刪除 了，則視圖也將無(wú)法使用。了，則視圖也將無(wú)法使

16、用。 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 其語(yǔ)法格式如下：其語(yǔ)法格式如下： DROP INDEX table_name . index_name 【例例9-11】刪除【例】刪除【例9-8】創(chuàng)建的索引?！縿?chuàng)建的索引。 DROP INDEX 教師表教師表.index_

17、教師姓名教師姓名 GO SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005 2.根據(jù)T-SQL服務(wù)器游標(biāo)的處理特性，分為四種： (1) 靜態(tài)游標(biāo)靜態(tài)游標(biāo) 靜態(tài)游標(biāo)總是按照打開(kāi)游標(biāo)時(shí)的原樣顯示結(jié)果集。靜態(tài)游標(biāo)總是按照打開(kāi)游標(biāo)時(shí)的原樣顯示結(jié)果集。 (2) 動(dòng)態(tài)游標(biāo)動(dòng)態(tài)游標(biāo) 動(dòng)態(tài)游標(biāo)與靜態(tài)游標(biāo)相對(duì)。當(dāng)滾動(dòng)游標(biāo)時(shí)，動(dòng)態(tài)游標(biāo)反映結(jié)果動(dòng)態(tài)游標(biāo)與靜態(tài)游標(biāo)相對(duì)。當(dāng)滾動(dòng)游標(biāo)時(shí)，動(dòng)態(tài)游標(biāo)反映結(jié)果 集中所做的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員在集中所做的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員在 每次提取時(shí)都會(huì)改變。每次提取時(shí)都會(huì)改變。 (3) 只進(jìn)游標(biāo)只進(jìn)游標(biāo) 只進(jìn)游標(biāo)不支持滾動(dòng)，它只支持游標(biāo)從頭到尾順序提取。只進(jìn)游標(biāo)不支持滾動(dòng)，它只支持游標(biāo)從頭到尾順序提取。 (4) 由鍵集驅(qū)動(dòng)游標(biāo)由鍵集驅(qū)動(dòng)游標(biāo) 打開(kāi)由鍵集驅(qū)動(dòng)的游標(biāo)時(shí)，該游標(biāo)中各行的成員身份和順序是打開(kāi)由鍵集驅(qū)動(dòng)的游標(biāo)時(shí)，該游標(biāo)中各行的成員身份和順序是 固定的。固定的。 SQL Server 2005SQL Server 2005 SQL Server 2005SQL Server 2005