五.Oracle中的子查询

不到万不得已不要用子查询!!!(子查询的效率低于连接查询)当然表连接查询都可以用子查询替换，但反过来说却不一定(子查询是万能的)
   
子查询:
   单行单列子查询:
       放在条件后的子查询(条件子查询~~容易记忆) 并且条件 = 、!= 、>、>=、<、<= 返回的是单行单列  也叫单行单列子查询
       例:select * from emp where sal=(select sal from emp where ename='SCOTT')
       
       放在列上的子查询(列子查询~~容易记忆,返回的只能是单行单列,否则会报错)
       例:select e.ename,e.deptno,(select dname from dept d where d.deptno=e.deptno)from emp e;(效果很低,不建议用,但做分页必须要用这种)
          select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;(这种比上面的子查询效率高,尽量用这种不要用上面那种)
        
   多行单列子查询:any in...关键字
       例:select * from emp where sal=any(select sal from emp where ename='SCOTT' or ename='SMITH')

   多行多列子查询(表子查询~~容易记忆):
       放在from和where之间 用于主查询的数据源 虚表
          //注意主表要查询的列(例:empno)必须要在虚表里有
       例:select empno from(select empno,sal from emp where sal>2000)

总结:
单行单列子查询：
    单行比较操作符：= 、!= 、>、>=、<、<=
多行单列子查询：
    多行比较操作符：IN、NOT  IN、ANY、ALL
    IN：检查指定值是否包含在一个值列表中。
    ANY：用指定值与一个列表中的任意一个值进行比较。
    ALL：用指定值与一个列表中的所有值进行比较。
    ANY和ALL必须组合=、!=、>、>=、<、<=来使用。
    =ANY：与IN操作符相同
    >ANY：比列表中最小值更大
    <ANY：比列表中最大值更小
    >ALL：比列表中最大值还要大
    <ALL：比列表中最小值还要小
多列子查询：
    可以把子查询返回的结果当作一张新表的数据来使用。


1.查询出月薪高于公司平均月薪的员工姓名、职位、月薪
 select * from emp where sal>(select avg(sal)from emp)
2.查询出每个部门月薪最低的员工信息
 第一种:select dname,(select ename from emp where sal=(select min(sal)from emp where deptno=d.deptno)and deptno=d.deptno)from dept d;
 第二种:select * from emp e where exists (select deptno,min(sal)from emp group by deptno having e.deptno=deptno and e.sal=min(sal))
 
3.查询出每个部门的编号、名称、员工数量、平均月薪
 第一种:select deptno,dname,(select count(*) from emp where deptno=d.deptno),(select avg(sal)from emp where deptno=d.deptno)from dept d;
 第二种:select d.deptno,d.dname,count(e.rowid),avg(e.sal) from emp e inner join dept d on e.deptno=d.deptno group by d.deptno,d.name

4.查询出每个月薪级别中最高月薪的员工信息
 select grade,(select max(sal)from emp where sal between s.losal and s.hisal)from salgrade s
 
集合操作符:合并多个查询结果
    UNION ALL：将多个查询结果合并到一个结果中，有重复行
        例:{1,2}{2,3}合并={1,2,2,3}
    UNION：将多个查询结果合并到一个结果中，没有重复行（并集）
        例:{1,2}{2,3}合并={1,2,3}
    INTERSECT：返回两个查询结果中共有的行 (交集)
        例:{1,2}{2,3}取交集={2}
    MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)
        例:{1,2}{2,3}取差集={1}  (以左边为主,减不掉的取左边剩下的)

rownum:(重点)
    使用ROWNUM可以完成对结果集中的部分行的选取
    ROWNUM不能直接用于>、>=、!=、BETWEEN.. AND条件(因为rownum是达成条件才+1)
       例:select * from where rn>1 查不出结果
       例:select * from (select e.*,rownum rn from emp e)where rn>1 对(先求出结果在判断条件)

    子查询中的ROWNUM必须要有别名，否则还是不会查出记录来
    这是因为ROWNUM不是某个表的列，如果不起别名的话,无法知道ROWNUM是子查询的列还是主查询的列
       例:select e.*,rownum rn from emp e   rownum rn 别名