MySQL：第07章-多表查询

已于 2023-06-14 18:22:35 修改
阅读量96 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
于 2023-06-14 18:13:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zhong_Er_Bing/article/details/131185235
版权

多表查询，也称为关联查询，指两个或多个表一起完成查询操作

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），他们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键

1.一个案例引发的多表连接

1.1 案例说明

从多个表中获取数据

# 案例：查询员工的姓名及其部门名称
SELECT last_name,department_name
FROM employees,departments;

查询结果：

 分析错误情况：

SELECT COUNT(employee_id) FROM employees;
# 输出107行

SELECT COUNT(department_id) FROM departments;
# 输出27行

SELECT 107*27 FROM DUAL;

我们把上述多表查询中出现的问题称为：笛卡尔积错误

1.2 笛卡尔积（或交叉连接）的理解

笛卡尔乘积是一个数学运算。

假设有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，即X * Y

 SQL92中，笛卡尔积也称为交叉连接，英文为CROSS JOIN。

SQL99中，也是使用CROSS JOIN表示交叉连接。

作用就是可以把任意表进行连接，即使两张表不相关。

# 案例：查询员工的姓名及其部门名称
# 错误写法：会造成笛卡尔积
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

1.3 案例分析与问题解决

  • 笛卡尔积的错误会在下面条件下产生：
    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接
  • 为了避免笛卡尔积，可以在WHERE加入有效的连接条件
  • 加入连接条件后，查询语法： 
    SELECT table1.clolumn,table2.column
FROM table1,table2
WHERE table1.column1 = table2.column2; # 连接条件

  • 在WHERE子句中写入连接条件

  • 正确写法：

    # 案例：查询员工的姓名及其部门名称
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;

    在表中有相同列时，在列名之前加上表名前缀

2.多表查询分类讲解

分类1：等值连接 VS 非等值连接

等值连接：

SELECT employees.employee_id,employees.last_name,employees.department_id,
departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

 拓展1：多个连接条件用AND操作符

拓展2：区分重复的列名

  • 多个表中有相同列时，必须在列名之前加上表名前缀
  • 在不同表中具有相同列名的列可以用表名加以区

拓展3：表的别名

  • 使用别名可以简化查询
  • 列名前使用表名前缀可以提高查询效率 
    SELECT e.employee_id,e.last_name,d.department_id,d.department_name,d.location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id;

    需要注意的是，如果使用了表的别名，在查询字段、过滤条件中就只能使用别名进行替代，不能使用原有的表名，否则会报错

阿里开发规范：

  • 【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定
  • 说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列再多个表中存在在时，就会抛出异常
  • 正例： 
    SELECT t1.name FROM table1 AS t1,table2 AS t2 WHERE t1.id=t2.id;

  • 反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年之后，若在表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句会出现1052异常：Column 'name' in field list is ambiguous。

拓展4：连接多个表

总结：连接n个表，至少需要n-1个连接条件

非等值连接

SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

运行结果：

分类2：自连接 VS 非自连接

  • 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不通的意义，然后两个表再进行内连接、外连接等查询 
    SELECT CONCAT(worker.last_name, ' works for ',manager.last_name)
FROM employees worker,employees manager
WHERE worker.manager_id = manager.employee_id;

    运行结果：

     

分类3：内连接 VS 外连接

除了查询满足条件的记录外，外连接还可以查询某一方不满足条件的记录

  •  内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
  • 外连接：两个表再链接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（NULL）
    • 左外连接：左表为主表，右表为从表
    • 右外连接：右表为主表，左表为从表

3.SQL99语法实现多表查询

3.1 基本语法

SELECT table1.column,t2.column,t3.column
FROM table1
JOIN table2
ON table1 和 table2 的连接条件
JOIN table3
ON table2 和 table3 的连接条件;
  • 语法说明：
    • 可以使用ON子句指定额外的连接条件
    • 这个连接条件是与其他条件分开的
    • ON子句使语句具有更好的易读性
    • 关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接

3.2 内连接（INNER JOIN）的实现

  • 语法 
    SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

  • 举例1：

    SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;

    运行结果：

  • 举例2：

    SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

    运行结果：

3.3 外连接（OUTER JOIN）的实现

3.3.1 左外连接（LEFT OUTER JOIN）

  • 语法 
    # 查询结果是表A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例 
    SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

    运行结果：

3.3.2 右外连接（RIGHT OUTER JOIN）

  • 语法 
    # 查询结果是表B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例： 
    SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

    运行结果：

 3.3.3 满外连接（FULL OUTER JOIN）

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
  • SQL99是支持满外连接的，使用FULL JOIN或FULL OUTER JOIN实现
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以使用 LEFT JOIN UNION RIGHT JOIN 代替

4.UNION的使用

  • 合并查询结果，利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集
  • 合并时，两个表对应的列数和数据类型必须相同，并且相互对应
  • 各个SELECT语句之间使用UNION或UNION ALL关键字分隔
  • 语法格式： 
    SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2;

  • UNION操作符

  • UNION ALL操作符
  • 注意：
    • 执行UNION ALL语句时所需要的资源比UNION少
    • 如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高查询的效率

5.七种SQL JOINS的实现

 以表employees和表departments为例，代码实现上述：

# 中图：内连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id;

# 左上图：左外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id;
# 右上图：右外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id;
# 左中图
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
# 右中图
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

# 左下图：满外连接
# 左中图+右上图
SELECT e.employee_id,e.last_name,d.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 e.employee_id,e.last_name,d.department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id;
# 右下图
SELECT e.employee_id,e.last_name,d.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 e.employee_id,e.last_name,d.department_name
FROM employees e 
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

语法格式小结：

  • 左中图

    SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句;

  • 右中图

    SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句;

  • 左下图

    SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句

UNION

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

  • 右下图

    SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句

UNION

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 从表关联字段 IS NULL AND 等其他子句

6.注意

  • 控制连接表的数量
  • 多表连接就相当于嵌套for循环，非常消耗资源，会让SQL查询性能下降
  • 超过三个表禁止JOIN
  • 需要JOIN的字段，数据类型保持绝对一致
  • 多表关联时，保证被关联的字段需要有索引
予愚爱
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL高级-第07章-InnoDB数据存储结构
05-17
MySQL是世界上最流行的关系型数据库管理系统之一，其InnoDB存储引擎是默认且最常使用的引擎，尤其在事务处理和数据安全性方面表现出色。本章节将深入探讨InnoDB数据存储结构，帮助你理解其内部机制，提升数据库管理...
笛卡尔查询
yeewingho的学习笔记
05-16 784
SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。 例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写： SELECT * FROM students, classes; 这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列
MySQL --- 多表查询 - 笛卡尔积和正确的多表查询、等值连接和不等值连接、内连接和外连接
weixin_60569662的博客
07-18 1942
前面我们使用的查询，只是对单表进行查询，但是在具体的应用中，经常需要实现在一个查询语句中显示多张数据表的数据，因为基本上数据都是分表的，需要根据当前表在其他表中查询数据，把多张表结合起来进行查询数据的方式就是多表联合查询。虽然我们确实可以找到某一位员工的信息和所在的部门信息，但是我们可以发现这两个查询语句是独立的，我们并不能直观地看到所有的数据，如果要直观地看到所有的数据，这就需要使用到多表查询了。在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。.............
Oracle快速入门（多表查询大全）
程序员猫爪
08-30 2797
笛卡尔积的概念，Oracle当中笛卡尔查询的格式，Oracle当中笛卡尔查询的格式，Oracle当中笛卡尔查询的格式，内链接，外链接，子查询：查询语句当中嵌套查询语句;用来解决复杂的查询的语句，exists（查询语句）：存在的意思，rownum：伪列，rowid：伪列（每行记录所存放的真实物理地址）
MySQL的多表查询(笛卡尔积原理)
weixin_30682127的博客
03-30 3522
先确定数据要用到哪些表。 将多个表先通过笛卡尔积变成一个表。 然后去除不符合逻辑的数据（根据两个表的关系去掉）。 最后当做是一个虚拟表一样来加上条件即可。 注意：列名最好使用表别名来区别。 笛卡尔积 Demo: 左，右连接，内，外连接 l 内连接： 要点：返回的是所有匹配的记录。 2....
mysql加强（4）~多表查询：笛卡尔积、消除笛卡尔积操作(等值、非等值连接)，内连接（隐式连接、显示连接)、外连接、自连接
伟庭的博客
01-29 4222
mysql加强（4）~多表查询：笛卡尔积、消除笛卡尔积操作(等值、非等值连接)，内连接（隐式连接、显示连接)、外连接、自连接
mysql第七章.pdf
06-25
MySQL数据应用与开发第七章的内容，包括自定义变量的应用、表达式、自定义函数、控制流程语句等知识。
JAVA-WEB开发教程-第七章-JSP数据库开发基础知识.ppt
11-16
JAVA-WEB开发教程第七章JSP数据库开发基础知识 本章主要讲述了JSP数据库开发的基础知识，包括搭建JSP开发数据库的环境、SQL语言的基本操作、JSP与数据库的连接和操作等内容。通过学习本章的内容，读者可以了解JSP...
mysql第七章.doc
06-25
MySQL数据应用与开发第七章的内容，包括自定义变量的应用、表达式、自定义函数、控制流程语句等知识。
MySQL8.0学习第二章
09-09
MySQL 8.0 学习第二章主要涵盖了数据库的基础操作，包括数据查询语言（DQL）、数据表约束、表间关系、多表查询以及数据库的备份与还原。本章的知识点详细解析如下： 1. DQL 数据查询语言： - 排序（ORDER BY）：...
mysql多表查询（一）笛卡尔集
yx1166的博客
01-28 1万+
为什么要使用多表查询？         因为我们经常需要在很多个表之间查询数据。比如，查询会员的银行卡提现记录。首先我们在会员表中查询会员的银行卡id，再到提现记录表中根据银行卡id查询提现记录。 什么是笛卡尔集？ 笛卡尔集的列数为每个表的列数之和，笛卡尔集的行数为每个表的行数相乘。我们经常做的多表查询就是在笛卡尔集中通过筛选条件得出的数据，所以笛卡尔集是多表查询的基础。 多表查询的条件有
多表查询。笛卡尔积查询，内连接，外连接
qiuchaoxi的博客
04-18 1万+
如果想查询出每个部门，和每个部门对应的员工的名字。（dept，emp两张表）。图1笛卡尔积查询笛卡尔积查询:将两张表的记录进行一个相乘的操作查询出来的结果就是笛卡尔积查询,如果左表有n条记录,右表有m条记录,笛卡尔积查询出有n*m条记录,其中往往包含了很多错误的数据,所以这种查询方式并不常用。select * from dept,emp;图2内连接查询 内连接查询:查询的是左边表和右边表都能找到对...
【MySQL】多表查询
热门推荐
Cheng_913的博客
06-18 3万+
首先，为了方便说明问题，创建两个表emp（雇员信息）和dept（雇员部门信息），其数据如下： 在之前的博客中，我们分享了单表查询的方法，但是在实际应用中，我们要查的数据很可能不在同一个表中，而是来自于不同的表。多表查询如果不加任何条件，得到的结果称为笛卡尔积。 例如，查找雇员名、雇员工资以及部门所在的名字。 可以发现，结果是这样的，从第一个表中选出第一条记录，和第二个表中的所有所有记...
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 462
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL：SELECT 语句
Zachyy的博客
07-20 320
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 669
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
mysql中ONLY_FULL_GROUP_BY报错
最新发布
zzzzzucc的博客
07-21 131
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
使用 Vue3、Node.js、MySQL、Electron 和 Express 实现用户登录、文章管理和截屏功能
liyananweb的博客
07-15 954
通过本文，我们详细介绍了如何使用 Vue3、Node.js、MySQL、Electron 和 Express 实现一个完整的用户登录、文章管理和截屏功能的应用。希望这篇文章能为你提供有价值的参考，帮助你更好地理解和实现前后端分离的应用开发。如果你有任何问题或建议，欢迎在评论区留言讨论。
MySQL8.0学习第二章：DQL掌握、约束与关系、多表查询、备份与还原
MySQL8.0学习第二章是MySQL数据库课程的一部分，该章节主要介绍了DQL数据查询语言、约束和策略、表关系、多表查询以及数据库备份与还原等方面的内容。学习这一章的目标是掌握各种查询语句的排序、单行函数、聚合函数...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值