SQL第8-9课：函数

• 聚集函数：对（一列的）某些行运行计算的函数，计算并返回一个值。

函数	说明
AVG()	返回平均值
COUNT()	行数
MAX()	最大值
MIN()	最小值
SUM()	最小值

mysql> select avg(prod_price) as avg_price from Products;
+-----------+
| avg_price |
+-----------+
|  6.823333 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(prod_price) as avg_price from Products where vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  3.865000 |
+-----------+
1 row in set (0.00 sec)

只计算不同的值

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

组合集聚函数

mysql> select count(*) as num_items,
    ->  min(prod_price) as price_min,
    -> max(prod_price) as price_max,
    -> avg(prod_price) as price_avg
    -> from Products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|         9 |      3.49 |     11.99 |  6.823333 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)