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.精减查询列表，得到想要的结果。（给列起别名）

   一、内连接：

     根本语法：

1. select 列名1, 列名2, ...
2. from 表1 (表1别名)
3. join 表2 (表2别名)
4.   on 表1.关联字段 = 表2.关联字段
5. join 表3 (表3别名)
6.   on 表2或表1.关联字段 = 表3.关联字段
7. -- 可继续添加更多  join...
8. where 其他条件

复制代码

• 逐层关联：通过多次利用 join，逐步将新表与已连接的结果关联。
  
• 明确关联条件：每个 join 必须通过 on 指定匹配条件。
  
• ON：指定两个表的关联条件（通常是外键关联）。
  
• 表1 和 表2：可以是同一个表（自连接），也可以是不同的表。
  
• 匹配原则：仅返回两个表中关联字段值相等的行。
  
• 过滤机制：不满足条件的行会被完全排除。
  
• 利用别名会使得代码更简洁。
  

   示例：

  

 查询目的1：

查询学生姓名、课程名称及对应的授课教师。

1. select s.name as 学生姓名,
2.        c.course_name as 课程名称,
3.        t.teacher_name as 老师姓名
4. from students s
5.   join courses c
6.    on s.student_id = c.student_id   -- 先关联学生和选课表
7.     join teachers t
8.      on c.course_id = t.course_id;  -- 再关联选课表和教师表

复制代码

查询结果：

查询目的2：

查询张三的课程名称及对应的授课教师。

1. select s.name as 学生姓名,
2.        c.course_name as 课程名称,
3.        t.teacher_name as 老师姓名
4. from students s
5.   join courses c
6.    on s.student_id = c.student_id   -- 先关联学生和选课表
7.     join teachers t
8.      on c.course_id = t.course_id  -- 再关联选课表和教师表
9.         where s.name = '张三'; -- 筛选条件：学生姓名为张三

复制代码

查询结果：

 值得注意的是：
尽量少对大表进行关联查询，且一般表关联的个数不能凌驾三个。



   二、外连接：

  外连接用于返回至少一个表的所有行，并根据关联条件匹配另一个表的行。若某行在另一表中无匹配，则对应字段用 null 填充。外连接分为三种类型：

• 左外连接（left join）：生存左表所有行。
  
• 右外连接（right join）：生存右表所有行。
  
• 全外连接：生存左右表所有行（MySQL 不支持）。
  

   根本语法：

  左外连接：

1. select 列名
2. from 左表
3. left join 右表
4.   on 左表.关联字段 = 右表.关联字段;

复制代码

• 生存左表所有行，右表无匹配时填充 null 。
  

右外连接：

1. select 列名
2. from 左表
3. right join 右表
4.   on 左表.关联字段 = 右表.关联字段;

复制代码

• 生存右表所有行，左表无匹配时填充 null 。
  

   示例：

   查询目的1：

利用左外连接查询未选课学生：
首先，上面的两个表可以看到，选课表的 student_id 没有 等于 3 的 数据，以是选课表是缺失的，那么，就可以确定 学生表就是基准表了。

1. select s.student_id, s.name
2. from student s
3. left join courses c
4. on s.student_id = c.student_id -- 左外连接学生表和选课表
5. where c.student_id is null -- 筛选右表未匹配的行

复制代码

查询结果：

关键逻辑解析

• 左外连接：生存 students 表的所有行。
  
  
  
  • 已选课的学生（张三、李四）会匹配到 courses 表的记录。
    
  • 未选课的学生（王五）在 courses 表中无匹配，courses 表的字段会填充为 NULL。
    
  
  
• 过滤条件：where c.student_id is null
  
  
  
  • 筛选出 courses 表中无匹配的行（即未选课的学生）。
    
  
  

查询目的2：

利用右外连接查询未下单的客户：

1. select c.name as 未下单的客户
2. from orders o
3. right join customers c
4. on o.customer_id = c.customer_id
5. where o.customer_id is null;

复制代码

查询结果：

   注意：

   值得注意的是：
在mysql执行的过程中，右外连接会被转换成左外连接。并且，我们一般也习惯利用左外连接。



   三、自连接：

          自连接是自己与自己取笛卡尔积（相称于连接的两张表是同一张表），可以把行转换成列，在查询的时间可以利用 where 条件对结果进行过滤，或者说实现行与行之间的比力。 
   根本语法：

1. select 列名1, 列名2, ...
2. from 表1 (表1别名)
3. join 表2 (表2别名)
4.   on 表1.关联字段 = 表2.关联字段
5. where 其他条件

复制代码

这里的表1和表2就是同一张表。

• from 表1 别名1：为表指定第一个别名，用于代表表的一种角色。
  
• join 表2 别名2：再次引用同一个表，并为其指定第二个别名，代表表的另一种角色。
  
• on 别名1.列名 = 别名2.列名：指定连接条件，通常是两个别名表中具有关联关系的列。
  

   示例：

  查询目的1：

 查询每个员工及其直接上级的姓名：

1. select e1.name as 员工姓名 , e2.name as 上级姓名
2. from employees e1
3. left join employees e2 -- 自连接，用 left join 包含无上级的员工
4. 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：

 找到同一客户购买的不同商品组合：

1. select o1.customer as 客户id,o1.product as 商品1 ,o2.product as 商品2
2. from orders o1
3. join orders o2
4. on o1.customer_id = o2.customer_id -- 同一客户
5. 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 子句中与主查询进行比力。
根本语法：

1. select * from table1 where col_name1 {= | IN} (
2. select col_name1 from table2 where col_name2 {= | IN} [(
3. select ...)
4. ] ...
5. )

复制代码

 下面查询语句得到的 col_name1 的查询结果， 会给到 上面的 col_name1 作为 上面查询语句的 where条件。
并且子查询可以无限嵌套，但是要注意嵌套的深度。


例子：

查询工资高于公司平均工资的员工：

1. select name,salary
2. from employees         -- 表
3. where salary > (
4.     select avg(salary) -- 子查询返回单个值（平均工资）
5.     from employees     
6. );

复制代码

   2.多行子查询：

   多行子查询返回多行单列的结果，通常必要利用 in 操作符与主查询进行比力。
根本语法：

1. select 列名
2. from 表名
3. where 列名 in (select 列名 from 表名 where 条件);

复制代码

 例子：

查询选修了课程编号为 101 或 102 的学生:

1. select name
2. from students -- 表
3. where students_id in (
4.     select stuidents_id -- 学生的id编号
5.     from courses   
6.     where courese_id in (101,102)   -- 子查询返回多个 student_id
7. );

复制代码

这里的 courese_id 是课程的编号，里面的查询得到选了课程编号为101或102的 学生id；表面的查询通过得到的学生的id得到学生名字。





   3.多列子查询：

   单行子查询和多行子查询都只返回⼀列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配。
根本语法：

1. select 列名
2. from 表名
3. where(列1, 列2) in  (select 列1, 列2 from 表名 where 条件);

复制代码

例子：

查找与张三所在部门和工资相同的员工:

1. select name,dept,salary
2. from employees
3. where (dept,salary) in (
4.     select dept,salary
5.     from employees
6.     where name = '张三'  -- 子查询返回两列：部门、工资   
7. );

复制代码

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

   当⼀个查询产生结果时，MySQL主动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回 给用户，在 from 子句中也可以利用临时表进行子查询或表连接操作。
根本语法：

1. select 列名
2. from (select 列名 from 表名 where 条件) as 别名
3. where 条件;

复制代码

• from 子句中的子查询：将一个完备的 SELECT 语句放在 FROM 子句中，其查询结果会被视为一张临时表，供外层查询操作。
  
• 别名要求：子查询必须有一个别名，以便外层查询引用它。
  

• 外层查询只能引用子查询结果中的列,下面是个树模：
  
• 1. select dept_id, avg_salary
  2. from (
  3.     select dept_id, avg(salary) as avg_salary
  4.     from employees
  5.     group by dept_id
  6. ) as dept_avg  -- 临时表名为 dept_avg
  7. where avg_salary > 5000;  -- 正确：引用了子查询的列

  复制代码
  外层查询只能利用 dept_avg 临时表中的 dept_id 和 avg_salary 列。
  

1. select dept_id, name  -- 错误：name 不在子查询的结果中！
2. from (
3.     select dept_id, avg(salary) as avg_salary
4.     from employees
5.     group by dept_id
6. ) as dept_avg;

复制代码

此时会报错，因为子查询的临时表中没有 name 列。


例子：

假设必要找到平均工资高于公司平均工资的部门：

1. select dept_id, avg_salary
2. from (
3.     select dept_id, AVG(salary) AS avg_salary
4.     from employees
5.     group by dept_id
6. ) as dept_avg
7. where avg_salary > (select avg(salary) from employees);

复制代码

代码执行逻辑：
1. 执行 from 子句中的子查询（派生表 dept_avg）

1. -- 子查询逻辑
2. select dept_id, avg(salary) as avg_salary
3. from employees
4. group by dept_id;

复制代码

• 从 employees 表读取数据。
  
• 按 dept_id 分组，计算每个部门的平均工资（avg(salary)）。
  
• 天生临时表 dept_avg，包含两列：dept_id 和 avg_salary。
  

2. 执行  where 子句中的子查询（标量子查询）

1. -- 子查询逻辑
2. select avg(salary) from employees;

复制代码

• 从 employees 表读取所有记录的 salary。
  
• 计算全局平均工资（不分组）。
  
• 返回一个标量值（单值），例如 7000。
  

3. 将  where 子查询结果与 dept_avg 临时表关联
主查询逻辑:

1. select dept_id, avg_salary
2. from dept_avg  -- 已生成的临时表
3. where avg_salary > 7000;  -- 假设子查询结果为7000

复制代码

• 从临时表 dept_avg 中筛选出 avg_salary > 7000 的部门。
  
• 返回最闭幕果。
  

   五、归并查询：

    1、union 操作符

  union 操作符用于归并两个或多个 select 语句的结果集，它会主动去除重复的行。

根本语法：

1. select column1, column2, ...
2. from table1
3. union
4. select column1, column2, ...
5. from table2;

复制代码

例子：

假设有两个表 table1 和 table2，结构和数据如下：

1. -- 创建 table1 表并插入数据
2. create table table1 (
3.     id int,
4.     name varchar(50)
5. );
6. insert into table1 (id, name) values (1, 'Alice');
7. insert into table1 (id, name) values (2, 'Bob');
9. -- 创建 table2 表并插入数据
10. create table table2 (
11.     id int,
12.     name varchar(50)
13. );
14. insert into table2 (id, name) values (2, 'Bob');
15. insert into table2 (id, name) values (3, 'Charlie');
17. -- 使用 union 合并查询结果
18. select id, name from table1
19. union
20. select id, name from table2;

复制代码

执行结果：

 结果将包含 table1 和 table2 中的所有不重复的行。
 要注意的是：查询的两个表的列，除了列的数量要对应，对应位置的列的数据类型也必要兼容。



   2、union all 操作符

  union all 操作符也用于归并两个或多个 select 语句的结果集，但它不会去除重复的行。

根本语法：

1. select column1, column2, ...
2. from table1
3. union all
4. select column1, column2, ...
5. from table2;

复制代码

例子：

1. -- 创建 table1 表并插入数据
2. create table table1 (
3.     id int,
4.     name varchar(50)
5. );
6. insert into table1 (id, name) values (1, 'Alice');
7. insert into table1 (id, name) values (2, 'Bob');
9. -- 创建 table2 表并插入数据
10. create table table2 (
11.     id int,
12.     name varchar(50)
13. );
14. insert into table2 (id, name) values (2, 'Bob');
15. insert into table2 (id, name) values (3, 'Charlie');
17. -- 使用 union 合并查询结果
18. select id, name from table1
19. union all
20. select id, name from table2;

复制代码

执行结果：

 结果包了含 table1 和 table2 中的所有行。
要注意的是：查询的两个表的列，除了列的数量要对应，对应位置的列的数据类型也必要兼容。



   六、插入查询结果：

   根本语法:

1. insert into target_table (column1, column2, ...)
2. select column1, column2, ...
3. from source_table
4. where condition;

复制代码

• insert into target_table：指定要插入数据的目的表。
  
• (column1, column2, ...)：可选参数，指定要插入数据的列名。假如省略，则默认按照目的表的列序次插入。
  
• select column1, column2, ...：从源表中选择要插入的数据。
  
• from source_table：指定源表。
  
• where condition：可选参数，用于筛选源表中的数据。
  

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

1. -- 创建 employees 表
2. create table employees (
3.     id int,
4.     name varchar(50),
5.     salary decimal(10, 2)
6. );
8. -- 插入示例数据
9. insert into employees (id, name, salary) values
10. (1, 'Alice', 6000),
11. (2, 'Bob', 4000),
12. (3, 'Charlie', 7000);
14. -- 创建 new_employees 表
15. create table new_employees (
16.     id int,
17.     name varchar(50),
18.     salary decimal(10, 2)
19. );
21. -- 插入查询结果
22. insert into new_employees (id, name, salary)
23. select id, name, salary
24. from employees
25. where salary > 5000;
27. -- 查看 new_employees 表中的数据
28. select * from new_employees;

复制代码

插入数据后，查询的结果

 


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

1. -- 创建源表
2. create table source_table2 (
3.     id int,
4.     name varchar(50),
5.     age int,
6.     salary decimal(10, 2)
7. );
9. -- 插入示例数据
10. insert into source_table2 (id, name, age, salary) values
11. (1, 'Alice', 25, 5000.00),
12. (2, 'Bob', 30, 6000.00);
14. -- 创建目标表，只包含部分列
15. create table target_table2 (
16.     id int,
17.     name varchar(50)
18. );
20. -- 插入查询结果，明确指定列
21. insert into target_table2 (id, name)
22. select id, name from source_table2;
24. -- 查看目标表数据
25. select * from target_table2;

复制代码

插入数据后，查询结果：

 这里 source_table2 比 target_table2 多了 age 和 salary 两列，但通过明确指定要插入的列 id 和 name，依然可以将源表中对应列的数据插入到目的表中。


   查询语句执行序次总结：

  例如这一条查询语句：

1. 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企服之家，中国第一个企服评测及商务社交产业平台。