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

This article mainly introduces the method of multi-table query by MySQL, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

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 example:

SELECT a.iddepartment a.namerecovera.addressrecovera.daterecoveryb.mathrecoveryb.english.b.chinese FROM tb_demo065_tel AS bgramdemo065 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 actual development, and it is best to use the primary foreign key constraint to achieve it.

Second, use the alias of the table for multi-table query

Example:

In the SELECT a.idrea.namerea.addressrecoverb.mathreb. Englishrewb.chinese FROM tb_demo065 a relegated tbaked demo065 domains tel b WHERE a.id=b.id AND b.idwritten tables _ post [textid] 'SQL language, you can assign aliases to a table in two ways:

1. Specify by keyword AS, such as

SELECT a. ID FROM tb_demo065 AS a. Name. Address. B. mathre.englishreib. Eng lishreb. Eng FROM tb_demo065 AS a dint tbaud demo065 demotel AS b WHERE a.id=b.id2, the alias implementation of adding the table directly after the table name

SELECT a. ID FROM tb_demo065 a. Name WHERE a.id=b.id a. Address. Mathre.englishreib.englishreb. Eng Enlish.Eng.

● (1 alias is usually a shortened table name that refers to a specific column in a table in a join. If the same name column exists in multiple tables in the join, the column name must be qualified with the table name or table alias.

If ● defines an alias for a table, you can no longer use the table name

Simple nested queries (subqueries, inner joins)

Subquery: a subquery is an 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. Example:

SELECT id,name,sex,date FROM tb_demo068 WHERE id in (SELECT id FROM tb_demo068 WHERE id='$_ post [test]') inner join: the query condition in which the query result is used as the WHERE clause is called inner join.

SELECT filedlist FROM table1 [INNER] JOIN table2 ON table1.column1=table2.column1, where filedlist is the field to be displayed, INNER indicates that the connection between tables is inner join, and table1.column1=table2.column1 is used to indicate the connection conditions between two tables, such as:

SELECT a.namememery a.addressrea.datere.chinese reportb.mathrem b.english FROM tb_demo065 AS an INNER JOIN tb_demo065_tel AS b on a.id=b.id IV, complex nested queries

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

Test_ expression [not] IN {subquery} parameter description: test_expression refers to SQL expression, and 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 association between the table and the table. The query can be realized by establishing this association through the WHERE clause.

Fifth, multi-table joint query

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

SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten should be aware of the following two points when using UNION:

● in statements that use UNION operators, all select lists must have the same number of expressions, such as column names, arithmetic expressions, aggregate functions, etc.

● the data structure of the corresponding column must be the same in each query table.

VI. Complex inner join query

Complex inner join queries attach some query conditions to the basic inner join queries, such as:

In short, the essence of realizing the association between a table and a table is that there are common data items or the same data items between the two tables, INNER JOIN through the WHERE clause or inner join INNER JOIN. The ON statement joins the two tables to implement the query.

Thank you for reading this article carefully. I hope the editor will share the content of MySQL's method of multi-table query to help everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.