Shulou(Shulou.com)05/31 Report--

What is the relationship between mysql foreign keys, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Many to one

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

)

# insert a record into the associated table first

Insert into press (name) values

(Beijing Industrial Mine Publishing House)

(people's Music is not good for Publishing House)

('intellectual property rights are not useful to publishers')

# insert a record into the associated table

Insert into book (name,press_id) values

('Jiuyang Shengong', 1)

(Jiuyin True Sutra, 2)

('Jiuyin White Bone claw', 2)

('Dugujiujian', 3)

('falling Dragon Ten slaps', 2)

(the Treasure Book of Sunflower, 3)

Query results:

Mysql > select * from book

+-- +

| | id | name | press_id | |

+-- +

| | 1 | Jiuyang Shengong | 1 |

| | 2 | Jiuyin Sutra | 2 |

| | 3 | Jiuyin White Bone claw | 2 |

| | 4 | Dugu Jiujian | 3 |

| | 5 | Lianglong ten slaps | 2 |

| | 6 | Sunflower Baodian | 3 |

+-- +

Rows in set (0.00 sec)

Mysql > select * from press

+-+-

| | id | name |

+-+-

| | 1 | Beijing Industrial Mine Publishing House |

| | 2 | people's Music is not good for Publishing House |

| | 3 | intellectual property rights are not used by publishers |

+-+-

Rows in set (0.00 sec)

Many to many

# create the associated table author table. The previous book table says that a many-to-one relationship has been created

Create table author (

Id int primary key auto_increment

Name varchar (20)

)

# this table stores the relationship between author table and book table, that is, you can query the relationship between them and look up this table.

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)

)

# insert four authors and id in turn

Insert into author (name) values ('egon'), (' alex'), ('wusir'), (' yuanhao')

# the representative work of each author

Egon: Jiuyang Shenggong, Jiuyin True Sutra, Jiuyin White Bone claw, Dugu Nine Sword, falling Dragon Ten slaps, Sunflower Treasure Dian

Alex: Jiuyang Shenggong, Sunflower Treasure Book

Wusir: solitary nine swords, falling dragon ten slaps, sunflower treasure book

Yuanhao: Jiuyang Shenggong

# insert the corresponding data in the author2book table

Insert into author2book (author_id,book_id) values

(1pc1)

(1pd2)

(1pr 3)

(1pc4)

(1par 5)

(1pc6)

(2pr 1)

(2pc6)

(3pr 4)

(3pr 5)

(3pd6)

(4par 1)

# now you can check the relationship between the author and the book corresponding to author2book

Mysql > select * from author2book

+-- +

| | id | author_id | book_id | |

+-- +

| | 1 | 1 | 1 |

| | 2 | 1 | 2 |

| | 3 | 1 | 3 |

| | 4 | 1 | 4 |

| | 5 | 1 | 5 |

| | 6 | 1 | 6 |

| | 7 | 2 | 1 |

| | 8 | 2 | 6 |

| | 9 | 3 | 4 |

| | 10 | 3 | 5 | |

| | 11 | 3 | 6 | |

| | 12 | 4 | 1 |

+-- +

Rows in set (0.00 sec)

one-for-one

# for example: a user can only register for one blog

# two tables: user table (user) and blog table (blog)

# create a user table

Create table user (

Id int primary key auto_increment

Name varchar (20)

)

# create a blog table

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 records in the user table

Insert into user (name) values

('alex')

('wusir')

('egon')

('xiaoma')

# insert the record of the blog table

Insert into blog (url,user_id) values

('http://www.cnblog/alex',1),

('http://www.cnblog/wusir',2),

('http://www.cnblog/egon',3),

('http://www.cnblog/xiaoma',4)

# query the blog address of wusir

Select url from blog where user_id=2

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.