MySQL基础之MySQL必知必会(十六)创建高级联结

variab

2020-07-28

使用表别名

SQL允许给表取别名, 这样做的理由是

缩短SQL语句
允许在单条SELECT语句中多次使用相同的表

MariaDB [crashcourse]> 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        |
+----------------+--------------+
2 rows in set (0.002 sec)

MariaDB [crashcourse]>

表别名只在查询中使用, 与列别名不一样, 表别名不返回到客户机。

使用不同类型的联结

自联结

使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。

MariaDB [crashcourse]> 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) |
+---------+----------------+
7 rows in set (0.001 sec)

MariaDB [crashcourse]>

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终结果相同, 但有时候处理联结远比处理子查询快得多。

自然联结

无论何时对表进行联结, 应该至少有一个列出现在不止一个表中。标准的联结返回所有数据, 甚至相同的列多次出现。自然联结排除多次出现, 使每个列只返回一次。

系统完不成这项操作, 需要自己完成。自然联结是这样的一种联结, 其中你只能选择那些唯一的列。这一般是通过对表使用通配符(SELECT *), 对所有其他表的列使用明确的子集来完成。

MariaDB [crashcourse]> 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 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.001 sec)

MariaDB [crashcourse]>

外部联结

联结包含了那些在相关表中没有的关联行的行。这种类型的联结称为外部联结。

MariaDB [crashcourse]> SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.001 sec)

MariaDB [crashcourse]>

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型。与内部联结关联两个表中的行不同的是, 外部联结还包括没有关联行的行。

在使用OUTER JOIN语法时, 必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表, 而LEFT指出的是OUTER JOIN左边的表)。

使用带聚集函数的联结

MariaDB [crashcourse]> 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 |
+----------------+---------+---------+
4 rows in set (0.002 sec)

MariaDB [crashcourse]>