MySQL

7.查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示

遗忘知识点:IFNUll函数的使用。已知月薪和奖金率（部分员工无奖金率）

6.查询公司中所有manager的详细信息.

#我的思路:题目要求查询的是所有员工的领导信息.也就是说要使用自连接来展示所有的有下属的员工.
SELECT*
FROM employees ea
INNER JOIN employees eb
ON ea.manager_id = eb.employee_id
#问题:题目要求展示的是领导信息.我的做法展示的是有领导的员工信息.


#正确做法:
#①查找所有的领导id,并去重
SELECT DISTINCT manager_id 
FROM employees


#②查找员工id是①中结果中的任意一个的员工
SELECT *
FROM employees
WHERE employee_id = 
ANY(
	SELECT DISTINCT manager_id 
	FROM employees
)

5.查询平均工资最低的部门信息和该部门的平均工资

USE myemployees
SELECT *
FROM departments 
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
)
#问题:能找得到最低工资的部门id后找到部门的信息,但是最低工资是
#子查询的结果,怎么同时显示呢?
#解决办法:使用连接查询,把子查询的结果作为新表.
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT department_id,AVG(salary)ag
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1

) ag_dep
ON d.`department_id` = ag_dep.department_id;

4.查询平均工资最低的部门信息

#思路:①查询各部门的平均工资,并升序排序,取前1.
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#having avg(salary) limit 10
ORDER BY AVG(salary) 
#问题:不知道怎么排序后在选择前几名.不熟悉升序关键字.
#解决办法:复习limit 的用法,order by后面的语法结构.熟记升序关键字asc.

#正确语法:
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

#②找到department_id后,直接从部门表查该部门信息即可
SELECT * 
FROM departments d
WHERE d.`department_id`=(SELECT AVG(salary) ag
	FROM employees e
	GROUP BY e.`department_id` 
	ORDER BY ag LIMIT 1);
#问题:①未知=后只能接标量子查询②未知虽然要用到avg(salary),但其实可以不用查询出来
#只需查询department_id即可.



#正确语法:
SELECT *
FROM departments
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);

3.查询平均工资最低的部门信息

sql
#①查询各部门的最低平均工资
SELECT AVG(salary) avg_salary,department_id
FROM employees
GROUP BY department_id;


#查询平均工资最低的部门信息
#①查询各部门的平均工资
SELECT AVG(salary) avg_salary,department_id
FROM employees
GROUP BY department_id;
#②查询①结果的最低工资

SELECT MIN(avg_salary)
FROM (
	SELECT AVG(salary) avg_salary,department_id
	FROM employees
	GROUP BY department_id
)ag_dep;

#语法错误点,未给虚拟表起别名

#③查询①中工资=②的部门信息.
SELECT AVG(salary),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
	)ag_dep
)

#④查询部门编号=③中的部门信息

SELECT d.* 
FROM departments d
WHERE d.`department_id` = (	
	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
		)ag_dep
	)
);
#第④步中,子查询不能在查询最低平均工资,只能查询department_id




#方式二：
#①查询平均工资并排序选取第一个的department_id
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1

;
#②拿到①中的department_id直接去查department表
SELECT *
FROM departments
WHERE department_id = (
	
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
)

2.查询所有学员的邮箱的用户名（注：邮箱中@前面的字符）

SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名

考点：substr（原字符串，开始索引，最后索引）[] 截取字符串 注:索引从1开始.
instr(原字符串,子字符串)返回子字符串在原字符串第一次出现的索引. 注:索引从1开始.

1.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资

①查询各部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

②连接①结果集和employees表,进行筛选

SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag ,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
ON e.`department_id` = ag_dep.department_id
WHERE salary > ag;