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必知必会

阅读 578

陈工移山
移山之志,一点一滴。

移山之志,一点一滴。

3.2k 声望
435 粉丝
0 条评论
你知道吗?

移山之志,一点一滴。

3.2k 声望
435 粉丝
宣传栏