mysql< Multi-table operation >

Multi-table Operation FOREIGN Key

What is a foreign key

A foreign key refers to a column or columns that reference another table, and the referenced column should have a PRIMARY key constraint or a uniqueness constraint.

Foreign keys are used to establish and strengthen links between two table data.

To add a foreign key constraint to a table

To actually connect two tables of data, you need to add a foreign key constraint to the table.

ALTER TABLE name add constraint fk_id foreign key (foreign key field name) references outer table name (primary key field name);

(1) The table that establishes the foreign key must be InnoDB type cannot be a temporary table. Because only InnoDB tables in MySQL support foreign keys.

(2) When you define a foreign key name, you cannot enclose it. such as: Constraint ' fk_id ' or constraint "fk_id" are all wrong.

The foreign key is created to ensure the integrity and uniformity of the data, that is, the primary table and the corresponding data from the table are consistent, you can add an on delete or an ON UPDATE clause when the foreign key is established.

ALTER TABLE name add constraint fk_id foreign key (foreign key field name) REFERENCES outer table name (primary key field name);

[on DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

[on UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

CASCADE: Delete all records that are included in the referenced relationship with the deleted key value

SET null: Modifies all records that contain a reference relationship to the deleted key value, replacing it with a null value

No action: Do nothing

RESTRICT: Deny primary key Delete or modify foreign Key association column.

Delete a FOREIGN KEY constraint

Remove a FOREIGN KEY constraint when you disassociate an association between two tables

ALTER TABLE name drop FOREIGN key foreign key name;

principle of multi-table establishment

Pair of more:

Create a field on the many side as the primary key for the foreign key that points to one side.

Many-to-many:

Create an intermediate table with at least two fields in the intermediate table, pointing to the primary key of many-to-many parties as a foreign key

One:

Unique foreign key correspondence, primary key corresponding

Operation Relationship Table

Association relationship

One-to-many (department and employee), many-to-many (teachers and students), one-to-one (identity card)

Add data

Delete data

Connection Query

Cross Connect

The result of the cross join is the product of the Cartesian product rows of all the data lines in the connected two tables.

SELECT * FROM table 1 cross JOIN table 2;

This kind of business requirement is very rare in the actual development, generally does not use the cross-linking, but uses the concrete condition to carry on the purposeful query to the data.

Internal connection

An inner join (INNER join) is also known as a simple or natural connection, and the inner join uses comparison operators to compare the data in two tables, listing the rows of data that match the join criteria, and combining them into a new record.

is a query that connects multiple tables into a single table. Only those columns that meet the query criteria appear in the final table

SELECT query field from table 1 [INNER] JOIN table 2 on table 1. Relationship field = Table 2. Relationship Fields

Implicit intra-connection

SELECT * from a b where condition

Show internal connections

SELECT * FROM A inner join B on condition

External connection

The results of the left join include all records for the left table specified in the RIGHT join clause, and all records that satisfy the join condition. If a record in the left table does not exist in the right table, it appears empty in the right table.

The right connection is exactly the opposite of left join, returning all the specified records in the right table and all records that meet the join criteria. If a record in the right table does not match in the left table, the left table returns a null value.

SELECT search field from table 1 left| Right [OUTER] JOIN table 2

On table 1. Relationship fields = Table 2. Relationship Field WHERE condition

Left outer connection

SELECT * FROM A LEFT outer join B on condition

Right outer connection

SELECT * FROM A right outer join B on condition

Self-connect

is a table connected to itself

Compound conditional join query (nested query)

A nested query can include a subquery with an in predicate, a subquery with a comparison operator, a subquery with an any or all predicate, and a subquery with a exists predicate.

In the process of connecting queries, the query results are more accurate by adding filtering criteria to limit the results.

Sub-query

Sub-query with in keyword

Subquery with exists keyword

Sub-query with any keyword

subquery with the ALL keyword

Subqueries with comparison operators

SQL Execution Order

1.from Table Name

2.where conditions

3.group by

4.having

5.select

6.order by

mysql< Multi-table operation >