数据库基础篇《9. 子查询》

TheFanXY

已于 2023-04-24 01:25:18 修改

阅读量2.3k

点赞数 1

文章标签：数据库 java 开发语言

于 2023-04-22 15:12:29 首次发布

本文链接：https://blog.csdn.net/weixin_44981126/article/details/130305023

版权

本文详细介绍了数据库查询中的子查询技术，包括单行子查询、多行子查询、相关子查询和不相关子查询的使用，涵盖各种比较操作符和场景应用，如在HAVING和CASE语句中的使用，以及如何处理空值问题。同时，文章提供了丰富的实例练习，帮助读者加深理解和掌握子查询的实战技巧。

摘要由CSDN通过智能技术生成

分类方式1：我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

分类方式2：我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询

2. 单行子查询

2.1 单行比较操作符

2.2 代码示例

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

题目：返回公司工资最少的员工的last_name,job_id和salary

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

4.3 EXISTS 与 NOT EXISTS关键字

1.查询和Zlotkey相同部门的员工姓名和工资

2.查询工资比公司平均工资高的员工的员工号，姓名和工资

3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

5.查询在部门的location_id为1700的部门工作的员工的员工号

6.查询他的管理者是King的员工姓名和工资

7.查询工资最低的员工信息: last_name, salary

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

9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

10.查询平均工资最高的 job 信息

11. 查询平均工资高于公司平均工资的部门有哪些?

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

13. 各个部门中最高工资中最低的那个部门的最低工资是多少?

14. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

16. 选择所有没有管理者的员工的last_name

17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

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

19.查询每个部门下的部门人数大于 5 的部门名称

1. 需求分析与问题解决

1.1 实际问题

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

1.2 子查询的基本使用

子查询的基本语法结构：

1.3 子查询的分类

分类方式1：我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

分类方式2：我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询

2. 单行子查询

2.1 单行比较操作符

2.2 代码示例

题目：查询工资大于 149 号员工工资的员工的信息

题目：返回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);

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
    (SELECT MIN(salary)
     FROM employees);

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

实现方式 1 ：不成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
    (SELECT manager_id
    FROM employees
    WHERE employee_id IN (174,141))
AND department_id IN
    (SELECT department_id
    FROM employees
    WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

实现方式2：成对比较

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
    (SELECT manager_id, department_id
    FROM employees
    WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

2.3 HAVING 中的子查询

首先执行子查询。

向主查询中的 HAVING 子句返回结果。

题目：查询最低工资大于 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.4 CASE中的子查询

在 CASE 表达式中使用单列子查询：

题目：显式员工的 employee_id,last_name 和 location 。其中，若员工 department_id 与 location_id 为 1800 的 department_id 相同，则 location 为 ’Canada’ ，其余则为 ’USA’ 。

SELECT employee_id, last_name,(CASE department_id WHEN
               (SELECT department_id 
                FROM departments
                WHERE location_id = 1800)
                THEN 'Canada' ELSE 'USA' END) location
FROM employees;

2.5 子查询中的空值问题

SELECT last_name, job_id
FROM employees
WHERE job_id =
            (SELECT job_id
             FROM employees
             WHERE last_name = 'Haas');

2.5 非法使用子查询

SELECT employee_id, last_name
FROM employees
WHERE salary =
            (SELECT MIN(salary)
             FROM employees
             GROUP BY department_id);

3. 多行子查询

也称为集合比较子查询

内查询返回多行

使用多行比较操作符

3.1 多行比较操作符

3.2 代码示例

题目：返回其它 job_id 中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号、姓名、 job_id 以及 salary

题目：返回其它 job_id 中比 job_id 为 ‘IT_PROG’ 部门所有工资都低的员工的员工号、姓名、 job_id 以及 salary

题目：查询平均工资最低的部门id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
                        SELECT MIN(avg_sal)
                        FROM (
                             SELECT AVG(salary) avg_sal
                             FROM employees
                             GROUP BY department_id
                             ) dept_avg_sal
                     );

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
                            SELECT AVG(salary) avg_sal
                            FROM employees
                            GROUP BY department_id
                          );

3.3 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
                            SELECT manager_id
                            FROM employees
                            );

4. 相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

4.2 代码示例

SELECT last_name,salary,e1.department_id
FROM employees e1,
    (SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP    
     BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

from型的子查询：子查询是作为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`
);

题目：若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于 2 ，输出这些相同 id 的员工的 employee_id,last_name 和其 job_id

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
            FROM job_history
            WHERE employee_id = e.employee_id);