MySQL 中几种不同的子查询实践

子查询：sub query；查询是在某个查询结果之上进行的(即，一条select语句内部包含了另外一条或多条select语句)。

【1】子查询分类

子查询有两种分类方式：按位置分类和按结果分类。

① 按位置分类

子查询(select语句)在外部查询(select语句)出现的位置

• 1.from子查询：子查询跟在from之后；

• 2.where子查询：子查询出现在where条件中；

• 3.exists子查询：子查询出现在exists里面；

• 4.in子查询：子查询出现在in 里面。

② 按结果分类

根据子查询得到的数据进行分类—理论上任何一个查询得到的结果都可以理解为一个二维表。

• 1.标量子查询：子查询得到的结果是一行一列；

• 2.列子查询：子查询得到的结构是一列多行；

• 3.行子查询：子查询的得到的结果是一行多列(也可以是多行多列)；

前三者出现的位置都是在where之后；

• 4.表子查询：子查询得到的结果是多行多列(位置在from之后)。

【2】子查询实例

① 标量子查询

select * from p_user_2 where id=(select id from p_user where age = 3 );

-- 子查询只能返回一条记录！！

② 列子查询

select * from p_user_2 where id in (select id from p_user);

-- 子查询返回一列数据，但是此时 不能再使用 'id =' !,需要用in

类似的还有 all，some，any。

肯定结果

Any === Some :

select * from p_user_2 where id = some (select id from p_user);

-- '= all'将会为null;

否定结果

select * from p_user_2 where id != some (select id from p_user);

-- id 不等于其中的某个，返回p_user_2全部结果;

select * from p_user_2 where id != any (select id from p_user);

-- id 不等于全部，返回p_user_2全部结果;

select * from p_user_2 where id != all (select id from p_user);

-- id 不等于子集中的每一个，返回id 不在p_user中的p_user_2的记录。等价于 not in

③ 行子查询

需求如下：查询用户表里面生日和薪水最高的；

① 使用两个标量子查询：

select * from s_user where 
user_birthday = (select MAX(user_birthday) from s_user ) and 
user_salary = (select MAX(user_salary) from s_user)

② 使用行子查询：

select * from s_user where 
-- 行元素
(user_birthday,user_salary)
= 
(select MAX(user_birthday), MAX(user_salary) from s_user)

-- 必须用括号，且行子查询不能再 使用表别名 ' as t'...

④ 表子查询

表子查询，返回的是多行多列的二维表，且位置在from之后，返回的结果当作二维表来使用。

如，查询每个班级中身高最高的学生信息：

select * from 
(select * from tb_student order by height desc) as t
group by class;

-- 作为二维表使用的查询结果必须有表别名;

下面sql错误：

select * from tb_student group by class order by height desc;

-- 先进行分组，然后对每组进行排序。这显然是错误的！！！
-- 分组的时候会按照表中顺序默认保存第一条(该条记录很可能不是该组最大值)，故再对分组排序毫无意义！！！

⑤ Exists子查询

Exists：用来判断某些条件是否满足(跨表使用)，exists是接在where之后，其返回的结果为0和1，等价于false和true。

测试如下：

SELECT   EXISTS(SELECT * from p_user_2 );

SELECT  NOT  EXISTS(SELECT * from p_user_2 );

-- 将返回  0,因为 NOT取反;

SELECT   EXISTS(SELECT * from p_user_2 where id =100 );

-- 将返回  0,因为不存在id=100。