有oracle特色的sql語句整理

我們知道每個RDBMS在sql方面都會存在自己的特色。那麼今天我們來看看oracle有啥特色值得我們來在

意。

特色1 ：
Oracle分析函數與開窗函數：
文法：

    FUNCTION_NAME  (<argument>,<argument>...)
    OVER           (<Partition-Clause><Order-by-Clause><Windowing Clause>)
例如：
   sum(sal) over (partition by deptno order by ename rows between。。。)
   其中，sum是函數名，
         Over（）是關鍵字，直接點就是給分析函數加條件,用於識別sum（）是彙總函式還是分析函數
說明：
（1）通常在做統計分析時我們都想儘可能多滴選擇出原始列和統計值列,但是這樣group by後面就必須

跟隨更多的列,流量分析函數可以避免使用group by時選擇出來的列名必須出現在group by列表中的痛苦

（2）彙總函式用group by分組，每個分組返回一個統計值；分析函數用partition by分組，每組每行都

可以返回一個統計值。

（3）分析函數帶有一個開窗函數over（），含三個分析字句：
           分組（partition by）排序（order by）視窗（rows）

（4）兩個order by的區別：

        分析函數是在整個sql查詢後（sql語句的執行比較特殊）再進行的操作，也就是說，sql語句的

order by也會影響分析函數的執行結果。

            A） 如果sql語句中的order by滿足分析函數分析時要求的排序，那麼sql語句的排序將先

執行，分析函數在分析時就 不必再排序了。

            B） 如果sql語句中的order by不滿足分析函數分析時要求的排序，那麼sql語句中的排序

將先執行。

（5）視窗就是分析函數分析時要處理的資料範圍：

           第一行是：unbounded preceding

           當前行是：current row

           最後一行是：unbounded following

      視窗字句不能單獨出現，必須有order by子句時才能出現。而出現order by子句時，不一定要有

視窗子句，此時的視窗預設是第一行到最後一行；

           當省略視窗子句時：

           A）如果存在order by，則預設的視窗是unbounded preceding
And current row；

           B）如果同時省略order by，則預設的視窗是unbounded preceding and unbounded

following

例子：

1 統計每個部門工資最高的哪位？

select * from                                                                      

   (                                                                           

    select ename,sal,deptno,rank()over(partition by deptno order by sal desc) mm from emp

   )                                                                           

where mm=1

 

注意：
      1）.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個並列第一，    

row_number()只返回一個結果         
      2）.rank()和dense_rank()的區別是：
     --rank()是跳躍排序，有兩個第二名時接下來就是第四名
     --dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名

 
2  顯示各部門員工的工資，並附帶該部門的最高工資。

select deptno,empno,ename,sal,last_value(sal)over(partition by deptno order by sal rows

between unbounded preceding and unbounded following)

max_sal from emp;

 
特色2：
靈活使用decode（）函數：

文法：
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等於if1時，DECODE函數的

結果返回then1,...,如果不等於任何一個if值，則返回else。可以用函數或運算式來替代value，if，

then，else從而作出一些更有用的比較。

來看看具體的運用：
  1 假設我們想給百度職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加

15％
則：

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee

  2 表table_subject，有subject_name列。要求按照：語、數、外的順序進行排序
則：

select * from table_subject order by decode(subject_name, '語文', 1, '數學', 2, , '外語',3)

特色3：
oracle  update的特色：
    
來看個問題先：

       有一表a，列有id 和count；現在有表b，列也是id count，怎麼用把表b中id相對應的count 更

新到表a中呢？

       不嚴格的解法如下：

       Update  a

       Set  count=count+nvl((select count from b where id=a.id),0)

如果a跟b表一對多的話，會有問題,因為，子查詢跟運算子比如等號連用，只能是單值。以後，子查詢

和運算子聯用需要特別在意這個問題：看看子查詢返回的值的個數！！！

       遇到需要從另外一個表來更新本表的值的問題的時候，oracle有兩種解決的辦法:

       其一，使用子查詢，使用子查詢時一定要注意where條件(一般後面接exists子句)，除非兩個表

是一對一關聯性，否則where條件必不可少，遺漏掉where條件時可能會導致插入大量空值（如果不寫where

子句,oracle將會預設的把所有的值全部更新，即使你這裡使用了子查詢並且某值並不能在子查詢裡找到

，你就會想當然的以為,oracle或許將會跳過這些值吧，你錯了，oracle將會把該行的值更新為空白）

update a

set a.count = a.count+(select nvl(sum(count),0) from b where b.id = a.id)

where exists (select 1 from b where b.id = a.id)

       其二，類別檢視的更新方法，這也是oracle所專屬的。先把對應的資料全部抽取出來，然後更新表

一樣更新資料，這裡需要注意的是，必須保證表的資料唯一性（設主鍵來實現）

update (select a.count acount,b.count bcount from a,b where a.id=b.id)

set acount=acount+bcount

 

特色4：
oracle  ROWNUM淺談

 (1) rownum是偽劣，會根據返回記錄自動產生一個序列化的數字。

（2）作用：可以做一些原先難以實現的結果輸出

常見的操作如下：

（3）TOP  N結果輸出：

          Select  *  from  emp  where  rownum < 5

          [對排序結果去top  n  時要注意陷阱]

（4）分頁查詢：

          利用rownum對結果進行分頁，下面返回結果的第6到10條記錄：

           select * from (select e.*,rownum as rn from emp e where rownum<=10)b
           where b.rn>5;

（5）利用rownum作分組子排序

     如果我們希望在分組後對組中的成員的再進行編號，則：

       select decode(ROWNUM-min_sno,0,a.job,NULL)job,decode(ROWNUM-min_sno,0,1,rownum+1-   

                     min_sno) sno,a.ename
       from  (select * from emp order by job,ename) a,
             (select job,min(rownum) min_sno from  (select * from  emp order by            

              job,ename) group by job)              b
       where a.job=b.job

（6）確認某個表是否含資料：
       Select * from emp where rownum=1；

  常見的陷阱如下：由於rownum是偽劣，只有有結果記錄時，rownum才有相應的值。

（7）對rownum使用>(大於1的值),>=（大於或等於1的值）,=（大於1的值），這樣子沒有結果輸出；

    因為：

A ：rownum是偽列，必須要有返回結果後，每條返回記錄就會對應產生一個rownum數值；

B ：返回結果記錄的rownum是從1開始排序的，因此第一條始終是1；

    這樣當查詢到第一條記錄時，該記錄的rownum為1，但條件要求rownum>1,因此不符合，繼續查詢下

一條；因為前面沒有符合要求的記錄，因此下一條記錄過來後，其rownum還是為1，如此迴圈就不會有結

果。

    但可以通過執行個體化來實現對>,>=,=的使用：
  例如：

Select deptno，ename
From （select deptno，ename ，rownum as r From emp）
Where r>5

（8）rownum 和 order by

在使用rownum時，只有當order by 的欄位是主鍵時，查詢結果才會先排序再計算rownum
我們來做個測試：
環境：scott方案下的emp表，先把empno設定為主鍵：
alter table emp add constraint emp_pk_empno primary key (empno);
然後，
當order by的欄位是主鍵時：
[code=SQL][/code]
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by empno;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  1 7369 SMITH
  2 7499 ALLEN
  3 7521 WARD
  4 7566 JONES
  5 7654 MARTIN
查詢結果集先排序再計算rownum。情況很好很正常。
我們再來看一下，當order by 欄位不是主鍵，會怎樣呢？
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by ename;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  2 7499 ALLEN
  4 7566 JONES
  5 7654 MARTIN
  1 7369 SMITH
  3 7521 WARD
對ename排序，結果瞬間就淩亂了哈。

現在，我們來分析一下原因：
oracle會先按物理rowid順序取出滿足rownum條件的記錄，即：物理位置上的前五條記錄。接下來將有兩

種情況：
其一，order by欄位為非主鍵，則只對進行order by排序。
其二，order by欄位為主鍵，則先將結果集進行order by排序，然後計算rownum。

但在工業環境中，不可能一直對主鍵進行排序，那麼，我們可以通過執行個體化來進行非主鍵的排序。
[code=SQL][/code]

SQL> select empno,ename from 
  2 (select empno,ename from c order by ename)
  3 where rownum<=5;
 
EMPNO ENAME
----- ----------
 7876 ADAMS
 7499 ALLEN
 7698 BLAKE
 7782 CLARK
 7902 FORD

特色5：
oracle 常用函數rollup（）和cube（），grouping（）的使用：

        1  rollup（）只作用於第一列：將第一列分成幾個小組，先對各小組小計，再對全部小組總計

。而cube（）先對第一列小計，再對第二列小計，。。。，每一列的處理和rollup一致，最後對全部總

計。所以，要有兩個rollup語句才能頂上一個cube（）。

       例如：

       SQL> select deptno,job,sum(sal) from c group by rollup(deptno,job);

+

       SQL> select deptno,job,sum(sal) from c group by rollup(job,deptno);

=

       SQL> select deptno,job,sum(sal) from c group by cube(deptno,job)；

       2  GROUPING函數可以接受一列，返回0或者1。如果列值為空白，那麼GROUPING()返回1；如果列值

非空，那麼返回0。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。當需要在返回空值的地方顯示某個

值時，GROUPING()就非常有用。可以通過case…when..then..else來增加可讀性。

       SQL> select case grouping(deptno)

            when 0 then '部門'

            when 1 then '部門匯總'

            end , sum(sal)

            from c group by rollup(deptno);

 

下面是幾個比較有用的oracle sql：

1 oracle計算時間差

      Ceil(n):取比n大的最小整數；

      To_date() :時間格式可按需調整，’yyyy-mm-dd hh24:mi:ss’

       mm與mi區別：因為sql不區分大小寫；

       To_date()：兩兩相減後是天數

  毫秒級

select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30  23:59:59' , 'yyyy-    mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒數 

from   dual

         秒級，分鐘級，時級，天級 只要調整24*60*60*1000便可。

2  阿拉伯的英漢對照

select to_char(to_date(n,'yyyy'),'year') from dual；

   輸入：n=2

   輸出：two

3  返回標量值可用dual表測試：

比如：

Select power（3,2） from dual

select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

【sign（n）：取數字n的符號,大於0返回1,小於0返回-1,等於0返回0】

4   獲得一個列的所有行的乘積：

Select power(10, Sum(Log(10, columnName))) From t

5   查詢指定記錄：

例如：

      顯示第5到第10記錄

select a.* from  (select rownum num,e.* from emp e) a 

where a.num >= 5 and a.num <= 10

6   查詢目前使用者某個表中建立了哪些索引：

select index_name from user_indexes where table_name='表名';

    查詢目前使用者的所有表：

select table_name from user_tables;

7   去掉字母保留數字：

   select regexp_replace(v,'[[:alpha:]]','') from b；

8   查看系統參數表：

       Select * from nls_session_parameters;

       若想對其修改：

       Alter session set ………….

我們知道每個RDBMS在sql方面都會存在自己的特色。那麼今天我們來看看oracle有啥特色值得我們來在

意。

特色1 ：
Oracle分析函數與開窗函數：
文法：

    FUNCTION_NAME  (<argument>,<argument>...)
    OVER           (<Partition-Clause><Order-by-Clause><Windowing Clause>)
例如：
   sum(sal) over (partition by deptno order by ename rows between。。。)
   其中，sum是函數名，
         Over（）是關鍵字，直接點就是給分析函數加條件,用於識別sum（）是彙總函式還是分析函數
說明：
（1）通常在做統計分析時我們都想儘可能多滴選擇出原始列和統計值列,但是這樣group by後面就必須

跟隨更多的列,流量分析函數可以避免使用group by時選擇出來的列名必須出現在group by列表中的痛苦

（2）彙總函式用group by分組，每個分組返回一個統計值；分析函數用partition by分組，每組每行都

可以返回一個統計值。

（3）分析函數帶有一個開窗函數over（），含三個分析字句：
           分組（partition by）排序（order by）視窗（rows）

（4）兩個order by的區別：

        分析函數是在整個sql查詢後（sql語句的執行比較特殊）再進行的操作，也就是說，sql語句的

order by也會影響分析函數的執行結果。

            A） 如果sql語句中的order by滿足分析函數分析時要求的排序，那麼sql語句的排序將先

執行，分析函數在分析時就 不必再排序了。

            B） 如果sql語句中的order by不滿足分析函數分析時要求的排序，那麼sql語句中的排序

將先執行。

（5）視窗就是分析函數分析時要處理的資料範圍：

           第一行是：unbounded preceding

           當前行是：current row

           最後一行是：unbounded following

      視窗字句不能單獨出現，必須有order by子句時才能出現。而出現order by子句時，不一定要有

視窗子句，此時的視窗預設是第一行到最後一行；

           當省略視窗子句時：

           A）如果存在order by，則預設的視窗是unbounded preceding
And current row；

           B）如果同時省略order by，則預設的視窗是unbounded preceding and unbounded

following

例子：

1 統計每個部門工資最高的哪位？

select * from                                                                      

   (                                                                           

    select ename,sal,deptno,rank()over(partition by deptno order by sal desc) mm from emp

   )                                                                           

where mm=1

 

注意：
      1）.在求第一名成績的時候，不能用row_number()，因為如果同班有兩個並列第一，    

row_number()只返回一個結果         
      2）.rank()和dense_rank()的區別是：
     --rank()是跳躍排序，有兩個第二名時接下來就是第四名
     --dense_rank()l是連續排序，有兩個第二名時仍然跟著第三名

 
2  顯示各部門員工的工資，並附帶該部門的最高工資。

select deptno,empno,ename,sal,last_value(sal)over(partition by deptno order by sal rows

between unbounded preceding and unbounded following)

max_sal from emp;

 
特色2：
靈活使用decode（）函數：

文法：
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等於if1時，DECODE函數的

結果返回then1,...,如果不等於任何一個if值，則返回else。可以用函數或運算式來替代value，if，

then，else從而作出一些更有用的比較。

來看看具體的運用：
  1 假設我們想給百度職員加工資，其標準是：工資在8000元以下的將加20％；工資在8000元以上的加

15％
則：

select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee

  2 表table_subject，有subject_name列。要求按照：語、數、外的順序進行排序
則：

select * from table_subject order by decode(subject_name, '語文', 1, '數學', 2, , '外語',3)

特色3：
oracle  update的特色：
    
來看個問題先：

       有一表a，列有id 和count；現在有表b，列也是id count，怎麼用把表b中id相對應的count 更

新到表a中呢？

       不嚴格的解法如下：

       Update  a

       Set  count=count+nvl((select count from b where id=a.id),0)

如果a跟b表一對多的話，會有問題,因為，子查詢跟運算子比如等號連用，只能是單值。以後，子查詢

和運算子聯用需要特別在意這個問題：看看子查詢返回的值的個數！！！

       遇到需要從另外一個表來更新本表的值的問題的時候，oracle有兩種解決的辦法:

       其一，使用子查詢，使用子查詢時一定要注意where條件(一般後面接exists子句)，除非兩個表

是一對一關聯性，否則where條件必不可少，遺漏掉where條件時可能會導致插入大量空值（如果不寫where

子句,oracle將會預設的把所有的值全部更新，即使你這裡使用了子查詢並且某值並不能在子查詢裡找到

，你就會想當然的以為,oracle或許將會跳過這些值吧，你錯了，oracle將會把該行的值更新為空白）

update a

set a.count = a.count+(select nvl(sum(count),0) from b where b.id = a.id)

where exists (select 1 from b where b.id = a.id)

       其二，類別檢視的更新方法，這也是oracle所專屬的。先把對應的資料全部抽取出來，然後更新表

一樣更新資料，這裡需要注意的是，必須保證表的資料唯一性（設主鍵來實現）

update (select a.count acount,b.count bcount from a,b where a.id=b.id)

set acount=acount+bcount

 

特色4：
oracle  ROWNUM淺談

 (1) rownum是偽劣，會根據返回記錄自動產生一個序列化的數字。

（2）作用：可以做一些原先難以實現的結果輸出

常見的操作如下：

（3）TOP  N結果輸出：

          Select  *  from  emp  where  rownum < 5

          [對排序結果去top  n  時要注意陷阱]

（4）分頁查詢：

          利用rownum對結果進行分頁，下面返回結果的第6到10條記錄：

           select * from (select e.*,rownum as rn from emp e where rownum<=10)b
           where b.rn>5;

（5）利用rownum作分組子排序

     如果我們希望在分組後對組中的成員的再進行編號，則：

       select decode(ROWNUM-min_sno,0,a.job,NULL)job,decode(ROWNUM-min_sno,0,1,rownum+1-   

                     min_sno) sno,a.ename
       from  (select * from emp order by job,ename) a,
             (select job,min(rownum) min_sno from  (select * from  emp order by            

              job,ename) group by job)              b
       where a.job=b.job

（6）確認某個表是否含資料：
       Select * from emp where rownum=1；

  常見的陷阱如下：由於rownum是偽劣，只有有結果記錄時，rownum才有相應的值。

（7）對rownum使用>(大於1的值),>=（大於或等於1的值）,=（大於1的值），這樣子沒有結果輸出；

    因為：

A ：rownum是偽列，必須要有返回結果後，每條返回記錄就會對應產生一個rownum數值；

B ：返回結果記錄的rownum是從1開始排序的，因此第一條始終是1；

    這樣當查詢到第一條記錄時，該記錄的rownum為1，但條件要求rownum>1,因此不符合，繼續查詢下

一條；因為前面沒有符合要求的記錄，因此下一條記錄過來後，其rownum還是為1，如此迴圈就不會有結

果。

    但可以通過執行個體化來實現對>,>=,=的使用：
  例如：

Select deptno，ename
From （select deptno，ename ，rownum as r From emp）
Where r>5

（8）rownum 和 order by

在使用rownum時，只有當order by 的欄位是主鍵時，查詢結果才會先排序再計算rownum
我們來做個測試：
環境：scott方案下的emp表，先把empno設定為主鍵：
alter table emp add constraint emp_pk_empno primary key (empno);
然後，
當order by的欄位是主鍵時：
[code=SQL][/code]
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by empno;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  1 7369 SMITH
  2 7499 ALLEN
  3 7521 WARD
  4 7566 JONES
  5 7654 MARTIN
查詢結果集先排序再計算rownum。情況很好很正常。
我們再來看一下，當order by 欄位不是主鍵，會怎樣呢？
SQL> select rownum,empno,ename from c
  2 where rownum<=5
  3 order by ename;
 
  ROWNUM EMPNO ENAME
---------- ----- ----------
  2 7499 ALLEN
  4 7566 JONES
  5 7654 MARTIN
  1 7369 SMITH
  3 7521 WARD
對ename排序，結果瞬間就淩亂了哈。

現在，我們來分析一下原因：
oracle會先按物理rowid順序取出滿足rownum條件的記錄，即：物理位置上的前五條記錄。接下來將有兩

種情況：
其一，order by欄位為非主鍵，則只對進行order by排序。
其二，order by欄位為主鍵，則先將結果集進行order by排序，然後計算rownum。

但在工業環境中，不可能一直對主鍵進行排序，那麼，我們可以通過執行個體化來進行非主鍵的排序。
[code=SQL][/code]

SQL> select empno,ename from 
  2 (select empno,ename from c order by ename)
  3 where rownum<=5;
 
EMPNO ENAME
----- ----------
 7876 ADAMS
 7499 ALLEN
 7698 BLAKE
 7782 CLARK
 7902 FORD

特色5：
oracle 常用函數rollup（）和cube（），grouping（）的使用：

        1  rollup（）只作用於第一列：將第一列分成幾個小組，先對各小組小計，再對全部小組總計

。而cube（）先對第一列小計，再對第二列小計，。。。，每一列的處理和rollup一致，最後對全部總

計。所以，要有兩個rollup語句才能頂上一個cube（）。

       例如：

       SQL> select deptno,job,sum(sal) from c group by rollup(deptno,job);

+

       SQL> select deptno,job,sum(sal) from c group by rollup(job,deptno);

=

       SQL> select deptno,job,sum(sal) from c group by cube(deptno,job)；

       2  GROUPING函數可以接受一列，返回0或者1。如果列值為空白，那麼GROUPING()返回1；如果列值

非空，那麼返回0。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。當需要在返回空值的地方顯示某個

值時，GROUPING()就非常有用。可以通過case…when..then..else來增加可讀性。

       SQL> select case grouping(deptno)

            when 0 then '部門'

            when 1 then '部門匯總'

            end , sum(sal)

            from c group by rollup(deptno);

 

下面是幾個比較有用的oracle sql：

1 oracle計算時間差

      Ceil(n):取比n大的最小整數；

      To_date() :時間格式可按需調整，’yyyy-mm-dd hh24:mi:ss’

       mm與mi區別：因為sql不區分大小寫；

       To_date()：兩兩相減後是天數

  毫秒級

select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30  23:59:59' , 'yyyy-    mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒數 

from   dual

         秒級，分鐘級，時級，天級 只要調整24*60*60*1000便可。

2  阿拉伯的英漢對照

select to_char(to_date(n,'yyyy'),'year') from dual；

   輸入：n=2

   輸出：two

3  返回標量值可用dual表測試：

比如：

Select power（3,2） from dual

select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

【sign（n）：取數字n的符號,大於0返回1,小於0返回-1,等於0返回0】

4   獲得一個列的所有行的乘積：

Select power(10, Sum(Log(10, columnName))) From t

5   查詢指定記錄：

例如：

      顯示第5到第10記錄

select a.* from  (select rownum num,e.* from emp e) a 

where a.num >= 5 and a.num <= 10

6   查詢目前使用者某個表中建立了哪些索引：

select index_name from user_indexes where table_name='表名';

    查詢目前使用者的所有表：

select table_name from user_tables;

7   去掉字母保留數字：

   select regexp_replace(v,'[[:alpha:]]','') from b；

8   查看系統參數表：

       Select * from nls_session_parameters;

       若想對其修改：

       Alter session set ………….