MySQL入门学习：子查询

一、子查询

        SQL允许创建子查询（subquery），即嵌套在其他查询中的查询。

1.1 利用子查询进行过滤

        我们有两个表，orders表包含订单号、客户ID、订单日期。而各订单的物品存储在相关的
orderitems表中。即orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。 

假如我们需要列出订购物品TNT2的所有客户，那么我们需要进行如下的检索：

1. 检索orderitems表中包含物品TNT2的所有订单的编号。
2. 在orders表中检索具有前一步骤列出的订单编号的所有客户的ID。 
3. 根据前一步骤返回的所有客户ID，在customers表中检索相应的客户信息。 

我们可以使用子查询来把3个查询组合成一条语句 ：

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'))

查询结果：

        上例中为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。 

        子查询一般与IN操作符结合使用。

1.2 作为计算字段使用子查询 

        使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中，查询步骤如下：

1. 从customers表中检索客户列表
2. 对于检索出的每个客户，统计其在orders表中的订单数目。

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*) 
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

        上述SELECT语句对customers表中每个客户返回3 列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。 

二、连接表

        SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作。

2.1 关系表

        假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。存储每种物品产品描述和价格，以及生产该产品的供应商信息。 
        现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下：

• 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。 
• 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。 
• 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。 

        关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。 

        上述例子中可建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）可以是供应商ID或任何其他唯一值。 

        products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。 

        外键（foreign key） ：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。 

这样做的好处如下：  

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

2.2 创建联结

        分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性，但是，如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？答案是联结。

        联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

        重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。 

        联结的创建非常简单，规定要联结的所有表以及它们如何关联即可：

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name

        上述SELECT语句与前面所有语句一样指定要检索的列。这里，最大的差别是所指定的两个列（prod_name和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。而FROM子句，与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。 可 以 看 到 要 匹 配 的 两 个 列 以 vendors.vend_id 和 products. vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个（它们有两个，每个表中一个）。

        应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。

2.2.1 内部联结

        目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。

        其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据： 

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name

        这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

2.2.2 联结多个表

        SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。如下：

SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id 
    AND orderitems.prod_id = products.prod_id
    AND order_num = 20005

三、结束

        本文是MySQL必知必会的学习笔记，主要介绍了子查询以及组合查询。