Oracle复习

--复习
--1.查询与指定员工名称为SCOTT在同一个部门的员工信息
select * from emp e where e.empno in (
       select a.empno from emp a where deptno=(select deptno from emp where ename='SCOTT')
)
--老师的写法
select * from emp where deptno in(select deptno from emp where ename='SCOTT')
--2.找出每个员工的工资超过该部门平均工资的员工信息
select * from emp where sal ? (select avg(sal) from emp group by deptno)--忘记了返回多个时怎么写
--老师写法
select e1.* from emp e1 where e1.sal > 
(select avg(e2.sal) from emp e2 where e1.deptno=e2.deptno)
--用等值连接
select e.* from emp e,(select deptno,avg(sal) avg1 from emp group by deptno) tmp 
where e.deptno=tmp.deptno and e.sal>avg1;
--3.查询其他部门中比30号部门某一员工工资少的员工信息
select distinct e1.* from emp e1 ,emp e2 
where e1.sal<(select min(e2.sal) from emp e2 
where deptno=30)and e1.deptno<>30;
--老师写法
select * from emp e1 where 
e1.sal<any(select e2.sal from emp e2 where deptno=30)
and e1.deptno<>30--<max,其中some和any是一样的
select * from emp e1 where 
e1.sal<all(select e2.sal from emp e2 where deptno=30)
and e1.deptno<>30--<min
select * from emp e1 where e1.sal<(select min(sal) from emp e2 
where e2.deptno=30)and e1.deptno<>30
--4.查询部门编号为20且工资等于3000的员工信息
select * from emp e1 where e1.deptno=20 and e1.sal=3000

--分析函数(oracle)
select empno,ename,sal,dense_rank() over (order by sal desc) rank1 from emp
--查询员工工资无论工资是否相同，取最高的5位(不管工资相同情况）
select * from (
    select empno,ename,sal,row_number()over (order by sal desc) rank1
        from emp 
)tmp 
where tmp.rank1<=5
--获得各个部门工资的前3名(不管多少人)
select * from (
    select empno,ename,deptno,sal,rank() over (partition by deptno order by sal desc) rank1
    from emp
)where rank1<=3
--查询员工工资大于3000的部门名称
select d.dname from dept d where exists(
    select e.* from emp e where d.deptno=e.deptno and sal>3000)
--内链接：等值连接
--查询员工名称和员工所在的部门名称
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,dname from emp inner join dept on emp.deptno=dept.deptno;
--内链接：不等值连接（条件不相等判断）
select empno,ename,dname,emp.deptno emp_d1,dept.deptno dept_d2
from emp,dept where emp.deptno<>dept.deptno order by empno;
--外连接:左外连接
select dept.deptno,dname,empno,ename from dept
left join emp on dept.deptno=emp.deptno;
--右外连接:
select dept.deptno,dname,empno,ename from dept
right join emp on dept.deptno=emp.deptno
--自连接:
--查询显示员工姓名和上级领导的员工名称
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2
where e1.mgr=e2.empno
--联合查询：2个查询结果的合并（过滤掉重复数据）
-- union all不会过滤掉重复数据
select * from emp where sal>1500 
union 
select * from emp where sal>=1000 and sal<=2000;
--intersect：2个查询结果的交集（不包括重复行，返回查询结果中相同的部分）
select deptno from dept
intersect
select distinct deptno from emp;
--minus：2个查询结果的差操作（不包括重复行，返回第1个查询结果在第2个查询结果中不相同的部分）

select deptno from dept
minus
select distinct deptno from emp;
-------------------------------------------------------------------------------------------------------------------------------------------

还没复习

--4、oracle分页
--rownum：伪列

--查询员工工资最高的前5人
select emp.*,rownum,rowid from emp;--这行查询结果和下面一行效果是一样的，默认就是按照rowid排序的
select emp.*,rownum,rowid from emp order by rowid;
select emp.*,rownum from emp order by sal desc;
select emp.*,rownum from emp where rownum<6 order by sal desc;--错:rownum是排序前的行号
select * from (select * from emp order by sal desc) where rownum<6;--对:rownum是排序后的行号
select * from (select emp.*,rownum rn from emp order by sal desc) where rownum<6;--对:rownum不是rn的值，rownum是排序后的行号
select * from (select emp.*,rownum rn from emp order by sal desc) where rn<6;--错:rn是排序前的行号

--获取员工表中工资排名第3的员工信息(排名可以相同)
select * from emp order by sal desc;

select * from (select * from emp order by sal desc) where rownum=3;--错
select * from (
select empno,ename,deptno,sal
      ,dense_RANK() OVER (order by sal desc) rank1
from emp
)
where rank1=3; --正确的

--查询员工表中工资从高到底排序的第5-9条记录
select * from emp where rownum>=5 and rownum<=9 order by sal desc;--错
select * from (
 select tmp.*,rownum rn from (
     select * from emp order by sal desc
 ) tmp
) t
where 1=1
    and t.rn>=5 and t.rn<=9; --rn是固定值，已经确定下来不会变更的，所以可以直接使用rn=5就可以获得到值
    --and rownum>=5 --加上这个条件拿不到数据