MySQL分组查询实例

DDL——学生表——成绩表

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdate` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `pwd` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `result` (
  `rid` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
  `testname` varchar(255) DEFAULT NULL COMMENT '测试名称',
  `score` double(255,2) DEFAULT NULL COMMENT '成绩',
  `studentid` int(255) DEFAULT NULL,
  PRIMARY KEY (`rid`),
  KEY `studentid` (`studentid`),
  CONSTRAINT `result_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

DML

INSERT into class VALUES(0,'2024-02-25','香菱','123','15614333260',18,'女','万民堂大厨');
INSERT into class VALUES(0,'2024-02-25','凝光','123','15614333260',26,'女','璃月七星天权星');
INSERT into class VALUES(0,'2024-02-25','刻晴','123','15614333260',23,'女','璃月七星玉衡星');
INSERT into class VALUES(0,'2024-02-25','北斗','123','15614333260',26,'女','大船长');
INSERT into class VALUES(0,'2024-02-25','胡桃','123','15614333260',18,'女','往生堂第77代堂主');
SELECT * FROM class;

INSERT into result values(0,'政治',96,1);
INSERT into result values(0,'地理',95,1);
INSERT into result values(0,'政治',94,1);
INSERT into result values(0,'英语',93,1);
INSERT into result values(0,'历史',92,1);
INSERT into result values(0,'化学',91,1);
INSERT into result values(0,'Java',96,1);
INSERT into result values(0,'Java',95,1);
INSERT into result values(0,'政治',94,1);
INSERT into result values(0,'物理',93,1);
INSERT into result values(0,'物理',92,1);
INSERT into result values(0,'化学',91,1);

DQL

select testName,count(testName),sum(score),avg(score) from result GROUP BY testName;