数据库mysql学生成绩管理

CREATE TABLE Students (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Gender ENUM('Male', 'Female'),
    DateOfBirth DATE
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY AUTO_INCREMENT,
    CourseName VARCHAR(100),
    Credits INT
);

CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    CourseID INT,
    Score DECIMAL(5, 2),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

SELECT c.CourseName, s.Score
FROM Scores s
JOIN Courses c ON s.CourseID = c.CourseID
WHERE s.StudentID = ?;

SELECT AVG(Score) AS AverageScore
FROM Scores
WHERE StudentID = ?;

INSERT INTO Scores (StudentID, CourseID, Score)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE Score = VALUES(Score);

CREATE INDEX idx_studentid ON Scores(StudentID);