50个SQL查询实战案例：从基础到高级应用

1. SQL查询技能提升的必要性在数据驱动的时代SQL作为关系型数据库的标准查询语言其重要性不言而喻。无论是数据分析师、后端开发工程师还是产品经理掌握SQL查询技能都能极大提升工作效率。我见过太多职场新人因为SQL基础薄弱而影响工作进度也见证过SQL高手如何通过一个精妙的查询节省数小时的人工分析时间。SQL学习最难的不是语法本身而是如何将业务需求转化为有效的查询语句。很多教程只讲解语法规则却缺少真实场景的案例演示。这正是我整理这50个常用查询案例的初衷——通过实际问题带大家理解SQL的思维方式。2. 基础查询案例解析2.1 单表基础查询我们从最简单的单表查询开始这是所有复杂查询的基础。假设我们有一个员工表employees-- 案例1查询所有员工信息 SELECT * FROM employees; -- 案例2查询特定列避免SELECT * 的生产环境实践 SELECT employee_id, first_name, last_name FROM employees; -- 案例3使用WHERE条件过滤 SELECT * FROM employees WHERE department IT; -- 案例4使用ORDER BY排序 SELECT * FROM employees ORDER BY hire_date DESC;注意在生产环境中尽量避免使用SELECT *明确列出需要的字段能减少网络传输和提高查询效率。2.2 条件查询进阶条件查询是SQL中最常用的功能之一掌握各种条件表达式至关重要-- 案例5多条件组合(AND/OR) SELECT * FROM employees WHERE department IT AND salary 8000; -- 案例6BETWEEN范围查询 SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000; -- 案例7IN操作符 SELECT * FROM employees WHERE department IN (IT, HR, Finance); -- 案例8LIKE模糊查询 SELECT * FROM employees WHERE last_name LIKE 张%;实操心得LIKE查询特别是前导通配符(%)会导致全表扫描大数据量表应考虑全文索引。3. 多表连接查询实战3.1 各种JOIN的区别与应用理解不同类型的JOIN是SQL进阶的关键-- 案例9INNER JOIN只返回匹配的记录 SELECT e.employee_id, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id d.department_id; -- 案例10LEFT JOIN保留左表所有记录 SELECT e.employee_id, e.last_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id d.department_id; -- 案例11RIGHT JOIN保留右表所有记录 SELECT e.employee_id, e.last_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id d.department_id; -- 案例12FULL OUTER JOIN返回所有记录 SELECT e.employee_id, e.last_name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id d.department_id;注意事项实际工作中LEFT JOIN使用频率最高RIGHT JOIN较少见FULL OUTER JOIN部分数据库不支持。3.2 多表连接复杂案例-- 案例13三表连接查询 SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e JOIN departments d ON e.department_id d.department_id JOIN locations l ON d.location_id l.location_id; -- 案例14自连接查询查询员工及其经理 SELECT e.employee_id, e.last_name, m.last_name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id m.employee_id;4. 聚合函数与分组查询4.1 常用聚合函数-- 案例15基本聚合函数 SELECT COUNT(*) AS employee_count, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, SUM(salary) AS total_salary FROM employees; -- 案例16GROUP BY分组 SELECT department, COUNT(*) AS employee_count, AVG(salary) FROM employees GROUP BY department; -- 案例17HAVING过滤分组 SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) 5;关键区别WHERE过滤行HAVING过滤分组。4.2 高级分组技巧-- 案例18GROUPING SETS多维度分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY GROUPING SETS ( (department, job_title), (department), (job_title), () ); -- 案例19ROLLUP层级分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY ROLLUP (department, job_title); -- 案例20CUBE所有组合分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY CUBE (department, job_title);5. 子查询与CTE应用5.1 各种子查询类型-- 案例21WHERE子句中的子查询 SELECT * FROM employees WHERE salary (SELECT AVG(salary) FROM employees); -- 案例22FROM子句中的子查询 SELECT dept_avg.department, dept_avg.avg_salary FROM ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) dept_avg WHERE dept_avg.avg_salary 8000; -- 案例23SELECT子句中的子查询 SELECT employee_id, last_name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees;5.2 公共表表达式(CTE)-- 案例24简单CTE WITH dept_stats AS ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ) SELECT * FROM dept_stats WHERE avg_salary 8000; -- 案例25递归CTE查询组织架构 WITH RECURSIVE org_chart AS ( -- 基础查询找出所有没有经理的员工顶级管理者 SELECT employee_id, last_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- 递归查询找出每个员工的下属 SELECT e.employee_id, e.last_name, e.manager_id, oc.level 1 FROM employees e JOIN org_chart oc ON e.manager_id oc.employee_id ) SELECT * FROM org_chart ORDER BY level, employee_id;6. 窗口函数高级应用6.1 排名函数-- 案例26ROW_NUMBER连续排名 SELECT employee_id, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -- 案例27RANK并列排名会跳过名次 SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -- 案例28DENSE_RANK并列排名不跳过名次 SELECT employee_id, last_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;6.2 分析函数-- 案例29部门内薪资排名 SELECT employee_id, last_name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; -- 案例30计算移动平均 SELECT order_date, amount, AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales; -- 案例31计算累计和 SELECT employee_id, month, sales, SUM(sales) OVER (PARTITION BY employee_id ORDER BY month) AS cumulative_sales FROM employee_sales;7. 数据修改操作7.1 插入数据-- 案例32基本INSERT INSERT INTO employees (employee_id, first_name, last_name, email) VALUES (1001, John, Doe, john.doeexample.com); -- 案例33插入多行数据 INSERT INTO employees (employee_id, first_name, last_name, email) VALUES (1002, Jane, Smith, jane.smithexample.com), (1003, Robert, Johnson, robert.johnsonexample.com); -- 案例34从查询结果插入 INSERT INTO employee_archive SELECT * FROM employees WHERE hire_date 2010-01-01;7.2 更新与删除-- 案例35基本UPDATE UPDATE employees SET salary salary * 1.05 WHERE department IT; -- 案例36使用JOIN更新 UPDATE employees e SET e.salary e.salary * 1.1 FROM departments d WHERE e.department_id d.department_id AND d.location New York; -- 案例37条件DELETE DELETE FROM employees WHERE hire_date 2000-01-01 AND performance_rating 3;8. 事务与并发控制8.1 事务基本操作-- 案例38显式事务 BEGIN TRANSACTION; UPDATE accounts SET balance balance - 1000 WHERE account_id 123; UPDATE accounts SET balance balance 1000 WHERE account_id 456; -- 如果执行到这里没有错误 COMMIT; -- 如果出现错误 -- ROLLBACK;8.2 隔离级别与锁-- 案例39设置隔离级别 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 案例40显式加锁 SELECT * FROM orders WHERE order_date 2023-01-01 FOR UPDATE; -- 排他锁9. 索引优化与查询性能9.1 索引创建与使用-- 案例41创建索引 CREATE INDEX idx_employees_department ON employees(department); -- 案例42复合索引 CREATE INDEX idx_employees_name ON employees(last_name, first_name); -- 案例43查看查询执行计划 EXPLAIN SELECT * FROM employees WHERE last_name Smith;9.2 查询优化技巧-- 案例44避免全表扫描 -- 不好的写法 SELECT * FROM employees WHERE YEAR(hire_date) 2020; -- 优化写法 SELECT * FROM employees WHERE hire_date BETWEEN 2020-01-01 AND 2020-12-31; -- 案例45LIMIT分页优化 -- 低效写法偏移量大时 SELECT * FROM employees ORDER BY employee_id LIMIT 10000, 20; -- 高效写法使用WHERE替代OFFSET SELECT * FROM employees WHERE employee_id 10000 ORDER BY employee_id LIMIT 20;10. 高级SQL特性10.1 存储过程与函数-- 案例46创建存储过程 DELIMITER // CREATE PROCEDURE raise_salary(IN dept VARCHAR(50), IN percent DECIMAL(5,2)) BEGIN UPDATE employees SET salary salary * (1 percent/100) WHERE department dept; END // DELIMITER ; -- 调用存储过程 CALL raise_salary(IT, 5.0); -- 案例47创建函数 CREATE FUNCTION get_employee_count(dept VARCHAR(50)) RETURNS INT BEGIN DECLARE emp_count INT; SELECT COUNT(*) INTO emp_count FROM employees WHERE department dept; RETURN emp_count; END; -- 使用函数 SELECT get_employee_count(IT);10.2 触发器应用-- 案例48创建触发器 CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary OLD.salary THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Salary cannot be decreased; END IF; END;10.3 视图与物化视图-- 案例49创建视图 CREATE VIEW employee_details AS SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e JOIN departments d ON e.department_id d.department_id JOIN locations l ON d.location_id l.location_id; -- 使用视图 SELECT * FROM employee_details WHERE city New York; -- 案例50物化视图不同数据库实现不同 -- MySQL示例 CREATE TABLE employee_details_mv AS SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e JOIN departments d ON e.department_id d.department_id JOIN locations l ON d.location_id l.location_id; -- 定期刷新 TRUNCATE TABLE employee_details_mv; INSERT INTO employee_details_mv SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e JOIN departments d ON e.department_id d.department_id JOIN locations l ON d.location_id l.location_id;11. SQL实战经验分享在实际工作中我发现有几个常见的SQL陷阱需要特别注意NULL值处理NULL与任何值的比较结果都是NULL而非FALSE这经常导致查询结果不符合预期。正确的做法是使用IS NULL或IS NOT NULL判断或者使用COALESCE函数提供默认值。-- 错误写法 SELECT * FROM employees WHERE commission_pct NULL; -- 不会返回任何结果 -- 正确写法 SELECT * FROM employees WHERE commission_pct IS NULL;日期时间处理不同数据库的日期函数差异很大建议使用标准SQL的日期函数避免数据库特定的函数。-- 标准SQL写法大多数数据库支持 SELECT * FROM orders WHERE order_date CURRENT_DATE - INTERVAL 30 DAY; -- 避免使用数据库特定函数如MySQL的DATE_SUB()JOIN性能优化多表JOIN时应该从小表驱动大表并确保JOIN条件上有适当的索引。EXPLAIN命令是分析查询性能的利器。批量操作当需要处理大量数据时批量操作比单条操作效率高得多。例如批量INSERT可以显著减少I/O操作。-- 低效 INSERT INTO table VALUES (1, A); INSERT INTO table VALUES (2, B); INSERT INTO table VALUES (3, C); -- 高效 INSERT INTO table VALUES (1, A), (2, B), (3, C);事务粒度事务不应该过长应该尽快提交释放锁资源。但同时也要确保事务包含完整的业务逻辑单元。最后SQL技能需要通过大量实践来掌握。建议读者在自己的数据库环境里运行这些案例尝试修改参数观察结果变化并针对自己的业务数据设计类似的查询。记住写出能工作的SQL只是第一步写出高效、可维护的SQL才是专业水准。