day46 练习

#班级
create table class(
cid int primary key auto_increment,
caption char(66) not null
);


insert into class values(1,'上海 python 14期');
insert into class values(2,'北京 python 28期');

#学生
create table student(
sid int primary key auto_increment,
sname varchar(66) not null,
gender enum('male','female') default 'male',
class_id int not null,
foreign key(class_id) references class(cid)
on update cascade
on delete cascade
);

insert into student(sname,class_id) values('aaa',1);
insert into student(sname,class_id) values('bbb',2);

#老师
create table teacher(
tid int primary key auto_increment,
tname varchar(66) not null
);

insert into teacher(tname) values('egon');
insert into teacher(tname) values('alex');

#课程
create table course(
cid int primary key auto_increment,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
on update cascade
on delete cascade
);

insert into course(teacher_id) values(1);
insert into course(teacher_id) values(2);

#成绩
create table achievement(
aid int primary key auto_increment,
student_id int not null unique ,
course_id int not null unique,
number int not null,
foreign key(student_id) references student(sid)
on update cascade
on delete cascade,
foreign key(course_id) references course(cid)
on update cascade
on delete cascade
);

insert into achievement(student_id,course_id,number) values(1,1,100);
insert into achievement(student_id,course_id,number) values(2,2,0);