Oracle 筆記之高級查詢

為列起別名

目的 我們進行查詢時 經常查詢某一列時使用的是表達式 SELECT enamme,sal*12 FROM emp

這樣不好的地方是第二列在查詢后列用使用的就是sal*12.這樣的可讀性比較差為此我們會為列起別名，來增加可讀性

別名本身不區分大小寫，若希望區分，則別名需要使用雙引號，當別名中含有空格，也應使用雙引號

SELECT ename,sal*12 "Annual Salary" FROM emp;

WHERE子句

用于在查詢數據的過程中過濾記錄的，只有滿足WHERE子句中的條件的記錄才會被查詢出來， 數據庫在查詢表的時候，每一條記錄都要經過一次WHERE的過濾

查看工資小于2000的

SELECT ename,sal FROM emp WHERE sal<2000;

查看部門不屬于10的員工信息

SELECT ename,sal,job FROM emp WHERE deptno !=10;

查詢1980年出生的

SELECT ename,sal,hiredate FROM emp WHERE hiredate>TO_DATE('1980','YYYY');

查詢sal大于1000 且工作為clerk

SELECT ename,sal,job FROM emp WHERE sal>1000 AND job='CLERK';

查詢工資大于1500的CLERK 或者是不限制工資的所有SALESMAN

SELECT * FROM emp WHERE sal>1500 AND job='CLERK' OR job ='SALESMAN';

提高優先級的意思是 查看工資大于1500的CLERK 和SALESMAN

SELECT * FROM emp WHERE sal>1500 AND (job='CLERK' OR job ='SALESMAN');

查看emp表中ename 第二個字母的是A的名字

SELECT * FROM emp WHERE ename LIKE '_A%';

IN 比較符 可以比較等于列別中的其中之一

SELECT * FROM emp WHERE job IN('MANAGER','CLERK'); ==SELECT * FROM emp WHERE job='MANAGER' OR job='CLERK';

查詢不等于MANAGER 和CKERK的所有工作

SELECT * FROM emp WHERE job NOT IN('MANAGER','CLERK');

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

SELECT * FROM emp WHERE sal>ANY(3000,2000,4000);

SELECT * FROM emp WHERE sal*12>50000;

SELECT ename,sal FROM emp WHERE UPPER(ename) = upper('rose');

DISTINCT關鍵字用于去除給定列的重復數據 查看公司都有哪些職位，重復的去除

SELECT DISTINCT job FROM emp;

當我們對多個列進行去重復時，表示這幾個列的值的組合沒有重復的

SELECT DISTINCT deptno,job FROM emp;

ORDER BY子句，用于對結果集進行排序

ORDER BY子句只能出現在SELECT語句的最后。

ORDER BY 后面可以指定若干字段，排序優先級為從左到右，

ASC 表示升序，默認就是升序，所以可以不寫

DESC表示降序

按照部門編號從小刀大進行排序結果集

SELECT * FROM emp ORDER BY deptno

查詢emp表里部門為10的工資和姓名 并降序排列

SELECT ename,sal FROM emp WHERE deptno =10 ORDER BY  sal DESC;

在排序一個含有NULL值的字段時，NULL值被認為是無限大，所以在降序排序時，NULL值會出現在最前面

-------------------------------------------------------------------------------------------

聚合函數

MAX和MIN

SELECT MAX(sal) FROM emp;

***聚合函數都是忽略NULL值

SELECT MAX(sal) as "最大",MIN(sal) as "最小" FROM emp;

SELECT AVG(NVL(comm,0) ) as "平均值" FROM emp;

SELECT AVG(sal),SUM(sal) FROM emp;

SELECT COUNT(comm) FROM emp;--統計條數，不統計值

SELECT COUNT(*) FROM emp;--統計整張表有多少條記錄

GROUP BY子句 用于將表里的數據進行 分組，分組原則為GROUP BY 后面給定的字段的值相同的記錄看做一組

查看每個部門的最大工資 最小工資等：

SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal),deptno FROM emp GROUP BY deptno;

聚合函數又稱組函數

SQL有一個要求

在SELECT子句紅若出現了組函數，那么不在組函數中的其他字段，必須出現GROUP BY 子句中，但是反過來則不是必須的

若字段中每條疾苦的值都不重復，那么該字段通常不應該在GROUP BY子句中作為分組的參照。

若指定了GROUP BY子句，那么SELECT子句中不在組函數中的其他字段且不在GROUP BY子句中則不能出現

WHERE 與HAVING的區別

相同點，都是用作過濾,只不過HAVING是用在第二次過濾的，WHERE是用在第一次查詢表的時候過濾條件的。

HAVING是在第一次查詢后，得到結果的基礎上再次進行過濾使用的。

---------------------------------------

查詢優先級

from子句，執行順序，從后往前，從右到左 數據量少的放后面

where子句 執行順序為自上而下，從右到左，將能過濾掉最大數量記錄的條件卸載where子句的最右

group by 執行順序從左往右分組

select子句，少用*號 盡量取字段名

----------------------------------------------------------------------------

關聯查詢

N張表聯合查詢，至少要有N-1個連接條件，否則會產生笛卡爾積：A表的記錄總數*B表的記錄總數，無意義的結果集

兩張表聯合查詢，要添加至少一個連接條件。因為查詢時很難避免兩張表出現相同名字的字段，為了解決這個問題，可以

使用 表名.字段 來確認

若別名比較長，柯表名叫上別名來簡化

SELECT e.ename,e.sal,d.dname,e.deptno FROM emp e,dept d WHERE e.deptno = d.pdeptno;

SELECT e.ename,e.sal,d.dname,e.deptno FROM emp e JOIN dept d ON(d.deptno=e.deptno);

SQL89標準時，弊端在于，連接條件與過濾條件都要寫在where子句中，可讀性相對較差，尤其多表查詢加上過濾條件多的時候。

SELECT e.ename,d.DNAME  FROM emp e,dept d WHERE d.DEPTNO=e.DEPTNO AND d.DNAME='SALES';

SQL92建議多表連接用 用SELECT ... FROM JOIN...ON ...WHERE(只寫條件)

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = e.deptno WHERE d.dname ='SALES'

SQL92標準建議我們在奪標連接時使用內連接形式

這樣會發現，連接條件單獨定義在ON子句中，而過濾條件卸載WHERE子句中，可讀性比較強

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.dname='SALES';

外連接

自連接

當前表中的數據關聯 表中的其他數據。就構成了自連接。通常表被設計成自連接是為了表示同類型數據有存在上下級關系（樹狀結構的數據通常設計為自連接。)

例如：淘寶網的類別。就被設計為自連接。

設計表的時候以及關聯關系時通常有兩個字段很重要，他們被稱為：主鍵外鍵

通常每張表的第一個字段就是主鍵，主鍵保存的數據一般與數據無關系，他只是用來標識每條數據的唯一性，所以主鍵的要求是

存儲的值非空且唯一。

外鍵，用來保存關系表中記錄的主鍵的值，例如

部門表中有一個字段叫做dept，它用來保存dept表中某條記錄主鍵的值那么emp表的dept就是外鍵

在關聯關系中，保存外鍵的表通常是“一對多”中“多”的一方