1、基本概念

oracle數據庫是一個物理概念，oracle實例是數據庫在內存中的鏡像，屬于一種邏輯概念。

oracle數據庫與oracle實例至少是一對一的關系，也可能是一對多關系（oracle集群：RAC real application cluster）。

集群的優點：

load balance 負載平衡

fail over 失敗遷移

數據文件：是一種物理概念，用來存儲數據用的，后綴為.dbf的文件

表空間：表空間是一種邏輯概念，表空間有多個數據文件組成，數據文件只能屬于一個表空間。

2、基本查詢

命令中oracle的基本操作

清屏

sql

操作數據庫

sql

tab就是當前用戶的數據字典，可以查看當前用戶的表和視圖。

基本SQL select 語句

• 查詢所有員工信息

sql

SQL優化（數據庫的優化）

盡量使用列名，數據庫在解析*號時需要耗時。

盡量不要使用 集合運算 符號。

where 解析的順序問題

• where 解析的順序是從右往左解析；
• 所以當一個子條件為假時，and 符號左側的條件就不再判斷；
• 若Where子句和Having子句中都可以使用的條件，從語句的執行效率來看，最好寫在Where子句中(where 先過濾再分組；having 先分組再過濾)

select查詢語句

• 查詢中的空值null問題

查詢員工的年收入，其中年收入=月薪*12+獎金

select

下面的查詢結果顯然錯誤，沒有獎金的員工居然沒有年薪，獎金為空實際上表示獎金為零。

SQL中的null值問題：

空值是無效的，未指定的不可預知的值，空值并不是空格或0；

包含null的表達式都為null；

null值永遠！=null；

如果集合中含有null ，不能使用not in，但可以使用 in；

組函數（多行函數）自動濾空;可以嵌套濾空函數 來屏蔽他的濾空功能；

select

6. null排序 ——>降序排列時：nulls last 語句將空值顯示再查詢結果的最后（oracle中null值最大）

oracle中的通用函數（濾空函數）:nvl(a,b) 與 nvl2(expr1，expr2，expr3)

nvl

? 查詢獎金為null空的員工

-- 該語句無法查詢出獎金為空的員工，因為null！=null

• 列的別名
• 列的別名緊跟在列名之后，也可在列名和別名之間加入關鍵字“AS”；
• 別名使用雙引號，以便在別名中包含空格或特殊字符、數字等；
• AS 關鍵字可以省略；

? 列的別名的區別：

員工號與姓名這兩個列名之間沒有區別

姓名與月薪兩個別名之間有區別，當別名中存在關鍵字、數字或者特殊符號（空格等），必須要添加雙引號。

distinct關鍵字——去除重復記錄

-- 查詢員工的部門號，不同員工屬于同一部門，部門有重復

distinct 關鍵字 作用與select 后的所有列。distinct關鍵字保證所有的列組合起來是不相同的。

• 連接符
  1、 把列與列，列于字符連接起來；2、用“||“表示； 3、可以用來”合成“列

--

可以使用concat()函數將兩個字符串連接起來,oracle中提供dual偽表供使用

select

偽表：dual表 dual表在Oracle中是非常特殊的一張表，它存在的意義就是來滿足sql99的語法格式（select后必須要有from子句存在）

字符和日期注意：

日期和字符只能在單引號中出現，即單引號中出現的可以是字符也可以是日期；

雙引號中出現的是列的別名；

默認的日期格式：DD-Mon-RR

-- 查看日期格式

3、過濾和排序

• 比較運算符

注意：

% 表示：任意長度的任意字符串；

“_”表示：任意的一個字符；

模糊查詢中，可以使用 escape 標識符 選擇“%”“_”符號（即escape起到了 轉義字符的功能）

-- 查詢薪水在1000~2000之間的員工

• 邏輯運算符

注意：

-- 下面兩條sql語句是否一樣？ 這兩條語句完全不同，僅僅是這兩條sql語句執行結果一致

• 排序

order by 列名/表達式/別名/序號 排序方式（asc 升序 ；desc 降序）

-- 查詢員工信息，按照月薪排序

2、order by 多個列的排序

-- 查詢員工信息，按照月薪排序

desc 只作用于離他最近的列

select

order by 子句應當再where子句之后

參考資料：

SQL語句的語法順序和執行順序_tree_ifconfig的博客-CSDN博客_sql語句順序?blog.csdn.net

4、單行函數

定義：操作數據對象，接受參數并返回一個結果，僅對一行進行變換，每行返回一個結果

• 字符函數

1、lower()函數、upper()函數與initcap()函數

-- 字符大小寫轉換

2、substr()求字串函數

-- substr(a,b) 從a串中，第b位置為開始取后面所有字符

3、instr()查找字符串函數

-- instr(a,b) 在a字符串中，查找b字符串，如果找到，返回下標，如果沒找到返回0

4、length()字符數 lenghthb()字節數

sql

5、lpad()左填充 rpad()右填充

-- lpad("被填充的字串",填充幾位,"用什么字符填充")

6、trim()函數

-- trim("被去除字符"from"字符串")去除前后指定的字符

7、replace()函數

-- replace('字符串','待替換字符','替換字符')替換函數

• 數字函數

1、四舍五入 round()

select

2、截斷函數 trunc()

select

3、注意

round()與trunc()函數不僅僅是數字函數，也可以對日期進行操作

• 日期

oracle中的日期類型只有date類型，不同于mysql中有date與datetime兩種。在oracle中date=日期+時間

-- 查詢當前時間

兩個日期相減返回日期之間相差的天數

-- 時間差

• 日期處理函數

• 數據轉換函數

1、 隱式數據類型轉換

oracle數據庫自動完成數據類型間的轉換

2、顯示數據類型轉換

-- to_char(date,'格式')

to_char()函數常用的數字格式

• 通用函數

通用函數中參數適用于任何類型數據，同時也適用于空值null

• 條件表達式

在sql中完成if-then-else的邏輯

-- 不同級別員工漲不同的錢

select

5、分組函數

分組函數作用于一組函數，并對一組數據返回一個值。

組函數（多行函數）自動濾空

• 常用的組函數

-- sum()函數 求和函數

統計函數count(*) ，統計不重復的次數時使用distinct關鍵字

select

• 分組數據（group by 子句）

--求每個部門的平均工資

將上面問題抽象化

select

oracle中的規定，所有包含于select列表中，而未包含于組函數中的列，都必須在group by語句中

group by 語句中出現的列，未必要在select語句中出現

select

• 多個列的分組

-- 按照不同部門的不同工種進行分組，計算工資

• 過濾分組數據 having 子句
  1、行已經被分組

2、使用了組函數

3、滿足having子句中條件的分組將被顯示

-- 求出平均工資大于2000的部門

having 子句與where 子句的理解：

1、where后面不能使用多行函數（聚合函數，例如max、min等）

2、where 與having 的執行順序不同：

where的搜索條件是在執行分組之前應用；

having的搜索條件是在分組條件后執行的；

如果where和having一起用時，where會先執行，having后執行；

3、Where關鍵字作用是過濾，選取符合條件的記錄，而Having關鍵字作用則是為聚合結果指定條件!4、Where 子句是用來指定 "行" 的條件的，而Having 子句是指定 “組” 的條件

Where 子句 = 指定行所對應的條件

Having 子句 = 指定組所對應的條件

5、若Where子句和Having子句中都可以使用的條件，從語句的執行效率來看，最好寫在Where子句中。（where 先過濾再分組；having 先分組再過濾）

where 與 having 可以通用的情況：

-- 查詢10號部門的平均工資

• group by 語句 增強

-- 按照部門統計各部門不同工種的工資情況

6、多表查詢

• 笛卡爾積問題

在多表連接查詢問題中，連接查詢會產生笛卡爾積的問題，產生很多錯誤的數據記錄。笛卡爾積就是求出多個表的乘積，例如 emp 表與 dept 表連接，那么笛卡爾積就是 emp*dept。

上圖中紅框中的記錄數據明顯是由笛卡爾積計算而產生的錯誤數據。 那么多表查詢產生這樣的結果并不是我們想要的，那么怎么去除重復的，不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關系，那么就通過關聯關系去除笛卡爾積。 設置表與表之間的連接條件，將多張表連接起來。

-- 連接條件

• 等值連接

通過連接條件對多張表的笛卡爾積進行過濾

--查詢員工信息： 員工號 姓名 月薪 部門名稱

注意：

在where子句中寫入連接條件，

在表中有相同名稱的列，需要在列名之前加上前綴，以區別是那一張表的列。

連接n張表，至少需要n-1個連接條件（例如連接3張表，就需要2個連接條件）。

多個連接條件需要使用 and 關鍵字加以合并。

• 不等值連接

連接多張表的連接條件并不是 等值 條件

--查詢員工信息： 員工號 姓名 月薪 工資級別

• 外連接 ([outer] join on)

參考資料:

Oracle的連接詳解（左連接、右連接、全連接...） - 無時無刻 - 博客園?www.cnblogs.comOracle連接查詢，徹底搞懂外連接（左外連接&右外連接）_ljinxin的博客-CSDN博客_oracle左連接查詢?blog.csdn.netMySQL數據庫的內連接，左外連接和右外連接查詢_SpiderLi的博客-CSDN博客_數據庫內連接查詢示例?blog.csdn.netOracle中的連接查詢講解?www.jianshu.com

外連接結果是內連接結果的超集

在左連接和右連接時都會以一張表作為基表，該表的內容會全部的顯示，然后加上兩張表匹配的內容。

下面例子的SQL明顯錯誤，由于emp表中不包含40號部門（dept表中卻存在），所以在做連接查詢的時候，40號部門并不能滿足 “where e.deptno=d.deptno” 條件，所以等值連接查詢無法查出該部門

-- 按部門統計員工人數：部門號 部門名稱 人數

1、左外連接 (left [outer] join on)

• 結果包括左表（出現在JOIN子句最左邊）中的所有行，不包括右表中的不匹配行
• 對于外連接，在Oracle中可以使用(+)來表示
• On子句：外連接的表之間連接條件 不使用WHERE，而是使用ON。將連接的條件放在on子句中，而且每個on子句只能指定一個連接條件。如果需要連接N個表，則需要有N-1個join-on對。
• 左連接是先查詢出左表（即以左表為主），然后查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。
• 希望： 對于某些不成立的記錄，任然希望包含在最后的結果中 左外連接：當where e.deptno=d.deptno不成立的時候，等號左邊的表任然被包含 寫法:where d.deptno=e.deptno(+) 右外連接：當where e.deptno=d.deptno不成立的時候，等號右邊的表任然被包含 寫法: where e.deptno(+)=d.deptno

-- 按部門統計員工人數：部門號 部門名稱 人數

2、 右邊外連接 (right [outer] join on)結果包括右表（出現在JOIN子句最右邊）中的所有行，不包括左表中的不匹配行

-- 按部門統計員工人數：部門號 部門名稱 人數

3、全外連接（full [outer] join）

結果包括左、右兩個表的所有行，不論他們是否匹配（即不管在另一邊表中是否存在于他們匹配的行）
左外連接：返回包括左表中的所有記錄和右表中聯結字段相等的記錄；
右外連接：返回包括右表中的所有記錄和左表中聯結字段相等的記錄；
全外連接 ：查詢結果等于左外連接和右外連接的和

-- 按部門統計員工人數：部門號 部門名稱 人數

• 內連接（[inner] join on）

外連接結果是內連接結果的超集

內聯接，結果為兩個聯接表中的匹配行的聯接 （即 只連接兩張表的匹配行）

inner join連接作為一種等值連接，其操作相當于where中的等值連接。

等值連接 的語法并不是標準的SQL99語法,屬于數據庫的方言, inner join 是SQL99標準的實現方式

--查詢員工信息： 員工號 姓名 月薪 部門名稱

• 自然連接（natural join）

連接查詢會產生無用笛卡爾積，我們通常使用主外鍵關系等式(等值連接條件)來去除它。而自然連接無需你去給出主外鍵等式，它會自動找到這一等式

自然連接是在廣義笛卡爾積R×S中選出同名屬性上符合相等條件元組，再進行投影，去掉重復的同名屬性，組成新的關系。即自然連接是在兩張表中尋找那些數據類型和列名都相同的字段，然后自動地將他們連接起來，并返回所有符合條件按的結果。例emp和dept表都存在deptno列，并且類型一致，所以會被自然連接找到！

Using子句：用于顯示指定兩個表中的同名列，并將其作為連接條件。假設兩個表中有超過一列的同名列，如果使用自然連接(natural join),則會把所有的同名列當成連接條件，使用using子句，就可以顯示指定那些同名列作為連接條件。 即,using 子句可以在有多個滿足條件的連接列時, 進行自主的選擇

-- 按部門統計員工人數：部門號 部門名稱 人數

自然連接的注意事項：

如果做自然連接的兩個表中有多個字段都滿足有相同名稱和類型，那么他們都會被作為連接的條件。

如果自然兩個表中僅字段名稱相同，但是數據類型不同時，此時會返回一個錯誤。

自然連接不要用表的別名

自連接

通過表的別名，將同一張表視為多張表（即 自己與自己的等值連接）

-- 查詢每個員工對應的老板編號（員工的上司）

自連接操作過程中會產生 笛卡爾積 ，查詢結果即 表的記錄的平方行記錄，故自連接不適合操作大表。

• 層次查詢（connect by 與 start with）

Oracle層次化查詢是Oracle特有的功能實現,主要用于返回一個數據集,這個數據集存在樹的關系(數據集中存在一個Pid記錄著當前數據集某一條記錄的Id)

層次化查詢主要包含兩個子句,一個start with另一個是connect by 。

start with:這個子句一般用于指定層次化查詢的開始節點(也就是樹的最頂級節點),找到最頂級節點,然后按照一定的規則開始查找其剩余的子節點。

connect by:這個子句就是上面所說的規則,用于查找剩余子節點的規則。

-- 查詢每個員工的上司的員工編號

具有層次關系的查詢，可以考慮層次查詢。

根節點的定義比較靈活,但是(connect by)遍歷子節點的規則,比較固定基本都是判斷父節點和子節點的ID的

參考資料：

oracle 層次查詢_qq_38743952的博客-CSDN博客_oracle 地區層次查詢?blog.csdn.net

• 交叉查詢（cross join）

交叉聯接：結果包括兩個聯接表中所有可能的行組合。 即笛卡爾積連接，是兩個或多個表間的無條件連接 。

交叉連接結果是其他連接結果的超集，外連接結果是內連接結果的超集

select

7、集合操作

常見的集合操作共有union與union all并集、intersect交集與minus差集。

• union與union all 并集

union與union all的作用都是合并結果集。 將是把多個select語句的查詢結果合并到一起 。union與union all的區別在于：

• UNION：去除重復記錄，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
• UNION ALL：不去除重復記錄，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

-- 查詢10和20號部門的員工

集合運算注意點：

• 參與運算的各個集合必須列數相同且類型一致，當列數不同時，需要添加列，若類型不同時需要轉換
  列數不足時，補充NULL，并根據需要做相應類型轉換。

-- 按照部門統計各部門不同工種的工資情況

采用第一個集合作為最后的表頭。

order by 永遠在最后。

括號可以集合運算的先后順序。

• intersect 交集

intersect 運算返回同時屬于兩個集合的記錄（圖中黃色部分）。

-- 顯示薪水同時位于級別1（700~1300）和 級別2（1201~1400）的員工信息

• minus 差集

minus返回屬于第一個集合，但不屬于第二個集合的記錄

-- 顯示薪水同時位于級別1 （700~1300），但不屬于級別2（1201~1400）的員工

• Mysql數據庫中的集合運算

參考資料：

MySQL 并集、交集、差集_三中故人的學習筆記-CSDN博客_mysql 并集?blog.csdn.net

mysql數據庫中只有union與union all結果集合并運算符，而沒有intersect（交集）與minus（差集）運算符。但是可以通過表之間的內外連接等運算達到同樣的效果。

現在有如下兩張表

表object_a

表object_b

• 交集（實現intersect）

-- 使用 內連接 inner join 實現 交集運算

• 差集（實現minus）

-- 使用 左外連接 實現差集運算 （獲取a - b 表）

• 補集

-- 使用 左外連接 實現補集運算

8、子查詢

To be continued......

總結

以上是生活随笔為你收集整理的oracle 会话数上不去_Oracle初识的全部內容，希望文章能夠幫你解決所遇到的問題。

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