SQL 快速參考手冊：標準 SQL 語句語法大全（查詢、DDL、DML、連接、聚合、條件）

本手冊收錄標準 SQL 核心語句語法，涵蓋查詢 (SELECT)、定義 (DDL)、操縱 (DML)、連接 (JOIN)、聚合 (GROUP BY/HAVING)、條件 (WHERE/CASE)、集合運算、事務控制。適用於 MySQL、PostgreSQL、SQL Server、Oracle、SQLite 等主流資料庫（細節差異請查閱各廠商文檔）。

一、查詢與條件

SELECT 基礎

SELECT [DISTINCT] col1, col2, ...
FROM table_name
[WHERE condition]
[GROUP BY col_list]
[HAVING condition]
[ORDER BY col_list [ASC|DESC]]
[LIMIT offset, count];

WHERE 條件運算子

-- 比較
=  <> (!=)  >  <  >=  <=
-- 範圍/集合
BETWEEN val1 AND val2
IN (val1, val2, ...)
NOT IN (...)
-- 模糊
LIKE pattern      -- % 任意長度, _ 單字符
NOT LIKE pattern
-- 空值
IS NULL / IS NOT NULL
-- 邏輯
AND / OR / NOT
-- 優先級: NOT > AND > OR，建議加括號

CASE 條件表達式

-- 簡單 CASE
CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE r_def END

-- 搜尋 CASE
CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ELSE r_def END

二、資料定義 (DDL)

資料庫

CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci];
DROP DATABASE [IF EXISTS] db_name;
ALTER DATABASE db_name CHARSET utf8mb4;

資料表

CREATE TABLE [IF NOT EXISTS] tbl_name (
    col1 type [NOT NULL] [DEFAULT val] [AUTO_INCREMENT] [COMMENT '...'],
    col2 type ...,
    PRIMARY KEY (col1),
    UNIQUE KEY uk_name (col2),
    INDEX idx_name (col3),
    FOREIGN KEY (col4) REFERENCES other_tbl(col) [ON DELETE CASCADE]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE [IF EXISTS] tbl_name;
TRUNCATE TABLE tbl_name; -- 清空表、重置自增、不可回滾

ALTER TABLE tbl_name
    ADD COLUMN col_new type [attrs],
    DROP COLUMN col_old,
    MODIFY COLUMN col_exist type [attrs],
    CHANGE COLUMN old_name new_name type [attrs],
    ADD PRIMARY KEY (col),
    DROP PRIMARY KEY,
    ADD INDEX idx_name (col),
    DROP INDEX idx_name,
    ADD FOREIGN KEY (col) REFERENCES other(col),
    DROP FOREIGN KEY fk_name;

索引

CREATE [UNIQUE] INDEX idx_name ON tbl_name (col1, col2);
DROP INDEX idx_name ON tbl_name; -- MySQL
ALTER TABLE tbl_name DROP INDEX idx_name; -- MySQL 別名

視圖

CREATE [OR REPLACE] VIEW view_name AS SELECT ... FROM ... WHERE ...;
DROP VIEW [IF EXISTS] view_name;

三、資料操縱 (DML)

INSERT

INSERT INTO tbl (col1, col2) VALUES (val1, val2), (val3, val4);
INSERT INTO tbl (col1, col2) SELECT col_a, col_b FROM other_tbl WHERE ...;
INSERT IGNORE INTO tbl ...; -- 忽略重複鍵
INSERT INTO tbl ... ON DUPLICATE KEY UPDATE col = VALUES(col), ...; -- Upsert

UPDATE

UPDATE tbl SET col1=val1, col2=val2 [WHERE condition];
-- 多表更新 (MySQL)
UPDATE tbl_a a JOIN tbl_b b ON a.id=b.a_id SET a.col=b.val WHERE ...;

DELETE

DELETE FROM tbl [WHERE condition];
-- 多表刪除 (MySQL)
DELETE a FROM tbl_a a JOIN tbl_b b ON a.id=b.a_id WHERE ...;

四、連接查詢 (JOIN)

-- 內連接
SELECT ... FROM t1 INNER JOIN t2 ON t1.col = t2.col;
SELECT ... FROM t1 JOIN t2 ON t1.col = t2.col; -- 同義

-- 左連接
SELECT ... FROM t1 LEFT JOIN t2 ON t1.col = t2.col;

-- 右連接
SELECT ... FROM t1 RIGHT JOIN t2 ON t1.col = t2.col;

-- 完全連接 (MySQL 不支援，用 UNION 模擬)
SELECT ... FROM t1 FULL JOIN t2 ON t1.col = t2.col;

-- 交叉連接 (笛卡兒積)
SELECT ... FROM t1 CROSS JOIN t2;

五、聚合與分組

SELECT col, COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
FROM tbl
[WHERE ...]
GROUP BY col
[HAVING agg_condition];

聚合函數

• COUNT(*) / COUNT(col) / COUNT(DISTINCT col)
• SUM(col) / AVG(col)
• MIN(col) / MAX(col)
• GROUP_CONCAT(col SEPARATOR ',') (MySQL)
• STRING_AGG(col, ',') (PostgreSQL/SQL Server 2017+)

WITH ROLLUP / CUBE / GROUPING SETS

-- MySQL
SELECT a, b, COUNT(*) FROM t GROUP BY a, b WITH ROLLUP;

-- PostgreSQL/SQL Server/Oracle
SELECT a, b, COUNT(*) FROM t GROUP BY ROLLUP (a, b);
SELECT a, b, COUNT(*) FROM t GROUP BY CUBE (a, b);
SELECT a, b, COUNT(*) FROM t GROUP BY GROUPING SETS ((a), (b), ());

六、集合運算

SELECT col FROM t1 UNION     SELECT col FROM t2; -- 去重
SELECT col FROM t1 UNION ALL SELECT col FROM t2; -- 保留重複
SELECT col FROM t1 INTERSECT SELECT col FROM t2; -- 交集 (MySQL 8.0+)
SELECT col FROM t1 EXCEPT    SELECT col FROM t2; -- 差集 (MySQL 8.0+)

七、事務控制 (TCL)

START TRANSACTION; -- 或 BEGIN;
-- SQL statements...
COMMIT;   -- 提交
ROLLBACK; -- 回滾
SAVEPOINT sp1; ROLLBACK TO sp1; -- 保存點

八、權限控制 (DCL)

GRANT SELECT, INSERT ON db.* TO 'user'@'host' IDENTIFIED BY 'pass';
REVOKE INSERT ON db.* FROM 'user'@'host';
CREATE USER 'user'@'host' IDENTIFIED BY 'pass';
DROP USER 'user'@'host';
FLUSH PRIVILEGES;

九、常用函數速查

十、MySQL 專用擴展

• LIMIT offset, count / LIMIT count OFFSET offset
• INSERT ... ON DUPLICATE KEY UPDATE
• REPLACE INTO
• SHOW DATABASES/TABLES/COLUMNS/INDEX/STATUS/PROCESSLIST/ENGINES/VARIABLES
• EXPLAIN [FORMAT=JSON|TREE] SELECT ...
• ANALYZE TABLE, OPTIMIZE TABLE, CHECK TABLE, REPAIR TABLE
• LOCK TABLES / UNLOCK TABLES
• SET NAMES utf8mb4, SET time_zone = '+00:00'

本手冊作為日常開發、面試複習、SQL 審核的桌邊參考。記住：標準 SQL 是基線，各資料庫有擴展與差異，生產環境必查官方文檔。將常用語法內化為肌肉記憶，讓寫 SQL 如行雲流水。