用于获取两个维度记录的SQLite查询(学生vs测验)

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name TEXT
);

CREATE TABLE exams (
    exam_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    score INTEGER,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO students (student_id, student_name) VALUES (1, '张三');
INSERT INTO students (student_id, student_name) VALUES (2, '李四');

INSERT INTO exams (exam_id, student_id, score) VALUES (1, 1, 90);
INSERT INTO exams (exam_id, student_id, score) VALUES (2, 1, 85);
INSERT INTO exams (exam_id, student_id, score) VALUES (3, 2, 95);

-- 获取所有学生的姓名和测验成绩
SELECT students.student_name, exams.score
FROM students
JOIN exams ON students.student_id = exams.student_id;

-- 获取特定学生的姓名和测验成绩
SELECT students.student_name, exams.score
FROM students
JOIN exams ON students.student_id = exams.student_id
WHERE students.student_name = '张三';