MySQL学习足迹记录12--使用子查询

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录12--使用子查询

MySQL学习足迹记录12--使用子查询

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录12--使用子查询1.子查询(subquery):即嵌套在其他查询中的查询原始数据如下: mysql SELECT order_num FROM orderitems;+-----------+| order_num |+-----------+| 20005...
MySQL学习足迹记录12--使用子查询
 
1.子查询(subquery):即嵌套在其他查询中的查询
原始数据如下:
 
   > SELECT order_num FROM orderitems;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20005 |
|     20009 |
|     20005 |
|     20009 |
|     20008 |
|     20006 |
|     20009 |
|     20009 |
|     20005 |
|     20007 |
+-----------+
11 rows in set (0.01 sec)

mysql> SELECT cust_id FROM orders;
+---------+
| cust_id |
+---------+
|   10001 |
|   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 |
|   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)

 

 
TIPS:
  子查询最常见的使用是在WHERE子句的IN操作符中,以及用来填充计算列
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<