学生信息数据库mysql

-- 创建数据库
CREATE DATABASE student_db;

-- 使用数据库
USE student_db;

-- 创建学生表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Gender ENUM('Male', 'Female'),
    BirthDate DATE
);

-- 创建课程表
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credits INT
);

-- 创建成绩表
CREATE TABLE Grades (
    StudentID INT,
    CourseID INT,
    Grade DECIMAL(5, 2),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

SELECT c.CourseName, g.Grade
FROM Grades g
JOIN Courses c ON g.CourseID = c.CourseID
WHERE g.StudentID = 1;

SELECT COUNT(*) AS StudentCount
FROM Grades
WHERE CourseID = 1;