bitscn.com
mysql学习足迹记录12--使用子查询
1.子查询（subquery):即嵌套在其他查询中的查询
原始数据如下：
mysql> select order_num from orderitems;+-----------+| order_num |+-----------+| 20005 || 20005 || 20009 || 20005 || 20009 || 20008 || 20006 || 20009 || 20009 || 20005 || 20007 |+-----------+11 rows in set (0.01 sec)mysql> select cust_id from orders;+---------+| cust_id |+---------+| 10001 || 10001 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.01 sec)现在先分步查询step1: mysql> select order_num -> from orderitems -> where prod_id = 'tnt2';+-----------+| order_num |+-----------+| 20005 || 20007 |+-----------+2 rows in set (0.00 sec)step2: mysql> select cust_id from orders -> where order_num in( 20005,20007);+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.00 sec) step3: 使用子查询把step1,step2组合起来（即把20005,20007换掉） mysql> select cust_id -> from orders -> where order_num in( select order_num -> from orderitems -> where prod_id = 'tnt2');+---------+| cust_id |+---------+| 10001 || 10004 |+---------+2 rows in set (0.00 sec)tips： 在select语句中，子查询总是从内向外处理的。 子查询可以嵌套多重 step4: mysql> select cust_name,cust_contact -> from customers -> where cust_id in (10001,10004); #（10001，10004）既是step3查询的结果+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| coyote inc. | y lee || yosemite place | y sam |+----------------+--------------+2 rows in set (0.01 sec)step5:把step4的in (10001,10004)换成子查询 mysql> select cust_name,cust_contact -> from customers -> where cust_id in (select cust_id -> from orders -> where order_num in (select order_num -> from orderitems -> where prod_id = 'tnt2'));+----------------+--------------+| cust_name | cust_contact |+----------------+--------------+| coyote inc. | y lee || yosemite place | y sam |+----------------+--------------+2 rows in set (0.00 sec)
2.计算字段使用子查询
原始数据
mysql> select cust_id from orders;+---------+| cust_id |+---------+| 10001 || 10001 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.01 sec)mysql> select cust_id from customers;+---------+| cust_id |+---------+| 10001 || 10002 || 10003 || 10004 || 10005 |+---------+5 rows in set (0.00 sec)mysql> select cust_id,(select count(*) from orders -> where orders.cust_id = customers.cust_id) as orders -> from customers -> order by cust_id;+---------+--------+ | cust_id | orders |+---------+--------+| 10001 | 2 || 10002 | 0 || 10003 | 1 || 10004 | 1 || 10005 | 1 |+---------+--------+5 rows in set (0.00 sec)
tips：
  子查询最常见的使用是在where子句的in操作符中，以及用来填充计算列
bitscn.com