第6章 高级查询-子查询与集合操作.ppt

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