扒扒数据库长长知识(下载资源组合看day2)之 07 (课堂练习)

习题课1
找到员工表中工资最高的前三名


select * from emp
order by sal desc
/



select rownum, empno, ename  from emp

求不排序的前3条
 1  select rownum, empno, ename  from emp
 2* where rownum <=3
SQL> /

   ROWNUM      EMPNO ENAME
---------- ---------- ----------
        1       7369 SMITH
        2       7499 ALLEN
        3       7521 WARD
        
        


 ROWNUM的知识点
 1 ROWNUM按照oracle的默认机制生成, 
 2 rownum 只能使用<= < 号,,不能使用 > >=
 
 =======================rownum的实现机制有关系
 rownum表示,返回的结果集的行号(是一个属性,固化到一行只中,不会因为你排序,而发生变化)..
  没有第一行,就没有第二行;
  没有第二行,就没有第三行
 


 
Top-N
select  rownum, empno, ename, sal
from (select empno, ename , sal 
from emp
order by sal desc) 
where rownum<=3
/


 
分页  5=<x<=8




select  rownum, empno, ename, sal
from (select empno, ename , sal 
from emp
order by sal desc) 
where rownum<=8 and rownum>=5
/

=======错误
SQL> ed
已写入 file afiedt.buf

 1  select  rownum, empno, ename, sal
 2     from (select empno, ename , sal
 3             from emp
 4             order by sal desc)
 5*  where rownum<=8 and  rownum>=5
SQL> /

未选定行


SQL>



 

select  rownum r, empno, ename, sal
from (select empno, ename , sal 
from emp
order by sal desc) 
where rownum<=8  B

========================================
m=<x<=n

select r, empno, ename, sal
from  
(
select  rownum r, empno, ename, sal
from (select empno, ename , sal 
from emp
order by sal desc
) 
where rownum<=8

)

where r>=5

        R      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        5       7698 BLAKE            2850
        6       7782 CLARK            2450
        7       7499 ALLEN            1600
        8       7844 TURNER           1500



======>分页 内层排序 外层选

=======oracle分页思想总结

（内层排序 外层选，需要三层查询） 
内：排序。
中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。
外：去掉前m条结果。



习题2: 找到员工表中薪水大于本部门平均薪水的员工


员工表   本部门平均薪水

思路1: 查员工的薪水   本部门平均薪水  
本部门关系


要去本部门的薪水,需要求部门的薪水,要对部门分组  ====>分组s


select deptno, avg(sal)
from emp
group by deptno  部门薪水表

思路2 查找员工表 和 部门薪水表 ====> 多表查询

思路3 :等值连接条件

方法1
select e.empno, e.ename, e.sal, d.avgsal
from emp e, 
(select deptno, avg(sal) avgsal
from emp
group by deptno) d
where e.deptno=d.deptno  and e.sal > d.avgsal

考察点: 分组  多表查询 等值连接条件 别名 分组函数

    EMPNO ENAME             SAL     AVGSAL
---------- ---------- ---------- ----------
     7499 ALLEN            1600 1566.66667
     7566 JONES            2975       2175
     7698 BLAKE            2850 1566.66667
     7788 SCOTT            3000       2175
     7839 KING             5000 2916.66667
     7902 FORD             3000       2175

已选择6行。

方法2   相关子查询:   主查询的参数,让子查询用...一般是通过别名技术
一般子查询: 子查询的结果,被主查询使用


select avg(sal)  from emp where deptno = 10

select empno, ename, sal, (select avg(sal)  from emp where deptno =10) avgsal
from emp e
where sal > (select avg(sal)  from emp where deptno = 10) 
求本部门的平均薪水, ====>


===========================
select empno, ename, sal, (select avg(sal)  from emp where deptno = e.deptno) avgsal
from emp e
where sal > (select avg(sal)  from emp where deptno = e.deptno) 
求本部门的平均薪水, ====>

select avg(sal)  from emp where deptno = 10


    EMPNO ENAME             SAL     AVGSAL
---------- ---------- ---------- ----------
     7499 ALLEN            1600 1566.66667
     7566 JONES            2975       2175
     7698 BLAKE            2850 1566.66667
     7788 SCOTT            3000       2175
     7839 KING             5000 2916.66667
     7902 FORD             3000       2175