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

【MySQL】（五）子查询

2022-05-29 126
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS PostgreSQL，集群系列 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 版子查询
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
云祁
目录
相关文章
程序三两行
|
7月前
|
SQL 关系型数据库 MySQL
mysql子查询、聚合函数
mysql子查询、聚合函数
程序三两行
91 0
wljslmz
|
4月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用子查询
【8月更文挑战第12天】
wljslmz
199 0
在 MySQL 中使用子查询
红目香薰
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——EXISTS(存在)
MySQL数据库子查询练习——EXISTS(存在)
红目香薰
64 1
红目香薰
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询——in多个数据查询的示例
MySQL数据库子查询——in多个数据查询的示例
红目香薰
38 1
程序员小海绵
|
3月前
|
SQL 缓存 关系型数据库
MySQL高级篇——关联查询和子查询优化
左外连接：优先右表创建索引，连接字段类型要一致、内连接：驱动表由数据量和索引决定、 join语句原理、子查询优化：拆开查询或优化成连接查询
程序员小海绵
286 4
MySQL高级篇——关联查询和子查询优化
一颗小谷粒
|
5月前
|
关系型数据库 MySQL 数据库
MySQL—子查询
MySQL—子查询
一颗小谷粒
48 1
三分钟热度的鱼
|
5月前
|
SQL Java 数据库
MySQL设计规约问题之为什么应尽量避免使用子查询，而可以考虑将其优化为join操作
MySQL设计规约问题之为什么应尽量避免使用子查询，而可以考虑将其优化为join操作
三分钟热度的鱼
84 0
红目香薰
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——单个数据的子查询
MySQL数据库子查询练习——单个数据的子查询
红目香薰
31 1
红目香薰
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
MySQL数据库子查询练习——DDL与DML语句(包括引入视频)
红目香薰
60 1
sky_qiyi
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库——多表查询（3）-自连接、联合查询、子查询
MySQL数据库——多表查询（3）-自连接、联合查询、子查询
sky_qiyi
284 1

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    云原生数据仓库产品使用合集之ADB MySQL湖仓版和 StarRocks 的使用场景区别，或者 ADB 对比 StarRocks 的优劣势
  • 4
    MySQL优势特点
  • 5
    mysql 数据类型
  • 6
    MySQL的innodb_flush_log_at_trx_commit配置值的设定
  • 7
    mysql合并查询(多张表) union 和 union all
  • 8
    Mysql数据库主从不同步解决案例
  • 9
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 10
    MySQL各种日期类型与整型（转）
  • 1
    【MySQL系列笔记】分库分表
    189
  • 2
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    157
  • 3
    服务器Linux系统配置mysql数据库主从自动备份
    203
  • 4
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    414
  • 5
    golang使用gorm操作mysql3，数据查询
    93
  • 6
    golang使用gorm操作mysql2
    55
  • 7
    golang使用gorm操作mysql1
    102
  • 8
    Hbase与MySQL对比，区别是什么？
    282
  • 9
    解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
    2146
  • 10
    navicat过期了，直接用idea连接mysql
    61

    • 相关课程

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

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    使用阿里云接口（API）进行身份证实名认证