Database SQL FOREIGN KEY constraint multi-table query

Multi-table design and multi-table query

1. Foreign KEY Constraints
Tables are used to save real-life data, and real-life data and data often have a certain relationship, we use the table to store data, we can explicitly declare the table and table before the dependency, command database to help us maintain this relationship, such constraints are called foreign KEY constraints.  Defining FOREIGN KEY constraints foreign key (column name of this table Ordersid) references orders (ID) (Reference and column names) CREATE tableDept(IDint primary key auto_increment, name varchar (20)); CREATE TABLE EMP (ID int primary key auto_increment, name varchar (20),dept_idIntforeign KEY (dept_id) ReferencesDept(ID)); 2. Multi-table design one-to-many: in many of the side of a party to save the primary key as a foreign key to a single: on either side to save the other party's primary key as foreign key many-to-many: Create a third-party relationship table holds two tables of primary keys as foreign keys, save their correspondence3. Multi-Table Query
3.1 Cartesian product query:The result of a multiplication of two table records is a Cartesian product query, if the left table has n records, the right table has m records, Cartesian product query has N*M records, which often contain a lot of wrong data, so this query method is not commonly usedSELECT * from Dept,emp;        3.2 Internal connection query: The query is the left and right table can find the corresponding record of record select * from dept,emp where dept.id = emp.dept_id;    SELECT * FROM dept INNER join EMP on dept.id=emp.dept_id; 3.3 External Connection QueryA. Left outer join query: Add a record on the left side of the table that has not the right table, based on the internal connectionSELECT * FROM dept LEFT join EMP on dept.id=emp.dept_id;        B. Right outer join query: Add the right table on the basis of the inner join and the left table does not have a record of select * FROM dept-join EMP on DEPT.ID=EMP.DEPT_ID; C. Full outer JOIN query: Add a record on the left side of the table that has not the right table, based on the internal connectionand theThe right table has a record of the left table without the SELECT * from dept full join EMP on dept.id=emp.dept_id; D. But MySQL does not support full-outer joins, but we can use the Union keyword to simulate a full outer join SELECT * FROM dept LEFT join EMP on dept.id = emp.dept_id UN Ion select * FROM dept right join EMP on dept.id = emp.dept_id;

Multi-table query instances

CREATE TABLE A (ID int primary key,job varchar);Insert into a values (1, ' AA '), (2, ' BB '), (3, ' CC '), and select * from A;CREATE TABLE B (id int, name varchar, foreign key (ID) references a (ID));
One-to-many: on the many side (b) The primary key of a party (a) is saved as a foreign key; many-to-many: Create a third-party relationship table to save two tables of the primary key as a foreign key, save their correspondence note: The referenced a (ID) must be defined as unique to be referenced, otherwise an error (logic Insert into B values (1, ' BQT '), (2, ' Bqt2 '), (2, ' bqt3 '); note: The ID of the inserted data must already exist in the referenced table, otherwise an error (logical fault). select * from B;SELECT * from a B;
The result of a multiplication of two table records is a Cartesian product query, if the left table has n records, the right table has m records, Cartesian product query has N*M records, which often contain a lot of wrong data, so this query method is not commonly used.SELECT * from a b where a.id = b.ID;
or select * from a INNER join B on a.id=b.id; Internal connection query: The query is the left table and the right table can find records of corresponding records.SELECT * from a LEFT join B on a.id=b.id;
Left OUTER JOIN query: Add a record on the left side of the table that has not the right table on the inner joinSELECT * from a RIGHT join B on a.id=b.id;
Right outer join query: Add a record on the right side of the table that has not the left table because the right table references the left table, so there is no record on the right table with the left table.SELECT * from a LEFT join B on a.id = b.ID
Unionselect * from a RIGHT join B on a.id = b.ID; Full outer JOIN query: Adds the left table on the basis of the inner join and the right table does not, and the right table has a record of the left table without the SELECT * from a full join B on a.id=b.id; --some database support, but MySQL does not support!

From for notes (Wiz)

Database SQL FOREIGN KEY constraint multi-table query