MySQL的子查询

2021-03-24  本文已影响0人  程序员汪汪

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

分类

按出现位置

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

按结果集的行列

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

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

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

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

示例

WHERE或HAVING后面

  1. 标量子查询（单行单列）
  2. 列子查询（多行单列）
  3. 行子查询（多行多列）

特点

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

标量子查询

  1. 谁的工资比Abel搞？

    ① 先查询Abel的工资

    SELECT salary
FROM employees
WHERE last_name = 'Abel';

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

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

  2. 查询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
);

  3. 查询公司工资最少的员工的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
);

  4. 查询最低工资大于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
);

  5. 非法使用标量子查询

    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 和子查询返回的所有值比较

  1. 查询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)
);

  2. 查询其它工种中比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';

  3. 查询其它工种中比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';

行子查询

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

    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后面

仅仅支持标量子查询

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

    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;

  2. 查询员工号为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后面

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

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

    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，可用于筛选判断

  1. 查询有员工的部门名

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

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

    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替代

上一篇下一篇

猜你喜欢

热点阅读