Oracle易忘知識點記錄

1、SQL Select語句完整的執行順序：

①from子句組裝來自不同資料源的資料；

②where子句基于指定的條件對記錄行進行篩選；

③group by子句将資料劃分為多個分組；

④使用聚集函數進行計算；

⑤使用having子句篩選分組；

⑥計算所有的表達式；

⑦使用order by對結果集進行排序。

⑧select 集合輸出。

Having與Where的差別

* where 子句的作用是在對查詢結果進行分組前，将不符合where條件的行去掉，即在分組之前過濾資料，where條件中不能包含聚組函數，使用where條件過濾出特定的行。

* having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，條件中經常包含聚組函數，使用having 條件過濾出特定的組，也可以使用多個分組标準進行分組。

2、建立索引（CREATE INDEX）時，将索引配置設定到專門的索引空間；對于有實時資料改變的表，需要在最後加上online關鍵字，就不會阻塞DML語句。

CREATE INDEX index_nm ON table_nm(col_nm)  TABLESPACE tablespace_nm ONLINE;

3、其他

在sql腳本中建立存儲過程時，存儲過程部分代碼結束後，在其下一行加一個反斜杠 / ，表示存儲過程結束，防止oracle将下面的其他sql語句當成存儲過程的一部分。

在某個字段後面追加内容：

UPDATE  table_nm T SET T.IP=T.IP||',127.0.0.1' WHERE 篩選條件;

4、oracle中對于''和null的判斷必須使用 IS NULL才能正确得到傳回值，使用< = >都是false；使用like進行模糊查詢時，'%%'也無法查出null的記錄。

5、EXISTS與IN：

exists用法是把主查詢中的字段傳入到子查詢中去。如果有符合的條件，會停止全表檢索，傳回TRUE。是以效率才要高于IN，IN是要進行完全表檢索得到集合才會結束執行。而EXISTS遇到符合的條件，就會停止執行子查詢。

6、select ...for update語句

for update：鎖定所有表符合條件的行

for update of A.字段：鎖定A表符合條件的行

7、為字段添加注釋時，不要有&字元，oracle會把&解析成變量。

8、在為大表添加有預設值的字段時，使用匿名塊循環處理，将一條語句拆分成三條執行。

ALTER TABLE MKMTJNL ADD (COMT_DIF_AMT NUMBER(9,2));
declare
  I       INT;
  V_COUNT INT;
  V_LOOP  INT;
BEGIN
  SELECT COUNT(*)
    INTO V_COUNT
    FROM MKMTJNL;
  SELECT ceil(V_COUNT / 100000) INTO V_LOOP FROM dual;
  I := 1;
  WHILE I <= V_LOOP LOOP
    UPDATE MKMTJNL SET COMT_DIF_AMT = 0.00 WHERE ROWNUM <= 100000;
    COMMIT;
    I := I + 1;
  END LOOP;
END;

ALTER TABLE MKMTJNL MODIFY (COMT_DIF_AMT DEFAULT 0.00);

9、MINUS(減集),，NTERSECT(交集)，UNION ALL(并集)，UNION（去重并集）

MINUS：運用在兩個SQL語句上，使用第一條sql的查詢結果減去第二條sql的查詢結果集，結果的是第一條sql結果集中的有并且第二條sql結果集中沒有的記錄。

NTERSECT：取兩個sql結果集的交集，結果是兩個 sql結果集中都存在的記錄。

UNION ALL：兩個sql結果集的完全并集。

UNION：兩個sql結果集記錄去重後的并集（distinct）。

使用條件：兩個sql查詢的列必須明确，不能用*；兩個sql列的個數，字段類型，順序必須一緻。

10、WITN AS短語

with as 相當于虛拟視圖。如果with as短語所定義的表名被調用兩次以上，則優化器會自動将with as短語所擷取的資料放入一個temp表裡，如果隻是被調用一次，則不會。

特别對于union all比較有用。因為union all的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，是以可以使用with as短語，則隻要執行一遍即可。

WITH stat AS（SELECT * FROM table_nm）select * from stat;

11、LPAD（左側填充）與RPAD（右側填充）函數

LPAD（原字元串str，長度/正整數x，[用于填充的字元串]y）：①如果沒有第三個參數y（或者長度x比字元串本身長度小），則表示從左側截取長度x個字元 ②使用y填充str到直到長度達到x。

SELECT lpad('abcde',3) FROM dual;　　　　　　 --abc
SELECT lpad('abcde',8,'x') FROM dual;　　　　--xxxabcde

12、SUBSTR與REGEXP_SUBSTR函數

SUBSTR(原字元串str，截取開始位置x，[截取個數]y)：①如果第三個參數為空，表示從x位開始（包括x位）截取到最後一位，位數從1開始；②截取x位之後（包括x位）的y個字元。

SELECT SUBSTR('abcdef',3) FROM dual;　　　　　　　　　　--cdef
SELECT SUBSTR('abcdefefgh',3,5) FROM dual;　　　　　　 --cdefe

REGEXP_SUBSTR(String, pattern, position, occurrence, [modifier]) 五個參數分别表示：①要處理的字元串 ②進行比對的正規表達式 ③比對的開始位置，預設為1 ④字元串處理後取第幾個比對組，預設為1 ⑤模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。預設為'c'。）

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 1) FROM dual;　　　　--111
SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 2) FROM dual;　　　　--222

[^,]+ 表示比對以非,開始，非，結束的規則。（^用在方括号[]裡面時表示非，否則表示字元串開始位置）。

常用應用：

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, LEVEL) spli,LEVEL FROM dual 
CONNECT BY LEVEL <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1;　　--僅限單條記錄的表

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, ROWNUM) spli,ROWNUM FROM dual 
CONNECT BY ROWNUM <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1;　　　　--僅限單條記錄的表

當連接配接條件（connect by條件）沒有限制記錄之間的關系（即 connect by裡沒有類似 id=prior pid的條件，而是 connect by rownum<xxx 或connect by level<xxx ）時，每一條記錄都會作為自己或者其他記錄的子節點，也就說，每一條記錄的子節點就是表上所有的記錄。而樹的層數就是rownum（或是level）值。

13、REPLACE函數

REPLACE（原字元串str，被替換的字元串x，[替換字元串]y}）：①如果第三個參數為空，則将原字元串str中所有的x都删除（換成空字元串）②使用y替換str中出現的所有x。

SELECT REPLACE('abcabcabc','b') FROM dual;　　　　　　--acacac
SELECT REPLACE('abcabcabc','b',' ') FROM dual;　　　　--a ca ca c

14、WM_CONCAT函數

WM_CONCAT（列名）：把列值用逗号分隔并顯示成一行（列轉行）

SELECT WM_CONCAT(COL)
  FROM (SELECT 1 COL FROM DUAL
        UNION ALL
        SELECT 2 COL FROM DUAL);　　　　　　--1,2

15、CAST函數

CAST(expr AS type_name) ：将一種類型轉換成另一種類型。而不僅僅是限于使用用to_number、to_char（）以及to_date（）類型。

使用to_char（）将日期轉換成字元串時，注意分鐘使用mi，而不是mm（mm表示月份）。

16、DECODE與SIGN函數

SIGN（val）：根據val的值是0、正數還是負數，分别傳回0、1、-1

decode(條件/字段,值1,傳回值1,值2,傳回值2,...值n,傳回值n,預設值):根據條件與各個值比對，如果相等，則取對應的傳回值；如果沒有比對的值，則傳回預設值。

SELECT DECODE('x','y','值y','z','值z','預設') FROM dual;　　　　--預設
SELECT DECODE('x','x','值x','z','值z','預設') FROM dual;　　　　--值x

sign與decode聯合使用

SELECT DECODE(SIGN(4-3),0,'表達式等于0',1,'表達式大于0',-1,'表達式小于0') FROM dual;　　　　--表達式大于0

使用decode進行自定義排序

SELECT *FROM 
       (SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL
        UNION ALL
        SELECT 'z' COL FROM DUAL)
 ORDER BY DECODE(COL, 'x', 3, 'y', 2, 'z', 1);

結果：

17、LENGTH與LENGTHB

LENGTH（字元串/字段值）：計算字元串的字元個數。

LENGTHB（字元串/字段值）：計算字元串的位元組個數。

SELECT  length('這是6個字元') FROM dual;　　　　--6
SELECT lengthb('這是12個位元組') FROM dual;　　　　--12

18、OVER(partition by 按哪個字段劃分組，order by 按哪個字段排序)分析函數

說明：聚合函數（如sum()、max()、COUNT()等）可以計算基于組的某種聚合值，但是聚合函數對于某個組隻能傳回一行記錄。若想對于某組傳回多行記錄，則需要使用分析函數。

COUNT() OVER()：應用在查詢明細的同時查詢總記錄數，可使用partition by分組

SELECT COL,COUNT(*)OVER(PARTITION BY COL) COUNT_NUM FROM 
       (SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL);

SUM()OVER() ：累加，統計總和，使用方法與COUNT()OVER()相同

偏移分析函數LAG（） LEAD()

lag與lead函數是跟偏移量相關的兩個分析函數，通過這兩個函數可以在一次查詢中取出同一字段的前N行的資料(lag)和後N行的資料(lead)作為獨立的列,進而更友善地進行進行資料過濾。這種操作可以代替表的自聯接，并且LAG和LEAD有更高的效率。偏移分析函數必須使用order by進行排序，否則報錯。

SELECT COL 本行COL,
       LAG(COL,1,NULL)OVER(ORDER BY col) 上一行COL,
       LEAD(COL,1,NULL)OVER(ORDER BY col) 下一行COL FROM 
       (SELECT 'a' COL FROM DUAL
        UNION ALL
        SELECT 'b' COL FROM DUAL
        UNION ALL
        SELECT 'c' COL FROM DUAL
        UNION ALL
        SELECT 'd' COL FROM DUAL);

作者：葬瞳飄血

出處：https://www.cnblogs.com/ZTPX/

本文版權歸作者和部落格園共有，歡迎轉載，但必須給出原文連結，并保留此段聲明，否則保留追究法律責任的權利。

Oracle易忘知識點記錄

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List