数据库作业

#班级表

cid caption

#学生表

sid sname gender class_id

create table class(
    cid int primary key auto_increment,
    caption varchar(16) not null
);

insert into class values(1,'python_class');
insert into class(caption) values('linux_class');

create table student(
    sid int primary key auto_increment,
    sname varchar(16) not null,
    gender enum（‘male','female','others') defualt 'male',
    class_id int,
    foreign key(class_id) references class(id)
    on update cascade
    on delete cascade
);

insert into class values(1,'egon','female',1);

 

#老师表

tid name

#课程表

cid cname teacher_id

#成绩表

sid student_id course_id number

create table teacher(
    tid int primary key auto_increment,
    tname varchar(16) not null unique
);
insert into teacher values(1,'egon');

create table course(
    cid int primary key auto_increment,
    cname varchar(16),
    teacher_id int,
    foreign key(teacher_id) references teacher(id)
    on update cascade
    on delete cascade
);

insert into course values(1,'python',2);

create table score(
    sid int primary key auto_increment,
    student_id int,
    course_id int,
    number int unique,
    foreign key(student_id) references student(id)
    on updata casdate
    on delete casdate,
    foreign key(course_id) references course(id)
    on update casdate
    on delete casdate
);