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