Join Type

#

Inner Join

#

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Left Join

#

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right Join

#

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Full Join

#

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;

GROUP BY

#

GROUP BY语句将具有相同值的行分组为汇总行，例如“查找每个国家/地区的客户数量”。
该GROUP BY语句通常与聚合函数 ( COUNT(), MAX(), MIN(), SUM(), AVG()) 一起使用， 以按一列或多列对结果集进行分组。

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

列出了每个国家的客户数量，从高到低排序：

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

group by 和 distinct 去重

单列去重，结果一样

SELECT DISTINCT `name` FROM user_info;
SELECT `name` FROM user_info GROUP BY name;

多列去重

SELECT DISTINCT `name`,number FROM user_info;
SELECT `name`,number FROM user_info GROUP BY `name`,number;

对于单列或多列去重，使用 group by 和 distinct 结果是相同的

执行方式

• distinct 主要是对数据两两进行比较，需要遍历整个表
• group by 是在查询时先把数据按照分组字段分组出来再查询，当数据量较大时，group by 速度要优于 distinct

Having

#

列出了每个国家的客户数量，从高到低排序（仅包括客户超过 5 个的国家）

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;

EXISTS

#

返回 TRUE 并列出产品价格等于 22 的供应商：

SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

USING连接

#

USING 就是等值连接的一种简化形式,SQL99 中新增的。

当我们进行连接的时候，可以用USING指定数据表里的同名字段进行等值连接。

SELECT  player_id,  team_id,  player_name,  height,  team_name  FROM  player  JOIN  team  USING(team_id) 

同时使用 JOIN USING 可以简化 JOIN ON 的等值连接，它与下面的SQL查询结果是相同的:

SELECT  player_id,  player.team_id,  player_name,  height,  team_name  FROM  player  JOIN  team  ON  player.team_id  =  team.team_id