编辑代码

CREATE DATABASE qinfeng;
USE qinfeng;
CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    age INT,
    gender VARCHAR(10)
);
CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credits INT
);
CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    teacher_name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);
CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
INSERT INTO Students (student_name, age, gender)
VALUES ('Alice', 20, 'Female'),
       ('Bob', 21, 'Male');
INSERT INTO Courses (course_name, credits)
VALUES ('Math', 3),
       ('English', 4);
INSERT INTO Teachers (teacher_name, department)
VALUES ('Mr. Smith', 'Math'),
       ('Ms. Johnson', 'English');
INSERT INTO Enrollments (student_id, course_id)
VALUES (1, 1),
       (2, 2);
INSERT INTO Teachings (teacher_id, course_id)
VALUES (1, 1),
       (2, 2);
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT s.student_name, c.course_name
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id;