组团学

表与表之间关系

阅读 (190563)

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

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

一、表与表之间关系概述

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

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

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

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

1.2、表与表之间关系分类

1.2.1、一对多关系

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

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

image20200215112949021.png

注意:

一对多的创建原则:

主外键关连

1.2.2、一对一关系

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

image20200215122050731.png

注意:

一对一的创建原则:

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

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

1.2.3、多对多关系

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

image20200215121200999.png

注意:

多对多的创建原则:

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

2、一对多应用

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

image20200215123556759.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 秒)

三、一对一应用

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

image20200215164955805.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设置成联合唯一

image20200215161523092.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 秒)
需要 登录 才可以提问哦