MySQL ORDER BY,GROUPBY 与各种JOIN

1. Order By

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

原始的表 (用在例子中的)：

Orders 表:

Company	OrderNumber
Titan Tech.	3532
Goodman Inc.	2356
Trump Limited.	4698
Goodman Inc.	6953

以字母顺序显示公司名称：

代码语言：javascript复制

SELECT Company, OrderNumber FROM Orders ORDER BY Company

结果：

Company	OrderNumber
Trump Limited.	4698
Titan Tech.	3532
Goodman Inc.	6953
Goodman Inc.	2356

2. Group By

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SQL GROUP BY 语法

代码语言：javascript复制

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SQL GROUP BY 实例

我们拥有下面这个 "Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

现在，我们希望查找每个客户的总金额（总订单）。

我们想要使用 GROUP BY 语句对客户进行组合。

我们使用下列 SQL 语句：

代码语言：javascript复制

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

结果集类似这样：

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

3. HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SQL HAVING 语法

代码语言：javascript复制

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SQL HAVING 实例

我们拥有下面这个 "Orders" 表：

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

现在，我们希望查找订单总金额少于 2000 的客户。

我们使用如下 SQL 语句：

代码语言：javascript复制

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

结果集类似：

Customer	SUM(OrderPrice)
Carter	1700

4. INNER JOIN

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

NNER JOIN 关键字语法

代码语言：javascript复制

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：INNER JOIN 与 JOIN 是相同的。

原始的表 (用在例子中的)：

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Titan	HW	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

内连接（INNER JOIN）实例

现在，我们希望列出所有人的定购。

您可以使用下面的 SELECT 语句：

代码语言：javascript复制

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Titan	HW	22456
Titan	HW	24562
Carter	Thomas	77895
Carter	Thomas	44678

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配，就不会列出这些行。

5. STRAIGHT JOIN

引用 MySQL Official Tutorial 的说法：

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.

也就是说，STRAIGHT_JOIN与 JOIN 类似，只不过左表始终在右表之前读取。这可用于联接优化器以次优顺序处理表的那些（少数）情况。

在 4. INNER JOIN 中的 INNER JOIN 语句可以改变为：

代码语言：javascript复制

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
STRAIGHT_JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

6. LEFT OUTER JOIN

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字语法

代码语言：javascript复制

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

原始的表 (用在例子中的)：

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Titan	HW	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

左连接（LEFT JOIN）实例

现在，我们希望列出所有的人，以及他们的定购 - 如果有的话。

您可以使用下面的 SELECT 语句：

代码语言：javascript复制

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Titan	HW	22456
Titan	HW	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

7. RIGHT OUTER JOIN

SQL RIGHT JOIN 关键字

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

RIGHT JOIN 关键字语法

代码语言：javascript复制

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：在某些数据库中， RIGHT JOIN 称为 RIGHT OUTER JOIN。

原始的表 (用在例子中的)：

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Titan	HW	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

右连接（RIGHT JOIN）实例

现在，我们希望列出所有的定单，以及定购它们的人 - 如果有的话。

您可以使用下面的 SELECT 语句：

代码语言：javascript复制

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo<br>FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Titan	HW	22456
Titan	HW	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。

8. NATURAL JOIN

自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。

比如我们有一个 order_info 表，和一个 odrer_detail 表

order_info 的表结构为

Name	OrderID
Titan	1001
Titan	1002
Titan	1003
Trump	1004
Trump	1005
Trump	1006
Trump	1007

order_detail 的表结构为

OrderID	Price
1001	55
1002	66
1003	77
1004	88
1005	99
1006	100
1007	133

当我们进行NATURAL JOIN 查询:

代码语言：javascript复制

SELECT * FROM `order_info` NATURAL JOIN `oder_detail`

结果会是：

OrderID	Name	Price
1001	Titan	55
1002	Titan	66
1003	Titan	77
1004	Trump	88
1005	Trump	99
1006	Trump	100
1007	Trump	133

9. CROSS JOIN

如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积

还是上面那个表，我只保留了OrderID为 1001，1002，1005的这三条数据。

SQL语句

代码语言：javascript复制

SELECT * FROM `order_info` CROSS JOIN `oder_detail`

查询结果

Name	OrderID	OrderID(1)	Price
		1001	55
		1002	66
		1005	99
Titan	1001	1001	55
Titan	1001	1002	66
Titan	1001	1005	99
Titan	1002	1001	55
Titan	1002	1002	66
Titan	1002	1005	99
Trump	1005	1001	55
Trump	1005	1002	66
Trump	1005	1005	99

10. SELF JOIN

SELF JOIN 是内部连接，自连接，可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

还是查询上面那个 order_info 数据表，用自连接的方式查询OrderID比Trump的订单小的所有订单

代码语言：javascript复制

SELECT a.`*` FROM `order_info` a,`order_info` b WHERE b.'name' = `Trump` AND `a`.OrderID <  `b`.OrderID

sql 数据库 nat NAT网关

0 人点赞