专栏首页零基础自学Java【JavaWeb】64:多表查询详解

【JavaWeb】64:多表查询详解

昨天学习了多表设计,事实上我们所需要的数据,通常会来自多张表。

那么如何使用sql语句一次性查询多张表的数据?

这是我们应该去考虑的问题,为了解决这个问题,今天继续学习多表查询。

一、笛卡尔积与内连接

万万没有想到,学个数据库竟然还能接触到笛卡尔积?后面不会学着学着还会出现牛顿吧……

牛顿、拉格朗日、泰勒、傅里叶……简直就是大学噩梦般的存在。

现在有两张表:部门表、成员表。

那如何查询出一个结果既显示成员又显示部门呢?

就需要引入笛卡尔积的概念:

格式:select * from member,department;

查出来的数据就相当于成员表与部门表的乘积。

也就是将成员表里的每一条数据都和部门表中的每一条匹配连接。

  • 成员表一共有7条数据
  • 部门表一共有4条数据
  • 那根据笛卡尔积查询出来的数据一共4*7=28条

那么现在问题来了:这就变成排列组合了,查询到的结果冗余。如何避免结果冗余?

将这两张表相同的地方作为查询条件:

①隐式内连接

select * from+表A+表B+where+A与B相交的部分;

②显示内连接

select * from+表A+inner join+表B+on+A与B相交的部分;

  • inner join,也就是内连接的意思。
  • on,在这里就相当于where,后面接查询条件,其中on也可以替换成where。

那这两者有没有区别呢?

基本没区别,就只是语法不一样,个人觉得:

  • 第一种语法更好理解。
  • 第二种语法看上去更加地专业。

二、三种外连接

外连接又分为左外连接和右外连接。

  • 左外连接:显示左表的全部记录以及右边符合连接条件的记录。
  • 右外连接:原理同上,只不过表相反。

①左外连接

select * from+表A+left outer join+表B+on+A与B相交的部分;

  • left,左边
  • outerjoin:外连接

也就是左边表A全部的数据,同时加上表B中与之相交的部分。

②右外连接

select * from+表A+right outer join+表B+on+A与B相交的部分;

  • right ,右边
  • outerjoin:外连接

也就是右边表B全部的数据,同时加上表A中与之相交的部分。

③全外连接

select * from+表A+full outer join+表B+on+A与B相交的部分;

全外连接里的关键单词为full。

但是这个语法在MySQL数据库中不支持,Oracle数据库才支持。那MySQL中是如何办的?

就是将左外连接和右外连接结合起来了。

三、四种连接方式图解

表A与表B,其中它们相互重合的部分为C。

四种连接方式图解如下:

①内连接

就相当于C。

将这两张表重合的部分查询出来。

②左外连接

就相当于A+C。

左边的表加上另一张表与之相交的部分。

③右外连接

就相当于C+B。

右边的表加上另一张表与之相交的部分。

④全外连接

就相当于A+B+C。

两张表的数据相结合,其中相关联的部分要结合起来。在MySQL数据库中全外连接是如何表示的?

全外连接=左外连接+右外连接;

所以多了一个C,那么就要去重。

语法格式也就是左外连接和右外连接相加。

  • union :去掉重复的数据。
  • union all :不去掉重复的数据。
  • 其中注意第一条查询语句不用加分号。

四、关联子查询

现有一个需求:要查询出年龄最小的部门成员信息。

①常规方法

先查询出最小的年龄是多少(通过聚合函数)

再根据查询到的最小年龄查询对应的成员信息

这样做自然是能解决需求的,但是有一个问题:

查询要访问数据库两次,并且第二次查询需要等到第一次的查询结果出来后才能写sql语句。②子查询

等于是将常规方法中的两步结合成一步了。

语法:A查询语句作为B查询语句的条件,那么A查询称之为子查询,B查询称之为主查询。

子查询都要写在 () 里,且执行要先于主查询。

③all的用法

前面的两个方案中都是使用了聚合函数min()来找出最小的年龄是多少。

而all也能达到这样的目的,语法为:

age<=all(select age from member)。

翻译过来就是:年龄小于或等于从member表中查到的所有年龄,也就是最小年龄了。

五、in、any和some的用法

昨天程序员表和项目表的多对多表关系。

看中间表

  • 第一行:刘小爱开发微软,money为10000;
  • 第二行:刘小爱开发苹果,money为20000;
  • ……

这样看下来就一目了然。现有一个需求:

查询money大于10000的程序员信息。

分析:

第一步:先从中间表coder_project中查询出money大于10000的coder_id;

第二步:根据查询到的coder_id查询对应的coder信息。

学了子查询,可以将这两步并为一步。

子查询查出coder_id,主查询再查出程序员信息。

①In的使用

in就是表示包含子查询查出的数据。

  • 当子查询结果只有一个时,可以用=,也可以用in。
  • 当子查询结果有多个时,不可以用=,只能用in。

所以说in的使用其实包含了=。

②any的使用

虽然子查询结果有多个,我就是要用=,怎么办?

就可以用any,any表示任意的意思,也就是=子查询查出的任意一个数据。

③some的使用

同any一样的道理:也就是=子查询查出的一些数据。some作用和any一样。

六、as的使用

需求 : 查询money大于10000的程序员信息和对应的money。

分析

第一步:我们从中间表coder_project中可以找到满足条件的coder_id和money,结果就是一张新的临时表,取名temp。

第二步:根据code_id(临时表)找出所有对应的程序员信息(coder表)和money(临时表)。

①思路分析

第一步:

将查询到的结果作为了一张临时表temp,但是这张表本身是不存在的,我们没法直接在第二步中用,会报错。

temp表其实就是中间表coder_project中的一部分满足条件的数据。

第二步:

  • 我们需要对应程序员的所有信息(也就是coder.*)
  • 我们需要temp表中的money
  • 从temp表、coder表中查询
  • coder表中的id和temp表中的coder_id相同,也就是内连接相等的部分。

②as定义临时表

利用子查询的思路,将第一步的结果作为子查询,然后给它起一个别名,也就是temp表,这样就能直接使用了。

思路捋清晰了,as定义临时表也就很好懂了。

最后

给这两天的学习做一个总结

本文分享自微信公众号 - 刘小爱(liuxiaoai946),作者:刘小爱

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【微服务】152:Stream流和通用mapper批量查询的使用

    昨天虽然完成了商品查询,但是其有一个问题,我们查询的数据是SPU,SPU中关于商品分类和品牌只是记录了其Id。

    刘小爱
  • 【微服务】164:商品微服务需要提供的接口

    因为涉及到一个分页查询,所以service层的代码较为复杂,当时详细编写并讲解过其代码实现,在此不再赘述。

    刘小爱
  • 【微服务】160:Elasticsearch高级使用

    昨天学了match匹配和term匹配,这是两种最基础也很重要的查询方式,使用起来也简单。

    刘小爱
  • MySQL查询执行的过程

    当我们希望MySQL能够以更高的性能进行查询时,弄清楚MySQL中是如何优化和执行查询的就显得很有必要,这里,先搬出来一张图镇楼:

    AsiaYe
  • MySQL探秘(二):SQL语句执行过程详解

     昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

    程序员历小冰
  • MySQL探秘(二):SQL语句执行过程详解

     昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

    aoho求索
  • 如何加倍提升 Elasticsearch 查询性能

    我们在使用Elasticsearch进行查询的过程中发现,如果查询时间跨度大,查询数据集比较庞大,即使只是返回少量的结果,查询耗时仍然比较长。我们通过分析pro...

    腾讯云时序数据库团队
  • MySQL性能优化(六):常见优化SQL的技巧

    在面对不够优化、或者性能极差的SQL语句时,我们通常的想法是将重构这个SQL语句,让其查询的结果集和原来保持一样,并且希望SQL性能得以提升。而在重构SQL时,...

    xcbeyond
  • 如何加倍提升 Elasticsearch 查询性能

    我们在使用Elasticsearch进行查询的过程中发现,如果查询时间跨度大,查询数据集比较庞大,即使只是返回少量的结果,查询耗时仍然比较长。我们通过分...

    用户1644123
  • MYSQL子查询与连接

    37:子查询与连接 SET 列名 gbk;//改变客户端数据表的编码类型。 子查询 子查询(Subquery)是指出现在其他SQL语句内的SELECT子句 例...

    别先生

扫码关注云+社区

领取腾讯云代金券