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);
