Oracle基礎（四）：單表查詢，oracle基礎單表查詢

Oracle基礎（四）：單表查詢，oracle基礎單表查詢

一、基本查詢 （一）基本語句1、文法

• select 【distinct】 * | {列名...}  from 表名 【where {條件}】

2、注意1）distinct：指顯示結果時，是否剔除重複資料

• 指查詢的欄位資料，全部一致，才算重複。

• SELECT distinct deptno,job,empno FROM EMP order by deptno;

• 查詢的這（deptno,job,empno）三個欄位需要一樣，才算重複。

SELECT a.deptno,a.job FROM EMP a; SELECT distinct a.deptno,a.job FROM EMP a order by a.deptno

• 查詢的這（deptno,job）2個欄位需要一樣

2）去重：

SELECT a.deptno,a.job FROM EMP a; SELECT distinct a.deptno,a.job FROM EMP a order by a.deptno

• 查詢的這（deptno,job）2個欄位需要一樣

（二）其他用法1、算數運算式1）方法：

• ABS(x) ：絕對值
• sign(x) ：判斷正負。
  • 正值返回1，負值返回-1，0返回0
• ceil(x)：向上取整。
  • select ceil(3.1),ceil(0),ceil(-3.9) from dual; --4、0、-3
• floor(x) ：向下取整。
• power(x,y) ：返回x的y次冪 
• mod(x,y) ：返回x除以y的餘數
• round(x[,y]) ：返回四捨五入後的值 
• trunc(x[,y]) ：返回x按精度y截取後的值 。*

• select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;

  
  
  • 顯示：5555.66、5500、5555
• sqrt(x) ：返回x的平方根

2）、執行個體：顯示每個僱員的年工資

• 語句：

• SELECT a.ename,(a.sal*13+nvl(a.comm,0)*13) as "年薪", a.ename||'-'||(a.sal*13+nvl(a.comm,0)*13) as "拼接字串"FROM EMP a ;

  
  
  • comm：為獎金
• 注意：
  • nvl(欄位，0)：處理null值
  • as "" ：增加別名
  • ||：拼接字串

2、where 條件1）to_char()函數：

SELECT * FROM EMP a WHERE to_char(a.hiredate,'yyyy-mm-dd') > '1981-11-17';

2）like：‘%’-0~多個；‘_’-1個3）between：薪水在2000~2500的員工

SELECT * FROM EMP a WHERE a.sal between 2000 and 2500;--包含本身，都是閉區間SELECT * FROM EMP a WHERE a.sal >= 2000 and a.sal <= 2500;

4）in：員工編號號為7369,7521,7654的員工

SELECT * FROM EMP a WHERE a.empno in (7369,7521,7654);

5）is null ：沒有上級的員工

SELECT * FROM EMP a WHERE a.mgr IS NULL;

6）邏輯操作符：NOT 、And 、Or

SELECT * FROM EMP a WHERE (a.sal >500 or a.job='MANAGER') and a.ename like 'J%';

• and 與 or的優先順序：NOT > AND > OR
  • where 後面如果有or的條件，則or自動會把左右的查詢條件分開
  • 用()來區分執行順序

7）order by：對結果進行排序（先有結果，支援對別名排序）

• asc-升序（預設）/desc-降序
• 排序執行個體

• SELECT * FROM EMP a order by a.sal;--一個條件SELECT * FROM EMP a order by a.deptno , a.hiredate desc;--2個條件

  
  
  • 先按deptno 排序
  • 在第一個排序結果中，按第二個條件。
• 按列的別名排序

SELECT a.ename,(a.sal*13+nvl(a.comm,0)*13) as "年薪" FROM EMP a order by 年薪;

• 這邊可以按照列的編號排序

• SELECT a.ename,(a.sal*13+nvl(a.comm,0)*13) as "年薪" FROM EMP a order by 1;

  • 這邊的編號，不能超出顯示的列的數量。

二、彙總函式：max、min、avg、sum、count
1、取最高和最低工資：max、min1）SQL執行個體

SELECT max(a.sal),min(a.sal) FROM emp a ;SELECT max(a.sal*13) , min(a.sal*13) FROM emp a ;

• --放在一起，前提條件是max 和min都返回一個值

2）注意：max 與min 只能返回一個值。
2、顯示平均工資與工資總額:avg、sum1）SQL執行個體

SELECT round(avg(a.sal),2),sum(a.sal) FROM emp a ;

• round：四捨五入

2）注意：avg：計算平均值，不會把sal為null的統計在內

SELECT avg(a.comm) FROM emp a ;SELECT sum(a.comm)/count(*) FROM emp a ;

• 為空白的參與，可以這麼解決

3、計算總的員工數：count1）SQL執行個體

SELECT count(*) FROM emp a ;SELECT count(a.empno) FROM emp a ;

2）注意：

SELECT count(a.comm) FROM emp a ;

• 不統計 null 的列。

4、擴充練習1）顯示工資最高的員工的名字，工作崗位

SELECT a.ename,a.job FROM emp a WHERE a.sal = (SELECT max(a.sal) FROM emp a);

• 子查詢
• 可以先查出最高工資
• 然後查出哪位員工的工資是這麼多

• 分析執行順序
  • sql執行預設是從右至左執行
  • 先執行：SELECT max(a.sal) FROM emp a 得到 5000
  • 再執行：SELECT a.ename,a.job FROM emp a WHERE a.sal =5000；

2）顯示工資高於平均工資的員工資訊

SELECT a.ename,a.job FROM emp a WHERE a.sal > (SELECT avg(a.sal) FROM emp a);

三、分組：group by 和 having
1、解釋：

• group by：對查詢的結果進行分組統計
• having：修飾分組的條件，限制分組顯示結果。
  • 不支援別名

2、執行個體：1）顯示每個部門的平均工資和最高工資

• SQL：

SELECT avg(a.sal), max(a.sal),a.deptno FROM emp a group by a.deptno;

• 分析：
  • SELECT avg(a.sal), max(a.sal),a.deptno FROM emp 報錯
  • avg(a.sal) 和max(a.sal)為一個結果，而 a.deptno 很多結果。

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

• SQL：

SELECT avg(a.sal), max(a.sal),a.deptno,a.job FROM emp a group by a.deptno ,a.job order by a.deptno ；

• 分析：
  • 先對【部門】分組，再對【崗位】分組。
  • 【排序】都是放在末尾

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

• SQL：

SELECT avg(a.sal) as "平均工資", a.deptno FROM emp a group by a.deptno having avg(a.sal)<2000

• 分析：
  • having：限制分組顯示結果。後面不支援別名，只能重新計算。
  • 先分組，計算各個部門的平均工資。再利用having對分組後的結果進行刪選。

3、原則總結：1）彙總函式（avg...）只能出現在挑選清單、having、order by 子句中，絕不能出現在group by  函數 和where中。2）如果在 select 語句中同時包含 group by 、having、order by 那麼他們的順序是 group by 、having、order by 。3）在選擇列中如果有列、運算式和彙總函式，那麼這些列和運算式必須有一個出現在 group by 子句中，否則就會出錯。

• SELECT avg(a.sal), max(a.sal),a.deptno，a.job FROM emp a group by a.deptno, a.job;
• 這邊的 deptno 和 job 必須都要 出現在 group by 中。
• 原因為彙總函式 每次返回一個結果