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

--SQL 基礎(chǔ) --> 子查詢

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

?

/*

一、子查詢

??? 子查詢就是位于 SELECT 、 UPDATE 、或 DELETE 語句中內(nèi)部的查詢

???

二、子查詢的分類

??? 單行子查詢

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

??? 多行子查詢

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

??? 多列子查詢

??????? 返回多列

??? 相關(guān)子查詢

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

??? 嵌套子查詢

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

?

三、子查詢語法 */

??? SELECT select_list

??? FROM table

??? WHERE expr operator

?????? ( SELECT select_list

?????? ? FROM table );

?????? ? /*

??? 子查詢 ( 內(nèi)部查詢 ) 在執(zhí)行主查詢之前執(zhí)行一次

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

?

四、子查詢的規(guī)則

??? 將子查詢括在括號(hào)中

??? 將子查詢放置在比較條件的右側(cè)

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

??? 單行運(yùn)算符用于單行子查詢，多行運(yùn)算符用于多行子查詢 ?

?

五、單行子查詢

??? 僅返回一行

??? 使用單行的表較運(yùn)算符： = ,>, >= ,< , <= ,<>

???

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

??? SQL > select ename , job from emp

??? ? 2 ? where empno = (

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

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

?

??? ENAME ????? JOB

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

??? SMITH ????? CLERK

???

??? -- 使用分組函數(shù)的子查詢

??? 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

???

??? -- 單行子查詢中的常見錯(cuò)誤

??? ??? -- 子查詢的結(jié)果返回多于一行

?????? 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

??????

?????? -- 子查詢內(nèi)部沒有返回行，如下語句可以正確執(zhí)行，但沒有數(shù)據(jù)返回

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

?????? ? 2 ? from emp

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

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

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

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

?

?????? no rows selected

?

??? /* ?

六、多行子查詢

??? 返回多個(gè)行

??? 使用多行比較運(yùn)算符 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

???

???? /*

七、相關(guān)子查詢

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

?

??? -- 查詢工資高于同一部門的員工的部門號(hào)，姓名，工資

??? 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

??????????

??? -- 查詢負(fù)責(zé)管理其它員工的員工記錄 ( 使用 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 要檢查實(shí)際值的存在性 ( 一般情況下 EXISTS 的性能高于 IN)

??? ??? NOT EXISTS 和 NOT IN

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

??????????

??? -- 看下面的查詢，查詢部門號(hào)不在 emp 表中出現(xiàn)的部門名稱及位置

??? 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

???????????

????

??? /* ?

? ? 注：子查詢要包含在括號(hào)內(nèi)

??? ??? 子查詢一般放在比較條件的右側(cè)

??? ??? 除非進(jìn)行 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 、非成對比較 , 實(shí)現(xiàn)了與上述類似的功能 */

??? 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

?????? ?

???? /*

九、嵌套子查詢

??? 即位于子查詢內(nèi)部的子查詢，嵌套層數(shù)最多可達(dá)層。然而應(yīng)盡量避免使用嵌套子查詢，使用表連接的查詢性能會(huì)更高 */

??? 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(*) 外，都會(huì)忽略掉空值 */

?

??? /*

十、更多 */ ??

Oracle 數(shù)據(jù)庫實(shí)例啟動(dòng)關(guān)閉過程

?

Oracle 10g SGA 的自動(dòng)化管理

?

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實(shí)例

?

Oracle 實(shí)例和 Oracle 數(shù)據(jù)庫 (Oracle 體系結(jié)構(gòu) )

?

SQL 基礎(chǔ) --> 常用函數(shù)

?

SQL 基礎(chǔ) --> 過濾和排序

?

SQL 基礎(chǔ) -->SELECT 查詢

?

?

?

???

?

?

???

?

??

SQL 基礎(chǔ)--> 子查詢