Fancy understanding---MySQL multi-table query-Mysql Tutorial-php.cn

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

Copy after login

Note: In the above code, the condition is that the id field information of the two tables is the same Establish an association between two tables, but this should not be used in actual development. It is best to use primary and foreign key constraints to achieve

2. Use 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=&#39;$_POST[textid]&#39;

Copy after login

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

Copy after login

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

Copy after login

You should pay attention to a few points when using the alias of the table
(1)The alias is usually a shortened table name, used for When referencing specific columns in a table in a connection, if columns with the same name exist in multiple tables in the connection, the column name must be qualified with the table name or table alias
(2) If the table alias is defined, it cannot Then use the table name

3. Merge multiple result sets
In SQL language, the query results of multiple SELECT statements can be combined and output through UNION or ALL. This The instructions for using the two keywords are as follows:
UNION: Use this keyword to combine the query results of multiple SELECT statements and delete duplicate rows.
ALL: Use this keyword to combine the results of multiple SELECT statements. The query results are merged and output, but duplicate rows will not be deleted
When using the UNION or ALL keyword to merge and output multiple tables, the query results must have the same structure and the data types must be compatible. In addition, when using UNION, the two tables The number of fields must also be the same, otherwise an error will be prompted in the SQL statement.

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

Copy after login

4. 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 In or other query statements, subqueries 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=&#39;$_POST[test]&#39;)

Copy after login

Inner join: Using the query results as the query conditions of the WHERE clause is called an inner join

5. Complex nested queries
Nested queries between multiple tables can be implemented through predicate IN. The syntax format is as follows:
test_expression[NOT] IN{<br/> subquery<br/>}
Parameter description: test_expression refers to the SQL expression, subquery contains the subquery of a certain result set
The principle of multi-table nested query: no matter how many tables are used Nesting, there must be some kind of association between 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 return values satisfy the search conditions. The SOME and ANY predicates are existential quantities. , it only focuses on whether there is a return value that meets the search conditions. The two predicates have the same meaning and can be used interchangeably; the ALL predicate is called a universal predicate, and it only cares about whether there is a predicate that meets the search requirements.

SELECT * FROM tb_demo069_people WHERE uid IN(SELECT deptID FROM tb_demo069_dept WHERE deptName=&#39;$_POST[select]&#39;)
SELECT a.id,a.name FROM tb_demo067 AS a WHERE id<3)

Copy after login

>ANY is greater than a certain value in the subquery
>=ANY is greater than or equal to a certain value in the subquery
<=ANY is less than or equal to a certain 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 the values in the subquery
<=ALL is less than or equal to all the values in the subquery
=ALL is equal to all the 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 obtain more complete information from one An information table containing only a few key fields is derived from the table. 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

Copy after login

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 in the outer statement must be compatible with the columns named by the inner subquery
(2 ) A subquery introduced by an immutable comparison operator (a comparison operator not followed by the keyword ANY or ALL) does not include a GROUP BY or HAVING clause unless the group or individual values are predetermined
(3) The SELECT list introduced with EXISTS generally consists of *, and there is no need to specify column names.
(4) Subqueries cannot process their 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

Copy after login

Note: It is best to give the list items an alias when using a subquery. This will make it easier for users to assign values to the 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

Copy after login

9. Use subqueries to associate data

SELECT * FROM tb_demo072_student WHERE id=(SELECT id FROM tb_demo072_class WHERE className = &#39;$_POST[text]&#39;)

Copy after login

10. Multi-table joint queries
Use UNION in SQL statements , you can display qualified data information in different tables in the same column.

e.x:SELECT * FROM tb_demo074_student UNION SELECT * FROM tb_demo074_fasten

Copy after login

注:使用UNION时应注意以下两点：
(1)在使用UNION运算符组合的语句中，所有选择列表的表达式数目必须相同，如列名、算术表达式及聚合函数等
(2)在每个查询表中，对应列的数据结构必须一样。

十一、对联合后的结果进行排序
为了UNION的运算兼容，要求所有SELECT语句都不能有ORDER BY语句，但有一种情况例外，那就是在最后一个SELECT语句中放置ORDER BY 子句实现结果的最终排序输出。

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

Copy after login

使用UNION条件上相对比较苛刻，所以使用此语句时一定要注意两个表项数目和字段类型是否相同

十二、条件联合语句

SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name=&#39;人民邮电出版社&#39; OR name=&#39;机械工业出版社&#39; UNION SELECT * FROM tb_demo076_BEIJING GROUP BY name HAVING name <>&#39;人民邮电出版社&#39; AND name <>&#39;机械工业再版社&#39; ORDER BY id

Copy after login

上面语句应用了GROUP BY分组语句和HAVING语句实现条件联合查询。其实现目的是先保证将'人民邮电出版社'和'机械工业出版社'始终位于名单最前列，然后再输出其它的出版社

十三、简单内连接查询

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

Copy after login

其中，filedlist是要显示的字段,INNER表示表之间的连接方式为内连接，table1.column1=table2.column1用于指明两表间的连接条件，如:

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 after login

十四、复杂内连接查询
复杂的内连接查询是在基本的内连接查询的基础上再附加一些查询条件，如:

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=&#39;$_POST[text]&#39;)

Copy after login

总之，实现表与表之间的关联的本质是两表之间存在共同的数据项或者相同的数据项，通过WHERE 子句或内连接INNER JOIN … ON 语句将两表连接起来，实现查询

十五、使用外连接实现多表联合查询
(1)LEFT OUTER JOIN表示表之间通过左连接方式相互连接，也可简写成LEFT JOIN,它是以左侧的表为基准故称左连接，左侧表中所有信息将被全部输出，而右侧表信息则只会输出符合条件的信息，对不符合条件的信息则返回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 after login

(2)RIGHT OUTER JOIN表示表之间通过右连接方式相互连接，也可简写成RIGHT JOIN,它是以右侧的表为基准故称右连接，右侧表中所有信息将被全部输出，而左侧表信息则只会输出符合条件的信息，对不符合条件的信息则返回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 after login

十六、利用IN或NOTIN关键字限定范围

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code BETWEEN &#39;$_POST[text1]&#39; AND &#39;$_POST[text2]&#39;)

Copy after login

利用IN可指定在范围内查询，若要求在某范围外查询可以用NOT IN代替它

十七、由IN引入的关联子查询

e.x:SELECT * FROM tb_demo083 WHERE code IN(SELECT code FROM tb_demo083 WHERE code = &#39;$_POST[text]&#39;)

Copy after login

十八、利用HAVING语句过滤分组数据
HAVING子句用于指定组或聚合的搜索条件，HAVING通常与GROUP BY 语句一起使用，如果SQL语句中不含GROUP BY子句，则HAVING的行为与WHERE子句一样.

e.x:SELECT name,math FROM tb_demo083 GROUP BY id HAVING math > &#39;95&#39;

Copy after login

推荐学习：mysql教程

The above is the detailed content of Fancy understanding---MySQL multi-table query. For more information, please follow other related articles on the PHP Chinese website!