12、oracle--查詢/連接配接/層次查詢

執行個體;http://www.1keydata.com/cn/sql/sql-truncate.php

會話隔離：

備份表

create table table_name1 as select * from emp;

最常用的SELECT:

SELECT的基本構成

DUAL

查詢（ query）和子查詢（ subquery）

層次查詢

集合

連接配接

WITH語句

了解SELECT文法圖

執行順序：

where最新執行

group by

order by

詳細順序：

from：對from子句中的前兩個表執行笛卡爾積（Cartesian product)(交叉聯接），生成虛拟表VT1

on：對VT1應用ON篩選器。隻有那些使<join_condition>為真的行才被插入VT2。

outer(join)：如 果指定了OUTER JOIN（相對于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部聯接把左表标記為保留表，右外部聯接把右表标記為保留表，完全外部聯接把兩個表都标記為保留表）中未找到比對的行将作為外部行添加到 VT2,生成VT3.如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重複執行步驟1到步驟3，直到處理完所有的表為止。

where：對VT3應用WHERE篩選器。隻有使<where_condition>為true的行才被插入VT4.

group by：按GROUP BY子句中的列清單對VT4中的行分組，生成VT5.

CUBE|ROLLUP：把超組(Suppergroups)插入VT5,生成VT6.

having：對VT6應用HAVING篩選器。隻有使<having_condition>為true的組才會被插入VT7.

select：處理SELECT清單，産生VT8.

distinct：将重複的行從VT8中移除，産生VT9.

order by：将VT9中的行按ORDER BY 子句中的列清單排序，生成遊标（VC10).

top：從VC10的開始處選擇指定數量或比例的行，生成表VT11,并傳回調用者。

count(*)是計算有多少列

sum（salary）是計算salary列的總合

DML異常：

ORA-00979 不是 GROUP BY 表達式錯誤的解釋

err ;select deptno,job,avg(sal) from emp group by deptno;

right:select deptno,job,avg(sal) from emp group by deptno,job;

因為在select 清單像中出現了像deptno和job，而在group by中并沒有出現的緣故。

子查詢（ subquery）

一個查詢嵌套在另外的SQL語句中，該查詢稱為子查詢（ subquery）

子查詢不僅僅會出現在SELECT中

select empno, ename from emp where mgr in (select empno from emp where job='MANAGER');

select * from emp where sal > 1.4*(select avg(sal) from emp);

insert into dept(deptno, dname, loc) select 50, 'TRAINING', 'PEKING' from dual;

exists是判斷是否存在,和in類似,但效率要比in高

update emp set sal=sal*1.2 where exists (select 1 from dept where deptno=emp.deptno and loc='DALLAS');

update emp set sal=sal*1.2 where in (select deptno from dept where loc='DALLAS');

[color=green]注意exists的使用，一定要帶表的别名[/color]

Set:

數學上的集合

集合的概念

一個集合就是将數個對象歸類而分成為一個或數個形态各異的大小整體。

一般來講，集合是具有某種特性的事物的整體，或是一些确認對象的彙集。

構成集合的事物或對象稱作元素或是成員。

集合的特性

唯一性

無序性

确定性

計算機上的集合

清單：資料項的順序是确定的，也可以存在多個相同的資料項。

隊列、堆棧

集：資料項是無序的，也不允許存在相同資料項。

多重集：類似于集，其中資料項是無序的。但在多重集中，可以存在相同的資料項。

關聯數組：和字典相似，為鍵輸入提供一個值輸出，即Key-Value

樹：“根”節點與一定數量的資料項以親-子關系聯系起來，而其子資料項也與另外的資料項

以同樣的方式聯系。

圖：每個資料項都可以與一個或多個其它資料項聯系起來，其中每個節點都是平等的，類似于

無根節點、無親-子關系的樹。

Oracle的結果集（ ResultSet）

結果集的概念

一個查詢（包括子查詢）的結果，稱為結果集。

結果集的特性

[color=green]不唯一性[/color]

無序性

确定性

Oracle中的集合操作

并集

UNION：集

UNION ALL：集或多重集

交集

INTERSECT：集

差集（補集）

MINUS：集

操作符（ UNION [ALL], INTERSECT, MINUS）

擁有同樣的優先級

括号可以改變運算順序

query1 minus (query2 union query3)

contect:

連接配接定義

從兩張或更多張表/視圖中做的查詢稱為連接配接

隻要FROM後面出現多個表/視圖， Oracle總會去執行連接配接操作

如果某列的名稱在多個表/視圖中出現，那麼為避免模棱兩可必須在列前加上表/視圖的名或别名

連接配接條件

通常多表連接配接都會至少有一個連接配接條件

無連接配接條件的連接配接結果集稱為笛卡爾積（ Cartesian Products）

連接配接條件兩端的字段必然來自不同的兩張表/視圖

多表連接配接時，除了連接配接條件，還可以有單表資料過濾條件

連接配接類型: 等值連接配接、自連接配接、笛卡爾積、内連接配接（簡單連接配接/連接配接）、外連接配接、反連接配接、半連接配接

等值連接配接 [ = ] (笛卡爾積受制于where條件)

自連接配接 from emp a, emp b

内連接配接 from emp inner join salgrade on

反連接配接 not in

半連接配接 where exists 相當于 and （沒有生成笛卡爾積，故快速）

select * from emp a where exists (select 1 from dept b where loc='NEW YORK' and a.deptno=b.deptno);

外連接配接 （+）過時但可用，替代：left outer join, right outer join和full outer join

from dept b left join emp a on

from emp a right join dept b on

from emp a right outer join dept b on

左連接配接和左外連接配接的差別：

兩者是一樣的，隻是寫法不同，相同的概念。

層次查詢Hierarchical Query

select empno, ename, job, mgr, deptno, level from emp s[color=green]tart with job='PRESIDENT'connect by prior empno= mgr;[/color]

CONNECT BY rownum <= length(p_str))對輸入的字元串進行逐個周遊

select * from dual connect by rownum<5