读书笔记：SQL必知必会【第10课-第12课】

第10课 分组数据

这一课介绍如何分组数据，以便汇总表内容的子集。

10.1 数据分组

从上一课得知，使用SQL的聚集函数可以汇总数据，这样，我们就能对行进行计数，计算和取平均数，不检索所有数据就能取最值。目前为止所有的计算都是在表的所有数据或者匹配特定的WHERE子句的数据上进行的。本课则说明使用分组，对每个组聚集计算。

10.2 创建分组

分组是使用SELECT语句的GROUP BY子句创建的，下例：

mysql> SELECT vend_id,COUNT(*) AS num_prods    -> FROM Products    -> GROUP BY vend_id;+---------+-----------+| vend_id | num_prods |+---------+-----------+| BRS01   |         3 || DLL01   |         4 || FNG01   |         2 |+---------+-----------+3 rows in set (0.03 sec)

在使用GROUP BY之前，需要知道一些重要的规定：

• GROUP BY子句可以包含任意数目的列，因而可以对分组进行嵌套，进而更细致分组。
• 如果在GROUP BY中嵌套了分组，数据将在最后指定的分组上进行汇总，换句话说，在建立分组时，指定的所有列都一起计算。

• 嵌套分组，可以看下列示例：

   mysql> SELECT COUNT(*),prod_id,order_num FROM orderitems GROUP BY prod_id;+----------+---------+-----------+| COUNT(*) | prod_id | order_num |+----------+---------+-----------+|        3 | BNBG01  |     20007 ||        3 | BNBG02  |     20007 ||        3 | BNBG03  |     20007 ||        2 | BR01    |     20005 ||        1 | BR02    |     20006 ||        4 | BR03    |     20005 ||        2 | RGAN01  |     20007 |+----------+---------+-----------+7 rows in set (0.00 sec)mysql> SELECT COUNT(*),prod_id,order_num FROM orderitems GROUP BY order_num,prod_id;+----------+---------+-----------+| COUNT(*) | prod_id | order_num |+----------+---------+-----------+|        1 | BR01    |     20005 ||        1 | BR03    |     20005 ||        1 | BR01    |     20006 ||        1 | BR02    |     20006 ||        1 | BR03    |     20006 ||        1 | BNBG01  |     20007 ||        1 | BNBG02  |     20007 ||        1 | BNBG03  |     20007 ||        1 | BR03    |     20007 ||        1 | RGAN01  |     20007 ||        1 | BNBG01  |     20008 ||        1 | BNBG02  |     20008 ||        1 | BNBG03  |     20008 ||        1 | BR03    |     20008 ||        1 | RGAN01  |     20008 ||        1 | BNBG01  |     20009 ||        1 | BNBG02  |     20009 ||        1 | BNBG03  |     20009 |+----------+---------+-----------+18 rows in set (0.00 sec)

• GROUP BY子句列出的每一列都必须是所检索的列或有效的表达式（但不可以是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY的子句中使用相同的表达式。不要使用别名。

• 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型。
• 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
• 如果分组列中包含具有NULL值的行，则该NULL会作为一个单独的分组返回。如果有多行NULL值，这些NULL值会被分成一组。
• GROUP BY必须出现在WHERE之后，ORDER BY之前。

10.3 过滤分组

除了能够用GROUP BY分组之外，SQL也允许过滤分组，规定包括哪些分组、排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客，为此必须基于完整的分组而不是个别的行进行过滤。

我们已经看到WHERE子句的作用，但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组，事实上，WHERE是没有分组的概念的。那么，不使用分组使用什么呢？

SQL为此提供了另一个子句，那就是HAVING。HAVING非常类似WHERE，实际上，目前所学过的所有类型的WHERE子句都可以用HAVING代替，唯一的差别是WHERE过滤行，而HAVIUNG过滤分组。HAVING支持所有的WHERE操作符。

例子如下：

mysql> SELECT cust_id,COUNT(*) AS orders    -> FROM Orders    -> GROUP BY cust_id    -> HAVING COUNT(*) >=2;+------------+--------+| cust_id    | orders |+------------+--------+| 1000000001 |      2 |+------------+--------+1 row in set (0.00 sec)

这条语句的最后一行增加了HAVING子句，它过滤出来的只有COUNT(*)>=2（两个以上订单）的那些分组。可以看到，WHERE子句在这里不起作用，因为我们想要的过滤是基于分组聚集值而非特定行的值。

WHERE子句和HAVING子句可以在必要时同时使用，下例列出具有两个以上产品，且其价格在大于等于4的供应商：

mysql> SELECT COUNT(*) AS num_prods ,prod_id    -> FROM Products    -> WHERE prod_price>= 4    -> GROUP BY vend_id    -> HAVING COUNT(*)>=2;+-----------+---------+| num_prods | prod_id |+-----------+---------+|         3 | BR01    ||         2 | RYL01   |+-----------+---------+2 rows in set (0.00 sec)

10.4 分组和排序

GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同，理解这一点很重要，下表汇总了它们的差别：

这其中的第一项差别极为重要：GROUP BY分组后的数据很多时候是以分组顺序输出的——但并不总是如此，更何况，也有些时候我们需求以特定顺序来输出分组。因此，应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句。

下例检索包含三个或更多物品的订单号和订购物品的数目：

mysql> SELECT COUNT(*) AS items,order_num    -> FROM orderitems    -> GROUP BY order_num    -> HAVING COUNT(*) >=3;+-------+-----------+| items | order_num |+-------+-----------+|     3 |     20006 ||     5 |     20007 ||     5 |     20008 ||     3 |     20009 |+-------+-----------+4 rows in set (0.00 sec)

可以看到，是按照分组顺序输出的数据。
而如果想要按照订购物品的数目排序输出的话，就需要添加ORDER BY：

mysql> SELECT COUNT(*) AS items,order_num    -> FROM orderitems    -> GROUP BY order_num    -> HAVING COUNT(*) >=3    -> ORDER BY items,order_num;+-------+-----------+| items | order_num |+-------+-----------+|     3 |     20006 ||     3 |     20009 ||     5 |     20007 ||     5 |     20008 |+-------+-----------+4 rows in set (0.00 sec)

这样，就按照订购物品的数目排序输出了。

在这个例子中，使用GROUP BY子句按订单后分组数据，以便COUNT(*)函数能够返回每个订单中的物品数目，HAVING子句则过滤数据，使得只返回包含三个或更多物品的订单。最后，用ORDER BY子句排序输出。

10.5 SELECT子句顺序

下面回顾一下SELECT语句中子句的顺序：

第11课 使用子查询

11.1 子查询

SELECT子句是SQL的查询，我们迄今为止看到的所有SELECT都是简单查询，即从单个数据库表中检索数据的单条语句。

SQL也允许创建子查询，即嵌套在查询中的查询。

11.2 利用子查询进行过滤

如果要列出订购物品RGAN01的所有顾客，应该怎样检索？

订单存储在两个表中：每个订单包含订单编号、客户ID、订单日期，在Orders表中存储为一行；而各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息，只存储顾客ID，顾客的实际信息存储在Customers表中。

下面列出具体的检索步骤：

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

上述每个步骤都可以单独作为一个查询来执行，也可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE语句。

第一条SELECT语句的含义很明确：对prod_id为RGAN01的所有订单物品，检索其order_num列：

mysql> SELECT prod_id,order_num    -> FROM OrderItems    -> WHERE prod_id = 'RGAN01';+---------+-----------+| prod_id | order_num |+---------+-----------+| RGAN01  |     20007 || RGAN01  |     20008 |+---------+-----------+2 rows in set (0.01 sec)

输出列出了两个包含此物品的订单，现在，我们知道哪些订单包含了要检索的物品。下一步查询与订单20007，20008相关的顾客ID，这里要用到前面介绍过的IN语句：

mysql> SELECT cust_id,order_num    -> FROM orders    -> WHERE order_num in (20007,20008);+------------+-----------+| cust_id    | order_num |+------------+-----------+| 1000000004 |     20007 || 1000000005 |     20008 |+------------+-----------+2 rows in set (0.00 sec)

这样，就检索到了对应的顾客编号。

现在，结合这两个查询，把第一个查询（即获得订单号的查询）变为子查询：

mysql> SELECT cust_id    -> FROM orders    -> WHERE order_num IN    -> (SELECT order_num    -> FROM orderitems    -> WHERE prod_id = 'RGAN01');+------------+| cust_id    |+------------+| 1000000004 || 1000000005 |+------------+2 rows in set (0.00 sec)

这样，输出是正确的。
但是，需要注意一件事，嵌套在查询中的那个查询，SELECT查询的只有一个order_num而已。
如果代码变成这样的话：

mysql> SELECT cust_id    -> FROM orders    -> WHERE order_num in    -> (SELECT order_num,prod_id    -> FROM orderitems    -> WHERE prod_id = 'RGAN01');ERROR 1241 (21000): Operand should contain 1 column(s)

可以看到，由于嵌套中的查询语句是 SELECT order_num,prod_id，除了order_num外，还有一个prod_id，那么，查询就不成立了。嵌套其中的查询，其键值只能有一个。

现在得到了订购物品RGAN01的所有顾客ID，下一步是检索这些顾客ID的顾客信息，检索两列的SQL语句为：

mysql> SELECT cust_name,cust_contact    -> FROM Customers    -> WHERE cust_id in ('1000000004','1000000005');+---------------+--------------------+| cust_name     | cust_contact       |+---------------+--------------------+| Fun4All       | Denise L. Stephens || The Toy Store | Kim Howard         |+---------------+--------------------+2 rows in set (0.00 sec)

同样的。可以把WHERE子句转换为子查询，而不是硬编码这些顾客ID：

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 = 'RGAN01')    ->          )    -> ;+---------------+--------------------+| cust_name     | cust_contact       |+---------------+--------------------+| Fun4All       | Denise L. Stephens || The Toy Store | Kim Howard         |+---------------+--------------------+2 rows in set (0.00 sec)

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

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

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

这里，可以采用SELECT COUNT(*) 对表中的行进行计数，并且通过提供一条WHERE子句，来过滤某个特定的顾客ID，仅对该顾客的订单进行计数，例如，下面的代码对顾客1000000001的订单进行计数：

mysql> SELECT COUNT(*) AS orders    -> FROM orders    -> WHERE cust_id = '1000000001';+--------+| orders |+--------+|      2 |+--------+1 row in set (0.00 sec)

而如果要对每个顾客执行COUNT(*)，应该把它作为一个子查询：

mysql> SELECT cust_id,cust_name,    ->          (SELECT COUNT(*)    ->          FROM orders    ->          WHERE orders.cust_id = customers.cust_id) AS orders    -> FROM customers    -> ORDER BY cust_name;+------------+---------------+--------+| cust_id    | cust_name     | orders |+------------+---------------+--------+| 1000000003 | Fun4All       |      1 || 1000000004 | Fun4All       |      1 || 1000000002 | Kids Place    |      0 || 1000000005 | The Toy Store |      1 || 1000000001 | Village Toys  |      2 |+------------+---------------+--------+5 rows in set (0.00 sec)

子查询中采用了完全限定列名：orders.cust_id = customers.cust_id，这是为了防止不同表中相同列名的情况。如果不采用完全限定列名，DBMS会认为要对Orders表中的cust_id自身进行比较。

需要说明的是，并非只有这一种方案来完成这样的查询，接下来的课程会出现连接的方法。

第12课 联结表

SQL最强大的功能之一就是能在数据查询的执行中，对表进行联结（Join）。联结是利用SQL的SELECT能够执行的最重要的操作，很好滴理解联结及其语法，是学习SQL的极其重要的部分。

在能够有效使用联结前，必须了解关系表以及关系型数据库设计的一些基础知识。

12.1 联结

12.1.1 关系表

理解关系表，最好是来看个例子。

有一个包含产品目录的数据库表，其中每类物品占一行，对于每一个物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？

将这些数据与产品信息分开存储的理由是：

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

关键是相同的数据多次出现绝不是一件好事，这是关系型数据库设计的基础。关系表的设计，就是要把信息分解成多个表，一类数据一类表，各表通过某些共同的值互相关联。

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

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

这样做的好处是：

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

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

12.1.2 为什么使用联结

如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。

如果数据存储在多个表中，怎样用一条SELECT语句就检索出数据呢？

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

12.2 创建联结

创建联结非常简单，指定要联结的所有表以及关联他们的方式即可，请看下例：

mysql> SELECT vend_name,prod_name,prod_price    -> FROM Vendors,Products    -> WHERE Vendors.vend_id = Products.vend_id;+-----------------+---------------------+------------+| vend_name       | prod_name           | prod_price |+-----------------+---------------------+------------+| Doll House Inc. | Fish bean bag toy   |       3.49 || Doll House Inc. | Bird bean bag toy   |       3.49 || Doll House Inc. | Rabbit bean bag toy |       3.49 || Bears R Us      | 8 inch teddy bear   |       5.99 || Bears R Us      | 12 inch teddy bear  |       8.99 || Bears R Us      | 18 inch teddy bear  |      11.99 || Doll House Inc. | Raggedy Ann         |       4.99 || Fun and Games   | King doll           |       9.49 || Fun and Games   | Queen doll          |       9.49 |+-----------------+---------------------+------------+9 rows in set (0.00 sec)

可以看到，要匹配的两列指定为Vendors.vend_id和Products.vend_id，这里需要完全限定列名。从前面的输出可以看到，一条SELECT语句反映了两个不同表中的数据。

12.2.1 WHERE子句的重要性。

使用WHERE子句建立联结关系似乎有点奇怪，但实际上是有个很充分的理由的。要记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示DBMS如何对表进行联结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。

例如，可以参看下面的语句与输出：

mysql> SELECT vend_name,prod_name,prod_price    -> FROM Vendors,Products;+-----------------+---------------------+------------+| vend_name       | prod_name           | prod_price |+-----------------+---------------------+------------+| Bear Emporium   | Fish bean bag toy   |       3.49 || Bears R Us      | Fish bean bag toy   |       3.49 || Doll House Inc. | Fish bean bag toy   |       3.49 || Fun and Games   | Fish bean bag toy   |       3.49 || Furball Inc.    | Fish bean bag toy   |       3.49 || Jouets et ours  | Fish bean bag toy   |       3.49 || Bear Emporium   | Bird bean bag toy   |       3.49 || Bears R Us      | Bird bean bag toy   |       3.49 || Doll House Inc. | Bird bean bag toy   |       3.49 || Fun and Games   | Bird bean bag toy   |       3.49 || Furball Inc.    | Bird bean bag toy   |       3.49 || Jouets et ours  | Bird bean bag toy   |       3.49 || Bear Emporium   | Rabbit bean bag toy |       3.49 || Bears R Us      | Rabbit bean bag toy |       3.49 || Doll House Inc. | Rabbit bean bag toy |       3.49 || Fun and Games   | Rabbit bean bag toy |       3.49 || Furball Inc.    | Rabbit bean bag toy |       3.49 || Jouets et ours  | Rabbit bean bag toy |       3.49 || Bear Emporium   | 8 inch teddy bear   |       5.99 || Bears R Us      | 8 inch teddy bear   |       5.99 || Doll House Inc. | 8 inch teddy bear   |       5.99 || Fun and Games   | 8 inch teddy bear   |       5.99 || Furball Inc.    | 8 inch teddy bear   |       5.99 || Jouets et ours  | 8 inch teddy bear   |       5.99 || Bear Emporium   | 12 inch teddy bear  |       8.99 || Bears R Us      | 12 inch teddy bear  |       8.99 || Doll House Inc. | 12 inch teddy bear  |       8.99 || Fun and Games   | 12 inch teddy bear  |       8.99 || Furball Inc.    | 12 inch teddy bear  |       8.99 || Jouets et ours  | 12 inch teddy bear  |       8.99 || Bear Emporium   | 18 inch teddy bear  |      11.99 || Bears R Us      | 18 inch teddy bear  |      11.99 || Doll House Inc. | 18 inch teddy bear  |      11.99 || Fun and Games   | 18 inch teddy bear  |      11.99 || Furball Inc.    | 18 inch teddy bear  |      11.99 || Jouets et ours  | 18 inch teddy bear  |      11.99 || Bear Emporium   | Raggedy Ann         |       4.99 || Bears R Us      | Raggedy Ann         |       4.99 || Doll House Inc. | Raggedy Ann         |       4.99 || Fun and Games   | Raggedy Ann         |       4.99 || Furball Inc.    | Raggedy Ann         |       4.99 || Jouets et ours  | Raggedy Ann         |       4.99 || Bear Emporium   | King doll           |       9.49 || Bears R Us      | King doll           |       9.49 || Doll House Inc. | King doll           |       9.49 || Fun and Games   | King doll           |       9.49 || Furball Inc.    | King doll           |       9.49 || Jouets et ours  | King doll           |       9.49 || Bear Emporium   | Queen doll          |       9.49 || Bears R Us      | Queen doll          |       9.49 || Doll House Inc. | Queen doll          |       9.49 || Fun and Games   | Queen doll          |       9.49 || Furball Inc.    | Queen doll          |       9.49 || Jouets et ours  | Queen doll          |       9.49 |+-----------------+---------------------+------------+54 rows in set (0.00 sec)

可以看到，这样的输出是将每个表的每一行分别互相匹配的结果，这样的输出并不是我们想要的，也不是正确的。

12.2.2 内联结

目前为止使用的联结称为等值联结，它基于两个表之间的相等测试，这种联结也称为内联结（inner join）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型，下面的SELECT语句，返回与前面例子相同的数据：

mysql> SELECT vend_name,prod_name,prod_price    -> FROM Vendors INNER JOIN Products    -> ON Vendors.vend_id = Products.vend_id;+-----------------+---------------------+------------+| vend_name       | prod_name           | prod_price |+-----------------+---------------------+------------+| Doll House Inc. | Fish bean bag toy   |       3.49 || Doll House Inc. | Bird bean bag toy   |       3.49 || Doll House Inc. | Rabbit bean bag toy |       3.49 || Bears R Us      | 8 inch teddy bear   |       5.99 || Bears R Us      | 12 inch teddy bear  |       8.99 || Bears R Us      | 18 inch teddy bear  |      11.99 || Doll House Inc. | Raggedy Ann         |       4.99 || Fun and Games   | King doll           |       9.49 || Fun and Games   | Queen doll          |       9.49 |+-----------------+---------------------+------------+9 rows in set (0.00 sec)

此语句的SELECT与前面的相同，但是FROM子句不同，这里，两个表的关系是以INNER JOIN指定的部分FROM子句，使用这种语法时，联结条件要用特定的ON子句而不是WHERE子句，传递给ON的实际条件则与给WHERE的相同。

12.2.3 联结多个表

SQL不限制一条SELECT语句可以联结的表的数目，创建联结的基本规则也和两个表时相同：

mysql> 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 = 20007;+---------------------+-----------------+------------+----------+| prod_name           | vend_name       | prod_price | quantity |+---------------------+-----------------+------------+----------+| 18 inch teddy bear  | Bears R Us      |      11.99 |       50 || Fish bean bag toy   | Doll House Inc. |       3.49 |      100 || Bird bean bag toy   | Doll House Inc. |       3.49 |      100 || Rabbit bean bag toy | Doll House Inc. |       3.49 |      100 || Raggedy Ann         | Doll House Inc. |       4.99 |       50 |+---------------------+-----------------+------------+----------+5 rows in set (0.00 sec)

现在回顾一下前面曾经要进行的查询：用SELECT语句返回订购产品RGAN01的顾客列表。

前面使用子查询完成这个查询，这里则使用联结查询：

mysql> SELECT cust_name,cust_contact    -> FROM Customers,OrderItems,Orders    -> WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num AND prod_id = 'RGAN01';+---------------+--------------------+| cust_name     | cust_contact       |+---------------+--------------------+| Fun4All       | Denise L. Stephens || The Toy Store | Kim Howard         |+---------------+--------------------+2 rows in set (0.00 sec)