我要评论在 mysql 日常使用中，单表查询仅能满足基础数据需求，而实际开发中，数据往往分散在多张表中，且需要复杂的条件筛选与统计。本文将从单表查询回顾入手，逐步深入多表查询、自连接、子查询等复合场景，结合真实案例拆解用法，帮你掌握企业级查询技巧。
1. 单表查询回顾：夯实基础操作
单表查询是复合查询的基石，核心围绕「筛选条件」「排序规则」「聚合统计」三大维度展开，以下通过经典案例复习关键用法。
1.1 多条件筛选查询
需求：查询工资高于 3000 或 岗位为「analyst」的雇员，且姓名首字母为大写「s」。实现方式有两种：通配符匹配或字符串截取函数，结果一致但适用场景不同。
- 方式 1：使用
like通配符（更简洁，适合模糊匹配场景）
select * from emp where (sal > 3000 or job = 'analyst') and ename like 's%';
- 方式 2：使用
substring函数（更精准，适合固定位置匹配场景）
select * from emp where (sal > 3000 or job = 'analyst') and substring(ename, 1, 1) = 's';
查询结果（示例）：
+--------+-------+---------+------+---------------------+---------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+-------+---------+------+---------------------+---------+------+--------+ | 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | null | 20 | +--------+-------+---------+------+---------------------+---------+------+--------+ 1 row in set (0.00 sec)
1.2 自定义排序查询
排序不仅支持表中原有字段，还能基于「计算字段」排序，比如按「年薪」排序。需注意：奖金comm可能为null，需用ifnull函数处理空值，避免计算结果异常。
- 场景 1：按「月薪 ×12」计算年薪排序
select ename, sal*12 as 年薪 from emp order by 年薪 desc;
- 场景 2：按「月薪 ×12 + 奖金」计算年薪排序（处理空值）
select ename, sal*12 + ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;
1.3 聚合与筛选结合查询
聚合查询（avg/max/count等）需搭配group by分组，若需过滤聚合结果，需用having（区别于where过滤行数据）。
- 场景 1：查询每个部门的平均工资和最高工资
select deptno, avg(sal) as 平均工资, max(sal) as 最高工资 from emp group by deptno;
- 场景 2：查询平均工资低于 2500 的部门（聚合后筛选）
select deptno, avg(sal) as 平均工资 from emp group by deptno having 平均工资 < 2500;
查询结果（示例）：
+--------+-------------+ | deptno | 平均工资 | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 2 rows in set (0.00 sec)
2. 多表查询：关联多张表取数
实际开发中，数据常分散在多张表（如员工表emp、部门表dept、工资等级表salgrade），需通过「关联字段」（如deptno）将表连接，获取完整信息。
2.1 两表关联查询
核心逻辑：找到两张表的共同字段（关联字段），用where指定关联条件，避免笛卡尔积（数据重复）。
- 需求 1：显示雇员名、工资及所在部门名称（关联
emp和dept）
select e.ename, e.sal, d.dname from emp e, dept d -- 给表起别名，简化代码 where e.deptno = d.deptno; -- 关联条件：员工表部门号=部门表部门号
- 需求 2：显示 10 号部门的部门名、员工名和工资（关联 + 筛选）
select d.dname, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and e.deptno = 10; -- 额外筛选10号部门
2.2 三表关联查询
当需要从三张表取数时，需依次指定表间关联条件，确保数据逻辑正确。
- 需求：显示每个员工的姓名、工资、部门名称及工资等级（关联
emp/dept/salgrade）
select e.ename, e.sal, d.dname, s.grade from emp e, dept d, salgrade s where e.deptno = d.deptno -- 关联emp和dept and e.sal between s.losal and s.hisal; -- 关联emp和salgrade（工资在等级范围内）
3. 自连接：同一张表的 “自我关联”
自连接是特殊的多表查询，指同一张表通过别名视为两张表，解决 “表内数据关联” 场景（如查询员工的上级领导）。
- 需求：显示员工「ford」的上级领导编号和姓名（
emp表中mgr字段是领导的empno）
select leader.empno as 领导编号, leader.ename as 领导姓名 from emp emp, emp leader -- 同一张表起两个别名：员工表(emp)、领导表(leader) where emp.ename = 'ford' -- 筛选员工ford and emp.mgr = leader.empno; -- 关联条件：员工的领导编号=领导的员工编号
查询结果（示例）：
+--------+-----------+ | 领导编号 | 领导姓名 | +--------+-----------+ | 007566 | jones | +--------+-----------+ 1 row in set (0.00 sec)
4. 子查询：嵌套查询的灵活应用
子查询（嵌套查询）指将一个select语句嵌入到另一个 sql 语句中，按返回结果行数可分为「单行子查询」「多行子查询」，按位置可嵌入where或from子句。
4.1 单行子查询（返回 1 行结果）
适用于 “基于单个值筛选” 的场景，常用=匹配子查询结果。
- 需求：查询与「smith」同一部门的所有员工（不含 smith）
select * from emp where deptno = (select deptno from emp where ename = 'smith') -- 子查询：获取smith的部门号 and ename != 'smith'; -- 排除smith本人
4.2 多行子查询（返回多行结果）
适用于 “基于多个值筛选” 的场景，需搭配in/all/any等关键字。
| 关键字 | 作用说明 |
|---|---|
in | 匹配子查询结果中的任意一个值 |
all | 匹配所有子查询结果（如> all表示大于所有值） |
any | 匹配任意一个子查询结果（如> any表示大于其中一个值） |
- 场景 1：用
in查询与 10 号部门岗位相同的员工（不含 10 号部门）
select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) -- 子查询：10号部门的所有岗位 and deptno != 10; -- 排除10号部门
- 场景 2：用
all查询工资高于 30 号部门所有员工的员工
select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30); -- 大于30号部门所有工资
4.3 子查询嵌入 from 子句
将子查询结果视为「临时表」，用于复杂统计（如查询 “高于部门平均工资的员工”）。
- 需求：显示每个高于自己部门平均工资的员工姓名、部门、工资及部门平均工资
select e.ename, e.deptno, e.sal, format(tmp.部门平均工资, 2) -- format格式化小数
from emp e,
(select deptno, avg(sal) as 部门平均工资 from emp group by deptno) tmp -- 子查询作为临时表tmp
where e.deptno = tmp.deptno -- 关联员工表和临时表
and e.sal > tmp.部门平均工资; -- 筛选高于平均工资的员工
5. 合并查询：union 与 union all
当需要合并多个select的结果集时，可使用union或union all，两者核心区别是是否去重。
| 操作符 | 去重情况 | 性能 | 适用场景 |
|---|---|---|---|
union | 自动去重 | 较低（需比对去重） | 需避免结果重复 |
union all | 不去重 | 较高（直接合并） | 结果无重复或允许重复 |
- 需求：查询工资高于 4000 或 岗位为「president」的员工
-- union去重（若有重复数据会自动剔除） select * from emp where sal > 4000 union select * from emp where job = 'president'; -- union all不去重（性能更优，适合确认无重复的场景） select * from emp where sal > 4000 union all select * from emp where job = 'president';
5. 表的连接：内连接与外连接详解
在多表查询中，表的连接方式直接决定了数据的查询范围和结果形态。常用的连接方式分为内连接和外连接，外连接又可细分为左外连接与右外连接。本节将结合实例，拆解不同连接方式的语法、逻辑及适用场景。
6.1 内连接：只保留匹配的记录
内连接是最常用的连接方式，核心逻辑是只保留两张表中 “关联条件匹配” 的记录，不匹配的记录会被过滤掉。本质上，它等同于用where子句筛选两张表的笛卡尔积，我们之前学习的多表查询都属于内连接。
6.1.1 内连接语法
内连接支持两种语法格式，核心都是通过on指定关联条件（推荐用on，逻辑更清晰）：
-- 格式1：显式内连接（推荐，明确标注 inner join） select 字段名 from 表1 inner join 表2 on 表1.关联字段 = 表2.关联字段 -- 核心：表间关联条件 and 其他筛选条件; -- 可选：对结果进一步筛选 -- 格式2：隐式内连接（即之前的多表查询写法） select 字段名 from 表1, 表2 where 表1.关联字段 = 表2.关联字段 -- 用where代替on指定关联条件 and 其他筛选条件;
6.1.2 内连接案例
需求：显示员工「smith」的姓名和所在部门名称（关联emp和dept表）。
- 方式 1：隐式内连接（笛卡尔积 + where 筛选）
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno -- 关联条件：员工部门号=部门表部门号 and e.ename = 'smith'; -- 筛选条件：员工姓名为smith
- 方式 2：显式内连接（
inner join + on）
-- 写法1：筛选条件放在on后 select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno -- 关联条件 and e.ename = 'smith'; -- 筛选条件 -- 写法2：筛选条件放在where后（更易理解，先关联表再筛选） select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno -- 先通过on完成表关联 where e.ename = 'smith'; -- 再通过where筛选目标员工
三种写法的查询结果一致：
+-------+----------+ | ename | dname | +-------+----------+ | smith | research | +-------+----------+ 1 row in set (0.00 sec)
6.2 外连接：保留某一张表的全部记录
外连接与内连接的核心区别是：会保留其中一张表的 “全部记录”，即使这些记录在另一张表中没有匹配项（无匹配的字段会显示null）。根据 “保留哪张表”，外连接分为左外连接和右外连接。
6.2.1 左外连接：保留左表全部记录
左外连接的逻辑是：以 “左表” 为基准，保留左表的所有记录，右表只保留与左表匹配的记录；若右表无匹配项，对应字段显示null。
select 字段名 from 左表 left join 右表 on 左表.关联字段 = 右表.关联字段; -- 关联条件（与内连接一致）
注：
left join可省略outer（即left outer join），效果相同。
左外连接案例
为了更直观展示 “保留左表全部记录”，先创建两张测试表：stu（学生表）和exam（成绩表），其中部分学生无成绩，部分成绩无对应学生。
- 准备测试数据
-- 1. 创建并插入学生表数据（左表，需保留全部学生） create table stu (id int, name varchar(30)); insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); -- 2. 创建并插入成绩表数据（右表，部分成绩无对应学生） create table exam (id int, grade int); insert into exam values(1,56),(2,76),(11,8); -- id=11的成绩无对应学生
- 需求：查询所有学生的成绩，即使学生没有成绩也要显示其个人信息
select s.id as 学生id, s.name as 学生姓名, e.grade as 成绩 from stu s left join exam e on s.id = e.id; -- 关联条件：学生id=成绩表id
查询结果（关键：学生 kity、nono 无成绩，成绩字段显示 null，但仍保留记录）：
+--------+----------+--------+ | 学生id | 学生姓名 | 成绩 | +--------+----------+--------+ | 1 | jack | 56 | | 2 | tom | 76 | | 3 | kity | null | -- 无成绩，显示null | 4 | nono | null | -- 无成绩，显示null +--------+----------+--------+ 4 rows in set (0.00 sec)
6.2.2 右外连接：保留右表全部记录
右外连接的逻辑与左外连接相反：以 “右表” 为基准，保留右表的所有记录，左表只保留与右表匹配的记录；若左表无匹配项，对应字段显示null。
select 字段名 from 左表 right join 右表 on 左表.关联字段 = 右表.关联字段; -- 关联条件
注：
right join可省略outer（即right outer join），效果相同。
右外连接案例
需求：查询所有成绩记录，即使成绩没有对应学生也要显示成绩信息（以exam表为右表，保留全部成绩）。
- 方式 1：直接使用右外连接
select s.id as 学生id, s.name as 学生姓名, e.grade as 成绩 from stu s right join exam e on s.id = e.id; -- 关联条件：学生id=成绩表id
- 方式 2：等价于 “左表与右表互换的左外连接”右外连接可通过调换表的顺序，用左外连接实现（更符合直觉，推荐）：
select s.id as 学生id, s.name as 学生姓名, e.grade as 成绩 from exam e left join stu s -- 成绩表作为左表，保留全部成绩 on e.id = s.id; -- 关联条件不变
两种写法的查询结果一致（关键：id=11 的成绩无对应学生，学生信息显示 null，但成绩记录保留）：
+--------+----------+--------+ | 学生id | 学生姓名 | 成绩 | +--------+----------+--------+ | 1 | jack | 56 | | 2 | tom | 76 | | null | null | 8 | -- 无对应学生，显示null +--------+----------+--------+ 3 rows in set (0.00 sec)
6.2.3 内连接与外连接的核心区别
为了更清晰区分，用表格对比三种连接方式的逻辑差异（以stu左表、exam右表为例）：
| 连接方式 | 保留的记录范围 | 无匹配项的处理 | 适用场景 |
|---|---|---|---|
| 内连接 | 只保留两表匹配的记录 | 不保留无匹配的记录 | 需获取 “双方都有数据” 的结果（如：有成绩的学生） |
| 左外连接 | 保留左表全部记录，右表匹配记录 | 右表无匹配项显示 null | 需 “以左表为基准”（如：所有学生的成绩，含无成绩的） |
| 右外连接 | 保留右表全部记录，左表匹配记录 | 左表无匹配项显示 null | 需 “以右表为基准”（如：所有成绩 |
要不要我帮你补充一份 mysql 复合查询核心语法对照表？包含本文所有场景的语法模板、关键字说明和注意事项，方便你日常开发时直接查阅。
到此这篇关于mysql 复合查询从单表到多表的实战攻略的文章就介绍到这了,更多相关mysql复合查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论