讀SQL學習指南（第3版）筆記06_連接和集合

来源:https://www.cnblogs.com/lying7/archive/2023/08/29/17657365.html
-Advertisement-
Play Games

![](https://img2023.cnblogs.com/blog/3076680/202308/3076680-20230825164340432-1938857156.png) # 1. 連接 ## 1.1. 笛卡兒積 ### 1.1.1. 交叉連接（cross join） ### 1.1 ...


1. 連接

1.1. 笛卡兒積

1.1.1. 交叉連接（cross join）

1.1.2. 查詢並沒有指定兩個數據表應該如何連接，資料庫伺服器就生成了笛卡兒積

1.1.2.1. 兩個數據表的所有排列組合

1.1.3. 很少會用到（至少不會特意用到）

1.1.3.1. 使用頻率並不高

1.1.4. mysql

-＞ SELECT c.first_name, c.last_name, a.address
    -＞ FROM customer c JOIN address a;

1.1.5. 為2020年的每一天生成一行，但是資料庫中沒有包含每天一行的數據表

1.1.5.1. 涉及交叉連接、外連接、日期函數、分組、集合運算（union all）和聚合函數（count()）

1.1.5.2. sql

 SELECT '2020-01-01' dt
UNION ALL
SELECT '2020-01-02' dt
UNION ALL
SELECT '2020-01-03' dt
UNION ALL
...
...
...
SELECT '2020-12-29' dt
UNION ALL
SELECT '2020-12-30' dt
UNION ALL
SELECT '2020-12-31' dt

1.1.5.3. mysql

 -＞ SELECT ones.num + tens.num + hundreds.num
    -＞ FROM
    -＞ (SELECT 0 num UNION ALL
    -＞ SELECT 1 num UNION ALL
    -＞ SELECT 2 num UNION ALL
    -＞ SELECT 3 num UNION ALL
    -＞ SELECT 4 num UNION ALL
    -＞ SELECT 5 num UNION ALL
    -＞ SELECT 6 num UNION ALL
    -＞ SELECT 7 num UNION ALL
    -＞ SELECT 8 num UNION ALL
    -＞ SELECT 9 num) ones
    -＞ CROSS JOIN
    -＞ (SELECT 0 num UNION ALL
    -＞ SELECT 10 num UNION ALL
    -＞ SELECT 20 num UNION ALL
    -＞ SELECT 30 num UNION ALL
    -＞ SELECT 40 num UNION ALL
    -＞ SELECT 50 num UNION ALL
    -＞ SELECT 60 num UNION ALL
    -＞ SELECT 70 num UNION ALL
    -＞ SELECT 80 num UNION ALL
    -＞ SELECT 90 num) tens
    -＞ CROSS JOIN
    -＞ (SELECT 0 num UNION ALL
    -＞ SELECT 100 num UNION ALL
    -＞ SELECT 200 num UNION ALL
    -＞ SELECT 300 num) hundreds;

1.1.5.4. 如果生成{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}、{0, 10, 20, 30, 40, 50, 60, 70, 80, 90}和{0,100, 200, 300}這3個集合的笛卡兒積，並將這3列的值相加，就可以得到包含0～399的所有數值的400行結果集

1.1.5.5. mysql

 -＞ SELECT DATE_ADD('2020-01-01',
    -＞   INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
    -＞ FROM
    -＞  (SELECT 0 num UNION ALL
    -＞   SELECT 1 num UNION ALL
    -＞   SELECT 2 num UNION ALL
    -＞   SELECT 3 num UNION ALL
    -＞   SELECT 4 num UNION ALL
    -＞   SELECT 5 num UNION ALL
    -＞   SELECT 6 num UNION ALL
    -＞   SELECT 7 num UNION ALL
    -＞   SELECT 8 num UNION ALL
    -＞   SELECT 9 num) ones
    -＞   CROSS JOIN
    -＞  (SELECT 0 num UNION ALL
    -＞   SELECT 10 num UNION ALL
    -＞   SELECT 20 num UNION ALL
    -＞   SELECT 30 num UNION ALL
    -＞   SELECT 40 num UNION ALL
    -＞   SELECT 50 num UNION ALL
    -＞   SELECT 60 num UNION ALL
    -＞   SELECT 70 num UNION ALL
    -＞   SELECT 80 num UNION ALL
    -＞   SELECT 90 num) tens
    -＞   CROSS JOIN
    -＞  (SELECT 0 num UNION ALL
    -＞   SELECT 100 num UNION ALL
    -＞   SELECT 200 num UNION ALL
    -＞   SELECT 300 num) hundreds
    -＞ WHERE DATE_ADD('2020-01-01',
    -＞   INTERVAL (ones.num + tens.num + hundreds.num) DAY) ＜ '2021-01-01'
    -＞ ORDER BY 1;

1.1.5.6. 無須人為介入，結果集會自動包含額外的閏日（2月29日），這是由資料庫伺服器通過將2020年1月1日加上59天計算得出

1.2. 內連接

1.2.1. 增加了連接類型（註意關鍵字inner）

1.2.2. mysql

 -＞ SELECT c.first_name, c.last_name, a.address
    -＞ FROM customer c JOIN address a
    -＞   ON c.address_id = a.address_id;

1.2.3. sql

SELECT c.first_name, c.last_name, a.address
FROM customer c INNER JOIN address a
  ON c.address_id = a.address_id;

1.2.4. 如果用於連接兩個數據表的列名相同，則可以使用using子句替代on

1.2.4.1. sql

SELECT c.first_name, c.last_name, a.address
FROM customer c INNER JOIN address a
  USING (address_id);

1.2.4.2. using是一種只能在某些特定情況下使用的簡寫表示法

1.3. 外連接（outer join）

1.3.1. 如果要將一個數據表中的所有行全部納入結果集，不管其在另一個數據表中是否存在匹配

1.3.2. mysql

 -＞ SELECT f.film_id, f.title, count(i.inventory_id) num_copies
    -＞ FROM film f
    -＞   LEFT OUTER JOIN inventory i
    -＞   ON f.film_id = i.film_id
    -＞ GROUP BY f.film_id, f.title;

1.3.3. 關鍵字left和right只是告知伺服器哪個數據表的數據可以不足

1.3.4. outer關鍵字是可選的，可以使用A left join B來代替

1.4. 自然連接（natural join）

1.4.1. 依靠多個數據表之間相同的列名來推斷適合的連接條件

1.4.2. mysql

 -＞ SELECT c.first_name, c.last_name, date(r.rental_date)
    -＞ FROM customer c
    -＞   NATURAL JOIN rental r;
Empty set (0.04 sec)

1.4.3. 資料庫伺服器檢查數據表定義並添加了連接條件r.customer_id = c.customer_id

1.4.4. 應該避免使用這種連接類型，而使用帶有顯式連接條件的內連接

1.5. 連接的舊方法

1.5.1. mysql

 -＞ SELECT c.first_name, c.last_name, a.address
    -＞ FROM customer c, address a
    -＞ WHERE c.address_id = a.address_id;

1.5.2. 不需要on子句

1.5.3. from子句中的數據表名以逗號分隔

1.5.4. 連接條件出現在where子句中

1.6. ANSI連接語法

1.6.1. 連接條件和過濾條件被分隔在兩個不同的子句中（on子句和where子句），使得查詢語句更易於理解

1.6.2. 兩個數據表的連接條件出現在其各自單獨的on子句中，這樣就不太可能錯誤地忽略連接條件

1.6.3. 使用SQL92連接語法的查詢語句可以在各種資料庫伺服器間移植，而舊語法在不同伺服器上的表現略有不同

1.7. 連接順序

1.7.1. 各數據表在from子句中出現的順序並不重要

1.7.1.1. 伺服器使用從資料庫對象收集的統計信息，在3個數據表中選擇一個作為起點（所選擇的數據表被稱為驅動表），然後確定其他數據表的連接順

1.7.2. 如果認為查詢語句中的數據表應該始終以特定的順序連接，可以將數據表按照需要的順序排列

1.7.2.1. 在MySQL中指定straight_join關鍵字

1.7.2.1.1. sql
SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city
FROM city ct
  INNER JOIN address a
  ON a.city_id = ct.city_id
  INNER JOIN customer c
  ON c.address_id = a.address_id

1.7.2.2. 在SQL Server中請求force order選項

1.7.2.3. 在Oracle Database中使用ordered或leading優化器

1.7.3. 三路外連接

1.7.3.1. mysql

-＞ SELECT f.film_id, f.title, i.inventory_id, r.rental_date
    -＞ FROM film f
    -＞   LEFT OUTER JOIN inventory i
    -＞   ON f.film_id = i.film_id
    -＞   LEFT OUTER JOIN rental r
    -＞   ON i.inventory_id = r.inventory_id
    -＞ WHERE f.film_id BETWEEN 13 AND 15;

1.8. 自連接

1.8.1. mysql

-＞ SELECT f.title, f_prnt.title prequel
    -＞ FROM film f
    -＞   INNER JOIN film f_prnt
    -＞   ON f_prnt.film_id = f.prequel_film_id
    -＞ WHERE f.prequel_film_id IS NOT NULL;

2. 集合

2.1. 在對兩個數據集執行集合運算時

2.1.1. 兩個數據集的列數必須相等

2.1.2. 兩個數據集各列的數據類型必須相同

2.1.2.1. 資料庫伺服器必須能夠將一種數據類型轉換成另一種數據類型

2.2. union運算符

2.2.1. mysql

 -＞ SELECT 1 num, 'abc' str
    -＞ UNION
    -＞ SELECT 9 num, 'xyz' str;

2.2.2. union會對組合後的集合進行排序並去除重覆項

2.2.3. union all則不然

2.3. intersect運算符

2.3.1. ANSI SQL規範中定義了用於執行交集運算的intersect運算符

2.3.2. ANSI SQL規範還提供了不去除重覆行的intersect all運算符

2.3.2.1. 唯一實現了intersect all運算符的資料庫伺服器是IBM DB2 Universal Server

2.3.3. MySQL 8.0版還未實現intersect運算符

2.3.4. Oracle或SQL Server 2008中可以使用

2.4. except運算符

2.4.1. ANSI SQL規範提供了執行差集運算的except運算符

2.4.2. ANSI SQL規範還提供了except all運算符

2.4.2.1. 只有IBM的 DB2 Universal Server實現了該運算符

2.4.3. MySQL 8.0版也沒有實現except運算符

2.4.4. Oracle Database需要使用非ANSI相容的minus運算符替代except運算符

2.5. 對符合查詢結果排序

2.5.1. mysql

 -＞ SELECT a.first_name fname, a.last_name lname
    -＞ FROM actor a
    -＞ WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
    -＞ UNION ALL
    -＞ SELECT c.first_name, c.last_name
    -＞ FROM customer c
    -＞ WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
    -＞ ORDER BY lname, fname;

2.6. 集合運算的優先順序

2.6.1. 包含3個或以上查詢語句的複合查詢，是以自頂向下的順序來評估查詢的

2.6.2. 根據ANSI SQL規範，intersect運算符擁有比其他集合運算符更高的優先順序

2.6.3. 可以將查詢放入括弧內，以明確指定查詢的執行順序

2.6.3.1. MySQL目前還不允許在複合查詢中使用括弧

2.6.3.2. 將相鄰查詢放入括弧中，以覆蓋複合查詢預設的自頂向下的處理方式


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

-Advertisement-
Play Games
更多相關文章
  • 【23種設計模式】工廠方法模式（二）
    ****## 前言 在講述之工廠方法模式前，我們來先瞭解簡單工廠模式，簡單工廠模式是最簡單的設計模式之一，它雖然不屬於GoF的23種設計模式，但是應用也較為頻繁，同時它也是學習其他創建型模式的基礎。下麵我們來先瞭解下簡單工廠模式，然後針對它的缺點來引出工廠方法模式。 ## 簡單工廠模式定義 **簡單 ...
  • [回饋]ASP.NET Core MVC開發實戰之商城系統(六)
    經過一段時間的準備，新的一期【ASP.NET Core MVC開發實戰之商城系統】已經開始，在之前的文章中，講解了商城系統的整體功能設計，頁面佈局設計，環境搭建，系統配置，及首頁【商品類型，banner條，友情鏈接，降價促銷，新品爆款】，商品列表頁面，商品詳情，購物車等功能的開發，今天繼續講解訂單管... ...
  • Linux運維工程師面試題（3）
    目錄 Linux運維工程師面試題（3）1 LVS 的工作模式有幾種，分別是什麼2 LVS 由哪幾部分組成3 LVS 相關的術語有哪些4 LVS 集群的負載調度演算法有哪些5 使用LVS 可不可以把iptables給禁用刪除6 haproxy 調度演算法有哪些7 nginx 實現負載均衡的分發策略有那些8... ...
  • Linux 如何輸出 1..10
    > 利用for迴圈語句， # 方案一 ##### > 定義1到10的變數，for語句引用該變數 ### 單行代碼 `list="1 2 3 4 5 6 7 8 9 10"; for i in $list; do echo $i; done` ### 多行代碼 for……do……done ``` li ...
  • 鍵盤敲入 A 字母時，操作系統期間發生了什麼？
    # 鍵盤敲入 A 字母時，操作系統期間發生了什麼？ 關於[8.1 鍵盤敲入 A 字母時，操作系統期間發生了什麼？](https://xiaolincoding.com/os/7_device/device.html)的總結，前面都介紹了，但是在最後總結操作系統發生了什麼的時候，我覺得有點不詳細，於是 ...
  • Git和Gitlab使用
    # Git和Gitlab使用 ## 前言 **版本控制概念**：記錄開發文件的時間機器 **分類**：1.本地版本控制系統、2.集中化的版本控制系統CVS、Subversion(SVN)、3.分散式版本控制系統GIT **產品**：github、git、gitlab ## Gitlab部署 **1. ...
  • 刪除文件後磁碟空間未釋放，只能重啟進程？（僅供參考）
    很多運維同學都遇到過“磁碟告警”，遇到這種情況就需要去清理磁碟。 這時候，很多同學通過各種途徑、手段、命令找到了占用磁碟比較大的文件，然後大手一揮， sudo rm -rvf xxxxx.log 以為這樣任務就完成了，誰知道，一查詢磁碟使用量還是居高不下，完全沒有釋放。 這是因為在Linux中，如果 ...
  • 深入理解操作系統中進程與線程的區別及切換機制（上）
    進程是正在運行的程式的實例，它可以包含一個或多個線程。我們瞭解了進程的執行方式，包括早期單核處理器上的順序執行以及引入多任務概念實現的偽並行。我們還探討了進程的狀態模型。進程可以處於就緒、運行、阻塞和結束等不同的狀態。就緒狀態表示進程已經準備好運行，但還沒有被調度執行。運行狀態表示進程正在執行。阻塞... ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類