MySQL学习——SQL查询语句(使用集合函数查询)(二)
集合函数包括COUNT(),SUM(),AVG(),MAX()和MIN()分别用于计数,求和,求平均值,求最大值和最小值。
1.COUNT()函数
count()函数用来统计记录的条数,如果统计examplee表中有多少条记录,可以使用OCUNT()函数,如果统计examplee表中不同部门的人数,也可以使用COUNT()函数。
mysql> select COUNT(*) from examplee;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select d_id,count(*) from examplee group by d_id;
+------+----------+
| d_id | count(*) |
+------+----------+
| 1001 | 2 |
| 1003 | 1 |
| 1004 | 1 |
+------+----------+
3 rows in set (0.00 sec)
mysql>
2.SUM()函数
SUM()函数式求和函数,使用SUM()函数可以求出表中某个字段取值的总和,例如可以使用SUM()函数求学生的总成绩。
mysql> select d_id,sum(age) from examplee where d_id=1001;
+------+----------+
| d_id | sum(age) |
+------+----------+
| 1001 | 45 |
+------+----------+
1 row in set (0.00 sec)
mysql> select * from examplee;
+------+------+--------+------+------+----------+
| num | d_id | name | age | sex | homeaddr |
+------+------+--------+------+------+----------+
| 1 | 1001 | aa | 24 | w | abc |
| 3 | 1003 | cxsa | 25 | w | abc |
| 2 | 1001 | cxsa | 21 | m | abc |
| 4 | 1004 | ccdxsa | 27 | w | abc |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)
mysql>
3.AVG()函数
AVG()函数是求平均值的函数,使用AVG()函数可以求出表中某个字段取值的平均值
使用AVG()函数求平均年龄;
mysql> select AVG(age) from examplee;
+----------+
| AVG(age) |
+----------+
| 24.2500 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql> select d_id,AVG(age) from examplee group by d_id;
+------+----------+
| d_id | AVG(age) |
+------+----------+
| 1001 | 22.5000 |
| 1003 | 25.0000 |
| 1004 | 27.0000 |
+------+----------+
3 rows in set (0.00 sec)
mysql>
4.MAX()函数
MAX()函数求最大值的函数,使用MAX()函数可以求出表中某个字段取值的最大值:
mysql> mysql> select MAX(age) from examplee; +----------+ | MAX(age) | +----------+ | 27 | +----------+ 1 row in set (0.00 sec) mysql> select d_id,MAX(age) from examplee group by d_id; +------+----------+ | d_id | MAX(age) | +------+----------+ | 1001 | 24 | | 1003 | 25 | | 1004 | 27 | +------+----------+ 3 rows in set (0.00 sec) mysql>
mysql> select MAX(name) from examplee;
+-----------+
| MAX(name) |
+-----------+
| cxsa |
+-----------+
1 row in set (0.04 sec)
mysql>
5.MIN()函数
MIN()函数是求最小值的函数,使用MIN()函数可以求出表中某个字段取值的最小值。
mysql> select MIN(age) from examplee;
+----------+
| MIN(age) |
+----------+
| 21 |
+----------+
1 row in set (0.00 sec)
mysql> select d_id,MIN(age) from examplee group by d_id;
+------+----------+
| d_id | MIN(age) |
+------+----------+
| 1001 | 21 |
| 1003 | 25 |
| 1004 | 27 |
+------+----------+
3 rows in set (0.00 sec)
mysql>