DQL---子查询

DQL—子查询

7. 子查询

含义：
出现在其他语句中的内部的select语句，外部的查询语句为主查询或者外查询
分类：
按子查询位置：
select后面/from后面/where或having后面/exists后面
按结果集行列数：
标量子查询（结果集为一行一列）
列子查询（只有一列多行）
行子查询（只有一行多列）
表子查询（多行多列）

# (一)放在where或Having后面
/*
1. 标量子查询 2.列子查询 3. 行子查询
特点： 
 1. 放在小括号内
 2. 放在条件右侧
 3. 子查询先于主查询执行
*/
# 1.标量子查询
## 查询谁的工资比Abel工资高
SELECT last_name,salary
FROM employees
WHERE salary>(
 SELECT salary FROM employees WHERE last_name='Abel'
);

## 返回job_id与141号员工相同，salary比143号员工多的员工姓名，Job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
 SELECT job_id FROM employees WHERE employee_id=141
) AND salary>(
 SELECT salary FROM employees WHERE employee_id=143
);

## 返回公司工资最少的员工姓名，Job_id和工资
SELECT last_name,job_id,salary
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(最小), all(最大)
*/
## 返回location_id是1400 或者1700 的部门中的所有员工姓名
SELECT last_name,location_id
FROM employees AS e
JOIN departments AS d
ON e.`department_id`=d.`department_id`
WHERE d.`location_id` IN (1400,1700);

SELECT last_name
FROM employees
WHERE department_id IN (
 SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700)
);

## 返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的工号姓名job_id以及工资
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';

# 3. 行子查询（了解）
## 查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM employees 
WHERE (employee_id,salary)=(
 SELECT MIN(employee_id),MAX(salary)
 FROM employees
);

# (二) 放在select 后面的子查询
## 查询每个部门的员工个数
SELECT d.*,(
 SELECT COUNT(*)
 FROM employees AS e
 WHERE e.department_id=d.department_id
) 个数
FROM departments AS d;

#(三) 放在from后面
/*
将子查询的结果放在表中，必须起别名
*/
## 查询每个部门的平均工资的工资等级
SELECT ag.*,grade_level
FROM (
	SELECT department_id,AVG(salary) AS avg_sa
	FROM employees 
	GROUP BY department_id
) AS ag
JOIN job_grades AS jg
ON ag.avg_sa BETWEEN jg.lowest_sal AND jg.highest_sal;
## 查询工资比本部门工资高的员工信息
SELECT employee_id,last_name,salary,ag.*
FROM employees AS e
JOIN (
 SELECT department_id,AVG(salary) AS ag_sa
 FROM employees 
 GROUP BY department_id) ag
ON e.`department_id`=ag.department_id
WHERE salary> ag.ag_sa;


# (四) exists后面(相关子查询)
/*
语法：
 exists(完整的查询语句)
 返回布尔值（0/1）
*/
## 查询有员工的部门名
SELECT department_name
FROM departments AS d 
WHERE EXISTS(
 SELECT * FROM employees AS e
 WHERE e.`department_id`=d.`department_id`
);