Oracle資料庫從入門到精通 多表查詢知識以及範例

来源:https://www.cnblogs.com/launolife/archive/2018/07/14/9308065.html
-Advertisement-
Play Games

視頻課程：李興華 Oracle從入門到精通視頻課程 學習者：陽光羅諾 視頻來源：51CTO學院 總體內容： 一、認識多表查詢 所謂的多表查詢就是同時從多張表中取出數據並且顯示的一種操作。語法只是做了一些簡單的修改。 語法格式： 於是我們就可以按照這樣子的語法結構來實現多表查詢。本次將會使用emp和d ...


視頻課程：李興華 Oracle從入門到精通視頻課程

學習者：陽光羅諾

視頻來源：51CTO學院

總體內容：

  1. 多表查詢的意義以及基本問題。
  2. 表的連接查詢
  3. SQL：1999語法標準對多表查詢的支持。
  4. 數據的集合操作。

 

一、認識多表查詢

所謂的多表查詢就是同時從多張表中取出數據並且顯示的一種操作。語法只是做了一些簡單的修改。

 

語法格式：

 

1 【③選出所需要的數據列】SELECT [DISTINCT] *  列[別名]，列[別名]，列[別名]······
2 
3 【①確定數據來源（行和列的集合）】FROM 表名稱 [別名]，表名稱 [別名]，······
4 
5 【②篩選數據行】[WHERE 限定條件]  此時的條件可以是多個語法結構。
6 
7 【④數據排序】[ORDER BY 排序欄位 [ASC|DESC] 可以設置多個]

 

於是我們就可以按照這樣子的語法結構來實現多表查詢。本次將會使用emp和dept兩張表格進行多表的查詢。於是在查詢之前，先做一些準備，在資料庫中有一個COUNT()函數，這個函數的主要作用：可以統計出一張數據表中的數據量。

 

準備查詢一：查詢dept表中的數據量。（4行記錄）

代碼示例：

1 select count(*) from dept;

準備查詢二：查詢emp表中的數據量。（14行記錄）

代碼示例：

1 select count(*) from emp;

目前兩張表的記錄加起來，總記錄是18行。根據語法格式可以實現多表的查詢。

 

範例：實現emp與dept的多表查詢。

 

發現在每一行emp表中的記錄出現了4次，而4次是dept表中的數據量，所以最終產生了14行X4行=56行記錄。

 

之所以出現這樣的情況，主要用於資料庫的產生原理有關———數學的集合。在這樣子的集合操作裡面，我們會將這兩個集合（數據表）統一查詢，作為乘法的形式出現。結果一定會產生積————笛卡兒積。在任何情況下要進行的多表查詢中都一定會存在有笛卡兒積的問題。事實上，這些積的產生對用戶而言是沒有實質上的用處，需要想辦法去消除積。

如果要消除積，就必須有關聯欄位。

 

很明顯，現在emp和dept數據表之中存在有關聯欄位（大部分情況下，都習慣將關聯欄位設置為同名）。我們就可以使用關聯欄位來消除笛卡兒積。

 

代碼示例：

1 SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

 

可以得出結論：只要是多表查詢，這多張表之間就一定要存在有關聯關係。沒有關聯關係的表是不可能多表查詢的。

 

但是就目前而言，代碼依然是存在有問題。

此時進行欄位訪問的時候採用的是“表名稱.欄位名稱”，如果是表名稱短的話，一般是沒有什麼問題的。而如果表名稱長了，例如：“yuzhou_yinghexi_diqiu_yazhoudalu_shanghai_ren”，所以在進行多表查詢的時候，強烈建議使用別名。

 

實際上笛卡兒積的存在對於整個程式的影響是相當巨大的，即使現在可以消除顯示的笛卡兒積，但是從本質上來講，永遠無法避免笛卡兒積。

 

例如：在Oracle的樣本數據中有sh的大數據用戶。

 

分析一：取得costs表中的記錄數。

SELECT COUNT(*) FROM costs;

 

 

 

多表查詢操作案例（分析過程）

 

範例：要求查詢出每一個雇員的編號、姓名、職位、基本工資、部門名稱、部門位置。

         ·確定要使用的數據表

                  - emp表：雇員的編號、姓名、職位、基本工資

                  - dept表：部門名稱、部門位置

 

         ·確定已知的關聯欄位

-        雇員與部門關聯：emp.deptno = dept.deptno

 

第一步：查詢出每一個雇員的編號、姓名、職位、基本工資。現在只需要使用emp一張數據即可。

代碼示例：

1 select e.empno,e.ename,e.job,e.sal from emp e;

第二步：查詢出每一個雇員對應的部門信息。需要引入dept表（引入表的時候一定要考慮有關聯），這兩張表直接可以利用dept表deptno欄位關聯，所以需要利用WHERE子句來消除笛卡兒積。

代碼示例：

1 select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno;

         以上的操作屬於之前基本概念的加強，並且給出了明確的關聯欄位。可是很多的查詢是不會明確給出關聯欄位的。

 

範例：要求查詢出每一個雇員的編號、姓名、職位、基本工資、工資等級。

 

　　　　　　- 　確定要使用的數據表。

　　　　　　　　　　emp表：雇員的編號、姓名、職位、基本工資

　　　　　　　　　　salgrade表：工資等級

　　　　　　-  確定已知的關聯欄位。

　　　　　　　　　　雇員與工資等級：emp.sal BETWEEN salgrade.losal AND salgrade.hisal；

 

第一步：查詢出每一個雇員的編號、姓名、職位、基本工資。現在只需要使用emp一張數據即可。

代碼示例：

1 select e.empno,e.ename,e.job,e.sal from emp e;

 

第二步：增加salgrade表，增加了數據表之後，就需要引入WHERE子句來消除掉笛卡兒積。

代碼示例：

1 select e.empno,e.ename,e.job,e.sal,s.grade
2 
3 from emp e,salgrade s
4 
5 WHERE e.sal BETWEEN s.losal AND s.hisal;

 

範例：查詢每一個雇員的編號、姓名、職位、基本工資、部門名稱、工資等級。

　　　　確定所需要的數據表：

　　　　　　- emp表：編號、姓名、職位、基本工資

　　　　　　- dept表：部門名稱

　　　　　　- salgrade表：工資等級

　　　　確定相互關聯的欄位：

　　　　　　- 雇員與部門：emp.deptno = dept.deptno;

　　　　　　- 雇員與工資等級：emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

 

第一步：查詢每一個雇員的編號、姓名、職位、基本工資。

代碼示例：

1 select e.empno,e.ename,e.job,e.sal
2 
3 from emp e;

 

 

 

第二步：加入部門名稱，增加一張表就增加陽光條件就消除笛卡兒積。

代碼示例;

1 select e.empno,e.ename,e.job,e.sal,d.dname
2 
3 from emp e,dept d
4 
5 WHERE e.deptno= d.deptno;

 

第三步：加入工資等級信息，與原始的消除笛卡兒積條件應該同時滿足，所以使用AND 來連接。

示例代碼：

1 select e.empno,e.ename,e.job,e.sal,d.dname
2 
3 from emp e,dept d,salgrade s
4 
5 WHERE e.deptno= d.deptno and e.sal BETWEEN s.losal AND s.hisal;

 

 

 

表的連接

實際上對於兩張數據表進行多表查詢，對於消除笛卡兒積來講主要是依靠連接模式來處理的，而對於表的連接模式在資料庫的定義上，有兩種：

l  內連接：在之前都利用WHERE子句消除了笛卡兒積，這就屬於內連接。

l  外連接：分為三種：左外連接、右外連接、全外連接。

為了方便更好的觀察到連接的區別，現在已經在dept表中提供了一個沒有雇員的部門（40部門），同時在emp表中增加一個沒有部門的雇員。

代碼示例：

觀察1：內連接的實現效果。

 

 

觀察2：使用左（外）連接。希望所有的雇員信息都顯示出來。即使沒有對應的部門。

代碼示例：

1 SELECT e.empno,e.ename,d.deptno,d.dname
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno=d.deptno(+);

此時沒有部門的雇員信息出現了，也就是說左表的數據全部顯示了。

 

觀察3：使用右（外）連接。將所有的部門信息顯示出來。

代碼示例：

1 SELECT e.empno, e.ename, d.deptno, d.dname
2 
3 FROM emp e, dept d
4 
5 WHERE e.deptno(+) = d.deptno;

 

內連接就是所有滿足關聯關係的數據出現，不滿足的不出現，外連接就是指定一張數據表中的內容全部都顯示，但是沒有對應的其他的表的數據內容位null。

         在Oracle中使用了“(+)”來控制連接方式。

                          左外連接：關聯欄位1=關聯欄位2(+)；

                          右外連接：關聯欄位1(+)=關聯欄位2；

 

大部分情況下一般都只會考慮內連接，但是當你發現所需要的數據不全的時候就可以考慮外連接。、

範例：查詢每一個雇員的編號、姓名、職位、領導姓名、領導職位。

　　　　確定所需要的數據表。

　　　　　　　　- emp表（雇員信息）：編號、姓名、職位。

　　　　　　　　- emp表（領導信息）：領導姓名、領導職位。

　　　　確定已知的關聯欄位。

　　　　　　　　-  雇員與領導：emp.mgr = memp.empno

 

第一步：查詢出每一個雇員的編號、姓名、職位。

代碼示例：

1 SELECT e.empno,e.ename,e.job FROM emp e;

 

 

第二步：加入領導信息。需要引入自身關聯，而後消除笛卡兒積。

代碼示例：

1 SELECT e.empno,e.ename,e.job,m.ename,m.job
2 
3 FROM emp e, emp m
4 
5 WHERE e.mgr=m.empno;

 

第三步：發現emp表（雇員信息）數據不完整，因為不滿足於等值關聯判斷，所以要讓雇員信息顯示完整，則必須使用外連接控制。

 

 

SQL：1999語法定義

         對於數據表的連接操作，從實際使用來講各個資料庫都是有所支持的，所以對於所有的資料庫，進行表連接最好的做法是利用以下的語法可以完成：

 

語法：

 1 SELECT [DISTINCT] * | 列 [別名] ，列 [別名]，······
 2 
 3 FROM 表1 [別名]
 4 
 5          [CROSS JOIN 表2 [別名]]
 6 
 7          [NATURE JOIN 表2 [別名]]
 8 
 9          [JOIN 表2 [別名] ON （條件）| USING（關聯欄位）]
10 
11          [LEFT | RIGHT | FULL OUTER JOIN ON（條件）表2]；

 

個人在進行表連接的時候，如果是內連接一定使用等值判斷，如果是外連接才會使用LEFT、RIGHT、OUTER等操作。

 

  1. 交叉連接：目的是產生笛卡兒積

語法：

1 SELECT [DISTINCT] * | 列 [別名] ，列 [別名]，······
2 
3 FROM 表1 [別名]  [CROSS JOIN 表2 [別名]]

 

 

範例：實現交叉連接

代碼示例：SELECT  * FROM emp CROSS JOIN dept;

 

  1. 自然連接：利用關聯欄位自己進行消除笛卡兒積（只要欄位名稱相同即可）

語法：

1 SELECT [DISTINCT] * | 列 [別名] ，列 [別名]，······
2 
3 FROM 表1 [別名]  [NATURE JOIN 表2 [別名]]

 

 

範例：實現自然連接。（內連接）

代碼示例：

1 SELECT  * FROM emp NATURAL JOIN dept;

 

 

  1. 使用自然連接是要求兩張表的欄位名稱相同，但是如果說有一天不相同了呢？或者兩張表中中有兩組欄位都是重名的。所以這種使用ON子句指定關聯條件，而利用USING子句設置關聯欄位。

 

範例：利用USING子句設置關聯欄位實現自然連接。

1 SELECT  * FROM emp  JOIN dept USING(deptno);

 

範例：利用ON子句設置關聯條件。

1 SELECT  * FROM emp e JOIN dept d ON(e.deptno=d.deptno);

外連接 

1 SELECT [DISTINCT] * | 列 [別名] ，列 [別名]，······
2 
3 FROM 表1 [別名]  [LEFT | RIGHT | FULL OUTER JOIN ON（條件）表2]；

 

範例：觀察左外連接 

1 SELECT  * FROM emp e LEFT OUTER JOIN dept d  ON (e.deptno=d.deptno);

 

範例：觀察右外連接。

1 SELECT  * FROM emp e RIGHT OUTER JOIN dept d  ON (e.deptno=d.deptno);

 

範例：全外連接

代碼示例：

1 SELECT  * FROM emp e FULL OUTER JOIN dept d  ON (e.deptno=d.deptno);

 部分截圖：

 

 

數據集合操作

數學集合：交集、並集、補集。

每一次查詢實際上都會返回數據集合，所以返回的結果可以使用UNION、UNION ALL、MINUS、INTERSECT來實現集合的操作。

 

 1 SELECT [DISTINCT] *  列[別名]，列[別名]，列[別名]······
 2 
 3 FROM 表名稱 [別名]，表名稱 [別名]，······
 4 
 5 [WHERE 限定條件]  此時的條件可以是多個語法結構。
 6 
 7 [ORDER BY 排序欄位 [ASC|DESC] 可以設置多個]
 8 
 9          UNION | UNION ALL | INTERSECT | MINUS
10 
11  
12 
13 SELECT [DISTINCT] *  列[別名]，列[別名]，列[別名]······
14 
15 FROM 表名稱 [別名]，表名稱 [別名]，······
16 
17 [WHERE 限定條件]  此時的條件可以是多個語法結構。
18 
19 [ORDER BY 排序欄位 [ASC|DESC] 可以設置多個]

 

  


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用 Moq 測試.NET Core 應用 - Why Moq?
    什麼是Mock 當對代碼進行測試的時候, 我們經常需要用到一些模擬(mock)技術. 綠色的是需要被測試的類, 黃色是它的依賴項, 灰色的無關的類 在一個項目里, 我們經常需要把某一部分程式獨立出來以便我們可以對這部分進行測試. 這就要求我們不要考慮項目其餘部分的複雜性, 我們只想關註需要被測試的那 ...
  • try、catch、finally詳解，你不知道的異常處理
    介紹 不管是新手還是工作幾年的老油條，對try｛｝catch｛｝來說是不陌生的。他可以來幫助我們獲取異常信息，在try中的代碼出現錯誤，火災catch代碼塊中被捕獲到。官方也給了詳細的解釋：。 拋出異常時，公共語言運行庫（CLR）會查找catch處理此異常的語句。如果當前正在執行的方法不包含這樣的c ...
  • late_initcall和module_init的區別
    在 中有如下定義： 詳情參照： "linux 設備驅動載入的先後順序" 所有的__init函數在區段.init.text區段中，同時還在.initcall.init中還保存了一份函數指針，在初始化時內核會通過這些函數指針調用這些__init函數指針，併在整個初始化完成後，釋放整個init區段（包括. ...
  • 字元設備驅動之常用內核函數（一）
    字元設備驅動含有open、read、write、ioctl等函數，用於用戶層和內核之間的通信，所以當用戶要獲得內核驅動的一些數據或者發送一些控制命令，就需要使用設備驅動了。對於一些中斷類型的驅動，比如輸入子系統，用戶層不需要對其進行操作，可以不使用設備驅動。 ...
  • 怎麼從Linux伺服器上下載超過4G的文件？
    使用sz命令下載文件時，超過4G下載不了，如何下載呢？ 本文介紹的方法是先對該文件進行拆分，拆分成多個小於4G的文件，然後分別下載，下載到本地後再進行合併或直接解壓，具體操作如下： 1.分拆為多個文件的命令： 命令解釋： workspace_20150624230059.tar.gz 是待下載的21 ...
  • 如何吧linux純命令行界面變為圖形界面
    使用版本為6.x 1， 連接網路 沒網路就下載不了哦 2，設置下yum源，我本機原來的yum源是網易的但是我用不了，我設置阿裡雲可以下載， 你們有這問題的可以試試 3， yum groupinstall "Desktop" -y -->安裝基本桌面系統 不過這個文件太大了！！！ 我這個網路。。。 。 ...
  • nginx 配置簡單網站項目（linux下）
    1.新建html2與html3兩個網站項目測試，而html是本身就有，記得到/etc/hosts 添加dns記錄 2.修改nginx.conf文件 3.測試訪問 中間用到一些nginx的命令，就不截圖，可以查看下麵 ****************************************** ...
  • Docker 搭建 ELK 收集並展示 tomcat 日誌
    架構 前端展示 索引搜索 日誌緩存 Elastash redis Waiting for set levelDesc [ERROR] [2018 06 30 17:41:56][com.iba.boss.pubsub.listener.core.ListenerTemplate]BmcLevelDe ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類