MySQL学习足迹记录10--汇总数据--MAX(),MIN(),AVG(),SUM(),COUNT()
来源:互联网 发布:360办公软件下载 编辑:程序博客网 时间:2024/06/06 21:39
本文所用到的数据
mysql>SELECT prod_price FROM products;
+------------+
| prod_price |
+------------+
| 5.99 |
| 9.99 |
| 14.99 |
| 13.00 |
| 10.00 |
| 2.50 |
| 3.42 |
| 35.00 |
| 55.00 |
| 8.99 |
| 50.00 |
| 4.49 |
| 2.50 |
| 10.00 |
+------------+
14 rows in set (0.00 sec)
1.聚集函数
AVG(): 返回某列的平均值
COUNT(): 返回会某列的行数
MAX(): 返回会某列的最大值
MIN(): 返回会某列的最小值
SUM(): 返回会某列值之和
2.AVG()函数
Examples:
mysql> SELECT AVG(prod_price) AS avg_price
-> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.01 sec)
*返回特定列或行的平均值
Examples:
mysql> SELECT AVG(prod_price) AS avg_price #过滤出vend_id为1003的产品,再求平均值
-> FROM products
-> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)
Tips:
AVG()只能用来求特定数值列的平均值,为了获得多个列的平均值,必须使用多个AVG()函数
AVG()函数忽略列值为NULL的行
3.COUNT()函数
*COUNT(*)对表中行的数目进行计数,不管列标中包含的是空值(NULL)还是非空值
*COUNT(column)对特定的列中具有值的行进行计数,忽略NULL值
Examples:
mysql> select COUNT(*) AS count_prod from products;
+------------+ #products表中行的数目进行计数
| count_prod |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
先列出cust_email的内容
mysql>SELECT cust_email FROM customers;
+---------------------+
| cust_email |
+---------------------+
| ylee@coyote.com |
| NULL |
| rabbit@wascally.com |
| sam@yosemite.com |
| NULL |
+---------------------+
5 rows in set (0.00 sec)
对cust_email进行计数
mysql> SELECT COUNT(cust_email) AS num_cust
-> FROM customers; #忽略NULL值
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
4.MAX()函数
返回指定列中的最大值,忽略NULL值
Examples:
mysql> SELECT MAX(prod_price) AS max_price
-> FROM products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
1 row in set (0.00 sec)
5.MIN()函数
*返回指定列的最小值
mysql> SELECT MIN(prod_price) AS min_price
-> FROM products;
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.00 sec)
6.SUM()函数
*返回指定列值的和
mysql> SELECT SUM(prod_price) AS sum_price
-> FROM products;
+-----------+
| sum_price |
+-----------+
| 225.87 |
+-----------+
1 row in set (0.00 sec)
*SUM也可用来合计计算值
Examples:
下面先列出要计算的数据
mysql> SELECT item_price,quantity
-> FROM orderitems
-> WHERE order_num = 20005;
+------------+----------+
| item_price | quantity |
+------------+----------+
| 5.99 | 10 |
| 9.99 | 3 |
| 10.00 | 5 |
| 10.00 | 1 |
+------------+----------+
4 rows in set (0.01 sec)
mysql> SELECT SUM(item_price*quantity) AS total_price
-> FROM orderitems #返回订单中所有的物品价钱之和
-> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
1 row in set (0.00 sec)
7.聚集不同的值,关键字DISTINCT
对于SUM(),MAX(),MIN(),AVG(),COUNT(),默认的参数为ALL,如果要计算只包含不同的值,需指定DISTINCT参数
EXAMPLES:
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
-> FROM products
-> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.02 sec)
8.组合聚集函数
eg:
mysql> SELECT COUNT(*) AS num_items,
-> MIN(prod_price) AS price_min,
-> MAX(prod_price) AS price_min,
-> AVG(prod_price) AS price_avg
-> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_min | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)
mysql>SELECT prod_price FROM products;
+------------+
| prod_price |
+------------+
| 5.99 |
| 9.99 |
| 14.99 |
| 13.00 |
| 10.00 |
| 2.50 |
| 3.42 |
| 35.00 |
| 55.00 |
| 8.99 |
| 50.00 |
| 4.49 |
| 2.50 |
| 10.00 |
+------------+
14 rows in set (0.00 sec)
1.聚集函数
AVG(): 返回某列的平均值
COUNT(): 返回会某列的行数
MAX(): 返回会某列的最大值
MIN(): 返回会某列的最小值
SUM(): 返回会某列值之和
2.AVG()函数
Examples:
mysql> SELECT AVG(prod_price) AS avg_price
-> FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.01 sec)
*返回特定列或行的平均值
Examples:
mysql> SELECT AVG(prod_price) AS avg_price #过滤出vend_id为1003的产品,再求平均值
-> FROM products
-> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)
Tips:
AVG()只能用来求特定数值列的平均值,为了获得多个列的平均值,必须使用多个AVG()函数
AVG()函数忽略列值为NULL的行
3.COUNT()函数
*COUNT(*)对表中行的数目进行计数,不管列标中包含的是空值(NULL)还是非空值
*COUNT(column)对特定的列中具有值的行进行计数,忽略NULL值
Examples:
mysql> select COUNT(*) AS count_prod from products;
+------------+ #products表中行的数目进行计数
| count_prod |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
先列出cust_email的内容
mysql>SELECT cust_email FROM customers;
+---------------------+
| cust_email |
+---------------------+
| ylee@coyote.com |
| NULL |
| rabbit@wascally.com |
| sam@yosemite.com |
| NULL |
+---------------------+
5 rows in set (0.00 sec)
对cust_email进行计数
mysql> SELECT COUNT(cust_email) AS num_cust
-> FROM customers; #忽略NULL值
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
4.MAX()函数
返回指定列中的最大值,忽略NULL值
Examples:
mysql> SELECT MAX(prod_price) AS max_price
-> FROM products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
1 row in set (0.00 sec)
5.MIN()函数
*返回指定列的最小值
mysql> SELECT MIN(prod_price) AS min_price
-> FROM products;
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.00 sec)
6.SUM()函数
*返回指定列值的和
mysql> SELECT SUM(prod_price) AS sum_price
-> FROM products;
+-----------+
| sum_price |
+-----------+
| 225.87 |
+-----------+
1 row in set (0.00 sec)
*SUM也可用来合计计算值
Examples:
下面先列出要计算的数据
mysql> SELECT item_price,quantity
-> FROM orderitems
-> WHERE order_num = 20005;
+------------+----------+
| item_price | quantity |
+------------+----------+
| 5.99 | 10 |
| 9.99 | 3 |
| 10.00 | 5 |
| 10.00 | 1 |
+------------+----------+
4 rows in set (0.01 sec)
mysql> SELECT SUM(item_price*quantity) AS total_price
-> FROM orderitems #返回订单中所有的物品价钱之和
-> WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
1 row in set (0.00 sec)
7.聚集不同的值,关键字DISTINCT
对于SUM(),MAX(),MIN(),AVG(),COUNT(),默认的参数为ALL,如果要计算只包含不同的值,需指定DISTINCT参数
EXAMPLES:
mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
-> FROM products
-> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.02 sec)
8.组合聚集函数
eg:
mysql> SELECT COUNT(*) AS num_items,
-> MIN(prod_price) AS price_min,
-> MAX(prod_price) AS price_min,
-> AVG(prod_price) AS price_avg
-> FROM products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_min | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)
- MySQL学习足迹记录10--汇总数据--MAX(),MIN(),AVG(),SUM(),COUNT()
- MySQL之汇总数据(AVG,COUNT,MAX,MIN,SUM)
- Mysql对检索结果进行汇总COUNT,AVG,MIN,MAX,SUM
- mysql之avg(),count(),min()/max(),sum()函数
- mysql之count,max,min,sum,avg,celing,floor
- count,sum,avg,max,min__mysql学习笔记
- oracle学习笔记(二)substr,nvt,max,min,avg,sum,count,group,having
- 常用集函数,count(),sum(),avg(),max(),min()
- linq to sql 用法 ----- count/sum/min/max/avg
- LINQ to SQL Count/Sum/Min/Max/Avg Join
- HQL中的聚合函数:count()sum()avg()max()min()
- 12 聚集函数:avg() count() max() min() sum()
- oracle中的聚合函数count、max、min、sum、avg等等
- 常用集函数,count(),sum(),avg(),max(),min()
- mysql sum Count AVG
- SQL中count()/max() /min()/sum()avg()/sum()等函数用法
- LINQ体验——LINQ语句之Select/Distinct和Count/Sum/Min/Max/Avg
- LINQ to SQL语句(3)之Count/Sum/Min/Max/Avg
- Android:Unable to execute dex: Multiple dex files define Lcom/androidquery/util/Constants的解决办法
- Activity的启动方式和flag详解
- 伪静态的实现和客户端跳转与服务器端跳转
- Arithmetic Progression解题报告
- Ubuntu 12.04下编译安装 GCC4.1.2 matlab 低版本gcc
- MySQL学习足迹记录10--汇总数据--MAX(),MIN(),AVG(),SUM(),COUNT()
- html解析器不能正确过滤注释
- oracle数据库常用的导入导出
- 程序算法与人生选择
- 对jquery框架的理解
- C语言中判断int,long型等变量是否赋值的方法
- Hibernate3.0中的session.find()问题
- 2,少年白_出差的这些日子
- 003javase中的一些模糊的知识点