One-to-many MySQL even table operation

Introduced

When we create a table in the database, it is possible that the values within some columns are large and repeatable.

Example: Create a student table, according to the school age class points, the table content is as follows:

Id	Name	Partment
1	Xxx	X School x Grade X class
2	Ooo	X School x Grade X class
3	zzz	Z School x Grade x class
4	Ddd	Y school x Grade X class

We can see that the corresponding Partment value is very long, and the amount of repetition is very large, which is not appropriate.

So we're thinking about separating the complex and repetitive parts separately into 2 tables:

First sheet:

Id	Caption
1	X School x Grade X class
2	Y school x Grade X class
3	Z School x Grade x class

The second table is modified on the basis of the previous one:

Id	Name	Partment
1	Xxx	1
2	Ooo	2
3	zzz	3
4	Ddd	1

This seems to be very concise, we will be a long and repeated parts out, and then set the number, create student tables, students corresponding to the partment only need to take the school's corresponding ID can be, so at the same time the 2 tables will be linked together.

Description

1, their related relations; Table 2, partment and table 1 IDs are linked together.

2, table One of the data will correspond to the data in table 2, which is called a pair of more .

Problem: At this point, the two tables are relatively independent, you can insert their own data, it is appropriate to do, and there is no real line of association?

Therefore, it is necessary to limit it. Table II's Partment data must be in table one, otherwise, it will not be added.

Here (partment) introduced a noun--外键

FOREIGN key

Description
1, FOREIGN key: one table receives the primary key of another table.
2, Partment foreign key is the NID in Table I.
3, when we create a foreign key, then the system will be added to the default for us, the corresponding constraints, such as: Table two partment data must be in the table one of the NID; Table II and table one are linked.

Navicat Creating a foreign key

Create part table:

To create a person table:

Note the creation of foreign keys:
When foreign keys are created, they correspond to each other in the table 数据类型必须一样 .

Create a foreign key

1. First right-click on the table where you want to create the foreign key, and select the design table.
2, enter the design table, display the table row information on the right, then click the foreign key:

3, select the field, a selection box will appear, select the column you want to set the foreign key;

4, select the Reference table, select the table to be associated with the foreign key,

Description
Parameter database: Because two databases are in the consent directory, so there is no choice here, the default is the same directory.

5. Select the Reference field and select the column in the reference table that you want to set as the foreign key;

Attention:
Foreign key creation, the data type of the associated column in a table must be the same, or it will not succeed.

6. Save Ctrl + S

Using foreign keys

There is no data in part, and if you add data to person at this point, the result:

To add data to the part, the result:

At this point, add the data to the person:

Attention:

You cannot enter the value that NID does not have in the part, or you will get an error.

SQL commands to create foreign keys

Create part:

CREATE TABLE part (NID int. NOT NULL auto_increment primary key,caption varchar (+) NOT NULL)

Part

Create Person:

CREATE TABLE person (  nid int not null auto_increment,  name varchar (+) default NULL,  email varchar (+) Defaul T null,  Part_nid int,  primary key (NID),  constraint Fk_person_part foreign key (Part_nid) references part ( NID))

Person

The relationship of the foreign key corresponding code after creation:

Analysis:
1, from the name can be seen where the code corresponds to what position.
2, the figure in the name (c corresponding code in the constraint) this line can not be set, the system will default to help me Setup, but it is best to set, if you want to delete the foreign key, you can use this name to do the corresponding operation .

Code Delete foreign Key

ALTER TABLE Person1 drop foreign key fk_person1_part1;

Code add foreign Key

ALTER TABLE Person1 add constraint fk_p1_t1 foreign key (Part1_nid) references part1 (NID);

Data manipulation with a foreign key

Part table:

Person Table:

Need: To find out who is the X school in the person table?

1, before the method of learning:

• 1, first go to part to get the x corresponding NID
• 2, and then through the NID and Parson in the part_id to use the relationship, find out the corresponding name

Select name from the person where part_id in (select Nid form part where caption = "x")

2. Linked list method left join

Left Join

Using the method provided by the attached table, the left JOIN operation code:

SELECT * from-person-left join-on person.part_id = Part.nid

The results show:

Analysis:
Left join: The portion that matches the part table is moved directly behind the person's column and is displayed together.

Now that the content is merged, we can then add the judgment to get the data we want.

Code Listing 1:

SELECT * from-person-left join-on person.part_id = Part.nid where part.caption = "x"

Results:

Description

Previously we used * to get all the information, if we get the specified information, it can be modified.

For example: Get the name code for person only:

Select Person.name from-person-left join-on person.part_id = Part.nid where part.caption = "x"

Results:

注意：
The condition of a join connection, using on for docking, the condition is written on the back.

A left join B on a.xx = b.xx

Features of the LEFT join:
1, to a-based
2. List all the data in a
3, B displays only the data corresponding to a

Question: Verify who we are talking about who is the primary, who's data is all displayed, we change the person and part position?

SELECT * from "LEFT join person" on person.part_id = Part.nid;

Results:

Right Join

In terms of who shows all the data, he and the left join are just the opposite, with the following table as the main, showing all of their data.

INNER JOIN

Ignores data that does not have a relationship established. No matter who is in front, the result is the same.

SELECT * FROM person INNER JOIN part on person.part_id = Part.nid;

Results:

Summarize:
1, these join can write multiple, meaning that a table can have multiple foreign keys at the same time.

2, when the selected column name, is the only word in all tables, you can not write the prefix table name. Such as: person.part_id can be directly written part_id.

3, the above part table, has the individual name, the dictionary table

One-to-many MySQL even table operation