数据库讲义ch07

7 子查询和子查询表达式

7.1 使用子查询

列出教师平均年龄小于50岁的单位名称

SELECT 名称 FROM 单位 WHERE 50 > ???

这里的“???”怎样给定呢？如果仅考虑“工业工程系”（代码为0509），则“???”应该是下面的查询结果：

SELECT Avg(年龄) FROM 教师 WHERE 单位代码 = '0509'

在这个例子中为了获得最后的查询结果，需要两个查询共同完成，前一个称之为主查询，后一个就是子查询。 单位 代码 名称 50 >年龄之平均值 SELECT Avg(年龄) 25 WHERE 单位代码 = '0103'FROM 教师 01 机械工程学院 0102 机械电子工程系 0103 机械设计及自动化系 0104 车辆与交通工程系 教师表 0105 精密仪器与测控工程系 0503 制造工程及自动化系 0508 设计艺术系 0509 工业工程系 ?? 年龄之平均值 SELECT Avg(年龄) 50 > 42.55555 WHERE 单位代码 = '0509'FROM 教师 教师表

从图中可以看出，主查询从表“单位”中逐行扫描数据，从单行数据中取出该单位的代码，这个代码提供给子查询以计算该单位教师的平均年龄，然后比较之以确定是否在最后的查询结果中保留该行。

SELECT 名称 FROM 单位 WHERE 50 >

(SELECT Avg(年龄) FROM 教师 WHERE 单位代码=代码)

名称 机械设计及自动化系 工业工程系 7-1

7.1.1 什么是子查询

子查询是一个 SELECT 查询，它返回单个值且嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。 SELECT 名称 FROM 单位 WHERE 50 >

(SELECT Avg(年龄)

子查询、内部查询、内部选择

主查询、外部查询、外部选择

FROM 教师 WHERE 单位代码=代码)

SQL子查询一般作为WHERE子句或HAVING了句的一部分出现。在WHERE子句中，它们用作选择出现在查询结果中的单行；在HAVING子句中，它们用作选择出现查询结果中的行组。

(SELECTALLDISTINCTFROMselect-Item,*table-specification,WHEREsearch-conditionGROUP BYgrouping-colunm,HAVINGsearch-condition)

ORDER BY子句不能用于子查询。子查询结果只是被主查询内部使用，对用户是不可见的，所以，对它们的任何排序都是没有意义的。

7.1.2 WHERE子句中的子查询

子查询在SQL语句的WHERE子句中使用最频繁。

列出微机原理及应用课程成绩中超过平均分的学生

SELECT 教与学.学号, 学生.姓名

FROM 教与学 INNER JOIN 学生 ON 教与学.学号 = 学生.学号 WHERE 教与学.课程编号='01028203' AND

7-2

教与学.成绩>(SELECT Avg(成绩)

FROM 教与学 WHERE 课程编号='01028203')

学号 姓名 04302768 芮丹平 04302809 袁国平 04303396 陈凤 04303707 张里 04300477 李超 04300522 张兆营 04301190 王璐 04301378 蔡亚梅 04301388 吴挺 04301391 洪文达 04302472 沙洋娟 04302610 侯蕾 04302633 杜鹃 04302653 张微 04302686 蔡成凯 04302696 赵小俊 04302714 董莺 04302717 张豆 7.1.3 外部参照

在子查询体内，通常有必要指定主查询当前行字段的值。外部参照是一个字段名，但该字段名并不参照出现的子查询中FROM子句指定表的任何字段，而是参照主查询FROM子句指定表的—个字段。当DBMS在子查询中检查搜索条件时，外部参照中字段值从当前主查询测试行中获得。

列出可授予多个学位的专业名称

SELECT 名称 FROM 专业 WHERE 1 <

(SELECT Count(学位代码) FROM 专业学位

WHERE 专业代码=代码)

外部参照

7-3

名称 社会工作 思想政治教育 应用物理学 材料物理 材料化学 工业工程 7.2 子查询搜索条件

子查询通常作为WHERE子句或HAVING子句搜索条件的—部分出现。除了前面介绍的简单搜索条件之外，子查询中还可以有下面的搜索条件：

? 子查询比较测试，它用以将表达式的值与子查询生成的单值进行比较，这个测试与

简单比较测试类似。 ? 子查询组成员测试，它用于检查表达式的值是否与子查询生成的—组值的之一匹

配。这种测试与简单组成员测试类似。 ? 存在性测试，它用于测试子查询是否生成查询结果的一些行。

? 限定比较测试，它用于将表达式的值与子查询生成的一组值中的每—个值进行比

较。

7.2.1 子查询比较测试

expression=<><<=>>=subquery

列出班级人数大于等于30人的班级及其所在院系名称和专业名称

SELECT 班级.班级号, 单位.名称, 专业.名称

FROM (班级 INNER JOIN 单位 ON 班级.所属学院 = 单位.代码) INNER JOIN 专业 ON 班级.所属专业 = 专业.代码

WHERE 30<=(SELECT Count(*) FROM 学生 WHERE 学生.班级=班级.班级号)

班级号 单位.名称 专业.名称 04014001 机械工程学院 工业工程 05014001 机械工程学院 工业工程 列出2005年同一专业在不同学院分别开设的专业大类名称、专业代码、专

7-4