MySQL --- 多表查询 - 七种JOINS实现、集合运算、多表查询练习

已于 2022-07-26 14:42:34 修改
阅读量738 收藏 6
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库
于 2022-07-20 09:44:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_60569662/article/details/125849203
版权

七种JOINS实现

左上图的JOIN是左外连接，右上图的JOIN是右外连接，中间图的JOIN是内连接，左中图的JOIN在左上图的基础上再去掉中间重复的，只需要 A 在 B 中没有的部分(空的部分)，右中图的JOIN在右上图的基础上再去掉中间重复的，只需要 B 在 A 中没有的部分，左下角是全外连接

-- 左上 左外连接
SELECT * FROM
emp LEFT JOIN dept
ON emp.deptno=dept.deptno;

-- 右上 右外连接
SELECT * FROM
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno;

-- 左中 在左外连接的基础上
SELECT * FROM
emp LEFT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL;

-- 右中 在右外连接的基础上
SELECT * FROM
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL;

全外连接可以使用以上的两张图拼接而成，一共有 3 种方式可以实现

接下来用左上图和右中图来实现全外连接

-- 左上  左外连接
SELECT * FROM
emp LEFT JOIN dept
ON emp.deptno=dept.deptno -- 去掉;
UNION -- 求并集
-- 右中  在右外连接的基础上
SELECT * FROM
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL;

使用右上图和左中图来实现全外连接

-- 右上 右外连接
SELECT * FROM
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno -- 去掉;
UNION -- 求并集
-- 左中 在左外连接的基础上
SELECT * FROM
emp LEFT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL;

右下图可以在全连接的基础上去掉中间部分，或者用左中图和右中图拼接

-- 右下图用左中图和右中图拼接
-- 左中 在左外连接的基础上
SELECT * FROM
emp LEFT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL -- 去掉;
UNION
-- 右中 在右外连接的基础上
SELECT * FROM
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno
-- 去掉满足连接条件的记录→只需要不满足条件的→连接条件不匹配或者为NULL
WHERE emp.deptno IS NULL;

补充

USING 指定条件，等值条件的两个字段都是一模一样的，既然是一模一样的，能不能只写一个字段呢？答案是肯定的，USING 可以让 MySQL 自己在表中找这个字段给我们建立关联

-- 内连接
-- 隐式内连接
SELECT *
FROM emp,dept
WHERE emp.deptno=dept.deptno;

-- 显式内连接 语法不同
SELECT *
FROM emp INNER JOIN dept
ON emp.deptno=dept.deptno;

-- 使用USING来关联相同的字段
SELECT *
FROM emp JOIN dept
USING(deptno);

自连接：一张表做等值查询，把一张表作为两张表做等值查询，由于字段不一样，所以不能使用 USING

每一个员工都有领导编号 mgr，领导的信息也在这张表当中

-- 自连接 由于两张表名一样 只能通过取别名区分

SELECT *
FROM emp e, emp m
-- 对应的领导信息
WHERE e.mgr=m.empno;


-- 自连接 由于两张表名一样 只能通过取别名区分

SELECT e.empno,e.ename,m.ename 领导
FROM emp e,emp m
-- 对应的领导信息
WHERE e.mgr=m.empno;

集合运算

MySQL只支持并集运算。

并集即两个集合所有部分

 

UNION DISTINCT

  1. 会删除重复行

  2. 相同的行在结果中只出现一次

A={A，B，C}，B={C，D，E}

UNION DISTINCT C={A，B，C，D，E}，有两个 C，重复的 C 只保留一个

SELECT * FROM emp
UNION
SELECT * FROM emp WHERE deptno=10;

整个表有 15 条记录

部门编号为 10 有 3 条记录 

求并集有 15 条记录，UNION 后面不加默认为 DISTINCT

UNION ALL

  1. 不会删除重复行

  2. 相同的行在结果中可能出现多次

A={A，B，C}，B={C，D，E}

UNION ALL C={A，B，C，C，D，E}，会保留重复的

SELECT * FROM emp
UNION ALL
SELECT * FROM emp WHERE deptno=10;

求并集，保留重复的，共 15 + 3 = 18 条

要求

(1)输入的查询不能包含ORDER BY字句，可以为整个集合运算结果选择性地增加一个ORDER BY字句

SELECT * FROM emp
UNION ALL
SELECT * FROM emp WHERE deptno=10
-- 按姓名查询 对整个临时表排序而不是对emp表排序
ORDER BY ename;

MySQL --- 数据库查询 - 排序查询、分页查询

错误用法

SELECT * FROM emp ORDER BY emp.job
UNION ALL
SELECT * FROM emp WHERE deptno=10;

(2)两个查询必须包含相同的列数

SELECT emp.empno FROM emp 
UNION ALL
SELECT * FROM emp WHERE deptno=10;

第一个表只有 1 列

第二个表的列数正常  

(3)相应列必须具有兼容的数据类型。兼容个的数据类型：优先级较低的数据类型必须能隐式地转换为较高级的数据类型。比如输入的查询1的第一列为int类型，输入的查询2的第一列为float类型，则较低的数据类型int类型可以隐式地转换为较高级float类型。如果输入的查询1的第一列为char类型，输入的查询2的第一列为datetime类型，则会提示转换失败：从字符串转换日期和/或时间时，转换失败

(4)集合运算结果中列名由输入的查询1决定，如果要为结果分配结果列，应该在输入的查询1中分配相应的别名

SELECT emp.empno,emp.ename FROM emp 
UNION ALL
SELECT emp.empno 'id',emp.ename 'name' FROM emp WHERE deptno=10;

第一种情况在输入的查询2取别名，结果没有改变

SELECT emp.empno 'id',emp.ename 'name' FROM emp 
UNION ALL
SELECT emp.empno,emp.ename FROM emp WHERE deptno=10;

第二种情况在输入的查询1取别名，结果改变

(5)集合运算时，对行进行比较时，集合运算认为两个NULL相等

多表查询练习

  1. 查询出雇佣日期在1981年的所有员工的编号、姓名、雇佣日期、工作、领导姓名、雇佣月工资、雇佣年工资(基本工资+奖金)，工资等级、部门编号、部门名称、部门位置，并且求这些员工的月基本工资在1500~3500之间，将最后的结果按照年工资的降序排列，如果年工资相等，则按照工作进行排序。

需求很多，逐步分析

  • 确定所需要的数据表

    • emp：编号、姓名、雇佣日期，工作、月工资、年薪

    • emp：领导姓名

    • dept：部门编号、名称、位置

    • salgrade：工资等级

  • 确定一致的关联字段

    • 员工和领导：e.mgr=e1.empno

    • 员工和部门：e.deptno=dept.deptno

    • 员工和工资等级：e.sal BETWEEN s.losal AND s.hisal

步骤一：查询出所有在1981年雇佣的雇员编号、姓名、雇佣日期、工作、月工资、年工资，并且月薪在1500~3500之间。只需要emp单张表即可

奖金有空的部分，年薪最后的结果为空，需要判断如果奖金为空，就把奖金变成0

-- 获取1981年入职员工的信息并且满足薪资在1500~3500之间
SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪
FROM emp e
WHERE e.sal BETWEEN 1500 AND 3500;

步骤二：加入领导信息，使用自身关联

-- 获取1981年入职员工的信息并且满足薪资在1500~3500之间
SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,
m.ename 领导
FROM emp e,emp m
WHERE e.sal BETWEEN 1500 AND 3500
-- 获取领导姓名
AND e.mgr=m.empno;

 步骤三：加入部门信息

-- 获取1981年入职员工的信息并且满足薪资在1500~3500之间
SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,
m.ename 领导,d.deptno,d.dname,d.loc
FROM emp e,emp m,dept d
WHERE e.sal BETWEEN 1500 AND 3500
-- 获取领导姓名
AND e.mgr=m.empno
-- 加入部门信息
AND e.deptno=d.deptno;

步骤四：加入工资等级

-- 获取1981年入职员工的信息并且满足薪资在1500~3500之间
SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,
m.ename 领导,d.deptno,d.dname,d.loc,s.grade 工资等级
FROM emp e,emp m,dept d,salgrade s
WHERE e.sal BETWEEN 1500 AND 3500
-- 获取领导姓名
AND e.mgr=m.empno
-- 加入部门信息
AND e.deptno=d.deptno
-- 加入工资等级
AND e.sal BETWEEN s.losal AND s.hisal;

步骤五：排序

最后的结果按照年工资的降序排列，如果年工资相等，则按照工作进行排序。

-- 获取1981年入职员工的信息并且满足薪资在1500~3500之间
SELECT e.empno,e.ename,e.hiredate,e.job,e.sal,(e.sal+IFNULL(e.comm,0))*12 年薪,
m.ename 领导,d.deptno,d.dname,d.loc,s.grade 工资等级
FROM emp e,emp m,dept d,salgrade s
WHERE e.sal BETWEEN 1500 AND 3500
-- 获取领导姓名
AND e.mgr=m.empno
-- 加入部门信息
AND e.deptno=d.deptno
-- 加入工资等级
AND e.sal BETWEEN s.losal AND s.hisal
-- 排序
ORDER BY 年薪 DESC,e.job ASC;

qiuqiuyaq
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
【心得分享】-- 关系型数据库中七种常见的join(连接)查询
muzi木子
06-29 1737
1.1 什么是join查询？ join查询，即连接查询，当我们需要查询两张或两张以上表的关联数据的时候就需要使用连接查询，数据库中的连接查询分为：内连接查询(INNER JOIN)、左外连接查询(LEFT OUTER JOIN 或 LEFT JOIN)、右连接查询(RIGHT OUTER JOIN或RIGHT JOIN)以及全连接查询(FULL OUTER JOIN)。 1.2 内连接、外连接、全连接查询之间的区别是什么？ 类型 描述 图示 INNER JOIN 内连接查询，会把关联表中，有
MySQL高级知识-查询与索引优化分析
02-20
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、B两表共有2、A、B两表共有+A的独有3、A、B两表共有+B的独有4、A的独有5、B的独有6、AB全有MySQLFullJoin的实现因为MySQL不支持...
MySQL 多表查询 思维导图
guliguliguliguli的博客
08-01 220
MySQL多表查询思维导图，参考尚硅谷MySQL2022教程编写
mysql 多表联合查询 的七种方式 全部 左连接 右连接 内连接
qq_41486847的博客
02-25 4678
总共存在七总情况 图一 ： 也就是我们说的查询左边表的全部数据 使用左连接 select 字段列表 from A表 left join B表 on 关联条件 图二 ： 也就是我们说的查询右边表的全部数据 使用右连接 select 字段列表 from A表 right join B表 on 关联条件 图三 ： 也就是我们说的查询两张表的交集 也就是A∩B 使用内连接 select 字段列表 from A表 inner join B表 on 关联条件 图...
【MySQL篇】多表查询知识—— 多表查询（全）
m0_64231944的博客
11-11 278
多表查询语法及案例：内连接，外连接，自连接，联合查询等语法及案例使用：
MySQL：七种 SQL JOINS 的实现（图文详解）
weixin_52533007的博客
06-18 8235
本博主将用CSDN记录软件开发求学之路上亲身所得与所学的心得与知识，有兴趣的小伙伴可以关注博主！也许一个人独行，可以走的很快，但是一群人结伴而行，才能走的更远！
cakephp2.X多表联合查询join及使用分页查询的方法
10-20
在CakePHP 2.x框架中，进行多表联合查询（JOIN）和分页查询是数据库操作中的常见需求，尤其在处理复杂数据时显得尤为重要。以下将详细解释如何在CakePHP中实现这些功能。 首先，多表联合查询是通过JOIN语句来完成的...
mysql_advanced-master.zip
07-06
对大型表进行分区可以提高查询性能，而分片则是一种水平扩展策略，通过在多个服务器上分散数据来提高处理能力。 8. **复制与高可用性**： MySQL 的主从复制是实现高可用性和故障恢复的重要手段。了解如何配置复制...
sql-joins-app：SQL JOINS可视化工具
01-30
SQL Joins Visualizer帮助您使用维恩图在两个表之间构建SQL JOIN。 发展历程 。 Web应用程序基于构建。 要以开发模式启动它，需要安装ruby并在终端中运行： $ bundle # get all deps $ middleman server # start ...
mysql的JOIN用法详解-附带查询示例
qq_61950936的博客
06-30 6809
在 SQL 中，JOIN是用于将多个表中的数据连接在一起的操作。它通过指定连接条件将两个或多个表中符合条件的行组合起来，产生一个新的结果集。SQL 中常见的 JOIN 类型包括和。
Mysql Join语法解析与性能分析--通过集合来看join
10年老码农
01-26 540
一．Join语法概述 join 用于多表中字段之间的联系，语法如下： ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表；table2:右表。 JOIN 按照功能大致分为如下三类： INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。 LEFT JOIN
【详解】MySQL JOINS大总结
CodingLJ
12-01 366
以下图结合例子，自己敲一遍。 SQL语句的JOIN连接在开发中非常常用。 先看下面这张图，包括了内连接inner join，左连接left join，右连接 right join等。 以下两个表为例子，一个是tbl_emp，一个是tbl_dept。 CREATE TABLE `tbl_emp`( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `deptId` int(11) DEFAULT..
mysql的7种join(图码并茂,清晰易懂!)
weixin_46273997的博客
01-22 2万+
mysql的7种join1.总括2.具体实现(1)内连接(inner join 可简写为join)(2)左外连接(3)右外连接(4)全外连接 1.总括 mysql种共有7种join( 但实际上只有4种主要的:左外连接,右外连接,内连接,全外连接` 以下7种不过在此基础上有一些变动: 2.具体实现 (1)内连接(inner join 可简写为join) 从图可知:就是交集,也就是两张表的共同数据; 用sql语句表示为: select * from a [inner] join b on a.key = b
MySQL的两张表的七种Join查询
heyianliunian的博客
11-02 7498
sql示例：利用union去重将上面的第四、第五种两条sql中间用union连接即可完成；想掌握最基础的三个:left join、right join、inner join。left join 特点是保留A表所有字段，如果没有匹配到连接条件则用null填充。我们都知道数据库在进行表连接即（join）操作的时候，是进行笛卡尔积操作。（不考虑数据真实性）假设如上A、B两张表进行笛卡尔积操作结果就是如下。A表两行记录，B表两行记录。B表：主键id、部门名、部门主管。假设有A表和B表两张表。SQL的语法格式如下。
MySQL SQL 多个Join on(表连接) 和Where间的执行顺序（nest loop join机制）
点滴之积
11-05 4万+
这篇博客写的不好，一些内容有误，最新的可以看 https://blog.csdn.net/qq_27529917/article/details/87904179， 里面的应该是值得看的
OJ练习第174题——组合两个表
weixin_45662626的博客
09-16 112
编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。 以 任意顺序 返回结果表。 结果格式如下所示。
MySQL基础——多表查询（各种join连接详解）
热门推荐
Gorden的博客
04-28 5万+
Mysql 多表查询详解 目录 Mysql 多表查询详解 一.前言 二.下面以实例进行分析 三 注意事项 一.前言 上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1.1多表连接有哪些分类? 1.2针对这些分类有哪些连接方法? 1.3这些连接方法分别作用于哪些应用场景? 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 首先先列举本篇用到的分类(内连接，外连接，交叉连接...
MySQL常用的七种join查询
weixin_45149712的博客
06-10 275
目录 一、INNER JION 内连接 （ A ∩ B ） 二、LEFT JOIN 左外连接（ A 全有 ） 三、RIGHT JOIN 右外连接 （B 全有） ​四、FULL JOIN 全外连接（ A + B）  五、LEFT Excluding JOIN  （ A - B 即 A 表独有） 六、RIGHT Excluding JOIN ( B...
数据库系列之MySQL中Join语句优化问题
最新发布
牧羊人的方向
10-16 2444
最近使用MySQL 8.0.25版本时候遇到一个SQL问题，两张表做等值Join操作执行很慢，当对Join连接字段添加索引优化后，执行效率反而变得更差，其中的原因值得分析。因此本文介绍下MySQL中常见的Join算法，并对比使用不同Join算法时候的性能情况。
mysql的多表查询
08-30
此外，MySQL还提供了7种SQL JOINS用于实现多表查询。这些JOIN操作包括内连接(INNER JOIN)，左连接(LEFT JOIN)，右连接(RIGHT JOIN)，全连接(FULL JOIN)，左外连接(LEFT OUTER JOIN)，右外连接(RIGHT OUTER JOIN)和...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

qiuqiuyaq

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值