实验：设计一个学生管理系统的数据库

1. 设计学生管理系统的表结构及关系

根据需求，可以设计以下表结构：

1. students：存储学生信息
2. classes：存储班级信息
3. courses：存储课程信息
4. teachers：存储老师信息
5. class_courses：存储班级与课程的多对多关系

表结构设计

students 表

• student_id (INT, PRIMARY KEY, AUTO_INCREMENT)
• name (VARCHAR)
• class_id (INT, FOREIGN KEY references classes(class_id))

classes 表

• class_id (INT, PRIMARY KEY, AUTO_INCREMENT)
• class_name (VARCHAR)

courses 表

• course_id (INT, PRIMARY KEY, AUTO_INCREMENT)
• course_name (VARCHAR)
• teacher_id (INT, FOREIGN KEY references teachers(teacher_id))

teachers 表

• teacher_id (INT, PRIMARY KEY, AUTO_INCREMENT)
• name (VARCHAR)

class_courses 表

• class_id (INT, FOREIGN KEY references classes(class_id))
• course_id (INT, FOREIGN KEY references courses(course_id))

表关系

• 学生 (students) 通过 class_id 关联到班级 (classes)。
• 班级 (classes) 通过 class_id 在 class_courses 表中关联到课程 (courses)。
• 课程 (courses) 通过 teacher_id 关联到老师 (teachers)。

2. 插入数据

创建表结构的 SQL 语句

CREATE TABLE teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50)
);

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50),
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

CREATE TABLE class_courses (
    class_id INT,
    course_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

插入数据的 SQL 语句

插入班级

INSERT INTO classes (class_name) VALUES 
('Class 1'),
('Class 2'),
('Class 3');

插入学生

INSERT INTO students (name, class_id) VALUES
('Student 1', 1),
('Student 2', 1),
('Student 3', 1),
('Student 4', 2),
('Student 5', 2),
('Student 6', 2),
('Student 7', 3),
('Student 8', 3),
('Student 9', 3);

插入老师

INSERT INTO teachers (name) VALUES 
('Teacher 1'),
('Teacher 2'),
('Teacher 3'),
('Teacher 4'),
('Teacher 5'),
('Teacher 6');

插入课程

INSERT INTO courses (course_name, teacher_id) VALUES
('Python', 1),
('Linux', 2),
('Go', 3),
('PHP', 4),
('Java', 5),
('K8s', 6);

插入班级和课程的关系

-- Class 1
INSERT INTO class_courses (class_id, course_id) VALUES 
(1, 1), -- Python
(1, 3), -- Go
(1, 4); -- PHP

-- Class 2
INSERT INTO class_courses (class_id, course_id) VALUES 
(2, 2), -- Linux
(2, 3), -- Go
(2, 6); -- K8s

-- Class 3
INSERT INTO class_courses (class_id, course_id) VALUES 
(3, 6), -- K8s
(3, 5), -- Java
(3, 2); -- Linux