MySQL Essentials : création de jointures avancées

1. Utiliser des alias de table

En plus des alias pour les noms de colonnes et les champs calculés, SQL autorise également les alias pour les noms de table. Il y a deux raisons principales à cela :

• pour raccourcir les instructions SQL

• pour permettre à la même table d'être utilisée plusieurs fois dans un une seule instruction 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='TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

• Analyse : vous pouvez voir que les trois tables de la clause FROM ont des alias. customers AS c établit c comme alias pour customers, etc. Cela permet d'utiliser l'elliptique c au lieu du nom complet customers. Dans cet exemple, l'alias de table est utilisé uniquement dans la clause WHERE. Cependant, les alias de table peuvent être utilisés non seulement dans les clauses WHERE, mais également dans les listes de SELECT, les clauses ORDER BY et d'autres parties d'instructions.

2. Utiliser différents types de jointures

2.1 Auto-jointure

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='DTNTR'
    -> ;
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

• Analyse : les deux requises dans cette requête La table est en fait la même table, donc la table products apparaît deux fois dans la clause FROM. Bien que cela soit parfaitement légal, la référence à products est ambiguë car MySQL ne sait pas à quelle instance de la table products vous faites référence.

Utiliser l'auto-jointure au lieu de la sous-requête : l'auto-jointure est généralement utilisée comme instruction externe pour remplacer l'instruction de sous-requête utilisée lors de la récupération des données de la même table. Bien que le résultat final soit le même, le traitement des jointures est parfois beaucoup plus rapide que le traitement des sous-requêtes

2.2 Jointures naturelles

Les jointures naturelles excluent plusieurs occurrences afin que chaque colonne ne renvoie qu'une seule fois.

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 = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| 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 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------

+

• Analyse : Dans cet exemple, le caractère générique n'est utilisé que pour la première table. Toutes les autres colonnes sont répertoriées explicitement afin qu'aucune colonne en double ne soit récupérée.

2.3 Connexion externe

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

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 |
+----------------+---------+---------+

• 分析：这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

推荐教程：《MySQL教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!