《MySQL必知必会》学习笔记六(数据汇总&分组顺序)------掌握部分-CSDN博客

本文链接：https://blog.csdn.net/sinat_28978689/article/details/54949503

MySQL必知必会知识预览
第一章——了解SQL
第二章——MySQL简介
第三章——使用MySQL
第四章——检索数据
第五章——排序检索数据
第六章——过滤数据
第七章——数据过滤
第八章——用通配符进行过滤
第九章——用正则表达式进行搜索
第十章——创建计算字段
第十一章——使用数据处理函数
第十二章——汇总数据
第十三章——分组数据
第十四章——使用子查询
第十五章——联结表
第十六章——创建高级联结
第十七章——组合查询
第十八章——全文本搜索
第十九章——插入数据
第二十章——更新和删除数据
第二十一章——创建和操纵表
第二十二章——使用视图
第二十三章——使用储存过程
第二十四章——使用游标
第二十五章——使用触发器
第二十六章——管理事务处理
第二十七章——全球化和本地化
第二十八章——安全管理
第二十九章——数据库维护
第三十章——改善性能

————————————– 华丽的分隔符 ————————————————–

第十二章

聚合函数使用原因
我们常常需要汇总数据而不用把它们实际检索出来，返回实际表数据是对时间和处理资源的一种浪费，为此MySQL提供了专门的函数，使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。利用聚合函数汇总数据，比你自己在客户机应用程序中效率要高得多。
常见的聚合函数使用场景
1.确定表中的行数（或者满足某个条件或者包含某个特定值的行数）
2.获取表中行组的和。
3.找出表列的最大值、最小值、平均值。
聚集函数定义
运行在行组上，计算和返回单个值的函数
五个聚集函数
1. AVG()
2. COUNT()
3. MAX()
4. MIN()
5. SUM()

AVG() // 求该列的平均值
select AVG(prod_price) as avg_price from products;
注意：
AVG只能用来确定特定数值列的平均值，并且列名作为函数参数给出，为了获取多个列的平均值，必须使用多个AVG()函数。
AVG函数忽略值为NULL的行。

Count() //进行计数。
select count(*) as num_cust from customers;
select count (cust_email) as num_cust from customers;
这里写图片描述

注意：
Count 参数带不带引号 //应该为不带，带引号的应该为匹配的全部，包括null列
Null值如果指定列名，则指定列的值为空的行被count()函数忽略，但如果count(*)则不被忽略。

Max() // 求最大值
select Max(prod_price) as max_price from products;
注意：
对非数值数据使用MAX()函数，虽然MAX()函数一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。
MAX()函数忽略列值为NULL的行。

Min() //求最小值规则同Max函数

SUM() //用来返回指定列值的和;
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
select SUM(listprice*unitcost) as total_Prices from orderitems where order_num = 20005;
select AVG(distinct pord_price) as avg_price from products where vend_id = 1003;
这里写图片描述

注意：
1.利用标准的算术操作符，所有的聚合函数都可以用来执行多个列上的计算。
2.如果是只包含不同的值，指定distinct参数。（在5.03中添加该功能）。
3.DISTINCT用于count函数时，必须指定列名，不能用于count(*) 。
4.DISTINCT在聚合函数中的使用（使用在MAX和MIN中没有实际意义）。
5.DISTINCT只能使用列名，而不能使用于计算或者表达式。

第十三章Group By 和Having

Group By分组

select vend_id,count(*) as num_prods from products group by vend_id;
这里写图片描述

说明：group by 子句，指示MySQL 按照vend_id 排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。从输出中可以看出，供应商1001有3个产品，1002有2个产品。

Group By 子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

Group By一些重要的规定:
1. 可以包含任意数量的列.这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2. 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。也就是说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)。
3. Group By子句中列出的每个列都必须是检索列，或有效的表达式(但不能是聚合函数)。如果在select中使用表达式，则必须在group By子句中指定相同的表达式，不能使用别名(5.5可以使用别名，而且效率高于不使用别名的)。
4. 除聚集计算语句外，select语句中的每个列都必须在group By 子句中给出(5.5可以使用不打印的列进行分组)。
5. 如果分组具有NULL值，则NULL将作为一个分组返回。如果列中有多个NULL，他们将被分为一组。
6. Group By子句必须出现在where子句之后，order By 子句之前。

Having 过滤分组

Where 与Having的联系
上述所学的where子句都可以用having来代替，唯一的差别就是where过滤行，having过滤分组。Having支持所有where操作符(包括通配符条件和带多个操作符的子句)，所学的有关where的所有技术都适用于having，他们的语法是相同的，只是关键字有差别。
【5.5 中Where子句中不能使用别名，having中可以使用】

select vend_id,count(*)  as num_prods from products group by vend_id having count(*)>=3;

//与上述group by 相比较
这里写图片描述

Having和where的差别：
Where在数据分组前进行过滤，having在数据分组后进行过滤，这是一个重要的区别。Where排除的行不包括在分组中，这可能会改变计算值，从而影响having子句中基于这些数值过滤掉的分组。【说明where子句优先级高于having】

select vend_id,count(*)  as num_prods from products where vend_id>1002 group by vend_id having count(*)>=3;

这里写图片描述

Having出现则必定有group by出现【having是过滤分组】

Group By(分组) 和 Order By(排序)
这里写图片描述

select order_num ,SUM(quantity * item_price) as ordertotal from orderitems Group By order_num
Having SUM(quantity*item_price)>=50;

select order_num ,SUM(quantity * item_price) as ordertotal from orderitems Group By order_num
Having SUM(quantity*item_price)>=50 order by ordertotal;
这里写图片描述