MySQL - 通用表達式:初學者指南
您好,未來的數據庫大師!今天,我們將踏上一段令人興奮的旅程,進入 MySQL 通用表達式(CTEs)的世界。別擔心你對編程是新手——我將成為你友好的導遊,我們會一步步來。在本教程結束時,你將能夠像專業人士一樣編寫 CTEs!
什麼是通用表達式?
在我們深入之前,讓我們從基礎開始。想像你正在組織一個大型派對(因為誰不喜歡一個好的數據庫派對,對吧?)你可能會列出一個賓客名單、食物清單和活動清單。這些清單幫助你組織思維,並使計劃變得更容易。通用表達式就像是這些清單,但對於你的數據庫查詢!
通用表達式,或簡稱 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;
讓我們分解這個:
-
WITH
告訴 MySQL 我們即將定義一個 CTE。 -
cte_name
是我們給我們 CTE 的名字(你可以選擇你喜歡的任何名字)。 -
AS
後面是我們定義 CTE 中將包含哪些數據的查詢。 - 在 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;
哇,這真是很多!讓我們分解它:
- 我們創建了兩個新表:
courses
和enrollments
。 - 我們定義了一個 CTE 名為
student_courses
,它將這些表連接起來,得到學生和他們課程的列表。 - 我們定義了另一個 CTE 名為
science_students
,它使用第一個 CTE 來找到選修科學的學生。 - 最後,我們選擇了所有的科學學生。
這個查詢將向我們顯示 Alice、Bob 和 Eva——我們的年輕科學家!
CTE 的力量:你為什麼應該關心?
你可能會想,“為什麼要這麼麻煩?我不能只用子查詢嗎?”好問題!CTEs 提供了幾個優勢:
-
可讀性:CTEs 使複雜查詢更易於閱讀和理解。這就像把一個大問題分解成更小、更易於管理的部分。
-
可重用性:你可以在主查詢中多次引用 CTE,這可能比重複子查詢更有效。
-
遞歸: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