MySQL第六讲·where和having的异同?

2023-12-13 10:01:56

在这里插入图片描述

你好,我是安然无虞。

面试常考:where与having有什么不同?

我们在进行查询的时候,经常需要按照条件对查询结果进行筛选,这就要用到条件语句where和having了。

where是直接对表中的字段进行限定来筛选结果,having则需要跟分组关键字group by一起使用,通过对分组字段或分组计算函数进行限定来筛选结果。虽然它们都是对查询进行限定,却有着各自的特点和适用场景。很多时候,我们会遇到2个都可以用的情况。一旦用错,就容易出现执行效率低下、查询结果错误,甚至是查询无法运行的情况。

一个实际查询需求

超市经营者提出,要查单笔销售金额超过50元的商品。

我们来分析一下这个需求:需要查询出一个商品记录集,限定条件是单笔销售超过50元。这个时候,我们就需要用到where 和 having了。

这个问题的条件很明确,查询的结果也只有“商品”一个字段,看起来挺容易实现的。

假设我们有一个商品信息表demo.goodsmaster,里面有2种商品:书和笔。

mysql> select *
    -> from demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
|          1 | 0001    ||               ||      89.00 |
|          2 | 0002    ||               ||       5.00 |
+------------+---------+-----------+---------------+------+------------+
2 rows in set (0.00 sec)

同时我们还有一个商品销售明细表demo.transactiondetails,里面有4条销售记录:

mysql> select *
    -> from demo.transactiondetails;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |
+---------------+------------+----------+-------+------------+
4 rows in set (0.01 sec)

接下来我们分别用where和having尽心查询,看看它们各自是如何查询的,是否能够得到正确的结果。

首先用where关键字进行查询:

mysql> select distinct b.goodsname
-> from demo.transactiondetails as a
-> join demo.goodsmaster as b
-> on (a.itemnumber=b.itemnumber)
-> where a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
||
+-----------+
1 row in set (0.00 sec)

接着,使用having 关键字进行查询:

mysql> select b.goodsname
-> from demo.transactiondetails as a
-> join demo.goodsmaster as b
-> on (a.itemnumber = b.itemnumber)
-> group by b.goodsname
-> having max(a.salesvalue) > 50;
+-----------+
| goodsname |
+-----------+
||
+-----------+
1 row in set (0.00 sec)

可以看到两次查询的结果是一样的,那么这两种查询到底有什么区别,那个更好呢?要明白这个问题,首先我们需要先学习where和having的执行过程。

where

我们先来分析一下刚才使用 where 条件的查询语句,来看看 MySQL 是如何执行这个查询的。

首先,MySQL 从数据表 demo.transactiondetails 中抽取满足条件“a.salesvalue>50”的记录:

mysql> select *
    -> from demo.transactiondetails as a
    -> where a.salesvalue > 50;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
+---------------+------------+----------+-------+------------+
2 rows in set (0.00 sec)

为了获取到销售信息所对应的商品名称,我们需要通过公共字段“itemnumber”与数据表 demo.goodsmaster 进行关联,从 demo.goodsmaster 中获取商品名称:

mysql> select
    ->     a.*, b.goodsname
    -> from
    ->     demo.transactiondetails a
    -> join
    ->     demo.goodsmaster b 
    -> on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+---------------+------------+----------+-------+------------+-----------+
| transactionid | itemnumber | quantity | price | salesvalue | goodsname |
+---------------+------------+----------+-------+------------+-----------+
|             1 |          1 |    1.000 | 89.00 |      89.00 ||
|             2 |          1 |    2.000 | 89.00 |     178.00 ||
+---------------+------------+----------+-------+------------+-----------+
2 rows in set (0.00 sec)

这个时候,如果查询商品名称,就会出现两个重复的记录:

mysql> select
    ->     b.goodsname
    -> from
    ->     demo.transactiondetails as a
    -> join
    ->     demo.goodsmaster as b on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
||
||
+-----------+
2 rows in set (0.00 sec)

需要注意的是,为了消除重复的语句,这里我们需要用到一个关键字:DISTINCT,它的作用是返回唯一不同的值。比如,DISTINCT 字段 1,就表示返回所有字段 1 的不同的值。

下面我们尝试一下加上 DISTINCT 关键字的查询:

mysql> select
    ->     distinct(b.goodsname)  -- 返回唯一不同的值
    -> from
    ->     demo.transactiondetails as a
    ->         join
    ->     demo.goodsmaster as b on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
||
+-----------+
1 row in set (0.00 sec)

这样,我们就得到了需要的结果:单笔销售金额超过 50 元的商品就是“书”。

总之,WHERE 关键字的特点是,直接用表的字段对数据集进行筛选。如果需要通过关联查询从其他的表获取需要的信息,那么执行的时候,也是先通过 WHERE 条件进行筛选,用筛选后的比较小的数据集进行连接。这样一来,连接过程中占用的资源比较少,执行效率也比较高。

having

讲完了where,我们再说说having 是如何执行的。不过,在这之前,我们先来了解一下group by,因为having不能单独使用,必须要跟group by一起使用。

我们可以把group by 理解成对数据进行分组,方便我们对组内的数据进行统计计算。

举个小例子,来具体讲讲group by 如何使用,以及如何在分组里面进行统计计算。

假设现在有一组销售数据,我们需要从里面查询每天、每个收银员的销售数量和销售金额。我们通过以下的代码,来查看一下数据的内容:

mysql> select *
    -> from demo.transactionhead;
+---------------+------------------+------------+---------------------+
| transactionid | transactionno    | operatorid | transdate           |
+---------------+------------------+------------+---------------------+
|             1 | 0120201201000001 |          1 | 2020-12-10 00:00:00 |
|             2 | 0120201202000001 |          2 | 2020-12-11 00:00:00 |
|             3 | 0120201202000002 |          2 | 2020-12-12 00:00:00 |
+---------------+------------------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> select *
    -> from demo.transactiondetails;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |
+---------------+------------+----------+-------+------------+
4 rows in set (0.01 sec)

mysql> select *
    -> from demo.operator;
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
| operatorid | branchid | workno | operatorname | phone       | address | pid                | duty   |
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
|          1 |        1 | 001    | 张静         | 18612345678 | 北京    | 110392197501012332 | 店长   |
|          2 |        1 | 002    | 李强         | 13312345678 | 北京    | 110222199501012332 | 收银员 |
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
2 rows in set (0.01 sec)

mysql> select
    -> a.transdate,   -- 交易时间
    -> c.operatorname,-- 操作员
    -> d.goodsname,   -- 商品名称
    -> b.quantity,    -- 销售数量
    -> b.price,       -- 价格
    -> b.salesvalue   -- 销售金额
    -> from
    ->   demo.transactionhead as a
    -> join
    ->   demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->  demo.operator as c on (a.operatorid = c.operatorid)
    -> join
    ->  demo.goodsmaster as d on (b.itemnumber = d.itemnumber);
+---------------------+--------------+-----------+----------+-------+------------+
| transdate           | operatorname | goodsname | quantity | price | salesvalue |
+---------------------+--------------+-----------+----------+-------+------------+
| 2020-12-10 00:00:00 | 张静         ||    1.000 | 89.00 |      89.00 |
| 2020-12-10 00:00:00 | 张静         ||    2.000 |  5.00 |      10.00 |
| 2020-12-11 00:00:00 | 李强         ||    2.000 | 89.00 |     178.00 |
| 2020-12-12 00:00:00 | 李强         ||   10.000 |  5.00 |      50.00 |
+---------------------+--------------+-----------+----------+-------+------------+
4 rows in set (0.00 sec)

如果我想看看每天的销售数量和销售金额,可以按照一个字段transdate对数据进行分组和统计。

mysql> select
    -> a.transdate,
    -> sum(b.quantity), -- 统计分组的总计销售数量
    -> sum(b.salesvalue) -- 统计分组的总计销售金额
    -> from
    ->   demo.transactionhead as a
    -> join
    -> demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> group by a.transdate;
+---------------------+-----------------+-------------------+
| transdate           | SUM(b.quantity) | SUM(b.salesvalue) |
+---------------------+-----------------+-------------------+
| 2020-12-10 00:00:00 |           3.000 |             99.00 |
| 2020-12-11 00:00:00 |           2.000 |            178.00 |
| 2020-12-12 00:00:00 |          10.000 |             50.00 |
+---------------------+-----------------+-------------------+
3 rows in set (0.00 sec)

如果想看看每天、每个收银员的销售数量和销售金额,可以按照2个字段进行分组和统计,分别是transdate和operatorname:

mysql>  select
    ->     a.transdate,
    ->     c.operatorname,
    ->     sum(b.quantity), -- 数量求和
    ->     sum(b.salesvalue)-- 金额求和
    -> from
    ->     demo.transactionhead as a
    -> join
    ->     demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->     demo.operator as C on (a.operatorid = c.operatorid)
    -> group by a.transdate , c.operatorname; -- 按照交易日期和操作员分组
+---------------------+--------------+-----------------+-------------------+
| transdate           | operatorname | SUM(b.quantity) | SUM(b.salesvalue) |
+---------------------+--------------+-----------------+-------------------+
| 2020-12-10 00:00:00 | 张静         |           3.000 |             99.00 |
| 2020-12-11 00:00:00 | 李强         |           2.000 |            178.00 |
| 2020-12-12 00:00:00 | 李强         |          10.000 |             50.00 |
+---------------------+--------------+-----------------+-------------------+
3 rows in set (0.00 sec)

可以看到,通过对销售数据按照交易日期和收银员进行分组,再对组内数据进行求和统计,就实现了对每天、每个收银员的销售数量和销售金额的查询。

好了,知道了group by 的使用方法,我们就来学习having。

回到开头的超市经营者的需求:查询单笔销售金额超过 50 元的商品。现在我们来使用 having 来实现,代码如下:

mysql> select b.goodsname
    -> from demo.transactiondetails as a
    -> join demo.goodsmaster as b
    -> on (a.itemnumber=b.itemnumber)
    -> group by b.goodsname
    -> having max(a.salesvalue) > 50;
+-----------+
| goodsname |
+-----------+
||
+-----------+
1 row in set (0.00 sec)

这种查询方式在MySQL里面分四步实现。

第一步,把流水明细表和商品信息表通过公共字段itemnumber连接起来,从两个表中获取数据:

mysql> select
    ->     a.*, b.*
    -> from
    ->     demo.transactiondetails a
    ->         join
    ->     demo.goodsmaster b on (a.itemnumber = b.itemnumber);
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
| transactionid | itemnumber | quantity | price | salesvalue | itemnumber | barcode | goodsname | specification | unit | salesprice |
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |          1 | 0001    || NULL          ||      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |          2 | 0002    || NULL          ||       5.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |          1 | 0001    || NULL          ||      89.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |          2 | 0002    || NULL          ||       5.00 |
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
4 rows in set (0.00 sec)

查询的结果有点复杂,为了方便理解,对结果进行了分类,并加了注释,如下图所示:

img

第二步,把结果集按照商品名称进行分组,分组的示意图如下所示:

组一:

img

组二:

img

第三步,对分组后的数据集进行筛选,把组中字段salesvalue的最大值 >50 的组筛选出来,结果如下:

img

第四步,返回商品名称。这是我们就得到了结果:单笔销售金额超过50元的商品。

总结使用having的查询过程:

首先我们需要把所有的信息都准备好,包括从关联表中获取的信息,对数据集进行分组,形成一个包含所有需要的信息的数据集合。接着,再通过having 条件筛选,得到需要的数据。

怎么正确的使用where和having?

首先我们需要知道它们的2个典型区别:

第一个区别是,如果需要通过连接从关联表中获取需要的数据,where是先筛选后连接,而having是先连接后筛选。

这一点就决定了在关联查询中,where比having更高效。因为where可以先筛选,用一个筛选后的较小的数据集和关联表进行连接,这样占用的资源比较少,执行效率也就比较高。having则需要先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也比较低。

第二个区别是,where可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;having必须要与group by配合使用,可以把分组计算的函数和分组字段作为筛选条件。

这也就决定了,在需要对数据进行分组统计的时候,having可以完成where不能完成的任务。这是因为,在查询语法结构中,where在group by 之前,所以无法对分组结果进行筛选。having在group by之后,可以使用分组字段和分组中的计算函数对分组的结果集进行筛选,这个功能是where无法完成的。

举个例子,假如超市经营者提出,要查询是哪个收银员、在哪天卖了2单商品。这种必须先分组才能筛选的查询,用where语句实现就比较难,我们可能要分好几步,通过把中间结果存储起来,才能搞定,但是用having,就很轻松,如下:

mysql> select
    ->   a.transdate, c.operatorname
    -> from
    ->   demo.transactionhead as a
    -> join
    ->   demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->   demo.operator as c on (a.operatorid = c.operatorid)
    -> group by a.transdate, c.operatorname
    -> having count(*)=2;  -- 销售了2单
+---------------------+--------------+
| transdate           | operatorname |
+---------------------+--------------+
| 2020-12-10 00:00:00 | 张静         |
+---------------------+--------------+
1 row in set (0.01 sec)

where和having的优缺点:

img

当然了,where和having也可以一起配合使用,包含分组统计函数的条件用having,普通条件用where。这样,我们就既利用了where条件的高效快速,又发挥了having可以使用包含分组统计函数的查询条件的优点,当数据量特别大的时候,运行效率会有很大的差别。

文章来源:https://blog.csdn.net/weixin_57544072/article/details/134260859
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。