关系数据查询语言 全省一等奖

主讲教师：魏巍巍北京信息职业技术学院数据库技术与应用关系数据查询语言创建采集任务01子查询01目录CONTENTS子查询01可在外部查询的许多位置指定子查询：在WHERE子句的行筛选条件line_search_condition或者HAVING子句的组筛选条件group_search_condition中：使用比较运算符（注：必须是单值子查询）使用ANY、SOME或ALL修饰的比较运算符使用IN或NOTIN运算符使用EXISTS或NOTEXISTS运算符子查询在FROM子句中：必须使用表别名。在SELECT子句select_list中：代替表达式或表达式的一部分（注：必须是单值子查询）。子查询单值子查询：即子查询得到的结果为一个值！例如：查询生命学院都开设了那些课程。SELECT课程.*FROM课程WHERE学院编号

=(SELECT学院编号FROM学院WHERE学院名称

='生命学院')子查询单值子查询出现在行筛选条件中例

假设生物科学1702班的吴冰同学的姓名是唯一的，查询与吴冰同学的所有同乡（指相同省份）的姓名和专业班级。SELECT姓名,专业班级FROM学生WHERELEFT(籍贯,3)=(SELECTLEFT(籍贯,3)AS省份FROM学生WHERE姓名

='吴冰'AND

专业班级

='生物科学1702')AND

姓名

<>'吴冰';子查询单值子查询出现在组筛选条件中例

在所有的“数据库技术及应用”课堂中，查询比“数据库-交通工程1701-2”课堂的平均分还高的其它课堂的编号和平均分。子查询SELECT课堂.课堂编号,AVG(选课成绩.成绩)AS平均分FROM选课成绩

INNERJOIN

课堂

ON选课成绩.课堂编号

=课堂.课堂编号

INNERJOIN

课程

ON课堂.课程编号

=课程.课程编号WHERE课程.课程名称

='数据库技术与应用'GROUPBY课堂.课堂编号HAVINGAVG(选课成绩.成绩)>(SELECTAVG(选课成绩.成绩)AS平均分FROM选课成绩

INNERJOIN

课堂

ON选课成绩.课堂编号

=课堂.课堂编号

INNERJOIN

课程

ON课堂.课程编号

=课程.课程编号WHERE课程.课程名称

='数据库技术与应用'AND课堂.课堂名称

='数据库-交通工程1701-2');子查询单值子查询的结果作为查询的结果集中一列[例]在所有的“数据库技术及应用”课堂中，显示每一个课堂的平均分和所有课堂总平均分的比对情况，要求显示每一个课堂的编号和平均分、所有课堂的总平均分（作为比对值）。子查询第一步：先做一个生成表查询，因为后续的两步，均要用到第一步的结果。求各个课堂的平均分，并将结果送到临时表“#各课堂平均分”表中保存

SELECT课堂.课堂编号,AVG(选课成绩.成绩)AS平均分INTO#各课堂的平均分FROM选课成绩

INNERJOIN

课堂

ON选课成绩.课堂编号

=课堂.课堂编号

INNERJOIN

课程

ON课堂.课程编号

=课程.课程编号WHERE课程.课程名称

='数据库技术与应用'GROUPBY课堂.课堂编号子查询第二步：在第一步的执行结果上，求所有课堂的总平均分：

SELECTAVG(平均分)AS总平均分FROM#各课堂的平均分第三步：在第一步和第二步的执行结果上，查询每一个课堂的平均分和所有课堂的总平均分的差异比较。

SELECT*,(第二步的SELECT语句)AS总平均分FROM#各课堂的平均分子查询最后，将第一步（取消临时表）和第二步的语句带入第三步合成：SELECT*,(SELECTAVG(平均分)AS总平均分FROM(SELECT课堂.课堂编号,AVG(成绩)AS平均分FROM选课成绩INNERJOIN课堂ON选课成绩.课堂编号=课堂.课堂编号INNERJOIN课程ON课堂.课程编号=课程.课程编号WHERE课程名称=

'数据库技术与应用'GROUPBY课堂.课堂编号)AS各课堂的平均分)AS总平均分子查询FROM(SELECT课堂.课堂编号,AVG(成绩)AS平均分FROM选课成绩INNERJOIN课堂ON选课成绩.课堂编号=课堂.课堂编号INNERJOIN课程ON课堂.课程编号=课程.课程编号WHERE课程名称=

'数据库技术与应用'GROUPBY课堂.课堂编号)AS各课堂的平均分子查询多值子查询多值子查询是指结果集中有多行多列多个数据的子查询。可使用ANY、SOME、ALL修饰的比较运算符或者IN、EXISTS运算符，可出现：在WHERE子句的行筛选条件line_search_condition中HAVING子句的组筛选条件group_search_condition中直接做为外部查询的数据源出现在FROM子句中子查询子查询直接作为外部查询的数据源例

在所有选修课堂中，统计选修人数人数在18人以上的每一个课堂，要求显示每一个课堂的课堂编号、课堂名称和选修人数。子查询SELECT课堂.课堂编号,课堂名称,选课人数FROM课堂

INNERJOIN(SELECT课堂.课堂编号,COUNT(*)AS选课人数FROM课程

INNERJOIN课堂

ON课程.课程编号

=课堂.课程编号

INNERJOIN选课成绩

ON课堂.课堂编号

=选课成绩.课堂编号WHERE课程性质='选修‘

GROUPBY课堂.课堂编号

HAVINGCOUNT(*)>18)ASTON课堂.课堂编号

=Ｔ.课堂编号

;子查询ALL、ANY和SOME的用法ALL：指定比较运算要作用于集合中的每一个数据，如果所有的比较运算都满足比较关系，则比较的最终结果为真，否则，只要有一个比较运算不满足比较关系，则比较的最终结果为假。ANY和SOME：ANY和SOME的含义是一样的，均指定比较运算要作用于集合中的每一个数据，那么，只要有一个比较运算满足比较关系，则比较的最终结果为真，否则，就是所有的比较运算均不满足比较关系，则比较的最终结果为假。子查询例

在“C++程序设计基础-土木工程1704-6”课堂中找出比“数据库-工程力学1701-3”课堂所有学生的成绩还要高的那些学生的学号、姓名和成绩。SELECT学生.学号,姓名,成绩FROM课堂

INNERJOIN

选课成绩

ON课堂.课堂编号

=选课成绩.课堂编号

INNERJOIN

学生

ON选课成绩.学号

=学生.学号WHERE课堂名称

='C++程序设计基础-土木工程1704-6'AND

子查询成绩

>A