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操作符中，以及用来填充计算列