SQL 学生 课程 分数 2

建表

create table course (
  id integer primary key,
  name text not null
);

create table student (
  id integer primary key,
  name text not null
);

create table score (
  id integer primary key,
  course_id integer not null,
  student_id integer not null,
  score integer not null
);

准备数据

insert into course values (1, '语文'), (2, '数学'),(3,'外语');

insert into student values (1, '小张'), (2, '小王'), (3, '小马'), (4, '小李');

--小张只考了语文
insert into score values (1, 1, 1, 80);
--小王考了语文和数学
insert into score values (4,1,2,70),(5,2,2,90);
--小马考了语文、数学、外语
insert into score values (7,1,3,80),(8,2,3,60),(9,3,3,70);
--小李考了语文、数学、外语，外语考了两次
insert into score values (7,1,3,80),(8,2,3,60),(9,3,3,70),(10,3,3,80);