10 分钟,带你彻底掌握 SQL 多表查询(建议收藏)

AirPython
AirPython
AirPython
2
文章
0
评论
2021-02-2314:10:57 评论 120 3682字
摘要

相比单表查询,多表查询可以覆盖更多业务场景,大大提升我们的工作效率!实际工作当中,可以根据需要选择性的去使用!

1. 前言

多表查询,也称为多表连接查询;作为关系型数据库最主要的查询方式,在日常工作中被广泛使用

常见的多表查询操作包含:子查询、内连接、左外连接、右外连接、完全连接、交叉连接

本篇文章将利用一个实例逐一介绍这些操作

2. 准备

以 Mysql 数据库为例,创建两张数据表:

  • student - 学生表
  • record - 选课记录表

其中,学生表 id 字段对应选课记录表中的 student_id 字段

Sql 如下:

# 学生表:studentcreate table student(    id   int          not null        primary key,    name varchar(255) null,    age  int          null)    comment "学生表";# 选课记录表:recordcreate table record(    id         int          not null        primary key,    name       varchar(255) not null,    student_id int          not null,    time       datetime     null)    comment "选课记录";

然后,向 2 张表中插入一些数据

# 学生表数据1,张三,182,李四,233,王五,304,马六,355,孙七,406,朱八,197,黄九,53# 记录表数据2021001,语文,1,2021-01-18 15:32:472021002,数学,2,2021-01-18 15:33:412021003,英语,3,2021-01-18 15:34:012021004,物理,4,2021-01-18 15:34:332021005,体育,5,2021-01-18 15:34:472021006,化学,8,2021-01-18 15:35:122021007,生物,9,2021-01-18 15:35:392021008,音乐,10,2021-01-18 15:36:00

3.1 子查询

子查询,又称之为内查询,是一种嵌套在其他 Sql 查询的 Where 子句中的查询

一般用于对查询结果的进一步限制,返回所需要的数据;子查询可以用在 SELECT、INSERT、UPDATE DELETE 语句中

这里以 SELECT 语句为例,在两张表中使用子查询,筛选出满足条件的记录

# 子查询select * from student where id in (select student_id from record where student_id<=3)

查询结果如下:

# 子查询的结果1,张三,182,李四,233,王五,30)

需要注意的是,子查询必须包含在圆括号内,并且不能使用 ORDER BY 进行排序

3.2 内连接

内连接是通过关键字 inner join 连接两张表,只返回满足 on 条件的,两张表的交集数据

# 内连接select * from student s inner join record r on s.id=r.student_id;

查询结果如下:

# 内连接查询结果1,张三,18,2021001,语文,1,2021-01-18 15:32:472,李四,23,2021002,数学,2,2021-01-18 15:33:413,王五,30,2021003,英语,3,2021-01-18 15:34:014,马六,35,2021004,物理,4,2021-01-18 15:34:335,孙七,40,2021005,体育,5,2021-01-18 15:34:47

需要注意的是,如果内连接没有通过 on 关键字指定条件的话,查询结果和交叉连接查询结果一样,只是执行效率高于交叉连接

3.3 外连接

外连接包含:

  • 左外连接
  • 右外连接

其中,

左外连接:使用关键字 left join,以左表为准,返回左表的所有数据,右表满足 on 条件的数据会全部显示,否则用 null 值去填充

右外连接:和左外连接相反。使用关键 right join,以右表为准,返回右表的所有数据,左表满足 on 条件的数据会全部显示,否则用 null 值去填充

首先,我们来看左连接的实例

# 左外连接select * from student s left join record r on s.id=r.student_id;

返回结果如下:

# 左外连接结果1,张三,18,2021001,语文,1,2021-01-18 15:32:472,李四,23,2021002,数学,2,2021-01-18 15:33:413,王五,30,2021003,英语,3,2021-01-18 15:34:014,马六,35,2021004,物理,4,2021-01-18 15:34:335,孙七,40,2021005,体育,5,2021-01-18 15:34:476,朱八,19,NULL,NULL,NULL,NULL7,黄九,53,NULL,NULL,NULL,NULL

然后,我们再来看看右连接

# 右外连接select * from student s right join record r on s.id=r.student_id;

返回结果如下:

# 右外连接结果1,张三,18,2021001,语文,1,2021-01-18 15:32:472,李四,23,2021002,数学,2,2021-01-18 15:33:413,王五,30,2021003,英语,3,2021-01-18 15:34:014,马六,35,2021004,物理,4,2021-01-18 15:34:335,孙七,40,2021005,体育,5,2021-01-18 15:34:47NULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39NULL,NULL,NULL,2021008,音乐,10,2021-01-18 15:36:00

3.4 完全连接

完全连接,是通过关键字 full join 连接两张表,返回左表和右表的所有数据,并使用 null 值填充缺失的数据

# 完全连接select * from student s full join record r on s.id = r.student_id;

需要注意的是,Mysql 并不支持完全连接,我们可以使用左连接 + union + 右连接的方式去模拟完全连接

select * from student left join record on student.id = record.student_idunionselect * from student right join record on student.id = record.student_id;

查询结果如下:

# 完全连接结果1,张三,18,2021001,语文,1,2021-01-18 15:32:472,李四,23,2021002,数学,2,2021-01-18 15:33:413,王五,30,2021003,英语,3,2021-01-18 15:34:014,马六,35,2021004,物理,4,2021-01-18 15:34:335,孙七,40,2021005,体育,5,2021-01-18 15:34:476,朱八,19,NULL,NULL,NULL,NULL7,黄九,53,NULL,NULL,NULL,NULLNULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39NULL,NULL,NULL,2021008,音乐,10,2021-01-18 15:36:00

3.5 交叉连接

交叉连接,又称之为笛卡尔积,使用关键字 cross join 连接两张表进行查询

如果不使用 where 加入限制条件,则返回两张表行数的乘积;如果加入限制条件,则返回满足条件表达式的数据并合成一行

以加入限制条件的交叉连接查询为例

# 交叉连接select * from xag.student as s cross join xag.record as r where s.id=r.student_id;

查询结果如下:

# 交叉连接结果1,张三,18,2021001,语文,1,2021-01-18 15:32:472,李四,23,2021002,数学,2,2021-01-18 15:33:413,王五,30,2021003,英语,3,2021-01-18 15:34:014,马六,35,2021004,物理,4,2021-01-18 15:34:335,孙七,40,2021005,体育,5,2021-01-18 15:34:47

需要注意的是,交叉连接查询如果带有限制条件,它会先生成两张表行数成绩生成查询结果集,然后再通过限制条件去过滤;因此,在数据量大的时候,查询速度会很慢

4. 最后

相比单表查询,多表查询可以覆盖更多业务场景,大大提升我们的工作效率!实际工作当中,可以根据需要选择性的去使用!

End.爱数据网专栏作者:星安果作者介绍:分享 Python 爬虫、自动化、Web原创技术干货个人微信公众号:AirPython(ID:AirPython)本文为挖数网专栏作者原创文章,未经允许禁止转载,需要转载请微信联系授权(微信号:lovedata0520)

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: