【MySQL】多表查询(四)
🚗MySQL学习·第四站~
❤️文末附全文思维导图,感谢各位点赞收藏支持~
之前我们介绍DQL语句,也就是数据查询语句的时候,介绍的查询操作都是单表查询,他的功能当然不仅局限于此,我们还可以一次性对多个表的数据进行查询操作,也就是接下来要介绍的多表查询。
一.多表关系
在我们的项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间经常会存在相互关联关系,所以由此设计出的各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
(1) 一对多
- 业务示例: 部门与员工的关系
- 关系介绍: 一个部门对应多个员工,一个员工对应一个部门
- 实现方式: 我们通常会在 ‘多’ 的一方建立外键,指向 ‘一’ 的一方的主键
(2) 多对多
- 业务示例: 学生与课程的关系
- 关系介绍: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现方式: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
(3) 一对一
- 业务示例: 用户与用户详情的关系
- 关系介绍: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
- 实现方式: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
二.多表查询
(1) 引入
多表查询就是指一次性从多张表中查询数据。
原来我们查询单表数据,执行的SQL形式为:select 字段列表 from 表名;
现在我们想要执行多表查询,就只需要使用逗号分隔多张表即可,如: select 字段列表 from 表名1, 表名2;
但是我们这样使用却发现存在问题:的确同时查到了多张表的数据,但是数据形式和我们想要的并不一样,它排列组合了两张表中的所有数据项!
例如我们查询员工、部门表,本来我们预期是每个员工对应其所在的部门,但事实确实,每个员工都对应了所有部门。这种现象也称之为 笛卡尔积 。
因此,在多表查询中,我们需要根据业务情况进行连接查询,消除无效的笛卡尔积,只保留两张表关联部分的有效数据。
例如在上述示例,我们通过表名.字段名
指定员工表的外键等于部门表的主键即可获得预期数据~
特别说明:
- 我们不光可以为字段起别名,同样可以使用相同的语法给表起别名,
table as 别名或table 别名
- 一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字 段
- 在获取表字段时,我们可以使用
表名.字段名
来进行指定。
(2) 内连接
内连接查询的是两张表交集部分的数据(也就是绿色部分的数据)。语法分为两种: 隐式内连接、显式内连接。
(2.1) 隐式内连接
- 语法
SELECT 字段列表 FROM 表1,表2 WHERE 限制条件;
- 示例
# 查询每一个员工的姓名 , 及关联的部门的名称
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
(2.2) 显式外连接
- 语法
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
- 示例(与上述相同)
# 查询每一个员工的姓名 , 及关联的部门的名称
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
(3) 外连接
外连接分为:左外连接 和 右外连接。左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
(3.1) 左外连接
- 语法
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
- 示例:
# 查询emp表的所有数据, 和对应的部门信息
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
不仔细看会觉得和内连接没啥区别,但是仔细看会发现我们null数据也能够正常显示出来,而内连接则无法查询到null,这是因为我们外连接会获取到一张表的全部数据,而内连接只获取交集部分数据。
(3.2) 右外连接
- 语法
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
- 示例
# 查询emp表的所有数据, 和对应的部门信息
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
这么一看感觉左右外连接查询结果都差不多,确实如此,通常左外连接和右外连接是可以相互替换的,只需要调整在连接查询时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;
(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;
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 = '销售部');
(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 = '财务部') );
(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 = '张无忌');
(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 = '宋远桥');
最后值得一提是:多表查询业务能实现相关需求的SQL往往会很多, 写法也多种多样,总之,能满足我们的需求,查询出符合条件的记录即可~
三.全文概览
- 点赞
- 收藏
- 关注作者
评论(0)