5.7 课堂案例：学生成绩管理的数据查询

1. 单表和多表的查询

 （1）查询入学总分前3名学生的学号sno、姓名sname和入学总分enterscore。

mysql> select sno AS 学号, sname AS 姓名, enterscore AS 入学总分
    -> from student
    -> order by enterscore desc
    -> limit 3;

（2）查询教师工号tno、姓名tname、性别sex、职称title和院系名称deptname。

mysql>  select tno AS 教师工号, tname AS 姓名, sex AS 性别, title AS 职称,deptname AS 院系名称
    -> from teacher inner join department on department.deptno=teacher.deptno;

注意拼写,表相连关键条件

（3）查询选修了“大学物理”课程的学生信息。

mysql>  select student.sno AS 学号, sname AS 姓名, grade AS 大学物理成绩
    -> from score inner join student on score.sno=student.sno
    -> where cno=(select cno from course where cname ='大学物理');

注意:

1.拼写

2.当2个表中有相同的列名时,注意声明查询的是哪一个表的列

（4）查询“大学物理”和“证券投资学”这两门课程不及格的学生。

mysql> select student.sno AS 学号, student.sname AS 姓名, course.cname AS 课程名称, score.grade AS 成绩
    ->  from score join student on score.sno=student.sno
    -> join course on score.cno= course.cno
    -> where score.grade<60 and course.cname IN('证券投资学','大学物理');

（5）查询尚未选修任何课程的学生信息。

mysql> select sno AS 学号, sname AS 姓名
    -> from student
    -> where sno NOT IN(select sno from score);

2. 使用GROUP BY子句和聚合函数

 （1）查询各院系的学生人数，要求显示结果中包含所有院系。

mysql> select department.deptname AS 院系名称, count(student.sno) AS 学生人数
    -> from student join department on student.deptno=department.deptno
    -> group by student.deptno;

（2）查询每位教师授课的班级数量，查询结果按班级数量的降序排列。

mysql>  select teacher.tname AS 姓名,count(teaching.classno) AS 教学班级数
    -> from teaching join teacher on teacher.tno=teaching.tno
    -> group by teaching.tno
    -> order by count(teaching.classno) desc;

注意:

order by 字段名或表达式 

（3）查询各院系的学生的平均成绩，查询结果按平均成绩的降序排列。

mysql> select department.deptname AS 院系名称, avg(grade) AS 平均分
    -> from score join student on student.sno=score.sno
    -> join department on student.deptno=department.deptno
    -> group by student.deptno
    -> order by avg(grade) desc;

注意: 记得写分组

（4）查询每门课程的选课人数，结果按照选课人数的降序显示。

mysql> select course.cname AS 课程名称, count(score.cno) AS 学生人数
    -> from score join course on score.cno= course.cno
    -> group by score.cno
    -> order by count(score.cno) desc;

（5）查询各门课程的平均成绩，查询结果按平均成绩的降序排列。

mysql> select score.cno AS 课程编号, course.cname AS 课程名称, avg(grade) AS 平均分
    -> from score join course on score.cno=course.cno
    -> group by score.cno
    -> order by avg(grade) desc;

（6）查询每个学生平均成绩和选课门数，查询结果先按照选课数量的降序排序，选课数
量相同时按照平均成绩降序排序。

mysql> select score.sno AS 学号, student.sname AS 姓名, count(score.cno) AS 选课数量, avg(grade) AS 平均成绩
    -> from score join student on score.sno=student.sno
    -> group by score.sno
    -> order by count(score.cno) desc,avg(grade) desc;

（7）查询选修课程少于3门的学生信息。

mysql> select student.sno AS 学号, student.sname AS 姓名, count(score.cno) AS 选课数量
    -> from score join student on student.sno=score.sno
    -> group by(score.sno)
    -> having count(score.cno)<3;

（8）统计学生未通过课程的数量。

mysql> select student.sno AS 学号,student.sname AS 姓名,count(score.cno) AS 未通过课程数量
    -> from score join student on score.sno=student.sno
    -> where grade<60
    -> group by score.sno;

（9）统计每个学生已修课程的总学分。

 分析：大于等于60分才能获得学分，在成绩表course中没有表示学分的字段，可以通过学
时计算出来，每16个学时是1个学分，即hours/16。

mysql> select student.sno AS 学号,student.sname AS 姓名,count(score.cno) AS 通过课程数量, sum(hours/16) AS 总学分
    -> from student join score on student.sno=score.sno join course on course.cno=score.cno
    -> where score.grade>=60
    -> group by score.sno;

3. 子查询

 （1）查询尚未被学生选修的课程信息。

mysql> select cno AS 课程编号,cname AS 课程名称
    -> from course
    -> where cno NOT IN (select cno from score);

 （2）查询高等数学成绩低于平均值的学生。

mysql> select score.sno AS 学号, student.sname AS 姓名, score.grade AS 成绩
    -> from score,course,student,(select cno,avg(grade) AS avg_grade from score group by cno) AS t
    -> where score.sno=student.sno and score.cno=course.cno and t.cno=score.cno and grade<avg_grade and cname='高等数学';

（3）查询与“王晓红”在同一院系的其他学生的信息。

mysql> select sno AS 学号, sname AS 姓名, deptno AS 院系代码
    -> from student
    -> where deptno=(select deptno from student where sname='王晓红') and sname !='王晓红';