数据基础及应用 8

教案二：嵌套查询、连接查询与窗口函数（4.5-4.7）（2学时）一、授课内容本节课主要讲授MySQL中的高级查询技术，包括嵌套查询（子查询）、连接查询和窗口函数查询，具体包括以下内容：（一）嵌套查询1.嵌套查询的概念在一个SELECT语句的WHERE条件子句中嵌入另一个SELECT查询语句称为嵌套查询。嵌套查询的处理由里向外进行，先处理最内层子查询，再逐层向外执行外层查询。子查询中不能使用ORDERBY子句。2.单值嵌套查询子查询返回一个值，外层可直接使用=、<>、>、<等关系运算符。3.多值嵌套查询子查询返回多个值，需结合ANY（满足任意一个）、ALL（满足所有）、IN（属于集合）、EXISTS（存在性判断）运算符使用。（二）连接查询1.连接概述连接查询用于根据表之间的关系从多个表中检索数据。连接可以在WHERE子句或FROM子句中建立，MySQL推荐在FROM子句中使用JOIN…ON语法。2.内连接（INNERJOIN）内连接只返回符合连接条件的数据行，分为等值连接、不等值连接和自然连接三种。3.外连接（OUTERJOIN）左外连接（LEFTOUTERJOIN）返回左表所有行及右表匹配行，右表无匹配时显示NULL。右外连接（RIGHTOUTERJOIN）返回右表所有行及左表匹配行。4.交叉连接（CROSSJOIN）交叉连接不带WHERE子句，返回笛卡儿积，结果行数等于两表行数的乘积。（三）窗口函数查询1.窗口函数的概念MySQL8.0开始支持窗口函数（OLAP函数），对查询的每一行都使用与该行相关的行进行计算。窗口函数与GROUPBY的区别在于窗口函数会对每个分组后的数据分别操作，而GROUPBY一般使用聚合函数汇总。2.常用窗口函数排序类：ROW_NUMBER（连续序号）、RANK（间断排名）、DENSE_RANK（不间断排名）。偏移类：LAG（向前取值）、LEAD（向后取值）。首末值：FIRST_VALUE、LAST_VALUE。统计类：PERCENT_RANK、CUME_DIST。3.窗口函数语法函数名()OVER([PARTITIONBY分组列][ORDERBY排序列])。PARTITIONBY类似GROUPBY进行分组，ORDERBY指定窗口内的排序。二、教学目标（一）知识目标1.理解嵌套查询的概念和执行过程2.掌握单值嵌套查询和多值嵌套查询的语法3.掌握ANY、ALL、IN、EXISTS运算符的用法4.掌握内连接、外连接和交叉连接的语法和区别5.掌握常用窗口函数的功能和语法（二）能力目标1.能够使用子查询实现复杂的数据检索2.能够根据需求选择合适的连接类型进行多表查询3.能够使用窗口函数实现排名、累计分析等功能（三）素养目标1.培养学生分析复杂查询需求、分解为多步SQL操作的能力三、教学重点与难点（一）教学重点1.多值嵌套查询中ANY、ALL、IN运算符的区别2.内连接、左外连接、右外连接的区别3.ROW_NUMBER、RANK、DENSE_RANK的区别（二）教学难点1.EXISTS和NOTEXISTS的用法和执行逻辑2.窗口函数中PARTITIONBY和ORDERBY的组合使用3.多表连接查询时表别名的使用和字段歧义的处理四、教学实施（一）导入环节（5分钟）上节课我们学习了基本的SELECT查询，但实际应用中经常需要更复杂的查询。比如"查询和李思思同专业的同学"——需要先查李思思的专业，再用这个结果去查其他学生。这就需要嵌套查询。又比如"查询学生成绩单，需要同时显示学号、姓名、课程名称、成绩"——这些信息分散在学生表、课程表和选课表中，需要使用连接查询。（二）讲授环节（55分钟）1.嵌套查询（20分钟）（1）讲解嵌套查询的概念和执行过程（3分钟）强调嵌套查询由里向外执行，子查询中不能使用ORDERBY。（2）单值嵌套查询（5分钟）演示例4-38：查询与"李思思"相同专业的所有同学。（3）多值嵌套查询（12分钟）演示例4-39到例4-42：ANY运算符（比子查询中任意值满足条件即可）、ALL运算符（比子查询中所有值都满足条件）、IN运算符（属于子查询结果集合）、EXISTS运算符（判断子查询是否有结果返回）。2.连接查询（25分钟）（1）连接概述（5分钟）讲解WHERE子句连接和FROM子句JOIN…ON连接两种方式。演示例4-43：三表连接查询学生成绩单。（2）内连接（10分钟）演示等值连接（例4-44：统计每门课程选课人数）、不等值连接（例4-45：自连接查询）、自然连接（例4-46）。（3）外连接和交叉连接（10分钟）演示左外连接（例4-47：包含未选课学生）、右外连接（例4-48：包含无人选课程）、交叉连接（例4-49）。对比左外连接和右外连接的结果差异。3.窗口函数查询（10分钟）（1）讲解窗口函数的概念和语法对比窗口函数与GROUPBY的区别，讲解OVER子句中PARTITIONBY和ORDERBY的作用。（2）演示常用窗口函数演示例4-50：查询各专业平均成绩（AVGOVERPARTITIONBY）。演示例4-51：按成绩排名（RANKOVERORDERBY）。（三）实践环节（25分钟）【课堂练习】学生根据student数据库，完成以下操作：练习1：查询入学成绩高于平均入学成绩的所有学生信息（用子查询）练习2：查询选修了"数据库"相关课程的学生学号和姓名（用IN子查询）练习3：查询有学生选修的课程信息（用EXISTS子查询）练习4：查询所有学生的选课成绩单（学号、姓名、课程名、成绩），包括未选课的学生（用左外连接）练习5：查询每门课程的课程名称和平均成绩（用内连接+GROUPBY）练习6：对学生表按入学成绩排名，使用ROW_NUMBER和RANK分别实现（四）课堂总结（5分钟）1.提问：ANY和ALL运算符的区别是什么？2.提问：左外连接和内连接的区别是什么？3.提问：ROW_NUMBER和RANK在处理相同值时有什么不同？4.总结本节课重点，布置课后习题（教材习题4的问答题和应用题）五、总结与反思（一）本节课总结本节课学习了三种高级查询技术。嵌套查询将一个SELECT嵌入另一个SELECT的WHERE子句中，由里向外执行，支持单值嵌套（使用关系运算符）和多值嵌套（使用ANY、ALL、IN、EXISTS）。连接查询用于从多个表中检索数据，包括内连接（只返回匹配行）、外连接（保留不匹配的行）和交叉连接（笛卡儿积）。窗口函数是MySQL8.0的