第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表中。
下面列出具体的检索步骤:
- 检索包含物品RGAN01的所有订单的编号。
- 检索具有前一步骤列出的订单编号的所有顾客的ID。
- 检索前一步骤返回的所有顾客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表中,步骤如下:
- 从Customer表中检索顾客列表。
- 对于检索出的每个顾客,统计其在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)