SQL查询是数据库操作的核心，掌握常用语句能显著提升数据处理效率。下面系统梳理16类经典查询场景，结合示例代码和性能分析，帮助开发者构建完整的SQL知识体系。
一、基础数据检索
-- 全表查询（慎用大数据表）
SELECT * FROM employees;
-- 指定列查询（推荐明确字段）
SELECT name, job_title, salary FROM employees;
执行原理： 数据库引擎通过全表扫描（Table Scan）或索引扫描（Index Scan）获取数据。当表数据量超过10万行时，建议添加WHERE条件或分页。
二、条件过滤（WHERE）
-- 数值比较
SELECT * FROM employees WHERE salary > 8000;
-- 日期范围
SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';
-- 多重条件
SELECT *
FROM employees
WHERE department_id = 3 AND salary >= 6000;
索引优化： 在salary和hire_date字段创建索引可使查询速度提升5-10倍：
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_hire_date ON employees(hire_date);
三、结果排序（ORDER BY）
-- 单字段排序
SELECT name, salary
FROM employees
ORDER BY salary DESC; -- 降序排列
-- 多字段排序
SELECT name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;
性能注意： 排序操作消耗内存资源，大数据集排序可能导致临时表创建。添加LIMIT可缓解：
SELECT ... ORDER BY salary DESC LIMIT 100;
四、数据分组统计（GROUP BY）
-- 部门平均薪资
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- 各部门最高薪和人数
SELECT department_id,
MAX(salary) AS max_salary,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
分组原理： 数据库创建临时分组表，计算聚合函数。GROUP BY字段加索引可优化性能。
五、分组后过滤（HAVING）
-- 筛选平均薪资>7000的部门
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 7000;
-- 对比WHERE（分组前过滤）
SELECT department_id, AVG(salary)
FROM employees
WHERE salary > 5000 -- 先过滤个体
GROUP BY department_id;
关键区别： WHERE在分组前过滤行，HAVING在分组后过滤组。
六、表连接查询（JOIN）
示例表结构：
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(100)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT REFERENCES departments(id),
salary DECIMAL(10,2)
);
1. 内连接（INNER JOIN）
SELECT e.name, d.name AS dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
结果特点：仅返回两表匹配的记录
2. 左连接（LEFT JOIN）
SELECT e.name, d.name AS dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
结果特点：包含所有员工，无部门员工显示NULL
3. 全外连接（FULL OUTER JOIN）
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
应用场景：包含所有员工和所有部门，无对应关系处显示NULL
七、子查询（Subquery）
1. 标量子查询
-- 查询高于平均薪资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
2. 行子查询
-- 查找与ID100员工同部门同职位的其他人
SELECT name
FROM employees
WHERE (department_id, job_title) = (
SELECT department_id, job_title
FROM employees
WHERE id = 100
);
3. EXISTS子查询
-- 查找有员工的部门
SELECT name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
八、分页查询
MySQL语法
SELECT id, name
FROM employees
ORDER BY id
LIMIT 10 OFFSET 20; -- 跳过20条取10条（第3页）
SQL Server语法
SELECT id, name
FROM employees
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
性能陷阱： 深分页（如OFFSET 10000）效率低下，改用基于ID的连续分页：
SELECT ... WHERE id > 10000 ORDER BY id LIMIT 10;
九、联合查询（UNION）
-- 合并在职和离职员工表
SELECT id, name, 'active' AS status
FROM active_employees
UNION ALL
SELECT id, name, 'inactive'
FROM inactive_employees;
区别：
UNION：自动去重，有排序开销
UNION ALL：保留重复，效率更高
十、CASE条件表达式
-- 薪资分级
SELECT name, salary,
CASE
WHEN salary > 10000 THEN 'A'
WHEN salary BETWEEN 7000 AND 10000 THEN 'B'
ELSE 'C'
END AS grade
FROM employees;
-- 配合聚合函数
SELECT
SUM(CASE WHEN salary > 8000 THEN 1 ELSE 0 END) AS high_paid,
COUNT(*) AS total
FROM employees;
十一、窗口函数（Window Functions）
-- 部门内薪资排名
SELECT name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 计算移动平均
SELECT date, revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
AS moving_avg
FROM sales;
常用函数： ROW_NUMBER(), LEAD(), LAG(), NTILE(), SUM() OVER()
十二、日期处理
-- 提取年份
SELECT name, EXTRACT(YEAR FROM hire_date) AS hire_year;
-- 日期加减
SELECT name, hire_date,
hire_date + INTERVAL '1 year' AS anniversary
FROM employees;
-- 日期格式化
SELECT name, TO_CHAR(hire_date, 'YYYY-MM-DD') AS formatted_date;
十三、空值处理
-- 将NULL转为默认值
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;
-- 空值判断
SELECT name
FROM employees
WHERE bonus IS NULL;
-- NULLIF避免除零错误
SELECT revenue / NULLIF(visitors, 0) AS rpv
FROM metrics;
十四、全文搜索
PostgreSQL示例
-- 创建全文索引
CREATE INDEX idx_fts ON employees
USING gin(to_tsvector('english', job_description));
-- 关键词搜索
SELECT name
FROM employees
WHERE to_tsvector('english', job_description)
@@ to_tsquery('developer & (python | java)');
十五、递归查询
组织层级表结构：
CREATE TABLE org_structure (
id INT PRIMARY KEY,
name VARCHAR(50),
parent_id INT REFERENCES org_structure(id)
);
递归查询所有下属：
WITH RECURSIVE subordinates AS (
SELECT id, name, parent_id
FROM org_structure
WHERE id = 100 -- 指定上级ID
UNION
SELECT os.id, os.name, os.parent_id
FROM org_structure os
INNER JOIN subordinates s ON os.parent_id = s.id
)
SELECT * FROM subordinates;
十六、动态SQL（存储过程）
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
SET @sql = CONCAT('SELECT * FROM employees WHERE department_id = ', dept_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
性能优化黄金法则
索引策略
WHERE/JOIN字段建索引
避免对索引列使用函数（如WHERE YEAR(date)=2023）
复合索引遵循最左匹配原则
避免全表扫描
数据量>1万时，必须添加查询条件
用EXPLAIN分析执行计划
减少网络传输
只SELECT必要字段
分页处理大数据集
事务优化
批量操作使用事务提交
避免长事务锁定资源
-- 查询执行计划（MySQL示例）
EXPLAIN SELECT name FROM employees WHERE salary > 6000;
真实场景综合应用
需求：生成各部门薪资报告，包含：
部门名称和地点
员工总数和高薪（>8000）人数
平均薪资和最高薪资
薪资中位数
WITH dept_stats AS (
SELECT
d.id,
d.name AS dept_name,
d.location,
COUNT(e.id) AS total_employees,
SUM(CASE WHEN e.salary > 8000 THEN 1 ELSE 0 END) AS high_paid_count,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.salary) AS median_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name, d.location
)
SELECT
dept_name,
location,
total_employees,
high_paid_count,
ROUND(avg_salary, 2) AS avg_salary,
max_salary,
median_salary,
ROUND(high_paid_count * 100.0 / NULLIF(total_employees,0), 1) AS high_paid_percent
FROM dept_stats
ORDER BY avg_salary DESC;
通过系统掌握这些经典查询模式，开发者能应对90%的日常数据操作需求。重点在于理解每类语句的执行逻辑和适用场景，避免在复杂查询中过度嵌套影响性能。实际工作中应结合数据库的特定优化特性（如MySQL的索引下推、PostgreSQL的JIT编译）进行深度调优。