神奇的 SQL 之團結的力量 → JOIN

来源:https://www.cnblogs.com/youzhibing/archive/2019/10/28/11385179.html
-Advertisement-
Play Games

前言 開心一刻 閨蜜家暴富，買了一棟大別野，喊我去吃飯，菜挺豐盛的，筷子有些不給力，銀筷子，好重，我說換個竹子的，閨蜜說，這種銀筷子我家總共才五雙，只有貴賓才能用~我咬著牙享受著貴賓待遇，終於，在第三次夾蝦排滑落盤子時，我爆發了：去它喵的貴賓，我要蝦排……不是……我要竹筷子！ 連接 簡單來說，就是將 ...


前言

　　開心一刻

　 　　閨蜜家暴富，買了一棟大別野，喊我去吃飯，菜挺豐盛的，筷子有些不給力，銀筷子，好重，我說換個竹子的，閨蜜說，這種銀筷子我家總共才五雙，只有貴賓才能用~我咬著牙享受著貴賓待遇，終於，在第三次夾蝦排滑落盤子時，我爆發了：去它喵的貴賓，我要蝦排……不是……我要竹筷子！

連接

　　簡單來說，就是將其他表中的列添加過來，進行"添加列"的運算，如下圖所示。

　　為什麼需要進行"添加列"的操作 了？ 因為我們在設計資料庫的時候，往往需要滿足範式（具體滿足範式幾，無法一概而論，這裡不做細究），會導致我們某個需求的全部列分散在不同的表中，所以為了滿足需求，我們需要將某些表的列進行連接。我們來看個簡單例子，假如我們有兩張表（t_user，t_login_log）：

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  sex TINYINT(1) NOT NULL COMMENT '性別, 1:男，0:女',
  age TINYINT(3) UNSIGNED NOT NULL COMMENT '年齡',
  phone_number VARCHAR(11) NOT NULL DEFAULT '' COMMENT '電話號碼',
  email VARCHAR(50) NOT NULL DEFAULT '' COMMENT '電子郵箱',
  create_time datetime NOT NULL COMMENT '創建時間',
  update_time datetime NOT NULL COMMENT '更新時間',
  PRIMARY KEY (id)
) COMMENT='用戶表';

DROP TABLE IF EXISTS t_login_log;
CREATE TABLE t_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  ip VARCHAR(15) NOT NULL COMMENT '登錄IP',
  client TINYINT(1) NOT NULL COMMENT '登錄端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '創建時間',
  PRIMARY KEY (id)
) COMMENT='登錄日誌';

INSERT INTO t_user(user_name, sex, age, phone_number,email,create_time,update_time) VALUES
('Bruce Lee', 1, 32, '15174480987', '[email protected]', NOW(), NOW()),
('Jackie Chan', 1, 65, '15174481234', '[email protected]', NOW(), NOW()),
('Jet Li', 1, 56, '15174481245', '[email protected]', NOW(), NOW()),
('Jack Ma', 1, 55, '15174481256', '[email protected]', NOW(), NOW()),
('Pony', 1, 48, '15174481278', '[email protected]', NOW(), NOW()),
('Robin Li', 1, 51, '15174481290', '[email protected]', NOW(), NOW());

INSERT INTO t_login_log(user_name, ip, client, create_time) VALUES
('Jackie Chan', '10.53.56.78',2, '2019-10-12 12:23:45'),
('Jackie Chan', '10.53.56.78',2, '2019-10-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2018-08-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2019-10-19 10:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-05-12 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-11-11 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2019-06-18 22:23:45'),
('Robin Li', '220.181.38.148',3, '2019-10-21 09:45:56'),
('Robin Li', '220.181.38.148',3, '2019-10-26 22:23:45'),
('Pony', '104.69.160.60',4, '2019-10-12 10:23:45'),
('Pony', '104.69.160.60',4, '2019-10-15 20:23:45');
View Code

　　如果我們需要展示如下列表（需求：展示用戶列表，並顯示其最近登錄時間、最近登錄 IP），那麼就需要 t_user 和 t_login_log 連表查了

　　連接的類型有很多種，細分如下圖

交叉連接

　　講交叉連接之前了，我們先來看看笛卡爾積，假設我們兩個集合，集合A={a, b}，集合B={0, 1, 2}，則A與B的笛卡爾積為{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}，表示為AxB，也就是集合A中的任一元素與集合B的每個元素組合後的新集合則為A與B的笛卡爾積（AxB）。數學上的笛卡爾積反映到資料庫中就是交叉連接（CROSS JOIN），結合上述的案例如下：

SELECT * FROM t_user CROSS JOIN t_login_log;

-- 與 CROSS JOIN 得到的結果相同
-- 過時的寫法，不符合 SQL標準，能讀懂就好，不推薦使用
SELECT * FROM t_user, t_login_log;

　　t_user 中有 6 條記錄， t_login_log 中有 11 條記錄，t_user CROSS JOIN t_login_log 的結果是 66（ 6 乘以 11） 條記錄

　　交叉連接就是對兩張表中的全部記錄進行交叉組合，因此其結果是兩張表的乘積，這也是為什麼交叉連接無法使用內連接或外連接中所使用的 ON 子句的原因。交叉連接基本不會應用到實際業務之中，原因有兩個，一是其結果沒有實用價值，二是結果行數太多，需要花費大量的運算時間和硬體資源。雖說交叉連接的實際使用場景幾乎沒有，但還是有它的理論價值的，交叉連接是其他所有連接運算的基礎，內連接是交叉連接的一部分，其結果是交叉連接的一部分（子集），外連接有點特殊，其結果包含交叉連接之外的內容；更多詳情，我們接著往下看。

內連接

　　只返回兩張表匹配的記錄，就叫內連接，直觀的表現就是關鍵字：INNER JOIN ... ON，ON 表示兩張表連接所使用的列（連接鍵）；而內連接中又屬等值連接最常用

　　等值連接

　　　　簡單點來說，就是連接鍵相等

-- 等值連接
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- INNER JOIN 可以簡寫成 JOIN
SELECT * FROM t_user tu JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- 不加連接鍵, 結果與 CROSS JOIN 一樣
SELECT * FROM t_user tu INNER JOIN t_login_log ttl

　　　　等值連接的結果中，每一條記錄的連接鍵的列的值是想等的，如上圖中的 user_name 和 user_name1（為了區別於第一個user_name，資料庫系統自動取的別名，我們可以顯示的指定）

　　不等值連接

　　　　連接鍵的比較謂詞除了 = 之外的所有情況，比如 >、<、<>（!=）；不等值連接使用場景比較少，反正我在實際工作中幾乎沒用到過

SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name <> ttl.user_name;
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name > ttl.user_name;

　　自然連接

　　　　不需要指定連接條件，資料庫系統會自動用相同的欄位作為連接鍵，直觀的表現就是關鍵字：NATURAL JOIN，NATURAL LEFT JOIN、NATURAL RIGHT JOIN；

　　　　連接鍵不直觀，需要去看兩張表中相同的欄位有哪些；對於自然連接，瞭解即可，不推薦使用，反正我工作這麼久，一次都沒用過。

外連接

　　外連接的使用方式與內連接一樣，也是通過 ON 使用連接鍵將兩張表連接，從結果中獲取我們想要的數據，但是返回的結果與內連接有區別，具體我們往下看

　　左連接

　　　　返回匹配的記錄，以及左表多餘的記錄，關鍵字：LEFT JOIN（LEFT OUTER JOIN 的簡寫）

SELECT * FROM t_user tu LEFT OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- LEFT JOIN 是 LEFT OUTER JOIN 的簡寫
SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　　　上圖中，前 11 條記錄是匹配的記錄，而第 12 條是不匹配、左表的記錄

　　右連接

　　　　返回匹配的記錄，以及表 B 多餘的記錄，關鍵字：RIGHT JOIN（RIGHT OUTER JOIN 的簡寫）

SELECT * FROM t_login_log ttl RIGHT OUTER JOIN t_user tu ON tu.user_name = ttl.user_name;
-- RIGHT JOIN 是 RIGHT OUTER JOIN 的簡寫
SELECT * FROM t_login_log ttl RIGHT JOIN t_user tu ON tu.user_name = ttl.user_name;

　　　　由於我們習慣了從左往右（閱讀方式、寫作方式），因此在實際項目中，基本上用的都是左連接

　　全連接

　　　　返回匹配的記錄，以及左表和右表各自的多餘記錄，關鍵字：FULL JOIN （FULL OUTER JOIN 的簡寫）

SELECT * FROM t_user tu FULL OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- FULL JOIN 是 FULL OUTER JOIN 的簡寫
SELECT * FROM t_user tu FULL JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　　　註意：MySQL 不支持 全連接，我們可以通過 左連接、右連接之後，再 UNION 來實現全連接

自連接

　　一張表，自己連接自己，簡單點來理解就是，左表、右表是同一張表；連接方式可以是內連接、也可以是外連接

　　更多詳情大家可以去看：項目上線後，談一下感觸比較深的一點：查詢優化

需求：展示用戶列表，並顯示最近登錄時間、最近登錄 IP

　　對於此需求，大家會如何來寫這個 SQL ？ 也許大家很容易想到左連接，如下所示

SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　可結果如下：

　　顯示的是每個用戶的所有登錄日誌，不是我們想要的結果；原因是 t_user 中的一條記錄在 t_login_log 對應的記錄有多種情況：0 條對應、1 條對應、多條對應，那這個 SQL 要怎麼寫呢，方式有多種，不局限於如下實現

-- 1、連接配合子查詢，註意 Bruce Lee 從未登陸過
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN t_login_log tll ON tu.user_name = tll.user_name
WHERE tll.id = (SELECT MAX(id) FROM t_login_log WHERE user_name = tu.user_name) OR tll.user_name IS NULL;

-- 2、t_login_log分組統計出各個用戶的最近一次登錄信息後，再與 t_user 聯表
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN (
    SELECT tb.* FROM(
        SELECT user_name, MAX(id) id FROM t_login_log GROUP BY user_name
    ) ta LEFT JOIN t_login_log tb ON ta.id = tb.id
) tll ON tu.user_name = tll.user_name;

　　具體的實現還得結合具體的業務和需求來實現，那樣才能寫出高效的 SQL；另外結合執行計劃來建立合適的索引。總之，沒有一成不變的、通用的高效 SQL，結合具體的業務才能寫出最合適的 SQL。

總結

　　1、連接的描述方式

　　　　常用的維恩圖，描述如下

　　　　維恩圖描述有他的優勢，但它不好表示交叉連接，同時容易讓人誤解成 SQL 中的集合操作；這裡推薦另外一種描述方式，我覺得描述的更準確

　　　　CROSS JOIN

 　　　　常用 JOIN

 　　　　上圖中，顏色表示匹配關係，顏色相同表示匹配。返回結果中，如果另一張表沒有匹配的記錄，則用 null 填充， 在上圖中則表示為空白。

　　2、連接中 ON 指定連接鍵，連接鍵可以指定多個，而 WHERE  還是平時的作用，用來指定過濾條件；不推薦將連接鍵放於 WHERE 後；

　　3、實際工作中，用的最多的是 左連接 和 等值連接，其他的用的特別少

參考

　　《SQL基礎教程》

　　《SQL進階教程》


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

-Advertisement-
Play Games
更多相關文章
  • python實現LRU熱點緩存
    基於列表+Hash的LRU演算法實現。 訪問某個熱點時，先將其從原來的位置刪除，再將其插入列表的表頭 為使讀取及刪除操作的時間複雜度為O(1)，使用hash存儲熱點的信息的鍵值 訪問某個熱點時，先將其從原來的位置刪除，再將其插入列表的表頭 為使讀取及刪除操作的時間複雜度為O(1)，使用hash存儲熱點 ...
  • ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    修改密碼報異常mysql> update user set password=password("y1jhcfzX!") where user="root"; 異常[root@ddtest-mysql01 ~]# mysql -uroot -pEnter password: ERROR 1045 ( ...
  • 達夢資料庫
    摘自： 達夢常用語句 https://blog.csdn.net/zhangxuechao_/article/details/47300953 達夢資料庫管理之表空間 https://blog.csdn.net/lucky_fly/article/details/81566616 --查看表空間se ...
  • 【MongoDB詳細使用教程】三、高級查詢
    [toc] 1、使用比較運算符查詢 MongoDB | 運算 | $gt | 大於 $lt | 小於 $gte | 大於等於 $lte | 小於等於歐 $ne | 不等於 2、使用關鍵字查詢 2.1、in/not in 關鍵字 2.2、size 關鍵字 對於值為list的欄位，可以對list的長度( ...
  • 【MongoDB詳細使用教程】二、MongoDB基本操作
    [toc] 1、數據類型 MongoDB常見類型 | 說明 | Object ID|文檔ID String|字元串，最常用，必須是有效的UTF 8 Boolean|存儲一個布爾值，true或false Integer|整數可以是32位或64位，這取決於伺服器 Double|存儲浮點值 Arrays| ...
  • Excel數據導入到Sql server
    問題:資料庫內直接操作導致 錯誤 0xc020901c: 數據流任務 1: 源 - yndata1$.輸出[Excel 源輸出] 上的 源 - yndata1$.輸出[Excel 源輸出].列[indications] 出錯。返回的列狀態是:“文本被截斷，或者一個或多個字元在目標代碼頁中沒有匹配項。 ...
  • Oracle排名函數(Rank)實例詳解
    這篇文章主要介紹了Oracle排名函數(Rank)實例詳解,需要的朋友可以參考下 --已知：兩種排名方式(分區和不分區):使用和不使用partition --兩種計算方式(連續,不連續),對應函數：dense_rank,rank ·查詢原始數據：學號,姓名,科目名,成績 select * from  ...
  • 阿裡蔣曉偉談計算引擎Flink和Spark的異同與優勢
    本文整理自雲棲社區之前對阿裡搜索事業部資深搜索專家蔣曉偉老師的一次採訪，蔣曉偉老師，認真而嚴謹。在加入阿裡之前，他曾就職於西雅圖的臉書，負責過調度系統，Timeline Infra和Messenger的項目。而後在微軟的SQL Server引擎擔任過Principal Engineer，負責關係數據 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類