mysql中group分组与函数统计
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql中group分组与函数统计,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5176字,纯文字阅读大概需要8分钟。
内容图文
![mysql中group分组与函数统计](/upload/InfoBanner/zyjiaocheng/918/0443075198d0415d9443b778e07104c8.jpg)
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)
内容总结
以上是互联网集市为您收集整理的mysql中group分组与函数统计全部内容,希望文章能够帮你解决mysql中group分组与函数统计所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。