【技術積累】Mysql中的SQL高級技巧【一】

来源:https://www.cnblogs.com/yyyyfly1/archive/2023/06/10/17471764.html
-Advertisement-
Play Games

博客推行版本更新，成果積累制度，已經寫過的博客還會再次更新，不斷地琢磨，高質量高數量都是要追求的，工匠精神是學習必不可少的精神。因此，大家有何建議歡迎在評論區踴躍發言，你們的支持是我最大的動力，你們敢投，我就敢肝 ...


什麼是多表查詢？如何在MySQL中進行多表查詢？

多表查詢就是在一個查詢中涉及到多個表，通過特定的關聯方式連接多個表，並根據條件從中查詢出所需要的數據。

多表查詢是關係型資料庫中最為基礎的應用之一。

一個比較典型的例子就是，我們在查詢一個訂單的詳細信息時，需要從訂單表中獲取訂單編號，然後根據訂單編號關聯到商品表，獲取商品信息，再根據商品信息關聯到庫存表中，查詢商品數量。一個完整的查詢操作需要同時查詢多個表才能完整獲取所需信息。

 

在MySQL中，進行多表查詢的方式有多種，比較常用的有三種：內連接、左連接、右連接。其中：

  1. 內連接是一種常用的表連接方式，只有在兩個表中都具有相同的數據時，才會將兩個表進行連接。內連接包含四種：等值連接，非等值連接，自連接和多表連接。
  2. 左連接也是一種常用的表連接方式，左連接會保留左表中的所有數據，並將右表中符合條件的數據進行匹配，如果沒有匹配的數據則返回NULL。左連接只會返回左表中的數據。
  3. 右連接和左連接很相似，只是保留的是右表的數據。

 

建表語句

# 1.訂單表


CREATE TABLE orders (
id int(11) NOT NULL AUTO_INCREMENT,
order_num bigint(20) NOT NULL,
order_time datetime NOT NULL,
user_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO orders (id, order_num, order_time, user_id)
VALUES
(1, 1111111, '2022-11-11 12:00:00', 1),
(2, 2222222, '2022-10-10 10:00:00', 2),
(3, 3333333, '2022-09-09 09:00:00', 3);


# 2.商品表


CREATE TABLE goods (
id int(11) NOT NULL AUTO_INCREMENT,
goods_name varchar(20) NOT NULL,
price decimal(10,2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO goods (id, goods_name, price)
VALUES
(1, '商品1', 99.00),
(2, '商品2', 399.00),
(3, '商品3', 199.00);


# 3.訂單商品關聯表


CREATE TABLE order_goods (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) NOT NULL,
goods_id int(11) NOT NULL,
goods_num int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO order_goods (id, order_id, goods_id, goods_num)
VALUES
(1, 1, 2, 2),
(2, 1, 3, 1),
(3, 2, 1, 3),
(4, 2, 2, 1),
(5, 3, 3, 2);

 

多表查詢的SQL語句

# 1.查詢訂單表中所有訂單的詳細信息，包括訂單編號、訂單時間、用戶ID、商品名稱、商品價格、商品數量等。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id;


# 2.查詢訂單表中所有訂單的詳細信息，包括訂單編號、訂單時間、用戶ID、商品名稱、商品價格、商品數量等。但只顯示2019-12-10之後的訂單。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id
WHERE o.order_time >= '2019-12-10';


# 3.查詢用戶ID為1的用戶所下的所有訂單的詳細信息，包括訂單編號、訂單時間、用戶ID、商品名稱、商品價格、商品數量等。


SELECT o.order_num, o.order_time, o.user_id, g.goods_name, g.price, og.goods_num
FROM orders o
INNER JOIN order_goods og ON o.id = og.order_id
INNER JOIN goods g ON og.goods_id = g.id
WHERE o.user_id = 1;

什麼是子查詢？如何在MySQL中進行子查詢？

子查詢是指在一個查詢語句中嵌套另一個查詢語句的過程。子查詢可以作為一個完整的查詢來使用，也可以作為其他查詢的一個子部分。子查詢可以返回一個值或一組值，這些值可以用來過濾主查詢中的結果，或者作為主查詢的一部分。

在MySQL中，可以使用子查詢來執行複雜的數據查詢和分析。子查詢可以嵌套在SELECT、INSERT、UPDATE 和 DELETE等語句中。

下麵是一些常用的MySQL子查詢語句：

# 1.基本子查詢語法


SELECT column1, column2, …
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);


# 2.帶IN操作符的子查詢


SELECT column1, column2, …
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);


# 3.帶EXISTS操作符的子查詢


SELECT column1, column2, …
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

現在，我們來看一個具體的例子：假設我們有兩張表，一張是學生表，另一張是成績表。學生表包含學生的信息，包括姓名、學號、性別等。成績表包含學生的成績信息，包括學號、科目、成績等。我們通過子查詢來查詢某個學生的平均成績和最高成績。

CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender ENUM('male', 'female') NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE score (
id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
course VARCHAR(50) NOT NULL,
grade INT NOT NULL,
PRIMARY KEY (id)
);


INSERT INTO student (name, gender) VALUES
('Alice', 'female'),
('Bob', 'male'),
('Charlie', 'male');


INSERT INTO score (student_id, course, grade) VALUES
(1, 'math', 80),
(1, 'physics', 90),
(2, 'math', 85),
(2, 'physics', 95),
(3, 'math', 90),
(3, 'physics', 80);

現在，我們來寫一個子查詢，查詢Bob的平均成績和最高成績：

SELECT name,
(SELECT AVG(grade) FROM score s WHERE s.student_id = student.id) AS avg_grade,
(SELECT MAX(grade) FROM score s WHERE s.student_id = student.id) AS max_grade
FROM student
WHERE name = 'Bob';

在這個例子中，我們使用了兩個子查詢來計算Bob的平均成績和最高成績。第一個子查詢使用了AVG聚合函數來計算平均成績，第二個子查詢使用了MAX聚合函數來計算最高成績。在子查詢中，我們使用了WHERE子句來限制查詢結果的範圍，以便僅查詢與當前學生相關的成績。最後，我們把子查詢的結果作為主查詢的一部分輸出。

總的來說，子查詢是一個非常強大的工具，可以用來執行複雜的數據分析和查詢。在MySQL中，子查詢可以嵌套在其他查詢中，以實現更靈活、更高效的數據查詢和操作。

子查詢和多表查詢有什麼區別？

子查詢是在一個SQL語句中嵌套另一個完整的SELECT語句，用於提供一個參考對象或結果集，這個結果集將被嵌入到更大的查詢中。它用於在一個查詢中檢索條件的子集，它可以被用於任意的SELECT, INSERT, UPDATE和DELETE語句中。

多表查詢是指從多張表中檢索信息的查詢，它可以結合使用JOIN, UNION等等，以檢索或計算來自多個表的特定數據集。多表查詢經常用於大型資料庫中，其中將數據分佈在許多表中而不是一個表中。

主要區別在於：

  1. 子查詢一般是單獨的SQL語句，而多表查詢通常涉及到多個表格。

  2. 子查詢一般只返回一行，而多表查詢可以返回多行。

  3. 子查詢用於在一個查詢中檢索條件的子集，而多表查詢則用於在多個表中聯結，計算或篩選出需要的結果集。

  4. 子查詢可以嵌套在其他語句中，而多表查詢中嵌套其他SELECT語句時候通常使用JOIN等關鍵字。

在實際使用中，兩種類型的查詢可以結合使用，以實現更複雜和更靈活的資料庫操作。

什麼是聯合查詢？如何在MySQL中進行聯合查詢？

聯合查詢（Union）是將多個SELECT語句的結果合併成一個結果集輸出的查詢方式。它的關鍵字是UNION，它可以連接兩個或多個SELECT語句的結果集，並去除重覆數據。

在MySQL中進行聯合查詢，需要滿足以下幾個條件：

  1. 聯合查詢的每個SELECT語句的列數必須相同。

  2. 聯合查詢的每個SELECT語句的列名可以不同，但是需要用AS關鍵字定義別名。

  3. 聯合查詢的每個SELECT語句的列的數據類型必須相同或可自動轉換。

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `table1` (`id`, `name`, `age`) VALUES
(1, '張三', 18),
(2, '李四', 19),
(3, '王五', 20);

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `table2` (`id`, `name`, `age`) VALUES
(1, '張三', 18),
(2, '李四', 19),
(3, '趙六', 22);

下麵是一個SQL語句的示例，用於查詢兩個表的name和age列的聯合結果，並去除重覆數據： 

SELECT name, age FROM table1
UNION
SELECT name, age FROM table2;

聯合查詢和多表查詢有什麼區別？

聯合查詢和多表查詢都是SQL語言中用於從多個表中獲取數據的查詢方法，但它們的方式和作用略有不同。

聯合查詢（UNION）是指將兩個或多個表中相同列的數據連接在一起，形成一個結果集。比如在兩個表中查詢出相同的列，可以使用UNION將它們合併成一個結果集。使用UNION操作時，查詢語句必須返回相同的列名和數據類型，而且返回的結果集將自動去除重覆行。

而多表查詢（JOIN）是指用戶可以使用SQL語言將多個表連接起來，形成一個虛擬的表，允許在其中進行查詢或數據操作。其作用是將多張表中有關聯的數據重新組合，從而可以更方便的查詢和操作數據。在多表查詢中有不同的連接類型，例如INNER JOIN（內連接）、LEFT JOIN（左連接）、RIGHT JOIN（右連接）等，它們表示不同的數據連接方式和結果集輸出方式。

因此，聯合查詢和多表查詢是兩種不同的查詢方式。聯合查詢適用於查詢相同結構的數據集，而多表查詢則適用於查詢在多個相關表之間的數據。

什麼是交叉查詢？如何在MySQL中進行交叉查詢？

交叉查詢（Cross Join）是一種在資料庫中組合兩個或多個表的查詢方法，即返回每個表的所有行的組合。交叉查詢用於從多個表中檢索數據，它返回兩個（或更多）表的所有可能的組合。交叉查詢不使用任何條件過濾數據，它僅僅是將數據組合在一起。

在MySQL中進行交叉查詢，需要使用關鍵字“CROSS JOIN”或者省略掉關鍵字“CROSS”，直接使用“JOIN”。

下麵是建立兩個表的建表語句，我們用交叉查詢找到這兩個表的笛卡爾積：

CREATE TABLE table_a (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE table_b (
id int(11) NOT NULL AUTO_INCREMENT,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO table_a (id, name) VALUES (1, '張三'), (2, '李四');


INSERT INTO table_b (id, age) VALUES (1, 20), (2, 30);

交叉查詢語句：

SELECT * FROM table_a CROSS JOIN table_b;


SELECT * FROM table_a JOIN table_b;

交叉查詢和多表查詢有什麼區別？

交叉查詢和多表查詢都用於從多個表中檢索數據。但是，它們之間存在明顯的區別。

交叉查詢： 交叉查詢也稱為笛卡爾積查詢。它是指從表中取出所有可能的組合併生成結果集。也就是說，交叉查詢會返回一個表和另一個表中所有行的組合。如果有兩個表，每個表都有10行，則結果集將包含100個行。交叉查詢的結果集非常大，往往會消耗大量的時間和系統資源。通常不建議使用交叉查詢。

多表查詢： 多表查詢指的是從多個表中檢索有關聯的數據。多表查詢通常使用JOIN操作符將多個表連接起來，然後根據各個表之間的關係提取數據。JOIN操作符將返回一個表中的行與另一個表中的行相關聯。根據JOIN操作符的類型，多表查詢可以分為內連接、左連接、右連接和全連接。

總體來說，交叉查詢的結果集包含了兩個表中所有行的組合，而多表查詢能夠檢索有相關聯的數據。雖然交叉查詢能夠檢索到所有數據，但它的結果往往特別冗長且不可操作。相反，多表查詢更為常見，它能更精準地提取需要的數據，也能夠更高效地操作數據。

在黑夜裡夢想著光，心中覆蓋悲傷，在悲傷里忍受孤獨，空守一絲溫暖。 我的淚水是無底深海，對你的愛已無言，相信無盡的力量，那是真愛永在。 我的信仰是無底深海，澎湃著心中火焰，燃燒無盡的力量，那是忠誠永在。
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ASP.NET Core MVC 從入門到精通之Html輔助標簽補充及模型校驗基礎
    經過前幾篇文章的講解，初步瞭解ASP.NET Core MVC項目創建，啟動運行，以及命名約定，創建控制器，視圖，模型，接收參數，傳遞數據ViewData，ViewBag，路由，頁面佈局，wwwroot和客戶端庫，Razor語法，EnityFrameworkCore與資料庫，HttpContext，... ...
  • 【C#/.NET】Dapper使用QueryMultipleAsync執行多條SQL
    在.NET開發中，.NET的JSON序列化反序列化工具除了Newtonsoft.Json和System.Text.Json其實還有很多優秀的開源的序列化和反序列化工具，這些工具有的性能更加優秀，更加輕量等特征。本文將彙總介紹這些.NET中常用的JSON序列化和反序列化工具，供大家選擇參考使用。 ** ...
  • Dapper.Lite 背景介紹
    本人以前增刪改查項目做的多，先是用ADO.NET+動軟代碼生成器，後來是NHibernate，再後來是各種DbHelper。我把各種DbHelper合成了一個DbHelper，後改名為LiteSql，後來覺得不如直接調用Dapper，於是有了Dapper.LiteSql。因為不想再維護兩套，所以改名 ...
  • WPF中有中心點的slider滑動條
    想要實現的效果 原生滑動條 需要認識一下滑動條的組成 在原生控制項中生成“資源字典”對應的樣式 然後在track所在的列進行添磚加瓦 由於track在row="1"的位置，只需要在這個位置上面添加一個Ellipse和Line Ellipse是來描述固定在滑動條上的中心點的位置 line是來描述Thum ...
  • EF Core + MySQL 基本增刪改查
    # 前言 基於EF Core + MySQL的基本增刪改查，示例是基於[.NET6 + EF Core + MySQL 創建實體和資料庫、EFCore 數據遷移](https://www.cnblogs.com/lym003/p/17411699.html)項目基礎上的內容增加。同時也是對[基於Ca ...
  • c# HttpClient超時重試
    當使用c# HttpClient 發送請求時，由於網路等原因可能會出現超時的情況。為了提高請求的成功率，我們可以使用超時重試的機制。 超時重試的實現方式可以使用迴圈結構，在請求發起後等待一定時間，若超時未收到響應，則再次發起請求。迴圈次數可以根據實際情況進行設置，一般建議不超過三次。 百度搜索的關於 ...
  • 解決github無法打開問題
    在國內訪問國外伺服器（如github）會有卡頓、無法載入等問題，提供兩種解決方案： 1.查看github的IP地址並修改Hosts windows鍵+R，打開cmd（或windows鍵+X，打開Windows PowerShell），輸入指令：ping github.com 獲取IP地址，之後打開文 ...
  • 【Ubuntu22.04】配置靜態IP地址和FTP服務
    ## 一、配置靜態IP 1. 使用命令`ip a`查看當前網卡名稱，Ubuntu22.04預設網卡為ens33： ![](https://img2023.cnblogs.com/blog/3081210/202306/3081210-20230610150008286-884644878.png) ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類