Mysql8.0习题系列（四）：聚合函数

2023-03-04 95

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： Mysql8.0习题系列（四）：聚合函数

Mysql8.0习题系列

软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9

@TOC

聚合函数

1.where子句可否使用组函数进行过滤?

不可以

2.查询公司员工工资的最大值，最小值，平均值，总和

mysql> SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
    -> FROM employees;
+-------------+-------------+-------------+-------------+
| MAX(salary) | MIN(salary) | AVG(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
|    24000.00 |     2100.00 | 6588.679245 |   698400.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.02 sec)

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

mysql> SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
    -> FROM employees
    -> GROUP BY job_id;
+------------+-------------+-------------+--------------+-------------+
| job_id     | MAX(salary) | MIN(salary) | AVG(salary)  | SUM(salary) |
+------------+-------------+-------------+--------------+-------------+
| AC_ACCOUNT |     8300.00 |     8300.00 |  8300.000000 |     8300.00 |
| AC_MGR     |    12000.00 |    12000.00 | 12000.000000 |    12000.00 |
| AD_ASST    |     4400.00 |     4400.00 |  4400.000000 |     4400.00 |
| AD_PRES    |    24000.00 |    24000.00 | 24000.000000 |    24000.00 |
| AD_VP      |    17000.00 |    17000.00 | 17000.000000 |    34000.00 |
| FI_ACCOUNT |     9000.00 |     6900.00 |  7920.000000 |    39600.00 |
| FI_MGR     |    12000.00 |    12000.00 | 12000.000000 |    12000.00 |
| HR_REP     |     6500.00 |     6500.00 |  6500.000000 |     6500.00 |
| IT_PROG    |     9000.00 |     4200.00 |  5760.000000 |    28800.00 |
| MK_MAN     |    13000.00 |    13000.00 | 13000.000000 |    13000.00 |
| MK_REP     |     6000.00 |     6000.00 |  6000.000000 |     6000.00 |
| PR_REP     |    10000.00 |    10000.00 | 10000.000000 |    10000.00 |
| PU_CLERK   |     3100.00 |     2500.00 |  2780.000000 |    13900.00 |
| PU_MAN     |    11000.00 |    11000.00 | 11000.000000 |    11000.00 |
| SA_MAN     |    15000.00 |    11000.00 | 13000.000000 |    65000.00 |
| SA_REP     |    12500.00 |     6100.00 |  8741.379310 |   253500.00 |
| SH_CLERK   |     4200.00 |     2500.00 |  3215.000000 |    64300.00 |
| ST_CLERK   |     3600.00 |     2100.00 |  2785.000000 |    55700.00 |
| ST_MAN     |     8200.00 |     5800.00 |  7280.000000 |    36400.00 |
+------------+-------------+-------------+--------------+-------------+
19 rows in set (0.01 sec)

4.选择具有各个job_id的员工人数

mysql> SELECT job_id,count(*) '人数'
    -> FROM employees GROUP BY job_id;
+------------+------+
| job_id     | 人数 |
+------------+------+
| AC_ACCOUNT |    1 |
| AC_MGR     |    1 |
| AD_ASST    |    1 |
| AD_PRES    |    1 |
| AD_VP      |    2 |
| FI_ACCOUNT |    5 |
| FI_MGR     |    1 |
| HR_REP     |    1 |
| IT_PROG    |    5 |
| MK_MAN     |    1 |
| MK_REP     |    1 |
| PR_REP     |    1 |
| PU_CLERK   |    5 |
| PU_MAN     |    1 |
| SA_MAN     |    5 |
| SA_REP     |   29 |
| SH_CLERK   |   20 |
| ST_CLERK   |   20 |
| ST_MAN     |    5 |
+------------+------+
19 rows in set (0.01 sec)

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

mysql> SELECT MAX(salary) - MIN(salary) AS 'difference'
    -> FROM employees;
+------------+
| difference |
+------------+
|   21900.00 |
+------------+
1 row in set (0.00 sec)

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

mysql> SELECT manager_id,MIN(salary)
    -> FROM employees
    -> WHERE manager_id IS NOT NULL
    -> GROUP BY manager_id
    -> HAVING MIN(salary) > 6000;
+------------+-------------+
| manager_id | MIN(salary) |
+------------+-------------+
|        102 |     9000.00 |
|        108 |     6900.00 |
|        145 |     7000.00 |
|        146 |     7500.00 |
|        147 |     6200.00 |
|        148 |     6100.00 |
|        149 |     6200.00 |
|        205 |     8300.00 |
+------------+-------------+
8 rows in set (0.01 sec)

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

mysql> SELECT department_name, location_id, COUNT(*), AVG(salary)
    -> FROM employees e RIGHT JOIN departments d
    -> ON e.`department_id` = d.`department_id`
    -> GROUP BY department_name
    -> ORDER BY AVG(salary) DESC;
+----------------------+-------------+----------+--------------+
| department_name      | location_id | COUNT(*) | AVG(salary)  |
+----------------------+-------------+----------+--------------+
| Executive            |        1700 |        3 | 19333.333333 |
| Accounting           |        1700 |        2 | 10150.000000 |
| Public Relations     |        2700 |        1 | 10000.000000 |
| Marketing            |        1800 |        2 |  9500.000000 |
| Sales                |        2500 |       33 |  9439.393939 |
| Finance              |        1700 |        6 |  8600.000000 |
| Human Resources      |        2400 |        1 |  6500.000000 |
| IT                   |        1400 |        5 |  5760.000000 |
| Administration       |        1700 |        1 |  4400.000000 |
| Purchasing           |        1700 |        6 |  4150.000000 |
| Shipping             |        1500 |       45 |  3475.555556 |
| Treasury             |        1700 |        1 |         NULL |
| Corporate Tax        |        1700 |        1 |         NULL |
| Control And Credit   |        1700 |        1 |         NULL |
| Shareholder Services |        1700 |        1 |         NULL |
| Benefits             |        1700 |        1 |         NULL |
| Manufacturing        |        1700 |        1 |         NULL |
| Construction         |        1700 |        1 |         NULL |
| Contracting          |        1700 |        1 |         NULL |
| Operations           |        1700 |        1 |         NULL |
| IT Support           |        1700 |        1 |         NULL |
| NOC                  |        1700 |        1 |         NULL |
| IT Helpdesk          |        1700 |        1 |         NULL |
| Government Sales     |        1700 |        1 |         NULL |
| Retail Sales         |        1700 |        1 |         NULL |
| Recruiting           |        1700 |        1 |         NULL |
| Payroll              |        1700 |        1 |         NULL |
+----------------------+-------------+----------+--------------+
27 rows in set (0.00 sec)

8.查询每个工种、每个部门的部门名、工种名和最低工资


mysql> SELECT department_name,job_id,MIN(salary)
    -> FROM departments d LEFT JOIN employees e
    -> ON e.`department_id` = d.`department_id`
    -> GROUP BY department_name,job_id;
+----------------------+------------+-------------+
| department_name      | job_id     | MIN(salary) |
+----------------------+------------+-------------+
| Administration       | AD_ASST    |     4400.00 |
| Marketing            | MK_MAN     |    13000.00 |
| Marketing            | MK_REP     |     6000.00 |
| Purchasing           | PU_MAN     |    11000.00 |
| Purchasing           | PU_CLERK   |     2500.00 |
| Human Resources      | HR_REP     |     6500.00 |
| Shipping             | ST_MAN     |     5800.00 |
| Shipping             | ST_CLERK   |     2100.00 |
| Shipping             | SH_CLERK   |     2500.00 |
| IT                   | IT_PROG    |     4200.00 |
| Public Relations     | PR_REP     |    10000.00 |
| Sales                | SA_MAN     |    11000.00 |
| Sales                | SA_REP     |     6100.00 |
| Executive            | AD_PRES    |    24000.00 |
| Executive            | AD_VP      |    17000.00 |
| Finance              | FI_MGR     |    12000.00 |
| Finance              | FI_ACCOUNT |     6900.00 |
| Accounting           | AC_MGR     |    12000.00 |
| Accounting           | AC_ACCOUNT |     8300.00 |
| Treasury             | NULL       |        NULL |
| Corporate Tax        | NULL       |        NULL |
| Control And Credit   | NULL       |        NULL |
| Shareholder Services | NULL       |        NULL |
| Benefits             | NULL       |        NULL |
| Manufacturing        | NULL       |        NULL |
| Construction         | NULL       |        NULL |
| Contracting          | NULL       |        NULL |
| Operations           | NULL       |        NULL |
| IT Support           | NULL       |        NULL |
| NOC                  | NULL       |        NULL |
| IT Helpdesk          | NULL       |        NULL |
| Government Sales     | NULL       |        NULL |
| Retail Sales         | NULL       |        NULL |
| Recruiting           | NULL       |        NULL |
| Payroll              | NULL       |        NULL |
+----------------------+------------+-------------+
35 rows in set (0.00 sec)

有的部门没有工资信息。

Mysql8.0习题系列（四）：聚合函数

Mysql8.0习题系列

聚合函数

1.where子句可否使用组函数进行过滤?

2.查询公司员工工资的最大值，最小值，平均值，总和

3.查询各job_id的员工工资的最大值，最小值，平均值，总和

4.选择具有各个job_id的员工人数

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

8.查询每个工种、每个部门的部门名、工种名和最低工资

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像