SQL第10课：分组数据（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.00 sec)

mysql> select vend_id, count(*) as num_prods from Products;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         9 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select *
    -> from Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)

group by 在WHERE 之后 在ORDER BY 之前。WHERE 没有分组的概念，用having过滤分组。

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.01 sec)

mysql> select vend_id,count(*) as num_prod
    -> from Products
    -> where prod_price>=4
    -> group by vend_id
    -> having count(*) >=2;
+---------+----------+
| vend_id | num_prod |
+---------+----------+
| BRS01   |        3 |
| FNG01   |        2 |
+---------+----------+

SELECT 子句的使用顺序

最近在学习数据库的相关知识，想和大家分享一下学习后的相关笔记。
当SELECT语句被DBMS执行时，其子句会按照固定的先后顺序执行：

如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

常用子句使用顺序：