MySQL必知必会12-14 汇总 分组 子查询

12 汇总数据-用聚集类函数

用于诸如：表中的行数、表中行组的和、列中的最大值平均值等。
（python的dataframe 在输出此类结果时有优势）

MySQL 五个聚集函数（其它还有：标准偏差聚集函数等）

• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()

SELECT AVG(alltotal) FROM yc_tmp;
SELECT MAX(alltotal),MIN(alltotal) FROM yc_tmp

# 若只聚集不同的值，用distinct-- p ;

13 分组和排序

13.1 分组 Group by

• 可以跟多个变量，相当于嵌套分组；
• 除聚集计算语句外，select的每列均需在group by 子句中给出。
• Null 值会单独分为一组；
• 顺序：group by 必需在where子句之后，order by 子句之前

SELECT province,Max(alltotal) as max_total
    ,AVG(alltotal) as avg_total FROM yc_tmp
group by province;

13.2 过滤分组 having

having过滤分组，而where只过滤行。
二者用法相同，功能也类似。having可以替代where的功能

SELECT province,Max(alltotal) as max_total
    ,AVG(alltotal) as avg_total FROM yc_tmp
group by province
having avg_total >1000;

也会有同时使用的情形。
另外：用where是先过滤，再分组计算，having是分组计算后再过滤

SELECT province,Max(alltotal) as max_total
    ,AVG(alltotal) as avg_total FROM yc_tmp
where province not in ('陕西','湖北')
group by province
having avg_total >1000
order by avg_total;

未排序结果

13.3 排序 order by

group by 出来的结果有时看起来是按分组排序的，但SQL并无此机制，故：若需要排序结果，必需用order by 指明。

排序结果

语句顺序：

Select
from
where
group by
having
order by
limit

14 子查询

子查询可用来：

• 实现过滤，A表中的数据符合B表某些条件时显示；
• 提供计算字段。

实现过滤的用法

一般顺序：先从B表中查询到需要的数据
再把该数据结果，放在下一个查询语句中使用。
子查询可以将这两个过程嵌套起来。

Select cust_id 
from orders
Where order_num in  ( select order_num 
                       from  orderitems
                        Where prod_id = 'TNT2') ;  

子查询可以多级嵌套，在使用时建议使用多行，并且适当缩进
注意效率，比对项较多时，速度会慢……

实现字段计算

即正常查询语句中的某一字段，是从其它表中查询过来的。

select cust_name,
         (select count(*) 
       from orders
       Where orders.cust_id = customers.cust_id ) as orders
from customers