MySQL Essentials: 高度な結合の作成-mysql チュートリアル-php.cn

MySQL Essentials: 高度な結合の作成

hzc

リリース： 2020-06-04 11:51:19

転載

1599 人が閲覧しました

1. テーブルエイリアスの使用

列名と計算フィールドのエイリアスに加えて、SQL ではテーブル名のエイリアスも使用できます。これを行う主な理由は 2 つあります。

SQL ステートメントを短縮する;
同じテーブルを 1 回の実行で複数回使用できるようにするSELECT ステートメント。

mysql> SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id=&#39;TNT2&#39;;
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

ログイン後にコピー

分析: FROM 句内の 3 つのテーブルすべてに別名があることがわかります。 customers AS c customers のエイリアスとして c を作成する、など。これにより、フルネーム customers の代わりに、短縮形の c を使用できるようになります。この例では、テーブルの別名は WHERE 句でのみ使用されます。ただし、テーブルエイリアスは、WHERE 句だけでなく、SELECT、 ORDER BY 句のリスト、およびステートメントの他の部分でも使用できます。

2. さまざまな種類の結合を使用する

2.1 自己結合

mysql> SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id=&#39;DTNTR&#39;
    -> ;
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

ログイン後にコピー

分析: このクエリで必要な 2 つはこれらのテーブルは実際には同じテーブルであるため、products テーブルは FROM 句内に 2 回現れます。これは完全に合法ですが、products への参照はあいまいです。MySQL は、products テーブル内のどのインスタンスを参照しているのかわからないからです。

サブクエリの代わりに自己結合を使用する: 自己結合は、同じテーブルからデータを取得するときに使用されるサブクエリステートメントを置き換える外部ステートメントとしてよく使用されます。最終結果は同じですが、結合の処理はサブクエリの処理よりもはるかに高速な場合があります。

2.2 自然結合

自然結合では複数の出現が除外されるため、各列は 1 回だけ返されます。

mysql> SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o,orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = &#39;FB&#39;;
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------

ログイン後にコピー

分析: この例では、ワイルドカードは最初のテーブルにのみ使用されています。他のすべての列は明示的にリストされるため、重複する列は取得されません。

2.3 外部接続

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
为了检索所有客户，包括那些没有订单的客户，可如下进行：

mysql> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON omers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

ログイン後にコピー

分析：类似于上一章中所看到的内部联结，这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（ RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。

3 使用带聚集函数的联结

如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

mysql> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

ログイン後にコピー

分析：此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY 子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

mysql> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

ログイン後にコピー