MySql015——使用子查询

一、创建customers表

########################
# Create customers table
########################
use `study`;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;##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

在这里插入图片描述

二、子查询举例

2.1、使用的表格数据信息

customers表存储：客户信息
orders表存储：订单
在这里插入图片描述

orderitems表存储：订单中的详细信息
在这里插入图片描述

2.2、例子：要列出订购物品TNT2的所有客户

(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。

-- (3) 检索前一步骤返回的所有客户ID的客户信息。
SELECT cust_name, cust_contact
FROMcustomers
WHEREcust_id IN (-- (2) 检索具有前一步骤列出的订单编号的所有客户的ID。SELECT cust_idFROMordersWHEREorder_num IN (-- (1) 检索包含物品TNT2的所有订单的编号。SELECT order_numFROMorderitemsWHEREprod_id = 'TNT2'));