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_positionsFROM 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 empwhere 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 empwhere sex= '男'and salary>900 group by depart;