MySQL - 通用表達式:初學者指南

您好,未來的數據庫大師!今天,我們將踏上一段令人興奮的旅程,進入 MySQL 通用表達式(CTEs)的世界。別擔心你對編程是新手——我將成為你友好的導遊,我們會一步步來。在本教程結束時,你將能夠像專業人士一樣編寫 CTEs!

MySQL - Common Table Expressions

什麼是通用表達式?

在我們深入之前,讓我們從基礎開始。想像你正在組織一個大型派對(因為誰不喜歡一個好的數據庫派對,對吧?)你可能會列出一個賓客名單、食物清單和活動清單。這些清單幫助你組織思維,並使計劃變得更容易。通用表達式就像是這些清單,但對於你的數據庫查詢!

通用表達式,或簡稱 CTE,是一個臨時命名的結果集,你可以在 SELECT、INSERT、UPDATE、DELETE 或 MERGE 語句中引用它。這就像創建一個只存在於你查詢期間的臨時表。酷炫吧?

MySQL 的 WITH 語句:你的 CTE 魔法杖

在 MySQL 中,我們使用 WITH 語句來創建 CTE。這就像說,“嘿 MySQL,我想創建一個臨時結果集,我將稱它為 X。”讓我們看一個簡單的例子:

WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;

讓我們分解這個:

  1. WITH 告訴 MySQL 我們即將定義一個 CTE。
  2. cte_name 是我們給我們 CTE 的名字(你可以選擇你喜歡的任何名字)。
  3. AS 後面是我們定義 CTE 中將包含哪些數據的查詢。
  4. 在 CTE 定義之後,我們有我們的主要查詢,該查詢使用 CTE。

現在,讓我們試試一個真實世界的例子。想像我們有一個學生和他們成績的表:

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
grade INT
);

INSERT INTO students VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 78),
(4, 'Diana', 95),
(5, 'Eva', 88);

WITH high_achievers AS (
SELECT name, grade
FROM students
WHERE grade > 90
)
SELECT * FROM high_achievers;

在這個例子中,我們創建了一個名為 high_achievers 的 CTE,它只包含成績超過 90 分的學生。然後,我們從這個 CTE 選擇所有列。結果將顯示 Bob 和 Diana,我們的高成就者!

從多個表中的 CTEs:加入派對

CTEs 不僅限於一個表。哦不,它們可以像你需要的那樣複雜!讓我們在我們的學校數據庫中添加一個課程表:

CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(50),
teacher VARCHAR(50)
);

INSERT INTO courses VALUES
(1, 'Math', 'Mr. Smith'),
(2, 'Science', 'Ms. Johnson'),
(3, 'History', 'Mrs. Brown');

CREATE TABLE enrollments (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO enrollments VALUES
(1, 1), (1, 2), (2, 2), (3, 3), (4, 1), (4, 3), (5, 2);

WITH student_courses AS (
SELECT s.name AS student_name, c.name AS course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
),
science_students AS (
SELECT student_name
FROM student_courses
WHERE course_name = 'Science'
)
SELECT * FROM science_students;

哇,這真是很多!讓我們分解它:

  1. 我們創建了兩個新表:coursesenrollments
  2. 我們定義了一個 CTE 名為 student_courses,它將這些表連接起來,得到學生和他們課程的列表。
  3. 我們定義了另一個 CTE 名為 science_students,它使用第一個 CTE 來找到選修科學的學生。
  4. 最後,我們選擇了所有的科學學生。

這個查詢將向我們顯示 Alice、Bob 和 Eva——我們的年輕科學家!

CTE 的力量:你為什麼應該關心?

你可能會想,“為什麼要這麼麻煩?我不能只用子查詢嗎?”好問題!CTEs 提供了幾個優勢:

  1. 可讀性:CTEs 使複雜查詢更易於閱讀和理解。這就像把一個大問題分解成更小、更易於管理的部分。

  2. 可重用性:你可以在主查詢中多次引用 CTE,這可能比重複子查詢更有效。

  3. 遞歸:CTEs 可以是遞歸的,這讓你可以輕鬆地處理分層或樹結構數據(但這是另一天的話題!)。

CTE 方法:你的瑞士軍刀查詢工具

讓我們總結一下你可以使用 CTE 的不同方式,在便攜表中:

方法 描述 示例
基礎 CTE 定義一個簡單的命名結果集 WITH cte AS (SELECT * FROM table)
多個 CTE 在一個查詢中定義多個 CTE WITH cte1 AS (...), cte2 AS (...)
嵌套 CTE 在另一個 CTE 中使用一個 CTE WITH outer_cte AS (WITH inner_cte AS (...) SELECT * FROM inner_cte)
遞歸 CTE 創建一個引用自身的 CTE WITH RECURSIVE cte AS (...)

記住,像任何強大的工具一樣,要明智地使用 CTE。它們很適合組織複雜查詢,但對於簡單操作,一個直接的 SELECT 可能就足夠了。

結論:你的 CTE 旅程開始!

恭喜!你已經邁出了進入通用表達式世界的第一步。我們已經介紹了基礎知識,看到了如何使用 WITH 語句,甚至處理了多表 CTE。記住,熟能生巧,所以不要害怕在您自己的數據庫中進行實驗。

在你繼續你的 MySQL 旅程時,你會發現 CTE 是你查詢編寫工具包中的無價工具。它們將幫助你寫出更乾淨、更高效、更易於維護的代碼。誰知道呢?也許有一天你會成為教導別人 CTE 魔法的人!

持續編碼,保持好奇心,願你的查詢總是返回你期望的結果!直到下次,快樂地整理數據!

Credits: Image by storyset