使用聚合函数查询

我不是女神ヾ 2022-03-27 05:52 210阅读 0赞

1 COUNT（） 函数

count函数如果指定列，如count（province），会忽略NULL的值 ，如果不指定列，使用count（\*），所有记录都不会忽略。

mysql> select * from fruits;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | b11  |  101 | apple      |    4.00 | NULL     |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    4 rows in set (0.00 sec)
    
    mysql> select count(*) as total from fruits;
    +-------+
    | total |
    +-------+
    |     4 |
    +-------+
    1 row in set (0.00 sec)
    
    mysql> select count(province) from fruits;
    +-----------------+
    | count(province) |
    +-----------------+
    |               2 |
    +-----------------+
    1 row in set (0.00 sec)

2 SUM（）函数

SUM 可以求指定列的总和。

mysql> select * from fruits;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | b11  |  101 | apple      |    4.00 | NULL     |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    4 rows in set (0.01 sec)
    
    mysql> select SUM(f_price) as 'Total Price' from fruits;
    +-------------+
    | Total Price |
    +-------------+
    |       30.60 |
    +-------------+
    1 row in set (0.01 sec)
    
    mysql> select SUM(f_price) as "Total Price" from fruits group by s_id;
    +-------------+
    | Total Price |
    +-------------+
    |       19.40 |
    |       11.20 |
    +-------------+
    2 rows in set (0.00 sec)

3 AVG() 函数

mysql> select AVG(f_price) from fruits;
    +--------------+
    | AVG(f_price) |
    +--------------+
    |     7.650000 |
    +--------------+
    1 row in set (0.00 sec)

4 MAX()函数和MIN（）函数

用法相同，这里只以MAX（）函数为例。

max函数不仅可以比较数值，还可以比较字符或字符串。

mysql> select max(f_price) from fruits group by s_id;
    +--------------+
    | max(f_price) |
    +--------------+
    |        10.20 |
    |        11.20 |
    +--------------+
    2 rows in set (0.00 sec)