第6章 高级查询-子查询与集合操作
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Oracle数据库查询与管理
第6章：子查询与集合操作
本章内容
编写单行子查询 编写多行子查询 EXISTS运算符 使用集合操作符
本章目标
描述子查询的类型和子查询可解决的问题 编写单行、多行子查询 使用集合函数运算符将多个查询组成一个查询
编写单行子查询
知识点概述
# 1 2 3 4 5 6 7 8 9 知识点 子查询概要 使用单行操作符 在HAVING子句中使用子 查询 在FROM子句中使用子查 询 常见错误 √ √ √ √ √ √ √ √ 重点 应用 说明 介绍子查询的语法、准则及类型 学习在查询中使用单行操作符 介绍如何使用HAVING子句对多行行组进行过滤 介绍如何在FROM子句中使用子查询 介绍子查询可能会遇到的错误
内外查询中可以使用不同的表 示例：查询“IT”部门的员工信息
SELECT * FROM employees WHERE department_id = (select department_id from departments where department_name=‘IT’)
在子查询中使用组函数 示例：查询薪资等于最高（最低）工资的员工的姓名、 工种和薪资
上例试图显示没有任何下属的雇员，逻辑上没有任何问 题。 实际上，该语句并不会返回任何结果，因为子查询的结 果中有一个为null
注意：
NOT IN等同于<>ALL IN等同于=ANY
如果子查询中可能存在空值，则不要使用NOT IN，但 使用IN是可以的
在FROM子句中使用子查询
在外部查询的FROM子句中使用子查询，子查询为FROM 子句提供内联数据，也称为内联视图 示例：
Oracle中分页查询的实现
应用案例：分页查询
数据显示的页面，数据量大的时候，要限制每页显示 的数量，因此要分页显示
ORACLE中的rownum列和rowid列 分页的实现思路
确定每页显示多少条记录/数据 确定总共要显示多少条记录 计算总共多少页 根据当前页码，显示当前页的数据项
使用集合操作符
Having子句中使用子查询
示例：查询最低薪资高于部门50的最低薪资的所有部 门及其平均薪资
Oracle Server会先执行子查询 再将结果返回给主查询的HAVING子句
示例：查询平均薪资小于所有员工平均薪资的职务ID 及平均薪资
常见错误
子查询返回了多个结果
子查询没有返回任何结果
编写多行子查询
SELECT product_id,name,list_price FROM products WHERE list_price > (SELECT AVG(list_price) FROM products) ORDER BY product_id DESC;
常见错误-3
子查询中的空值，会导致整个查询没有结果 分析下例：
说明
<ANY：低于最高值 >ANY：高于最低值 =ANY：等同于IN操作符
使用ALL操作符
ALL操作符用来将一个值与一个列表中的所有值进行比 较
在ALL操作符前，必须使用一个=、<>、<、>、<=或>=操 作符
示例;
查询不是IT_PROG且薪资高于任一IT_PROG的雇员信息
>ALL：大于最高值 <ALL：小于最低值
子查询类型
单行子查询返回单个值
多行子查询返回多个值
单行子查询Hale Waihona Puke Baidu
仅返回一行 使用单行比较运算符
运算符 = > >= < <= <> 等于 大于 大于或等于 小于 小于或等于 不等于
含义
示例：显示与雇员141号职务ID相同的雇员信息
示例：查询职务与Taylor相同，但薪资高于Taylor的 雇员信息
不会删除重复行 不会对结果进行排序
使用INTERSECT操作符
INTERSECT操作符将返回两个查询的共同行 不会忽略NULL值 示例：显示符合以下条件的雇员ID和职务ID，这些职 员的当前职务和以前的职务相同，即：曾担任过别的 职务，现在又重新担任了以前的职务。
使用MINUS操作符
MINUS操作符将返回由第一个查询选定的但没有出现在 第二个查询结果集的所有不同行 示例：查询从未更换过职务的雇员的ID
EXISTS运算符
在查询中使用EXISTS运算符，结果取决于某些行是否 存在于表中。 如果EXISTS子查询中至少返回一样，则求值结果为 true 一般来说，EXISTS子查询都和主查询进行条件关联
常见错误-2
子查询不能包含ORDER BY子句
相反：任何排序都必须在外部查询中完成
示例：
外部查询最后一条ORDER BY 子句，对product_id列按 降序排序
运算符 IN ANY 等于列表中的任意一个
含义
将值与子查询返回的任意一个值进行比较。如果子查询 结果没有返回行，则结果为false。 将值与子查询返回的每个值进行比较。如果子查询结果 没有返回任何行，则结果为true
ALL
使用IN操作符
IN用来检查在一个值中是否包含指定的值
这个值列表可以是来自一个子查询返回的结果
集合运算符的准则
SELECT列表中的表达式在数量上必须匹配 第二个查询中每一列的数据类型必须与第一个查询中对 应列的数据类型相匹配 可以使用括号更改执行顺序 ORDER BY子句只能出现在语句的末尾
Oracle Server中
除非使用UNION ALL，否则会自动删除重复行 第一个查询中的列名将显示在结果中 除非使用UNION ALL，否则默认情况下输出按照第一列 升序进行排列
知识点概述
# 1 2 4
知识点 多行子查询概要 使用IN操作符 使用ANY操作符
重点
难点
应用 介绍多行子查询的操作符
说明
√ √
√ √
介绍IN操作符的作用及使用 介绍ANY操作符的作用及使用
5
6 7 8
使用ALL操作符
√
√
介绍ALL操作符的作用及使用
9
多行子查询概要
返回多个行的子查询称为多行子查询 在多行子查询中使用的是多行运算符
示例：
查询每个部门薪资最低的雇员信息
使用ANY操作符
ANY用来将一个值与一个列表中的任何值进行比较
在ANY操作符前，必须使用一个=、<>、<、>、<=或>=操 作符
示例：
查询不是IT_PROG且薪资低于任一IT_PROG的雇员信息。
上例等价于以下查询
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary<(SELECT max(salary) FROM employees WHERE job_id=‘IT_PROG’ )
集合运算中使用ORDER BY子句
ORDER BY子句只能在复合查询的末尾出现一次 ORDER BY子句仅识别第一个SELECT中的列 默认情况下，如果不使用ORDER BY，则使用第一个 SELECT中的第一列按照升序进行排序
上机任务
第7章练习 第8章练习
示例表
使用UNION操作符
UNION操作符符从两个查询中返回不包括重复的行 示例：查询所有雇员的当前职务和以前职务的信息， 每个职务仅显示一次
注意重复行的定义，是所有列均相同才视为重复
使用UNION ALL操作符
UNION ALL操作符从两个查询中返回行，包括重复行 所有规则与UNION相同，除了
问题：查询雇员中谁的薪资高于Abel的薪资？
查询Abel的薪资 salary_1 查询高于salary_1的雇员信息
基本语法（条件比较子查询）
执行：
先执行子查询，再执行主查询 主查询使用子查询的结果
可使用的地方
WHERE子句 HAVING子句 FROM 子句
基本语法（条件比较子查询）
比较条件有：
知识点概述
# 1 2
知识点 集合操作符概要 使用UNOIN ALL操作 符
重点 √
难点
应用
说明 介绍4种集合操作符及准则 学习UNOIN ALL操作符的使用
3
4 5 6
使用UNION 操作符
使用INTERSECT操作 符 使用MINUS操作符 组合使用集合操作符
√
√ √ √
学习UNION操作符的使用
单行运算符：>、=、>=、<、<=、<> 多行运算符：IN、ANY、ALL、EXISTS
示例：查询高于Abel的薪资的员工信息
子查询使用准则：
将子查询扩在括号内 将子查询放置在比较条件的右侧(只是建议） 只有在执行排序Top-N时，子查询中才需要使用ORDER BY子句 单行运算符用于单行子查询，而多行运算符用于多行子 查询
学习INTERSECT操作符的使用 学习MINUS操作符的使用 学习组合使用集合操作符
7
8 9
数学上的集合运算
并 交 差
集合操作符
集合操作符可以将两个或多个查询结果合并成一个结 果 集合操作符
操作符 UNION ALL UNION INTERSECT MINUS 说明 返回各个检索出的所有行，包括重复行 返回各个检索出的所有行，不包括重复行 返回两个查询检索出的公有行 返回第二个查询检索出的行从第一个查询检索出的行中 减去之后剩余的行
第6章：子查询与集合操作
本章内容
编写单行子查询 编写多行子查询 EXISTS运算符 使用集合操作符
本章目标
描述子查询的类型和子查询可解决的问题 编写单行、多行子查询 使用集合函数运算符将多个查询组成一个查询
编写单行子查询
知识点概述
# 1 2 3 4 5 6 7 8 9 知识点 子查询概要 使用单行操作符 在HAVING子句中使用子 查询 在FROM子句中使用子查 询 常见错误 √ √ √ √ √ √ √ √ 重点 应用 说明 介绍子查询的语法、准则及类型 学习在查询中使用单行操作符 介绍如何使用HAVING子句对多行行组进行过滤 介绍如何在FROM子句中使用子查询 介绍子查询可能会遇到的错误
内外查询中可以使用不同的表 示例：查询“IT”部门的员工信息
SELECT * FROM employees WHERE department_id = (select department_id from departments where department_name=‘IT’)
在子查询中使用组函数 示例：查询薪资等于最高（最低）工资的员工的姓名、 工种和薪资
上例试图显示没有任何下属的雇员，逻辑上没有任何问 题。 实际上，该语句并不会返回任何结果，因为子查询的结 果中有一个为null
注意：
NOT IN等同于<>ALL IN等同于=ANY
如果子查询中可能存在空值，则不要使用NOT IN，但 使用IN是可以的
在FROM子句中使用子查询
在外部查询的FROM子句中使用子查询，子查询为FROM 子句提供内联数据，也称为内联视图 示例：
Oracle中分页查询的实现
应用案例：分页查询
数据显示的页面，数据量大的时候，要限制每页显示 的数量，因此要分页显示
ORACLE中的rownum列和rowid列 分页的实现思路
确定每页显示多少条记录/数据 确定总共要显示多少条记录 计算总共多少页 根据当前页码，显示当前页的数据项
使用集合操作符
Having子句中使用子查询
示例：查询最低薪资高于部门50的最低薪资的所有部 门及其平均薪资
Oracle Server会先执行子查询 再将结果返回给主查询的HAVING子句
示例：查询平均薪资小于所有员工平均薪资的职务ID 及平均薪资
常见错误
子查询返回了多个结果
子查询没有返回任何结果
编写多行子查询
SELECT product_id,name,list_price FROM products WHERE list_price > (SELECT AVG(list_price) FROM products) ORDER BY product_id DESC;
常见错误-3
子查询中的空值，会导致整个查询没有结果 分析下例：
说明
<ANY：低于最高值 >ANY：高于最低值 =ANY：等同于IN操作符
使用ALL操作符
ALL操作符用来将一个值与一个列表中的所有值进行比 较
在ALL操作符前，必须使用一个=、<>、<、>、<=或>=操 作符
示例;
查询不是IT_PROG且薪资高于任一IT_PROG的雇员信息
>ALL：大于最高值 <ALL：小于最低值
子查询类型
单行子查询返回单个值
多行子查询返回多个值
单行子查询Hale Waihona Puke Baidu
仅返回一行 使用单行比较运算符
运算符 = > >= < <= <> 等于 大于 大于或等于 小于 小于或等于 不等于
含义
示例：显示与雇员141号职务ID相同的雇员信息
示例：查询职务与Taylor相同，但薪资高于Taylor的 雇员信息
不会删除重复行 不会对结果进行排序
使用INTERSECT操作符
INTERSECT操作符将返回两个查询的共同行 不会忽略NULL值 示例：显示符合以下条件的雇员ID和职务ID，这些职 员的当前职务和以前的职务相同，即：曾担任过别的 职务，现在又重新担任了以前的职务。
使用MINUS操作符
MINUS操作符将返回由第一个查询选定的但没有出现在 第二个查询结果集的所有不同行 示例：查询从未更换过职务的雇员的ID
EXISTS运算符
在查询中使用EXISTS运算符，结果取决于某些行是否 存在于表中。 如果EXISTS子查询中至少返回一样，则求值结果为 true 一般来说，EXISTS子查询都和主查询进行条件关联
常见错误-2
子查询不能包含ORDER BY子句
相反：任何排序都必须在外部查询中完成
示例：
外部查询最后一条ORDER BY 子句，对product_id列按 降序排序
运算符 IN ANY 等于列表中的任意一个
含义
将值与子查询返回的任意一个值进行比较。如果子查询 结果没有返回行，则结果为false。 将值与子查询返回的每个值进行比较。如果子查询结果 没有返回任何行，则结果为true
ALL
使用IN操作符
IN用来检查在一个值中是否包含指定的值
这个值列表可以是来自一个子查询返回的结果
集合运算符的准则
SELECT列表中的表达式在数量上必须匹配 第二个查询中每一列的数据类型必须与第一个查询中对 应列的数据类型相匹配 可以使用括号更改执行顺序 ORDER BY子句只能出现在语句的末尾
Oracle Server中
除非使用UNION ALL，否则会自动删除重复行 第一个查询中的列名将显示在结果中 除非使用UNION ALL，否则默认情况下输出按照第一列 升序进行排列
知识点概述
# 1 2 4
知识点 多行子查询概要 使用IN操作符 使用ANY操作符
重点
难点
应用 介绍多行子查询的操作符
说明
√ √
√ √
介绍IN操作符的作用及使用 介绍ANY操作符的作用及使用
5
6 7 8
使用ALL操作符
√
√
介绍ALL操作符的作用及使用
9
多行子查询概要
返回多个行的子查询称为多行子查询 在多行子查询中使用的是多行运算符
示例：
查询每个部门薪资最低的雇员信息
使用ANY操作符
ANY用来将一个值与一个列表中的任何值进行比较
在ANY操作符前，必须使用一个=、<>、<、>、<=或>=操 作符
示例：
查询不是IT_PROG且薪资低于任一IT_PROG的雇员信息。
上例等价于以下查询
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary<(SELECT max(salary) FROM employees WHERE job_id=‘IT_PROG’ )
集合运算中使用ORDER BY子句
ORDER BY子句只能在复合查询的末尾出现一次 ORDER BY子句仅识别第一个SELECT中的列 默认情况下，如果不使用ORDER BY，则使用第一个 SELECT中的第一列按照升序进行排序
上机任务
第7章练习 第8章练习
示例表
使用UNION操作符
UNION操作符符从两个查询中返回不包括重复的行 示例：查询所有雇员的当前职务和以前职务的信息， 每个职务仅显示一次
注意重复行的定义，是所有列均相同才视为重复
使用UNION ALL操作符
UNION ALL操作符从两个查询中返回行，包括重复行 所有规则与UNION相同，除了
问题：查询雇员中谁的薪资高于Abel的薪资？
查询Abel的薪资 salary_1 查询高于salary_1的雇员信息
基本语法（条件比较子查询）
执行：
先执行子查询，再执行主查询 主查询使用子查询的结果
可使用的地方
WHERE子句 HAVING子句 FROM 子句
基本语法（条件比较子查询）
比较条件有：
知识点概述
# 1 2
知识点 集合操作符概要 使用UNOIN ALL操作 符
重点 √
难点
应用
说明 介绍4种集合操作符及准则 学习UNOIN ALL操作符的使用
3
4 5 6
使用UNION 操作符
使用INTERSECT操作 符 使用MINUS操作符 组合使用集合操作符
√
√ √ √
学习UNION操作符的使用
单行运算符：>、=、>=、<、<=、<> 多行运算符：IN、ANY、ALL、EXISTS
示例：查询高于Abel的薪资的员工信息
子查询使用准则：
将子查询扩在括号内 将子查询放置在比较条件的右侧(只是建议） 只有在执行排序Top-N时，子查询中才需要使用ORDER BY子句 单行运算符用于单行子查询，而多行运算符用于多行子 查询
学习INTERSECT操作符的使用 学习MINUS操作符的使用 学习组合使用集合操作符
7
8 9
数学上的集合运算
并 交 差
集合操作符
集合操作符可以将两个或多个查询结果合并成一个结 果 集合操作符
操作符 UNION ALL UNION INTERSECT MINUS 说明 返回各个检索出的所有行，包括重复行 返回各个检索出的所有行，不包括重复行 返回两个查询检索出的公有行 返回第二个查询检索出的行从第一个查询检索出的行中 减去之后剩余的行