MySQL查询练习题1.平均工资2.查询各部门的总薪水3.查询总薪水排名第二的部门4.查询姓名重复的员工信息5.查询各部门薪水大于900的男性员工的平均薪水

创建一个员工表emp，包含字段：姓名name，性别sex，部门depart，工资salary

 create table emp(
     name varchar(30) not null,
     sex varchar(30) not null,
     depart int not null,
     salary int not null
     );

插入数据打印为

mysql> insert into emp values
    -> ('张三','男',1,1000),
    -> ('李艾','男',3,3000),
    -> ('李离','男',2,2000),
    -> ('王五','男',3,5000),
    -> ('妹妹','女',2,4000),
    -> ('姐姐','女',1,6000),
    -> ('女老板','女',1,9000);

了解group by

在MySQL中，GROUP BY子句的主要作用是根据一个或多个列对结果集进行分组，以便对每个分组执行聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()等）。

1. 基本分组与聚合

最常见的用法是结合聚合函数对某一列或多列进行分组，并计算每组的聚合值。

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

这查询会按照 department 列的值对员工进行分组，并计算每个部门的员工数量。

2. 多列分组

你可以按照多个列的组合进行分组。

SELECT department, position, COUNT(*) as num_positions
 FROM employees 
 GROUP BY department, position;

这个查询会先按照 department 列的值进行分组，然后在每个部门内部再按照 position 列的值进行分组，最后计算每个部门中每个职位的数量。

3. 与HAVING子句结合使用

HAVING 子句用于过滤分组后的结果。它通常与 GROUP BY 一起使用，以基于聚合函数的结果来过滤分组。

​​​​​​SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department 
HAVING avg_salary > 50000;

这个查询会计算每个部门的平均工资，但只返回那些平均工资超过 50000 的部门。

4. 与ORDER BY结合使用

你可以使用 ORDER BY 子句对分组后的结果进行排序。

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department
ORDER BY num_employees DESC;

这个查询会按照员工数量降序排列部门。

5. 在分组中使用表达式

你可以在 GROUP BY 子句中使用表达式或函数来对结果进行分组。

SELECT YEAR(hire_date) as hire_year, COUNT(*) as num_hires
FROM employees
GROUP BY YEAR(hire_date);

这个查询会按照员工入职年份对员工进行分组，并计算每年的入职员工数量。

查询男女员工的平均工资

 select sex,avg(salary) from emp
   where sex in('男','女') 
   group by sex;

 

查询各部门的总薪水

select depart ,sum(salary) from emp 
  group by depart;

 

查询总薪水排名第二的部门

select depart ,sum(salary) from emp 
  group by depart 
  order by sum(salary) desc --按照薪水降序
  limit 1 offset 1;--分页一个数据占一页,找排名第二就跳过第一页数据

查询姓名重复的员工信息

select * from emp 
  where name in(
    select name from emp --查找出重复的姓名
    group by name 
    having count(*)>1
  );

 

查询各部门薪水大于900的男性员工的平均薪水

select depart,avg (salary) from emp
  where sex= '男'and salary>900 
  group by depart;