oracle表与表的关联-电脑资料.doc

oracle表与表的关联 电脑资料
两个表之间进行的连接（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
[nbextpage]
--第二种解决方法：
思路：将同一个表复制成两份，要求某一个表中的sal小于另外一个表中的sal,只有工资最高的不可能小，
再利用 not in 进行筛选从而起得最大值
--1,先跨表查询自己，先求出的结果中，e1.sal不可能出现最大数
select e1.sal from emp e1 join emp e2 on e1.sal
--2,然后再not in
select sal from emp where sal not in（select e1.sal from emp e1 join emp e2 on e1.sal
-----------------------------------------------------------------
--求平均薪水最高的部门的部门编号
--第一种解决方法：
--1,先求出每个部门的平均薪水，
select deptno,avg（sal） from emp group by deptno;
--2,再求每个部门的平均薪水的最高值，
select max（avgsal） from（select avg（sal） avgsal from emp group by deptno）
--3,最后再求第一步结果中avgsal = 最高薪水的记录，
select deptno,avgsal from
（select deptno,avg（sal） avgsal from emp group by deptno）
where avgsal=
（select max（avgsal）from（select avg（sal） avgsal from emp group by deptno） ）
--这种写法没有考虑并列第一的情况
select deptno from
（select deptno,avg（sal） avgsal 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,最后再求第二步结果中（即每个部门的平均薪水），avgsal = （第一步结果）的记录。即avgsal =最高薪水的记录。
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） avgsal from emp group by deptno
having avg（sal）=（select max（avg（sal）） from emp group by deptno）