mysql连接表

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 创建课程表
CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 创建连接表
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 插入示例数据
INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO courses (id, name) VALUES (101, 'Math'), (102, 'Science');
INSERT INTO student_courses (student_id, course_id) VALUES (1, 101), (1, 102), (2, 101);

-- 查询学生及其选修的课程
SELECT students.name AS student_name, courses.name AS course_name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id;