您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL DML 語法全解析：SELECT 進階用法與實戰範例

編輯：MySQL 資料庫相關知識

SELECT 完整語法骨架

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options
     | INTO DUMPFILE 'file_name'
     | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

簡化版核心語法

SELECT column1, column2, ...
FROM table1, table2, ...
[WHERE condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
[LIMIT ...]

基礎查詢實戰

建表示例

CREATE TABLE jokes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    joketext TEXT,
    jokedate DATE
) ENGINE=MyISAM CHARSET=utf8;

INSERT INTO jokes VALUES ('', 'why not?', NOW());

常用查詢模式

場景	SQL	說明
計算列	`SELECT 1+1 AS total;`	無需 FROM 子句
全表查詢	`SELECT * FROM jokes;`	返回所有列
投影（選列）	`SELECT id, jokedate FROM jokes;`	僅返回需要的列
字符串截取預覽	`SELECT id, LEFT(joketext,3) AS content, jokedate FROM jokes;`	用 LEFT/SUBSTRING
統計行數	`SELECT COUNT(*) AS records FROM jokes;`	聚合函數
條件計數	`SELECT COUNT(*) FROM jokes WHERE jokedate >= '2000-01-01';`	WHERE 過濾
模糊搜索	`SELECT joketext FROM jokes WHERE joketext LIKE '%not%';`	% 任意字符，_ 單字符
組合條件	`SELECT joketext FROM jokes WHERE joketext LIKE '%not%' AND jokedate BETWEEN '2000-04-01' AND '2000-04-30';`	AND/OR 邏輯

分組聚合（GROUP BY）

-- 每個部門最高薪資
SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno;

-- 注意：SELECT 列表中若含聚合函數，非聚合列必須出現在 GROUP BY 中

子查詢與嵌套查詢

每個部門薪資最高的員工

SELECT * FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

薪資降序排列

SELECT * FROM emp ORDER BY sal DESC;

LIMIT 分頁與截取

-- 前 5 筆（薪資最低）
SELECT * FROM emp ORDER BY sal LIMIT 5;
-- 或 LIMIT 0,5

-- 第 3~7 筆（偏移量從 0 開始）
SELECT * FROM emp LIMIT 2,5;

關鍵點： LIMIT offset, count 中 offset 從 0 開始；大偏移量分頁性能差，建議用游標/ID 續傳分頁。

進階 SELECT 選項速查

選項	作用
`SQL_CALC_FOUND_ROWS`	配合 `FOUND_ROWS()` 獲取無 LIMIT 時總行數
`SQL_CACHE / SQL_NO_CACHE`	控制查詢緩存行為
`STRAIGHT_JOIN`	強制按 FROM 順序連接表
`HIGH_PRIORITY`	提升 SELECT 優先級（MyISAM）
`FOR UPDATE`	加排他鎖（事務中）
`LOCK IN SHARE MODE`	加共享鎖