您的位置:首页 > 教程 > Mysql/MariaDB > MySQL中几种常见的嵌套查询详解

MySQL中几种常见的嵌套查询详解

2022-08-05 17:54:24 来源:易采站长站 作者:

MySQL中几种常见的嵌套查询详解

目录
几种常见的嵌套查询——以学员成绩为例含ANY或ALL关键词的嵌套查询含IN关键词的嵌套查询含EXISTS关键词的嵌套查询【补充】关于IN和EXISTS两个关键词还有两个延伸关键词NOT IN和NOT EXISTS附:其他使用方法和注意总结

几种常见的嵌套查询——以学员成绩为例

嵌套查询,也称为子查询,是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句,也就是把内层查询结果当做外层查询参照的数据表来使用。

在工作中,经常会遇见4种子查询,即含有比较运算符(>、>=、<、<=、=、!=)、IN关键词、ANY/ALL关键词以及EXISTS关键词的嵌套查询。下面我们以学员考试成绩为例,来学习一下这四种子查询的应用。

# 创建学员信息表 
CREATE TABLE stu_info
(
id INT AUTO_INCREMENT PRIMARY KEY, 
iname VARCHAR(20), 
gender CHAR(1), 
department VARCHAR(10), 
age TINYINT, 
province VARCHAR(10), 
email VARCHAR(50), 
mobilephone CHAR(11)
);
# 向学员表中插入数据 
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES 
('张勇','男','数学系',23,'河南',[email protected]','13323564321'), 
('王兵','男','数学系',25,'江苏',[email protected]','17823774329'), 
('刘伟','男','计算机系',21,'江苏',[email protected]','13834892240'), 
('张峰','男','管理系',22,'上海','102945328@QQ.com','13923654481'), 
('董敏','女','生物系',22,'浙江',[email protected]','13428439022'), 
('徐晓红','女','计算机系',24,'浙江',[email protected]','13720097528'), 
('赵伊美','女','数学系',21,'江苏',[email protected]','13417723980'), 
('王建国','男','管理系',24,'浙江',[email protected]','13768329901'), 
('刘清','女','统计系',23,'安徽',[email protected]','17823651180'), 
('赵家和','男','计算机系',28,'山东',[email protected]','13827811311');

# 创建学员成绩表 
CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); 
# 向成绩表中插入数据 
INSERT INTO stu_score VALUES 
(1,87,72,88), 
(3,90,66,72), 
(2,90,70,86), 
(4,88,82,76), 
(8,92,67,80), 
(10,88,82,89), 
(5,79,66,60), 
(7,91,78,90), 
(6,82,79,88), 
(9,85,70,85); 

# 1.查询年龄超过所有学员平均年龄的学员信息 
SELECT * FROM stu_info 
WHERE age >= avg(age); 
#需要注意的是Where后面不能使用聚合函数
#应该修改成
SELECT AVG(age) FROM stu_info;
SELECT * FROM stu_info
WHERE age>=23.3
#二合一 
# 1.查询年龄超过所有学员平均年龄的学员信息 
SELECT * FROM stu_info 
WHERE age >= (SELECT AVG(age) FROM stu_info);

# 2.查询年龄不低于所属系平均年龄的学员信息 
SELECT * FROM stu_info AS s1 
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2 
			  WHERE s1.department = s2.department);

使用含比较运算符的嵌套查询时,需要注意,比较运算符后面的子查询只能返回一个结果。

含ANY或ALL关键词的嵌套查询

对于含比较运算符的嵌套查询来说,嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值,就需要用到ANY或者ALL关键词了。通常,ANY>

# 1.查询非管理系中比管理系任意一个学员年龄小的学员信息 
SELECT * FROM stu_info 
WHERE age < ANY(SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
			AND department != '管理系';

这里的查询逻辑是这样的:首先查询管理系中学生的年龄(去重),得到的结果是22和24;然后查询出非管理系中年龄比22或24年龄小的学生信息(也就是年龄小于24的非管理系学生信息)。

# 2.查询非管理系中比管理系所有学员年龄大的学员信息 
SELECT * FROM stu_info 
WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
      AND department != '管理系';

这里的查询逻辑是这样的:首先查询管理系中学生的年龄(去重),得到的结果是22和24;然后查询出非管理系中年龄比22和24都大的学生信息(也就是年龄大于24的非管理系学生信息)。

含IN关键词的嵌套查询

当查询条件涉及某些已知的可枚举离散值的时候,我们就可以选择IN关键词来完成数据的提取。IN关键词有两种用法:

    将可枚举的离散值直接写在值列表中当离散值是基于其他表的筛选结果时,就可以使用嵌套查询,即把另一个表的查询语句块写在IN关键词后面的括号里。
# 1.查询数学系和计算机系的学员信息 
SELECT * FROM stu_info WHERE department IN('数学系','计算机系'); 
# 2.查询与张勇、刘伟同一个系的学员信息 
SELECT * FROM stu_info 
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('张勇','刘伟')); 
# 3.查询MySQL成绩大于85分的学员信息 
SELECT * FROM stu_info 
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);

需要注意的是,在使用IN关键词的嵌套查询的时候,嵌套部分只能返回一个字段的信息(比如上面的department字段或者id字段),如果返回两个及以上字段信息则会出现语法错误。

含EXISTS关键词的嵌套查询

EXISTS>

# 查询MySQL成绩大于85分的学员信息 
SELECT * FROM stu_info 
WHERE EXISTS
(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);

需要注意的是,使用EXISTS关键词的嵌套语句 WHERE与EXISTS关键词之间没有任何参数,这是因为EXISTS只需要一个参数,通常是在EXISTS右侧加一个子查询语句。此外,EXISTS后面的子查询中SELECT后面可以写表中任何一个字段或者星号或者一个常数,因为EXISTS后面的子查询只关心是否存在满足条件的记录。下面返回的结果都是一样:

【补充】关于IN和EXISTS两个关键词还有两个延伸关键词NOT>
# 查询数学系和计算机系之外的学员信息 
# 方法一 
SELECT * FROM stu_info 
WHERE department NOT IN('数学系','计算机系'); 
#方法二 
SELECT * FROM stu_info 
WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('数学系','计算机系') and stu_score.id = stu_info.id); 
# not exists的逻辑比较复杂,需要大家慢慢领会 
# 主要看not exists括号中的sql语句是否有结果,无结果:才会继续执行where条件;有结果:视为 where条件不成立。 
# 当子查询和主查询有关联条件时,相当于从主查询中去掉子查询的数据。

对于IN和EXISTS两个关键词,大多数情况下都可以相互替换,主要差别是使用效率问题,通常情况下采用EXISTS要比IN效率要高,但也要看实际情况具体使用:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

以上我们只是了解了where语句后面的子查询,除此之外,子查询还可以放在select语句、from语句、having语句后面。

附:其他使用方法和注意

除了上面这些还有很多很多,不过就不去细讲了,因为这些跟别的数据库差不多,只是为了给大家一个参考,提提就够了。

         SELECT (SELECT s1 FROM t2) FROM t1;
         SELECT (SELECT s2 FROM t1);

支持子查询的语法有:SELECT,INSERT,UPDATE,DELETE,SET和DO。

子查询可以使用任何普通查询中使用的关键词:如DINSTINCT,GROUP BY,LIMIT,ORDER BY,UNION,ALL,UNION ALL等。可以使用<,>, <=, >=, =, <>运算符进行比较,也可以使用ANY ,IN和SOME进行集合的匹配。

总结

到此这篇关于MySQL中几种常见的嵌套查询的文章就介绍到这了,更多相关MySQL嵌套查询内容请搜索易采站长站以前的文章或继续浏览下面的相关文章希望大家以后多多支持易采站长站!

如有侵权,请发邮件到 [email protected]

相关文章

  • mysql 5.7.30安装配置方法图文教程

    mysql 5.7.30安装配置方法图文教程

    之前把服务器里面的MySQL卸了重装,安装mysql时未做总结,换新电脑,补上安装记录,安装的时候,找了些网友的安装记录,发现好多坑 截个图,作为笔记,也正好留给需要的朋友们。
    2019-01-03
  • MySQL5.6免安装版环境配置图文教程

    MySQL5.6免安装版环境配置图文教程

    MySQL是一个小巧玲珑但功能强大的数据库,目前十分流行。但是官网给出的安装包有两种格式,一个是msi格式,一个是zip格式的。很多人下了zip格式的解压发现没有setup.exe,面对一堆文
    2019-01-04
  • Linux下安装mysql-8.0.20的教程详解

    Linux下安装mysql-8.0.20的教程详解

    ** Linux下安装mysql-8.0.20 ** 环境介绍 操作系统:CentOS 7 mysql下载地址:https://dev.mysql.com/downloads/mysql/ 下载版本:mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz 卸载mysql 查看是否安装过mysql,命令:fin
    2020-05-24
  • linux环境下安装mysql数据库的详细教程

    linux环境下安装mysql数据库的详细教程

    1.安装数据库 1)yum -y install mysql-server(简单) yum命令自动从网上寻找mysql服务资源,下载至本地并完成安装 2)也可以自己在网上下载mysql服务,通过xftp传输至Linux系统,自己安装(一般安
    2020-06-20
  • MySQL8.0.20安装教程及其安装问题详细教程

    MySQL8.0.20安装教程及其安装问题详细教程

    原文地址:https://blog.csdn.net/m0_46579864/article/details/105981304 官网下载MySQL的安装包 1.下载链接如下: MySQL8.0.20版本 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html 其他版本:MySQL8.0.16版本
    2020-05-10
  • MySQL对数据库操作(创建、选择、删除)

    MySQL对数据库操作(创建、选择、删除)

    MySQL 创建数据库 我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下: CREATE DATABASE 数据库名; 以下命令简单的演示了创建数据库的过程,数据名为 RUNOOB: [[email protected]]# mysql
    2020-07-01
  • Linux系统MySQL8.0.19快速安装配置教程图解

    Linux系统MySQL8.0.19快速安装配置教程图解

    一、环境介绍 Linux系统使用yum安装MySQL需要从网上下载MySQL的一系列组件,这个过程非常耗时且有下载中断的可能,如果想要快速安装MySQL,可以先在网上将MySQL的离线包下载下来传到系
    2020-02-27
  • mysql 加了 skip-name-resolve不能链接数据库问题的解决方法

    mysql 加了 skip-name-resolve不能链接数据库问题的解决方法

    mysql 加了 skip-name-resolve不能链接的问题, 要确认 MySql 是否采用过主机名的授权 在 MySql Server 的配置文件 My.ini 中,增加如下两行: [mysqld] skip-name-resolve 它将禁止 MySql Server 对外部连接进
    2019-01-04