MySQL basic multi-table operation

Multi-table operation

Cross-query (Cartesian product query):

#A表中的每一条记录都对应B表中的所有记录, so the number of cross-query entries for A and B is: A number of entries *b entries

Grammar:

Select column name from A Cross join B where condition

Select column name from A, a Where condition

Internal query

#A表与B表有主外键关系时, the primary key entry corresponding to the foreign key is queried

Grammar:

Select column name from a INNER join B on (A. Foreign key = B. primary key); #显式内查询

Select column name from A, a where condition; #隐式内查询

Outside query--left outer query

#左外查询即在左外查询关键字的左边的所有条目全部列出

Select column name from a LEFT join B on (A. Foreign key = B. primary key);

Outside query--right out of the query

#右外查询即在查询关键字的右边的所有条目全部列出

Select column name from a right join B on (A. Foreign key = B. primary key);

Federated queries

#使用union将两个select的结果想加 and merge the same results;

(SELECT statement 1) Union (SELECT statement 2);

Sub-query

#在一个select的查询语句中的where后面再跟上一个select查询语句

Select Column name from table name where field name on (SELECT statement);

Group queries

#使用group by will need to be grouped into the column, which can also keep up with having as a group after the query criteria

Select Column name from table name where condition group BY column name having condition

Aggregation functions

#使用函数进行一些必要的统计

Count () max () min () sum () AVG ()

MySQL basic multi-table operation