【测试开发学习历程】MySQL分组查询与子查询 + MySQL表的联结操作

本文主要是介绍【测试开发学习历程】MySQL分组查询与子查询 + MySQL表的联结操作,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

目录

1 MySQL分组查询与子查询

1.1 数据分组查询

1.2 过滤分组

1.3 分组结果排序

1.4 select语句中子句的执行顺序

1.5 子查询

2 MySQL表的联结操作

2.1 关系表

2.2 表联结

2.3 笛卡尔积

2.4 内部联结

2.5 外联结

2.6 自联结

2.7 组合查询


1 MySQL分组查询与子查询

1.1 数据分组查询

分组允许把数据表中的数据按照某个字段,分为多个组,字段值相同的为一组。分组是为了便于对每个组进行聚集计算;

分组是在select语句的group by子句中建立;

注意:group by只是创建分组,但并不保证分组里面的数据的排列顺序,需要使用order by子句对分组里面的数据进行排序。

使用group by语句对select查询的结果进行分组,以便统计:

select语句指定了两个列,vend_id为产品供应商的ID,prod_num为计算字段(使用count()函数);

group by子句按vend_id分组数据,这导致对每个vend_id而不是整个表计算prod_num一次,从而按照vend_id为单位,来计算每个组的总和;

注意:group by子句必须出现在where子句之后,order by子句在group by之后。where XXX group by YYY order by ZZZ

1.2 过滤分组

除了能用group by分组数据外,MySQL还允许对分组指定条件,规定包含哪些分组,排除哪些分组;

MySQL使用having子句来完成该操作;

where子句过滤指定的行,having子句过滤指定的分组。

如,过滤总数大于等于2的分组:

where与having同时使用,列出具有2个(含)以上,价格为10(含)以上的产品的供应商:

1.3 分组结果排序

  • select后面只能查看group by子句后有的列;
  • group by可以根据多个列进行分组,多个列分组即对多个列的组合值相同的进行分组;

1.4 select语句中子句的执行顺序

select语句中使用的子句必须按照一定的次序,下图列出来各个子句在select语句中出现的次序:

子句

说明

是否必须使用

SELECT

要返回的列或表达式

FROM

从中检索数据的表

仅在从表选择数据时使用

WHERE

行级过滤

GROUP BY

分组说明

仅在按组计算聚集时使用

HAVING

组级过滤

ORDER BY

输出排序顺序

LIMIT

要检索的行数

1.5 子查询

  • 前面我们讲的所有查询都是在单个数据库表中执行;
  • SQL还允许建立子查询,即嵌套在其他查询中的查询;
  • MySQL从4.1版本开始支持子查询,要使用子查询必须使用MySQL4.1或更高版本。

订单存储在两个表中:orders保存订单号,客户ID、订单日期;

各订单的物品信息保存在orderitems表中,order表不保存客户信息,它只保存客户的ID。实际的客户信息存储在customers表中;

如果需要列出订购物品编号TNT2的所有客户姓名,可以采用下列步骤:

  1. 查询包含物品TNT2的所有订单的编号;
  2. 查找前一步骤列出的订单编号的所有客户的ID;
  3. 查找前一步返回的所有客户ID的客户姓名;

上述每个步骤都可以单独作为一个查询来执行,可以把一条select语句返回的结果用于另一条select语句的where子句中。

方法一:用多个查询语句实现

(1)查找包含物品TNT2的所有订单的编号

(2)查找前一步骤列出的订单编号的所有客户的ID

(3)查找前一步列出的客户ID的客户姓名及其他信息

方法二:用子查询把多个查询语句组合起来 

使用子查询语句完成

在Where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。比子查询更优的查询方法是联结。

2 MySQL表的联结操作

2.1 关系表

为什么需要关系表?

假设有一个包含产品目录的数据库表,其中每种类别的产品占一行。对于每种物品要存储的信息包括产品描述和价格,以及生成该产品的供应商信息。

现在,假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如:供应商名、地址、联系方式等)呢?将这些数据与产品信息分开存储还是存储在同一张表里呢?通常的原则是分开存储,理由:

  • 因为同一供应商生产的每个产品的供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空间
  • 如果供应商信息改变(如:供应商搬家或电话号码变动,只需要改动一次即可)
  • 如果有重复数据(即每种产品都存储供应商信息),很难保证每次输入该数据的方式都相同,不一致的数据在报表中很难利用。

在这个例子中,可建立两个表,一个存储供应商信息(vendors表),另一个存储产品信息(products表)。

关系数据可以有效地存储和方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好。

2.2 表联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结表;

数据是存储在关系表中的,关系表的设计原则是保证把信息分解成多个表,一类数据一个表,各表通过某些常用的值互相关联。

2.3 笛卡尔积

笛卡尔积是由没有联结条件的表关系返回的结果,检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

2.4 内部联结

创建链接:

where子句:

  • 在一条SELECT语句中联结几个表时,相应的关系是在运行中构造的。在数据库表的定义中并不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情;
  • 在联结两个表时,你实际上做得是将第一个表中的每一行与第二个表中的每一行配对;
  • Where子句作为过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。没有Where子句,第一个表中的每个行将与第二个表中的每个行配对,而不管逻辑上是否可以配在一起。

上述语句中的select与前面的select语句相同,但from子句不同,这里两个表之间的关系是from子句的组成部分,以INNER JOIN指定。在使用这种语法时,联结条件用特定的ON子句而不是where子句。传递给ON的实际条件与传递给where的相同。

2.5 外联结

许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行,例如,可能需要使用联结来完成以下工作:

  • 统计每个客户下了哪些订单,包括那些至今尚未下订单的客户;
  • 列出所有产品以及订购数量,包括没有人订购的产品;

在上述的例子中,联结包含了那些在相关表中没有关联行的行,这种类型的联结称为外部联结;

外部联结分为左外部联结和右外部联结;

左外部联结:以左边表为基准,按照过滤条件查找右边表的记录,如果匹配到,那么就组合成一行,并显示结果,如果没有匹配到,那么只显示左边表的字段,右边表中不存在的字段用空值NULL来表示。

右外部联结:作用和左联结刚好相反,以右边表为基准,去匹配左边的表,如果左边表字段为空,那么就用空值NULL来表示。 

左右外连接实例:

2.6 自联结

自联结为在同一个表中做联结操作;

例:假如发现某物品(其ID为DTNTR)存在问题,因此想知道生产该物品的供应商的其他产品是否也存在问题。此查询要求首先找到生产ID是DTNTR的物品的供应商,然后找出这个供应商生产的其他。

效率高于子查询

连结的注意事项:

  • 注意所使用的联结类型,一般我们使用内部联结,但使用外部联结也是有效的;
  • 保证使用正确的联结条件,否则将返回不正确的数据;
  • 应该总是指定联结条件,否则会得到笛卡尔积;
  • 在一个联结中可以包含多个表,甚至对每个联结可以采用不同的联结类型,虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结,这样使故障排除更为简单。

2.7 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句,MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回;

这些组合查询通常称为并(union)或符合查询(compound query);

可用UNION操作符来组合数条SQL查询,利用UNION可以将多条select语句组合起来,将它们的结果组合成单个结果集。

上面的例子中,UNION从查询结果集中自动去除了重复的行,这是UNION的默认行为,但是如果需要,可以改变它;

如果要想返回所有匹配行,可使用UNION ALL来代替UNION关键字

在用UNION组合查询时,只能使用一条order by子句,它必须出现在最后一条SELECT语句之后;

对于结果集,不存在用一种方式排列一部分,而又用另一种方式排列另一部分的情况,因此不运行使用多条order by子句;

规则: 

  • UNION必须由两条或者以上的语句组成,语句之间用关键字UNION分隔(如果组合4条SELECT语句,将要使用3个UNION关键字);
  • UNION中的每个查询必须包含相同数量的列、表达式或聚集函数;
  • 列数据类型必须兼容。

 

这篇关于【测试开发学习历程】MySQL分组查询与子查询 + MySQL表的联结操作的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/838744

相关文章

MySQL多实例管理如何在一台主机上运行多个mysql

《MySQL多实例管理如何在一台主机上运行多个mysql》文章详解了在Linux主机上通过二进制方式安装MySQL多实例的步骤,涵盖端口配置、数据目录准备、初始化与启动流程,以及排错方法,适用于构建读... 目录一、什么是mysql多实例二、二进制方式安装MySQL1.获取二进制代码包2.安装基础依赖3.清

详解MySQL中JSON数据类型用法及与传统JSON字符串对比

《详解MySQL中JSON数据类型用法及与传统JSON字符串对比》MySQL从5.7版本开始引入了JSON数据类型,专门用于存储JSON格式的数据,本文将为大家简单介绍一下MySQL中JSON数据类型... 目录前言基本用法jsON数据类型 vs 传统JSON字符串1. 存储方式2. 查询方式对比3. 索引

在MySQL中实现冷热数据分离的方法及使用场景底层原理解析

《在MySQL中实现冷热数据分离的方法及使用场景底层原理解析》MySQL冷热数据分离通过分表/分区策略、数据归档和索引优化,将频繁访问的热数据与冷数据分开存储,提升查询效率并降低存储成本,适用于高并发... 目录实现冷热数据分离1. 分表策略2. 使用分区表3. 数据归档与迁移在mysql中实现冷热数据分

SpringBoot集成MyBatis实现SQL拦截器的实战指南

《SpringBoot集成MyBatis实现SQL拦截器的实战指南》这篇文章主要为大家详细介绍了SpringBoot集成MyBatis实现SQL拦截器的相关知识,文中的示例代码讲解详细,有需要的小伙伴... 目录一、为什么需要SQL拦截器?二、MyBATis拦截器基础2.1 核心接口:Interceptor

Python使用openpyxl读取Excel的操作详解

《Python使用openpyxl读取Excel的操作详解》本文介绍了使用Python的openpyxl库进行Excel文件的创建、读写、数据操作、工作簿与工作表管理,包括创建工作簿、加载工作簿、操作... 目录1 概述1.1 图示1.2 安装第三方库2 工作簿 workbook2.1 创建:Workboo

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

基于Python开发一个图像水印批量添加工具

《基于Python开发一个图像水印批量添加工具》在当今数字化内容爆炸式增长的时代,图像版权保护已成为创作者和企业的核心需求,本方案将详细介绍一个基于PythonPIL库的工业级图像水印解决方案,有需要... 目录一、系统架构设计1.1 整体处理流程1.2 类结构设计(扩展版本)二、核心算法深入解析2.1 自

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成