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

阿里云国内75折 回扣 微信号:monov8
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6

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

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

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

语法说明如下。

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

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

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

1. 连接条件

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

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

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

2. 连接的类型

连接查询是关系数据库中多表查询的主要形式分为三种类型交叉连接、内连接和外连接。为了便于理解各种类型的连接运算假设两个表R和SR和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编程思想”同一类型的图书的编号、书名和图书类型代码。

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

阿里云国内75折 回扣 微信号:monov8
阿里云国际,腾讯云国际,低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号:monov8 飞机:@monov6
标签: mysql数据库