实验训练4mysql数据库_MySQL数据库练习-4

查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

分析:这道题明显也只用到sc和student两张表,对这两张表进行联合查询,选课总数和课程成绩总和的操作对象都是sc表,因此我们可以构建表r,里面字段是选课总数和成绩总和还有SId,然后跟student表进行联合查询

构建表r

select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber fromsc

group by sc.sid;

查询结果:

+------+----------+--------------+

| sid | scoresum | coursenumber |

+------+----------+--------------+

| 01 | 269.0 | 3 |

| 02 | 210.0 | 3 |

| 03 | 240.0 | 3 |

| 04 | 100.0 | 3 |

| 05 | 163.0 | 2 |

| 06 | 65.0 | 2 |

| 07 | 187.0 | 2 |

+------+----------+--------------+

与student进行联合查询:

selectstudent.sid, student.sname,r.coursenumber,r.scoresumfromstudent,

(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber fromsc

group by sc.sid)rwhere student.sid = r.sid;

查询结果:

+------+--------+--------------+----------+

| sid | sname | coursenumber | scoresum |

+------+--------+--------------+----------+

| 01 | 赵雷 | 3 | 269.0 |

| 02 | 钱电 | 3 | 210.0 |

| 03 | 孙风 | 3 | 240.0 |

| 04 | 李云 | 3 | 100.0 |

| 05 | 周梅 | 2 | 163.0 |

| 06 | 吴兰 | 2 | 65.0 |

| 07 | 郑竹 | 2 | 187.0 |

+------+--------+--------------+----------+

到这里结果其实就出来了,但是,这里有一点需要思考的地方,我们的同学其实有13个,但是这里只出现了7个,说明有一些同学是没选课的,这里的查询显然是忽略了这一点,所以如果严谨一些,我们需要吧其他没选课的同学都考虑进去,我们用join就可以给没选课的同学一个null值.

先查询student表拿出所有的学生的sid和sname

select student.sid,student.sname from student;

查询结果;

+------+--------+

| sid | sname |

+------+--------+

| 01 | 赵雷 |

| 02 | 钱电 |

| 03 | 孙风 |

| 04 | 李云 |

| 05 | 周梅 |

| 06 | 吴兰 |

| 07 | 郑竹 |

| 09 | 张三 |

| 10 | 李四 |

| 11 | 李四 |

| 12 | 赵六 |

| 13 | 孙七 |

+------+--------+

然后拿出入第一种方法一样查询出总分和科目数

selectsc.sid, sum(sc.score)as scoresum, count(sc.cid) ascoursenumberfromsc

group by sc.sid;

查询结果:

+------+----------+--------------+

| sid | scoresum | coursenumber |

+------+----------+--------------+

| 01 | 269.0 | 3 |

| 02 | 210.0 | 3 |

| 03 | 240.0 | 3 |

| 04 | 100.0 | 3 |

| 05 | 163.0 | 2 |

| 06 | 65.0 | 2 |

| 07 | 187.0 | 2 |

+------+----------+--------------+

然后left join这两个查询结果,进行查询:

selects.sid, s.sname,r.coursenumber,r.scoresumfrom(

(selectstudent.sid,student.snamefromstudent

)s

left join

(selectsc.sid, sum(sc.score)as scoresum, count(sc.cid) ascoursenumberfromsc

group by sc.sid

)r

on s.sid=r.sid

);

最终结果:

+------+--------+--------------+----------+

| sid | sname | coursenumber | scoresum |

+------+--------+--------------+----------+

| 01 | 赵雷 | 3 | 269.0 |

| 02 | 钱电 | 3 | 210.0 |

| 03 | 孙风 | 3 | 240.0 |

| 04 | 李云 | 3 | 100.0 |

| 05 | 周梅 | 2 | 163.0 |

| 06 | 吴兰 | 2 | 65.0 |

| 07 | 郑竹 | 2 | 187.0 |

| 09 | 张三 | NULL | NULL |

| 10 | 李四 | NULL | NULL |

| 11 | 李四 | NULL | NULL |

| 12 | 赵六 | NULL | NULL |

| 13 | 孙七 | NULL | NULL |

+------+--------+--------------+----------+

以上