employee表
id | name | sex | age | hire_date | post | salary |
添加的数据
部门名post 字段 teacher student sale it
hire_date 范围 2000-01-01 -------2019-12-12
插入6条数据进行以下筛选
1.每个部门的人数
select post, count(id) from employee group by post;
2.每个部门男性的数量
结果select count(id)
条件 where sex="male" group by post
select post,count(id) from employee where sex="male" group by post;
3.每个部门平均工资小于10000的员工数量
结果 select count(id)
条件 group by post having avg(salary) < 10000;
每个班级名字叫郭志强的数量
4.工资在5000到8000之间的员工的姓名及年龄
select name,age,salary from employee where salary between 5000 and 8000;
5.雇佣日期在2018年之前的员工信息
select * from employee where hire_date < "2010-01-01";
6.年龄大于20岁的员工信息，从第二条起显示3条
select * from employee where age>20 limit 1,3;
7.按每个部门的平均工资降序排列，显示平均工资
select * from employee where age>20 limit 1,3;