《在sql中对成绩表的各种查询.docx》由会员分享，可在线阅读，更多相关《在sql中对成绩表的各种查询.docx（3页珍藏版）》请在三一办公上搜索。
1、在sql中对成绩表的各种查询#成绩表：编号 姓名 科目名称 分数 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_
2、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) valu
3、es(小猫,数学,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(n
4、ame,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
5、; #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 nam
6、e ;(错) 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;
