【MySQL进阶之路 | 基础篇】MySQL之多表查询

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL进阶之路 | 基础篇】MySQL之多表查询

1. 前言

  • 多表查询,也称为关联查询.指两个或两个以上的表一起完成查询操作.
  • 前提条件 : 这些一起查询的表之间是有关系的(一对一/一对多).他们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键.

2. 笛卡尔积现象(交叉连接)

(1).例 :

如果我们在两个表中未进行条件关联,直接查找,可能会出现笛卡尔积现象.即第一张表的一个数据需要跟第二张表的所有数据匹配.又称交叉连接.

  • 图中2889条数据=第一张表的记录数X第二张表的记录数.
  • CROSS JOIN的作用就是可以把任意表进行连接,即使这两张表不相关.
  • 为了避免出现笛卡尔积现象,我们可以在WHERE子句中加入有效的条件.

3. 带有连接条件的多表查询

例 :

  • 从第一张表的第一条记录开始,与第二章表的所有记录进行条件关联,剩下的是满足关联条件的记录. 只剩下106条记录.因为employees表中第一条记录的departmentid字段为null.
  • FROM子句中,可以给表起别名.一旦起了别名,后续WHERE子句中,就不能使用以前的表名了.相当于表的别名对原先的表名进行了覆盖.但字段的别名不会对原字段进行覆盖.

4. 多表查询的分类

  • 等值连接与非等值连接
  • 自连接与非自连接
  • 内连接与外连接

5. 等值连接与非等值连接

(1).等值连接 :

上述例就是等值连接.因为连接条件是=运算符.

(2). 非等值连接 :

自然而然,连接条件不是=运算符的,即是非等值连接.比如 :

  • 当查询的字段是两个表中的共有字段时,需要指定是哪个表中的字段.不然会报错.
  • 对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名进行限定(如d.locationid).

6. 自连接与非自连接

(1). 自连接

例 :

  • 自连接顾名思义,自己的表与自己连接查询.
  • emp与mana本质上是一张表(物理磁盘上只有一张表),只是用取别名的方式在逻辑上虚拟成两个表代表不同的意义,然后两个表进行内连接,外连接.

(2) 非自连接

上述例均为非自连接.

7. 内连接与外连接

(1). 内连接 : (数学中的交集)


上述我们涉及到的全部例子均是内连接.


合并具有同一列的两个以上的行,结果集中不包含一个表与另一个表不匹配的行.


格式为 :


SELECT 字段列表


# INNER可省略.


FROM A表 JOIN B表


ON 关联条件


WHERE 等其他子句.


例 :  

只能查询到满足关联条件的记录,不能查询到不满足条件的记录.

还可以多个表内连接.

(2). 外连接


  • 外连接分为 : 左外连接,右外连接,满外连接.
  • 左外连接(LEFT OUTER JOIN / LEFT JOIN) : 结果集中不仅包含了两个表中满足连接条件的记录,还包含了左表中不满足连接条件的记录.
  • 右外连接(RIGHT OUTER JOIN / RIGHT JOIN) : 结果集中不仅包含了两个表中满足连接条件的记录,还包含了右表中不满足连接条件的记录.
  • 满外连接 : 结果集中不仅包含了两个表中满足条件的记录,还包含了左表中不满足条件的记录+右表中不满足条件的记录.

8. 左外连接与右外连接

(1). 左外连接

例 :

可以看到有107条记录,而上述内连接的情况下只有106条记录.可知左外连接包含了左表不满足条件的记录.


(2). 右表连接


右表连接与左表连接类似.


格式 :


SELECT 查询字段


FROM A表


RIGHT JOIN B表


ON 连接条件


WHERE 其他子句.

9. 满外连接与UNION关键字

  • SQL99是支持满外连接的.即使用FULL JOIN/FULL OUTER JOIN来实现
  • 但MySQL并不支持这种写法.但可以使用LEFT JOIN UNION RIGHT JOIN代替.

10. UNION的使用

(1). 合并查询结果 : 利用UNION关键字,可以给出多条SELECT语句,并将他们的结果组合成单个结果集.合并时,两个表对应的列数和数据类型必须相同.并且相互对应.各个SELECT语句间使用UNION/UNION ALL关键字分隔.


(2). UNION操作符


返回两个查询的结果集的并集,并去重复记录.

(3). UNION ALL操作符

返回两个查询的结果集的并集,但并不去重.

注意 : 执行UNION ALL语句时所需要的资源比UNION语句少.如果明知合并数据候的结果集不存在重复数据,或不需要去重,则尽量使用UNION ALL语句,以提高查询效率.


(4). 例 : UNION ALL实现连接 : 对应下表 左中 UNION ALL 右上 ---> 左下

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`;

11. 七种SQL JOINS的实现

如图 :

  • 左上 : 左外连接 LEFT JOIN
  • 左中 : 左外连接+WHERE过滤匹配的行
  • 左下 : 左外连接+WHERE过滤匹配的行 UNION ALL 右外连接 / 右外连接+WHERE过滤匹配的行 UNION ALL 左外连接
  • 中上 : 内连接 仅有匹配的行
  • 右下 : 左外连接+WHERE过滤匹配的行 UNION ALL 右外连接+WHERE过滤匹配的行
  • 右上 : 右外连接 RIGHT JOIN
  • 右中 : 右外连接+WHERE过滤匹配的行

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4天前
|
存储 关系型数据库 MySQL
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(4)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
4天前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(3)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
9天前
|
SQL 存储 关系型数据库
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)二
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)二
19 0
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
19 5
|
9天前
|
关系型数据库 MySQL 数据库
MySQL数据库——多表查询(4)-实例练习、多表查询总结
MySQL数据库——多表查询(4)-实例练习、多表查询总结
14 1
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——多表查询(3)-自连接、联合查询、子查询
MySQL数据库——多表查询(3)-自连接、联合查询、子查询
13 1
|
9天前
|
关系型数据库 MySQL 数据库
MySQL数据库——多表查询(2)-内连接、外连接
MySQL数据库——多表查询(2)-内连接、外连接
13 1
|
9天前
|
关系型数据库 MySQL 数据库
MySQL数据库——多表查询(1)-多表关系(一对多、多对对多、一对一)、多表查询概述(概念、笛卡尔积、分类)
MySQL数据库——多表查询(1)-多表关系(一对多、多对对多、一对一)、多表查询概述(概念、笛卡尔积、分类)
15 0