数据库实验—7

数据库实验-7

嵌套查询

在SQL中，一个select—from—where 语句作为一个查询块。将一个查询块嵌套在另一个查询块的where子句或者having语句众女的查询成为嵌套查询。

例如如下代码

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

• 其表示是下层查询块 SELECT Sno SC WHERE Cno='2’是嵌套在上查询块 SELECT Sname FROM Student WHERE Sno in 的 WHERE条件上。上层语句为外查询，下层查询为内层查询。

SQL 语句允许多层嵌套查询，即一个子查询中还可以嵌套在其他查询，需要特别指出的是，子查询的select语句不能使用order by，order by子句只能对最终查询结果排序。

带有IN 谓词的子查询

【例3.55】查询与“刘晨”在同一个系学习的学生。

select Sno,Sname,Sdept
from Student
where Sdept='CS';--解法一


--本题中我们还可以有第二种解法也就是前面说的自身链接

select S1.Sno,S1.Sname,S1.Sdept
from Student S1,Student S2
where S1.Sdept=S2.Sdept and S2.Sname='刘晨';--第二种解法。

这个题里面子查询的查询条件不依赖于父查询，为不相关查询。

【例3.56】 查询选修了课程名为“信息系统”的学生学号和姓名。

select Sno,Sname
from Student		-- 在Student中取出Sno 和 Sname
where Sno in
 (select Sno
	from SC  --在SC关系中找到 与找到的课程号相符的
	where Cno in
	(select Cno
	from Course
	where Cname = '信息系统' --在Course中找到信息系统的课程号
	)
	);
)

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno=SC.Sno and
      SC.Cno=Course.Cno and
      Course.Cname="信息系统"；//链接查询实现

• 本查询涉及学号、姓名、课程三个属性。所以需要Student与Course表连接（通过SC表），所以涉及三个表。
• 有些嵌套查询可以用连接运算替代，有些是不能替代的。从例3.55和例3.56可以看出查询多个关系时候，嵌套查询会让层次更清楚。但是相比于连接查询优化还不够，所以现实能用链接查询做的话尽可能用链接查询做。

例3.55和3.56中的子查询不依赖与父查询，这类子查询称为不相关子查询不相关查询是一类简单的子查询，如果子查询的查询条件依赖于父查询，这类子查询成为相关子查询，整个查询语句为 相关嵌套查询。

带有比较运算的子查询

带有比较运算符的子查询是指父查询和子查询之间用比较符进行连接，例如<=、>=等。

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

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

x是SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层嵌套查询是求一个学生所有选修课的平均成绩，至于是那个学生的平均成绩要看参数x.Sno的值，这类和父查询有关。因此这类查询为相关子查询。

这里面的执行过程可以看成从外到内的两个for循环。

带有any（some）或者all谓词的子查询

ANY 大于子查询结果中的某个值
ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
= ANY 大于等于子查询结果中的某个值
= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
= ALL 等于子查询结果中的所有值（通常没有实际意义）
!=(或<>) ANY 不等于子查询结果中的某个值
!=(或<>) ALL 不等于子查询结果中的任何一个值

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

select Sname,Sage
from Student
where Sage<any(
               select Sage
               from Student
               where Sdept='CS'
                )
and Sdept !='CS';

--//本查询也可以聚集函数来实现，首先用子查询找CS中年龄最大的那个人，然后在父查询中查询所有非CS系且年龄小于20岁的学生。SQL语句如下：

select Sname,Sage
from Student 
where Sage< (
            select Max(Sage)
            from Student
            where Sdept='CS'
            )
and Sdept<>'CS'; 

【例3.59】查询非计算机科学系统中比计算机科学所有学生年龄都小的学生姓名及其年龄。

select Sname,Sage
from Student
where Sage <ALL
          (
          select Sage
          from Student
          where Sdept='CS')
         and Sdept!='CS';
         --//本函数同样可以用聚集函数实现

select Sname,Sage
from Student 
where Sage< (
            select min(Sage)
            from Student
            where Sdept='CS'
            )
and Sdept<>'CS'; 

带有EXISTS谓词的子查询

带有exists的语句不会带有返回任何数据，只产生逻辑真值“true”“false”。

带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“ true” 或逻辑>假值“ false” 。
1、若内层查询结果 非空 ，则外层的 WHERE 子句 返回真值
2、若内层查询结果为 空 ，则外层的 WHERE 子句 返回假值
3、由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带>EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS谓词
若内层查询结果非空，则外层的 WHERE 子句返回假值
若内层查询结果为空，则外层的 WHERE 子句返回真值

【例3.60】查询所有选修了一号课程的学生姓名。

select Sname
from Student
where exists
  (
  select*
  from SC
  where Sno=Student.Sno and Cno='2');--和上文相同这里是以两个for循环的执行方式。

详细的过程请戳这里

1. 使用存在exists只返回 true和false
2. 目标行列通常使用*，因为不返回任何列值

谓词和exists的关系
1、一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
2、所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询 等价替换

【例 3.61】 查询没有选修1号课程的学生姓名。

select Sname
from Student
where not exists --和上文代码近似，只需取反就行
(select * 
 from SC
 where Sno = Student.Sno and Cno = '1');

【例 3.55】查询与“刘晨”在同一个系学习的学生。

select Sno,Sname,Sdept
from Student S1
where exists --存在和刘晨Sdept一样的学生
(select *
 from Student S2
 where S2.Sdept = S1.Sdept and
  S2.Sname = '刘晨');

量词的实现

【例 3.62】 查询选修了全部课程的学生姓名。(没有一门课程是他不选修的)

select Sname
from Student 
where not exists -- 内层false 本层true，并且内层是或的关系
(select *
 from Course
 where not exists -- 选修了就返回false 没选修返回true 
 (select *
   from SC
   where Sno = Student.Sno
   and Cno = Course.Cno
   )
);

【例 3.63】查询至少选修了学生201215122选修的全部课程的学生号码。

select distinct Sno
from SC SCX
where not exists 
    (select *
	  from SC SCY
	  where SCY.Sno = '201215122' and
	   not exists --
	   (select *
	   from SC SCZ
	   where SCZ.Sno = SCX.Sno and
	   SCZ.Cno = SCY.Cno)
	 );