10-基础SQL-DQL(数据查询语言)-分组查询（GROUP BY）-526互联

DQL-介绍（常用）

DQL英文全称是Data Query Language（数据查询语言），数据查询语言用来查询数据库中表的记录

查询关键字：SELECT

DQL-语法

DQL-分组查询

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

WHERE 与 HAVING 区别：

案例：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES    
(1,"1","马铃薯","男","23","123456789000000000","江西","2023-12-31"),
(2,"2","任先生","男","26","123456789000000001","河北","2023-12-31"),
(3,"3","张三","女","23","123456789000000002","河北","2023-12-31"),
(4,"4","李四","女","24","123456789000000003","山西","2023-12-31")

1）根据性别分组，统计男性员工和女性员工的数量

SELECT gender,count(*) FROM emp GROUP BY gender;

2）根据性别分组，统计男性员工和女性员工的平均年龄

SELECT gender,avg(age) FROM emp GROUP BY gender;

3）查询年龄小于28岁，并分局工作地址分组，获取员工数量大于等于2的工作地址

SELECT workaddress,count(*) FROM emp WHERE age < 28 GROUP BY workaddress HAVING count(*) >= 2;