1. 前言
表查询,也称为多表连接查询;作为关系型数据库最主要的查询方式,在日常工作中被广泛使用
常见的多表查询操作包含:子查询、内连接、左外连接、右外连接、完全连接、交叉连接
本篇文章将利用一个实例逐一介绍这些操作
2. 准备
以 Mysql 数据库为例,创建两张数据表:
其中,学生表 id 字段对应选课记录表中的 student_id 字段
Sql 如下:
- # 学生表:student
- create table student
- (
- id int not null
- primary key,
- name varchar(255) null,
- age int null
- )
- comment '学生表';
- # 选课记录表:record
- create table record
- (
- id int not null
- primary key,
- name varchar(255) not null,
- student_id int not null,
- time datetime null
- )
- comment '选课记录';
然后,向 2 张表中插入一些数据
- # 学生表数据
- 1,张三,18
- 2,李四,23
- 3,王五,30
- 4,马六,35
- 5,孙七,40
- 6,朱八,19
- 7,黄九,53
- # 记录表数据
- 2021001,语文,1,2021-01-18 15:32:47
- 2021002,数学,2,2021-01-18 15:33:41
- 2021003,英语,3,2021-01-18 15:34:01
- 2021004,物理,4,2021-01-18 15:34:33
- 2021005,体育,5,2021-01-18 15:34:47
- 2021006,化学,8,2021-01-18 15:35:12
- 2021007,生物,9,2021-01-18 15:35:39
- 2021008,音乐,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,张三,18
- 2,李四,23
- 3,王五,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:47
- 2,李四,23,2021002,数学,2,2021-01-18 15:33:41
- 3,王五,30,2021003,英语,3,2021-01-18 15:34:01
- 4,马六,35,2021004,物理,4,2021-01-18 15:34:33
- 5,孙七,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:47
- 2,李四,23,2021002,数学,2,2021-01-18 15:33:41
- 3,王五,30,2021003,英语,3,2021-01-18 15:34:01
- 4,马六,35,2021004,物理,4,2021-01-18 15:34:33
- 5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
- 6,朱八,19,NULL,NULL,NULL,NULL
- 7,黄九,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:47
- 2,李四,23,2021002,数学,2,2021-01-18 15:33:41
- 3,王五,30,2021003,英语,3,2021-01-18 15:34:01
- 4,马六,35,2021004,物理,4,2021-01-18 15:34:33
- 5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
- NULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12
- NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39
- NULL,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_id
- union
- select * from student right join record on student.id = record.student_id;
查询结果如下:
- # 完全连接结果
- 1,张三,18,2021001,语文,1,2021-01-18 15:32:47
- 2,李四,23,2021002,数学,2,2021-01-18 15:33:41
- 3,王五,30,2021003,英语,3,2021-01-18 15:34:01
- 4,马六,35,2021004,物理,4,2021-01-18 15:34:33
- 5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
- 6,朱八,19,NULL,NULL,NULL,NULL
- 7,黄九,53,NULL,NULL,NULL,NULL
- NULL,NULL,NULL,2021006,化学,8,2021-01-18 15:35:12
- NULL,NULL,NULL,2021007,生物,9,2021-01-18 15:35:39
- NULL,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:47
- 2,李四,23,2021002,数学,2,2021-01-18 15:33:41
- 3,王五,30,2021003,英语,3,2021-01-18 15:34:01
- 4,马六,35,2021004,物理,4,2021-01-18 15:34:33
- 5,孙七,40,2021005,体育,5,2021-01-18 15:34:47
需要注意的是,交叉连接查询如果带有限制条件,它会先生成两张表行数成绩生成查询结果集,然后再通过限制条件去过滤;因此,在数据量大的时候,查询速度会很慢
4. 最后
相比单表查询,多表查询可以覆盖更多业务场景,大大提升我们的工作效率!实际工作当中,可以根据需要选择性的去使用!
问题:我们在做flex的开发中,如果用到别人搭建好的框架,而别人的server名称往...
本文转载自微信公众号「SH的全栈笔记」,作者SH。转载本文请联系SH的全栈笔记公...
CKeditor,以前叫FCKeditor,已经使用过好多年了,功能自然没的说。最近升级到3....
本文实例讲述了AJAX+Servlet实现的数据处理显示功能。分享给大家供大家参考,具...
本文转载自微信公众号「SQL数据库」,作者丶平凡世界 。转载本文请联系开发公众...
大家好,我是狂聊君。 今天来聊一聊 Mysql 缓存池原理。 提纲附上,话不多说,直...
来源:DeepenStudy 漏洞文件:js.asp % Dimoblog setoblog=newclass_sys oblog.a...
前言 项目开发中不管是前台还是后台都会遇到烦人的null,数据库表中字段允许空值...
idea官方推送了2020.2.4版本的更新,那么大家最关心的问题来了,之前激活idea202...
在Flash Player 10.1及以上版本中,adobe新增了全局错误处理程序UncaughtErrorEv...