5.5mysql_5.5MySQL作业

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

#用户表

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)

);

练习二:

# 班级表 cidcaption

create table classes(

cid int primary key auto_increment,

caption varchar(10) not null

);

# 学生表 sid sname gender class_id

create table student(

sid int primary key auto_increment,

sname varchar(16) not null,

gender enum('male','female') not null,

class_id int,

foreign key(class_id) references classes(cid)

on update cascade

on delete cascade

);

# 老师表 tidtname

create table teacher(

tid int primary key auto_increment,

tname varchar(16) not null

);

# 课程表 cidcnameteacher_id

create table course(

cid int primary key auto_increment,

cname varchar(12) not null,

teacher_id int unique,

foreign key(teacher_id) references teacher(tid)

on update cascade

on delete cascade

);

# 成绩表 sidstudent_id course_id number

create table score(

sid int primary key auto_increment,

student_id int not null,

course_id int not null,

number int not null,

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

);