查詢是數據庫的基本應用之一，oracle中的SQL查詢語法，一方面遵循了SQL標準，另一方面又有自己的獨特之處。

從而使得oracle中的SQL查詢功能更加強大。接下來將會涉及oracle中的SQL查詢語句，包括：

基本查詢：主要講述查詢語句及各種子句的使用；

子查詢：主要講述如何在查詢語句中添加子查詢；

聯合語句：主要講述多個查詢語句之間的集合運算；

關聯語句：主要講述多個表/視圖之間的關聯關系；

層次化查詢：主要講述樹狀結構的查詢。

一、基本查詢：

1. 查詢命令select

執行查詢的命令為select命令，該命令用于在數據源中捕獲最終數據。無論查詢語句多么復雜，最外層的select命令總是最后執行。

具體表現為：oracle首先根據from子句獲得數據源的所有記錄，接著，oracle掃描所有記錄，并根據select命令所指定的列獲取最終結果。

2. 指定過濾條件----where子句

where子句用于限定from子句所指定的數據源，或者各數據源進行運算之后形成的結果集合。因此，執行順序處于from子句之后，select命令之前。即使from子句更加復雜，其執行順序仍然如此。

注：笛卡爾積-->獲得的是兩個數據表的乘積，第一表的每條記錄與第二表的每條記錄組合所獲得結果集。

3. 獲取唯一記錄----distinct

在查詢時，會出現重復記錄。為了剔除重復記錄，可以利用distinct關鍵字。

select distinct a.xxx, b.yyy from table a, tables b where a.a_id = b.a_id

其中，distinct關鍵字用于獲取結果集中列a.xxx，b.yyy的唯一性組合。

4. 分組----group by 子句

在數據庫查詢中，分組是一個非常重要的應用。分組是指將數據表中的所有記錄，以某個或者某些列為標準，劃分為一組。例如：在一個存儲了地區學生的表中，以學校為標準，可以將所有學生信息劃分為多個組。

進行分組查詢應該使用group by子句。該子句指定分組標準，并將數據源按照該標準進行劃分，然后循環處理每組數據。

select cd.channel_id, cd.channel_name?
? ? ? ? from intf.ecs_order eo , liuxin.channel_dianqu cd
? ? ? ? ? where eo.org_id = cd.channel_id
? ? ? ? ? group by cd.channel_id, cd.channel_name

其中，group by?cd.channel_id, cd.channel_name用于對數據源按列?cd.channel_id, cd.channel_name 進行分組。詳細步驟如下：

（1）from子句獲得數據源；（2）利用where 子句篩選符合條件的記錄；（3）利用group by子句進行分組；

（4）對每組進行循環處理，獲得最終結果----每組的cd.channel_id, cd.channel_name。

5. 過濾分組----having子句

where子句可以過濾from子句所指定的數據源，但是對于group by子句所產生的分組無效。為了將分組按照一定條件進行過濾，應該使用having子句。

having子句是依附于group by子句存在而存在的。

select cd.channel_id, cd.channel_name?
? ? ? ? from intf.ecs_order eo , liuxin.channel_dianqu cd
? ? ? ? ? where eo.org_id = cd.channel_id
? ? ? ? ? group by cd.channel_id, cd.channel_name

having cd.channel_id >100000199

上面例子說明的是ID號大于100000199的記錄。

（1）利用group by子句分組；（2）利用having子句篩選分組----組內所有ID號大于100000199。

（3）對每組進行循環處理，獲得最終結果----每組的cd.channel_id, cd.channel_name。

6. 排序----order by子句

order by子句用于排序結果集。order by子句在使用時需要指定排序標準和排序方式。排序標準是指按照結果集中哪個或哪些列進行排序；order by有兩種排序方式---升序（asc，也是默認的排序方式）和降序（desc）。

select cd.channel_id, cd.channel_name?
? ? ? ? from intf.ecs_order eo , liuxin.channel_dianqu cd
? ? ? ? ? where eo.org_id = cd.channel_id
? ? ? ? ? order by cd.channel_id desc;

7. order by與group by子句

order by與group by同時存在的時候，oracle是怎樣執行的呢？

oracle首先執行group by子句，然后才進行排序操作。

8. order by與distinct

order by子句與distinct關鍵字同時使用時，也必須遵循這樣的一個規則---order by子句所指定的排序列，必須出現在select表達式中。

二、子查詢：

子查詢是指在查詢語句的內部嵌入查詢，以獲得臨時的結果集。oracle總是自動優化帶有子查詢的查詢語句。如果子查詢的數據源與父查詢中的數據可以實現連接操作，那么將轉化為連接操作；否則，將首先執行子查詢，然后執行父查詢。 內嵌視圖也可以看做子查詢的一種形式，內嵌視圖式的子查詢出現在from子句中，作為父查詢的數據源。 本階段重點講述子查詢的另外兩種應用模式：用于查詢條件和insert into語句。
1、查詢條件中的子查詢 子查詢可以用于查詢條件。例子： select * from employees where employee_id in (select employee_id from salary); 其中，select employee_id from salary 是子查詢。 子查詢實際返回一個結果集，該結果集僅有有一列，并用于查詢條件where employee_id in (select employee_id from salary)中。該查詢條件要求搜尋表employees中，employee_id的值在子查詢的結果集中。
2、建表語句中的子查詢 子查詢的另外一個應用場景用于建表語句中。 例如：oracle的內置視圖user_objects描述了當前用戶所有對象信息，現需要創建一個與該視圖具有相同結果的空數據表，則可以利用如下SQL語句。 create table tmp_user_objects ?as select * from user_objects where 1<>1; 子查詢select * from user_objects where 1<>1 獲得的實際是一個空結果集；利用該結果集創建數據表時，將創建一個空的數據表。 利用語句可瀏覽：desc tmp_user_objects;
3、插入語句中的子查詢 同樣，我們也可以在插入語句中使用子查詢，這相當于向表中批量插入數據。 例如：insert into tmp_user_objects selects * from user_objects where object_type = 'table'; slect * from user_objects where objects_type = 'TABLE'用于獲得視圖 user_objects 中object_type 為table的所有記錄。 該子查詢的所有記錄，將被插入表tmp_user_objects中。

三、聯合語句：

聯合語句是指對于多個查詢獲得的結果集進行集合操作。這些集合操作包括union、union all、intersect 和 minus。

這些集合運算都是二元運算，運算結果仍然是一個記錄集合。

本階段講述這幾種聯合運算的使用。

1、求并集（記錄唯一）----union運算

union運算實際是合并兩個結果集中的所有記錄，并將其中重復記錄剔除（保證結果集中的記錄唯一）。

例：數據庫中存在著兩個表a_students和b_students，分別存儲了參加了a培訓班和b培訓班的學生信息，其數據如下所示。現需要取得a班和b班共有多少學生，實際為獲取表a_students與表b_students的并集，相應的SQL語句如下所示。

select student_id, student_name from a_students?

union?

select student_id, student_name from b_students;

union 用于對select student_id, student_name from a_students 和 select student_id, student_name from b_students所獲得結果集進行并集運算。

但需要注意的是，union運算的兩個結果集必須具有完全相同的列數，并且各列具有相同的數據類型。

select student_id, student_name, student_age from a_students

union

select student_id, student_name from b_students

上面這個就會報錯！

oracle將拋出錯誤：ORA-01789：query block has incorrect number of result columns表面查詢結果的列數非法，不能進行并集運算。

2、求并集----union all 運算

union all 運算與union運算都可看做并集運算。但是union all只是將兩個運算結果集進行簡單整合，并不剔除其中的重復數據。這是與union運算的最大區別。

為了統計

例如：為了統計A班級和B班級有多少人次參加了培訓，則可以利用union all代替union來執行上面的SQL語句。

select student_id, student_name from a_students

union all

select student_id, student_name from b_students;

union all 并不刪除重復記錄，因此該SQL語句的執行結果記錄較多。同時，由于union all運算不刪除重復記錄，因此在執行效率上要高于union操作。因此，當對兩個結果集已經確定不會存在重復記錄時，應該使用union all操作，以提升效率。

3、求交集---intersect運算

intersect運算是指交集運算。該運算可以獲得兩個結果集的交集----即同時存在于兩個結果集中的記錄。

例如：在表a_students和表b_students中均存在著學生信息，現需要獲得既參加了a班，又參加了b班的學生姓名。即同時存在于兩個表中的學生姓名，則可以使用intersect運算。相應的SQL如下：

select student_name from a_students?

intersect

select student_name from b_students

4、求差集----minus運算

minus是集合間的減法運算。該運算將返回第一個集合中存在，而第二個集合中不存在的記錄。

例如：現需要獲得參加A培訓班，但是未參加B培訓班的學生。此時，可以利用minus運算獲得存在于表a_students中，而不存在于表b_students中的學生姓名。

select student_name from a_students

minus

select student_name from a_students;

minus用于獲得兩個結果集的差集。注意：是返回得哪個表中的！

5、聯合運算的混合運算

對于這4種集合運算----union運算、union all運算、intersect運算和minus運算，oracle允許進行混合運算。在混合運算時，這4種運算的優先級是相同的，也就是說，他們將按照自左至右的順序依次進行。

例如：intersect和union all的混合運算

select student_name from a_students

intersect?

select student_name from a_students

union all

select student_name from b_students

調整優先級，可以使用小括號！

四、連接：

在大多數查詢中，所使用的數據源往往有多個。當多個數據源同時使用時，這些數據源如何進行組合便成為了一個至關重要的問題。連接即用來指定多個數據源之間的組合關系。默認情況下，多個數據源之間使用的是笛卡爾積方式進行組合。除此之外，oracle還提供了另外幾種特殊的組合方式。這些特殊方式有效地補充了笛卡爾積的不足。

1、自然連接

自然連接，顧名思義，即無須用戶指定任何連接條件，只需指定連接的兩個數據源。至于兩個數據源如何進行數據整合則無須用戶操心，自然連接所使用的關鍵字為natural join。其連接原則：兩個數據源的共有列，并且具有相同列值。

例如：表employees和表salary都包含了employee_id列。二者可以進行自然連接操作，相應的SQL語句如下所示。

select * from employees natural join salary;

natural join 用于兩個表之間的自然連接。搜尋結果獲得的結果集，將含有公共列employee_id。這里所說的公共列是指，不能為列employee_id指定限定詞。例如，不能講列employee_id標識為特定表employees或者salary中的列。詳看如下SQL：

select e.employee_id from employees e natural join salary s;

上面語句將會拋出錯誤：ORA-25155：column used in NATURAL join cannot have qualifier

select e.employee_id嘗試在自然連接的結果集中獲得表employees中的列employee_id。此處的錯誤原因是：用于自然連接的列不能指定限定詞。

當然，對于自然連接之外的其他列，可以使用限定詞進行修飾。例如：

select employee_id, e.employee_name, s.month, s.salary from employees e natural join salary s

2、內連接

自然連接強制使用兩個表之間的公共列表作為搜尋條件；而且要求公共列的值必須相等，這帶來了極大的限制，因此，自然連接并不常用。而內連接突破了這兩種約束，內連接可以自行指定連接列和連接條件。內連接運算的關鍵字為inner join.

例如：同樣為了實現獲得員工工資狀況這一需求，利用內連接的SQL語句如下所示。

selecte.employee_id, e.employee_name, s.month, s.salary

from employees e

inner join salarys

on e.employee_id =s.employee_id;

其中，select e.employee_id, e.employee_name, s.month, s.salary 用于獲取表employees中的employee_id、employee_name和表salary中的month、salary等列，與自然連接不同的是，此處的employee_id可以使用限定符e進行修飾；from employees e inner join salary s 用于將表employees與表salary進行內聯操作；on e.employee_id = s.employee_id用于指定搜尋條件----表employees的列employee_id與表salary的列employee_id具有相同的列值。

另外，內連接運算inner join 中的inner關鍵字可以省略，如下所示。

selecte.employee_id, e.employee_name, s.month, s.salary

from employees e

join salary s

on e.employee_id =s.employee_id；

默認情況下，oracle的連接為內連接，因此，在這里，使用了join代替inner join可以實現相同的效果，而且寫法更為簡潔。

對于大多數開發者來說，并不習慣使用內連接方式，而更習慣于where 條件實現，利用where 條件改寫本示例語句如下：

selecte.employee_id, e.employee_name, s.month, s.salary

from employees e,salary s

wheree.employee_id = s.employee_id;

雖然可以利用where 子句改寫內連接的SQL查詢，但需要注意的是，當實現多表關聯，oracle在執行時還是有區別的，對于where子句方式，并且from子句中含有多個數據源，oracle在進行笛卡爾積運算時會自行優化。例如：

select * fromemployees, salary, company, sales wehre ...

from 子句中包含了多個數據表，而oracle執行的方式，并非按照如下順序employees X salary X company X sales（其中X代表進行笛卡爾積運算）。而有可能被優化為salary X employees X sales X company，因此，有時我們對oracle的執行結果感到莫名其妙，使用肉眼即可分辨這種執行結果是錯誤的，盡管這種機會微乎其徽。

此時，不妨使用內連接來嘗試解決該問題，改寫后的代碼為：

select * fromemployees

join salary on....

join company on

join sales on

在這種執行方式下，oracle所執行的連接順序一定是遵循employees X salary X company X sales。

?

3、外鏈接

內連接所指定的兩個數據源，處于平等的地位。而外鏈接不同，外鏈接總是以一個數據源為基礎，將另外一個數據源與之進行條件匹配。即使條件不匹配，基礎數據源中的數據總是出現在結果集中。那么，依據哪個數據源作為基礎數據源，便出現了兩種外鏈接的方式----左（外）連接和右（外）連接。因為內連接沒有左右之分，所以，習慣上，將左外連接和右外連接簡稱為左連接和右連接。

3.1 左連接

例子：左連接和右連接的運算應該使用left join 和right join.利用內連接獲得員工的工資信息，但是這并不包括所有員工的工資狀況，因為有的員工并未出現在工資表salary中，為了獲得所有員工的工資狀況，可以利用外連接來實現。

selecte.employee_id, e.employee_name, s.month, s.salary

from employeese?

left join salary s

on e.employee_id =s.employee_id;

其中，from employees e left join salary s 指定數據源為表employees與salry的左連接；如果將left join 視作運算符，那么左表為employees,右表為salary；左連接的意思即以左表----employees為基礎表；on e.employee_id = s.employee_id指定連接條件----表employees的employee_id列與表salary的employee_id具有相同的值。

?

3.2 右連接

與左連接相反，右連接是以運算符右側的表作為基礎表來實現關聯。右連接的運算符為right join.

?

3.3 外連接的簡略寫法

使用left join和right join無非是為了判斷到底以數據源作為基礎，因此，oracle提供了外連接的簡略寫法----在where條件中獎附屬數據源的列使用（+）進行標識，從而省略left join right join 及on關鍵字。

wheree.employee_id = s.employee_id(+) ? --左連接

wheree.employee_id(+) = s.employee_id ?--右連接

?

3.4 完全連接

完全連接實際是一個左連接和右連接的組合，也就是說，如果兩個數據源使用了完全連接，那么將首先進行一次左連接，然后進行一次右連接，最后再刪除其中的重復記錄，即得到完全連接。完全連接應該使用full join 關鍵字，并使用on關鍵字指定連接條件。

selecte.employee_id, e.employee_name, s.month, s.salary

from employees e

full join salarys?

on e.employee_id =s.employee_id;

完全連接的執行過程：首先執行employees與salary的左連接，然后執行二者的右連接，最后將兩個臨時結果集進行union操作。

select e.employee_id, e.employee_name, s.month, s.salary

from employees e

left join salary s

on e.employee_id = s.employee_id

union

select e.employee_id, e.employee_name, s.month, s.salary

from employees e

right join salary s

on e.employee_id = s.employee_id;

五、層次化查詢：

關系型數據庫中，同一個數據表中的記錄具有相同的列，因此，不同的記錄之間存在著平行關系。但是，有時候，各記錄之間也可能存在著“父子”關系。當這些“父子”關系較為復雜時，可以將整個表中的數據看做樹狀結構，而基于樹狀結構數據的查詢，稱為層次化查詢。

1、樹狀模型

樹狀模型一個很典型的實例為市場信息。不同層級的市場之間存在著父子關系，如下圖：

可以將市場信息存儲于表market中，相應的表結構及數據如下所示。

在該表中，列parent_market_id非常重要。該列存儲了當前市場的父級市場ID，依據parent_market_id與market_id，可以將原本平行結構的市場信息組裝為獲得樹狀模型。

2、層次化查詢

自9i版本開始，oracle提供了層次化查詢方案遞歸獲得樹狀模型的信息，層次化查詢的語法如下所示：

select 列名1、列名2.....

from 表名

start with 開始條件

connect by 遞歸條件

其中，start with指定查詢的起點，即從哪些記錄開始查詢；connect by指定遞歸條件，以獲得下一條記錄。

例如：在表market中搜索市場“亞洲”及其所有市場信息，相應的SQL語句及搜尋結果如下所示。

select market_id, market_name?

from market

start with market_name = '亞洲'

connect by prior market_id = parent_market_id；

start with market_name = '亞洲'指定查詢的起始節點為market_name等于'亞洲'的記錄；connect by prior market_id = parent_market_id指定如何遞歸獲得下一條記錄----前一條記錄的market_id等于下一條記錄的parent_market_id。

connect by 遵循深度優先的搜索策略，對于以上SQL語句，其執行順序如下所示：

（1）start with market_name = '亞洲',獲得一條記錄。該記錄為查詢的起始結點；

（2）當使用connect by prior parent_market_id = market_id，進行遞歸時。prior是指前一條記錄，即當前記錄‘亞洲’。前一條記錄的parent_market_id等于下一條的market_id，那么將獲得3條記錄，即market_id分別為5.6.7的記錄。oracle將利用最先獲得記錄（中國）再次進行遞歸；

（3）connect by prior parent_market_id = market_id，對于遞歸操作，前一條（prior）記錄為中國，因此，此次的遞歸操作，會再次獲得下級市場信息。

（4）對于記錄北京，當oracle再次利用connect by prior market_id = parent_market_id嘗試獲得下一條記錄時，將無法獲得任何記錄。這意味著已完成了最“左”端最深層的遞歸查詢。那么oracle將返回上一級記錄中國，并對第二條記錄天津繼續進行遞歸查詢。

（5）以此類推，oracle遵循從左至右、深度優先的策略，可以遞歸的獲得亞洲及亞洲之下的所有市場信息。

通過以上步驟及查詢語句的執行結果可知，利用connect by 實現的層次化查詢返回的實際是一個結果集，該結果集為遞歸遍歷所有經過的每條記錄(樹中的結點)的集合。

3、層次化查詢的相關函數

對于層次化查詢，最常用的函數為sys_connect_by_path()函數。層次化查詢總是以某條記錄為起點，根據connect by所指定的條件遞歸獲得結果集合。

而sys_connect_by_path()函數，則可以對起始至當前記錄之間的結果集進行聚合操作。該操作僅限于串聯字符串，響應的語法如下：

sys_connect_by_path(列名，分隔符)

其中，列名指定將哪個列的值進行串聯，而分割符則指定字符串串聯時的分隔符。

例如：為了獲得北京至頂級市場的完整路徑，可以使用如下SQL語句。

select market_id, market_name, sys_connect_by_path(market_name, '/' ) market_path?

from market?

start with market_name = '北京'

connect by prior parent_market_id = market_id;

select market_id, market_name, sys_connect_by_path(market_name, '/' )market_path用于獲得層次化查詢過程中，每條記錄的market_id、market_name、起始記錄至當前記錄的market_name列的字符串串聯，串聯過程中，使用'/'作為分隔符；start with market_name = '北京' connect by prior parent_market_id = market_id指定層次化查詢的定義---從market_name等于北京的記錄開始，按照前一條記錄的parent_market_id等于下一條記錄的market_id的規則進行遞歸。

分析查詢結果可知，市場路徑是節節攀升的，鑒于此，可以利用max()函數獲得最完整的路徑。

select max(sys_connect_by_path(market_name, '/')) market_path

from market

start with market_name = '北京'

connect by prior parent_market_id = market_id;

結果為：/北京/中國/亞洲/全球

其中，max()函數可用于字符串類型，已獲得按字母表順序排列時，處于末位位置的字符串。

4、總結一條記錄衍生出多條記錄

對于數據表中的某條記錄，有時需要多條記錄為其服務。這就涉及一條記錄衍生出多條記錄的問題。在oracle中，如果要使一條記錄衍生出多條記錄，大致有兩種方法，一種是通過窗口函數（將在后面介紹），另一種是通過層次化查詢。

對于窗口函數來說，數據表中有多條記錄是必要的，如果一個數據表中僅有一條記錄，那么無論如何是無法衍生出多條記錄的。并且，衍生出的記錄數目（窗口大小）也不能大于數據表的記錄總數。

針對層次化查詢語法，所能獲得的記錄數目則要靈活的多。connect by 只是指定獲得下一條記錄的條件，針對start with所指定的記錄（如果沒有start with，則針對所有記錄），oracle會依次掃描數據表中的記錄，并以遞歸的方式進行下去。也就是說，connect by完全具備死循環的條件。

1、二進制轉十進制

在oracle 9i以后，oracle提供了bin_to_num()函數，用于將二進制數據轉換為數值型，例如：

select bin_to_num(1, 0, 1) a, bin_to_num(1,0) b from dual;

同樣可以利用如下SQL語句進行處理：

select sum(data) from ( select substr('101', rownum, 1) * power (2, length('101') - rownum) data?

from dual

connect by rownum ?< = length('101'))

在該SQL語句中，表dual本來僅有一條記錄X，但是當利用connect by rownum <=length('101')獲得下一條記錄時，仍會獲得記錄X，直至獲得的記錄總數等于3（二進制101的總位數）。而針對每次獲得的記錄，oracle依次計算各位置對應的實際數字（利用2的乘方運算），最后將所有數字相加，便獲得了二進制數字代表的十進制數字。

轉載于:https://www.cnblogs.com/iamliuxin/p/5617814.html

總結

以上是生活随笔為你收集整理的【数据库】SQL查询强化篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。