详细讲述MySQL中的子查询操作

继续做以下的前期准备工作：

    新建一个测试数据库TestDB；

   

  create database TestDB;

</div>

    创建测试表table1和table2；

   CREATE TABLE table1
   (
     customer_id VARCHAR(10) NOT NULL,
     city VARCHAR(10) NOT NULL,
     PRIMARY KEY(customer_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

   CREATE TABLE table2
   (
     order_id INT NOT NULL auto_increment,
     customer_id VARCHAR(10),
     PRIMARY KEY(order_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

</div>

    插入测试数据；

   INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
   INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');

   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('tx');

</div>

    准备工作做完以后，table1和table2看起来应该像下面这样：

   mysql> select * from table1;
   +-------------+----------+
   | customer_id | city   |
   +-------------+----------+
   | 163     | hangzhou |
   | 9you    | shanghai |
   | baidu    | hangzhou |
   | tx     | hangzhou |
   +-------------+----------+
   4 rows in set (0.00 sec)

   mysql> select * from table2;
   +----------+-------------+
   | order_id | customer_id |
   +----------+-------------+
   |    1 | 163     |
   |    2 | 163     |
   |    3 | 9you    |
   |    4 | 9you    |
   |    5 | 9you    |
   |    6 | tx     |
   +----------+-------------+
   7 rows in set (0.00 sec)

</div>

准备工作做的差不多了，开始今天的总结吧。
一个问题

现在需要查询所有杭州用户的所有订单号，这个SQL语句怎么写？首先，你可以这么写：

select table2.customer_id, table2.order_id from table2 join table1 on table1.customer_id=table2.customer_id where table1.city='hangzhou';

</div>

能实现我们需要的结果。但是，我们也可以这么写：

select customer_id, order_id from table2 where customer_id in (select customer_id from table1 where city='hangzhou');

</div>

呃？在()括号中的的select语句是什么？问题来了，这到底是什么语法，怎么也可以完成任务，那么这篇博文就围绕着这个问题开始展开。
啥是子查询

简单的说，子查询就是：

如上图所示，子查询，有叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。子查询可以包含普通select可以包括的任何子句，比如：distinct、group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或者do。

我们可以在where和having子句中使用子查询，将子查询得到的结果作为判断的条件。
使用比较进行子查询

一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询。

当一个子查询返回一个标量时，我们就可以在where或者having子句中使用比较符与子查询得到的结果进行直接判断。比如，我现在要得到比用户tx订单数多的customer_id、city和订单数，这个sql语句怎么写。

先来说说，我写sql的一般步骤：

•     读懂需求；
•     得到比用户tx订单数多的customer_id、city和对应的订单数。
•     看看最终需要得到哪些字段信息；
•     最终需要得到customer_id、city和订单数信息。
•     分析这些字段信息涉及到哪几个表；
•     涉及到表table1和表table2。
•     这几个表是如何关联的；
•     表table1和表table2的关联就在于customer_id字段。
•     分解需求，得到一个个小的需求；
•         需要得到tx用户的订单数；
•         需要得到其它用户的订单数；
•         比较订单数。

•     确认每一个小需求的过滤条件；
•     得到每个小需求的结果，进行组装，得到最终结果。

最终，我会写出一下的sql语句：

select table1.customer_id,city,count(order_id) 
from table1 join table2 
on table1.customer_id=table2.customer_id 
where table1.customer_id <> 'tx'
group by customer_id 
having count(order_id) > 
            (select count(order_id) 
             from table2 
             where customer_id='tx' 
             group by customer_id);

</div>

上面的查询中使用了子查询，外部查询与子查询得到的结果进行了比较判断。如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。
使用ANY进行子查询

上面使用比较符进行子查询，规定了子查询只能返回一个标量值；但是，如果子查询返回的是一个集合，怎么办？

没问题，我们可以使用：any、in、some或者all来和子查询的返回结果进行条件判断。这里先总结使用any进行子查询。

any关键词必须与上面总结的比较操作符一起使用；any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。

好比“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意一个进行比较，得到TRUE时，就会返回TRUE。

比如，我现在要查询比customer_id为tx或者9you的订单数量多的用户的id、城市和订单数量。

我可以得到以下的sql语句来完成需求。

select table1.customer_id,city,count(order_id)
from table1 join table2
on table1.customer_id=table2.customer_id
where table1.customer_id<>'tx' and table1.customer_id<>'9you'
group by customer_id
having count(order_id) >
any (
select count(order_id)
from table2
where customer_id='tx' or customer_id='9you'
group by customer_id);

</div>

any的意思比较好明白，直译就是任意一个，只要条件满足任意的一个，就返回TRUE。
使用IN进行子查询

使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。

in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。这里就不举例了，尽情的发挥想象，自行发挥吧。

有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思，关于all，下面马上就要总结了。
使用SOME进行子查询

some是any的别名，用的比较少。只需要理解any的意思就好了，这里就不做过多的总结。具体请参考上面的any部分的总结。
使用ALL进行子查询

all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。

好比“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。

<