川石教育
全国咨询热线:136-9172-9932
  1. 首页 > 资讯与干货 > IT资讯

MySQL | 数据查询DQL语言:分组统计

作者:川石学院 日期:2022-04-20 11:32:30 点击数:

一天一个关于测试知识点,5分钟内讲解你最关心的软件测试问题,今天就接着来谈谈关于软件测试中的“数据查询DQL语言:分组统计”。

MySQL:数据库安装,基本配置及客户端工具使用(图1)

  1、统计函数

  ▲ count(*)

  统计查询结果集返回的行数.

  #统计客户表中客户人数

  SELECT COUNT(*) FROM ecs_users;

  

  #统计商品售价大于500的产品数量

  SELECT COUNT(*) FROM ecs_goods

  WHERE shop_price > 500;

  ▲ count(ve)

  统计值表达式返回的非空值的个数.

  #找出设置了密码提示问题用户人数

  SELECT COUNT(*)

  FROM ecs_users

  WHERE passwd_question IS NOT NULL;

  

  SELECT COUNT(passwd_question) FROM ecs_users;

  ▲ count(distinct ve)

  统计值表达式返回的非空不同值的个数.

  #统计商品表中有多少种产品类型

  SELECT COUNT(DISTINCT goods_type) FROM ecs_goods;

  ▲ sum(ve)

  统计值表达式返回的非空值的和.

  ▲ sum(distinct ve)

  统计值表达式返回的非空不同值的和.

  #统计24号商品的销售数量

  SELECT SUM(goods_number) FROM ecs_order_goods

  WHERE goods_id = 24;

  

  #统计被购买过的商品的价格之和

  SELECT SUM(DISTINCT goods_price)

  FROM ecs_order_goods;

  ▲ avg(ve)

  统计值表达式返回的非空值的平均值.

  ▲ avg(distinct ve)

  统计值表达式返回的非空不同值的平均值.

  #统计商品表中类型为9的商品的平均市场价格

  SELECT AVG(market_price) FROM ecs_goods

  WHERE goods_type = 9;

  

  #统计商品表中类型为9的商品的不同价格的平均值

  SELECT AVG(distinct market_price) FROM ecs_goods

  WHERE goods_type = 9;

  ▲ max(ve)

  统计值表达式返回的非空值的最大值.

  ▲ min(ve)

  统计值表达式返回的非空值的最小值.

  #统计商品表中商品的最大市场价格与最小市场价格

  SELECT max(market_price),MIN(market_price)

  FROM ecs_goods;

  

  #统计产品表中具有最大价格与最小价格的产品信息

  SELECT *

  FROM ecs_goods

  WHERE market_price = (SELECT MAX(market_price) FROM ecs_goods)

  OR market_price = (SELECT MIN(market_price) FROM ecs_goods);

  2、数据分组

  由group by子句来实现的.

  语法:

  select list

  from tabs

  where search_condition

  group by grouping_columns;

  (1)分组的引入

  #统计每种类型的商品的最大市场价格

  SELECT distinct goods_type FROM ecs_goods;

  #type 1

  SELECT MAX(market_price) FROM ecs_goods

  WHERE goods_type = 1;

  #type 2

  SELECT MAX(market_price) FROM ecs_goods

  WHERE goods_type = 2;

  ...

  (2)分组定义

  指定某列或某些列作为划分的依据,比较这些列值是否相同,具有相同列值的行放在同一组,这样就可以将最初的结果集划分为若干个子集,每个子集称为一个分组.

  一般来说有多少个不同的列值就可以分为多少组.

  group 分组

  by 依据 ...

  group by col_name;

  group by c1,c2,...,cn;

  

  #按照商品类型分组

  SELECT goods_type

  FROM ecs_goods

  GROUP BY goods_type;

  

  #在ecs_order_info表中查看不同用户选择的寄送方式

  SELECT user_id,shipping_id

  FROM ecs_order_info

  GROUP BY user_id,shipping_id;

  3、分组后统计

  语法:

  select ...,stat_function(ve),...#stat_function统计函数ve列或计算列

  from tabs

  where search_condition

  group by c1,c2,...,cn;

  指定某列或计算列作为统计的对象,将统计函数作用在该对象上,统计函数按照分组自动对每一分组的列值进行统计,每一分组返回一个统计后的结果.

  #统计每种类型的商品的最大市场价格

  SELECT * FROM ecs_goods;

  

  SELECT goods_type,MAX(market_price)

  FROM ecs_goods

  GROUP BY goods_type;

  

  #类型编号不太直观,将编号转换为对应的类型名称显示

  SELECT gt.cat_name,MAX(market_price)

  FROM ecs_goods g INNER JOIN ecs_goods_type gt

  ON g.goods_type = gt.cat_id

  GROUP BY gt.cat_name;

  (1)MYSQL对GROUP BY的非ANSI标准扩展

  #统计每种类型的产品的最大价格

  SELECT * FROM products;

  

  SELECT goods_type,goods_name,MAX(market_price)

  FROM ecs_goods

  GROUP BY goods_type;

  #MySQL扩展了GROUP BY的用途,可以在SELECT列表中使用GROUP BY子句中未出现过的列

  #这在其他数据库(比如Oracle)中是不允许的.

  

  #统计每种类型下每种商品的销售总额

  SELECT * FROM ecs_goods;

  SELECT * FROM ecs_goods_type;

  SELECT * FROM ecs_order_goods;

  

  #统计之前的数据

  SELECT gt.cat_name, g.goods_name, og.goods_price, og.goods_number

  FROM (ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id)

  LEFT OUTER JOIN ecs_order_goods og

  ON g.goods_id = og.goods_id

  ORDER BY 1, 2;

  #统计之后的数据

  SELECT gt.cat_name,

  g.goods_name,

  IFNULL(SUM(og.goods_price * og.goods_number), 0) amount

  FROM (ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id)

  LEFT OUTER JOIN ecs_order_goods og

  ON g.goods_id = og.goods_id

  GROUP BY gt.cat_name, g.goods_name

  ORDER BY 1, 2;

  #IFNULL(p1,p2) 若p1为null,则输出p2;否则输出p1

  若统计的数据来源于多个表中,需要将多个表连接起来再进行分组统计.

  4、统计后过滤

  是由having子句来实现的.

  语法:

  select list

  from tabs

  where search_condition #过滤原始数据

  group by grouping_columns

  having search_condition; #过滤分组统计后的数据

  若要使用having来过滤数据,则必须先使用group by进行分组.

  having的用法与where用法相似.

  #哪些类型的商品的平均市场价格超过了1000

  SELECT gt.cat_name, ROUND(AVG(market_price), 2)

  FROM ecs_goods g

  INNER JOIN ecs_goods_type gt

  ON g.goods_type = gt.cat_id

  GROUP BY gt.cat_name

  HAVING AVG(market_price) > 1000;

  #round(p1,p2) 对p1进行四舍五入,精确到p2位,p2>0小数点后,p2<0小数点前

  (1)having子句中的子查询

  #哪些类型的商品的平均市场价格超过了所有产品的市场均价

  SELECT gt.cat_name, ROUND(AVG(market_price), 2)

  FROM ecs_goods g

  INNER JOIN ecs_goods_type gt

  ON g.goods_type = gt.cat_id

  GROUP BY gt.cat_name

  HAVING AVG(market_price) > (SELECT AVG(market_price) FROM ecs_goods);


相关文章
  • 亚马逊运营成功转行软件测试,薪资13K表示很满意!2022-04-20 11:32:30
  • 西安川石的兰朋友喊你来当他的学弟学妹啦!2022-04-20 11:32:30
  • 国外的月亮也不一定比国内测试猿的年薪美~2022-04-20 11:32:30
  • 建筑工程专业朱同学成功转行为软件测试人!2022-04-20 11:32:30
  • 财务管理专业转行软件测试月薪甩会计几条街!2022-04-20 11:32:30
  • 只有技术沉淀才能成功上岸,深圳就业薪资13K!2022-04-20 11:32:30
  • 薪资11K!实现自我价值,从掌握一门IT技术开始...2022-04-20 11:32:30
  • 文科生转行软件测试照样拿下高薪15K!2022-04-20 11:32:30
  • 恭喜罗同学喜提19.5K,成功入行软件测试!2022-04-20 11:32:30
  • 毕业1年,迷茫的他最终选择转行软件测试2022-04-20 11:32:30