Mysql 库表

create database student_info default character set utf8 collate utf8_general_ci;

------------------------班级表---------------------

create table class(
    cid int not null auto_increment primary key,
    title  varchar(32)
    )engine=innodb default charset=utf8;

insert into class(title) values("全栈一期"),("全栈二期"),("全栈三期"),("全栈四期"),("全栈五期");



------------------------学生表---------------------

create table student(
    sid int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null,
    class_id int not null,
    constraint student_class foreign key(class_id) references class(cid)
    )engine=innodb default charset=utf8;



insert into student(name,age,class_id) values("张根",25,4),("李智",23,4),("黄未",32,5),("贾永旭",22,5);


------------------------老师表---------------------

create table teacher(
    tid int not null auto_increment primary key,
    name varchar(32) not null
    )engine=innodb default charset=utf8;


insert into teacher(name) values("alex"),("egon"),("wuSir"),("老妖");


------------------------老师班级表---------------------

create table teacher_class(
    id int not null auto_increment primary key,
    teacher_id int not null,
    class_id int not null,
    unique(teacher_id,class_id),
    constraint tescher foreign key(teacher_id) references teacher(tid),
    constraint class foreign key(class_id) references class(cid)
    )engine=innodb default charset=utf8;

insert into teacher_class(teacher_id,class_id) values(1,1),(1,2),(2,3)(3,3),(4,1);

create database student_info default character set utf8 collate utf8_general_ci;



#class 表

create table class_info(
    cid int not null auto_increment primary key,
    caption varchar(32)
    )engine=innodb default charset=utf8;


create table student_info(
    sid int not null auto_increment primary key,
    sname varchar(32) not null,
    gender ENUM("男","女"),
    class_id int,
    constraint student_class foreign key(class_id) references class_info(cid)
    )engine=innodb default charset=utf8;


#老师  课程

create table teacher_info(
    tid int not null auto_increment primary key,
    tname varchar(32)
    )engine=innodb default charset=utf8;



create table course_info(
    cid int not null auto_increment primary key,
    cname varchar(32) not null,
    tearch_id int,
    constraint course_teacher foreign key(tearch_id) references teacher_info(tid)
    )engine=innodb default charset=utf8;




#成绩表

create table score_info(
    sid int not null auto_increment primary key,
    student_id int,
    corse_id  int,
    number  int,
    unique(student_id,corse_id),
    constraint score_student foreign key(student_id) references student_info(sid),
    constraint score_course foreign key(corse_id) references course_info(cid)
    )engine=innodb default charset=utf8;


insert into class_info(caption) values("三年二班");
insert into class_info(caption) values("三年三班");
insert into class_info(caption) values("五年一班");


insert into student_info(sname,gender,class_id) values("alex","男",1);
insert into student_info(sname,gender,class_id) values("egon","女",2);
insert into student_info(sname,gender,class_id) values("jim","女",2);



insert into teacher_info(tname) values("李老师");
insert into teacher_info(tname) values("王老师");
insert into teacher_info(tname) values("李鹏");
insert into teacher_info(tname) values("李三");
insert into teacher_info(tname) values("叶平");

insert into course_info(cname,tearch_id) values("生物",1);
insert into course_info(cname,tearch_id) values("物理",2);
insert into course_info(cname,tearch_id) values("体育",1);
insert into course_info(cname,tearch_id) values("化学",5);





insert into score_info(student_id,corse_id,number) values(6,3,100);
insert into score_info(student_id,corse_id,number) values(2,3,100);



insert into score_info(student_id,corse_id,number) values(2,1,100);
insert into score_info(student_id,corse_id,number) values(2,2,200);
insert into score_info(student_id,corse_id,number) values(2,3,200);
insert into score_info(student_id,corse_id,number) values(2,4,200);
insert into score_info(student_id,corse_id,number) values(6,4,200);
insert into score_info(student_id,corse_id,number) values(1,2,200);
insert into score_info(student_id,corse_id,number) values(1,4,200);
insert into score_info(student_id,corse_id,number) values(1,3,180);
insert into score_info(student_id,corse_id,number) values(1,1,170);
insert into score_info(student_id,corse_id,number) values(6,1,210);