Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1059617
  • 博文数量: 228
  • 博客积分: 474
  • 博客等级: 下士
  • 技术积分: 1930
  • 用 户 组: 普通用户
  • 注册时间: 2010-03-19 18:40
文章分类

全部博文(228)

文章存档

2019年(3)

2018年(12)

2017年(13)

2016年(11)

2015年(55)

2014年(75)

2013年(39)

2012年(2)

2011年(18)

我的朋友

分类: Mysql/postgreSQL

2018-07-11 15:32:02

转自:https://www.cnblogs.com/pyspark/p/7763241.html
前言    

  本篇博客将在上一篇的基础上,继续为大家梳理约束相关的知识,前面我们学习了Primary key和unique key方面的知识,本节我们专注于解决使用外键来定义表和表之间的三种关系:多对一,多对多,一对一。本次博客的内容比较重要,希望大家好好理解和记忆。

 

一.总体原则

   判断表与表之间的关系,我们主要通过三个步骤:

【001】1.解读两张表中的每条记录代表什么意思,例如员工表中的一条记录代表一个员工的信息,例如姓名,性别,年龄,所属部门;
而部门表中的一条记录代表某一个部门的信息,例如部门ID,部门名称等;
【002】2.判断左表的多条记录是否可以关联右表的一条记录,同时判断右表的多条记录是否可以关联左表的一条记录
【003】3.如果判断是多对一的关系,那么需要在这个基础上考虑是否是一对一的关系,因此一对一实际上多对一的变种。

二.多对一:单向的foreign key

所谓多对一是指左表的多条记录可以关联右表的一条记录;但是右表的多条记录却不能关联左表的一条记录。例如教师表和课程表,多个老师可以关联一门课程,这就表示多个老师都可以教同一门课程,例如alex可以教Python,tom可以教Python,carson也可以教Python;但是多门课程却不能关联一个老师,毕竟术业有专攻嘛,不可能alex又教Python,又教Linux。所以教师表和课程表是多对一之间的关系。这个多指的是某张表中的多条记录,既然是多条记录,我们就需要使用foreign key来修饰。我们一起来实现刚才的例子:

复制代码
# 创建课程表 多门课程不能关联一个老师
CREATE TABLE course (
  cid   INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(6) NOT NULL UNIQUE
)AUTO_INCREMENT = 100;

# 创建老师表 多个老师可以关联一门课程,属于多对一的关系
CREATE TABLE teacher (
  tid   INT PRIMARY KEY AUTO_INCREMENT,
  tname CHAR(10) NOT NULL , cid   INT NOT NULL , (必须要定义,对应课程表中的cid) FOREIGN KEY(cid) REFERENCES course (cid)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)AUTO_INCREMENT = 10; 在上面教师表的定义中,我们使用foreign key定义了教师表和课程表之间的关系,最后使用 update cascade和delete cascade 定义了两张表之间的更新和删除关系:级联更新和级联删除 这表明教师表中的cid字段跟随着课程表中的cid字段所对应的记录的更新而更新,删除而删除。 试想一下,例如alex教授Python这门课程,但是有一天课程表中讲这条记录删除了,如果不设置 级联删除,那么课程表中不存在Python这门课程了,但是教师表中还存在,这岂不是很矛盾。同理 如果对课程表中的课程进行了修改,如果不设置级联修改的话,那么教师表中的课程也不会有相应的更新 所以为了保证表与表之间的同步,必须设置级联删除和级联修改。
另外还有一点要注意:既然两表示多对一之间的关系,教师表中的cid字段关联课程表中的cid字段
我们肯定要先创建课程表,然后再创建教师表进行关联。
再例如员工表和部门表,多个员工记录可以关联部门表中的一条记录,表示多个员工属于相同的一个部门;但是多个部门记录却不能
关联同一个员工,因为不可能一个员工你属于多个部门;因为虽然有些人可能做的事跨部门,但是它毕竟还是属于某一个部门。这里的
员工表和部门表之间的关系是多对一的关系。

在上面的例子中,我们判断出教师表和课程表是多对一的关系,在此基础上考虑两者是否存在一对一之间的关系。由于教师表中的课程ID
不可能是唯一的,因为不可能存在每个老师教授的课程都是唯一的,只能是这样的情况。例如alex,tom,carson教授python,Eva,Egon教授
云计算,老男孩老师教授Linux;所以在教师表中的CID就对应多个课程ID。因此两者不可能是一对一之间的关系。
复制代码

 三.一对一

     在上面的例子中,我们谈到如果两张表的关系是多对一的关系,我们就要考虑两者是否可能是一对一之间的关系。所谓一对一是指左表的一条记录唯一对应右表的一条记录。采用foreign key+unique实现。我们来看下面的例子:

复制代码
以老男孩的教学为例,现在给定学生表和客户表,学生表记录着所以已经成为老男孩正式学员的学生,而客户表意味着有潜在机会成为老男孩正式学员的人,就是说学生表中的人肯定都是由客户演变过来的 。我们来尝试分析下两张表之间的关系,学生表中的多条记录不能关联客户表中的一条记录,因为不可能说客户表中的一条记录能够演变为多个正式学员,只能是演变为一个学员;而客户表中的多条记录却可以关联 学生表中的一条记录,因为可能存在说这5个客户,最后只有一个成为老男孩的正式成员,其他四个由于各种原因都没有成为老男孩的学员。分析到这里,我们可以断定客户表和学生表是多对一之间的关系。那我们接着分析 两者是否可能是一对一的关系。对于客户表而言,它里面的学生表ID是唯一的,不可能存在任意两个学生的ID是相同的,所以分析到这里,两者应该是一对一之间的关系。我们来看看它们的一个关系图
复制代码

如上图所示,一个客户演变为一个学生,然后在学生表的记录中添加一个c_id即可。既然两者是一对一之间的关系,那么具体在SQL语句上怎么设计呢?如下:

复制代码
create table student(
    id int primary key auto_increment,
    name char(6),
    class_name char(10),
    c_id int unique, #保证学生表中的c_id是唯一的,由客户演变为学生,所以是学生foreign key 客户表   使用unique关键字来修饰外键,只有这样才能保证是一对一之间的关系 foreign key(s_id) references student(id)
     on update cascade
     on delete cascade
);

create table customer( # 客户表只需要正常创建即可
    id int primary key auto_increment,
    name char(6),
    phone int,
    qq char(11),
    mail varchar(20),
);
复制代码

四.多对多

     所谓多对多是指双向的foreign key,具体而言是说左表的多条记录对应右表的一条记录,而右表的多条记录也能对应左表的一条记录。我们还是通过实际的例子来说明:

复制代码
需求:给定角色表和用户表,因为每个用户都有相应的角色,例如角色可以是管理员,教学主管,班主任等等。角色表中的多条记录可以
关联用户表中的一条记录,这就类似于一个人拥有多种角色,例如它既可以是管理员,又可以是教学主管;而用户表中的多条记录也可以
关联角色表中的一条记录,这就意味着多个用户都可以是同一种角色,例如Alex,Egon,Tom这些人可以都是教学主管。这样就形成了一个
双向多对多的关系,所以两者的关系是一个多对多的关系。
现在我们来考虑如何创建两者表,前面我们说过当两张表之间的关系是多对一时,例如教师表和课程表,肯定需要先创建多对一中的一,即
肯定需要先创建课程表,然后教师表中的cid才能foreign key到课程表中。但是现在是多对多之间的关系,这就陷入了一个双向死循环,怎么办?
我们的思路是创建一个第三方表来表示两张表之间的关系,然后这两张表直接按照正常创建即可,看如下的例子:
复制代码

【001】创建用户角色表:

复制代码
CREATE TABLE role (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  name       CHAR(10),
  permission CHAR(3),
  COMMENT    VARCHAR(10)
);
复制代码

【002】创建用户表

CREATE TABLE user (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     CHAR(6),
  password VARCHAR(20)
);

【003】既然是多对多的关系,双方彼此关联对方,因此需要创建一个第三张表来关联这两张表,如下所示:

复制代码
CREATE TABLE user2role (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT, # 对应用户表中的ID,相当于是外键 role_id INT, # 对应角色表中的ID,相当于是外键 FOREIGN KEY (user_id) REFERENCES user (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES role (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);
复制代码

五.案例解析

前面我们详细分析了使用外键来关联两张表之间的关系,这里我们通过几个实际的例子来强化上面的知识点,一起来看看下面的几个例子:

【001】

复制代码
# 6.表结构设计(20分)
# a)设计表
# 用户表与部门表(一个用户只能属于一个用户组)(6分)
# 部门表与主机表(一个部门可以管理多台主机)(6分)
# b)查询
# 查询技术部门的员工总数(2分)
# 查询技术部门管理的主机数(2分)
# 查询员工李平所在部门管理的主机信息(2分)
# 查询ip为192.168.45.10的主机所在部门的员工信息(2分)
# 创建三张表:1.用户表,2.部门表,3.主机表
# 多个用户可以对应一个部门记录,多个部门记录不能关联一个用户,属于多对一的关系
# 多台主机可以对应一个部门,多个部门可以关联一台主机 属于多对多的关系
复制代码

我们一起来分析下用户表,部门表,主机表三张表之间的关系:

复制代码
【001】部门表的整体记录可以看作是一个用户组,多个用户可以关联部门表中的一条记录,即这多个用户属于同一个部门,在同一个用户组里面;
而部门表中的多条记录却不能关联一个用户,因为一个用户它只能属于一个用户组,所以用户表和部门表示多对一的关系;
【002】再来分析部门表和主机表之间的关系。部门表中的多条记录可以关联主机表中的一条记录,因为一台主机是可以被多个部门使用的,这样
做到了资源共享;主机表中的多条记录是可以关联部门表中的一条记录的,即一个部门在使用主机表中的这些主机。因此部门表和主机表是
多对多的关系。明确了两者之间的关系,我们可以开始来创建表:
复制代码
复制代码
# 创建用户表
CREATE TABLE monthexam.user_info (
  uid INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(20) NOT NULL,
  password VARCHAR(50) NOT NULL,
  d_id int,
  FOREIGN KEY(d_id) REFERENCES department(dept_id) ON UPDATE CASCADE ON DELETE CASCADE
);

# 创建部门表
create table department(
dept_id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20) NOT NULL
);

# 创建主机表
CREATE TABLE host (
  host_id int PRIMARY KEY AUTO_INCREMENT,
  ip CHAR(16) NOT NULL UNIQUE DEFAULT '127.0.0.1' )AUTO_INCREMENT=100;

# 建立主机表和部门表多对多的关系
CREATE TABLE hostTodept(
  id INT PRIMARY KEY AUTO_INCREMENT,
  host_id INT NOT NULL ,
  dept_id INT NOT NULL ,
  FOREIGN KEY (host_id) REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON UPDATE CASCADE ON DELETE CASCADE
);
复制代码

接下来我们插入数据:

复制代码
# 为用户表插入数据
INSERT INTO user_info (username, password, d_id) VALUES
  ('root', '123', 200),
  ('carson', '456', 201),
  ('李平', 'cisco',200),
  ('eva', '123456', 203),
  ('王五', '45678', 202),
  ('赵六', '666', 201),
  ('王依', '678910', 200),
  ('tom', '1234', 202);

#为部门表插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

# 为主机表插入数据
INSERT INTO host (ip) VALUES
  ('172.16.41.3'),
  ('172.16.28.10'),
  ('172.16.44.3'),
  ('172.16.32.11'),
  ('172.10.45.3'),
  ('172.10.45.4'),
  ('192.168.45.10'),
  ('192.168.11.22'),
  ('192.168.21.23'),
  ('192.168.11.223'),
  ('192.168.11.24');
复制代码

最后我们来完成上面的查询需求:

复制代码
# 查询技术部门的员工总数
SELECT d.name as '部门', count(u.uid) as '员工总数' FROM department d LEFT JOIN user_info u ON d.dept_id = u.d_id where d.name='技术' GROUP BY d.dept_id;

#查询技术部门管理的主机数
SELECT d.name as '部门' , count(h.host_id) as '主机数' FROM department d INNER JOIN hostTodept h ON d.dept_id = h.dept_id
WHERE d.name='技术';

# 查询员工李平所在部门管理的主机信息
SELECT host_id as '主机ID', ip as '主机IP' FROM host where host_id IN
(
SELECT host_id
FROM hostTodept
WHERE dept_id IN
(SELECT d.dept_id
FROM user_info u INNER JOIN department d ON u.d_id = d.dept_id where u.username='李平')
  );

# 查询ip为192.168.45.10的主机所在部门的员工信息
SELECT * FROM user_info where d_id IN(
  SELECT ht.dept_id
FROM host h INNER JOIN hostTodept ht ON h.host_id = ht.host_id
WHERE h.ip='192.168.45.10' );
复制代码

六.选课系统之表关系

我们首先来看如下这张选课系统之间的图,我们尝试来理清它们之间的关系:

复制代码
上面主要存在五张表:学生表,班级表,老师表,课程表,成绩表 学生和班级之间的关系:多个班级可以关联一个学生(即一个学生可以参加不同的班级学习多门不同的课程,例如carson即可参加Linux班
的学习,又可以参加Python班级的学习);
多个学生可以关联一个班级,这表示一个Python班级里面有多名学生;这又是一个多对多的关系 老师和课程之间的关系:多个老师可以教一门课程,多个课程不可以被一个老师教(术业有专攻)即一个老师不能教多门课程。
这个关系是多对一的关系,是老师关联课程,意味着teacher表要添加一个字段:cid 班级和课程之间的关系:多个班级可以对应一门课程,例如7期python,6期python
多门课程不可以对应一个班级,即7期Python不能又教Python,还教Linux课程。因此这个属于多对一的关系。即班级foreign key 课程 学生和课程之间的关系:多个学生可以选择一门课程,多个课程可以关联一个学生,这是一个多对多的关系
这里我们有点小问题,学生和课程之间不应该直接发生关系,而应该采用成绩表来关联。虽然学生表和课程表实际上是多对多的关系,但是
为了按照实际的需求,例如统计某个学生某门课程的分数,我们是应该创建一张成绩表。
复制代码

在本篇博客的最后,我们来一起复习下前面的set知识点:

按 Ctrl+C 复制代码
按 Ctrl+C 复制代码

 

 

 

 

 

阅读(516) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册