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

mysql> select post,group_concat(name) from employee group by post;+-----------+-------------------------------------------------+| post | group_concat(name) |+-----------+-------------------------------------------------+| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 || sale | 格格,星星,丁丁,丫丫,歪歪 || teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,alex |+-----------+-------------------------------------------------+3 rows in set (0.00 sec)

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

mysql> select post,count(id) from employee group by post;+-----------+-----------+| post | count(id) |+-----------+-----------+| operation | 5 || sale | 5 || teacher | 6 |+-----------+-----------+3 rows in set (0.00 sec)

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

mysql> select sex,count(id) from employee group by sex;+--------+-----------+| sex | count(id) |+--------+-----------+| male | 8 || female | 8 |+--------+-----------+2 rows in set (0.00 sec)

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

mysql> select post,avg(salary) from employee group by post;+-----------+---------------+| post | avg(salary) |+-----------+---------------+| operation | 16800.026000 || sale | 2600.294000 || teacher | 175766.718333 |+-----------+---------------+3 rows in set (0.00 sec)

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

mysql> select post,max(salary) from employee group by post;+-----------+-------------+| post | max(salary) |+-----------+-------------+| operation | 20000.00 || sale | 4000.33 || teacher | 1000000.31 |+-----------+-------------+3 rows in set (0.00 sec)

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

mysql> select post,min(salary) from employee group by post;+-----------+-------------+| post | min(salary) |+-----------+-------------+| operation | 10000.13 || sale | 1000.37 || teacher | 2100.00 |+-----------+-------------+3 rows in set (0.00 sec)

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

mysql> select sex,avg(salary) from employee group by sex;+--------+---------------+| sex | avg(salary) |+--------+---------------+| male | 136700.055000 || female | 7250.183750 |+--------+---------------+2 rows in set (0.00 sec)

8. 查询岗位名以及各岗位内 年龄50岁以上 包含的员工个数

先把年龄大于50岁的 先取出来 然后进行分组

mysql> select post,count(id) from employee where age >50 group by post;+---------+-----------+| post | count(id) |+---------+-----------+| teacher | 2 |+---------+-----------+1 row in set (0.00 sec)