實(shí)驗(yàn)3 交互式SQL

1、實(shí)驗(yàn)3 交互式SQL(2)實(shí)驗(yàn)?zāi)康? 熟悉SQL查詢語句。2 了解查詢執(zhí)行計(jì)劃、加深理解關(guān)系代數(shù)理論3 熟悉視圖的創(chuàng)建和使用4 熟悉訪問控制語句5 鞏固對(duì)DDL，DML語言的應(yīng)用實(shí)驗(yàn)平臺(tái)數(shù)據(jù)庫系統(tǒng)： SQL Server2000操作系統(tǒng)： Windows Professional 2000實(shí)驗(yàn)素材關(guān)于關(guān)系表。在本實(shí)驗(yàn)中，繼續(xù)使用上一個(gè)實(shí)驗(yàn)中的大學(xué)數(shù)據(jù)庫University，其中包括5個(gè)表(student, enrollment, course, offering, faculty)：student:(StdSSN,StdFirstName,StdLastName,StdCity,StdStat

2、e,StdMajor,StdClass,StdGPA,StdZip）enrollment:( OfferNo,StdSSN,EnrGrade)course:( CourseNo,CrsDesc,CrsUnits)offering:(OfferNo,CourseNo,OffTerm,OffYear,OffLocation,OffTime,FacSSN,OffDays)faculty:(FacSSN,FacFirstName,FacLastName,FacCity,FacState,FacDept,FacRank,FacSalary,FacSupervisor,FacHireDate,FacZip

3、Code)注：上述定義中，標(biāo)注下劃線的屬性是表的主碼。在表faculty中，F(xiàn)acSupervisor是faculty的外碼，屬于自引用關(guān)系。該數(shù)據(jù)庫的ER-圖為：實(shí)驗(yàn)內(nèi)容：1 建立關(guān)系表，并建立索引；導(dǎo)入數(shù)據(jù)。A. 建立關(guān)系表，注意定義數(shù)據(jù)完整性。答：在上一次實(shí)驗(yàn)中已經(jīng)建立了完整的關(guān)系表，本次實(shí)驗(yàn)繼續(xù)使用上次實(shí)驗(yàn)的數(shù)據(jù)庫。在數(shù)據(jù)庫中已經(jīng)導(dǎo)入了一些數(shù)據(jù)。B. 建立索引。在五張表的主碼上建立聚簇索引；自選在一些屬性上建立非聚簇索引；建立索引時(shí)，請(qǐng)思考索引的填充因子是什么意思，有什么作用；請(qǐng)思考索引的數(shù)據(jù)結(jié)構(gòu)有哪些？可否指定索引的數(shù)據(jù)結(jié)構(gòu)？如果不可以，請(qǐng)說明為什么？如果可以，請(qǐng)說明你的操作過程或S

4、QL操作。答：（1）經(jīng)過查詢SQL Server聯(lián)機(jī)叢書知道，在SQL Server中，存在三種類型的索引，UNIQUE、CLUSTERED和NONCLUSTERED三種類型，并且UNIQUE類型可以與CLUSTERED或NONCLUSTERED可以組合使用，例如UNIQUE CLUSTERED類型。為此，在主碼上建立CLUSTERED索引，在其他一些屬性列中建立非聚簇索引。首先，在五個(gè)表的主碼上建立聚簇索引，其SQL語句為：CREATE CLUSTERED INDEX CourseInd ON Course(CourseNo) with FILLFACTOR = 100CREATE CLUS

5、TERED INDEX EnrollmentInd ON Enrollment(OfferNo) with FILLFACTOR = 100CREATE CLUSTERED INDEX FacultyInd ON Faculty(FacSSN, StdSSN) with FILLFACTOR = 100CREATE CLUSTERED INDEX OfferingInd ON Offering(OfferNo) with FILLFACTOR = 100CREATE CLUSTERED INDEX StudentInd ON Student(StdSSN) with FILLFACTOR =

6、100（2）a.然后在Course表上建立CrsDesc屬性的非聚簇索引，并以升序排列，為此設(shè)計(jì)SQL語句如下：CREATE UNIQUE NONCLUSTERED INDEX CrsDescInd ON Course(CrsDesc ASC)b. 在Enrollment表上建立EnrGrade屬性的非聚簇索引，并使EnrGrade已降序排列，設(shè)計(jì)SQL語句如下：CREATE NONCLUSTERED INDEX EnrGradeInd ON Enrollment(EnrGrade DESC)c. 在Faculty 表上建立FacNameInd屬性的非聚簇索引，并使FacFirstName和F

7、acLastName的升序排列索引，設(shè)計(jì)SQL語句如下：CREATE UNIQUE NONCLUSTERED INDEX FacNameInd ON Faculty(FacFirstName ASC, FacLastName ASC)d. 在 Offering表上建立OffYear的UINQUE NONCLUSTERED索引，為此設(shè)計(jì)SQL語句：CREATE UNIQUE NONCLUSTERED INDEX OffYearInd ON Offering(OffYear DESC)語句分析正確，但在執(zhí)行時(shí)報(bào)錯(cuò)如下：服務(wù)器: 消息 1505，級(jí)別 16，狀態(tài) 1，行 1CREATE UNIQUE

8、 INDEX 終止，因?yàn)榘l(fā)現(xiàn)了索引 ID 2 的重復(fù)鍵。最重要的主鍵為 2003。語句已終止。原來UNIQUE只能建立單值索引，而OffYear屬性列中存在重復(fù)值，因此不能在該屬性列上建立UNIQUE索引。去掉UNIQUE后，重新執(zhí)行，建立索引成功。e.在Student表的StdFirstName和StdLastName上建立升序索引，在StdGPA上建立降序索引。其SQL語句如下：CREATE NONCLUSTERED INDEX StdNameInd ON Student(StdFirstName ASC, StdLastName ASC)CREATE NONCLUSTERED INDEX

9、 StdGPAInd ON Student(StdGPA DESC)（3）建立索引時(shí)，請(qǐng)思考索引的填充因子是什么意思，有什么作用；請(qǐng)思考索引的數(shù)據(jù)結(jié)構(gòu)有哪些？可否指定索引的數(shù)據(jù)結(jié)構(gòu)？如果不可以，請(qǐng)說明為什么？如果可以，請(qǐng)說明你的操作過程或SQL操作。答：在建立索引時(shí)，可以指定索引的填充因子，其定義為：在系統(tǒng)中，填充因子的作用為：創(chuàng)建索引時(shí)，可以指定一個(gè)填充因子，以便在索引的每個(gè)葉級(jí)頁上留出額外的間隙和保留一定百分比的空間，供將來表的數(shù)據(jù)存儲(chǔ)容量進(jìn)行擴(kuò)充和減少頁拆分的可能性。填充因子的值是從 0 到 100 的百分比數(shù)值，指定在創(chuàng)建索引后對(duì)數(shù)據(jù)頁的填充比例。值為 100 時(shí)表示頁將填滿，所留出的

10、存儲(chǔ)空間量最小。只有當(dāng)不會(huì)對(duì)數(shù)據(jù)進(jìn)行更改時(shí)（例如，在只讀表中）才會(huì)使用此設(shè)置。值越小則數(shù)據(jù)頁上的空閑空間越大，這樣可以減少在索引增長過程中對(duì)數(shù)據(jù)頁進(jìn)行拆分的需要，但需要更多的存儲(chǔ)空間。當(dāng)表中數(shù)據(jù)會(huì)發(fā)生更改時(shí)，這種設(shè)置更為適當(dāng)。根據(jù)所學(xué)的數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)庫的知識(shí)，索引的作用就是進(jìn)行快速檢索，所以任何高效的檢索數(shù)據(jù)結(jié)構(gòu)都可以作為索引的數(shù)據(jù)結(jié)構(gòu)。因此索引數(shù)據(jù)結(jié)構(gòu)可以有二叉樹、哈希表、倒排表、B樹和B+樹等。不可以?；蛟S現(xiàn)在我還沒有發(fā)現(xiàn)。c.導(dǎo)入數(shù)據(jù)。答：在數(shù)據(jù)庫中已經(jīng)導(dǎo)入了一些數(shù)據(jù)，并且由手工加入了一些數(shù)據(jù)。出現(xiàn)的問題：在進(jìn)行導(dǎo)入數(shù)據(jù)時(shí)，有一次導(dǎo)入Enrollment表的數(shù)據(jù)總是出錯(cuò)，后來發(fā)現(xiàn)原來在進(jìn)

11、行查詢時(shí)，刪去了Faculty表中FacSSN為1234的一行，而Enrollment表中引用了FacSSN作為外碼，而在要導(dǎo)入的數(shù)據(jù)中含有FacSSN為1234的數(shù)據(jù)，從而使原來合法的數(shù)據(jù)變成了非法的，導(dǎo)致倒入數(shù)據(jù)失敗。結(jié)論：導(dǎo)入數(shù)據(jù)時(shí)一定要注意數(shù)據(jù)的合法性，違反了任何一條完整性約束條件的數(shù)據(jù)都會(huì)導(dǎo)致整個(gè)表的數(shù)據(jù)導(dǎo)入失敗。2簡單查詢a. 在單表上進(jìn)行查詢，查看某個(gè)或多個(gè)特定屬性.答：（1）建立一個(gè)非常簡單的查詢，查詢University數(shù)據(jù)庫Student表中StdMajor為IS的學(xué)生的所有信息，設(shè)計(jì)SQL語句如下：SELECT *FROM studentWHERE stdmajor =

12、IS;察看其執(zhí)行計(jì)劃，發(fā)現(xiàn)其查詢的方法為聚集索引：（2）考慮到全表掃描的效率比較低，在要查詢的StdMajor屬性上建立非聚簇索引，其SQL語句為CREATE NONCLUSTERED INDEX StdMajorInd ON Student(StdMajor);建立索引后，繼續(xù)進(jìn)行剛才的查詢，察看其查詢計(jì)劃為：對(duì)比兩個(gè)查詢計(jì)劃，第一種查詢的查詢成本要遠(yuǎn)高于第二種查詢，這說明即使是非聚簇索引，在進(jìn)行單表簡單查詢時(shí)，其查詢效率也要高于聚集索引：操作Clustered Index ScanBookmark Lookup + Index Seek預(yù)計(jì)行計(jì)數(shù)77預(yù)計(jì)行大小131 150 + 45 =

13、195預(yù)計(jì)I/O成本0.0375 0.00625 + 0.00632 = 0.01257預(yù)計(jì)CPU成本0.0. + 0. = 0.預(yù)計(jì)執(zhí)行次數(shù)11預(yù)計(jì)成本0.037670. + 0. = 0.預(yù)計(jì)子樹成本0.0376 0.0126 + 0.00641 = 0.0767（3）選擇一個(gè)數(shù)據(jù)量最大的表Enrollment進(jìn)行查詢，查詢語句如下：SELECT StdSSN, EnrGradeFROM EnrollmentWHERE OfferNo = 4321其查詢計(jì)劃為：查詢結(jié)果為: 結(jié)論：注意到查詢計(jì)劃中預(yù)計(jì)行計(jì)數(shù)為15列，而查詢結(jié)果僅為6列，說明查詢計(jì)劃的信息并不是十分準(zhǔn)確。在執(zhí)行計(jì)劃中會(huì)顯示出

14、工具提示信息：其具體各項(xiàng)含義為：當(dāng)將游標(biāo)指向每個(gè)節(jié)點(diǎn)時(shí)，該節(jié)點(diǎn)顯示工具提示信息。工具提示信息可能包括： a) 使用的物理運(yùn)算符（物理操作），例如 Hash Join 或 Nested Loops。以紅色顯示的物理運(yùn)算符表示查詢優(yōu)化器已發(fā)出警告，例如丟失列統(tǒng)計(jì)或丟失聯(lián)接謂詞。這可能導(dǎo)致查詢優(yōu)化器選擇比預(yù)期的效率低的查詢計(jì)劃。有關(guān)列統(tǒng)計(jì)的更多信息，請(qǐng)參見統(tǒng)計(jì)信息。圖形執(zhí)行計(jì)劃建議補(bǔ)救操作，例如創(chuàng)建或更新統(tǒng)計(jì)或者創(chuàng)建索引。使用 SQL 查詢分析器的上下文菜單，可以立即創(chuàng)建或更新缺少的列統(tǒng)計(jì)和索引。b) 與物理運(yùn)算符匹配的邏輯運(yùn)算符（邏輯操作），如 Join 運(yùn)算符。如果邏輯運(yùn)算符與物理運(yùn)算符不同，將

15、在工具提示的頂端列在物理運(yùn)算符之后，并用正斜杠 (/) 分開。c) 由運(yùn)算符輸出的行數(shù)（行計(jì)數(shù)）。d) 由運(yùn)算符輸出的行的預(yù)計(jì)大小（預(yù)計(jì)行大?。?。e) 用于操作的所有 I/O 活動(dòng)的預(yù)計(jì)成本（I/O 成本）。該值應(yīng)盡可能低。f) 用于操作的所有 CPU 活動(dòng)的預(yù)計(jì)成本（CPU 成本）。g) 查詢時(shí)執(zhí)行操作的次數(shù)（執(zhí)行次數(shù)）。h) 查詢優(yōu)化器執(zhí)行此操作的成本（成本），包括此操作的成本占查詢總成本的百分比。由于查詢引擎選擇最高效的操作執(zhí)行查詢或執(zhí)行語句，因此該值應(yīng)盡可能低。i) 查詢優(yōu)化器執(zhí)行此操作及同一子樹內(nèi)位于此操作之前的所有操作的總成本（子樹成本）。j) 查詢所使用的謂詞和參數(shù)（參數(shù)）。 注

16、：以上信息摘自SQL Server聯(lián)機(jī)從書。（4）單表查詢多個(gè)屬性列，查詢條件也為多個(gè)屬性列，設(shè)計(jì)查詢語句如下：SELECT FacFirstName, FacLastName, FacCity, FacHireDate FROM Faculty WHERE year(FacHireDate) 1991起執(zhí)行結(jié)果為：其執(zhí)行計(jì)劃為，兩者的行數(shù)完全不同，再次說明統(tǒng)計(jì)信息不一定準(zhǔn)確：（）使用LIKE進(jìn)行查詢，查詢Offering表中CourseNo中含有IS的行，設(shè)計(jì)SQL語句如下：SELECT * FROM Offering WHERE CourseNo LIKE IS%查詢結(jié)果為：執(zhí)行計(jì)劃為：b

17、. 使用排序操作Order By答：查詢Enrollment表的所有行，結(jié)果按EnrGrade的降序排列，設(shè)計(jì)查詢語句如下：SELECT *FROM EnrollmentORDER BY EnrGrade DESC觀察其執(zhí)行計(jì)劃圖為： 結(jié)論：使用ORDER BY語句后，系統(tǒng)先對(duì)表進(jìn)行聚集索引查詢，然后進(jìn)行排序。疑問：假如在EnrGrade上建立非聚集索引，系統(tǒng)是否還會(huì)利用主索引？（）為此，在EnrGrade上建立非聚集索引，其SQL語句為：CREATE NONCLUSTERED INDEX EnrGradeInd ON Enrollment(EnrGrade DESC)觀察建立索引的執(zhí)行計(jì)劃圖

18、為：然后重新執(zhí)行以上查詢：觀察執(zhí)行計(jì)劃圖如下：對(duì)比兩次查詢，可以發(fā)現(xiàn)，盡管這一次使用了ORDER BY，但是并沒有進(jìn)行SORT，所以效率上也高了許多。結(jié)論：使用ORDER BY并不一定要進(jìn)行SORT，如果已經(jīng)建立的索引上已經(jīng)經(jīng)過了排序，則系統(tǒng)并不會(huì)進(jìn)行SORT操作。 使用聚集索引的效率并不一定高于非聚集索引。 當(dāng)屬性列上建立有索引時(shí)，系統(tǒng)一般都會(huì)利用索引進(jìn)行查詢。c. 使用分組操作Group by、having；并作相關(guān)的集函數(shù)查詢；例如sum，count，avg，max，min等答：（1）對(duì)Faculty和Offering兩表作查詢，查詢兩表中FacSSN相同的，并且OffYear都是200

19、3的Faculty，并按照FacSSN和FacDept進(jìn)行分組，然后選出行數(shù)大于1的元組的FacSSN和FacDept，設(shè)計(jì)SQL語句如下：SELECT Faculty.FacSSN, FacDept FROM Faculty, Offering WHERE Offering.FacSSN = Faculty.FacSSN AND OffYear = 2003 GROUP BY Faculty.FacSSN, FacDept HAVING COUNT(*) 1察看其執(zhí)行計(jì)劃，可以看到其中大部分代價(jià)是用于排序：為了驗(yàn)證在已經(jīng)排過序的情況下，繼續(xù)進(jìn)行ORDER BY操作會(huì)怎樣，在其查詢語句的最后面

20、加上ORDER BY FacSSN，重新察看其執(zhí)行計(jì)劃，發(fā)現(xiàn)與上圖完全相同，說明SQL Server并不會(huì)做重復(fù)的排序工作。結(jié)論：SQL Server中排序的代價(jià)很高，但大多數(shù)情況下，系統(tǒng)都不會(huì)做重復(fù)的排序。（2）sum，avg，max，min的使用。答：為了驗(yàn)證sum的使用，查詢IS系所開設(shè)的課程的總學(xué)分?jǐn)?shù)，設(shè)計(jì)SQL語句如下：SELECT sum(CrsUnits) as SumOfCourseFROM CourseWHERE CourseNo LIKE IS%察看其執(zhí)行計(jì)劃為：執(zhí)行結(jié)果為：結(jié)果為40，因?yàn)镃rsUnits屬性列中有大量NULL值，可以看到，sum函數(shù)并不處理NULL值。結(jié)

21、論：sum函數(shù)并不處理NULL值。同樣對(duì)于avg函數(shù)，設(shè)計(jì)SQL語句如下：SELECT avg(CrsUnits) as AvgOfCourseFROM CourseWHERE CourseNo LIKE IS%其執(zhí)行結(jié)果為：說明avg函數(shù)并不處理NULL值。結(jié)論：avg函數(shù)并不處理NULL值。max，min函數(shù)。查詢Enrollment表中GPA最高和最低的學(xué)生。其SQL語句為：SELECT MAX(EnrGrade) as MaxOfGPA, MIN(EnrGrade) as MinOfGPAFROM Enrollment觀察其查詢計(jì)劃，發(fā)現(xiàn)max函數(shù)和min函數(shù)的成本完全一樣，并且因?yàn)橐?/p>

22、經(jīng)在EnrGrade上建立了索引，本次查詢并沒有做排序。查詢結(jié)果為：結(jié)論：（1）當(dāng)在屬性列上建立索引時(shí)，max和min函數(shù)不需做排序。 （2）max和min函數(shù)不對(duì)NULL列做處理。在此，可以得出結(jié)論，集函數(shù)不對(duì)空值列作處理。d. 取消重復(fù)distincte. 通配符的使用。答：已經(jīng)在前面的查詢中涉及到，在此不再贅述。f. 請(qǐng)大家仔細(xì)看各個(gè)查詢的查詢計(jì)劃圖，思考如下問題：1 空值對(duì)結(jié)果的影響（例如在計(jì)算sum, avg, min集函數(shù)時(shí)系統(tǒng)如何處理空值屬性）？2 注意察看查詢執(zhí)行計(jì)劃：如果察看的屬性列上有索引和沒有索引，系統(tǒng)是讀取數(shù)據(jù)的方式有何不同？3是不是在一個(gè)表上有索引，所有的查詢都會(huì)使用

23、該索引去讀取數(shù)據(jù)？為什么？4 為什么有時(shí)候即使使用了Order by操作，但查詢計(jì)劃里并沒有相應(yīng)的操作？對(duì)于distinct也有類似的情況。答：1.在SQL Server中，即函數(shù)不處理空值，即當(dāng)碰到NULL值時(shí)，這一列都被直接跳過。2.有不同。當(dāng)沒有索引時(shí)，系統(tǒng)使用Clustered Index Scan；當(dāng)有索引時(shí)，系統(tǒng)使用Index Seek。3是。至少我看到的全是。 原因：假如有索引的話，那就一定會(huì)利用索引，可能是因?yàn)榕卸ㄊ褂盟饕矢叩偷膯栴}本身代價(jià)很大，所以系統(tǒng)默認(rèn)為使用索引的效率更高一些。4這是因?yàn)樵M原來就已經(jīng)有序，則系統(tǒng)就不再進(jìn)行排序。有時(shí)Order by的列在主索引上，這樣

24、系統(tǒng)會(huì)直接利用索引，也不會(huì)進(jìn)行排序工作。復(fù)雜的查詢a. 連接查詢。答：（1）設(shè)計(jì)如下SQL語句：SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering, Faculty WHERE OffTerm = FALL AND OffYear = 2002 AND CourseNo LIKE IS% AND Faculty.FacSSN = Offering.FacSSN其執(zhí)行計(jì)劃圖如下：其查詢采用Nested Loops/Inner Join方式，可以看出：當(dāng)表很小時(shí)，SQL Server采用Nested Loops/Inn

25、er Join 操作方式。在SQL Server中引入了流水線的思想，當(dāng)中間結(jié)果不需要寫入外存時(shí)，并不進(jìn)行I/O操作。結(jié)論：當(dāng)表很小時(shí)，SQL Server采用Nested Loops/Inner Join 操作方式。在SQL Server中引入了流水線的思想，當(dāng)中間結(jié)果不需要寫入外存時(shí)，并不進(jìn)行I/O操作。（2）繼續(xù)設(shè)計(jì)左外連接、右外連接、全外連接如下，限于篇幅，不做詳細(xì)分析：SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Offering LEFT JOIN Faculty ON Offer

26、ing.FacSSN = Faculty.FacSSN WHERE CourseNo LIKE IS%SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Offering RIGHT JOIN Faculty ON Offering.FacSSN = Faculty.FacSSN WHERE CourseNo LIKE IS%SELECT FacSSN, FacFirstName, FacLastName, StdSSN, StdFirstName, StdLastName, StdGPA FROM

27、 Faculty FULL JOIN Student ON Student.StdSSN = Faculty.FacSSN其中全外連接的結(jié)果如下：A.觀察查詢計(jì)劃圖，Join操作系統(tǒng)有哪些不同的實(shí)現(xiàn)join的方式？答：Join有Inner Join、Left Outer Join、Left Semi Join、Left Anti Semi Join、Right Outer Join、Right Semi Join和Right Anti Semi Join等類型。系統(tǒng)實(shí)現(xiàn)JOIN操作的方式有Nested Loops和Merge Join方式。B.系統(tǒng)是否區(qū)別等值連接和自然連接？如果不區(qū)分，為什么

28、？如果區(qū)分，請(qǐng)說明系統(tǒng)是如何區(qū)分的？答：不區(qū)分。在系統(tǒng)中沒有自然連接，因此用等值連接實(shí)現(xiàn)自然連接。因?yàn)樵谙到y(tǒng)中等值連接直接做了去重工作，即為自然連接。b.嵌套查詢A.對(duì)同一個(gè)查詢，寫出非嵌套查詢和嵌套查詢兩種形式；觀察他們的執(zhí)行速度差異；觀察他們的執(zhí)行計(jì)劃有何不同，并解釋為什么。答：設(shè)計(jì)兩個(gè)查詢，一個(gè)為嵌套查詢，另一個(gè)為非嵌套查詢，其SQL語句如下：非嵌套查詢：SELECT DISTINCT Student.StdSSNFROM Student,EnrollmentWHERE Student.StdSSN = Enrollment.StdSSNAND StdMajor = IS;嵌套查詢：SE

29、LECT Student.StdSSNFROM StudentWHERE StdMajor = IS AND Student.StdSSN IN (SELECT Enrollment.StdSSN FROM Enrollment);非嵌套查詢的執(zhí)行計(jì)劃為：嵌套查詢的執(zhí)行計(jì)劃為：與上圖完全相同，說明系統(tǒng)在做優(yōu)化操作時(shí)將這兩個(gè)查詢作為同一種查詢進(jìn)行。結(jié)論：對(duì)于同一個(gè)查詢的不同的查詢語句，有時(shí)系統(tǒng)內(nèi)部實(shí)現(xiàn)時(shí)會(huì)可能會(huì)完全一樣。B.對(duì)同一個(gè)查詢，寫出相關(guān)嵌套查詢和非相關(guān)嵌套查詢的形式，執(zhí)行計(jì)劃和執(zhí)行效率對(duì)比 答：設(shè)計(jì)如下查詢：相關(guān)嵌套SELECT *FROM Enrollment e1WHERE e1.

30、EnrGrade IN (SELECT e2.EnrGrade FROM Enrollment e2 WHERE e1.EnrGrade = e2.EnrGrade);非相關(guān)嵌套SELECT *FROM Enrollment eWHERE EXISTS(SELECT *FROM Enrollment e1, Enrollment e2WHERE e1.EnrGrade = e2.EnrGrade);對(duì)比兩者的執(zhí)行計(jì)劃：相關(guān)嵌套：非相關(guān)嵌套：對(duì)比兩個(gè)查詢，竟然發(fā)現(xiàn)，非相關(guān)查詢的效率居然可以低于相關(guān)查詢。C.通過查詢驗(yàn)證IN，Exists，ANY，ALL，Some之間的等價(jià)關(guān)系答：在上個(gè)例子中，已

31、經(jīng)驗(yàn)證了IN和EXISTS的等價(jià)性。在此只驗(yàn)證其余的等價(jià)性。對(duì)于以下三個(gè)查詢，還有上題中的兩個(gè)查詢，查詢結(jié)果完全相同，說明他們是等價(jià)的。ANY：SELECT *FROM Enrollment e1WHERE e1.EnrGrade =ANY (SELECT e2.EnrGrade FROM Enrollment e2 WHERE e1.EnrGrade = e2.EnrGrade);ALL：SELECT *FROM Enrollment e1WHERE EXISTS( SELECT *FROM EnrollmentWHERE EnrGrade !=ALL (SELECT e2.EnrGrade

32、 FROM Enrollment e2 WHERE e1.EnrGrade = e2.EnrGrade);SOME：SELECT *FROM Enrollment e1WHERE e1.EnrGrade =SOME (SELECT e2.EnrGrade FROM Enrollment e2 WHERE e1.EnrGrade = e2.EnrGrade);c.復(fù)合條件查詢AWhere條件中除了有連接條件外，還有一些選擇條件。B注意察看它們的執(zhí)行計(jì)劃，執(zhí)行這些操作的順序，是否是安裝語句中條件的順序來執(zhí)行，是先做選擇還是先做連接？答：設(shè)計(jì)查詢?nèi)缦拢篠ELECT *FROM Enrollment

33、e1, Student s1WHERE e1.StdSSN = s1.StdSSN AND s1.StdMajor = IS;其執(zhí)行計(jì)劃為下圖，可以看到，并沒有按照語句中的條件的順序來執(zhí)行，而且先做選擇，后做連接: 思考和要求：對(duì)于復(fù)雜的查詢，盡量使用不同的形式表示出來。對(duì)比其結(jié)果，是否一樣？查詢計(jì)劃是否一樣？答：結(jié)果一樣，查詢計(jì)劃不一樣。對(duì)同樣的查詢語句，有無索引的情況下，他們的查詢計(jì)劃有何不一樣？答：不一樣。有索引時(shí)大都利用索引進(jìn)行查詢，無索引時(shí)多是全表掃描。引入視圖實(shí)驗(yàn)步驟3中的查詢，先建立相應(yīng)的視圖。再在視圖上進(jìn)行查詢。觀察他們結(jié)果是否一樣。它們的執(zhí)行計(jì)劃是否一樣。答：以下面的查詢?yōu)槔?/p>

34、，這個(gè)例子已經(jīng)在前面的查詢中作過：SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering, Faculty WHERE OffTerm = FALL AND OffYear = 2002 AND CourseNo LIKE IS% AND Faculty.FacSSN = Offering.FacSSN分析這個(gè)例子，決定在上面建立一個(gè)視圖，視圖定義語句如下：CREATE VIEW OfferView AS SELECT *FROM OfferingWHERE OffTerm = FALL AND OffYear = 2

35、002 AND CourseNo LIKE IS%查詢語句變?yōu)椋篠ELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM OfferView, Faculty WHERE OffTerm = FALL AND OffYear = 2002 AND CourseNo LIKE IS% AND Faculty.FacSSN = OfferView.FacSSN其執(zhí)行計(jì)劃為：與前面不用視圖的查詢作比較，兩者的執(zhí)行計(jì)劃完全相同。所以說視圖只是一組SQL語句，當(dāng)執(zhí)行時(shí)便去調(diào)用這組查詢語句。其他問題（1） SQL Server中是否支持FROM子句中

36、嵌套查詢語句？為此，設(shè)計(jì)SQL語句如下：SELECT T.CourseNo, T.CrsDesc, COUNT(*) AS NumOfferings, Avg(T.EnrollCount) AS AvgEnrollFROM (SELECT Course.CourseNo, CrsDesc, Offering.OfferNo, COUNT(*) AS EnrollCount FROM Offering, Enrollment, Course WHERE Offering.OfferNo = Enrollment.OfferNo AND Course.CourseNo = Offering.Cou

37、rseNo GROUP BY Course.CourseNo, CrsDesc, Offering.OfferNo) AS TGROUP BY T.CourseNo, T.CrsDesc;將以上語句在SQL Server上執(zhí)行，分析執(zhí)行均正確，說明SQL Server支持在FROM子句中嵌套查詢語句。（2） SQL Server是否支持集函數(shù)嵌套？為此，設(shè)計(jì)查詢語句如下：SELECT Max(avg(EnrGrade)FROM EnrollmentGROUP BY OfferNo語句分析時(shí)直接報(bào)錯(cuò)：服務(wù)器: 消息 130，級(jí)別 15，狀態(tài) 1，行 1不能對(duì)包含聚合或子查詢的表達(dá)式執(zhí)行聚合函數(shù)。

38、結(jié)論：不能對(duì)包含聚合或子查詢的表達(dá)式執(zhí)行聚合函數(shù)（3）SQL Server支持多少層嵌套查詢？開始設(shè)計(jì)了20層循環(huán)，但是系統(tǒng)識(shí)別正常。為此，設(shè)計(jì)了一個(gè)超大的卻極其簡單的查詢語句，一共有四十層循環(huán)：SELECT StdSSNFROM EnrollmentWHERE EnrGrade = 4.0 AND EXISTS(SELECT * FROM Enrollment WHERE EnrGrade =3.9 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.8 AND EXISTS (SELECT * FROM Enrollment WHE

39、RE EnrGrade =3.7 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.6 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.5 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.4 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.3 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.

40、2 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.1 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =3.0 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.9 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.8 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.7 AND EXISTS (S

41、ELECT * FROM Enrollment WHERE EnrGrade =2.6 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.5 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.4 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.3 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.2 AND EXISTS (SELECT * FROM En

42、rollment WHERE EnrGrade =2.1 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.09 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.08 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.07 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.06 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.05 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.04 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.03 AND EXISTS (SELECT * FROM Enrollment WHERE EnrGrade =2.02 AND EXISTS (SELECT * FROM Enrollment WHERE