[转]ORACLE SQL 總整理

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

大寫字母

指令 keyword

小寫字母

由使用者決定(指定)

[ ]

Optional(選項) 可有可無

;

SQL Statement 結束符號並執行

{ }

一定要,不可省略

或

，

逗號

…

可重複輸入

Null Value

任何數和null做運算，其結果都為null

設別名時的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者皆符合的資料才顯示)

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_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

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

TO_CHAR(SYSDATE,'DY MM YY') , → 星期一 04 05

DAY

TO_CHAR(SYSDATE,'DAY MM YY') , → 星期一 04 05

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

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 設定之格式要寫在 ' ' 裡

99999 →1234

099999 →001234

$999999 →$1234

L999999 →FF1234

999999.99 →1234.00

999,999 →1,234

TO_CHAR(salary, '$99,999.00'), → $24,000.00

999999MI →1234- 表示負號用的

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

posted @ 2010-03-11 16:18 Franky Chan 阅读(811) 评论(0) 编辑收藏举报

有CODE同亨!

[转]ORACLE SQL 總整理

公告