【测试开发学习历程】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

相关文章

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Python基于wxPython和FFmpeg开发一个视频标签工具

《Python基于wxPython和FFmpeg开发一个视频标签工具》在当今数字媒体时代,视频内容的管理和标记变得越来越重要,无论是研究人员需要对实验视频进行时间点标记,还是个人用户希望对家庭视频进行... 目录引言1. 应用概述2. 技术栈分析2.1 核心库和模块2.2 wxpython作为GUI选择的优

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

C# WinForms存储过程操作数据库的实例讲解

《C#WinForms存储过程操作数据库的实例讲解》:本文主要介绍C#WinForms存储过程操作数据库的实例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、存储过程基础二、C# 调用流程1. 数据库连接配置2. 执行存储过程(增删改)3. 查询数据三、事务处

Java使用Curator进行ZooKeeper操作的详细教程

《Java使用Curator进行ZooKeeper操作的详细教程》ApacheCurator是一个基于ZooKeeper的Java客户端库,它极大地简化了使用ZooKeeper的开发工作,在分布式系统... 目录1、简述2、核心功能2.1 CuratorFramework2.2 Recipes3、示例实践3

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误