【跟上黑马学sql】学习笔记-MySQL-DQL-分组查询

学扎娃的小白

已于 2022-06-05 16:42:50 修改

阅读量110

点赞数

文章标签： mysql sql 学习

于 2022-06-03 20:08:29 首次发布

本文链接：https://blog.csdn.net/weixin_67842001/article/details/125115426

版权

MySQL-DQL-分组查询笔记实例

#创建emp3表并填入数据

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

insert into emp3(id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '1', '嫦娥', '女', 30, '123456789012345678', '北京', '2000-01-01'),
       (2, '2', '张飞', '男', 18, '123456789012345670', '北京', '2005-09-01'),
       (3, '3', '李坤', '女', 38, '123456789012345670', '上海', '2005-08-01'),
       (4, '4', '赵路', '女', 18, '123456789012345670', '北京', '2009-12-01'),
       (5, '5', '小果', '女', 16, '123456789012345678', '上海', '2007-07-01'),
       (6, '6', '杨过', '男', 28, '12345678901234567X', '北京', '2006-01-01'),
       (7, '7', '范为', '男', 40, '123456789012345670', '北京', '2005-05-01'),
       (8, '8', '卢子侧', '男', 38, '123456789012345670', '天津', '2015-05-01'),
       (9, '9', '李凉凉', '女', 45, '123456789012345678', '北京', '2010-04-01'),
       (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
       (11, '11', '张士诚', '男', 55, '123456789012345670', '江苏', '2015-05-01'),
       (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'),
       (13, '13', '张无几', '男', 108, '123456789012345678', '江苏', '2020-11-01'),
       (14, '14', '女作家', '女', 65, '123456789012345670', '西安', '2019-05-01'),
       (15, '15', '胡时', '男', 70, null, '西安', '2018-04-01'),
       (16, '16', '周熟人', '男', 28, null, '河南', '2022-06-01'),
       (17, '17', '王字清', '男', 24, null, '河南', '2022-06-01'),
       (18, '18', '凤城表', '男',30, null, '河南', '2022-06-01'),
       (19, '19', '卢书杰', '男', 31, null, '河南', '2022-06-01'),
       (20, '20', '狗证', '男', 26, null, '河南', '2022-06-01');

#分组查询

##1.根据性别分组，统计男性员工和女性员工的数量
select gender,count(*)from emp3 group by gender;
##2.根据性别分组，统计男性员工和女性员工的平均年龄
select avg(age) from emp3 group by gender;
##3.查询年龄小于65的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) from emp3 where age<65 group by workaddress having count(*)>=3;