星期四, 7月 19, 2012

[轉載] 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的欄位格式

BETWEENAND

區間設定值,介於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_idNULL的欄位)

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  DUALtable無關的算式都可以用

 

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  ElementsTime  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_idIT_PROGslary*1.10,為SA_REPslary*1.20,其餘的一樣為salary

DECODE

一個IF-THEN-ELSE的判斷句

DECODE( job_id ,'IT_PROG', 1.10*salary,

                'SA_REP' , 1.20*salary,

          salary )

job_idIT_PROGslary*1.10,為SA_REPslary*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

SQL1999

Equijoin

Natural or Inner Join

Outerjoin

Left Outer Join (LeftRightFull)

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

求資料列數的rowsnumber of rowsnull不會顯示

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

 

 

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...