1.0 INSERT ON DUPLICATE KEY UPDATE

当插入的数据中的主键 与 数据库中现有的数据主键 重复的情况下就不会执行插入操作，而是可以对现有的数据进行更新操作，不存在相同主键则执行插入操作。

2.0 join

参见 https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. OUTER JOIN EXCLUDING INNER JOIN

For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, and OUTER EXCLUDING JOIN, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE clause).

2.1 Inner JOIN

INNER_JOIN.png

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

2.2 Left JOIN

LEFT_JOIN.png

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

2.3 Right JOIN

RIGHT_JOIN.png

This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

2.4 Outer JOIN

FULL_OUTER_JOIN.png

This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

2.5 Left Excluding JOIN

LEFT_EXCLUDING_JOIN.png

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

2.6 Right Excluding JOIN

RIGHT_EXCLUDING_JOIN.png

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

2.7 Outer Excluding JOIN

OUTER_EXCLUDING_JOIN.png

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows: