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; -- 案例6：BETWEEN范围查询 SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000; -- 案例7：IN操作符 SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance'); -- 案例8：LIKE模糊查询 SELECT * FROM employees WHERE last_name LIKE '张%';

实操心得：LIKE查询特别是前导通配符(%)会导致全表扫描，大数据量表应考虑全文索引。

3. 多表连接查询实战

3.1 各种JOIN的区别与应用

理解不同类型的JOIN是SQL进阶的关键：

-- 案例9：INNER 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; -- 案例10：LEFT 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; -- 案例11：RIGHT 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; -- 案例12：FULL 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; -- 案例16：GROUP BY分组 SELECT department, COUNT(*) AS employee_count, AVG(salary) FROM employees GROUP BY department; -- 案例17：HAVING过滤分组 SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

关键区别：WHERE过滤行，HAVING过滤分组。

4.2 高级分组技巧

-- 案例18：GROUPING SETS多维度分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY GROUPING SETS ( (department, job_title), (department), (job_title), () ); -- 案例19：ROLLUP层级分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY ROLLUP (department, job_title); -- 案例20：CUBE所有组合分组 SELECT department, job_title, COUNT(*) FROM employees GROUP BY CUBE (department, job_title);

5. 子查询与CTE应用

5.1 各种子查询类型

-- 案例21：WHERE子句中的子查询 SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -- 案例22：FROM子句中的子查询 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; -- 案例23：SELECT子句中的子查询 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 排名函数

-- 案例26：ROW_NUMBER连续排名 SELECT employee_id, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -- 案例27：RANK并列排名（会跳过名次） SELECT employee_id, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -- 案例28：DENSE_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.doe@example.com'); -- 案例33：插入多行数据 INSERT INTO employees (employee_id, first_name, last_name, email) VALUES (1002, 'Jane', 'Smith', 'jane.smith@example.com'), (1003, 'Robert', 'Johnson', 'robert.johnson@example.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'; -- 案例45：LIMIT分页优化 -- 低效写法（偏移量大时） 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陷阱需要特别注意：

1. 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;

2. 日期时间处理：不同数据库的日期函数差异很大，建议使用标准SQL的日期函数，避免数据库特定的函数。

-- 标准SQL写法（大多数数据库支持） SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY; -- 避免使用数据库特定函数如MySQL的DATE_SUB()

3. JOIN性能优化：多表JOIN时，应该从小表驱动大表，并确保JOIN条件上有适当的索引。EXPLAIN命令是分析查询性能的利器。

4. 批量操作：当需要处理大量数据时，批量操作比单条操作效率高得多。例如，批量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');

5. 事务粒度：事务不应该过长，应该尽快提交释放锁资源。但同时也要确保事务包含完整的业务逻辑单元。

最后，SQL技能需要通过大量实践来掌握。建议读者在自己的数据库环境里运行这些案例，尝试修改参数观察结果变化，并针对自己的业务数据设计类似的查询。记住，写出能工作的SQL只是第一步，写出高效、可维护的SQL才是专业水准。