常用 SQL 查詢模式速查：建表、連接、子查詢、彙總、刪除更新

建立資料庫與核心表

CREATE DATABASE studb;
USE studb;

CREATE TABLE teacher (
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(20),
    tage INT
);

CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20),
    sage INT,
    tid INT,
    FOREIGN KEY (tid) REFERENCES teacher(tid)
);

CREATE TABLE course (
    cid INT PRIMARY KEY,
    cname VARCHAR(20),
    tid INT,
    FOREIGN KEY (tid) REFERENCES teacher(tid)
);

CREATE TABLE sc (
    scid INT PRIMARY KEY,
    sid INT,
    cid INT,
    score INT,
    FOREIGN KEY (sid) REFERENCES student(sid),
    FOREIGN KEY (cid) REFERENCES course(cid)
);

核心查詢模式

1. 多表等值連接（隱式/顯式）

-- 查詢「小張」的課程
SELECT c.cname
FROM course c, student s, sc
WHERE s.sname = '小張'
  AND s.sid = sc.sid
  AND c.cid = sc.cid;

-- 現代寫法（建議）
SELECT c.cname
FROM student s
JOIN sc ON s.sid = sc.sid
JOIN course c ON sc.cid = c.cid
WHERE s.sname = '小張';

2. 子查詢（標量/IN/EXISTS）

-- 刪除「小張」的所有選課
DELETE FROM sc
WHERE sid = (SELECT sid FROM student WHERE sname = '小張');

-- 查詢及格且課程為 Android 的學生
SELECT sname
FROM student s
WHERE EXISTS (
    SELECT 1 FROM sc
    JOIN course c ON sc.cid = c.cid
    WHERE sc.sid = s.sid
      AND c.cname = 'android'
      AND sc.score >= 60
);

3. 多層巢狀子查詢（李老師課程加 5 分）

UPDATE sc
SET score = score + 5
WHERE cid = (
    SELECT cid FROM course
    WHERE cname = (
        SELECT cname FROM course
        WHERE tid = (SELECT tid FROM teacher WHERE tname = '李老師')
    )
);

4. 彙總與分組

SELECT tid, COUNT(*) AS stu_cnt, AVG(sage) AS avg_age
FROM student
GROUP BY tid
HAVING COUNT(*) > 5;

關鍵提醒

• 外鍵約束確保參考完整性，刪除主表資料需先處理子表或設定 ON DELETE CASCADE。
• 子查詢關聯欄位通常為主外鍵，確保索引存在。
• 複雜巢狀查詢可改用 CTE（WITH）或臨時表提升可讀與效能。