create table class(

cid int primary key auto_increment,

caption char(20) not null

)engine=innodb;

create table teacher(

tid int primary key auto_increment,

tname char(20) not null

)engine=innodb;

create table student(

sid int primary key auto_increment,

sname char(20) not null,

gender enum('男','女'),

class_id int,

foreign key(class_id) references class(cid)

on delete cascade

on update cascade

)engine=innodb;

create table course(

cid int primary key auto_increment,

cname char(20) not null,

teacher_id int,

foreign key(teacher_id) references class(cid)

on delete cascade

on update cascade

)engine=innodb;

create table score(

sid int primary key auto_increment,

student_id int,

course_id int,

number int  unsigned not null check (number <= 100),

foreign key(student_id) references student(sid)

on delete cascade

on update cascade,

foreign key(course_id) references course(cid)

on delete cascade

on update cascade

)engine=innodb;

insert into class(caption) values

('三年二班'),

('一年三班'),

('三年一班');

insert into teacher(tname) values

('波多'),

('苍空'),

('饭岛');

insert into student(sname,gender,class_id) values

('钢蛋','女',1),

('铁锤','女',1),

('山炮','男',2);

insert into course(cname,teacher_id) values

('生物',1),

('体育',1),

('物理',1);

insert into score(student_id,course_id,number) values

(1,1,60),

(1,2,59),

(2,3,100);