Chapter04S2升Y2TP04

第四章高级查询（二）,预习检查,EXISTS关键字的应用场景有哪些？EXISTS子查询的语法是怎样的？子查询无返回行时外层查询的结果是什么？SQL语句中SELECT子句、FROM子句中是否可以有子查询？只出现在子查询而没出现在父查询中的字段是否可以出现在输出列中？,集中测试,2/30,回顾与作业点评3-1,下面的SQL语句有错吗？实现了什么功能？上述语句可以用下面的连接替换吗？,可以,SELECT*FROMstudentWHEREstudentNoIN,SELECT*FROMstudentINNERJOINresultONstudent.studentNo=result.studentNo;,(SELECT*FROMresult);,(SELECTstudentNoFROMresult);,提问,3/30,回顾与作业点评3-2,下列语句实现了什么功能？上述语句可以用下面的连接替换吗？,查看没有参加考试的学生,有的子查询不能用连接替换。这就是典型的例子,SELECT*FROMstudentWHEREstudentNoNOTIN(SELECTstudentNoFROMresult);,SELECT*FROMstudentINNERJOINresultONstudent.studentNoresult.studentNo;,不可以,提问,4/30,点评作业的提交情况和共性问题,回顾与作业点评3-3,作业点评,5/30,本章任务,查询S2学员考试成绩信息制作学生成绩单SQL语句的综合应用,6/30,本章目标,掌握EXISTS子查询的用法应用SQL进行综合查询,7/30,EXISTS子查询3-1,如何用SQL语句检测temp表是否已经创建？,DROPTABLEIFEXISTStemp;CREATETABLEtemp(#省略建表语句);,问题,EXISTS关键字是否还有其他用法？,8/30,EXISTS子查询2-1,语法,SELECTFROM表名WHEREEXISTS(子查询);,EXISTS子查询的语法子查询有返回结果：EXISTS子查询结果为TRUE子查询无返回结果：EXISTS子查询结果为FALSE,外层查询不执行,9/30,EXISTS子查询2-2,检查“LogicJava”课程最近一次考试如果有80分以上的成绩，显示分数排在前5名的学员学号和分数,采用EXISTS检测是否有人考试成绩达到80分以上如果有，使用SELECT语句按成绩从高到低排序，显示前5名学员学号和成绩,分析,10/30,NOTEXISTS子查询2-1,检查“LogicJava”课程最近一次考试如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分,可以采用NOTEXISTS检测是否全部未通过考试，即不存在“成绩=60分”的记录,分析,11/30,NOTEXISTS子查询2-2,参考语句,SELECTAVG(studentresult)+5AS平均分FROMresultWHERENOTEXISTS(SELECT*FROMresultWHEREsubjectNo=(SELECTsubjectNoFROMsubjectWHEREsubjectName=LogicJava)ANDexamDate=(SELECTMAX(examDate)FROMresultWHEREsubjectNo=(SELECTsubjectNoFROMsubjectWHEREsubjectName=LogicJava)ANDstudentResult60)ANDsubjectNo=(SELECTsubjectNoFROMsubjectWHEREsubjectName=LogicJava)ANDexamDate=(SELECTMAX(examDate)FROMresultWHEREsubjectNo=(SELECTsubjectNoFROMsubjectWHEREsubjectName=LogicJava);,12/30,学员操作查询S2学员考试成绩信息,需求说明如果有S1的学生，就查询参加S2学科考试的学员学号、科目编号、考试成绩，考试时间检测是否有S2的学生记录如果存在，从成绩表中查询S2科目的学员成绩信息,练习,提示,13/30,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,14/30,子查询注意事项,任何允许使用表达式的地方都可以使用子查询嵌套在父查询SELECT语句的子查询可包括SELECT子句FROM子句WHERE子句GROUPBY子句HAVING子句只出现在子查询中而没有出现在父查询中的列不能包含在输出列中,可选子句，根据业务需求决定,15/30,学员操作制作学生成绩单2-1,训练要点任何允许使用表达式的地方都可以使用子查询需求说明为每个学生制作在校期间每门课程的成绩单，要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩成绩单的数据项学生姓名课程所属的年级名称课程名称考试日期考试成绩,指导,16/30,学员操作制作学生成绩单2-2,实现思路使用分组查询获得每门课程最近一次考试的日期使用连接查询得到各门课程最近一次考试成绩的学生姓名、年级、课程名称、考试日期和考试成绩,SELECTstudentName姓名,(SELECTgradeNameFROMgradeWHEREgradeId=subject.gradeId)AS课程所属年级,subjectName课程名称,examDate考试日期,studentResult成绩FROMresultr1INNERJOINstudentONr1.studentNo=student.studentNoINNERJOINsubjectONsubject.subjectNo=r1.subjectNoWHEREr1.examDateIN(SELECTMAX(examDate)FROMresultr2WHEREr1.subjectNo=r2.subjectNo#主查询和子查询间参数值绑定GROUPBYr2.subjectNo)ORDERBYsubject.gradeId;,子查询嵌套在父查询的WHERE子句中，得到每门课程最近一次考试的日期,子查询嵌套在父查询的SELECT子句中，得到年级名称,指导,17/30,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,18/30,学员操作“我的租房网”数据库设计,客户信息表sys_user,区县信息表hos_district,街道信息表hos_street,SQL语句综合应用“我的租房网”数据库设计2-1,19/30,SQL语句综合应用“我的租房网”数据库设计2-2,房屋类型表hos_type,出租房屋信息表hos_house,20/30,训练要点使用LIMIT关键字实现查询数据分页显示使用临时表保存临时的查询结果需求说明查询输出第6条第10条出租房屋信息,学员操作分页显示查询出租房屋信息2-1,指导,21/30,实现思路使用LIMIT子句实现分页查询起始位置从0开始，第6条记录位置为5使用临时表保存查询结果,学员操作分页显示查询出租房屋信息2-2,指导,CREATETEMPORARYTABLE表名（查询语句）;,语法,临时表只在当前连接可见，连接关闭自动删除，修改临时表数据不影响原表数据,提示,22/30,需求说明查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县结果数据来源于出租房屋信息表、客户信息表、区县信息表、街道信息表使用连接查询和子查询两种方式关联多表数据实现,学员操作查询指定客户发布的出租房屋信息,练习,提示,23/30,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,24/30,需求说明根据户型和房屋所在区县和街道，为至少有两个街道有出租房屋的区县制作出租房屋清单先使用HAVING子句筛选出街道数量大于1的区县,学员操作按区县制作房屋出租清单,练习,提示,25/30,需求说明按季度统计出本年各区县各街道各种户型房屋出租数量输出2016年从1月1日起至今的全部出租房屋数量、各区县出租房屋数量及各街道、户型出租房屋数量,学员操作按季度统计本年发布的房屋出租数量,练习,26/30,常见问题及解决办法代码规范问题调试技巧,共性问题集中讲解,27/30,总结,EXISTS关键字子查询注意事项,检测对象是否存在IFEXISTSEXISTS子查询,高级查询（二）,子查询语句可以嵌套在SQL语句中任何表达式出现的位置只出现在子查询中而没