標籤：
多表查詢等值串連（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 24 4滿串連12 23 4 45 6左串連12 23 4 45 右串連2 24 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.列出員工表中每個部門的員工數，和部門noselect deptno,count(*) from emp group by deptno2.列出員工表中每個部門的員工數（員工數必須大於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‘ )；
oracle從零開始學習筆記 二
