SQL查询技巧：深入解析学生选课系统数据库

        在大学的学生选课系统中，数据库的管理和查询是日常操作中的重要部分。本文通过一系列具体的SQL查询示例，深入解析如何高效地从数据库中获取所需信息，包括学生选课情况、成绩分析、教师课程管理等。

系统数据库结构

首先，我们有一个包含以下表的数据库：

course - 存储课程信息

建表

CREATE TABLE `course` (
  `CNO` varchar(5) NOT NULL,
  `CNAME` varchar(10) NOT NULL,
  `TNO` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` VALUES ('6-166', 'Java编程', '856');
INSERT INTO `course` VALUES ('9-888', '马克思主义', '100');

查询数据是否导入成功

select * from course;

grade - 存储成绩等级

 建表

CREATE TABLE `grade` (
  `low` int(3) DEFAULT NULL,
  `upp` int(3) DEFAULT NULL,
  `rank` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `grade` VALUES ('90', '100', 'A');
INSERT INTO `grade` VALUES ('80', '89', 'B');
INSERT INTO `grade` VALUES ('70', '79', 'C');
INSERT INTO `grade` VALUES ('60', '69', 'D');
INSERT INTO `grade` VALUES ('0', '59', 'E');

查询数据是否导入成功

select * from grade;

score - 存储学生成绩

 建表

CREATE TABLE `score` (
  `SNO` varchar(3) NOT NULL,
  `CNO` varchar(5) NOT NULL,
  `DEGREE` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `score` VALUES ('103', '3-245', '86.0');
INSERT INTO `score` VALUES ('105', '3-245', '75.0');
INSERT INTO `score` VALUES ('109', '3-245', '68.0');
INSERT INTO `score` VALUES ('103', '3-105', '92.0');
INSERT INTO `score` VALUES ('105', '3-105', '88.0');
INSERT INTO `score` VALUES ('109', '3-105', '76.0');
INSERT INTO `score` VALUES ('101', '3-105', '64.0');
INSERT INTO `score` VALUES ('107', '3-105', '91.0');
INSERT INTO `score` VALUES ('108', '3-105', '78.0');
INSERT INTO `score` VALUES ('101', '6-166', '85.0');
INSERT INTO `score` VALUES ('107', '6-106', '79.0');
INSERT INTO `score` VALUES ('108', '6-166', '81.0');

查询数据是否导入成功

select * from score;

student - 存储学生信息

 建表

CREATE TABLE `student` (
  `SNO` varchar(3) NOT NULL,
  `SNAME` varchar(4) NOT NULL,
  `SSEX` varchar(2) NOT NULL,
  `SBIRTHDAY` datetime DEFAULT NULL,
  `CLASS` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `student` VALUES ('108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `student` VALUES ('105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `student` VALUES ('107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `student` VALUES ('101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` VALUES ('109', '王芳', '女', '1975-02-10 00:00:00', '95031');
INSERT INTO `student` VALUES ('103', '陆君', '男', '1974-06-03 00:00:00', '95031');

查询数据是否导入成功

select * from student;

teacher - 存储教师信息

 建表

CREATE TABLE `teacher` (
  `TNO` varchar(3) NOT NULL,
  `TNAME` varchar(4) NOT NULL,
  `TSEX` varchar(2) NOT NULL,
  `TBIRTHDAY` datetime NOT NULL,
  `PROF` varchar(6) DEFAULT NULL,
  `DEPART` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `teacher` VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
INSERT INTO `teacher` VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');

查询数据是否导入成功

select * from teacher;

SQL查询示例与分析

1. 查询特定班级的学生人数

查询“95031”班的学生人数

SELECT COUNT(1) FROM student WHERE class = '95031';

这条查询统计了班级“95031”的学生人数，使用COUNT(1)确保每个学生都被计数。

2. 查询最高分的学生信息

查询Score表中的最高分的学生学号和课程号

select SNO, CNO, DEGREE
from score
where DEGREE = (select max(DEGREE) from score);

通过子查询找到最高分，然后查询所有获得这一分数的学生信息。

3. 查询成绩在特定区间的学生编号

查询最低分大于70，最高分小于90的Sno列

SELECT SNO FROM score WHERE DEGREE BETWEEN 70 AND 90 GROUP BY SNO;

这条查询找出了成绩在70到90分之间的所有学生编号，使用BETWEEN简化了查询条件。

4. 查询特定班级所选课程的平均分

查询“95033”班所选课程的平均分

select CNO, AVG(DEGREE) as avg_score
from score
where SNO in (select SNO from student where class = '95033')
group by CNO;

通过子查询找到“95033”班的所有学生，然后计算他们每门课程的平均成绩。

5. 查询成绩高于特定学生的成绩记录

查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select stu.*
from student stu
         join score sco on stu.SNO = sco.SNO
where sco.CNO = '3-105'
  and DEGREE > (select DEGREE
                from score
                where SNO = '109' and CNO = '3-105');

这条查询首先找到课程“3-105”中编号为“109”的学生的成绩，然后找出所有成绩高于这一分数的学生记录。

6. 查询选修多门课程且成绩非最高的学生记录

查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

select sco1.* from score sco1 where exists(
    select 1 from score sco2 where sco2.SNO = sco1.SNO and DEGREE < (
        select max(DEGREE)  from score sco3 where sco3.SNO = sco1.SNO))
    and sco1.SNO in (
        select SNO from score group by SNO having count(CNO) > 1);

通过子查询找到选修多门课程的学生，然后筛选出成绩不是最高的记录。

7. 查询选修学生多于5人的教师姓名

查询选修某课程的同学人数多于5人的教师姓名。

select tea.TNAME from teacher tea
    join course cou on cou.TNO = tea.TNO
    where cou.CNO in (select CNO from score group by CNO having count(SNO) > 5);

这条查询首先找出选修学生多于5人的课程，然后查询这些课程的任课教师姓名。

8. 查询不同系不同职称的教师

查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

select TNAME, PROF from teacher where DEPART in ('计算机系', '电子工程系');

通过限定部门和职称，查询特定条件下的教师信息。

9. 查询成绩低于课程平均成绩的学生

查询成绩比该课程平均成绩低的同学的成绩表

select CNO, avg(DEGREE) from score where CNO = CNO group by CNO;
select * from score sco where DEGREE < (select avg(DEGREE) from score where CNO = sco.CNO );

这条查询找出了所有成绩低于其所在课程平均成绩的学生记录。

10. 查询所有任课教师的信息

查询所有任课教师的Tname和Depart

select TNAME, DEPART from teacher where TNO in (select  TNO from course);

通过关联查询，找出所有有授课记录的教师信息。

结论

        通过这些SQL查询示例，我们可以看到如何有效地从教育数据库中提取信息，无论是进行学生成绩分析、教师课程管理还是班级学生统计。这些技巧对于数据库管理员和教育工作者来说都是非常实用的。希望这篇文章能帮助你更好地理解和使用SQL进行数据查询。如果你有任何疑问或需要进一步的帮助，请随时联系。