基本语法

group by 后跟需要分组的字段进行分组查询。

  1. select age from student
  2. group by age;

分组也可以去除重复数据，使用分组：常用于在单个表中的查询结果。进行汇总数据。

  1. 统计班级中学生的age，并统计出每个age的人数。

    1. SELECT age, COUNT(age) from student
    2. GROUP BY age;

    image.png

  2. 统计班级student表中学生的分数score，每个得分的人数统计出来。 ```sql — 统计 score SELECT score, COUNT(*) from student GROUP BY score;

  1. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1632282473681-36d45469-498b-4386-a03f-7d7fdbd8bb32.png#clientId=u1f2b176f-7069-4&from=paste&height=206&id=u66bcae52&margin=%5Bobject%20Object%5D&name=image.png&originHeight=412&originWidth=332&originalType=binary&ratio=1&size=39936&status=done&style=none&taskId=uf6a9ffc4-861c-4337-85ab-f3552954c90&width=166)
  3. 3. 统计班级student表中学生的性别，并统计出每个性别的人数。
  4. ```sql
  5. -- 统计 sex 
  6. SELECT sex, COUNT(*) from student
  7. GROUP BY sex;

image.png

分组与排序

在进行分组的时候，主要使用语法 group by, 分完组的数据，也可以进行排序 order by。

  1. 按照年龄age 进行分组，统计每个age对应的人数，并按age的大小进行降序排序。

    1. SELECT age,COUNT(age) from student
    2. GROUP BY age
    3. ORDER BY age DESC

    image.png

  2. 按照年龄age 进行分组，统计每个age对应的人数，并按人数的大小进行降序排序。

    1. SELECT age,COUNT(age) from student
    2. GROUP BY age
    3. ORDER BY COUNT(age) DESC, age

    image.png

having 分组的条件

使用group by 进行分组，分组完成之后可以对返回的结果进行条件过滤。
having 放在group by 之后， 跟 group by 一起使用，不能单独使用。

  1. 以age字段进行分组，统计每个age的人数，查询人数为2的信息；

    1. select age, count(*) from student
    2. group by age
    3. having count(*) = 2;

    image.png

  2. 以score字段进行分组，统计每个分数的人数，查询人数为2的相关信息；

    1. select score,count(*) from student
    2. group by score
    3. having count(*) = 2;

    image.png
    image.png

  3. 统计order 表中 每种水果p_name,销售总数量 p_num;

    1. -- 使用 sum 函数进行数据统计 
    2. select p_name, sum(p_num) from orders 
    3. GROUP BY p_name;

    image.png

image.png

  1. 统计每种水果的销售总额（p_price*p_num）。
    1. SELECT p_name, sum(p_price * p_num) from orders 
    2. GROUP BY p_name;
    image.png

总结

使用分组的场景主要是做一些数据汇总的操作，比如统计销售每种商品销售的数量，销售的总额。
使用方式：

  1. 先确定分组的字段。
  2. 使用 sum，count等聚合函数对相关字段进行对应的运算。

统计orders 表中，每种水果的 订单数（count()），销售总额(sum(pricenum))，销售数量，销售最大单价，销售最小单价，销售平均单价。
image.png

  1. select p_name, count(*) 订单数, 
  2. sum(p_price*p_num) 销售总额, 
  3. sum(p_num) 销售数量, 
  4. max(p_price) 最大价格,
  5. min(p_price) 最小价格, 
  6. avg(p_price) 平均价格 from orders
  7. group by p_name;

image.png
查询订单数量大于3的商品名称，订单数量，销售数量。

  1. SELECT p_name,SUM(p_num)销售数量,COUNT(*)订单数 FROM `orders` 
  2. GROUP BY p_name
  3. HAVING 订单数>3;  -- 过滤

image.png

查询订单数量大于3并且销售数量不低于20的商品名称，订单数量，销售数量。

  1. select p_name, count(*)订单数量,  sum(p_num) 销售数量 from orders
  2. group by p_name
  3. having 订单数量>3 and 销售数量>=20;

image.png

  1. 按照年龄age 进行分组，统计年龄人数最多的 年龄 age。

image.png
分析：

  1. 先找到 人数最多。
    1. select count(age) from student
    2. group by age     -- 分组。age
    3. order by count(age) desc   -- 按照 人数 排序
    4. limit 1 -- 最大人数
    b. 根据人数找对应的年龄
    1. SELECT age ,count(*) from student
    2. GROUP BY age 
    3. HAVING COUNT(*) = 2
    c. 两个查询使用子查询的方式合并到一句
    1. SELECT age ,count(*) from student
    2. GROUP BY age 
    3. HAVING COUNT(*) = (
    4. select count(age) from student
    5. group by age     -- 分组。age
    6. order by count(age) desc   -- 按照 人数 排序
    7. limit 1 -- 最大人数
    8. );

    image.png

练习

image.png

  1. 统计每天的订单数量，订单总额

    1. SELECT create_at,count(*), sum(p_price*p_num) from orders 
    2. GROUP BY create_at;

    image.png

  2. 统计2021年每个月的订单数量，订单总额

    1. 分析，先把 日期 2021-08-30 格式化 2021-08 
      1. select date_format(create_at,"%Y-%m") from orders;
      image.png
      b. 按照格式化之后的数据进行分组；
      1. select date_format(create_at,'%Y-%m'),count(*),sum(p_price*p_num) from orders
      2. group by  date_format(create_at,'%Y-%m');
      image.png

  3. 统计2021年9月份每天的订单数量，订单总额

    1. 分析：因为要查询9月份的数据，可以先使用 where 条件过滤 create_at 字段
      1. select * from orders
      2. where create_at like "2021-09%";
      image.png

b. 9月份的数据查询出来之后，再进行分组；

  1. select create_at,count(*),sum(p_price*p_num) from orders
  2. where create_at like "2021-09%" -- 先过滤出来9月份的数据
  3. group by create_at;    -- 按照每天进行分组；

image.png

应用场景

image.png

  1. 统计最近10天的订单数； — 使用 datediff() 计算

    1. 分析：先过滤出来最近10 使用 datediff(now(),create_at) <= 10 
      1. -- 最近10天之内的数据
      2. SELECT * from orders
      3. WHERE  DATEDIFF(NOW(),create_at) <= 10 ;
      image.png
      b. 再进行分组
      1. select create_at, count(*) from orders
      2. where datediff(now(),create_at) <= 10 -- 过滤日期
      3. group by create_at;   -- 进行分组
      image.png

  2. 统计最近10天的销售额；

原理与上面原理一样。先过滤出 最近10天的所有数据，再进行分组查询;

  1. select create_at, sum(p_price*p_num) from orders
  2. where datediff(now(),create_at) <= 10 -- 过滤日期
  3. group by create_at;   -- 进行分组

image.png

查询顺序

Mysql-03-数据分组 - 图30
使用这些查询语句的时候，语法顺序

where
group by … having
order by
limit

面试题

  1. count(*), count(name), count(1) 之间的区别；

统计结果上对比
image.png

  2. SELECT count(*), count(score), count(1) from student;

image.png
count(name) 如果为空，不统计。
count(*), count(1) 都会统计。

执行效率
count(1) 比count(*) 速度快。

  1. 查询的时候，去重有哪些方法？

distinct
group by 去重。

  1. 分组查询你使用过吗？ 举一个场景；

使用过，统计相关数据的时候，比如：电商里面统计最近10天的订单数的数。 https://www.yuque.com/imhelloworld/bypiud/ltnb1h#CUj24
银行中 最近10天的 每天刷卡次数。

作业

根据订单表
image.png

  1. 查询 单价 (p_price) 大于5 的所有订单；

  2. 查询 销售数量(p_num) 在10-20 之间的订单；

    1. -- 查询 单价 (p_price) 大于5 的所有订单；
    2. SELECT * FROM orders
    3. WHERE p_price > 5;
    4. -- 查询 销售数量(p_num) 在10-20 之间的订单；
    5. SELECT *FROM orders
    6. WHERE p_num BETWEEN 10 AND 20 ;

  3. 查看 商品名称(p_name)为 apple,orange 的订单信息;

  4. 查询 每个订单的 商品名称(p_name) , 商品单价(p_price), 商品数量(p_num), 订单价格，并按订单价格降序； ```python SELECT *FROM orders WHERE p_name in(“apple”,”orange”)

SELECT id,p_name,p_price,p_num,p_nump_price FROM orders ORDER BY p_nump_price desc;

  2. 5. 查询 最近1周的订单信息；
  3. 5. 针对每个订单中商品的单价大于5元的订单进行降序排序；
  4. ```python
  5. -- 查询 最近1周的订单信息；
  6. select * from orders
  7. where datediff(now(),create_at) <=7;
  8. -- 针对每个订单中商品的单价大于5元的订单进行降序排序；
  9. select * from orders
  10. where p_price >5
  11. order by p_price desc;
  1. 统计每个商品（p_name）的订单数量，订单总额； ```python 统计每个商品（p_name）的订单数量，订单总额；

SELECT p_name 商品名称,count()订单数量,sum(p_pricep_num)订单总额 FROM orders GROUP BY p_name;

  2. 8. 查询商品名称(p_name)以`e` 结尾，每天的订单总额，订单销售总量；
  3. ```python
  4. select create_at,sum(p_num*p_price),count(*) from orders
  5. where p_name like "%e"
  6. group by create_at;
  1. 统计单个订单中销售数量(p_num)最高的 商品名称（p_name）,销售数量，销售单价，订单日期（create_at）; ```python — 1. 先找到最大的销售数量 select max(p_num) from orders;

select p_num from orders order by p_num desc limit 1;

— 2 根据数量找订单 select * from orders WHERE p_num = ( select max(p_num) from orders )

  2. 10. 统计 销售总量最多的商品的名字，商品销售总量；
  3. ```python
  4. -- 1. 找到每个商品的销售总量
  5. select p_name, sum(p_num) from orders
  6. group by p_name;
  8. -- 2. 最大 
  9. select sum(p_num) from orders
  10. group by p_name
  11. ORDER BY SUM(p_num) DESC
  12. LIMIT 1;
  14. -- 根据最大值找到对应的商品 
  16. SELECT p_name, sum(p_num) FROM orders 
  17. GROUP BY p_name
  18. HAVING SUM(p_num) = (
  19.     select sum(p_num) from orders
  20.     group by p_name
  21.     ORDER BY SUM(p_num) DESC
  22.     LIMIT 1
  23. );
  1. 统计 销售总量第二多的商品的名字，商品销售总量； ```python — 1. 第二高的销售数量 select distinct(sum(p_num)) from orders group by p_name ORDER BY SUM(p_num) DESC LIMIT 1,1

— SELECT p_name, sum(p_num) FROM orders GROUP BY p_name HAVING SUM(p_num) = ( — 子查询 select distinct(sum(p_num)) from orders group by p_name ORDER BY SUM(p_num) DESC LIMIT 1,1 )

  2. 12. 统计 销售总额sum(p_price*p_num)最多的商品名字，销售总额；
  3. ```python
  4. -- 1. 销售总额最大
  5. select p_name,sum(p_price*p_num) from orders
  6. group by p_name
  7. order by sum(p_price*p_num) desc
  8. limit 1
  11. -- 
  12. select p_name,sum(p_price*p_num) from orders
  13. group by p_name having sum(p_price*p_num) = (
  14.     select sum(p_price*p_num) from orders
  15.     group by p_name
  16.     order by sum(p_price*p_num) desc
  17.     limit 1
  19. );