-- IN / BETWEEN / LIKE
SELECT * FROM db_user WHERE user_type IN (5,8);
SELECT * FROM db_user WHERE user_type BETWEEN 5 AND 8;
SELECT * FROM db_user WHERE user_name LIKE '%s%'; -- % 匹配任意長度
SELECT * FROM db_user WHERE user_name LIKE '_3_'; -- _ 匹配單字符
-- OR / AND
SELECT * FROM db_user WHERE user_name='a' OR password='3';
-- 排序
SELECT * FROM db_user ORDER BY id DESC; -- 降序
SELECT * FROM db_user ORDER BY id ASC; -- 升序
-- 分組去重
SELECT id, user_name, password FROM db_user GROUP BY user_type; -- 每組僅顯示一行
-- 限制條數
SELECT * FROM db_user ORDER BY id ASC LIMIT 3; -- 前 3 行
SELECT * FROM db_user ORDER BY id ASC LIMIT 1, 2; -- 跳過 1 行，取 2 行
SELECT COUNT(*) FROM db_user; -- 總行數
-- 內連接（隱式/顯式）
SELECT user_name, user_type FROM tp_user, db_user WHERE tp_user.id = db_user.id;
SELECT * FROM tp_user INNER JOIN db_user ON tp_user.id = db_user.id;
-- 子查詢
SELECT * FROM tp_user WHERE user_name IN (SELECT user_name FROM db_user);
SELECT * FROM tp_user WHERE money >= (SELECT money FROM db_user WHERE id=1);
SELECT * FROM tp_user WHERE money < ANY (SELECT money FROM db_user); -- 小於任一
SELECT * FROM tp_user WHERE money > ALL (SELECT money FROM db_user); -- 大於所有
SELECT user_name FROM tp_user UNION SELECT user_name FROM db_user; -- 合並去重
SELECT user_name FROM tp_user UNION ALL SELECT user_name FROM db_user; -- 全部保留
| 模式 | 含義 | 範例 |
|---|---|---|
^php | 以 php 開頭 | books REGEXP '^php' |
php$ | 以 php 結尾 | books REGEXP 'php$' |
.p | 任意字符 + p | books REGEXP '.p' |
[pca] | 含 p/c/a 任一 | books REGEXP '[pca]' |
[^c-z] | 非 c-z 字符 | books REGEXP '[^c-z]' |
php|java|c# | 或關係 | books REGEXP 'php|java|c#' |
J*A | A 前有 0+ 個 J | books REGEXP 'J*A' |
J+A | A 前有 1+ 個 J | books REGEXP 'J+A' |
a{3} | 連續 3 個 a | books REGEXP 'a{3}' |
a{2,4} | 連續 2~4 個 a | books REGEXP 'a{2,4}' |
SELECT MAX(score) FROM tp_user; -- 最高分
SELECT MIN(score) FROM tp_user; -- 最低分
SELECT AVG(score) FROM tp_user; -- 平均分
SELECT SUM(money) FROM tp_user; -- 總金額
% 多字符、_ 單字符LIMIT offset, count 分頁神器UNION 去重、UNION ALL 不去重（更快）ANY = 滿足任一、ALL = 滿足所有REGEXP 支援完整 POSIX 正則，比 LIKE 強大得多收藏此頁，寫 SQL 時隨查隨用，告別語法遺忘。
