Oracle-关联查询、分页查询、排序函数

 关联查询，分页查询，排序函数

高级关联查询

    子查询:一个查询语句所需要的数据，来自于另外一个查询语句的结果。

             先执行的查询语句就是子查询

    父查询(主查询)：被子查询嵌入的查询语句就是主查询。

   子查询返回的数据的特点：

    (1)返回一行一列

    (2)返回多行一列

    (3)返回多行多列

1.子查询在where子句中(续)

   当子查询的语句中可以使用主查询的数据时，可以使用exists。

   exists 表示存在，后面的子查询如果能查出至少一条数据，会返回true.

          否则返回false

    需求:查询有员工的部门信息。

       select * from dept d where 

       exists 

       (select * from emp e where d.deptno=e.deptno)

     需求： 查询没有员工的部门信息

       分析:找到员工表中的部门号，

           去部门表中排除这些部门号    

        ①select * from dept d where  deptno not in

        (select distinct deptno from emp e where d.deptno=e.deptno );

       ②select * from dept d where  deptno not in

        (select distinct e.deptno from emp e join dept d on e.deptno=d.deptno);        

        ③select * from dept d where 

        not exists 

        (select 1 from emp e where e.deptno=d.deptno)  

2子查询在having子句中

       需求:查询部门平均工资大于10部门平均的部门号及其平均工资

           select deptno,avg(nvl(sal,0)) from emp 

           group by deptno having avg(nvl(sal,0))>

           (select avg(nvl(sal,0)) from emp where deptno=10);

   需求:查询职位人数大于'clerk'这个职位人数的职位，及其最高工资，工资之和。

   select count(*),max(sal),sum(sal) from emp 

   group by job having count(*)<(select count(*) from emp where job='CLERK');

3子查询在from子句中

       一个查询语句后，还想再查询出来的数据的基础上在查询。

       当子查询在form子句中，相当于一张没有名字的表。

       这样的表叫行内视图(view),也叫匿名视图。

       需求:查询部门的平均工资大于2000的部门信息和员工信息

        ①select deptno from emp group by deptno having avg(nvl(sal,0))>2000;

       ② select * from dept d join emp e on d.deptno=e.deptno and d.deptno in(select deptno from emp             group by deptno having avg(nvl(sal,0))>2000);

       ③ select e.*,d.*from emp e,dept d,

           (select deptno from emp group by deptno having avg(nvl(sal,0))>2000) f

           where e.deptno=d.deptno and e.deptno=f.deptno;

    4.子查询在select子句中：

      可以理解为外连接的另一种写法。

      需求：员工信息

     ① select * from emp join dept on emp.dept=dept.deptno;

      select e.ename,e.job,(select d.dname  from dept d where d.deptno=e.deptno) 部门名称,

       (select d.loc  from dept d where d.deptno=e.deptno) 地理位置 from  emp e

 分页查询:

  rownum:是数据库中提供的一个伪列，作用是给记录分配行号。

                  从1开始。此时不能做区间查询(如第三行到第五行)

                  只能查询前n条记录

          需求：查询员工信息，同时分配行号.

                   select rownum ,e.* from emp where rownum>3 and rownum<5.//不能做范围查询

           需求：前5条员工信息

                   select * from emp where rownum<=5;

   注意： 使用伪页做区间查询，只能先查询前n条数据后，当成行内视图，同时显示行号，另外之后再做范围查询

        需求：查询员工表中第六条到第十条数据

        1:先查询前10条记录

              select rownum rn,e.* from emp where rownum<=10

        2.将上一个查询作为子查询放入from子句中，充当行内视图，继续查询

                此时，rn是可以作为条件使用

             select * from (  select rownum rn,e.* from emp e where rownum<=10) 

             where rn between 6 and 10;

  分页查询：

      需求：第page页，每页的记录数pageSize

       第1页： 1-pageSize

       第2页：(pageSize+1)-2*pageSize

        ....    .....     .....

       第n页：(n-1)*pageSize+1-n*pageSize

    需求：每页3条记录，查询第5页的记录

             select * from ( select rownum rn,e.* from emp e  )

             where rn between 13 and 15;

    分页查询与order by

    需求：每页3条记录,按照工资降序排序，查询第5页 4

   //错误： select * from ( select rownum rn,e.* from emp e  )

     where rn between 13 and 15 order by sal desc;

    先对工资进行排序，再输出i

            select * from (  

                select rownum rn,t.* from

                (select  * from emp  order by sal desc) t )  

            where rn between 13 and 15;

  需求：查询员工信息及其部门的平均工资，按照部门号升序排序，查询第三页数据，每页5条

 select  *,avg(nvl(sal,0)) avg_sal from emp e join dept d on e.deptno=d.deptno order by deptno 

    select * from(

     select rownum rn,t.* from  (  select  e.*,f.avg_sal from emp e,

            (select avg(nvl(sal,0)) avg_sal,deptno from emp  group by deptno)  f

            where e.deptno=f.deptno order by e.deptno) t

       ) 

       where rn between 11 and 15

    decode函数：

      用法：     列名   列值    赋值     列值    赋值                      否则其他列值为default

        decode(colName,search1,result1,[search2,result2],......[ searchn,resultn],[default])

      逻辑：检索colName的值，如果是search1,那么就使用result1，

                             如果是search1,那么就使用result1，

                              .....       ......

                             否则default

      没有default时，返回的是null.

    需求：年终奖发奖金，职位'MANAGER' 发放月薪的1.2倍，

                          'SALESMAN'  1.1倍

                          'CLERK'     1.05倍 返回年终奖

    练习：查询每个人的年终奖,以及姓名，职位。

      select ename,job,sal,decode(job,'MANAGER',sal*1.2,'SALESMAN',sal*05,'CLERK',sal*1.05,sal*1) 年终奖 from emp  

        与decode函数功能一样的：

      列名        列值          结果

       case colName when serach1 then result1

                    when serach2 then result2

                    ...   ...     ...

                    [else result0] end

         select ename,job,sal,case job when 'MANAGER' then sal*1.2

                                       when  'SALESMAN' then sal*05  

                                       when 'CLERK' then sal*1.05

                                       else sal end 年终奖

                                       from emp

          decode与group by连用

          需求：职位：'MANAGER' 和'SALESMAN'输入vip

                    'CLERK'   属于svip   

                    其他职位 属于ip

              查询三种级别的人数

              1)查看员工的姓名，职位，级别

                   select  ename,job,decode(job,'MANAGER','VIP','SALESMAN','VIP','CLERK','SVIP','IP') s  from emp  

              2)查看每个级别的人数

                 select s,count(*)from ( select decode(job,'MANAGER','VIP','SALESMAN','VIP','CLERK','SVIP','IP') s  from emp  )    

                 group by s  //对级别分组，再count

                或者：

                select decode(job,'MANAGER','VIP','SALESMAN','VIP','CLERK','SVIP','IP') le,count(*)

                from emp group by decode(job,'MANAGER','VIP','SALESMAN','VIP','CLERK','SVIP','IP') ;

          decode与order by 连用

              需求：部门中有四个部门：'ACCOUNTING' 'RESEARCH' SALES OPERATIONS

              1.按照部门名称排序，显示部门信息

              select * from dept order by dname desc;

              2.排序规则：acounting 排第一位，SALES排第二位

                          research第三 operations 第四

   select * from dept order by decode(dname,'ACCOUNTING',1,'SALES',2,'RESEARCH',3,'OPERATIONS',4) 

    排序函数：

      1：row_number() over(partition by colName1 order by colName2); 

          排序特点：连续不重复 分组               排序

      2: rank() over (partition by colName1 order by colName2);

             重复不连续

      3:rank_dense() over (partition by colName1 order by colName2);

      排序函数可以直接区间查询。

      partition by colName:表示按照colName1分组

      order by colName:表示按照colName排序

      需求：

        1.查询所有员工信息，按照月薪排序

          select e.*, row_number() over(order by sal desc) rn from emp e

          select e.*, rank() over(order by sal desc) rn from emp e

          select e.*, dense_rank() over(order by sal desc) rn from emp e

        2.查看每个部门的员工信息，按照月薪排序

        select e.*, rank() over(partition by deptno order by sal desc) rn from emp e

        3. 使用排序函数进行分页查询    

         select * from(

         select e.*,row_number() over(order by sal desc) rn

         from emp e) where  rn between 3 and 6;

              100   100     90

    连续不重复  第一名  第二名  第三名

    不连续重复  第一名 第一名 第三名

    连续重复   第一名 第一名  第二名