8.3.7-mysql Relationships between tables

How to find the relationship between two tables

analysis steps: # 1 , first stand in the angle of the left table to find whether the left table of multiple records can correspond to a record of the right table, if so, prove a field of the left table foreign key right Table a field (usually ID) # 2 , and then stand on the right side of the table to find out if the right table of multiple records can correspond to a record of the left table, if so, then prove a field of the right table foreign key left table a field (usually ID) # 3 , Summary: #多对一: If only step 1 is established, it is the left table many to one right table if only step 2 is established, then the right table is many pairs of a left table # Many to many if steps 1 and 2 are also established, then prove that the two tables when a two-way multi-pair, that is, many-to-many, You need to define a relational table of these two tables to store the relationship of both. # one-to-one: if both 1 and 2 are not true, a single record of the left table corresponds to a record in the right table and vice versa. This is simple, that is, in the left table foreign key to the right table based on the left table's foreign key field is set to a unique can

Establish a relationship between tables

#一对多或称为多对一三张表: Publishing house, author information, book one-to-many (or many to one): A publishing house can publish multiple books related ways: foreign key

the relationship between two sheets1. Many-to-one publishing house book (press_idint, foreign Key (preess_id) references press (ID))2. Many-to-many3. One-to-many or multiple-pair 13 tables: publishing house, author information, book-to-many (or many to one): A publishing house can publish multiple books related ways: foreign key===================== many to one =====================CREATE TABLE Press (IDintPRIMARY key auto_increment,name varchar ( -) ; CREATE table book (IDintPRIMARY key auto_increment,name varchar ( -), press_idintNotNULL, foreign Key (press_id) references press (ID) on the delete cascadeon update cascade); INSERT into press (name) VALUES ('Mechanical Industry Press'),('Publishing House'),('Nanjing Press') insert into book (name,press_id) VALUES ('Computer Fundamentals',1),('Getting started with Python',2),('MySQL',2),('Mechanical Principle',3),('100,000 Why',2),('Social Sciences',3); # operation Process MySQL>CREATE TABLE Press (Idintprimary Key auto_increment,Name varchar ( -)        - ); Query OK,0Rows Affected (0.03sec) MySQL>MySQL>CREATE TABLE book (Idintprimary Key auto_increment,Name varchar ( -),        press_idintNotNULL,        -foreign KEY (press_id) references press (ID)-On Delete Cascade-On update Cascade- ); Query OK,0Rows Affected (0.03sec) MySQL>INSERT into Press (name) values('Mechanical Industry Press'),        ('Publishing House'),        ('Nanjing Press')        - ; Query OK,3Rows Affected (0.00sec) Records:3Duplicates:0Warnings:0MySQL>MySQL>INSERT into book (name,press_id) Values('Computer Fundamentals',1),        ('Getting started with Python',2),        ('MySQL',2),        ('Mechanical Principle',3),        ('100,000 Why',2),        ('Social Sciences',3)        - ; Query OK,6Rows Affected (0.01sec) Records:6Duplicates:0Warnings:0MySQL>DESC Press; +-------+-------------+------+-----+---------+----------------+    | Field | Type | Null | Key | Default |    Extra | +-------+-------------+------+-----+---------+----------------+    | ID |int( One)     | NO | PRI | NULL |    auto_increment | | name | varchar -) |     YES | |                NULL |    | +-------+-------------+------+-----+---------+----------------+2Rowsinch Set(0.00sec) MySQL>Select* fromBook ; +----+--------------------+----------+    | ID | name |    press_id | +----+--------------------+----------+    |1| Fundamentals of Computing |1| |2| Getting Started with Python |2| |3| MySQL |2| |4| Mechanical Principle |3| |5| 100,000 Why |2| |6| Social Sciences |3| +----+--------------------+----------+6Rowsinch Set(0.00sec) MySQL>Select* fromPress ; +----+-----------------------+    | ID |    name | +----+-----------------------+    |1|    Mechanical Industry Press | |2|    Publishing House | |3|    Nanjing Press | +----+-----------------------+3Rowsinch Set(0.00Sec

8.3.7-mysql relationships between tables