Mysql数据库查询练习

设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构如下所示：

学生表.png 课程表.png 成绩表.png 教师表.png

表结构及数据下载：https://github.com/wangzhangzong/sqlProject

现在开始上题：

1、查询同时参加计算机导论和数字电路考试的学生的信息。

SELECT * FROM t_student WHERE sno IN 
 (SELECT * FROM (
     (SELECT sno FROM t_score WHERE cno=(SELECT cno FROM t_course WHERE cname='计算机导论')) 
UNION ALL 
     (SELECT sno FROM t_score WHERE cno=(SELECT cno FROM t_course WHERE cname='数字电路'))
                ) AS a 
GROUP BY a.sno HAVING COUNT(*)=2
  )

查询结果：

1查询结果.png

2、查询所有课程的学习人数、及格人数和及格率的百分数。

SELECT course.cno,course.cname,COUNT(course.cno) AS '学习人数',
 COUNT(IF(score.degree >= 60,'1',NULL)) AS '及格人数',
 CONCAT (ROUND(COUNT(IF(score.degree >= 60,'1',NULL))/COUNT(course.cno)*100, 1),  '','%') AS '及格率'
FROM t_course course LEFT JOIN t_score score ON course.cno=score.cno GROUP BY course.cno

查询结果：

2查询结果.png

3、统计列出各科成绩的各分数段人数，格式如下：

课程ID, 课程名称, [100-85], [85-70], [70-60], [<60]

SELECT 
  course.cno AS '课程ID',
  course.cname AS '课程名称',
  COUNT(CASE WHEN score.degree BETWEEN 85 AND 100 THEN '1' ELSE NULL END) AS '[100-85]',
  COUNT(CASE WHEN score.degree BETWEEN 70 AND 84 THEN '1' ELSE NULL END) AS '[85-70]',
  COUNT(CASE WHEN score.degree BETWEEN 60 AND 69 THEN '1' ELSE NULL END) AS '[70-60]',
  COUNT(CASE WHEN score.degree < 60 THEN '1' ELSE NULL END) AS '[<60]'
 FROM t_score score,t_course course WHERE score.cno=course.cno GROUP BY course.cno

查询结果

3查询结果.png

4、查询各科成绩前两名的记录。

 SELECT course.cname '课程',student.sname '姓名',degree '成绩' 
 FROM t_score s1 LEFT JOIN t_course course ON s1.cno=course.cno 
    LEFT JOIN t_student student ON s1.sno=student.sno 
 WHERE(SELECT COUNT(1) FROM t_score s2 WHERE s1.cno=s2.cno AND s2.degree>=s1.degree)<=2  
 ORDER BY s1.cno,s1.degree DESC

查询结果：

4查询结果.png

5、查询每个学生的成绩单，格式如下：

5格式.png

SELECT 
  student.sname AS '姓名',
  SUM(IF(course.cname = '计算机导论', score.degree, 0)) AS '计算机导论',
  SUM(IF(course.cname = '操作系统', score.degree, 0)) AS '操作系统',
  SUM(IF(course.cname = '数字电路', score.degree, 0)) AS '数字电路',
  SUM(score.degree) AS '总分',
  ROUND(AVG(score.degree),1) AS '平均成绩'
FROM t_student student LEFT JOIN t_score score ON student.sno=score.sno JOIN t_course course ON score.cno=course.cno
GROUP BY student.sno

查询结果：

5查询结果.png

以上为自己写的一些查询的练习，敬请指正。