Sql Service的藝術（四） SQL多表查詢

来源:https://www.cnblogs.com/liuchenxing/archive/2018/01/16/8287635.html
-Advertisement-
Play Games

表的基本連接 SQL的一個重要特性就是能通過JOIN關鍵詞，從多個交叉表中查詢、分析數據。 連接表的目的 在關係資料庫中，數據表設計的一個重要原則就是要避免冗餘性。 學習本節需要的數據表： CREATE TABLE TEACHER ( ID INT IDENTITY (1,1) PRIMARY KE ...


　　表的基本連接

　　　　SQL的一個重要特性就是能通過JOIN關鍵詞，從多個交叉表中查詢、分析數據。

　　連接表的目的

　　　　在關係資料庫中，數據表設計的一個重要原則就是要避免冗餘性。

    1. 減少了冗餘信息，節省了資料庫存儲空間。
    2. 簡化了數據修改、維護操作。

　　學習本節需要的數據表：

CREATE TABLE TEACHER
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主鍵，自增長
    TNO INT NOT NULL, --教工號
    TNAME CHAR(10) NOT NULL, --教師姓名
    CNO INT NOT NULL, --課程號
    SAL INT, --工資
    DNAME CHAR(10) NOT NULL, --所在系
    TSEX CHAR(2) NOT NULL, --性別
    AGE INT NOT NULL --年齡
)
INSERT INTO dbo.TEACHER VALUES( 1,'王軍',4,400,'數學','男',32)
INSERT INTO dbo.TEACHER VALUES( 2,'李彤',5,6600,'生物','女',54)
INSERT INTO dbo.TEACHER VALUES( 3,'王永軍',1,1000,'電腦','女',45)
INSERT INTO dbo.TEACHER VALUES( 4,'劉曉婧',2,8000,'電腦','女',23)
INSERT INTO dbo.TEACHER VALUES( 5,'高維',8,6000,'電子工程','男',54)
INSERT INTO dbo.TEACHER VALUES( 6,'李偉',7,230,'機械工程','女',23)
INSERT INTO dbo.TEACHER VALUES( 7,'劉輝',3,0,'生物','女',65)
INSERT INTO dbo.TEACHER VALUES( 8,'劉偉',9,500,'電腦','男',23)
INSERT INTO dbo.TEACHER VALUES( 9,'劉靜',12,0,'經濟管理','男',45)
INSERT INTO dbo.TEACHER VALUES( 10,'劉奕鍇',13,70000,'電腦','女',65)
INSERT INTO dbo.TEACHER VALUES( 11,'高維',14,70000,'經濟管理','男',61)

CREATE TABLE COURSE
(
    ID INT IDENTITY (1,1) PRIMARY KEY ,  --主鍵，自增長
    CNO INT NOT NULL, --課程號
    CNAME CHAR(30) NOT NULL, --課程名稱
    CTIME INT NOT NULL, --學時
    SCOUNT INT NOT NULL, --容納人數
    CTEST SMALLDATETIME NOT NULL, --考試時間
)
INSERT INTO dbo.COURSE VALUES( 4,'應用數學基礎',48,120,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 5,'生物工程概論',32,80,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 1,'電腦軟體基礎',32,70,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 2,'電腦硬體基礎',24,80,'2006-6-28')
INSERT INTO dbo.COURSE VALUES( 8,'模擬電路設計',28,90,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 7,'機械設計實踐',48,68,'2006-7-14')
INSERT INTO dbo.COURSE VALUES( 3,'生物化學',32,40,'2006-7-2')
INSERT INTO dbo.COURSE VALUES( 9,'資料庫設計',16,80,'2006-7-1')
INSERT INTO dbo.COURSE VALUES( 6,'設計理論',28,45,'2006-6-30')
INSERT INTO dbo.COURSE VALUES( 10,'電腦入門',25,150,'2006-6-29')
INSERT INTO dbo.COURSE VALUES( 11,'數字電路設計基礎',30,125,'2006-6-20')
CREATE TABLE STUDENT
(
    ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    SNO CHAR(4) NOT NULL,    --學號
    SNAME CHAR(10) NOT NULL, --姓名
    DNAME CHAR(10) NOT NULL, --系
    SSEX CHAR(2) NOT NULL,   --性別
    CNO INT ,                --課程號
    MARK DECIMAL(3,1),       --成績
    TYPE CHAR(4)             --課程類型
)
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',4,82.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',10,70,'選修')
INSERT INTO dbo.STUDENT VALUES('9701','劉建國','管理工程','男',1,78.5,'選修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','環境工程','女',5,63,'必修')
INSERT INTO dbo.STUDENT VALUES('9702','李春','環境工程','女',10,58,'選修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',5,48.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9703','王天','生物','男',2,86,'選修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',4,76,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',1,92,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',2,89,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',9,80,'必修')
INSERT INTO dbo.STUDENT VALUES('9704','李華','電腦','女',8,70,'選修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',8,79,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',1,59,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',11,52,'必修')
INSERT INTO dbo.STUDENT VALUES('9705','孫慶','電子工程','男',6,68,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',13,93,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',12,88.5,'必修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',1,78,'選修')
INSERT INTO dbo.STUDENT VALUES('9706','高偉','機械工程','男',10,76,'選修')
數據表

 

　　１、簡單的二表連接

SELECT TNAME,DNAME,CNAME,CTEST FROM dbo.TEACHER,dbo.COURSE WHERE dbo.TEACHER.CNO=dbo.COURSE.CNO   --查詢老師和系表的數據
　

　　根據以上SQL，可知道執行過程：

    • 系統首先執行FROM子句，這裡FROM子句列出的有兩個表TEACHER和COURSE，DBMS將計算這兩個表的笛卡兒積，列出這兩個表中行的所有可能組合，形成一個中間表。中間表的每條記錄包含了兩個表中的所有行。
    • 而後系統將執行WHERE子句，根據“dbo.TEACHER.CNO=dbo.COURSE.CNO”關係對中間表進行搜索，去除那些不滿足該條件的記錄。
    • 最後系統執行SELECT語句，從執行WHERE子句後得到的中間表的每條記錄中，提取TNAME,DNAME,CNAME,CTEST這4個欄位的信息作為結果表。

　　註意：

　　　　由於兩張表都存在相同的欄位CNO，所以在查詢的時候要指明來自哪張表，如dbo.TEACHER.CNO、dbo.COURSE.CNO，而其他具有重名的欄位，也要進行同樣的處理，否則會報錯。

　　問題：

　　　　表的連接時一句關係WHERE子句來定義的，實際開發中，也一定更要用到關係連接。如果不指明連接關係呢？

    SELECT TNAME,DNAME,CNAME,CTEST FROM dbo.TEACHER,dbo.COURSE
    SELECT COUNT(*) AS 總條數 FROM dbo.TEACHER,dbo.COURSE  --查詢一共有幾條數據
　　　　

 

 　　２、三表查詢（在WHERE子句中進行奪標查詢）

SELECT SNAME,dbo.STUDENT.SNAME,CNAME,CTEST,MARK,dbo.TEACHER.TNAME FROM dbo.TEACHER,dbo.COURSE,dbo.STUDENT WHERE dbo.TEACHER.CNO=dbo.COURSE.CNO AND dbo.COURSE.CNO=dbo.STUDENT.CNO

　　使用別名，S，T，C
SELECT SNAME,S.SNAME,CNAME,CTEST,MARK,TNAME FROM dbo.TEACHER AS T,dbo.COURSE AS C,dbo.STUDENT AS S WHERE T.CNO=C.CNO AND C.CNO=S.CNO
　　

 　　”dbo.TEACHER.CNO=dbo.COURSE.CNO AND dbo.COURSE.CNO=dbo.STUDENT.CNO“，含義為”只有同時存在TEACHER，STUDENT，COURSE“中CNO的信息才會作為結果顯示出來。

　　註意：

　　　　由於學生表和老師表都有欄位”DNAME“，在SELECT子句中查詢DNAME欄位時，我們要指定表名，否則系統會報錯。

　　可見，創建表的基本連接，只要遵守下麵的基本原則即可：

    • FROM子句中應列出所有連接的表的表名。
    • WHERE子句應定義連接的關聯條件。
    • 當列名為多個表共有時，要指明列的所在表，即採用”表名.欄位名“的形式。

 

　　３、採用JOIN關鍵字建立連接

SELECT COLUMN
FROM join_table
JOIN_TYPE join_table
ON (join_condition)

　　　　說明如下：

      • join_table指出參與連接操作的表名。
      • JOIN_TYPE為連接類型，可分為四種：自然連接、內連接、外連接和交叉連接。
        • 自然連接JOIN_TYPE的形式為NATURAL　JOIN
        • 內連接JOIN_TYPE的形式為INNER　JOIN
        • 外連接，又分為左連接，JOIN_TYPE形式為LEFT　OUTER　JOIN或LEFT　JOIN；右連接，JOIN_TYPE的形式為RIGHT　OUTER　JOIN或RIGHT　JOIN；全外連接，JOIN_TYPE的形式為CROSS　JOIN；全外連接，JOIN_TYPE的形式為FULL　OUTER　JOIN或FULL　JOIN。
        • 交叉連接中JOIN_TYPE的形式為CROSS　JOIN。
      • ON（join_condition）子句指出連接條件，由被連接表中的列和比較遠算符、邏輯運算符等構成。

　　

　　４、表的連接類型

　　　　４.１、自連接

SELECT DISTINCT SNO FROM dbo.STUDENT WHERE MARK<60  --查詢不及格學生學號，重覆的去除
SELECT DNAME,MARK,SNAME,CNO FROM dbo.STUDENT WHERE SNO IN('9702','9703','9705') AND MARK <60  --查詢三個學號中不及格學生的信息
　　

 　　　　４.２、內連接（INNER JOIN）

　　　　　　內連接也稱為等同連接，返回的結果集是兩個表中所有相匹配的數據，而捨棄不匹配的數據。查詢的結果表包含的兩源表行，必須滿足ON子句中的搜索條件。

 　　　　　   使用等於號（=）比較被連接列的列值，在查詢結果中列出被連接表中的所有列，包括其中的重覆列。圖給出了典型的等值內連接示意圖。

                     

SELECT s.SNAME,s.DNAME,s.CNO,t.TNAME FROM dbo.STUDENT AS s inner join dbo.TEACHER AS t ON t.CNO = s.CNO  --查詢兩表部分信息

使用不等連接進行查詢　　後面會講到各種運算符　先給官方網站地址：http://www.runoob.com/sqlite/sqlite-operators.html
SELECT s.SNAME,s.DNAME,s.CNO,t.TNAME FROM dbo.STUDENT AS s inner join dbo.TEACHER AS t ON s.DNAME<>t.DNAME AND t.CNO = s.CNO
等價於：
SELECT s.SNAME,s.DNAME,s.CNO,t.TNAME FROM dbo.STUDENT AS s,dbo.TEACHER AS t WHERE s.DNAME<>t.DNAME AND t.CNO = s.CNO
DBMS預設多表查詢按INNER　JOIN來執行，除非指定OUTER　JOIN。
　

 　　　　註意：

　　　　　　INNER　JOIN可以實現奪標查詢，但是一次只能連接兩張表，要連接多表，必須進行多次連接。

 

　　　　　　４.２.１、使用INNER JOIN實現多表連接

－－三張表進行查詢，用兩個INNER　JOIN
SELECT s.SNAME,s.DNAME,c.CNAME,c.CTEST,s.MARK,t.TNAME FROM dbo.TEACHER AS t INNER JOIN dbo.COURSE AS c ON c.CNO = t.CNO INNER JOIN dbo.STUDENT AS s ON s.CNO = t.CNO

 

　　　　４.３、外連接（OUTER　JOIN）

　　　　　　左外連接＝內連接＋左邊表中失配的元組

　　　　　　右外連接＝內連接＋右邊表中失配的元組

　　　　　　全外連接＝內連接＋左邊表中失配的元組＋右邊表中失配的元組

　　　　　　表達式示意圖如：

　　　　　　

SELECT s.SNO,s.SNAME,c.CNO,c.CNAME,c.CTEST,s.MARK FROM dbo.STUDENT AS s LEFT OUTER JOIN dbo.COURSE AS c ON c.CNO = s.CNO
在WHERE子句中用”*=“實現左外連接
SELECT s.SNO,s.SNAME,c.CNO,c.CNAME,c.CTEST,s.MARK FROM dbo.STUDENT AS s，dbo.COURSE AS c WHERE c.CNO ＊= s.CNO
－－－－三種連接方式都一樣，只需要改”LEFT　OUTER　JOIN“為”RIGHT　OUTER　JOIN“和”FULL OUTER JOIN“，
　　　　就不做三個演示了，可以自己對比數據的異同
　

 　　　　

　　　　　4.4、交叉連接（CROSS JOIN）

　　　　　　　除了在FROM子句中使用逗號間隔連接的表外，SQL還支持另一種被稱為交叉連接的操作，它們都返回被連接的兩個表所有數據行的笛卡爾積，返回到的數據行數等於第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數。惟一的不同在於，交叉連接分開列名時，使用CROSS JOIN關鍵字而不是逗號。　　　　　

實際上，下麵兩個表達式是等價的。
SELECT  *  FROM  table1, table2
SELECT  *  FROM  table1  CROSS JOIN  table2

　　　　　　　交叉連接示意圖：

 　　　　　　   

SELECT s.SNO,s.SNAME,c.CNO,s.CNO,c.CNAME,c.CTEST,s.MARK FROM dbo.STUDENT AS s CROSS JOIN dbo.COURSE AS c WHERE s.CNO=c.CNO AND s.MARK>60

　　　　　　註意：

　　　　　　　　在使用CROSS JOIN關鍵字交叉連接表時，因為生成的是兩個表的笛卡爾積，因而不能使用ON關鍵字，只能在WHERE子句中定義搜索條件。

　　　　　　事實上，直接使用CROSS JOIN很少得到想要的結果，但是，正如實例所示，作為查詢的第一步，DBMS通常在FROM子句中，對連接的表進行CROSS JOIN，然後過濾得到的中間表。


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

-Advertisement-
Play Games
更多相關文章
  • 時間序列資料庫概覽
    背景 目前對於時序大數據的存儲和處理往往採用關係型資料庫的方式進行處理，但由於關係型資料庫天生的劣勢導致其無法進行高效的存儲和數據的查詢。時序大數據解決方案通過使用特殊的存儲方式，使得時序大數據可以高效存儲和快速處理海量時序大數據，是解決海量數據處理的一項重要技術。該技術採用特殊數據存儲方式，極大提 ...
  • SQL多表連接查詢（詳細實例）
    本文主要列舉兩張和三張表來講述多表連接查詢。 新建兩張表： 表1：student 截圖如下： 表2：course 截圖如下： （此時這樣建表只是為了演示連接SQL語句，當然實際開發中我們不會這樣建表，實際開發中這兩個表會有自己不同的主鍵。） 一、外連接 外連接可分為：左連接、右連接、完全外連接。 1 ...
  • SQL Server中文排序混亂
    在sql語句中指定了含有中文的列進行排序，但排序結果看起來毫無規則，並不是按照拼音進行排序的檢查了DB的Collation後，發現是SQL_Latin1_General_CP1_CI_AS解決方法： 在sql語句中指定排序列的collation為Chinese_PRC_CI_AS ...
  • sql語句case when then else end 返回一個符合條件的值
    Case具有兩種格式 簡單Case函數和Case搜索函數： 這兩種方式，可以實現相同的功能。簡單Case函數的寫法相對比較簡潔，但是和Case搜索函數相比，功能方面會有些限制，比如寫判斷式。 還有一個需要註意的問題，Case函數只返回第一個符合條件的值，剩下的Case部分將會被自動忽略。 下麵我們來 ...
  • 常用SQL語句集合
    一、數據定義 1、創建新資料庫：CREATE DATABASE database_name2、創建新表：CREATE TABLE table_name （column_name datatype,column_name datatype,...）3、修改數據表： 添加列：ALTER TABLE ta ...
  • 大數據分析中Redis怎麼做到220萬ops
    大數據時代，海量數據分析就像吃飯一樣，成為了我們每天的工作。為了更好的為公司提供運營決策，各種抖機靈甚至異想天開的想法都會緊跟著接踵而來！業務多變，決定了必須每天修改系統，重新跑數據，這就要求極高的海量數據讀取和存儲速度！ 公司每天增加幾億行的業務日誌數據，我們需要從中分析出各種維度的業務畫像。經過 ...
  • lnmp之mysql安裝
    linux 進行mysql安裝 # yum -y install make gcc-c++ cmake bison-devel ncurses-devel // 安裝編譯代碼需要的包 # wget http://www.mysql.com/Downloads/MySQL-5.6/mysql-5.6. ...
  • 資料庫系統的基本概念（第一章）-----數據管理技術的發展史
    1、人工管理階段 2、文件系統階段 3、資料庫階段 資料庫管理技術進入資料庫階段的標誌是20世紀60年代末的三件大事： 資料庫階段的數據管理具有以下特點： 4、高級資料庫階段 高級資料庫階段的主要標誌是20世紀80年代的分散式資料庫系統，90年代的對象資料庫系統以及21世紀的Web資料庫系統 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類