oracle園地

大寫字母

指令  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

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;