编辑代码

-- DQL查询语言中的分组查询,group by查询;
create database info;
use info;
create table worker(
    id int,
    name varchar(10),
    age int,
    gender char(1),
    workcity varchar(10),
    entrytime date
);
insert into worker values (1,'张三',28,'F','上海','2020-08-20');
insert into worker values(2,'李四',24,'F','上海','2021-06-24');
insert into worker values(3,'王二麻',27,'M','北京','2020-07-22');
insert into worker values(4,'王五',29,'M','深圳','2022-01-10');
insert into worker values(5,'赵六',32,'F','深圳','2019-07-07');
insert into worker values(6,'钱九',31,'M','北京','2018-04-19');
insert into worker values(7,'孙八',30,'F','深圳','2021-09-17');
insert into worker values(8,'何十',34,'M','北京','2022-03-28');
insert into worker values(9,'九九',26,'M','深圳','2023-08-19');
insert into worker values(10,'零七',24,'F','上海','2021-06-24');
insert into worker values(11,'可可',27,'M',null,'2024-07-21');
-- select * from worker;

-- group by 的查询语法
-- select 字段 from 表名 where分组前条件 group by 分组字段名 having 分组后的过滤条件; 
-- 根据性别分组,统计男性和女性的数量;
select gender,count(*) from worker group by gender;
-- 根据工作地点分组,统计人数;
select workcity,count(*) from worker group by workcity;
-- 根据性别分组,统计男性和女性员工的平均年龄;
select gender,avg(age) from worker group by gender;
-- 查询年龄大于25的员工,并根据工作地点分组,获取员工数量大于1;
select workcity,count(*) from worker where age>25 group by workcity having count(*)>1;
-- 本句SQL是分三步 先用where 再用group by 然后添加count(*)最后添加having;
-- 总结 group by 查询需要有聚合函数;查询后的是聚合字段或者分组后的字段;