注意）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;
有兩個簡單例子以說明 exists和in的效率問題
) 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
