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


    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)



     
  • 相关阅读:
    Ajax学习笔记3种Ajax的实现
    分页学习笔记真分页和假分页实现
    学习笔记链表练习,模仿StringBuilder的山寨版StringBuilder
    学习笔记将Asp.Net网站发布到IIS的四种方法及注意事项
    3D 音频技术产品介绍(1):Iosono the future of spatial audio
    国际顶级语音信号增强工作组:IWAENC(International Workshop on Acoustic Echo and Noise Control)
    转:《欢聚时代(多玩YY)IPO招股书》(概要)
    苏州阔地网络科技有限公司专利分析
    CELT和SILK以及Opus的位分配方法
    ISAC 码流格式
  • 原文地址:https://www.cnblogs.com/mingerlcm/p/9878751.html
Copyright © 2020-2023  润新知