oracle資料集合的操作

標籤：資訊   完成   關心   關於   功能   參與   推薦   color   div   

一、統計函數    統計函數包括：    COUNT()：統計個數    SUM()：計算求和    MIN()：求最小值    AVG()：求平均值    MAX()：求最大值     範例：計算emp表中的總人數、平均工資、總工資、最低工資與最高工資    SELECT COUNT(*) 人數, AVG(sal) 平均工資, SUM(sal) 每月總支出,MIN(sal) 最低工資,MAX(sal) 最高工資 FROM emp;範例：統計出公司的平均僱傭年限SELECT AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12)FROM emp;  ---統計函數允許與其他的函數嵌套 注意：以上的幾個操作函數，在表中沒有資料的時候，只有COUNT()函數會返回結果，其他都是null。 二、分組統計 如果要進行分組則應該使用GROUP BY句子完成，那麼此時的SQL文法結構變為如下形式：【4選出所需要的資料列】SELECT [DISTINCT] * | 分組列[別名]，分組列 [別名]…【1確定資料來源】FROM 表名【2篩選資料行】[WHERE 限定條件(s)];稱[別名]【3對篩選的行分組】[GROUP BY 分組欄位，分組欄位…]【5資料排序】ORDER BY 排序欄位 [ASC|DESC]，排序欄位 [ASC|DESC], …  範例：根據部門編號分組，查詢出每個部門的編號、人數、平均工資SELECT deptno, COUNT(*), AVG(sal)FROM empGROUP BY deptno; 使用分組需要注意三個條件：1、如果查詢不使用GROUP BY 子句，那麼SELECT 子句中只允許出現統計函數，其他任何欄位不允許出現。 2、如果查詢中使用了GROUP BY 子句，那麼SELECT子句中只允許出現分組欄位、統計函數、其他欄位都不允許出現。3、統計函數允許嵌套，但是嵌套之後的SELECT子句裡面只允許出現嵌套函數，而不允許出現任何欄位，包括分組欄位。 三、HAVING 子句 HAVING 子句：有HAVING時必然有GROUP BY。反之未必。【5選出所需要的資料列】SELECT [DISTINCT] * | 分組列[別名]，分組列 [別名]…【1確定資料來源】FROM 表名稱[別名]【2篩選資料行】[WHERE 限定條件(s)];【3對篩選的行分組】[GROUP BY 分組欄位，分組欄位…]【4 針對於篩選的行分組】[HAVING 分組過濾]【6資料排序】ORDER BY 排序欄位 [ASC|DESC]，排序欄位 [ASC|DESC], … 範例：要求查詢出每個職位的名稱，職位的平均工資，但是要求顯示的平均工資高於2000。SELECT job, AVG(sal)FROM empGROUP BY jobHAVING AVG(sal) > 2000; 說明：關於WHERE與HAVING的區別？WHERE子句是在GROUP BY 分組之前進行篩選，指的是選出那些可以參與分組的資料，並且WHERE子句中不允許使用統計函數；HAVING子句實在GROUP BY分組之後執行的，可以使用統計函數   四、子查詢嵌套子查詢就是查詢嵌套。都需要使用（）。而且查詢子句的一個任意位置上都可以隨意出現子查詢。但是出現子查詢最多的位置：WHERE, FROM，HAVING 。如下使用推薦方案。 WHERE子句：子查詢返回單行單列、單行多列、多行單列； HAVING子句：子查詢返回單行單列，而且要使用統計函數過濾； FROM子句：子查詢返回的是多行多列；  1、where子句：        where子句中主要返回單行單列、單行多列、多行單列等幾種情況：        範例：要求查詢出工資工資最低的固原資訊。        SELECT *        FROM emp        WHERE sal=(SELECT MIN(sal) FROM emp);         對於where子句查詢返回多行單列的情況，那麼實質上就相當於告訴使用者一個資料的操作範圍。而對範圍的判斷，主要有三個運算子：IN 、ANY 、ALL        IN 操作：範例：找出工資在MANAGER職員範圍內的僱員資訊SELECT *FROM empWHERE sal IN(SELECT sal FROM emp WHERE job = ‘MANAGER‘); NOT IN操作：範例：找出工資不在MANAGER職員範圍內的僱員資訊SELECT *FROM empWHERE sal IN(SELECT sal FROM emp WHERE job = ‘MANAGER‘); 注意，這裡的操作，一定要保證子句裡查詢的結果不為null。 ANY操作：1、=ANY: 功能上與IN完全沒有任何區別SELECT *FROM empWHERE sal = ANY (SELECT sal FROM emp WHERE job = ‘MANAGER‘); 2、>ANY: 比子查詢返回的最小的內容要大SELECT *FROM empWHERE sal > ANY (SELECT sal FROM emp WHERE job = ‘MANAGER‘); 3、<ANY: 比查詢返回的最大值的內容要小SELECT *FROM empWHERE sal < ANY (SELECT sal FROM emp WHERE job = ‘MANAGER‘);  ALL操作：1、>ALL: 比子查詢返回最大的值要大SELECT *FROM empWHERE sal > All (SELECT sal FROM emp WHERE job = ‘MANAGER‘); 2、<ALL: 比子查詢返回的最小的值還要小SELECT *FROM empWHERE sal < All (SELECT sal FROM emp WHERE job = ‘MANAGER‘);  Exists()判斷是否滿足條件如果現在子查詢有資料返回（不管任何資料）就表示條件滿足。那麼就顯示出資料，否則不顯示。使用exists()只關心子查詢裡面返回的是否有行，至於什麼行，它不關心SELECT *FROM EMP
WHERE EXISTS( SELECT * FROM emp WHERE empno=7839）; 2、having 子句使用子查詢使用HAVING的前提是得使用了GROUP BY，而使用了GROUP BY就一定要分組。範例：要求統計出所有高於公司平均工資的部門編號、平均工資、部門人數。SELECT deptno, COUNT（*）,AVG(sal)FROM empGROUP BY deptnoHAVING AVG(sal) > (SELECT AVG(sal) FROM emp); 3、from子句使用子查詢 範例：將這個語句查詢出來的表最為一張暫存資料表：SELECT deptno, COUNT(empno) count, AVG(sal) avg  FROM emp  GROUP BY deptno---根據部門進行分組查詢出部門人數、平均工資等資訊。然後將temp這張表與dept這張表進行多表查詢。 SELECT d.deptno, d.dname, d.loc, temp.count, temp.avgFROM dept d, ( SELECT deptno, COUNT(empno) count, AVG(sal) avgFROM emp  GROUP BY deptno ) temp  WHERE d.deptno = temp.deptno(+);              

oracle資料集合的操作