mysql聚合函数求平均值 mysql聚集函数

转载

编程艺术之光 2023-09-29 13:10:03

文章标签 mysql聚合函数求平均值数据库取值字段主键 文章分类 MySQL 数据库

mysql 聚集函数 count 使用详解

本文将探讨以下问题

1.count(*) 、 count(n)、count(null)与count(fieldName)
2.distinct 与 count 连用
3.group by (多个字段) 与 count 实现分组计数
4.case when 语句与 count 连用实现按过滤计数

参考文章:
Select count(*)和Count（1）的区别和执行方式

准备工作

-- 创建表
CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '学生姓名',
  `tea_name` varchar(255) DEFAULT NULL COMMENT '教师姓名',
  `stu_class` varchar(255) DEFAULT NULL COMMENT '所在班级名称',
  `stu_sex` varchar(255) DEFAULT NULL COMMENT '学生性别',
  `stu_sex_int` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据 
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('0', '小明', '老张', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('1', '小红', '老张', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('2', '小刚', '老王', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('3', '小兰', '老王', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('4', '小军', '老张', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('5', '小芳', '老张', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('6', '小强', '老王', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('7', '小娜', '老王', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('8', null, null, null, null,null);
-- 创建表
CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '学生姓名',
  `tea_name` varchar(255) DEFAULT NULL COMMENT '教师姓名',
  `stu_class` varchar(255) DEFAULT NULL COMMENT '所在班级名称',
  `stu_sex` varchar(255) DEFAULT NULL COMMENT '学生性别',
  `stu_sex_int` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据 
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('0', '小明', '老张', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('1', '小红', '老张', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('2', '小刚', '老王', '一班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('3', '小兰', '老王', '一班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('4', '小军', '老张', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('5', '小芳', '老张', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('6', '小强', '老王', '二班', '男',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('7', '小娜', '老王', '二班', '女',0);
INSERT INTO `tb_student` (`id`, `stu_name`, `tea_name`, `stu_class`, `stu_sex`) VALUES ('8', null, null, null, null,null);

问题一：count(*) 、 count(n)、count(null)与count(fieldName)

我们看一下执行过程:
EXPLAIN SELECT count(0) from tb_studentEXPLAIN SELECT count(*) from tb_student两次执行结果相同，结果为:
(select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) VALUES ('1', 'SIMPLE', 'tb_student', 'index', NULL, 'stu_sex_int', '2', NULL, '8', 'Using index'); 。
此次查询使用了索引stu_sex_int。我们知道某个字段建立索引之后数据库引擎会对该字段排序并把排序结果作为索引数据存储。查询时候对索引进行
二分查找提高命中率。在使用聚集函数同样也会使用索引。数据库引擎在处理count时,会直接从索引数据(排序结果中)中求排序结果的id最大值。这样一
来会大大提高count的速度。主键也有索引此处为什么没有使用主键的索引？因为stu_sex_int 字段长度更短为tinyint 类型，查找速度更快。
由此可见 count(*)和count(n) n>=0 的效果相同。
当count的表达式为 NULL 时不会计数 ,所以count(fieldName) 当fieldName 为null时不会计数。比如

select count(stu_name) as count from tb_student; 结果: count=8
select count(id) as count from tb_student; 结果: count=9
select count(null) as count from tb_student ; 结果: count= 0

问题二: distinct 与 count 连用

distinct 的作用是对查询结果去重。distinct fieldA 那么在查询结果中 fieldA 的值不会重复。当 count 内的表达式是distinct时候
所表达的意思就是对被distinct的字段取值类型计。例如:

select distinct stu_class from tb_student;

执行结果:

stu_class
一班
二班

select count(distinct stu_class) as count from tb_student;执行结果:

count
2

问题三：group by (多个字段) 与 count 实现分组计数

group by fieldA 是表示根据 fieldA 的不同取值对查询结果进行分组。比如对于 tb_student 表
根据 stu_sex 的不同取值 (男,女) 可把查询结果分成两组。fieldA 有n个不同的取值,查询结果就会被分成
n组。当分组字段有多个时候group by fieldA,fieldB 会对fieldA 和fieldB 进行排列组合。每个排列组合的
结果作为查询一个的一个分组。如果fileA 的取值有 n 个 fieldB的取值有 m 个，那么查询结果将会被分称m*n
组。当count 与 group by 连用时，count是对 group by 结果的各个分组进行计数。
单个分组条件:
SELECT stu_sex ,COUNT(*) as count from tb_student GROUP BY stu_sex ;结果为:

stu_sex	count
NULL	1
男	4
女	4

多个分组条件:

SELECT
        stu_sex,
        stu_class,
        COUNT(*) AS count
    FROM
        tb_student
    GROUP BY
        stu_sex,
        stu_class
    SELECT
        stu_sex,
        stu_class,
        COUNT(*) AS count
    FROM
        tb_student
    GROUP BY
        stu_sex,
        stu_class

结果为:

stu_sex	stu_class	count
NULL	NULL	1
女	一班	2
女	二班	2
男	一班	2
男	二班	2

问题四：case when 语句与 count 连用实现按过滤计数

在上述数据库中如果我们要查每个教师教了多少个 一班 同学和二班同学有两种方法
方法一:对教师和班级分组计数

SELECT
        tea_name,
        stu_class,
        count(*) AS count
      FROM
        tb_student
      GROUP BY
        tea_name,
        stu_class

结果为:

tea_name	stu_class	count
NULL	NULL	1
老张	一班	2
老张	二班	2
老王	一班	2
老王	二班	2

这种方法不太直观我们可以把结果行转列更加清晰表达每个教师交每个班的人数

方法二:使用case when 行转列

SELECT
        tea_name,
        count(case when stu_class='一班' then 1 else null end ) AS `一班人数` ,
        count(case when stu_class='二班' then 1 else null end ) AS `一班人数` 
      FROM
        tb_student
      GROUP BY
        tea_name

结果为: