当前位置: article > 正文

SQL-（重难点）嵌套查询_sql嵌套查询

作者：羊村懒王 | 2024-04-27 21:30:28

踩

sql嵌套查询

求至少用了供应商S1所供应的全部零件的工程号JNO

差集操作符（EXCEPT）

1 相关子查询和不相关子查询

不相关子查询：内层查询的执行与外层查询无关。
由里向外逐层处理，每一个子查询在上一级查询处理之前求解。子查询的结果用于建立其父查询的查找条件。
相关子查询：内层执行结果依赖于外层。
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，如果WHERE子句返回值为True，则取此元组放入结果表。
重复这一过程，直到外层表遍历一次为止。

2 子查询的限制

不能使用ORDER BY子句
层层嵌套方式反映了SQL语言的结构化
有些嵌套查询可以用连接运算替代（不相关子查询）

3嵌套查询的谓词

3.1 带有 IN谓词的子查询

找出没有使用天津产的零件的工程号码：


-- 7.找出没有使用天津产的零件的工程号码
-- （遍历工程，不在“该工程有供应商是天津的”工程集合中）
SELECT JNO
FROM J
WHERE JNO NOT IN
	(SELECT JNO
	FROM S,SPJ
	WHERE SPJ.SNO=S.SNO AND S.CITY='天津');

3.2 带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。

当我们确定内层循环返回的是单个值时，就可以用>、<、=>、=<、=、!=、<>等比较运算符。

例如，在学生表中，因为一个学生只可能在一个系学习，即内查询的结果是一个值，则可以用=代替IN：


SELECT Sno,Sname,Sdept
FROM SC x
WHERE Sdept =
    (SELECT Sdept
    FROM Student
    WHERE Sname='Cheese');

举个栗子：找出每个学生超过他自己选修课程平均成绩的课程号，如何查询？

思路：选出SC表中的学生各科成绩Grade，与子查询中查询到的由SC表计算产生的平均成绩AVG(Grade)比较。每个学生的各科成绩对应自己的平均成绩。


SELECT Sno,Cno
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
                FROM SC y
                WHERE y.Sno = x.Sno);

3.3 带有 ANY或ALL谓词的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的DBMS用SOME）或ALL修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符。

例如 >ANY 表示大于子查询结果中的某个值，<ALL 表示小于子查询结果中的所有值。

举个栗子1：我们查询 非CS系中比 CS系任意一个学生年龄小的学生姓名和年龄。


SELECT Sname,Sage
FROM Student
WHERE Sage < ANY (SELECT Sage
                  FROM Student
                  WHERE Sdept='CS')
AND Sdept <> 'CS'; /*这是父查询块的并列条件，不是子查询中的*/

上述查询执行时，首先处理子查询，找出CS系中所有学生的年龄组成一个集合(20,19)，然后处理父查询，找出所有不是CS系且年龄小于20或年龄小于19的学生。查询结果有两个元组，年龄值域为(18,19)。

本查询也可以用聚集函数来实现！首先用子查询找出CS系中最大年龄(20)，然后在父查询中找出所有不是CS系且年龄小于20的学生。请读者自己思考并编写语句。（提示：使用聚集函数MAX）

举个栗子2：查询 非CS系中比 CS系所有学生年龄小的学生姓名和年龄。

只需要把上述代码的ANY改为ALL即可。查询结果为一个元组，年龄值域为(18)。

3.4 带有 EXISTS谓词的子查询

只返回逻辑真True或逻辑假False

使用exists引出的子查询，其目标表达式列都使用*，因为带exists的子查询只返回真值或假值，给出列名无实际含义。

如果子查询中因为加了条件而没有结果集返回，则主语句就不执行了。


#列出选修了C001课程的学生的学号、姓名
select sno,sname from student
where exists(
select * from sc where sc.sno=student.sno and cno='C001'
);

【查询过程】：从student的第一条记录开始查询，将第一条记录代入子查询中，在sc表中匹配该学生选课记录，若匹配到则立刻返回真，父查询中输出该记录；若匹配完后结果仍为空，否则返回假，继续父查询继续代入下一条记录到子查询中查询。

求至少用了供应商S1所供应的全部零件的工程号JNO

第一种解读：假设S1供应零件Px（Px不唯一），这个工程用到的Px可以不是S1产的，但此工程必须含有全部的Px。

翻译成可以转化为代码的语句：在 S1 生产的全部零件y中，不存在有一个零件y，工程x不使用。

因为这个语义中，含有两个否定，因此需要两个 `NOT EXISTS` 来完成从自然语言到 SQL 语言的转化。


select distinct 工程号
 
from SPJ SPJZ
 
where not exists (      -- 4. 它不使用
 
select *                -- 1. 在 S1 生产的全部零件中，
 
from SPJ SPJX
 
where SNO = 'S1'
 
and not exists (        -- 2. 不存在
 
select *                -- 3. 有一个零件
 
from SPJ SPJY
 
where SPJY.零件号 = SPJX.零件号 and SPJY.工程号 = SPJZ.工程号
 
)
 
);