Mysql学习——更多用例

# stuinfo(id,name,email,gradeId,sex,age)

# 查询所有学员的邮箱用户名
SELECT SUBSTR(email,1,INSTR('@')-1)
FROM stuinfo

# 查询男生和女生个数
SELECT sex,COUNT(*)
FROM stuinfo
GROUP BY sex;

# 查询哪个年级学生的最小年龄>20
SELECT MIN(age),gradeId
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age)>20;


# 查询平均工资最低的部门信息
# 方法一：
# 1、平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
# 2、最低平均工资
SELECT MIN(avg_salary)
FROM(
	SELECT AVG(salary) avg_salary,department_id
	FROM employees
	GROUP BY department_id;
) adg;

# 3、哪个部门平均工资是最低工资
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(avg_salary)
	FROM(
		SELECT AVG(salary) avg_salary,department_id
		FROM employees
		GROUP BY department_id;
	) adg;
)
# 4、查询部门信息
SELECT d.*
FROM departments d
WHERE d.department_id=(
	SELECT department_id
	FROM employees e
	GROUP BY e.department_id
	HAVING AVG(e.salary)=(
		SELECT MIN(avg_salary)
		FROM(
			SELECT AVG(ee.salary) avg_salary,ee.department_id
			FROM employees ee
			GROUP BY ee.department_id
		) adg
	)
)

# 方法二：
# 1、最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

# 2、查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

## 查询平均工资高于公司整体平均工资的部门信息
#1、公司整体平均公司
SELECT AVG(salary)
FROM employees;
#2、查询高于这个工资的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
	FROM employees;
);
#3、查询这些部门的部门信息
SELECT d.*
FROM departments d
INNER JOIN (
	SELECT department_id id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary) > (
		SELECT AVG(salary)
		FROM employees
	)
) high_department
ON d.department_id=high_department.id;