12-mysql数据查询语言(DQL)-子查询

子查询

基本含义

一个查询，通常就是一个select语句（即出现一次select关键字）。

一个select语句最终执行的结果就是一个“数据集”(数据表)。该数据集(表)可大可小，可一行可多行，可一列可多

列，最简情况就是一行一列，极端情况就是空数据(没有数据)。

子查询，就是一个select语句中的某些位置（select部分，from部分，where部分等）上，又出现了“内部select语

句”，此内部select语句就是子查询。相应的，外部的select语句就是主查询（也可以称为外部查询）。

一个主查询可以使用什么样的子查询，跟其所需要使用的位置和运算符有关。

语法：

select 字段或表达式或(子查询1) [as 别名] from 表名 或(子查询2) where 字段或表达式或(子查询3)的条件判断

注意：一个子查询的查询结果要符合其所放在的主查询所在位置的数据要求。每个位置所放置的子查询结果，应该符合该位置的数据需求。

通常：

子查询1应该是一个“数据结果”。

子查询2可以是“任意结果”，此位置的查询结果，通常作为数据源，可以给一个别名。

子查询3可以是一个数据或一列数据，甚至是一行数据。

子查询按返回结果分类

表子查询：

一个子查询返回的结果，理论上是“多行多列”的时候。此时可以当做一个“表”来使用，通常是放在from后面。

行子查询：

一个子查询返回的结果，理论上是“一行多列”的时候。此时可以当做一个“行”来使用，通常放在“行比较语法”中。

列子查询 ：

一个子查询返回的结果，理论上是“多行一列”的时候。此时可以当做“多个值”使用，类似这种：(5, 17, 8, 22)。

标量子查询：

一个子查询返回的结果，理论上是“一行一列”的时候。此时可以当做“一个值”使用，类似这种：

select 5 as c1; 

# 或
select ...where a = 17;

# 或
select ... where b > 8;

按使用场合分类

作为主查询的结果数据：

这里子查询应该只有一个数据（一行一列，标量子查询）

select c1,(select f1 from tab2) as f11 from tab1;

作为主查询的条件数据：

这里子查询可以是多个数据（多行一列，列子查询）

select c1 from tab1 where c1 in (select f1 from tab2); 

作为主查询的来源数据：

这里子查询可以是任意查询结果（表子查询）

select c1 from (select f1 as c1, f2 from tab2) as t2; 

常见子查询及关键字

比较运算符中使用子查询：

语法：

操作数  比较运算符 (标量子查询);

# 操作数通常是一个字段

含义：判断该操作数（字段）的值是否满足该比较运算符所设定的比较结果。

其实就是相当于最简单的这种形式：　id > 5;

此时子查询的结果应该是一个值（标量子查询）。

操作数可以是一个字段名（通常情形）或一个具体字面值。

... where 'abc' = (select c1 from tab2 limit 1);

... where  age > (select max(age) from tab2 where jiguan='北京');

示例：

数据源：

需求：找出所有大于平均价的商品。

第一步：找平均价：

select avg(price) as avg_price  from  product;

第二部：找商品：

select * from product  where price  > 4287.7; 

select * from product  where price 〉（select avg(price) as avg_price  from  product）;

使用in的子查询

in的基本语法形式：

where  操作数  in (值1，值2， ....);

则in子查询就是：

where  操作数  in (列子查询);

含义：表示该操作数（字段值） 等于该子查询的其中任意一个值，就算满足条件。

此时子查询应该是多行一列值（列子查询），其相当于： 操作数 in (值1，值2，…)。

比如这样的数据表结构：

要找出其中商品数量超过2个的所有类别的信息，语句如下：

select * from product_type where protypeID in(select protypeid from product group by protypeid having count(protypeid) >2);

示例：

找出所有带“电”字的类别的产品

第一步：找出所有带“电”字的类别ID：

第二步：根据结果找出这些类别的产品：

select * from product  where  protype_id in (1, 3);  

select * from product  where  protype_id in (select protype_id from product_type where protype_name like '%电%');

使用any的子查询

使用形式：

where  操作数  比较运算符  any (列子查询);

含义：如果子查询返回的数据中有其中任何一个值，该比较结果为TRUE，则最终结果是TRUE。

表示该操作数的值只要跟列子查询的任意一个值满足给定的比较运算，就算满足了条件。

说明：

1. 操作数通常仍然是字段名。
2. 比较运算符就是常规的 > >= ＜ <= ＝　＜＞。
3. 列子查询也可以是标量子查询，都表示“若干个数据值”。

应用举例（还以前面产品数据为例）：

找出产地和品牌均相同的产品：

select * from product where row(chandi,pinpai)=any(select chandi,pinpai from product group by chandi,pinpai having count(*)>=2);

考察一个特定情况：

where  操作数  =  any ( 列子查询 );

则其完全相当于：

where  操作数  in  ( 列子查询 );

示例：

找出所有带“电”字的类别的产品：

使用all的子查询

使用形式：

where  操作数  比较运算符   all (列子查询);

含义：如果子查询返回的数据中的所有值，该比较结果都为TRUE，则最终结果是TRUE。

表示该操作数的值必须跟列子查询的所有值都满足给定的比较运算，才算满足了条件。

说明：

1. 操作数通常仍然是字段名。
2. 比较运算符就是常规的 > >=　＜　＜＝　＝　＜＞。
3. 列子查询也可以是标量子查询，都表示“若干个数据值”。

all的一个典型应用是用来找到在一个字段中的最大（最小）值的数据。

示例：

找出产品表中的价格最高的产品。

分析：最高价的产品的价格会大于等于所有产品价格。

select  * from  product  where  price >= all( select price from product );

方法二：

select  * from  product  where  price = ( select max(price) from product );

使用some的子查询

使用some的子查询：some是any的“同义词”，用法和含义一样。

使用exists的子查询

使用形式：

where exists (表子查询);

含义：如果该子查询有任何数据结果（无论什么数据，即大于等于1行），则该判断就是true，否则为false。

例如：找出具有在售商品的那些品牌：

select * from product_type as pt where exists(select * from product as p where p.protype_id=pt.protype_id);

使用not exists子查询

使用形式：

where not exists(表子查询);

含义：如果该子查询没有任何数据结果（即0行），则该判断就是true，否则为false。含义跟exists子查询相反。

例如：找出没有在售商品的那些品牌：

select  *  from  product_type  where  not  exists( select  *  from  product  where product.protype_id=product_type.protype_id );

实际上，这种exists（或not exists）子查询，如果涉及到2个表（或以上），其内部其实会自动进行“连接查询”，且其逻辑过程较为负责，而且还不明确，通常认为属于效率较低的子查询，尽量少用。