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