编辑代码

CREATE DATABASE 覃钰芳;
use 覃钰芳;
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50),
age INT,
gender VARCHAR(10)
); 
CREATE TABLE courses (
course_id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(50),
teacher VARCHAR(50)
);

CREATE TABLE student_course (
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)
);

CREATE TABLE scores (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
score DECIMAL(5, 2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);


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

INSERT INTO students (student_name, age, gender)
VALUES 
('张三', 22, '男'),
('小李', 18, '男'),
('小红', 21, '女');

INSERT INTO courses (course_name, teacher)
VALUES 
('数学', '刘老师'),
('英语', '李老师');

INSERT INTO student_course (student_id, course_id)
VALUES 
(1, 1),
(1, 2),
(2, 1);

INSERT INTO scores (student_id, course_id, score)
VALUES 
(1, 1, 95.5),
(1, 2, 87),
(2, 1, 81);

INSERT INTO teachers (teacher_name, subject)
VALUES 
('刘老师', '数学'),
('李老师', '英语');

SELECT * FROM students;
SELECT * FROM courses;


SELECT s.student_name, c.course_name
FROM students s
JOIN student_course sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

SELECT s.student_name, c.course_name, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;