嵌套查询

嵌套查询

带有IN谓词的嵌套查询

查询与刘晨同在一个系学习的学生

可以分成两步进行查询，首先确定刘晨所在的系名a，再在Student表中筛选出系名为a的数据，显示出来

select Sno, Sname, Sdept
from Student
where Sdept in (
        select Sdept
        from Student
        where Sname = '刘晨'
    );

注意不能使用Sdept = 应为刘晨不是主键，查询出来的结果是一个集合不是一个元素，如果有重名的刘晨就惨了。
使用内连接完成：

select s1.Sno, s1.Sname, s1.Sdept
from Student s1, Student s2
where s1.Sdept = s2.Sdept and s2.Sname = '刘晨';

查询选择了课程2的学生信息

• 通过连接操作查询

select Student.*
from Student, SC
where Student.Sno = SC.Sno and Cno = 2;

• 通过子查询进行

select Student.*
from Student
where Sno in (
        select Sno
        from SC
        where Cno = 2 
    );

注意：
子查询的select语句中不允许使用order by语句，order by语句只能用于对最终查询结果进行排序。
有些嵌套查询可以用连接查询进行替代，但是有些是无法进行替代的。
查询时涉及到多个查询的时候，使用多个查询层次分明。但是连接操作效率更高，因此能够使用连接操作完成的话，尽量使用连接。
根据子查询是否依赖于父查询可以分为相关子查询和不相关子查询，前面的例子都是不相关的子查询

带有比较运算符的子查询

确切的知道内层查询返回的单个元素不是元素集合的时候，可以使用比较运算符

找出每个学生超过他自己选修平均成绩的课程号

select Sno, Cno
from SC x
where x.Grade > (
        select avg(Grade)
        from SC y
        where y.Sno = x.Sno
    );

注意这是一个相关查询,相关查询通过 传入内查询的x.Sno隐含了对内部查询的分组，因此使用avg得出的是该学生的一个平均成绩
如果要显示学生姓名等信息，可以加上一个连接查询学生信息

select Student.*, Cno
from SC x, Student
where x.Grade > (
        select avg(Grade)
        from SC y
        where y.Sno = x.Sno
    ) and
    Student.Sno = x.Sno;

带有ANY(SOME)或ALL谓词的子查询

子查询返回的是一个元素集合的话，这是由不能使用比较运算符，使用ANY SOME ALL 谓词

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select s1.Sname, s1.Sage
from Student s1
where s1.Sdept <> 'CS' and s1.Sage < any (
        select Sage
        from Student  s2
        where s2.Sdept = 'CS'
    );

使用聚集函数说明
select Sname, Sage
from Student
where Sage < (
        select max(Sage)
        from Student
        where Sdept = 'CS'
    ) and
    Sdept != 'CS';

带有exists 谓词的子查询

exists代表存在量词

查询所有选修了课程2的学生姓名

使用exists谓词的子查询实现，用来表述是否在某个属性的集合

select Sname
from Student
where exists (
        select * 
        from SC
        where
         Sno = Student.Sno and Cno = 2
    );

注意：
使用exists关键字更加的容易被理解，更接近于自然语义
有exists引出的子查询，其目标表达式通常使用*, 因为exists的子查询只返回真或者假，给出列名是没有任何意义的。
使用连接查询实现

select Sname
from Student, SC
where Student.Sno = SC.Sno and Cno = 2;

使用in子查询实现

select Sname
from Student x
where Sno in (
        select Sno
        from SC
        where Cno = 2
    );

查询选修了全部课程的学生

SQL中没有全程量词，因此可以将全称量词构成的命题转换为存在量词构成的命题。
实现的SQL语句可以是如下的代码：

select Sname
from Student
where not exists ( 
        select * 
        from Course    
        where not exists ( 
            select * 
            from SC 
            where Sno = Student.Sno and  
                    Cno = Course.Cno 
        )
    );

---

学习《数据库系统概论》笔记