零點(diǎn)起飛學(xué)Oracle之查詢數(shù)據(jù)表

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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