SQL查询语句大全：16类经典场景详解与性能优化指南

发布时间：2025-08-09 05:31:23
本文热度：浏览 16 赞 0 评论 0
文章标签： SQL 数据库 Mysql
全文共1字，阅读约需1分钟

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编译）进行深度调优。

正文到此结束

所属分类：Mysql

本文链接： https://refblogs.com/article/1142
版权声明： 本文由老牛原创发布，转载或复制请以超链接形式转载,并注明出处搬砖的码农。