Oracle-SQL

這是對Oracle-SQL知識點(diǎn)詳細(xì)介紹的文章系列噩茄，其他文章如下：

• Oracle-SQL系列知識點(diǎn)(一)
• Oracle-SQL系列知識點(diǎn)(三)
• PL/SQL基礎(chǔ)知識詳解

多表連接

• 1澜薄、什么是連接

連接是在多個表之間通過一定的連接條件缅疟，使表之間發(fā)生關(guān)聯(lián)，進(jìn)而能從多個表之間獲取數(shù)據(jù)

1477117179356.png

在 WHERE子句中書寫連接條件粤策。
如果在多個表中出現(xiàn)相同的列名向胡，則需要使用表名作為來自該表的列名的前綴。
N個表相連時蟆炊，至少需要N－1個連接條件

• 2、笛卡爾積
  第一個表中的所有行和第二個表中的所有行都發(fā)生連接瀑志。

笛卡爾積在下列情況產(chǎn)生：

• 連接條件被省略
• 連接條件是無效的

為了避免笛卡爾積的產(chǎn)生涩搓，通常需要在WHERE子句中包含一個有效的連接條件；

• 3劈猪、 限制歧義列名

在用到多個表時可以使用表名作前綴來限定列昧甘；

通過使用表前綴可以提高性能；

通過使用列的別名可以區(qū)分來自不同表但是名字相同的列岸霹；

4、用等值連接檢索數(shù)據(jù)

1477120390478.png

5将饺、非等值連接的數(shù)據(jù)檢索

1477120416865.png

6贡避、外部連接

在多表連接時痛黎，可以使用外部連接來查看哪些行,按照連接條件沒有被匹配上。

外部連接的符號是 (+)刮吧，語法包括：

1477120464641.png

1477120472009.png

（+）所在的一邊數(shù)據(jù)缺失湖饱；

• 7、自連接

自身連接杀捻，也叫自連接井厌，是一個表通過某種條件和本身進(jìn)行連接的一種方式，就如同多個表連接一樣致讥。

1477120575678.png

• 8仅仆、SQL:1999語法的連接

• 交叉連接

交叉連接會產(chǎn)生連個表的交叉乘積，和兩個表之間的笛卡爾積是一樣的垢袱；

使用CROSS JOIN 子句完成墓拜。

1477120745841.png

• 自然連接

自然連接是對兩個表之間相同名字和數(shù)據(jù)類型的列進(jìn)行的等值連接；

如果兩個表之間相同名稱的列的數(shù)據(jù)類型不同请契，則會產(chǎn)生錯誤咳榜；

使用NATURAL JOIN子句來完成；

1477120758631.png

• USING子句

自然連接是使用所有名稱和數(shù)據(jù)類型相匹配的列作為連接條件爽锥，而USING子句可以指定用某個或某幾個相同名字和數(shù)據(jù)類型的列作為連接條件

1477120815300.png

使用USING子句創(chuàng)建連接時涌韩，應(yīng)注意以下幾點(diǎn)：

• 如果有若干個列名稱相同但數(shù)據(jù)類型不同，自然連接子句可以用USING子句來替換氯夷，以指定產(chǎn)生等值連接的列臣樱。

• 如果有多于一個列都匹配的情況，使用USING子句只能指定其中的一列肠槽。

• USING子句中的用到的列不能使用表名和別名作為前綴擎淤。

• NATURAL JOIN子句和USING子句是相互排斥的，不能同時使用

• ON子句

自然連接條件基本上是具有相同列名的表之間的等值連接秸仙；

如果要指定任意連接條件,或指定要連接的列嘴拢，則可以使用ON子句；

用ON將連接條件和其它檢索條件分隔開寂纪，其它檢索條件寫在WHERE子句席吴；

ON子句可以提高代碼的可讀性。

1477120972480.png

1477120988643.png

• 左外連接

左外連接以FROM子句中的左邊表為基表捞蛋，該表所有行數(shù)據(jù)按照連接條件無論是否與右邊表能匹配上孝冒，都會被顯示出來

1477121046649.png

• 右外連接

右外連接以FROM子句中的右邊表為基表，該表所有行數(shù)據(jù)按照連接條件無論是否與左邊表能匹配上拟杉，都會被顯示出來庄涡。

1477121086219.png

• 全外連接

全外連接返回兩個表等值連接結(jié)果，以及兩個表中所有等值連接失敗的記錄

1477121109510.png

分組函數(shù)

• 為什么使用分組函數(shù)

分組函數(shù)是對數(shù)據(jù)行的集合進(jìn)行操作并按組給出一個結(jié)果搬设，這個結(jié)果可直接輸出穴店，或者用來做判斷條件撕捍。

單行函數(shù)和分組函數(shù)區(qū)別

1477121627533.png

• 分組函數(shù)

分組函數(shù)是對表中一組記錄進(jìn)行操作，每組只返回一個結(jié)果泣洞，即首先要對表記錄進(jìn)行分組忧风，然后再進(jìn)行操作匯總，每組返回一個結(jié)果球凰，分組時可能是整個表分為一組狮腿，也可能根據(jù)條件分成多組。

分組函數(shù)常用到以下五個函數(shù)：
MIN
MAX
SUM
AVG
COUNT

1477121709556.png

• MIN函數(shù)和MAX函數(shù)

MIN和MAX函數(shù)主要是返回每組的最小值和最大值呕诉。

• MIN([DISTINCT|ALL] column|expression)
• MAX([DISTINCT|ALL] column|expression)

MIN和MAX可以用于任何數(shù)據(jù)類型

• SUM函數(shù)和AVG函數(shù)

SUM和AVG函數(shù)分別返回每組的總和及平均值缘厢。

• SUM([DISTINCT|ALL] column|expression)
• AVG([DISTINCT|ALL] column|expression)

SUM和AVG函數(shù)都是只能夠?qū)?strong>數(shù)值類型的列或表達(dá)式操作。

• COUNT函數(shù)

COUNT函數(shù)的主要功能是返回滿足條件的每組記錄條數(shù)义钉。

• COUNT( *|{[DISTINCT|ALL] column|expression})

**DISTINCT會消除重復(fù)記錄后再使用組函數(shù) **

• 分組函數(shù)中空值處理

除了COUNT（）*之外昧绣，其它所有分組函數(shù)都會忽略列中的空值，然后再進(jìn)行計算捶闸；

在分組函數(shù)中使用NVL函數(shù)夜畴，NVL 函數(shù)可以使分組函數(shù)強(qiáng)制包含含有空值的記錄

• 用GROUP BY子句創(chuàng)建數(shù)據(jù)組

1477121955949.png

通過 GROUP BY 子句可將表中滿足WHERE條件的記錄按照指定的列劃分成若干個小組，其中GROUP BY子句指定要分組的列

在SELECT列表中除了分組函數(shù)那些項(xiàng)删壮，所有列都必須包含在GROUP BY 子句中

• 用 HAVING Clause子句排除組結(jié)果

使用 HAVING 子句限制組

• 記錄已經(jīng)分組.

• 使用過組函數(shù).

• 與 HAVING 子句匹配的結(jié)果才輸出

• SELECT語句執(zhí)行過程

1477122124139.png

1477122182122.png

------>>>

1477122207610.png

------>>>

1477122218458.png

1477122230438.png

1477122240872.png

------>>>

1477122249240.png

1.通過FROM子句中找到需要查詢的表贪绘；
2.通過WHERE子句進(jìn)行非分組函數(shù)篩選判斷；
3.通過GROUP BY子句完成分組操作央碟；
4.通過HAVING子句完成組函數(shù)篩選判斷税灌；
5.通過SELECT子句選擇顯示的列或表達(dá)式及組函數(shù)；
6.通過ORDER BY子句進(jìn)行排序操作亿虽。

子查詢

• 使用子查詢

1477122409227.png

• 括號內(nèi)的查詢叫做子查詢菱涤，也叫內(nèi)部查詢，先于主查詢執(zhí)行洛勉。
• 子查詢的結(jié)果被主查詢（外部查詢）使用
• expr operator包括比較運(yùn)算符粘秆。
  • 單行運(yùn)算符：>、=收毫、>=攻走、<、<>此再、<=
  • 多行運(yùn)算符： IN昔搂、ANY、ALL

子查詢可以嵌于以下SQL子句中：

• WHERE子句

• HAVING子句

• FROM子句

• 子查詢使用指導(dǎo)

  • 子查詢要用括號括起來
  • 將子查詢放在比較運(yùn)算符的右邊
  • 對于單行子查詢要使用單行運(yùn)算符
  • 對于多行子查詢要使用多行運(yùn)算符

• 子查詢的類型

單行子查詢输拇；
多行子查詢摘符；
多列子查詢；

• 多行子查詢

子查詢返回記錄的條數(shù) 可以是一條或多條。

和多行子查詢進(jìn)行比較時逛裤，需要使用多行操作符蠢古，多行操作符包括：

• IN
• ANY

ANY的使用

ANY：表示和子查詢的任意一行結(jié)果進(jìn)行比較，有一個滿足條件即可别凹。

• < ANY：表示小于子查詢結(jié)果集中的任意一個，即小于最大值就可以胁后。

• ‘> ANY’：表示大于子查詢結(jié)果集中的任意一個莹痢，即大于最小值就可以他嚷。

• = ANY：表示等于子查詢結(jié)果中的任意一個，即等于誰都可以拍霜，相當(dāng)于IN。

• ALL

ALL的使用

ALL：表示和子查詢的所有行結(jié)果進(jìn)行比較薪介，每一行必須都滿足條件祠饺。

• < ALL:表示小于子查詢結(jié)果集中的所有行，即小于最小值汁政。

• ‘> ALL:表示大于子查詢結(jié)果集中的所有行道偷，即大于最大值。

• = ALL :表示等于子查詢結(jié)果集中的所有行,即等于所有值记劈，通常無意義勺鸦。

--

• 多列子查詢

之前講的子查詢都是在一個條件表達(dá)式內(nèi)和子查詢的一個列進(jìn)行比較，多列子查詢可以在一個條件表達(dá)式內(nèi)同時和子查詢的多個列進(jìn)行比較目木。

多列子查詢通常用IN操作符完成换途。

• ROWNUM

ROWNUM是一個偽列，偽列是使用上類似于表中的列刽射，而實(shí)際并沒有存儲在表中的特殊列军拟；

ROWNUM的功能是在每次查詢時，返回結(jié)果集的順序號誓禁，這個順序號是在記錄輸出時才一步一步產(chǎn)生的懈息，第一行顯示為1，第二行為2现横，以此類推

• 分頁查詢

1477122929348.png

集合運(yùn)算

集合運(yùn)算：是用來把兩個或多個查詢的結(jié)果集做并漓拾、交、差的集合運(yùn)算戒祠，包含集合運(yùn)算的查詢稱為復(fù)合查詢

1477123042522.png

• 聯(lián)合(UNION)運(yùn)算

1477123142755.png

返回由任一查詢結(jié)果集包含的行骇两，并且去除重復(fù)行,并且按照查詢結(jié)果集的第一列升序排序。

使用原則：

1姜盈、多個被聯(lián)合的查詢語句所選擇的列數(shù)和列的數(shù)據(jù)類型必須一致低千，列的名字不必相同。

2、如果多個查詢結(jié)果都有NULL值示血，整個結(jié)果中只包含一個NULL值棋傍。

3、每個查詢不能包含自己的Order by子句难审，只能在聯(lián)合之后使用Order by子句瘫拣。

• 完全聯(lián)合 (UNION ALL) 運(yùn)算

1477123214134.png

返回由任一查詢結(jié)果集包含的行，并且包含重復(fù)行告喊，默認(rèn)情況下不對結(jié)果集進(jìn)行排序 麸拄。

使用原則：

1、多個被聯(lián)合的查詢語句所選擇的列數(shù)和列的數(shù)據(jù)類型必須一致黔姜，列的名字不必相同拢切。
2、如果多個查詢結(jié)果都有NULL值秆吵，不被去掉淮椰。
3、每個查詢不能包含自己的Order by子句纳寂，只能在聯(lián)合之后使用Order by子句主穗。

注意：使用UNION ALL會比UNION的速度快，因?yàn)槭∪チ巳サ糁貜?fù)記錄和排序的時間毙芜。

• 相交運(yùn)算

返回多個查詢結(jié)果集的公有行黔牵。

使用原則

1、多個查詢語句所選擇的列數(shù)和列的數(shù)據(jù)類型必須一致爷肝，列的名字不必相同猾浦。
2、相交運(yùn)算不忽略空值灯抛。

• 相減運(yùn)算

1477123354054.png

返回第一個查詢中存在 而第二個查詢中不存在的行記錄金赦。

使用原則

1、多個查詢語句所選擇的列數(shù)和列的數(shù)據(jù)類型必須一致对嚼，列的名字不必相同夹抗。
2、相減運(yùn)算不忽略空值纵竖。

• 相關(guān)子查詢

1477123533067.png

父查詢中的行每被處理一次漠烧，子查詢就執(zhí)行一次

• EXISTS和NOT EXISTS操作符

相關(guān)子查詢還可使用EXISTS和 NOT EXISTS操作符來進(jìn)行操作

EXISTS判斷是否“存在”，具體操作如下：

子查詢中如果有記錄找到靡砌，子查詢語句不會繼續(xù)執(zhí)行已脓，返回值為TRUE；

子查詢中如果到表的末尾也沒有記錄找到通殃，返回值為FALSE度液。

EXISTS 子查詢并沒有確切記錄返回，只判斷是否有記錄存在，而且只要找到相關(guān)記錄堕担，子查詢就不需要再執(zhí)行已慢，然后再進(jìn)行下面的操作。這樣大大提高了語句的執(zhí)行效率霹购。

NOT EXISTS正好相反佑惠，判斷子查詢是否沒有返回值。如果沒有返回值齐疙，表達(dá)式為真兢仰，如果找到一條返回值，則為假剂碴。

數(shù)據(jù)操作

Data Manipulation Language ,簡稱DML，主要用來實(shí)現(xiàn)對數(shù)據(jù)庫表中的數(shù)據(jù)進(jìn)行操作轻专。

數(shù)據(jù)操作語言主要包括如下幾種：

增加行數(shù)據(jù)：使用INSERT語句實(shí)現(xiàn)
修改行數(shù)據(jù)：使用UPDATE語句實(shí)現(xiàn)
刪除行數(shù)據(jù)：使用DELETE語句實(shí)現(xiàn)
合并行數(shù)據(jù)：按照指定條件合并兩個表的數(shù)據(jù)忆矛，使用MERGE語句實(shí)現(xiàn)

• 插入數(shù)據(jù)

插入單行數(shù)據(jù)

使用INSERT語句向表中插入數(shù)據(jù)，語法結(jié)構(gòu)如下

1477124378123.png

采用這種語法一次只能追加一條記錄请垛；
列名列表和值列表必須在個數(shù)及數(shù)據(jù)類型上保持一致催训；
列名列表部分可以省略，如果省略宗收，默認(rèn)包括該表的所有列漫拭；
注意值列表必須和列名列表對應(yīng)；
字符和日期型數(shù)據(jù)必須要用單引號括起來混稽。

插入多行數(shù)據(jù)

1477124486871.png

不必書寫values子句采驻；
INSERT子句和數(shù)據(jù)類型必須和子查詢中列的數(shù)量和類型相匹配中列的數(shù)量；

• 修改數(shù)據(jù)

修改數(shù)據(jù)使用UPDATE子句完成匈勋，語法結(jié)構(gòu)如下

1477124562024.png

WHERE子句用來限定修改哪些行礼旅。
SET子句用來限定修改哪些列

相關(guān)UPDATE

用一個相關(guān)子查詢來更新在一個表中的行，該表中的行基于另一個表中的行

1477124653362.png

• 刪除數(shù)據(jù)

使用 DELETE 語句刪除表中滿足條件的行記錄

1477124706136.png

相關(guān)DELETE

1477124764861.png

• Oracle-SQL系列知識點(diǎn)(一)
• Oracle-SQL系列知識點(diǎn)(三)
• PL/SQL基礎(chǔ)知識詳解