1000 Line MySQL learning notes (iv)

/* UNION */------------------

Combines the results of multiple select queries into a single result set.

SELECT ... UNION [all| DISTINCT] SELECT ...

The default DISTINCT method, that is, all returned rows are unique

It is recommended that you enclose each select query with parentheses.

The order by order is combined with a LIMIT.

Requires the same number of fields for each select query.

The field list (quantity, type) for each select query should be consistent because the field names in the results will be the first SELECT statement.

/* Sub-query */-----------------

--The subquery needs to be wrapped in parentheses.

- -from type
The from post requirement is a table, and the subquery result must be given an individual name.
-simplifies the conditions within each query.
-The from type requires the result to generate a temporary table that can be released with the lock of the original table.

-The subquery returns a table-type subquery.

Select * from (select * from TB where id>0) as Subfrom where id>1;

--Where type

-A subquery Returns a value that is a scalar subquery.
-You don't need to alias the subquery.
-the table within the where subquery is not intended to be updated directly.
Select * from TB where money = (select max(money) from TB );

--Query
If the subquery result returns a column.

To complete a query using in or not
exists and not exists conditions

Returns 1 or 0 if the subquery returns data. Often used for judging conditions.
Select column1 from T1 where exists (select * from T2);

--row sub-query
A query condition is a row.
Select * from T1 where (ID, gender) in (Select ID, gender from T2);

Line constructor: (col1, col2, ...) or row (col1, col2, ...)

A row constructor is typically used to compare a subquery that returns two or more columns that can be returned.

--Special operators

! = All () equivalent to not in
= Some () equals in. Any is an alias of some
! = Some () is not equal to not, and not equal to one of them.
All , some can be used in conjunction with other operators.

/* Connection query (join) */-------------
You can specify join conditions by connecting fields from multiple tables.

--Inner connection (inner join)

-The default is internal connection, can omit inner.
-You can send a connection only if the data exists. That is, the connection result cannot appear blank lines.
on indicates the connection condition. Its conditional expression is similar to where. You can also omit the condition (which indicates that the condition is always true) or where to represent the join condition. There is a using, but the field names are the same. using (field name)

--Cross Join
That is, there are no conditions within the connection.
Select * from tb1 Cross join TB2;

--External connection (outer join)

-If the data does not exist, it will also appear in the connection results.

--left outer join
If the data does not exist, the left table record appears, and the right table is a null fill

--Right outer join
If the data does not exist, the right table record appears, and the left table is null populated

--Natural connection (natural join)
Automatically determine the connection condition to complete the connection. Equivalent to omitting the using, the same field name is automatically found.

Natural Join

Natural left Join

Natural Right join

(not to be continued)
(Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Line MySQL learning notes (iv)