mysql 数据操作 单表查询 group by 练习
2024-09-25 15:39:44
小练习：
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)
最新文章
- caching与缓存
- asp.net分割字符串的几种方法
- interproscan 的使用和遇到的问题
- Codeforces Round #362 (Div. 2) C. Lorenzo Von Matterhorn (类似LCA)
- Catch That Cow（BFS）
- linux 软连接 硬连接
- 简单Elixir游戏服设计-创建玩家模型
- SpringBatch的核心组件JobLauncher和JobRepository
- .NET Core微服务之基于Apollo实现统一配置中心
- qt5程序打包含qml
- 解决Fatal error in launcher: Unable to create process using '"'
- Opencv 2.4.10 +VS2010 项目配置
- Plantuml画图工具
- Java如何处理异常层次结构？
- 正则表达式(Java版整理)
- 使用Git进行代码管理心得------------个人练习
- 【leetcode 简单】第十六题 二进制求和
- Spring Boot - Spring Cache
- 使用makecontext实现用户线程【转】
- 一次vm 虚拟机时间倒流而导致的oracle 数据库启动故障