MySQL Foundation-database Multi-table query-record (vi)

0x01

MySQL multi-table query and sub-query

Join query: Two or more tables joined in advance, based on the results of the join query

Cross join: Less intersection, equivalent to (a+b) * (c+d+e) of the resulting form with less

Natural Coupling---can only be connected if they are equal
Equivalent junction: Connect the same field to the equivalent

Outer coupling:
Left OUTER join: Only the tuples that appear in the relationship before the left outer join element (left) are retained (whichever is left table)
Left_tb left JOIN RIGHT_TB on condition
Right outer join: only tuples in the relationship that appear after the right outer join element (right) are preserved (whichever is the right table)
LEFT_TB right JOIN right_tb on condition
Full outer joins: less ---

Self-coupling:

Alias: As
Table Aliases:
Field aliases

Subqueries: Queries nested in queries------for subqueries in where
1. To compare subqueries in an expression
The return value of a subquery can have only one
2. Subqueries used in exists
Judging the existence or not
3. Subqueries for in
The judgment exists in the specified list
For the From Neutron query
Select Alias.col,.... From (SELECT Cluse) alias WHERE condition

MySQL is not good at subqueries, you should avoid using sub-queries

Summary: MySQL connection query and sub-query
Join:
Cross join:
Inner coupling:
Outer coupling:
Left outer
Right outside
Self-coupling
Sub-query:
Subqueries used in where
For condition comparison:
For exists
For in
For the From

MySQL's federated query:
Combine the results of two or more query statements into one result for output
Select Clauase UNION SELECT Clause union .....
Index
Show Indexs from Tb_name View index

show indexes from Students\g;

ALTER TABLE Tb_name Add index (field)---change to index
Create an index

Explain interpret the command to see the execution of the command

MySQL view (virtual table)
The SELECT statement that is stored
Create:
CREATE VIEW name as SELECT statement

CREATE VIEW stud as select Name,age,gender from students;
Delete:
Drop View Name

MySQL Foundation-database Multi-table query-record (vi)