MySQL基础-多表查询

目录

简单概述

1.多表之间的关系

1.1 一对多/多对一

1.2 多对多

 1.3 一对一

2. 多表查询-内连接

2.1 隐式内连接

2.2 显式内连接

2.3 内连接小结

 3.多表查询-外连接

3.1 左外连接

 3.2 右外连接

 4.多表查询-自连接

4.1 应用

5.多表查询-联合查询

 6.子查询

6.1 标量子查询

6.2 列子查询

 6.3 行子查询

7.小结

---

简单概述

在MySQL中，多表查询是指在一个查询中同时涉及多个表的操作。它可以帮助我们从多个表中检索相关数据，并将它们结合在一起进行分析和展示。

1.多表之间的关系

1.1 一对多/多对一

典型的案例就像我上一篇中所写的：MySQL基础篇-约束-CSDN博客

外键约束的案例

也就是利用外键约束来将表之间建立联系

1.2 多对多

案例：学生与课程之间的关系

关系：一个学生可以选修多门课程，一个课程可以供多名学生选择

建立关系：这时候就要建立一张中间表，中间表中至少包含二个外键，分别关联二张表的主键

 创建对应的表

create table student(
    id int auto_increment primary key ,
    name varchar(10),
    no varchar(10)
) comment '学生表';
 
insert into student values (null,'zs','2001010202'),(null,'ls','2001010202'),(null,'kk','2001010202'),(null,'ww','2001010202');
 
create table course(
    id int auto_increment primary key ,
    name varchar(10)
) comment '课程表';
 
insert into course values (null,'Java'),(null,'PHP'),(null,'C++');

创建关系表

create table student_course(
    id int auto_increment primary key ,
    studentid int not null comment '学生id',
    courseid int not null comment '课程id',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

此时学生表和课程表已经与其中的第三张表建立了联系

 1.3 一对一

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

这是一张用户的详情表

此时我们需要将用户的基本信息和受教育信息拆分出来，就可以使用这种一对一的关系来管理用户数据

 

在其中的一张表中使用约束外键将二者联系起来，这样就可以方便的管理了。

2. 多表查询-内连接

内连接返回的数据

使用内连接查询返回的数据必须要有强制条件那就是建立连接，二张表之间有外键约束建立了连接的数据才会返回。

2.1 隐式内连接

语法结构：

select * from 表名,表名 where 条件;

这里有二张表，他们之间通过 ：dept_id 这个约束外键来建立了联系

现在要查询其中建立了联系且对应的数据

select spm.name,dept.name from spm,dept where spm.dept_id = dept.id;

结果： 这样就可以查找到员工对应的部门了

 扩展：通过上面的sql，可以看到where条件以及前面需要查询的字段，其中都需要涉及到

表名.某个字段，所以可以另起别名来简化

select s.name,d.name from spm s,dept d where s.dept_id = d.id;

这个sql的运行结果跟上面的结果是一样的

2.2 显式内连接

语法：

select * from 表名1 INNER JOIN 表名2 ON 条件;

跟上面一样的需求，求出对应的员工的部门

select e.name,d.name from spm e inner join dept d on e.dept_id = d.id;

2.3 内连接小结

显式内连接和隐式内连接的区别在于二点

一：语法不同，这个显而易见

二：sql代码可读性

总的来说，虽然两种方式都可以执行内连接操作，但显式内连接更为推荐，因为它具有更好的可读性和可维护性，能够清晰地展示查询中的连接关系，特别是在处理复杂查询时。而隐式内连接虽然有效，但容易让查询变得混乱和难以理解。因此，建议优先选择显式内连接的方式来编写SQL查询。

 3.多表查询-外连接

外连接返回的数据

外连接和内连接返回的数据基本差不多，但是外连接返回的数据包括没有匹配上的数据，比如绑定的外键是NULL值，外连接也可以查询的到。

3.1 左外连接

返回的数据：

• 返回左表中的所有数据行，不论是否在右表中有匹配。
• 如果有匹配，将返回右表中匹配的数据行。
• 如果没有匹配，右表的列将包含NULL值。
• 左表中没有匹配的数据行仍然会包含在结果中。

语法： 

select 字段 from  表名1 left join 表名2 on 条件;

例如现在要查询所有的员工信息以及他们的部门信息

select s.*,d.name from spm s left join dept d on d.id = s.dept_id

结果，可以即使是null，依然能够返回

 3.2 右外连接

返回的数据：

• 返回右表中的所有数据行，不论是否在左表中有匹配。
• 如果有匹配，将返回左表中匹配的数据行。
• 如果没有匹配，左表的列将包含NULL值。
• 右表中没有匹配的数据行仍然会包含在结果中。

语法：

select 字段 from  表名1 right join 表名2 on 条件;

 没错，它跟左外连接只相差了一个单词

案例：现在我们要查询部门表的所有信息以及对应的员工信息

select d.*,s.* from spm s right join dept d on d.id = s.dept_id;

结果，没有建立连接的则会返回null

 4.多表查询-自连接

语法：

select 字段 from 表名1 join 表名2 on 条件;

4.1 应用

自连接顾名思义就是自己连接自己，在一张表中通过某个字段来将其中的数据建立联系。

例如这种情况：

通过managerid字段，来查询到对应员工的上级

自连接的常见的应用场景：

1. 组织结构：自连接可用于表示组织结构，例如公司的部门和子部门之间的层次关系。在一个表中存储部门信息，使用自连接可以轻松地查找部门的上级部门或子部门。

2. 朋友关系：在社交网络或朋友关系管理系统中，自连接可用于查找用户之间的朋友关系。通过在同一用户表中存储用户信息，并使用自连接来建立用户之间的联系，可以轻松地查找用户的朋友或关注者。

3. 评论和回复：在博客、论坛或社交媒体应用中，自连接可用于构建评论和回复的层次结构。每个评论可以与其父评论相关联，形成回复链。

4. 员工管理：在员工管理系统中，自连接可用于表示员工与其直接上级的关系。这对于构建组织结构图以及查询员工的管理链非常有用。

5. 产品和分类：自连接可以用于表示产品与其父产品或分类之间的关系。这在创建产品目录或层次结构时非常有用。

6. 文件系统：自连接可以用于表示文件系统中的文件和文件夹之间的关系。每个文件夹可以包含文件和子文件夹，形成层次结构。

7. 图形数据库：自连接是图形数据库中的常见操作，用于表示图形中节点之间的关系，例如社交网络图、地理信息系统等。

自连接在处理具有层次结构或复杂关系的数据时还是挺有作用的。它允许在同一表中查找相关数据，简化了数据检索和操作。在设计数据库模型时，考虑数据的层次结构和关系时，自连接是很有用的。

 案例1：查询员工及其对应上级

select a.name ,b.name 上级 from spm a,spm b where a.managerid = b.id;

结果

案例2：查询员工及其对应上级，没有上级的也要查询出来

这个时候需要查询没有关联的数据，要用到外连接

select a.name ,b.name 上级 from spm a left join spm b on a.managerid = b.id;

结果

5.多表查询-联合查询

它用于合并两个或多个具有相同列结构的查询结果集，生成一个包含所有结果的单个结果集。联合查询通常用于将多个查询结果组合成一个单一的结果集，以便在应用程序中进行更方便的处理和显示。

 案例：查询员工表中age<30 和 salary < 5000 的员工

select * from spm where age < 30
union all
select * from spm where salary < 5000;

结果

可以看到游侠出现了2次，因为并没有做去重处理

select * from spm where age < 30
union 
select * from spm where salary < 5000;

 将 all 去掉就可以达到去重的效果了

 6.子查询

6.1 标量子查询

标量子查询的返回结果只包含单个值（数字，字符串，日期），而不是一组行或多列值。

常见的操作符：=, <>, >, >= , <, <=

案例：查询 “研发部” 的所有员工信息

select * from spm where dept_id = (select id from dept where name = '研发部')

其中括号内的sql返回结果为单一值

6.2 列子查询

列子查询返回的结果是一列（可以是多行）。

常用的操作符：IN （包含）, NOT IN , ANY （任一个）, SOME , ALL（所有的）

案例:查询研发部和财务部的所有员工信息

select * from spm where dept_id in (select id from dept where name = '研发部' or name = '财务部')

 6.3 行子查询

行子查询返回的结果是一行（可以是多列）。

常见操作符：= ，<> , IN , NOT IN

案例：查询与 ‘游侠’ 的薪资和上级相同的员工信息

 

select * from spm where (salary,managerid) = (select salary,managerid from spm where name = '游侠') and name != '游侠';

结果

7.小结

多表关系

• 一对多:在多的一方设置外键,关联一的一方的主键
• 多对多:建立中间表，中间表包含两个外键，关联两张表的主键
• 一对一:用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键

 多表查询

• 内连接

1. 隐式    where
2. 显式    inner join ...  on ...

• 外连接

1. 左外    left join ....   on ...
2. 右外    right join ... on ...

• 自连接
• 子查询