Single-Row Function 的指令
Function:
將0個或多個參數並回傳單數值
Single function v.s. Group function
Single: 在從Table擷取資料之前，知道有多少參數需要處理
Group: 直到資料已經被截取和組成category後，才知道有多少參數需要處理
SINGLE FUNTION 的使用位置示意
SELECT ename, TO_CHAR(hiredate,’Day, DD-Mon-YYYY’)
FROM emp
WHERE UPPER(ename) LIKE ‘AL%’
ORDER BY SOUNDEX(ename)
INSTR (原字串,欲找字串,起始位置,第幾個符合)
意義: 從string1中第n個字元開始尋找第m次遇到string2的位置。
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) FROM DUAL
↑ ↑從右邊第一個開始找起
14 (position仍是左到右開始數)
Result: 14
LENGTH(String1)
意義: 計算String1的長度。
SELECT LENGTH('Ariel Shao') FROM DUAL //空白也算是一個字元
Result: 10
SUBSTRB(String1,integeri[,intergerj])
SELECT SUBSTRB('Ariel',2,4) FROM DUAL;
Result: riel ?????
SELECT SUBSTRB('Ariel',1,4) FROM DUAL;
Result: Arie
SELECT SUBSTRB('Ariel',2) FROM DUAL;
Result: riel
SELECT SUBSTRB('Ariel',2,-1) FROM DUAL; // integerj為負數則回傳空白
Result:
SELECT SUBSTRB('Ariel',-3,2) FROM DUAL; //從右數第3個(i)開始數兩個數(ie)
Result:ie
TRANSLATE(String1, 要被抽換的字元, 更改的字元)
SELECT TRANSLATE('MyexgirlfriendisArielShao','Ariel','Peggy') FROM DUAL; // A換成P、r換成e
Result: MygxggeyfeggndgsPeggyShao
RTRIM(string[,set]) // 從字串右邊去除所有set字元，set預設空白
SELECT RTRIM('Mississippi','ip') FROM DUAL;
Result: Mississpi
CASE 條件判斷 有兩種寫法，一種適合某欄位"單值比對"，一種適合"條件比對"
http://mark-freefox.blogspot.tw/2011/08/oracle_12.html
SELECT
http://webdesign.kerthis.com/sql/sql_case
【DECODE(條件,值1,回傳值1,值2,回傳值2,...值n,回傳值n,缺省值)】//就是if..else if..else
該函數的含義如下：
IF 條件=值1 THEN
RETURN(翻譯值1)
ELSIF 條件=值2 THEN
RETURN(翻譯值2)
......
ELSIF 條件=值n THEN
RETURN(翻譯值n)
ELSE
RETURN(缺省值)
END IF
e.g.
SELECT empno,ename,salary,grade
FROM emp e,grades g
WHERE e.salary BETWEEN LOW_SALARY AND DECODE(g.grade,'PI','999999',g.high_salary)
【EXISTS】 用來測試內查詢subquery有無產生結果。有則執行外查詢SQL; 無，整個SQL都不會產生結果
語法:
SELECT 欄位1,欄位2
FROM 表格名
WHERE EXISTS (SELECT * FROM 表格名2 WHERE 條件句)
NOT EXISTS 子查詢判斷為False才會執行外查詢
注意:內查詢若產生了超過一筆的資料，則 EXISTS 的條件成立，所以外查詢會被執行，內查詢跟外查詢基本上是獨立關係，子查詢結果不會成為外查詢的Input。
【Special Characters】
'%' 代替任何自元
'_' 代替一個字元
'\' ESCAPE 跳脫字元，使跟在後面的保留字成為一般字元，ex: LIKE 'A\_%' → A_.... ex: 'LIKE 'A\_\%'→ A_%
'desc' 跟在某個column後面，能夠使排序呈現遞減狀態
'||'
【Group Functions】
1.COUNT()/SUM()/MIN()/MAX()/AVG()/STDDEV()/VARIANCE()
2.常搭配使用GROUP BY群組資料
3.限制資料使用HAVING取代WHERE(HAVING有包含Aggregate function)
4.注意子句順序(1.WHERE 2.GROUP 3.HAVING)
直到query執行時和值都截取後才知道inputs數量的function叫做Group Function
注意: 不能處理NULL 值，不能使用在WHERE函數
不可以針對Group Data使用Programmer-written functions
使用地點: SELECT or HAVING (因為Group function不能搭配WHERE使用 ex: SUM()、AVG()..)，HAVING 子句是用來取代 WHERE 搭配聚合函數 (Aggregate Function) 進行"條件查詢"
AVG(DISTINCT|ALL <number>)
COUNT(* ) 傳回整個table所有row的數量(部分資料包含NULL也算)
COUNT(Field) 注意含有NULL值的Field則不會被計算進去
【GROUP BY】-通常搭配Group Function使用,例如依照每個銷售員的"銷售總額"，以銷售員做為分類基準
1.GROUP BY不可以取代別名(Alias name)
.如SELECT中有一欄非聚合函數必用Group By ex: SELECT state,count(*)
3.未搭配聚合函數【SUM() AVG() MAX() MIN()】時，GROUP BY子句後面必須包含所有的欄位，否則有問題
ORDER BY搭配聚合函數
SELECT zip,count(*)
FROM AMS_2G_ONAIR_SITE_BY_ZIP
GROUP BY zip
ORDER BY COUNT(*) DESC;
【Limiting Grouped Data with HAVING(HAVING)-HAVING代替WHERE搭配Aggregate Function進行條件查詢，過濾資料，WHERE無法搭配聚合函數】
注意語法的搭配順序
1.HAVING 其實是對 aggregate method 之結果再做條件篩選， aggregate method 可搭配2.GROUP BY 使用; HAVING 也可以出現在沒有 GROUP BY 的查詢中
範例:
想要查詢每位銷售員的平均銷售，並且屬於outside sales department，並只
回傳銷售超過100,000的銷售員(HAVING):
SELECT sales_clerk, SUM(sale_amount)
FROM gross_sales
1.WHERE sales_dept='OUTSIDE'
2.GROUP BY sales_clerk
3.HAVING SUM(sale_amount)>10000
特別注意，HAVING跟WHERE可以同時出現，注意Grouping前就要先以WHERE篩選,HAVING
要在GROUPING之後
【Nesting Function】
槽狀的 Function可以使得內部Function的Output成為外部Function的Input
SELECT ,deptno 部門號碼, MAX(COUNT(DISTINCT job)) //觀念，MAX()會交代出一個數字，不要管裡面會有幾筆數字
FROM emp
GROUP BY deptno;
