9.1 聚集函数
聚集函数（aggregate function），对某些行运行的函数，计算并返回一个值。
使用聚集函数可以汇总数据而不必将涉及的数据实际检索出来。
可利用标准的算术操作符，实现更高级的汇总。
AVG() --- 返回某列的平均值
COUNT() --- 返回某列的行数
MAX() --- 返回某列的最大值
MIN() --- 返回某列的最小值
SUM() --- 返回某列值之和
9.1.1 AVG()函数
AVG()函数可用来返回特定列或行的平均值。
也可以返回多个列的平均值，此时必须使用多个AVG()函数。
AVG()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| 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 |
| 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 |
| 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)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products;
+-----------+
| avg_price |
+-----------+
| 6.823333 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.2 COUNT()函数
可利用COUNT()函数获取表中行的数目或者符合特定条件的行的数目。
- COUNT(*)统计表中行的数目，包括NULL的行。
- COUNT(column)统计特定列中的行的数目，忽略NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(cust_email) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.3 MAX()函数
MAX()函数一般用来找出最大的数值或日期值。必须指定列名。
在用于文本数据时，则是返回该列排序后的最后一行。
MAX()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| 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 |
| 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 |
| 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)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_price) AS max_price FROM Products;
+-----------+
| max_price |
+-----------+
| 11.99 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_name) AS max_prod_name FROM Products;
+---------------+
| max_prod_name |
+---------------+
| Raggedy Ann |
+---------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.4 MIN()函数
MIN()函数返回最小的数值或日期值。必须指定列名。
在用于文本数据时，则是返回该列排序后的最前面的行。
MIN()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT MIN(prod_price) AS min_price FROM Products;
+-----------+
| min_price |
+-----------+
| 3.49 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MIN(prod_name) AS min_prod_name FROM Products;
+--------------------+
| min_prod_name |
+--------------------+
| 12 inch teddy bear |
+--------------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.5 SUM()函数
SUM()函数用来返回指定列值的和。
SUM()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM OrderItems WHERE order_num = 20005;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
+-----------+------------+---------+----------+------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 200 |
+---------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 1648.00 |
+-------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.2 聚集不同值
对以上5个聚集函数都可以如下使用：
- 对所有行执行计算，指定ALL参数或不指定参数（ALL参数是默认行为）
- 只包含不同的值，指定DISTINCT参数。
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 4.240000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.3 组合聚集函数
SELECT语句可根据需要包含多个聚集函数。
注意：指定别名，不应该使用表中实际的列名。
MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS avg_price FROM Products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | avg_price |
+-----------+-----------+-----------+-----------+
| 9 | 3.49 | 11.99 | 6.823333 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>