一文掌握MySQL多表查询技巧:告别繁琐操作,轻松搞定数据查询!

YunDuanCode · · 1267 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

在数据库的世界里,我们经常需要处理各种各样的数据。有时候,我们需要从多个表中查询数据,这时候就需要用到MySQL的多表查询了。今天,就让我们一起来了解一下MySQL多表查询的魅力吧! ## 一、表的关系简介 现实生活中,实体与实体之间肯定是有关系的,比如:部门和员工,老师和学生等。在设计表的时候,就应该体现出表与表之间的这种关系。 ### 1.1 一对多 一对多:班级和学生,部门和员工,分类和商品。 ![image.png](https://static.golangjob.cn/231124/7c53608190976c582eff3760c14b9bbc.png) 一对多(1:n)建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键。 ### 1.2 多对多 多对多:老师和学生,学生和课程,用户和角色。 ![image.png](https://static.golangjob.cn/231124/19cfd0cfe4aa0e9ff12114bb0582434f.png) 多对多(m:n)建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。 SQL实现代码: ![image.png](https://static.golangjob.cn/231124/9a4bf68845d2cb596c7dc8e65f8c3577.png) ### 1.3 一对一 在实际的开发中应用不多,因为一对一可以创建成一张表。 **实现方式:** 可以在任意一方添加唯一外键指向另一方的主键(一般情况下,直接合成一张表即可)。 **场景选择:** 多用于单表的拆分,将一张表的基础字段放在一张表中,其他的详情字段放在另一张表中,从而提升效率,比如说:如果叫我们描述一个人,我们能从它的身体的基本信息来描述(身高,体重,姓名,年龄),也可以使用受教育信息(小学,中学,大学)等等。 ![image.png](https://static.golangjob.cn/231124/c4071c10615d31acc0cc3ac71734b50b.png) ### 1.4 表关系小结 ![image.png](https://static.golangjob.cn/231124/82b08b7432380ac498cc27dcf5b027ec.png) ## 二、笛卡尔积 在表连接查询时,由于没有指定表的连接条件,那么数据库默认情况下会将任意表的全部记录都匹配一边任意表。 这样达到的结果总的记录就是所有表的记录数的乘积就是笛卡尔积;所以在表连接时,一定要指定好表的连接条件,一般情况下是从表的外链 = 主表的主键。 ![image.png](https://static.golangjob.cn/231124/7ef04899ee0183713ea722b3196084da.png) ### 2.1 什么是笛卡尔积现象 需求:查询所有的员工和所有的部门。 ``` select * from dept,emp; ``` 结果如下: ![image.png](https://static.golangjob.cn/231124/fe1316d3b55d676c596b62a76e315d86.png) 如果左表是部门表,右表是员工表,左表中每一行记录与右表中的每一行记录全都匹配一次。 **结果的行数=左表中行数x右表中行数** 结果分析: ![image.png](https://static.golangjob.cn/231124/fe1316d3b55d676c596b62a76e315d86.png) ### 2.2 如何清除笛卡尔积 我们发现不是所有的数据组合都市有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的,所以需要通过条件过滤掉没用的数据。 - 设置过滤条件 - 指定过滤条件 主表.主键=从表.外键 ``` select * from dept,emp where dept.id = emp.dept_id; ``` - 可以给表起别名 ``` select * from dept d,emp e where d.id = e.dept_id; ``` ![image.png](https://static.golangjob.cn/231124/1aa1cede2e6bac9370590e5b097a2a94.png) 注:以上过滤以后的结果称为隐式内连接。 >编程学习,从[云端源想](https://ydcode.cn/memberIndex?sourceId=321)开始,课程视频、在线书籍、在线编程、一对一咨询……你想要的全部学习资源这里都有,重点是统统免费![点这里即可查看](https://ydcode.cn/memberIndex?sourceId=321) ## 三、内连接查询 用左边表的记录去匹配右边表的记录,如果符合条件的则显示。 **特点:** 查询出来的记录必须符合表关联的条件。 ### 隐式内连接 - 看不到 join 关键字,条件使用where指定 ``` select 列名 from 左表 [左表别名], 右表 [右表别名] where 主表.主键=从表.外键; select * from dept,emp where dept.id=emp.dept_id; ``` - 相当于查询A,B(员工表和部门表)的交集数据; - 使用where条件消除无效数据; - 字段要由对应的表名或别名调用。 ### 显示内连接 无论是显示内连接还是隐式内连接查询结果是一样的,只是写法不同。 - 使用 inner join … on 语句,可以省略 inner; ``` select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键; ``` - 如果是表关联的条件推荐使用on关键字; - 如果是其他普通的条件使用where。 案例:查询小龙的信息,显示员工id,姓名,地址,年龄和所在的部门名称,我们发现需要联合2张表同时才能查询出需要的数据,使用内连接。 ![image.png](https://static.golangjob.cn/231124/a1b7aa57f5fb8342613c1caeaee2067d.png) **内连接的流程** 1)确定需要查询的表(dept、emp表); 2)确定连接条件(过滤掉不需要的数据,消除笛卡尔积); 3)确定查询条件,我们查询的是小龙的信息,员工表.name=‘小龙’; 4)确定查询的列名(员工id,姓名,地址,年龄,部门名称)。 代码实现如下: ![image.png](https://static.golangjob.cn/231124/aa0c33118401a9e0eafab28a35cb2bcb.png) 结果如下: ![image.png](https://static.golangjob.cn/231124/311fd924ac0f03f93691717bc0944d59.png) ## 四、外连接 特点:需要指定一张查询主表,查询主表的记录必须全部显示,即使不符合表关联的条件 ### 左外连接 左外连接:查询的数据以左表为准,即使在其他表中没有匹配的记录也会显示出来。 - 使用 left outer join on,outer可以省略 - 相当于查询A表所有数据和交集部分数据 - select 列名 from 左表 left join 右表 on 表连接条件 需求:在部门表中增加一个行政部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表。 ``` select * from dept; insert into dept (name) values ('行政部'); -- 使用内连接查询 -- 需要查询所有的部门和员工,无论这个部门下有没有员工 select * from dept inner join emp on dept.id = emp.dept_id; ``` 结果如下: ![image.png](https://static.golangjob.cn/231124/8bbcae7a655653149b6212461dd83472.png) 使用左外连接查询: ``` select * from dept left join emp on dept.id = emp.dept_id; ``` ![image.png](https://static.golangjob.cn/231124/ca6ec1537a72a86ae6291deb53bc509b.png) ### 右外连接 右外连接:查询的数据以右表为准,即使在其他表中没有匹配的记录也会显示出来。 - 使用right outer join … on,outer可以省略 - 相当于查询B表所有数据和交集部分数据 - select 列名 from 左表 left join 右表 on 表连接条件 需求:在员工表中增加一个员工,但该员工还未分配部门 ``` select * from emp; -- 不在任何一个部门 INSERT INTO `emp` VALUES (6, '小赵', '吉安', 26, '男', null); -- 希望员工的信息全部显示出来 -- 使用内连接查询 select * from dept inner join emp on dept.id = emp.dept_id; ``` 结果如下: ![image.png](https://static.golangjob.cn/231124/344affe17e42291679fecb4119e03527.png) 使用右外连接查询: ``` -- 使用右外连接查询 select * from dept right join emp on dept.id = emp.dept_id; ``` ![image.png](https://static.golangjob.cn/231124/d54da0ae108a05e2101a3d9b6f66149b.png) ## 五、全连接 左连接是无论如何左表的数据都能够显示全,右连接是右表的数据无论如何都能够显示全面,那么如果希望左表和右表的数据都能够显示全面呢(在对方表中没有匹配的数据就以null补齐)? 这种连接查询我们称之为全连接(full join),但是很遗憾,MySQL并没有提供全连接,但Oracle支持; 虽然MySQL不支持全连接,但是我们可以利用MySQL提供的其它功能来完成全连接的功能: ``` left join + right join select * from dept d left join emp e on d.id=e.dept_id union select * from dept d right join emp e on d.id=e.dept_id; ``` 全连接查询的结果如下: ![image.png](https://static.golangjob.cn/231124/2f59aaccf3871bdd518cd053a7ec1d0f.png) Tips: - union关键字可以将两个或多个SQL语句的结果集拼接成一个结果集,前提是这些SQL语句的结果集列数必须相同; - union关键字自带去重功能,即去除重复的数据; - 如果需要保留重复的数据则可以使用union all关键字来连接多个SQL语句的结果集。 ## 六、子查询 - 一个查询语句结果做为另一个查询语句的条件; - 查询语句有嵌套,里面的查询称为子查询,外面的查询称为父查询; - 子查询要使用括号括起来。 准备数据: ![image.png](https://static.golangjob.cn/231124/4d0a361b80596ab1e5177a50f68b5f92.png) **查询的结果** 子查询结果是单行单列的:子查询可以作为条件值,使用运算符去判断。 ``` select * from 表名 where 字段名 = (子查询); ``` 需求: 查询年龄最高的员工是谁? ``` -- 1. 查询年龄大的员工的详细信息 select max(age) from emp; -- 2. 根据最高年龄到员工表查询到对应的员工信息 select * from emp where age = 26; -- 使用子查询 select * from emp where age = (select max(age) from emp); ``` ![image.png](https://static.golangjob.cn/231124/ed90c5f4fb6cb7334251ffe4aaf0f8b8.png) 子查询结果是多行单列的:子查询可以作为条件值,使用运算符 in/any/all 去判断: ``` select * from 表名 where 字段名 IN/ANY/ALL(子查询) ``` 下面看两个案例: 需求1:查询年龄大于23的员工的部门信息 ``` -- 关联查询 SELECT d.* FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.age > 23 -- 子查询 -- 1. 首先查询年龄大于23岁的员工所在的部门id select dept_id from emp where age > 23; -- 2. 再查询在这些部门id中部门的名字 -- ERROR 1242 (21000): Subquery returns more than 1 row 子查询返回了多行数据 select * from dept where id = (select dept_id from emp where age > 23); -- 采用in 取结果集中的数据 in (1,2,3) select * from dept where id in (select dept_id from emp where age > 23); ``` ![image.png](https://static.golangjob.cn/231124/ffe3b5d59587619647d971bbb556e5b3.png) 需求2: 查询年龄大于1号部门所有员工的人 ``` -- 1. 查询1号部门所有员工的年龄,得到多行单列 select age from emp where dept_id=1; -- 2. 当结果集返回多行时不能使用比较运算符 -- ERROR 1242 (21000): Subquery returns more than 1 row select * from emp where age > (select age from emp where dept_id=1); select * from emp where age > all (select age from emp where dept_id=1); 等价于: select * from emp e where e.age > (select max(age) from emp e where e.dept_id=1); -- 比1号部门任意一个大就行 select * from emp where age > any (select age from emp where dept_id=1); 等价于: select * from emp e where e.age > (select min(age) from emp e where e.dept_id=1); ``` ![image.png](https://static.golangjob.cn/231124/f44fde46570a3a59507b6167f33a2f48.png) 子查询结果是多行多列的:子查询可以作为一张虚拟表 ``` select 列名 from 表, (子查询的结果) 别名 where 条件 ``` 如果子查询的结果是多行多列,父查询可以将这个查询结果做为一个虚拟表,进行第2次查询。不是放在where后面,而是放在from的后面;子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段。 需求: 查询出年龄大于23岁的员工信息和部门名称 ``` -- 1. 在员工表中查询年龄大于23岁的员工 select * from emp where age > 23; -- 2.查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id select * from dept d, emp e where d.id = e.dept_id; -- 隐式内连接 select e.*,d.name 部门名称 from dept d, (select * from emp where age > 23) e where d.id = e.dept_id; -- 隐式内连接 -- 也可以使用表连接 select e.*,d.name 部门名称 from dept d, emp e where d.id = e.dept_id and e.age > 23; ``` ![image.png](https://static.golangjob.cn/231124/62517fbfea2e47777ade68690a22c3bb.png) ### 子查询小结 - 单行单列:父查询使用比较运算符(>,<,=)。 - 多行单列:父查询使用关键字:in/any/all,只要是单列的情况放在where后面。 - 多行多列:父查询放在from后面做为虚拟表并起别名再次进行查询。 关于多表查询就讲到这里了,文中举了一些例子来说明,大家阅读的时候也可以自己对照例子敲敲代码哦。在实际开发中多表查询是很常用的,通过多表查询,我们可以将多个表中的数据进行关联,从而得到更全面、更精确的数据结果。``

有疑问加站长微信联系(非本文作者))

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

1267 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传