---

一、引言

在信息技術飛速發展的當下，數據已成為各行業至關重要的資產。無論是大型企業的海量業務數據，還是互聯網公司的用戶信息，都離不開高效的數據管理和處理。而 SQL（Structured Query Language），即結構化查詢語言，作為與關系型數據庫交互的標準語言，在數據管理和軟件開發領域中占據著舉足輕重的地位。

在數據管理方面，SQL 是創建、修改、管理和控制數據庫中數據與數據結構的核心工具。通過 SQL，我們能夠輕松完成數據庫和表的創建、修改與刪除，以及數據的插入、查詢、更新和刪除等操作。同時，SQL 還提供了豐富的數據查詢工具和命令，支持對數據進行分類、排序、聚合等操作，幫助我們有效地管理和使用數據。比如在一個電商企業中，借助 SQL 可以方便地管理商品信息、訂單數據以及用戶資料，從而實現精準的庫存管理、銷售分析以及個性化推薦。

在軟件開發領域，數據庫是眾多應用系統的重要組成部分，而 SQL 作為操作數據庫的語言，自然成為了開發人員必須掌握的關鍵技能。無論是前端開發還是后端開發，都需要與數據庫進行交互，以實現數據的存儲、讀取和更新。例如，在開發一個在線教育平臺時，通過 SQL 可以實現用戶課程信息的存儲、學習進度的跟蹤以及學習資料的查詢等功能，為平臺的穩定運行和用戶體驗提供有力支持。

掌握 SQL 語句不僅能提升數據處理的效率和準確性，還能為數據分析、數據挖掘以及機器學習等領域打下堅實的基礎。無論是數據分析師從海量數據中提取有價值的信息，還是數據科學家進行復雜的數據建模，都離不開 SQL 的支持。此外，在面試中，SQL 的考察也是一道必不可少的關卡，掌握 SQL 能夠為你的職業發展增添強大的競爭力。

本文將全面深入地講解 SQL 語句，從基礎語法到高級應用，從簡單查詢到復雜的多表關聯，旨在幫助讀者系統地掌握 SQL 這門強大的語言，提升在數據管理和軟件開發領域的能力。無論你是初學者，還是有一定經驗的開發者，都能從本文中獲取到有價值的知識和技巧，開啟 SQL 學習的進階之旅。

二、SQL 基礎語法

2.1 SQL 語句寫法順序

SQL 語句中常見的子句有 SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY 和 LIMIT ，它們的書寫順序決定了查詢的邏輯結構和功能實現。

• SELECT：該子句用于指定要查詢的列。可以是具體的列名，如SELECT column1, column2 FROM table_name;，也可以使用通配符*來表示查詢所有列，即SELECT * FROM table_name;。此外，還能使用聚合函數，比如計算某列的總和SUM(column_name)、平均值AVG(column_name)等 。
• FROM：用于指定查詢數據的來源表，例如FROM employees表示從名為employees的表中獲取數據。
• WHERE：用于篩選數據，通過指定條件來過濾表中的記錄，只有滿足條件的行才會被包含在結果集中。條件表達式可以使用比較運算符（如=、<>、<、>等）、邏輯運算符（如AND、OR、NOT）等。例如WHERE salary > 5000表示篩選出工資大于 5000 的記錄。需要注意的是，WHERE 子句針對的是原表，條件中的列名只能是原表的列名，不能使用別名或者聚合函數。
• GROUP BY：用于對查詢結果進行分組。可以根據一個或多個列的值將結果集劃分為不同的組，通常會與聚合函數一起使用，以便對每個組進行計算。例如GROUP BY department會按照department列對結果進行分組。條件中的列既可以使用原名，也可以使用別名。
• HAVING：用于對分組后的結果進行進一步篩選。與 WHERE 子句類似，但 HAVING 子句是在分組之后進行過濾，所以可以使用聚合函數。例如HAVING AVG(salary) > 6000表示篩選出平均工資大于 6000 的分組。條件中的列同樣可以使用原名或別名。
• ORDER BY：用于對查詢結果進行排序，可以按照一個或多個列進行升序（ASC）或降序（DESC）排序。例如ORDER BY salary DESC表示按照salary列降序排列結果集。條件中的列可以使用原名或別名，默認是升序排序，如果不指定ASC或DESC，則按照升序排列。
• LIMIT：用于限制查詢結果返回的行數。可以指定返回的起始行和行數，如LIMIT 0, 10表示從第 1 行開始（索引從 0 開始），返回 10 行數據；也可以只指定一個參數，如LIMIT 5，表示返回前 5 行數據。

以一個電商數據庫為例，假設我們有orders表（包含order_id、customer_id、order_date、total_amount等字段）和customers表（包含customer_id、customer_name、city等字段），要查詢每個城市的訂單總金額，并按照總金額降序排列，只返回前 5 個城市的數據，SQL 語句如下：

SELECT c.city, SUM(o.total_amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.city
HAVING SUM(o.total_amount) > 10000
ORDER BY total_amount DESC
LIMIT 5;

在這個例子中，首先通過FROM和JOIN指定了數據來源是orders表和customers表，并通過ON條件關聯這兩個表；然后使用WHERE篩選出符合條件的訂單記錄；接著用GROUP BY按照城市對訂單進行分組；HAVING進一步篩選出總金額大于 10000 的分組；ORDER BY對分組后的結果按照總金額降序排列；最后LIMIT限制只返回前 5 個城市的數據。通過這樣的順序組合這些子句，能夠實現復雜的數據查詢和分析需求。

2.2 關聯查詢

關聯查詢是 SQL 中用于從多個表中獲取相關數據的重要技術，通過連接條件將不同表中的數據進行關聯，從而生成包含多個表信息的結果集。常見的關聯查詢類型有內連接、外連接（左、右、全外連接）和自連接。

• 內連接（INNER JOIN）：內連接是最常用的連接類型之一，它返回兩個表中在連接條件上匹配的行。只有當兩個表中連接列的值相等時，對應的行才會被包含在結果集中。其語法結構為：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

假設我們有students表（包含student_id、student_name、class_id等字段）和classes表（包含class_id、class_name等字段），要查詢每個學生所屬的班級名稱，SQL 語句可以這樣寫：

SELECT s.student_name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.class_id;

在這個例子中，通過內連接將students表和classes表根據class_id列進行關聯，返回了每個學生及其對應的班級名稱。內連接適用于需要獲取多個表中相互關聯的數據，且只關注匹配數據的場景，比如在電商系統中查詢訂單對應的商品信息，只有訂單中包含的商品信息才會被返回。

• 外連接：
  • 左外連接（LEFT JOIN）：左外連接會返回左表中的所有行，以及右表中與左表匹配的行。如果右表中沒有匹配的行，則在結果集中用NULL值填充右表的列。語法如下：

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

例如，要查詢所有學生及其可能所屬的班級名稱（包括沒有分配班級的學生），SQL 語句為：

SELECT s.student_name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

此時，即使某個學生沒有對應的班級（即students表中的class_id在classes表中沒有匹配值），該學生的記錄也會被返回，class_name列顯示為NULL。左外連接常用于保留左表的所有數據，并獲取與之相關的右表數據，即使右表中沒有完全匹配的數據。比如在員工管理系統中，查詢所有員工及其可能關聯的部門信息，即使某些員工還未分配到具體部門，這些員工的信息也會被完整返回。

• 右外連接（RIGHT JOIN）：右外連接與左外連接相反，它返回右表中的所有行，以及左表中與右表匹配的行。如果左表中沒有匹配的行，則在結果集中用NULL值填充左表的列。語法為：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

假設要查詢所有班級及其包含的學生信息（包括沒有學生的班級），可以使用右外連接：

SELECT s.student_name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.class_id;

這里，即使某個班級當前沒有學生，該班級的記錄也會被返回，student_name列顯示為NULL。右外連接適用于需要重點關注右表數據，并獲取與之相關的左表數據的場景，比如在課程管理系統中，查詢所有課程及其可能關聯的學生成績，即使某些課程還沒有學生選修，這些課程的信息也會被完整展示。

• 全外連接（FULL JOIN）：全外連接會返回左表和右表中的所有行。如果某個表中沒有與另一個表匹配的行，則在結果集中用NULL值填充相應的列。語法為：

SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

例如，要查詢所有學生和所有班級的信息，無論學生是否分配到班級，班級是否有學生，都可以使用全外連接：

SELECT s.student_name, c.class_name
FROM students s
FULL JOIN classes c ON s.class_id = c.class_id;

全外連接適用于需要獲取兩個表的所有數據，并展示它們之間可能的關聯關系的場景，即使這種關聯關系并不完整。比如在一個綜合的學校管理系統中，查詢所有學生和所有班級的信息，以便全面了解學生和班級的整體情況，包括那些尚未建立關聯的學生和班級。

• 自連接（SELF JOIN）：自連接是指在同一個表上進行的連接操作，通常用于比較或組合表中不同行的數據。在自連接中，需要為表取不同的別名來區分不同的行。語法結構如下：

SELECT t1.column1, t2.column2
FROM table_name t1
JOIN table_name t2 ON t1.some_column = t2.some_column;

假設有一個employees表（包含employee_id、employee_name、manager_id等字段），manager_id表示員工的上級經理的employee_id，要查詢每個員工及其對應的經理姓名，可以使用自連接：

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

在這個例子中，通過自連接將employees表與自身進行關聯，e表示員工，m表示經理，根據manager_id和employee_id的對應關系，查詢出每個員工及其經理的姓名。自連接常用于處理層級關系的數據，如員工與經理關系、商品類別層級關系等，通過自身連接來獲取同一表中不同行之間的關聯信息。

2.3 數據處理常用函數和運算符

在 SQL 中，函數和運算符是進行數據處理和計算的重要工具，能夠幫助我們對數據進行各種操作和轉換，以滿足不同的查詢和分析需求。下面介紹一些常用的函數和運算符。

• DISTINCT：DISTINCT不是一個函數，而是一個關鍵字，用于在查詢結果中去除重復的行。當我們希望獲取某一列或多列的唯一值時，可以使用它。例如，在students表中有city列，要查詢所有不同的城市，SQL 語句為：

SELECT DISTINCT city
FROM students;

這樣就能得到city列中所有不重復的城市名稱，避免重復數據對分析結果的干擾，在統計城市數量等場景中非常有用。

• IF 函數：IF函數用于條件判斷，其語法為IF(condition, value_if_true, value_if_false)。如果condition條件為真，則返回value_if_true，否則返回value_if_false。假設在orders表中有order_amount列表示訂單金額，我們要根據訂單金額判斷訂單狀態，金額大于 1000 為 “大額訂單”，否則為 “普通訂單”，SQL 語句如下：

SELECT order_id, order_amount,IF(order_amount > 1000, '大額訂單', '普通訂單') AS order_status
FROM orders;

通過IF函數，我們能夠根據訂單金額動態地生成訂單狀態，方便對訂單數據進行分類和分析，在電商數據分析、客戶分級等場景中經常使用。

• IFNULL 函數：IFNULL函數用于判斷一個表達式是否為NULL，如果為NULL，則返回指定的值，否則返回表達式本身的值。語法為IFNULL(expr1, expr2)。在employees表中，如果salary列可能存在NULL值，我們希望將其替換為 0，以便進行統計計算，SQL 語句可以寫成：

SELECT employee_name, IFNULL(salary, 0) AS salary
FROM employees;

這樣，在查詢結果中，salary列的NULL值就會被替換為 0，保證了數據的完整性和可用性，在數據清洗、統計分析等場景中是非常實用的函數，能夠避免NULL值對計算結果產生影響。

• EXISTS 和 NOT EXISTS：EXISTS和NOT EXISTS是用于子查詢的運算符，用于判斷子查詢是否返回結果。EXISTS運算符檢查子查詢是否至少返回一行數據，如果是，則EXISTS條件為真；NOT EXISTS則相反，檢查子查詢是否沒有返回任何數據，如果是，則NOT EXISTS條件為真。假設我們有customers表和orders表，要查詢有訂單的客戶，SQL 語句可以使用EXISTS：

SELECT customer_name
FROM customers c
WHERE EXISTS (SELECT 1FROM orders oWHERE o.customer_id = c.customer_id
);

這條語句表示對于customers表中的每一個客戶，檢查orders表中是否存在與之關聯的訂單記錄，如果存在，則該客戶被包含在結果集中。NOT EXISTS則可以用于查詢沒有訂單的客戶：

SELECT customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1FROM orders oWHERE o.customer_id = c.customer_id
);

EXISTS和NOT EXISTS在處理復雜的關聯查詢和條件篩選時非常有用，能夠有效地判斷數據之間的存在關系，常用于數據查詢、數據校驗等場景，幫助我們獲取符合特定條件的數據子集。

• CASE WHEN：CASE WHEN語句用于條件判斷和數據轉換，類似于編程語言中的switch語句。它可以根據不同的條件返回不同的結果。語法有兩種形式：
  • 簡單CASE WHEN：

CASE expressionWHEN value1 THEN result1WHEN value2 THEN result2...ELSE result
END

• 搜索CASE WHEN：

CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE result
END

假設在students表中有score列表示學生成績，我們要根據成績進行等級劃分，90 分及以上為 “A”，80 - 89 分為 “B”，70 - 79 分為 “C”，60 - 69 分為 “D”，60 分以下為 “F”，使用搜索CASE WHEN的 SQL 語句如下：

SELECT student_name, score,CASEWHEN score >= 90 THEN 'A'WHEN score >= 80 AND score < 90 THEN 'B'WHEN score >= 70 AND score < 80 THEN 'C'WHEN score >= 60 AND score < 70 THEN 'D'ELSE 'F'END AS grade
FROM students;

通過CASE WHEN語句，我們能夠靈活地根據不同的條件對數據進行分類和轉換，在數據分析、報表生成等場景中廣泛應用，為數據的展示和分析提供了更多的靈活性和定制性。

三、數據庫和表的基本操作

3.1 創建數據庫

在 SQL 中，使用CREATE DATABASE語句來創建數據庫，這是構建數據庫應用的基礎步驟之一。其基本語法如下：

CREATE DATABASE database_name;

其中，database_name是你要創建的數據庫的名稱，需遵循數據庫的命名規則，通常由字母、數字和下劃線組成，且不能以數字開頭，同時應具有一定的描述性，以便清晰地標識數據庫的用途。例如，要創建一個用于存儲電商數據的數據庫，可使用以下語句：

CREATE DATABASE e_commerce_db;

這樣就創建了一個名為e_commerce_db的數據庫。然而，在實際操作中，如果嘗試創建一個已經存在的數據庫，數據庫管理系統會拋出錯誤，這可能會導致程序中斷或出現不可預期的結果。為避免這種情況，可以使用IF NOT EXISTS選項 ，它的作用是在創建數據庫之前先檢查該數據庫是否已經存在，如果不存在則創建，若已存在則不會執行創建操作，也不會拋出錯誤。語法如下：

CREATE DATABASE IF NOT EXISTS database_name;

比如，再次執行創建e_commerce_db數據庫的操作時，使用IF NOT EXISTS選項：

CREATE DATABASE IF NOT EXISTS e_commerce_db;

無論e_commerce_db數據庫之前是否已經存在，這條語句都能正常執行，不會產生錯誤，增強了數據庫創建操作的穩定性和可靠性，尤其適用于可能會重復執行創建數據庫語句的場景，如數據庫初始化腳本中。

3.2 使用數據庫

在創建數據庫之后，若要對該數據庫進行各種操作，如創建表、插入數據等，首先需要選擇要操作的數據庫。在 SQL 中，使用USE語句來指定當前要使用的數據庫，語法非常簡單：

USE database_name;

例如，要使用前面創建的e_commerce_db數據庫，只需執行以下語句：

USE e_commerce_db;

執行這條語句后，后續的 SQL 操作，如創建表、查詢數據等，都將在e_commerce_db數據庫的上下文中進行。如果不先使用USE語句指定數據庫，直接執行相關操作，數據庫管理系統可能無法確定操作的目標數據庫，從而導致錯誤。USE語句就像是打開了一個特定數據庫的 “大門”，讓我們能夠在這個數據庫中進行各種數據管理和操作，它是與特定數據庫進行交互的關鍵步驟，確保了操作的準確性和針對性。

3.3 創建表

表是數據庫中存儲數據的基本結構，它由行和列組成，每列都有特定的數據類型和約束條件。在 SQL 中，使用CREATE TABLE語句來創建表，其語法相對復雜，涵蓋了多個方面的定義。基本語法如下：

CREATE TABLE table_name (column1 datatype constraint,column2 datatype constraint,...[, PRIMARY KEY (column1 [, column2,...])][, FOREIGN KEY (column) REFERENCES another_table(column)]
);

在這個語法中：

• 列定義：
  • column1、column2等是表中的列名，應具有明確的含義，以便清晰地表示該列所存儲的數據內容。
  • datatype是列的數據類型，常見的數據類型有：
    • 數值類型：如INT（整數類型，用于存儲整數數值，如員工編號、商品數量等）、DECIMAL(m, n)（定點數類型，m表示總位數，n表示小數位數，常用于存儲需要精確表示的數值，如商品價格）。
    • 字符串類型：VARCHAR(n)（可變長度字符串，n表示最大長度，適用于存儲長度不固定的字符串，如員工姓名、商品描述等）、CHAR(n)（固定長度字符串，無論實際存儲的字符串長度是多少，都會占用n個字符的存儲空間，常用于存儲長度固定的字符串，如郵政編碼、身份證號碼等）。
    • 日期和時間類型：DATE（用于存儲日期，格式為YYYY - MM - DD，如訂單日期、員工入職日期等）、DATETIME（用于存儲日期和時間，格式為YYYY - MM - DD HH:MM:SS，如商品上架時間、系統操作時間等）。
  • constraint是列的約束條件，常見的約束有：
    • 主鍵約束（PRIMARY KEY）：用于唯一標識表中的每一行數據，確保列中的值具有唯一性且不能為空。一個表只能有一個主鍵，可以由單個列組成，也可以由多個列組成復合主鍵。例如，在students表中，可以將student_id列定義為主鍵：

CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50),age INT
);

• 唯一約束（UNIQUE）：保證列中的值在表中是唯一的，但可以為空。例如，在employees表中，email列通常需要設置為唯一約束，以確保每個員工的郵箱地址是唯一的：

CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50),email VARCHAR(100) UNIQUE
);

• 非空約束（NOT NULL）：規定列的值不能為空。比如在products表中，product_name列不能為空，因為每個商品都應該有名稱：

CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(50) NOT NULL,price DECIMAL(10, 2)
);

• 主鍵定義：除了在列定義時直接指定主鍵，也可以在表定義的末尾使用PRIMARY KEY關鍵字來定義主鍵，當主鍵由多個列組成時，這種方式更為常用。例如，在orders表中，order_id和product_id共同組成復合主鍵，用于唯一標識每一筆訂單中的商品記錄：

CREATE TABLE orders (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);

• 外鍵關聯：外鍵用于建立兩個表之間的關聯關系，確保數據的一致性和完整性。FOREIGN KEY關鍵字用于定義外鍵，REFERENCES關鍵字指定外鍵關聯的主表和列。例如，有departments表（包含department_id、department_name等字段）和employees表，employees表中的department_id是外鍵，關聯departments表中的department_id，表示員工所屬的部門：

CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(50)
);CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

這樣，在向employees表中插入數據時，department_id的值必須是departments表中已存在的department_id值，否則插入操作會失敗，從而保證了數據的一致性。如果主表departments中刪除了某個部門記錄，從表employees中與之關聯的員工記錄如何處理，可以通過外鍵的級聯操作來定義，如ON DELETE CASCADE表示主表記錄刪除時，從表相關記錄也一并刪除；ON DELETE SET NULL表示主表記錄刪除時，從表相關記錄的外鍵字段設置為NULL。例如：

CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(50),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)ON DELETE SET NULL
);

四、基礎增刪改查操作

4.1 插入數據（增）

在 SQL 中，插入數據是向數據庫表中添加新記錄的操作，主要通過INSERT INTO語句來實現，它是數據庫操作中最基本的功能之一，為數據庫提供了新的數據來源。該語句有多種用法，以滿足不同的插入需求。

• 插入單條記錄：插入單條記錄是最常見的插入操作，其基本語法為：

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

其中，table_name是要插入數據的表名；column1, column2, …是表中的列名，這些列名指定了要插入數據的位置；value1, value2, …是要插入的值，這些值與前面指定的列名一一對應，并且數據類型要匹配。例如，在employees表中插入一條員工記錄，包含員工編號、姓名、年齡和工資信息：

INSERT INTO employees (employee_id, employee_name, age, salary)
VALUES (1001, 'John Doe', 30, 5000);

在這個例子中，employee_id為 1001，employee_name為John Doe，age為 30，salary為 5000，這些值被插入到employees表對應的列中。插入單條記錄適用于在數據庫中添加單個獨立的數據項，比如在用戶管理系統中添加一個新用戶的信息。

• 插入多條記錄：當需要一次性向表中插入多條記錄時，可以使用以下語法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1a, value2a, ...), (value1b, value2b, ...), ...;

通過這種方式，可以在一個INSERT INTO語句中插入多條記錄，每個VALUES子句對應一條記錄，子句之間用逗號分隔。例如，要向students表中插入多名學生的信息：

INSERT INTO students (student_id, student_name, grade)
VALUES (2001, 'Alice', 'A'), (2002, 'Bob', 'B'), (2003, 'Charlie', 'A');

這樣就一次性將三名學生的記錄插入到了students表中。插入多條記錄能夠顯著提高數據插入的效率，減少與數據庫的交互次數，適用于批量添加數據的場景，如在電商系統中導入一批新商品的信息。

4.2 查詢數據（查）

查詢數據是 SQL 中最常用的操作之一，通過SELECT語句可以從數據庫表中獲取所需的數據，它提供了豐富的功能和靈活的語法，能夠滿足各種復雜的數據查詢需求。

• 查詢所有列：使用通配符*可以查詢表中的所有列，語法非常簡單：

SELECT *
FROM table_name;

例如，要查詢employees表中的所有員工信息，包括員工編號、姓名、年齡、工資等所有列的數據，可以使用以下語句：

SELECT *
FROM employees;

這條語句會返回employees表中的每一行數據，并且包含表中的所有列，方便快速獲取表的整體數據情況，但在實際應用中，如果只需要部分列的數據，使用*可能會導致查詢效率降低，因為它會返回所有列的數據，包括一些可能不需要的列。

• 查詢特定列：如果只需要查詢表中的某些特定列，可以在SELECT關鍵字后面列出這些列的名稱，列名之間用逗號分隔：

SELECT column1, column2, ...
FROM table_name;

例如，要查詢employees表中員工的姓名和工資信息，SQL 語句如下：

SELECT employee_name, salary
FROM employees;

這樣只會返回employees表中employee_name和salary這兩列的數據，相比查詢所有列，減少了數據的傳輸和處理量，提高了查詢效率，適用于只關注部分列數據的場景，比如在生成員工工資報表時，只需要員工姓名和工資列。

• 帶條件查詢：通過WHERE子句可以對查詢結果進行條件篩選，只返回滿足特定條件的數據。WHERE子句中可以使用各種比較運算符（如=、<>、<、>、<=、>=）、邏輯運算符（如AND、OR、NOT）以及其他條件表達式 。語法如下：

SELECT columns
FROM table_name
WHERE condition;

例如，要查詢employees表中工資大于 5000 的員工信息：

SELECT *
FROM employees
WHERE salary > 5000;

如果要查詢工資在 5000 到 8000 之間的員工，可以使用BETWEEN關鍵字：

SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 8000;

還可以使用LIKE關鍵字進行模糊查詢，比如查詢姓名以 “J” 開頭的員工：

SELECT *
FROM employees
WHERE employee_name LIKE 'J%';

帶條件查詢能夠從大量數據中精準地篩選出符合要求的數據，在數據分析、業務處理等場景中廣泛應用，幫助用戶快速獲取有價值的信息。

• 排序：使用ORDER BY子句可以對查詢結果進行排序，默認是升序（ASC）排序，如果需要降序排序，可以使用DESC關鍵字。可以按照一個或多個列進行排序。語法如下：

SELECT columns
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

例如，要按照工資降序查詢employees表中的員工信息：

SELECT *
FROM employees
ORDER BY salary DESC;

如果要先按照部門進行升序排序，在同一部門內再按照工資降序排序，可以這樣寫：

SELECT *
FROM employees
ORDER BY department ASC, salary DESC;

排序功能使得查詢結果更加有序，方便用戶查看和分析數據，在報表生成、排行榜展示等場景中經常使用。

• 聚合查詢：聚合函數用于對一組數據進行計算，并返回一個單一的值。常見的聚合函數有COUNT（統計數量）、SUM（求和）、AVG（求平均值）、MAX（求最大值）、MIN（求最小值）等。聚合查詢通常與GROUP BY子句一起使用，GROUP BY子句用于將數據按照指定的列進行分組，然后對每個組應用聚合函數。語法如下：

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

例如，要查詢每個部門的員工數量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

如果要查詢每個部門的平均工資，并且只顯示平均工資大于 6000 的部門，可以使用HAVING子句對分組后的結果進行篩選：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

聚合查詢在數據分析和統計中起著重要作用，能夠幫助用戶快速獲取數據的總體特征和分組信息，為決策提供支持。

4.3 更新數據（改）

更新數據是對數據庫表中已存在的數據進行修改的操作，通過UPDATE語句來實現。它可以更新單個或多個列的值，并且可以使用WHERE子句指定更新條件，以確保只對滿足特定條件的數據進行更新，從而保證數據的準確性和一致性。

• 更新單個列的值：更新單個列的值的語法如下：

UPDATE table_name
SET column1 = new_value1
WHERE condition;

其中，table_name是要更新數據的表名；column1是要更新的列名；new_value1是該列的新值；WHERE子句是可選的，用于指定更新的條件，如果不指定WHERE子句，則會更新表中所有行的該列值。例如，在employees表中，將員工編號為 1001 的員工的工資增加 1000：

UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 1001;

在這個例子中，WHERE子句確保只有employee_id為 1001 的員工的工資會被更新，其他員工不受影響。這種操作適用于對特定記錄的單個屬性進行修改的場景，比如在員工管理系統中，調整某個員工的薪資。

• 更新多個列的值：當需要同時更新多個列的值時，可以在SET子句中列出多個列及其新值，列之間用逗號分隔，語法如下：

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

例如，要將employees表中部門為 “HR” 的所有員工的年齡增加 1 歲，并且將工資提高 5%：

UPDATE employees
SET age = age + 1, salary = salary * 1.05
WHERE department = 'HR';

通過這種方式，可以一次性對多個列進行更新，提高數據更新的效率，適用于需要同時修改多個屬性的場景，比如在用戶信息管理中，同時更新用戶的姓名、地址和聯系方式等多個字段。

4.4 刪除數據（刪）

刪除數據是從數據庫表中移除不需要的數據記錄的操作，使用DELETE FROM語句來完成。可以刪除單條或多條記錄，同樣可以借助WHERE子句指定刪除條件，以避免誤刪數據。

• 刪除單條記錄：刪除單條記錄時，通過WHERE子句指定唯一標識該記錄的條件，語法如下：

DELETE FROM table_name
WHERE condition;

例如，在students表中刪除學生編號為 2005 的學生記錄：

DELETE FROM students
WHERE student_id = 2005;

這樣就可以準確地刪除指定的單條記錄，在數據清理、用戶注銷等場景中經常使用，確保數據庫中只保留有用的數據。

• 刪除多條記錄：當需要刪除多條滿足特定條件的記錄時，同樣使用WHERE子句來定義刪除條件，語法與刪除單條記錄相同。例如，要刪除orders表中訂單日期在 2022 年 1 月 1 日之前的所有訂單記錄：

DELETE FROM orders
WHERE order_date < '2022-01-01';

通過這種方式，可以批量刪除符合條件的多條記錄，在數據維護、過期數據清理等場景中非常有用，能夠有效減少數據庫中的冗余數據，提高數據庫的性能和存儲空間利用率。如果不使用WHERE子句，DELETE FROM語句將刪除表中的所有記錄，這在實際操作中需要謹慎使用，以免造成數據丟失 。例如：

DELETE FROM table_name; -- 慎用，會刪除表中所有記錄

五、復雜關系操作

5.1 一對多關系操作

在數據庫設計中，一對多關系是一種常見的數據關系模式，它表示一個表中的一條記錄可以與另一個表中的多條記錄相關聯 。以電商系統中的訂單和訂單項為例，一個訂單可以包含多個訂單項，而每個訂單項只能屬于一個訂單，這就是典型的一對多關系。

• 創建表：在 SQL 中，創建一對多關系的表時，通常在多的一方（訂單項表）添加一個外鍵，用于引用一的一方（訂單表）的主鍵，以此建立兩個表之間的關聯。假設我們要創建orders表（訂單表）和order_items表（訂單項表），SQL 語句如下：

-- 創建訂單表
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_name VARCHAR(255),order_date DATE
);-- 創建訂單項表
CREATE TABLE order_items (item_id INT PRIMARY KEY,order_id INT,product_name VARCHAR(255),quantity INT,FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

在這個例子中，orders表的order_id作為主鍵，唯一標識每個訂單。order_items表中的order_id是外鍵，它引用orders表中的order_id，通過這個外鍵建立了訂單和訂單項之間的一對多關系。這樣的設計確保了數據的一致性和完整性，當插入訂單項時，order_id必須是orders表中已存在的order_id值，否則插入操作會因為外鍵約束而失敗。

• 插入數據：插入數據時，需要先插入一的一方（訂單表）的數據，然后再插入多的一方（訂單項表）的數據，并且在訂單項表中指定正確的order_id值，以建立關聯。例如：

-- 插入訂單數據
INSERT INTO orders (order_id, customer_name, order_date)
VALUES (1, 'John Doe', '2023-04-01');-- 插入訂單項數據，關聯到訂單1
INSERT INTO order_items (item_id, order_id, product_name, quantity)
VALUES (101, 1, 'Product A', 2), (102, 1, 'Product B', 3);

這里先插入了一個訂單記錄，order_id為 1。然后插入了兩個訂單項記錄，它們的order_id都為 1，表明這兩個訂單項屬于訂單 1。如果先插入訂單項，而對應的訂單在orders表中不存在，就會違反外鍵約束，導致插入失敗。

• 查詢數據：查詢一對多關系的數據時，通常需要使用連接操作（JOIN）將兩個表關聯起來，以獲取完整的訂單和訂單項信息。例如，要查詢訂單 1 的所有訂單項，SQL 語句可以這樣寫：

SELECT o.order_id, o.customer_name, oi.item_id, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 1;

這條語句通過JOIN操作將orders表和order_items表根據order_id進行關聯，然后通過WHERE子句篩選出order_id為 1 的訂單及其對應的訂單項信息。通過連接操作，可以方便地從兩個相關的表中獲取所需的數據，在數據分析、報表生成等場景中，這種查詢方式能夠提供全面且有價值的信息。

5.2 多對多關系操作

多對多關系是數據庫設計中更為復雜的一種關系模式，它表示兩個表中的記錄可以相互關聯，即一個表中的一條記錄可以與另一個表中的多條記錄相關聯，反之亦然。以教育系統中的學生和課程為例，一個學生可以選修多門課程，一門課程也可以被多個學生選修，這就是典型的多對多關系。在 SQL 中，處理多對多關系通常需要借助中間表來實現。

• 創建表：創建多對多關系的表時，需要創建一個中間表，該中間表至少包含兩個外鍵，分別指向兩個主表（學生表和課程表）的主鍵，通過這些外鍵來建立兩個主表之間的關聯。假設我們要創建students表（學生表）、courses表（課程表）和中間表student_courses，SQL 語句如下：

-- 創建學生表
CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50)
);-- 創建課程表
CREATE TABLE courses (course_id INT PRIMARY KEY,course_name VARCHAR(50)
);-- 創建中間表
CREATE TABLE student_courses (student_id INT,course_id INT,PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

在這個例子中，students表的student_id和courses表的course_id分別作為各自表的主鍵。student_courses表作為中間表，它的主鍵由student_id和course_id共同組成，這是因為同一個學生可以選修多門課程，同一門課程也可以被多個學生選修，只有兩個字段組合才能唯一標識中間表中的每一條記錄。同時，student_courses表中的student_id和course_id分別作為外鍵，引用students表和courses表的主鍵，從而建立起學生和課程之間的多對多關系 。這種設計確保了數據的一致性和完整性，當插入中間表的數據時，student_id和course_id必須是各自主表中已存在的值，否則插入操作會因為外鍵約束而失敗。

• 插入數據：插入數據時，首先要插入兩個主表（學生表和課程表）的數據，然后再向中間表插入數據，以建立學生和課程之間的關聯。例如：

-- 插入學生數據
INSERT INTO students (student_id, student_name)
VALUES (1, 'Alice'), (2, 'Bob');-- 插入課程數據
INSERT INTO courses (course_id, course_name)
VALUES (101, 'Math'), (102, 'Science');-- 插入中間表數據，建立關聯
INSERT INTO student_courses (student_id, course_id)
VALUES (1, 101), (1, 102), (2, 101);

這里先插入了兩個學生記錄和兩門課程記錄，然后通過向student_courses表插入數據，建立了學生和課程之間的關聯。其中，學生 1 選修了數學和科學課程，學生 2 選修了數學課程。如果在插入中間表數據時，student_id或course_id在對應的主表中不存在，插入操作將無法成功，這保證了數據的準確性和一致性。

• 查詢數據：查詢多對多關系的數據時，需要通過連接操作將三個表（兩個主表和中間表）關聯起來，以獲取學生和課程之間的完整關聯信息。例如，要查詢學生 1 選修的所有課程，SQL 語句如下：

SELECT s.student_name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.student_id = 1;

這條語句通過兩次JOIN操作，首先將students表和student_courses表根據student_id進行關聯，然后將student_courses表和courses表根據course_id進行關聯，最后通過WHERE子句篩選出student_id為 1 的學生及其選修的課程信息。通過這種方式，可以清晰地展示學生和課程之間復雜的多對多關系，在教育系統的選課管理、成績統計等場景中，這種查詢方式能夠提供關鍵的數據支持。

六、SQL 優化技巧

6.1 索引的使用

索引在數據庫中就如同書籍的目錄，是一種特殊的數據結構，能夠極大地提升數據查詢的效率 。當數據庫執行查詢操作時，如果沒有索引，它可能需要逐行掃描整個表來查找符合條件的數據，這在數據量較大時會耗費大量的時間和資源。而索引的存在可以讓數據庫快速定位到所需數據的位置，減少數據掃描的范圍，從而顯著提高查詢速度。以電商數據庫中包含大量商品信息的products表為例，假設表中有product_id、product_name、price、category等字段，如果經常需要根據product_name查詢商品信息，在product_name字段上創建索引后，查詢效率將大幅提升。例如執行查詢語句SELECT * FROM products WHERE product_name = ‘智能手表’;，有索引時，數據庫可以通過索引快速定位到product_name為 “智能手表” 的記錄，而無需掃描整個products表。

在 SQL 中，創建索引的方法有多種。最常見的是使用CREATE INDEX語句，其基本語法為：

CREATE INDEX index_name ON table_name (column_name);

其中，index_name是你為索引指定的名稱，應具有描述性，便于識別和管理；table_name是要創建索引的表名；column_name是要在其上創建索引的列名。例如，要在employees表的email列上創建索引，可以使用以下語句：

CREATE INDEX idx_employees_email ON employees (email);

除了普通索引，還可以創建唯一索引（CREATE UNIQUE INDEX），它確保索引列中的值是唯一的，常用于保證數據的唯一性，如用戶表中的email或phone_number列；主鍵索引（CREATE PRIMARY KEY），它是一種特殊的唯一索引，用于唯一標識表中的每一行數據，一個表只能有一個主鍵索引；組合索引（CREATE INDEX index_name ON table_name (column1, column2, …)），它基于多個列創建索引，適用于經常使用多個列進行查詢的場景。

在創建索引時，有一些注意事項需要牢記。首先，雖然索引能顯著提升查詢性能，但并非索引越多越好。過多的索引會增加數據庫的存儲開銷，因為每個索引都需要占用一定的磁盤空間。同時，在進行數據插入、更新和刪除操作時，數據庫不僅要更新數據本身，還要更新相關的索引，這會導致這些操作的性能下降。例如，在一個頻繁進行數據插入的orders表中，如果創建了過多不必要的索引，插入新訂單記錄時，數據庫需要花費額外的時間來更新這些索引，從而降低插入操作的效率 。其次，要避免在選擇性低的列上創建索引。選擇性是指索引列中不同值的數量與總行數的比例，選擇性越高，索引的效果越好。如果一個列的選擇性很低，如性別列，只有 “男” 和 “女” 兩個值，在這樣的列上創建索引對查詢性能的提升作用不大，反而會增加索引的維護成本。此外，對于經常用于WHERE、JOIN和ORDER BY子句的列，通常適合創建索引，但也要根據實際情況進行評估和測試，確保索引的創建真正能提高查詢效率。

6.2 避免子查詢

子查詢是指在一個查詢語句中嵌套另一個查詢語句，它在實現復雜數據檢索和處理任務時非常強大，但在性能方面存在一些潛在問題。當執行子查詢時，數據庫通常需要為內層查詢語句的查詢結果建立一個臨時表，然后外層查詢語句從臨時表中查詢記錄，查詢完畢后再撤銷這些臨時表。這個過程會消耗大量的 CPU 和 IO 資源，尤其是當子查詢返回的結果集較大時，對查詢性能的影響更為顯著。例如，有一個查詢要查找在過去 30 天內下過訂單的所有客戶，使用子查詢的方式如下：

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE()));

在這個例子中，內層子查詢先從orders表中篩選出過去 30 天內有訂單的客戶customer_id，并將結果存儲在臨時表中，外層查詢再從customers表中根據臨時表中的customer_id獲取客戶信息。如果orders表數據量很大，創建和處理這個臨時表會帶來很高的性能開銷。

為了提高查詢效率，可以使用JOIN操作來替代子查詢。連接查詢不需要建立臨時表，其速度通常比子查詢要快，如果查詢中使用索引的話，性能會更好。以上述查詢為例，使用JOIN替代子查詢的方式如下：

SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= DATEADD(day, -30, GETDATE());

這條語句通過JOIN將customers表和orders表直接連接起來，然后根據訂單日期條件篩選出符合要求的客戶信息。相比子查詢，這種方式減少了臨時表的創建和處理，能夠更高效地獲取結果。在實際應用中，當需要從多個表中獲取相關數據時，應優先考慮使用JOIN操作，仔細分析查詢需求，選擇合適的連接類型（如內連接、左連接、右連接等），以避免因使用子查詢而導致的性能問題。

6.3 其他優化建議

在編寫 SQL 語句時，還有許多其他優化技巧可以提高查詢性能，以下是一些常見的建議：

• 減少通配符使用：在 SQL 查詢中，通配符（如%和_）用于模糊匹配數據。然而，過度使用通配符會降低查詢性能，因為當使用通配符時，數據庫必須掃描整個表來查找相關數據。例如，使用LIKE '%keyword%'這樣的查詢，由于無法利用索引，數據庫需要逐行檢查表中的每一條記錄，在數據量較大的情況下，查詢速度會非常慢。為了優化查詢，應盡量減少通配符的使用，僅在必要時使用，并且盡量將通配符放在查詢條件的末尾，如LIKE ‘keyword%’，這樣可以利用索引提高查詢效率。如果可以通過其他方式實現相同的查詢目的，如使用全文索引，應優先選擇更高效的方法。
• 使用適當的數據類型：在數據庫中，為列選擇適當的數據類型對查詢性能有著重要影響。對于包含數字值的列，使用整數數據類型（如INT、BIGINT等）通常比使用文本數據類型（如VARCHAR）查詢速度更快，因為數字類型在比較和計算時更加高效。例如，在存儲年齡信息時，使用INT類型而不是VARCHAR類型，可以減少數據處理的時間。同時，選擇正確的數據類型還可以確保數據的完整性，避免數據轉換錯誤。對于固定長度的字符串字段，應使用CHAR類型，它的存儲空間是固定的，適用于存儲長度固定的字符串，如郵政編碼、身份證號碼等；對于長度可變的字符串字段，使用VARCHAR類型，它的存儲空間會根據實際數據的長度調整，不會浪費存儲空間 。在選擇數據類型時，還應考慮數據的取值范圍和精度要求，避免使用過大或過小的數據類型，以充分利用數據庫的存儲空間和性能。
• 限制返回行數：如果只需要檢索少量記錄，使用LIMIT（在 MySQL 中）或TOP（在 SQL Server 中）子句來限制返回的行數，可以顯著減少需要處理和返回的數據量，從而提高查詢性能。例如，要查詢orders表中最新的 10 個訂單，可以使用以下語句：

-- MySQL
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM orders ORDER BY order_date DESC;

這樣可以避免查詢返回大量不必要的數據，減少網絡傳輸和數據處理的開銷，尤其在處理大數據集時，效果更為明顯。在實際應用中，根據業務需求合理設置返回行數的限制，能夠提高系統的響應速度和資源利用率。

• 避免 SELECT *：在編寫查詢語句時，應盡量避免使用SELECT *，因為它會返回表中的所有列，包括可能不需要的列。這不僅會增加數據傳輸和處理的開銷，還可能導致查詢性能下降。例如，在employees表中，如果只需要查詢員工的姓名和工資信息，使用SELECT *會返回所有列的數據，而實際上可能只需要employee_name和salary這兩列。正確的做法是明確指定需要查詢的列，如SELECT employee_name, salary FROM employees;，這樣可以減少數據的傳輸量，提高查詢效率。在實際應用中，根據具體的業務需求，仔細選擇需要查詢的列，避免不必要的數據獲取，從而優化查詢性能。

七、SQL 高級用法

7.1 窗口函數

窗口函數，也被稱為分析函數，是 SQL 中功能強大且靈活的工具，它能夠在不改變查詢結果集行數的情況下，對每一行執行聚合計算或者其他復雜的計算 。與普通聚合函數不同，窗口函數不會將多行數據合并為一行，而是為每一行返回一個計算結果，同時保留原始數據的行結構，這使得我們能夠在同一結果集中同時展示原始數據和基于這些數據的計算結果，為數據分析和處理提供了極大的便利。

窗口函數的基本語法結構如下：

<窗口函數> OVER ([PARTITION BY <分區列>] [ORDER BY <排序列>] [ROWS 或 RANGE <窗口框架定義>])

• 窗口函數表達式：可以是各種聚合函數（如SUM、AVG、COUNT、MAX、MIN），也可以是一些專用的窗口函數（如ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG等）。例如，SUM(salary)表示對salary列進行求和計算。
• PARTITION BY 子句：用于將數據分成不同的分區，窗口函數將在每個分區內獨立執行計算。這類似于GROUP BY子句的分組功能，但GROUP BY會將分組后的數據合并為一行，而PARTITION BY只是劃分計算的范圍，不會改變結果集的行數。例如，PARTITION BY department表示按department列進行分區，這樣窗口函數會針對每個部門的數據分別進行計算。
• ORDER BY 子句：定義了數據在每個分區內的排序方式，它決定了窗口函數的計算順序。例如，ORDER BY salary DESC表示按salary列降序排序，窗口函數在計算時會按照這個順序依次處理每一行數據。
• ROWS 或 RANGE 窗口框架定義：用于指定窗口的范圍，即窗口函數計算時所涉及的行范圍。可以是固定的行數（ROWS），也可以是基于值的范圍（RANGE）。如果省略此部分，默認是從分區的起始行到當前行。例如，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW表示窗口范圍包括當前行及其前一行。

窗口函數在實際應用中有著廣泛的場景，以下是一些常見的例子：

• 排名：在employees表中，要根據salary對每個部門的員工進行排名，可以使用RANK函數：

SELECT employee_id, department_id, salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

在這個查詢中，PARTITION BY department_id按部門進行分區，ORDER BY salary DESC按工資降序排列，RANK()函數為每個部門內的員工根據工資進行排名。如果有多個員工工資相同，他們的排名會相同，并且下一個排名會跳過相應的數量。例如，如果有兩個員工并列第 2 名，下一個員工的排名將是第 4 名。

• 計算累計值：假設有一個sales表，包含sale_date（銷售日期）和sales_amount（銷售金額）字段，要計算每天的累計銷售金額，可以使用SUM窗口函數：

SELECT sale_date, sales_amount,SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

這里沒有使用PARTITION BY子句，表示對整個結果集進行計算。ORDER BY sale_date按銷售日期排序，SUM(sales_amount) OVER (…)計算從第一條記錄到當前記錄的銷售金額累計值，隨著日期的遞增，累計值不斷更新。

7.2 公共表達式（CTEs）

公共表表達式（Common Table Expressions，簡稱 CTEs）是一種在 SQL 查詢中定義臨時結果集的方法，它可以使復雜的查詢更加易讀和易于維護 。CTE 就像是一個臨時的命名結果集，只在包含它的查詢的執行期間有效，它可以被看作是一個 “虛擬表”，這個 “虛擬表” 基于一個查詢語句生成，并且可以在后續的查詢中像普通表一樣被引用。

CTE 的基本語法結構如下：

WITH cte_name AS (查詢語句
)
SELECT * FROM cte_name;

• WITH 關鍵字：用于引入 CTE 的定義。
• cte_name：為 CTE 指定的名稱，在后續的查詢中通過這個名稱來引用該 CTE。
• 查詢語句：用于生成 CTE 的查詢，可以是任何有效的 SQL 查詢，包括簡單的選擇查詢、連接查詢、子查詢等。

例如，有一個employees表，包含employee_id、employee_name、department_id和salary字段，要計算每個部門的平均工資，并找出工資高于部門平均工資的員工，可以使用 CTE 來實現：

WITH department_avg_salary AS (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
)
SELECT e.employee_id, e.employee_name, e.salary, das.avg_salary
FROM employees e
JOIN department_avg_salary das ON e.department_id = das.department_id
WHERE e.salary > das.avg_salary;

在這個例子中，首先定義了一個名為department_avg_salary的 CTE，它通過查詢計算出每個部門的平均工資。然后在主查詢中，將employees表與department_avg_salary CTE 進行連接，篩選出工資高于部門平均工資的員工記錄。

CTE 在遞歸查詢中也有著重要的應用，特別是在處理樹形結構數據時，如組織架構、目錄結構等。例如，有一個employees表，包含employee_id、manager_id和employee_name字段，manager_id表示員工的上級經理的employee_id，要查詢整個組織架構的層級關系，可以使用遞歸 CTE：

WITH RECURSIVE EmployeeCTE AS (-- 初始查詢，找到頂層經理（manager_id為NULL）SELECT employee_id, manager_id, employee_name, 1 AS levelFROM employeesWHERE manager_id IS NULLUNION ALL-- 遞歸查詢，將每個員工與其上級經理關聯起來SELECT e.employee_id, e.manager_id, e.employee_name, ecte.level + 1FROM employees eINNER JOIN EmployeeCTE ecte ON e.manager_id = ecte.employee_id
)
SELECT employee_id, employee_name, level
FROM EmployeeCTE
ORDER BY level, employee_id;

在這個遞歸 CTE 中，首先通過初始查詢找到頂層經理，然后通過UNION ALL將每個員工與其上級經理關聯起來，不斷遞歸，直到所有員工都被包含在結果集中。最終查詢結果展示了每個員工的層級關系，level字段表示員工在組織架構中的層級。

7.3 聚合函數的高級應用

聚合函數是 SQL 中用于對一組值進行計算并返回單個結果值的函數，如SUM（求和）、AVG（求平均值）、MIN（求最小值）、MAX（求最大值）和COUNT（計數）等。它們在數據分析和統計中起著關鍵作用，而將聚合函數與窗口函數、GROUP BY子句結合使用，可以實現更高級的數據處理和分析功能。

當聚合函數與窗口函數結合時，能夠在不改變原始數據行結構的情況下，對數據進行靈活的聚合計算。例如，有一個sales表，包含sale_id、product_id、sale_date、sale_amount和region字段，要計算每個地區每種產品的累計銷售金額，并按銷售日期排序，可以使用如下查詢：

SELECT sale_id, product_id, sale_date, sale_amount, region,SUM(sale_amount) OVER (PARTITION BY region, product_id ORDER BY sale_date) AS running_total
FROM sales;

在這個查詢中，SUM(sale_amount)是聚合函數，OVER (PARTITION BY region, product_id ORDER BY sale_date)是窗口函數的定義。PARTITION BY region, product_id按地區和產品進行分區，ORDER BY sale_date按銷售日期排序，這樣SUM(sale_amount)會在每個分區內，按照銷售日期的順序計算累計銷售金額，為每一行返回從分區起始行到當前行的銷售金額總和。

聚合函數與GROUP BY子句的結合是實現數據分組統計的基礎。例如，要計算每個地區的銷售總額、平均銷售金額以及產品數量，可以使用以下查詢：

SELECT region,SUM(sale_amount) AS total_sales,AVG(sale_amount) AS avg_sales,COUNT(product_id) AS product_count
FROM sales
GROUP BY region;

在這個例子中，GROUP BY region按地區對數據進行分組，然后對每個分組應用聚合函數，SUM(sale_amount)計算每個地區的銷售總額，AVG(sale_amount)計算平均銷售金額，COUNT(product_id)計算產品數量。

在實際應用中，還經常需要查找每個分組的 Top N 記錄。例如，要找出每個地區銷售金額排名前三的產品，可以結合窗口函數和GROUP BY子句來實現：

WITH RankedSales AS (SELECT product_id, region, sale_amount,RANK() OVER (PARTITION BY region ORDER BY sale_amount DESC) AS sale_rankFROM sales
)
SELECT product_id, region, sale_amount
FROM RankedSales
WHERE sale_rank <= 3;

在這個查詢中，首先通過 CTERankedSales使用RANK窗口函數對每個地區的產品按銷售金額進行排名，PARTITION BY region按地區分區，ORDER BY sale_amount DESC按銷售金額降序排列。然后在主查詢中，從RankedSales中篩選出排名小于等于 3 的記錄，即每個地區銷售金額排名前三的產品。

八、總結

通過本文，我們全面深入地學習了 SQL 語句，從基礎的數據庫和表的創建，到復雜的多表關聯查詢、高級的窗口函數和公共表達式，以及重要的優化技巧。我們了解了 SQL 在數據管理和軟件開發領域的核心地位，掌握了數據的增刪改查基本操作，學會了處理各種數據關系，如一對多、多對多關系 。同時，我們還學習了如何使用索引、避免子查詢等優化方法來提升 SQL 查詢性能，以及窗口函數、CTEs 等高級特性在復雜數據分析中的應用。

SQL 是一門不斷發展和演進的語言，隨著數據量的增長和業務需求的日益復雜，持續學習和實踐 SQL 是提升數據處理能力的關鍵。希望讀者在今后的學習和工作中，不斷練習和應用所學的 SQL 知識，勇于探索新的功能和技術，將 SQL 靈活運用到實際項目中，通過實踐不斷積累經驗，提升自己的 SQL 技能水平，從而更好地應對各種數據管理和分析的挑戰，為業務發展提供有力的數據支持。