MySQL multi-table query _ MySQL

I. use the SELECT clause to perform multi-table query the SELECT field name FROM Table 1, table 2WHERE Table 1 field Table 2 field AND other query conditions SELECTaid, aname, aaddress, adate, bmath, benglish, bchineseFROMtb_demo0 I. use the SELECT clause for multi-table queries

SELECT field name FROM Table 1, table 2... WHERE table 1. field = Table 2. Field AND other query conditions
SELECT. id,. name,. address,. date, B. math, B. english, B. chinese FROM tb_demo065_tel AS B, tb_demo065 AS a WHERE. id = B. id
Note: in the code above, two table associations are created based on the same id field information of the two tables, but they should not be used in actual development, it is best to use the primary and foreign key constraints for implementation.

2. use the table alias for multi-table query
For example, SELECT. id,. name,. address, B. math, B. english, B. chinese FROM tb_demo065 a, tb_demo065_tel B WHERE. id = B. id AND B. id = '$ _ POST [textid]'
In SQL, you can specify an alias for a table in two ways.
The first is specified by the keyword AS, such
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 to directly add the table alias 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
Note the following when using the table alias:
(1) an alias is usually a shortened table name used to reference a specific column in the table in the connection. if multiple tables in the connection have the same name column, the column name must be specified by the table name or table alias.
(2) if the table alias is defined, the table name cannot be used any more.

3. Merge multiple result sets
In SQL, the query results of multiple SELECT statements can be combined and output through UNION or ALL. the usage of these two keywords is described as follows:
UNION: this keyword can be used to merge and output 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 are not deleted.
When multiple tables are combined and output using the UNION or ALL keyword, the query results must have the same structure and the data type must be compatible, in addition, when UNION is used, the number of fields in the two tables must be the same; otherwise, an error occurs in the SQL statement.
E. x: SELECT id, name, pwd FROM tb_demo067 union select uid, price, date FROM tb_demo067_tel

IV. simple nested query
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: the query condition that uses the query result as the WHERE clause is called Inner join.

5. complex nested queries
Nested queries between multiple tables can be implemented through the predicate IN. the syntax format is as follows:
Test_expression [NOT] IN {
Subquery
}
Parameter description: test_expression refers to an SQL expression. subquery contains subqueries of a result set.
Principle of multi-table nested query: no matter how many tables are nested, there must be some association between the table and the table. this association is established through the WHERE clause to implement the query.

6. Application of nested query in query statistics
When performing 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 together with comparison operators to determine whether ALL return values meet the search conditions. SOME and ANY predicates exist in quantity, and only pay attention to whether the returned values meet the search conditions. These two predicates have the same meaning and can be replaced and used. ALL predicates are called general predicates, it only cares about whether a predicate meets 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 is greater 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 is equal to ALL values in the subquery.
! = ALL or <> ALL is not equal to ALL values in the subquery.

7. use subquery as a derived table
In the actual project development process, it is often used to generate an information table containing only several key fields from a well-equipped table. this goal can be achieved through subqueries, as shown in
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 layer subquery introduced by the comparison operator only contains one expression or column name. the columns named in the WHERE clause in the outer statement must be compatible with the columns named in the inner layer subquery.
(2) subqueries introduced BY unchangeable comparison operators (the comparison operator is not followed BY the keyword ANY or ALL) and do not include group by or HAVING clauses, unless a group or a single value is pre-determined
(3) the SELECT list introduced by EXISTS is generally composed of *, and the column name does not need to be specified.
(4) subqueries cannot process the results internally.

8. use 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: it is best to set an alias for the list item when using the subquery, so that you can assign values to the table item when using the mysql_fetch_array () function, as shown in
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. multi-table joint query
Using UNION in SQL statements, you can display the qualified data information in different tables in the same column.
E. x: SELECT * FROM tb_demo074_student union select * FROM tb_demo074_fasten
Note: pay attention to the following points when using UNION:
(1) in a statement using the UNION operator combination, the number of expressions in all selection lists 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.

11. sort the combined results
To make UNION operations compatible, all SELECT statements are not allowed to have order by statements, except in one case, that is, place the order by clause in the last SELECT statement to achieve the final sorting output of the result.
E. x: SELECT * FROM tb_demo074_student union select * FROM tb_demo074_fasten order by id
The use of the UNION condition is relatively harsh. Therefore, when using this statement, be sure to check whether the number of two table items and the field type are the same.

12. condition Union statement
SELECT * FROM tb_demo076_BEIJING group by name HAVING name = 'People's Post and Telecommunications Publishing House 'OR name = 'machinery Industry Publishing House' union select * FROM tb_demo076_BEIJING group by name HAVING name <> 'People's Post and Telecommunications Publishing House' AND name <> 'machinery industrial Review 'order by id
The preceding statement applies the group by and HAVING statements to implement joint condition query. The goal is to ensure that the 'People's Post and Telecommunications Publishers 'and 'machinery industry Publishers' 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
Here, filedlist is the field to be displayed. INNER indicates that the connection mode between tables is internal connection. table1.column1 = table2.column1 indicates the connection conditions between two tables, for example:
SELECT. name,. address,. date, B. chinese, B. math, B. english FROM tb_demo065 AS a inner join tb_demo065_tel AS B on. id = B. id

14. complex internal connection query
Complex internal connection queries are based on basic internal connection queries, and some query conditions are added, such:
SELECT. name,. address,. date, B. chinese, B. math, B. english FROM tb_demo065 AS a inner join tb_demo065_tel AS B on. id = B. id WHERE B. id = (SELECT id FROM tb_demo065 WHERE tb_demo065.name = '$ _ POST [text]')
In short, the essence of association between tables is that two tables have a common data item or the same data item, and join inner join through the WHERE clause or inner join... The ON statement connects two tables for query

15. multi-table joint query using outer join
(1) left outer join indicates that tables are connected by left join, which can also be abbreviated as left join. it is called left join based on the LEFT table, all information in the left table is output, while the right table information only outputs information that meets the conditions. if the information does not meet the conditions, NULL is returned.
E. x: 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 indicates that tables are connected through the right join method, which can also be abbreviated as right join. it is called right join based on the table on the RIGHT, all information in the right table is output, while the left table information only outputs information that meets the conditions. if the information does not meet the conditions, NULL is returned.
E. X: 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 keyword to limit the range
E. x: SELECT * FROM tb_demo083 WHERE code IN (SELECT code FROM tb_demo083 WHERE code BETWEEN '$ _ POST [text1]' AND '$ _ POST [text2]')
You can use IN to specify a query IN a range. if you want to query outside a range, you can use not in to replace it.

17. Association subquery introduced by IN
E. x: SELECT * FROM tb_demo083 WHERE code IN (SELECT code FROM tb_demo083 WHERE code = '$ _ POST [text]')

18. use HAVING statements to filter grouped data
HAVING clause is used to specify search conditions for a GROUP or aggregation. HAVING is usually used together with the group by statement. if the SQL statement does not include the GROUP BY clause, HAVING acts the same as the WHERE clause.
E. x: SELECT name, math FROM tb_demo083 group by id HAVING math> '95'