MySQL Multi-table query

a multiple-table query using the SELECT clause Select field name from Table 1, table 2 ... WHERE table 1. Fields=table 2. Fields 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 table ID field information is the same as the criteria to establish two table association, but in the actual development should not be used, it is best to use the primary foreign key constraints to implement the two use table alias for multi-table query 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]'in the SQL language, there are two ways to specify aliases for a table the first is specified by the keyword as, such as the 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 implement the alias of the table directly after the table name implementation of 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 the alias of the table should be noted several points (1The alias is typically a shortened table name that is used to refer to a specific column in a table in a connection, and if more than one table in the connection has the same name column, the column name must be qualified with the table name or the alias of the table (2If you define a table alias, you can no longer use the table name to merge multiple result sets in the SQL language, the query results for multiple SELECT statements may be combined output by UNION or all, using the following instructions for the two keywords: UNION: Use this keyword to add multiple select The query result of the statement merges the output and removes the duplicate row all: This keyword allows you to combine the results of multiple SELECT statements, but not delete duplicate rows when you combine output from multiple tables using the union or all keyword, the query result must have the same structure and the data type must be compatible. The number of fields in the two tables must also be the same when using union, otherwise the SQL statement will be prompted with an error. E.x:select id,name,pwd from tb_demo067 UNION SELECT uid,price,date from Tb_demo067_tel four simple nested query subquery: subquery is a SELECT query, Returns a single value that is nested within a SELECT, INSERT, update, and DELETE statement or other query statement, where you can use a subquery wherever you can use an expression. SELECT id,name,sex,date from tb_demo068 WHERE IDinch(SELECT ID from tb_demo068 WHERE id='$_post[test]'Inner joins: The query result as a WHERE clause is called an INNER join five complex nested query nested queries between multiple tables can be implemented through predicate in, syntax format is as follows: Test_expression[not] in{subquery} parameter Description: Test _expression refers to the SQL expression, subquery contains a result set of subqueries multi-table nested query principle: No matter how many tables are nested, there must be some kind of association between the table and the table, Using the WHERE clause to establish this kind of association implementation query the application of six nested queries in query statistics when implementing a multi-table query, you can use both predicate any, SOME, all, which are called quantitative comparison predicates, and can be used in conjunction with comparison operators to determine whether all return values satisfy the search criteria. The some and any predicates are present and only pay attention to whether a return value satisfies the search condition, and the two predicates have the same meaning and can be substituted for use; The all predicate is called a universal predicate, and it only cares if there are predicates that satisfy 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 a subquery>=Any greater than or equal to a value in a subquery<=Any is less than or equal to a value in a subquery=Any equals a value in the subquery!=any or <>any is not equal to a value in a 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 is not equal to all values in the subquery seven. Tables derived using subqueries are often used in the actual project development process to derive a table of information that contains only a few key fields from a more sophisticated table, which can be achieved by subqueries, such as Select People.name, People.chinese,people.math,people.english from (SELECT name,chinese,math,english from tb_demo071) as people note: The subquery should follow these rules: (1The inner subquery introduced by the comparison operator contains only one expression or column name, and the column named within the WHERE clause in the outer statement must be compatible with the column named inside the subquery (2a subquery introduced by a non-changing comparison operator (the comparison operator is not followed by the keyword any or all) does not include the group BY or HAVING clause unless a group or a single value is predetermined (3The select list introduced with exists is generallywithout specifying a column name (4The subquery cannot process its results internally using a subquery as an expression select (select AVG (Chinese) from tb_demo071), (select AVG (中文版) from tb_demo071), ( Select AVG (math) from tb_demo071) from tb_demo071 Note: It's a good idea to take an individual name for a list item when using a subquery, which makes it easier for the user to assign a value to a table item when using the Mysql_fetch_array () function, such as Select (SELECT AVG (Chinese) from tb_demo071) As Yuwen, (select AVG (中文版) from tb_demo071) as Yingyu, (select AVG (math) from tb_demo071) as Shuxue from tb_demo071 IX using sub Querying associated data Select* FROM Tb_demo072_student where id= (SELECT ID from tb_demo072_class where className ='$_post[text]'10 Multi-table federated queries with the Union in the SQL statement, you can display data information that meets the criteria in the different tables in the same column. E.x:select* FROM Tb_demo074_student UNION SELECT *From Tb_demo074_fasten Note: The following two points should be noted when using union: (1In statements combined with the Union operator, the number of expressions for all picklist must be the same, such as column names, arithmetic expressions, and aggregate functions (2in each query table, the data structure of the corresponding column must be the same. 11 Sorting the results of a union in order to be compatible with the union operation, it is required that all SELECT statements cannot have an ORDER by statement, but in one case, the final sort output that puts the ORDER BY clause in the last SELECT statement to achieve the result. E.x:select* FROM Tb_demo074_student UNION SELECT *From Tb_demo074_fasten The ORDER by ID is relatively harsh with the union condition, so when using this statement, be sure to note that the number of two table entries and the field type are the same 12-piece UNION statement select* FROM Tb_demo076_beijing GROUP by name have name='People's Post and Telecommunications publishing house'OR name='Mechanical Industry Press'UNION SELECT * from tb_demo076_beijing GROUP by name have name <>'People's Post and Telecommunications publishing house'and name <>'Machinery Industry Reprint Society'the above statement of the ORDER by ID applies the group BY grouping statement and the having statement to implement the conditional union query. The purpose of the implementation is to ensure that the'People's Post and Telecommunications publishing house'And'Mechanical Industry Press'always at the forefront of the list and then output other publishers 13 Simple Inner Connection query select Filedlist from table1 [INNER] JOIN table2 on Table1.column1=Table2.column1 where filedlist is the field to be displayed, inner means that the connection between tables is an inner connection, table1.column1=table2.column1 is used to indicate connection conditions between 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 a complex INNER join query is based on a basic internal connection query with additional query conditions, such as: SELECT a.name,a.address,a.date,b.chinese,b.math,b.english From tb_demo065 as a INNER joins 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 Summary, the nature of the association between tables and tables is that there are common data items or identical data items between the two tables, through the WHERE clause or the INNER join inner join ... The ON statement joins the two tables, implementing a query that uses outer joins to implement a multi-table union query (1The left OUTER join indicates that the table is connected to each other by means of a connection between the two sides, or it can be abbreviated to a LEFT join, which is the table on the left as the datum, so it is said to leave, all the information in the left table will be output, and the right table information will only output the information that matches the condition. For non-conforming information return Nulle.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 (2The right OUTER join indicates that the tables are connected to each other by the way they are connected, or they can be abbreviated to the right join, which is based on the table on the right, so that all the information in the right table will be output, and the left table information will only output the information that matches the condition. For non-conforming information return Nulle.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]'use in to specify the query within the scope, if the request is in a range outside the query can be used instead of its 17 in the association subquery introduced by in E.x:select* FROM tb_demo083 where code in (SELECT code from tb_demo083 where code ='$_post[text]'18 using a having statement to filter grouped data The HAVING clause is used to specify the search criteria for a group or aggregate, and the having is usually used with the group BY statement if the SQL statement does not contain a GROUP BY clause. The having behavior is the same as the WHERE clause. E.x:select Name,math from tb_demo083 GROUP by ID have math>' the'

MySQL Multi-table query