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

分手后的思念是犯贱 2022-01-11 10:53 224阅读 0赞

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