深圳产品网站建设,烟台城乡建设学校网站,商城网站用html做,上海建站模板搭建条件查询和聚合函数 一、条件查询语句二、聚合函数1、SUM#xff08;#xff09;2、AVG()、MAX()、MIN()3、COUNT#xff08;#xff09; 一、条件查询语句
WHERE 和 HAVING 的区别#xff1a;
WHERE是直接对表中的字段进行限定#xff0c;来筛选结果#xff1b;HAVIN… 条件查询和聚合函数 一、条件查询语句二、聚合函数1、SUM2、AVG()、MAX()、MIN()3、COUNT 一、条件查询语句
WHERE 和 HAVING 的区别
WHERE是直接对表中的字段进行限定来筛选结果HAVING则需要跟分组关键字GROUP BY一起使用通过对分组字段或分组计算函数进行限定来筛选结果。
虽然它们都是对查询进行限定却有着各自的特点和适用场景。
WHERE
WHERE关键字的特点是直接用表的字段对数据集进行筛选。如果需要通过关联查询从其他的表获取需要的信息那么执行的时候也是先通过WHERE条件进行筛选用筛选后的比较小的数据集进行连接。这样一来, 连接过程中占用的资源比较少执行效率也比较高。
HAVING
HAVING不能单独使用必须要跟GROUP BY 一起使用。
我们可以把GROUP BY理解成对数据进行分组方便我们对组内的数据进行统计计算。
它们两个典型的区别就是
如果需要通过连接从关联表中获取需要的数据WHERE 是先筛选后连接而HAVING是先连接后筛选。WHERE可以直接使用表中的字段作为筛选条件但不能使用分组中的计算函数作为筛选条件HAVING必须要与GROUP BY配合使用可以把分组计算的函数和分组字段作为筛选条件。
这决定了在需要对数据进行分组统计的时候HAVING 可以完成WHERE不能完成的任务。这是因为在查询语法结构中WHERE在GROUP BY之前所以无法对分组结果进行筛选。HAVING在GROUP BY之后可以使用分组字段和分组中的计算函数对分组的结果集进行筛选这个功能是WHERE无法完成的。
这么说可能不太好理解举个小例子理解一下。假如超市经营者提出要查询一下是哪个收银员、在哪天卖了2单商品。
这种必须先分组才能筛选的查询用WHERE语句实现就比较难我们可能要分好几步通过把中间结果存储起来才能搞定。但是用HAVING则很轻松代码如下:
SELECTa. transdate, c.operatorname
FROM demo. transactionhead AS a
JOINdemo. transactiondetails AS b ON (a. transactionid b. transactionid)
JOINdemo.operator AS C ON (a.operatorid c. operatorid)
GROUP BY a. transdate, c. operatorname
HAVING count(*)2; --销售了2单
不过需要注意的是WHERE和HAVING也不是互相排斥的也可以在一 个查询里面同使用WHERE和HAVING。
二、聚合函数
MySQL中有5种聚合函数较为常用分别是
求和函数SUM) 可以返回指定字段值的和。求平均函数AVG()最大值函数MAX()最小值函数MIN()计数函数COUNT()
先创建三个表基于这三个表的基础上对聚合函数进行操作理解
-- 创建销售明细表
CREATE TABLE demo.transactiondetails
(
transactionid INT NOT NULL,
itemnumber INT NOT NULL,
quantity INT,
price DECIMAL(10,2),
salesvalue DECIMAL(10,2),
-- 联合主键
PRIMARY KEY(transactionid,itemnumber)
);销售明细表transactiondetails 销售单头表transactionhead 商品信息表goodmaster
1、SUM
SUM () 函数可以返回指定字段值的和。我们可以用它来获得用户某个门店每天、每种商品的销售总计数据
SELECTLEFT(b.transdate, 10), -- 从关联表获取交易时间并且通过LEFT函数获取交易的年月日c.goodsname, -- 从关联表获取商品名称SUM(a.quantity), -- 数量求和SUM(a.salesvalue) -- 金额求和
FROMdemo.transactiondetails aJOINdemo.transactionhead b ON (a.transactionid b. transactionid)JOINdemo.goodmaster c ON (a.itemnumber c.itemnumber)
GROUP BY LEFT(b.transdate,10), c.goodsname -- 分组
ORDER BY LEFT(b.transdate,10), c.goodsname; -- 排序查询结果
这里引入了两个关键字
LEFT(str, n)表示返回字符串str最左边的n个字符。ORDER BY表示按照指定的字段排序。
需要注意的是求和函数获取的是分组中的合计数据所以要对分组的结果有准确的把握否则就很容易搞错。这也就是说我们要知道是按什么字段进行分组的。
如果是按多个字段分组就要知道字段之间有什么样的层次关系如果是按照以字段作为变量的某个函数进行分组的就要知道这个函数的返回值是什么返回值又是如何影响分组的等。
2、AVG()、MAX()、MIN()
AVG() 首先我们来学习下计算平均值的函数AVG ()。它的作用是通过计算分组内指定字段值的和以及分组内的记录数算出分组内指定字段的平均值。 举个例子如果用户需要计算每天、每种商品平均一次卖出多少个、多少钱这个时候我们就可以用到AVG () 函数了如下所示
SELECTLEFT(a. transdate,10),c.goodsname,AVG (b.quantity), -- 平均数量AVG (b.salesvalue) -- 平均金额
FROMdemo. transactionhead aJOINdemo. transactiondetails b ON (a.transactionid b.transactionid)JOINdemo. goodmaster c ON (b.itemnumber c.itemnumber )
GROUP BY LEFT(a. transdate,10) ,c.goodsname
ORDER BY LEFT(a. transdate,10) ,c.goodsname;查询结果 MAX()、MIN() MAX()表示获取指定字段在分组中的最大值MIN()表示获取指定字段在分组中的最小值。它们的实现原理差不多。
我们还是来看具体的例子。假如户要求计算每天里的一次销售的最大数量和最大金额就可以用下面的代码得到我们需要的结果
SELECTLEFT(a. transdate,10),MAX(b.quantity),MAX(b.salesvalue)
FROMdemo. transactionhead aJOINdemo. transactiondetails b ON (a.transactionid b.transactionid)JOINdemo. goodmaster c ON (b.itemnumber c.itemnumber )
GROUP BY LEFT(a. transdate,10) ,c.goodsname
ORDER BY LEFT(a. transdate,10) ,c.goodsname;注意MAX (字段)这个函数返回分组集中最大的那个值。如果你要查询MAX (字段1)和MAX (字段2)而它们是相互独立、分别计算的千万不要想当然地认为结果在同一条记录上。
3、COUNT
通过COUNT ()我们可以了解数据集的大小这对系统优化十分重要。
比如分页策略这个策略能够实现的一个关键就是要计算出符合条件的记录一共有多少条之后才能计算出一共有几页、能不能翻页或跳转。
要计算记录数就要用到COUNT()函数了。这个函数有两种情况。
COUNT (*) 统计一共有多少条记录COUNT (字段) 统计有多少个不为空的字段值。
COUNT (*) 如果COUNT (*)与GROUP BY 一起使用就表示统计分组内有多少条数据。它也可以单独使用这就相当于数据集全体是一个分组统计全部数据集的记录数。
那么如果超市经营者想知道每天、每种商品都有几次销售我们就需要按天、按商品名称进行分组查询
SELECTLEFT(a.transdate, 10), c.goodsname, COUNT(*) -- 统计销售次数
FROMdemo. transactionhead aJOINdemo. transactiondetails b ON (a.transactionid b.transactionid)JOINdemo. goodmaster c ON (b.itemnumber c.itemnumber )
GROUP BY LEFT(a. transdate,10) ,c.goodsname
ORDER BY LEFT(a. transdate,10) ,c.goodsname;运行结果
COUNT (字段) COUNT (字段)用来统计分组内这个字段的值出现了多少次。如果字段值是空就不统计。 针对这个表
如果我们要统计字段cashierNo 出现了多少次就要用到函数COUNT (cashierNo) 结果是3次如果我们要统计字段memberId 出现了多少次就要用到函数COUNT (memberId) 结果是1次。
