Mysql学习——子查询

/*

	子查询：
		出现在其他语句中的查询成为子查询或内查询
	

	按结果分类：
		标量子查询：结果只有一行一列
			标量子查询执行顺序先于主查询
		列子查询：结果具有一列多行
		行子查询：结果具有一行多列
		表子查询：结果是多行多列
		
	可能的位置：
		select后面：
			标量子查询
		from后面：  
			表子查询
		where、having后面
			标量子查询
			列子查询
			行子查询
		exists后面
			表子查询
	特点：
		子查询放在小括号内
		标量子查询经常搭配单行操作符使用
		列子查询经常搭配多行操作符使用 in,any,all
*/

#1、标量子查询

# 谁的工资比Abel高
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name="Abel"
)

# job_id与141号员工相同，salary比143号员工高的员工
SELECT job_id,salary,last_name
FROM employees
WHERE job_id =(
	SELECT job_id
	FROM employees
	WHERE employee_id=141	
)    AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id=143
)

# 查找公司工资最少的员工的信息
SELECT *
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
)

# 查询最低工资大于50号部门最低工资的部门id及其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
)



#2、列子查询（多行子查询）
/*

多行操作符：
	in/not in 是否等于集合中的任意一个
	any/some 相当于数学里面的“存在”
		eg: salary > any(10,20,30) 等价于salary>10
	all: 相当于数学里的“全部”
		eg: salary > all(10,20,30) 等价于salary>30

*/

# 查询其他工种中比job_id为“IT_PROG"的工种任意一位员工工资低得员工得工号，姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE job_id<>"IT_PROG"	AND salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id="IT_PROG"
)

# 查询其他工种中比job_id为“IT_PROG"的工种所有一位员工工资低得员工得工号，姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE job_id<>"IT_PROG"	AND salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id="IT_PROG"
)

## 行子查询
# 查询工号最小且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
	FROM  employees
)

# select 后面的子查询

# 查询每个部门的员工个数

# 这样的话，如果有的部门没有员工就不会显示出来
SELECT department_id,COUNT
FROM employees
GROUP BY department_id;

# 这样根据部门来找员工逻辑上更合理
SELECT d.department_id,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
)
FROM departments d;


## from 后面的子查询

# 查询每个部门平均工资的工资等级
SELECT grade_level,agd.*
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) agd
INNER JOIN job_grades g
ON agd.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;


## exists 后面的子查询/相关子查询
# exists 括号里是一个完整的select语句，如果没查询结果就返回0，否则返回1


## 习题

# 查询各部门中工资比本部门平均工资搞得员工的员工信息
# 方法一：
SELECT last_name,salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.`department_id`=e2.`department_id`
)
# 方法二：
SELECT last_name,salary
FROM employees e1
INNER JOIN (
	SELECT AVG(salary) avg_salary,e2.department_id
	FROM employees e2
	GROUP BY e2.`department_id`
) e
ON e1.`department_id`=e.department_id
WHERE e1.`salary`>e.avg_salary;