MySQL常用语句（五）：分组查询
1、CREATE TABLE class_info(`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',`class` int(11) NOT NULL COMMENT '班级',`name` char(10) NOT NULL COMMENT '姓名',`score` int(11) NOT NULL COMMENT '分数',PRIMARY KEY (`id`),index index_score(`score`)) ENGINE=InnoDB CHARSET=utf8;
创建表，以便后续使用
2、insert into class_info(`class`,`name`,`score`) values (1601,'a',87),(1601,'b',90),(1602,'d',91),(1602,'c',85);
写入测试数据
3、select * from class_info;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
| 1 | 1601 | a | 87 |
| 2 | 1601 | b | 90 |
| 3 | 1602 | d | 91 |
| 4 | 1602 | c | 85 |
+----+-------+------+-------+
显示表中所有数据
4、select * from class_info group by score;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
| 4 | 1602 | c | 85 |
| 1 | 1601 | a | 87 |
| 2 | 1601 | b | 90 |
| 3 | 1602 | d | 91 |
+----+-------+------+-------+
根据分数字段排序（升序）显示全表数据
5、select * from class_info group by score desc;
+----+-------+------+-------+
| id | class | name | score |
+----+-------+------+-------+
| 3 | 1602 | d | 91 |
| 2 | 1601 | b | 90 |
| 1 | 1601 | a | 87 |
| 4 | 1602 | c | 85 |
+----+-------+------+-------+
根据分数字段排序（降序）显示全表数据
6、select class,count(*) from class_info group by class;
+-------+----------+
| class | count(*) |
+-------+----------+
| 1601 | 2 |
| 1602 | 2 |
+-------+----------+
显示每个班级的人数
7、select class,group_concat(name) from class_info group by class;
+-------+--------------------+
| class | group_concat(name) |
+-------+--------------------+
| 1601 | a,b |
| 1602 | d,c |
+-------+--------------------+
按班级分开显示学生名字
8、select class,max(score) as maxscore from class_info group by class;
+-------+----------+
| class | maxscore |
+-------+----------+
| 1601 | 90 |
| 1602 | 91 |
+-------+----------+
显示每个班的最高分
9、select class,avg(score) as avgscore from class_info group by class;
+-------+----------+
| class | avgscore |
+-------+----------+
| 1601 | 88.5000 |
| 1602 | 88.0000 |
+-------+----------+
显示每个班的平均分数
10、select class,avg(score) as avgscore from class_info group by class having avgscore>88;
+-------+----------+
| class | avgscore |
+-------+----------+
| 1601 | 88.5000 |
+-------+----------+
显示平均分大于88的班级
感谢分享 有用
发表评论