十四、表与表之间关系

四哥1个月前MySQL43

引言

可以在数据库图表中的表之间创建关系,以显示一个表中的列与另一个表中的列是如何相链接的。

在一个关系型数据库中,利用关系可以避免多余的数据。

目录

  • 表与表之间关系概述

  • 一对多应用

  • 一对一应用

  • 多对多应用

1、表与表之间关系概述

1.1、什么是表与表之间关系

在关系型数据库中,为了避免数据冗余,我们的一些表与表之间肯定是有一定的关系。

如:学生表与老师表,部门表与员工表,用户表与权限表等。

在表设计的时候,就应该体现出来表与表之间的这种关系。

1.2、表与表之间关系分类

1.2.1、一对多关系

一对多关系是最普通的一种关系。在这种关系中,A 表中的一行可以匹配 B 表中的多行,但是 B 表中的一行只能匹配 A 表中的一行。

只有当一个相关列是一个主键或具有唯一约束时,才能创建一对多关系。

image-20200215112949021

image-20200215112949021.png


注意:

一对多的创建原则:

主外键关连

1.2.2、一对一关系

在一对一关系中,A 表中的一行最多只能匹配于 B 表中的一行,反之亦然。如果相关列都是主键或都具有唯一约束,则可以创建一对一关系。

image-20200215122050731.png

image-20200215122050731

注意:

一对一的创建原则:

外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE

外键是主键:主表的主键和从表的主键,形成主外键关系

1.2.3、多对多关系

在多对多关系中,A 表中的一行可以匹配 B 表中的多行,反之亦然。要创建这种关系,需要定义第三个表,称为结合表,它的主键由 A 表和 B 表的外部键组成。

image-20200215121200999

image-20200215121200999.png


注意:

多对多的创建原则:

二个表与中间表创建1对多的关系。

2、一对多应用

创建一对多关系:主外键关连

image-20200215123556759

image-20200215123556759.png


新华出版社(Python爬虫、Linux)

海燕出版社(操作系统、数学)

摆渡出版社(英语、网页设计)

大众出版社()

案例:

这是一个书和出版社的一个例子,书要关联出版社(多个书可以是一个出版社,一个出版社也可以有好多书)。

表的创建

#出版社表(被关联表)
create table press(
id int primary key auto_increment,
name char(20)-- 出版社名字
);

#书表(关联表)
create table book(
book_id int primary key auto_increment,
book_name varchar(20),
book_price int,
press_id int,
constraint fk_pressid_id foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

插入数据

mysql> insert into press(name) values('新华出版社'), ('海燕出版社'), ('摆渡出版社'), ('大众出版社');
Query OK, 4 rows affected (0.09 秒)

mysql> insert into book(book_name,book_price,press_id) values('Python爬虫',100,1), ('Linux',80,1), ('操作系统',70,2), ('数学',50,2), ('英语',103,3), ('网页设计',22,3);
Query OK, 6 rows affected (0.07 秒)

查看数据

mysql> select * from press;
+----+-----------------+
| id | name            |
+----+-----------------+
| 1  | 新华出版社 |
| 2  | 海燕出版社 |
| 3  | 摆渡出版社 |
| 4  | 大众出版社 |
+----+-----------------+
4 行于数据集 (0.01 秒)

mysql> select * from book;
+---------+--------------+------------+----------+
| book_id | book_name    | book_price | press_id |
+---------+--------------+------------+----------+
| 1       | Python爬虫 | 100        | 1        |
| 2       | Linux        | 80         | 1        |
| 3       | 操作系统 | 70         | 2        |
| 4       | 数学       | 50         | 2        |
| 5       | 英语       | 103        | 3        |
| 6       | 网页设计 | 22         | 3        |
+---------+--------------+------------+----------+
6 行于数据集 (0.01 秒)

3、一对一应用

创建一对一:外键唯一,user_id唯一对应一个uid,user_id必须在uid里出现

image-20200215164955805

image-20200215164955805.png


案例:

用户和管理员(只有管理员才可以登录,一个管理员对应一个用户)

表创建

#用户表(被关联表)
create table user(
id int primary key auto_increment, #主键自增
name char(10)
);

#管理员表(关联表)
create table manager(
id int primary key auto_increment,
user_id int unique,
password varchar(16),
foreign key(user_id) references user(id)
on delete cascade
on update cascade
);

插入数据

mysql> insert into user(name)values('susan1'),('susan2'),('susan3'),('susan4'),('susan5'),('susan6');
Query OK, 6 rows affected (0.02 秒)

mysql> insert into manager(user_id,password) values(4,'sds156'),(2,'531561'),(6,'f3swe');
Query OK, 3 rows affected (0.03 秒)

查看数据

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
| 1  | susan1 |
| 2  | susan2 |
| 3  | susan3 |
| 4  | susan4 |
| 5  | susan5 |
| 6  | susan6 |
+----+--------+
6 行于数据集 (0.01 秒)

mysql> select * from manager;
+----+---------+----------+
| id | user_id | password |
+----+---------+----------+
| 1  | 4       | sds156   |
| 2  | 2       | 531561   |
| 3  | 6       | f3swe    |
+----+---------+----------+
3 行于数据集 (0.01 秒)

4、多对多应用

创建多对多:要把book_id和author_id设置成联合唯一

image-20200215161523092

image-20200215161523092.png


九阳神功(egon、e3)

葵花宝典(egon、e4)

辟邪剑谱(e1、e2、e3)

降龙十巴掌(e4)

或者

egon(九阳神功、葵花宝典)

e1(辟邪剑谱)

e2(辟邪剑谱)

e3(九阳神功、辟邪剑谱)

e4(葵花宝典、降龙十巴掌)

案例:

这是一个书和作者的一个例子,书要关联作者(一个作者可以写多个书,一本书也可以有多个作者,双向的一对多,即多对多)。

表创建

#书表(被关联表)
create table book1(
id int primary key auto_increment,
name varchar(10),
price float(3,2)
);

#作者表(被关联表)
create table author(
id int primary key auto_increment,
name char(5)
);

#作者和书表(关联表)
create table author2book(
id int primary key auto_increment,
book_id int not null,
author_id int not null,
unique(book_id,author_id),
foreign key(book_id) references book1(id)
on delete cascade
on update cascade,
foreign key(author_id) references author(id)
on delete cascade
on update cascade
);

插入数据

mysql> insert into book1(name,price) values('九阳神功',9.9), ('葵花宝典',9.5), ('辟邪剑谱',5),  ('降龙十巴掌',7.3);
Query OK, 4 rows affected (0.09 秒)

mysql> insert into author(name) values('egon'),('e1'),('e2'),('e3'),('e4');
Query OK, 5 rows affected (0.07 秒)

mysql> insert into author2book(book_id,author_id) values(1,1),(1,4),(2,1),(2,5),(3,2),(3,3),(3,4),(4,5);
Query OK, 8 rows affected (0.03 秒)

查看数据

mysql> select * from book1;
+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
| 1  | 九阳神功    | 9.90  |
| 2  | 葵花宝典    | 9.50  |
| 3  | 辟邪剑谱    | 5.00  |
| 4  | 降龙十巴掌 | 7.30  |
+----+-----------------+-------+
4 行于数据集 (0.02 秒)

mysql> select * from author;
+----+------+
| id | name |
+----+------+
| 1  | egon |
| 2  | e1   |
| 3  | e2   |
| 4  | e3   |
| 5  | e4   |
+----+------+
5 行于数据集 (0.01 秒)

mysql> select * from author2book;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 1  | 1       | 1         |
| 2  | 1       | 4         |
| 3  | 2       | 1         |
| 4  | 2       | 5         |
| 5  | 3       | 2         |
| 6  | 3       | 3         |
| 7  | 3       | 4         |
| 8  | 4       | 5         |
+----+---------+-----------+
8 行于数据集 (0.01 秒)



相关文章

十、DQL-聚合函数

十、DQL-聚合函数

引言SQL基本函数,聚合函数对一组值执行计算,并返回单个值,也被称为组函数。聚合函数对一组值执行计算并返回单一的值。除 COUNT 以外,聚合函数忽略空值,如果COUNT函数的应用对象是一个确定列名,...

十六、子查询

十六、子查询

引言在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作,首先必须创建一个查询来确定用户不知道但包含在数据库中的值,将一个查询块嵌套在另一个...

九、DQL-分组语句

九、DQL-分组语句

引言数据分组是根据统计研究的需要,将原始数据按照某种标准划分成不同的组别,分组后的的数据称为分组数据。数据分组应遵循两个基本原则:穷尽性原则这一原则就是要求调查的每一单位都能无一例外地划归到某一组去,...

十二、DQL-模糊查询

十二、DQL-模糊查询

引言模糊查询即模糊检索,是指搜索系统自动按照用户输入关键词的同义词进行模糊检索,从而得出较多的检索结果。与之相反的是“精准搜索”。模糊检索也可以说是同义词检索,这里的同义词是用户通过“检索管理”中的“...

十五、多表连接查询

十五、多表连接查询

引言连接查询是关系数据库中最主要的查询,主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在关系数据...

七、DQL语句

七、DQL语句

引言DQL(Data QueryLanguage )数据查询语言,基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块。目录DQL概述记录查询条件查询1、DQL概述1.1、什么是DQ...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。