AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL】（五）子查询

2022-05-29 172
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，高可用系列 2核4GB
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 【MySQL】（五）子查询

子查询

含义：


出现在其他语句中的select语句，称为子查询或内查询外部的查询语句，称为主查询或外查询


分类：


按子查询出现的位置：

select后面：
        仅仅支持标量子查询
   from后面：
        支持表子查询
    where或having后面：★重点
        标量子查询（单行子查询） √重点
        列子查询  （多行子查询） √重点
         行子查询(用的较少)
 exists后面（相关子查询）
        表子查询
exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)


按结果集的行列数不同：


标量子查询（结果集只有一行一列）又叫做单行子查询

列子查询（结果集只有一列多行）又叫做多行子查询

行子查询（结果集有一行多列、多行多列）

表子查询（结果集一般为多行多列[一行一列、一行多列等等都可以，无所谓，只要是一个查询结果就行]）


一、where或having后面

1、标量子查询（单行子查询）

2、列子查询（多行子查询）

3、行子查询（多列多行）


特点：


①子查询放在小括号内

②子查询一般放在条件的右侧

③标量子查询，一般搭配着单行操作符使用


>< >= <= = <>


列子查询，一般搭配着多行操作符使用


in、any / some、all


IN/NOT IN 等于列表中的任意一个

ANY|SOME 和子查询返回的某一个值比较

ALL 和子查询返回的所有值比较

any表示有任何一个满足就返回true，all表示全部都满足才返回true


④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果


代码案例如下：

#子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
  select后面：
  仅仅支持标量子查询
  from后面：
  支持表子查询
  where或having后面：★重点
  标量子查询（单行子查询） √重点
  列子查询  （多行子查询） √重点
  行子查询(用的较少)
  exists后面（相关子查询）
  表子查询
exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)
按结果集的行列数不同：
  标量子查询（结果集只有一行一列）又叫做单行子查询
  列子查询（结果集只有一列多行）又叫做多行子查询
  行子查询（结果集有一行多列、多行多列）
  表子查询（结果集一般为多行多列[一行一列、一行多列等等都可以，无所谓，只要是一个查询结果就行]）
*/
#########################################
#select emp.* from (select last_name,salary from employees) emp;
#SELECT last_name,salary FROM (SELECT last_name,salary FROM employees) emp;
#SELECT last_name FROM (SELECT last_name,salary FROM employees) emp;
#select (select count(*) from employees);
#select * from employees where exists (select * from employees where salary > 23000) and department_id = 90;
#SELECT * FROM employees WHERE department_id = 90;
#########################################
#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
IN/NOT IN 等于列表中的任意一个
ANY|SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
any表示有任何一个满足就返回true，all表示全部都满足才返回true
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
SELECT DATEDIFF('2019/06/06', NOW());
SELECT DATEDIFF('2019-06-06', NOW());
#1.标量子查询★(结果集只有一行一列)
#案例1：谁的工资比 Abel 高?
SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;
#案例2：返回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) ;
#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;
#案例4：查询最低工资大于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) ;
#非法使用标量子查询
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) ;
#列子查询（结果集只有一列多行）又叫做多行子查询
#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;
#或者使用ANY(把in换成ANY)，= ANY和上面的IN效果一样
SELECT 
  last_name 
FROM
  employees 
WHERE department_id = ANY 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;
#NOT IN 
SELECT 
  last_name 
FROM
  employees 
WHERE department_id NOT IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;  
#<> ALL和上面的NOT IN 效果一样
SELECT 
  last_name 
FROM
  employees 
WHERE department_id <> ALL 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;   
#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < ANY 
  (SELECT DISTINCT
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
#使用SOME关键字
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < SOME 
  (SELECT DISTINCT
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;  
#或者
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary <  
  (SELECT 
    MAX(salary)
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
#案例3：返回其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < ALL  
  (SELECT DISTINCT
    salary
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
#或者
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary <   
  (SELECT 
    MIN(salary)
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
#3、行子查询（结果集一行多列或多行多列）用的比较少
#案例：查询员工编号最小并且工资最高的员工信息
#这种用的比较少
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 emp 
  WHERE emp.`department_id` = d.`department_id`) AS 个数 
FROM
  departments d ;
#用连接也可以
SELECT 
  dep.*,
  (SELECT COUNT(*) FROM employees WHERE employees.`department_id` = dep.`department_id`) AS 个数
FROM 
  departments dep 
LEFT OUTER JOIN 
  employees emp 
ON dep.`department_id` = emp.`department_id`;
#用连接也可以
SELECT 
  DISTINCT dep.department_id, department_name, dep.manager_id, location_id,  
  (SELECT COUNT(*) FROM employees WHERE employees.`department_id` = dep.`department_id`) AS 个数
FROM 
  departments dep 
LEFT OUTER JOIN 
  employees emp 
ON dep.`department_id` = emp.`department_id`;
#案例2：查询员工号=102的部门名
SELECT 
  department_name 部门名
FROM
  departments 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  WHERE employee_id = 102) ;
#或者
SELECT 
  (SELECT 
    department_name 
  FROM
    departments dep 
  WHERE dep.department_id = emp.`department_id`) AS 部门名
FROM
  employees emp 
WHERE emp.`employee_id` = 102 ;
##select后面：仅仅支持标量子查询(放在select后面的子查询结果只能是一行一列)
SELECT 
  (SELECT 
    department_name
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.department_id = e.department_id 
  WHERE e.employee_id = 102) 部门名 ;
#
SELECT 
    department_name AS 部门名
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.department_id = e.department_id 
  WHERE e.employee_id = 102;
#有NULL的情况(子查询中的空值问题)
#能查到数据
SELECT * FROM employees WHERE department_id IN (90); 
#能查到数据
SELECT * FROM employees WHERE department_id IN (90, NULL);  
#能查到数据
SELECT * FROM employees WHERE department_id IN ('90');
#能查到数据
SELECT * FROM employees WHERE department_id IN ('90', NULL);
#能查到数据(子查询的结果中包含了null值，但是外查询把null忽略掉了)
SELECT * FROM employees WHERE commission_pct IN (
SELECT DISTINCT commission_pct FROM employees);
#查不到数据
SELECT * FROM employees WHERE commission_pct IN (NULL);
#只能查到commission_pct等于0.15的记录，不能查到commission_pct为null的记录
SELECT * FROM employees WHERE commission_pct IN (0.15, NULL);
#查不到数据
SELECT * FROM employees WHERE commission_pct NOT IN (0.15, NULL);
#能查到数据
SELECT * FROM employees WHERE commission_pct NOT IN (0.15);
#查不到数据(因为子查询中,commission_pct字段有null值)
SELECT * FROM employees WHERE commission_pct NOT IN (
SELECT commission_pct FROM employees WHERE employee_id > 100 AND employee_id < 160
);
#能查到数据(因为子查询中,把commission_pct字段为null的筛选掉了)
SELECT * FROM employees WHERE commission_pct NOT IN (
SELECT commission_pct FROM employees WHERE employee_id > 100 AND employee_id < 160 AND commission_pct IS NOT NULL
);
#可以查到数据(子查询的结果中有null值)
SELECT * FROM employees WHERE  commission_pct > ALL(
SELECT commission_pct FROM employees WHERE employee_id = 100 OR employee_id = 155);
#可以查到数据(子查询的结果中有null值)
SELECT * FROM employees WHERE EXISTS (
SELECT commission_pct FROM employees WHERE employee_id = 100 OR employee_id = 155);
#
#三、from后面
/*
特别注意：将子查询结果充当一张表，要求必须起别名
*/
#
SELECT * FROM job_grades gra;
#案例：查询每个部门的平均工资的工资等级
#特别注意：将子查询结果充当一张表，要求必须起别名
SELECT 
  avg_dep.*,
  gra.`grade_level` 
FROM
  (SELECT 
    AVG(salary) avg_sal,
    department_id 
  FROM
    employees 
  GROUP BY department_id) avg_dep 
  INNER JOIN job_grades gra 
    ON avg_dep.avg_sal BETWEEN gra.`lowest_sal` 
    AND gra.`highest_sal` ;
#四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
#查询结果为1
SELECT EXISTS (SELECT employee_id FROM employees) AS 结果;
#查询结果为1
SELECT EXISTS (SELECT * FROM employees) AS 结果;
#查询结果为1
SELECT EXISTS (SELECT last_name, salary FROM employees) AS 结果;
#查询结果为0
SELECT EXISTS (SELECT employee_id FROM employees WHERE salary = 80000) AS 结果;
#查询结果为0
SELECT EXISTS (SELECT * FROM employees WHERE salary = 80000) AS 结果;
#查询结果为1
SELECT NOT EXISTS (SELECT * FROM employees WHERE salary = 80000) AS 结果;
/*
exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)
*/
#案例1：查询有员工的部门名
#使用EXISTS
SELECT 
  department_name 
FROM
  departments dep 
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees emp 
  WHERE dep.`department_id` = emp.`department_id`) ;
#使用in
SELECT 
  department_name 
FROM
  departments dep 
WHERE dep.`department_id` IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    employees);
#    
USE girls;    
#案例2：查询没有女朋友的男神信息
SELECT * FROM boys;
SELECT * FROM beauty;
INSERT INTO boys VALUES (5, '乔峰', 200);
#使用NOT EXISTS
SELECT 
  * 
FROM
  boys bo 
WHERE NOT EXISTS 
  (SELECT 
    * 
  FROM
    beauty b 
  WHERE bo.`id` = b.`boyfriend_id`) ;
#使用in
SELECT 
  * 
FROM
  boys bo 
WHERE bo.`id` NOT IN 
  (SELECT DISTINCT 
    b.`boyfriend_id` 
  FROM
    beauty b) ;
#题目
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary FROM employees emp
WHERE emp.`department_id` = (
SELECT `department_id` FROM employees WHERE last_name = 'Zlotkey');
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id, last_name, salary FROM employees emp
WHERE emp.`salary` > (
SELECT AVG(salary) FROM employees);
#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#######先查一下各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#######
#
SELECT employee_id, last_name, salary, emp01.`department_id` FROM employees emp01
WHERE emp01.`salary` > (
SELECT AVG(salary) FROM employees emp02 GROUP BY department_id
HAVING emp01.`department_id` = emp02.`department_id`);
#或者
SELECT employee_id, last_name, salary, emp01.`department_id` FROM employees emp01
WHERE emp01.`salary` > (
SELECT AVG(salary) FROM employees emp02 
WHERE emp01.`department_id` = emp02.`department_id`
GROUP BY department_id);
#或者
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
  SELECT AVG(salary) ag,department_id
  FROM employees
  GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name FROM employees WHERE department_id IN (
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');
#5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees WHERE department_id IN (
SELECT DISTINCT department_id FROM departments WHERE location_id = 1700);
#或者
SELECT employee_id FROM employees WHERE department_id = ANY (
SELECT DISTINCT department_id FROM departments WHERE location_id = 1700);
#6.查询管理者是K_ing的员工姓名和工资
SELECT last_name, salary FROM employees WHERE manager_id IN (
SELECT employee_id FROM employees WHERE last_name = 'K_ing');
#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT 
  CONCAT(first_name, last_name) AS "姓.名" 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;
#用IN也可以
SELECT 
  CONCAT(first_name, last_name) AS "姓.名" 
FROM
  employees 
WHERE salary IN 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
关键词：
云数据库 RDS MySQL 版子查询
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
云祁
目录
相关文章
wljslmz
|
SQL 关系型数据库 MySQL
在 MySQL 中使用子查询
【8月更文挑战第12天】
wljslmz
653 0
在 MySQL 中使用子查询
红目香薰
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——EXISTS(存在)
MySQL数据库子查询练习——EXISTS(存在)
红目香薰
207 1
红目香薰
|
SQL 关系型数据库 MySQL
MySQL数据库子查询——in多个数据查询的示例
MySQL数据库子查询——in多个数据查询的示例
红目香薰
135 1
威哥爱编程
|
11月前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好，我是V哥。上周与朋友讨论数据库子查询问题，深受启发。为此，我整理了10个案例，详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例，帮助大家在实际开发中选择更高效的查询方式，提升系统性能。关注V哥，一起探讨技术，欢迎点赞支持！
威哥爱编程
551 5
程序员小海绵
|
SQL 缓存 关系型数据库
MySQL高级篇——关联查询和子查询优化
左外连接：优先右表创建索引，连接字段类型要一致、内连接：驱动表由数据量和索引决定、 join语句原理、子查询优化：拆开查询或优化成连接查询
程序员小海绵
1536 4
MySQL高级篇——关联查询和子查询优化
一颗小谷粒
|
关系型数据库 MySQL 数据库
MySQL—子查询
MySQL—子查询
一颗小谷粒
119 1
红目香薰
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——单个数据的子查询
MySQL数据库子查询练习——单个数据的子查询
红目香薰
98 1
红目香薰
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
红目香薰
184 1
sky_qiyi
|
SQL 关系型数据库 MySQL
MySQL数据库——多表查询（3）-自连接、联合查询、子查询
MySQL数据库——多表查询（3）-自连接、联合查询、子查询
sky_qiyi
1349 1
三分钟热度的鱼
|
SQL Java 数据库
MySQL设计规约问题之为什么应尽量避免使用子查询，而可以考虑将其优化为join操作
MySQL设计规约问题之为什么应尽量避免使用子查询，而可以考虑将其优化为join操作
三分钟热度的鱼
238 0

热门文章

最新文章

  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
  • 2
    MySQL中日期和时间函数的使用指南
  • 3
    MySQL · 引擎特性 · 像NOSQL那样使用MySQL
  • 4
    Mysql 查询最近90天数据
  • 5
    MySQL replace into的使用细则
  • 6
    Centos安装docker安装mysql安装redis linux配置docker镜像加速
  • 7
    【巡检问题分析与最佳实践】RDS MySQL 实例IO高问题
  • 8
    CentOS7下mysql5.6修改默认编码
  • 9
    win7中mysql安装
  • 10
    MySQL 数据库的启动与关闭
  • 1
    【赵渝强老师】使用mydumper备份MySQL
    243
  • 2
    【赵渝强老师】使用select...into outfile语句备份MySQL
    399
  • 3
    【赵渝强老师】使用mysqldump备份MySQL
    250
  • 4
    MySQL数据库上云迁移
    176
  • 5
    数据库运维：mysql 数据库迁移方法-mysqldump
    1016
  • 6
    Super MySQL｜揭秘PolarDB全异步执行架构，高并发场景性能利器
    463
  • 7
    MySQL报错：未知系统变量'tx_isolation'及隔离级别查询
    270
  • 8
    MySQL 常用函数
    481
  • 9
    MySQL 5.6/5.7 DDL 失败残留文件清理指南
    346
  • 10
    MySQL字符串拼接方法全解析
    604

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    oss云网关配置