Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL multi-table query content in detail for everyone, the sample code introduced in the article is very detailed, the detailed explanation of graphics is easy to learn, very suitable for beginners to get started.

1. Use SELECT clause for multi-table query

SELECT Field Name FROM Table 1, Table 2 … WHERE Table 1. Field = Table 2. Field 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 association between two tables is established 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 implement it.

Second, using table aliases for multi-table queries

For 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, you can assign aliases to tables in two ways

The first is specified by 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

The second is the alias implementation of adding tables directly after table names

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

The use of table aliases should pay attention to a few points

(1)An alias is usually a shortened table name used to refer to a specific column in a table in a join. If columns with the same name exist in more than one table in a join, column names must be qualified by table names or table aliases

(2)If you define a table alias, you can no longer use a table name

III. Merge multiple result sets

SQL language, you can use UNION or ALL to combine the query results of multiple SELECT statements output, the use of these two keywords are described as follows:

UNION: Use this keyword to merge and output the query results of multiple SELECT statements and delete duplicate rows.

ALL: This keyword allows you to combine the query results of multiple SELECT statements, but does not delete duplicate rows.

When using UNION or ALL keywords to merge multiple tables, the query results must have the same structure and data types must be compatible. In addition, when using UNION, the number of fields in the two tables must also be the same, otherwise an error in the SQL statement will be prompted.

e.x:SELECT id,name,pwd FROM tb_demo067 UNION SELECT uid,price,date FROM tb_demo067_tel

IV. Simple nested queries

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. Subqueries can be used wherever expressions can be used.

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

Inner join: A query condition that uses the query result as a WHERE clause is called an inner join.

5. Complex nested queries

Nested queries between multiple tables can be implemented with predicate IN, with the following syntax:

test_expression[NOT] IN{

subquery

}

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

Principle of multi-table nested query: no matter how many tables are nested, there must be some association between tables, and this association is established through WHERE clause to realize query.

VI. Application of nested query in query statistics

When implementing multi-table queries, predicates ANY, SOME and ALL can be used simultaneously. These predicates are called quantitative comparison predicates and can be used in combination with comparison operators to determine whether all return values meet the search conditions.SOME and ANY predicates are quantifiable and only focus on whether return values meet the search conditions. These two predicates have the same meaning and can be used interchangeably. ALL predicates are called general predicates and only care about whether predicates meet 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 idANY is greater than a value in the subquery

>=ANY is greater than or equal to a value in the subquery

ALL is greater than all values in the subquery

>=ALL is greater than or equal to all values in the subquery

'95'

After reading the appeal content, do you master MySQL multi-table query method? Hope that the above content can have a certain reference value for everyone, if you want to know more, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.