作业30

作业30

练习：账号信息表，用户组，主机表，主机组

#用户表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

#用户组表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

#主机表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);

#业务线表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);

#建关系：user与usergroup

create table user2usergroup(
id int not null unique auto_increment, 
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);

#建关系：host与business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);

#建关系：user与host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);

练习:

# 班级表
cid	caption
# 学生表
sid sname gender class_id
# 老师表
tid	tname
# 课程表
cid	cname	teacher_id
# 成绩表
sid	student_id course_id number

班级表

cid caption

create table class(cid int primary key auto_increment,caption char(5));

insert into class(caption) values('三年二班'),('一年三班'),('三年一班');

学生表

sid sname gender class_id

create table student(
    sid int primary key auto_increment,
    sname varchar(8),
	gender enum('男','女'),
    class_id int,
    foreign key(class_id) references class(cid) 
    on update cascade on delete cascade
);


insert into student(sname,gender,class_id) values('钢蛋','女',1),('铁锤','女',1),('山炮','男',2);

老师表

tid tname

create table teacher(tid int primary key auto_increment,tname varchar(8));

insert into teacher(tname) values('波多'),('苍空'),('饭岛');

课程表

cid cname teacher_id

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


insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);

成绩表

sid student_id course_id number

create table score(
    sid int primary key auto_increment,
    student_id int,
    foreign key(student_id) references student(sid) 
    on update cascade on delete cascade,
    course_id int,
    foreign key(course_id) references course(cid) 
    on update cascade on delete cascade,
    number int
);


insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100);