MYSQL团结查询
目次什么是团结查询：
一些团结查询的方法大致通用的思路：
一、内连接：
根本语法：
示例：
查询目的1：
查询目的2：
二、外连接：
根本语法：
示例：
查询目的1：
查询目的2：
注意：
三、自连接：
根本语法：
示例：
查询目的1：
查询目的2：
注意：
1. 必须利用表别名
2. 明确条件的逻辑方向
3. 选择合适的连接类型
5. 处置惩罚 NULL 值
四、子查询：
1.单行子查询：
根本语法：
例子：
2.多行子查询：
根本语法：
例子：
3.多列子查询：
根本语法：
例子：
4.在 from 子句中利用子查询：
根本语法：
例子：
五、归并查询：
1、union 操作符
根本语法：
例子：
2、union all 操作符
根本语法：
例子：
六、插入查询结果：
根本语法:
例子1（表结构完全一致）：
例子2（表结构不完全一致）：
查询语句执行序次总结：
什么是团结查询：
数据库计划时通常遵循规范化原则，数据会分散到多个表中（例如学生表、课程表、成绩表），必要通过团结查询关联这些表。单一表大概无法提供完备信息，团结查询可以归并多个维度的数据。
比如我必要得到课程为数学科目低于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：可以是同一个表（自连接），也可以是不同的表。
[*] 匹配原则：仅返回两个表中关联字段值相等的行。
[*] 过滤机制：不满足条件的行会被完全排除。
[*] 利用别名会使得代码更简洁。
示例：
https://i-blog.csdnimg.cn/direct/a3b191b20fc24bd4a564b09e333b22f3.png
查询目的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;-- 再关联选课表和教师表 查询结果：
https://i-blog.csdnimg.cn/direct/d380ae72962045509c249a9ea816ddc8.png
查询目的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 = '张三'; -- 筛选条件：学生姓名为张三 查询结果：
https://i-blog.csdnimg.cn/direct/22d5d927a9654dfeb3413bbebaf44c6b.png
值得注意的是：
尽量少对大表进行关联查询，且一般表关联的个数不能凌驾三个。
二、外连接：
外连接用于返回至少一个表的所有行，并根据关联条件匹配另一个表的行。若某行在另一表中无匹配，则对应字段用 null 填充。外连接分为三种类型：
[*] 左外连接（left join）：生存左表所有行。
[*] 右外连接（right join）：生存右表所有行。
[*] 全外连接：生存左右表所有行（MySQL 不支持）。
根本语法：
左外连接：
select 列名
from 左表
left join 右表
on 左表.关联字段 = 右表.关联字段;
[*] 生存左表所有行，右表无匹配时填充 null 。
右外连接：
select 列名
from 左表
right join 右表
on 左表.关联字段 = 右表.关联字段;
[*] 生存右表所有行，左表无匹配时填充 null 。
示例：
查询目的1：
https://i-blog.csdnimg.cn/direct/3d517476a66b4b78b00fb8981008201a.png
利用左外连接查询未选课学生：
首先，上面的两个表可以看到，选课表的 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 -- 筛选右表未匹配的行 查询结果：
https://i-blog.csdnimg.cn/direct/75928dec97e9464d8ff03b5df0ad2a26.png
关键逻辑解析
[*] 左外连接：生存 students 表的所有行。
[*] 已选课的学生（张三、李四）会匹配到 courses 表的记录。
[*] 未选课的学生（王五）在 courses 表中无匹配，courses 表的字段会填充为 NULL。
[*] 过滤条件：where c.student_id is null
[*] 筛选出 courses 表中无匹配的行（即未选课的学生）。
查询目的2：
https://i-blog.csdnimg.cn/direct/85b5b1faafde40f2932ee67d7837390a.png
利用右外连接查询未下单的客户：
select c.name as 未下单的客户
from orders o
right join customers c
on o.customer_id = c.customer_id
where o.customer_id is null; 查询结果：
https://i-blog.csdnimg.cn/direct/35142822282248e58a649fe9a121e83a.png
注意：
值得注意的是：
在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：
https://i-blog.csdnimg.cn/direct/e1783e67357449159b23244efb0e49eb.png
查询每个员工及其直接上级的姓名：
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。
查询结果：
https://i-blog.csdnimg.cn/direct/3aba2b8482b346e4ae8d73e0844bacde.png
查询目的2：
https://i-blog.csdnimg.cn/direct/0ded1e0f95974a3ab4894ff937b9fcf3.png
找到同一客户购买的不同商品组合：
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 -- 避免重复组合（如 (手机,耳机) 和 (耳机,手机） 查询结果：
https://i-blog.csdnimg.cn/direct/3540f0bba67441f39c46d1b91d010f80.png
注意：
以下是 自连接查询中 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 ...)
] ...
)
https://i-blog.csdnimg.cn/direct/14fa5d39bd77459eb39d8e41e95fe87c.png
下面查询语句得到的 col_name1 的查询结果， 会给到 上面的 col_name1 作为 上面查询语句的 where条件。
并且子查询可以无限嵌套，但是要注意嵌套的深度。
例子：
查询工资高于公司平均工资的员工：
select name,salary
from employees -- 表
where salary > (
select avg(salary) -- 子查询返回单个值（平均工资）
from employees
); https://i-blog.csdnimg.cn/direct/04b2cd8f65fa4e6893c9bbbece2dbe14.png
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
);
https://i-blog.csdnimg.cn/direct/978a8f8429bd4efaae884d011cc68dc8.png
这里的 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 = '张三'-- 子查询返回两列：部门、工资
); https://i-blog.csdnimg.cn/direct/a2249d185f6c4830a83315a32fde68c9.png
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; 执行结果：
https://i-blog.csdnimg.cn/direct/d16af4ff191842ae99c122183d9eb18e.png
结果将包含 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; 执行结果：
https://i-blog.csdnimg.cn/direct/9deefdadf1694c769bf242f0d44e9f03.png
结果包了含 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; 插入数据后，查询的结果
https://i-blog.csdnimg.cn/direct/f0d562c0a81b43d794fcd1771b6b365a.png
例子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; 插入数据后，查询结果：
https://i-blog.csdnimg.cn/direct/dca6f69c07644ce394dd0c30860b1a5e.png
这里 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）。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]