SELECT select_list
FROM table_references
WHERE search_condition
[ORDER BY ...] [LIMIT ...];
search_condition 由一個或多個條件通過 AND、OR、NOT 組合,優先級:NOT > AND > OR,建議用括號明確分組。
| 運算符 | 含義 | 示例 (A=10, B=20) |
|---|---|---|
= | 等於 | (A = B) → false |
!= / <> | 不等於 | (A != B) → true |
> | 大於 | (A > B) → false |
< | 小於 | (A < B) → true |
>= | 大於等於 | (A >= B) → false |
<= | 小於等於 | (A <= B) → true |
IS NULL | 為 NULL | col IS NULL |
IS NOT NULL | 不為 NULL | col IS NOT NULL |
BETWEEN ... AND ... | 閉區間範圍 | price BETWEEN 100 AND 500 |
IN (val1, val2, ...) | 集合成員 | cat_id IN (4, 11) |
NOT IN | 非集合成員 | cat_id NOT IN (3, 11) |
%:匹配任意長度字符(含 0 個)_:匹配**單個**字符-- 以「諾基亞」開頭
WHERE goods_name LIKE '諾基亞%'
-- 名稱為「諾基亞Nxx」(N 後兩位任意字符)
WHERE goods_name LIKE '諾基亞N__'
-- 包含「充電器」
WHERE goods_name LIKE '%充電器%'
-- 區分大小寫匹配(二進制比較)
WHERE BINARY tutorial_author = 'sanjay' -- 不匹配 'Sanjay'
WHERE tutorial_author COLLATE utf8mb4_bin = 'sanjay' -- 同效果,推薦
| 模式 | 能否用索引 | 說明 |
|---|---|---|
LIKE 'prefix%' | ✅ 是 | 前綴匹配,B+Tree 範圍掃描 |
LIKE '%suffix' | ❌ 否 | 前導通配符,全表掃描 |
LIKE '%middle%' | ❌ 否 | 雙邊通配符,全表掃描 |
LIKE '_exact' | ❌ 否 | 單字符通配符同樣阻礙索引 |
優化策略: 全文搜索需求請用 Fulltext Index (MyISAM/InnoDB 5.6+) 或 Elasticsearch;前綴查詢確保列有索引且無函數包裹。
-- 1. 靜態列表
WHERE status IN ('active', 'pending', 'review')
-- 2. 子查詢返回單列
WHERE user_id IN (SELECT user_id FROM vip_users WHERE expire_at > NOW())
-- 3. 應用層構建(防注入)
$ids = [4,2,3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT * FROM test WHERE id IN ($placeholders) ORDER BY FIELD(id, " . implode(',', $ids) . ")";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat('i', count($ids)), ...$ids);
IN / JOIN 通常更快。EXISTS 往往更優(可短路)。IN (subquery) 轉為 SEMI JOIN,性能差異縮小。-- 標量子查詢:返回單值
SELECT * FROM goods WHERE shop_price > (SELECT AVG(shop_price) FROM goods);
-- 列子查詢:返回單列多行,配合 IN / ANY / ALL
SELECT * FROM goods WHERE cat_id IN (SELECT cat_id FROM category WHERE parent_id = 0);
-- 行子查詢:返回一行多列
SELECT * FROM t1 WHERE (col1, col2) = (SELECT col1, col2 FROM t2 WHERE id = 1);
-- 每個分類最新商品(MySQL 8.0+ 推薦窗口函數 ROW_NUMBER())
SELECT * FROM (
SELECT g.*, ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY goods_id DESC) AS rn
FROM ecs_goods g
) t WHERE rn = 1;
-- 兼容舊版:派生表 + GROUP BY 技巧
SELECT * FROM (
SELECT goods_id, cat_id, goods_name FROM ecs_goods ORDER BY goods_id DESC
) AS t GROUP BY cat_id;
-- 查詢每個分類價格高於該分類平均價的商品
SELECT * FROM ecs_goods g1
WHERE shop_price > (
SELECT AVG(shop_price) FROM ecs_goods g2 WHERE g2.cat_id = g1.cat_id
);
優化: 相關子查詢可改寫為JOIN+GROUP BY或窗口函數,避免逐行執行的 N+1 問題。
SELECT g.goods_id, g.goods_name, g.shop_price, g.click_count, c.cat_name
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id
WHERE g.cat_id = 3 -- 指定分類
AND (g.shop_price < 1000 OR g.shop_price > 3000) -- 價格區間 OR
AND g.click_count > 5 -- 點擊量閾值
AND g.goods_name LIKE '諾基亞%' -- 品牌前綴
ORDER BY g.shop_price DESC
LIMIT 20;
%、善用複合索引最左前綴。NOT IN 含 NULL 會返回空集,改用 NOT EXISTS。WITH) 和窗口函數,提升可讀性與性能。