mysql中group by的使用

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。

select中的 列名 必须是 group by 后面的 列名。

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

group by 的执行流程(员工各城市数量)

explain select city ,count(*) as num from staff group by city;

group by + where 的执行流程(员工年龄大于30)

explain select city ,count(*) as num from staff where age> 30 group by city;

 group by + having 的执行流程(员工不低于3)

explain select city ,count(*) as num from staff  group by city having num >= 3;

group by + where + having的执行流程(员工年龄大于19且不低于3)

explain select city ,count(*) as num from staff  where age> 19 group by city having num >= 3;

where + having 区别总结

• having子句用于分组后筛选，where子句用于行条件筛选
• having一般都是配合group by 和聚合函数一起出现如(count(),sum(),avg(),max(),min())
• where条件子句中不能使用聚集函数，而having子句就可以
• having只能用在group by之后，where执行在group by之前