mysql检索相差30min时_Mysql对检索结果进行汇总COUNT,AVG,MIN,MAX,SUM

1、AVG

mysql> SELECT AVG(prod_price) AS avg_price FROM products;结果：

+-----------+

| avg_price |

+-----------+

| 16.133571 |

+-----------+

1 row in set (0.05 sec)

2、COUNT

mysql> SELECT COUNT(*) AS num_cust FROM customers;或

mysql> SELECT COUNT(cust_email) AS num_cust FROM customers;指定列名和使用*的区别是：指定列名则忽略NULL值，只对含有具体值的行进行计数；不指定列名则对表中的行数进行计数，不管表列中包含的是空值NULL还是非空值。

3、MAX

mysql> SELECT MAX(prod_price) AS max_price FROM products;结果：

+-----------+

| max_price |

+-----------+

|     55.00 |

+-----------+

1 row in set (0.05 sec)

4、MIN

mysql> SELECT MIN(prod_price) AS max_price FROM products;结果：

+-----------+

| max_price |

+-----------+

|      2.50 |

+-----------+

1 row in set (0.00 sec)

5、SUM

mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;结果：

+---------------+

| items_ordered |

+---------------+

|            19 |

+---------------+

1 row in set (0.08 sec)

6、组合使用聚合函数

上面的例子中都只涉及单个聚合函数，但实际上，我们可以在一个SELECT语句中使用多个聚合函数。

mysql> SELECT COUNT(*) AS num_items,

-> MIN(prod_price) AS min_price,

-> MAX(prod_price) AS max_price,

-> AVG(prod_price) AS price_avg

-> FROM products;结果是：

+-----------+-----------+-----------+-----------+ | num_items | min_price | max_price | price_avg | +-----------+-----------+-----------+-----------+ |        14 |      2.50 |     55.00 | 16.133571 | +-----------+-----------+-----------+-----------+ 1 row in set (0.00 sec)