数据准备:mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> age SMALLINT UNSIGNED DEFAULT 10,
-> sex BOOLEAN
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT users VALUES(DEFAULT,'tom',23,1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT users VALUES(DEFAULT,'tomi',23,1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT users VALUES(DEFAULT,'jerry',26,0);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users VALUES(DEFAULT,'jerry',26,NULL);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT users VALUES(DEFAULT,'marry',DEFAULT,NULL);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT users VALUES(DEFAULT,'marry',DEFAULT,1);
Query OK, 1 row affected (0.11 sec)
mysql> INSERT users VALUES(DEFAULT,'tom',34,0);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM users;
+----+----------+------+------+
| id | username | age | sex |
+----+----------+------+------+
| 1 | tom | 23 | 1 |
| 2 | tomi | 23 | 1 |
| 3 | jerry | 26 | 0 |
| 4 | jerry | 26 | NULL |
| 5 | marry | 10 | NULL |
| 6 | marry | 10 | 1 |
| 7 | tom | 34 | 0 |
+----+----------+------+------+
7 rows in set (0.00 sec)
1、聚集函数
聚集函数是运行在行组上,计算和返回单个值的函数。
SQL聚集函数
函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某个列之和
(1)、AVG()函数
可以返回所有列的平均值,也可以返回特定列的平均值。mysql> SELECT AVG(age) AS avg_age FROM users; #计算所有记录中age字段的均值。
+---------+
| avg_age |
+---------+
| 21.7143 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT AVG(age) AS avg_age FROM users WHERE sex=1;#计算所有的sex=1的记录中age字段的均值。
+---------+
| avg_age |
+---------+
| 18.6667 |
+---------+
1 row in set (0.00 sec)
(2)、COUNT()函数
COUNT(*)对表中行的数目进行计数,不管表列中包含的是空(NULL)还是非空值;
COUNT(column)对特定列中有值的行进行计算,忽略NULL值。mysql> SELECT COUNT(*) FROM users;#对表中行的数目进行计数
+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(sex) FROM users;#对sex列中,有值的行进行计算,忽略NULL值。
+------------+
| COUNT(sex) |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
(3)、MAX()函数
MAX()返回指定列的最大值,要求指定列名,忽略NULL值。
在MySQL中,MAX()函数可以对非数据列使用,在用于文本数据时,如果数据按相应的列排序,MAX()返回最后一行。mysql> SELECT MAX(age) FROM users;
+----------+
| MAX(age) |
+----------+
| 34 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT MAX(sex) FROM users;
+----------+
| MAX(sex) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT MAX(username) FROM users;
+---------------+
| MAX(username) |
+---------------+
| tomi |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT username FROM users ORDER BY username ASC; #可以看到,tomi排在最后。
+----------+
| username |
+----------+
| jerry |
| jerry |
| marry |
| marry |
| tom |
| tom |
| tomi |
+----------+
7 rows in set (0.00 sec)
(4)、MIN()函数
MIN()返回指定列的最小值,要求指定列名,忽略NULL值。
在MySQL中,MIN()函数可以对非数据列使用,在用于文本数据时,如果数据按相应的列排序,MIN()返回最前面的一行。mysql> SELECT MIN(sex) FROM users;
+----------+
| MIN(sex) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT MIN(age) FROM users;
+----------+
| MIN(age) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
(5)、SUM()函数
用来返回指定列的和(总计),忽略NULL值的行。mysql> SELECT SUM(age) FROM users;
+----------+
| SUM(age) |
+----------+
| 152 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT SUM(sex) FROM users;
+----------+
| SUM(sex) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT SUM(age) AS sum_age FROM users WHERE sex=1;#对sex=1的记录,求年龄之和。
+---------+
| sum_age |
+---------+
| 56 |
+---------+
1 row in set (0.00 sec)
SUM()也可以合计计算值。mysql> SELECT * FROM users WHERE sex=0;
+----+----------+------+------+
| id | username | age | sex |
+----+----------+------+------+
| 3 | jerry | 26 | 0 |
| 7 | tom | 34 | 0 |
+----+----------+------+------+
2 rows in set (0.00 sec)
mysql> SELECT SUM(age*id) AS sum_age_id FROM users WHERE sex=0;
+------------+
| sum_age_id |
+------------+
| 316 |
+------------+
1 row in set (0.00 sec)
2、聚集不同值
DISTINCT关键字:如果使用DISTINCT关键字,则需要去掉重复值。(即:重复的数值只能使用1次)mysql> SELECT * FROM users WHERE sex=1;
+----+----------+------+------+
| id | username | age | sex |
+----+----------+------+------+
| 1 | tom | 23 | 1 |
| 2 | tomi | 23 | 1 |
| 6 | marry | 10 | 1 |
+----+----------+------+------+
3 rows in set (0.00 sec)
mysql> SELECT AVG(DISTINCT age) FROM users WHERE sex=1;#对23,10求均值。
+-------------------+
| AVG(DISTINCT age) |
+-------------------+
| 16.5000 |
+-------------------+
1 row in set (0.03 sec)
mysql> SELECT AVG(age) FROM users WHERE sex=1;#对23,23,10求均值
+----------+
| AVG(age) |
+----------+
| 18.6667 |
+----------+
1 row in set (0.00 sec)
3、组合聚集函数
聚集函数可以组合使用mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(age) AS age_avg FROM users;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 7 | 10 | 34 | 21.7143 |+-----+---------+---------+---------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(age) AS age_avg FROM users WHERE sex=1;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 3 | 10 | 23 | 18.6667 |+-----+---------+---------+---------+1 row in set (0.00 sec)mysql> SELECT * FROM users WHERE sex=1;+----+----------+------+------+| id | username | age | sex |+----+----------+------+------+| 1 | tom | 23 | 1 || 2 | tomi | 23 | 1 || 6 | marry | 10 | 1 |+----+----------+------+------+3 rows in set (0.00 sec)mysql> SELECT COUNT(*) AS num,MIN(age) AS age_min,MAX(age) AS age_max,AVG(DISTINCT age) AS age_avg FROM users WHERE sex=1;+-----+---------+---------+---------+| num | age_min | age_max | age_avg |+-----+---------+---------+---------+| 3 | 10 | 23 | 16.5000 |+-----+---------+---------+---------+1 row in set (0.00 sec)
本文由职坐标整理并发布,希望对同学们有所帮助。了解更多详情请关注数据库MySQL频道!