MySQL keyword explanation (join, order BY, group BY, have, distinct)

1. Join
1.1 OUTER JOIN: You want to include all rows from the right table and rows with matching records in the table on the left.
1.11 MySQL has a LEFT join:
SELECT * from a left JOIN B on A.aid =b.bid
The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.
1.12 Right Join:
SELECT * from a right joing b on a.aid = B.bid
The result of the left join is exactly the opposite, this time is based on the right table (B), where a is not sufficient to fill with null
1.2 Internal connection (inner join or cross join):
SELECT * from a b WHERE a.aid = B.bid
Only records that match on the fields that are joined are returned. INNER join is done in the exclusion.
Self-reference is a way to join a table back to itself for some reason.
INNER join is the default join method.
1.3 Full join: To include all rows in the table on either side of the join.
1.4 Cross join: There is no on join, and each record in the Join side table is joined to all records in the table on the other side. That is, the Cartesian product in the join table.
Cross join can be used to provide sample data and scientific data

2. ORDER by
The return result of a query is usually given in alphabetical or numerical order, which is accidental. How it is given, in the absence of a specified case, usually depends on how SQL Server considers the aggregated data to be the least expensive. Therefore, the returned results are usually based on the physical order of the data in the table or an index used by SQL Server to find the data.
The default is ascending ASC, and descending is desc.
If the order is required, it is recommended to explicitly indicate it in the SQL statement.
The ORDER BY clause can be sorted based on any field in any table used in the query, regardless of whether the column is included in the select list.

3. GROUP by
Once each column in the group By,select list is used in a query statement, it is either included in the group by list or included in the aggregation.
When aggregation is not used with group by, the aggregation can only be in the select list with other aggregates, not with the column name in the select list.
In addition to count (*), any aggregation function ignores null values.
SELECT *, COUNT (id) from ' organisms ' WHERE 1 GROUP by user_id

4. having
The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.
The HAVING clause is used only when there is a GROUP BY clause in the query statement.
The WHERE clause is applied to each row that forms the group, and the HAVING clause is applied to the aggregation of the group.
SELECT region, sum (population), sum (area) the have sum (area) of the BBC GROUP by region >1000000

5. DISTINCT
Distinct eliminate duplicate data. If the value is the same, the value appears once.
Distinct appears at the beginning of the list, or appears in count.

MySQL keyword explanation (join, order BY, group BY, have, distinct)