10_MySQL DQL_子查询(嵌套的select)

#子查询
/*
含义:出现在其他语句中的select语句,称为子查询(内查询)
           内部嵌套其他select语句的查询,称为主查询(外查询)

特点:
1、子查询都会放在小括号内
2、单行操作符:>, <, =
3、多行操作符: in, any/some, all

分类:
按子查询出现的位置:
  select后面
    子查询的结果集只能是一行一列
  from后面
    支持表子查询
  where或having后面(筛选)------ 重点
    标量子查询 -- 重点
    列子查询 -- 重点
    行子查询
  exists后面(相关子查询)
    表子查询

按结果集的行列数不同:
  标量子查询(子查询的结果集只有一行一列)
  列子查询(子查询的结果集只有一列多行)
  行子查询(子查询的结果集有多行多列,主要为一行多列)
  表子查询(子查询的结果集一般为多行多列)
*/

# 实例
SELECT first_name FROM employees
WHERE department_id IN (
SELECT department_id FROM departments
WHERE location_id = 1700
);

#一、where或having后面
# 1. 标量子查询(单行)
# 2、列子查询(多行)
# 3、行子查询(多行多列)

# 1. 标量子查询
# 案例1:谁的工资比abel高?
# 1) 查询Abel的工资
SELECT salary FROM employees
WHERE last_name = 'Abel'
# 2) 查询员工姓名和工资,满足 salary>上一步的结果
SELECT last_name, salary
FROM employees
WHERE
salary > (
SELECT salary FROM employees
WHERE last_name = 'Abel'
);

# 案例2:查询job_id和141号员工相同,salary比143号员工高的员工姓名,job_id,工资
# 1) 查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
# 2)查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
# 3)查询员工姓名,job_id, salary,job_id和1相同,salary比2高
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
);

# 案例3: 返回公司工资最少的员工的last_name, job_id, salary

# 1) 查询最小工资
SELECT MIN(salary)
FROM employees;

# 2) 查询工资=1的员工
SELECT last_name,job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

# 案例4: 查询最低工资大于50号部门最低工资的部门id和最低工资
# 1)查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = 50;

# 2) 根据部门分组,查询每个部门的最低工资,然后过滤出比1高的
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id = 50
);

# 2. where或having后的列子查询

/*
多行操作符:
1)in/not in, 值是子查询结果中的任意一个
2) any|some, 和子查询结果中的某一个比较
3)all, 和子查询结果中的所有值比较
*/

# 案例1:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name, d.`location_id`
FROM employees AS e
LEFT JOIN departments AS d
ON e.`department_id` = d.`department_id`
WHERE d.`location_id` IN (1400,1700)
ORDER BY last_name;

# 1) 查询location_id为1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700);

# 2)查询部门号是1中任意一个的员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700)
)
ORDER BY last_name;

# 案例2:返回其他部门中比job_id为IT_PROG的部门任一工资低的员工号和姓名
# 1)查询job_id为IT_PROG的部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
# 2)
SELECT employee_id, last_name
FROM employees
WHERE job_id != 'IT_PROG'
AND salary < ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

# 3. where或having后的行子查询(结果集一行多列 或 多行多列)
# 案例:查询员工编号最小,并且工资最高的员工信息
# 1) 查询最大工资
SELECT MAX(salary)
FROM employees;
# 2) 查询最小员工编号
SELECT MIN(employee_id)
FROM employees;
# 3) 查询员工信息
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)
AND salary = (
SELECT MAX(salary)
FROM employees
);

# 三、from后的子查询
# 将子查询的结果集当做临时表

# 案例:查询每个部门的平均工资的工资等级
# 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

# 将1的结果集和job_grades连接,添加连接条件
SELECT avg_dept.*, g.`grade_level`
FROM (
   SELECT AVG(salary) AS ag,department_id
   FROM employees
   GROUP BY department_id
) AS avg_dept
INNER JOIN job_grades AS g
ON avg_dept.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

上一篇:vuex的理解


下一篇:Swift-常量变量