MySQL multi-table, MySQL

MySQL multi-table, MySQL

I. Foreign keys
1. Foreign key: link the fields of the two tables and describe the relationship between the primary key and the foreign key of the slave table. The relationship is one-to-many. For example, for a commodity category (1), for a commodity (multiple items), the main table is the commodity category table, from the table to the commodity table.
2. Foreign key features: the foreign key value of the slave table is a reference to the primary key of the master table. The foreign key type of the slave table must be consistent with the primary key type of the master table.
3. Declare foreign key constraints
Syntax: alter table (slave table) add [constraint] (foreign key name) foreign key (foreign key field name) references primary table (primary key of the master table)
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 the primary table );
(2). alter table product add foreign key (cid) REFERENCES category (cid );
[Foreign key name] is used to delete foreign key constraints. It is generally recommended to end with "_ fk ".
Alter table drop the foreign key name of the foreign key from the table;
3. Foreign keys are used to ensure data integrity.
4. Note: you cannot add records that do not exist in the primary table or delete referenced records in the primary table from the foreign key.
2. Relationship between tables
The relationship between tables is the relationship between tables and table data.
1. One-to-multiple relationship:
Common examples: customers and orders, categories and products, departments and employees
One-to-multiple table creation principle: create a field from the table (multiple parties) and use the field as the foreign key to point to the primary key of the main table (one party ).
Syntax: alter table product add foreign key (cid) REFERENCES category (cid );
2. many-to-many relationship:
Common examples: Students and courses, goods and orders, people and Roles
Multi-to-Multi-Table creation principle: the third table must be created. The intermediate table must have at least two fields, these two fields respectively serve as the foreign key pointing to the primary key of the respective Party (that is, one many-to-many split into two one-to-many)
Both tables are master tables, and the third table is slave table. Two fields are provided, both of which are foreign keys.
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: (understanding)
There are not many applications in actual development, because one-to-one tables can be created
Common examples: Company and address
Two principles: the foreign key is unique and the foreign key is the primary key.
Iii. Operations
1. Multi-Table query
(1). Cross join query (get the product of two tables, basically not used)
Select * from A, B;
(2). inner join query (Keyword: inner join -- inner can be omitted)
Implicit inner join: select * from A, B where condition;
Example: select * from category c, product p where c. cid = p. cid;
Explicit inner join: select * from A inner join B on condition;
Example: select * from category as c inner join product as p on p. cid = c. cid;
(3). outer join query (the keyword outer join -- outer can be omitted)
Left outer join: left outer join
Select * from A left outer join B on condition;
Example: select * from category c left join product p on p. cid = c. cid;
Outer right join: right outer join
Select * from A right outer join B on condition;
Example: select * from category c right join product p on p. cid = c. cid;
Differences between inner connection and outer connection:
Left Outer Join: query the intersection of all left tables and two tables
Inner join: queries the intersection of two tables.
Outer right join: queries the intersection of all the right tables and the two tables.
2. subquery
A select statement result is part of another select syntax (query conditions, query results, tables, etc)
For example, you can query the details of cosmetics classified shelving items.
Select * from product where cid = (select cid from category where cname = 'cosmetic ');