03 多个表操作

3.1 子查询

子查询有两个作用:一是过滤,二是计算

作为过滤作用的子查询

我们有customers、orders、orderitems三个表,其中customers和orderitems没有直接关系。我们要查询买了TNT2商品的用户信息

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

先通过TNT2在orderitems中找到order_num,再用order_num在orders找到对应的cust_id,再用cust_id找出customer的信息。

作为计算作用的子查询

SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+

查找每个customer的order数量。

3.2 内联结

mysql> SELECT vend_name, prod_name, prod_price
-> FROM vendors, products
-> WHERE vendors.vend_id = products.vend_id
-> ORDER BY vend_name, prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+

注意这里WHERE子句的重要性,如果没有联结条件的过滤,返回的结果将是笛卡尔积。检索出的数目是第一个表的行数乘以第二个表的行数,即第一个表的每一个记录,都对第二个表的每个记录组合了一次。

联结应应当都给出ON条件,不然会返回笛卡尔积。

上面这样使用的联结叫内部联结或等值联结(equijoin),更规范的写法如下:

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

条件用ON给出

子查询未必是最有效率方法,3.1中的过滤也可以用两个联结写成下面这样

mysql> SELECT cust_name, cust_contact
-> FROM customers, orders, orderitems
-> WHERE customers.cust_id = orders.cust_id
-> AND orderitems.order_num = orders.order_num
-> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

所谓自然联结,是指结果不会出现相同的列。上面的内联结就是自然联结。很可能我们永远也不会用到不是自然联结的内联结。

3.3 自联结

一个例子是prod_id为DTNTR的产品出了问题,我们要在products表中找到生产DTNTR的vendor生产的其他产品信息。

一种方法是用子查询,先通过DTNTR找到vend_id,再用vend_id找到这个vendor生产的其他产品

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE 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) |
+---------+----------------+

通常使用联结的效率要高于子查询,我们使用自联结。在自联结中,为了区分同一个表的不同指代,我们使用了别名。

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

3.4 外联结

要检索所有客户的订单,可以向下面这样使用内联结

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

如果要检索包括没有订单的客户,可以使用左联结

mysql> SELECT customers.cust_id, orders.order_num
-> FROM customers LEFT 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 |
+---------+-----------+

左右联结的唯一区别在于顺序不同,可以通过交换顺序转换左右联结。

3.5 组合查询

通过UNION,可以给出多条SELECT语句,把它们的结果组合成单个结果集。注意UNION中的每个查询必须包含相同的列、表达式或聚集函数(顺序可以不同)。列的类型要相同,或者DBMS要能隐式转换。

mysql> SELECT vend_id, prod_id, prod_price
-> FROM products
-> WHERE prod_price <= 5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
mysql> SELECT vend_id, prod_id, prod_price
-> FROM products
-> WHERE vend_id IN (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
mysql> SELECT vend_id, prod_id, prod_price
-> FROM products
-> WHERE prod_price <= 5
-> UNION
-> SELECT vend_id, prod_id, prod_price
-> FROM products
-> WHERE vend_id IN (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+

注意UNION后的结果之有8个,比前两次单个查询结果之和少了一个。这是因为UNION默认会在查询结果集中取消重复的行。如果要想包含重复的行,可以用UNION ALL

另外,UNION的结果集也可以用ORDER BY进行排序,但是只能对结果集排一次序,出现在最后一个SELECT语句之后。不能部分结果用一种排序,而另一部分结果用另一种排序。