【Mysql】教程全解（四）表连接

L罗乐 2018-05-06

展开全文

4.1 别名

有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。

以下语句说明了如何使用列别名：

SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;

SQL

要给列添加别名，可以使用AS关键词后跟别名。如果别名包含空格，则必须引用以下内容：

SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;

SQL

因为AS关键字是可选的，可以在语句中省略它。请注意，还可以在表达式上使用别名。

我们来看看示例数据库(yiibaidb)中的employees表，其表结构如下所示 -

以下查询选择员工的名字和姓氏，并将其组合起来生成全名。 CONCAT_WS函数用于连接名字和姓氏。

SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;

SQL

执行上面代码，得到以下结果 -

mysql> SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;
 -------------------------------------- 
| CONCAT_WS(', ', lastName, firstname) |
 -------------------------------------- 
| Murphy, Diane                        |
| Patterson, Mary                      |
| Firrelli, Jeff                       |
| Patterson, William                   |
| Bondur, Gerard                       |
| Bow, Anthony                         |
| Jennings, Leslie                     |
| Thompson, Leslie                     |
| Firrelli, Julie                      |
| Patterson, Steve                     |
| Tseng, Foon Yue                      |
| Vanauf, George                       |
| Bondur, Loui                         |
| Hernandez, Gerard                    |
| Castillo, Pamela                     |
| Bott, Larry                          |
| Jones, Barry                         |
| Fixter, Andy                         |
| Marsh, Peter                         |
| King, Tom                            |
| Nishi, Mami                          |
| Kato, Yoshimi                        |
| Gerard, Martin                       |
 -------------------------------------- 
23 rows in set

Shell

在上面示例中，列标题很难阅读理解。可以为输出的标题分配一个有意义的列别名，以使其更可读，如以下查询：

SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;

SQL

执行上面代码，得到以下结果 -

mysql> SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;
 -------------------- 
| Full name          |
 -------------------- 
| Murphy, Diane      |
| Patterson, Mary    |
| Firrelli, Jeff     |
... ...
| King, Tom          |
| Nishi, Mami        |
| Kato, Yoshimi      |
| Gerard, Martin     |
 -------------------- 
23 rows in set

Shell

在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列别名来引用该列。

以下查询使用ORDER BY子句中的列别名按字母顺序排列员工的全名：

SELECT
 CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
 employees
RDER BY
 `Full name`;

SQL

执行上面代码，得到以下结果 -

mysql> SELECT
 CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
 employees
ORDER BY
 `Full name`;
 ------------------- 
| Full name         |
 ------------------- 
| Bondur Gerard     |
| Bondur Loui       |
| Bott Larry        |
| Bow Anthony       |
| Castillo Pamela   |
| Firrelli Jeff     |
| Firrelli Julie    |
| Fixter Andy       |
| Gerard Martin     |
| Hernandez Gerard  |
| Jennings Leslie   |
| Jones Barry       |
| Kato Yoshimi      |
| King Tom          |
| Marsh Peter       |
| Murphy Diane      |
| Nishi Mami        |
| Patterson Mary    |
| Patterson Steve   |
| Patterson William |
| Thompson Leslie   |
| Tseng Foon Yue    |
| Vanauf George     |
 ------------------- 
23 rows in set

Shell

以下语句查询总金额大于60000的订单。它在GROUP BY和HAVING子句中使用列别名。

SELECT
 orderNumber `Order no.`,
 SUM(priceEach * quantityOrdered) total
FROM
 orderdetails
GROUP BY
 `Order no.`
HAVING
 total > 60000;

SQL

执行上面查询语句，得到以下结果 -

请注意，不能在WHERE子句中使用列别名。原因是当MySQL评估求值WHERE子句时，SELECT子句中指定的列的值可能尚未确定。

MySQL表的别名

可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：

table_name AS table_alias

SQL

该表的别名称为表别名。像列别名一样，AS关键字是可选的，所以完全可以省略它。

一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。

下面来看看客户(customers)和订单(orders)表，它们的ER图如下所示 -

两个表都具有相同的列名称：customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列，则执行查询时将收到类似以下错误消息：

Error Code: 1052. Column 'customerNumber' in on clause is ambiguous

Shell

为避免此错误，应该使用表别名来限定customerNumber列：

SELECT
 customerName,
 COUNT(o.orderNumber) total
FROM
 customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY customerName
HAVING total >=5
ORDER BY total DESC;

SQL

执行上面查询语句，得到以下结果 -

上面的查询从客户(customers)和订单(orders)表中选择客户名称和订单数量。它使用c作为customers表的表别名，o作为orders表的表别名。customers和orders表中的列通过表别名(c和o)引用。

如果您不在上述查询中使用别名，则必须使用表名称来引用其列，这样的会使得查询冗长且可读性较低，如下 -

SELECT
       customers.customerName,
 COUNT(orders.orderNumber) total
FROM  customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY  customerName
ORDER BY  total DESC

4.2 INNER JOIN

MySQL INNER JOIN子句将一个表中的行与其他表中的行进行匹配，并允许从两个表中查询包含列的行记录。

INNER JOIN子句是SELECT语句的可选部分，它出现在FROM子句之后。

在使用INNER JOIN子句之前，必须指定以下条件：

首先，在FROM子句中指定主表。
其次，表中要连接的主表应该出现在INNER JOIN子句中。理论上说，可以连接多个其他表。但是，为了获得更好的性能，应该限制要连接的表的数量(最好不要超过三个表)。
第三，连接条件或连接谓词。连接条件出现在INNER JOIN子句的ON关键字之后。连接条件是将主表中的行与其他表中的行进行匹配的规则。

INNER JOIN子句的语法如下：

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

SQL

假设使用INNER JOIN子句连接两个表：t1和t2，我们来简化上面的语法。

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

SQL

对于t1表中的每一行，INNER JOIN子句将它与t2表的每一行进行比较，以检查它们是否都满足连接条件。当满足连接条件时，INNER JOIN将返回由t1和t2表中的列组成的新行。

请注意，t1和t2表中的行必须根据连接条件进行匹配。如果找不到匹配项，查询将返回一个空结果集。当连接超过2个表时，也应用此逻辑。

以下维恩图说明了INNER JOIN子句的工作原理。结果集中的行必须出现在两个表中：t1和t2，如两个圆的交叉部分所示 -

在MySQL INNER JOIN中避免列错误

如果连接具有相同列名称的多个表，则必须使用表限定符引用SELECT和ON子句的列，以避免列错误。

例如，如果t1和t2表都具有名为c的一个相同列名，则必须在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是那个表中的c列。

为了节省书写表限定符的时间，可以在查询中使用表别名。例如，可以长名称verylonglonglong_tablename表使用表别名，并使用t.column引用其列，而不是使用verylonglonglong_tablename.column，但是如果喜欢书写或使用这么长的表名称，那么也应该允许你的开发伙伴骂你几句类似：傻逼~等这样的话！

MySQL INNER JOIN示例

下面来看看示例数据库(yiibaidb)中的产品(products)和产品线(productlines)表。它们的 ER 图如下所示 -

在上面图中，products表中的productLine列参考引用productlines表的productline列。 products表中的productLine列称为外键列。

通常，连接具有外键关系的表，如产品线(productlines)和产品(products)表。现在，如果想获取以下数据 -

获取products表中的productCode和productName列的值。
获取productlines表产品线的描述 - textDescription列的值。

为此，需要通过使用INNER JOIN子句根据productline列匹配行来从两个表中查询选择数据，如下所示：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM    products t1        
INNER JOIN  productlines t2 ON t1.productline = t2.productline;

SQL

执行上面查询，得到下面的结果(部分)-

由于两个表的连接列是使用相同一个列：productline，因此可以使用以下语法：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products        
INNER JOIN
    productlines USING (productline);

SQL

上面语句返回相同的结果集，但是使用此语法，不必使用表的别名。

MySQL INNER JOIN GROUP BY子句

请参阅以下订单和订单详细表，orders表和orderdetails表的结构如下所示 -

可以使用具有GROUP BY子句的INNER JOIN子句从orders和orderdetails表中获取订单号，订单状态和总销售额，如下所示：

SELECT 
    T1.orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders AS T1        
INNER JOIN
    orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;

SQL

执行上面查询，结果如下所示(部分) -

类似地，以下语句查询与上述得到结果相同：

SELECT 
    orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders        
INNER JOIN
    orderdetails USING (orderNumber)
GROUP BY orderNumber;

SQL

MySQL INNER JOIN使用等于以外的运算符

到目前为止，您已经看到连接谓词使用相等的运算符(=)来匹配行。但是也可以使用大于(>)，小于(<)和不等于(<>)运算符的其他运算符来形成连接谓词。

以下查询使用少于(<)连接来查找低于代码为S10_1678的产品的销售价格的制造商建议零售价(MSRP)的所有产品。

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';

SQL

执行上面查询语句，得到以下输出结果 -

mysql> 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';
 ------------- --------------------------------------- ------ ----------- 
| orderNumber | productName                           | msrp | priceEach |
 ------------- --------------------------------------- ------ ----------- 
|       10107 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10121 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 86.13     |
|       10134 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 90.92     |
|       10145 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 76.56     |
|       10159 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10168 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 94.74     |
|       10399 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 77.52     |
|       10403 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 85.17     |
... ...
|       10417 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 79.43     |
 ------------- --------------------------------------- ------ ----------- 
26 rows in set

4.3 LEFT JOIN

MySQL LEFT JOIN子句允许您从两个或多个数据库表查询数据。LEFT JOIN子句是SELECT语句的可选部分，出现在FROM子句之后。

我们假设要从两个表t1和t2查询数据。以下语句说明了连接两个表的LEFT JOIN子句的语法：

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1        
LEFT JOIN
    t2 ON t1.c1 = t2.c1;

SQL

当使用LEFT JOIN子句将t1表加入t2表时，如果来自左表t1的行与基于连接条件(t1.c1 = t2.c1)的右表t2匹配，则该行将被包含在结果集中。

如果左表中的行与右表中的行不匹配，则还将选择左表中的行并与右表中的“假”行组合。“假”行对于SELECT子句中的所有相应列都包含NULL值。

换句话说，LEFT JOIN子句允许您从匹配的左右表中查询选择行记录，连接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也显示出来，但使用NULL值代替。

下图可帮助您可视化LEFT JOIN子句的工作原理。两个圆圈之间的交点是两个表中匹配的行，左圆的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。因此，左表中的所有行都包含在结果集中。

请注意，如果这些子句在查询中可用，返回的行也必须与WHERE和HAVING子句中的条件相匹配。

2. MySQL LEFT JOIN示例

2.1 使用MySQL LEFT JOIN子句来连接两个表

我们来看看在示例数据库(yiibaidb)中的两个表：订单表和客户表，两个表的 ER 图如下所示 -

在上面的数据库图中：

订单(orders)表中的每个订单必须属于客户(customers)表中的客户。
客户(customers)表中的每个客户在订单(orders)表中可以有零个或多个订单。

要查询每个客户的所有订单，可以使用LEFT JOIN子句，如下所示：

SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;

SQL

执行上面查询语句，得到以下结果(部分) -

mysql> SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
 ---------------- ------------------------------------ ------------- ------------ 
| customerNumber | customerName                       | orderNumber | status     |
 ---------------- ------------------------------------ ------------- ------------ 
|            103 | Atelier graphique                  |       10123 | Shipped    |
|            103 | Atelier graphique                  |       10298 | Shipped   |
... 省略部分 ...
|            477 | Mit Vergngen & Co.                 | NULL        | NULL       |
|            480 | Kremlin Collectables, Co.          | NULL        | NULL       |
|            481 | Raanan Stores, Inc                 | NULL        | NULL       |
|            484 | Iberia Gift Imports, Corp.         |       10184 | Shipped    |
|            484 | Iberia Gift Imports, Corp.         |       10303 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10109 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10236 | Shipped  |
 ---------------- ------------------------------------ ------------- ------------ 
350 rows in set

Shell

左表是customers表，因此，所有客户都包含在结果集中。但是，结果集中有一些行具有客户数据，但没有订单数据。如：customerNumber列值为：477，480等。这些行中的订单数据为NULL。也就是说这些客户在orders表中没有任何订单(未购买过任何产品)。

因为我们使用相同的列名(orderNumber)来连接两个表，所以可以使用以下语法使查询更短：

SELECT
 c.customerNumber,
 customerName,
 orderNumber,
 status
FROM
 customers c
LEFT JOIN orders USING (customerNumber);

SQL

在上面查询语句中，下面的子句 -

USING (customerNumber)

SQL

相当于 -

ON c.customerNumber = o.customerNumber

SQL

如果使用INNER JOIN子句替换LEFT JOIN子句，则只能获得至少有下过一个订单的客户。

2.2 使用MySQL LEFT JOIN子句来查找不匹配的行

当您想要找到右表中与不匹配的左表中的行时，LEFT JOIN子句非常有用。要查询两个表之间的不匹配行，可以向SELECT语句添加一个WHERE子句，以仅查询右表中的列值包含NULL值的行。

例如，要查找没有下过订单的所有客户，请使用以下查询：

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

3. WHERE子句与ON子句中的条件

请参见以下示例。

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o        
LEFT JOIN
    orderDetails USING (orderNumber)
WHERE
    orderNumber = 10123;

SQL

在本示例中，我们使用LEFT JOIN子句来查询orders表和orderDetails表中的数据。该查询返回订单号为10123的订单及其购买产品明细信息(如果有的话)。

但是，如果将条件从WHERE子句移动到ON子句：

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCodeFROM
    orders o        LEFT JOIN
    orderDetails d ON o.orderNumber = d.orderNumber       
                  AND o.orderNumber = 10123;

SQL

想想上面代码将会输出什么结果 -

4.4 CROSS JOIN （笛卡尔积）

CROSS JOIN子句从连接的表返回行的笛卡儿乘积。

假设使用CROSS JOIN连接两个表。结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。当连接的表之间没有关系时，会使用这种情况。

要特别注意的是，如果每个表有1000行，那么结果集中就有1000 x 1000 = 1,000,000行，那么数据量是非常巨大的。

下面说明连接两个表：T1和T2的CROSS JOIN子句的语法：

SELECT 
    *
FROM
    T1        
CROSS JOIN
    T2;

SQL

请注意，与INNER JOIN或LEFT JOIN子句不同，CROSS JOIN子句不具有连接条件。

如果添加了WHERE子句，如果T1和T2有关系，则CROSS JOIN的工作方式与INNER JOIN子句类似，如以下查询所示：

SELECT 
    *
FROM
    T1        
CROSS JOIN
    T2
WHERE
    T1.id = T2.id;

SQL

MySQL CROSS JOIN子句示例

下面我们将使用以下几个表来演示CROSS JOIN的工作原理。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13 , 2 ));

DROP TABLE IF EXISTS sales;

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100));

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id , store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE);

SQL

上面语句中，创建了三个表：

产品(products)表包含产品编号，产品名称和销售价格等产品主要数据。
商店(stores)表包含销售产品的商店信息。
销售(sales)表包含在特定商店按数量和日期销售的产品。

假设有三个产品：iPhone，iPad和Macbook Pro，在北部(North)和南部(South)的这两个商店中出售。

INSERT INTO products(product_name, price)
            VALUES('iPhone', 699),
                  ('iPad',599),
                  ('Macbook Pro',1299);
INSERT INTO stores(store_name)
VALUES('North'),
      ('South');
INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
      (1,2,15,'2017-01-05'),
      (1,3,25,'2017-01-05'),
      (2,1,30,'2017-01-02'),
      (2,2,35,'2017-01-05');

SQL

要获得每个商店和每个产品的总销售额，您可以计算销售额，并按商店和产品分组如下：

SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales        
INNER JOIN
    products ON products.id = sales.product_id        
INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name; 
 ------------ -------------- ------------ 
| store_name | product_name | revenue    |
 ------------ -------------- ------------ 
| North      | iPad         | 8985.0000  |
| North      | iPhone       | 13980.0000 |
| North      | Macbook Pro  | 32475.0000 |
| South      | iPad         | 20965.0000 |
| South      | iPhone       | 20970.0000 |
 ------------ -------------- ------------ 
5 rows in set

Shell

现在，如果你想知道哪个商店中的哪些产品的没有销售怎么办？上面的查询无法回答这个问题。

要解决这个问题，可以使用CROSS JOIN子句。

首先，使用CROSS JOIN子句来获取所有商店和产品的组合：

SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;
 ------------ -------------- 
| store_name | product_name |
 ------------ -------------- 
| North      | iPhone       |
| South      | iPhone       |
| North      | iPad         |
| South      | iPad         |
| North      | Macbook Pro  |
| South      | Macbook Pro  |
 ------------ -------------- 
6 rows in set

Shell

接下来，将上述查询的结果与按商店和产品返回总销售额的查询相结合。以下查询说明了这个想法：

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
         products AS a
    CROSS JOIN
         stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
            product_name,
            ROUND(SUM(quantity * price), 0) AS revenue    
FROM
        sales    
       INNER JOIN products ON products.id = sales.product_id    
       INNER JOIN stores ON stores.id = sales.store_id    
GROUP BY store_name , product_name) AS c ON c.store_id = b.id       
 AND c.product_id= a.idORDER BY b.store_name;