mysql分组聚合优化 mysql分表聚合查询

目录

• 1.数据准备
• 2.聚合函数-count（统计数量）
• 3.聚合函数-max（最大值）
• 4.聚合函数-min（最小值）
• 5.聚合函数-sum（求和）
• 6.聚合函数-avg（平均值）
• 7.group by （分组）
• 8.having子句
• 9.limit（分页）

前言：2022新年新气象，新年第一篇文章，看到一张图，特别有感触，分享给大家，2022继续努力，加油！

学习内容：本期咱们学习聚合函数、group by（分组）、having子句、limit（分页）。

1.数据准备

-- 创建mydb库：mydb
create database mydb default character set utf8 collate utf8_general_ci;
/*
创建学生表:students,
学生号为Sid，数据类型为varchar(10)，主键，
姓名为name，数据类型为varchar(10)，
性别为sex，数据类型为varchar(1)，
年龄为age，数据类型为int，
班级为class，数据类型为varchar(10)，
身份证号为card，数据类型为varchar(20)，
城市为city，数据类型为varchar(20)
*/
create table students(
	Sid varchar(10) primary key,
	name varchar(10),
	sex varchar(1),
	age int,
	class varchar(10),
	card varchar(20),
	city varchar(20)
);
-- 插入数据
insert into students values
('001','王昭君','女',30,'3班','634101199003157654','北京'),
('002','诸葛亮','男',29,'2班','110102199104262354','上海'),
('003','鲁班大师','男',30,'1班','820102199003047654','南京'),
('004','白起','男',35,'4班','840202198505177654','安徽'),
('005','大乔','女',28,'3班','215301199204067654','天津'),
('006','孙尚香','女',25,'1班','130502199506137654','河北'),
('007','百里玄策','男',39,'2班','140102198107277654','山西'),
('008','小乔','女',25,'3班',null,'河南'),
('009','百里守约','男',31,'1班','','湖南'),
('010','妲己','女',24,'2班','440701199607147654','广东'),
('011','廉颇','男',30,'1班','110202199005017754','北京'),
('012','孙膑','男',36,'3班','650102198401297655','新疆');

2.聚合函数-count（统计数量）

注意：聚合函数不能用在where条件中
语法一：select count(*) from 表名; 作用：对查询出来的结果计算总记录数
注意：该条语句中count()中写任意字段名与写*的结果是一直的
语法二：select count(distinct 字段名) from 表名; 作用：对查询出来的结果根据字段名进行去重后再计算总记录数
练习一：

-- 查询学生总数
select count(*) from students;

练习二：

-- 查询性别为女的学生总数
select count(*) from students where sex='女';

练习三：

-- 查询总共有多少个班级
select count(distinct class) from students;

练习四：

-- 查询每个班级的学生数量
select class,count(*) from students group by class;

3.聚合函数-max（最大值）

语法：select max(字段名) from 表名; 作用：对查询出来的结果找该字段的最大值
练习一：

-- 查询最大年龄
select max(age) from students;

练习二：

-- 查询性别为女的最大年龄
select max(age) from students where sex='女';

练习三：

-- 查询1班最大年龄
select max(age) from students where class='1班';

4.聚合函数-min（最小值）

语法：select min(字段名) from 表名; 作用：对查询出来的结果找该字段的最小值
练习一：

-- 查询最小年龄
select min(age) from students;

练习二：

-- 查询性别为男的最小年龄
select min(age) from students where sex='男';

练习三：

-- 查询2班的最小年龄
select min(age) from students where class='2班';

5.聚合函数-sum（求和）

语法：select sum(字段名) from 表名; 作用：对查询出来的结果计算该字段的总和
练习一：

-- 查询学生年龄总和
select sum(age) from students;

练习二：

-- 查询城市为北京的学生年龄总和
select sum(age) from students where city='北京';

6.聚合函数-avg（平均值）

注意：计算平均值时是忽略null值的
语法：select avg(字段名) from 表名; 作用：对查询出来的结果计算该字段的平均值
练习一：

-- 查询学生的平均年龄
select avg(age) from students;

练习二：

-- 查询性别为男的平均年龄
select avg(age) from students where sex='男';

7.group by （分组）

语法：select 聚合函数 from 表名 where 条件 group by 字段名; 作用：
可以把字段相同的数据放到一个组中；
分组就是配合聚合函数使用的，聚合函数会对每一组的数据分别进行统计
注意：当分组和排序同时出现时，排序放在最后面，因为排序是对查询出来的结果最后进行处理
练习一：

-- 查询各个性别的人数
select sex,count(*) from students group by sex;

练习二：

-- 查询各个年龄的人数
select age,count(*) from students group by age;

练习三：

-- 查询1班各个性别的学生数量
select class,sex,count(*) from students where class='1班' group by sex;

练习四：

-- 统计各个班级学生总数、平均年龄、最大年龄、最小年龄
select class,count(*),avg(age),max(age),min(age) from students group by class;

练习五：

-- 统计各个班级学生总数、平均年龄、最大年龄、最小年龄，但不统计3班，统计结果按班级名称从大到小排序
select class,count(*),avg(age),max(age),min(age) from students where class !='3班' group by class order by class desc;

8.having子句

语法：select 聚合函数 from 表名 group by 字段名 having 条件/聚合函数; 作用：分组聚合统计之后进行数据筛选
注意：
having子句总是出现在group by之后
where后面条件不能使用聚合函数，但having可以使用聚合函数
练习一：

-- 查询男生总数
-- where先筛选符合条件的记录，再进行聚合统计
select sex,count(*) from students where sex='男';
-- having先分组聚合统计，然后在统计的结果中筛选
select sex,count(*) from students group by sex having sex='男';

练习二：

-- 查询班级人数大于3人的班级名称
select class from students group by class having count(*)>3;

练习三：

-- 查询平均年龄大于30岁的班级名称和班级总人数
select class,count(*) from students group by class having avg(age)>30;

9.limit（分页）

语法：select * from 表名 limit start,count; 作用：控制查询的结果的数量
解释：
start：代表从第几个索引开始，索引0代表第一行记录；start可以省略，若省略则默认从索引0（也就是第一行）开始
count：代表总共获取多少条记录
另外的计算公式：
每页显示m条数据，查询第n页的数据
select * from 表名 limit (n-1)*m,m; 练习一：

-- 查询前3行的学生记录
select * from students limit 3;

练习二：

-- 查询从第4条记录开始，总共3条学生记录
select * from students limit 3,3;

练习三：

-- 使用limit语句，查询年龄最大的学生记录
select * from students order by age desc limit 1;

练习四：

-- 使用limit语句，查询年龄最小的女同学的学生记录
select * from students where sex='女' order by age limit 1;