练习:账号信息表,用户组,主机表,主机组
#用户表
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
create table class(
cid int primary key auto_increment,
caption char(16) not null
);
# 学生表
sid sname gender class_id
create table class(
sid int primary key auto_increment,
sname char(16) not null,
gender enum('male','female','others'),
class_id int ,
foreign key(class_id) references class(cid)
on update cascade
on delete cascade
);
# 老师表
tid tname
create table teacher(
tid int primary key auto_increment,
tname char(16) not null
);
# 课程表
cid cname teacher_id
create table course(
cid int primary key auto_increment,
cname char(16) not null,
teacher_id int ,
foreign key(teacher_id) references teacher(tid)
on update cascade
on delete cascade
);
# 成绩表
sid student_id course_id number
create table score(
sid int primary key auto_increment,
student_id int,
course_id int,
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
)