MySQL有关学生成绩查询练习

由于主要是关于问题的解决方法,因此表结构在这不做详细的说明,数据也仅供参考,实验使用。

下面为所建数据表。(接下来的查询会用到下面表和字段)

 

题目要求1.

           查询科目编号为10001高于平均分的学生信息。

思路: 1.求出编号为10001的平均值(avg());

            2.查询学生信息,判断条件:1)学生在编号为10001的科目   2)成绩>平均值;

 

具体sql指令:(方法有很多种,但大体思路相同)

第一种写法:在where条件中使用and将查询结果进行详细约束

SELECT student.* FROM student,score
 where ( student.id=score.stu_id  AND
 score.subject_id=10001 AND score.score>( SELECT AVG(score.score)FROM score));
-- 方法1 别法
SELECT student.* from student,score WHERE score.score>=
(SELECT AVG(sc.score)FROM score sc
WHERE sc.subject_id=10001)AND score.subject_id=10001 AND score.stu_id =student.id;

第二种写法:in后面接一个列查询,列子查询查出符合要求的学生id  //列子查询语法:主查询 where 条件 in(列子查询)

SELECT * FROM student stu
WHERE stu.id in
(SELECT sc.stu_id FROM score sc
WHERE sc.subject_id = 10001 AND sc.score >
(SELECT AVG(sc.score) FROM score sc
WHERE sc.subject_id = 10001));

第三种写法:在score表中查出学生id,和符合要求的学生成绩,之后和student进行关联

SELECT stu.* FROM student stu

-- 右关联(外连接)
RIGHT JOIN (
    SELECT sc1.stu_id,sc1.score FROM score sc1
    WHERE sc1.score >= (
            SELECT avg(sc.score) FROM score sc
            WHERE sc.subject_id = 10001
        )
    AND sc1.subject_id = 10001
) stu1 on stu.id = stu1.stu_id;

题目要求2.

       查询所有科目都在所在科目平均分以上的学生信息

 

思路: 1.先查询出每个科目成绩在平均分之上的学生;

            2.进行内连接(inner  join    on(这里必须为on而不是where,具体参考手册)),得到所有科目都在平均分之上的学生。

具体sql指令:

方法一:

SELECT student .*from student,score,
(select   student.* ,student.id as s4 FROM student,
(select  stu1.id as s from score sc ,student stu1
where (select avg(score.score)from score where score.subject_id=10001 )and stu1.id=sc.stu_id and sc.subject_id =10001) tab1
INNER JOIN
(select  stu2.id as s2 from score sc2 ,student stu2
where (select avg(score.score)from score where score.subject_id=10002 )and stu2.id=sc2.stu_id and sc2.subject_id =10002)tab2
on tab1.s=tab2.s2 )
as tab3
INNER JOIN
(select stu1.id as s3 from score sc3 ,student stu3
where (select avg(score.score)from score where score.subject_id=10003 )and stu3.id=sc3.stu_id and sc3.subject_id =10003)tab4
on tab4.s3=tab3.s4

方法二:动态查询(不将科目数进行限制,会根据表数据变化,查询结果而发生改变)

select stu.* from
(select count(*) avgcount,stu_score.stu_id from
(select sc1.stu_id,sc1.subject_id,sc1.score from score sc1
,
(select avg(sc.score) avgscore,sc.subject_id  from score sc group by sc.subject_id) score1
where sc1.subject_id=score1.subject_id and sc1.score>=score1.avgscore) stu_score
group by stu_score.stu_id having avgcount=(
select count(*) from (select count(*) from score sc group by sc.subject_id) a
))stu1
INNER JOIN student stu on stu1.stu_id = stu.id
;

题设要求3.

查询总成绩最高的学生信息和最低的学生信息以及总成绩

思路:使用联合查询,

         1.先查出学生总分;

         2.进行排序(不写默认为asc升序,desc降序);

         3.使用limit限制查询出来的结果条数(limit);

         4.使用union all进行拼接。

具体sql指令:

方法一:思路基础上使用外连接


select stu.`name`,c.sum1 from
(select * from (select sum(sc.score) sum1,sc.stu_id from score sc group by  sc.stu_id
ORDER BY sum1 desc LIMIT 1) a
UNION ALL
select * from (
select sum(sc.score) sum1,sc.stu_id from score sc group by  sc.stu_id
ORDER BY sum1 LIMIT 1) b) c LEFT JOIN student stu on
c.stu_id = stu.id;

方法二:直接先查出学生信息之后进行拼接

SELECT student.*,s.sum1   FROM
(SELECT score.stu_id as sc,SUM(score.score) as sum1 FROM score GROUP BY score.stu_id) as s ,student
where s.sc=student.id  GROUP BY s.sum1 DESC limit 1

UNION ALL

SELECT stu1.*, s1.a  FROM
(SELECT score.stu_id as sc,SUM(score.score) as a FROM score GROUP BY score.stu_id) as s1 ,student as stu1
where s1.sc=stu1.id  GROUP BY s1.a limit 2

注意:方法二中第一个limit是对第一次查询的限制,第二个是对拼接后的限制,而非第二次查询的限制。

 

题设要求4.


查各科成绩最高的学生信息。

思路:使用联合查询,

         1.先查出各个科目最高分;

         2.使用union all进行拼接;

         3. 使用外连接。

具体sql指令:

select stu.id,stu.`name`,su.sub_name,a6.score from (
SELECT * from (SELECT sc.subject_id,sc.stu_id,sc.score from score sc where sc.subject_id=10001
and sc.score = (select max(score) from score sc where sc.subject_id=10001 )
ORDER BY sc.score desc ) a1
UNION ALL
SELECT * from (SELECT sc.subject_id,sc.stu_id,sc.score from score sc where sc.subject_id=10002
and sc.score = (select max(score) from score sc where sc.subject_id=10002 )
ORDER BY sc.score desc ) a2
UNION ALL
SELECT * from (SELECT sc.subject_id,sc.stu_id,sc.score from score sc where sc.subject_id=10003
and sc.score = (select max(score) from score sc where sc.subject_id=10003 )
ORDER BY sc.score desc ) a3
) a6
left JOIN student stu on a6.stu_id = stu.id LEFT JOIN `subject` su on su.sub_id= a6.subject_id


题设要求5.


    查询各科成绩都是优秀的学生信息(分数大于80)


具体sql指令:

方法一:按照学生id进行分组,判断最小分数是否大于80

select * from student where id in(
select sc.stu_id from score sc
group by sc.stu_id
having min(sc.score) > 80);

方法二:查询出分数大于80的学生和相对应的科目。按照学生id进行分组,

              统计每个id的出现的次数,这里有三门课,筛选等于三的

select student.* FROM student ,
(SELECT sc.stu_id  FROM score sc where sc.score >80  ) stu1
where stu1.stu_id =student.id  GROUP BY stu1.stu_id HAVING COUNT(stu1.stu_id)=3

方法二:使用连接查询

SELECT stu.* from (select * from (
SELECT sc.stu_id,sc.subject_id,sc.score from
score sc where sc.score>=80
ORDER BY sc.score DESC
) a1 group by a1.stu_id having count(stu_id) = 3) a1
LEFT JOIN student stu on a1.stu_id = stu.id


题设要求6.

查询各班平均成绩

 

思路:查询出每个学生的总分,按照班级进行分组,查询班级平均分

具体sql指令:
select stu2.classid,avg(stu2.sum1)
from
(select stu.id,stu.name,stu.classid,a.sum1 from
(
SELECT sum(sc.score) sum1,sc.stu_id from score sc  group by sc.stu_id
ORDER BY sum1 desc
) a RIGHT JOIN student stu
on a.stu_id = stu.id
) stu2
GROUP BY stu2.classid

题设要求7.

查询各班最高分学生的信息

 

思路:查询学生总分数,与学生表关联,按照班级分组取最高分。

具体sql指令:
select stu.id,stu.name,stu.classid,a.sum1 from
(SELECT sum(sc.score) sum1,sc.stu_id from score sc  group by sc.stu_id
ORDER BY sum1 desc) a LEFT JOIN student stu
on a.stu_id = stu.id
GROUP BY stu.classid


题设要求8.

查询所有学生各科成绩按照总成绩降序排列。

 

思路:1.需要显示为成绩单的格式,因此需要查询出每个学生的各科成绩

           2.使用连接查询将各个科目进行拼接

           3.查询出每个学生的总成绩并根据总成绩进行排序

具体sql指令:
        
SELECT stu.name,a4.java,a4.mysql,a4.html,a4.`总成绩` from
(select a.stu_id,a.score 'java',a1.score 'mysql',a2.score 'html',a3.sum1 '总成绩' from
(select sc.score,sc.stu_id from score sc where sc.subject_id=10001) a
LEFT JOIN
(select sc.score,sc.stu_id from score sc where sc.subject_id=10002) a1
on a.stu_id = a1.stu_id LEFT JOIN
(select sc.score,sc.stu_id from score sc where sc.subject_id=10003) a2
on a1.stu_id=a2.stu_id
LEFT JOIN (SELECT sum(sc.score) sum1 ,sc.stu_id from score sc GROUP BY sc.stu_id) a3
on a2.stu_id = a3.stu_id
ORDER BY a3.sum1 desc) a4
LEFT JOIN student stu on a4.stu_id = stu.id

没有更多推荐了,返回首页