Use the SELECT clause to query multiple tables

SELECT name FROM table1The table2... WHERE the table1.Field = table2.Field AND other search conditionsCopy the code
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
Copy the code

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 in practical development should not be used in this way, it is better to use the primary foreign key constraint to achieve

Multiple table queries using table aliases

Such as:

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]'
Copy the code

In THE SQL language, you can specify an alias for a table in two ways

The first is specified by the keyword AS, for example

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
Copy the code

The second method is to implement the alias of the table directly after the name of the table

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 
Copy the code

There are a few things to note when using table aliases

  1. An alias is usually a shortened table name used to refer to a specific column in a table in a join. If the same named column exists in more than one table in a join, the column name must be qualified with the table name or table alias

  2. You cannot use the table name if you have defined an alias for the table

Merge multiple result sets

In SQL, you can use UNION or ALL to combine the query results of multiple SELECT statements. The usage of these two keywords is described as follows:

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

ALL: You can use this keyword to combine the query results of multiple SELECT statements without deleting duplicate rows

When the UNION or ALL keyword is used to combine the output of multiple tables, the query results must have the same structure and the data types must be compatible. In addition, the number of fields in the two tables must be the same when the UNION keyword is used. Otherwise, the SQL statement error message will be displayed.

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

Simple nested query

Subquery: A subquery is a SELECT query that returns a single value nested within SELECT, INSERT, UPDATE, and DELETE statements, or other query statements, and can be used anywhere expressions can be used.

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

Inner join: An inner join is a query condition that uses the result of a query as a WHERE clause

Complex nested query

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

test_expression[NOT] IN{

 subquery

}
Copy the code

Parameter description: Test_expression indicates an 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 tables, through the WHERE clause to establish such association to implement the query

Application of six nested query in query statistics

When implementing multi-table queries, you can use predicates ANY, SOME, and ALL together. These predicates are called quantitative comparison predicates and can be used in conjunction with comparison operators to determine whether ALL return values meet the search criteria. The “SOME” and “ANY” predicates are existential and only care if there is a value returned that satisfies the search criteria. The ALL predicate is called a universal predicate, and it only cares if there are any predicates that meet the search requirements.

SELECT * FROM tb_demo069_people WHERE uid IN(SELECT deptID FROM tb_demo069_dept WHERE deptName='$_POST[select]')
Copy the code
SELECT a.id,a.name FROM tb_demo067 AS a WHERE id<3)
Copy the code

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 equals some value in the subquery

! =ANY or <>ANY does not equal 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 equals ALL values in the subquery

! =ALL or <>ALL does not equal ALL values in the subquery

Table derived from subquery

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 perfect information. This goal can be achieved through sub-query, such as

SELECT people.name,people.chinese,people.math,people.english FROM (SELECT name,chinese,math,english FROM tb_demo071) AS people
Copy the code

Note: Subqueries should follow the following rules:

(1) The inner subquery introduced by the comparison operator contains only one expression or column name. The column named in the WHERE clause in the outer statement must be compatible with the column named in the inner subquery

(2) Subqueries introduced BY immutable comparison operators (which are not followed BY ANY or ALL keywords) do not include GROUP BY or HAVING clauses unless groups or individual values are predetermined

(3) SELECT lists introduced in EXISTS generally consist of * and do not have to specify column names

(4) Subqueries cannot process their results internally

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 assign individual names to table entries when using subqueries, so that users can easily assign values to table entries when using mysql_fetch_array(), as shown in the following example

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_demo071Copy the code

Use subqueries to associate data

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

More than 10 tables joint query

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

e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten
Copy the code

Note: The following points should be noted when using UNION:

(1) In the statement that uses 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 corresponding column data structure must be the same.

11. Sort the results of the combination

For operational compatibility with UNION, it is required that all SELECT statements cannot have an ORDER BY statement, with the exception of placing the ORDER BY clause in the last SELECT statement to achieve the final sorted output of the results.

e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten ORDER BY id
Copy the code

The UNION condition is relatively harsh, so it is important to note that the number of entries and field types are the same when using this statement

Twelve conditional union statements

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

The above statement applies the GROUP BY statement and the HAVING statement to implement the conditional union query. The purpose is to ensure that ‘Posts and Telecommunications Press’ and ‘China Machine Press’ are always at the top of the list, and then export other publishing houses

Simple inner join query

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

Where, filedList is the field to display,INNER indicates that the join mode between tables is INNER join, table1.column1=table2.column1 is used to specify the join condition between two tables, for example:

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
Copy the code

Complex internal join query

Complex inner join query is to add some query conditions on the basis of basic inner join query, 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]')
Copy the code

INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN INNER JOIN The ON statement joins the two tables to implement the query

Use external join to realize multi-table joint query

(1)LEFT OUTER JOIN means that the tables are joined by the LEFT JOIN method, which can also be shortened to LEFT JOIN. It is called LEFT JOIN because it is based on the LEFT table. All information in the LEFT table will be output, while the information in the right table will only output the information that meets the conditions, and the information that does not meet the conditions will be returned NULL

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
Copy the code

(2)RIGHT OUTER JOIN means that the tables are joined by RIGHT JOIN, which can also be shortened to RIGHT JOIN. It is called RIGHT JOIN because it is based on the RIGHT table. 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, and the information that does not meet the conditions will be returned NULL

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
Copy the code

Use the IN or NOTIN keyword to define the scope

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code BETWEEN '$_POST[text1]' AND '$_POST[text2]')
Copy the code

Use IN to specify a query within a range, or NOT IN to replace it if you want a query outside a range

Associative subquery introduced by IN

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = '$_POST[text]')
Copy the code

Using the HAVING statement to filter group data

The HAVING clause is used to specify search criteria for groups or aggregations. HAVING is usually used in conjunction with GROUP BY statements. If a SQL statement does not contain a GROUP BY clause, HAVING behaves like the WHERE clause.

e.x:SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > '95'
Copy the code