作用练习

1. 查询岗位名以及岗位包含的所有员工名字

mysql> select post,group_concat(name) from emp group by post;

2. 查询岗位名以及各岗位内包含的员工个数

mysql> select post,count(id) from emp group by post;

3. 查询公司内男员工和女员工的个数

mysql> select sex,count(id) from emp group by sex;

4. 查询岗位名以及各岗位的平均薪资

mysql> select post,avg(salary) from emp group by post;

5. 查询岗位名以及各岗位的最高薪资

mysql> select post,max(salary) from emp group by post;

6. 查询岗位名以及各岗位的最低薪资

mysql> select post, min(salary) from emp group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

mysql> select sex,avg(salary) from emp group by sex;

8. 统计各部门年龄在30岁以上的员工平均工资

mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary);

9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

1.升序
mysql> select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

2.降序
mysql> select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;