熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle通用函數,分組函數,子查詢

2013-11-13 22:13:01  來源: Oracle 

  注意)Oracle中的字符串是嚴格區分大小寫的日期和字符只能在單引號中出現

  )把列與列列與字符連接在一起用 ||符號

  )列的別名緊跟列名也可以在列名和別名之間加入關鍵字AS以便在別名中包含空格或特殊的字符並區分大小寫使用雙引號

  例子

  SELECT    last_name  ||  is a   ||  job_id   AS  Employee Details

  FROM    employees

  where    first_name  like  %s_;

  通用函數

  空值是無效的未指定的未知的或不可預知的值空值不是空格或者 包含空值的數學表達式的值都為空值

  這些函數適用於任何數據類型同時也適用於空值

  NVL (expr expr)

  NVL (expr expr expr)

  NULLIF (expr expr)

  COALESCE (expr expr exprn)

  ()NVL (expr expr) >expr為NULL返回expr不為NULL返回expr注意兩者的類型要一致

  ()NVL (expr expr expr) >expr不為NULL返回expr為NULL返回exprexpr和expr類型不同的話expr會轉換為expr的類型

  ()NULLIF (expr expr) >相等返回NULL不等返回expr

  ()COALESCE(expr expr exprn) >列表中第一個非空的表達式是函數的返回值如果所有的表達式都是空值最終將返回一個空值

  條件表達式在SQL語句中使用IFTHENELSE 邏輯可以使用兩種方法:–CASE表達式    CASE  expr  WHEN  comparison_expr  THEN  return_expr                    [WHEN  comparison_expr  THEN  return_expr                     WHEN  comparison_exprn  THEN  return_exprn                     ELSE  else_expr]    END

  –DECODE函數    DECODE ( col | expression  search  result                [ search result]                [ default] )

  分組函數

  分組函數作用於一組數據並對一組數據返回一個值

  組函數語法

  SELECT [column] group_function(column)

  FROM table

  [WHERE condition]

  [GROUP BY column]

  [ORDER BY column];

  注意在SELECT列表中所有未包含在組函數中的列都應該包含在GROUP BY子句中

  (所用包含於SELECT列表中而未包含於組函數中的列都必須包含於GROUPBY子句中)

  但包含在GROUP BY 子句中的列不必包含在SELECT列表中

  且可以在在GROUP BY子句中包含多個列

  不能在WHERE子句中使用組函數但可以在HAVING子句中使用組函數

  使用HAVING過濾分組

  行已經被分組

  使用了組函數

  滿足HAVING子句中條件的分組將被顯示

  SELECT column group_function

  FROM table

  [WHERE condition]

  [GROUP BY group_by_expression]

  [HAVING group_condition]

  [ORDER BY column];

  子查詢

  語法

  SELECT select_list

  FROM table

  WHERE expr operator

  (SELECT select_list

  FROM table);

  注意子查詢(內查詢) 在主查詢之前一次執行完成

  子查詢的結果被主查詢使用(外查詢)

  子查詢要包含在括號內

  將子查詢放在比較條件的右側

  除非進行TopN 分析否則不要在子查詢中使用ORDER BY子句

  單行操作符對應單行子查詢多行操作符對應多行子查詢

  單行操作符operator                                                    多行操作符operator

  =    等於                                                                            IN     等於列表中的任何一個

  >    大於                                                                            ANY  和子查詢返回的任意一個值比較

  >=  大於等於                                                                    ALL   和子查詢返回的所有值比較

  <    小於                                                                            EXISTS  功能等價於IN

  <=  小於等於

  <>  不等於

  DECODE和CASESELECT中除GROUP BY 子句以外的所有子句中都可以使用單行子查詢

  在子查詢中使用HAVING子句

  SELECT select_list

  FROM table

  [GROUP BY group_by_expression]

  [HAVING group_condition] expr operator

  (SELECT select_list

  FROM table);

  在FROM子句中使用子查詢例子

  SELECT alast_name asalary adepartment_id bsalavg

  FROM employees a (SELECT department_id AVG(salary) salavg

  FROM employees

  GROUP BY department_id) b

  WHERE adepartment_id = bdepartment_id

  AND   asalary > bsalavg;

  有兩個簡單例子以說明 existsin的效率問題

  ) select * from T where exists(select from T where Ta=Ta) ;

  T數據量小而T數據量非常大時T<<T) 的查詢效率高

  ) select * from T where Ta in (select Ta from T) ;

  T數據量非常大而T數據量小時T>>T) 的查詢效率高

  exists 用法

  請注意 )句中的有顏色字體的部分 理解其含義

  其中 select from T where Ta=Ta 相當於一個關聯表查詢相當於

  select from TT     where Ta=Ta

  但是如果你當當執行 ) 句括號裡的語句是會報語法錯誤的這也是使用exists需要注意的地方

  exists(xxx)就表示括號裡的語句能不能查出記錄它要查的記錄是否存在

  因此select 這裡的 其實是無關緊要的換成*也沒問題它只在乎括號裡的數據能不能查找出來是否存在這樣的記錄如果存在) 句的where 條件成立

  in 的用法

  繼續引用上面的例子

  ) select * from T where Ta in (select Ta from T)

  這裡的in後面括號裡的語句搜索出來的字段的內容一定要相對應一般來說T和T這兩個表的a字段表達的意義應該是一樣的否則這樣查沒什麼意義

  打個比方TT表都有一個字段表示工單號但是T表示工單號的字段名叫ticketidT則為id但是其表達的意義是一樣的而且數據格式也是一樣的這時)的寫法就可以這樣

  select * from T where Tticketid in (select Tid from T)

  EXISTS操作符

  EXISTS 操作符檢查在子查詢中是否存在滿足條件的行

   如果在子查詢中存在滿足條件的行

  – 不在子查詢中繼續查找

  – 條件返回TRUE

   如果在子查詢中不存在滿足條件的行

  – 條件返回FALSE

  – 繼續在子查詢中查找

  相關子查詢

  相關子查詢按照一行接一行的順序執行主查詢的每一行都執行一次子查詢子查詢使用了主查詢中的列

  SELECT  column column

  FROM  table outer

  WHERE columnk operator (SELECT  colum colum

  FROM  table

  WHERE expr= outerexpr);

  相關更新

  使用相關子查詢依據一個表中的數據更新另一個表的數據

  UPDATE  table alias

  SET  column = (SELECT  expression

  FROM  table  alias

  WHERE  lumn = lumn);

  相關刪除

  使用相關子查詢依據一個表中的數據刪除另一個表的數據

  DELETE FROM  table alias

  WHERE  column operator (SELECT  expression

  FROM  table alias

  WHERE lumn = lumn);

  WITH子句

   使用WITH子句可以避免在SELECT語句中重復書寫相同的語句塊

   WITH子句將該子句中的語句塊執行一次並存儲到用戶的臨時表空間中

   使用WITH子句可以提高查詢效率

  我們可以使用WITH  Clause來定義一個query   block然後在SELECT   statement的其它地方使用這個query   block如果在一個很復雜的 Query 裡我們必須重復地使用某個 subquery那麼使用WITH   Clause可以降低Query的復雜度以及提高 performance因為WITH   Clause 所讀出的資料會暫存在User的temporary   tablespace中

  WITH子句應用舉例

  WITH

  dept_costs AS (SELECT  ddepartment_name SUM(esalary) AS dept_total

  FROM  employees e departments d

  WHERE  edepartment_id = ddepartment_id

  GROUP BY  ddepartment_name)

  avg_cost  AS (SELECT  SUM(dept_total)/COUNT(*) AS dept_avg

  FROM  dept_costs)

  SELECT  *

  FROM  dept_costs

  WHERE  dept_total > (SELECT  dept_avg

  FROM  avg_cost)

  ORDER BY  department_name;

  GROUP BY 擴展

  帶有ROLLUP和CUBE操作的GROUP BY子句

   使用帶有ROLLUP和CUBE操作的GROUP BY子句產生多種分組結果

   ROLLUP產生n + 種分組結果其是對GROUP BY子句的擴展

   CUBE產生的n次方種分組結果其是對GROUP BY子句的擴展

  注其中的n指的是group_by_expression的數目

  ROLLUP操作符 ROLLUP產生n + 種分組結果順序是從右向左

  SELECT  [column] group_function(column)

  FROM  table

  [WHERE condition]

  [GROUP BY [ROLLUP] group_by_expression]

  [HAVING having_expression]

  [ORDER BY column];

  CUBE操作符CUBE會產生類似於笛卡爾集的分組結果

  SELECT  [column] group_function(column)

  FROM  table

  [WHERE condition]

  [GROUP BY [CUBE] group_by_expression]

  [HAVING having_expression]

  [ORDER BY column];

  GROUPING函數能夠實現更加直觀的分組結果顯示提示

  SELECT  [column] group_function(column) [GROUPING(group_by_expression)]

  FROM  table

  [WHERE condition]

  [GROUP BY  [ROLLUP] [CUBE] group_by_expression]

  [HAVING having_expression]

  [ORDER BY column];

   GROUPING函數可以和CUBE或ROLLUP結合使用

   使用GROUPING函數可以找到哪些列在該行中參加了分組

   使用GROUPING函數 可以區分空值產生的原因

   GROUPING函數返回

  GROUPING SETS

   GROUPING SETS是對GROUP BY子句的進一步擴充

   使用GROUPING SETS可以實現在同一個查詢中定義多個分組集

   Oracle 對GROUPING SETS子句指定的分組集進行分組後用UNION ALL操作將各分組結果結合起來

   Grouping set 的優點

  – 只進行一次分組即可

  – 不必書寫復雜的UNION語句

  – GROUPING SETS中包含的分組項越多性能越好

  以下例子實現了對department_id job_id分組對job_id manager_id分組最終形成兩個分組

  SELECT  department_id job_id manager_id avg(salary)

  FROM  employees

  GROUP BY GROUPING SETS ((department_id job_id) (job_id manager_id));

  復合列

   復合列是被作為整體處理的一組列的集合

   使用括號將若干列組成復合列在ROLLUP或CUBE中作為整體進行操作

   在ROLLUP或CUBE中復合列可以避免產生不必要的分組結果

  例如GROUP BY ROLLUP( department_id (job_id manager_id)); //小括號實現復合列

  連接分組集

   連接分組集可以產生有用的對分組項的結合

   將各分組集ROLLUP 和CUBE用逗號連接Oracle自動在GROUP BY子句中將各分組集進行連接

   連接的結果是對各分組生成笛卡爾集

  例如GROUP BY GROUPING SETS(a b) GROUPING SETS(c d);

  例如GROUP BY department_id ROLLUP(job_id) CUBE(manager_id);


From:http://tw.wingwit.com/Article/program/Oracle/201311/18505.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.