6.2.1 子查询与表连接

子查询(嵌套sql)

SELECT语句句是SQL的查询。迄今为⽌止我们所看到的所有SELECT语句都是简单查询，即从单个数据
库表中检索数据的单条语句。
SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。 各订单的
物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。
实际的客户信息存储在customers表中。
现在，假如需要列列出订购物品TNT2的所有客户，应该怎样检索？

  1. --(1) 检索包含物品TNT2的所有订单的编号。
  2. select order_num from orderitems where prod_id = 'TNT2';
  3. +-----------+
  4. | order_num |
  5. +-----------+
  6. | 20005         |
  7. | 20007         |
  8. +-----------+
  9. --(2) 检索具有前⼀一步骤列列出的订单编号的所有客户的ID
  10. select cust_id from orders where order_num IN (20005,20007);
  11. +---------+
  12. | cust_id |
  13. +---------+
  14. | 10001   |
  15. | 10004   |
  16. +---------+
  17. --(3) 检索前⼀一步骤返回的所有客户ID的客户信息。
  18. select cust_name,cust_contact from customers where cust_id in (10001,10004);
  19. +----------------+--------------+
  20. | cust_name          | cust_contact |
  21. +----------------+--------------+
  22. | Coyote Inc.      | Y Lee               |
  23. | Yosemite Place | Y Sam               |
  24. +----------------+--------------+

可以把其中的WHERE子句转换为子查询而不是硬编码这些SQL返回的数据：

  1. select cust_name,cust_contact
  2. from customers
  3. where cust_id in (select cust_id
  4.                   from orders
  5.                                     where order_num IN (select order_num
  6.                                       from orderitems
  7.                                       where prod_id = 'TNT2'));
  8. +----------------+--------------+
  9. | cust_name          | cust_contact |
  10. +----------------+--------------+
  11. | Coyote Inc.      | Y Lee               |
  12. | Yosemite Place | Y Sam               |
  13. +----------------+--------------+
  14. --为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。
  15. --最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。
  16. --外⾯面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。
  17. --最外层查询确实返回所需的数据。

作为计算字段使用子查询

使用子查询的另⼀方法是创建计算字段。

  1. -- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
  2. -- (1) 从customers表中检索客户列列表。
  3. select cust_id,cust_name from customers ;
  4. +---------+----------------+
  5. | cust_id | cust_name          |
  6. +---------+----------------+
  7. | 10001     | Coyote Inc.      |
  8. | 10002     | Mouse House      |
  9. | 10003     | Wascals            |
  10. | 10004     | Yosemite Place |
  11. | 10005     | E Fudd                  |
  12. +---------+----------------+
  14. -- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬目。
  15. select count(*) as orders from orders where cust_id = 10001;
  16. +--------+
  17. | orders |
  18. +--------+
  19. | 2          |
  20. +--------+

为了对每个客户执行COUNT() 计算，应该将 COUNT()作为一个子查询。

  1. select cust_id,cust_name,
  2.   (select count(*)
  3.     from orders
  4.     where orders.cust_id = customers.cust_id) as orders
  5.     )
  6. from customers
  7. order by cust_name;

6.2.2关系表

SQL最强大的功能之一就是能在数据检索查询的执行中连接（join）表。
在能够有效地使用连接前，必须了解关系表以及关系数据库设计的一些基础知识。

—假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。
—对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
产品表：
产品，描述，价格，供应商名称，供应商地址,供应商联系方式

  • A6 … … 奥迪 … ….
  • 520li . . …. 宝马 … …

…
—现在，假如有由同一供应商生产的多种物品，那么在何处存储供应
—商信息（如，供应商名、地址、联系方法等）呢？

相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。
各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。

  1. -- vendors表包含所有供应商信息
  2. |vend_id | vend_name | vend_address| vend_city ....
  3. -- products表只存储产品信息，它除了了存储供应商ID（vendors表的主键）外不不存储其他供应商信息。
  4. prod_id | vend_id | prod_name | prod_price | prod_desc

vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。 这样做的好处如下：

  • 供应商信息不重复，从而不浪费时间和空间；
  • 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
  • 由于数据无重复，显然数据是一致的，这使得处理数据更简单

关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。