11.1 子查询
子查询，顾名思义，就是嵌套在其他查询中的查询。
先创建以下表：
###############################
# 作用： 存储所有的顾客信息
# cust_id 唯一的顾客ID
# cust_name 顾客名
# cust_address 顾客的地址
# cust_city 顾客的城市
# cust_state 顾客的州
# cust_zip 顾客的邮政编码
# cust_country 顾客的国家
# cust_contact 顾客的联系名
# cust_email 顾客的联系email地址
###############################
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
##########################################
# 作用： 存储顾客订单
# order_num 唯一订单号
# order_date 订单日期
# cust_id 订单顾客ID(关联customers表中的cust_id)
##########################################
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num),
CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id)
) ENGINE=InnoDB;
#########################################
# 作用： 每个订单中的实际物品
# order_num 订单号(关联到orders表的order_num)
# order_item 订单物品号(在某个订单中的顺序)
# prod_id 产品ID(关联products表中的prod_id)
# quantity 物品数量
# item_price 物品价格
#########################################
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item) ,
CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num) ,
CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id)
) ENGINE=InnoDB;插入数据：
# products
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');
# orders
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);
# orderitems
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);orders表存储顾客的订单，orderitems表存储每个订单中的实际物品信息，customers表存储客户信息。
现在，假设需要列出订购物品TNT2的所有客户，应该怎么检索？以下是步骤：
检索包含物品TNT2的所有订单的编号。
检索具有前一步骤列出的订单编号的所有客户的ID。
检索前一步骤返回的所有客户ID的客户信息。
上述的每个步骤都可以单独作为一个查询来执行：
检索包含物品TNT2的所有订单的编号：
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
输出：
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+- 检索具有前一步骤列出的订单编号的所有客户的ID:
SELECT cust_id
FROM orders
WHERE order_num IN(20005, 20007);可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。所以，把上面的第一条查询变为子查询组合两个查询：
SELECT cust_id
FROM orders
WHERE order_num IN(
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2');
输出：
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+解释：子查询总是从内向外处理的。在上面的SQL语句中，先执行以下语句：
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';就会返回INT2的订单编号，然后把订单编号作为WHERE子句的条件，给外部查询。
3.现在得到了订购物品INT2的所有客的ID。下一步是检索这些客户ID的客户信息。
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN(10001,10004);
而我们的子查询可以继续嵌套：
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 |
+----------------+--------------+
在WHERE子句中使用子查询能够编写除功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数据没有限制，但是实际使用时由于性能的限制，不能嵌套太多的子查询。
在WHERE子句中使用子查询（如上面所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但也可以使用多个列。
子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。
11.2 作为计算字段使用子查询
使用子查询的另一种方式是创建计算字段。假设需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。以下是步骤：
从customers表中检索客户列表。
对于检索出的每个客户，统计其在orders表中的订单数目。
可以使用SELECT COUNT(*)对表中的行进行计数，并通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。比如：
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;
为了对每个客户执行COUNT()计算，应该将COUNT()作为一个子查询。如下：
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
上面中，有： orders.cust_id=customers.cust_id。这一句是相关子查询（correlated subquery），即涉及外部表查询的子查询。而且我们使用完全限定名，为了更好的区分两个表的相同列名。
11.3 子查询技巧
首先，建立和测试最内层的查询；
然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。
最好，再次测试组合好的子查询，对于要增加的每个查询，重复这些步骤。
硬编码是将数据直接嵌入到程序或其他可执行对象的源代码中的软件开发实践，与从外部获取数据或在运行时生成数据不同。
小结：介绍了什么是子查询以及如何使用。子查询最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列
————————————————
原文链接：https://blog.csdn.net/weixin_41800884/article/details/104028561