MySQL database learns the relationship between the "sixth" table and the table

Table 1 foreign Key Table 2 Table 1 is a number of records corresponding to table 2 a record, that is, a multi-to-one use of foreign key principle we can make two tables of many-to-many, one-to-many relationship:    table 1 of the multiple records can correspond to a record    of table 2 Table 2, a number of records can also correspond to a record of table 1 one-to-one:    table 1 is a record unique to table 2 a record, and vice versa, we first from the above basic principles to set, and then translated into the real meaning, it is very good understanding

1. Establish the relationship first

2, find more than one side, bar related fields written on the side of many

One, many pairs of one or more (multiple records on the left table correspond to only one record of the right table)

Note: 1. First build the associated table so that the fields of the associated table must be unique.

2. In creating an association table, the associated field must be guaranteed to be duplicated.

In fact, the previous blog has already given a small example of a many-to-one relationship, then we are using another small example to review.

This is an example of a book and a publishing house, a book to be associated with a publishing house (multiple books can be a publishing house, and a publishing house can have a lot of books).

Who is associated with who is who to follow whose standards.

The book to associate the publishing house is associated table CREATE TABLE press  (ID int primary key auto_increment,name char (20)); Associated Table CREATE TABLE book (book_id int Primary key Auto_increment,book_name varchar (), Book_price int,press_id int,constraint fk_pressid_id foreign Key ( press_id) references Press (ID) on delete cascadeon update cascade), insert record inserts into press (name) values (' Xinhua publishing House '),                              (' Haiyan Publishing House '),                              (' Ferry publishing house '),                              (' Volkswagen Press '); INSERT into book (book_name,book_price,press_id) VALUES (' Python crawler ', 100, 1),                                                       (' Linux ', 80,1),                                                       (' Operating system ', 70,2), ('                                                       math ', 50,2), ('                                                       English ', 103,3),                                                       (' web Design ', 22, 3);

Operation Result:

Two, one to one

Example one: Users and administrators (only administrators can log in, one administrator for one user)

Administrator Associated User

=========== Example one: The user table and the Administrator table ========= first build the associated table CREATE table user (ID int primary key auto_increment, #主键自增name char (10)); Building the association Table CREATE TABLE admin (ID int primary key auto_increment,user_id int unique,password varchar) foreign key (USER_ID) re Ferences User (ID) on the delete cascadeon update cascade), insert into user (name) values (' Susan1 '), ('                             susan2 '),                             (' Susan3 '), (' Susan4 '), (' Susan5 '), ('                             susan6 '); insert into admin (User_id,password) VALUES (4, ' sds156 '),                                          (2, ' 531561 '),                                          (6, ' f3swe ');

Operation Result:

Example two: Student tables and Customer tables

======== example two: Student table and Customer table =========create table customer (ID int primary KEY auto_increment,name varchar), QQ int Unique, phone int unique); CREATE table student1 (SID int primary key Auto_increment,course char), class_time time,cid int Unique, FOREIGN KEY (CID) references customer (ID) on delete cascadeon update cascade), insert into customer (Name,qq,phone) VALUES ('                                          Little ', 13564521,11111111), (' Hip-hop ', 14758254,22222222),                                          (' Wang Wei ', 44545522,33333333), (' Hu June ', 545875212,4444444),                                          (' Mr Leahy ', 145578543,5555555), (' Liddy ', 754254653,8888888), (' Ahava ', 74545145,8712547), (' Tut ', 11147752,7777777                                                 Insert into Student1 (COURSE,CLASS_TIME,CID) VALUES (' Python ', ' 08:30:00 ', 3),      (' Python ', ' 08:30:00 ', 4),                                           (' Linux ', ' 08:30:00 ', 1), (' Linux ', ' 08:30:00 ', 7);

Operation Result:

Three, many-to-many (multiple records corresponding to multiple records)

Book and author (we can create a second table to store books and author two-sheet relationships)

To set the book_id and author_id as a joint unique

Union unique: Unique (book_id,author_id)

Federated PRIMARY key: ALTER TABLE T1 add primary key (ID,AVG)

Many-to-many: an author can write many books, a book can also have multiple authors, two-way one-to-many, that is, many-to-many associations: foreign key+ a new table

======== Book and author, in addition to the construction of a table to save the book and the author's relationship # is associated with the CREATE TABLE Book1 (ID int primary KEY auto_increment,name varchar), Price float ( 3,2)); #======== is associated with the CREATE table author (id int primary key auto_increment,name char (5)); #======== Associated CREATE table Author2book (ID int primary key auto_increment,book_id int not null,author_id int. not Null,unique (BOOK_ID,AUTHOR_ID), Foreign KEY (book_id) references Book1 (id) on delete cascadeon update cascade,foreign key (author_id) references author (id)                                    On delete cascadeon update cascade) #======== inserting record insert into Book1 (Name,price) VALUES (' Nine Yang ', 9.9), (' Sunflower treasure ', 9.5), (' Evil spirits Sword Spectrum ', 5), (' Dragon 10 Slap ', 7.3); Insert into author (name) VALUES (' Egon '), (' E1 '), (' E2 '), (' E3 '), (' E4 '); insert into Author2book (book_id,author_id)                                                  VALUES (1,4), (2,1),    (2,5),                                             (3,2), (3,3), (3,4), (4,5);

Examples of many-to-many relationships

User tables, user groups, host tables

--User Group CREATE TABLE user (ID int primary key auto_increment,username varchar () not null,password varchar (a) not null); Sert into User (Username,password) VALUES (' Egon ', ' 123 '), ('                                          root ', 147), ('                                          Alex ', 123), ('                                          Haiyan ', 123),                                          (' Yan ', 123);--User Group table CREATE TABLE usergroup (ID int primary key auto_increment,groupname varchar () not  null Unique) insert into usergroup (groupname) VALUES (' IT '), (' Sale '), ('                                        Finance '),                                        (' Boss ');-- Establishing a relationship table for user and UserGroup

CREATE TABLE User2usergroup (
ID int not NULL UNIQUE auto_increment,
USER_ID int NOT NULL,
group_id int not NULL,
PRIMARY KEY (user_id,group_id),
Foreign KEY (user_id) references user (ID)
On DELETE CASCADE
On UPDATE CASCADE,
Foreign KEY (group_id) references UserGroup (ID)
On DELETE CASCADE
On UPDATE CASCADE
);

Insert into User2usergroup (user_id,group_id) values,
                                                      (),
                                                      (1,3),
                                                      (1,4),
                                                      (2,3),
                                                      (2,4),
                                                      (3,4);

--Host Table CREATE table host (ID int primary key AUTO_INCREMENT,IP CHAR () not NULL UNIQUE DEFAULT ' 127.0.0.1 '), insert into hos                             T (IP) VALUES (' 172.16.45.2 '), (' 172.16.31.10 '), (' 172.16.45.3 '), (' 172.16.31.11 '), (' 172.10.45.3 '), (' 172                             .10.45.4 '), (' 172.10.45.5 '), (' 192.168.1.20 '),                             (' 192.168.1.21 '), (' 192.168.1.22 '), (' 192.168.2.23 '), (' 192.168.2.223 '), (' 192.168.2.24 '), (' 19 2.168.3.22 '), (' 192.168.3.23 '), (' 192.168.3.24 ');--Business Line table Create tab                         Le Business (id int primary KEY auto_increment,business varchar (a) not null unique); Insert to business values               (' Easy loan '), (' Casual spending '), (' Monopoly '), (' poor life ');--establish host and business Relationship table CREATE TABLE host2business (ID int not NULL unique auto_i  ncrement,host_id int NOT NULL, business_id int NOT NULL, PRIMARY key (host_id,business_id), foreign Key (host_id) references                                                        Host (ID), FOREIGN KEY (business_id) REFERENCES Business (ID)) and insert into host2business (host_id,business_id) values                                                        (+),                                                        (1,3), (2,2), (2,3), (3,4);

 

--Establish the relationship between user and host CREATE TABLE user2host (ID int not NULL unique AUTO_INCREMENT,USER_ID int. not null,host_id int not NULL,PR Imary Key (user_id,host_id), foreign Key (user_id) references user (ID), foreign key (host_id) references host (ID)); insert                                                Into User2host (user_id,host_id) values (+),                                                (1,3), (1,4), (1,5), (1,6), (1,7                                                ), (1,8), (1,9),                                                (1,10), (1,11),                                                (1,12), (1,13),          (1,14),                                      (1,15), (1,16),                                                (2,2), (2,3), (2,4), (2,5), (3 , 10), (3,11), (3,12);

Practice

MySQL database learns the relationship between the "sixth" table and the table