Oracle-SQL

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

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

多表連接

• 1、什么是連接

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

1477117179356.png

在 WHERE子句中書(shū)寫(xiě)連接條件。
如果在多個(gè)表中出現(xiàn)相同的列名，則需要使用表名作為來(lái)自該表的列名的前綴。
N個(gè)表相連時(shí)，至少需要N－1個(gè)連接條件

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

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

• 連接條件被省略
• 連接條件是無(wú)效的

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

• 3、 限制歧義列名

在用到多個(gè)表時(shí)可以使用表名作前綴來(lái)限定列；

通過(guò)使用表前綴可以提高性能；

通過(guò)使用列的別名可以區(qū)分來(lái)自不同表但是名字相同的列；

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

1477120390478.png

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

1477120416865.png

6、外部連接

在多表連接時(shí)，可以使用外部連接來(lái)查看哪些行,按照連接條件沒(méi)有被匹配上。

外部連接的符號(hào)是 (+)，語(yǔ)法包括：

1477120464641.png

1477120472009.png

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

• 7、自連接

自身連接，也叫自連接，是一個(gè)表通過(guò)某種條件和本身進(jìn)行連接的一種方式，就如同多個(gè)表連接一樣。

1477120575678.png

• 8、SQL:1999語(yǔ)法的連接

• 交叉連接

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

使用CROSS JOIN 子句完成。

1477120745841.png

• 自然連接

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

如果兩個(gè)表之間相同名稱(chēng)的列的數(shù)據(jù)類(lèi)型不同，則會(huì)產(chǎn)生錯(cuò)誤；

使用NATURAL JOIN子句來(lái)完成；

1477120758631.png

• USING子句

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

1477120815300.png

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

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

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

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

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

• ON子句

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

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

用ON將連接條件和其它檢索條件分隔開(kāi)，其它檢索條件寫(xiě)在WHERE子句；

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

1477120972480.png

1477120988643.png

• 左外連接

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

1477121046649.png

• 右外連接

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

1477121086219.png

• 全外連接

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

1477121109510.png

分組函數(shù)

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

分組函數(shù)是對(duì)數(shù)據(jù)行的集合進(jìn)行操作并按組給出一個(gè)結(jié)果，這個(gè)結(jié)果可直接輸出，或者用來(lái)做判斷條件。

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

1477121627533.png

• 分組函數(shù)

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

分組函數(shù)常用到以下五個(gè)函數(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ù)類(lèi)型

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

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

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

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

• COUNT函數(shù)

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

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

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

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

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

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

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

1477121955949.png

通過(guò) GROUP BY 子句可將表中滿(mǎn)足WHERE條件的記錄按照指定的列劃分成若干個(gè)小組，其中GROUP BY子句指定要分組的列

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

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

使用 HAVING 子句限制組

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

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

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

• SELECT語(yǔ)句執(zhí)行過(guò)程

1477122124139.png

1477122182122.png

------>>>

1477122207610.png

------>>>

1477122218458.png

1477122230438.png

1477122240872.png

------>>>

1477122249240.png

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

子查詢(xún)

• 使用子查詢(xún)

1477122409227.png

• 括號(hào)內(nèi)的查詢(xún)叫做子查詢(xún)，也叫內(nèi)部查詢(xún)，先于主查詢(xún)執(zhí)行。
• 子查詢(xún)的結(jié)果被主查詢(xún)（外部查詢(xún)）使用
• expr operator包括比較運(yùn)算符。
  • 單行運(yùn)算符：>、=、>=、<、<>、<=
  • 多行運(yùn)算符： IN、ANY、ALL

子查詢(xún)可以嵌于以下SQL子句中：

• WHERE子句

• HAVING子句

• FROM子句

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

  • 子查詢(xún)要用括號(hào)括起來(lái)
  • 將子查詢(xún)放在比較運(yùn)算符的右邊
  • 對(duì)于單行子查詢(xún)要使用單行運(yùn)算符
  • 對(duì)于多行子查詢(xún)要使用多行運(yùn)算符

• 子查詢(xún)的類(lèi)型

單行子查詢(xún)；
多行子查詢(xún)；
多列子查詢(xún)；

• 多行子查詢(xún)

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

和多行子查詢(xún)進(jìn)行比較時(shí)，需要使用多行操作符，多行操作符包括：

• IN
• ANY

ANY的使用

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

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

• ‘> ANY’：表示大于子查詢(xún)結(jié)果集中的任意一個(gè)，即大于最小值就可以。

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

• ALL

ALL的使用

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

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

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

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

--

• 多列子查詢(xún)

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

多列子查詢(xún)通常用IN操作符完成。

• ROWNUM

ROWNUM是一個(gè)偽列，偽列是使用上類(lèi)似于表中的列，而實(shí)際并沒(méi)有存儲(chǔ)在表中的特殊列；

ROWNUM的功能是在每次查詢(xún)時(shí)，返回結(jié)果集的順序號(hào)，這個(gè)順序號(hào)是在記錄輸出時(shí)才一步一步產(chǎn)生的，第一行顯示為1，第二行為2，以此類(lèi)推

• 分頁(yè)查詢(xún)

1477122929348.png

集合運(yùn)算

集合運(yùn)算：是用來(lái)把兩個(gè)或多個(gè)查詢(xún)的結(jié)果集做并、交、差的集合運(yùn)算，包含集合運(yùn)算的查詢(xún)稱(chēng)為復(fù)合查詢(xún)

1477123042522.png

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

1477123142755.png

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

使用原則：

1、多個(gè)被聯(lián)合的查詢(xún)語(yǔ)句所選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須一致，列的名字不必相同。

2、如果多個(gè)查詢(xún)結(jié)果都有NULL值，整個(gè)結(jié)果中只包含一個(gè)NULL值。

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

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

1477123214134.png

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

使用原則：

1、多個(gè)被聯(lián)合的查詢(xún)語(yǔ)句所選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須一致，列的名字不必相同。
2、如果多個(gè)查詢(xún)結(jié)果都有NULL值，不被去掉。
3、每個(gè)查詢(xún)不能包含自己的Order by子句，只能在聯(lián)合之后使用Order by子句。

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

• 相交運(yùn)算

返回多個(gè)查詢(xún)結(jié)果集的公有行。

使用原則

1、多個(gè)查詢(xún)語(yǔ)句所選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須一致，列的名字不必相同。
2、相交運(yùn)算不忽略空值。

• 相減運(yùn)算

1477123354054.png

返回第一個(gè)查詢(xún)中存在 而第二個(gè)查詢(xún)中不存在的行記錄。

使用原則

1、多個(gè)查詢(xún)語(yǔ)句所選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須一致，列的名字不必相同。
2、相減運(yùn)算不忽略空值。

• 相關(guān)子查詢(xún)

1477123533067.png

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

• EXISTS和NOT EXISTS操作符

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

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

子查詢(xún)中如果有記錄找到，子查詢(xún)語(yǔ)句不會(huì)繼續(xù)執(zhí)行，返回值為T(mén)RUE；

子查詢(xún)中如果到表的末尾也沒(méi)有記錄找到，返回值為FALSE。

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

NOT EXISTS正好相反，判斷子查詢(xún)是否沒(méi)有返回值。如果沒(méi)有返回值，表達(dá)式為真，如果找到一條返回值，則為假。

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

Data Manipulation Language ,簡(jiǎn)稱(chēng)DML，主要用來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)表中的數(shù)據(jù)進(jìn)行操作。

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

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

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

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

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

1477124378123.png

采用這種語(yǔ)法一次只能追加一條記錄；
列名列表和值列表必須在個(gè)數(shù)及數(shù)據(jù)類(lèi)型上保持一致；
列名列表部分可以省略，如果省略，默認(rèn)包括該表的所有列；
注意值列表必須和列名列表對(duì)應(yīng)；
字符和日期型數(shù)據(jù)必須要用單引號(hào)括起來(lái)。

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

1477124486871.png

不必書(shū)寫(xiě)values子句；
INSERT子句和數(shù)據(jù)類(lèi)型必須和子查詢(xún)中列的數(shù)量和類(lèi)型相匹配中列的數(shù)量；

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

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

1477124562024.png

WHERE子句用來(lái)限定修改哪些行。
SET子句用來(lái)限定修改哪些列

相關(guān)UPDATE

用一個(gè)相關(guān)子查詢(xún)來(lái)更新在一個(gè)表中的行，該表中的行基于另一個(gè)表中的行

1477124653362.png

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

使用 DELETE 語(yǔ)句刪除表中滿(mǎn)足條件的行記錄

1477124706136.png

相關(guān)DELETE

1477124764861.png

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