oracle子查询

最近在加强oracle查询，在网上看到了一个不错的视频，把学习笔记和大家分享一下

oracle 子查询的语法（即select语句的嵌套）

子查询要注意的问题：
  1.子查询语法中的小括号
  2.子查询的书写风格
  3.可以使用子查询的位置：where ,select ,having,from
  4.不可以在主查询的group by 使用
  5.from的子查询
  6.主查询和子查询可以不是同一张表
  7.一般不在子查询中使用排序，因为对主查询没有意义，但在top-N分析顺序，要排序
  8.执行顺序：先执行子查询，再执行主查询，但相关查询例外
  9.单行子查询只能使用单行子查询，多行子查询使用多行子查询(查询结果多行）
  10.子查询null问题

---------------------------------------------------------------------------------------------

(1).可以使用子查询的位置：where ,select ,having,from

1. select(在select语句后面的查询必需是单行子查询，即结果返回为1条）
      
    SELECT EMPNO,ENAME,SAL,(SELECT JOB FROM EMP where empno=7839) from emp
   
   2.having（查询平均薪水大于30号部门最高薪水的部门平均薪水
   select deptno,avg(sal)
        from emp
        group by deptno
        having avg(sal) >(select max(sal)
                  from emp
                          where deptno=30)
   
   3.from--后面是放一张表，或结果集（一条查询语句）,在from的子查询可以看成一张新的表
      select *
      from (select empno,ename,sal,sal*12 from emp)
   
   
   

   4.where

        查询工资比scott的员工

        select * from emp where sal > (select sal
                from emp
                where ename='scott')

(2).主查询和子查询可以不是同一张表,只要子查询返回的结果主查询能够使用就行

select * from emp
           where deptno = (select deptno
                           from dept
                                where dname='sales')  --也可以使用多表查询（数据库只需要请求一次，根据笛卡尔积的大小才能判断哪种方法
                                                      --比较好

(3)一般不在子查询中使用排序，因为对主查询没有意义，但在top-N分析顺序，要排序

查找员工工资高的前3名：

--rownum:oracle自动加上的伪列，要得到伪列的值，必须在select语句中显示的查询出来
--rownum只能使用<,<=
--行号永远按照默认的顺序生成，不会随着排序而变化
 select rownum,empno,ename,sal
 from(select * from emp order by sal desc)
 where rownum<=3;

(4).执行顺序：先执行子查询，再执行主查询，但相关查询例外

相关子查询：（对于外面的表有一个要求，即必须有一个别名），可以把主查询中的值作为参数传递给子查询
 例：查询员工表中薪水大于本部门的平均薪水,
 select empno,ename,sal，(select avg(sal) from emp where deptno=e.deptno) salavg
 from emp e
 where sal >(select avg(sal) from emp where deptno=e.deptno)

(5).单行子查询只能使用单行子查询，多行子查询使用多行子查询(查询结果多行）

单行操作符：=，>，>=，<,<=,<>
多行操作符：in,any,all

查询员工信息，职位为7566员工一样，sal大于7588

select * from emp
         where job =
          (select job from emp where empno=7566 ) and
          sal>(select sal from emp where empno=7588)
     

查询工资最低的员工信息
ｓｅｌｅｃｔ　* from emp
where sal = (select min(sal) from emp);

查询最低工资大于20号部门最低工资的部门号和部门的最低工资


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


多行子查询：
   select *
    from emp
    where deptno in (select * from dept where dname='sales' or dname='accounting')

还可以使用多表查询

查询工资比30号部门任意一个员工高的员工信息

select * from emp
         where sal > any (select sal from emp where deptno=30)
                  --(select min(sal) from emp where deptno=30)

查询工资比30号部门所有一个员工高的员工信息

select * from emp
         where sal > all(select sal from emp where deptno=30)
             --(select max(sal) from emp where deptno=30)




(6)子查询null问题
单行子查询null问题
   select * from emp
          where job =
               (select job
                from emp where ename='tom')


多行子查询的null值问题
查询不是老板的员工
select * from emp
      where emp not in (select mgr from emp )--如果子查询出来的结果有null
  只要集合中有null值，那么不能使用not in （可以使用in）,因为not in 相当于<>all,(in等同于any)
  <>null永远为假


正确的

select * from emp
      where emp not in (select mgr from emp where mgr is not null )



 ----------------------------华丽丽的分割线--------------------------------------------------------

分页查询
     select *　from
      (select rownum r ,e.* from  
     (select * from emp order by sal desc) e1 where rownum<=5)
      where r>1