Oracle學習（6）：子查詢

Oracle學習（6）：子查詢

子查詢

為何要用子查詢

SQL> --查詢工資比SCOTT高的員工信息
SQL> --1. 知道SCOTT的工資
SQL> select sal from emp where ename='SCOTT';


SAL
----------
3000


SQL> --2. 查詢比3000高的員工
SQL> set linesize 120
SQL> col sal for 9999
SQL> select * from emp where sal>3000;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10


SQL> --子查詢解決問題：不能一步求解時，考慮使用子查詢
SQL> select *
2 from emp
3 where sal> (select sal
4 from emp
5 where ename='SCOTT');


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10

子查詢注意的問題

SQL> /*
SQL> 注意的問題：
SQL> 1. 子查詢應該在括號中
SQL> 2. 子查詢相對主查詢采用縮進
SQL> 3. 可以在主查詢的where from select having後，都可以放子查詢
SQL> 4. 在主查詢的group by後面，不能放置子查詢
SQL> 5. 強調在from後面放置子查詢
SQL> 6. 主查詢和子查詢可以不是同一張表，只要子查詢返回的結果，主查詢可以使用，即可
SQL> 7. 一般來講，不在子查詢中使用 order by;但在Top-N分析中，必須使用order by
SQL> 8. 單行子查詢必須使用單行操作符；多行子查詢必須使用多行操作符
SQL> 9. 注意：子查詢中的空值

SQL> */

子查詢 (內查詢) 在主查詢之前一次執行完成。 子查詢的結果被主查詢使用 (外查詢)。

子查詢要包含在括號內。 將子查詢放在比較條件的右側。 單行操作符對應單行子查詢，多行操作符對應多行子查詢。

子查詢放置的位置

SQL> -- 3. 可以在主查詢的where from select having後，都可以放子查詢
SQL> select *
2 from (select ename,sal from emp);


ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100


ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300


已選擇14行。


SQL> ed
已寫入 file afiedt.buf


1 select e.*
2* from (select ename,sal from emp) e
SQL> /


ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100


ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300


已選擇14行。



SQL> ---select後放子查詢:必須是單行子查詢
SQL> select ename,sal,(select job from emp where empno=7839)
2 from emp;


ENAME SAL (SELECTJO
---------- ----- ---------
SMITH 800 PRESIDENT
ALLEN 1600 PRESIDENT
WARD 1250 PRESIDENT
JONES 2975 PRESIDENT
MARTIN 1250 PRESIDENT
BLAKE 2850 PRESIDENT
CLARK 2450 PRESIDENT
SCOTT 3000 PRESIDENT
KING 5000 PRESIDENT
TURNER 1500 PRESIDENT
ADAMS 1100 PRESIDENT


ENAME SAL (SELECTJO
---------- ----- ---------
JAMES 950 PRESIDENT
FORD 3000 PRESIDENT
MILLER 1300 PRESIDENT


已選擇14行。

主查詢和子查詢是否同表

SQL> --6. 主查詢和子查詢可以不是同一張表，只要子查詢返回的結果，主查詢可以使用，即可
SQL> select ename,sal,(select dname from dept where deptno=10)
2 from emp;


ENAME SAL (SELECTDNAMEFR
---------- ----- --------------
SMITH 800 ACCOUNTING
ALLEN 1600 ACCOUNTING
WARD 1250 ACCOUNTING
JONES 2975 ACCOUNTING
MARTIN 1250 ACCOUNTING
BLAKE 2850 ACCOUNTING
CLARK 2450 ACCOUNTING
SCOTT 3000 ACCOUNTING
KING 5000 ACCOUNTING
TURNER 1500 ACCOUNTING
ADAMS 1100 ACCOUNTING


ENAME SAL (SELECTDNAMEFR
---------- ----- --------------
JAMES 950 ACCOUNTING
FORD 3000 ACCOUNTING
MILLER 1300 ACCOUNTING


已選擇14行。

子查詢與多表查詢對比

SQL> host cls


SQL> --查詢部門名稱為SALES的員工信息
SQL> --子查詢

1 select *
2 from emp
3 where deptno=(select deptno
4 from dept
5* where dname='SALES')
SQL> /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30


已選擇6行。


SQL> --多表查詢
SQL> select e.*
2 from dept d,emp e
3 where d.dname='SALES' and d.deptno=e.deptno;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30


已選擇6行。

一個數據庫優化問題（子查詢與多表查詢）

SQL> --如果子查詢和多表查詢均可，一般采用多表查詢 （子查詢要查詢兩次數據庫，多表查詢查詢一次）

單行子查詢

子查詢結果只返回一行
使用單行比較操作符



查詢示例：

SELECTlast_name,job_id<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vc3Ryb25nPjxzdHJvbmc+LHNhbGFyeTwvc3Ryb25nPjwvcD4KPHA+CjxzdHJvbmc+RlJPTSAgIGVtcGxveWVlczwvc3Ryb25nPjwvcD4KPHA+CjxzdHJvbmc+V0hFUkUgCjwvc3Ryb25nPjxzdHJvbmc+am9iX2lkPC9zdHJvbmc+PHN0cm9uZz49IAo8L3N0cm9uZz48L3A+CjxwPgo8c3Ryb25nPiAgICAgICAgICAgICAgICAoU0VMRUNUCjwvc3Ryb25nPjxzdHJvbmc+am9iX2lkPC9zdHJvbmc+PC9wPgo8cD4KPHN0cm9uZz4gICAgICAgICAgICAgICAgIEZST00gICBlbXBsb3llZXM8L3N0cm9uZz48L3A+CjxwPgo8c3Ryb25nPiAgICAgICAgICAgICAgICAgV0hFUkUgCjwvc3Ryb25nPjxzdHJvbmc+ZW1wbG95ZWVfaWQ8L3N0cm9uZz48c3Ryb25nPj0gMTQxKTwvc3Ryb25nPjwvcD4KPHA+CjxzdHJvbmc+QU5EICAgIHNhbGFyeSAmZ3Q7PC9zdHJvbmc+PC9wPgo8cD4KPHN0cm9uZz4gICAgICAgICAgICAgICAgKFNFTEVDVCBzYWxhcnk8L3N0cm9uZz48L3A+CjxwPgo8c3Ryb25nPiAgICAgICAgICAgICAgICAgRlJPTSAgIGVtcGxveWVlczwvc3Ryb25nPjwvcD4KPHA+CjxzdHJvbmc+ICAgICAgICAgICAgICAgICBXSEVSRSAKPC9zdHJvbmc+PHN0cm9uZz5lbXBsb3llZV9pZDwvc3Ryb25nPjxzdHJvbmc+PSAxNDMpOzwvc3Ryb25nPjwvcD4KPGJyPgoKPGgyPrbg0NDX07Lp0a88L2gyPgo8YnI+Cgo8aDM+aW7T625vdCBpbjwvaDM+ClNRTCZndDsgLS224NDQ19Oy6dGvPGJyPgpTUUwmZ3Q7IC0taW46ICDU2ryvus/W0Dxicj4KU1FMJmd0OyAtLbLp0a+yv8PFw/uzxs6qU0FMRVO6zUFDQ09VTlRJTke1xNSxuaTQxc+iPGJyPgpTUUwmZ3Q7IC0twbfPsKO6tuCx7bLp0a88YnI+ClNRTCZndDsgc2VsZWN0ICo8YnI+CiAgMiAgZnJvbSBlbXA8YnI+CiAgMyAgd2hlcmUgZGVwdG5vIGluICAgKHNlbGVjdCBkZXB0bm8gZnJvbSBkZXB0IHdoZXJlIGRuYW1lPQ=="SALES' or dname='ACCOUNTING');


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10


已選擇9行。


SQL> ed
已寫入 file afiedt.buf


1 select *
2 from emp
3* where deptno not in (select deptno from dept where dname='SALES' or dname='ACCOUNTING')
SQL> /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20


SQL> host cls

any與all查詢

any

SQL> --any: 集合中的任意一個
SQL> --查詢工資比20號部門任意員工工資高的員工信息
SQL> select *
2 from emp
3 where sal > any (select sal from emp where deptno=20);


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30


已選擇13行。


SQL> ed
已寫入 file afiedt.buf


1 select *
2 from emp
3* where sal > any (select sal from emp where deptno=10)
SQL> /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30


已選擇8行。

all

SQL> --all:和集合的所有值比
SQL> --查詢工資比20號部門所有員工工資高的員工信息
SQL> select *
2 from emp\
3 ;
from emp\
*
第 2 行出現錯誤:
ORA-00911: 無效字符




SQL> select *
2 from emp
3 where sal > all (select sal from emp where deptno=20);


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10


SQL> select *
2 from emp
3 where sal > (select min(sal) from emp where deptno=10);


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7902 FORD ANALYST 7566 03-12月-81 3000 20


已選擇8行。


SQL> ed
已寫入 file afiedt.buf


1 select *
2 from emp
3* where sal > (select max(sal) from emp where deptno=20)
SQL> /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10


SQL> host cls


SQL> --查詢不是經理的員工
SQL> select * from emp;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10


已選擇14行。

關於not in與in後面的空值問題

not in相當於all的范圍，即非集合中的所有，所以集合中出現null時，返回空，即結果集為空集！

in相當於any的范圍，即在集合中的任意一個元素符合即可，所以即使集合中出現null，也不妨礙結果集！

SQL> select *
2 from emp
3 where empno not in (select mgr from emp);


未選定行


SQL> --查詢是經理的員工
SQL> ed
已寫入 file afiedt.buf


1 select *
2 from emp
3* where empno in (select mgr from emp)
SQL> /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3000 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7839 KING PRESIDENT 17-11月-81 5000 10
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10


已選擇6行。


SQL> select *
2 from emp
3 where empno not in (select mgr from emp where mgr is not null);


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30


已選擇8行。


SQL> spool off