联接查询和分组查询

1、第十一章第十一章联接查询和分组查询联接查询和分组查询如下的如下的T-SQL语句含义是什么？语句含义是什么？Temp表中表中A列为商品名称，请说出查询以大写字列为商品名称，请说出查询以大写字母开头的所有商品名称的母开头的所有商品名称的T-SQL语句语句聚合函数聚合函数COUNT()和和SUM()各代表什么意思？各代表什么意思？回顾和作业点评回顾和作业点评SELECT Telephone FROM stuWHERE Telephone NOT LIKE 6%SELECT A FROM TempWHERE A LIKE A-Z% 预习检查预习检查分组查询主要解决什么问题？分组查询主要解决什么问题？多

2、表联接主要解决什么问题？用在什么场合？多表联接主要解决什么问题？用在什么场合？统计学生考试信息统计学生考试信息 从多个数据表中取得学生考试成绩单从多个数据表中取得学生考试成绩单本章任务本章任务使用使用GROUP BY进行分组查询进行分组查询掌握多表联接查询掌握多表联接查询本章目标本章目标学号学号姓名姓名地址地址所属年级所属年级001王明全王明全湖南长沙湖南长沙1002张菲张菲湖北宜昌湖北宜昌1003于寄谦于寄谦甘肃天水甘肃天水1004刘国正刘国正山东荷泽山东荷泽1005周接轮周接轮台湾新竹台湾新竹2006巩小妹巩小妹香港龙湾香港龙湾2007巩大妹巩大妹香港龙湾香港龙湾2008张明敏张明敏北京顺

3、义北京顺义3009矛十八矛十八四川棉阳四川棉阳3010张林光张林光陕西临潼陕西临潼3需求需求1每年级的学生人数各是多少？每年级的学生人数各是多少？分组查询原理分组查询原理第一学期人数第一学期人数第二学期人数第二学期人数第三学期人数第三学期人数学号学号姓名姓名地址地址所属年级所属年级001王明全王明全湖南长沙湖南长沙1002张菲张菲湖北宜昌湖北宜昌1003于寄谦于寄谦甘肃天水甘肃天水1004刘国正刘国正山东荷泽山东荷泽1005周接轮周接轮台湾新竹台湾新竹2006巩小妹巩小妹香港龙湾香港龙湾2007巩大妹巩大妹香港龙湾香港龙湾2008张明敏张明敏北京顺义北京顺义3009矛十八矛十八四川棉阳四川棉阳

4、3010张林光张林光陕西临潼陕西临潼3三三组组分组查询用法分组查询用法SELECT FROM WHERE GROUP BY 分组查询语句分组查询语句SELECT COUNT(*) AS 人数人数, SGrade AS 年级年级FROM Students GROUP BY SGrade演示示例演示示例1：分组查询：分组查询SELECT CourseID, AVG(Score) AS 课程平均成绩课程平均成绩FROM ScoreGROUP BY CourseID分组查询解析分组查询解析2-1对分组后的每个组内的记录进行一个聚集，通常用到聚合函数对分组后的每个组内的记录进行一个聚集，通常用到聚合函数

5、查询每门课程的平均分查询每门课程的平均分按照什么进行分组？按照什么进行分组？分组查询解析分组查询解析2-2SELECT CourseID, AVG(Score) AS 课程平均成绩课程平均成绩FROM ScoreGROUP BY CourseIDORDER BY AVG(Score) 查询每门课程的平均分，并且按照分数由查询每门课程的平均分，并且按照分数由低到高的顺序排列显示低到高的顺序排列显示SELECT StudentID, CourseID, AVG(Score) AS 课程平均成绩课程平均成绩FROM ScoreGROUP BY CourseID思考思考分组查询所查询的列分组查询所查询

6、的列1、分组列、分组列2、聚合函数计算出的列、聚合函数计算出的列分析以下的分析以下的T-SQL，结果会怎样？，结果会怎样？练习练习 使用分组查询学生相关信息使用分组查询学生相关信息 需求说明需求说明查询每个年级的总学时数，并按照升序排列查询每个年级的总学时数，并按照升序排列查询每个参加考试的学员的平均分查询每个参加考试的学员的平均分查询每门课程的平均分，并按照降序排列查询每门课程的平均分，并按照降序排列查询每个学生参加的所有考试的总分，并按照降序查询每个学生参加的所有考试的总分，并按照降序排列排列完成时间：完成时间：15分钟分钟共性问题集中讲解共性问题集中讲解n常见调试问题及解决办法常见调试问

7、题及解决办法n代码规范问题代码规范问题共性问题集中讲解共性问题集中讲解需求需求2统计每学期男女同学的人数，该怎么办？统计每学期男女同学的人数，该怎么办？ 对学生进行分对学生进行分组组计算每组学生计算每组学生的总人数的总人数 多列分组多列分组2-1年级分组年级分组性别分组性别分组多列分组多列分组2-2SELECT COUNT(*) AS 人数人数,SGrade AS 年级年级,SSex AS 性别性别 FROM StudentS GROUP BY SGrade,SSex ORDER BY SGrade演示示例演示示例2：多列分组：多列分组ORDER BY 子句在这里的作用？子句在这里的作用？需求

8、需求3如何获得总人数超过如何获得总人数超过15人的年级？人的年级？条件限定条件限定分组筛选分组筛选SELECT FROM WHERE GROUP BY HAVINGSELECT COUNT(*) AS 人数人数,SGrade AS 年级年级FROM Students GROUP BY SGradeHAVING COUNT(*)15 分组筛选语句分组筛选语句演示示例演示示例3：分组筛选：分组筛选WHEREGROUP BYHAVINGWHERE子句：子句：用来筛选用来筛选 FROM 子句中指定的操作所产生的行子句中指定的操作所产生的行 GROUP BY子句：子句：用来分组用来分组 WHERE 子句

9、的输出子句的输出 HAVING子句：子句：用来从分组的结果中筛选行用来从分组的结果中筛选行 WHERE与与HAVING对比对比SELECT 部门编号部门编号, COUNT(*)FROM 员工信息表员工信息表WHERE 工资工资 = 2000GROUP BY 部门编号部门编号HAVING COUNT(*) 1思考思考查询有多个员工的工资不低于查询有多个员工的工资不低于2000 2000 的部门编号的部门编号 分析以下的分析以下的T-SQL，其作用是什么？，其作用是什么？指导指导限定条件的分组查询限定条件的分组查询2-1 训练要点训练要点GROUP BY子句子句HAVING子句子句聚合函数聚合函数

10、需求说明需求说明查询学生相关信息查询学生相关信息查询考试相关信息查询考试相关信息讲解需求说明讲解需求说明指导指导限定条件的分组查询限定条件的分组查询2-2难点分析难点分析计算学生的年龄计算学生的年龄 至少一次不及格的信息至少一次不及格的信息 DATEDIFF(dd,BornDate,GETDATE()/365 WHERE StudentResult60GROUP BY StudentNo完成时间：完成时间：25分钟分钟小结小结如果查询语句如果查询语句将得到以下的查询结果，那么以下的查询语句将得到以下的查询结果，那么以下的查询语句将输出哪些信息？将输出哪些信息？SELECT * FROM ASE

11、LECT A1,B1,MAX(C1) FROM A需求需求4学号学号姓名姓名年级年级001张青裁张青裁1002陈刚陈刚1003苏三东苏三东1学号学号科目科目分数分数001260002270003480如何同时从这两个表中取得数据？如何同时从这两个表中取得数据？内联接内联接(INNER JOIN)外联接外联接左外联接左外联接 (LEFT JOIN)右外联接右外联接 (RIGHT JOIN)常用的多表联接查询常用的多表联接查询内联接内联接3-1梅超风梅超风1陈玄风陈玄风2陆乘风陆乘风3曲灵风曲灵风4100197200189200267300276300381Students Score 梅超风梅超

12、风00197陈玄风陈玄风00189陈玄风陈玄风00267陆乘风陆乘风00276陆乘风陆乘风00381查询结果查询结果 内联接使用比较运算符根据每个表的通用列中的内联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行值匹配两个表中的行 内联接内联接3-2SELECT FROM 表表1INNER JOIN 表表2ON SELECT FROM 表表1，表，表2WHERE SELECT S.SName,C.CourseID,C.Score FROM Score AS CINNER JOIN Students AS SON C.StudentID = S.SCodeSELECT Students.

13、SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID等等价价演示示例演示示例4：内联接：内联接内联接语句内联接语句内联接内联接3-3SELECT S.SName,C.CourseID,C.Score FROM Students AS SINNER JOIN Score AS CON C.StudentID S.SCodeSELECT S.SName,C.CourseID,C.Score FROM Students AS SINNER JOIN Score AS CO

14、N C.StudentID = S.SCode15行行下面的查询语句返回的查询结果是一样的吗？下面的查询语句返回的查询结果是一样的吗？根据前面表中数据，下面的查询语句将会返根据前面表中数据，下面的查询语句将会返回多少行记录？回多少行记录？SELECT Students.SName, Score.CourseID, Score.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID两个语句的执两个语句的执行结果相同行结果相同指导指导两表内联接查询信息两表内联接查询信息2-1 训练要点训练要点两表内联接查询两表内联接查询INNE

15、R JOINON的使用的使用 WHERE的使用的使用需求说明需求说明查询学生相关信息查询学生相关信息查询考试相关信息查询考试相关信息讲解需求说明讲解需求说明指导指导两表内联接查询信息两表内联接查询信息2-2难点分析难点分析查询年级编号为查询年级编号为1的年级名称、科目名称及学时的年级名称、科目名称及学时 SELECT FROM Subject AS J INNER JOIN Grade AS G ON J.GradeId=G.GradeIdWHERE G.GradeId=1完成时间：完成时间：20分钟分钟SELECT S.SName AS 姓名姓名, CS.CourseName AS 课程课程

16、, C.Score AS 成绩成绩FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID)INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)三表内联接三表内联接CourseIDCourseName使用使用WHERE语句如何实现？语句如何实现？练习练习三表内联接查询信息三表内联接查询信息 需求说明需求说明查询学生学号、姓名、考试科目名称及成绩查询学生学号、姓名、考试科目名称及成绩查询参加查询参加“走进走进Java编程世界编程世界”考试的学生姓名、考试的学生姓名、成

17、绩、考试日期成绩、考试日期完成时间：完成时间：10分钟分钟左外联接左外联接梅超风梅超风1陈玄风陈玄风2陆乘风陆乘风3曲灵风曲灵风4100197200189200267300276300381StudentsScore梅超风梅超风00197陈玄风陈玄风00189陈玄风陈玄风00267陆乘风陆乘风00276陆乘风陆乘风00381曲灵风曲灵风NULLNULL查询结果查询结果SELECT S.SName,C.CourseID,C.Score FROM Score AS CLEFT JOIN Students AS SON C.StudentID = S.SCode不一样，主不一样，主表和从表位表和从表

18、位置已互换置已互换SELECT S.SName,C.CourseID,C.Score FROM Students AS SLEFT JOIN Score AS CON C.StudentID = S.SCode主表（左表）主表（左表）Students中中的数据逐条匹配从表的数据逐条匹配从表Score中的数据中的数据1、匹配，返回到结果集、匹配，返回到结果集2、无匹配，、无匹配，NULL值返回值返回到结果集到结果集演示示例演示示例5：左外连接：左外连接猜一猜：这样写，返回的查询结果是一样的吗？猜一猜：这样写，返回的查询结果是一样的吗？SELECT 图书编号图书编号,图书名称图书名称,出版社名称出版社名称FROM 图书表图书表RIGHT OUTER JOIN 出版社表出版社表 ON 图书表图书表.出版社编号出版社编号 = 出版社表出版社表.出版社编号出版社编号右外联接右外联接图书编号图书编号图书名称图书名称出版社名称出版社名称1走进走进Java编程世界编程世界北大出版社北大出版社2HTML和和CSS网页技术网页技术清华出版社清华出版社NULLNULL新知出版社新知出版社也许很久也许很久没出版书没出版书籍了籍了右外联接的原理与