mysql-聚合函数

mysql-聚合函数

 

mysql中聚合函数其实只有5个，

1.COUNT()函数：用来统计记录的条数；

2.SUM()函数:是求和函数； 

3.AVG()函数:是求平均值的函数 ;

4.MAX()函数是求最大值的函数 

5.MIN()函数是求最小值的函数 

具体的实列：

/*
SQLyog 企业版 - MySQL GUI v8.14 
MySQL - 5.1.49-community 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `t_grade` (
`id` int ,
`stuName` varchar (60),
`course` varchar (60),
`score` int 
); 
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','张三','数学','90');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','张三','英语','87');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','语文','79');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','数学','95');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英语','80');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','语文','77');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','数学','81');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英语','89');

SELECT COUNT(*) FROM t_grade;//统计有多少条数据

SELECT COUNT(*) AS total FROM t_grade;//把统计的条数放在totle列中

SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;//统计出每个学生学习了几门课程

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";//张三的总分

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;//统计每个学生的总分

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;