MySQL学习足迹记录12--使用子查询_MySQL

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