MySQL的子查询

作者: 程序员汪汪 | 来源:发表于2021-03-24 21:58 被阅读0次

嵌套在其他语句内部的SELECT语句称为子查询或内查询，外面的语句可以是INSERT、UPDATE、DELETE、SELECT等，一般SELECT作为外面语句较多，外面如果为SELECT语句，则次语句称为外查询或主查询

分类

按出现位置

SELECT 后面：
        仅仅支持标量子查询
FROM 后面：
        表子查询
WHERE 或 HAVING后面：
        标量子查询
        列子查询
        行子查询
EXISTS 后面：
        标量子查询
        列子查询
        行子查询
        表子查询

按结果集的行列

标量子查询（单行子查询）：结果集为一行一列

列子查询（多行子查询）：结果集为多行一列

行子查询：结果集为多行多列

表子查询：结果集为多行多列

示例

WHERE或HAVING后面

标量子查询（单行单列）
列子查询（多行单列）
行子查询（多行多列）

特点

子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符（<、>、<=、>=、=、<>）使用
列子查询，一般搭配着多行操作符（IN、ANY/SOME、ALL）使用
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

标量子查询

谁的工资比Abel搞？

① 先查询Abel的工资

SELECT salary
FROM employees
WHERE last_name = 'Abel';

② 查询员工的信息，满足salary > ①的结果

SELECT *
FROM employees
WHERE salary > (
 SELECT salary
 FROM employees
 WHERE last_name = 'Abel'
);

查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

① 查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id = 141;

② 查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id = 143;

③ 查询员工的姓名，job_id和工资，要求job_id=①并且salary > ②

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 MIN(salary)
FROM employees;

② 查询last_name, job_id，要求salary = ①

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

查询最低工资大于50号部门最低工资的部门的id和其最低工资

① 查询50号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id = 50;

② 查询每个部门的最低工资

SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id;

③ 在②的基础上，筛选MIN(salary) > ①

SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
 SELECT MIN(salary)
 FROM employees
 WHERE department_id = 50
);

非法使用标量子查询
```
SELECT MIN(salary),department_id 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary)>(     
    SELECT  salary     
    FROM employees     
    WHERE department_id = 250 
);

SELECT MIN(salary),department_id 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary)>(     
    SELECT  salary     
    FROM employees     
    WHERE department_id = 50 
);
```
这里250号部门不存在（员工表没人在250号部门），是查不出结果的，没有结果就不是标量子查询(结果不是一行一列)，所以这样非法的，mysql虽然没有报错，但是没有得到我们想要的结果。而50号部门，查出的是多行一列，属于列子查询，这也是非法的，会报错。

列子查询

操作符	含义
IN\|NOT IN	等于或不等于列表中的任意一个
ANY\|SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

查询location_id是1400或1700的部门中的所有员工名

① 查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id
FROM departments
WHERE 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以及salary

① 查询job_id为“IT_PROG”部门工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

② 查询员工号、姓名、job_id、以及salary，salary<(①)的任意一个

SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY(
 SELECT DISTINCT salary
 FROM employees
 WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

或者(小于最大值，就肯定满足任意一个)

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

查询其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

或者（小于最小的）

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN( salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

行子查询

查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

或者

SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

SELECT后面

仅仅支持标量子查询

查询每个部门的员工个数（注意有些部门没有员工）

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

或者（使用外连接）

SELECT
    d.*,
    count( e.employee_id ) 个数 
FROM
    employees e
    RIGHT JOIN departments d ON e.department_id = d.department_id 
GROUP BY
    d.department_id;

查询员工号为102的员工所在的部门名

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

或者

SELECT department_name,e.department_id
    FROM departments d
    INNER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102;

FROM后面

将子查询结果充当一张表，要求必须起别名

查询每个部门的平均工资的工资等级

SELECT  ag_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

EXISTS后面(相关子查询)

判断子查询是否存在结果，存在返回1，不存在返回0，可用于筛选判断

查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id`=e.`department_id`
);

查询没有女朋友的男神信息

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.`id`=b.`boyfriend_id`
);

或者

SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)

能用EXISTS的地方就都能用IN替代

网友评论

本文标题：MySQL的子查询

本文链接：https://www.haomeiwen.com/subject/daochltx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！