数据库作业

班级表：class

create table class(
cid int auto_increment primary key,
caption char(20)
)engine=innodb default charset=utf8;
insert into class(caption) values("三年二班");
insert into class(caption) values("一年三班");
insert into class(caption) values("三年一班");


学生表：student

create table student(
sid int auto_increment primary key,
sname char(20),
gender enum('女','男'),
class_id int,
constraint student_class foreign key (class_id) references class(cid)
) engine=innodb default charset=utf8;

insert into student(sname,gender,class_id) values('钢蛋','女',1);
insert into student(sname,gender,class_id) values('铁锤','女',1);
insert into student(sname,gender,class_id) values('山炮','男',2);


老师表：teacher

create table teacher(
tid int auto_increment primary key,
tname char(20))engine=innodb default charset=utf8;
insert into teacher(tname) values("波多");
insert into teacher(tname) values("苍空");
insert into teacher(tname) values("饭岛");

学生表：course

create table course(
cid int auto_increment primary key,
cname char(20),
teacher_id int,
constraint teacher_class foreign key (teacher_id) references teacher(tid)
) engine=innodb default charset=utf8;

insert into course(cname,teacher_id) values('生物','1');
insert into course(cname,teacher_id) values('体育','1');
insert into course(cname,teacher_id) values('物理','2');

成绩表：
create table score(
sid int auto_increment primary key,
student_id int,
corse_id int,
number int,
constraint score_class foreign key (student_id) references student(sid),
constraint course_class foreign key (corse_id) references course(cid)
)engine=innodb default charset=utf8;
insert into score(student_id,corse_id,number) values(1,1,60);
insert into score(student_id,corse_id,number) values(1,2,59);
insert into score(student_id,corse_id,number) values(2,2,100);

http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png