溫習 SQL 01(Z)

1. 特殊别名 ( 重要指數 * )

當想起的别名中有特殊字元時，要用 “”括起來

select employee_id “#emp_id#”from employees;

2. DINSTINCT ( 重要指數 * )

當使用DISTINCT關鍵字時，即想要去除重複内容時，distinct一定要放在查詢清單中的第一個位置

select distinct first_name, phone_number from employees;

3. escape 用法 ( 重要指數 * )

SELECT *

FROM ZHPPEXP05

WHERE WORK_DATE = '20080104'

AND BUYER2 LIKE 'XAA\_%' ESCAPE '\'

這樣以後, _ 隻代表普通字元，下劃線，而沒有特殊意義了。

4. 常用函數 ( 重要指數 ** )

CONCAT, INITCAP, INSTR, LENGTH, LOWER, UPPER, RPAD, LPAD, LTRIM, RTRIM, TRIM, SUBSTR, REPLACE, ABS, CEIL, FLOOR, MOD, ROUND, TRUNC, SIGN, ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, SYSDATE, TO_CHAR, TO_DATE, TO_NUMBER

具體函數内容， 請參閱另一篇， 函數大全。

5. 子查詢 ( 重要指數 *** )

傳回單值子查詢比較符号 ( = , > , < , <> 等等 )

傳回多值子查詢比較符号( in , Any , All 等等 )

Any ( 同意詞 some ) : <ANY means less than the maximum , >ANY means more than the minimum =ANY means IN

<ALL means less than the minimum , >ALL means more than the maximum

子查詢： 最容易出現的錯誤就是傳回 NULL , 而進行了相應的判斷。

相關子查詢

每一次執行，都要執行一遍子查詢，而以上标量子查詢，總共隻需要執行一次子查詢。

SELECT column1 , column2

FROM table1 outer --> 有時外部表和内部表是一個表, 是以使用别名好一點 , 必須的用

WHERE column1 operator

( SELECT column1 , column2

FROM table2

WHERE expr1 = outer.expr2 ) ;

使用 EXISTS 關鍵字 ( 隻是邏輯判斷，是以速度快，例如子查詢可以查出10,000條資料，如果使用 exists的話，那麼當發現可以查詢到時，就傳回。)

SEELCT column1, column2

FROM  table1 outer

WHERE exists ( select cola from table2 where expr1 = outer. expr2 ) ;

個人推薦 exists

修改使用子查詢

UPDATE table1 别名1

SET column = ( SELECT expression FROM table2 别名2 WHERE 别名1.column = 别名2.column ) ;

删除使用子查詢

DELETE FROM table1 别名1

WHERE column operator ( SELECT expression FROM table2 别名2 WHERE 别名1.column = 别名2.column ) ;

6. with 語句 ( 重要指數 * )

當寫的query 太長時，可以将部分内容分解，然後用一個辨別符将其替代， 這樣，整個query看起來就沒有那麼複雜，類似C中函數分解功能

with

with_plan_qty as ( select plan from zhppexp05 ) ; 這樣以後就可以使用 with_plan_qty 來代替括号中的内容。

7. merge ( 重要指數 ** )

merge的作用是，根據不同的條件來判斷是進行 insert 操作，還是update操作, 一般是從一個 table | view 複制到另一個 table.

MERGE INTO copy_emp as c

USING employees e

ON ( C.employee_id = e.employee_id )

WHEN MATCHED THEN

UPDATE SET

c.first_name = e.first_name,

c.last_name = e.last_name,

c.department_id = e.department_id

WHEN NOT MATCHED THEN

INSERT(employee_id, first_name, last_name, department_id)

VALUES(e.employee_id,e.first_name,e.last_name,e.department_id)

注意， merge 最後不需要 ; 分号, 并且 update 和 insert 關鍵詞後每有表明

8. Database transaction

DML , DCL , DDL

資料狀态是 , 硬碟database data block儲存一份 , 還有 undo segment也儲存一份 , 當一個使用者改變資料時 , 他可以讀data block中的一份,在他沒 commit 之前 , 隻有他本人可以看到這個資料 , 其他使用者檢視的是 undo segment中的資料 , 當他 commit 之後 , 其他使用者才可以看到data block中的内容, database 并将 undo segment 清空 , 而當使用者 rollback 時 , The original , older version , of the data in the undo segment iswritten back to the table . All user can see this existed before the transaction began .

DCL : SAVEPOINT A, SAVEPOINT B , ROLLBACK TO SAVEPOINT A

DML : INSERT 等等

DDL : CREATE TABLE

9. Date類型 & TIMESTAMP

Timestamp類型是指在傳統的 date類型下 (最小機關是秒) , 更加精确 , 可以指定精确秒的位數 , 例如 TIMESTAMP(6) 精确到秒後6位

Timestamp主要是應對國際化的要求

oracle 時區分為2部分 : 資料庫時區 和 session時區

SELECT DBTIMEZONE FROM DUAL ( 資料庫時區 )

SELECT SESSIONTIMEZONE FROM DUAL ( session時區 )

資料類型 : DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE

DATE類型 : 存儲日期和時間，精确到秒

TIMESTAMP : DATE類型的擴充，保留小數級别的秒，預設為小數點後6位，不儲存時區和地區資訊。

TIMESTAMP WITH TIMEZONE : 帶時區的 TIMESTAMP類型

TIMESTAMP WITH LOCAL TIMEZONE: 和 TIMESTAMP WITH TIMEZONE的差別在于，資料庫不儲存時區資訊，而是把用戶端輸入的事件轉換為基于 database timezone 的時間後存入資料庫（這也是 database timezone設定的意義 ) 當使用者請求時， oracle把資料轉為使用者所在 session的時區傳回給使用者，是以，oracle建議吧 database timezone設定為标準時間 UTC, 這樣可以節省轉換的開銷。

如何選擇時間資料類型

當你不需要儲存時區/地區資訊的時候，選擇使用TIMESTAMP資料類型，因為它一般需要7-11bytes的存儲空間，可以節省空間。

當你需要儲存時區/地區資訊的時候，請選擇使用TIMESTAMP WITH TIMEZONE資料類型。比如一個跨國銀行業務應用系統，需要精确紀錄每一筆交易的時間和地點(時區)，在這種情況下就需要紀錄時區相關資訊。因為需要紀錄時區相關資訊，是以需要多一些的存儲空間，一般需要13bytes。

當你并不關心操作發生的具體地點，而隻是關心操作是在你目前時區的幾點發生的時候，選擇使用TIMESTAMP WITH LOCALTIME ZONE。比如一個全球統一的change controlsystem。使用者可能隻關心某某操作是在我的時間幾點發生的(比如中國使用者看到的是中原標準時間8:00am，而倫敦的使用者看到的是0:00am)。記住，此類行不儲存時區/地區資訊，是以如果需要儲存相關資訊的要慎重！

存儲時間間隔

interval year to month ( 年和月 )

interval day to second ( 天和秒 )

存儲時間間隔，隻存儲一段時間，例如 1年3個月，是一個線或段， 而儲存時間值，是某一個點。

10. 利用查詢語句建立一個 table