[轉載] ORACLE SQL FUNCTIONS 總整理

轉載來源:

http://oracled2k.pixnet.net/blog/post/24564662-oracle-sql-%E7%B8%BD%E6%95%B4%E7%90%86

 

Statement 陳述	Description 敘述
SELECT	Retrieves data from the database.

 

大寫字母	指令  keyword
小寫字母	由使用者決定(指定)
[ ]	Optional(選項)  可有可無
;	SQL Statement 結束符號並執行
{ }	一定要,不可省略
|	或
，	逗號
…	可重複輸入
Null Value	任何數和null做運算，其結果都為null
AS	設別名時的Keyword
"  "	使用別名時，有要區分大小寫時用此符號，無使用則都為大寫
||	用在字串連結
'  '	用於所要顯示的字串或日期
DISTINCT	Keyword 用來排除重複資料
WHERE	條件子句，可指定特定的欄位，字串要分大小寫
DD-MM-RR	日期的格式，需依照建構資料的基本格式
＝	Equal to
＞	Greater than
＞＝	Greater than or equal to
＜	Less than
＜＝	Less than or equal to
＜＞	Not equal to  (不等於)
	DESC employees  為檢視employees 這個table的欄位格式
BETWEEN…AND…	區間設定值，介於2值之間 WHERE  salary  BETWEEN  2500  AND  3500;
IN(SET)	為值的列表；SET為集合 WHERE  manager_id  IN (100, 101, 201);
LIKE	用於模糊比對 %  此符號為0~多字元的模糊比對 _  此符號為單一值的模糊比對 WHERE  fist_name  LIKE 'S%';(為S開頭的字母以下模  糊比對) WHERE  fist_name  LIKE '_o%';(為第一個字模糊比對第2的字元為o的字母以下模糊比對) 若想要select的字串裡有%和_時，用ESCAPE來定義跳脫字元 WHERE  job_id  LIKE '%SA\_%'ESCAPE '\';(此就可以顯示出_的符號，可參考P2-13)
IS NULL	WHERE  manager_id  IS NULL ;(顯示出manager_id為NULL的欄位)
AND	2者為真，則傳回為真 WHERE  salary  >=1000    AND  job_id  LIKE '%MAN%';(2者皆符合的資料才顯示)
OR	2者其一為真，則傳回為真 WHERE  salary  >=1000     OR  job_id  LIKE '%MAN%';(2者中其一的資料符合者就顯示)
NOT	用於不想顯示的 WHERE  job_id  NOT  IN ('IT_PROG','ST_CLERK'); (job_id為此2者時，不顯示其他的資料就顯示)P2-18
規則順序	1.   算數的運算 2.   關聯的運算 3.   比較條件 4.   IS [NOT] NULL,LIKE,[NOT] IN 5.   [NOT] BETWEEN 6.   NOT  logical condition 7.   AND  logical condition 8.   OR    logical condition WHERE  job_id  LIKE 'SA_REP'j     OR  job_id  LIKE 'AD_PRES' k    AND  salary  > 1500 ; l  (先判斷2.3後再和1一起判斷) WHERE  job_id  LIKE 'SA_REP'     OR  job_id  LIKE 'AD_PRES'    AND  salary  > 1500 ; (若加上()時，先判斷1.2後再和3一起判斷)
ORDER BY	用於指定的欄的排序，預設為ASC(遞增)；DESC(遞減) ORDER  BY  hire_date; (預設為ASC) ORDER  BY  hire_date  DESC; (為遞減) ORDER  BY子句的寫法：支援多個column的排序 1.   BY  column  欄位 2.   BY  column  alias  欄位別名 3.   BY  expression  欄位運算方式 4.   BY  position  欄位編號 ORDER  BY  department_id, salary  DESC;(先照department_id的遞減排序在照salary的遞減排序)
	Case  Manipulation  Functions
LOWER	將字串轉為小寫，可用於欄位 LOWER ('TEST');結果為test LOWER(job_id);結果會顯示job_id都為小寫的
UPPER	將字串轉為大寫，可用於欄位 UPPER('TEST');結果為TEST UPPER(last_name);結果會顯示last_name都為大寫
INITCAP	將字串的第一個字母轉為大寫其餘的小寫，可用於欄位 INITCAP('TEST');結果為Test
	Character-Manipulation  Functions
CONCAT	值的結合 CONCAT('Hello','World');結果為HelloWorld
SUBSTR	獲得一個在決定長度內的字串 SUBSTR('HelloWorld',4,5);結果為loWor
LENGTH	查出此字串的長度 LENGTH('HelloWorld');結果為10
INSTR	查出指定字元在字串中第幾個位置 INSTR('HelloWorld','l');結果為3
LPAD	墊個字串值從右邊放起 LPAD(salary,10,'*');結果為*****24000，設定總共為10個數字其餘補上*
RPAD	墊個字串值從左邊放起 RPAD(salary,10,'*');結果為24000*****，設定總共為10個數字其餘補上*
TRIM	從設定的第一個字元以後才顯示 TRIM('H' FROM 'HelloWorld');結果為elloWorld
DUAL	FROM  DUAL；與table無關的算式都可以用
	Number  Functions
ROUND	四捨五入 ROUND(45.926,2);四捨五入到小數點第2位其結果為45.93 ROUND(45.926,-1);結果為50   ROUND(45.926);結果為46
TRUNC	無條件捨去 TRUNC(45.926,2);無條件捨去到小數點第2位其結果為45.92 TRUNC(45.926,-1);結果為40   TRUNC(45.926,-2);結果為0
MOD	餘數 MOD(1600,300);將1600除以300結果的餘數為100
	Working  with  Dates SELECT  SYSDATE FROM  DUAL;   顯示現在的系統時間
	Date  Functions
MONTHS_ BETWEEN	算在2個日期之間有多少個月，會有小數 MONTHS_ BETWEEN('01-SEP-95','11-JAN-94') →19.677
ADD_MONTHS	依照設定的數字再加上現有的月份 ADD_MONTHS('11-JAN-94',6) → '11-JUL-94'
NEXT_DAY	找出設定日期的下一個指定星期 NEXT_DAY('01-SEP-95','FRIDAY') →'08-SEP-95'
LAST_DAY	找出設定日期的月份的最後一天 LAST_DAY('01-FEB-95') →'28-FEB-95'
ROUND	Assume  SYSDATE = '25-JUL-95' ROUND(SYSDATE, 'MONTH') →01-AUG-95 ROUND(SYSDATE, 'YEAR') →01-JAN-96
TRUNC	Assume  SYSDATE = '25-JUL-95' TRUNC (SYSDATE, 'MONTH') →01-JUL-95 TRUNC (SYSDATE, 'YEAR') →01-JAN-95
	TO_NUMBER   TO_CHAR   TO_CHAR   TO_DATE   CHARACTER   NUMBER   DATE                 Explicit  Data-Type  Conversion
	Elements  of  the  Date  Format  Modle
YYYY	SYSDATE, 25-4月 -05 TO_CHAR(SYSDATE,' DD MM YY') ,  →  25 04 05 TO_CHAR(SYSDATE,'DD MM YYYY') ,  → 25 04 2005
YEAR	TO_CHAR(SYSDATE,'DD MM YEAR') ,  → 25 04 TWO THOUSAND FIVE
MM	TO_CHAR(SYSDATE,'DD MM YY') ,  → 25 4月 05
MONTH	TO_CHAR(SYSDATE,'DD MONTH YY') ,  → 25 4月 05
MON	TO_CHAR(SYSDATE,'DD MON YY') ,  → 25 4月 05
DY	TO_CHAR(SYSDATE,'DY MM YY') ,  → 星期一 04 05
DAY	TO_CHAR(SYSDATE,'DAY MM YY') ,  → 星期一 04 05
DD	TO_CHAR(SYSDATE,'DD MM YY') ,  → 25 04 05
	Sample  Format  Elements  of  Valid  Date  Fromats
WW or W	一年的第幾週or這個月的第幾週
DDD or DD or D	Day of year,month,or week
	Date  Fromat  Elements：Time  Formats
AM or PM	TO_CHAR(SYSDATE,'AM HH DD MM YY'), → 下午 08 25 04 05只要設定一個就可以了
A.M. or P.M.	只要設定一個就可以了
HH or HH12 or HH24	TO_CHAR(SYSDATE,'HH DD MM YY'), → 08 25 04 05 TO_CHAR(SYSDATE,' HH12 DD MM YY'), → 08 25 04 05 TO_CHAR(SYSDATE,'HH24 DD MM YY'), → 20 25 04 05
MI	TO_CHAR(SYSDATE,'AM HH MI SS DD MM YY'), →下午 08 33 01 25 04 05
SS	TO_CHAR(SYSDATE,'AM HH MI SS DD MM YY'), →下午 08 33 01 25 04 05
SSSSS	TO_CHAR(SYSDATE,'HH24 MI SSSSS DD MM YY'), → 20 33 73981 25 04 05
	Number  Format  Elements 設定之格式要寫在 '  ' 裡
9	99999 →1234
0	099999 →001234
$	$999999 →$1234
L	L999999 →FF1234
.	999999.99 →1234.00
,	999,999 →1,234 TO_CHAR(salary, '$99,999.00'), → $24,000.00
MI	999999MI →1234-  表示負號用的
PR	999999PR →<1234>  表示負號用的
EEEE	99.999EEE →1.234E+03
	General  Functions
NVL	給null value一個實際值 NVL(commission_pct,0) NVL(hire_date,'01-JAN-97' →日期要引號 NVL(job_id,'No Job Yet' →字串也要引號
NVL2	NVL2(commission_pct,'SAL+COMM','SAL'), → SAL+COMM  當commission_pct不是null時就顯示 SAL          為null時則顯示SAL
NULLIF	NULLIF(LENGTH(first_name),LENGTH(LAST_name)); → 4 ; 6 ; 4     4 ; 4 ; 4 前後2者傳回值相等時,為null,不相等時,則為前一個傳回值
COALESCE	COALESCE(commission_pct,salary,10), → 當commission_pct不為null時就顯示commission_pct的值,若為null時,就顯示salary的值
	Conditional  Expressions
CASE	一個IF-THEN-ELSE的判斷句 CASE job_id WHEN 'IT_PROG' then  1.10*salary             WHEN 'SA_REP'  then  1.20*salary ELSE        salary  END   當job_id為IT_PROG時slary*1.10，為SA_REP時slary*1.20，其餘的一樣為salary
DECODE	一個IF-THEN-ELSE的判斷句 DECODE( job_id ,'IT_PROG', 1.10*salary,                 'SA_REP' , 1.20*salary,           salary ) 當job_id為IT_PROG時slary*1.10，為SA_REP時slary*1.20，其餘的一樣為salary
	Types of Joins
Equijoin	為一簡單或是內部的join SELECT e.employee_id,e.last_name,d.department_id   FROM employees e,departments d  WHERE e.department_id=d.department_id; →值相同的join
Nonequijoin	SELECT e.salary,e.last_name,j.grade_level   FROM employees e,job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; → 值不相同的，可用>= or<=，但是BETWEEN是最常用的
Outer Join	允許資料not match，主要表格(PT)均呈現，次要表格(ST)可不呈現。(+)為次要表格 SELECT e.department_id,e.last_name,d.department_name   FROM employees e,departments d WHERE e.department_id(+) =d.department_id;
Self Join	只在同個table執行 SELECT e.last_name||'  work for  '||d.last_name   FROM employees e,employees d WHERE e.manager_id = d.employee_id;
	Joining Tables Using SQL
Cross Joins	作向量積的join FROM employees 可改寫為→ FROM employees  Cross join departments
Natural Joins	和Equijoin join 一樣 欄位名稱一定要一樣 FROM employees e,departments d WHERE e.department_id=d.department_id  可改寫為→ FROM employees  NIATURAL JOIN departments
JOIN  USING	指定欄位的JOIN，不能用別名 FROM employees e,departments d WHERE e.department_id=d.department_id FROM employees e JOIN departments d USING (department_id);
	Creating Join with the on Clause
	FROM employees e JOIN employees m   ON  (e.manager_id = m.employees_id);
	Creating Three-Way Join with the on Clause
	WHERE d.dempartment_id = e.dempartment_id    AND  d.location_id = l.location_id JOIN departments d  ON d.dempartment_id = e.dempartment_id JOIN locations l  ON d.location_id = l.location_id;

 

Oracle	SQL：1999
Equijoin	Natural or Inner Join
Outerjoin	Left Outer Join (Left；Right；Full)
Selfjoin	Join ON
Nonequijoin	Join Using
Cartesian Product	Cross Join

 

Left Outer Join	主要表格在左，次要表格在右，為之 FROM employees e, departments d WHERE e.department_id = d.department_id(+);  FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
Right Outer Join	主要表格在右，次要表格在左，為之 FROM employees e, departments d WHERE e.department_id(+) = d.department_id;  FROM employees e RJGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
Full Outer Join	用於去年比較分析表 FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
	Tyoes of Group Functions
AVG	平均值，資料只能是數值，字串和日期不行 AVG(salary) AVG(commission_pct)；含null值時商變小 AVG(NVL(commission_pct,0))；將null轉成0
COUNT	求資料列數的rows，number of rows，null不會顯示 COUNT(*)；returns the number of rows in s table COUNT(commission_pct)；null不會顯示 COUNT(DISTINCT department_id)；去除重複的資料在顯示rows
MAX	求最大值，任何資料型態都可 MAX(salary)；MAX(hire_date)； MAX(AVG(salary)) 先求平均在找最大值
MIN	求最小值，任何資料型態都可 MIN(salary)；MIN(hire_date)
STDDEV	標準平均差
SUM	加總，資料只能是數值，字串和日期不行 SUN(salary)
VARIANCE	變異數
	Creating Groups of Data  資料分群組
GROUP BY	預設為ASC的排序，不能用欄位別名；任何一個欄位或是敘述不是一個合計的功能時，必須用GROUP BY SELECT department_id, AVG(salary)   FROM employees  GROUP BY department_id→先依照部門分組再算AVG(salary)  BRDER BY AVG(salary);也可在接指定排序欄位 SELECT department_id,COUNT(last_name)   FROM employees;  →要改寫成 SELECT department_id,COUNT(last_name)   FROM employees  GROUP BY department_id;
HAVING	用來限制GROUPS SELECT department_id,AVG(salary)   FROM employees  WHERE AVG(salary)>8000  GROUP BY department_id; →要改寫成 SELECT department_id,AVG(salary)   FROM employees HAVING AVG(salary)>8000  GROUP BY department_id;
	Subquery Syntax
	在Subquery裡不要用ORDER BY SELECT last_name   FROM employees  WHERE salary >(SELECT salary                   FROM employees                  WHERE last_name ='Abel'); → ()內的條件會先去找出來，在和原來的salary比對
	Types of Subquery
傳回一個資料	當在Subquery傳回的資料為多筆或是無資料時；則不能對應 SELECT last_name,SALARY   FROM employees  WHERE salary =(SELECT MIN(salary)                   FROM employees); → ()內傳回的為一單個資料
傳回一個以上的資料
IN	Equal to any member in the list SELECT last_name   FROM employees  WHERE salary IN (SELECT salary                    FROM employees                   WHERE last_name ='Abel'); → 為一筆資料對應回去，也可寫 =
AND	任一的對應 SELECT last_name,job_id,salary   FROM employees  WHERE salary < ANY (SELECT salary                        FROM employees                       WHERE job_id='IT_PROG')    AND job_id<>'IT_PROG';  → 判斷()內為傳回多筆資料(9000,6000,4200)；回上層判斷要<這3筆資料，再和ANDㄧ起判斷；
ALL	完全的對應 SELECT last_name,job_id,salary  FROM employees WHERE salary < ALL (SELECT salary                        FROM employees                       WHERE job_id='IT_PROG')    AND job_id<>'IT_PROG';  → 判斷()內為傳回多筆資料(9000,6000,4200)；回上層判斷為ALL則是要<這3筆資料的最小的那一個，再和ANDㄧ起判斷
<ANY	小於最大值
>ANY	大於最小值
=ANY	Is equivalent to IN
<ALL	小於最小值
>ALL	大於最大值
	當在Subquery裡有傳回的值為null，需將null排除 SELECT last_name   FROM employees  WHERE employee_id not in           (SELECT manager_id              FROM employees             WHERE manager_id is not null);
	出現一輸入視窗,欄位鍵入式查詢
適用於	WHERE conditions
	ORDER BY clauses
	Column expressions
	Table names
	Entire SELECT statements