MySQL——嵌套查询

嵌套查询（把内层的查询结果作为外层的查询条件）

语法格式：

SELECT 字段名列表FROM 表名 WHERE 条件（SELECT ... FROM ... WHERE ...）；

1. 带有比较运算符的子查询

当用户能确切知道内层查询返回的是单值时，可以用>,<,=,<=,>=,!=等比较云端福
例子：

找出大于平均年龄的用户，显示用户名和年龄
SELECT name,age FROM userlist
WHERE age<(WHERE SELECT AVG(age) FROM userlist);

2. 带有IN谓词的子查询

WHERE 字段名 IN（值1，值2，...）
值为数字或字符，可以是一个或者多个
案例:
两个表，一个用户表，一个排名表（只包含用户id）

查询出中1等奖的用户姓名
SELECT name FROM userlist
WHERE userid IN(SELECT userid FROM scorelist WHERE prize=1);

单表使用分组聚合

查找出每个国家年龄最高的用户(单表使用分组聚合)
SELECT nation,name,age FROM userlist
WHERE (nation,age) IN(SELECT country,MAX(age) FROM userlist GROUP BY country);

3. 带有ANY或ALL谓词的子查询

当子查询结果为多值且外层查询涉及到与子查询结果中的某个值或所有值进行比较时，使用ANY或ALL谓词必须同时使用比较运算符

符号	语义
>ANY/>ALL	大于子查询结果中的某个值/所有值
<ANY/<ALL	小于子查询结果中的某个值/所有值
>=ANY/>=ALL	大于等于子查询结果中的某个值/所有值
<=ANY/<=ALL	大于等于子查询结果中的某个值/所有值
=ANY/=ALL	等于子查询结果中的某个值/所有值（通常没有实际意义）
!=ANY/!=ALL	不等于等于子查询结果中的某个值/任何一个值

案例：

查询其他国家中比中国某一用户年龄小的用户名和年龄
SELECT name,age FROM userlist
WHERE nation!='中国' AND < ANY(SELECT age FROM userlist WHERE nation='中国');
使用聚合函数的写法
SELECT name,age FROM userlist
WHERE nation!='中国' AND < (SELECT MAX(age) FROM userlist WHERE nation='中国');

查询其他国家中比中国用户所有年龄小的用户姓名和年龄
SELECT name,age FROM userlist
WHERE nation!='中国' AND < ALL(SELECT age FROM userlist WHERE nation='中国');
使用聚合函数的写法
SELECT name,age FROM userlist
WHERE nation!='中国' AND < (SELECT MIN(age) FROM userlist WHERE nation='中国');

注意：使用聚合函数实现子查询通常比直接用ANY或ALL的查询效率要高

4. 带有EXIT谓词的子查询

不返回任何数据，只返回true和false
案例：

查询所有选择1号产品的用户姓名
SELECT name FROM userlist
WHERE EXIST (SELECT * FROM productlist WHERE productlist.userid=userlist.userid AND productlist.productid=1);