The use of views in MySQL and the sharing of skills of multi table INNER JOIN
- 2020-06-23 02:05:53
- OfStack
Create a view
Sql code
CREATE VIEW view_name AS
SELECT t1.xxx, t2.xxx, t3.xxx
FROM (table1 t1
INNER JOIN table2 t2 ON t1.fid = t2.fid)
INNER JOIN table3 t3 ON t1.mid = t3.mid;
Three table associations are used here, and there is a trick for writing INNER JOIN for multi-table associations
1. Write the simplest 2 table association INNER JOIN first
2. Then use () to expand from FROM to the end of the statement
3. Join INNER JOIN to the next table at the end of the statement
With this principle in mind, it will not be difficult to do 4-table association and 5-table association in the future
Delete the view
DROP VIEW view_name
Here's what others have added:
Multi-table joins are a useful 10-point technique because in some cases we need to query data across multiple tables.
Grammar format:
FROM ((Table 1 INNER JOIN table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN table 3 ON Table 1. Field number = Table 3.
Notes:
In the process of letter input, 1 must use English half - corner punctuation, between words leave 1 half - corner space;
When creating a data table, if a table joins with more than one table, the fields in that table must be a "numeric" data type, and the same fields in multiple tables must be a primary key and an "autoid" data type. Otherwise, it is difficult to connect successfully.
Rapid code nested method: such as, to connect five tables, as long as the connection before and after the four table code and add a bracket (with brackets behind the FROM before parentheses after added at the end of the code), then in the parenthesis continue to add "INNER JOIN table name X ON table 1. The field number = table X. Field" code, so that it can be infinite join tables.
Usage of joining two data tables:
FROM Table 1 INNER JOIN Table 2 ON Table 1
Usage of joining three data tables:
FROM (Table 1, INNER JOIN table 2, ON Table 1. Field number = Table 2. Field number
Usage of joining four data tables:
FROM (Table 1, INNER JOIN table 2, ON Table 3, ON Table 1, ON table 3, JOIN Table 4, ON Member
Usage of joining 5 data tables:
FROM ((Table 1 INNER JOIN table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN table 3 ON Table 1