MYSQL联合查询

06-01 1029阅读

什么是联合查询：

一些联合查询的方法大致通用的思路：

一、内连接：

基本语法：

示例：

查询目标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：可以是同一个表（自连接），也可以是不同的表。
匹配原则：仅返回两个表中关联字段值相等的行。
过滤机制：不满足条件的行会被完全排除。
使用别名会使得代码更简洁。

示例：

查询目标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 -- 筛选右表未匹配的行
```
  查询结果：
  
  关键逻辑解析
  1. 左外连接：保留 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  
查询结果： 
 
 
 
注意： 
以下是 自连接查询中 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）。

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。

一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

什么是联合查询：

一些联合查询的方法大致通用的思路：

一、内连接：

基本语法：

示例：

查询目标1：

查询目标2：

二、外连接：

基本语法：

示例：

查询目标1：

查询目标2：

注意：

三、自连接：

基本语法：

示例：

查询目标1：

查询目标2：

注意：

1. 必须使用表别名

2. 明确条件的逻辑方向

3. 选择合适的连接类型

5. 处理 NULL 值

四、子查询：

1.单行子查询：

基本语法：

例子：

2.多行子查询：

基本语法：

例子：

3.多列子查询：

基本语法：

例子：

4.在 from 子句中使用子查询：

基本语法：

例子：

五、合并查询：

1、union 操作符

基本语法：

例子：

2、union all 操作符

基本语法：

例子：

六、插入查询结果：

基本语法:

例子1（表结构完全一致）：

例子2（表结构不完全一致）：

查询语句执行顺序总结：

相关阅读

Linux Shell异常处理，常见问题与解决方案？Shell脚本报错怎么解决？Shell脚本报错如何快速解决？

Linux中高效复制多级目录与文件的技巧与方法？如何快速复制Linux多级目录？Linux多级目录如何快速复制？

IDA Pro监听Linux进程，逆向工程中的动态调试技术？IDA如何动态调试Linux进程？IDA如何逆向调试Linux进程？

Linux下按pip安装与管理Python包指南？Linux如何用pip管理Python包？Linux下pip怎么管理Python包？

目录[+]