mysql-外键的三种关系

发布时间:2018-06-13 18:48:43编辑:Run阅读(3845)

    介绍

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

    多对一

    多对多

    一对一


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

    分析步骤:
    #1、先站在左表的角度去找
    是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)
    
    #2、再站在右表的角度去找
    是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)
    
    #3、总结:
    #多对一:
    如果只有步骤1成立,则是左表多对一右表
    如果只有步骤2成立,则是右表多对一左表
    
    #多对多
    如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的
    关系
    
    #一对一:
    如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右
    表的基础上,将左表的外键字段设置成unique即可


    表的三种关系

    (1)书和出版社

      一对多(或多对一):一个出版社可以出版多本书。看图说话。

      关联方式:foreign key

    blob.png


    先创建主表press

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

    Query OK, 0 rows affected (0.09 sec)


    再创建从表book

    mysql> create table book(id int primary key auto_increment,name varchar(20),press_id int not null,constraint fk_book_press foreign key(press_id) references press(id) on delete cascade on update cascade);

    Query OK, 0 rows affected (0.04 sec)


    先往主表中插入记录

    mysql> insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知 识产权没有用出版社');

    Query OK, 3 rows affected (0.00 sec)

    Records: 3  Duplicates: 0  Warnings: 0


    再往从表中插入记录

    mysql> insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3);

    Query OK, 6 rows affected (0.01 sec)

    Records: 6  Duplicates: 0  Warnings: 0


    查询结果:

    mysql> select * from book;

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

    | id | name            | press_id |

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

    |  1 | 九阳神功        |        1 |

    |  2 | 九阴真经        |        2 |

    |  3 | 九阴白骨爪      |        2 |

    |  4 | 独孤九剑        |        3 |

    |  5 | 降龙十巴掌      |        2 |

    |  6 | 葵花宝典        |        3 |

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

    6 rows in set (0.00 sec)


    mysql> select * from press;

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

    | id | name                           |

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

    |  1 | 北京工业地雷出版社             |

    |  2 | 人民音乐不好听出版社           |

    |  3 | 知识产权没有用出版社           |

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

    3 rows in set (0.00 sec)



    (2)作者和书籍的关系

      多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多。看图说话。

      关联方式:foreign key+一张新的表

    blob.png


    创建被关联表author表,之前的book表在讲多对一的关系已创建

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

    Query OK, 0 rows affected (0.09 sec)


    这张表就存放了author表和book表的关系,即查询二者的关系查这表就可以了

    mysql> create table author2book(id int not null unique 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,primary key(author_id,book_id));

    Query OK, 0 rows affected (0.07 sec)


    插入四个作者,id依次排开

    mysql> insert into author(name) values('zhangsan'),('lisi'),('wangwu'),('zhuliu');

    Query OK, 4 rows affected (0.01 sec)

    Records: 4  Duplicates: 0  Warnings: 0


    每个作者的代表作

    zhangsan: python全栈开发,web前端、爬虫技术、linux高级运维

    lisi: python全栈开发、linux高级运维

    wangwu:web前端、爬虫技术、linux高级运维

    zhuliu:python全栈开发


    在author2book表中插入相应的数据

    mysql> insert into author2book(author_id,book_id) values (1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(3,2),(3,3),(3,4),(4,1);

    Query OK, 10 rows affected (0.00 sec)

    Records: 10  Duplicates: 0  Warnings: 0


    现在就可以查author2book对应的作者和书的关系了

    mysql> select * from author2book;

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

    | id | author_id | book_id |

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

    |  1 |         1 |       1 |

    |  2 |         1 |       2 |

    |  3 |         1 |       3 |

    |  4 |         1 |       4 |

    |  5 |         2 |       1 |

    |  6 |         2 |       2 |

    |  7 |         3 |       2 |

    |  8 |         3 |       3 |

    |  9 |         3 |       4 |

    | 10 |         4 |       1 |

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

    10 rows in set (0.00 sec)


    (3)用户和博客

      一对一:一个用户只能注册一个博客,即一对一的关系。看图说话

      关联方式:foreign key+unique

    blob.png


    例如: 一个用户只能注册一个博客

    两张表: 用户表 (user)和 博客表(blog)


    创建用户表

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

    Query OK, 0 rows affected (0.09 sec)


    创建博客表

    mysql> create table blog(id int primary key auto_increment,url varchar(100),user_id int unique,constraint fk_user foreign key(user_id) references user(id) on delete cascade on update cascade);

    Query OK, 0 rows affected (0.06 sec)


    插入用户表中的记录

    mysql> insert into user(name) values('alex'),('wusir'),('egon'),('xiaoma');

    Query OK, 4 rows affected (0.01 sec)

    Records: 4  Duplicates: 0  Warnings: 0


    插入博客表的记录

    mysql> insert into blog(url,user_id) values('http://www.zhangsan.com',1),('http://www.lisi.com',2),('http://www.wangwu.com',3),('http://www.zhuliu.com',4);

    Query OK, 4 rows affected (0.01 sec)

    Records: 4  Duplicates: 0  Warnings: 0


    查询lisi的博客地址

    mysql> select url from blog where user_id=2;

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

    | url                 |

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

    | http://www.lisi.com |

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

    1 row in set (0.00 sec)


关键字