数据库原理及应用课件：第7章 高级查询

数据库基础与实践技术（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可用在任何允许使用表达式的地方。可用在任何允许使用表达式的地方。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示例示例【例【例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 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函数函数CASECASE 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，则在查询结果中显示，则在查询结果中显示“优优”；如果成绩在如果成绩在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 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 WHEN 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.SnoWHERE 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 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.Cno=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语句中，则称为语句中，则称为子查询。子查询。2.2.嵌套子查询：内层查询中不关联外层查询的子查询。嵌套子查询：内层查询中不关联外层查询的子查询。3.3.相关子查询：内层查询利用外层查询提供的信息执相关子查询：内层查询利用外层查询提供的信息执行。行。4.4.子查询语句可以出现在任何能够用表达式的地方。子查询语句可以出现在任何能够用表达式的地方。7.2 7.2 子查询（子查询（subquerysubquery）1.1.查询学生姓名、所在系和该学生选的课程查询学生姓名、所在系和该学生选的课程门数。门数。SELECT Sname,Dept,SELECT Sname,Dept,(SELECT COUNT(*)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 semester 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 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 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 Semester)AS Max MAX(Credit)OVER(PARTITION BY Semester)AS Max FROM Course FROM CourseOVEROVER子句与聚合函数结合使用子句与聚合函数结合使用3 3查询选了查询选了“JavaJava”课程的学生学号、姓课程的学生学号、姓名和名和JAVAJAVA成绩。成绩。SELECT Student.Sno,Sname,Grade SELECT Student.Sno,Sname,Grade FROM Student FROM Student JOIN SC ON Student.Sno=SC.Sno JOIN SC ON Student.Sno=SC.Sno JOIN Course ON Course.Cno=S