【MySQL】多表查询(四)

举报
观止study 发表于 2023/08/25 10:49:03 2023/08/25
【摘要】 🚗MySQL学习·第四站~❤️文末附全文思维导图,感谢各位点赞收藏支持~之前我们介绍DQL语句,也就是数据查询语句的时候,介绍的查询操作都是单表查询,他的功能当然不仅局限于此,我们还可以一次性对多个表的数据进行查询操作,也就是接下来要介绍的多表查询。 一.多表关系在我们的项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间经常会存在相互关...

🚗MySQL学习·第四站~
❤️文末附全文思维导图,感谢各位点赞收藏支持~

之前我们介绍DQL语句,也就是数据查询语句的时候,介绍的查询操作都是单表查询,他的功能当然不仅局限于此,我们还可以一次性对多个表的数据进行查询操作,也就是接下来要介绍的多表查询。

一.多表关系

在我们的项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间经常会存在相互关联关系,所以由此设计出的各个表结构之间也存在着各种联系,基本上分为三种:

  • 一对多(多对一)
  • 多对多
  • 一对一

(1) 一对多

  • 业务示例: 部门与员工的关系
  • 关系介绍: 一个部门对应多个员工,一个员工对应一个部门
  • 实现方式: 我们通常会在 ‘多’ 的一方建立外键,指向 ‘一’ 的一方的主键

image.png

(2) 多对多

  • 业务示例: 学生与课程的关系
  • 关系介绍: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
  • 实现方式: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

image.png

(3) 一对一

  • 业务示例: 用户与用户详情的关系
  • 关系介绍: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
  • 实现方式: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
    image.png

二.多表查询

(1) 引入

多表查询就是指一次性从多张表中查询数据。

原来我们查询单表数据,执行的SQL形式为:select 字段列表 from 表名;

现在我们想要执行多表查询,就只需要使用逗号分隔多张表即可,如: select 字段列表 from 表名1, 表名2;

但是我们这样使用却发现存在问题:的确同时查到了多张表的数据,但是数据形式和我们想要的并不一样,它排列组合了两张表中的所有数据项!

image.png

例如我们查询员工、部门表,本来我们预期是每个员工对应其所在的部门,但事实确实,每个员工都对应了所有部门。这种现象也称之为 笛卡尔积
image.png

因此,在多表查询中,我们需要根据业务情况进行连接查询,消除无效的笛卡尔积,只保留两张表关联部分的有效数据。

例如在上述示例,我们通过表名.字段名指定员工表的外键等于部门表的主键即可获得预期数据~
image.png

特别说明

  • 我们不光可以为字段起别名,同样可以使用相同的语法给表起别名,table as 别名或table 别名
  • 一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字 段
  • 在获取表字段时,我们可以使用表名.字段名来进行指定。

(2) 内连接

内连接查询的是两张表交集部分的数据(也就是绿色部分的数据)。语法分为两种: 隐式内连接、显式内连接。
image.png

(2.1) 隐式内连接

  • 语法
SELECT 字段列表 FROM1,2 WHERE 限制条件;
  • 示例
# 查询每一个员工的姓名 , 及关联的部门的名称 
select e.name,d.name from emp e,dept d where e.dept_id = d.id;

image.png

(2.2) 显式外连接

  • 语法
SELECT 字段列表 FROM1 [ INNER ] JOIN2 ON 连接条件 ... ;
  • 示例(与上述相同)
# 查询每一个员工的姓名 , 及关联的部门的名称 
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

image.png

(3) 外连接

外连接分为:左外连接 和 右外连接。左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。

image.png

(3.1) 左外连接

  • 语法
SELECT 字段列表 FROM1 LEFT [ OUTER ] JOIN2 ON 条件 ... ;
  • 示例:
# 查询emp表的所有数据, 和对应的部门信息
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

image.png

不仔细看会觉得和内连接没啥区别,但是仔细看会发现我们null数据也能够正常显示出来,而内连接则无法查询到null,这是因为我们外连接会获取到一张表的全部数据,而内连接只获取交集部分数据。

(3.2) 右外连接

  • 语法
SELECT 字段列表 FROM1 RIGHT [ OUTER ] JOIN2 ON 条件 ... ;
  • 示例
# 查询emp表的所有数据, 和对应的部门信息 
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

image.png

这么一看感觉左右外连接查询结果都差不多,确实如此,通常左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。

(4) 自连接

自连接查询,顾名思义,就是自己连接自己,也就是把一张表进行连接查询多次。需要注意的是在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。

  • 自连接查询,可以是内连接查询,也可以是外连接查询,其关键点在于 自己连接自己
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
  • 示例
# 查询员工 及其 所属领导的名字,如果员工没有领导, 也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = 
b.id;

image.png

(5) 联合查询

对于联合查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。

  • 语法说明
SELECT 字段列表 FROM 表A  ...   # 查询结果集1
UNION [ ALL ]
SELECT 字段列表 FROM 表B  ....;  # 查询结果集2
  • 注意事项

    • 对于联合查询的多张表的字段列表必须保持一致字段类型也需要保持一致,如果不一致将会报错。

    • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重

  • 示例代码

select * from emp where salary < 5000
union all
select * from emp where age > 50;

image.png

union all查询出来的结果,仅仅只对数据集进行简单的合并,查询结果中可能会存在重复数据项,使用union即可去除重复数据项。

(6) 子查询

(6.1) 介绍

SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。

# 示例语法
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); 

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。嵌套SELECT语句可以位于SELECT之后FROM之后WHERE之后

  • 子查询根据结果不同,分为:
    • 标量子查询(子查询结果为单个值
    • 子查询 (子查询结果为一列)
    • 子查询 (子查询结果为一行)
    • 子查询 (子查询结果为多行多列)

(6.2) 标量子查询

子查询返回的结果是单个值(例如数字、字符串、日期等)。 常用的操作符有:= <> > >= < <=

  • 使用示例:查询 “销售部” 的所有员工信息
# 拆分1: 查询 "销售部" 部门ID,返回单个id值
select id from dept where name = '销售部';

# 拆分2:根据部门ID, 查询员工信息
select id from dept where name = xxx;

# 完整版
 select * from emp where dept_id = (select id from dept where name = '销售部');

image.png

(6.3) 列子查询

子查询返回的结果是一列(可以是多行)。常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL

  • 使用示例:查询比 财务部 所有人工资都高的员工信息
# 拆分1: 查询财务部id
select id from dept where name = '财务部';

# 拆分2: 查询财务部员工工资
select salary from emp where dept_id = 拆分1;
select salary from emp where dept_id = (select id from dept where name = '财务部');

#  拆分3 : 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all (拆分2);

# 合并
select * from emp where salary > all ( select salary from emp where dept_id = 
(select id from dept where name = '财务部') );

image.png

(6.4) 行子查询

子查询返回的结果是一行(可以是多列)。常用的操作符:= 、<> 、IN 、NOT IN

  • 使用示例:查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
# 拆分1:查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

# 拆分2:查询与 "张无忌" 的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (xx,xx);

# 合并
select * from emp where (salary,managerid) = (select salary, managerid from emp 
where name = '张无忌');

image.png

(6.5) 表子查询

子查询返回的结果是多行多列。常用的操作符:IN

  • 使用示例:查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
# 拆分1:查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

# 拆分2:查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( xxx );

 # 合并
 select * from emp where (job,salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');

image.png

最后值得一提是:多表查询业务能实现相关需求的SQL往往会很多, 写法也多种多样,总之,能满足我们的需求,查询出符合条件的记录即可~

三.全文概览

image.png

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。