一. 练习1：账号信息表，用户组，主机表，主机组
# 用户表
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)
);
1. 表关系分析
'''
用户表与用户组表之间的关系:
站在用户表的角度考虑: 一个用户可以属于多个用户组. --> 成立
站在用户组表的角度考虑: 一个用户组可以包含多个用户. --> 成立
结论: 多对多
用户表与业务线表之间的关系:
站在用户表的角度考虑: 一个用户可以拥有多个业务线. --> 成立
站在业务线表的角度考虑: 一条业务线可以被多个用户进行. --> 成立
结论: 多对多
用户表与主机表之间的关系:
站在用户表的角度考虑: 一个用户可以拥有多台主机. --> 成立
站在主机表的角度考虑: 一台主机可以被多个用户使用. --> 成立
结论: 多对多
'''
2. 插入数据的顺序
# 先往被关联表插值
host
business
host2business
user
user2host
usergroup
user2usergroup
# 当然也可以先往所有的被关联表 或者 无关联表插值
host
business
user
usergroup
host2business
user2usergroup
host2business
3. 如果没有指定级联更新和级联删除时删表的顺序
# 先删除关联表
drop table user2usergroup;
drop table usergroup;
drop table host2business;
drop table business;
drop table user2host;
drop table user;
drop table host;
# 当然也可以先删除所有的关联表
drop table user2usergroup;
drop table host2business;
drop table user2host;
drop table user;
drop table host;
drop table business;
drop table usergroup;
二. 练习2
# 班级表
cid caption
# 学生表
sid sname gender class_id
# 老师表
tid tname
# 课程表
cid cname teacher_id
# 成绩表
sid student_id course_id number
1. 表关系分析
"""
班级表与学生表之间的关系:
站在学生表的角度考虑: 一个学生可以属于多个班级. --> 不成立
站在班级表的角度考虑: 一个班级可以容纳多个学生. --> 成立
结论: 一对多. 学生表是多的一方.
课程表与老师表之间的关系:
站在课程表的角度考虑: 一个课程可以被多个老师教. --> 不成立
站在老师表的角度考虑: 一个老师可以教多门课程. --> 成立
结论: 一对多. 课程是多的一方.
成绩表中: 学生表与课程表之间的关系:
站在学生表的角度考虑: 一个学生可以选修多门课程多门课程都有其所属的成绩. --> 成立
站在课程表的的角度考虑: 一个课程可以被多个学生选修每个学生都有其所属的成绩. --> 成立
结论: 多对多.
"""
2. 表结构视图
| 班级表: class |
|
| cid |
caption |
| 1 |
脱产14期 |
| 2 |
脱产20期 |
| 3 |
脱产29期 |
| 老师表: teacher |
|
| tid |
tname |
| 1 |
egon |
| 2 |
jsaon |
| 学生表: student |
|
|
|
| sid |
sname |
gender |
class_id |
| 1 |
刘洋 |
男 |
1 |
| 2 |
刘旭 |
男 |
2 |
| 3 |
张晨 |
男 |
3 |
| 4 |
陈诚 |
女 |
1 |
| 5 |
郑惠雯 |
女 |
2 |
| 6 |
薛贤妻 |
男 |
3 |
| 课程表: course |
|
|
| cid |
cname |
teacher_id |
| 1 |
python |
1 |
| 2 |
web |
2 |
| 成绩表: score |
|
|
|
| sid |
student_id |
course_id |
number |
| 1 |
1 |
1 |
99 |
| 2 |
1 |
2 |
98 |
| 3 |
2 |
1 |
97 |
| 4 |
2 |
2 |
96 |
| 5 |
3 |
1 |
59 |
| 6 |
3 |
2 |
50 |
| 7 |
4 |
1 |
48 |
| 8 |
4 |
2 |
47 |
| 9 |
5 |
1 |
46 |
| 10 |
5 |
2 |
99 |
| 11 |
6 |
1 |
98 |
| 12 |
6 |
2 |
97 |
3. 创建表并插入记录
# 创建表的顺序: 先创建被关联表, 再创建关联表指定外键
# 插入记录的顺序: 先往被关联表插入记录, 再往关联表插入记录.
# ============== 班级表 ==============
drop table class;
create table class(
cid int primary key auto_increment,
caption varchar(16) not null
);
insert into class(caption) values('脱产14期'), ('脱产20期'), ('脱产29期');
select * from class;
# ============== 老师表 ==============
drop table teacher;
create table teacher(
tid int primary key auto_increment,
tname varchar(16) not null
);
insert into teacher(tname) values('egon'), ('jsaon');
select * from teacher;
# ============== 学生表 ==============
drop table student;
create table student(
sid int primary key auto_increment,
sname varchar(16) not null,
gender enum('男', '女'),
class_id int not null,
foreign key(class_id) references class(cid)
on update cascade
on delete cascade
);
insert into student(sname, gender, class_id) values('刘洋', '男', 1), ('刘旭', '男', 2), ('张晨', '男', 3), ('陈诚', '女', 1), ('郑惠雯', '女', 2), ('薛贤妻', '男', 3);
select * from student;
# ============== 课程表 ==============
drop table course;
create table course(
cid int primary key auto_increment,
cname varchar(16) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid)
on update cascade
on delete cascade
);
insert into course(cname, teacher_id) values('python', 1), ('web', 2);
select * from course;
# ============== 成绩表 ==============
drop table score;
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
);
insert into score(student_id, course_id, number) values(1, 1, 99), (1, 2, 98), (2, 1, 97), (2, 2, 96), (3, 1, 59), (3, 2, 50), (4, 1, 48), (4, 2, 47), (5, 1, 46), (5, 2, 99), (6, 1, 98), (6, 2, 97);
select * from score;
4. 如果没有设置级联跟新或者级联删除时的删除表的顺序是
# 先删除关联表
drop table score;
drop table student;
drop table course;
drop table class;
drop table teacher;
