在mysql中有没有嵌套查询语句

2024-07-24 12:32:51

字体：大中小

来源：转载

供稿：网友

　　本文小编为大家详细介绍“mysql中有没有嵌套查询语句”，内容详细，步骤清晰，细节处理妥当，希望这篇“mysql中有没有嵌套查询语句”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

　　mysql中有嵌套查询语句，语法为“SELECT语句 WHERE 条件(SELECT语句)”；该语句也被称为子查询语句，能够在已有的查询语句中的where后面再嵌套一层查询语句，也即将内层查询结果当做外层查询参照的数据来使用。

　　本教程操作环境：windows10系统、mysql8.0.22版本、Dell G3电脑。

　　嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的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,'河南','sfddf123dd@163.com','13323564321'),

　　('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'),

　　('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'),

　　('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'),

　　('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'),

　　('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'),

　　('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'),

　　('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'),

　　('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'),

　　('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','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);

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

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

　　对于含比较运算符的嵌套查询来说，嵌套部分的查询语句只能返回一个值。那如果子查询返回多个值，就需要用到ANY或者ALL关键词了。通常，ANY / ALL 关键词经常和比较运算符连用，下面是6种比较运算符与ANY / ALL 关键词的搭配结果：mysql中有没有嵌套查询语句

　　# 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 != '管理系';

　　mysql中有没有嵌套查询语句

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

　　（3）含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字段），如果返回两个及以上字段信息则会出现语法错误。

　　（4）含EXISTS关键词的嵌套查询

　　EXISTS 关键词的作用和 IN关键词非常类似，不同的是，通过EXISTS关键词的嵌套查询返回的不是具体的值集合，而是满足条件的逻辑值（也就是True / False）。也就是说，EXISTS的作用就是“判断是否存在满足某种条件的记录”，如果存在这样的记录就返回真（True），如果不存在这样的记录就返回假（False）。

　　# 查询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 IN和NOT EXISTS

　　# 查询数学系和计算机系之外的学员信息

　　# 方法一

　　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适合于外表小而内表大的情况。

　　读到这里，这篇“mysql中有没有嵌套查询语句”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会。

（编辑：武林网）

上一篇：如何判断mysql数据库是否存在

下一篇：MySQL的存储过程实例解析