目录
什么是联合查询：
数据库设计时通常遵循规范化原则，数据会分散到多个表中（例如学生表、课程表、成绩表），需要通过联合查询关联这些表。单一表可能无法提供完整信息，联合查询可以合并多个维度的数据。
比如我需要得到课程为数学科目低于60分的学生；由于数据都分散在不同的表中，所以需要联合查询得到完整的数据。
一些联合查询的方法大致通用的思路：
1. 确定哪几张表参与查询。（确定 join ... on ...）
2. 根据表与表之间的关系，过滤掉无效的数据。（ join ... on 表1.关联字段 = 表2.关联字段）
3. 通过 where 条件进一步过滤结果。
4.精减查询列表，得到想要的结果。（给列起别名）
一、内连接：
基本语法：
select 列名1, 列名2, ...
from 表1 (表1别名)
join 表2 (表2别名)
on 表1.关联字段 = 表2.关联字段
join 表3 (表3别名)
on 表2或表1.关联字段 = 表3.关联字段
-- 可继续添加更多 join...
where 其他条件逐层关联：通过多次使用
join，逐步将新表与已连接的结果关联。明确关联条件：每个
join必须通过on指定匹配条件。ON：指定两个表的关联条件（通常是外键关联）。表1和表2：可以是同一个表（自连接），也可以是不同的表。匹配原则：仅返回两个表中关联字段值相等的行。
过滤机制：不满足条件的行会被完全排除。
使用别名会使得代码更简洁。
示例：
查询目标1：
查询学生姓名、课程名称及对应的授课教师。
select s.name as 学生姓名,
c.course_name as 课程名称,
t.teacher_name as 老师姓名
from students s
join courses c
on s.student_id = c.student_id -- 先关联学生和选课表
join teachers t
on c.course_id = t.course_id; -- 再关联选课表和教师表查询结果：
查询目标2：
查询张三的课程名称及对应的授课教师。
select s.name as 学生姓名,
c.course_name as 课程名称,
t.teacher_name as 老师姓名
from students s
join courses c
on s.student_id = c.student_id -- 先关联学生和选课表
join teachers t
on c.course_id = t.course_id -- 再关联选课表和教师表
where s.name = '张三'; -- 筛选条件：学生姓名为张三查询结果：
值得注意的是：
尽量少对大表进行关联查询，且一般表关联的个数不能超过三个。
二、外连接：
外连接用于返回至少一个表的所有行，并根据关联条件匹配另一个表的行。若某行在另一表中无匹配，则对应字段用 null 填充。外连接分为三种类型：
左外连接（left join）：保留左表所有行。
右外连接（right join）：保留右表所有行。
全外连接：保留左右表所有行（MySQL 不支持）。
基本语法：
左外连接：
select 列名
from 左表
left join 右表
on 左表.关联字段 = 右表.关联字段;保留左表所有行，右表无匹配时填充 null 。
右外连接：
select 列名
from 左表
right join 右表
on 左表.关联字段 = 右表.关联字段;保留右表所有行，左表无匹配时填充
null。
示例：
查询目标1：
使用左外连接查询未选课学生：
首先，上面的两个表可以看到，选课表的 student_id 没有 等于 3 的 数据，所以选课表是缺失的，那么，就可以确定 学生表就是基准表了。
select s.student_id, s.name
from student s
left join courses c
on s.student_id = c.student_id -- 左外连接学生表和选课表
where c.student_id is null -- 筛选右表未匹配的行查询结果：
关键逻辑解析
左外连接：保留
students表的所有行。已选课的学生（张三、李四）会匹配到
courses表的记录。未选课的学生（王五）在
courses表中无匹配，courses表的字段会填充为NULL。
过滤条件：
where c.student_id is null筛选出
courses表中无匹配的行（即未选课的学生）。
查询目标2：
使用右外连接查询未下单的客户：
select c.name as 未下单的客户
from orders o
right join customers c
on o.customer_id = c.customer_id
where o.customer_id is null;查询结果：
注意：
值得注意的是：
在mysql执行的过程中，右外连接会被转换成左外连接。并且，我们一般也习惯使用左外连接。
三、自连接：
自连接是自己与自己取笛卡尔积（相当于连接的两张表是同一张表），可以把行转换成列，在查询的时候可以使用 where 条件对结果进行过滤，或者说实现行与行之间的比较。
基本语法：
select 列名1, 列名2, ...
from 表1 (表1别名)
join 表2 (表2别名)
on 表1.关联字段 = 表2.关联字段
where 其他条件这里的表1和表2就是同一张表。
from 表1 别名1：为表指定第一个别名，用于代表表的一种角色。join 表2 别名2：再次引用同一个表，并为其指定第二个别名，代表表的另一种角色。on 别名1.列名 = 别名2.列名：指定连接条件，通常是两个别名表中具有关联关系的列。
示例：
查询目标1：
查询每个员工及其直接上级的姓名：
select e1.name as 员工姓名 , e2.name as 上级姓名
from employees e1
left join employees e2 -- 自连接，用 left join 包含无上级的员工
on e1.manager_id = e2.emp_id;left join：使用左外连接，确保即使某个类别没有父类别（在这个例子中可以理解为该员工没有上级，manager_id为 null），该类别的信息也会出现在结果中。
别名为 e1 的表可以作为员工表(基准表)，别名为 e2 的表作为 上级表 ,他们两个本质就是同一张表。，所以 ，on 连接条件 则是 e1.manager_id = e2.emp_id ，要避免 e2.manager_id = e1.emp_id ，因为员工的 manager_id 对应上级的 emp_id。
查询结果：
查询目标2：
找到同一客户购买的不同商品组合：
select o1.customer as 客户id,o1.product as 商品1 ,o2.product as 商品2
from orders o1
join orders o2
on o1.customer_id = o2.customer_id -- 同一客户
where o1.order_id < o2.order_id -- 避免重复组合（如 (手机,耳机) 和 (耳机,手机）查询结果：
注意：
以下是 自连接查询中 ON 连接条件 的核心注意事项：
1. 必须使用表别名
原因：自连接需将同一张表视为两个逻辑表，必须用别名区分。
2. 明确条件的逻辑方向
核心原则：on 后的条件顺序决定数据的关联方向。
示例（比如查询目标1）的场景（员工与上级）：
正确条件：
e1.manager_id = e2.emp_id
（员工的manager_id关联上级的emp_id）错误条件：e2
.manager_id = e1.emp_id
（变成“上级的上级是当前员工”，逻辑完全相反）
3. 选择合适的连接类型
- join：仅返回匹配的行（如父子分类必须成对存在）。
left join：保留主表所有行，即使无匹配（比如查询目标1的无上级的员工）。
5. 处理 NULL 值
场景：根节点（如顶级分类、无上级的员工）的关联字段可能为
null。建议：
使用 left join保留无匹配的行。
在应用层（比如java方面）处理
null（如显示“无上级”）。(推荐方法)
四、子查询：
子查询是把⼀个 select 语句的结果当做别⼀个 select 语句的条件，也叫嵌套查询。
1.单行子查询：
单行子查询返回单行单列的结果，常用于 where 子句中与主查询进行比较。
基本语法：
select * from table1 where col_name1 {= | IN} (
select col_name1 from table2 where col_name2 {= | IN} [(
select ...)
] ...
)
下面查询语句得到的 col_name1 的查询结果， 会给到 上面的 col_name1 作为 上面查询语句的 where条件。
并且子查询可以无限嵌套，但是要注意嵌套的深度。
例子：
查询工资高于公司平均工资的员工：
select name,salary
from employees -- 表
where salary > (
select avg(salary) -- 子查询返回单个值（平均工资）
from employees
);
2.多行子查询：
多行子查询返回多行单列的结果，通常需要使用 in 操作符与主查询进行比较。
基本语法：
select 列名
from 表名
where 列名 in (select 列名 from 表名 where 条件);例子：
查询选修了课程编号为 101 或 102 的学生:
select name
from students -- 表
where students_id in (
select stuidents_id -- 学生的id编号
from courses
where courese_id in (101,102) -- 子查询返回多个 student_id
);
这里的 courese_id 是课程的编号，里面的查询得到选了课程编号为101或102的 学生id；外面的查询通过得到的学生的id得到学生名字。
3.多列子查询：
单行子查询和多行子查询都只返回⼀列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配。
基本语法：
select 列名
from 表名
where(列1, 列2) in (select 列1, 列2 from 表名 where 条件);例子：
查找与张三所在部门和工资相同的员工:
select name,dept,salary
from employees
where (dept,salary) in (
select dept,salary
from employees
where name = '张三' -- 子查询返回两列：部门、工资
);
4.在 from 子句中使用子查询：
当⼀个查询产生结果时，MySQL自动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回 给用户，在 from 子句中也可以使用临时表进行子查询或表连接操作。
基本语法：
select 列名
from (select 列名 from 表名 where 条件) as 别名
where 条件;from 子句中的子查询：将一个完整的
SELECT语句放在FROM子句中，其查询结果会被视为一张临时表，供外层查询操作。别名要求：子查询必须有一个别名，以便外层查询引用它。
外层查询只能引用子查询结果中的列,下面是个示范：
select dept_id, avg_salary from ( select dept_id, avg(salary) as avg_salary from employees group by dept_id ) as dept_avg -- 临时表名为 dept_avg where avg_salary > 5000; -- 正确：引用了子查询的列外层查询只能使用
dept_avg临时表中的dept_id和avg_salary列。
select dept_id, name -- 错误：name 不在子查询的结果中！
from (
select dept_id, avg(salary) as avg_salary
from employees
group by dept_id
) as dept_avg;此时会报错，因为子查询的临时表中没有 name 列。
例子：
假设需要找到平均工资高于公司平均工资的部门：
select dept_id, avg_salary
from (
select dept_id, AVG(salary) AS avg_salary
from employees
group by dept_id
) as dept_avg
where avg_salary > (select avg(salary) from employees);代码执行逻辑：
1. 执行 from 子句中的子查询（派生表 dept_avg）
-- 子查询逻辑
select dept_id, avg(salary) as avg_salary
from employees
group by dept_id;从
employees表读取数据。按
dept_id分组，计算每个部门的平均工资（avg(salary)）。生成临时表
dept_avg，包含两列：dept_id和avg_salary。
2. 执行 where 子句中的子查询（标量子查询）
-- 子查询逻辑
select avg(salary) from employees;从
employees表读取所有记录的salary。计算全局平均工资（不分组）。
返回一个标量值（单值），例如
7000。
3. 将 where 子查询结果与 dept_avg 临时表关联
主查询逻辑:
select dept_id, avg_salary
from dept_avg -- 已生成的临时表
where avg_salary > 7000; -- 假设子查询结果为7000从临时表
dept_avg中筛选出avg_salary > 7000的部门。返回最终结果。
五、合并查询：
1、
union操作符
union 操作符用于合并两个或多个 select 语句的结果集，它会自动去除重复的行。
基本语法：
select column1, column2, ...
from table1
union
select column1, column2, ...
from table2;例子：
假设有两个表 table1 和 table2，结构和数据如下：
-- 创建 table1 表并插入数据
create table table1 (
id int,
name varchar(50)
);
insert into table1 (id, name) values (1, 'Alice');
insert into table1 (id, name) values (2, 'Bob');
-- 创建 table2 表并插入数据
create table table2 (
id int,
name varchar(50)
);
insert into table2 (id, name) values (2, 'Bob');
insert into table2 (id, name) values (3, 'Charlie');
-- 使用 union 合并查询结果
select id, name from table1
union
select id, name from table2;执行结果：
结果将包含 table1 和 table2 中的所有不重复的行。
要注意的是：查询的两个表的列，除了列的数量要对应，对应位置的列的数据类型也需要兼容。
2、
union all操作符
union all 操作符也用于合并两个或多个 select 语句的结果集，但它不会去除重复的行。
基本语法：
select column1, column2, ...
from table1
union all
select column1, column2, ...
from table2;例子：
-- 创建 table1 表并插入数据
create table table1 (
id int,
name varchar(50)
);
insert into table1 (id, name) values (1, 'Alice');
insert into table1 (id, name) values (2, 'Bob');
-- 创建 table2 表并插入数据
create table table2 (
id int,
name varchar(50)
);
insert into table2 (id, name) values (2, 'Bob');
insert into table2 (id, name) values (3, 'Charlie');
-- 使用 union 合并查询结果
select id, name from table1
union all
select id, name from table2;执行结果：
结果包了含 table1 和 table2 中的所有行。
要注意的是：查询的两个表的列，除了列的数量要对应，对应位置的列的数据类型也需要兼容。
六、插入查询结果：
基本语法:
insert into target_table (column1, column2, ...)
select column1, column2, ...
from source_table
where condition;insert into target_table：指定要插入数据的目标表。(column1, column2, ...)：可选参数，指定要插入数据的列名。如果省略，则默认按照目标表的列顺序插入。select column1, column2, ...：从源表中选择要插入的数据。from source_table：指定源表。where condition：可选参数，用于筛选源表中的数据。
例子1（表结构完全一致）：
-- 创建 employees 表
create table employees (
id int,
name varchar(50),
salary decimal(10, 2)
);
-- 插入示例数据
insert into employees (id, name, salary) values
(1, 'Alice', 6000),
(2, 'Bob', 4000),
(3, 'Charlie', 7000);
-- 创建 new_employees 表
create table new_employees (
id int,
name varchar(50),
salary decimal(10, 2)
);
-- 插入查询结果
insert into new_employees (id, name, salary)
select id, name, salary
from employees
where salary > 5000;
-- 查看 new_employees 表中的数据
select * from new_employees;插入数据后，查询的结果
例子2（表结构不完全一致）：
-- 创建源表
create table source_table2 (
id int,
name varchar(50),
age int,
salary decimal(10, 2)
);
-- 插入示例数据
insert into source_table2 (id, name, age, salary) values
(1, 'Alice', 25, 5000.00),
(2, 'Bob', 30, 6000.00);
-- 创建目标表，只包含部分列
create table target_table2 (
id int,
name varchar(50)
);
-- 插入查询结果，明确指定列
insert into target_table2 (id, name)
select id, name from source_table2;
-- 查看目标表数据
select * from target_table2;插入数据后，查询结果：
这里 source_table2 比 target_table2 多了 age 和 salary 两列，但通过明确指定要插入的列 id 和 name，依然可以将源表中对应列的数据插入到目标表中。
查询语句执行顺序总结：
例如这一条查询语句：
select distinct id, name, avg(age) from student join class on student.class_id = class.id where class.id = 1 group by class.id having avg(age) > 10 order by student.id asc limit 100;执行顺序依次为：
先确定数据来源（from），接着进行表连接（join on），筛选符合条件的记录（where），对记录分组（group by），筛选分组结果（having），选取指定列（select），去重（distinct），排序（order by），限制结果数量（limit）。