MySQL: MySQL基础查询SELECT语法详解
最后更新：2026-08-26
SELECT 是 SQL 中使用最频繁的语句——几乎所有的数据读取都从它开始。
本课系统讲解 SELECT 的核心语法和常用操作。
graph TB
A[SELECT 查询流程] --> B[FROM<br/>指定数据源]
B --> C[WHERE<br/>条件过滤]
C --> D[GROUP BY<br/>分组]
D --> E[HAVING<br/>过滤分组]
E --> F[SELECT<br/>选择字段]
F --> G[DISTINCT<br/>去重]
G --> H[ORDER BY<br/>排序]
H --> I[LIMIT<br/>分页]
1. 你将学到
- SELECT 字段选择（全部/指定/别名）
- WHERE 条件过滤
- DISTINCT 去重查询
- ORDER BY 排序
- LIMIT 分页
2. 一个报表的真实故事
(1) 痛点：数据太多看不完
一个数据分析师要从 10 万条订单中找出"上个月金额最高的 10 个客户"。
手动翻页？Excel 筛选？都不高效。
(2) SELECT 的解法
SQL
SELECT
customer_name,
SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01'
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;
收益： 一条 SQL，秒级返回结果。
3. SELECT 基础语法
▶ 示例：查询所有字段
SQL
-- 查询所有字段（生产环境慎用）
SELECT * FROM employees;
-- 查询指定字段（推荐）
SELECT first_name, last_name, salary FROM employees;
▶ 示例：字段别名
SQL
-- AS 关键字设置别名
SELECT
first_name AS 名,
last_name AS 姓,
salary AS 薪资
FROM employees;
-- 省略 AS（效果相同）
SELECT first_name 名, last_name 姓 FROM employees;
-- 别名包含空格需加引号
SELECT first_name AS 'First Name' FROM employees;
输出：
TEXT
📖 仅展示
+--------+--------+--------+
| 名 | 姓 | 薪资 |
+--------+--------+--------+
| John | Smith | 5000 |
| Jane | Doe | 6000 |
+--------+--------+--------+
▶ 示例：表达式计算
SQL
-- 计算年薪
SELECT
first_name,
salary,
salary * 12 AS annual_salary
FROM employees;
-- 字符串拼接
SELECT
CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
4. WHERE 条件过滤
▶ 示例：基本条件
SQL
-- 等值查询
SELECT * FROM employees WHERE department = 'Engineering';
-- 不等查询
SELECT * FROM employees WHERE salary != 5000;
-- 范围查询
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;
-- 列表查询
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'HR');
-- 模糊查询
SELECT * FROM employees WHERE first_name LIKE 'J%';
-- NULL 查询
SELECT * FROM employees WHERE manager_id IS NULL;
▶ 示例：组合条件
SQL
-- AND 组合
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 6000;
-- OR 组合
SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
-- 混合组合（用括号明确优先级）
SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
AND salary > 5000;
-- NOT 取反
SELECT * FROM employees WHERE NOT (department = 'HR');
5. DISTINCT 去重
▶ 示例：去重查询
SQL
-- 单字段去重
SELECT DISTINCT department FROM employees;
-- 多字段组合去重
SELECT DISTINCT department, job_title FROM employees;
-- 统计去重数量
SELECT COUNT(DISTINCT department) AS dept_count FROM employees;
输出：
TEXT
📖 仅展示
+-------------+
| department |
+-------------+
| Engineering |
| Sales |
| Marketing |
| HR |
+-------------+
6. ORDER BY 排序
▶ 示例：排序查询
SQL
-- 单字段升序（默认）
SELECT * FROM employees ORDER BY salary ASC;
-- 单字段降序
SELECT * FROM employees ORDER BY salary DESC;
-- 多字段排序
SELECT * FROM employees
ORDER BY department ASC, salary DESC;
-- 按表达式排序
SELECT
first_name,
salary,
salary * 12 AS annual
FROM employees
ORDER BY annual DESC;
-- 按字段位置排序（第2列）
SELECT first_name, salary FROM employees ORDER BY 2 DESC;
输出：
TEXT
📖 仅展示
+------------+--------+
| first_name | salary |
+------------+--------+
| Charlie | 8000 |
| Jane | 6000 |
| John | 5000 |
+------------+--------+
7. LIMIT 分页
▶ 示例：分页查询
SQL
-- 查询前 10 条
SELECT * FROM employees LIMIT 10;
-- 跳过前 10 条，取 10 条（第 2 页）
SELECT * FROM employees LIMIT 10 OFFSET 10;
-- 简写形式：LIMIT offset, count
SELECT * FROM employees LIMIT 10, 10; -- 第 2 页
-- 分页公式：LIMIT (page-1) * pageSize, pageSize
-- 第 3 页，每页 20 条
SELECT * FROM employees LIMIT 40, 20;
▶ 示例：分页实战
SQL
-- 第 1 页
SELECT * FROM products ORDER BY id LIMIT 0, 10;
-- 第 2 页
SELECT * FROM products ORDER BY id LIMIT 10, 10;
-- 第 3 页
SELECT * FROM products ORDER BY id LIMIT 20, 10;
8. 完整查询示例
▶ 示例：综合查询
SQL
-- 查询 Engineering 部门工资最高的 5 名员工
SELECT
CONCAT(first_name, ' ', last_name) AS employee_name,
department,
salary,
hire_date
FROM employees
WHERE department = 'Engineering'
AND salary IS NOT NULL
ORDER BY salary DESC
LIMIT 5;
-- 查询 2025 年入职的员工，按部门分组统计
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY department
ORDER BY avg_salary DESC;
❓ 常见问题
Q SELECT * 有什么问题？
A 生产环境避免用
*——返回所有字段浪费网络和内存，表结构变更可能破坏代码。明确列出需要的字段。Q WHERE 和 HAVING 有什么区别？
A WHERE 在分组前过滤行，HAVING 在分组后过滤组。WHERE 不能用聚合函数，HAVING 可以。
Q ORDER BY 多字段时的优先级？
A 先按第一个字段排序，第一个相同时再按第二个排序。
Q LIMIT offset 很大时性能差怎么办？
A 大偏移量（LIMIT 100000, 10）性能很差。改用游标分页：
WHERE id > last_id LIMIT 10。Q DISTINCT 会影响性能吗？
A DISTINCT 需要排序去重，数据量大时较慢。如果不需要去重，不要加 DISTINCT。
📖 小节
- SELECT 查询数据，
*查所有字段，推荐指定字段名 - WHERE 过滤条件，支持
= / != / < / > / BETWEEN / LIKE / IN / IS NULL - DISTINCT 去重，可作用于单字段或多字段组合
- ORDER BY 排序，ASC 升序（默认），DESC 降序
- LIMIT 分页，
LIMIT offset, count或LIMIT count OFFSET offset - 大偏移量分页用游标分页优化
📝 作业
-
基础题（难度⭐）：查询
employees表中工资大于 5000 的员工，按工资降序排列，只显示前 5 条。 -
进阶题（难度⭐⭐）：查询
orders表中 2026 年的订单，按月统计订单数量和总金额，按月份排序。 -
挑战题（难度⭐⭐⭐）：实现一个分页查询，每页 20 条，查询第 5 页的数据，并对比 LIMIT 大偏移量和游标分页的性能差异。
