表与表的关联

--两个表之间进行的连接(99语法)
select * from emp join dept on emp.deptno=dept.deptno;
select * from emp join dept on dept.deptno=emp.deptno;
等价于(92语法)
select * from emp,dept where emp.deptno=dept.deptno;

--左外连接 以右边为万能行 （确保左边的数据能在右边找到）

select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.mgr=e2.empno;

--右外连接 以左边为万能行
select * from emp;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;
等价于
select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.empno=e2.mgr;(左外连接)

--全外连接 保证左边和右边的记录都被找到
select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

92语法关于左外连接 在右边加上一个"+"
select * from emp,dept where emp.deptno=dept.deptno(+);

反过来右外连接 在左边加上一个"+"
select * from emp,dept where emp.deptno(+)=dept.deptno;

-使用伪字段:rownum，----------------------
------------------------------------------------------
--用来标识每条记录的行号，行号从1开始，每次递增1

select a.* ,rownum from emp a;
或者
select emp.* ,rownum from emp ;

--oracle下rownum只能使用 < <=， 不能使用 = > >= 等比较操作符，

--当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序
--例如，当我们要求薪水最高的前5个人时，最直接的想法可以这样写：

select sal from emp order by sal desc;

select ename ,sal from
(select ename,sal from emp order by sal desc)
where rownum<=5
//取出薪水在排名在6-10的人
select * from(
select t.*,rownum r from(
(select sal from emp order by sal desc)t
)where rownum<=10)
where r>=6

--------------------
--不准用组函数(即MAX()),求薪水的最高值(面试题)
--第一种解决办法:
--1,先把所有薪水按照倒序排列

select sal from emp order by sal desc;

--2,再取第一行
select sal from
(select sal from emp order by sal desc)
where rownum=1

--第二种解决办法:
思路：将同一个表复制成两份，要求某一个表中的sal小于另外一个表中的sal,只有工资最高的不可能小，
再利用 not in 进行筛选从而起得最大值

--1,先跨表查询自己,先求出的结果中,e1.sal不可能出现最大数
select e1.sal from emp e1 join emp e2 on e1.sal<e2.sal

--2,然后再not in

select sal from emp where sal not in(select e1.sal from emp e1 join emp e2 on e1.sal<e2.sal)

-----------------------------
--求平均薪水最高的部门的部门编号
--第一种解决办法:
--1,先求出每个部门的平均薪水,
select deptno,avg(sal) from emp group by deptno;

--2,再求每个部门的平均薪水的最高值,
select max(avg_sal) from(select avg(sal) avg_sal from emp group by deptno)

--3,最后再求第一步结果中avg_sal = 最高薪水的记录.
select deptno,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal=
(select max(avg_sal)from(select avg(sal) avg_sal from emp group by deptno) )

--这种写法没有考虑并列第一的情况
select deptno from
(select deptno,avg(sal) avg_sal from emp group by deptno order by avg(sal) desc)
where rownum<=1

--第二种解决办法:
--1,将上面的第一步第二步合并,先求最高平均薪水,用max(avg(sal))的办法
select max(avg(sal)) from emp group by deptno;

--2,求出每个部门的平均薪水

select deptno,avg(sal) from emp group by deptno;

--3,最后再求第二步结果中(即每个部门的平均薪水),avg_sal = (第一步结果)的记录.即avg_sal =最高薪水的记录.
select deptno,avg(sal) from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno);

--第三种解决办法:
--1,先求出每个部门的平均薪水,
select deptno,avg(sal) from emp group by deptno

--2,求最高平均薪水,用max(avg(sal))的办法

select max(avg(sal)) from emp group by deptno;

--3,再使用having语句, avg(sal) = 第二步的结果
--注意：为组函数起的别名在having中不能用

select deptno,avg(sal) avg_sal from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno)

--课堂练习：求平均薪水最高的部门的部门名称
--1,部门平均最高薪水
--2,得到部门编号列表,注意用group by deptno
--3,再应用having子句, having avg(sal) = (第一步的结果)
--4,得到平均最高薪水的那个部门的编号
--5,再得到部门名称

select * from dept where deptno =
(select deptno from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno))

--求平均薪水的等级最低的部门的部门名称
--第一步：部门平均薪水的等级,分成两个小步骤,第一小步是求部门平均薪水
select t1.deptno,t1.avg_sal ,s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno ) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal ---t1

--第二步：最低的等级值
select min(grade) from(
select t1.deptno,t1.avg_sal ,s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno ) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal)

--第三步：等于最低值的部门编号

select *
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal) t1
where t1.grade =
(select min(grade)
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal))

--第四步：求名称

select t.*,dname,loc from
(select *
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal) t1
where t1.grade =
(select min(grade)
from (select t1.deptno, t1.avg_sal, s.grade
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t1
join salgrade s on t1.avg_sal between s.losal and s.hisal))
)t join dept d on t.deptno=d.deptno