mysql workbench8.0.22汉化_22.MySQL中的GROUP BY(分组)

1.准备

CREATE DATABASE mahaiwuji;USE mahaiwuji;CREATE TABLE student (    sid INT (4) PRIMARY KEY,    sname VARCHAR (36),   course VARCHAR (36),   score INT,   grade INT (4)) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO student VALUES (1,'aa','语文',60,1);INSERT INTO student VALUES (2,'aa','数学',70,1);INSERT INTO student VALUES (3,'aa','英语',80,1);INSERT INTO student VALUES (4,'bb','语文',70,1);INSERT INTO student VALUES (5,'bb','数学',60,1);INSERT INTO student VALUES (6,'bb','英语',60,1);INSERT INTO student VALUES (7,'cc','语文',90,2);INSERT INTO student VALUES (8,'cc','数学',50,2);INSERT INTO student VALUES (9,'cc','英语',60,2);INSERT INTO student VALUES (10,'dd','语文',70,2);INSERT INTO student VALUES (11,'dd','数学',60,2);INSERT INTO student VALUES (12,'dd','英语',90,2);

2.实例演示

目的：就是为了统计。

格式：group by 字段的集合。

功能：把表中的记录按照字段分成不同的组。

-- 查询每个人的平均分SELECT sname,AVG(score) FROM student GROUP BY sname;

-- 查询每个班的平均分SELECT grade,AVG(score) FROM student GROUP BY grade;

-- 查询每个班的每科的平均分SELECT grade,course,AVG(score) FROM student GROUP BY grade,course;