Shulou(Shulou.com)06/01 Report--

I don't know if you know anything about the relationship between MySQL tables and tables before. Today, I'm here to tell you a little bit about it. If you are interested, let's take a look at the text. I believe you will gain something after reading the relationship between the MySQL table and the table.

The relationship between tables and tables

Table 1, foreign key, Table 2 corresponds to a record of Table 1, that is, many-to-one, using the principle of foreign key, we can make many-to-many and one-to-one relations of two tables: multiple records of Table 1 can correspond to one record of Table 2 or one record of Table 2: one record of Table 1 corresponds to one record of Table 2, and vice versa. It is easy to understand that we first set it up according to the above basic principles, and then translate it into the real meaning.

1. Determine the relationship first

2. Find more than one party and write the associated field on the multiple side

One to many

Many-to-one or one-to-many (multiple records in the left table correspond to the only record in the right table)

What to pay attention to:

1. Create the associated table first to ensure that the fields of the associated table must be unique.

two。 When creating an associated table, the associated fields must be guaranteed to be duplicated.

Example:

This is an example of a book and a publishing house, where books are associated with publishers (multiple books can be one publishing house, and one publishing house can have many books).

Whoever is connected is who should follow whose standards.

Create a tabl

The book should be associated with the associated table create table press (id int primary key auto_increment, name char (20)); the associated table create table book (book_id int primary key auto_increment,book_name varchar (20), book_price int,press_id int,constraint Fk_pressid_id foreign key (press_id) references press (id) on delete cascadeon update cascade)

Insert data

Insert into press (name) values (Xinhua Publishing House), (Haiyan Publishing House), (Ferry Publishing House), (Mass Publishing House); insert into book (book_name,book_price,press_id) values ('Python Crawler', 100penny 1), ('Linux',80,1), (' operating system', 70Person2), ('Mathematics', 50Person2), ('English', 103 Person3), ('Web Design', 22Person3)

Running result

one-for-one

Example 1:

Users and administrators (only administrators can log in, one administrator corresponds to one user)

Administrator Associates user

Create a tabl

First build the associated table create table user (id int primary key auto_increment, # primary key self-increasing name char (10)); then create the associated table create table admin (id int primary key auto_increment,user_id int unique,password varchar (16), foreign key (user_id) references user (id) on delete cascadeon update cascade)

Insert data

Insert into user (name) values ('susan1'), (' susan2'), ('susan3'), (' susan4') ('susan5'), (' susan6'); insert into admin (user_id,password) values

Running result

Example 2:

Student form and customer form

Create a tabl

Create table customer (id int primary key auto_increment,name varchar (10), qq int unique,phone int unique); create table student1 (sid int primary key auto_increment,course char (20), class_time time,cid int unique,foreign key (cid) references customer (id) on delete cascadeon update cascade)

Insert data

Insert into customer (name,qq,phone) values ('Xiao', 13564521, 11111111), (hip-hop, 14758254, 22222222), (Wang Wei, 44545522, 33333333), (Hu Jun, 545875212, 4444444), (Li Xi, 145578543, 5555555), (Li Di, 75425463, 8888888), (Iha, 7454514552, 8712547), (Tut, 11147752, 777777) 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)

Running result

Many to many

Book and author (we can create another table to store the relationship between the book and author tables)

To set book_id and author_id to be joint and unique

Joint uniqueness: unique (book_id,author_id)

Joint primary key: alter table T1 add primary key (id,avg)

Many-to-many: an author can write multiple books, or a book can have multiple authors, two-way one-to-many, that is, many pairs

Association method: foreign key+ a new table

Example:

Create a tabl

= the book and the author, and another table is being built to store the relationship between the book and the author # the associated create table book1 (id int primary key auto_increment,name varchar (10), price float (3Power2)); # = the associated 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)

Insert data

Insert into book1 (name,price) values ('Jiuyang Shengong', 9.9), ('Sunflower Treasure Dian', 9.5), ('Expedition of evil spirits', 5), ('falling dragon ten slaps', 7.3); insert into author (name) values ('egon'), (' e1'), ('e2'), (' e3'), ('e4') Insert into author2book (book_id,author_id) values (1), (1), (2), (2), (2), (3), (3), (4) and (4)

Examples of many-to-many relations

User table, user group, host table

Create three tables

-user table create table user (id int primary key auto_increment,username varchar (20) not null,password varchar (50) not null); insert 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 (20) not null unique) Insert into usergroup (groupname) values ('IT'), (' Sale'), ('Finance'), (' boss');-- Host table CREATE TABLE host (id int primary key auto_increment,ip CHAR (15) not NULL UNIQUE DEFAULT '127.0.0.1') Insert into host (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'), ('192.168.3.22'), ('192.168.3.23'), ('192.168.3.24')

Establish a relationship

Establish the relation tables of 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 CASCADEon UPDATE CASCADE, foreign key (group_id) references usergroup (id) ON DELETE CASCADEon UPDATE CASCADE); insert into user2usergroup (user_id,group_id) values (1), (1), (1), (1), (2), (2), (3) 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,primary 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), (1), (1), (2), (1), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (2), (2), (2), (4), (5), (10), (10), (11), (12)

What do you think of this article after reading the relationship between MySQL tables and watches? If you want to know more about it, you can continue to follow our industry information section.

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.