联接查询和分组查询.ppt

第十一章,联接查询和分组查询,如下的T-SQL语句含义是什么？Temp表中A列为商品名称，请说出查询以大写字母开头的所有商品名称的T-SQL语句聚合函数COUNT()和SUM()各代表什么意思？,回顾和作业点评,SELECTTelephoneFROMstuWHERETelephoneNOTLIKE6%,SELECTAFROMTempWHEREALIKEA-Z%,预习检查,分组查询主要解决什么问题？多表联接主要解决什么问题？用在什么场合？,统计学生考试信息从多个数据表中取得学生考试成绩单,本章任务,使用GROUPBY进行分组查询掌握多表联接查询,本章目标,需求1,每年级的学生人数各是多少？,分组查询原理,第一学期人数,第二学期人数,第三学期人数,三组,分组查询用法,SELECTFROMWHEREGROUPBY,分组查询语句,SELECTCOUNT(*)AS人数,SGradeAS年级FROMStudentsGROUPBYSGrade,SELECTCourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseID,分组查询解析2-1,对分组后的每个组内的记录进行一个聚集，通常用到聚合函数,查询每门课程的平均分,按照什么进行分组？,分组查询解析2-2,SELECTCourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseIDORDERBYAVG(Score),查询每门课程的平均分，并且按照分数由低到高的顺序排列显示,SELECTStudentID,CourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseID,思考,分组查询所查询的列1、分组列2、聚合函数计算出的列,分析以下的T-SQL，结果会怎样？,练习使用分组查询学生相关信息,需求说明查询每个年级的总学时数，并按照升序排列查询每个参加考试的学员的平均分查询每门课程的平均分，并按照降序排列查询每个学生参加的所有考试的总分，并按照降序排列,完成时间：15分钟,共性问题集中讲解,常见调试问题及解决办法代码规范问题,共性问题集中讲解,需求2,统计每学期男女同学的人数，该怎么办？,对学生进行分组计算每组学生的总人数,多列分组2-1,年级分组,性别分组,多列分组2-2,SELECTCOUNT(*)AS人数,SGradeAS年级,SSexAS性别FROMStudentSGROUPBYSGrade,SSexORDERBYSGrade,ORDERBY子句在这里的作用？,需求3,如何获得总人数超过15人的年级？,分组筛选,SELECTFROMWHEREGROUPBYHAVING,SELECTCOUNT(*)AS人数,SGradeAS年级FROMStudentsGROUPBYSGradeHAVINGCOUNT(*)15,分组筛选语句,WHERE子句：用来筛选FROM子句中指定的操作所产生的行GROUPBY子句：用来分组WHERE子句的输出HAVING子句：用来从分组的结果中筛选行,WHERE与HAVING对比,SELECT部门编号,COUNT(*)FROM员工信息表WHERE工资=2000GROUPBY部门编号HAVINGCOUNT(*)1,思考,查询有多个员工的工资不低于2000的部门编号,分析以下的T-SQL，其作用是什么？,指导限定条件的分组查询2-1,训练要点GROUPBY子句HAVING子句聚合函数需求说明查询学生相关信息查询考试相关信息,讲解需求说明,指导限定条件的分组查询2-2,难点分析计算学生的年龄至少一次不及格的信息,DATEDIFF(dd,BornDate,GETDATE()/365,WHEREStudentResult60GROUPBYStudentNo,完成时间：25分钟,小结,如果查询语句将得到以下的查询结果，那么以下的查询语句将输出哪些信息？,SELECT*FROMA,SELECTA1,B1,MAX(C1)FROMA,需求4,如何同时从这两个表中取得数据？,内联接(INNERJOIN)外联接左外联接(LEFTJOIN)右外联接(RIGHTJOIN),常用的多表联接查询,内联接3-1,Students,Score,查询结果,内联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行,内联接3-2,SELECTFROM表1INNERJOIN表2ON,SELECTFROM表1，表2WHERE,SELECTS.SName,C.CourseID,C.ScoreFROMScoreASCINNERJOINStudentsASSONC.StudentID=S.SCode,SELECTStudents.SName,Score.CourseID,Score.ScoreFROMStudents,ScoreWHEREStudents.SCode=Score.StudentID,等价,内联接语句,内联接3-3,SELECTS.SName,C.CourseID,C.ScoreFROMStudentsASSINNERJOINScoreASCONC.StudentIDS.SCode,SELECTS.SName,C.CourseID,C.ScoreFROMStudentsASSINNERJOINScoreASCONC.StudentID=S.SCode,15行,下面的查询语句返回的查询结果是一样的吗？,根据前面表中数据，下面的查询语句将会返回多少行记录？,SELECTStudents.SName,Score.CourseID,Score.ScoreFROMStudents,ScoreWHEREStudents.SCode=Score.StudentID,两个语句的执行结果相同,指导两表内联接查询信息2-1,训练要点两表内联接查询INNERJOINON的使用WHERE的使用需求说明查询学生相关信息查询考试相关信息,讲解需求说明,指导两表内联接查询信息2-2,难点分析查询年级编号为1的年级名称、科目名称及学时,SELECTFROMSubjectASJINNERJOINGradeASGONJ.GradeId=G.GradeIdWHEREG.GradeId=1,完成时间：20分钟,SELECTS.SNameAS姓名,CS.CourseNameAS课程,C.ScoreAS成绩FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID),三表内联接,使用WHERE语句如何实现？,练习三表内联接查询信息,需求说明查询学生学号、姓名、考试科目名称及成绩查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期,完成时间：10分钟,左外联接,Students,Score,查询结果,SELECTS.SName,C.CourseID,C.ScoreFROMScoreASCLEFTJOINStudentsASSONC.StudentID=S.SCode,不一样，主表和从表位置已互换,SELECTS.SName,C.CourseID,C.ScoreFROMStudentsASSLEFTJOINScoreASCONC.StudentID=S.SCode,主表（左表）Students中的数据逐条匹配从表Score中的数据1、匹配，返回到结果集2、无匹配，NULL值返回到结果集,猜一猜：这样写，返回的查询结果是一样的吗？,SELECT图书编号,图书名称,出版社名称FROM图书表RIGHTOUTERJOIN出版社表ON图书表.出版社编号=出版社表.出版社编号,右外联接,也许很久没出版书籍了,右外联接的原理与左外联接相同右表逐条去匹配记录；否则NULL填充,练习使用外联接查询信息,需求说明查询所有科目的参考信息查询从未考试的科目信息查询所有年级对应的学生信息,完成时间：15分钟,在一个SQL语句中，GROUPBY、WHERE、HAVING这三部分正确使用次序是什么？将以下的SQL语句改写成内联接查询的语句如下T-SQL语句返回结果是什么？,SELECTStudents.SName,Score.CourseID,Score.ScoreFROMStud