oracle从零开始学习笔记 二
多表查询
等值连接（Equijoin）
select ename,empno,sal,emp.deptno from emp,dept where dept.deptno=emp.deptno;
非等值连接(Non-Equijoin)
select ename ,empno,grade from emp,salgrade where sal>losal and sal<hisal;//between and 是包含俩边界值的，not between and 是不包含边界的 (between 1 and 100)
自连接（Self join）
select e.empno,e.ename,m.empno,ename from emp e,emp m where e.mgr=m.empno;
select e.empno,e.ename,m.empno,ename from emp e,emp m where m.mgr=e.empno;
左外连接（Left Out Join）
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno(+);
select empno,ename,dname from emp left outer join on emp.deptno=dept.deptno;
右外连接(Right Outer join)
select empno,ename,dname from emp,dept where emp.deptno(+)=dept.deptno;
select empno,ename,dname from emp right outer join on emp.deptno=dept.deptno;
满外连接（Full Outer join）
selece empno,ename,dname from emp full outer join dept on emp.deptno=dept.deptno;
PS：左连接为左表为主，右表的空值不考虑，右连接同理
内连接
2 2
4 4
满连接
1
2 2
3
4 4
5
6
左连接
1
2 2
3
4 4
5
右连接2 2
4 4
6
集合操作(最好表结构相同，起码要求union两边的字段数相同)
unino:并集，所有的内容都查询，重复的显示一次
unino all：并集，所有的内容都显示，包括重复的
intersect：交集:只显示重复的
minus：差集，只显示对方没有的（跟顺序是有关系的）
创建一张只包含20部门员工信息表：create teble emp20 as select * from emp where deptno=20;
select * from emp union select * from emp20;
select * from emp union all select * from emp20;
select * from emp intersect select * from emp20;select * from emp minus select * from emp20;
子查询
单行子查询
select * from emp where sal >(select sal from emp where empno=7566);(子查询为空值则主查询也不会返回任何结果)
多行子查询
select ename, sal from emp where sal>any(select avg(sal) from emp gorup by deptno);比三个部门任一平均水平高即可
select ename, sal from emp where sal>all(select avg(sal) from emp gorup by deptno);比三个部门所有平均水平高即可
select ename,job from emp where job in(select job from emp where ename='MARTIN' or ename='SMITH');
TopN查询
select * from emp order by sal desc where rownum<=5;(rownum的值为2即为第2行，这里是返回前五行)
分页查询
select * from(select rownum no,e.* from (select * from emp order by sal desc) e where rownum<5) where no>=3;
select * from(select rownum no,e.* from (select * from emp order by sal desc) e) where no>=3 and no<=5;
exists （类似于in）
select * from t1 where exists(select null from t2 where y=x);
对于in 和exists的性能区别：
如果子查询得出的结果集记录比较少，主查询中的表较大且又有索引时应该用in，反之如果外层的朱查询记录比较少，子查询的表达，又有索引时用exists.
其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么会先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了
另外in是不对null进行处理，如：
select 1 from dual where null in （0,1,2,null）为空
练习：
1.列出员工表中每个部门的员工数，和部门no
select deptno,count(*) from emp group by deptno
2.列出员工表中每个部门的员工数（员工数必须大于3）和部门名称
select d.*,ed.cou from dept d,(select deptno,count(*)cou from emp group by deptno having count(*)>3) ed where ed.deptno=d.deptno;
3.找出工资比Jones多的员工
select * from emp where sal>(select sal from emp where ename='JONES');
4,列出所有员工的姓名及其上级的姓名
select e1.ename lower,e2.ename upper from emp e1,emp e2 where e1.mgr=e2.empno(+);
5.以职位分组，找出平均工资最高的两种职位
select * from （select job,avg(sal) from emp group by job order by avg(sal) desc）where rownum<3;
6.查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名，部门名称
select ename,dname from emp e,dept d where e.deptno !=20 and e.deptno=d.deptno and sal >all(select sal from emp where deptno=20);
select ename,dname from emp e,dept d where e.deptno !=20 and e.deptno=d.deptno and sal >(select max(sal) from emp where deptno=20);
7.得到平均工资大于2000的工作职种
select job from emp group by job having avg(sal)>2000;
8.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select * from dept where deptno=(select e.deptno from (select deptno,sum(sal) from emp group by deptno order by sum(sal))e where rownum=1)
9.得到平均工资等级为4级（工资等级表salgrade）的部门编号
select e.deptno from salgrade g,(select deptno,avg(sal)avgsal from emp group by deptno)e where g.grade=4 and e.avgsal between g.losal and g.hisal;
10.找出收入（工资加奖金），下级比上级还高的员工编号，员工姓名，员工收入
select e.ename,e.ename,e.sal+nvl（e.comm,0）from emp e,emp m where e.mgr=m.empno and (e.sal+nvl(e.comm,0))>(m.sal+nvl(m.comm,0));
11.找出工资等级不为4级的员工的员工姓名，部门名称，部门位置
select e.ename,d.dname,d.loc from emp e,dept d,salgrade g where e.deptno=d.deptno and g.grade=4 and e.sal not between g.losal and g.hisal;
12.找出职位和‘Martin’或者‘smith’一样的员工的平均工资
select avg(sal) from emp where job in (select job from emp where ename='MARTIN' or ename='SMITH' )；
