學會這7種SQL進階用法，讓你少走99%的彎路!

来源:https://www.cnblogs.com/coderacademy/p/18058237
-Advertisement-
Play Games

掌握這幾種SQL進階用法，你將能夠更高效地處理各種複雜的數據操作，並少走很多彎路。這些技巧能夠提高你的SQL查詢能力，使得你在日常的數據處理工作中更得心應手。 ...


引言

在日常業務開發中，熟練掌握SQL語言是至關重要的。除了基礎的增刪改查操作外，瞭解和掌握一些進階的SQL用法能夠讓你更高效地處理各種複雜的數據操作。本文將介紹幾種SQL進階用法，讓你少走99%的彎路，提高數據處理效率。

自定義排序

在MySQL中，你可以通過使用自定義排序來指定特定欄位的排序順序。通常情況下，MySQL的排序是按照預設的升序（ASC）或降序（DESC）進行的。但是，有時候你可能需要按照特定的順序進行排序，而不僅僅是升序或降序。這時就可以使用自定義排序。

下麵我們來通過一個示例來講解自定義排序。

假設你有一個students表，其中包含學生的姓名和成績。現在，你想按照自定義的順序對學生進行排序，而不是按照成績的大小。例如，你想按照"A"、"B"、"C"、"D"、"E"這樣的順序進行排序。

在MySql8.0以前我們需要使用ORDER BY結合CASE語句來自定義一個排序規則去實現。例如：

SELECT * 
FROM students 
ORDER BY 
  CASE 
    WHEN grade = 'A' THEN 1 
    WHEN grade = 'B' THEN 2 
    WHEN grade = 'C' THEN 3 
    WHEN grade = 'D' THEN 4 
    WHEN grade = 'E' THEN 5 
    ELSE 6 -- 處理其他情況，例如如果有其他成績值
  END;

執行結果如下：
image.png

而在MySql8.0之後可以直接使用自定義排序規則通過FIELD()函數來實現自定義排序。FIELD()函數接受一個欄位和一個值列表作為參數，並返回該欄位在值列表中的位置。上述示例我們可以改造為：

SELECT * 
FROM students 
ORDER BY  FIELD(grade,'A','B','C','D','E');

image.png

再例如我們在按照自定義的名稱的規則排序：

SELECT *
FROM students 
ORDER BY  FIELD(name, 'Eve','David','Charlie','Bob','Alice');

執行結果：

image.png
需要註意的是，FIELD()函數會返回欄位在值列表中的位置，如果欄位的值不在值列表中，則返回0。所以如果你的欄位可能包含不在值列表中的值，可能需要在排序時進行適當的處理。

這種方法相對簡單，並且在某些情況下非常方便，但也有一些限制。特別是，當值列表很長時，這種方法可能不夠靈活，因為需要在ORDER BY子句中硬編碼所有的值。

空值NULL排序

在MySQL中，當涉及到NULL值排序時，通常，NULL值在升序排序（ASC）中被視為小於任何非NULL值，而在降序排序（DESC）中則被視為大於任何非NULL值。

SELECT * FROM students ORDER BY age ASC;

執行結果：

image.png

SELECT * FROM students ORDER BY age DESC;

執行結果：
image.png
如果你希望NULL值出現在排序結果的末尾或開頭，可以使用COALESCE或IFNULL函數或者ORDER BY IF(ISNULL(欄位), 0, 1)來替換NULL值，從而顯式控制其排序位置：

  1. 升序時將NULL值放在最後面：
SELECT * FROM students ORDER BY COALESCE(age, 999999) ASC;

或者

SELECT * FROM students ORDER BY IFNULL(age, 999999) ASC;

或者

SELECT * FROM students ORDER BY IF(ISNULL(age), 1, 0), age ASC;

執行結果如下：

image.png

  1. 降序時把NULL值放在最前面：
SELECT * FROM students ORDER BY COALESCE(age, 999999) DESC;

或者

SELECT * FROM students ORDER BY IFNULL(age, 999999) DESC;

或者

SELECT * FROM students ORDER BY IF(ISNULL(age), 0, 1), age DESC;

執行結果如下：
image.png

假如對需要排序的列的最大最小的邊界值可以確定，即邏輯上不會出現這麼大的值的時候上述三種方式都可以實現，否則建議使用ORDER BY IF(ISNULL(age), 0, 1)的方式，當然也建議不管那種情況都要使用這種方式。

CASE表達式

CASE表達式是一種條件表達式，類似於其他編程語言中的switch語句。它允許根據條件的不同返回不同的值。

CASE表達式的基本語法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END
  • condition1、condition2等是條件，可以是任何邏輯表達式。
  • result1、result2等是對應條件為真時要返回的結果。
  • ELSE子句是可選的，用於處理所有條件都不滿足的情況，如果省略了ELSE子句且所有條件都不滿足，則返回NULL。
SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade_level
FROM
    students;

執行結果如下：
image.png
CASE表達式在SQL查詢中用途廣泛，可用於SELECT、UPDATE的SET等語句中，實現動態計算列值、根據條件更新值等功能。

分組連接函數：GROUP_CONCAT

GROUP_CONCAT函數用於將每個組內的行連接成一個字元串，並返回該字元串。通常在對結果集進行分組後，需要將每個分組內的多個值合併成一個字元串時使用。該函數在MySQL中非常有用，特別是在處理與分組相關的數據時。

GROUP_CONCAT函數的語法：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
  • DISTINCT：可選項，用於去除重覆的值。
  • expr：要連接的表達式。
  • ORDER BY：可選項，用於指定連接後的字元串的排序方式。
  • SEPARATOR：可選項，用於指定連接後的字元串的分隔符，預設為逗號,。
SELECT
	class,
	GROUP_CONCAT( NAME ) AS students_list 
FROM
	students 
GROUP BY
	class;

查詢結果：
image.png

使用GROUP_CONCAT函數時，還可以通過ORDER BY子句可以指定連接後的字元串的排序方式。

SELECT
	class,
	GROUP_CONCAT( NAME ORDER BY age DESC SEPARATOR ';') AS students_list 
FROM
	students 
GROUP BY
	class;

執行結果：
image.png

預設情況下GROUP_CONCAT函數的最大連接長度是1024個字元。如果你的連接超過了這個限制，可以使用SET SESSION group_concat_max_len = val;語句來調整最大連接長度。

分組統計數據後在進行統計彙總：WITH ROLLUP

WITH ROLLUP是MySQL中用於執行聚合查詢並生成總計行（rollup summary）的選項之一。它允許在GROUP BY子句中創建一個額外的行，該行包含了對分組數據的彙總信息。這在需要同時查看詳細數據和總計數據時非常有用。

使用WITH ROLLUP時，查詢結果會包含每個分組的詳細數據，以及一個總計行，該總計行彙總了所有分組的數據。

SELECT
	class,
	AVG( age ) 
FROM
	students 
WHERE
	age IS NOT NULL 
GROUP BY
	class WITH ROLLUP;

查詢結果：
image.png
結果中包含了每個年級的平均年齡，並且返回所有的班級的學生平均年齡。

需要註意的是，總計行中分組鍵列的值為NULL，表示彙總了所有分組的數據。這使得我們可以通過檢查分組鍵列是否為NULL來確定行是否為總計行。

子查詢提取：WITH AS

在MySQL中，WITH AS子句（也稱為子查詢提取函數）允許你在一個查詢中創建一個臨時的命名子查詢（也稱為公共表表達式），然後在該查詢的後續部分引用這個子查詢。這有助於提高查詢的可讀性和簡潔性，尤其是當查詢中需要多次引用相同的子查詢時。

WITH AS子句的基本語法：

WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE conditions
)
SELECT columns
FROM cte_name
WHERE conditions;

假如我們查詢分數大於80的，並且評級為A的並且不是2班的學生信息：

WITH s1 AS ( SELECT * FROM students WHERE score > 80 ),
s2 AS ( SELECT * FROM students WHERE class = 2 ) 
SELECT
	* 
FROM
	s1 
WHERE
	s1.id NOT IN ( SELECT s2.id FROM s2 ) 
	AND s1.grade = 'A';

結果如下：

image.png

WITH AS是從MySql 8.0.1開始提供

優雅處理數據插入、更新時主鍵或者唯一鍵衝突

在日常開發中，我們嘗嘗在插入數據時會遇到唯一鍵衝突導致插入失敗的問題。如下：

假如我們的唯一鍵為：

ALTER TABLE students ADD UNIQUE KEY `uk_class_name` (`name`,`class`) USING BTREE;

我們執行以下sql：

SELECT * FROM students WHERE class = 1 and name = 'Alice';

查詢結果：

image.png

此時我們在插入一條數據：

INSERT INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

發現報錯：

image.png

我們通常的做法時先按照唯一鍵查詢一遍數據，如果存在則先刪除數據，在進行插入。這種做法略顯麻煩，並且可能會誤刪數據。此時我們就可以採取以下三種方法區優雅處理。

IGNORE

IGNORE關鍵字用於在執行插入、更新或刪除操作時，忽略特定類型的錯誤，而不是中止整個操作。特別是在插入或更新數據時，如果存在唯一索引衝突或主鍵衝突，使用IGNORE關鍵字可以使得操作繼續進行而不是因為錯誤而中斷。

語法如下：

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE IGNORE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

上述插入語句的示例中，我們使用IGNORE關鍵字：

INSERT IGNORE INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

執行成功。
image.png

查詢數據發現，數據沒有變化：
image.png

如果UPDATE使用了IGNORE關鍵字，在更新數據時忽略那些會導致錯誤的行，比如更新更新後會違反唯一約束。

UPDATE IGNORE students SET name = 'Alice' WHERE id = 3;

此時會執行成功：
image.png

IGNORE在使用時應該謹慎，它可能會掩蓋潛在的業務邏輯錯誤或數據一致性問題。並且在事務安全性和完整性要求較高的場景下可能並不適用。

REPLACE

REPLACE INTO是MySQL中用於向表中插入或替換數據的一種特殊語法。它類似於INSERT INTO語句，但是在插入數據時，如果發現表中已存在具有相同唯一索引或主鍵的記錄，則會刪除現有記錄，然後插入新記錄，而不是拋出錯誤或導致插入操作失敗。即有則刪除在插入，沒有則插入。

語法如下：

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

你也可以使用REPLACE INTO與SELECT語句結合使用來替換表中的數據：

REPLACE INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

我們執行如下sql：

REPLACE INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

執行成功，查詢這條記錄：
image.png
此時我們發現記錄的id重新生成了，並且數據頁是插入的最新的數據。

REPLACE INTO會首先嘗試刪除表中具有相同唯一索引或主鍵的記錄，然後再插入新記錄。因此，在使用REPLACE INTO時應當謹慎，以確保不會意外刪除需要保留的數據。

ON DUPLICATE KEY UPDATE

在MySQL中，ON DUPLICATE KEY UPDATE是用於在執行INSERT語句時，如果發生唯一鍵衝突（即違反了唯一索引或主鍵約束），則執行後面的更新操作。

當你想要向表中插入一行數據，但是如果這一行數據已經存在（即主鍵或唯一索引已經存在），則更新該行數據而不是插入新的行，即有則更新，沒有則插入。

語法如下：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

我們執行以下sql：

INSERT IGNORE INTO students ( NAME, grade, age, score, class )
VALUES
	( 'Alice', 'A', 18, 98, 1 ) 
	ON DUPLICATE KEY UPDATE score = 100,
	grade = 'B';

執行成功，重新看一下這條數據：
image.png

數據已執行了更新操作。

這種方式適用於需要維護唯一記錄（如用戶名、郵箱等）的情況下，無需預先檢查是否存在重覆，就能保證數據的一致性和完整性。同時，它也減少了對資料庫的請求次數，提高了處理效率。

總結

掌握以上這幾種SQL進階用法，能夠讓你在日常的數據處理工作中更加得心應手，少走很多彎路。通過優化查詢邏輯、處理異常情況以及執行插入或更新操作，你可以更高效地管理和操作資料庫，提升工作效率，從而更好地應對各種複雜的數據處理需求。

本文已收錄於我的個人博客：碼農Academy的博客，專註分享Java技術乾貨，包括Java基礎、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構設計、面試題、程式員攻略等


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

-Advertisement-
Play Games
更多相關文章
  • 一文讀懂MySQL7大日誌（slow、redo、undo、bin、relay、general、error）
    Slow Log 簡介 用於記錄執行時間超過指定值的 SQL 語句的詳細信息，多用於調試和監控。 配置 因為開啟會略微影響性能，所以預設沒有開啟，所以需要配置。 查看是否開啟 show variables like '%slow%'; + + + | Variable_name | Value | ...
  • MySQL索引底層原理相關問題自總結（難度對標18K-25K薪資，已總結80+，持續更新中）
    註：以下所有內容均為自己總結的筆記，涉及底層原理，難度對標18K-25K薪資，偏理論，不保證百分百準確性。 索引查找快速的原理？ 創建索引的本質是排序，排好序之後再找數據就快了。 對於B+tree索引，B+tree對數據排序後採用多路查找思想的非線性查找方案，減少了大量的查詢次數，從而避免多次磁碟i ...
  • 守護更多女性健康，華為雲GeminiDB助力美柚資料庫高效穩定遷移
    本文分享自華為雲社區《守護更多女性健康，華為雲GeminiDB助力美柚完成資料庫高效穩定遷移》，作者：華為雲頭條。 陽春三月，“三八”國際婦女節悄然而至。在社會快速發展的今天，女性力量在各個領域大放異彩，儘管女性成長路上可能會風雨兼顧，但是關註自己、保持身心健康才能走得更遠、看到更美的風景。美柚作為 ...
  • SQLQueryStress工具的指標解析
    SQLQueryStress是SQL Server資料庫的一個壓測工具，這裡不打算介紹這款工具，而是淺析一下SQLQueryStress工具的一些指標涵義。如有疏漏或不足之處，敬請指正。 如下截圖所示，你會看到下麵一些指標 那麼對應指標的具體含義是啥呢？下麵表格整理了這些指標的含義; 很多同學對於C ...
  • 手把手帶你認識GaussDB輕量化運維管理工具
    本期課程將從管理平臺的架構出發，結合平臺的實例管理、實例升級、容災管理和監控告警的功能和操作介紹，全面覆蓋日常運維操作，帶您理解並熟練運用GaussDB運維平臺完成運維工作。 ...
  • MySQL查詢優化方案彙總（索引相關）
    索引相關 類型隱式轉換 大坑 **欄位filed1是varchar類型，且加了索引，如果 where filed1 = 123; type 可能是all，因為123是數字類型，mysql內部會用函數做隱式轉換，用了函數，索引就失效了。** 大數據深度分頁，用主鍵 select field1,fiel ...
  • MySQL Server架構概述
    推薦：SQL語句執行順序相關問題。 MySQL Server架構 分層概述 MySQL Server架構可抽象為3層。 連接層：驗證用戶名密碼，認證成功後，獲取當前賬號的許可權並緩存，並分配TCP連接池和線程池資源。 處理層：實現核心的處理功能。 存儲層：將處理後的數據高性能安全的寫入磁碟，或從磁碟中 ...
  • 如何將應用一鍵部署至多個環境？丨Walrus教程
    在 Walrus 平臺上，運維團隊在資源定義（Resource Definition）中聲明提供的資源類型，通過設置匹配規則，將不同的資源部署模板應用到不同類型的環境、項目等。與此同時，研發人員無需關註底層具體實現方式，通過創建 Resource 對象聲明需要使用的資源類型及基本信息，就可以靈活地在 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類