SQL 查詢是數據分析、後端開發、報表製作的核心技能。本文按複雜度遞進,系統整理單表查詢、多表連接、子查詢、分頁、集合運算、數據變更等高頻場景的標準寫法,附帶性能提示與避坑指南,適合作為桌面速查手冊。
-- SQL Server / PostgreSQL EXEC sp_help 'TableName'; -- 或 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'; -- MySQL DESC TableName; -- 或 SHOW COLUMNS FROM TableName;
-- 全列(生產環境禁用,列明具體列名) SELECT * FROM Employees; -- 指定列 + 別名 SELECT EmpNo, EName AS Name, Sal * 12 + ISNULL(Comm,0) AS AnnualSal FROM Employees; -- 去重 SELECT DISTINCT DeptNo FROM Employees; -- 算術運算與空值處理 -- ISNULL(Comm,0) / COALESCE(Comm,0) / NVL(Comm,0) -- 不同 DB 語法
-- 比較運算
SELECT * FROM Employees WHERE Sal > 5000;
-- 字符模糊匹配
SELECT * FROM Employees WHERE EName LIKE 'S__T%'; -- _ 單字符,% 任意字符
-- 集合包含
SELECT * FROM Employees WHERE Job IN ('CLERK','ANALYST');
-- 空值判斷(只能用 IS NULL/IS NOT NULL,不能用 =/!=)
SELECT * FROM Employees WHERE Mgr IS NULL;
-- 邏輯組合(注意優先級:NOT > AND > OR,建議显式加括號)
SELECT * FROM Employees
WHERE (Sal > 5000 OR Job = 'MANAGER') AND EName LIKE 'J%';
-- 多字段排序:按部門升序,薪資降序 SELECT * FROM Employees ORDER BY DeptNo ASC, Sal DESC; -- 按別名排序 SELECT Sal * 12 AS AnnualSal FROM Employees ORDER BY AnnualSal DESC;
SELECT MAX(Sal), MIN(Sal), AVG(Sal), SUM(Sal), COUNT(*) FROM Employees;
-- 按部門統計 SELECT DeptNo, MAX(Sal), AVG(Sal) FROM Employees GROUP BY DeptNo; -- 多字段分組 + 組過濾 SELECT DeptNo, Job, AVG(Sal), MIN(Sal) FROM Employees GROUP BY DeptNo, Job HAVING AVG(Sal) < 2000;
分組鐵律:
1. SELECT 列表中的非聚合列必須出現在 GROUP BY 中
2. WHERE 不能用聚合函數,組過濾用 HAVING
3. 執行順序:WHERE → GROUP BY → HAVING → ORDER BY
SELECT E.EName, E.Sal, D.DName FROM Employees E INNER JOIN Departments D ON E.DeptNo = D.DeptNo ORDER BY D.DeptNo;
SELECT E.EName, E.Sal, S.Grade FROM Employees E JOIN SalGrade S ON E.Sal BETWEEN S.LoSal AND S.HiSal;
SELECT E.EName AS EmpName, M.EName AS MgrName FROM Employees E LEFT JOIN Employees M ON E.Mgr = M.EmpNo;
SELECT EName FROM Employees WHERE DeptNo = (SELECT DeptNo FROM Employees WHERE EName = 'SMITH');
-- IN:返回結果集中任意值匹配 SELECT EName, Job, Sal, DeptNo FROM Employees WHERE Job IN (SELECT DISTINCT Job FROM Employees WHERE DeptNo = 10); -- ALL:大於子查詢所有值 = 大於最大值 SELECT EName, Sal, DeptNo FROM Employees WHERE Sal > ALL (SELECT Sal FROM Employees WHERE DeptNo = 30); -- 等價優化寫法: WHERE Sal > (SELECT MAX(Sal) FROM Employees WHERE DeptNo = 30); -- ANY:大於子查詢任一值 = 大於最小值 WHERE Sal > ANY (SELECT Sal FROM Employees WHERE DeptNo = 30); -- 等價: WHERE Sal > (SELECT MIN(Sal) FROM Employees WHERE DeptNo = 30);
IN vs EXISTS 性能法則:
• 子查詢結果集小、外表大 → IN 優(可用索引)
• 子查詢結果集大、外表小 → EXISTS 優(早期終止)
• 現代優化器多能自動轉換,但 EXISTS 语义更清晰:
WHERE EXISTS (SELECT 1 FROM ... WHERE correlation_condition)
SELECT * FROM Employees WHERE (Job, DeptNo) = (SELECT Job, DeptNo FROM Employees WHERE EName = 'SMITH');
SELECT E.DeptNo, E.EName, E.Sal, T.AvgSal
FROM Employees E
JOIN (
SELECT DeptNo, AVG(Sal) AS AvgSal FROM Employees GROUP BY DeptNo
) T ON E.DeptNo = T.DeptNo
WHERE E.Sal > T.AvgSal
ORDER BY E.DeptNo;
-- 標準思路:ROW_NUMBER() 窗口函數 (SQL Server 2012+, PG, Oracle 12c+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY Sal DESC) AS RN
FROM Employees
) T
WHERE RN BETWEEN 6 AND 10; -- 第 2 頁,每頁 5 條
-- MySQL / PostgreSQL 簡寫
SELECT * FROM Employees ORDER BY Sal DESC LIMIT 5 OFFSET 5;
-- SQL Server 2012+ OFFSET-FETCH
SELECT * FROM Employees ORDER BY Sal DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
-- UNION:並集(去重) SELECT EName, Sal, Job FROM Employees WHERE Sal > 2500 UNION SELECT EName, Sal, Job FROM Employees WHERE Job = 'MANAGER'; -- UNION ALL:並集(保留重複,性能更好) -- INTERSECT:交集 -- EXCEPT / MINUS:差集(第一個結果集減去第二個)
CREATE TABLE MyEmp AS SELECT EmpNo, EName, Sal, Job, DeptNo FROM Employees; -- PG/Oracle/MySQL -- SQL Server SELECT EmpNo, EName, Sal, Job, DeptNo INTO MyEmp FROM Employees;
INSERT INTO MyEmp (EmpID, Name, Sal, Job, Dept) SELECT EmpNo, EName, Sal, Job, DeptNo FROM Employees WHERE DeptNo = 10;
UPDATE Employees SET (Job, Sal, Comm) = (SELECT Job, Sal, Comm FROM Employees WHERE EName = 'SMITH') WHERE EName = 'SCOTT';
| 場景 | 推薦寫法 | 避免寫法 |
|---|---|---|
| 存在性檢查 | EXISTS (SELECT 1 ...) | IN (SELECT ...)、COUNT(*) > 0 |
| 分頁大偏移量 | 鍵集分頁 / 游標分頁 | OFFSET 大數值 |
| 多表關聯 | ANSI JOIN 語法 + 索引 | 舊式 WHERE 連接、隱式轉換 |
| 字符串拼接 | 參數化查詢 | 字符串拼接 SQL(注入風險) |
| 列名 | 顯式列名 | SELECT * |
記住:SQL 是聲明式語言,描述「要什麼」而非「怎麼做」。理解優化器如何將邏輯轉為物理執行計劃,比背誦語法更重要。EXPLAIN / 顯示執行計劃,是通往 SQL 專家之路的必經之路。