查詢語句分類速查表
1. 數值型條件查詢
SELECT * FROM tb_name WHERE score > 100;
-- 謂詞: >, =, <, <>, !=, !>, !<, >=, <=
2. 字符串模糊查詢
SELECT * FROM tb_stu WHERE sname = '小劉'; -- 精確
SELECT * FROM tb_stu WHERE sname LIKE '劉%'; -- 前綴 (可用索引)
SELECT * FROM tb_stu WHERE sname LIKE '%程序員'; -- 後綴 (全掃)
SELECT * FROM tb_stu WHERE sname LIKE '%PHP%'; -- 包含 (全掃)
3. 日期型查詢 (跨數據庫差異)
-- MySQL / SQL Server
SELECT * FROM tb_stu WHERE birthday = '2011-04-08';
-- Access
SELECT * FROM tb_stu WHERE birthday = #2011-04-08#;
-- 範圍查詢 (通用)
SELECT * FROM tb_stu WHERE birthday BETWEEN '2011-01-01' AND '2011-12-31';
4. 邏輯/空值查詢
-- 布爾值
SELECT * FROM tb_name WHERE is_active = 'T';
SELECT * FROM tb_name WHERE is_active = 'F';
-- 非空
SELECT * FROM tb_name WHERE address <> '' ORDER BY addtime DESC;
-- 或
SELECT * FROM tb_name WHERE address IS NOT NULL;
-- 空值
SELECT * FROM tb_name WHERE address IS NULL;
5. 變量參數化查詢 (防注入)
-- 數值型
SELECT * FROM tb_name WHERE id = ?; -- 預處理綁定參數
-- 字符串模糊
SELECT * FROM tb_name WHERE name LIKE CONCAT('%', ?, '%');
6. 分頁查詢 (核心模板)
-- 前 N 條
SELECT * FROM tb_name LIMIT 0, 20;
-- 後 N 條
SELECT * FROM tb_stu ORDER BY id DESC LIMIT 20;
-- 指定偏移 N 條 (第 3 頁,每頁 20)
SELECT * FROM tb_stu ORDER BY id ASC LIMIT 40, 20;
-- 或 LIMIT 20 OFFSET 40 (8.0+ 標準語法)
7. 統計排序 Top-N
SELECT *, (chinese + math + english) AS total
FROM tb_score
ORDER BY total DESC
LIMIT 10;
8. 時間區間查詢
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18;
-- 等價: age >= 0 AND age <= 18
9. 按年/月/日統計
SELECT * FROM tb_stu WHERE MONTH(birthday) = 3 ORDER BY birthday;
-- 函數: YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
10. 比較運算 + 排序
SELECT * FROM tb_stu WHERE age > 18 ORDER BY age ASC;
11. 去重查詢
SELECT DISTINCT department FROM employees WHERE department IS NOT NULL;
-- 注意: DISTINCT 作用於所有選中列組合
12. NOT 謂詞組合
-- NOT BETWEEN
SELECT * FROM tb WHERE age NOT BETWEEN 18 AND 60;
-- 等價: age < 18 OR age > 60
-- IS NOT NULL / IS NULL
SELECT * FROM tb WHERE email IS NOT NULL;
-- NOT IN (子查詢慎用,大表改 NOT EXISTS)
SELECT * FROM tb WHERE id NOT IN (SELECT user_id FROM orders);
13. 重複記錄統計
SELECT name, age, COUNT(*) AS cnt
FROM tb_stu
WHERE age = 19
GROUP BY name, age
HAVING COUNT(*) > 1;
14. 多字段排序
-- 單字段
SELECT * FROM tb_stu ORDER BY score DESC; -- 降序
SELECT * FROM tb_stu ORDER BY score ASC; -- 升序 (默認)
-- 多字段
SELECT * FROM tb_stu ORDER BY class ASC, score DESC, name ASC;
15. 聚合統計排序
SELECT name, SUM(price) AS total_price
FROM tb_price
GROUP BY name
ORDER BY total_price DESC;
16. 單列分組統計
SELECT id, name, SUM(price) AS total, date
FROM tb_price
GROUP BY pid
ORDER BY total DESC;
-- 注意: GROUP BY 寫在 ORDER BY 前面
17. 多列分組統計
SELECT id, name, SUM(price * num) AS total_amount
FROM tb_price
GROUP BY pid
ORDER BY total_amount DESC;
18. 多表分組統計
SELECT a.name, AVG(a.price), b.name, AVG(b.price)
FROM tb_demo058 AS a
JOIN tb_demo058_1 AS b ON a.id = b.id
GROUP BY a.name, b.name;
19. 多表關聯查詢 (JOIN 範式)
-- 內連接
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左連接 (保留無訂單用戶)
SELECT u.name, IFNULL(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 多表鏈式
SELECT u.name, o.amount, p.product_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN products p ON o.product_id = p.id;
20. 子查詢位置
-- WHERE 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- FROM 衍生表
SELECT d.dept_name, t.avg_salary
FROM departments d
JOIN (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) t
ON d.id = t.dept_id;
-- SELECT 標量子查詢
SELECT u.name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_cnt
FROM users u;
21. 條件表達式 (CASE WHEN)
SELECT
name,
CASE
WHEN score >= 90 THEN '優秀'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS grade
FROM students;
22. 窗口函數 (8.0+)
-- 排名
SELECT
name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM students;
-- 分組 Top-N
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn
FROM students
)
SELECT * FROM ranked WHERE rn <= 3;
23. CTE (公用表表達式, 8.0+)
WITH RECURSIVE org_tree AS (
SELECT id, name, parent_id, 1 AS level
FROM departments WHERE parent_id = 0
UNION ALL
SELECT d.id, d.name, d.parent_id, ot.level + 1
FROM departments d
JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;
性能優化金句
- ✅ SELECT 具體列,禁用
SELECT *
- ✅ WHERE 條件推到驅動表,減少 JOIN 行數
- ✅ 被驅動表關聯列建索引,覆蓋索引消除回表
- ✅ LIMIT 分頁深翻頁用延遲關聯:
SELECT * FROM t JOIN (SELECT id FROM t WHERE ... LIMIT 100000, 20) tmp USING(id)
- ✅ 定期 ANALYZE TABLE 更新統計信息,優化器決策更準