外键的变种

---恢复内容开始---

外键的三种关系：

　　多对一‘，一对多

　　多对多

　　一对一

 

---恢复内容结束---

多对一‘，一对多

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

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
);

mysql> insert into press(name) values
    -> ("邮电出版社"),
    -> ("理工出版社"),
    -> ("大王出版社");
Query OK, 3 rows affected (0.05 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.08 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)

 多对多

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

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

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


mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.27 sec)

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.83 sec)

mysql> insert into author(name) values("egg"),("zz"),("qq"),("wx");
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> insert into book(name) values("九阳"),("八阳"),("七阳"),("六阳");
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into author2book(author_id,book_id) values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (1,4),
    -> (2,1),
    -> (2,2),
    -> (2,3),
    -> (3,1),
    -> (3,2),
    -> (4,1);
Query OK, 10 rows affected (0.14 sec)
Records: 10  Duplicates: 0  Warnings: 0

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 |         2 |       3 |
|  8 |         3 |       1 |
|  9 |         3 |       2 |
| 10 |         4 |       1 |
+----+-----------+---------+
10 rows in set (0.03 se

一对一

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

　　关联方式：foreign key+unique

 

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

#两张表： 用户表 （user）和 博客表(blog)
# 创建用户表
create table user(
    id int primary key auto_increment,
    name varchar(20)
);
# 创建博客表
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
);

# 插入数据
insert into user(name) values
    (1),
    (2),
    (3),
    (4);

insert into blog(url,user_id) values
    ("http://www.cncodad1",1),
    ("http://www.cgscoad2",2),
    ("http://www.cgs.cod3",3),
    ("http://www.cogsdad4",4);