目录
二、查询语句中select from where group by having order by的执行顺序
关于mysql的group by关键字大家一定不陌生,在平时的开发过程中经常会用到mysql的group by分组的用法,用来获取数据表中以分组字段作为依据统计数据。下面我们通过一个面试题对group by进行一个简单的了解。
一、什么是Group By以及用法有哪些
group by通俗一点的讲就是通过by后面指定的数据库表字段把数据进行分组,所谓的分组就是把一个大的“数据集合”划分为若干个“小区域数据集合”,最后针对这个“小区域数据集合”进行数据处理。group by通常和聚合函数结合使用,比如count()、sum()等聚合函数。
1、使用group by有几点需要注意的地方:
(1)、group by查询语句中select后面字段必须是by后面的分组字段,如果其他的字段想出现在select中必须要和聚合函数一起使用。
(2)、group by查询语句中返回分组后每个组的第一条数据。
(3)、group by多字段分组是将具有相同多个字段的数据放到同一个分组中,比如group by a,b,意思是将所有具有a字段值和b字段值的数据放到一个分组里。
(4)、group by常用的五种聚合函数:
max(列名):求最大值;
min(列名):求最小值;
sum(列名):求和;
avg(列名):求平均值;
count(列名):统计记录的条数;
2、group by与where联合使用:
当where和group by一起使用的时候,where一定是放在group by前面的,也就是说先对select xxx from xxx的数据集合根据where条件筛选,然后在使用group by对筛选后的数据进行分组,最后得到我们想要的数据集合。注意:where后的条件表达式不允许使用聚合函数。
3、group by和having联合使用:
having只能用在group by之后,对分组以后的数据集结果进行筛选,也就是说使用having的前提条件就是分组。另外having可以和聚合函数一起使用,比如having sum(x)>1000。
4、group by和order by联合使用:
当group by和order by一起使用的时候,会先执行group by进行分组,然后将分组好的数据集进行order by排序,order by x中的x是select 查询字段的某一个字段,order by的位置是放在having后面的。
order by 字段名 desc:降序排列
order by 字段名 asc:升序排列
5、group by和limit联合使用:
group by和limit联合使用时,会先执行group by分组,然后从分组好的数据集合中通过limit取出几条数据,注意,limit的位置要放在order by后面。
limit x 从第一条数据开始,取出x条数据;
limit x,y 从第x条数据开始,取出y条数据;
6、group by和group_concat()函数联合使用:
group by和group_concat()函数一起使用的话,可以将group by分组好的某个字段的数据拼接到一起,默认通过“,”拼接。并且group_concat()函数还可以针对某个字段数据进行内部排序,将最终的排序结果返回。
二、查询语句中select from where group by having order by的执行顺序
在查询语句中用到的关键词主要有六个,并且它们的书写顺序依次为:select--from--where--group by--having--order by。但是,在mysql数据库中这六个关键词执行顺序和书写顺序是不一样的,具体的执行顺序为:from--where--group by--having--select--order by。
1、from xxx从哪个表中获取数据,然后通过where xxx查询条件获取第一个结果集A;
2、将上面的结果集A通过group by xxx分组获取到第二个结果集B;
3、将结果集B通过having xxx进行筛选,获取到第三个结果集C;
4、将结果集C通过select xxx 查看某个字段或者某个字段计算的结果,生成结果集D;
5、将结果集D通过order by进行排序,返回最终的查询结果;
三、group by基础面试题举例
1、创建表并插入数据:
CREATE TABLE `tb_student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(15) DEFAULT NULL COMMENT '姓名',
`subject` varchar(15) DEFAULT NULL COMMENT '学科',
`score` int(3) DEFAULT NULL COMMENT '分数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '数学', '90');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '语文', '50');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('张三', '英语', '40');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '数学', '45');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '语文', '55');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '数学', '30');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '语文', '70');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('李四', '英语', '80');
INSERT INTO `tb_student` (`name`, `subject`, `score`) VALUES ('王五', '英语', '49');
问题一:使用group by分组查询每个学生有几科不及格?sql语句如下:
-- 注:先通过where查出不及格的学生,然后通过group by分组计算每个学生有几科不及格
select name,count(*) from tb_student where score < 60 group by name;
问题二:查出学生不及格科目的平均值是多少?sql语句如下:
-- 注:通过聚合函数avg()计算平均值
select name,avg(score) from tb_student where score < 60 group by name;
问题三:查询每个学生每科成绩的最大值,并倒序展示?sql语句如下:
-- 注:先通过group by结合max()聚合函数查出结果集,然后在通过order by排序
select name,max(score) as score from tb_student group by name order by score desc;
问题四:使用group_concat()函数将不及格学生的成绩详情排序展示?sql语句如下:
select name,group_concat(score order by score desc) as score from tb_student where score < 60 group by name;
问题五:使用group by 和 having查询两门以及两门以上不及格同学的平均分,sql语句如下:
-- 注:使用sum(score<60)来计算不及格的科目数
select name,avg(score) as score,sum(score<60) as gk from tb_student group by name having gk >= 2;