MySQL分组查询实例2

DDL——学生表——成绩表

1.  

   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

2. 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

3. # 分组显示时，只用被分组的列数据有效，其余无效
   SELECT testname'学科名称',
   sum(score)'科目总分数',
   count(score)'科目考试人数',
   avg(score)'科目平均成绩',
   max(score)'科目最高分',
   min(score)'科目最低分' # 最后一个不写,
   FROM result
   where studentid<5 # 具体的列信息筛选用where
   GROUP BY testname; # 分组
   HAVING avg(score)>70 # 聚合数据筛选用having
   ORDER BY avg(score)desc; # ORDER BY一定在后方，最后是limit

   # 分组显示时，只有被分组的列数据有效，其余无效
   SELECT studentid'学号',username'学生姓名',introduce'学生简介',
   sum(score)'学生各科目总成绩',
   count(score)'学生参与考试科目数量',
   avg(score)'学生所有科目平均成绩',
   max(score)'学生所有科目成绩最高分',
   min(score)'学生所有科目成绩最低分'
   FROM result INNER JOIN class on result.studentid=class.id
   WHERE sex ='女'
   GROUP BY studentid,username,introduce
   having avg(score)>90
   ORDER BY avg(score) desc;
   
   SELECT * FROM result INNER JOIN class on result.studentid=class.id