MySql015——使用子查询

朴灿烈づ我的快乐病毒、 2023-10-15 08:40 93阅读 0赞

## 一、创建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');

![在这里插入图片描述][d31187d330ea4cd5b0c710ca946553fd.png]

## 二、子查询举例 ##

### 2.1、使用的表格数据信息 ###

customers表存储：客户信息  
orders表存储：订单  
![在这里插入图片描述][ea801d4b7e944902a8c0532835ad7c26.png]

orderitems表存储：订单中的详细信息  
![在这里插入图片描述][c09313b43a2f4a47af28687002b7b3cb.png]

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

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

-- (3) 检索前一步骤返回的所有客户ID的客户信息。
    SELECT 
        cust_name, cust_contact
    FROM
        customers
    WHERE
        cust_id IN (
        	-- (2) 检索具有前一步骤列出的订单编号的所有客户的ID。
    		SELECT 
                cust_id
            FROM
                orders
            WHERE
                order_num IN (
                	-- (1) 检索包含物品TNT2的所有订单的编号。
    				SELECT 
                        order_num
                    FROM
                        orderitems
                    WHERE
                        prod_id = 'TNT2'));

![在这里插入图片描述][d6bff5fb453446abaa71b0de03bff78a.png]

## 三、子查询特点 ##

在SELECT语句中，子查询总是从内向外处理。

[d31187d330ea4cd5b0c710ca946553fd.png]: https://img-blog.csdnimg.cn/d31187d330ea4cd5b0c710ca946553fd.png
[ea801d4b7e944902a8c0532835ad7c26.png]: https://img-blog.csdnimg.cn/ea801d4b7e944902a8c0532835ad7c26.png
[c09313b43a2f4a47af28687002b7b3cb.png]: https://img-blog.csdnimg.cn/c09313b43a2f4a47af28687002b7b3cb.png
[d6bff5fb453446abaa71b0de03bff78a.png]: https://img-blog.csdnimg.cn/d6bff5fb453446abaa71b0de03bff78a.png