位置：首页-资讯-数据库

MySQL进阶查询、聚合查询和联合查询

2023-04-12 09:24

短信预约 -IT技能 免费直播动态提醒

1. 前言

文章主要围绕着以下三个问题:

group by的作用
where与having的区别
表的连接分为哪些,分别是什么作用

2. 表的设计

在创建数据表时,我们通常时根据需求找到"实体", 梳理"实体"之间的关系,从而进行创建.
"实体"之间可能会有以下几种关系:1.没关系 2.一对一 3.一对多 4.多对多.
没关系应该是最好理解的,就是单独的一张表,并不涉及到其它的表.

2.1 一对一

一对一的关系在生活中是很常见的,例如每个学生都有属于自己的学号,每个学号就只对应一个学生. 类似于这样的情况,就是一对一的关系.
此时就可以创建两张表,一个是学生表,另一个是学号表. 学生表里的学号就可以和学号表中的学号关联起来.

2.2 一对多

学生在学校上课时,会有一个班级. 但是一个班级可以有多个学生. 这就是一对多的关系.

2.3 多对多

举个例子,我们在学习课程时,可以选择多门课程进行学习,而课程也可以被多个学生进行选择. 这就是多对多的关系.
多对多的关系,在创建表时,可以使用"关联表" 将两个实体联系起来.

如果在设计数据库表时,场景很复杂,可以使用ER图帮助我们更好的创建数据库表.

3.将查询结果放到另一个表中

insert into 表2 select 字段 from 表1;
# 表2 是要存放查询结果的表
# 表1 是要查询的表
# 查询结果的列要和表2的列相匹配!
# 也可以将查询结果存放在表2的指定列中

示例:

下面这个"student1"这个表中,有三条数据.

在这里插入图片描述

接下来我们把查询结果放到一张新的 "student2"这个表中

在这里插入图片描述

4. 聚合查询

聚合查询可以进行"行"与"行"之间的运算

4.1 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总数
AVG([DISTINCT] expr)	返回查询到的数据的平均值
MAX([DISTINCT] expr)	返回查询到的数据的最大值
MIN([DISTINCT] expr)	返回查询到的数据的最小值

count函数可以对行进行计算,也可以对列进行计算.

示例:

在这里插入图片描述

这里有一张学生表,里面有学生的id,姓名,语数英三科的成绩.

在这里插入图片描述

sum,avg,max和min 都是只对数字生效,如果不是数字则没有意义

sum函数的用法和count函数类似. 不过只能计算"列",如果这一列存在"null",则不会参与运算
avg,max,min的用法和 sum 类似,就不一一介绍了

4.2 GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

例如:

在这里插入图片描述

在我的学生表中有这样一些数据.现在我要对语文成绩进行分组
使用group by之后,我们可以看到这已经以语文成绩进行分组了

在这里插入图片描述

成绩为57的有一个,69的有两个,82的有三个

需要注意的是:在进行查询分组的时候,只有分组的这一列,可以查询,其它列必须搭配聚合函数来进行查询

4.3 HAVING

分组查询还可以指定条件,这里的只当条件可以分组之前指定还可以分组之后指定 分组前进行筛选使用的是where 分组后进行筛选使用的则是having SQL 中增加 HAVING 子句原因就是因为WHERE 关键字无法与合计函数一起使用。

去除id=1的语文成绩后进行分组

在这里插入图片描述

分组之后筛选出语文成绩>60分的学生数量

在这里插入图片描述

where 关键字虽然无法与合计函数一起使用,但是可以和having一起使用

5. 联合查询(多表查询)

联合查询一般是在多表之间建立连接后查询的过程.其实就是计算"笛卡尔积"的过程
但是当表很大的时候,如果进行联合查询,效率就会特别低.因为"笛卡尔积"就是简单的排列组合,有些数据是"合理"的,有些数据是"不合理"的.所以我们就要把"有效"的数据筛选出来.因此联合查询通常需要加连接条件和其它筛选条件

5.1 内连接

内连接得到的是两张表中都存在的数据
两种写法:

select 字段 from 表1,表2;
select 字段 from 表1 join 表2 on 条件;

举个例子:

学生表:

在这里插入图片描述

班级表:

在这里插入图片描述

要求查询"王五"的班级的名字.
这就涉及到了两张表,就需要使用联合(多表)查询.

1.首先进行笛卡尔积

在这里插入图片描述

其实就是学生表和班级表进行排列组合,里面有很多无效的数据.

2. 添加连接条件

在这里插入图片描述

注意这里的条件写法,应该是表名.字段名,因为涉及到多个表,多个表中的字段名可能相同,因此需要使用 表名.字段 表明是哪个表中的字段.当然如果这个列名是唯一的,也可以不加表名.

在这里插入图片描述

刚才的结果还是太多了,我们也可以加上指定列进行查询,同样需要使用表名.字段的形式进行查询

使用join on也可以实现相同的效果,同样是刚才的例子
使用join on的方式来完成

1.首先进行笛卡尔积

在这里插入图片描述

2. 添加条件

在这里插入图片描述

直接写join或者inner join就是内连接
join on不仅可以实现内连接,还可以实现外连接

5.2 外连接

外连接分为左外连接(left join)和右外连接(right join)

还是刚才的学生表:

在这里插入图片描述

但班级表中多有两条数据

在这里插入图片描述

进行笛卡尔积后得到的结果

在这里插入图片描述

这是内连接得到的结果:

在这里插入图片描述

这个是进行右连接得到的结果

在这里插入图片描述

右连接会把右侧表中的数据都获取到,即使左边的值是NULL,也会显示出来
左连接也是类似,会把左侧表中的数据都获取到,即使右边的值是NULL,也会显示出来.

如果两张表中的数据,在对方表中都有,那么此时内外连接是没有区别的,如果两张表中的数据只有一部分在对方的表中,内连接就是获取两张表的"交集",如果是外连接,那么获取到的值就是一侧表的全部记录.

还有一种连接是"全外连接",但是在mysql中并不支持

5.3 自连接

自连接就是和自己进行笛卡尔积
在条件查询中,只是"列"和"列"之间的比较,但是有的地方需要用的 "行"和"行"之间的比较,就需要使用自连接,将"行"转为"列"再进行比较

例如这里有一张成绩表

在这里插入图片描述

如果要查询数学成绩比语文成绩高的同学的名字,就需要使用自连接.因为如果进行比较,那么就是"行"和"行"进行的比较

在这里插入图片描述

可以看到,如果是直接进行连接,那么是会报错的.
Not unique table/alias: 'grades': 这句话告诉我们不是唯一的表,但是可以起别名

在这里插入图片描述

通过起别名的方式,成功进行自连接
加上连接条件,先筛选出一部分记录,此时我们可以看到语文成绩和数学成绩就在两列了

在这里插入图片描述

将条件补全,就可以得到我们想要的结果了

在这里插入图片描述

5.4 子查询

子查询本质就是将多个查询语组合成一个SQL语句,例如在查询得到的临时表上再次进行查询

例如:在班级表中,找到与"张三"班级相同的同学

在这里插入图片描述

查到"张三"的班级id这个想必大家都会

在这里插入图片描述

我们要将得到的这个结果继续参与查询:

在这里插入图片描述

此时就得到"李四"同学的这条记录了,因为这里的班级id就只有一个,所以后面使用的是 = ,但是如果这个的临时表数据有多条,就可以使用 in 来完成

5.5 合并查询

合并查询是把两个查询的结果集合合并到一起,使用的是union 和union all 这两个关键字

union: 如果有重复的数据,就会去重union all: 如果有重复的数据,则不会去重

还是刚才的分数表

在这里插入图片描述

如果我们要查询数学成绩>90和英语成绩<60的人的姓名,就可以使用合并查询

在这里插入图片描述

6. 总结

在SQL语句中,查询数据的操作与其它操作语句相比还是有一些难度的,主要涉及到一些多表查询等操作.对于里面涉及到的一些关键字,连接类型要熟练掌握

以上就是MySQL进阶查询、聚合查询和联合查询的详细内容，更多关于MySQL进阶、聚合、联合查询的资料请关注我们其它相关文章！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL进阶查询 MySQL聚合查询 MySQL联合查询

阅读原文内容投诉

MySQL进阶查询、聚合查询和联合查询

下载Word文档到电脑，方便收藏和打印～

下载Word文档

猜你喜欢

【MySQL】进阶查询-聚合查询和联合查询

文章目录 1. 前言2. 表的设计2.1 一对一2.2 一对多2.3 多对多 3.将查询结果放到另一个表中4. 聚合查询4.1 聚合函数4.2 GROUP BY4.3 HAVING 5. 联合查询(多表查询)5.1 内连接5

2023-08-17

MySQL进阶查询、聚合查询和联合查询

目录1. 前言2. 表的设计2.1 一对一2.2 一对多2.3 多对多3.将查询结果放到另一个表中4. 聚合查询4.2 GROUP BY4.3 HAVING5. 联android合查询(多表查询)5.1 内连接5.2 外连接5.3 自连接5

2023-04-12

MySQL --- 聚合查询和联合查询

2023-10-21

MYSQL聚合查询、分组查询、联合查询举例详解

目录聚合查询聚合函数count()sum()avg()max()和min()总结分组查询group by 子句having 子句联合查询笛卡尔积内连接外连接自连接子查询单行子查询多行子查询from子句使用子查询合并查询总结聚合查询聚合查

2024-09-23

MySQL 数据库聚合查询和联合查询操作

2024-04-02

【MySQL】聚合查询 + 多表联合查询(重点)

2023-09-29

MySQL中的聚合查询和联合查询怎么实现

这篇文章主要介绍“MySQL中的聚合查询和联合查询怎么实现”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL中的聚合查询和联合查询怎么实现”文章能帮助大家解决问题。一、聚合查询（行与行之间的计

2023-07-05

MySQL中的聚合查询和联合查询操作代码

目录一、聚合查询（行编程与行之间的计算）1.常见的聚合函数有：2.group by3.having二、联合查询（多表查询）1.进行联合查询的步骤：2.内连接（from，join on）3.外连接（left/right join on）4.自

2023-03-20

MySQL聚合查询与联合查询操作实例

2024-04-02

MySQL数据库聚合查询和联合查询怎么实现

这篇文章主要介绍“MySQL数据库聚合查询和联合查询怎么实现”，在日常操作中，相信很多人在MySQL数据库聚合查询和联合查询怎么实现问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL数据库聚合查询和联合

2023-06-21

MySQL联合查询（多表查询）

2023-09-14

MySQL聚合查询与联合查询操作的示例分析

这篇文章主要为大家展示了“MySQL聚合查询与联合查询操作的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL聚合查询与联合查询操作的示例分析”这篇文章吧。一. 聚合查询1.聚合函

2023-06-29

【MySQL】详解聚合查询、多表查询

2023-10-07

MySQL 数据库约束、聚合查询和联合查询使用案例

目录数据库约束约束类型使用案例聚合查询聚合函数使用案例GROUP BY子句HAVING 联合查询内连接外连接自连接联合查询使用案例子查询子查询案例合并查询合并查询案例数据库约束约束类型数据库的约束类型主要包括以下几种：主键约束（P

2024-08-27

mysql 联合查询

2023-08-31

MySQL 中，子查询能否替代联合查询？(MySQL子查询能替代联合查询吗)

在 MySQL 数据库中，子查询和联合查询都是非常常用的查询技术，它们各自具有独特的优势和适用场景。那么，子查询能否替代联合查询呢？这是一个值得探讨的问题。

一、子查询的基本概念

子查询是在一个查询语句中嵌套另一个查询语句。外层查询可以基于子查询的结果进行进一步的操作，如筛选、排序等。子查询通常用于获取中间结果或满足特定条件的数据集。

例如，以下是一个简单的子查询示例：

SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

在这个例子中，子查询 (SELECT column3 FROM table2 WHERE condition) 首先在 table2 中查找满足条件的 column3 值，然后外层查询在 table1 中筛选出 column2 等于子查询结果的行，并返回 column1 的值。

二、联合查询的基本概念

联合查询用于将多个查询的结果合并成一个结果集。它通过 UNION 或 UNION ALL 操作符将多个查询的结果组合在一起。联合查询通常用于合并具有相似结构的数据集或在不同的表中查找相关的数据。

例如，以下是一个简单的联合查询示例：

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

在这个例子中，两个查询分别从 table1 和 table2 中选择 column1 的值，然后通过 UNION 操作符将它们合并成一个结果集，去除重复的行。

三、子查询与联合查询的适用场景

子查询的适用场景
- 当需要在查询中使用另一个查询的结果作为条件时，子查询非常有用。例如，在筛选数据时，可以使用子查询来获取特定条件的中间结果，并在主查询中使用该结果进行进一步的筛选。
- 子查询可以用于获取聚合函数的结果或计算中间值。例如，可以使用子查询计算某个表中的最大值或平均值，并在主查询中使用该值进行条件筛选。
- 子查询可以用于在不同的表之间进行关联查询。通过在子查询中获取关联表的相关数据，然后在主查询中使用该数据进行进一步的操作。
联合查询的适用场景
- 当需要合并多个具有相似结构的数据集时，联合查询是一个很好的选择。例如，可以使用联合查询将多个表中的相同字段合并成一个结果集，以便进行统一的分析和处理。
- 联合查询可以用于在不同的数据库或数据源之间进行数据集成。通过将多个数据源的查询结果合并成一个结果集，可以实现数据的整合和共享。
- 联合查询可以用于在查询结果中去除重复的行。通过使用 UNION ALL 操作符，可以保留所有的行，包括重复的行；而使用 UNION 操作符，则会去除重复的行。

四、子查询能否替代联合查询

子查询和联合查询在某些情况下可以相互替代，但在其他情况下，它们各自具有不可替代的优势。

子查询可以替代简单的联合查询
- 对于简单的联合查询，即只需要合并两个具有相似结构的数据集且不需要去除重复行的情况，子查询可以通过嵌套查询来实现相同的功能。
- 例如，以下两个查询是等价的：
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
```
SELECT column1 FROM (SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2) AS temp;
```
  在这个例子中，第二个查询使用子查询将两个表的查询结果合并成一个临时表，然后在主查询中选择 column1 的值。
联合查询可以替代简单的子查询
- 对于简单的子查询，即只需要在一个查询中使用另一个查询的结果作为条件的情况，联合查询可以通过连接操作来实现相同的功能。
- 例如，以下两个查询是等价的：
```
SELECT column1
FROM table1
WHERE column2 = (SELECT column3 FROM table2 WHERE condition);
```
```
SELECT t1.column1
FROM table1 t1
JOIN (SELECT column3 FROM table2 WHERE condition) t2 ON t1.column2 = t2.column3;
```
  在这个例子中，第二个查询使用连接操作将 table1 和一个子查询的结果连接起来，然后在主查询中选择 column1 的值。
子查询和联合查询在复杂查询中的应用
- 在复杂的查询中，子查询和联合查询都有各自的优势。子查询可以更灵活地处理嵌套查询和中间结果，而联合查询可以更方便地合并多个数据集。
- 例如，以下是一个使用子查询和联合查询的复杂查询示例：
```
SELECT column1
FROM table1
WHERE column2 IN (SELECT column3 FROM table2 WHERE condition1)
OR column2 IN (SELECT column4 FROM table3 WHERE condition2);
```
```
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition1 AND table1.column2 = table2.column3)
OR EXISTS (SELECT 1 FROM table3 WHERE condition2 AND table1.column2 = table3.column4);
```
  在这个例子中，第一个查询使用子查询在 table2 和 table3 中查找满足条件的 column3 和 column4 值，并将结果作为 IN 条件用于在 table1 中筛选数据；第二个查询使用存在量词 EXISTS 在 table2 和 table3 中查找满足条件的行，并通过连接操作将结果与 table1 关联起来。