嵌套查询
是指在一个完整的查询语句之中，包含若干个不同功能的小查询；从而一起完成复杂查询的一种编写形式。包含的查询放在（）里 ， 包含的查询出现的位置：
| 位置 | 含义 |
| SELECT之后 | 把查询结果作为表头使用 |
| FROM之后 | 把查询结果作为表使用 |
| WHERE之后 | 把查询结果作为查询条件 |
| HAVING之后 | 把查询结果作为过滤使用 |
SELECT之后：
查看客户的总订单数
mysql> select cust_name, -> (select count(*) from orders -> where orders.cust_id = customers.cust_id ) -> as orders_num from customers; +----------------+------------+ | cust_name | orders_num | +----------------+------------+ | Coyote Inc. | 2 | | Mouse House | 0 | | Wascals | 1 | | Yosemite Place | 1 | | E Fudd | 1 | +----------------+------------+ 5 rows in set (0.01 sec)
WHERE之后：
查询下单了TNT2的客户id
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.01 sec)
实验：
1.使用子查询，返回购买价格为10或以上的商品的客户列表。您需，然后使用要使用Orderltems表查找匹配的订单号(order num)Orders表检索每个匹配订单的客户ID(cust id)。
mysql> select distinct cust_id from orders -> where order_num in -> ( select order_num from orderitems -> where item_price >= 10 ) ; +---------+ | cust_id | +---------+ | 10001 | | 10003 | | 10004 | +---------+ 3 rows in set (0.01 sec)
2.您需要知道订购产品BR01的日期。编写一条SQL语句，使用子查询确定哪些订单(Orderltems中)购买了prod_id为BR01的商品，然后返回客户ID(cust_id)，和订单日期(order_date)。按订单日期排序结果。
mysql> select order_date from orders -> where order_num in -> ( select order_num from orderitems -> where prod_id = 'ANV01'); +---------------------+ | order_date | +---------------------+ | 2023-09-01 00:00:00 | +---------------------+ 1 row in set (0.00 sec)
3.更新前面的挑战，为购买了prod id为AVN01的商品的任何客户返回客户电子邮件(Customers表中的custemail)。这里有一个提示:这涉及到SELECT语句，最里面的查询从Orderltems返回order num，中间的查询从Customers返回custid。
mysql> select cust_email from customers -> where cust_id in -> ( select cust_id from orders -> where order_num in -> ( select order_num from orderitems -> where prod_id = 'ANV01' ) ); +-----------------+ | cust_email | +-----------------+ | ylee@coyote.com | +-----------------+ 1 row in set (0.00 sec)
4.您需要一个包含每个客户订购的总额的客户ID列表。编写一条SOL语句，返回客户ID(0rders表中的cust id)和total ordered，并使用一个子查询返回每个客户的订单总数。按花费从大到小的顺序排列结果。这里有一个提示:你已经使用SUM()来计算订单总计。
mysql> SELECT cust_id, -> ( SELECT SUM(quantity * item_price ) FROM orderitems -> WHERE order_num IN -> ( SELECT order_num FROM orders -> WHERE orders.cust_id = customers.cust_id ) ) -> AS total_ordered -> FROM customers -> ORDER BY total_ordered DESC; +---------+---------------+ | cust_id | total_ordered | +---------+---------------+ | 10004 | 1000.00 | | 10001 | 188.34 | | 10005 | 125.00 | | 10003 | 55.00 | | 10002 | NULL | +---------+---------------+ 5 rows in set (0.00 sec)
5.编写一条SQL语句，从Products表中检索所有产品名称(prodname)，以及一个名为quant_sold的计算列，该列包含此商品的销售总数(使用Orderltems表中的子查询和SUM(quantity)检索)。
mysql> SELECT prod_name, -> (SELECT Sum(quantity) -> FROM orderitems -> WHERE products.prod_id=orderitems.prod_id) -> AS quant_sold -> FROM products; +----------------+------------+ | prod_name | quant_sold | +----------------+------------+ | .5 ton anvil | 10 | | 1 ton anvil | 3 | | 2 ton anvil | 1 | | Detonator | NULL | | Bird seed | 2 | | Carrots | 50 | | Fuses | NULL | | JetPack 1000 | NULL | | JetPack 2000 | 1 | | Oil can | 1 | | Safe | NULL | | Sling | 1 | | TNT (1 stick) | NULL | | TNT (5 sticks) | 105 | +----------------+------------+ 14 rows in set (0.00 sec)
