How to perform multi-table query in mysql

Mysql method for multi-table query: 1. Use the SELECT clause to query; 2. Use the alias of the table to query; 3. Query through inner joins; 4. Query through nesting; 5. Query through multiple table joins.

#How to perform multi-table query in MySQL? The following article will introduce to you some MySQL methods for multi-table query. I hope it will be helpful to you.

(Recommended tutorial: mysql video tutorial)

1. Use SELECT clause for multi-table query

SELECT 字段名 FROM 表1，表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件

Example :

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 association between the two tables is established based on the condition that the id field information of the two tables is the same, but it should not be used in this way in actual development. It is best to use primary and foreign key constraints. To achieve

2. Use table aliases for multi-table queries

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 SQL language, you can use two The method is to specify an alias for the table:

1. Specify it through the keyword AS, such 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

2. Add the alias of the table directly after the table name to achieve

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

A few points should be noted when using table aliases

●(1An alias is usually a shortened table name, used to refer to specific columns in the table in a connection. If there are multiple If a column with the same name exists in the table, the column name must be qualified with the table name or table alias
● If the table alias is defined, the table name cannot be used

3. Simple nesting Query (subquery, inner join)

Subquery: A subquery is a SELECT query that returns a single value and is nested in SELECT, INSERT, UPDATE and DELETE statements or other query statements. Any Subqueries can be used wherever expressions are used. Example:

SELECT id,name,sex,date FROM tb_demo068 WHERE id in(SELECT id FROM tb_demo068 WHERE id='$_POST[test]')

Inner join: Using the query result as the query condition of the WHERE clause is called an inner join

SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1

Among them, filedlist is the The displayed fields, INNER, indicate that the connection mode between tables is inner connection, table1.column1=table2.column1 is used to specify the connection conditions 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 JOIN tb_demo065_tel AS b on a.id=b.id

4. Complex embedding Nested query

Nested query between multiple tables can be realized through predicate IN. The syntax format is as follows:

test_expression[NOT] IN{
 subquery
}

Parameter description: test_expression refers to the SQL expression, subquery contains Subquery of a certain result set

The principle of multi-table nested query: No matter how many tables are nested, there must be some kind of correlation between tables. This correlation is established through the WHERE clause to implement the query.

5. Multi-table joint query

Using UNION in SQL statements, qualified data information in different tables can be displayed in the same column. Example:

SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

When using UNION, you should pay attention to the following two points:

● In statements combined using the UNION operator, the number of expressions in all selection lists must be the same, such as column names, arithmetic expressions, aggregate functions, etc.
●In each query table, the data structure of the corresponding columns must be the same.

6. Complex inner join query

Complex inner join query is based on the basic inner join query and adds some query conditions, such as:

SELECT a.name,a.address,a.date,b.chinese,b.math,b.english FROM tb_demo065 AS a INNER JOIN 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 short, the essence of realizing the association between tables is that there are common data items or the same data items between the two tables. The two tables are connected through the WHERE clause or the INNER JOIN...ON statement. Implement the query.

The above is the detailed content of How to perform multi-table query in mysql. For more information, please follow other related articles on the PHP Chinese website!