多表连接

-- 准备示例表
CREATE TABLE scores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    course VARCHAR(50) NOT NULL,
    score DECIMAL(5,2) NOT NULL
);

INSERT INTO scores (student_id, course, score) VALUES
(1, "数学", 95), (1, "语文", 88),
(2, "数学", 78), (3, "语文", 90);

-- 内连接：学生 + 成绩
SELECT s.name, sc.course, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

-- 左连接：所有学生（含没成绩的）
SELECT s.name, sc.course, sc.score
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id;

-- 多表连接（三张表）
SELECT s.name, c.title, sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id;

-- 连接 + 聚合
SELECT s.name, COUNT(sc.id) AS 科目数, AVG(sc.score) AS 平均分
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id;