在数据库开发中，查询操作占据了日常工作的绝大部分。无论是简单的单表查询，还是复杂的多表关联、子查询，掌握这些技能对于提升数据处理能力和工作效率都至关重要。今天，我们将深入探讨SQL查询的各个方面，从基础的关联查询到高级的子查询应用，帮助大家全面掌握数据库查询的核心技术。
一、关联查询的基础认知
1.1 什么是关联查询？
关联查询，顾名思义，就是同时从两个或更多表中获取数据的查询方式。在实际业务场景中，数据往往分散在不同的表中，比如员工信息存储在员工表，部门信息存储在部门表，要查询员工及其所属部门的完整信息，就必须将这两个表关联起来。
关联查询的核心前提：这些表之间必须存在关联字段。这个关联字段在逻辑上代表相同的业务含义，数据类型必须一致，字段名可以相同也可以不同。例如，员工表中的did字段和部门表中的did字段，就是典型的关联字段。
1.2 关联查询的基本原则
原则一：必须有关联字段
关联字段是连接两个表的桥梁，就像拼图的两块碎片，必须找到匹配的接口才能拼接在一起。在t_employee（员工表）和t_department（部门表）中，did就是这个关键的接口。
原则二：关联条件的数量必须等于表数量减1
当查询n个表时，需要n-1个关联条件。例如：
- 2个表关联：需要1个关联条件
- 3个表关联：需要2个关联条件
- 4个表关联：需要3个关联条件
原则三：关联条件建议写在ON子句中
虽然关联条件可以写在WHERE子句中，但为了提高代码的可读性和维护性，强烈建议将关联条件写在ON子句中。每个JOIN操作都应该有对应的ON条件。
二、SQL JOIN的七种形态
SQL JOIN操作可以产生七种不同的结果集，理解这些形态对于解决实际业务问题至关重要。
2.1 内连接（INNER JOIN）
内连接只返回两个表中关联字段匹配的记录。以员工表和部门表为例：
SELECT ename, t_department.did, dnameFROM t_employee INNER JOIN t_department ON t_employee.did = t_department.did;实际应用场景：
查询所有有部门归属的员工及其部门信息，自动排除没有部门的员工和没有员工的部门。
-- 查询部门编号为1的女员工的所有信息SELECT ename, gender, t_department.did, dname, salaryFROM t_employee INNER JOIN t_department ON t_employee.did = t_department.didWHERE t_department.did = 1 AND gender = '女';-- 三表关联：员工+部门+职位SELECT ename, gender, t_department.did, dname, salary, job_id, jnameFROM t_employeeINNER JOIN t_department ON t_employee.did = t_department.didINNER JOIN t_job ON t_employee.job_id = t_job.jidWHERE t_department.did = 1;2.2 左连接（LEFT JOIN）
左连接返回左表（A表）的所有记录，即使右表（B表）中没有匹配的记录。对于左表中没有匹配的记录，右表部分会显示NULL。
实际应用场景：查询所有员工（包括没有部门的员工）
-- 查询所有员工，包括没有指定部门的员工SELECT ename, salary, t_department.did, dnameFROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did;查询左表独有的记录（A - A∩B）：
-- 查询没有部门的员工信息SELECT ename, salary, t_department.did, dnameFROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.didWHERE t_employee.did IS NULL;这里有个小技巧：WHERE条件中使用子表的关联字段IS NULL来判断，因为父表中的关联字段通常是主键，不会为NULL。
2.3 右连接（RIGHT JOIN）
右连接返回右表（B表）的所有记录，即使左表（A表）中没有匹配的记录。这是左连接的反向操作。
实际应用场景：查询所有部门（包括没有员工的部门）
-- 查询所有部门，包括没有对应员工的部门SELECT ename, salary, t_department.did, dnameFROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did;查询右表独有的记录（B - A∩B）：
-- 查询没有员工的部门信息SELECT ename, salary, t_department.did, dnameFROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.didWHERE t_employee.did IS NULL;有趣的是，我们可以通过调换表的顺序来将右连接转换为左连接：
-- 下面这条SQL实际上等同于上面的左连接查询SELECT ename, salary, t_department.did, dnameFROM t_department RIGHT JOIN t_employee ON t_employee.did = t_department.did;2.4 全外连接（FULL JOIN）与UNION实现
MySQL不直接支持FULL JOIN，但我们可以通过UNION组合左连接和右连接来实现相同效果。
实现A∪B（所有员工和所有部门）：
-- 查询所有员工和所有部门，包括没有部门的员工和没有员工的部门SELECT *FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.didUNIONSELECT *FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did;实现A∪B - A∩B（排除交集部分）：
-- 查询那些没有分配部门的员工和没有员工的部门SELECT *FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.didWHERE t_employee.did IS NULLUNIONSELECT *FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.didWHERE t_employee.did IS NULL;2.5 自连接（SELF JOIN）
自连接是将同一张表视为两张不同的表进行关联查询。这在处理层级结构数据时非常有用。
实际应用场景：查询员工及其领导信息
在t_employee表中，mid字段表示员工的领导编号。通过自连接，我们可以轻松获取每个员工及其领导的信息：
SELECT emp.eid, emp.ename, emp.salary, mgr.eid, mgr.ename, mgr.salaryFROM t_employee AS emp INNER JOIN t_employee AS mgr ON emp.mid = mgr.eid;这里的关键技巧是给同一张表取不同的别名：emp代表员工表，mgr代表领导表。emp.mid = mgr.eid这个关联条件表示员工表中的领导编号等于领导表中的员工编号。
三、SELECT语句的七个子句顺序
理解SELECT语句的执行顺序对于编写正确的SQL至关重要。以下是标准SELECT语句的执行顺序：
- FROM：确定数据来源
- JOIN ON：执行多表关联
- WHERE：筛选原始数据
- GROUP BY：对数据进行分组
- HAVING：对分组结果进行筛选
- ORDER BY：对结果进行排序
- LIMIT：限制返回的记录数
3.1 GROUP BY分组与聚合
分组查询是数据分析中的常用操作，但要注意GROUP BY的使用规则：
-- 查询每个部门的平均薪资SELECT did, ROUND(AVG(salary), 2)FROM t_employeeGROUP BY did;-- 查询每个部门的平均薪资，显示部门编号、名称和平均薪资SELECT t_department.did, dname, ROUND(AVG(salary), 2)FROM t_department LEFT JOIN t_employee ON t_department.did = t_employee.didGROUP BY t_department.did;-- 使用IFNULL处理NULL值SELECT t_department.did, dname, IFNULL(ROUND(AVG(salary), 2), 0)FROM t_department LEFT JOIN t_employee ON t_department.did = t_employee.didGROUP BY t_department.did;重要提醒：在分组统计时，SELECT后面只能写和分组有关的字段或聚合函数，其他无关字段不应该出现，否则会引起歧义。
3.2 WITH ROLLUP合计
WITH ROLLUP可以在分组结果的基础上增加一行合计行：
-- 按部门统计人数，并显示合计SELECT IFNULL(did, '合计') AS "部门编号", COUNT(*) AS "人数"FROM t_employeeGROUP BY did WITH ROLLUP;3.3 HAVING与WHERE的区别
HAVING和WHERE都用于筛选数据，但它们有本质区别：
- WHERE：在分组前对原始数据进行筛选，后面不能使用聚合函数
- HAVING：在分组后对聚合结果进行筛选，后面可以使用聚合函数
-- 查询每个部门女员工的平均薪资，只显示平均薪资高于12000的部门SELECT t_department.did, dname, IFNULL(ROUND(AVG(salary), 2), 0)FROM t_department LEFT JOIN t_employee ON t_department.did = t_employee.didWHERE gender = '女'GROUP BY t_department.didHAVING IFNULL(ROUND(AVG(salary), 2), 0) > 12000;3.4 ORDER BY排序
ORDER BY用于对查询结果进行排序，ASC表示升序（默认），DESC表示降序：
-- 多字段排序：先按薪资升序，再按员工编号降序SELECT *FROM t_employeeORDER BY salary ASC, eid DESC;3.5 LIMIT分页
LIMIT子句用于实现数据分页，格式为：LIMIT m, n
- n：每页显示的行数
- m：起始行索引（从0开始）
- 计算公式：m = (page - 1) * n
-- 每页显示5条记录-- 第1页SELECT * FROM t_employee LIMIT 0, 5;-- 第2页SELECT * FROM t_employee LIMIT 5, 5;-- 第3页SELECT * FROM t_employee LIMIT 10, 5;四、子查询的深度应用
子查询是指嵌套在另一个SQL语句中的查询，可以出现在SELECT、UPDATE、DELETE、INSERT等语句中。
4.1 SELECT中的子查询
在SELECT语句中嵌入子查询，可以实现更复杂的计算：
-- 查询每个人薪资与公司平均薪资的差值SELECT ename AS "姓名", salary AS "薪资", ROUND((SELECT AVG(salary) FROM t_employee), 2) AS "全公司平均薪资", ROUND(salary - (SELECT AVG(salary) FROM t_employee), 2) AS "差值"FROM t_employeeWHERE ABS(ROUND(salary - (SELECT AVG(salary) FROM t_employee), 2)) > 5000;4.2 WHERE/Having中的子查询
当子查询结果作为外层查询的过滤条件时，根据结果集的不同情况，有不同的处理方式：
情况一：子查询返回单值
-- 查询薪资最高的员工SELECT ename, salaryFROM t_employeeWHERE salary = (SELECT MAX(salary) FROM t_employee);-- 查询薪资高于平均薪资的男员工SELECT ename, salaryFROM t_employeeWHERE salary > (SELECT AVG(salary) FROM t_employee) AND gender = '男';情况二：子查询返回单列多值
-- 查询和白露、谢吉娜同一部门的员工SELECT ename, tel, didFROM t_employeeWHERE did IN (SELECT did FROM t_employee WHERE ename IN ('白露', '谢吉娜'));-- 或者使用ANY关键字SELECT ename, tel, didFROM t_employeeWHERE did = ANY (SELECT did FROM t_employee WHERE ename IN ('白露', '谢吉娜'));情况三：使用ALL关键字进行比较
-- 查询薪资比白露、李诗雨、黄冰茹三人薪资都高的员工SELECT ename, salaryFROM t_employeeWHERE salary > ALL (SELECT salary FROM t_employee WHERE ename IN ('白露', '李诗雨', '黄冰茹'));-- 等价于下面的写法SELECT ename, salaryFROM t_employeeWHERE salary > (SELECT MAX(salary) FROM t_employee WHERE ename IN ('白露', '李诗雨', '黄冰茹'));4.3 EXISTS子查询
EXISTS子查询与普通的WHERE子查询不同，它只关心子查询是否返回行，而不关心返回的内容：
-- 查询t_employee表中是否存在部门编号为NULL的员工-- 如果存在，就查询t_department表的所有记录SELECT * FROM t_departmentWHERE EXISTS (SELECT * FROM t_employee WHERE did IS NULL);-- 关联EXISTS：查询t_department表中与t_employee表有相同部门编号的记录SELECT * FROM t_departmentWHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.did = t_department.did);-- 上面的EXISTS查询等价于下面的INNER JOINSELECT DISTINCT t_department.*FROM t_departmentINNER JOIN t_employee ON t_department.did = t_employee.did;4.4 FROM中的子查询（派生表）
当子查询返回多行多列的结果时，可以将其放在FROM后面，作为一张临时表使用：
-- 查询每个部门的平均薪资，然后与部门表关联SELECT t_department.did, dname, pingjunFROM t_departmentLEFT JOIN (SELECT did, AVG(salary) AS pingjun FROM t_employee GROUP BY did) tempON t_department.did = temp.did;-- 使用窗口函数查询每个部门薪资排名前2的员工SELECT *FROM ( SELECT ename, did, salary, DENSE_RANK() OVER (PARTITION BY did ORDER BY salary DESC) AS paiming FROM t_employee) tempWHERE temp.paiming <= 2;4.5 UPDATE中的子查询
子查询也可以用于UPDATE语句中：
-- 修改测试部员工的薪资UPDATE t_employeeSET salary = salary * 1.5WHERE did = (SELECT did FROM t_department WHERE dname = '测试部');-- 将部门编号为NULL的员工分配到测试部UPDATE t_employeeSET did = (SELECT did FROM t_department WHERE dname = '测试部')WHERE did IS NULL;-- 重要：当UPDATE的表和子查询的表是同一张表时，需要将子查询结果用临时表包装UPDATE t_employeeSET salary = (SELECT pingjun FROM (SELECT AVG(salary) pingjun FROM t_employee WHERE did = (SELECT did FROM t_employee WHERE ename = '李冰冰')) temp)WHERE ename = '李冰冰';4.6 DELETE中的子查询
同样，DELETE语句也可以使用子查询：
-- 删除测试部的员工DELETE FROM t_employeeWHERE did = (SELECT did FROM t_department WHERE dname = '测试部');-- 删除和李冰冰同部门的员工（注意处理同一张表的情况）DELETE FROM t_employeeWHERE did = (SELECT did FROM (SELECT did FROM t_employee WHERE ename = '李冰冰') temp);4.7 使用子查询复制表结构和数据
-- 方式一：只复制表结构CREATE TABLE department LIKE t_department;-- 方式二：复制数据（INSERT + 子查询）INSERT INTO department (SELECT * FROM t_department WHERE did <= 3);-- 方式三：同时复制表结构和数据CREATE TABLE d_department AS (SELECT * FROM t_department);-- 如果只想复制部分字段，新表就只有这些字段CREATE TABLE d_department_simple AS (SELECT did, dname FROM t_department);五、通用表达式（CTE）的妙用
通用表达式（CTE）是命名的临时结果集，作用范围是当前语句。相比子查询，CTE具有更好的可读性和复用性，还可以递归引用自己。
5.1 基本CTE使用
-- 查询每个人薪资与公司平均薪资的差值WITH temp AS (SELECT ROUND(AVG(salary), 2) AS pingjun FROM t_employee)SELECT ename AS "员工姓名", salary AS "薪资", pingjun AS "公司平均薪资", ROUND(salary - pingjun, 2) AS "差值"FROM t_employee, tempHAVING ABS(差值) > 5000;5.2 多个CTE联合使用
-- 查询薪资低于9000的员工及其领导信息WITH emp AS (SELECT eid, ename, salary, `mid` FROM t_employee WHERE salary < 9000),mgr(meid, mename, msalary) AS (SELECT eid, ename, salary FROM t_employee)SELECT eid AS "员工编号", ename AS "员工姓名", salary AS "员工薪资", meid AS "领导编号", mename AS "领导姓名", msalary AS "领导薪资"FROM emp JOIN mgr ON emp.mid = mgr.meid;5.3 递归CTE
递归CTE可以处理树形结构数据，比如查询员工的完整领导链：
WITH RECURSIVE cte AS ( -- 初始查询：找到起始员工 SELECT eid, ename, `mid` FROM emp WHERE eid = 21 UNION ALL -- 递归查询：找到上一层的领导 SELECT emp.eid, emp.ename, emp.mid FROM emp JOIN cte ON emp.eid = cte.mid WHERE emp.eid IS NOT NULL)SELECT * FROM cte;六、总结
经过以上的深入探讨，我们对SQL查询有了全面而系统的认识。从基础的关联查询到复杂的子查询应用，每个知识点都是数据库开发中的重要组成部分。
核心要点回顾：
- 关联查询是连接多表数据的桥梁，理解内连接、外连接和自连接的差异，能够帮助我们准确获取所需的数据。在实际开发中，要根据业务需求选择合适的连接类型。
- SELECT语句的执行顺序是编写正确SQL的基础。牢记FROM → JOIN ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT的执行流程，可以有效避免语法错误和逻辑错误。
- GROUP BY分组时，SELECT后面只能出现分组字段和聚合函数，其他字段的出现会导致结果歧义。这是初学者最容易犯的错误之一。
- WHERE和HAVING的分工明确：WHERE负责分组前的数据筛选，HAVING负责分组后的结果筛选。理解这个区别，可以让我们的查询更加高效。
- 子查询是解决复杂查询的利器，但也要注意其性能影响。当子查询结果集很大时，考虑使用JOIN或CTE替代。
- CTE作为子查询的升级版，提供了更好的代码结构和可读性，特别是在处理递归查询时，递归CTE几乎是唯一的选择。
- 性能优化意识：在实际开发中，要注意查询性能。合理使用索引、避免SELECT *、尽量减少子查询嵌套、使用EXPLAIN分析执行计划等都是必要的优化手段。
掌握这些SQL查询技巧，不仅能够提高我们的开发效率，更能让我们在处理复杂业务逻辑时游刃有余。数据库查询看似简单，但其中蕴含着丰富的技术和智慧。希望本文的内容能够帮助大家在数据库开发的道路上更进一步。
最后提醒大家，纸上得来终觉浅，绝知此事要躬行。再多的理论知识，也需要通过实际项目中的反复练习才能内化为自己的技能。让我们一起在实践中不断进步，成为真正的SQL高手！
