设有学生课程关系数据库

数据库需求分析

我们需要明确这个数据库需要存储哪些信息,以及它们之间的关系。

• 学生: 每个学生都有一个唯一的标识符，以及一些基本信息，如学号、姓名、性别、出生日期、所属院系等。
• 课程: 每门课程也有一个唯一的标识符，以及课程的基本信息，如课程号、课程名称、学分、授课教师等。
• 关系: 学生和课程之间是多对多的关系，一个学生可以选修多门课程，一门课程也可以被多个学生选修，为了记录这种多对多关系，我们需要一个中间表，通常称为“选课记录”或“成绩表”。

数据库表结构设计

基于上述分析,我们设计以下三个核心表：

表1: Students (学生表)

存储学生的基本信息。

表2: Courses (课程表)

存储课程的基本信息。

表3: Enrollments (选课/成绩表)

这是连接 Students 和 Courses 的中间表，记录了哪个学生选修了哪门课程,以及该课程的成绩。

表关系图

为了更直观地理解,我们可以用ER图来表示它们之间的关系：

+-----------+       +-----------------+       +--------------+
|  Students |       |   Enrollments   |       |    Courses   |
+-----------+       +-----------------+       +--------------+
| PK StudentID |----<| FK StudentID    |----->| PK CourseID  |
| StudentName |     | PK EnrollmentID |     | CourseName   |
| Gender     |     | FK CourseID     |     | Credits      |
| BirthDate  |     | Grade           |     | Teacher      |
| Department |     | Semester        |     +--------------+
+-----------+       +-----------------+

• Students 和 Enrollments 是 一对多 关系：一个学生可以有多条选课记录。
• Courses 和 Enrollments 也是 一对多 关系：一门课程也可以被多个学生选修,从而有多条记录。
• 通过 Enrollments 表，Students 和 Courses 之间形成了 多对多 关系。

创建表的SQL语句

以下是使用标准SQL（MySQL语法）创建这些表的代码：

-- 创建学生表
CREATE TABLE Students (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Gender CHAR(1),
    BirthDate DATE,
    Department VARCHAR(50)
);
-- 创建课程表
CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT,
    Teacher VARCHAR(50)
);
-- 创建选课/成绩表
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    Grade DECIMAL(5, 2),
    Semester VARCHAR(20),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
    -- (可选) 确保一个学生在同一个学期不能重复选同一门课
    UNIQUE (StudentID, CourseID, Semester) 
);

插入示例数据

-- 向学生表插入数据
INSERT INTO Students (StudentID, StudentName, Gender, BirthDate, Department) VALUES
('S001', '张三', 'M', '2002-05-15', '计算机科学'),
('S002', '李四', 'F', '2003-02-20', '软件工程'),
('S003', '王五', 'M', '2002-11-08', '计算机科学'),
('S004', '赵六', 'F', '2003-07-12', '数据科学');
-- 向课程表插入数据
INSERT INTO Courses (CourseID, CourseName, Credits, Teacher) VALUES
('CS101', '数据库系统', 4, '王教授'),
('CS102', '数据结构', 3, '李教授'),
('CS201', '操作系统', 4, '张教授'),
('MA101', '高等数学', 5, '陈教授');
-- 向选课表插入数据
INSERT INTO Enrollments (StudentID, CourseID, Grade, Semester) VALUES
('S001', 'CS101', 88.50, '2025-Fall'),
('S001', 'CS102', 92.00, '2025-Fall'),
('S002', 'CS101', 95.00, '2025-Fall'),
('S002', 'MA101', 85.50, '2025-Fall'),
('S003', 'CS101', 78.00, '2025-Fall'),
('S003', 'CS201', NULL, '2025-Spring'), -- 王五选了操作系统，但成绩还未录入
('S004', 'MA101', 91.00, '2025-Fall');

常用SQL查询示例

这个数据库可以回答很多实际问题：

a. 查询所有学生的信息

SELECT * FROM Students;

b. 查询所有课程的名称和学分

SELECT CourseName, Credits FROM Courses;

c. 查询“张三”同学选修的所有课程及其成绩

SELECT 
    s.StudentName,
    c.CourseName,
    e.Grade
FROM 
    Students s
JOIN 
    Enrollments e ON s.StudentID = e.StudentID
JOIN 
    Courses c ON e.CourseID = c.CourseID
WHERE 
    s.StudentName = '张三';

d. 查询“数据库系统”这门课的所有学生名单

SELECT 
    s.StudentName,
    s.Department
FROM 
    Students s
JOIN 
    Enrollments e ON s.StudentID = e.StudentID
JOIN 
    Courses c ON e.CourseID = c.CourseID
WHERE 
    c.CourseName = '数据库系统';

e. 查询每个学生的平均成绩

SELECT
    s.StudentName,
    s.Department,
    AVG(e.Grade) AS AverageGrade
FROM
    Students s
JOIN
    Enrollments e ON s.StudentID = e.StudentID
WHERE
    e.Grade IS NOT NULL -- 排除未录入成绩的记录
GROUP BY
    s.StudentID, s.StudentName, s.Department
ORDER BY
    AverageGrade DESC;

f. 查询“计算机科学”院系平均分最高的学生

SELECT
    s.StudentName,
    AVG(e.Grade) AS AverageGrade
FROM
    Students s
JOIN
    Enrollments e ON s.StudentID = e.StudentID
WHERE
    s.Department = '计算机科学' AND e.Grade IS NOT NULL
GROUP BY
    s.StudentID, s.StudentName
ORDER BY
    AverageGrade DESC
LIMIT 1;

这个“学生-课程”数据库模型是关系数据库理论的经典范例，它清晰地展示了如何使用主键、外键和中间表来处理实体间的复杂关系。

标签： 课程 关系