在sql中对成绩表的各种查询

2022-06-15 07:03:23 文档大全网 [ 字体：小中大 ] [ 阅读： ]

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《在sql中对成绩表的各种查询》，欢迎阅读！
成绩,各种,查询,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_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;