mysql-练习(基础练习、分组，查询)

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','程心','女',20,'123456789012345678','北京','2009-01-01'),
       (2,'2','章北海','男',27,'123456789012345679','北京','2000-09-01'),
       (3,'3','罗辑','男',27,'123456789012345678','北京','2002-09-01'),
       (4,'4','罗德','男',39,'12345678901234567X','上海','1997-03-24');

-- 1. 查询所有员工的工作地址
select workaddress from emp;
-- 2. 查询公司员工的上班地址（不要重复）
select distinct workaddress from emp;

-- 1. 查询年龄等于20的员工
select * from emp where age=20;
-- 2. 查询年龄小于20的员工信息
select * from emp where age<20;
-- 3. 查询年龄小于等于20的员工信息
select * from emp where age <= 20;
-- 4. 查询没有身份证号的员工
select * from emp where idcard is null or idcard = '';
-- 5. 查询有身份证号的员工
select * from emp where idcard is not null and idcard != '';
-- 6. 查询年龄不等于20的员工
select * from emp where age != 20;
-- 7. 查询年龄在15岁（包含）到30岁（包含）之间的员工信息
select * from emp where age >= 15 and age <= 30;
-- 8. 查询性别为女且年龄小于25岁的员工信息
select * from emp where gender = '女' and age < 25;
-- 9. 查询年龄等于等于25或30或35的信息
select * from emp where age in (25,30,35);
-- 10. 查询姓名为两个字的信息
select * from emp where length(name)=2;
-- 11. 查询身份证最后为X的员工信息
select * from emp where idcard like '%X';
-- 12. 查询北京市的员工人数
select count(*) from emp where workaddress like '北京%'; 

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