SQL查询语句（二）—— 多表查询
一、列名/表名更改
列名更改
AS关键字是可选的，因此您可以在语句中将其省略
SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;
选择员工的名字和姓氏，使用CONCAT_WS()函数将名字和姓氏连接为全名
SELECT
CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
employees;
表名更改
table_name AS table_alias
将employees表别名更改为e：
SELECT * FROM employees e;
为表分配别名后，您可以使用以下语法引用表列：
table_alias.column_name
SELECT
e.firstName,
e.lastName
FROM
employees e
ORDER BY e.firstName;
SELECT
customerName,
COUNT(o.orderNumber) total
FROM
customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY
customerName
ORDER BY
total DESC;
二、INNER JOIN 内连接,等值连接
SELECT
select_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...;
示例：
查询 产品的号码、产品名、产品描述
SELECT
productCode,
productName,
textDescription
FROM
products t1
INNER JOIN productlines t2
ON t1.productline = t2.productline;
由于两个表的联接列都具有相同的名称 productline，因此可以使用以下USING
SELECT
productCode,
productName,
textDescription
FROM
products
INNER JOIN productlines USING (productline);
示例2：
从orders和orderdetails表中返回来自和表的订单号，订单状态和总销售额：INNER JOIN``GROUP BY
SELECT
t1.orderNumber,
t1.status,
SUM(quantityOrdered * priceEach) total
FROM
orders t1
INNER JOIN orderdetails t2
ON t1.orderNumber = t2.orderNumber
GROUP BY orderNumber;
INNER JOIN–连接三个表示例：
SELECT
orderNumber,
orderDate,
orderLineNumber,
productName,
quantityOrdered,
priceEach
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
INNER JOIN
products USING (productCode)
ORDER BY
orderNumber,
orderLineNumber;
INNER JOIN`使用其他运算符
SELECT
orderNumber,
productName,
msrp,
priceEach
FROM
products p
INNER JOIN orderdetails o
ON p.productcode = o.productcode
AND p.msrp > o.priceEach
WHERE
p.productcode = 'S10_1678';
三、LEFT JOIN 左连接
LEFT JOIN返回左表中的所有行，而不管左表中的行是否与右表中的行匹配。如果没有匹配项，则右表中该行的列将包含NULL
SELECT
select_list
FROM
t1
LEFT JOIN t2 ON
join_condition;
SELECT
c.customerNumber,
customerName,
orderNumber,
status
FROM
customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;
LEFT JOIN 查找不匹配的行
SELECT
c.customerNumber,
c.customerName,
o.orderNumber,
o.status
FROM
customers c
LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
WHERE
orderNumber IS NULL;
对INNER JOIN子句，该子句中on的使用 可以等效WHERE 但在LEFT JOIN不等效
SELECT
o.orderNumber,
customerNumber,
productCode
FROM
orders o
LEFT JOIN orderDetails
USING (orderNumber)
WHERE
orderNumber = 10123;
SELECT
o.orderNumber,
customerNumber,
productCode
FROM
orders o
LEFT JOIN orderDetails d
ON o.orderNumber = d.orderNumber AND
o.orderNumber = 10123;
-Condition-in-WHERE-clause.png)
四、RIGHT JOIN 右链接
RIGHT JOIN返回右表中的所有行，而不管右表中的行是否与左表中的行匹配。如果没有匹配项，则右左表中该行的列将包含NULL
五、CROSS JOIN 交叉连接
从联接表中返回行的笛卡尔积
SELECT * FROM t1
CROSS JOIN t2;
获取所有商店和产品的组合
SELECT
store_name, product_name
FROM
stores AS a
CROSS JOIN
products AS b;
https://www.mysqltutorial.org/mysql-cross-join/
六、SELF JOIN 自连接
SELECT
CONCAT(m.lastName, ', ', m.firstName) AS Manager,
CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
employees e
INNER JOIN employees m ON
m.employeeNumber = e.reportsTo
ORDER BY
Manager;
SELECT
IFNULL(CONCAT(m.lastname, ', ', m.firstname),
'Top Manager') AS 'Manager',
CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
employees e
LEFT JOIN employees m ON
m.employeeNumber = e.reportsto
ORDER BY
manager DESC;
显示位于同一城市的客户列表
SELECT
c1.city,
c1.customerName,
c2.customerName
FROM
customers c1
INNER JOIN customers c2 ON
c1.city = c2.city
AND c1.customername > c2.customerName
ORDER BY
c1.city;
c1.city = c2.city确保两个客户都在同一个城市。c.customerName > c2.customerName确保不包括同一位客户。
