92TP04

高级查询第四章回顾2-1•指出下列语句的错误：CREATETABLEbank(userNameVARCHAR(10),balanceMONEY)INSERTINTObank(userName,balance)VALUES('张三',500)INSERTINTObank(userName,balance)VALUES('李四',700)DECLAREmymoneyINT(4)mymoney=0SELECTmymoney=balanceFROMbankDECLARE@mymoneyINTSET@mymoney=0SELECT@mymoney=balanceFROMbankWHEREuserName='张三'CREATETABLEbank(userNameVARCHAR(10),balanceMONEY)INSERTINTObank(userName,balance)VALUES('张三',500)INSERTINTObank(userName,balance)VALUES('李四',700)DECLARE@mymoneyINTSET@mymoney=0SELECT@mymoney=balanceFROMbankWHEREuserName='张三'回顾2-2•指出下列语句的错误：IF@mymoney100print'卡上目前余额不足100，请及时充值!'print'卡上余额为：'+@mymoneyprint'您的年利息为:'SELECT利息=CASEWHENbalance100THENbalance*0.01WHENbalance1000THENbalance*0.20WHENELSEbalance*0.10FROMbankWHEREuserName='张三'GO多条语句添加BEGIN-END去掉WHEN缺少配对的END转换:Convert(varchar(10),@mymoney)IF@mymoney100BEGINprint'卡上目前余额不足100，请及时充值!'print'卡上余额为：'+Convert(varchar(10),@mymoney)ENDprint'您的年利息为:'SELECT利息=CASEWHENbalance100THENbalance*0.01WHENbalance1000THENbalance*0.20ELSEbalance*0.10ENDFROMbankWHEREuserName='张三'GO本章任务•查询年龄比“李斯文”大的学生•查询未参加“JavaLogic”课程最近一次考试的学生名单•检查是否有S1的学生。如果有，将他在读年级更新为S2•统计考试缺考情况•制作学生成绩单本章目标•掌握简单子查询的用法•掌握IN子查询的用法•掌握EXISTS子查询的用法•应用SQL进行综合查询什么是子查询3-1学生信息表编写T-SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息？第一步：查询得到“李斯文”的出生日期第二步：利用WHERE语句，筛选出生日期比“李斯文”大的学生什么是子查询3-2实现方法一：采用T-SQL变量实现DECLARE@Birthdaydatetime--定义变量，存放李斯文的出生日期SELECT@Birthday=BornDateFROMstudentWHEREstudentName='李斯文'--求出李斯文的出生日期SELECTStudentNo,StudentName,Sex,BornDate,AddressFROMstudentWHEREBornDate@Birthday--筛选出生日期比李斯文大的学生GO演示案例1：用变量查年龄比“李斯文”大的学生数据库服务器自上而下逐条执行每个SQL语句，前面语句的执行结果可以作为后面语句的条件什么是子查询3-3实现方法二：采用子查询实现SELECTStudentNo,StudentName,Sex,BornDate,AddressFROMStudentWHEREBornDate(SELECTBornDateFROMStudentWHEREStudentName='李斯文')GO子查询在WHERE语句中的一般用法：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询父查询子查询，总是用圆括号括起来演示案例2：用子查询查年龄比李斯文大的学生首先，执行小括号中的子查询，返回的结果是所有来自子查询的结果其次，才开始执行外围的父查询，返回查询的最终结果SELECT…FROM表1WHERE字段1比较运算符(子查询)使用子查询替换表联接3-1学生信息表和成绩表查询“JavaLogic”课程至少一次考试刚好等于60分的学生第一步：查询“JavaLogic”课程的课程编号第二步：根据课程编号查询成绩是60分学生的学号第三步：根据学号查询得到学生姓名使用子查询替换表联接3-2实现方法一：采用表联接SELECTStudentNameFROMStudentstuINNERJOINResultrONstu.StudentNO=r.StudentNoINNERJOINSubjectsubONr.SubjectNo=sub.SubjectNoWHEREStudentResult=60ANDSubjectName='JavaLogic'GO内联接（等值联接）演示案例3：用表联接查询成绩是60分的学生使用子查询替换表联接3-3实现方法二：采用子查询SELECTStudentNameFROMStudentWHEREStudentNo=(SELECTStudentNoFROMResultINNERJOINSubjectONResult.SubjectNo=Subject.SubjectNoWHEREStudentResult=60ANDSubjectName='JavaLogic')GO子查询一般来说，表联接都可以用子查询替换，但有的子查询却不能用表联接替换子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据表联接更适合于查看多表的数据演示案例4：用子查询查询成绩是60分的学生指导—使用子查询查特定学生成绩2-1•训练要点：–使用子查询返回单条记录•需求说明：–查询参加最近一次“C#OOP”考试成绩最高分和最低分讲解需求说明指导——使用子查询查特定学生成绩2-2•实现思路：–查询获得“C#OOP”课程的课程编号–查询获得“C#OOP”课程最近一次的考试日期–根据课程编号查询考试成绩的最高分和最低分SELECTMAX(StudentResult)AS最高分,MIN(StudentResult)AS最低分FROMResultWhereSubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='C#OOP')ANDExamDate=(selectmax(ExamDate)fromResult)完成时间：20分钟小结•什么是子查询？•简述子查询的执行步骤•如何将子查询的查询结果与父查询条件匹配？IN子查询3-1子查询返回不止1个值时，使用比较运算符会出错解决方法：采用IN子查询SELECTStudentNo,StudentNameFROMStudentWHEREStudentNoIN(SELECTStudentNoFROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')--课程ANDStudentResult=60--成绩)将＝号改为ININ后面的子查询可以返回多条记录常用IN替换等于（＝）的比较子查询这是一个三层嵌套的子查询查询“JavaLogic”课程考试成绩为60分的学生名单IN子查询3-2查询参加“JavaLogic”课程最近一次考试的在读学生名单第一步：获得JavaLogic课程的课程编号SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic'SELECTMAX(ExamDate)FROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')第二步：根据课程编号查询得到JavaLogic课程最近一次的考试日期第三步：根据课程编号和最近一次的考试日期查询出在读学生信息IN子查询3-3•参考语句SELECTStudentNo,StudentNameFROMStudentWHEREStudentNoIN(SELECTStudentNoFROMResultWHERESubjectNoIN(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')ANDExamDate=(SELECTMAX(ExamDate)FROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')))获得“JavaLogic”课程的课程编号获得“JavaLogic”课最近一次的考试日期获得参加“JavaLogic”课最近一次考试的学生学号查询出参加“JavaLogic”课最近一次考试的在读学生姓名这是一个四层嵌套的子查询演示案例5：使用IN子查询NOTIN子查询•查询未参加“JavaLogic”课程最近一次考试的在读学生名单SELECTStudentNo,StudentNameFROMStudentWHEREStudentNoNOTIN(SELECTStudentNoFROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')ANDExamDate=(SELECTMAX(ExamDate)FROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='JavaLogic')))演示案例6：使用NOTIN子查询ANDGradeId=(SELECTGradeIdFROMSubjectWHERESubjectName='JavaLogic')限定“JavaLogic”课程所在学期第一步：查询参加“JavaLogic”课程最近一次考试的学生名单第二步：在IN关键字之前增加否定词NOT第三步：限定“JavaLogic”课程所在学期指导——使用IN关键字的子查询2-1•训练要点：–使用子查询返回多条记录•需求说明：–查询S1学期开设的课程讲解需求说明指导——使用IN关键字的子查询2-2•实现思路：–查询获得年级名称是S1的所有课程的课程编号–根据课程编号查询课程表得到课程名称SELECTSubjectNameFROMSubjectWHEREGradeIdIN(SELECTGradeIdFROMGradeWHEREGradeName='S1')完成时间：20分钟练习—使用NOTIN关键字的子查询•需求说明：–查询未参加“SQLBase”课程最近一次考试的在读学生名单•提示：–获得SQLBase课程的课程编号–根据课程编号查询得到SQLBase课程最近一次的考试日期–根据课程编号和最近一次的考试日期查询出学生名单–通过NOTIN关键字查出没有参加最近一次考试的在读学生名单–限定SQLBase课程所在学期完成时间：20分钟SELECTStudentNameFROMStudentWHEREStudentNoNOTIN(SELECTStudentNoFROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName='SQLBase')ANDExamDate=(SELECTMA