"Go" MySQL union, left join, right join, inner join, and view learning

1. When Union union makes multiple query statements, the number of result columns required for multiple queries must be the same. At this point, the result of the query is based on the column name of the first SQL statement and the Union is automatically repeated we should use union all. Cases......

1. When Union union makes multiple query statements, the number of result columns required for multiple queries must be the same. At this point, the result of the query is based on the column name of the first SQL statement and the Union is automatically repeated we should use union all.
For example:
Select Id,sum (num) from (SELECT * FROM-TA UNION All-select * from-TB) as TMP GROUP by ID;
Select Id,sum (num) from (SELECT * FROM-TA Union select * from TB);
Select Id,sum (num) from (SELECT * FROM TA Union select * from TB) as TMP;
Please test the results of the above three statements yourself;
Note: The number of columns for TA and TB must be the same, otherwise it cannot be written as SELECT * FROM TA Union select * FROM tb;* should be replaced with a specific field name;
When a child statement contains a where, order by, or limit, the child statement should be enclosed in parentheses.
Such as:
Select Id,sum (num) from (SELECT * to Taorder by ID DESC-UNION ALL SELECT * FROM-TB ORDER BY-ID DESC) as TMP GROUP by ID; The wrong wording
Select Id,sum (num) from ([select * from TA order by id DESC] UNION ALL (SELECT * from TB ORDER BY id DESC)) as TMP Group B Y id; Correct writing, repeating, and not getting the results you want.
Select Id,sum (num) from ((SELECT * from TA) the union ALL (SELECT * from TB) the ORDER by id DESC) as the TMP group by ID; It's usually written after the merger.
In the sentence, only the order by with limit use will make sense, otherwise it will be removed by the parser.
2. Connect joins, left joins, right join, INNER join left join, Starboard joins, Inner joins, outer joins (currently MySQL is not supported, but can be implemented with left join +union+ connection)
The meaning of connection is: In order to get more complete information (for example: Student information), at this time, I need to from more tables (may be Student information table, student score, student family table, etc.) to find.
JOIN: Returns a row if there is at least one match in the table.
Left JOIN (left table): Returns all rows from the table, even if there are no matches in the right table.
Right JOIN (based on the table on starboard): All rows are returned from the right table, even if there are no matches in the left table.
Left join and connections are interchangeable. such as Ta left join TB =========== TB Right Join TA is a reference to the TA table for the query.
Connections can be used multiple times, but when a table is repeatedly concatenated, we should use as to give the table an alias.
such as: Selectt1.name, ' Match_result ', T2.name, ' match_time ' from TA left jointb as T1 on Ta.host_id=t1.id left join TB as T2 on TA . guest_id =t2.id;
3. View learning (You can set the permissions of some developers to access the database, see what should be seen, what should not be seen, such as: Sina.qq developed interface, third-party users can only use some data)
Definition: A view is a virtual table, a table that is exported from one or several basic tables (or views), that contains only the definition of the view in the system's data dictionary, and does not hold the data corresponding to the view.
Role: Simplicity (see what is needed), security (through view users can only query and modify the data they can see), Big Data sub-table when used (when the amount of data on a table is relatively large). The usage permissions can be limited to a subset of the rows of the base table. Use permissions can be limited to a subset of the columns of the base table. Use permissions can be limited to a subset of the rows and columns of the base table. Use permissions can be limited to rows that are qualified for connections to multiple base tables. Usage permissions can be limited to statistical summaries of the data in the base table. Use permissions can be restricted to a subset of another view, or to a subset of the merged views and base tables.
The relationship between the view and the table (they are one by one, similar to the relationship between the function and the inverse function):
1. When you modify a table, the data about the view is also changed for that table.
2. When modifying a view, if the column of the view corresponds to the column one by one of the table (that is, there is no aggregate function sum () AVG () count () Max () min (), etc., group by), the modification succeeds, or fails (as the whole modification cannot determine the local modification exactly);

Document Source: http://www.itxm.net/a/shujuku/2016/1205/840.html

"Go" MySQL union, left join, right join, inner join, and view learning