oracle的進階查詢

標籤：employee   express   匹配   自身   esc   insert   資料類型   支援   mgr   

1、簡單串連

基本串連文法：
SELECT [ALL|DISTINCT]column_name[,expression…]
FROM table1_name[,table2_name,view_name,…]
[WHERE condition]
[GROUP BY column_name1[,column_name2,…]
[HAVING group_condition]]
[ORDER BY column_name2 [ASC|DESC]
[,column_name2,…]];

基本串連
查詢emp表中所有員工的編號，姓名，工資，部門號，及部門名稱
方法一：
SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
方法二：
SQL> SELECT empno,ename,sal,e.deptno,dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;

使用JOIN串連查詢
除了使用“，”號分隔需要參與串連操作的表之外，SQL支援另外一種使用關鍵字JOIN的串連。
使用Join串連的文法形式如下：
SELECT [ALL|DISTINCT]column_name[,expression…]
FORM join_table JOIN TYPE join_table
ON join_condition

例如：SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept
ON emp.deptno=dept.deptno;

2、交叉串連：

概念
又稱為“笛卡兒積串連”，是兩個或多個表之間的無條件串連。一個表中所有記錄分別與其他表中所有記錄進行串連。
如果進行串連的表中分別有n1，n2，n3…條記錄，那麼交叉串連的結果集中將有n1×n2×n3×…條記錄。
以下情況可以出現交叉串連
串連條件省略
串連條件非法
一個表的所有行被串連到另一個表的所有行

SQL> SELECT ename,dname from emp,dept;

 

3、內串連：

執行過程
內串連文法
等值內串連
非等值內串連
自身內串連

內串連是根據指定的串連條件進行串連查詢，只有滿足串連
條件的資料才會出現在結果集中。

內串連文法：標準SQL語句的串連方式
SELECT table1.column,talbe2.column[,…]
FROM table1 [INNER] JOIN table2 [JOIN …]
ON condition;
內串連文法：Oracle擴充的串連方式
SELECT table1.column,talbe2.column[,…]
FROM table1,table2[,…]
WHERE condition;

等值內串連

使用等號(=)指定串連條件的串連查詢。
列的名稱可以不同，但是類型必須匹配；如果有相同名稱的列，需在列名前加上表名。

例如，查詢10號部門員工的員工號、員工名、工資、部門號和部門名稱。
SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept
ON emp.deptno=10 AND emp.deptno=dept.deptno;

SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno=10 AND emp.deptno=dept.deptno;

非等值內串連
串連條件中的運算子不是等號而是其他關係運算子。
例如，查詢10號部門員工的工資等級。

SQL> SELECT empno,ename,sal,grade
FROM emp JOIN salgrade
ON sal>losal AND sal<hisal
WHERE deptno=10;

SQL> SELECT empno,ename,sal,grade
FROM emp,salgrade
WHERE sal>losal AND sal<hisal AND deptno=10;

自身內串連
在同一個表或視圖中進行串連，相當於同一個表作為兩個或多個表使用。
例如，查詢所有員工的員工號、員工名和該員工領導的員工名、員工號。

SQL> SELECT worker.empno,worker.ename, manager.empno,
manager.ename
FROM emp worker JOIN emp manager
ON worker.mgr=manager.empno;

SQL> SELECT worker.empno,worker.ename,
manager.empno,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr=manager.empno;
4、外連結：

左外串連
右外串連
全外串連
外串連是在內串連的基礎上，將某個串連表中不符合串連
條件的記錄加入結果集中。

左外串連
指在內串連的基礎上，將串連操作符左側表中不符合串連條件的記錄加入結果集中，與之對應的串連操作符右側表列用NULL填充。
文法：
標準SQL語句的串連方式
SELECT table1.column, table2.column[,…]
FROM table1 LEFT JOIN table2[,]
ON table1.column <operator> table2.column[,…];
Oracle擴充的串連方式
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column <operator>
table2.column(+)[…];

例如，查詢10號部門的部門名、員工號、員工名和所有其他部門的名稱。語句為

SQL> SELECT dname,empno,ename
FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno AND dept.deptno=10;

或
SQL> SELECT dname,empno,ename
FROM dept,emp
WHERE dept.deptno=emp.deptno(+) AND
emp.deptno(+)=10;

說明：外部串連就好象是為符號(+)所在側的表增加一個“萬能”的行，這個
行全部由空值組成。它可以和另一側的表中所有不滿足串連條件的記
錄進行串連，將不合格列全部檢索出來。

例：查詢公司內所有的員工名、部門號和部門名。(說明：dept表中有4行記錄，其中40號部門沒有員工，但是該部門的資訊也必須檢索出來。)

SQL> SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE d.deptno=e.deptno(+);

右外串連
指在內串連的基礎上，將串連操作符右側表中不符合串連條件的記錄加入結果集中，與之對應的串連操作符左側表列用NULL填充。
文法：
標準SQL語句的串連方式
SELECT table1.column, table2.column[,…]
FROM table1 RIGHT JOIN table2[,…]
ON table1.column <operator> table2.column[…];
Oracle擴充的串連方式
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column (+)<operator>
table2.column[…];

例如，查詢20號部門的員工號、員工名及其部門名稱，和所有其他部門的員工號、員工名。

SQL> SELECT empno,ename,dname
FROM dept RIGHT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=20;

SQL> SELECT empno,ename,dname
FROM dept,emp
WHERE dept.deptno(+)=emp.deptno AND
dept.deptno(+)=20;

 

注意：(+)操作符僅適用於左/右外串連，而且如果WHERE子句中包含多個
條件，則必須在所有條件中都包含(+)操作符。

全外串連：指在內串連的基礎上，將串連操作符兩側表中不符合串連條件的記錄加入結果集中。
在Oracle資料庫中，全外串連的表示方式為
SELECT table1.column, table2.column[,…]
FROM table1 FULL JOIN table2[,…]
ON table1.column1 = table2.column2[…];
查詢所有的部門名和員工名，語句為
SQL> SELECT dname,ename
FROM emp FULL JOIN dept
ON emp.deptno=dept.deptno;

5、子查詢

子查詢概述
單行單列子查詢
多行單列子查詢
單行多列子查詢
多行多列子查詢
相互關聯的子查詢
在FROM子句中使用子查詢
在DDL語句中使用子查詢
使用WITH子句的子查詢

概念
子查詢：指嵌套在其他SQL語句中的SELECT語句，也稱為巢狀查詢 。
在執行時，由裡向外，先處理子查詢，再將子查詢的返回結果用於其父語句(外部語句)的執行。
作用
在INSERT或CREATE TABLE語句中使用子查詢，可以將子查詢的結果寫入到目標表中；
在UPDATE語句中使用子查詢可以修改一個或多個記錄的資料；
在DELETE語句中使用子查詢可以刪除一個或多個記錄；
在WHERE和HAVING子句中使用子查詢可以返回的一個或多個值。

注意：在DDL語句中的子查詢可以帶有ORDER BY子句，而在DML語句和DQL語
句中使用子查詢時不能帶有ORDER BY子句。

單行單列子查詢：指子查詢只返回一行資料，而且只返回一列的資料。
運算子
=，>，<，>=，<=，!=
例如，查詢比7934號員工工資高的員工的員工號、員工名、員工工資資訊，語句為

SQL> SELECT empno,ename,sal
FROM emp
WHERE sal>
(SELECT sal FROM emp WHERE empno=7934);

多行單列子查詢：指返回多行資料，且只返回一列的資料。
運算
符號

例：查詢與10號部門某個員工工資相等的員工資訊。
SQL> SELECT empno,ename,sal
FROM emp
WHERE sal IN
(SELECT sal FROM emp WHERE deptno=10);

 

例：查詢比10號部門某個員工工資高的員工資訊。
SQL> SELECT empno,ename,sal
FROM emp
WHERE sal >ANY
(SELECT sal FROM emp WHERE deptno=10);

例：查詢比10號部門所有員工工資高的員工資訊。

SQL> SELECT empno,ename,sal FROM emp
WHERE sal >ALL
(SELECT sal FROM emp WHERE deptno=10);

單行多列子查詢：指子查詢返回一行資料，但是包含多列資料。
多列資料進行比較時，可以成對比較，也可以非成對比較。
成對比較要求多個列的資料必須同時匹配；
非成對比較則不要求多個列的資料同時匹配。

例：查詢與7844號員工的工資、工種都相同的員工的資訊。
SQL> SELECT empno,ename,sal,job FROM emp
WHERE (sal,job)=(SELECT sal,job FROM emp
WHERE empno=7844);

例：查詢與10號部門某個員工工資相同，工種也與10號部門的某個員工相同的員工的資訊。
SQL> SELECT empno,ename,sal,job FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE deptno=10)AND
job IN (SELECT job FROM emp
WHERE deptno=10);

多行多列子查詢：指子查詢返回多行資料，並且是多列資料。
例如，查詢與10號部門某個員工的工資和工種都相同的員工的資訊，語句為
SQL> SELECT empno,ename,sal,job FROM emp
WHERE (sal,job) IN (
SELECT sal,job FROM emp WHERE deptno=10);

子查詢在執行時並不需要外部父查詢的資訊，這種子查詢稱為無關子查詢。
如果子查詢在執行時需要引用外部父查詢的資訊，那麼這種子查詢就稱為相互關聯的子查詢。
無關子查詢與相互關聯的子查詢的區別：
無關子查詢中，內部的SELECT查詢首先執行並且只執行一次，傳回值被主查詢使用。
相互關聯的子查詢中，對由外查詢考慮的每個候選行都執行一次，換句話說，內查詢由外查詢驅動。

相互關聯的子查詢的執行：
取得候選行(由外查詢取回)；
用候選行的值執行內查詢；
用來自內查詢的值確認或取消候選行；
重複前三步直到無剩餘的候選行。
當一個子查詢必須對每個由主查詢考慮的候選行返回一個不同的結果或結果集時，可以使用相互關聯的子查詢。
在相互關聯的子查詢中經常使用EXISTS或NOT EXISTS謂詞來實現。如果子查詢返回結果，則條件為TRUE，如果子查詢沒有返回結果，則條件為FALSE。也可以使用ANY和ALL進行運算。

 

例：查詢至少變換過兩次工作的員工資訊。
SQL> SELECT e.employee_id, last_name, e.job_id
FROM employees e
WHERE 2<=(SELECT count(*) FROM job_history
WHERE employee_id=e.employee_id);

例：查詢沒有任何員工的部門號、部門名。

SQL> SELECT deptno,dname,loc FROM dept
WHERE NOT EXISTS(SELECT * FROM emp
WHERE emp.deptno=dept.deptno);

說明：EXISTS和NOT EXISTS操作經常用於相互關聯的子查詢來測試是否一個由外查
詢取回的值存在或不存在於由內查詢取回的值的結果集中。

例如，查詢至少有一個僱員的經理資訊。
SQL> SELECT empno,ename,job,deptno FROM emp e
WHERE EXISTS(SELECT ‘X‘ FROM emp
WHERE mgr=e.empno);

SQL> SELECT empno,ename,job,deptno FROM emp e
WHERE empno IN (SELECT mgr FROM emp
WHERE mgr IS NOT NULL);
例：查詢比本部門平均工資高的員工資訊。
SQL> SELECT empno,ename,sal
FROM emp e
WHERE sal>(SELECT avg(sal) FROM emp
WHERE deptno=e.deptno); 、

當在FROM子句中使用子查詢時，該子查詢被作為視圖對待，必須為該子查詢指定別名。
例：查詢各個員工的員工號、員工名及其所在部門平均工資。
SQL> SELECT empno,ename,d.avgsal
FROM emp,(SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno) d
WHERE emp.deptno=d.deptno;

 

例：查詢各個部門號、部門名、部門人數及部門平均工資。
SQL> SELECT dept.deptno,dname, d.amount,d.avgsal
FROM dept,(SELECT deptno,count(*)amount,
avg(sal) avgsal
FROM emp
GROUP BY deptno)d
WHERE dept.deptno=d.deptno;

可以在CREATE TABLE和CREATE VIEW語句中使用子查詢來建立表和視圖。

CREATE TABLE emp_subquery
AS
SELECT empno,ename,sal FROM emp;

CREATE VIEW emp_view_subquery
AS
SELECT * FROM emp WHERE sal>2000;

如果在一個SQL語句中多次使用同一個子查詢，可以通過WITH子句給子查詢指定一個名字，從而可以實現通過名字引用該子查詢，而不必每次都完整寫出該子查詢。
查詢人數最多的部門的資訊。

SQL> SELECT * FROM dept
WHERE deptno IN (
SELECT deptno FROM emp GROUP BY deptno
HAVING count(*)>=ALL(
SELECT count(*) FROM emp GROUP BY deptno)
);

相同的子查詢連續出現了兩次，因此可以按下列方式編寫查詢語句。

SQL> WITH deptinfo AS(SELECT deptno,count(*) num
FROM emp GROUP BY deptno)
SELECT * FROM dept
WHERE deptno IN(SELECT deptno
FROM deptinfo
WHERE num=(SELECT max(num)
FROM deptinfo)
);

層次查詢(hierarchical_query)，又稱樹形查詢，能夠將一個表中的資料按照記錄之間的聯絡以樹狀結構的形式顯示出來。
層次查詢的文法為
SELECT [LEVEL], column[,expression...]
FROM table
[WHERE condition]
[START WITH column = value]
[CONNECT BY condition];

參數說明
LEVEL：偽列，表示記錄的層次，根行的LEVEL返回1，其孩子LEVEL返回2，依此類推；
WHERE：記錄(節點)選擇條件；
START WITH：層次查詢的起始記錄(起始節點)
CONNECT BY：指定父記錄與子記錄之間的關係及分支選擇條件。必須使用PRIOR引用父記錄，形式為
PRIOR column1=column2
或
column1=PRIOR column2。
層次查詢應遵循以下限制：
SELECT 語句不能包含串連 (JOIN) 或者，查詢來自一個包含串連的視圖。
如果在SELECT層次查詢語句中有ORDER BY從句，Oracle將以ORDER BY的次序重新給選出的層次資料排序。
CONNECT BY子句中不能包含子查詢。

層次查詢的執行步驟：(Oracle伺服器)
⑴將表中滿足START WITH從句中條件的行選取出來，將其作為層次資料的根資料；
⑵依據CONNECT BY子句中的條件，選擇每一個根行的子行；
⑶選擇子行的後續子行。Oracle先選擇⑵中提到的子行，將其作為根行，選擇出它們的子行；然後再將這些子行作為根行，再選擇這些行的子行，以此遞推。
⑷如果層次查詢語句中包含WHERE語句，Oracle將從層次資料(結果資料)中移去所有的不滿足WHERE條件子句的行。
⑸以特定的次序返回資料行，用以保證子行顯示在父行之下的次序不亂。

集合：
文法：
SELECT query_statement1
[UNION|UNION ALL|INTERSECT|MINUS]
SELECT query_statement2;
注意：
當要合并幾個查詢的結果集時，這幾個查詢的結果集必須具有相同的列數與資料類型。
如果要對最終的結果集排序，只能在最後一個查詢之後用ORDER BY子句指明排序列。

UNION運算子用於擷取幾個查詢結果集的並集，將重複的記錄只保留一個，並且預設按第一列進行排序。
例如，查詢10號部門的員工號、員工名、工資和部門號以及工資大於2000的所有員工的員工號、員工名、工資和部門號。語句為

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10
UNION
SELECT empno,ename,sal,deptno
FROM emp WHERE sal>2000
ORDER BY deptno;

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10 OR sal>2000;

如果要保留所有的重複記錄，則需要使用UNION ALL運算子。例如：
SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10
UNION ALL
SELECT empno,ename,sal,deptno
FROM emp WHERE sal>2000
ORDER BY deptno;
INTERSECT用於擷取幾個查詢結果集的交集，只返回同時存在於幾個查詢結果集中的記錄。同時，返回的最終結果集預設按第一列進行排序。
例如，查詢30號部門中工資大於2000的員工號、員工名、工資和部門號，語句為：
SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=30
INTERSECT
SELECT empno,ename,sal,deptno
FROM EMP WHERE sal>2000;

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=30 AND sal>2000;

MINUS用於擷取幾個查詢結果集的差集，即返回在第一個結果集中存在，而在第二個結果集中不存在的記錄。同時，返回的最終結果集預設按第一列進行排序。
例如，查詢30號部門中工種不是“SALESMAN”的員工號、員工名和工種名稱。語句為：
SQL> SELECT empno,ename,job
FROM emp WHERE deptno=30
MINUS
SELECT empno,ename,job
FROM EMP WHERE job=‘SALESMAN‘;

oracle的進階查詢