06 ORACLE子查询与集合操作.pptVIP

L o g o 子查询和集合操作 * 课程目标 子查询 集合运算 子查询概念 子查询(SUBQUERY)是指嵌入在其他SQL语句中的一个查询。 子查询可以嵌套，嵌套子查询的求解次序是由里向外，即先处理子查询，然后将其查询的结果用于其父查询。使用嵌套子查询的方法，可以用一系列简单的查询构成复杂的查询，从而明显增强SQL语句的功能。 语法形式如下： SELECT select_list FROM table WHERE expression operator(SELECT select_list FROM table); 子查询的分类 根据子查询的结果，可以把子查询分为3种类型： 单行子查询 多行子查询 相关子查询 单行子查询 单行子查询只返回一行数据(但父查询可以返回多行)。在WHERE子句中使用单行子查询时，可以使用单行运算符。 示例1：显示工资最高的雇员信息。先利用单行子查询找出最高工资，然后再运行父查询。 多行子查询 多行子查询可以返回多行数据。在WHERE子句中使用多行子查询时，必须使用多行运算符(IN、NOT IN、EXISTS、NOT EXISTS、ALL、ANY)。如果不能确认子查询会返回多少行，则在子查询中使用多行运算符比使用单行运算符更安全、否则容易失败。 示例2：显示与部门编号为20的岗位相同的雇员信息。 示例3：显示高于部门编号为20的所有雇员的工资的雇员信息。 示例4：显示高于部门编号为20的任意雇员的工资的雇员信息。 相关子查询 相关子查询是指引用了父查询中某些表或某些列的子查询(但父查询不能引用子查询中的表或列)。 示例5：显示每个部门最高工资的雇员编号、雇员姓名、职位、部门号和工资。 示例6：显示工作在NEW YORK的雇员信息。 比较两者的结果 select * from emp where exists (select deptno from dept where dept.deptno=emp.deptno and dept.deptno=20); select * from emp where exists (select count(*) from dept where dept.deptno=emp.deptno and dept.deptno=20); 子查询使用要遵守的规则 表示运算符的operator 既可以是单行运算符（如、=、=等）,也可以是多行运算符，如IN、ANY、ALL等。 子查询必须用括号括起来，否则无法判断子查询语句的开始和结束。 子查询只能出现在WHERE子句中比较运算符的右边。 不能在子查询语句中使用ORDER BY子句。 子查询允许嵌套多层。 在子查询中可以使用单行运算符和多行运算符。 子查询的嵌套 子查询是可以嵌套使用的。 例如:查询emp表中薪水大于部门编号最小部门平均薪水的雇员信息 select deptno,ename,job,sal from emp where (sal(select avg(sal) from emp where deptno=(select min(deptno) from emp) ) ); 集合操作 在实际的查询应用中，还有一类十分有用的查询方式：集合操作。通过对两个或多个子查询的结果集进行集合运算，从而得到想要的结果集。 集合操作 UNION并集运算 将多个查询运算的结果集进行“合并” 。要合并的结果集必须保持一一对应。 示例： SELECT empno,ename,mgr FROM emp WHERE deptno = 30 UNION ALL SELECT empno,ename,mgr FROM emp WHERE job = MANAGER; 集合操作(二) INTERSECT交集运算 INTERSECT运算符用于获得两个结果集合的交集，只会显示同时存在于两个结果集合中的数据，并且会以第一列的结果进行排序。 示例： SELECT empno,ename,mgr FROM emp WHERE deptno = 30 INTERSECT SELECT empno,ename,mgr FROM emp WHERE job = MANAGER; 集合查询(三) MINUS减运算 MINUS运算符用于获得两个结果集合的差集，只会显示在第一个结果集中存在、但在第二个结果集中不存在的数据，并且会以第一列的结果进行排序。 示例： SELECT empno,ename,mgr FROM emp WHERE deptno = 30 MINUS SELECT empno,ename,mgr FROM emp WHERE