您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 查詢語句大全：從基礎到進階的完整參考指南

編輯：MySQL 綜合教學

MySQL 查詢語句全方位速查手冊

本文系統整理 MySQL 查詢語句核心語法與實戰範例，涵蓋基礎查詢、條件過濾、排序分組、多表連接、子查詢、聚合分析、資料操作（增刪改）等完整體系，適合開發人員快速查閱與 DBA 日常參考。

一、基礎查詢與投影

-- 全欄位查詢
SELECT * FROM pet;

-- 指定欄位投影
SELECT name, owner FROM pet;

-- 算術運算與別名
SELECT SIN(1+2) AS sin_value;

二、WHERE 條件過濾大全

1. 複合邏輯條件

SELECT * FROM pet 
WHERE (birth > '1980' AND species = 'dog') OR species = 'bird';

2. NULL 值判斷

SELECT * FROM pet WHERE sex IS NOT NULL;

3. 模糊匹配

-- 第 4 個字元為 n
SELECT * FROM pet WHERE owner LIKE '___n%';

4. 集合包含

SELECT * FROM pet WHERE owner IN ('gwen', 'benny');

5. 範圍查詢

SELECT * FROM pet WHERE birth BETWEEN '1990' AND '1999';

三、排序與分頁

-- 多欄位混合排序
SELECT * FROM pet ORDER BY owner ASC, name DESC;

-- 條件排序
SELECT * FROM pet WHERE sex = 'm' ORDER BY birth DESC;

四、字串與數值函數應用

-- 字串長度對比
SELECT owner, LENGTH(owner) AS byte_len, CHAR_LENGTH(owner) AS char_len FROM pet;

-- 數值函數
SELECT ROUND(2.345, 2), TRUNCATE(2.345, 2), MOD(323, 5);

五、日期時間函數實戰

SELECT NOW(), CURDATE(), CURTIME();
SELECT ADDDATE('2007-02-02', INTERVAL 31 DAY);

六、年齡計算經典範例

SELECT name, birth,
    TRUNCATE(DATEDIFF(NOW(), birth)/365, 0) AS age1,
    YEAR(NOW()) - YEAR(birth) - (DAYOFYEAR(birth) > DAYOFYEAR(NOW())) AS age2
FROM pet;

七、聚合與分組分析

基礎聚合

SELECT MIN(birth), MAX(birth), AVG(birth), COUNT(*), COUNT(sex), SUM(birth) FROM pet;

分組統計

-- 各物種數量
SELECT species, COUNT(*) FROM pet GROUP BY species;

-- 年齡最大寵物（相關子查詢）
SELECT * FROM pet WHERE birth = (SELECT MAX(birth) FROM pet);

HAVING 進階過濾

-- 數量 >= 2 的物種
SELECT species, COUNT(*) AS c FROM pet GROUP BY species HAVING c >= 2;

八、多表連接查詢

內連接

SELECT a.name, a.species, a.sex, b.date, b.type, b.remark
FROM pet a, event b
WHERE a.name = b.name;

複雜業務查詢

-- 寵物產仔年齡
SELECT a.name, a.species, TRUNCATE(DATEDIFF(b.date, a.birth)/365, 0) AS age
FROM pet a, event b
WHERE a.name = b.name AND b.type = 'litter';

-- 90 年代狗的事件
SELECT a.name, birth, species, sex, date, type, remark
FROM pet a, event b
WHERE a.name = b.name AND birth BETWEEN '1990' AND '1999' AND species = 'dog';

九、子查詢與集合運算

-- 事件數 >= 2 的寵物
SELECT a.name, species, sex, COUNT(*)
FROM pet a, event b
WHERE a.name = b.name
GROUP BY b.name
HAVING COUNT(*) >= 2;

-- 正好兩件事件的寵物詳細記錄
SELECT a.name, type, date, remark, b.species, b.sex, b.owner
FROM event a, pet b
WHERE a.name = b.name AND b.name IN (
    SELECT name FROM event GROUP BY name HAVING COUNT(*) = 2
);

十、資料操作語句（DML）

INSERT 多種寫法

INSERT INTO pet (name, species, birth) VALUES ('KKK', 'snake', '2007-01-01');
INSERT INTO pet VALUES ('KK', 'Diane', 'cat', 'f', NULL, NULL);
INSERT INTO pet SET name = 'k', owner = 'Benny';

UPDATE 進階用法

-- 基本更新
UPDATE pet SET species = 'snake', sex = 'f', birth = NOW() WHERE name = 'k';

-- 跨表更新：同步事件表生日到寵物表
UPDATE pet a
SET birth = (SELECT date FROM event b WHERE a.name = b.name AND b.type = 'birthday')
WHERE a.name IN (SELECT name FROM event WHERE type = 'birthday');

DELETE 條件刪除

DELETE FROM pet WHERE name LIKE 'k%';

十一、通用語法速查表

操作類別	語法範例
基本查詢	SELECT * FROM `test` WHERE 1
指定欄位	SELECT id, uid FROM newdb.`test` WHERE 1
別名查詢	SELECT remark AS r FROM `test` WHERE 1
IN 查詢	SELECT * FROM `test` WHERE id IN (1,2,4)
NOT IN	SELECT * FROM `test` WHERE id NOT IN (2,3)
模糊查詢	SELECT * FROM `test` WHERE `uid` LIKE '%王%'
範圍查詢	SELECT * FROM `test` WHERE id BETWEEN 1 AND 3
多條件	SELECT * FROM `test` WHERE id=1 AND `remark`='學生'
分組	SELECT * FROM `test` GROUP BY `remark`
排序	SELECT * FROM `test` ORDER BY `regdate` ASC, id DESC
分頁	SELECT * FROM `test` LIMIT 0,3
統計	SELECT COUNT(*), MAX(id), MIN(id), AVG(id), SUM(id) FROM `test`