mysql数据库子查询实践报告_MySQL学习足迹记录12--使用子查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_32019909/article/details/114347936

本文通过具体实例演示了如何在SQL中使用子查询来逐步提取所需数据。从基本的单表查询到复杂的多级子查询应用，展示了如何通过子查询获取特定订单的产品购买者信息，并进一步计算客户订单数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql> SELECT order_num FROM orderitems;

+-----------+

| order_num |

+-----------+

| 20005 |

| 20009 |

| 20005 |

| 20009 |

| 20008 |

| 20006 |

| 20009 |

| 20005 |

| 20007 |

+-----------+

11 rows in set (0.01 sec)

mysql> SELECT cust_id FROM orders;

+---------+

| cust_id |

+---------+

| 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 |

| 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)