写点什么

数据库原理及 MySQL 应用 | 多表查询

作者:TiAmo
  • 2022-12-16
    江苏
  • 本文字数:2987 字

    阅读完需:约 10 分钟

数据库原理及MySQL应用 | 多表查询

在实际应用中,多表查询应用相对较多,根据多表之间的相关列,可以从多个表中检索出所需数据。

在实际应用中,单表查询应用范围相对较少,因为用户需要的数据往往存储在多个不同的表中,这时需要进行多表查询。多表查询是通过多表之间的相关列,从多个表中检索出所需数据。一个数据库中的多个表之间一般存在着某种内在联系或是相关属性,用户通过连接运算就可以把多张表连接成一张表,这样又回到了之前的简单查询,从而查询的范围可以扩展到多表。

多表查询的基本语法格式如下所示。


语法说明如下。

JOIN_TYPE 是连接运算符,用于指定连接类型,包括内连接(INNER JOIN 或 JOIN)、外连接(OUTER JOIN)和交叉连接(CROSS JOIN)。

ON 用于设置连接条件,join_condition 是连接条件表达式。

由于连接查询涉及多个表,所以列的引用必须明确,重复的列名必须使用表名加以限定。为了增加可读性,建议使用表名限定列名,格式为“表名.列名”。

1. 连接条件

连接条件是通过两张表的相关属性(一般情况下是外键)来实现的,多表连接需要两两连接。从下面两个方面实现连接查询。

● 找到要连接的表中用于连接的列。典型的连接条件是:找到两张表是否存在主外键关系,即一张表有与另一张表的主键存在参照关系的外键。

● 指定用于比较各列值的逻辑运算符(如=或<>),其中等值连接比较常见。

2. 连接的类型

连接查询是关系数据库中多表查询的主要形式,分为三种类型:交叉连接、内连接和外连接。为了便于理解各种类型的连接运算,假设两个表 R 和 S,R 和 S 中存储的数据如图 7-20 所示。


■ 图 7-20 表 R 和表 S 的数据

1) 交叉连接

交叉连接是指返回连接的两个表的笛卡儿积,即结果集中包含两个表中所有行的全部组合。

交叉连接的运算符是 CROSS JOIN。表 R 和表 S 进行交叉连接的结果集如图 7-21 所示。


■ 图 7-21 表 R 和表 S 交叉连接的结果集

2) 内连接

内连接是指用比较运算符设置连接条件,返回符合连接条件的数据行。内连接包括三种类型:等值连接、自然连接和不等值连接。

● 等值连接:在连接条件中使用等号(=)比较连接的列,返回符合连接条件的行。结果集中包括重复列,显示两次连接列。

● 自然连接:与等值连接的运算规则相同。但结果集中不包括重复列,只显示一次连接列。自然连接的连接列符合典型的连接条件,是具有内在连接的主键和外键列。

● 不等值连接:在连接条件中使用除去等号以外的其他运算符(>、<、>=、<=、!=)比较连接的列。

内连接运算符是 INNER JOIN 或 JOIN。表 R 和表 S 进行内连接的结果集如图 7-22 所示。


■ 图 7-22 表 R 和表 S 内连接的结果集

3) 外连接

外连接是指返回的结果集除了包括符合连接条件的行以外,还返回至少一个连接表的其他行。外连接包括三种类型:左外连接、右外连接和全外连接。

● 左外连接:是指通过左向外连接返回左表的所有行,右表中不符合连接条件的行设置为 NULL。运算符是 LEFT OUTER JOIN 或 LEFT JOIN。

● 右外连接:是指通过右向外连接返回右表的所有行,左表中不符合连接条件的行设置为 NULL。运算符是 RIGHT OUTER JOIN 或 RIGHT JOIN。

● 全外连接:是指返回两个表的所有行,两个表中不符合连接条件的行分别设置为 NULL。

表 R 和表 S 进行外连接的结果集如图 7-23 所示。


■ 图 7-23 表 R 和表 S 外连接的结果集

01、内连接

内连接是一种常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,即只返回满足连接条件的数据行。两个表连接时,连接列的名称可以不同,但要求连接列必须具有相同的数据类型、长度和精度,且表达同一意义。一般情况下,连接列是数据表的主键和外键。内连接查询的基本语法格式如下所示。



语法说明如下。

内连接有两种语法格式,分别是:在 FROM 子句中定义连接、在 WHERE 子句中定义连接。

内连接是默认连接,可以省略 INNER 关键字。

内连接包括三种类型:等值连接、自然连接和不等值连接。等值连接的比较运算符是“=”;不等值连接的比较运算符是“>、>=、<、<=、!=”;自然连接是不包含重复列的特殊等值连接。

【例 7-42】在图书销售数据库 booksale 中查询图书表 books 中所有图书的信息。


执行结果如图 7-24 所示。


■ 图 7-24 内连接查询

如果要连接的表的连接条件中列名是同名,可以将 ON 条件换成 USING()子句;

【例 7-43】在图书销售数据库 booksale 中查询图书表 books 中所有图书的信息。



输出的列如果是两个表中都有的列,则必须在输出的列名前加上表名进行区分,用“表名.列名”,如上面命令中的 b.bookid;其他输出列都是表中不重复的列,所以直接写列名即可。多表连接时,先将两表连接成一张表,再拿连接成的这张表和下一张表连接,以此类推。

02、外连接

内连接只返回满足连接条件和查询条件的数据行。但在实际应用中,有时需要以某一个表为参考表,显示和这个表连接的多个表的信息,参考表需要显示所有行。需要全记录显示的这个表可以是左表(左外连接)、右表(右外连接)或两个表(全外连接)。外连接查询的基本语法格式如下所示。


语法说明如下。

LEFT JOIN 是左外连接,查询记录时以 LEFT JOIN 左边的表为参考表,查询结果包含参考表里所有的记录,如果左表的某行在右表里没有匹配的行,则在右表的输出列上显示空值。

RIGHT JOIN 是右外连接,查询记录时以 RIGHT JOIN 右边的表为参考表,查询结果包含参考表里所有的记录,如果右表的某行在左表里没有匹配的行,则在左表的输出列上显示空值。

可将相同两个表的左外连接和右外连接使用 UNION 关键字进行合并连接,间接实现全外连接。

【例 7-44】在图书销售数据库 booksale 中查询图书表 books 中所有图书的销售情况。


执行结果如图 7-25 所示。


■ 图 7-25 左外连接查询

这个查询为左外连接查询。books 是左边的表,所以 books 表中所有记录都会显示,orderitems 是右边的表,当没有匹配内容时显示空值 NULL。

【例 7-45】在图书销售数据库 booksale 中查询图书表 books 中所有图书的评价情况。


执行结果如图 7-26 所示。


■ 图 7-26 右外连接查询

这个查询为右外连接查询。books 是右边的表,所以 books 表中所有记录都会显示,comments 是左边的表,当没有匹配内容时显示空值 NULL。

03、交叉连接

交叉连接是在没有 WHERE 子句的情况下,被连接的两个表中所有数据行的笛卡儿积。两个表进行交叉连接时,结果集大小为两个表数据行之积,数据列之和。交叉连接在实际应用中极少使用。交叉连接查询的基本语法格式如下所示。


【例 7-46】在图书销售数据库 booksale 中对图书表 books 和类别表 categories 进行交叉查询。


books 表有 8 列 10 条记录,categories 表有 2 列 4 条记录,因此查询结果为 10 列 40 条记录。

04、合并连接

在 MySQL 中可以通过 UNION 关键字将两个同结构的数据表进行合并。合并查询的基本语法格式如下所示。


【例 7-47】在图书销售数据库 booksale 中将图书表 books 中姓“张”的作者出版的图书和生活类图书信息合并显示。

(1) 带 UNION ALL 的合并连接。


当使用 UNION ALL 时,MySQL 会把所有的记录返回,不会去掉重复记录。其效率高于 UNION。

(2) 带 UNION 的合并连接。


当使用 UNION 时,MySQL 会把结果集中重复的记录删掉,其效率不如 UNION ALL。

05、自连接

自连接是一个表和其自身进行连接,就是同一个表在 FROM 子句中出现两次。这是一种特殊的等值连接,也是一种特殊的内连接。为了区分,必须对表指定不同的别名,列名前也要加上表的别名进行区分。自连接查询的基本语法格式如下所示。


【例 7-48】在图书销售数据库 booksale 中查询图书表 books 中和“Java 编程思想”同一类型的图书的编号、书名和图书类型代码。


因为自连接是内连接的特例,所以有两种语法格式。

发布于: 刚刚阅读数: 4
用户头像

TiAmo

关注

有能力爱自己,有余力爱别人! 2022-06-16 加入

还未添加个人简介

评论

发布
暂无评论
数据库原理及MySQL应用 | 多表查询_MySQL_TiAmo_InfoQ写作社区