數(shù)據(jù)庫原理及應(yīng)用課件：第7章 高級查詢 - 32學(xué)時實驗

1、數(shù)據(jù)庫基礎(chǔ)與實踐技術(shù)（SQL Server 2008）第七章第7章 高級查詢7.1 CASE函數(shù)7.2 子查詢7.3 查詢結(jié)果的并、交、差運(yùn)算7.4 其他一些查詢功能 7.4.1 開窗函數(shù) 7.4.2 公用表表達(dá)式 7.4.3 Merge語句2/1317.1 CASE函數(shù)是一種多分支函數(shù)，可以根據(jù)條件列表的值返回多個可能結(jié)果中的一個。可用在任何允許使用表達(dá)式的地方。不是一個完整的T-SQL語句，不能單獨(dú)執(zhí)行。3/1311. 簡單CASE函數(shù)CASE input_expression WHEN when_expression THEN result_expression .n ELSE else

2、_result_expression END4/131示例【例1】查詢選了Java課程的學(xué)生的學(xué)號、姓名、所在系和成績，并對所在系進(jìn)行如下處理：“計算機(jī)系”：顯示“CS”；“信息管理系”：顯示“IM”；“通信工程系”：顯示“COM”。 SELECT s.Sno 學(xué)號,Sname 姓名, CASE Dept WHEN 計算機(jī)系 THEN CS WHEN 信息管理系 THEN IM WHEN 通信工程系 THEN COM END AS 所在系,Grade 成績 FROM Student s join SC ON s.Sno = SC.Sno JOIN Course c ON c.Cno = SC.

3、Cno WHERE Cname = JAVA5/1312搜索CASE函數(shù)CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END6/131示例【例2】查詢“C001”課程的考試情況，列出學(xué)號和成績，對成績進(jìn)行如下處理如果成績大于等于90，則在查詢結(jié)果中顯示“優(yōu)”；如果成績在80到89分之間，則在查詢結(jié)果中顯示“良”；如果成績在70到79分之間，則在查詢結(jié)果中顯示“中”；如果成績在60到69分之間，則在查詢結(jié)果中顯示“及格”；如果成績小于60分，則在查詢結(jié)果中顯示“不及格”。7/1

4、31SELECT Sno, CASE WHEN Grade = 90 THEN 優(yōu) WHEN Grade between 80 and 89 THEN 良 WHEN Grade between 70 and 79 THEN 中 WHEN Grade between 60 and 69 THEN 及格 WHEN Grade 4 THEN 多 WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN 一般 WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN 少 WHEN COUNT(SC.Cno) = 0 THEN 未選END AS 選課情況FRO

5、M Student S LEFT JOIN SC ON S.Sno = SC.SnoWHERE Dept = 計算機(jī)系 GROUP BY S.SnoORDER BY COUNT(SC.Cno) DESC7.1.2 CASE 函數(shù)應(yīng)用示例【例4】修改全體學(xué)生的JAVA考試成績，修改規(guī)則如下：對通信工程系學(xué)生，成績加10分；對信息管理系學(xué)生，成績加5分；對其他系學(xué)生，成績不變。UPDATE SC SET Grade = Grade + CASE Dept WHEN 通信工程系 THEN 10 WHEN 信息管理系 THEN 5 ELSE 0 END FROM Student S JOIN SC O

6、N S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = JAVA12/1317.1.2 CASE 函數(shù)應(yīng)用示例7.2 子查詢7.2.1 嵌套子查詢(不相關(guān)子查詢）7.2.2 相關(guān)子查詢7.2.3 其他形式的子查詢13/131【說明】1.如果一個select語句嵌套在另一個select、insert、update或delete語句中，則稱為子查詢。2.嵌套子查詢：內(nèi)層查詢中不關(guān)聯(lián)外層查詢的子查詢。3.相關(guān)子查詢：內(nèi)層查詢利用外層查詢提供的信息執(zhí)行。4.子查詢語句可以出現(xiàn)在任何能夠用表達(dá)式的地方。7.2 子查詢（subquery

7、）1.查詢學(xué)生姓名、所在系和該學(xué)生選的課程門數(shù)。SELECT Sname,Dept, (SELECT COUNT(*) FROM SC WHERE Sno = Student.Sno ) AS CountCno FROM Student15/1317.2 子查詢（subquery）2.查詢課程名、開課學(xué)期及選該門課的學(xué)生人數(shù)、平均成績,不包括沒人選的課程。SELECT Cname AS 課程名,semester AS 開課學(xué)期, ( SELECT COUNT(*) FROM SC WHERE Cno = Course.Cno ) AS 選課人數(shù), ( SELECT AVG(Grade) FRO

8、M SC WHERE Cno = Course.Cno) AS 平均成績 FROM Course WHERE Cno IN (SELECT Cno FROM SC)16/1317.2 子查詢（subquery）【例5】查詢課程號、課程名、開課學(xué)期、學(xué)分以及該學(xué)期開設(shè)課程的總學(xué)分、平均學(xué)分、最低學(xué)分和最高學(xué)分。 SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, MIN(Credit

9、) OVER(PARTITION BY Semester) AS Min, MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM CourseOVER子句與聚合函數(shù)結(jié)合使用EXISTS形式的子查詢EXISTS代表存在量詞。WHERE NOT EXISTS（子查詢）不返回查詢的數(shù)據(jù)，只產(chǎn)生邏輯真值和假值。EXISTS：當(dāng)子查詢中有滿足條件的數(shù)據(jù)時，返回真值，否則返回假值。NOT EXISTS：當(dāng)子查詢中有滿足條件的數(shù)據(jù)時，返回假值；否則返回真值。18/131【例6.1】查詢選了JAVA課程的學(xué)生姓名和所在系。SELECT Sname, Dept

10、FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND EXISTS ( SELECT * FROM Course WHERE Cno = SC.Cno AND Cname = JAVA)【例6.2】查詢沒有選修JAVA課程的學(xué)生姓名和所在系。19/131EXISTS形式的子查詢not【例7】查詢至少選了全部課程的學(xué)生的學(xué)號、姓名和所在系。該查詢的關(guān)系代數(shù)表達(dá)式為： Sno,Sname,Sdept（Student）（SNO,CNO(SC) cno（ourse）SQL語言中沒有提供除運(yùn)算，而且，除運(yùn)算也不能用如

11、ALL、=ALL、=ALL等量化的謂詞形式構(gòu)造。20EXISTS形式的子查詢分析（1）構(gòu)造反例：設(shè)有一門課程是s.sno沒有選的（2）將步驟1構(gòu)造的反例表達(dá)為搜索條件；（3）建立表示這類反例不存在的搜索條件；（4）建立完整的SELECT語句。 21最終語句SELECT s.Sno, Sname, Sdept FROM Student s WHERE NOT EXISTS( SELECT * FROM Course c WHERE NOT EXISTS( SELECT * FROM SC x WHERE x. Cno = c.Cno and x.Sno = s.Sno)22【例8】查詢至少選了“

12、0811102”學(xué)生所選的全部課程的學(xué)生的學(xué)號和所選的課程號（1）構(gòu)造反例：有一個“0811102”學(xué)生選的課程是?.sno沒有選的。 23EXISTS形式的子查詢示例（續(xù)）（4）建立完整的SELECT語句SELECT Sno, Cno FROM SC s1 WHERE NOT EXISTS( SELECT * FROM SC x WHERE Sno = 0811102 and NOT EXISTS( select * from SC y where y.Cno = x.Cno and y.Sno = s1.Sno) and Sno != 0811102去掉0811102本人24派生表也稱為內(nèi)

13、聯(lián)視圖,是將子查詢做為一個表來處理。由子查詢產(chǎn)生的新表就稱之為“派生表”，這很類似于臨時表。select * from (select * from table) as temp25/131示例【例9】查詢至少選了C001和C002兩門課程的學(xué)生學(xué)號。SELECT T1.Sno FROM (SELECT * FROM SC WHERE Cno = C001) AS T1 JOIN (SELECT * FROM SC WHERE Cno = c002) AS T2 ON T1.Sno=T2.Sno26/1317.3 查詢結(jié)果的并、交、差運(yùn)算SELECT語句的查詢結(jié)果是元組的集合，所以多個SELE

14、CT語句的結(jié)果可進(jìn)行集合操作。集合操作主要包括：UNION（并）、INSTERSECT（交）EXCEPT（差）27/7627/131一些說明所有的SELECT語句列表中列的個數(shù)必須相同，而且對應(yīng)列的語義應(yīng)該相同。各SELECT語句中每個列的數(shù)據(jù)類型必須兼容。合并后的結(jié)果采用第一個SELECT語句的列標(biāo)題。如果要對查詢的結(jié)果進(jìn)行排序，則ORDER BY子句寫在最后一個查詢語句之后。28/7628/1317.4 其他一些查詢功能7.4.1 開窗函數(shù)7.4.2 公用表表達(dá)式7.4.3 Merge語句29/1317.4.1 開窗函數(shù)開窗函數(shù)是指可以用于“分區(qū)”或“分組”計算的函數(shù)。SQL SERVER

15、提供排名開窗函數(shù)和聚合開窗函數(shù)。結(jié)合over子句使用：OVER (PARTITION BY value_expression , . n )30/131【例1】 查詢課程號、課程名、開課學(xué)期、學(xué)分以及該學(xué)期開設(shè)課程的總學(xué)分、平均學(xué)分、最低學(xué)分和最高學(xué)分。 SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, MIN(Credit) OVER(PARTITION BY Semester

16、) AS Min, MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM Course31/1311. 將OVER子句與聚合函數(shù)結(jié)合使用【例2】設(shè)有表MyOrderDetail：32/1311. 將OVER子句與聚合函數(shù)結(jié)合使用【例2】查詢訂單號、產(chǎn)品號、訂購數(shù)量、每個訂單的總訂購數(shù)量以及每個產(chǎn)品的訂購數(shù)量占該訂單總訂購數(shù)量的百分比。(MySimpleDB)SELECT OrderID 訂單號, ProductID 產(chǎn)品號, OrderQty 訂購數(shù)量, SUM(OrderQty) OVER(PARTITION BY OrderID) 總計,

17、CAST(1.0*OrderQty/SUM(OrderQty) OVER(PARTITION BY OrderID) *100 AS DECIMAL(5,2) AS 所占百分比 FROM MyOrderDetail33/1311. 將OVER子句與聚合函數(shù)結(jié)合使用34/1311. 將OVER子句與聚合函數(shù)結(jié)合使用【練習(xí)】查詢學(xué)號、姓名、性別、所在系，以及該系的學(xué)生總?cè)藬?shù)、男女生人數(shù)及男女生百分比。1. 將OVER子句與聚合函數(shù)結(jié)合使用2.over子句與排名函數(shù)一起使用排名函數(shù)為分區(qū)中的每一行返回一個排名值。SQL Server提供了4個排名函數(shù)：RANKDENSE_RANKROW_NUMBER

18、NTILE36/131RANK()函數(shù)RANK()OVER( , n )RANK()函數(shù)返回結(jié)果集的分區(qū)內(nèi)每行的排名。每個分區(qū)內(nèi)行的排名從1開始。排序時有相同值的行具有相同排名。37/131查詢訂單號、產(chǎn)品號、訂購數(shù)量以及每個產(chǎn)品在每個訂單中的訂購數(shù)量排名。38/131RANK()函數(shù)【例3】查詢訂單號、產(chǎn)品號、訂購數(shù)量以及每個產(chǎn)品在每個訂單中的訂購數(shù)量排名。(MySimpleDB)SELECT OrderID, ProductID, OrderQty, RANK() OVER (PARTITION BY OrderID ORDER BY OrderQty DESC) AS RANKFROM

19、MyOrderDetailORDER BY OrderID39/131RANK()函數(shù)DENSE_RANK()函數(shù)DENSE_RANK()函數(shù)與RANK()函數(shù)的作用基本一樣，使用方法也一樣。DENSE_RANK()OVER( , n )唯一區(qū)別是DENSE_RANK()函數(shù)的排名中間沒有任何間斷，即該函數(shù)將返回的是一個連續(xù)的整數(shù)值。40/13141/131DENSE_RANK()函數(shù)ROW_NUMBER()函數(shù)返回結(jié)果集分區(qū)內(nèi)行的序列號，每個分區(qū)的第一行從1開始。 ROW_NUMBER ( ) OVER ( )與Rank()函數(shù)的區(qū)別是生成的序列號不重復(fù)。42/131【例4】 查詢“計算機(jī)文

20、化學(xué)”的考試情況，列出學(xué)號、姓名、所在系、考試成績及成績排名。SELECT S.Sno, Sname, Dept, Grade, ROW_NUMBER() OVER(ORDER BY Grade DESC) AS NumberFROM Student S JOIN SC ON S.Sno = SC.SnoJOIN Course C ON C.Cno = SC.CnoWHERE C.Cname = 計算機(jī)文化學(xué)43/131ROW_NUMBER()函數(shù)【練習(xí)】查詢學(xué)生學(xué)號、選的課程的課程號、考試成績以及考試成績在該門課程中排名。2.over子句與排名函數(shù)一起使用7.4.2 公用表表達(dá)式公用表表達(dá)式

21、（CTE，Common Table Expression)將查詢語句產(chǎn)生的結(jié)果集指定一個臨時命名的名字，這些命名的結(jié)果集就稱為公用表表達(dá)式。CTE可以在SELECT、INSERT、UPDATE、DELETE等語句中被多次引用。45/131公用表表達(dá)式的語法格式WITH ,.n AS ( SELECT 語句 )其中，:= expression_name ( column_name ,.n ) 46/131示例【例5】 定義一個統(tǒng)計每門課程的選課人數(shù)的簡單CTE，并利用該CTE查詢課程號和選課人數(shù)。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT

22、(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount 47/131示例【例6】利用CTE查詢選課人數(shù)超過2人的課程。WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno )SELECT Cno, Counts FROM CnoCount WHERE Counts 2 ORDER BY Counts48/1317.4.3 Merge語句MERGE語句是SQL Server 2008新增加的數(shù)據(jù)操作語句。該語句的功能是根據(jù)源表對目標(biāo)表執(zhí)行插

23、入、更新或刪除操作。最典型的應(yīng)用就是進(jìn)行兩個表的同步。MERGE語句最后的分號是不能省略的!49/1311.MERGE語句格式MERGE 目標(biāo)表USING 源表ON 匹配條件WHEN MATCHED THEN 語句WHEN NOT MATCHED by target|by source THEN 語句;50/131示例【例7】設(shè)有Product及ProductNew兩個表，現(xiàn)希望將Product表中的數(shù)據(jù)同步到ProductNew表中。 CREATE TABLE Product ( ProductID varchar(7) PRIMARY KEY, ProductName varchar(50

24、) NOT NULL, Price decimal(6,1) DEFAULT 0 ) CREATE TABLE ProductNew ( ProductID varchar(7) PRIMARY KEY, ProductName varchar(50) NOT NULL, Price decimal(6,1) DEFAULT 0 )51/131插入操作在Product表中插入如下數(shù)據(jù)： INSERT INTO Product Values (4100037,優(yōu)盤,50), (4100038,鼠標(biāo),30), (4100039,鍵盤,100)52/131修改Product表中“4100037”產(chǎn)品

25、的價格為55。 UPDATE Product SET Price=55 WHERE ProductID = 4100037從Product表中刪除“410037”號產(chǎn)品。 DELETE FROM Product WHERE ProductID = 4100037修改和刪除操作同步操作對ProductNew表同步以上操作：MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductID WHEN NOT MATCHED by target THEN INSERT( ProductID,ProductName,Price)

26、VALUES(s.ProductID,s.ProductName,s.Price) -WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;54/131第7章 高級查詢7.1 CASE函數(shù)7.2 子查詢7.3 查詢結(jié)果的并、交、差運(yùn)算7.4 其他一些查詢功能 7.4.1 開窗函數(shù) 7.4.2 公用表表達(dá)式 7.4.3 Merge語句55/1311查詢選了“Java”課程的學(xué)生學(xué)號、姓名和JAVA成績。2.統(tǒng)計選了Ja

27、va課程的這些學(xué)生的選課門數(shù)和平均成績。3.查詢選了“C001”號課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績。4. 查詢沒選“C001”號課程的學(xué)生姓名和所在系。5.將學(xué)分最低的課程的學(xué)分加2分。6.查詢每門課程考試成績最高的兩個學(xué)生的學(xué)號以及相應(yīng)的課程號和成績。不包括沒考試的課程。7.查詢有最高學(xué)分超過本學(xué)期平均學(xué)分1.5倍的學(xué)期。8.查詢所學(xué)每一門課程成績均不低于該課程平均成績的學(xué)生姓名及相應(yīng)課程號。 9.查詢學(xué)號、姓名、性別、所在系，以及該系的學(xué)生總?cè)藬?shù)、男女生人數(shù)及男女生百分比。10刪除JAVA考試成績最低的學(xué)生的JAVA修課記錄。56/131作 業(yè)2.用MERGE語句實現(xiàn)匯總數(shù)

28、據(jù)同步假設(shè)為了做月報表的需要，創(chuàng)建了一個月銷售匯總表。希望每日將新的銷售記錄添加到每月匯總表中。在每個月的第1天晚上，只需將銷售記錄插入到銷售匯總表中即可。但從第2天晚上開始情況就不一樣了，對于之前沒有銷售記錄的數(shù)據(jù)，只需將該數(shù)據(jù)插入到銷售匯總表中；對于之前有銷售記錄的數(shù)據(jù)，則需要更新該商品的匯總數(shù)據(jù)57/131示例（續(xù)1）本示例用MySimpleDB數(shù)據(jù)庫中的Sales.SalesOrderHeader和Sales.SalesOrderDetail表中的數(shù)據(jù)來同步銷售匯總數(shù)據(jù)。在MySimpleDB數(shù)據(jù)庫中創(chuàng)建銷售匯總表：CREATE TABLE Sales.MonthlyRollup( Y

29、ear smallint NOT NULL, Month tinyint NOT NULL, ProductID int NOT NULL REFERENCES Production.Product (ProductID), QtySold int NOT NULL, PRIMARY KEY(Year,Month,ProductID)58/131示例（續(xù)2）設(shè)只對2003年8月的數(shù)據(jù)進(jìn)行匯總，從該月的第1天開始。構(gòu)建MERGE語句，產(chǎn)生2003年8月1日的銷售匯總數(shù)據(jù)。MERGE Sales.MonthlyRollup AS smrUSING( SELECT soh.OrderDate, so

30、d.ProductID, SUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-01 GROUP BY soh.OrderDate, sod.ProductID) AS sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.Q

31、tySold + s.QtySoldWHEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold);59/131示例（續(xù)3）繼續(xù)查看該月第2天的總結(jié)果。更新日期，繼續(xù)運(yùn)行下述代碼（仿真在該月第2天運(yùn)行）MERGE Sales.MonthlyRollup AS smrUSING ( SELECT soh.OrderDate, sod.ProductID, SUM(so

32、d.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-02 GROUP BY soh.OrderDate, sod.ProductID) AS sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldW

33、HEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold);60/1317.4.2 公用表表達(dá)式公用表表達(dá)式（CTE，Common Table Expression)將查詢語句產(chǎn)生的結(jié)果集指定一個臨時命名的名字，這些命名的結(jié)果集就稱為公用表表達(dá)式。命名好公用表表達(dá)式后就可以在SELECT、INSERT、UPDATE、DELETE等語句中被多次引用。公用表表達(dá)式還可

34、以包括對自身的引用，這種表達(dá)式稱為遞歸公用表表達(dá)式。61/131公用表表達(dá)式的語法格式WITH ,.n := expression_name ( column_name ,.n ) AS ( SELECT 語句 )62/131示例例9 定義一個統(tǒng)計每門課程的選課人數(shù)的簡單CTE，并利用該CTE查詢課程號和選課人數(shù)。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount ORDER BY Counts63/131例10 使用公用表表達(dá)

35、式來限制返回結(jié)果。改進(jìn)例9的查詢，定義一個統(tǒng)計每門課程的選課人數(shù)的CTE，并利用該CTE查詢選課人數(shù)超過2人的課程。WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno )SELECT Cno, Counts FROM CnoCount WHERE Counts 2 ORDER BY Counts64/131Employees表及數(shù)據(jù)示例65/131示例首先建立兩個包含職工全部信息的CTE，然后利用這兩個CTE查詢每個職工信息及上級領(lǐng)導(dǎo)信息。WITH Emp1 AS (SELECT * FROM Employees ), Emp2 AS (SELECT * FROM Employees )SELECT * FROM Emp1 JOIN Emp2 ON