本节课,我们重点了解一下MySQL 中的高级查询功能,主要包含GROUP BY 分组查询、HAVING 过滤器、DISTINCT 子句。
一.GROUP BY
当需要进行分组统计数据的时候,比如语文班、数学班、英语班每个班所有学生的成绩总和。我们创建think_student 表,字段为:id、class、student、score。
通过新增语句新增如下六条数据填充。
INSERT INTO `think_student` VALUES ('1', '语文班', '蜡笔小新', '80');
INSERT INTO `think_student` VALUES ('2', '数学班', '樱桃小丸子', '95');
INSERT INTO `think_student` VALUES ('3', '英语班', '路飞', '77');
INSERT INTO `think_student` VALUES ('4', '语文班', '黑崎一护', '88');
INSERT INTO `think_student` VALUES ('5', '数学班', '孙悟空', '97');
INSERT INTO `think_student` VALUES ('6', '英语班', '星矢', '93');
如果我们想要获取所有班级分数的总和,那么SQL 写法如下:
SELECT SUM(score) FROM think_student;
如果我们想要获取某一个班级分数的总和,那么SQL 写法如下:
SELECT SUM(score) FROM think_student WHERE class='语文班';
但如果我们想要每个班级的分数总和,并且一次性显示出来,这时就需要采用GROUPBY 分组统计来实现这个需求,SQL 写法如下:
SELECT class,SUM(score) FROM think_student GROUP BY class;
二.HAVING
HAVING 和我们以前用过的ON、WHERE 一样,是过滤器。只不过这个过滤器可以在虚拟
表中的字段进行过滤,主要是对分组条件进行的筛选作用。我们添加一个上一次成绩的字段:prev_score,分别为:77,96,80,90,95,90。然后,求出进步本次成绩进步的同学。
//首先求出成绩差,进步的是正值,退步的是负值
SELECT student,score-prev_score FROM think_student;
//通过WHERE 过滤掉负值和零的学员
SELECT student,score-prev_score FROM think_student WHERE score-prev_score>0;
//由于score-prev_score 作为字段显示不方便,使用AS 生成一个虚拟字段
SELECT student,score-prev_score AS diff FROM think_student WHEREscore-prev_score>0;
//而虚拟字段,是无法用WHERE 进行过滤,这样会报错
SELECT student,score-prev_score AS diff FROM think_student WHERE diff>0;
//虚拟字段,采用HAVING 才能过滤
SELECT student,score-prev_score AS diff FROM think_student HAVING diff>0;
//结合分组,找出班级总分超过190 的
SELECT class,SUM(score) AS sum FROM think_student GROUP BY class HAVING sum>190;
三.DISTINCT 子句
如果字段指定了DISTINCT 子句,那么会创建一个内存临时表,并且对这个列增加了一个唯一索引,以此出去重复数据。
//去除相同的数据SELECT DISTINCT prev_score FROM think_student;