【MySQL 09】复合查询 (带思维导图)

2024-08-21 08:44

本文主要是介绍【MySQL 09】复合查询 (带思维导图),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 🌈 一、准备工作
  • 🌈 二、多表查询
    • ⭐ 1. 多表笛卡尔积
    • ⭐ 2. 多表查询示例
  • 🌈 三、自连接
  • 🌈 四、子查询
    • ⭐ 1. 标量子查询
    • ⭐ 2. 多行子查询 (需要插入其他博客的链接)
    • ⭐ 3. 多列子查询 (需要插入其他博客的链接)
    • ⭐ 4. 在 from 子句中使用子查询 (一切皆表)
  • 🌈 五、合并查询
    • ⭐ 1. union
    • ⭐ 2. union all

在这里插入图片描述

🌈 一、准备工作

  • 当前有三张表,分别是:部门表 dept,员工表 emp,薪资等级表 salgrade,之后的查询工作主要依赖于这三张表进行。
    • 这三张表各自包含的字段和每个字段的内容如下。
  1. 员工表 emp:员工编号 (empno)、员工姓名 (ename)、员工职位 (job)、员工领导的编号 (mgr)、雇佣时间 (hiredate)、月薪 (sal)、奖金 (comm)、部门编号 (deptno)。

image-20240817104531316

image-20240817104611038

  1. 部门表 dept:部门编号 (deptno)、部门名称 (dname)、部门所在地 (loc)。

image-20240817104818532

image-20240817104856942

  1. 薪资登记表 salgrade:等级 (grade)、此等级最低工资 (losal)、此等级最高工资 (hisal)。

image-20240817105133021

image-20240817105108090

🌈 二、多表查询

select * from1 ,2, ..., 表n;
  • 在实际开发中实际开发中数据通常来自不同的表,需要对多张表关联起来进行查询,这种查询被称为多表查询

  • 在进行多表查询时,只要将多张表的表名用逗号隔开依次放到 from 子句之后即可,MySQL 会对这多张表取笛卡尔积,用取到的笛卡尔积来作为多表查询的数据。

  • 多表查询的本质:多表查询就是对多张表取笛卡尔积,用笛卡尔积的形式将多张表合并成一张表,然后对这一张表进行单表查询

⭐ 1. 多表笛卡尔积

  • 多表笛卡尔积就是,一张表中的每一条记录都会和另一张表中的所有纪录进行组合。

    • 例:A = {a, b, c},B = {1, 2, 3},A 与 B 的笛卡尔积为:a1 a2 a3,b1 b2 b3,c1 c2 c3。
  • 在多表查询时,需要消除无效的笛卡尔积

select * from1,2 where1外键值 =2主键值;

举个例子

  • 对员工表 emp 和部门表 dept 表进行全列的多表查询,由于使用多表查询会产生笛卡尔积,对这两张表进行联合查询的结果如下。
    • 员工表 emp 和部门表 dept 合并形成了一张新的大表。
    • 员工表 emp 中的每一条记录都会和部门表 dept 中的所有记录进行组合从而产生很多无效的查询数据。

image-20240817112300675

对笛卡尔积进行过滤

  • 在进行多表查询时,将 emp 和 dept 这两张表合并成了一张表,可使用 where 条件过滤笛卡尔积对这一张表进行查询。

    • 例:由于两张表都有 deptno 这个字段,那么只需要筛选出与 emp 表中的 deptno 所对应的部门表的 deptno 即可。

    • 假设一个员工的所在部门是 10 号部门,那么他和其他的部门进行组合就没有任何意义,需要过滤掉这些无意义的笛卡尔积。

image-20240817113259575

⭐ 2. 多表查询示例

  • 为多表查询准备的案例如下:
  1. 显示雇员名、雇员工资以及所在部门的名字。
  2. 显示部门号为10的部门名,员工名和工资。
  3. 显示各个员工的姓名,工资,及工资级别。

1. 显示雇员名、雇员工资以及所在部门的名字

  • 部门名 dname 只存在于部门表 dept 中,而员工名 ename 和员工工资 sal 只存在于员工表 emp 中。需要同时对员工表 emp 和部门表 dept 进行多表查询,在 where 子句中指明筛选条件为 emp.deptno = dept.deptno。

image-20240817115439260

2. 显示部门号为10的部门名,员工名和工资

  • 在上一题的基础上,对 where 子句添加一个条件:并且部门号为 10 的记录。
    • 该筛选条件可以使用员工表 emp 中的部门编号,也可以使用部门表中的部门编号。

image-20240817115250427

3. 显示各个员工的姓名,工资,及工资级别

  • 员工名 ename 和员工工资 sal 只存在于员工表 emp 中,而工资级别 grade 只存在于工资等级表 salgrade 中。需要同时对员工表 emp 和工资等级表 salgrade 进行多表查询,在 where 子句中指定筛选条件为员工的工资在 losal 和 hisal 之间。

image-20240817120517405

🌈 三、自连接

select 字段列表 from 表A as 别名A, 表A as 别名B where 条件 ... ;
  • 既然对不同的表取笛卡尔积,同样也能对同一张表取笛卡尔积,对同一张表进行多表 (连接) 查询被称之为自连接
    • 在自连接中,必须对掉取别名
  • 如果一张表中的某个字段能够将表中的多条记录关联起来,则能通过自连接将表中与该字段关联的记录组合起来。

案例:显示员工 FORD 的上级领导的编号和姓名

  • 即使是员工的领导,甚至是公司老板,都会被记录在员工表 emp 中,因此 FORD 的上级领导的信息也需要在员工表 emp 中查询。
  1. 使用子查询解决:先对员工表进行查询得到 FORD 的领导的员工编号 mgr,再根据领导的员工编号从员工表中找到 FORD 领导的信息。

image-20240817171120129

  1. 使用自连接解决:emp 表中的领导编号 mgr 字段和员工编号字段 empno 可以关联起来。

image-20240817172010289

  • 因此可以将 emp 通过重命名的方式变成两张表,再对这两张表通过自连接的方式实现多表查询,将这两张表合并成一张未消除笛卡尔积的表。

    • 之后的操作就是消除这张表的笛卡尔积,在 where 子句中指定筛选条件为员工的领导编号 mgr 等于领导的员工编号 empno。

image-20240817172448089

🌈 四、子查询

select 字段列表 from t1 where column1 = (select column1 from t2);
  • MySQL 支持在一条查询语句的内部再执行一条查询语句,先执行最内部的查询语句,执行完后外部的 sql 语句根据内部 sql 的查询结果进行查询。

    • 嵌套在另一条查询语句中的查询语句被称之为子查询
  • 根据子查询返回的结果可将子查询分为标量子查询多行子查询多列子查询表子查询

    • 标量子查询:子查询返回的结果是单个的值,为单行单列的数据。
    • 多行子查询:子查询返回的结果为多行数据,每行都只能有一列。
    • 多列子查询:子查询返回的结果为多列数据,每列都只能有一行。
    • 表子查询:子查询返回的结果是一张子表,是多行多列的数据。
  • 在进行子查询时,如果搞不清楚逻辑,可以先将所有的子查询都拆成一条查询语句,再进行嵌套查询。

⭐ 1. 标量子查询

  • 子查询返回的结果是是单个的值,即表中背单行单列所框住的某一个格子的值。

image-20240817175850746

案例:显示与 SMITH 同一部门的员工

  • 在子查询中找出 SMITH 所在的部门号 deptno (SMITH 的部门号是单个的值)。

image-20240817181708607

  • 在 where 子句中指定筛选条件为员工部门号 deptno 等于子查询返回的部门号。

image-20240817180233667

⭐ 2. 多行子查询 (需要插入其他博客的链接)

  • 子查询返回的结果为多行数据,每行都只能有一列。
  • 多行子查询的常用操作符:=、<>、in、not in (这些操作符的意思详情见/表的增删查改 (此处要插入对应博客的链接)/中的操作符介绍)。

1. 查询和 10 号部门的工作岗位相同的员工的名字岗位,工资,部门号,但是不包含 10 号部门的员工

  • 先找出 10 号部门都有哪些工作岗位,由于某些员工的岗位可能相同,因此在查询时还需要进行去重操作。

image-20240817182651817

  • 再将上述查询作为多行子查询,在 where 子句中使用 in 关键字,判断员工的工作岗位是行子查询得到的这 3 个岗位中的一个。由于还要将 10 号部门的员工给筛出来,因此同时还需要在 where 子句中指定筛选条件为 deptno != 10。

image-20240817183325670

2. 显示工资比 30 号部门的所有员工的工资高的员工的姓名、工资和部门号

  • 先找出 30 号部门所有的工资,在查询时最好对结果进行去重。

image-20240817185228296

  • 再将上述查询语句作为多行子查询,在 where 子句中使用 all 关键字,判断员工的工资是否 > 行子查询得到的所有工资。

image-20240817185559750

  • 这道题也能看作找出员工表中比 30 号部门中的最高薪资高的员工的相关信息,可以使用标量子查询找出 30 号部门的最高薪资。

image-20240817190718425

3. 显示工资比 30 号部门的任意员工的工资高的员工的姓名、工资和部门号,包含 30 号部门的员工

  • 先找出 30 号部门所有的工资,在查询时最好对结果进行去重。
  • 再将上述查询语句最为多行子查询,在 where 子句中使用 any 关键字,只要工资高于行子查询得出的结果中的任意一个即可。

image-20240817191501973

  • 换个角度看,大于任意一个表示工资至少要大于 30 号部门的最低薪资才行,可以使用标量子查询找出 30 号部门的最低薪资。

image-20240817191828071

⭐ 3. 多列子查询 (需要插入其他博客的链接)

  • 子查询返回的结果为多列数据,每列都只能有一行。

  • 多列子查询的常用操作符:in、not in、any、some、all ( (这些操作符的意思详情见/表的增删查改 (此处要插入对应博客的链接)/中的操作符介绍)。

案例:显示和 SMITH 的部门和岗位完全相同的员工,但不包含 SMITH 本人

  • 先查询 SMITH 所在部门的部门编号 deptno 以及他的岗位 job。

image-20240817193542147

  • 将上述查询作为多列子查询,在 where 子句中,指定筛选条件为部门编号 deptno 和岗位 job 都等于多列子查询得到的部门号和岗位,并且员工的姓名 ename != SMITH。

image-20240817194013405

⭐ 4. 在 from 子句中使用子查询 (一切皆表)

  • 子查询不仅仅能在 where 中充当判断条件,也能在 from 子句中充当笛卡尔积
  • 支持这种做法的原因是,在 MySQL 中,一切皆表,因此也能将子查询查出来的结果当成一张临时表来看待。
  • 注意:在 from 子句中使用子查询时,必须为子查询得到的临时表取别名

1. 显示每个高于自己部门平均工资的员工的姓名、部门、工资和部门的平均工资

  • 先使用 group by 分组查询找出每个部门的平均工资,显示出来的信息就是所说的临时表。

image-20240817195336970

  • 将上述查询的结果作为临时表放在 from 子句中,再对员工表和临时表取笛卡尔积。在 where 子句中指定筛选条件为员工的部门号等于临时表中的部门号,并且员工的工资大于临时表中的平均工资。

image-20240817200121132

2. 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

  • 先使用 max 函数和 group by 分组查询找出每个部门的最高工资。

image-20240817201612442

  • 将上述查询结果作为临时表放在 from 子句中与 emp 作笛卡尔积,在 where 子句中指定筛选条件为员工的部门号等于临时表的部门号,并且员工的工资等于临时表的最高工资。

image-20240817201938732

3. 显示每个部门的信息(部门名,编号,地址)和人员数量

  1. 使用多表查询:对员工表 emp 和部门表取 dept 取笛卡尔积,在 where 子句中指定筛选条件为 emp.deptno = dept.deptno 筛出部门号相同的记录,在 group by 子句中指定按照部门号 deptno、部门名 dname 和部门地址 loc j进行分组,统计出每个部门的人数。

image-20240817204208167

  1. 使用子查询(推荐):
  • 在员工表 emp 中使用 group by 分组查询,找出每个部门的人员数量、部门编号。

image-20240817203226341

  • 将上述查询结果作为临时表放进 from 子句中,对部门表和临时表作笛卡尔积,将 where 子句的筛选条件指定为部门表的部门编号等于临时表的部门编号。

image-20240817205017354

  • 由于一切皆表的缘故,该方法其实也算是一种多表查询,只不过是用子查询的形式展现出来了而已。

🌈 五、合并查询

select 字段列表 from 表A ... union [all] select 字段列表 from 表B ... ;
  • MySQL 支持将多次查询的结果合并起来,形成一个新的查询结果集,可使用的操作符有 union 和 union all。
  • union 和 union all 都能获得多个查询结果的并集,区别是 union 会对合并后的并集进行去重,而 union all 则不会。
  • 注意:对于合并查询的多张表的列数必须保持一致,字段类型也需要保持一致。

⭐ 1. union

  • 该操作符用于取得两个结果集的并集。当使用该操作符时,会去掉结果集中的重复行。

案例:将工资大于 2500 或职位是 MANAGER 的人找出来

  • 找出工资 sal 大于 2500 的人。

image-20240817211350717

  • 找出职位 job 是 MANAGER 的人。

image-20240817211626713

  • 使用 union 关键字将上述两种查询结果进行去重合并(合并的前提是两个查询的字段数和字段类型要一致)。

image-20240817211727864

⭐ 2. union all

  • 该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。

案例:将工资大于 25000 或职位是 MANAGER 的人找出来

  • 前置操作就不演示了,直接使用 union 关键字将上述两种查询结果进行不去重合并

image-20240817211858910

这篇关于【MySQL 09】复合查询 (带思维导图)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1092678

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

活用c4d官方开发文档查询代码

当你问AI助手比如豆包,如何用python禁止掉xpresso标签时候,它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC