MySql聚合函数和GROUP BY
使用该篇文章中建立的数据库http://my.oschina.net/xinxingegeya/blog/311753
MySQL聚合函数
SUM, COUNT, MAX, MIN, AVG
mysql> select * from student_course;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 01 | 1234 | 10 |
| 01 | 1235 | 90 |
| 02 | 1234 | 70 |
| 02 | 1235 | 90 |
+-----+------+-------+
4 rows in set
COUNT函数
SQL COUNT(column_name) 语法
COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入):
SELECT COUNT(column_name) FROM table_name
SQL COUNT(*) 语法
COUNT(*) 函数返回表中的记录数:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:
SELECT COUNT(DISTINCT column_name) FROM table_name
mysql> select count(score) from student_course;
+--------------+
| count(score) |
+--------------+
| 4 |
+--------------+
1 row in set
SUM函数
SUM 函数返回数值列的总数(总额)。
mysql> select sum(score) from student_course;
+------------+
| sum(score) |
+------------+
| 260 |
+------------+
1 row in set
MAX函数
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
mysql> select max(score) from student_course;
+------------+
| max(score) |
+------------+
| 90 |
+------------+
1 row in set
MIN函数
MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
mysql> select min(score) from student_course;
+------------+
| min(score) |
+------------+
| 10 |
+------------+
1 row in set
mysql>
AVG函数
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
mysql> select avg(score) from student_course where sid = '01';
+------------+
| avg(score) |
+------------+
| 50 |
+------------+
1 row in set
mysql>
GROUP BY子句
根据一个或多个列对结果集进行分组
先看下面这个使用GROUP BY的例子
mysql> select * from student_course;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 01 | 1234 | 10 |
| 01 | 1235 | 90 |
| 02 | 1234 | 70 |
| 02 | 1235 | 90 |
+-----+------+-------+
4 rows in set
mysql> select * from student_course group by sid;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 01 | 1234 | 10 |
| 02 | 1234 | 70 |
+-----+------+-------+
2 rows in set
group by sid分组只能分成两个组,每个组有两条数据,但是只返回了每组的第一条记录。。。
如果根据sid和cid分组就是这样的结果:
mysql> select * from student_course group by sid,cid;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 01 | 1234 | 10 |
| 01 | 1235 | 90 |
| 02 | 1234 | 70 |
| 02 | 1235 | 90 |
+-----+------+-------+
4 rows in set
mysql>
根据sid和cid分成了四组,返回每组的第一条记录(虽然每组就是一条记录)。。。。
GROUP BY和聚合函数结合使用
mysql> select * from student_course;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 01 | 1234 | 10 |
| 01 | 1235 | 90 |
| 02 | 1234 | 70 |
| 02 | 1235 | 90 |
+-----+------+-------+
4 rows in set
mysql>
查询学生学号以及其所有课程的平均成绩
mysql> select sid,avg(score) from student_course group by sid;
+-----+------------+
| sid | avg(score) |
+-----+------------+
| 01 | 50 |
| 02 | 80 |
+-----+------------+
2 rows in set
mysql>
根据上表,我们知道有两个学生,其所有课程的平均成绩分别为50,80;
查询学生学号,姓名,以及其所选课程的平均成绩
mysql> select a.sid,a.sname,avg(b.score)
-> from student a,student_course b
-> where a.sid = b.sid
-> group by a.sid , a.sname
-> ;
+-----+-------+--------------+
| sid | sname | avg(b.score) |
+-----+-------+--------------+
| 01 | 赵一 | 50 |
| 02 | 赵二 | 80 |
+-----+-------+--------------+
2 rows in set
where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行。
having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件显示特定的组,也可以使用多个分组标准进行分组。
where在分组之前过滤数据,然后根据group by指定的列进行分组,然后根据having条件过滤分组。having条件中可能包含聚合函数。。
查询学生学号,姓名,以及其所选课程的平均成绩另一种查询方式
mysql> select a.sid,a.sname,avg(b.score) from student a left join student_course b on a.sid = b.sid
-> group by a.sid,a.sname;
+-----+-------+--------------+
| sid | sname | avg(b.score) |
+-----+-------+--------------+
| 01 | 赵一 | 50 |
| 02 | 赵二 | 80 |
| 03 | 赵三 | NULL |
| 04 | 赵四 | NULL |
+-----+-------+--------------+
4 rows in set
mysql>
=====END=====