mysql创建学生成绩管理系统数据库

CREATE DATABASE student_scores;

USE student_scores;
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id VARCHAR(10) NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender ENUM('Male', 'Female') NOT NULL,
    age INT NOT NULL
);

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id VARCHAR(10) NOT NULL,
    name VARCHAR(50) NOT NULL,
    credit INT NOT NULL
);

CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO students (student_id, name, gender, age) VALUES ('2021001', '张三', 'Male', 20);
INSERT INTO students (student_id, name, gender, age) VALUES ('2021002', '李四', 'Female', 21);

INSERT INTO courses (course_id, name, credit) VALUES ('C001', '数学', 4);
INSERT INTO courses (course_id, name, credit) VALUES ('C002', '英语', 3);

INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 90);
INSERT INTO scores (student_id, course_id, score) VALUES (1, 2, 85);
INSERT INTO scores (student_id, course_id, score) VALUES (2, 1, 95);