mysql 先查询再关联 mysql 关联查询子查询过程

转载

云端筑梦师 2023-08-31 15:08:36

文章标签 mysql 先查询再关联 p2p 网络协议网络子查询 文章分类 MySQL 数据库

1.相关子查询执行流程

如果子查询的执行依赖于外部查询,通常情况下,子查询的表用到了外部的表,并进行了条件关联.因此每执行一次外查询,子查询都要重新计算一次,这样的子查询称为关联子查询
关联子查询按照一行接一行的顺序查询,查询的每一行都执行一次子查询

2.代码示例

#练: 查询员工表中工资大于本部门平均工资的员工last_name,salary,department_id

方式一: 相关子查询

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
              SELECT AVG(salary)
              FROM employees e2
              WHERE department_id = e1.department_id
          );

方式二: 在FROM中声明子查询,即将子查询的结果看做一张新的表

SELECT *
FROM employees e,(
               SELECT department_id,AVG(salary) avg_sal
               FROM employees
               GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

使用from型的子查询时: 子查询需要使用()引起来,并且要给这个子查询取别名,把它当成一张'临时的虚拟表'来使用

在ORDER BY 中使用子查询:

#练: 查询员工id,salary,按照department_name排序

SELECT employee_id,salary
FROM employees e
ORDER BY(
        SELECT department_name
        FROM departments d
        WHERE e.department_id = d.department_id
      );

3.EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行
如果在子查询中不存在满足条件的行:

条件返回FALSE
继续在子查询中查找

如果在子查询中存在满足条件的行:

不再子查询中继续查找
条件返回TRUE

NOT EXISTS 关键字就用来表示如果不存在,,返回TRUE ,否则返回FALSE

#练: 查询公司管理者的employee_id,last_name,job_id,department_id

方式1: 自连接

SELECT DISTINCT employee_id,last_name,job_id,department_id
FROM employees e JOIN employees m
ON e.manager_id = m.employee_id;

方式2: 子查询

SELECT DISTINCT employee_id,last_name,job_id,department_id
FROM employees e
WHERE employee_id IN (
                    SELECT DISTINCT manager_id
                    FROM employees 
                   );

方式3: 使用EXISTS

SELECT DISTINCT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
            SELECT *
            FROM employees e2
            WHERE e1.employee_id = e2.manager_id
           );

4. 相关更新

相关更新即使用相关子查询依据一个表中数据更新另一个表的数据

#练: 在employees中增加一个department_name字段,数据为员工对应部门名称

#1)

ALTER TABLE employees
ADD (department_name VARCHAR(14));

#2)

UPDATE employees e
SET department_name = (
                     SELECT department_name
                     FROM departments d
                     WHERE e.department_id = d.department_id
                     );

5.相关删除

相关删除即使用相关子查询依据一个表中的数据删除另外一个表中的数据

#练: 删除表employees中,其与employees_history表共有的数据

DELETE FROM employees e
WHERE employee_id IN (
                     SELECT employee_id
                     FROM employee_history
                     WHERE employee_id  = e.employee_id
                    );