作业20200505

用户表

用户组表

主机表

业务线表

建关系：user ---- usergroup（多对多）

建关系：host --- business（多对多）

建关系：user --- host (多对多)

作业2:

班级表

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

insert into class(caption) values('python');
insert into class(caption) values('linux');

学生表

create table student(
	sid int primary key auto_increment,
    name varchar(30) 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 student(name, class_id) values('jack', 1);
insert student(name, gender, class_id) values('lili', 'female', 1);

老师表

create table teacher(
	tid int primary key auto_increment,
    name varchar(30) not null
);
insert into teacher(name) values('egon');
insert into teacher(name) values('jason');

课程表

create table course(
	cid int primary key auto_increment,
    cname varchar(30) not null unique,
    teacher_id int not null unique,
    foreign key(teacher_id) references teacher(tid)
    on update cascade
    on delete cascade
);
insert course(cname, teacher_id) values('python', 1);
insert course(cname, teacher_id) values('mysql', 2);

成绩表

create table score(
	sid int primary key auto_increment,
    student_id int not null,
    course_id int not null,
    number int,
    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 score(student_id, course_id, number) values(1, 1, 99);
insert score(student_id, course_id, number) values(1, 2, 98);
insert score(student_id, course_id, number) values(2, 1, 100);
insert score(student_id, course_id, number) values(1, 2, 100);