Specific example of MySQL multi-table query

1. Use the SELECT clause to query multiple tables

SELECT field name FROM table1, table2 ... WHERE table1.field = table2.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 two tables are associated with the same id field information as the condition, but this should not be used in actual development. It is best to use primary and foreign key constraints to achieve this.

2. Use table aliases for multi-table queries

like:

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 specify an alias for a table in two ways
The first is to 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

The second method is to add the table alias directly 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 

A few points to note when using table aliases

• An alias is usually a shortened table name used to reference a specific column in a table during a connection. If columns with the same name exist in multiple tables in a connection, the column name must be qualified with the table name or table alias.
• If a table alias is defined, the table name cannot be used again

3. Merge multiple result sets

In SQL language, you can combine the query results of multiple SELECT statements through UNION or ALL. The instructions for using these two keywords are as follows:

UNION: This keyword can be used to combine the query results of multiple SELECT statements and delete duplicate rows.

ALL: This keyword can be used to merge the query results of multiple SELECT statements, but duplicate rows will not be deleted.

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

ex: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 SELECT, INSERT, UPDATE, and DELETE statements or other query statements. Subqueries can be used anywhere an expression 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: Using the query result as the query condition of the WHERE clause is called inner join

5. Complex nested queries

Nested queries between multiple tables can be implemented using the IN predicate. The syntax is as follows:

test_expression[NOT] IN {

 subquery

}

Parameter description: test_expression refers to an SQL expression, subquery contains a subquery of a result set. The principle of multi-table nested query: no matter how many tables are nested, there must be some kind of association between the tables, and this association is established through the WHERE clause to implement the query.

6. Application of nested query in query statistics

When implementing multi-table queries, you can use the predicates ANY, SOME, and ALL at the same time. These predicates are called quantitative comparison predicates and can be used in conjunction with comparison operators to determine whether all returned values ​​meet the search conditions. The SOME and ANY predicates are existential predicates and only focus on whether the returned values ​​meet the search conditions. The two predicates have the same meaning and can be used interchangeably; the ALL predicate is called a general predicate and only cares about whether there are predicates that 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 id<3)

• ANY is greater than a value in the subquery
• =ANY is greater than or equal to a value in the subquery
• <=ANY is less than or equal to a value in the subquery
• =ANY is equal to a value in the subquery
• !=ANY or <>ANY is not equal to a value in the subquery
• ALL Greater than all values ​​in the subquery
• =ALL Greater than or equal to all values ​​in the subquery
• <=ALL 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 the subquery
  

7. Using subqueries as derived tables

In the actual project development process, it is often used to derive an information table containing only a few key fields from a table with relatively complete information. This goal can be achieved through 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 the following rules:

(1) The inner subquery introduced by the comparison operator contains only one expression or column name. The columns named in the WHERE clause of the outer statement must be compatible with the columns named in the inner subquery.
(2) A subquery introduced by an immutable comparison operator (a comparison operator not followed by the keywords ANY or ALL) does not include a GROUP BY or HAVING clause unless the grouped or individual values ​​are predetermined.
(3) The SELECT list introduced by EXISTS usually consists of *, and there is no need to specify the column name
(4) A subquery cannot process its results internally

8. Using subqueries as expressions

SELECT (SELECT AVG(chinese)FROM tb_demo071),(SELECT AVG(english)FROM tb_demo071),(SELECT AVG(math)FROM tb_demo071) FROM tb_demo071

Note: When using a subquery, it is best to give the list items an alias, which makes it easier for users to assign values ​​to table items when using the mysql_fetch_array() function, such as

SELECT (SELECT AVG(chinese) FROM tb_demo071) AS yuwen,(SELECT AVG(english) FROM tb_demo071) AS yingyu,(SELECT AVG(math) FROM tb_demo071) AS shuxue FROM tb_demo071

9. Use subqueries to associate data

SELECT * FROM tb_demo072_student WHERE id=(SELECT id FROM tb_demo072_class WHERE className = '$_POST[text]')

10. Multiple table joint query

By using UNION in SQL statements, data information that meets the conditions in different tables can be displayed in the same column.

ex:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

Note: When using UNION, you should pay attention to the following two points:
(1) In statements combined using the UNION operator, the number of expressions in all select lists must be the same, such as column names, arithmetic expressions, and aggregate functions.
(2) In each query table, the data structure of corresponding columns must be the same.

11. Sort the combined results

For UNION operation compatibility, all SELECT statements must not have an ORDER BY statement, but there is one exception, that is, placing an ORDER BY clause in the last SELECT statement to achieve the final sorted output of the results.

ex:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten ORDER BY id

The conditions for using UNION are relatively strict, so when using this statement, you must pay attention to whether the number of items and field types of the two tables are the same.

12 Conditional Join Statements

SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name='Posts and Telecommunications Press' OR name='Machinery Industry Press' UNION SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name <>'Posts and Telecommunications Press' AND name <>'Machinery Industry Reprint Press' ORDER BY id

The above statement uses the GROUP BY statement and the HAVING statement to implement conditional joint query. The purpose is to ensure that "People's Posts and Telecommunications Press" and "Machinery Industry Press" are always at the top of the list, and then export other publishing houses.

13. Simple inner join query

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

Among them, filedlist is the field to be displayed, INNER means the connection between the tables is inner connection, 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 JOIN tb_demo065_tel AS b on a.id=b.id

14 Complex inner join query

Complex inner join queries are based on basic inner join queries and add 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 identical data items between the two tables. The two tables are connected through the WHERE clause or the INNER JOIN ... ON statement to realize the query.

15. Use outer joins to implement multi-table joint queries

(1) LEFT OUTER JOIN means that the tables are connected to each other through a left join, which can also be abbreviated as LEFT JOIN. It is based on the table on the left, so it is called a left join. All information in the left table will be output, while only information in the right table that meets the conditions will be output. NULL will be returned for information that does not meet the conditions.
ex:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A LEFT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

(2) RIGHT OUTER JOIN means that the tables are connected to each other through right join, which can also be abbreviated as RIGHT JOIN. It is based on the table on the right, so it is called right join. All information in the right table will be output, while the information in the left table will only output the information that meets the conditions. NULL will be returned for information that does not meet the conditions.
EX:SELECT a.name,a.address,b.math,b.english FROM tb_demo065 AS A RIGHT OUTER JOIN tb_demo065_tel AS b ON a.id=b.id

16. Use the IN or NOTIN keywords to limit the range

ex:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code BETWEEN '$_POST[text1]' AND '$_POST[text2]')

IN can be used to specify a query within a range. If you need to query outside a certain range, you can use NOT IN instead.

17. Correlated subqueries introduced by IN

ex:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = '$_POST[text]')

18. Using the HAVING statement to filter grouped data

The HAVING clause is used to specify search conditions for groups or aggregates. HAVING is usually used with the GROUP BY clause. If the SQL statement does not contain a GROUP BY clause, HAVING behaves the same as the WHERE clause.

ex:SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > '95'

This is the end of this article about the specific examples of MySQL multi-table query. For more relevant MySQL multi-table query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!