（本科）第06章分组查询和连接查询ppt课件.pptx

《（本科）第06章分组查询和连接查询ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第06章分组查询和连接查询ppt课件.pptx（50页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、课程主讲人：第06章 分组查询和连接查询数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 6 章 分组查询和多表连接查询第 6 章 分组查询和多表连接查询 分组查询2 2 多表连接函数1 1目 录实际需求n每学期的学生人数各是多少？问题问题实际需求n每学期的学生人数各是多少？问题问题第1学期人数第3学期人数第5学期人数三组分组查询GROUP BY子句n分组查询子句SELECT FROM WHERE GROUP BY SELECT GradeId AS 学期学期，COUNT(*) AS 人数人数 FROM Student GROUP BY GradeId演示：分组查

2、询SELECT列表中只能包含1. 被分组的列2. 为每个分组返回一个值的表达式，如聚合函数注意注意!讲解讲解 演示演示分组查询GROUP BY子句n如何查询每门课程的平均分？n 按照什么进行分组？ 问题问题 提问提问SELECT SubjectId AS 课程号课程号, AVG(StudentResult) AS 平均平均分分 FROM Result GROUP BY SubjectId分组查询GROUP BY子句 演示演示演示：分组查询分组查询GROUP BY子句n查询每门课程的平均分，并且按照分数升序显示SELECT SubjectId AS 课程号课程号, AVG(StudentResu

3、lt) AS 平均平均分分 FROM Result GROUP BY SubjectId ORDER BY AVG(StudentResult) 问题问题 演示演示演示：分组查询实际需求n统计每学期男女同学的人数，如何实现？ n 对学生进行分组，先按照学期、再按照性别n 计算每组学生的总人数 问题问题分析分析多列分组查询n统计每学期男女同学的人数，如何实现？ 讲解讲解年级分组性别分组SELECT GradeId AS 年级年级,Sex AS 性别性别, COUNT(*) AS 人数人数 FROM Student GROUP BY GradeId,Sex ORDER BY GradeIdn OR

4、DER BY 子句在这里的作用？ 提问提问多列分组查询演示：多列分组 演示演示实训 使用分组查询学生相关信息 n需求说明u查询每个年级的总学时数，并按照升序排列u查询每个参加考试的学员的平均分u查询每门课程的平均分，并按照降序排列u查询每个学生参加的所有考试的总分，并按照降序排列练习练习完成时间：15分钟实际需求n如何获得总人数达到5人的学期问题问题实际需求n如何获得总人数达到5人的学期问题问题n分组筛选子句SELECT FROM WHERE GROUP BY HAVINGSELECT GradeId AS 学期学期,COUNT(*) AS 人数人数 FROM Student GROUP BY

5、 GradeId HAVING COUNT(*)=5 演示演示 语法语法分组筛选HAVING子句演示：分组筛选n查询平均分数达到及格的科目信息SELECT SubjectId AS 课程编号课程编号,AVG(StudentResult) AS 课程平均成绩课程平均成绩 FROM Result GROUP BY SubjectId HAVING AVG(StudentResult)=60 演示演示分组筛选HAVING子句演示：分组筛选WHERE与HAVING对比nWHERE子句u用来筛选 FROM 子句中指定的操作所产生的行 nGROUP BY子句u用来分组 WHERE 子句的输出 nHAVIN

6、G子句u用来从分组的结果中筛选行 WHEREGROUP BYHAVING讲解讲解n查询每门科目及格总人数和及格学生的平均分SELECT SubjectId AS 课程课程,COUNT(*) AS 人数人数,AVG(StudentResult) AS 平均平均分分 FROM Result WHERE StudentResult=60 GROUP BY SubjectId 演示演示分组筛选HAVING子句演示：分组筛选n查询每门科目及格总人数和及格平均分在80分以上的信息SELECT SubjectId AS 课程课程,COUNT(*) AS 人数人数,AVG(StudentResult) AS

7、平均平均分分 FROM Result WHERE StudentResult=60 GROUP BY SubjectId HAVING AVG(StudentResult)=80 演示演示分组筛选HAVING子句演示：分组筛选n查询“有两次及以上分数不低于85的学生的学号”SELECT StudentNo,COUNT(*) AS 次数次数 FROM Result WHERE StudentResult=85 GROUP BY StudentNo HAVING COUNT(*)1 演示演示分组筛选HAVING子句演示：分组筛选实训 使用HAVING子句查询n需求说明u查询每学期学时超过50的课程

8、数u查询每学期学生的平均年龄u查询安徽地区的每学期学生人数u查询参加考试的学生中，平均分及格的学生信息，并按成绩降序排列u查询至少一次考试不及格的学生学号、不及格次数练习练习完成时间：15分钟n关键点分析u 需求2中计算学生的年龄u 由出生日期计算年龄，使用函数DATEDIFF()u 与当前的日期比较，计算出天数差，然后天数差除以365得学生的年龄u 每年约等于365天实训 使用HAVING子句查询DATEDIFF(dd,BornDate,GETDATE()/365讲解讲解n关键点分析u 需求6中至少一次不及格的信息u WHERE进行不及格的条件限定u 再根据学号来分组获取所需信息实训 使用H

9、AVING子句查询讲解讲解SELECT StudentNo AS 学号学号,COUNT(*) AS 不及格不及格次数次数 FROM Result WHERE StudentResult= 2000 GROUP BY 部门编号部门编号 HAVING COUNT(*) 1思考n分析以下T-SQL语句的作用 提问提问查询有多个员工的工资不低于2000 的部门编号 !共性问题集中讲解n常见问题及解决办法n代码规范问题n调试技巧共性问题集中讲解 一起努力 共同提高 分组查询1 1 多表连接函数2 2目 录实际需求n如何同时从这两个表中取得数据问题问题学号学号姓名姓名年级年级G1263201王子洋王子洋5

10、G1263382张琪张琪5G1263458项宇项宇5学号学号科目科目分数分数G12632011376G12634581392G12633821379常用的多表连接查询n内连接(INNER JOIN)n外连接u左外连接 (LEFT JOIN)u右外连接 (RIGHT JOIN)讲解讲解内连接n使用比较运算符根据表的相同列中的值匹配两个表中的行Student StudentResult 讲解讲解学号学号姓名姓名年级年级G1263201王子洋王子洋5G1263382张琪张琪5G1263458项宇项宇5学号学号科目科目分数分数G12632011376G12634581392G12633821379相同

11、的列，匹配值相同的行内连接n内连接语句SELECT FROM 表表1 INNER JOIN 表表2 ON SELECT FROM 表表1，表，表2 WHERE SELECT Students.StudentName, Result.SubjectId, Result.StudentResult FROM Student, Result WHERE Students.StudentNo = Result.StudentNo 演示演示 语法语法等价等价演示：内连接内连接n下面的查询语句返回的查询结果是一样的吗？SELECT S.StudentName,C.SubjectId,C.StudentRe

12、sult FROM Students AS S JOIN Result AS C ON C.StudentNo S.StudentNoSELECT S.StudentName,C.SubjectId,C.StudentResult FROM Students AS S JOIN Result AS C ON C.StudentNo = S.StudentNon 根据前面表中数据，下面的查询语句将会返回多少行记录？SELECT Students.StudentName, Result.SubjectId, Result.StudentResult FROM Student,Result WHER

13、E Students.StudentNo = Result.StudentNo两个语句的执行结两个语句的执行结果相同果相同 提问提问演示：内连接实际需求n如何同时从这三个表中取得数据问题问题学号学号姓名姓名年级年级G1263201王子洋王子洋5G1263382张琪张琪5G1263458项宇项宇5学号学号课号课号分数分数G12632011376G12634581392G12633821379课程号课程号课程名课程名.1C语言程序设计语言程序设计2大学英语大学英语13软件测试技术软件测试技术SELECT S.StudentName AS 姓名姓名, CS.SubjectName AS 课程课程,

14、C.StudentResult AS 成绩成绩 FROM Student AS S JOIN Result AS C ON S.StudentNo = C.StudentNo JOIN Subject AS CS ON CS.SubjectId = C.SubjectId三表内连接SubjectIdSubjectName讲解讲解SELECT S.StudentName AS 姓名姓名, CS.SubjectName AS 课程课程, C.StudentResult AS 成绩成绩 FROM Student AS S JOIN Result AS C ON S.StudentNo = C.Stu

15、dentNo JOIN Subject AS CS ON CS.SubjectId = C.SubjectId三表内连接n 使用WHERE语句如何实现？ 提问提问讲解讲解SELECT S.StudentName AS 姓名姓名, CS.SubjectName AS 课程课程, C.StudentResult AS 成绩成绩 FROM StudentAS S ,Result AS C , Subject AS CS WHERE (S.StudentNo = C.StudentNo) AND (CS.SubjectId = C.SubjectId)演示：三表内连接实训两表内连接查询信息 n训练要点

16、u两表内连接查询uJOINON的使用 uWHERE的使用u两种方式都要灵活掌握和应用指导指导实训两表内连接查询信息n需求说明u查询学期编号为1的学期名称、科目名称及学时u查询学生姓名、所属学期名称及联系电话u查询参加科目编号为1的考试的学生姓名、分数、考试日期u学号为G1263458的学生参加的考试科目名称、分数、考试日期u查询参加考试的学生学号，所考科目名称、分数、考试日期指导指导讲解需求说明实训两表内连接查询信息n难点分析u查询年级编号为1的年级名称、科目名称及学时 SELECT FROM Subject AS J INNER JOIN Grade AS G ON J.GradeId=G.

17、GradeId WHERE G.GradeId=1指导指导完成时间：25分钟实训三表内连接查询信息 n需求说明u查询学生学号、姓名、考试科目名称及成绩u查询参加“C语言程序设计”考试的学生姓名、成绩、考试日期练习练习完成时间：10分钟100197200189200267300276300381张合肥张合肥1李翡翠李翡翠2王财贸王财贸3胡信息胡信息4左外连接张合肥张合肥00197李翡翠李翡翠00189李翡翠李翡翠00267王财贸王财贸00276王财贸王财贸00381胡信息胡信息NULLNULLStudentsStudentResult查询结果查询结果不一样，主表和从不一样，主表和从表位置已互换表

18、位置已互换主表（左表）Students中的数据逐条匹配表StudentResult中的数据1、匹配，返回到结果集2、无匹配，NULL值返回到结果集讲解讲解左外连接SELECT S.StudentName,C.SubjectId,C.StudentResult FROM Result AS C LEFT JOIN Students AS S ON C.StudentNo = S.StudentNo不一样，主表和从不一样，主表和从表位置已互换表位置已互换SELECT S.StudentName,C.SubjectId,C.StudentResult FROM Students AS S LEFT

19、JOINResult AS C ON C.StudentNo = S.StudentNon 想一想：这样写，返回的查询结果是一样的吗？ 演示演示 提问提问演示：左外连接图书编号图书编号图书名称图书名称出版社名称出版社名称1法律基础法律基础北京大学出版社北京大学出版社2C语言程序设计语言程序设计高等教育出版社高等教育出版社NULLNULL人民邮电出版社人民邮电出版社右外连接n右外连接的原理与左外连接相同n右表逐条去匹配记录；否则NULL填充没有该出版社的图书信息SELECT 图书编号图书编号,图书名称图书名称,出版社出版社名称名称 FROM 图书表图书表 RIGHT JOIN 出版社表出版社表

20、ON 图书表图书表.出版社编号出版社编号 = 出版社表出版社表.出版社编号出版社编号 演示演示讲解讲解实训使用外连接查询信息 n关键点分析u查询所有科目的参考信息 u查询从未考试的科目信息u查询所有年级对应的学生信息讲解讲解实训使用外连接查询信息 n需求说明u查询从未考试的科目信息练习练习完成时间：15分钟SELECT J.SubjectName AS 科目名称科目名称FROM Subject AS J LEFT JOIN Result AS R ON J.SubjectId=R.SubjectIdWHERE R.StudentResult IS NULL AND R.StudentNo IS

21、 NULLSELECT J.SubjectName AS 科目名称科目名称FROM Result AS RLEFT JOIN Subject AS J ON J.SubjectId=R.SubjectIdWHERE R.StudentResult IS NULL AND R.StudentNo IS NULL 执行执行结果相同结果相同总结分组查询是针对表中不同的组分类统计和输出的Having子句能够在分组的基础上，再次进行筛选在一个SQL语句中，执行顺序依次是：联接查询类型内查询：INNER JOIN外查询分组查询+联接查询WHEREGROUP BYHAVING左外查询：LEFT JOIN右外查询：RIGHT JOIN作业n课后作业u继续完成本章的演示需求和训练需求u完成书后习题n预习作业u带着下面问题，阅读、了解“QQ数据库管理”需求 QQ聊天共需要建立几张表？这些表的关系是什么？ 请写出对QQ数据库进行增加聊天记录的T-SQL语句 请写出对QQ数据库进行删除聊天记录的T-SQL语句 请写出对QQ数据库进行修改聊天记录的T-SQL语句 请写出对QQ数据库进行查询聊天记录的T-SQL语句讲解讲解一起努力 共同提高