萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL 常用查詢語句大全:從基礎過濾到複雜統計的 23 類實戰模板

查詢語句分類速查表

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 更新統計信息,優化器決策更準
copyright © 萬盛學電腦網 all rights reserved