数据库高阶

---

一、子查询与表连接？

1.子查询(嵌套sql)

SELECT语句句是SQL的查询。迄今为⽌止我们所看到的所有SELECT语句句都是简单查询，即从单个数据
库表中检索数据的单条语句句。
SQL还允许创建⼦子查询（subquery），即嵌套在其他查询中的查询。
利利⽤用⼦子查询进⾏行行过滤
订单存储在两个表中。对于包含订单号、客户ID、订单⽇日期的每个订单，orders表存储⼀一⾏行行。 各订单的
物品存储在相关的orderitems表中。orders表不不存储客户信息。它只存储客户的ID。
实际的客户信息存储在customers表中。
现在，假如需要列列出订购物品TNT2的所有客户，应该怎样检索？

--(1) 检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
--(2) 检索具有前⼀一步骤列列出的订单编号的所有客户的ID
select cust_id from orders where order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
--(3) 检索前⼀一步骤返回的所有客户ID的客户信息。
select cust_name,cust_contact from customers where cust_id in (10001,10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

可以把其中的WHERE⼦子句句转换为⼦子查询⽽而不不是硬编码这些SQL返回的数据：

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 |
+----------------+--------------+
--为了了执⾏行行上述SELECT语句句，MySQL实际上必须执⾏行行3条SELECT语句句。
--最⾥里里边的⼦子查询返回订单号列列表，此列列表⽤用于其外⾯面的⼦子查询的WHERE⼦子句句。
--外⾯面的⼦子查询返回客户ID列列表，此客户ID列列表⽤用于最外层查询的WHERE⼦子句句。
--最外层查询确实返回所需的数据。

这⾥里里给出的代码有效并获得所需的结果。
但是，使⽤用⼦子查询并不不总是执⾏行行这种类型的数据检索的最有效的⽅方法。

2.作为计算字段使⽤用⼦子查询

使⽤用⼦子查询的另⼀一⽅方法是创建计算字段。

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列列表。
select cust_id,cust_name from customers ;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬目。
select count(*) as orders from orders where cust_id = 10001;
+--------+
| orders |
+--------+
| 2 |
+--------+

为了了对每个客户执⾏行行COUNT() 计算，应该将 COUNT()作为⼀一个⼦子查询。

select cust_id,cust_name,
(select count(*)
from orders
where orders.cust_id = customers.cust_id) as orders
)
from customers
order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name | orders |
+---------+----------------+--------+
| 10001 | Coyote Inc. | 2 |
| 10005 | E Fudd | 1 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 6 |
| 10004 | Yosemite Place | 1 |
+---------+----------------+--------+

orders是⼀一个计算字段，它是由圆括号中的⼦子查询建⽴立的。该⼦子查询对检索出的每个客户执⾏行行⼀一次。在此例例⼦子中，该⼦子查询执⾏行行了了5次，因为检索出了了5个客户。
**注意:**⼦子查询中的WHERE⼦子句句与前⾯面使⽤用的WHERE⼦子句句稍有不不同，因为它使⽤用了了完全限定列列名这种类型的⼦子查询称为相关⼦子查询。任何时候只要列列名可能有多义性，就必须使⽤用这种语法（表名和列列名由⼀一个句句点分隔）。因为有两个cust_id列列，⼀一个在customers中，另⼀一个在orders中，需要⽐比较这两个列列以正确地把订单与它们相应的顾客匹配。如果不不完全限定列列名，MySQL将假定你是对orders表中的cust_id进⾏行行⾃自身⽐比较。

3.关系表

SQL最强⼤大的功能之⼀一就是能在数据检索查询的执⾏行行中联结（join）表。
在能够有效地使⽤用联结前，必须了了解关系表以及关系数据库设计的⼀一些基础知识。

--假如有⼀一个包含产品⽬目录的数据库表，其中每种类别的物品占⼀一⾏行行。
--对于每种物品要存储的信息包括产品描述和价格，以及⽣生产该产品的供应商信息。
产品表：
产品，描述，价格，供应商名称，供应商地址,供应商联系⽅方式
A6 ... ... 奥迪 ... ....