MySQL 基礎查詢語句實戰教程：從 SELECT 到子查詢的完整語法指南

MySQL 基礎查詢語句實戰教程：從 SELECT 到子查詢的完整語法指南

針對 MySQL 初學者與開發人員，系統講解查詢語句的核心語法、常用函數、過濾條件、排序分頁、聚合分組、多表連接、子查詢與 CTE，附帶可直接運行的示例數據腳本。

一、 環境準備：創建示例庫表

CREATE DATABASE IF NOT EXISTS learn_mysql CHARACTER SET utf8mb4;
USE learn_mysql;

-- 視頻表
CREATE TABLE videos (
    video_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    director VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

INSERT INTO videos (title, director) VALUES
('大上海', '武打片'),
('小上海', '愛情片'),
('紅上海', '喜劇片'),
('黑上海', '槍戰片'),
('大上海 2', '散打片');

-- 成績表
CREATE TABLE scores (
    sname VARCHAR(25) NOT NULL,
    math INT NOT NULL,
    physics INT NOT NULL,
    literature INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO scores VALUES
('john', 68, 37, 45),
('jim', 96, 89, 92),
('bill', 65, 12, 45),
('harry', 68, 25, 82);

二、 基礎 SELECT 查詢

1. 全列/指定列查詢

SELECT * FROM videos;
SELECT title, director FROM videos;

2. 去重

SELECT DISTINCT director FROM videos;

3. 列別名與運算

SELECT sname, math + physics + literature AS total_score FROM scores;

三、 WHERE 條件過濾

比較運算符

SELECT * FROM scores WHERE math > 90;
SELECT sname FROM scores WHERE math > 85;

邏輯運算符

-- AND / OR / NOT
SELECT * FROM scores WHERE math <= 25 OR physics <= 25 OR literature <= 25;
SELECT * FROM scores WHERE math > 60 AND physics > 60;

範圍與集合

SELECT * FROM scores WHERE math BETWEEN 60 AND 80;
SELECT * FROM scores WHERE sname IN ('john', 'jim');

模糊匹配

SELECT sname FROM scores WHERE sname LIKE '%j%';      -- 包含 j
SELECT math FROM scores WHERE math LIKE '%6%';        -- 數字隱式轉字符串匹配
SELECT * FROM videos WHERE title LIKE '大%';          -- 以 大 開頭
SELECT * FROM videos WHERE title LIKE '%海';          -- 以 海 結尾

NULL 判斷

SELECT * FROM videos WHERE director IS NULL;
SELECT * FROM videos WHERE director IS NOT NULL;

四、 聚合函數與分組

SELECT COUNT(*) AS total_students FROM scores;
SELECT AVG(math) AS avg_math FROM scores;
SELECT MIN(math) AS min_math, MAX(physics) AS max_physics FROM scores;

-- 分組聚合
SELECT sname, COUNT(*) FROM scores GROUP BY sname;
SELECT AVG(math) FROM scores GROUP BY sname;  -- 無意義，示例用

五、 排序與分頁

-- 排序
SELECT math FROM scores ORDER BY math DESC;
SELECT math FROM scores ORDER BY math ASC;  -- 默認 ASC

-- 分頁 LIMIT offset, count
SELECT math FROM scores ORDER BY math DESC LIMIT 2, 2;  -- 跳過前 2 行，取 2 行（第 2 頁，每頁 2 條）

-- 組合：排序後分頁
SELECT * FROM scores ORDER BY math DESC LIMIT 0, 3;  -- Top 3

六、 多表連接查詢

-- 創建部門表
CREATE TABLE dept (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);
INSERT INTO dept VALUES (1, '技術部'), (2, '市場部');

-- 員工表
CREATE TABLE emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);
INSERT INTO emp VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);

-- 內連接
SELECT e.emp_name, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.dept_id;

-- 左連接（保留左表所有行）
SELECT e.emp_name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id;

七、 子查詢

1. 標量子查詢（返回單值）

SELECT sname, math, (SELECT AVG(math) FROM scores) AS avg_math
FROM scores;

2. 列子查詢（返回單列多行）

SELECT * FROM scores WHERE math IN (SELECT math FROM scores WHERE math > 80);

3. 行子查詢（返回單行多列）

SELECT * FROM scores 
WHERE (math, physics) = (SELECT math, physics FROM scores WHERE sname = 'jim');

4. 表子查詢/衍生表（FROM 後）

SELECT t.sname, t.total
FROM (SELECT sname, math + physics + literature AS total FROM scores) t
WHERE t.total > 200;

5. EXISTS 相關子查詢（推薦）

SELECT * FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);

八、 CTE（公用表表達式，MySQL 8.0+）

WITH high_math AS (
    SELECT sname, math FROM scores WHERE math > 80
)
SELECT h.sname, h.math, s.physics
FROM high_math h
JOIN scores s ON h.sname = s.sname;

九、 常用內建函數速查

十、 練習題

1. 查詢《大上海》開頭的所有視頻。
2. 統計每位導演的視頻數量，按數量降序。
3. 找出數學成績高於平均分的學生姓名與分數。
4. 查詢總分前 3 名學生的姓名與各科成績。
5. 使用 LEFT JOIN 查詢所有部門及其員工數（含無員工部門）。

答案提示：
1. SELECT * FROM videos WHERE title LIKE '大上海%';
2. SELECT director, COUNT(*) FROM videos GROUP BY director ORDER BY COUNT(*) DESC;
3. SELECT sname, math FROM scores WHERE math > (SELECT AVG(math) FROM scores);
4. SELECT sname, math, physics, literature FROM scores ORDER BY (math+physics+literature) DESC LIMIT 3;
5. SELECT d.dept_name, COUNT(e.emp_id) FROM dept d LEFT JOIN emp e ON d.dept_id=e.dept_id GROUP BY d.dept_id;