SQL 語句按功能分為四大類，本文系統整理各類核心命令、語法範例與常見易混淆點（如 DROP vs DELETE），並附贈建表、索引、約束、增刪改查、聚合、連接查詢速查表。
四大分類
| 類別 | 全稱 | 核心命令 | 典型操作 |
|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, RENAME, TRUNCATE | 建立/修改/刪除資料庫、表、索引、視圖 |
| DQL | Data Query Language | SELECT | 查詢數據（含子查詢、JOIN、UNION） |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE | 增刪改數據行 |
| DCL | Data Control Language | GRANT, REVOKE | 授權/回收用戶權限 |
易混淆點：DROP vs DELETE vs TRUNCATE
| 命令 | 作用對象 | 可回滾 | 觸發 Trigger | 重置自增 | 速度 |
|---|
| DROP | 結構（庫/表/索引） | 否 | 否 | 是 | 最快 |
| TRUNCATE | 表中所有行 | 否 | 否 | 是 | 快 |
| DELETE | 符合條件的行 | 是 (事務中) | 是 | 否 | 慢 |
**口訣**：結構刪用 DROP，全表清空用 TRUNCATE，條件刪行用 DELETE。
核心 DDL 語法速查
資料庫與表
CREATE DATABASE db_name;
DROP DATABASE db_name;
USE db_name;
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE student ADD email VARCHAR(100);
ALTER TABLE student MODIFY name VARCHAR(60);
ALTER TABLE student DROP COLUMN email;
DROP TABLE student;
索引
CREATE INDEX idx_name ON student(name);
CREATE UNIQUE INDEX uk_email ON student(email);
DROP INDEX idx_name ON student;
約束完整性實現
| 約束 | 關鍵字 | 說明 |
|---|
| 非空 | NOT NULL | 列級 |
| 主鍵 | PRIMARY KEY | 唯一+非空，表級/列級 |
| 唯一 | UNIQUE | 允許一個 NULL |
| 預設值 | DEFAULT | 如 status TINYINT DEFAULT 1 |
| 檢查 | CHECK | MySQL 8.0.16+ 生效 |
| 自增 | AUTO_INCREMENT | 整數主鍵常用 |
| 外鍵 | FOREIGN KEY | 需 InnoDB，參考主鍵/唯一鍵 |
DML 增刪改核心語法
-- 插入單行
INSERT INTO student (id, name, age) VALUES (1, '張三', 20);
-- 多行插入
INSERT INTO student (id, name, age) VALUES (2,'李四',21), (3,'王五',22);
-- 從查詢結果插入
INSERT INTO student_archive SELECT * FROM student WHERE age > 25;
-- 更新
UPDATE student SET age = 21, name = '張三丰' WHERE id = 1;
-- 刪除
DELETE FROM student WHERE id = 1;
-- 全表刪除（保留結構）
TRUNCATE TABLE student;
DQL 查詢進階
基礎查詢
SELECT id, name FROM student;
SELECT * FROM student;
SELECT id AS student_id, name AS student_name FROM student; -- 別名
SELECT DISTINCT age FROM student; -- 去重
SELECT id, name, age * 12 AS age_months FROM student; -- 計算列
條件與排序
SELECT * FROM student WHERE age BETWEEN 18 AND 25;
SELECT * FROM student WHERE name IN ('張三','李四');
SELECT * FROM student WHERE name LIKE '張%'; -- % 多字元, _ 單字元
SELECT * FROM student WHERE email IS NULL;
SELECT * FROM student ORDER BY age DESC, id ASC;
聚合與分組
SELECT COUNT(*) FROM student;
SELECT AVG(age), MAX(age), MIN(age), SUM(age) FROM student;
SELECT class_id, COUNT(*), AVG(age)
FROM student
GROUP BY class_id
HAVING AVG(age) > 20;
集合運算
SELECT name FROM student UNION SELECT name FROM teacher; -- 去重合併
SELECT name FROM student UNION ALL SELECT name FROM teacher; -- 保留重複
SELECT name FROM student INTERSECT SELECT name FROM teacher; -- 交集 (MySQL 8.0+)
SELECT name FROM student EXCEPT SELECT name FROM teacher; -- 差集 (MySQL 8.0+)
連接查詢 (JOIN)
| 類型 | 關鍵字 | 結果特徵 |
|---|
| 內連接 | INNER JOIN / JOIN | 僅匹配行，無 NULL |
| 左外連接 | LEFT JOIN / LEFT OUTER JOIN | 左表全保留，右表無匹配則 NULL |
| 右外連接 | RIGHT JOIN / RIGHT OUTER JOIN | 右表全保留，左表無匹配則 NULL |
| 全外連接 | FULL JOIN / FULL OUTER JOIN | MySQL **不支援**，用 LEFT UNION RIGHT 模擬 |
SELECT s.name, c.class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;
權限管理 (DCL)
GRANT SELECT, INSERT ON db.* TO 'user'@'%' IDENTIFIED BY 'pwd';
REVOKE INSERT ON db.* FROM 'user'@'%';
FLUSH PRIVILEGES;
