#成绩表：编号 姓名 科目名称 分数 create table tb_scorce ( id int primary key auto_increment, name varchar(20), courese_name varchar(30), score float ); insert into tb_scorce(name,courese_name,score) values('大猫','外语',30); insert into tb_scorce(name,courese_name,score) values('大猫','计算机',59.9); insert into tb_scorce(name,courese_name,score) values('大猫','数学',5); insert into tb_scorce(name,courese_name,score) values('大猫','C#',80); insert into tb_scorce(name,courese_name,score) values('小猫','外语',88.8); insert into tb_scorce(name,courese_name,score) values('小猫','计算机',99.9); insert into tb_scorce(name,courese_name,score) values('小猫','数学',9); insert into tb_scorce(name,courese_name,score) values('小猫','C#',80); insert into tb_scorce(name,courese_name,score) values('中猫','外语',88.8); insert into tb_scorce(name,courese_name,score) values('中猫','计算机',99.9); insert into tb_scorce(name,courese_name,score) values('老猫','数学',9); insert into tb_scorce(name,courese_name,score) values('老猫','C#',80); insert into tb_scorce(name,courese_name,score) values('猫猫','C#',80); #1.查询每个学生的平均成绩，最高成绩、最低成绩、总成绩 select name,avg(score),max(score),min(score),sum(score) from tb_scorce group by name; #2.查询每个学生的考试的科目数 select name, count(courese_name) from tb_scorce group by name; #3.查询每个学生的考试的科目数大于2门的学生信息 查询出每个学生考试的科目数 select name, count(courese_name) from tb_scorce group by name; 筛选出科目数大于2的学生信息 select name, count(courese_name) from tb_scorce group by name where count(courese_name)>2;(错) select name, count(courese_name) from tb_scorce where count(courese_name)>2 group by name ;(错) select name, count(courese_name) from count(courese_name)>2; order by 学生的成绩从高到低排序 select * from tb_scorce order by score desc; tb_scorce group by name having 学生的成绩从低到高排序 select * from tb_scorce order by score asc; 附加： 前五条信息 select * from tb_student limit 0,5; select * from tb_student limit 5; 最新的五条信息 select * from tb_student order by id desc limit 5; 查询第三条到第五条信息 select * from tb_student limit 2,3; 年龄从高到低的前三条信息 select * from tb_student order by age desc,id limit 3; 年龄从高到低的第三条到第十条信息信息 2, 7 select * from tb_student order by age desc,id limit 2, 7; 本文来源：https://www.wddqw.com/doc/f1132248482fb4daa58d4bb1.html