oracle入門之對錶資料查詢（二）

標籤：需要   順序   使用   疑惑   部門   笛卡爾   職位   左外串連   多表查詢   

oracle表複雜查詢

在實際應用中經常需要執行複雜的資料統計，經常需要顯示多張表的資料，現在我們給大家介紹較為複雜的select語句

 

資料分組-max,min,avg,sum,count

如何顯示所有員工中最高工資和最低工資

SQL>select max(sal) "最高工資",min(sal) "最低工資" from emp;

 

請查詢最高年工資

SQL>select max(sal*13+nvl(comm,0)*13) "最高年工資",min(sal*13+nvl(comm,0)*13) "最低年工資" from emp;

 

顯示所有員工的平均工資和工資總和

SQL>select avg(sal) "平均工資",sum(sal) "工資總和" from emp;

特別注意：avg(sal)不會把sal為null的行進行統計，因此我們要注意，如果，你希望為空白值也考慮，則我們可以這樣做

SQL>selec sum(sal)/count(*) from emp;

 

計算共有多少員工

SQL>select count(*) "共有員工" from emp;

 

擴充要求：

請顯示工資最高的員工的名字，工作崗位

SQL>select ename,job from emp where sal=(select max(sal) from emp);

特別注意：select語句執行的順序是從右向左執行，正好和書寫的方式相反。

SQL>select ename,job from emp where sal=(select max(sal) from emp);

oracle會先執行select max(sal) from emp這個語句，得出最大工資後。再執行where條件前的語句。

 

請顯示工資高於平均工資的員工資訊

SQL>select * from emp where sal>(select avg(sal) from emp);

SQL>select * from emp where sal>(select sum(sal)/count(*) from emp);

 

oracle表複雜查詢

group by和having子句

group by用於對查詢的結果分組統計；

having子句用於限制(過濾)分組顯示結果。

 

如何顯示每個部門的平均工資和最高工資

SQL>select avg(sal) "平均工資",max(sal) "最高工資",deptno "部門編號" from emp group by deptno;

 

顯示每個部門的每種崗位的平均工資和最低工資

SQL>select avg(sal) "平均工資",min(sal) "最低工資",job "職位",deptno "部門編號" from emp group by deptno,job order by deptno;

 

顯示部門平均工資低於2000的部門號和它的平均工資

SQL>select avg(sal) "平均工資",deptno "部門編號" from emp group by deptno having avg(sal)<2000;

 

對資料分組的總結：

1、分組函數(avg...)只能出現在挑選清單、having、order by子句中；

2、如果在select語句中同時包含有group by/having/order by那麼他們的順序是group by/having/order by；

3、在選擇列中如果有列、運算式和分組函數，那麼這些列和運算式必需有一個出現在group by子句中，否則會出錯。

如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;

這裡deptno就一定要出現在group by 中。

 

oracle表複雜查詢--多表查詢

說明：

多表查詢是指基於兩個和兩個以上的表或是視圖的查詢，在實際應用中，查詢單個表可能不能滿足你的需求，(如顯示sales部門位置和其員工的姓名)，這種情況下需要使用到(dept表和emp表)

 

顯示僱員名，僱員工資及所在的部門的名字[笛卡爾集]

規定：多表查詢的條件是至少不能少於表的個數-1

注意：笛卡爾集，在多表查詢的時候，如果不帶任何條件，則會出現笛卡爾集，避免笛卡爾集多表查詢的條件是，至少不能少於表的個數-1

SQL>select e.ename,e.sal,d.dname from emp e,dept d where d.deptno=e.deptno;

 

如何顯示部門為10的部門名、員工名和工資

SQL>select d.dname,e.ename,e.sal,e.deptno from emp e,dept d where d.deptno=e.deptno and e.deptno=10;

 

顯示各個員工的姓名、工資及其工資的層級

SQL>select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

注意：在多表查詢時，不同的表中列名相同時要加表名，不同時可不加。(為增強可讀性，建議都加表名或別名)

 

擴充要求：

顯示僱員名，僱員工資及所在部門的名字，並按部門排序。

SQL>select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno order by d.dname;

 

oracle表複雜查詢--多表查詢

自串連

自串連是指在同一張表的串連查詢

 

顯示員工的上級領導的姓名

SQL>select e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

 

比如顯示‘FORD‘的上級

SQL>select e1.ename "員工姓名",e2.ename "領導姓名" from emp e1,emp e2 where e1.mgr=e2.empno and e1.ename=‘FORD‘;

 

擴充要求：

顯示各員工的姓名和他的上級領導姓名

SQL>select e1.ename "員工姓名",e2.ename "領導姓名" from emp e1,emp e2 where e1.mgr=e2.empno;

疑惑：這裡我們看到king沒有顯示，因為king沒有上級。如果我們希望把沒有上級的員工也顯示出來，則需要使用到外串連。外串連包括左外串連和右外串連。此處提到外串連，後面會詳細講解。

 

左外串連：select 列名,.. from 表名1 left join 表名2 on 條件;

SQL>select e1.ename "員工姓名",e2.ename "直接上級領導" from emp e1 left join emp e2 on e1.mgr=e2.empno;

或者使用(+)在右邊也可以實現左外串連。

SQL>select e1.ename "員工姓名",e2.ename "領導姓名" from emp e1,emp e2 where e1.mgr=e2.empno(+);

 

右外串連：select 列名,.. from 表名1 right join 表名2 on 條件;

SQL>select e1.ename "員工姓名",e2.ename "直接上級領導" from emp e2 right join emp e1 on e1.mgr=e2.empno;

或者使用(+)在左邊也可以實現右外串連。

SQL>select e1.ename "員工姓名",e2.ename "領導姓名" from emp e1,emp e2 where e2.empno(+)=e1.mgr;

左外串連和右外串連在這裡提到，後面會詳細講解。

oracle入門之對錶資料查詢（二）