第6-2章 嵌套查询

第六章
数据查询
Copyright by spring
6.1 基本查询
6.2 嵌套查询
本 章 内 容
6.3 连接查询
2
数据查询
第 2讲 嵌套查询
Copyright by spring
基本子查询语句 子查询类型 记录操作语句中的子查询 子查询规则
子 查 询
学习要点
基本子查询语句
SQL Server允许多层嵌套查询。嵌套查询一般的查询方法是由里


Cno= „0001‟，则取此学生的Sname和Sno送入结果关系
SELECT S_no，S_name 子 FROM Information 查 WHERE EXISTS 询 （SELECT * FROM Score 类 WHERE S_no=Information.S_no AND C_no=„0001'） 型 思考：用IN子查询如何实现？
比较运算符
【例6-28】
查询选修了课程名为“数据库原理与应用”的 学生学号和姓名
SELECT S_no，S_name FROM Information WHERE S_no IN
此子查询返回多个学生编号(s_no)
子 查 询 类 型
（SELECT S_no FROM Score WHERE C_no = （SELECT C_no FROM Course WHERE C_name='数据库原理与应用'）） 此子查询返回一个课程编号(C_no)
返回结果
EXITS子查询
返回例4-35
记录操作语句中的子查询
抽取数据到另一个表中 INSERT语句中的子查询 UPDATE语句中的子查询 删除语句中的子查询 unclear
子 查 询
子查询
抽取数据到另一个表中
记 录 操 作 中 的 子 查 询
【例6-33】由Infromation建立临时表Temp， 并查询Temp中所有记录
如果确定内层查询返回的是单值时，可以用 〉、 〈、 =、 〉=、〈=、!=或〈〉等比较运算符
• • 单值情况下使用比较运算符 多值情况下使用IN或NOT IN谓词
子 查 询 类 型
示例
【例6-27】 【例6-28】
子查询类型
【例6-27】
查询与“刘晶晶”在同一个班学习的学生 分析：
子 查 询 类 型
由EXISTS引出的子查询，其目标列表达式通常都用* ， 因为带EXISTS的子查询只返回真值或假值，给出列名 无实际意义
子查询类型
【例6-30】
查询所有选修了“0001”课程的学生S_no（学号）和S_name（姓 名） 思路分析：

本查询涉及information和Score关系 在Information中依次取每个元组的Sno值，用此值去检查Score关系 若Score中存在这样的元组，其Sno值等于此information.Sno值，并且其
该语句不需要先建立表，会自动生成一个新表 SELECT * INTO Temp FROM Information
返回结果
记录操作语句中的子查询
返回
INSERT语句中的子查询
记 录 操 作 中 的 子 查 询
基本语句格式
INSERT
INTO 〈表名〉 [（〈属性列1〉 [，〈属性列2〉...]] 子查询
FROM Information WHERE S_birth>ALL(……) AND S_class <> '信息021'
？
子 查 询 类 型
ORDER BY S_birth DESC
聚 合 函 数 改 写
总结：
SELECT S_birth FROM Information WHERE S_class='信息021'
子 查 询 类 型
返回结果
关系代数的除法运算基本上是靠两个not exists来实现的。
EXITS子查询
返回例4-34
【例6-32】
查询至少选修了学生“20021003003”选修的全 部课程的学生的S_no（学号）和S_name（姓名）
思路转换
• 不存在这样的课程y，学生“20021003003”选修了y，而 学生x没有选
比较运算符
使用ANY或ALL的子查询
使用ANY或ALL谓词时则必须同时使用比较运算符
表6-1 带有ANY和ALL谓词的相关连词
连词 〉 ANY 〈 ANY 含义 大于子查询结果中的某个值 （大于最小） 小于子查询结果中的某个值
〉= ANY
大于等于子查询结果中的某个值
小于等于子查询结果中的某个值 等于子查询结果中的某个值 不等于子查询结果中的某个值 大于子查询结果中的所有值 小于子查询结果中的所有值 大于等于子查询结果中的所有值 小于等于子查询结果中的所有值 等于子查询结果中的所有值（通常没有实际意义） 不等于子查询结果中的任何一个值
查找所有在‘信息021’班学习的学生
SELECT S_no， S_name， S_class FROM Information WHERE S_class='信息021'
In or not in
改进后的【例6-25】
step1 SELECT S_class FROM Information WHERE S_name='刘晶晶' step2
字段名称 可 以不同，但 字段类型必 须一致
SELECT S_no, AVG(grade)
FROM Score GROUP BY S_no SELECT * FROM avgGrade
返回结果
返回
返回
UPDATE语句中的子查询
记 录 操 作 中 的 子 查 询
子查询也可以嵌套在UPDATE语句中，用以 构造执行修改操作的条件
概念 (在以后章节中讲授)
WHERE Score.C_no=Course.C_no）
记录操作语句中的子查询
删除语句中的子查询
记 录 操 作 中 的 子 查 询
子查询同样也可以嵌套在DELETE语句中， 用以构造执行删除操作的条件
【例6-36】 删除’20021003’班的学生的“数据 库原理与应用”课程成绩记录
SELECT DISTINCT S_no
子 查 询 类 型
FROM Score ScoreX WHERE NOT EXISTS （SELECT * FROM Score ScoreY WHERE ScoreY.S_no='20021003003' AND NOT EXISTS （SELECT * FROM Score ScoreZ WHERE ScoreZ.S_no=ScoreX.S_no AND ScoreZ.C_no=ScoreY.C_no））
此子查询返回”T”or“F”
EXITS子查询
【例6-31】
查询选修了全部课程的学生学号和姓名
思路转换：
• 查询这样的学生，没有一门课程是他不选的
SELECT S_No, S_name FROM Information WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS （SELECT * FROM Score WHERE S_no=Score.S_no AND C_no=Course.C_no ））
【例6-35】将所有学生的“数据库原理与应用”成 绩置为70 UPDATE Score SET Grade=70 通常使用 UPDATE 语句一次只能操作一个表 WHERE „数据库原理与应用’= 如果有更新涉及两个表，则需使用两次update语句 （SELECT C_name
•为保证两个表能同步更新，将使用事务 （Transaction）的 FROM Course
（SELECT S_no FROM Score
WHERE C_no IN （SELECT C_no FROM Course
子 查 询 类 型
WHERE C_name='数据库原理与应用'））
返回结果如下：
思考：如何运用连接查询完成这个操作？
In or not in
使用比较运算符的子查询
指父查询与子查询之间用比较运算符进行联接
一个学生只可能在一个班级学习 ，故查询班级的结果是一 个唯一值
SELECT S_no，S_name，S_class
子 查 询 类 型
FROM Information
WHERE S_class = （SELECT S_class FROM Information WHERE S_name='刘晶晶'） 子查询返回一个班级名
向外进行处理，即每个子查询在上一级查询处理之前处理，子查询的
结果用于建立其父查询的查找条件。子查询中所存取的表可以是父查 询没有存取的表，子查询选出的记录不显示。
单值嵌套查询
多值嵌套查询
查询块
Select…… From…… Where……
子查询
Select…… From…… Where(having)
改进后的【例6-25】
子 查 询 类 型
【例6-26】
子查询类型
【例6-25】
查询与“刘晶晶”在同一个班学习的学生 Step1:
确定“刘晶晶”所在班级名
SELECT S_class
FROM Information WHERE S_name='刘晶晶'
子 查 询 类 型
Step2:
DELETE FROM Score AND S_no IN （SELECT s_no FROM information WHERE LEFT(s_no,8)=‟20021003‟） （SELETE C_name FROM Course WHERE Score.C_no=Course.C_no）