MySQL Multi-table

First, foreign key
1. Foreign key: A field that links two tables, describes the primary foreign key relationship through the primary key of the primary table and the foreign key from the table, and presents a one-to-many relationship. For example: commodity category (i) on commodity (multi), Main Table: Commodity category table, from table: Commodity table.
2. Foreign key Features: The value from the foreign key is a reference to the primary table primary key, and the foreign key type from the table must be the same as the primary table primary key type
3. Declaring a FOREIGN KEY constraint
Syntax: ALTER TABLE (from table) add [constraint] (foreign key name) foreign key (from table foreign key field name) references primary table (primary key of primary table)
For example: (1). ALTER table product (from table) ADD CONSTRAINT CID (foreign key name) FOREIGN key (CID) (From Table foreign key field name) REFERENCES category (Main Table) (CID) (primary key of Main table);
(2). ALTER TABLE Product ADD FOREIGN KEY (CID) REFERENCES category (CID);
[Foreign key name] is used to delete foreign key constraints, the general recommendation "_FK" end
ALTER TABLE drop FOREIGN key foreign key name from table;
3. Purpose of using foreign keys: ensure data integrity
4. Note: You cannot add a record from a table that does not exist in the primary table, the primary table cannot delete records that have been referenced from the table
Ii. the relationship between table and table
The relationship between a table and a table is the relationship between the table and the table data.
1. One-to-many relationships:
Common examples: Customers and orders, classifications and goods, departments and employees
One-to-many table principle: Create a field from a table (multiparty), and the field as a foreign key to the primary key of the primary table (party)
Syntax: ALTER TABLE product ADD FOREIGN KEY (CID) REFERENCES category (CID);
2. Many-to-many relationships:
Common examples: Students and courses, goods and orders, people and roles
Many-to-many table principles: the third table needs to be created, with at least two fields in the intermediate table, each of which points to the primary key of the respective party as a foreign key (that is, a many-to-many split into two one-to-many)
Both tables are the main table, and the third table, which provides two fields from the table, is a foreign key.
Syntax: ALTER TABLE from table (stu_course) Add foreign key (SNO) references Stu (SID);
ALTER TABLE from table (stu_course) Add foreign key (CNO) References course (CID);
3. One-to-one relationship: (learn)
Not many applications in real-world development, because one-to-one can be created as a table
Common examples: Company and address
Two principles: foreign key is unique, foreign key is primary key
Third, the operation
1. Multi-Table Query
(1). Cross join query (two tables are multiplied, not used)
SELECT * from a B;
(2). Internal connection query (using keyword: INNER join--inner can be omitted)
Implicit inner joins: SELECT * from a a Where condition;
For example: SELECT * from category C,product p where c.cid=p.cid;
Explicit inner joins: SELECT * from A inner join B on condition;
For example: SELECT * from category as C inner join product as p on p.cid=c.cid;
(3). Outer JOIN query (Use keyword outer join--outer can be omitted)
Left outer connection: outer JOIN
SELECT * FROM A LEFT outer join B on condition;
For example: SELECT * from category C left join product p on P.cid=c.cid;
Right outer connection: outer JOIN
SELECT * FROM A right outer join B on condition;
For example: SELECT * from category C right join product p on P.cid=c.cid;
The difference between inner and outer joins:
Left OUTER join: Query the intersection of all and two tables in the left table
INNER JOIN: Query for the intersection of two tables
Right outer join: Query the intersection of all and two tables in the right table
2. Sub-query
One SELECT statement result as part of another select syntax (query criteria, query results, tables, etc.)
For example: Search cosmetics category listing product Details
SELECT * FROM product WHERE cid = (select CID from category where cname= ' cosmetics ');

MySQL multi-table