第06章 分组查询和多表查询-MySQL.pptx

课程主讲人：第06章 分组查询和多表查询-MySQLMySQL数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 6 章 分组查询和多表查询工作情境和任务n在数据库SchoolDB中uStudent表中就存放了学号、姓名等信息uSubject表中存储课程编号和课程名称信息uResult表中存储的是学号、课程编号和考试成绩等信息u在实际应用中u某些应用既需要显示学号、姓名，还得显示课程名称和考试成绩u需要将“学生信息”表、“课程”表和“成绩”表联合起来进行查询工作情境和任务n实现分组查询n通过多表连接获得学生成绩单n通过子查询实现多表查询知识和技能目标n理解和掌握分组查询和连接查询的机制n使用GROUP BY和HAVING子句实现分组和筛选查询n掌握多表连接查询及应用n掌握子查询及应用本章重点和难点n外连接和内连接的区别及应用场景n灵活使用多表连接查询或者子查询解决实际问题知识技能结构图第1章 创建和管理数据库分组查询2 2多表查询1 13 3子查询目 录内容结构图n在实际的信息处理中，经常需要进行分类统计u例如：统计每个人的平均成绩u首先需要对成绩表的记录按照学号来分组u再对每组计算平均成绩n分类统计在实际应用场合比较多u例如，网上书店销售系统，需要分类统计不同种类图书的总数、平均价格u首先按照图书种类进行分类，然后在每组的基础上分别进行汇总和统计u分组后的统计计算要利用聚合函数COUNT()、AVG()等分类统计n使用该子句实现分组n只有一列表示单列分组查询n如果分多列进行分组查询，需要列出具体的列名，以分号分隔分析分析分组查询 语法语法n单列分组查询u成绩表中存储了学生参加考试的成绩u需要统计不同科目的平均成绩u首先需要对成绩表中的记录按照科目来分组u再针对每个组进行平均成绩计算u使用聚合函数AVG()使用GROUP BY进行分组查询【演示示例6-1】统计每门课的平均分【演示示例6-1】 统计每门课程的平均分n数据库SchoolDB的成绩表Result中u统计每门课程的平均分【演示示例6-1】统计每门课的平均分需求需求n成绩记录了多门课程的学生成绩，统计不同科目的平均成绩n把相同的subjectId都分为一组，这样就将数据分成了多组n针对每一组使用聚合函数取平均分分析分析【演示示例6-1】统计每门课的平均分分析代码分析代码【演示示例6-1】统计每门课的平均分分析结果分析结果操作操作演示演示：统计每门课的平均分：统计每门课的平均分n能否输出显示学号信息u不可以u学生的学号与课程不是一对一的关系u因为科目已经被“分组”了，分组后的数量减少为8组u每组中有多位学生，即多个学号【演示示例6-1】统计每门课的平均分拓展拓展【演示示例6-1】统计每门课的平均分拓展拓展一起努力 共同提高【演示示例6-2】统计每位学生的平均分【演示示例6-2】 统计每位学生的平均分n数据库SchoolDB的成绩表Result中u统计每位学生的平均分u按照平均分由高到低的顺序排列显示【演示示例6-2】统计每位学生的平均分需求需求n求每位同学的平均分u需要前按照学号分组，再使用聚会函数AVG()n对显示结果排序u排序的依据是平均分，即利用聚合函数AVG()作为排序的依据分析分析【演示示例6-2】统计每位学生的平均分分析代码分析代码【演示示例6-2】统计每位学生的平均分分析结果分析结果操作操作演示演示：统计：统计每位学生每位学生的平均分的平均分一起努力 共同提高n多列分组查询u在实际应用中，分组查询时还可以按照多个列来进行分组u比如：按照年级统计男女生的人数u先按照年级分组，再按照性别分组进行统计u使用COUNT()函数使用GROUP BY进行分组查询【演示示例6-3】统计每个年级的男女生人数【演示示例6-3】 统计每个年级的男女生人数n数据库SchoolDB的成绩表Result中u统计每个年级的男女生人数u按照年级编号显示【演示示例6-3】统计每个年级的男女生人数需求需求n需要使用多列分组查询uGROUP BY后面需要2列，年级编号和性别u年级编号在前面，使用逗号分隔n排序使用ORDER BY子句u写在GROUP BY子句的后面，排序依据为年级编号分析分析【演示示例6-3】统计每个年级的男女生人数分析代码分析代码【演示示例6-3】统计每个年级的男女生人数分析结果分析结果操作操作演示演示：统计每个年级男女生人数：统计每个年级男女生人数一起努力 共同提高【技能训练6-1】分组查询应用【技能训练6-1】 分组查询应用n使用GROUP BY进行分组查询n灵活应用GROUP BY和ORDER BY等子句解决实际问题技能目标技能目标需求分析需求分析n查询每个年级的总学时数，并按照总学时降序排列【技能训练6-1】分组查询应用需求分析需求分析n查询参加每门课考试的人数，并按照考试人数降序排列【技能训练6-1】分组查询应用需求分析需求分析n查询每个年级的总人数n查询每个学生参加的所有考试的总分，并按照总分降序排列【技能训练6-1】分组查询应用需求分析需求分析n查询每门课的最高分和最低分，并按照课程编号升序排列【技能训练6-1】分组查询应用需求分析需求分析n查询每位同学考试的最高分和最低分，并按照最高分降序排列【技能训练6-1】分组查询应用一起努力 共同提高n在实际应用中，有的需求还需要在分组统计的基础上进行筛选u对分组统计的结果进行过滤u比如：统计总分达到200分的学生信息nHAVING子句u实现分组后的筛选功能uHAVING子句要用在GROUP BY子句后，用于过滤分组后的结果n与WHERE子句比较uWHERE子句是用来在FROM子句中选择行uHAVING子句是在GROUP BY子句后选择行使用HAVING子句进行分组筛选nHAVING和WHERE子句可以一起使用n使用顺序使用HAVING子句进行分组筛选示例示例【演示示例6-4】分组筛选的应用【演示示例6-4】分组筛选的应用n在数据库SchoolDB的学生信息表Student中u查询年级总人数超过4的年级编号【演示示例6-4】分组筛选的应用需求需求n通过分组查询获取每个年级的总人数u使用GROUP BY子句，按照年级编号分组u使用COUNT()函数统计n在GROUP BY后面使用HAVING子句，条件:”COUNT(*)4”分析分析【演示示例6-4】分组筛选的应用分析代码分析代码【演示示例6-4】分组筛选的应用分析结果分析结果操作操作演示演示：分组筛选的应用：分组筛选的应用n使用WHERE子句是不能满足查询要求的uWHERE子句只能对没有分组统计前的数据进行筛选n用HAVING子句来指定筛选的条件u该子句中的条件通常是统计函数u如：条件为“COUNT(*)4”【演示示例6-4】分组筛选的应用注意注意一起努力 共同提高【演示示例6-5】分组筛选的综合应用【演示示例6-5】分组筛选的综合应用n在数据库SchoolDB的成绩表Result中u统计每门科目及格总人数u统计及格学生的平均分达到80分的课程信息u按照平均分降序显示【演示示例6-5】分组筛选的综合应用需求需求n所查询的信息都是及格的信息u先从数据源中将不及格的学生信息进行滤除u使用WHERE子句n对符合及格要求的数据再进行分组u通过分组查询获取每门课及格学生的人数和平均分u通过GROUP BY子句实现分组n筛选出平均分达到80分的课程信息u在分组统计出的平均分的基础上，使用HAVING子句【演示示例6-5】分组筛选的综合应用分析分析n步骤一：使用WHERE子句选择及格的信息n步骤二：使用GROUP BY对数据再进行分组n步骤三：使用HAVING筛选出平均分达到80分的课程信息【演示示例6-5】分组筛选的综合应用关键步骤关键步骤【演示示例6-5】分组筛选的综合应用分析代码分析代码【演示示例6-5】分组筛选的综合应用分析结果分析结果操作操作演示演示：分组筛选的综合应用：分组筛选的综合应用n理解各子句的功能和使用顺序uWHERE子句uGROUP BY子句uHAVING子句uORDER BY子句n在所有子句中，ORDER BY子句一般都在最后u用于对查询最后结果的显示排序【演示示例6-5】分组筛选的综合应用注意注意一起努力 共同提高【技能训练6-2】分组筛选的综合应用【技能训练6-2】 分组查询和筛选的综合应用n灵活使用GROUP BY进行分组查询n应用HAVING子句对分组统计后的结果进行筛选n灵活应用多个子句解决实际问题uWHERE子句uGROUP BY子句uHAVING子句uORDER BY子句技能目标技能目标【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询年级总学时超过50的课程数【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询每年级学生的平均年龄u年龄的计算精确到年份，不考虑月和日的信息【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询安徽地区的每年级学生人数【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询参加考试的学生中，平均分及格的学生记录u按照成绩降序排列【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询考试日期为2019年11月20日的科目的及格平均分u排除不及格的成绩后求平均分【技能训练6-2】分组筛选的综合应用需求分析需求分析n查询至少一次考试不及格的学生学号、不及格次数【技能训练6-2】分组筛选的综合应用需求分析需求分析n在学生成绩表中，查询有两次及以上分数不低于85的学生的学号【技能训练6-2】分组筛选的综合应用n理解各子句的功能和使用顺序uWHERE子句从数据源中去掉不符合其搜索条件的数据uGROUP BY子句搜集数据行到各个组中u统计函数为各个组计算统计值uHAVING子句去掉不符合其组搜索条件的各组数据行uORDER BY子句对最后的结果进行显示排序【技能训练6-2】分组筛选的综合应用小结小结一起努力 共同提高知识技能结构图第1章 创建和管理数据库分组查询1 1多表查询子查询2 23 3目 录内容结构图n在实际应用中，很多查询需要的数据要来源于多张表u比如：在查询成绩的结果中显示学生姓名和课程名称u涉及到三张表 成绩表Result 学生信息表Student 课程表Subjectn查询命令SELECT支持多表连接查询u内连接查询u外连接查询多表查询n内连接查询是最典型、最常用的连接查询，它根据表中共同的列来进行匹配。特别是两张表存在主外键关系时通常会使用内连接查询。nMySQL数据库中，内连接查询可以通过两种方式实现，一种是在FROM子句中使用INNER JOINON关键字，另一种是在WHERE子句中设置连接条件n在实际应用中，很多查询需要的数据要来源于多张表u比如：在查询成绩的结果中显示学生姓名和课程名称内连接查询n将查询需要数据所在的数据表分别列在FROM子句后面u表名之间用逗号分隔n将表与表之间连接的条件作为WHERE子句的条件表达式分析分析在WHERE子句中指定连接条件 语法语法【演示示例6-6】2张表的内连接查询应用【演示示例6-6】2张表内连接查询的应用n数据库SchoolDB中，查询学生的成绩u显示学号、姓名、课程编号和成绩，按照学号排列【演示示例6-6】2张表的内连接查询应用需求需求n需要查询的结果来源于2张表，需要2张表连接查询n成绩表中的学号引用了学生信息表的学号，连接条件：学号相等nStudent和Result中学号列的名称都是“studentNo”u需要在列名的前面加上表的名称，并使用点“.”作为连接符u如：“Student. studentNo”和“Result. studentNo”分析分析【演示示例6-6】2张表的内连接查询应用分析代码分析代码【演示示例6-6】2张表的内连接查询应用分析结果分析结果操作操作演示演示：2张表内连接查询的应用张表内连接查询的应用n多表列名相同问题的处理u只有当多表中存在相同名称的列时，需要使用“表名.列名”来区别u如果列名不相同时可以直接使用列名【演示示例6-6】2张表的内连接查询应用拓展拓展一起努力 共同提高n通过FROM子句中使用INNER JOINON关键字u不需要在WHERE子句中设计表连接的条件表达式u可以连接2张表，也可以连接3张表等分析分析在FROM子句中使用INNER JOINON 语法语法n数据库SchoolDB中，查询学生的成绩u显示学号、姓名、课程编号和成绩，按照学号排列需求需求再解【演示示例6-6】多表连接查询nINNER JOIN用来连接两个表，INNER可以省略nON用来设置条件，表示两张表连接的条件分析分析在FROM子句中使用INNER JOINONnAS指定表的“别名”u即表的名称可以用简单的别名表示u如：“Student AS S”，S表示Student表的别名u使用“Student”和使用“S”等价u如“S.studentNo”等同于“Student.studentNo”分析分析在FROM子句中使用INNER JOINON一起努力 共同提高【演示示例6-7】查询某门课及格学生的信息【演示示例6-7】查询某门课及格学生的信息n数据库SchoolDB中n查询课程编号为3的及格学生的姓名和分数【演示示例6-7】查询某门课及格学生的信息需求需求n需要查询的结果来源于2张表，需要2张表连接查询n采用组合条件u统计及格的学生使用WHERE子句u课程编号为“3”也是使用WHERE子句u使用逻辑运算符“AND”连接2个条件分析分析【演示示例6-7】查询某门课及格学生的信息分析代码分析代码【演示示例6-7】查询某门课及格学生的信息分析结果分析结果操作操作演示演示：查询某门课及格学生的信息：查询某门课及格学生的信息n多条件的应用u当查询的条件比较多时，建议先使用一个条件，查看一个条件的结果u再增加一个条件，和之前的结果作对比u更容易理解查询条件的含义和应用【演示示例6-7】查询某门课及格学生的信息经验经验一起努力 共同提高n多表连接u内连接查询可以连接两个表，还可以实现涉及三个表或者更多表u查询更多需要的信息n实现的方式u2张连接查询的方式相同u在FROM子句增加需要的表名u或者在WHERE中增加需要的连接条件3张表及更多表的查询【演示示例6-8】查询指定同学的信息【演示示例6-8】查询指定学生的信息n数据库SchoolDB中，查询“胡保蜜”同学的成绩u显示学号、姓名、课程名称和考试成绩u按照课程编号升序排序【演示示例6-8】查询指定同学的信息需求需求n所需查询的信息来源于3张表，需要3张表的连接查询n涉及3张表的查询，连接条件至少需要2个u先将Result表和Student表连接，连接条件是学号相等u再继续和Subject表连接，连接条件是课程编号相等分析分析【演示示例6-8】查询指定同学的信息代码分析代码分析【演示示例6-8】查询指定同学的信息分析结果分析结果操作操作演示演示：查询指定学生的信息：查询指定学生的信息n3张表的连接顺序u连接的表之间需要有主外键约束关系u不可以将Student表和Subject表先连接u2这之间没有含义相同的字段，找不到连接的条件【演示示例6-8】查询指定同学的信息拓展拓展nWHERE子句中通过设置连接条件u3个条件u使用AND连接【演示示例6-8】查询指定同学的信息拓展拓展一起努力 共同提高【技能训练6-3】多表内连接查询应用【技能训练6-3】 多表内连接查询的综合应用n掌握2张表和3张表的内连接查询的机制和实现方式n灵活应用INNER JOINON实现多表查询n灵活应用WHERE子句设置连接条件u实现多表查询u实现数据筛选条件与表间连接条件的合理搭配n所有查询均需要使用INNER JOINON和WHERE两种方式实现技能目标技能目标【技能训练6-3】多表内连接查询应用需求分析需求分析n查询“S1”年级开设课程的课程名称及学时【技能训练6-3】多表内连接查询应用需求分析需求分析n查询学生姓名、所属年级名称及联系电话【技能训练6-3】多表内连接查询应用需求分析需求分析n查询参加课程编号为1的课程考试的学生姓名、分数、考试日期【技能训练6-3】多表内连接查询应用需求分析需求分析n查询“G1263458”同学参加的考试科目名称、分数和考试日期【技能训练6-3】多表内连接查询应用需求分析需求分析n查询所有考试成绩达到90分的成绩信息u显示学生学号、课程名称、分数和考试日期【技能训练6-3】多表内连接查询应用需求分析需求分析n查询所有考试成绩不及格学生的信息u显示学号、姓名、考试科目名称及成绩【技能训练6-3】多表内连接查询应用需求分析需求分析n查询参加“C语言程序设计”考试的学生信息u显示学号、姓名、课程名称、成绩、考试日期【技能训练6-3】多表内连接查询应用一起努力 共同提高n在实际应用中，很多查询需要的数据要来源于多张表u比如：在查询成绩的结果中显示学生姓名和课程名称u涉及到三张表 成绩表Result 学生信息表Student 课程表Subjectn查询命令SELECT支持多表连接查询u内连接查询u外连接查询多表查询n应用场合u使用内连接查询会出现查询信息不完整情况u比如：查询所有课程的考试成绩，如果某门课没有被学生选修u则该门课程不会出现在查询结果结果中u如果需要查询未被选修的课程信息，可以通过外连接查询实现外连接查询n外连接查询与内连接查询最大区别u外连接查询中参与连接的表有主从之分u以主表的每行数据匹配从表的数据，将符合条件的数据直接返回到结果集中u对于不符合连接条件的列，在结果集中被填上NULL值（空值）n外连接分类u左外连接（LEFT OUTER JOIN）u右外连接（RIGHT OUTER JOIN)u其中OUTER关键字可以省略外连接查询n左外连接（LEFT OUTER JOIN）u结果表中除了匹配行外（即与内连接查询结果相同的部分）u还包括左表中有的但右表中不匹配的行，从右表选择列的列值为NULLu查询结果包括 第一个命名表（“左”表，出现在JOIN子句的最左边）中的所有行 不包括右表中的不匹配行u连接条件和方式与内连接相同，主要体现在查询的结果集不同上左外连接查询【演示示例6-9】统计所有课程选修情况【演示示例6-9】统计所有课程选修情况n数据库SchoolDB中，查询统计所有课程的选修及成绩信息n显示学号、姓名、课程名称和考试成绩，按照课程编号升序排序u如果在Result表中有成绩，则表示该同学选修了该门课u如果某门课Result表中没有一条成绩记录，则表示无人选修该课程，对应课程的学号和成绩显示为NULL需求需求n要查询包括没有被选修的课程信息，使用外链接n使用左外连接，左表为课程表Subject，右表为成绩表Result分析分析【演示示例6-9】统计所有课程选修情况代码分析代码分析【演示示例6-9】统计所有课程选修情况分析结果分析结果操作操作演示演示：统计统计每门课程选修情况每门课程选修情况【演示示例6-9】统计所有课程选修情况n查询结果分析u查询结果共48条u包括了有匹配值的42行u还包含了成绩表中无匹配的行，即表示成绩表中没有该课程的成绩信息，学号和成绩都显示为“NULL”拓展拓展【演示示例6-9】统计所有课程选修情况n与内连接的比较拓展拓展【演示示例6-9】统计所有课程选修情况一起努力 共同提高n右外连接（RIGHT OUTER JOIN)u结果表中除了匹配行外（即与内连接查询结果相同的部分）u还包括右表中有的但左表中不匹配的行，从左表选择列的列值为NULLu右外连接查询结果 包括第二个命名表（“右”表，出现在JOIN子句的最右边）中的所有行 不包括左表中的不匹配行n使用RIGHT JOINON或RIGHT OUTER JOINONn右外连接查询可以通过左外连接来实现u将左外连接中的2张表位置互换就实现了右外连接的效果右外连接查询【技能训练6-4】多表外连接查询应用【技能训练6-4】 多表外连接查询的综合应用n掌握2张表和3张表的外连接查询的机制和实现方式n灵活应用LEFT OUTER JOINON实现多表外连接查询n掌握内连接查询和外连接查询的区别与应用技能目标技能目标【技能训练6-4】多表外连接查询应用需求分析需求分析n查询所有没有被学生选修的课程信息u即成绩表没有该课程的成绩信息u显示课程编号、课程名称、学时和开设的学期等信息【技能训练6-4】多表外连接查询应用需求分析需求分析n查询每学年开设课程的信息u包括未开设课程的年级信息u显示年级编号、年级名称、开设课程的编号、课程名称和学时【技能训练6-4】多表外连接查询应用需求分析需求分析n查询所有年级对应的学生信息u某些年级可能还没有学生就读u显示年级名称、学号和姓名等信息【技能训练6-4】多表外连接查询应用一起努力 共同提高知识技能结构图第1章 创建和管理数据库分组查询1 1多表查询子查询2 23 3目 录内容结构图n子查询u将查询的结果直接用于WHERE子句n子查询也是一个SELECT查询u有返回值且嵌套在SELECT、INSERT、UPDATE、DELETE语句中u任何允许使用表达式的地方都可以使用子查询n子查询也称为内部查询或内部选择u包含子查询的语句也称为外部查询或外部选择子查询n子查询能够将比较复杂的查询分解为几个简单的查询n子查询可以嵌套u首先执行内部查询u它查询出来的数据并不被显示出来，而是传递给外层语句u并作为外层语句的查询条件来使用子查询n子查询语句必须放置在一对圆括号内n在字段名后面的运算符除u可以是“”、“(子查询)”n使用子查询实现分析分析【演示示例6-10】查询比某同学小的学生信息代码分析代码分析【演示示例6-10】查询比某同学大的学生信息分析结果分析结果操作操作演示演示：查询比某位同学大的学生信息：查询比某位同学大的学生信息【演示示例6-10】查询比某同学大的学生信息n关于比较运算符与子查询u使用比较运算符“”，要求子查询的结果最多只能有1个u可以为空n最后的返回结果为空，因为在学生信息表中没有“王忧草”的同学拓展拓展【演示示例6-10】查询比某同学大的学生信息一起努力 共同提高n子查询可以在单表内查询n子查询可以实现单表连接查询n在某些应用中还可以替换多表连接（JOIN）查询使用子查询替换表连接【演示示例6-11】子查询替换多表连接查询【演示示例6-11】 子查询替换多表连接查询n使用子查询u查询“大学英语”课程的考试成绩情况u显示课程编号、学号、成绩和考试日期需求需求n查询Subject表，获得“大学英语”课程的课程编号n根据课程编号，查询Result表中的成绩信息分析分析【演示示例6-11】子查询替换多表连接查询代码分析代码分析【演示示例6-11】子查询替换多表连接查询分析结果分析结果操作操作演示演示：子查询替换多表连接查询：子查询替换多表连接查询【演示示例6-11】子查询替换多表连接查询n本示例中需要显示的信息都来源于成绩表Result中u而子查询的最终结果一般只来源于一张表u因此通过子查询可以实现n如果需要显示的信息来源于多张表u必须使用多表连接查询u例如：如果需要同时显示课程名称和成绩u就必须使用多表连接查询，子查询无法实现拓展拓展【演示示例6-11】子查询替换多表连接查询一起努力 共同提高【技能训练6-5】简单子查询应用【技能训练6-5】 简单子查询的应用n理解子查询的机制及实现方式n灵活应用子查询实现在单表和多表间的查询技能目标技能目标【技能训练6-5】简单子查询应用需求分析需求分析n用子查询在SchoolDB数据库中u查询参加最近一次“大学英语”考试的最高分和最低分【技能训练6-5】简单子查询应用n查询获得大学英语科目的科目编号n查询获得大学英语科目最近一次的考试日期u使用MAX()函数，对“大学英语”课程的考试日期进行统计n根据科目编号查询考试成绩的最高分和最低分【技能训练6-5】简单子查询应用 参考步骤参考步骤n查询最近一次“大学英语“课程考试日期的子查询【技能训练6-5】简单子查询应用 参考步骤参考步骤n表连接都可以采用子查询替换n但有的子查询不能用表连接来替换n子查询比较灵活、形式多样，适合于作为查询的筛选条件n而表连接更适合于查看多表的数据【技能训练6-5】简单子查询应用小结小结一起努力 共同提高n在实际应用中u使用“=、=80”n只需要做查询，不需要更数据更新n前五名的处理方式u先对查询结果降序排序u再在排序的后面使用“LIMIT 5”子句【技能训练6-7】 EXISTS子查询应用 关键步骤关键步骤一起努力 共同提高第1章 创建和管理数据库连接MySQL服务器2 2使用Workbench连接登录MySQL服务器1 13 3创建和管理SchoolDB数据库目 录分析分析问题问题注意注意 语法语法练习练习讲解讲解 演示演示