mysql 分组查询

create table student (
class int  not null comment'班级',
name  varchar(200) not null comment'姓名',
chinese double,
math    double,
english double ,
physics double,
biology double,
chemistry double
);
--  创建 表 student 
insert into student values(3,'李青',89,95,74,83,92,67),(2,'花平',98,67,85,85,67,58),(1,'宋江',57,69,98,45,34,87),(2,'林冲',76,87,90,99,89,87);

insert into student values(3,'李威',89,67,68,56,90,67),(1,'王武',90,87,98,77,87,86),(2,'路飞',87,68,67,54,86,86),(1,'马云',88,90,88,90,79,93);

insert into student values(3,'聂风',78,80,79,79,80,89),(2,'无名',80,79,89,79,79,80);
-- 批量添加数据
select avg(math) from student group by class;
-- 以班级为分组条件 查询 数学成绩 平均分;

select class count(math) from student where math>80 group by class having count(math)>1;

-- 查询 哪个班级 math>80的人数大于1 ;

where 和 having 的区别 

where 在分组前过滤   having 在分组后过滤 

where 后不能跟聚合函数   having 后可以跟聚合函数 

having 不能脱离 group by   一般与group by 连用 

where 操作的数据源为原始表

having 操作的数据源 为 结果集