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的班级