mysql中group分组与函数统计
select查询中几个常规的函数:
max():最大值
count():次数
avg():求平均值
min():求最小值
sum():求和
#使用avg()函数求表中四人的平均年龄.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.01 sec)
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 19.2500 |
+----------+
1 row in set (0.00 sec)
#使用max()函数查看年纪最大的.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.01 sec)
mysql> select max(age) from student;
+----------+
| max(age) |
+----------+
| 23 |
+----------+
1 row in set (0.00 sec)
#使用min()函数查看年纪最小的.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.01 sec)
mysql> select min(age) from student;
+----------+
| min(age) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
#使用count()函数计算总共有几个人.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.01 sec)
mysql> select count(*) from student;
+------------+
| count(age) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)
注意:使用count(*)和count(1)的查询结果是一样的.
#使用sum()计算这四个人的年龄总和.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.01 sec)
mysql> mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
| 77 |
+----------+
1 row in set (0.00 sec)
#求四人年龄和id想成的总和.
mysql> select * from student;
+-----+--------+------+------+
| SNO | SNAME | AGE | SEX |
+-----+--------+------+------+
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 4 | NULL | 10 | NULL |
| 5 | 张友 | 22 | 男 |
+-----+--------+------+------+
4 rows in set (0.00 sec)
mysql> select sum(sno*age) from student;
+--------------+
| sum(sno*age) |
+--------------+
| 217 |
+--------------+
1 row in set (0.00 sec)
#使用group分组求平均值.
mysql> select * from sc;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 1 | K1 | 83 |
| 2 | K1 | 85 |
| 2 | K5 | 90 |
| 5 | K1 | 92 |
| 5 | K5 | 84 |
| 5 | K8 | 80 |
+-----+-----+-------+
6 rows in set (0.00 sec)
mysql> select cno,avg(score) from sc group by cno;
+-----+------------+
| cno | avg(score) |
+-----+------------+
| K1 | 86.6667 |
| K5 | 87.0000 |
| K8 | 80.0000 |
+-----+------------+
3 rows in set (0.00 sec)
#查询每个年级中分数最高的.
mysql> select * from sc;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 1 | K1 | 83 |
| 2 | K1 | 85 |
| 2 | K5 | 90 |
| 5 | K1 | 92 |
| 5 | K5 | 84 |
| 5 | K8 | 80 |
+-----+-----+-------+
6 rows in set (0.00 sec)
mysql> select cno,max(score) from sc group by cno;
+-----+------------+
| cno | max(score) |
+-----+------------+
| K1 | 92 |
| K5 | 90 |
| K8 | 80 |
+-----+------------+
3 rows in set (0.00 sec)
#使用group分组查询cno的次数.
mysql> select * from sc;
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 1 | K1 | 83 |
| 2 | K1 | 85 |
| 2 | K5 | 90 |
| 5 | K1 | 92 |
| 5 | K5 | 84 |
| 5 | K8 | 80 |
+-----+-----+-------+
6 rows in set (0.00 sec)
mysql> select cno,count(*) from sc group by cno;
+-----+----------+
| cno | count(*) |
+-----+----------+
| K1 | 3 |
| K5 | 2 |
| K8 | 1 |
+-----+----------+
3 rows in set (0.00 sec)
#如果查询中加入id使用group分组,会发现默认使用查询到的cno第一次的sno_id,而不是随机选取或者选取平均值.
mysql> select sno,cno from sc;
+-----+-----+
| sno | cno |
+-----+-----+
| 1 | K1 |
| 2 | K1 |
| 5 | K1 |
| 2 | K5 |
| 5 | K5 |
| 5 | K8 |
+-----+-----+
6 rows in set (0.00 sec)
mysql> select sno,cno,count(*) from sc group by cno;
+-----+-----+----------+
| sno | cno | count(*) |
+-----+-----+----------+
| 1 | K1 | 3 |
| 2 | K5 | 2 |
| 5 | K8 | 1 |
+-----+-----+----------+
3 rows in set (0.00 sec)