零點起飛學Oracle之查詢數(shù)據(jù)表

1、第3章 查詢數(shù)據(jù)表查詢數(shù)據(jù)表對數(shù)據(jù)表來說是最頻繁的一種操作。Oracle中的SQL查詢語法，一方面遵循了SQL標準，另一方面又有自己的獨特之處。本章將詳細講述Oralce中的SQL查詢語句，其主要內容包括，基本查詢、子查詢、聯(lián)合語句、關聯(lián)語句、層次化查詢。13.1 基本查詢方式所謂查詢就是讓數(shù)據(jù)庫服務器根據(jù)客戶端的要求搜尋出用戶所需要的信息資料，并按用戶規(guī)定的格式進行整理后返回給客戶端。使用select子句和from子句的查詢是簡單查詢。select子句和from子句是查詢語句的必選項。復雜查詢語句，都是由最簡單的基本查詢組成。本節(jié)將重點講述示例數(shù)據(jù)庫腳本的下載及安裝，Oracle中的基本查詢

2、語句。23.1.1 安裝示例數(shù)據(jù)庫腳本在安裝Oracle 11g時，我們沒有安裝數(shù)據(jù)庫示例。如果我們在創(chuàng)建實例時沒有選擇安裝示例，我們可以通過官網(wǎng)下載并安裝Oracle 11g R2數(shù)據(jù)庫示例用戶。在瀏覽器地址欄中輸入Oracle官方網(wǎng)站網(wǎng)址，進入Oracle網(wǎng)站。可供選擇的下載地址，。33.1.2 查詢關鍵詞selectselect語句主要用于查詢語句。在select關鍵字后面放置的是要在查詢結果中顯示的列名，這些列名源于from后面的表。如果想查詢表中的全部數(shù)據(jù)，可以在select后面使用“*”代表對應表的所有列。無論查詢語句多么復雜，最外層的select命令總是最后執(zhí)行。數(shù)據(jù)查詢sele

3、ct的基本語法如下。select 列名 from 表名;【示例3-1】查詢一個簡單的查詢員工表employees中所有員工姓名。43.1.3 帶條件的查詢where子句一個數(shù)據(jù)表中存放著大量相關的記錄數(shù)據(jù)。在實際應用中，往往只需要其中滿足要求的部分記錄。這時，就需要用到where條件子句。where子句用于限定from子句所指定的數(shù)據(jù)源或者各數(shù)據(jù)源進行運算之后形成的結果集合?！臼纠?-2】對于職員信息表employees，查詢DEPARTMENT_ID是“90”的職員信息?！臼纠?-3】本例是from子句的復雜情況，查詢每位員工所在部門的具體信息。53.1.4 去除查詢結果中的重復記錄dist

4、inct在查詢的過程中，有時候從表中搜索到的所有行的數(shù)據(jù)，會有數(shù)據(jù)重復的情況，這是我們不希望看到的。使用distinct關鍵字 ，就可以從返回的結果中刪除重復的行，使返回的結果更簡潔?！臼纠?-4】查詢jobs表中，員工的最低工資分布情況，查詢時我們會發(fā)現(xiàn)重復記錄。為了剔除其中的重復記錄，可以利用distinct關鍵字。63.1.5 分組group by子句查詢結果中，我們可以使用group by子句對指定的列進行分組，即是將相同的列聚集在一起，該子句一般是放在where子句的后面。【示例3-5】從表employees與表departmens中查詢各個部門的員工總數(shù)，分別顯示部門號、部門名字、

5、以及總員工數(shù)的信息。73.1.6 過濾分組having子句having子句通常與group by子句是一起使用的，在完成對分組結果統(tǒng)計后，可以使用having子句對分組的結果做進一步的篩選。having子句與where子句的相似之處都是定義搜索條件。為了將分組按照一定條件進行過濾，應該使用having子句。【示例3-6】having子句是依附于group by子句存在而存在。在示例3-5中，查詢部門人數(shù)多余10個人的部門信息記錄。83.1.7 排序order by子句order by子句是可以對查詢結果進行排序，可以規(guī)定升序（從低到高）或降序（從高到低），方法是使用ASC（升序）或DESC（降

6、序）。如果省略ASC或DESC，系統(tǒng)則默認是升序。可以在order by組合中指定多個列，查詢結果首先按第一列進行排序，對第一列列值相同的那些數(shù)據(jù)行，再按照第2列進行排序，依次類推。order by子句一般寫在where子句的后面。【示例3-7】在jobs表中查詢員工的工資分布情況，查詢結果中可以看到，沒有指定排序規(guī)則的情況下，搜尋結果是雜亂無章的。可以對該搜尋結果按照工資水平由低到高的順序排列。93.1.8 order by與group by子句在數(shù)據(jù)庫查詢中，同時存在order by與group by子句時，Oracle首先執(zhí)行group by子句，然后才進行排序操作。分組是指將數(shù)據(jù)表中所

7、有記錄中，以某個或者某些列為標準，劃分為一組。進行分組查詢應該使用group by子句。group by子句指定分組標準，并將數(shù)據(jù)源按照該標準進行劃分，然后循環(huán)處理每組數(shù)據(jù)。再使用order by子句用于將分組的結果進行排序?！臼纠?-8】在示例3-5中，從表employees與表departmens中查詢時我們可以獲得各個部門的員工總數(shù)，以及部門號、部門名字、以及總員工數(shù)的信息。查詢之后，可以按照各個部門的員工總數(shù)進行升序排序。103.1.9 order by與distinctorder by子句與distinct關鍵字同時使用時，需要注意的是，如果指定了SELECT DISTINCT，那么

8、ORDER BY 子句中的項就必須出現(xiàn)在選擇列表中?！臼纠?-9】我們可以通過表employees獲得員工的工資狀況。查詢時顯示員工姓名，以及員工的工資，最后并按工資進行升序排列。113.2 子查詢子查詢是一般嵌套在select、insert、update、delete語句中，或者還可以嵌套在其他子查詢中。任何允許使用表達式的地方都可以使用子查詢。子查詢也叫做內部查詢或內部選擇，而包含子查詢的語句可以稱為外部查詢或者外部選擇。子查詢本質上是一種嵌入的select語句，由于子查詢是在外部查詢返回的結果集上執(zhí)行的，因此它的效率肯定是有所下降的。一般情況下，子查詢可用于查詢條件。123.2 子查詢【

9、示例3-10】例如，departments表中列出了所有部門信息，但是目前有的部門有員工，有的部門目前還沒有員工。利用表employees、departments查詢部門中有員工的部門信息中存儲了員工信息。133.3 聯(lián)合語句如果有不同的查詢結果數(shù)據(jù)集，但又希望將它們按照一定的關系連接在一起，組成一組數(shù)據(jù)，這就可以使用集合運算來實現(xiàn)。在Oracle數(shù)據(jù)庫中，PL/SQL提供的運算符有，union、union all、intersect和minus。這些集合運算都是二元運算，運算結果仍然是一個記錄集合。最基本的運算是并、交和差運算。143.3.1 求并集在PL/SQL中，可以使用union運算符

10、實現(xiàn)集合并的運算。使用union運算符可以將兩個或更多查詢的結果合并為單個結果集，該結果集包含聯(lián)合查詢中的所有查詢的全部行。union運算不同于使用連接合并兩個表中的列的計算。153.3.1 求并集其基本的語法如下所示。select commandunion | union allselect command .其中，select command表示查詢表達式，用于返回與另一個查詢規(guī)范或查詢表達式所返回的數(shù)據(jù)合并的數(shù)據(jù)；union指定合并多個結果集并將其作為單個結果集返回；ALL將全部行并入結果中，其中包括重復行，如果未指定該參數(shù)，則刪除重復行。163.3.1 求并集使用union合并兩個查詢

11、結果集，必須滿足的條件如下所示。在用union運算符組合的語句中，所有選擇列表中的表達式數(shù)目必須相同。用union組合的結果集中的對應列，或者各個查詢中所使用的列都必須具有相同的數(shù)據(jù)類型。用union運算組合的各語句中對應結果集中列的順序必須相同，因為union運算符按照各個查詢中給定的順序一對一地比較各列?！臼纠?0-13】數(shù)據(jù)庫中存在著兩個表employees和employeenew，分別存儲了參加了公司已有員工和新加入的員工信息。173.3.2 求并集union all運算與union運算都是并集運算。但是union all只是將兩個運算結果集進行簡單整合，并不剔除其中的重復數(shù)據(jù)。這是與

12、union運算的最大區(qū)別?！臼纠?-14】數(shù)據(jù)庫中存在著兩個表employees和employeenew，查詢表中的的員工的名字、以及上級編號。可以利用union all代替union來執(zhí)行SQL語句，并不去掉重復記錄。183.3.3 求交集使用intersect運算可以實現(xiàn)集合交集運算，返回intersect操作符左右兩邊的兩個查詢都返回的非重復值?！臼纠?-15】查詢兩個員工表employees和employeenew屬于同一上級的并且同名的的員工姓名和上級編號信息。193.3.4 求差集minus運算符可以實現(xiàn)集合差的運算，即是從左查詢中返回右查詢沒有找到的所有非重復值?！臼纠?-16】

13、使用minus運算符實現(xiàn)集合差運算。203.3.5 混合運算對于這四種集合運算union運算、uion all運算、intersect運算和minus運算，Oracle允許進行混合運算。在混合運算時，這四種運算的優(yōu)先級是相同的，也就是說，他們將按照自左至右的順序依次進行?！臼纠?-17】下面演示intersect和union all的混合運算?！臼纠?-18】我們可以首先將表employees與表employeenew進行一次并集運算，然后獲得employees表與該并集的差集。213.4 連接查詢在實際的操作中，往往需要同時從兩個或兩個以上的表中想查詢相關數(shù)據(jù)，連接就是滿足這些需求的技術。通

14、過連接，可以根據(jù)各個表之間的邏輯關系從兩個或多個表中檢索數(shù)據(jù)。Oracle中提供的表的連接有自然連接、內部連接、外部連接、交叉連接。223.4.1 自然連接查詢自然連接是一種比較特殊的等值連接，它將表中具有相同名稱的列自動進行記錄匹配。自然連接自動判斷相同名稱的列，而后形成匹配。連接后相同的列只保留一個。【示例3-19】表employees和表jobs都包含了job_id列。二者可以進行自然連接操作。233.4.1 自然連接查詢自然連接實際自動指定了搜尋條件。這里包括兩方面的內容：首先，自然連接列必須同名，另外，所有同名列都將作為搜尋條件。自然連接與笛卡爾積的區(qū)別在于，笛卡爾積無論何時，都將獲

15、得兩個數(shù)據(jù)表中所有記錄的兩兩組合。而當兩個數(shù)據(jù)表中存在同名列，Oracle將同名列作為搜尋條件。相當于強制添加了條件where t1.column = t2.column。但是，當兩個表不存在同名列時，Oracle不會添加任何搜尋條件，其效果相當于笛卡爾積。例如，表departments與表jobs不存在同名列，二者進行自然連接獲得的結果集與笛卡爾積相同。243.4.2 內連接查詢內連接是一種常用的多表查詢，一般用關鍵字inner join。其中，inner可以省略，而只使用join關鍵字表示內連接。使用內連接查詢多個表時，在from子句中需要定義一個on子句?！臼纠?-20】使用內連接查詢員

16、工信息和員工所在的部門名稱,并要求job_id的值是“ST_CLERK”。253.4.3 外連接查詢使用內連接進行多表查詢時，返回的查詢結果集中僅包含符合查詢條件和連接條件的行。內連接消除了與另一個表中任何行不匹配的行，而外連接擴展了內連接的結果集，除了返回所有匹配的行外，還會返回一部分或全部不匹配的行。這主要取決于外連接的種類。外連接分為左（外）連接和右（外）連接。因為內連接沒有左右之分，所以習慣上，我們將左外連接和右外連接簡稱為左連接和右連接。263.4.3 外連接查詢1左連接左連接的運算使用left join。左連接的連接條件中左邊的表不加限制?！臼纠?-21】在employees表中，

17、新插入一條記錄，再使用左連接查詢員工信息和員工所在的部門名稱,并要求job_id的值是“IT_PROG”。273.4.3 外連接查詢2. 右連接右連接的運算使用right join。右連接中對右邊的表不加限制?！臼纠?-22】與左連接相反，右連接是以運算符右側的表作為基礎表來實現(xiàn)關聯(lián)。右連接的運算符為right join，使用右連接來改寫示例3-21。283.4.3 外連接查詢3. 外連接的簡略寫法其實，外連接，使用left join和right join無非是為了判斷到底以數(shù)據(jù)源作為基礎。因此，Oracle提供了外連接的簡略寫法在where條件中將附屬數(shù)據(jù)源的列使用(+)進行標識，從而省略l

18、eft join/right join以及on關鍵字?！臼纠?-23】我們可以利用外連接的簡略寫法來改寫示例3-21。293.4.3 外連接查詢4簡略寫法的優(yōu)劣很多時候，使用簡略寫法會縮小代碼長度，并且易于調整基礎表。例如，我們利用where e.department_id(+)=d.department_id來指定表d為基礎表，當需要轉換基礎表時，只需調整(+)的位置即可，如下所示。where e.department_id=d.department_id(+)但是，當from子句指定了多個數(shù)據(jù)源時，使用left join/right join的寫法更易維護。303.4.3 外連接查詢5完全

19、連接完全連接實際是一個左連接和右連接的組合。也就是，如果兩個數(shù)據(jù)源使用了完全連接，那么將首先進行一次左連接，然后進行一次右連接，最后再刪除其中的重復記錄，即得到完全連接。完全連接應該使用full join關鍵字，并使用on關鍵字指定連接條件?！臼纠?-24】例如，可以利用full join來獲得表employees與表departments的完全連接。313.5 層次化查詢關系型數(shù)據(jù)庫中，同一個數(shù)據(jù)表中不同的記錄之間存在著平行關系。但是，有時候，各記錄之間也可能存在著“父子”關系。當這些“父子”關系較為復雜時，我們可以將整個表中的數(shù)據(jù)看做樹狀結構，而基于樹狀結構數(shù)據(jù)的查詢，稱為層次化查詢。層次查詢使用樹的遍歷，走遍含樹形結構的數(shù)據(jù)集合，來獲取樹的層次關系報表的方法，對于樹形結構的父子關系，你可以控制，遍歷樹的方向，是自上而下，還是自下而上；確定層次的開始點(root)的位