MySQL必知必会—联结表和高级查询篇

MySQL必知必会—概念篇

MySQL必知必会—安装应用篇

MySQL必知必会—检索数据篇

下面用到的数据库文件可在

mysql_scripts 找到。

---

使用子查询

假设要列出订购物品 TNT2 的所有客户。我们可以拆分出下面三步。

1. 检索包含物品 TNT2 的所有订单的编号。
2. 检索具有前一步骤列出的订单编号的所有客户的 ID。
3. 检索前一步骤返回的所有客户 ID 的客户信息。

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

假设需要显示 customers 表中每个客户的订单总数，我们可以查分出下面两步。

1. 从 customers 表中检索客户列表。
2. 对于检索出的每个客户，统计其在 orders 表中的订单数目。

SELECT cust_name, cust_contact,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
+----------------+--------------+--------+
| cust_name      | cust_contact | orders |
+----------------+--------------+--------+
| Coyote Inc.    | Y Lee        |      2 |
| E Fudd         | E Fudd       |      1 |
| Mouse House    | Jerry Mouse  |      0 |
| Wascals        | Jim Jones    |      1 |
| Yosemite Place | Y Sam        |      1 |
+----------------+--------------+--------+

---

联结表

### 等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为 内部联结。
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;


### 内部联结的语法
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;


### 上面提到用子查询，返回订购产品 TNT 的客户列表，现在改成联结表的方式，可以跟子查询的方式对比一下。
SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';

注意

• 应该保证所有的联结都有 WHERE 子句，否则 MySQL 将返回比想要的数据多得多的数据。

---

创建高级联结

表别名和自联结

### 使用表别名，返回订购产品 TNT 的客户列表
SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id = 'TNT2';

### 使用自联结，查找商品 ID 为 DTNTR 的供应商供应的所有产品
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';

外部联结

联结包含了那些在相关表中没有关联行的行，外部联结的两种基本形式：左外部联结(LEFT OUTER JOIN 即 LEFT JOIN)和右外部联结。它们之间唯一差别是所关联的表的顺序不同。更具体可以看一下 JOIN详解。

### 列出每个客户下的订单，包括那些至今未下订单的客户
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 |
+---------+-----------+

#### 对每个用户下的订单计数，包括那些至今没下订单的客户
SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS order_count FROM customers AS c LEFT JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id;
+----------------+---------+-------------+
| cust_name      | cust_id | order_count |
+----------------+---------+-------------+
| Coyote Inc.    |   10001 |           2 |
| Mouse House    |   10002 |           0 |
| Wascals        |   10003 |           1 |
| Yosemite Place |   10004 |           1 |
| E Fudd         |   10005 |           1 |
+----------------+---------+-------------+

---

组合查询

MySQL 允许执行多个查询（多条 SELECT 语句），并将结果作为单个查询结果集返回。这些组合查询称为并（union） 或 复合查询（compound query）。

有两种基本情况，其中需要使用组合查询：

• 在单个查询中从不同的表返回类似结构的数据；
• 对单个表执行多个查询，按单个查询返回数据。

### 查询价格小于等于5的所有物品并且查出供应商 1001 和 1002 生产的所有物品（不考虑价格）

### 先用 WHERE 多个子句来实现。
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002);

### 使用组合查询实现，会自动去除重复的行
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);

### 使用组合查询查所有符合条件的列
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

### 组合查询排序
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) ORDER BY vend_id, prod_id;

注意

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。
• UNION 中的每个查询必须包含相同的列，表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 对组合查询结果排序时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。

---

一个持续更新的github笔记，链接地址：Front-End-Basics，可以watch,也可以star。

此篇文章的地址：MySql必知必会