18 Ways to query MySQL multiple tables



A multiple-table query using the SELECT clause

SELECT field name from Table 1, table 2 ... WHERE table 1. field = Table 2. Fields and other query conditions
SELECT A.id,a.name,a.address,a.date,b.math,b.english,b.chinese from Tb_demo065_tel as b,tb_demo065 as a WHERE a.id=b.id
Note: In the above code, the ID field information of two tables is the same as the condition to establish two table association, but in the actual development should not be used, it is best to use the primary foreign key constraints to implement

Multi-table query using alias of table
Example: SELECT a.id,a.name,a.address,b.math,b.english,b.chinese from tb_demo065 A,tb_demo065_tel b WHERE a.id=b.id and b.id= ' $_post[textid] '
in the SQL language, you can specify an alias for a table in one of two ways
The first type is specified by the keyword as, as
SELECT A.id,a.name,a.address,b.math,b.english,b.chinese from tb_demo065 as a,tb_demo065_tel as B WHERE a.id=b.id
The second is an alias implementation that directly adds a table after the table name
SELECT A.id,a.name,a.address,b.math,b.english,b.chinese from tb_demo065 A,tb_demo065_tel b WHERE a.id=b.id
There are several points to note when using table aliases
(1) An alias is usually a shortened table name that is used to reference a specific column in a table in a connection, and if more than one table in the connection has the same name column, the column name must be qualified with the table name or the alias of the table
(2) If you define a table alias, you can no longer use the table name

III Merging multiple result sets

union: Use this keyword to combine the output of query results from multiple SELECT statements and remove duplicate rows
all: This keyword allows you to merge the results of multiple SELECT statements into the output, but does not delete duplicate rows
When multiple tables are combined output using the union or all keyword, the query results must have the same structure and the data type must be compatible. The number of fields in the two tables must also be the same when using union, otherwise the SQL statement will be prompted with an error.
e.x:SELECT ID, Name,pwd from tb_demo067 UNION select , uid,price,date from Tb_demo067_tel

Four simple nested queries
Subquery: A subquery is a select query that returns a single value and is nested in a SELECT, INSERT, update, and DELETE statement or other query statement, where you can use a subquery wherever you can use an expression.
Select Id,name,sex,date from tb_demo068 where ID in (SELECT ID from tb_demo068 where id= ' $_post[test] ')
Inner joins: The query condition that takes the query result as a WHERE clause is called an INNER join

Five complex nested queries
Nested queries between multiple tables can be implemented through predicate in, with the following syntax:
Test_expression[not] in{
subquery
}
Parameter description: test_expression refers to an SQL expression that subquery a subquery containing a result set
The principle of multi-table nested queries: No matter how many tables are nested, there must be some kind of association between the table and the table, which is established by the WHERE clause to implement the query

Application of six nested queries in query statistics
when implementing a multi-table query, you can use both predicate any, SOME, and all, which are called quantitative comparison predicates and can be used in conjunction with comparison operators to determine whether all return values satisfy the search criteria. The some and any predicates are present and only pay attention to whether a return value satisfies the search condition, and the two predicates have the same meaning and can be substituted for use; The all predicate is called a universal predicate, and it only cares if there are predicates that satisfy the search requirements.
SELECT * from Tb_demo069_people where UID in (SELECT deptid from tb_demo069_dept where Deptname= ' $_post[select] ')
SELECT A.id,a.name from tb_demo067 as a WHERE id<3)
>any is greater than a value in a subquery

>=any is greater than or equal to a value in a subquery
<=any is less than or equal to a value in a subquery
=any equals one of the values in the subquery
!=any or <>any is not equal to a value in a subquery
>all is larger than all values in the subquery

>=all is greater than or equal to all values in the subquery
<=all is less than or equal to all values in the subquery
=all equals all values in the subquery
!=all or <>all is not equal to all values in a subquery

Seven. Using subqueries as a derived table
in the actual project development process, it is often used to derive an information table containing only a few key fields from a well-developed table, which can be achieved by subqueries, such as
Select People.name,people.chinese,people.math,people.english from (select Name,chinese,math,english from Tb_ demo071) as People
Note: Subqueries should follow these rules:
(1) The inner subquery introduced by the comparison operator contains only one expression or column name, and the column named within the WHERE clause in the outer statement must be compatible with the column named inside the subquery
(2) subqueries introduced by non-changing comparison operators (the comparison operator does not follow the keyword any or all) does not include the group BY or HAVING clause unless a group or a single value is predetermined
(3) The select list introduced with exists is generally composed of *, without specifying the column name
(4) subqueries cannot process their results internally

Eight using a subquery as an expression
Select (select AVG (Chinese) from tb_demo071), (select AVG (中文版) from tb_demo071), (select AVG (math) from tb_demo071) From tb_demo071
Note: It is a good idea to take an individual name for a list item when using a subquery, which makes it easier for the user to assign a value to a table item when using the Mysql_fetch_array () function, such as
Select (select AVG (Chinese) from tb_demo071) as Yuwen, (select AVG (中文版) from tb_demo071) as Yingyu (select AVG (math) F ROM tb_demo071) as Shuxue from tb_demo071

Ix. associating data with a subquery
SELECT * from Tb_demo072_student where id= (SELECT ID from tb_demo072_class where className = ' $_post[text] ')

10 Multi-Table joint query
With the Union in the SQL statement, you can display the data information that meets the criteria in the different tables in the same column.
E.x:select * FROM Tb_demo074_student UNION SELECT * from Tb_demo074_fasten
Note: The following two points should be noted when using union:
(1) In a statement combined with the Union operator, the number of expressions for all picklist must be the same, such as column names, arithmetic expressions, and aggregate functions.
(2) In each query table, the data structure of the corresponding column must be the same.

X. The results of a pair of unions are sorted by

e.x:SELECT * from Tb_demo074_student UNION SELECT * from Tb_demo074_fasten the ORDER by ID
using the union condition is relatively harsh, so when using this statement be sure to note that the number of two table entries is the same as the field type

12-Piece Union statement
SELECT * from Tb_demo076_beijing GROUP by name have name= ' people posts and Telecommunications press ' OR name= ' mechanical industry Press ' UNION SELECT * from Tb_demo076_beij ING GROUP by name have name <> ' People's post and Telecommunications press ' and name <> ' Machinery Industry Reprint society ' ORDER by ID
The above statement applies the GROUP BY grouping statement and the having statement to implement the conditional union query. The goal is to ensure that the ' people post and Telecommunications press ' and ' machinery industry press ' are always at the forefront of the list, and then output other publishers

13 Simple Internal Connection query
SELECT filedlist from table1 [INNER] JOIN table2 on table1.column1 = Table2.column1
Where Filedlist is the field to be displayed, inner means that the connection between the tables is an inner join, table1.column1=table2.column1 is used to indicate the connection condition between the two tables, such as:
SELECT A.name,a.address,a.date,b.chinese,b.math,b.english from tb_demo065 as a INNER joins Tb_demo065_tel as B on a.id=b.id

14 Complex INNER JOIN query
The complex inner join query is based on a basic internal connection query with some additional query conditions, such as:
SELECT A.name,a.address,a.date,b.chinese,b.math,b.english from tb_demo065 as a INNER joins Tb_demo065_tel as B on a.id=b.id where b.id= (SELECT ID from tb_demo065 WHERE tb_demo065.name= ' $_post[text] ')
In summary, the nature of the association between tables and tables is that there are common data items or identical data items between the two tables, through the WHERE clause or the INNER join inner join ... The ON statement joins the two tables to implement the query.

X. Five use outer joins to implement multi-table union query

e.x:SELECT a.name , A.address,b.math,b.english from tb_demo065 as a left OUTER joins Tb_demo065_tel as B on a.id=b.id
(2) Right OUTER join means that the tables are connected to each other by means of connecting to each other, or they can be abbreviated It is based on the right side of the table as a reference to the right connection, all the information in the right table will be output, and the left table information will only output the information that meets the criteria, the non-conforming information returns null
e.x:select a.name,a.address,b.math,b.english from tb_demo065 as A right OUTER joins Tb_demo065_tel as B on a.id=b.id

16 scoping with in or notin keywords
E.x:select * from tb_demo083 where code in (SELECT code from tb_demo083 where code between ' $_post[text1] ' and ' $_post[text 2] ')
Use in to specify the scope of the query, if the request in a range outside the query can be used instead of it

17 associative subqueries introduced by in
E.x:select * from tb_demo083 where code in (SELECT code from tb_demo083 where code = ' $_post[text] ')

18 filtering grouped data with a having statement
The HAVING clause is used to specify the search criteria for a group or aggregation, which is usually used with the group BY statement, and if the SQL statement does not contain a GROUP BY clause, the having behavior is the same as the WHERE clause.
E.x:select Name,math from tb_demo083 GROUP by ID have math > ' 95 '

18 Ways to query MySQL multiple tables