数据库原理及应用课件：第7章 高级查询 - 32学时实验

《数据库原理及应用课件：第7章 高级查询 - 32学时实验》由会员分享，可在线阅读，更多相关《数据库原理及应用课件：第7章 高级查询 - 32学时实验（72页珍藏版）》请在金锄头文库上搜索。

1、数据库基础与实践技术（SQL Server 2008）第七章第七章第7章 高级查询7.1 CASE7.1 CASE函数函数7.2 7.2 子查询子查询7.3 7.3 查询结果的并、交、差运算查询结果的并、交、差运算7.4 7.4 其他一些查询功能其他一些查询功能 7.4.1 7.4.1 开窗函数开窗函数 7.4.2 7.4.2 公用表表达式公用表表达式 7.4.3 Merge7.4.3 Merge语句语句2/1317.1 CASE7.1 CASE函数函数v是一种多分支函数，可以根据条件列表是一种多分支函数，可以根据条件列表的值返回多个可能结果中的一个。的值返回多个可能结果中的一个。v可用在任何

2、允许使用表达式的地方。可用在任何允许使用表达式的地方。v不是一个完整的不是一个完整的T-SQLT-SQL语句，不能单独执语句，不能单独执行。行。3/1311. 1. 简单简单CASECASE函数函数CASECASE input_expression input_expression WHENWHEN when_expression when_expression THENTHEN result_expression result_expression .n .n ELSEELSE else_result_expression else_result_expression ENDEND4/131

3、示例示例【例【例1 1】查询选了】查询选了JavaJava课程的学生的学号、姓名、课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：所在系和成绩，并对所在系进行如下处理：“计算机系计算机系”：显示：显示“CSCS”；“信息管理系信息管理系”：显示：显示“IMIM”；“通信工程系通信工程系”：显示：显示“COMCOM”。 SELECT s.Sno SELECT s.Sno 学号学号,Sname ,Sname 姓名姓名, , CASE Dept CASE Dept WHEN WHEN 计算机系计算机系 THEN CS THEN CS WHEN WHEN 信息管理系信息管理系 THEN

4、IM THEN IM WHEN WHEN 通信工程系通信工程系 THEN COM THEN COM END END AS AS 所在系所在系,Grade ,Grade 成绩成绩 FROM Student s join SC ON s.Sno = SC.SnoFROM Student s join SC ON s.Sno = SC.Sno JOIN Course c ON c.Cno = SC.Cno JOIN Course c ON c.Cno = SC.Cno WHERE Cname = JAVA WHERE Cname = JAVA5/1312 2搜索搜索CASECASE函数函数CASEC

5、ASE WHEN Boolean_expression WHEN Boolean_expression THEN result_expression THEN result_expression .n .n ELSE else_result_expression ELSE else_result_expression ENDEND6/131示例示例【例【例2 2】查询】查询“C001C001”课程的考试情况，列出课程的考试情况，列出学号和成绩，对成绩进行如下处理学号和成绩，对成绩进行如下处理如果成绩大于等于如果成绩大于等于9090，则在查询结果中显示，则在查询结果中显示“优优”；如果成绩在如果

6、成绩在8080到到8989分之间，则在查询结果中显分之间，则在查询结果中显示示“良良”；如果成绩在如果成绩在7070到到7979分之间，则在查询结果中显分之间，则在查询结果中显示示“中中”；如果成绩在如果成绩在6060到到6969分之间，则在查询结果中显分之间，则在查询结果中显示示“及格及格”；如果成绩小于如果成绩小于6060分，则在查询结果中显示分，则在查询结果中显示“不不及格及格”。7/131SELECT Sno,SELECT Sno, CASE CASE WHEN Grade = 90 THEN WHEN Grade = 90 THEN 优优 WHEN Grade between 80

7、and 89 THEN WHEN Grade between 80 and 89 THEN 良良 WHEN Grade between 70 and 79 THEN WHEN Grade between 70 and 79 THEN 中中 WHEN Grade between 60 and 69 THEN WHEN Grade between 60 and 69 THEN 及及格格 WHEN Grade 60 THEN WHEN Grade 4 THEN WHEN COUNT(SC.Cno) 4 THEN 多多 WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN W

8、HEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN 一般一般 WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN 少少 WHEN COUNT(SC.Cno) = 0 THEN WHEN COUNT(SC.Cno) = 0 THEN 未选未选 END END AS AS 选课情况选课情况FROM Student S LEFT JOIN SC ON S.Sno = SC.SnoFROM Student S LEFT JOIN SC ON S.Sno = SC.Sno

9、WHERE Dept = WHERE Dept = 计算机系计算机系 GROUP BY S.SnoGROUP BY S.SnoORDER BY COUNT(SC.Cno) DESCORDER BY COUNT(SC.Cno) DESC7.1.2 CASE 7.1.2 CASE 函数应用示例函数应用示例【例【例4 4】修改全体学生的】修改全体学生的JAVAJAVA考试成绩，修改规则如下：考试成绩，修改规则如下：对通信工程系学生，成绩加对通信工程系学生，成绩加1010分；分；对信息管理系学生，成绩加对信息管理系学生，成绩加5 5分；分；对其他系学生，成绩不变。对其他系学生，成绩不变。UPDATE

10、SC SET UPDATE SC SET Grade = Grade + Grade = Grade + CASE Dept CASE Dept WHEN WHEN 通信工程系通信工程系 THEN 10 THEN 10 WHEN WHEN 信息管理系信息管理系 THEN 5 THEN 5 ELSE 0 ELSE 0 END END FROM Student S JOIN SC ON S.Sno = SC.SnoFROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno JOIN Course C ON C.C

11、no = SC.Cno WHERE Cname = JAVA WHERE Cname = JAVA12/1317.1.2 CASE 7.1.2 CASE 函数应用示例函数应用示例7.2 7.2 子查询子查询7.2.1 7.2.1 嵌套子查询嵌套子查询( (不相关子查询）不相关子查询）7.2.2 7.2.2 相关子查询相关子查询7.2.3 7.2.3 其他形式的子查询其他形式的子查询13/131【说明说明】1.1.如果一个如果一个selectselect语句嵌套在另一个语句嵌套在另一个selectselect、insertinsert、updateupdate或或deletedelete语句中，

12、则称为语句中，则称为子查询。子查询。2.2.嵌套子查询：内层查询中不关联外层查询的子查询。嵌套子查询：内层查询中不关联外层查询的子查询。3.3.相关子查询：内层查询利用外层查询提供的信息执相关子查询：内层查询利用外层查询提供的信息执行。行。4.4.子查询语句可以出现在任何能够用表达式的地方。子查询语句可以出现在任何能够用表达式的地方。7.2 7.2 子查询（子查询（subquerysubquery）1.1.查询学生姓名、所在系和该学生选的课程查询学生姓名、所在系和该学生选的课程门数。门数。SELECT Sname,Dept,SELECT Sname,Dept, (SELECT COUNT(*)

13、 FROM SC (SELECT COUNT(*) FROM SC WHERE Sno = Student.Sno ) WHERE Sno = Student.Sno ) AS CountCnoAS CountCno FROM Student FROM Student15/1317.2 7.2 子查询（子查询（subquerysubquery）2.2.查询课程名、开课学期及选该门课的学生人数、查询课程名、开课学期及选该门课的学生人数、平均成绩平均成绩, ,不包括没人选的课程。不包括没人选的课程。SELECT Cname AS SELECT Cname AS 课程名课程名, ,s semeste

14、r AS emester AS 开课学期开课学期, , ( SELECT COUNT(*) FROM SC ( SELECT COUNT(*) FROM SC WHERE Cno = Course.Cno ) WHERE Cno = Course.Cno ) AS AS 选课人数选课人数, , ( SELECT AVG(Grade) FROM SC ( SELECT AVG(Grade) FROM SC WHERE Cno = Course.Cno) WHERE Cno = Course.Cno) AS AS 平均成绩平均成绩 FROM Course FROM Course WHERE Cno

15、 IN (SELECT Cno FROM SC) WHERE Cno IN (SELECT Cno FROM SC)16/1317.2 7.2 子查询（子查询（subquerysubquery）【例【例5 5】查询课程号、课程名、开课学期、学分】查询课程号、课程名、开课学期、学分以及该学期开设课程的总学分、平均学分、以及该学期开设课程的总学分、平均学分、最低学分和最高学分。最低学分和最高学分。 SELECT Cno, Cname, Semester,Credit, SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY

16、 Semester) AS Total, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, MIN(Credit) OVER(PARTITION BY Semester) AS Min, MIN(Credit) OVER(PARTITION BY Semester) AS Min, MAX(Credit) OVER(PARTITION BY Semeste

17、r) AS Max MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM Course FROM CourseOVEROVER子句与聚合函数结合使用子句与聚合函数结合使用EXISTSEXISTS形式的子查询形式的子查询vEXISTSEXISTS代表存在量词代表存在量词 。WHERE NOT EXISTSWHERE NOT EXISTS（子查询）（子查询）v不返回查询的数据，只产生逻辑真值和假不返回查询的数据，只产生逻辑真值和假值。值。vEXISTSEXISTS：当子查询中有满足条件的数据时，：当子查询中有满足条件的数据时，返回真值，否则返回假值

18、。返回真值，否则返回假值。vNOT EXISTSNOT EXISTS：当子查询中有满足条件的数：当子查询中有满足条件的数据时，返回假值；否则返回真值。据时，返回假值；否则返回真值。18/131【例例6.16.1】查询选了查询选了JAVAJAVA课程的学生姓名和所在系。课程的学生姓名和所在系。SELECT Sname, Dept FROM Student SELECT Sname, Dept FROM Student WHERE EXISTS ( WHERE EXISTS ( SELECT * FROM SC SELECT * FROM SC WHERE Sno=Student.Sno AND

19、EXISTS ( WHERE Sno=Student.Sno AND EXISTS ( SELECT * FROM Course SELECT * FROM Course WHERE Cno = SC.Cno WHERE Cno = SC.Cno AND Cname = JAVA) AND Cname = JAVA)【例例6.26.2】查询没有选修查询没有选修JAVAJAVA课程的学生姓名和所在系。课程的学生姓名和所在系。19/131EXISTSEXISTS形式的子查询形式的子查询notnot【例例7 7】查询查询至少至少选了全部课程的学生的学号、选了全部课程的学生的学号、姓名和所在系。姓名和

20、所在系。v该查询的关系代数表达式为：该查询的关系代数表达式为： Sno,Sname,SdeptSno,Sname,Sdept（StudentStudent）（SNO,CNOSNO,CNO(SC) (SC) cnocno（ourseourse）vSQLSQL语言中没有提供除运算，而且，除运算语言中没有提供除运算，而且，除运算也不能用如也不能用如ALLALL、=ALL=ALL、=ALL=ALL等量化的谓词等量化的谓词形式构造。形式构造。20EXISTSEXISTS形式的子查询形式的子查询分析分析（1 1）构造反例：）构造反例：设有一门课程是设有一门课程是s.snos.sno没有选的没有选的（2 2

21、）将步骤）将步骤1 1构造的反例表达为搜索条件；构造的反例表达为搜索条件；（3 3）建立表示这类反例不存在的搜索条件；）建立表示这类反例不存在的搜索条件；（4 4）建立完整的）建立完整的SELECTSELECT语句。语句。 21最终语句最终语句SELECT s.Sno, Sname, Sdept FROM Student sSELECT s.Sno, Sname, Sdept FROM Student s WHERE NOT EXISTS( WHERE NOT EXISTS( SELECT * FROM Course c SELECT * FROM Course c WHERE NOT EXI

22、STS( WHERE NOT EXISTS( SELECT * FROM SC x SELECT * FROM SC x WHERE x. Cno = c.Cno and x.Sno = WHERE x. Cno = c.Cno and x.Sno = s.Sno)s.Sno)22【例例8 8】查询至少选了查询至少选了“08111020811102”学生所选学生所选的全部课程的学生的学号和所选的课程号的全部课程的学生的学号和所选的课程号（1 1）构造反例：）构造反例：有一个有一个“08111020811102”学生选学生选的课程是的课程是?.sno?.sno没有选的。没有选的。 23EXIST

23、SEXISTS形式的子查询形式的子查询示例（续）示例（续）（4 4）建立完整的）建立完整的SELECTSELECT语句语句SELECT Sno, Cno FROM SC s1SELECT Sno, Cno FROM SC s1 WHERE NOT EXISTS( WHERE NOT EXISTS( SELECT * FROM SC x SELECT * FROM SC x WHERE Sno = WHERE Sno = 08111020811102 and NOT EXISTS( and NOT EXISTS( select * from SC y select * from SC y whe

24、re y.Cno = x.Cno where y.Cno = x.Cno and y.Sno = s1.Sno) and y.Sno = s1.Sno) and and Sno != Sno != 08111020811102去掉去掉08111020811102本人本人24派生表派生表v也称为内联视图也称为内联视图, ,是将子查询做为一个表是将子查询做为一个表来处理。来处理。v由子查询产生的新表就称之为由子查询产生的新表就称之为“派生表派生表”，这很类似于临时表。，这很类似于临时表。vselect * from (select * from table) select * from (sele

25、ct * from table) as tempas temp25/131示例示例【例例9 9】查询至少选了查询至少选了C001C001和和C002C002两门课程两门课程的学生学号。的学生学号。SELECT T1.Sno SELECT T1.Sno FROM ( FROM (SELECT * FROM SC SELECT * FROM SC WHERE Cno = C001 WHERE Cno = C001) AS T1 ) AS T1 JOIN ( JOIN (SELECT * FROM SC SELECT * FROM SC WHERE Cno = c002 WHERE Cno = c0

26、02) AS T2 ) AS T2 ON T1.Sno=T2.Sno ON T1.Sno=T2.Sno26/1317.3 7.3 查询结果的并、交、差运算查询结果的并、交、差运算vSELECTSELECT语句的查询结果是元组的集合，所语句的查询结果是元组的集合，所以多个以多个SELECTSELECT语句的结果可进行集合操作。语句的结果可进行集合操作。v集合操作主要包括：集合操作主要包括：UNIONUNION（并）、（并）、INSTERSECTINSTERSECT（交）（交）EXCEPTEXCEPT（差）（差）27/7627/131一些说明一些说明v所有的所有的SELECTSELECT语句列表中

27、语句列表中列的个数必须相列的个数必须相同同，而且对应列的语义应该相同。，而且对应列的语义应该相同。v各各SELECTSELECT语句中每个列的语句中每个列的数据类型必须兼数据类型必须兼容容。v合并后的结果采用第一个合并后的结果采用第一个SELECTSELECT语句的列语句的列标题。标题。v如果要对查询的结果进行排序，则如果要对查询的结果进行排序，则ORDER ORDER BYBY子句写在最后一个查询语句之后。子句写在最后一个查询语句之后。28/7628/1317.4 7.4 其他一些查询功能其他一些查询功能7.4.1 7.4.1 开窗函数开窗函数7.4.2 7.4.2 公用表表达式公用表表达式

28、7.4.3 Merge7.4.3 Merge语句语句29/1317.4.1 7.4.1 开窗函数开窗函数v开开窗窗函函数数是是指指可可以以用用于于“分分区区”或或“分分组组”计算的函数计算的函数。vSQL SQL SERVERSERVER提提供供排排名名开开窗窗函函数数和和聚聚合合开开窗窗函函数。数。v结合结合overover子句使用：子句使用：OVER (PARTITION BY value_expression , . OVER (PARTITION BY value_expression , . n ) n )30/131【例【例1 1】 查询课程号、课程名、开课学期、学查询课程号、课程

29、名、开课学期、学分以及该学期开设课程的总学分、平均学分、分以及该学期开设课程的总学分、平均学分、最低学分和最高学分。最低学分和最高学分。 SELECT Cno, Cname, Semester,Credit, SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY Semester) AS Total, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, AVG(Credit)

30、 OVER(PARTITION BY Semester) AS Avg, MIN(Credit) OVER(PARTITION BY Semester) AS Min, MIN(Credit) OVER(PARTITION BY Semester) AS Min, MAX(Credit) OVER(PARTITION BY Semester) AS Max MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM Course FROM Course31/1311. 1. 将将OVEROVER子句与聚合函数结合使用子句与聚合函数结合使用【例【例2

31、2】设有表设有表MyOrderDetailMyOrderDetail：32/1311. 1. 将将OVEROVER子句与聚合函数结合使用子句与聚合函数结合使用【例【例2 2】查询订单号、产品号、订购数量、每个订】查询订单号、产品号、订购数量、每个订单的总订购数量以及每个产品的订购数量占该单的总订购数量以及每个产品的订购数量占该订单总订购数量的百分比。订单总订购数量的百分比。(MySimpleDB)(MySimpleDB)SELECT OrderID SELECT OrderID 订单号订单号, ProductID , ProductID 产品号产品号, , OrderQty OrderQty

32、订购数量订购数量, , SUM(OrderQty) OVER(PARTITION BY OrderID) SUM(OrderQty) OVER(PARTITION BY OrderID) 总计总计, , CASTCAST(1.0*OrderQty/SUM(OrderQty) (1.0*OrderQty/SUM(OrderQty) OVER(PARTITION BY OrderID) OVER(PARTITION BY OrderID) *100 AS DECIMAL(5,2) AS *100 AS DECIMAL(5,2) AS 所占百分比所占百分比 FROM MyOrderDetail FR

33、OM MyOrderDetail33/1311. 1. 将将OVEROVER子句与聚合函数结合使用子句与聚合函数结合使用34/1311. 1. 将将OVEROVER子句与聚合函数结合使用子句与聚合函数结合使用【练习】查询学号、姓名、性别、所在系，【练习】查询学号、姓名、性别、所在系，以及该系的学生总人数、男女生人数及男以及该系的学生总人数、男女生人数及男女生百分比。女生百分比。1. 1. 将将OVEROVER子句与聚合函数结合使用子句与聚合函数结合使用2.over2.over子句与排名函数一起使用子句与排名函数一起使用v排名函数为分区中的每一行返回一个排排名函数为分区中的每一行返回一个排名值。

34、名值。vSQL ServerSQL Server提供了提供了4 4个排名函数：个排名函数：RANKRANKDENSE_RANKDENSE_RANKROW_NUMBERROW_NUMBERNTILENTILE36/131RANK()RANK()函数函数RANK()RANK()OVER( , OVER( , n n ) )vRANK()RANK()函数返回结果集的分区内每行的排函数返回结果集的分区内每行的排名。每个分区内行的排名从名。每个分区内行的排名从1 1开始。开始。v排序时有相同值的行具有相同排名。排序时有相同值的行具有相同排名。37/131查查询询订订单单号号、产产品品号号、订订购购数数量

35、量以以及及每每个个产产品品在在每每个订单中的订购数量排名。个订单中的订购数量排名。38/131RANK()RANK()函数函数【例【例3 3】查询订单号、产品号、订购数量以及每个】查询订单号、产品号、订购数量以及每个产品在每个订单中的订购数量排名。产品在每个订单中的订购数量排名。(MySimpleDB)(MySimpleDB)SELECT OrderID, ProductID, OrderQty,SELECT OrderID, ProductID, OrderQty, RANK() OVER RANK() OVER (PARTITION BY OrderID (PARTITION BY Ord

36、erID ORDER BY OrderQty DESC) ORDER BY OrderQty DESC) AS RANK AS RANKFROM MyOrderDetailFROM MyOrderDetailORDER BY OrderIDORDER BY OrderID39/131RANK()RANK()函数函数DENSE_RANK()DENSE_RANK()函数函数vDENSE_RANK()DENSE_RANK()函数与函数与RANK()RANK()函数的作用函数的作用基本一样，使用方法也一样。基本一样，使用方法也一样。DENSE_DENSE_RANK()RANK()OVER( , OVE

37、R( , n ) n )v唯一区别是唯一区别是DENSE_RANK()DENSE_RANK()函数的排名中函数的排名中间没有任何间断，即间没有任何间断，即该函数将返回的是该函数将返回的是一个连续的整数值。一个连续的整数值。40/13141/131DENSE_RANK()DENSE_RANK()函数函数ROW_NUMBER()ROW_NUMBER()函数函数v返回结果集分区内行的序列号，每个分返回结果集分区内行的序列号，每个分区的第一行从区的第一行从1 1开始。开始。 ROW_NUMBER ( ) OVER ( ROW_NUMBER ( ) OVER ( ) )v与与Rank()Rank()函数

38、的区别是生成的序列号不函数的区别是生成的序列号不重复。重复。42/131【例【例4 4】 查询查询“计算机文化学计算机文化学”的考试情况，的考试情况，列出学号、姓名、所在系、考试成绩及成绩列出学号、姓名、所在系、考试成绩及成绩排名。排名。SELECT S.Sno, Sname, Dept, Grade, SELECT S.Sno, Sname, Dept, Grade, ROW_NUMBER() OVER(ORDER BY Grade DESC) ROW_NUMBER() OVER(ORDER BY Grade DESC) AS NumberAS NumberFROM Student S JO

39、IN SC ON S.Sno = SC.SnoFROM Student S JOIN SC ON S.Sno = SC.SnoJOIN Course C ON C.Cno = SC.CnoJOIN Course C ON C.Cno = SC.CnoWHERE C.Cname = WHERE C.Cname = 计算机文化学计算机文化学 43/131ROW_NUMBER()ROW_NUMBER()函数函数【练习】查询学生学号、选的课程的课程号、【练习】查询学生学号、选的课程的课程号、考试成绩以及考试成绩在该门课程中排名。考试成绩以及考试成绩在该门课程中排名。2.over2.over子句与排名函

40、数一起使用子句与排名函数一起使用7.4.2 7.4.2 公用表表达式公用表表达式v公公 用用 表表 表表 达达 式式 （ CTECTE， Common Common Table Table Expression)Expression)v将将查查询询语语句句产产生生的的结结果果集集指指定定一一个个临临时时命命名名的的名名字字，这这些些命命名名的的结结果果集集就就称称为为公公用用表表达式表表达式。vCTECTE可可 以以 在在 SELECTSELECT、 INSERTINSERT、 UPDATEUPDATE、DELETEDELETE等语句中被多次引用。等语句中被多次引用。45/131公用表表达式的

41、语法格式公用表表达式的语法格式WITH WITH ,.n ,.n AS AS ( SELECT ( SELECT 语句语句 ) )其中，其中，:=:= expression_name ( column_name expression_name ( column_name ,.n ) ,.n ) 46/131示例示例【例【例5 5】 定义一个统计每门课程的选课人数定义一个统计每门课程的选课人数的简单的简单CTECTE，并利用该，并利用该CTECTE查询课程号和选查询课程号和选课人数。课人数。 WITH WITH CnoCount(Cno, Counts) CnoCount(Cno, Counts

42、) AS (AS ( SELECT Cno, COUNT(*) FROM SC SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) GROUP BY Cno ) SELECT Cno, Counts FROM SELECT Cno, Counts FROM CnoCountCnoCount 47/131示例示例【例【例6 6】利用】利用CTECTE查询选课人数超过查询选课人数超过2 2人的课程。人的课程。WITH WITH CnoCount(Cno, Counts) CnoCount(Cno, Counts) AS (AS ( SELECT Cno, COU

43、NT(*) FROM SC SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) GROUP BY Cno )SELECT Cno, Counts FROM SELECT Cno, Counts FROM CnoCountCnoCount WHERE Counts 2 WHERE Counts 2 ORDER BY Counts ORDER BY Counts48/1317.4.3 Merge7.4.3 Merge语句语句vMERGEMERGE语句是语句是SQL Server 2008SQL Server 2008新增加的新增加的数据操作语句。数据操作语句。v

44、该语句的功能是根据源表对目标表执行该语句的功能是根据源表对目标表执行插入、更新或删除操作。插入、更新或删除操作。v最典型的应用就是进行两个表的同步。最典型的应用就是进行两个表的同步。vMERGEMERGE语句最后的分号是不能省略的语句最后的分号是不能省略的! !49/1311.1.MERGEMERGE语句格式语句格式MERGE MERGE 目标表目标表USING USING 源表源表ON ON 匹配条件匹配条件WHEN MATCHED THENWHEN MATCHED THEN 语句语句WHEN NOT MATCHED WHEN NOT MATCHED by target|by source

45、by target|by source THENTHEN 语句语句; ;50/131示例示例【例【例7 7】设有】设有ProductProduct及及ProductNewProductNew两个表，现希望两个表，现希望将将ProductProduct表中的数据同步到表中的数据同步到ProductNewProductNew表中。表中。 CREATE TABLE CREATE TABLE ProductProduct ( ( ProductID varchar(7) PRIMARY KEY, ProductID varchar(7) PRIMARY KEY, ProductName varchar

46、(50) NOT NULL, ProductName varchar(50) NOT NULL, Price decimal(6,1) DEFAULT 0 ) Price decimal(6,1) DEFAULT 0 ) CREATE TABLE CREATE TABLE ProductNewProductNew ( ( ProductID varchar(7) PRIMARY KEY, ProductID varchar(7) PRIMARY KEY, ProductName varchar(50) NOT NULL, ProductName varchar(50) NOT NULL, Pr

47、ice decimal(6,1) DEFAULT 0 ) Price decimal(6,1) DEFAULT 0 )51/131插入操作插入操作v在在ProductProduct表中插入如下数据：表中插入如下数据： INSERT INTO Product Values INSERT INTO Product Values (4100037, (4100037,优盘优盘,50), ,50), (4100038, (4100038,鼠标鼠标,30),30), (4100039, (4100039,键盘键盘,100),100)52/131v修改修改ProductProduct表中表中“410003

48、74100037”产品的价格产品的价格为为5555。 UPDATE Product SET Price=55 UPDATE Product SET Price=55 WHERE ProductID = WHERE ProductID = 41000374100037v从从ProductProduct表中删除表中删除“410037410037”号产品。号产品。 DELETE FROM Product DELETE FROM Product WHERE ProductID = 4100037 WHERE ProductID = 4100037修改和删除操作修改和删除操作同步操作同步操作v对对Pro

49、ductNewProductNew表同步以上操作：表同步以上操作：MERGE ProductNew AS d MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductID USING Product AS s ON s.ProductID = d.ProductID WHEN NOT MATCHED by target WHEN NOT MATCHED by target THEN THEN INSERTINSERT( ProductID,ProductName,Price) ( ProductID,ProductN

50、ame,Price) VALUES(s.ProductID,s.ProductName,s.Price) VALUES(s.ProductID,s.ProductName,s.Price) -WHEN NOT MATCHED BY SOURCE-WHEN NOT MATCHED BY SOURCE THEN THEN DELETEDELETE WHEN MATCHEDWHEN MATCHED THEN THEN UPDATEUPDATE SET d.ProductName = s.ProductName,SET d.ProductName = s.ProductName, d.Price =

51、s.Price; d.Price = s.Price;54/131第7章 高级查询7.1 CASE7.1 CASE函数函数7.2 7.2 子查询子查询7.3 7.3 查询结果的并、交、差运算查询结果的并、交、差运算7.4 7.4 其他一些查询功能其他一些查询功能 7.4.1 7.4.1 开窗函数开窗函数 7.4.2 7.4.2 公用表表达式公用表表达式 7.4.3 Merge7.4.3 Merge语句语句55/1311 1查询选了查询选了“JavaJava”课程的学生学号、姓名和课程的学生学号、姓名和JAVAJAVA成绩。成绩。2.2.统计选了统计选了JavaJava课程的这些学生的选课门数和

52、平均成绩。课程的这些学生的选课门数和平均成绩。3.3.查询选了查询选了“C001C001”号课程且成绩高于此课程的平均成绩的学生的学号号课程且成绩高于此课程的平均成绩的学生的学号和成绩。和成绩。4. 4. 查询没选查询没选“C001C001”号课程的学生姓名和所在系。号课程的学生姓名和所在系。5.5.将学分最低的课程的学分加将学分最低的课程的学分加2 2分。分。6.6.查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成绩。不包括没考试的课程。绩。不包括没考试的课程。7 7. .查询有最高学分超过本学期平均学分查询有最高学分超

53、过本学期平均学分1.51.5倍的学期。倍的学期。8.8.查询所学每一门课程成绩均不低于该课程平均成绩的学生姓名及相应查询所学每一门课程成绩均不低于该课程平均成绩的学生姓名及相应课程号。课程号。 9.查询学号、姓名、性别、所在系，以及该系的学生总人数、男女生查询学号、姓名、性别、所在系，以及该系的学生总人数、男女生人数及男女生百分比。人数及男女生百分比。1010删除删除JAVAJAVA考试成绩最低的学生的考试成绩最低的学生的JAVAJAVA修课记录。修课记录。56/131作作 业业2.2.用用MERGEMERGE语句实现汇总数据同步语句实现汇总数据同步v假假设设为为了了做做月月报报表表的的需需要

54、要，创创建建了了一一个个月月销销售汇总表。售汇总表。v希希望望每每日日将将新新的的销销售售记记录录添添加加到到每每月月汇汇总总表表中中。在在每每个个月月的的第第1 1天天晚晚上上，只只需需将将销销售售记记录插入到销售汇总表中即可。录插入到销售汇总表中即可。v但但从从第第2 2天天晚晚上上开开始始情情况况就就不不一一样样了了，对对于于之之前前没没有有销销售售记记录录的的数数据据，只只需需将将该该数数据据插插入入到到销销售售汇汇总总表表中中；对对于于之之前前有有销销售售记记录录的的数据，则需要更新该商品的汇总数据数据，则需要更新该商品的汇总数据57/131示例（续示例（续1 1）v本示例用本示例用

55、MySimpleDBMySimpleDB数据库中的数据库中的Sales.SalesOrderHeaderSales.SalesOrderHeader和和Sales.SalesOrderDetailSales.SalesOrderDetail表中的数据来同步销售汇表中的数据来同步销售汇总数据。在总数据。在MySimpleDBMySimpleDB数据库中创建销售汇总表：数据库中创建销售汇总表：CREATE TABLE Sales.MonthlyRollup(CREATE TABLE Sales.MonthlyRollup( Year smallint NOT NULL, Year smallint

56、 NOT NULL, Month tinyint NOT NULL, Month tinyint NOT NULL, ProductID int NOT NULL ProductID int NOT NULL REFERENCES Production.Product (ProductID),REFERENCES Production.Product (ProductID), QtySold int NOT NULL, QtySold int NOT NULL, PRIMARY KEY(Year,Month,ProductID) PRIMARY KEY(Year,Month,ProductID

57、)58/131示例（续示例（续2 2）v设只对设只对20032003年年8 8月的数据进行汇总，从该月的第月的数据进行汇总，从该月的第1 1天开始。构建天开始。构建MERGEMERGE语句，产生语句，产生20032003年年8 8月月1 1日的销售汇总数据。日的销售汇总数据。MERGE Sales.MonthlyRollup AS smrMERGE Sales.MonthlyRollup AS smrUSING(USING( SELECT soh.OrderDate, sod.ProductID, SELECT soh.OrderDate, sod.ProductID, SUM(sod.Ord

58、erQty) AS QtySoldSUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-01 WHERE soh.OrderD

59、ate = 2003-08-01 GROUP BY soh.OrderDate, sod.ProductID GROUP BY soh.OrderDate, sod.ProductID) AS s) AS sON (s.ProductID = smr.ProductID)ON (s.ProductID = smr.ProductID)WHEN MATCHED THENWHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySold UPDATE SET smr.QtySold = smr.QtySold + s.QtySold

60、WHEN NOT MATCHED THENWHEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold) INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold); s.ProductID, s.QtySold);59/1

61、31示例（续示例（续3 3）v继续查看该月第继续查看该月第2 2天的总结果。更新日期，继续运行下述代码（仿真在该月第天的总结果。更新日期，继续运行下述代码（仿真在该月第2 2天运行）天运行）MERGE Sales.MonthlyRollup AS smrMERGE Sales.MonthlyRollup AS smrUSING (USING ( SELECT soh.OrderDate, sod.ProductID, SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySoldSUM(sod.OrderQty) AS Qty

62、Sold FROM Sales.SalesOrderHeader soh FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-02 WHERE soh.OrderDate = 2003-08-02 GROUP BY soh.OrderDate,

63、sod.ProductID GROUP BY soh.OrderDate, sod.ProductID) AS s) AS sON (s.ProductID = smr.ProductID)ON (s.ProductID = smr.ProductID)WHEN MATCHED THENWHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySold UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldWHEN NOT MATCHED THENWHEN NOT MATCHED THE

64、N INSERT (Year, Month, ProductID, QtySold) INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold); s.ProductID, s.QtySold);60/1317.4.2 7.4.2 公用表表达式公用表表达式v公公 用用 表表 表表 达达

65、 式式 （ CTECTE， Common Common Table Table Expression)Expression)v将将查查询询语语句句产产生生的的结结果果集集指指定定一一个个临临时时命命名名的的名名字，这些命名的结果集就称为字，这些命名的结果集就称为公用表表达式公用表表达式。v命命名名好好公公用用表表表表达达式式后后就就可可以以在在SELECTSELECT、INSERTINSERT、UPDATEUPDATE、DELETEDELETE等语句中被多次引用。等语句中被多次引用。v公公用用表表表表达达式式还还可可以以包包括括对对自自身身的的引引用用，这这种种表表达式称为达式称为递归公用表表

66、达式递归公用表表达式。61/131公用表表达式的语法格式公用表表达式的语法格式WITH ,.n WITH ,.n :=:= expression_name ( column_name expression_name ( column_name ,.n ) ,.n ) AS AS ( SELECT ( SELECT 语句语句 ) )62/131示例示例v例例9 9 定义一个统计每门课程的选课人数的定义一个统计每门课程的选课人数的简单简单CTECTE，并利用该，并利用该CTECTE查询课程号和选课查询课程号和选课人数。人数。 WITH WITH CnoCount(Cno, Counts) CnoC

67、ount(Cno, Counts) AS (AS ( SELECT Cno, COUNT(*) FROM SC SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) GROUP BY Cno ) SELECT Cno, Counts FROM SELECT Cno, Counts FROM CnoCountCnoCount ORDER BY Counts ORDER BY Counts63/131v例例10 10 使用公用表表达式来限制返回结果。使用公用表表达式来限制返回结果。改进例改进例9 9的查询，定义一个统计每门课程的选的查询，定义一个统计每门课程的选课

68、人数的课人数的CTECTE，并利用该，并利用该CTECTE查询选课人数超查询选课人数超过过2 2人的课程。人的课程。WITH WITH CnoCount(Cno, Counts) CnoCount(Cno, Counts) AS (AS ( SELECT Cno, COUNT(*) FROM SC SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) GROUP BY Cno )SELECT Cno, Counts FROM SELECT Cno, Counts FROM CnoCountCnoCount WHERE Counts 2 WHERE Counts

69、 2 ORDER BY Counts ORDER BY Counts64/131EmployeesEmployees表及数据示例表及数据示例65/131示例示例v首先建立两个包含职工全部信息的首先建立两个包含职工全部信息的CTECTE，然后利用这两个然后利用这两个CTECTE查询每个职工信息及查询每个职工信息及上级领导信息。上级领导信息。WITH WITH Emp1Emp1 AS (SELECT * FROM Employees ), AS (SELECT * FROM Employees ), Emp2Emp2 AS (SELECT * FROM Employees ) AS (SELECT

70、 * FROM Employees )SELECT * FROM SELECT * FROM Emp1Emp1 JOIN JOIN Emp2Emp2 ON Emp1.ManagerID = Emp2.EmployeeID ON Emp1.ManagerID = Emp2.EmployeeID66/131查询结果图示查询结果图示67/131递归递归CTECTE：包含三部分内容：包含三部分内容v例例程程的的调调用用。递递归归CTECTE的的第第一一个个调调用用包包括括一一个个或或多多个个由由UNION UNION ALLALL、UNIONUNION、EXCEPTEXCEPT或或INTERSECTI

71、NTERSECT运运算算符符联联接接的的CTECTE查查询询定定义义，这这些些查查询询定定义义形形成成了了CTECTE结结构构的的基基准准结结果果集集，因因此此被被称称为为“定定位位点点成成员员”。所所有有定定位位点点成成员员查查询询定定义义必必须须放放置置在在第第一一个个递递归归成成员员定定义义之之前前，而而且且必必须须使使用用UNION UNION ALLALL运运算算符符联联接接最最后后一一个个定位点成员和第一个递归成员。定位点成员和第一个递归成员。v例例程程的的递递归归调调用用。递递归归调调用用包包括括一一个个或或多多个个由由引引用用CTECTE本本身身的的UNION UNION AL

72、LALL运运算算符符联联接接的的CTECTE查查询询定定义义。这这些查询定义被称为些查询定义被称为“递归成员递归成员”。v终终止止检检查查。终终止止检检查查是是隐隐式式的的；当当上上一一个个调调用用中中未未返回行时，递归将停止。返回行时，递归将停止。68/131简单递归简单递归CTECTE伪代码伪代码WITH cte_name ( column_name ,.n )WITH cte_name ( column_name ,.n )ASAS( (CTE_query_definition CTE_query_definition - - 定义定位点成员定义定位点成员UNION ALLUNION A

73、LLCTE_query_definition CTE_query_definition - - 定义引用定义引用cte_namecte_name的递归成员的递归成员) )SELECT * FROM cte_name SELECT * FROM cte_name - - 使用使用CTECTE的语句的语句69/131示例示例v例例12 12 显示上级领导职工号、领导职务、领导管辖的显示上级领导职工号、领导职务、领导管辖的职工以及领导所在层次（设最高层领导层次为职工以及领导所在层次（设最高层领导层次为0 0）WITHWITH DirectReports(ManagerID, EmployeeID,

74、Title, Level) DirectReports(ManagerID, EmployeeID, Title, Level) AS (AS ( SELECT ManagerID, EmployeeID, Title, 0 AS Level SELECT ManagerID, EmployeeID, Title, 0 AS Level FROM Employees FROM Employees WHERE ManagerID IS NULL WHERE ManagerID IS NULL UNION ALLUNION ALL SELECT e.ManagerID, e.EmployeeID,

75、 e.Title,d.Level+1 SELECT e.ManagerID, e.EmployeeID, e.Title,d.Level+1 FROM Employees e INNER JOIN DirectReports d FROM Employees e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) ON e.ManagerID = d.EmployeeID )SELECT ManagerID, Title, EmployeeID, Level SELECT ManagerID, Title, EmployeeID

76、, Level FROM DirectReportsFROM DirectReports70/131执行结果图示执行结果图示71/1315 5删除删除JAVAJAVA考试成绩最低的学生的考试成绩最低的学生的JAVAJAVA修课修课记录。记录。 DELETE FROM SC WHERE Sno IN DELETE FROM SC WHERE Sno IN - - 查询查询JAVAJAVA成绩最低学生成绩最低学生 ( SELECT TOP 1 WITH TIES Sno ( SELECT TOP 1 WITH TIES Sno FROM SC JOIN Course C FROM SC JOIN Course C ON C.Cno = SC.Cno ON C.Cno = SC.Cno WHERE Cname = JAVA WHERE Cname = JAVA ORDER BY Grade ASC ) ORDER BY Grade ASC ) AND Cno IN ( SELECT Cno FROM Course AND Cno IN ( SELECT Cno FROM Course WHERE Cname = JAVA ) WHERE Cname = JAVA )72/1317.2 7.2 子查询（子查询（subquerysubquery）