专栏首页TechBoxMYSQL回顾(表关系相关)

MYSQL回顾(表关系相关)

数据库表有三种关系:

  1. 多对一
  2. 多对多
  3. 一对一

多对一

外键在“多”侧

比如有两个表,分别是书籍表和出版社表。书籍和出版社是典型的多对一关系,即一本书只能由一个出版社出版,一个出版社可以出版多本书。则书籍表应该有一个外键press_id指向出版社表的id primary key

建表顺序

先建被关联表(多)即出版社表,再建关联表即书籍表(一) 关联表(书籍表)有一个外键press_id指向被关联表的主键(出版社id)

实践

# 先建被关联表
mysql> create table press(

    -> id int primary key,

    -> name char(40)

    -> ) auto_increment=1;

Query OK, 0 rows affected (0.05 sec)
# 再建关联表
mysql> create table book(

    -> id int primary key,

    -> name char(60) not null,

    -> press_id int,

    -> constraint fk_press foreign key(press_id) references press(id)

    -> on delete cascade

    -> on update cascade

    -> ) auto_increment=1;

Query OK, 0 rows affected (0.02 sec)

插入数据

先插入被关联表记录 再插入关联表记录

mysql> insert into press(id, name) values(1, "北京邮电出版社"), (2, "中信出版社");

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into book(id, name, press_id) values

    -> (1, "计算机基础", 1),

    -> (2, "操作系统", 1),

    -> (3, "编译原理", 1),

    -> (4, "C语言", 2);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0
mysql> delete from press where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from book;

+----+-----------------+----------+

| id | name            | press_id |

+----+-----------------+----------+

|  1 | 计算机基础      |        1 |

|  2 | 操作系统        |        1 |

|  3 | 编译原理        |        1 |

+----+-----------------+----------+

3 rows in set (0.00 sec)

多对多

多对多因为都存在外键的依赖关系,所以建表的时候不会成功。需要第三张表来建立他们的外键关系,如下:

image.png

建表顺序

先建立author表和book表,在建立author2book表 author2book表中有两个外键 author_id 和 book_id

author_id指向author表的id book_id指向book表的id

实践

mysql> create table author(

    -> id int primary key auto_increment,

    -> name char(20)

    -> );

Query OK, 0 rows affected (0.02 sec)

mysql> create table book(

    -> id int primary key auto_increment,

    -> name char(60)

    -> );

Query OK, 0 rows affected (0.01 sec)
mysql> create table author2book(

    ->     id int primary key auto_increment,

    ->     author_id int not null,

    ->     book_id int not null,

    ->     constraint fk_author foreign key(author_id) references author(id)

    ->     on delete cascade

    ->     on update cascade,

    ->     constraint fk_book foreign key(book_id) references book(id)

    ->     on delete cascade

    ->     on update cascade

    -> );

Query OK, 0 rows affected (0.02 sec)

在author2book表中最使用联合唯一的方式指定主键,即author_id 和 book_id联合成为primary key

create table author2book2(

    id int not null unique auto_increment,

    author_id int not null,

    book_id int not null,

    foreign key(author_id) references author(id)

    on delete cascade

    on update cascade,

    foreign key(book_id) references book(id)

    on delete cascade

    on update cascade,

    primary key(author_id, book_id)

    );

建立author2book表:

可以使用constraint给外键起别名(可选)

image.png

建表时遇到以下错误,原因是最后一行不需要逗号,去掉逗号即可:

image.png

不允许存在同名的外键(因为已经在author2book表中有一个外键的名字叫做fk_author):

image.png

mysql> insert into author(name) value("jack"),("mark");

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into book(name) value("操作系统"),("C语言");

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from author;

+----+------+

| id | name |

+----+------+

|  1 | jack |

|  2 | mark |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from book;

+----+--------------+

| id | name         |

+----+--------------+

|  1 | 操作系统     |

|  2 | C语言        |

+----+--------------+

2 rows in set (0.00 sec)

mysql> insert into author2book(author_id, book_id)

    -> value(1, 1),(2,2)

    -> ;

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from author2book;

+----+-----------+---------+

| id | author_id | book_id |

+----+-----------+---------+

|  1 |         1 |       1 |

|  2 |         2 |       2 |

+----+-----------+---------+

2 rows in set (0.00 sec)

mysql> delete from author2book where author_id=1 and book_id=1;

Query OK, 1 row affected (0.00 sec)

mysql> select * from author2book;

+----+-----------+---------+

| id | author_id | book_id |

+----+-----------+---------+

|  2 |         2 |       2 |

+----+-----------+---------+

1 row in set (0.00 sec)

mysql> select * from author;

+----+------+

| id | name |

+----+------+

|  1 | jack |

|  2 | mark |

+----+------+

2 rows in set (0.00 sec)

mysql> select * from book;

+----+--------------+

| id | name         |

+----+--------------+

|  1 | 操作系统     |

|  2 | C语言        |

+----+--------------+

2 rows in set (0.00 sec)

可见,在多对多关系中,删除author2book表中的记录后,author表和book表的记录并没有删除

一对一

  1. 两张表:学生表和客户表 对于一些教育培训机构而言,客户即使潜在的学生,是一对一的关系
  2. 一对一:一个学生是一个客户,一个客户有可能变成一个学生,即一对一的关系
  3. 关联方式:foreign key+unique
  4. 对于多对一的关系而言:关联表中的外键无需指定为unique,因为存在多个记录的外键指向被关联表的同一个记录
  5. 但对于一对一的关系而言:为了保证两张表记录的一一对应,需要把关联表的外键约束为unique

image.png

  1. 一定是student来foreign key表customer,这样就保证了:
  2. 学生一定是一个客户,
  3. 客户不一定是学生,但有可能成为一个学生4. create table customer(
 create table customer(
 id int primary key auto_increment,
 name varchar(20) not null,
 qq varchar(10) not null,
 phone char(16) not null
 );
 create table student(
 id int primary key auto_increment,
 class_name varchar(20) not null,
 customer_id int unique, #该字段一定要是唯一的
 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
 on delete cascade
 on update cascade
 );

实践:

mysql> create table customer(

    -> id int primary key AUTO_INCREMENT,

    -> name char(20)

    -> );

Query OK, 0 rows affected (0.01 sec)
mysql> create table student(

    -> id int primary key AUTO_INCREMENT,

    -> name char(20),

    -> cus_id int unique,

    -> foreign key(cus_id) references customer(id)

    -> on delete cascade

    -> on update cascade

    -> );

Query OK, 0 rows affected (0.01 sec)

和多对一一样,更新或删除时:

删除被关联表的记录,关联表的相关记录也会被删除 删除关联表的记录,被关联表的相关记录不会被删除

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MYSQL回顾(单表查询相关)

    ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c...

    VV木公子
  • MYSQL回顾(多表查询相关)

    简单的数据我们可以直接从一个表中获取,但在真实的项目中查询符合条件的数据通常需要牵扯到多张表,这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询...

    VV木公子
  • MYSQL回顾(完整性约束相关)

    unique、 primary key、not null、default相对简单,本篇文章不做记录。

    VV木公子
  • THINKPHP 中关联查询(多表查询)

    THINKPHP 中关联查询(多表查询)可以使用 table() 方法或和join方法,请看示例:

    公众号php_pachong
  • mysql-外键的三种关系

    mysql> create table press(id int primary key auto_increment,name varchar(20));

    py3study
  • MySQL数据库3分组与单表、多表查询

    select * from 表名 where 列名 in (值1,值2,。。。);

    GH
  • 面试官问:MySQL的自增ID用完了,怎么办?

    可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned...

    用户4143945
  • Python for Everything

    freesan44
  • 面试官问:MySQL的自增ID用完了,怎么办?

    可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned...

    xcbeyond
  • TP踩过的坑【批量删除,(不涉及子栏目的批量删除)】

    简单、

扫码关注云+社区

领取腾讯云代金券