MySQL 常用查詢語句 23 種完整速查手冊：從基礎過濾到進階分組統計

一、數值型資料查詢

SELECT * FROM tb_name WHERE sum > 100;
-- 謂詞：>, =, <, <>, !=, !>, !<, >=, <=

二、字串查詢與模糊匹配

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%';   -- 包含匹配

三、日期型資料查詢（跨資料庫差異）

-- MySQL / SQL Server
SELECT * FROM tb_name WHERE birthday = '2011-04-08';
-- Access
SELECT * FROM tb_name WHERE birthday = #2011-04-08#;

四、邏輯型資料查詢

SELECT * FROM tb_name WHERE type = 'T';  -- TRUE
SELECT * FROM tb_name WHERE type = 'F';  -- FALSE
-- 邏輯運算子：AND, OR, NOT

五、非空資料查詢

SELECT * FROM tb_name WHERE address <> '' ORDER BY addtime DESC;
-- <> 相當於 !=

六、變數查詢數值型（防注入參數化）

-- PHP 範例：變數直接串接，資料庫驅動自動處理型別轉換
SELECT * FROM tb_name WHERE id = '$_POST[text]';

七、變數查詢字串型

SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%';
-- %_% 表示任意位置匹配

八、查詢前 N 筆記錄

SELECT * FROM tb_name LIMIT 0, $N;
-- 可搭配 ORDER BY 等子句靈活變化

九、查詢後 N 筆記錄

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n;

十、查詢指定位置起 N 筆（分頁核心）

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin], $n;
-- 注意：id 從 0 開始計算

十一、統計結果前 N 名

SELECT *, (yw+sx+wy) AS total 
FROM tb_score 
ORDER BY (yw+sx+wy) DESC 
LIMIT 0, $num;

十二、指定時間區間查詢

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18;
-- 語法：WHERE 欄位 BETWEEN 起始值 AND 終止值

十三、按月份統計查詢

SELECT * FROM tb_stu 
WHERE MONTH(date) = '$_POST[date]' 
ORDER BY date;
-- 日期函數：YEAR(), MONTH(), DAY()

十四、大於指定條件查詢

SELECT * FROM tb_stu WHERE age > $_POST[age] ORDER BY age;

十五、去重查詢

SELECT DISTINCT 欄位名 FROM 表名 WHERE 條件;
-- DISTINCT 必須配合 WHERE，欄位不能用 *

十六、NOT 組合條件查詢

-- NOT BETWEEN ... AND ...  →  < 起始值 AND > 終止值
-- IS NOT NULL  -- 非空查詢
-- IS NULL      -- 空值查詢
-- NOT IN       -- 排除列表值（常用於子查詢）

十七、顯示重複記錄與筆數

SELECT name, age, COUNT(*), age 
FROM tb_stu 
WHERE age = '19' 
GROUP BY date;

十八、單欄位排序

SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 DESC;  -- 降序
SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 ASC;   -- 升序（預設）

十九、多欄位排序

SELECT 欄位名 FROM tb_stu WHERE 條件 
ORDER BY 欄位1 ASC, 欄位2 DESC ...;
-- 共同限制記錄輸出順序

二十、統計結果排序

SELECT name, SUM(price) AS sumprice 
FROM tb_price 
GROUP BY name;

SELECT * FROM tb_name 
ORDER BY mount DESC, price ASC;

二十一、單欄位分組統計

SELECT id, name, SUM(price) AS title, date 
FROM tb_price 
GROUP BY pid 
ORDER BY title DESC;
-- GROUP BY 必須寫在 ORDER BY 之前

二十二、多欄位分組統計

SELECT id, name, SUM(price*num) AS sumprice 
FROM tb_price 
GROUP BY pid 
ORDER BY sumprice DESC;
-- GROUP BY 後接非聚合欄位

二十三、多表分組統計

SELECT a.name, AVG(a.price), b.name, AVG(b.price) 
FROM tb_demo058 AS a, tb_demo058_1 AS b 
WHERE a.id = b.id 
GROUP BY b.type;

速查總結表