【MySQL】外键的变种 -- 2019-08-07 09:54:58

原创: http://106.13.73.98/__/26/

目录

三种关系

多对一

多对多

一对一


因为有foreign key的约束,使得两张表形成了三种关系:

  • 多对一
  • 多对多
  • 一对多

重点理解如何找出两张表之间的关系

现在有A、B两张表
分析步骤:

1. 先站在A表的角度去找
:是否A表的多条记录可以对应B表的一条记录,如果是,则证明A表的一个字段 foreign key B表的一个字段(通常是id).

2. 再站在B表的角度去找
:是否B表的多条记录可以对应A表的一条记录,如果是,则证明B表的一个字段 foreign key A表的一个字段(通常是id).

3. 总结:
多对一
如果是步骤1成立,则是A表多对一B表
如果是步骤2成立,则是B表多对一A表

多对多
如果步骤1和步骤2同时成立,则证明这两张表是一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系.

一对一
如果1和2都不成立,而是A表的一条记录唯一对应B表的一条记录,反之亦然。这种情况很简单,就是在A表 foreign key B表的基础上,将A表的外键字段设置成unique即可



三种关系

多对一

或者说是一对多

举例:书和出版社
一个出版社可以出版多本书,请看图:
![在这里插入图片描述](http://106.13.73.98/media/ai/2019-03/463fea85-f6a4-4305-aff5-587f53b643e2.png)

  1. # 创建出版社表
  2. mysql> create table press(
  3. -> id int primary key auto_increment,
  4. -> name varchar(20)
  5. -> );
  6. Query OK, 0 rows affected (0.07 sec)
  7. # 创建书籍表
  8. mysql> create table book(
  9. -> id int primary key auto_increment,
  10. -> name varchar(20),
  11. -> press_id int not null,
  12. -> constraint fk_book_press foreign key(press_id) references press(id)
  13. -> on delete cascade
  14. -> on update cascade
  15. -> );
  16. Query OK, 0 rows affected (0.05 sec)
  17. # 先往被关联表中插入记录
  18. mysql> insert into press(name) values
  19. -> ('北京工业地雷出版社'),
  20. -> ('人民音乐不好听出版社'),
  21. -> ('知识产权没有用出版社');
  22. Query OK, 3 rows affected (0.00 sec)
  23. Records: 3 Duplicates: 0 Warnings: 0
  24. # 再往关联表中插入记录
  25. mysql> insert into book(name, press_id) values
  26. -> ('九阳神功',1),
  27. -> ('九阴真经',2),
  28. -> ('九阴白骨爪',2),
  29. -> ('独孤九剑',3),
  30. -> ('降龙十巴掌',2),
  31. -> ('葵花宝典',3);
  32. Query OK, 6 rows affected (0.04 sec)
  33. Records: 6 Duplicates: 0 Warnings: 0
  34. # 查询结果
  35. mysql> select * from book;
  36. +----+-----------------+----------+
  37. | id | name | press_id |
  38. +----+-----------------+----------+
  39. | 1 | 九阳神功 | 1 |
  40. | 2 | 九阴真经 | 2 |
  41. | 3 | 九阴白骨爪 | 2 |
  42. | 4 | 独孤九剑 | 3 |
  43. | 5 | 降龙十巴掌 | 2 |
  44. | 6 | 葵花宝典 | 3 |
  45. +----+-----------------+----------+
  46. 6 rows in set (0.00 sec)
  47. mysql> select * from press;
  48. +----+--------------------------------+
  49. | id | name |
  50. +----+--------------------------------+
  51. | 1 | 北京工业地雷出版社 |
  52. | 2 | 人民音乐不好听出版社 |
  53. | 3 | 知识产权没有用出版社 |
  54. +----+--------------------------------+
  55. 3 rows in set (0.00 sec)

多对多

举例:作者和书籍的关系
一个作者可以写多本书,一本书也可以有多个作者,即多对多。请看图:
![在这里插入图片描述](http://106.13.73.98/media/ai/2019-03/67921b80-a73c-4aed-89b3-dcbced259f18.png)

  1. # 创建书籍表
  2. mysql> create table book(
  3. -> id int primary key auto_increment,
  4. -> name varchar(20)
  5. -> );
  6. Query OK, 0 rows affected (0.06 sec)
  7. # 创建作者表
  8. mysql> create table author(
  9. -> id int primary key auto_increment,
  10. -> name varchar(20)
  11. -> );
  12. Query OK, 0 rows affected (0.18 sec)
  13. # 这张表用于记录author表与book表的关系
  14. mysql> create table author2book(
  15. -> id int not null unique auto_increment,
  16. -> author_id int not null,
  17. -> book_id int not null,
  18. -> constraint fk_author foreign key(author_id) references author(id)
  19. -> on delete cascade
  20. -> on update cascade,
  21. -> constraint fk_book foreign key(book_id) references book(id)
  22. -> on delete cascade
  23. -> on update cascade,
  24. -> primary key(author_id,book_id)
  25. -> );
  26. Query OK, 0 rows affected (0.09 sec)
  27. # 插入记录
  28. mysql> insert into author(name) values('egon'), ('alex'), ('wusir'), ('yuanhao');
  29. Query OK, 4 rows affected (0.00 sec)
  30. Records: 4 Duplicates: 0 Warnings: 0
  31. mysql> insert into book(name) values('Python全栈'), ('Linux高级运维'), ('爬虫技术'), ('Web前端');
  32. Query OK, 4 rows affected (0.01 sec)
  33. Records: 4 Duplicates: 0 Warnings: 0
  34. # 插入作者与书籍的对应关系
  35. mysql> insert into author2book(author_id, book_id) values
  36. -> (1, 1),
  37. -> (1, 2),
  38. -> (2, 1),
  39. -> (3, 1),
  40. -> (4, 4),
  41. -> (4, 3);
  42. Query OK, 6 rows affected (0.04 sec)
  43. Records: 6 Duplicates: 0 Warnings: 0
  44. mysql> select * from author2book;
  45. +----+-----------+---------+
  46. | id | author_id | book_id |
  47. +----+-----------+---------+
  48. | 1 | 1 | 1 |
  49. | 2 | 1 | 2 |
  50. | 3 | 2 | 1 |
  51. | 4 | 3 | 1 |
  52. | 5 | 4 | 4 |
  53. | 6 | 4 | 3 |
  54. +----+-----------+---------+
  55. 6 rows in set (0.00 sec)

一对一

举例:用户和博客
一个用户只能注册一个博客,即一对一的关系,请看图:
![在这里插入图片描述](http://106.13.73.98/media/ai/2019-03/939923a5-6aab-4aa4-b47b-1f9c71a5e598.png)

  1. # 创建用户表
  2. mysql> create table user(
  3. -> id int primary key auto_increment,
  4. -> name varchar(20)
  5. -> );
  6. Query OK, 0 rows affected (0.11 sec)
  7. # 创建博客表
  8. mysql> create table blog(
  9. -> id int primary key auto_increment,
  10. -> url varchar(100),
  11. -> user_id int unique,
  12. -> constraint fk_user foreign key(user_id) references user(id)
  13. -> on delete cascade
  14. -> on update cascade
  15. -> );
  16. Query OK, 0 rows affected (0.08 sec)
  17. # 插入用户表中的记录
  18. mysql> insert into user(name) values('alex'), ('wusir'), ('egon'), ('xiaoma');
  19. Query OK, 4 rows affected (0.04 sec)
  20. Records: 4 Duplicates: 0 Warnings: 0
  21. # 插入博客表的记录
  22. mysql> insert into blog(url, user_id) values
  23. -> ('http:/blog.csdn.net/alex', 1),
  24. -> ('http:/blog.csdn.net/wusir', 2),
  25. -> ('http:/blog.csdn.net/egon', 3),
  26. -> ('http:/blog.csdn.net/xiaoma', 4);
  27. Query OK, 4 rows affected (0.00 sec)
  28. Records: 4 Duplicates: 0 Warnings: 0
  29. # 查找wusir的博客地址
  30. mysql> select url from blog where user_id=2;
  31. +---------------------------+
  32. | url |
  33. +---------------------------+
  34. | http:/blog.csdn.net/wusir |
  35. +---------------------------+
  36. 1 row in set (0.00 sec)

 

 



 

原创: http://106.13.73.98/__/26/

上一篇:【正则表达式介绍篇】 -- 2019-08-07 10:54:47


下一篇:【正则表达式介绍篇】--2019-08-06 15:55:54