--=========================

--SQL 基礎 --> 子查詢

--========================= ??

?

/*

一、子查詢

??? 子查詢就是位于 SELECT 、 UPDATE 、或 DELETE 語句中內部的查詢

???

二、子查詢的分類

??? 單行子查詢

??????? 返回零行或一行

??? 多行子查詢

??????? 返回一行或多行

??? 多列子查詢

??????? 返回多列

??? 相關子查詢

??????? 引用外部 SQL 語句中的一列或多列

??? 嵌套子查詢

??????? 位于其它子查詢中的查詢

?

三、子查詢語法 */

??? SELECT select_list

??? FROM table

??? WHERE expr operator

?????? ( SELECT select_list

?????? ? FROM table );

?????? ? /*

??? 子查詢 ( 內部查詢 ) 在執行主查詢之前執行一次

??? 然后主查詢 ( 外部查詢 ) 會使用該子查詢的結果 ?

?

四、子查詢的規則

??? 將子查詢括在括號中

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

??? 只有在執行排序 Top-N 分析時，子查詢中才需要使用 ORDER BY 子句

??? 單行運算符用于單行子查詢，多行運算符用于多行子查詢 ?

?

五、單行子查詢

??? 僅返回一行

??? 使用單行的表較運算符： = ,>, >= ,< , <= ,<>

???

??? 在 WHERE 子句中使用子查詢 ??? */

??? SQL > select ename , job from emp

??? ? 2 ? where empno = (

??? ? 3 ??? select empno from emp

??? ? 4 ??? where mgr = 7902 );

?

??? ENAME ????? JOB

??? ---------- ---------

??? SMITH ????? CLERK

???

??? -- 使用分組函數的子查詢

??? SQL > select ename , job , sal

??? ? 2 ? from emp

??? ? 3 ? where sal >

??? ? 4 ??? ( select avg ( sal ) from emp );

?

??? ENAME ????? JOB ????????????? SAL

??? ---------- --------- ----------

??? JONES ????? MANAGER ???????? 2975

??? BLAKE ????? MANAGER ???????? 2850

??? CLARK ????? MANAGER ???????? 2450

??? SCOTT ????? ANALYST ???????? 3000

??? KING ?????? PRESIDENT ?????? 5000

??? FORD ?????? ANALYST ???????? 3000

???

??? -- 在 HAVING 子句中使用子查詢

??? SQL > select deptno , min ( sal )

??? ? 2 ? from emp

??? ? 3 ? group by deptno

??? ? 4 ? having min ( sal ) >

??? ? 5 ????? ( select min ( sal )

??? ? 6 ?????? from emp

??? ? 7 ?????? where deptno = 20 );

?

?????? DEPTNO ?? MIN ( SAL )

??? ---------- ----------

?????????? 30 ??????? 950

?????????? 10 ?????? 1300

??????????

??? -- 在 FROM 子句中使用子查詢

??? SQL > select empno , ename

??? ? 2 ? from

??? ? 3 ????? ( select empno , ename

??? ? 4 ?????? from emp

??? ? 5 ?????? where deptno = 20 );

?

?????? ? EMPNO ENAME

??? ---------- ----------

?????? ? 7369 SMITH

?????? ? 7566 JONES

?????? ? 7788 SCOTT

?????? ? 7876 ADAMS

?????? ? 7902 FORD

???

??? -- 單行子查詢中的常見錯誤

??? ??? -- 子查詢的結果返回多于一行

?????? SQL > select empno , ename

?????? ? 2 ? from emp

?????? ? 3 ? where sal =

?????? ? 4 ????? ( select sal ?????

?????? ? 5 ?????? from emp

?????? ? 6 ?????? where deptno = 20 );

?????????? ( select sal

?????????? ? *

?????? ERROR at line 4 :

?????? ORA - 01427 : single - row subquery returns more than one row

??????

?????? -- 子查詢中不能包含 ORDER BY 子句

?????? SQL > select empno , ename

?????? ? 2 ? from emp

?????? ? 3 ? where sal >

?????? ? 4 ????? ( select avg ( sal )

?????? ? 5 ?????? from emp

?????? ? 6 ?????? order by empno );

?????????? ? order by empno )

?????????? ? *

?????? ERROR at line 6 :

?????? ORA - 00907 : missing right parenthesis

??????

?????? -- 子查詢內部沒有返回行，如下語句可以正確執行，但沒有數據返回

?????? SQL > select ename , job

?????? ? 2 ? from emp

?????? ? 3 ? where empno =

?????? ? 4 ????? ( select empno

?????? ? 5 ?????? from emp

?????? ? 6 ?????? where mgr = 8000 );

?

?????? no rows selected

?

??? /* ?

六、多行子查詢

??? 返回多個行

??? 使用多行比較運算符 IN ,ANY ,ALL

???

??? 在多行子查詢中使用 IN 操作符 ? */

??? SQL > select empno , ename , job

??? ? 2 ? from emp

??? ? 3 ? where sal in

??? ? 4 ????? ( select max ( sal )

??? ? 5 ?????? from emp

??? ? 6 ?????? group by deptno );

?

?????? ? EMPNO ENAME ????? JOB

??? ---------- ---------- ---------

?????? ? 7698 BLAKE ????? MANAGER

?????? ? 7902 FORD ?????? ANALYST

?????? ? 7788 SCOTT ????? ANALYST

?????? ? 7839 KING ?????? PRESIDENT

?????? ?

??? -- 在多行子查詢中使用 ANY 操作符

??? SQL > select empno , ename , job

??? ? 2 ? from emp

??? ? 3 ? where sal < any

??? ? 4 ????? ( select avg ( sal )

??? ? 5 ?????? from emp

??? ? 6 ?????? group by deptno );

?

?????? ? EMPNO ENAME ????? JOB

??? ---------- ---------- ---------

??? ??? ? 7369 SMITH ????? CLERK

?????? ? 7900 JAMES ????? CLERK

?????? ? 7876 ADAMS ????? CLERK

?????? ? 7521 WARD ?????? SALESMAN

?????? ? 7654 MARTIN ???? SALESMAN

?????? ? 7934 MILLER ???? CLERK

?????? ? 7844 TURNER ???? SALESMAN

?????? ? 7499 ALLEN ????? SALESMAN

?????? ? 7782 CLARK ????? MANAGER

?????? ? 7698 BLAKE ????? MANAGER

?????? ?

??? -- 在多行子查詢中使用 ALL 操作符

??? SQL > select empno , ename , job

??? ? 2 ? from emp

??? ? 3 ? where sal > all

??? ? 4 ????? ( select avg ( sal )

??? ? 5 ?????? from emp

??? ? 6 * ????? group by deptno )

???

?????? ? EMPNO ENAME ????? JOB

??? ---------- ---------- ---------

?????? ? 7566 JONES ????? MANAGER

?????? ? 7788 SCOTT ????? ANALYST

?????? ? 7839 KING ?????? PRESIDENT

?????? ? 7902 FORD ?????? ANALYST

???

???? /*

七、相關子查詢

??? 子查詢中使用了主查詢中的某些字段，主查詢每掃描一行都要執行一次子查詢 */

?

??? -- 查詢工資高于同一部門的員工的部門號，姓名，工資

??? SQL > select deptno , ename , sal

??? ? 2 ? from emp outer

??? ? 3 ? where sal >

??? ? 4 ????? ( select avg ( sal )

??? ? 5 ?????? from emp inner

??? ? 6 ?????? where inner. deptno = outer. deptno );

?

?????? DEPTNO ENAME ???????????? SAL

??? ---------- ---------- ----------

?????????? 30 ALLEN ??????????? 1600

?????????? 20 JONES ??????????? 2975

?????????? 30 BLAKE ??????????? 2850

?????????? 20 SCOTT ??????????? 3000

?????????? 10 KING ???????????? 5000

?????????? 20 FORD ???????????? 3000

??????????

??? -- 查詢負責管理其它員工的員工記錄 ( 使用 exists)

??? SQL > select empno , ename

??? ? 2 ? from emp outer

??? ? 3 ? where exists

??? ? 4 ???? ( select empno

??? ? 5 ????? from emp inner

??? ? 6 ????? where inner. mgr = outer. empno );

?

?????? ? EMPNO ENAME

??? ---------- ----------

?????? ? 7566 JONES

?????? ? 7698 BLAKE

?????? ? 7782 CLARK

?????? ? 7788 SCOTT

?????? ? 7839 KING

?????? ? 7902 FORD

??? -- 查詢不管理其它員工的職員 (not exists)

??? SQL > l3

??? ? 3 * where exists

??? SQL > c / where / where not

??? ? 3 * where not exists

??? SQL > l

??? ? 1 ? select empno , ename

??? ? 2 ? from emp outer

??? ? 3 ? where not exists

??? ? 4 ???? ( select empno

??? ? 5 ????? from emp inner

??? ? 6 * ???? where inner. mgr = outer. empno )

??? SQL > /

?

?????? ? EMPNO ENAME

??? ---------- ----------

?????? ? 7369 SMITH

?????? ? 7499 ALLEN

?????? ? 7521 WARD

?????? ? 7654 MARTIN

?????? ? 7844 TURNER

?????? ? 7876 ADAMS

?????? ? 7900 JAMES

?????? ? 7934 MILLER

?????? ?

??? EXISTS 和 NOT EXISTS 與 IN 和 NOT IN 的比較

??????? EXISTS 與 IN 的不同：

??????????? EXISTS 只檢查行的存在性 ,IN 要檢查實際值的存在性 ( 一般情況下 EXISTS 的性能高于 IN)

??? ??? NOT EXISTS 和 NOT IN

?????? ??? 當值列表中包含空值的情況下 ,NOT EXISTS 則返回 true , 而 NOT IN 則返回 false .

??????????

??? -- 看下面的查詢，查詢部門號不在 emp 表中出現的部門名稱及位置

??? SQL > select deptno , dname , loc

??? ? 2 ? from dept d

??? ? 3 ? where not exists

??? ? 4 ????? ( select 1

??? ? 5 ?????? from emp e

??? ? 6 * ????? where e . deptno = d . deptno )

??? ?

?????? DEPTNO DNAME ????????? LOC

??? ---------- -------------- -------------

?????????? 40 OPERATIONS ???? BOSTON

??? ??

??? --IN 與空值

?????????? SQL > SELECT *

???????????? 2 ??? FROM emp e

???????????? 3 ??? WHERE e . empno NOT IN (

???????????? 4 ?????????????????????????? SELECT 7369 FROM dual

???????????? 5 ?????????????????????????? UNION ALL

???????????? 6 ?????????????????????????? SELECT NULL FROM dual

???????????? 7 ????????????????????????? )

???????????? 8 ? ;

???????????

?????????? EMPNO ENAME ????? JOB ???????? MGR HIREDATE ????????? SAL ????? COMM DEPTNO

?????????? ----- ---------- --------- ----- ----------- --------- --------- ------

???????????

?????????? SQL > SELECT *

???????????? 2 ??? FROM emp e

???????????? 3 ??? WHERE e . empno IN ( '7369' ,NULL)

???????????? 4 ? ;

???????????

?????????? EMPNO ENAME ????? JOB ???????? MGR HIREDATE ????????? SAL ????? COMM DEPTNO

?????????? ----- ---------- --------- ----- ----------- --------- --------- ------

??????????? 7369 SMITH ????? CLERK ????? 7902 1980 - 12 - 17 ???? 800.00 ?????????????? 20

???????????

????

??? /* ?

? ? 注：子查詢要包含在括號內

??? ??? 子查詢一般放在比較條件的右側

??? ??? 除非進行 TOP － N 分析，否則不要在子查詢中使用 ORDER BY 。 */

?

??? /*

八、多列子查詢

??? 1 、成對比較

??? 查詢工資為部門最高的記錄 */

??? SQL > select * from scott . emp

??? ? 2 ? where ( sal , job ) in

??? ? 3 ???? ( select max ( sal ), job from scott . emp group by job );

???

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 7934 MILLER ???? CLERK ?????????? 7782 23 - JAN - 82 ?????? 1300 ??????????????????? 10

?????? ? 7499 ALLEN ????? SALESMAN ??????? 7698 20 - FEB - 81 ?????? 1600 ??????? 300 ???????? 30

?????? ? 7839 KING ?????? PRESIDENT ??????????? 17 - NOV - 81 ?????? 5000 ??????????????????? 10

?????? ? 7566 JONES ????? MANAGER ???????? 7839 02 - APR - 81 ?????? 2975 ??????????????????? 20

?????? ? 7902 FORD ?????? ANALYST ???????? 7566 03 - DEC - 81 ?????? 3000 ??????????????????? 20

?????? ? 7788 SCOTT ????? ANALYST ???????? 7566 19 - APR - 87 ?????? 3000 ??????????????????? 20 ???

??????

?????? /* ??????

??? 2 、非成對比較 , 實現了與上述類似的功能 */

??? SQL > select * from scott . emp

??? ? 2 ? where sal in ( select max ( sal ) from scott . emp group by job )

??? ? 3 ? and job in ( select distinct job from scott . emp );

?

?????? ? EMPNO ENAME ????? JOB ????????????? MGR HIREDATE ???????? SAL ?????? COMM ???? DEPTNO

??? ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

?????? ? 7934 MILLER ???? CLERK ?????????? 7782 23 - JAN - 82 ?????? 1300 ??????????????????? 10

?????? ? 7499 ALLEN ????? SALESMAN ??????? 7698 20 - FEB - 81 ?????? 1600 ??????? 300 ???????? 30

?????? ? 7566 JONES ????? MANAGER ???????? 7839 02 - APR - 81 ?????? 2975 ??????????????????? 20

?????? ? 7788 SCOTT ????? ANALYST ???????? 7566 19 - APR - 87 ?????? 3000 ??????????????????? 20

?????? ? 7902 FORD ?????? ANALYST ???????? 7566 03 - DEC - 81 ?????? 3000 ??????????????????? 20

?????? ? 7839 KING ?????? PRESIDENT ??????????? 17 - NOV - 81 ?????? 5000 ??????????????????? 10

?????? ?

???? /*

九、嵌套子查詢

??? 即位于子查詢內部的子查詢，嵌套層數最多可達層。然而應盡量避免使用嵌套子查詢，使用表連接的查詢性能會更高 */

??? SQL > select deptno , Num_emp

??? ? 2 ? from ( select deptno , count ( empno ) as Num_emp from emp group by deptno ) d

??? ? 3 ? where Num_emp > 3 ;

?

?????? DEPTNO ??? NUM_EMP

??? ---------- ----------

?????????? 30 ????????? 6

?????????? 20 ????????? 5

??????

??? /* ?????

??? 注意：子查詢對空值的處理

??? 除了 count(*) 外，都會忽略掉空值 */

?

??? /*

十、更多 */ ??

Oracle 數據庫實例啟動關閉過程

?

Oracle 10g SGA 的自動化管理

?

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實例

?

Oracle 實例和 Oracle 數據庫 (Oracle 體系結構 )

?

SQL 基礎 --> 常用函數

?

SQL 基礎 --> 過濾和排序

?

SQL 基礎 -->SELECT 查詢

?

?

?

???

?

?

???

?

??

SQL 基礎--> 子查詢