快速对Oracle数据库的了解---3

高级关联查询

    子查询:一个查询语句所需要的数据，来自于另外一个查询语句的结果。
             先执行的查询语句就是子查询
    父查询(主查询)：被子查询嵌入的查询语句就是主查询。
               子查询返回的数据的特点：
                (1)返回一行一列
                (2)返回多行一列
                (3)返回多行多列
 1.子查询在where子句中(续)
           当子查询的语句中可以使用主查询的数据时，可以使用exists。
           exists 表示存在，后面的子查询如果能查出至少一条数据，会返回true，否则返回false。
         
    需求:查询有员工的部门信息。
       select * from dept d where 
       exists 
       (select * from emp e where d.deptno=e.deptno)
     
2子查询在having子句中
           需求:查询职位人数大于'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 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,t.* from
                (select  * from emp  order by sal desc) t )  
            where rn between 13 and 15;
 

decode函数：

      用法：          列名        列值      赋值           列值        赋值             否则其他列值为default
        decode(colName, search1,  result1 ,  [search2  ,  result2],  ......   ，[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

 排序函数：

      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排序