Oracle-第7章 子查询

子查询知识点

7.1 单行子查询
7.2 in,any,all多行子查询
7.3 in多列子查询
7.4 rownum伪行号
7.5 分页查询
7.6 练习
   括号内的查询叫做子查询，也叫内部查询，先于主查询执行。从结构上看子查询是嵌套在其他查询语句中的。子串可以嵌套在：from ,where,having子句中。
   子查询的结果被主查询（外部查询）使用在这里插入代码片
  • expr operator包括比较运算符。

  单行运算符：>、=、>=、<、<>、<=
  多行运算符： in、any、all
  其中，根据子查询返回的行和列数量可以分为三类：
  单行子查询：返回一行一列数据
  多行子查询：返回多行一列数据
  多列子查询：返回一行或多行且多列数据
   为子查询的在使用过程中要注意：–子查询要用括号括起来，–将子查询放在比较运算符的右边，–对于单行子查询要使用 单行运算符，–对于多行子查询要使用多行运算符。

7.1 单行子查询

   单行子查询只返回一行一列，使用单行运算符。并且，在单个子句中，可以编写多个子查询。

   例1：显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作,雇员编号。

select ename, job,empno
  from emp
 where job = (select job from emp where empno = 7369)
   and sal > (select sal from emp where empno = 7876);

   例2：查询工资最低的员工姓名，岗位及工资

M1：select ename,job,sal from emp where sal=(select min(sal) from emp);
M2：select ename,job,sal from emp where sal in (select min(sal) from emp);
M3：select ename,job,sal from emp where sal=any(select min(sal) from emp);
           子查询子句不仅可以嵌套在where中，也可以嵌套在having子句中。检查条件中是否有：最大值，最小值，平均值，数量，和值；考虑子查询编写到having字句中。

   例：
select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);

7.2 in,any,all多行子查询

   子查询返回记录的条数 可以是一条或多条。和多行子查询进行比较时，需要使用多行操作符，多行操作符包括：in，any，all。
a)、in操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

   例1：查询是经理的员工姓名，工资

select ename,sal from emp where empno in(select mgr from emp);

   释：mgr是经理的编号。
   b)、any表示和子查询的任意一行结果进行比较，有一个满足条件即可。
   any一共有3中运算：>any满足最小值,<any满足最大值,=any满足其中之一，与in相同。

   例1：查询是经理的员工姓名，工资

select ename,sal from emp where empno=any(select mgr from emp);

   例2：查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。
   M1：

select deptno, ename, job, sal
  from emp
 where deptno <> 10
   and sal > any (select sal from emp where deptno = 10);

   M2：

select deptno, ename, job, sal
  from emp
 where deptno <> 10
   and sal > (select min(sal) from emp where deptno = 10);

   c)、all表示和子查询的所有行结果进行比较，每一行必须都满足条件。
   all一共有3中运算：>all满足最大值，<all满足最小值，=all等于所有值，通常无意义。

   例3：查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资。
   M1：

select deptno, ename, job, sal
  from emp
 where deptno <> 10
   and sal > all (select sal from emp where deptno = 10);

   M2：

select deptno, ename, job, sal
  from emp
 where deptno <> 10
   and sal > (select max(sal) from emp where deptno = 10);

7.3 in多列子查询

   多列子查询可以在一个条件表达式内同时和子查询的多个列进行比较。多列子查询通常用IN操作符完成。

   例1:查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位、入职日期,不包括1981年入职员工.

select ename,deptno,job,hiredate 
  from emp
 where (deptno,job) in
 (select deptno,job from emp where to_char(hiredate,'yyyy')='1981')
 and to_char(hiredate,'yyyy')<>'1981';

   例2：查询出和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981年入职员工。

select ename,deptno,job,hiredate
  from emp
 where (deptno in(select deptno from emp where to_char(hiredate,'yyyy')='1981')
 or job in(select deptno job from emp where to_char(hiredate,'yyyy')='1981'))
 and to_char(hiredate,'yyyy')<>'1981';

7.4 rownum伪行号

   rownum是oracle数据库具有的一个特性，它针对每一个查询（包括子查询）都会生成一个rownum用于对该次查询的数据进行编号。每个rownum只针对当前select查询有效，可以使用别名进行显示。rownum在执行完from子句后就存在，可以在其他子句中使用。
   对于rownum只能执行<、<=运算，不能执行>、>=或一个区间运算Between…And等，但rownum的别名支持。
   rownum常用于分页查询。
   rownum和order by一起使用时，因为rownum在记录输出时生成，而order by子句在最后执行，所以当两者一起使用时，需要注意rownum实际是已经被排了序的rownum。

   例1：

select rownum,e.* from emp e;--如果使用*查询时还查询别的列，需要用别名进行指定。
select rownum,emp.* from emp;--使用表名指定列。

   例2：查询出工资收入最高的前5名员工信息。–先排序后筛选

select * from (select * from emp order by sal desc) where rownum<=5;

   释：从sql执行顺序：fromwheregroup byhavingselectorder by中可知道，要通过子查询先对sal进行排序，然后再通过rownum进行筛选。
   例3：查询出工资收入最高的前5名员工信息。

select * from(select * from emp order by sal) where rownum<=5;

   注意：查询排行榜时可能遇到边界问题，即可能存在多个相同值。
   例4：查询工资水平在前5名的员工姓名和工资
   本例包含3个查询，共生成3个rownum。

select ename, sal
  from emp
 where sal in (select *
                 from (select sal from emp order by sal desc)
                where rownum <= 5);

释：注意边界问题，工资水平前5个和工资排名前5个是不同意义的值，工资水平前5个可能存在多个相同的值。

7.5 分页查询

   分页计算参数:第几页，分页大小，计算分页。页方法：rownum和rownum别名
rownum不支持>和>=判断，但rownum的别名支持。

   例1：分页查询员工姓名，工资，入职日期，每页显示5条，分别查询第1,2,3,页数据，
用两种方法实现
   方法一：
   先在子查询中查询前N页，在二次查询第N页；
   需要在子查询中查询rownum的别名，通过别名进行分页。优先使用这种方法，效率更高。

select *
  from (select rownum rn, emp.* from emp where rownum <= 1 * 5)
 where rn > (1 - 1) * 5;第1页
select *
  from (select rownum rn, emp.* from emp where rownum <= 2 * 5)
 where rn > (2 - 1) * 5;第2页
select *
  from (select rownum rn, emp.* from emp where rownum <= 3 * 5)
 where rn > (3 - 1) * 5;第3页

   方法二：
   现在子查询中查询出所有数据和所有数据的rownum，并使用别名表示。在二次子查询中通过别名进行分页控制。

select * from (select rownum rn,emp.* from emp)
 where rn<=3*5 and rn>(3-1)*5;
select * from (select rownum rn,emp.* from emp)
 where rn<=2*5 and rn>(2-1)*5;
select * from (select rownum rn,emp.* from emp)
 where rn<=1*5 and rn>(1-1)*5;

   例2：按照入职日期早晚进行排序，并分页查询员工姓名，工资，入职日期，每页显示5条，分别查询第1,2,3,页数据。

select *
  from (select rownum rn, e.*
          from (select * from emp order by hiredate) e
         where rownum <= 1 * 5)
 where rn > (1 - 1) * 5;
select *
  from (select rownum rn, e.*
          from (select * from emp order by hiredate) e
         where rownum <= 2 * 5)
 where rn > (2 - 1) * 5;
select *
  from (select rownum rn, e.*
          from (select * from emp order by hiredate) e
         where rownum <= 3 * 5)
 where rn > (3 - 1) * 5;

7.6 练习

1.查询入职日期最早的员工姓名，入职日期

select ename,hiredate from emp where hiredate=(select min(hiredate) from emp);

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

select e.ename, e.sal, d.dname
  from emp e, dept d
 where e.deptno = d.deptno
   and e.sal > (select sal from emp where ename = 'SMITH')
   and d.loc = 'CHICAGO';

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

select ename,hiredate from emp
  where hiredate>(select min(hiredate) from emp where deptno=20);

4.查询部门人数小于所有部门平均人数的的部门编号，部门名称，部门人数

select deptno,dname,count(*) from dept
  group by deptno,dname
  having count(*)<(select avg(count(*)) from emp group by deptno);

5.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

M1：select ename, hiredate
  from emp
 where hiredate < any (select hiredate from emp)
   and deptno <> 10;
   
M2：select ename,hiredate
  from emp
 where hiredate<(select max(hiredate) from emp)
 and deptno<>10;

6.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

M1：select ename,hiredate
  from emp
 where hiredate<all(select hiredate from emp)
 and deptno<>10;
 M2：select ename,hiredate
  from emp
 where hiredate<(select min(hiredate) from emp)
 and deptno<>10;

7.查询职位和10部门任意一个员工职位相同的员工姓名，职位,不包括10部门员工

select ename, job
  from emp
 where job = any (select job from emp)
   and deptno <> 10;

8.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工。

select ename,job,mgr from emp
  where (job,mgr) in 
  (select job,mgr from emp where deptno=10)
  and deptno<>10;

9.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工。

select ename,job,mgr from emp
  where (job in (select job from emp where deptno=10)
  or mgr in (select mgr from emp where deptno=10)) 
  and deptno<>10;