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

来源：互联网发布：深度linux deepin 编辑：程序博客网时间：2024/06/06 02:20

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