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

1、数据库基础与实践技术（SQL Server 2008）第七章第7章 高级查询7.1 CASE函数7.2 子查询7.3 查询结果的并、交、差运算7.4 其他一些查询功能 7.4.1 开窗函数 7.4.2 公用表表达式 7.4.3 Merge语句2/1317.1 CASE函数是一种多分支函数，可以根据条件列表的值返回多个可能结果中的一个。可用在任何允许使用表达式的地方。不是一个完整的T-SQL语句，不能单独执行。3/1311. 简单CASE函数CASE input_expression WHEN when_expression THEN result_expression .n ELSE else

2、_result_expression END4/131示例【例1】查询选了Java课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：“计算机系”：显示“CS”；“信息管理系”：显示“IM”；“通信工程系”：显示“COM”。 SELECT s.Sno 学号,Sname 姓名, CASE Dept WHEN 计算机系 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函数CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END6/131示例【例2】查询“C001”课程的考试情况，列出学号和成绩，对成绩进行如下处理如果成绩大于等于90，则在查询结果中显示“优”；如果成绩在80到89分之间，则在查询结果中显示“良”；如果成绩在70到79分之间，则在查询结果中显示“中”；如果成绩在60到69分之间，则在查询结果中显示“及格”；如果成绩小于60分，则在查询结果中显示“不及格”。7/1

4、31SELECT Sno, CASE WHEN Grade = 90 THEN 优 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 = 计算机系 GROUP BY S.SnoORDER BY COUNT(SC.Cno) DESC7.1.2 CASE 函数应用示例【例4】修改全体学生的JAVA考试成绩，修改规则如下：对通信工程系学生，成绩加10分；对信息管理系学生，成绩加5分；对其他系学生，成绩不变。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 函数应用示例7.2 子查询7.2.1 嵌套子查询(不相关子查询）7.2.2 相关子查询7.2.3 其他形式的子查询13/131【说明】1.如果一个select语句嵌套在另一个select、insert、update或delete语句中，则称为子查询。2.嵌套子查询：内层查询中不关联外层查询的子查询。3.相关子查询：内层查询利用外层查询提供的信息执行。4.子查询语句可以出现在任何能够用表达式的地方。7.2 子查询（subquery

7、）1.查询学生姓名、所在系和该学生选的课程门数。SELECT Sname,Dept, (SELECT COUNT(*) FROM SC WHERE Sno = Student.Sno ) AS CountCno FROM Student15/1317.2 子查询（subquery）2.查询课程名、开课学期及选该门课的学生人数、平均成绩,不包括没人选的课程。SELECT Cname AS 课程名,semester AS 开课学期, ( SELECT COUNT(*) FROM SC WHERE Cno = Course.Cno ) AS 选课人数, ( 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】查询课程号、课程名、开课学期、学分以及该学期开设课程的总学分、平均学分、最低学分和最高学分。 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子句与聚合函数结合使用EXISTS形式的子查询EXISTS代表存在量词。WHERE NOT EXISTS（子查询）不返回查询的数据，只产生逻辑真值和假值。EXISTS：当子查询中有满足条件的数据时，返回真值，否则返回假值。NOT EXISTS：当子查询中有满足条件的数据时，返回假值；否则返回真值。18/131【例6.1】查询选了JAVA课程的学生姓名和所在系。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课程的学生姓名和所在系。19/131EXISTS形式的子查询not【例7】查询至少选了全部课程的学生的学号、姓名和所在系。该查询的关系代数表达式为： Sno,Sname,Sdept（Student）（SNO,CNO(SC) cno（ourse）SQL语言中没有提供除运算，而且，除运算也不能用如

11、ALL、=ALL、=ALL等量化的谓词形式构造。20EXISTS形式的子查询分析（1）构造反例：设有一门课程是s.sno没有选的（2）将步骤1构造的反例表达为搜索条件；（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”学生所选的全部课程的学生的学号和所选的课程号（1）构造反例：有一个“0811102”学生选的课程是?.sno没有选的。 23EXISTS形式的子查询示例（续）（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派生表也称为内

13、联视图,是将子查询做为一个表来处理。由子查询产生的新表就称之为“派生表”，这很类似于临时表。select * from (select * from table) as temp25/131示例【例9】查询至少选了C001和C002两门课程的学生学号。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 查询结果的并、交、差运算SELECT语句的查询结果是元组的集合，所以多个SELE

14、CT语句的结果可进行集合操作。集合操作主要包括：UNION（并）、INSTERSECT（交）EXCEPT（差）27/7627/131一些说明所有的SELECT语句列表中列的个数必须相同，而且对应列的语义应该相同。各SELECT语句中每个列的数据类型必须兼容。合并后的结果采用第一个SELECT语句的列标题。如果要对查询的结果进行排序，则ORDER BY子句写在最后一个查询语句之后。28/7628/1317.4 其他一些查询功能7.4.1 开窗函数7.4.2 公用表表达式7.4.3 Merge语句29/1317.4.1 开窗函数开窗函数是指可以用于“分区”或“分组”计算的函数。SQL SERVER

15、提供排名开窗函数和聚合开窗函数。结合over子句使用：OVER (PARTITION BY value_expression , . n )30/131【例1】 查询课程号、课程名、开课学期、学分以及该学期开设课程的总学分、平均学分、最低学分和最高学分。 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子句与聚合函数结合使用【例2】设有表MyOrderDetail：32/1311. 将OVER子句与聚合函数结合使用【例2】查询订单号、产品号、订购数量、每个订单的总订购数量以及每个产品的订购数量占该订单总订购数量的百分比。(MySimpleDB)SELECT OrderID 订单号, ProductID 产品号, OrderQty 订购数量, 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子句与聚合函数结合使用34/1311. 将OVER子句与聚合函数结合使用【练习】查询学号、姓名、性别、所在系，以及该系的学生总人数、男女生人数及男女生百分比。1. 将OVER子句与聚合函数结合使用2.over子句与排名函数一起使用排名函数为分区中的每一行返回一个排名值。SQL Server提供了4个排名函数：RANKDENSE_RANKROW_NUMBER

18、NTILE36/131RANK()函数RANK()OVER( , n )RANK()函数返回结果集的分区内每行的排名。每个分区内行的排名从1开始。排序时有相同值的行具有相同排名。37/131查询订单号、产品号、订购数量以及每个产品在每个订单中的订购数量排名。38/131RANK()函数【例3】查询订单号、产品号、订购数量以及每个产品在每个订单中的订购数量排名。(MySimpleDB)SELECT OrderID, ProductID, OrderQty, RANK() OVER (PARTITION BY OrderID ORDER BY OrderQty DESC) AS RANKFROM

19、MyOrderDetailORDER BY OrderID39/131RANK()函数DENSE_RANK()函数DENSE_RANK()函数与RANK()函数的作用基本一样，使用方法也一样。DENSE_RANK()OVER( , n )唯一区别是DENSE_RANK()函数的排名中间没有任何间断，即该函数将返回的是一个连续的整数值。40/13141/131DENSE_RANK()函数ROW_NUMBER()函数返回结果集分区内行的序列号，每个分区的第一行从1开始。 ROW_NUMBER ( ) OVER ( )与Rank()函数的区别是生成的序列号不重复。42/131【例4】 查询“计算机文

20、化学”的考试情况，列出学号、姓名、所在系、考试成绩及成绩排名。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 = 计算机文化学43/131ROW_NUMBER()函数【练习】查询学生学号、选的课程的课程号、考试成绩以及考试成绩在该门课程中排名。2.over子句与排名函数一起使用7.4.2 公用表表达式公用表表达式

21、（CTE，Common Table Expression)将查询语句产生的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表表达式。CTE可以在SELECT、INSERT、UPDATE、DELETE等语句中被多次引用。45/131公用表表达式的语法格式WITH ,.n AS ( SELECT 语句 )其中，:= expression_name ( column_name ,.n ) 46/131示例【例5】 定义一个统计每门课程的选课人数的简单CTE，并利用该CTE查询课程号和选课人数。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT

22、(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount 47/131示例【例6】利用CTE查询选课人数超过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新增加的数据操作语句。该语句的功能是根据源表对目标表执行插

23、入、更新或删除操作。最典型的应用就是进行两个表的同步。MERGE语句最后的分号是不能省略的!49/1311.MERGE语句格式MERGE 目标表USING 源表ON 匹配条件WHEN MATCHED THEN 语句WHEN NOT MATCHED by target|by source THEN 语句;50/131示例【例7】设有Product及ProductNew两个表，现希望将Product表中的数据同步到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表中插入如下数据： INSERT INTO Product Values (4100037,优盘,50), (4100038,鼠标,30), (4100039,键盘,100)52/131修改Product表中“4100037”产品

25、的价格为55。 UPDATE Product SET Price=55 WHERE ProductID = 4100037从Product表中删除“410037”号产品。 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函数7.2 子查询7.3 查询结果的并、交、差运算7.4 其他一些查询功能 7.4.1 开窗函数 7.4.2 公用表表达式 7.4.3 Merge语句55/1311查询选了“Java”课程的学生学号、姓名和JAVA成绩。2.统计选了Ja

27、va课程的这些学生的选课门数和平均成绩。3.查询选了“C001”号课程且成绩高于此课程的平均成绩的学生的学号和成绩。4. 查询没选“C001”号课程的学生姓名和所在系。5.将学分最低的课程的学分加2分。6.查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成绩。不包括没考试的课程。7.查询有最高学分超过本学期平均学分1.5倍的学期。8.查询所学每一门课程成绩均不低于该课程平均成绩的学生姓名及相应课程号。 9.查询学号、姓名、性别、所在系，以及该系的学生总人数、男女生人数及男女生百分比。10删除JAVA考试成绩最低的学生的JAVA修课记录。56/131作 业2.用MERGE语句实现汇总数

28、据同步假设为了做月报表的需要，创建了一个月销售汇总表。希望每日将新的销售记录添加到每月汇总表中。在每个月的第1天晚上，只需将销售记录插入到销售汇总表中即可。但从第2天晚上开始情况就不一样了，对于之前没有销售记录的数据，只需将该数据插入到销售汇总表中；对于之前有销售记录的数据，则需要更新该商品的汇总数据57/131示例（续1）本示例用MySimpleDB数据库中的Sales.SalesOrderHeader和Sales.SalesOrderDetail表中的数据来同步销售汇总数据。在MySimpleDB数据库中创建销售汇总表：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示例（续2）设只对2003年8月的数据进行汇总，从该月的第1天开始。构建MERGE语句，产生2003年8月1日的销售汇总数据。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示例（续3）继续查看该月第2天的总结果。更新日期，继续运行下述代码（仿真在该月第2天运行）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 公用表表达式公用表表达式（CTE，Common Table Expression)将查询语句产生的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表表达式。命名好公用表表达式后就可以在SELECT、INSERT、UPDATE、DELETE等语句中被多次引用。公用表表达式还可

34、以包括对自身的引用，这种表达式称为递归公用表表达式。61/131公用表表达式的语法格式WITH ,.n := expression_name ( column_name ,.n ) AS ( SELECT 语句 )62/131示例例9 定义一个统计每门课程的选课人数的简单CTE，并利用该CTE查询课程号和选课人数。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount ORDER BY Counts63/131例10 使用公用表表达

35、式来限制返回结果。改进例9的查询，定义一个统计每门课程的选课人数的CTE，并利用该CTE查询选课人数超过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表及数据示例65/131示例首先建立两个包含职工全部信息的CTE，然后利用这两个CTE查询每个职工信息及上级领导信息。WITH Emp1 AS (SELECT * FROM Employees ), Emp2 AS (SELECT * FROM Employees )SELECT * FROM Emp1 JOIN Emp2 ON