第四章 高级查询

 

 

一、        什么是子查询

 

问题：编写T-SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息 ？

 

实现方法一：采用T-SQL变量实现

declare @Birthday datetime        --定义变量，存放李斯文的出生日期

select @Birthday = BornDate from student

    where studentName = '李斯文'   --求出李斯文的出生日期

select StudentNo, StudentName,  Sex, BornDate, Address from student

    where BornDate > @Birthday    --筛选出生日期比李斯文大的学生

 

实现方法二：采用子查询实现

select StudentNo, StudentName,  Sex, BornDate, Address from Student

where BornDate >

(select BornDate from Student where StudentName='李斯文')

 

 

总结：

1、  将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

2、  子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询

3、执行小括号中的子查询，先返回子查询的结果，作为条件才开始执行外围的父查询，返回查询的最终结果

 

二、使用子查询替换表联接

 

问题：查询“Java Logic”课程至少一次考试刚好等于60分的学生

 

 

实现方法一：采用表联接

select StudentName from Student s

  inner join Result r on s.StudentNO = r.StudentNo

  inner join Subject sub on r.SubjectNo = sub.SubjectNo

where StudentResult = 60 AND SubjectName = 'Java Logic'

 

实现方法二：采用子查询

select * from Student where StudentNo=

  (select studentno from Result r where

      StudentResult=60

      and

      subjectno=

        (select SubjectNo from Subject where SubjectName='Java Logic')

  )

总结：

1、  一般来说，表联接都可以用子查询替换，但有的子查询却不能用表联接替换

2、  子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据

3、  表联接更适合于查看多表的数据

 

###############################################################################

       训练要点：

       使用子查询返回单条记录

       需求说明：

       查询参加最近一次“C# OOP”考试成绩最高分和最低分

       实现思路：

       查询获得“C# OOP”课程的课程编号

       查询获得“C# OOP”课程最近一次的考试日期

       根据课程编号查询考试成绩的最高分和最低分

 

答案：

 

###############################################################################

 

三、IN子查询

 

问题1：查询“Java Logic”课程考试成绩为60分的学生名单

 

select StudentNo, StudentName from Student where StudentNo IN

  (select StudentNo from Result where SubjectNo =

     (select SubjectNo from Subject where SubjectName='Java Logic')

   AND StudentResult=60)

 

将＝号改为IN

IN后面的子查询可以返回多条记录

常用IN替换等于（＝）的比较子查询

 

问题2：查询参加“Java Logic”课程最近一次考试的在读学生名单

第一步：获得Java Logic课程的课程编号

select SubjectNo from Subject where SubjectName='Java Logic'

第二步：根据课程编号查询得到Java Logic课程最近一次的考试日期

select max(ExamDate) from Result where SubjectNo=

  (select SubjectNo from Subject where SubjectName='Java Logic')

第三步：根据课程编号和最近一次的考试日期查询出在读学生信息

select StudentNo, StudentName from Student where StudentNo in

  (select StudentNo from Result where SubjectNo in

     (select SubjectNo from Subject where SubjectName='Java Logic')

     and ExamDate=

     (select max(ExamDate) from Result where SubjectNo=

        (select SubjectNo from Subject where SubjectName='Java Logic')

     )

   )

 

四、NOT IN子查询

 

问题：查询未参加“Java Logic”课程最近一次考试的在读学生名单

select StudentNo, StudentName from Student where StudentNo NOT IN

  (select StudentNo from Result where SubjectNo=

     (select SubjectNo from Subject where SubjectName='Java Logic')

     and ExamDate =

     (select max(ExamDate) from Result where SubjectNo=

        (select SubjectNo from Subject where SubjectName='Java Logic')

     )

  )

  and GradeId =

  (select GradeId from Subject where SubjectName = 'Java Logic')

 

###############################################################################

       训练要点：

       使用子查询返回多条记录

       需求说明：

       查询S1学期开设的课程

       实现思路：

       查询获得年级名称是S1的所有课程的课程编号

       根据课程编号查询课程表得到课程名称

答：

 

###############################################################################

###############################################################################

       需求说明：

       查询未参加“Winforms”课程最近一次考试的在读学生名单

       提示：

       获得Winforms课程的课程编号

       根据课程编号查询得到Winforms课程最近一次的考试日期

       根据课程编号和最近一次的考试日期查询出学生名单

       通过NOT IN关键字查出没有参加最近一次考试的在读学生名单

       限定Winforms课程所在学期

答：

#####################################################################

 

五、EXISTS子查询

 

问题：如何用SQL语句检测数据库MySchool是否已经创建？

if exists(select * from sysDatabases where name='MySchool')

    drop database MySchool

GO

create database MySchool

 

 

 

总结：

1、  如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false)

2、  EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换

 

问题：检查“Java Logic”课程最近一次考试。如果有 80分以上的成绩，则每人提2分；否则，每人提5分。最终的成绩不得大于100分

if exists(select * from Result where SubjectNo =

           (select SubjectNo from Subject where SubjectName='Java Logic')

           and ExamDate =

           (select max(ExamDate) from Result where SubjectNo =

             (select SubjectNo from Subject where SubjectName='Java Logic')) and StudentResult>80)

begin

    update Result set StudentResult=StudentResult+2  where SubjectNo=

      (select SubjectNo from Subject where SubjectName='Java Logic')

      and ExamDate =

      (select max(ExamDate) from Result where SubjectNo =

        (select SubjectNo from Subject WHERE SubjectName='Java Logic'))

      and StudentResult<=98                   

    print '本次Java Logic课程考试部分学生成绩高于分,每人只加分,加分后的成绩是：'

end

else

begin

    update Result set StudentResult=StudentResult+5 where SubjectNo=

      (select SubjectNo from Subject where SubjectName='Java Logic')

      and ExamDate =

      (select max(ExamDate) from Result where SubjectNo=

        (select SubjectNo from Subject where SubjectName='Java Logic'))

      and StudentResult<=95 

    print '本次JavaLogic课程考试没有学生成绩高于分,每人可以加分,加分后成绩是：'

end

select ExamDate as 考试日期, StudentNo as 学号, StudentResult as 成绩

from Result where SubjectNo =

  (select SubjectNo from Subject where SubjectName='Java Logic')

  and ExamDate =

  (select max(ExamDate) from Result where SubjectNo =

    (select SubjectNo from Subject where SubjectName='Java Logic'))

 

 

问题：检查“Java Logic”课程最近一次考试。如果全部没有通过考试（即：60分及格），则试题偏难，每人加3分，否则，每人只加1分

if not exists(select * from Result where SubjectNo=

                 (select SubjectNo from Subject where SubjectName='Java Logic')

               and ExamDate =

               (select max(ExamDate) from Result where SubjectNo =

                 (select SubjectNo from Subject where SubjectName='Java Logic')

               )

               and StudentResult>=60)

begin

    print '本次JavaLogic课程考试学生成绩都低于分，每人加分，加分后的成绩是：'

    update Result set StudentResult=StudentResult+3 where  SubjectNo =

      (select SubjectNo from Subject where SubjectName='Java Logic')

      and ExamDate =

      (select max(ExamDate) from Result where SubjectNo =

        (select SubjectNo from Subject where SubjectName='Java Logic'))

      and StudentResult<=97

end

else

begin

    print '本次JavaLogic课程考试有部分学生成绩高于分,每人加分,加分后的成绩是：'

    update Result set StudentResult=StudentResult+1 where  SubjectNo =

      (select SubjectNo from Subject where SubjectName='Java Logic')

      and ExamDate =

      (select max(ExamDate) from Result where SubjectNo =

        (select SubjectNo from Subject where SubjectName='Java Logic')) 

      and StudentResult<=99

end

 

###############################################################################

       需求说明：

       如果有S1的学生，就将他在读年级更新为S2

       提示：

       检测是否有S1的学生记录

       用UPDATE语句将学生表中S1 对应的年级编号更新为S2的年级编号

答：

###############################################################################

 

六、子查询注意事项

 

       任何允许使用表达式的地方都可以使用子查询

       嵌套在父查询SELECT语句的子查询可包括：

       SELECT子句

       FROM子句

       WHERE子句

       GROUP BY子句

       HAVING子句

       TEXT、NTEXT和IMAGE数据类型列不能出现在子查询的SELECT子句中

       只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

 

###############################################################################

       训练要点：

       任何允许使用表达式的地方都可以使用子查询

       需求说明：

       为每个学生制作在校期间每门课程的成绩单，要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩

       成绩单中包括：

       学生姓名

       课程所属的年级名称

       课程名称

       考试日期

       考试成绩

       实现思路：

       使用分组查询获得每门课程最近一次考试的日期

       使用联接查询得到每门课程最近一次考试成绩的学生姓名、课程名称、考试日期和成绩

       在父查询的SELECT子句中使用子查询获得年级名称

###############################################################################

###############################################################################

       训练要点：

       综合运用T-SQL语句实现业务处理

       需求说明：

       统计 “Java Logic”课程最近一次考试的学生合格率

       需求说明：

       统计 “Java Logic”课程最近一次考试学生应到人数、实到人数

       提取“Java Logic”课程最近一次考试的成绩信息并保存结果

       成绩信息（学生姓名、学号、成绩、是否通过）

       实现思路：

       声明并初始化变量

       使用子查询统计缺考情况

       将考试成绩保存到表TempResult中

提示：

1. 使用查询获得“Java Logic”课程的课程编号和最近一次考试日期

2. 提示: 使用子查询统计缺考情况

3. 提取学生的考试成绩并保存结果，包括学生姓名、学号、考试成绩、是否通过

 

###############################################################################

###############################################################################

       需求说明：

       根据考试平均分为低于平均分的学生加分

       显示提分后学生最终成绩和通过率

       实现思路：

       获取平均分

       判断平均分是否低于60分。如果低于60分，设置平均分为60分

       提分

       对成绩低于平均分的学生进行循环提分，每次每人提1分，提分后最高分不能超过97分

       输出提分处理后学生的最终成绩

       统计提分处理后学生的通过率情况

 

###############################################################################

 

七、总结

 

       总结我们曾学习过的查询，合并多个表中的数据的方法有三种，分别是什么？

       子查询中EXISTS子句和IN子句的作用分别是什么？

       子查询可以出现在SQL语言中哪些语句的什么位置？