首页 > 数据库技术 > 详细

mysql 数据操作 单表查询 group by 练习

时间:2019-03-12 23:32:47      阅读:73      评论:0      收藏:0      [点我收藏+]

标签:per   style   spa   平均薪资   bold   以及   数据   薪资   ros   

 

小练习:

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.10 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.01 sec)

 

 

 

 

mysql 数据操作 单表查询 group by 练习

标签:per   style   spa   平均薪资   bold   以及   数据   薪资   ros   

原文:https://www.cnblogs.com/mingerlcm/p/10520287.html

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 designnerd.net 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号