day7 -- 子查询和表联结

学习内容

了解子查询的概念
学习子查询的语句
作为计算字段使用子查询
表联结

---

---

brief

• 所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句

• SQL还允许创建子查询（ subquery） ，即嵌套在其他查询中的查询。
  MySQL 4.1引入了对子查询的支持，所以要想使用本章描述的SQL，必须使用MySQL 4.1或更高级的版本。

• 该处演示的数据库来自《Mysql必知必会》的样例表，下面将简要描述下其中的三张表：
  customers表 存储客户信息，包括 客户的ID，作为主键
  orders表存储订单号、客户ID、订单日期的每个订单，订单号作为主键
  orderitems表存储 各订单的物品，包括订单号

---

---

示例内容

• 现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤。
  (1) 检索包含物品TNT2的所有订单的编号。
  (2) 检索具有前一步骤列出的订单编号的所有客户的ID。
  (3) 检索前一步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。
也可以使用子查询来把3个查询组合成一条语句。

select order_num from orderitems where prod_id = "TNT2";
# 上面检索到对应的订单号，然后放在下一个select语句
select cust_id from customers where order_num in (20005,20007);

# 现在，把第一个查询（返回订单号的那一个）变为子查询组合两个查询
select cust_id 
from customers 
where order_num in (select order_num 
					from orderitems 
					where prod_id = "TNT2";);

# 在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时， MySQL实际上执行了两个操作。

# 下一步是检索这些客户ID的客户信息
select cust_name,cust_contact from customes where cust_id in (10001,10004);
# 3个select语句结合起来
select cust_name,cust_contact 
from customes 
where cust_id in(select cust_id 
				from customers 
				where order_num in(select order_num 
									from orderitems 
									where prod_id = "TNT2";))

• 作为计算字段使用子查询
  假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中

  为了执行这个操作，遵循下面的步骤。
  (1) 从customers表中检索客户列表。
  (2) 对于检索出的每个客户，统计其在orders表中的订单数目。

select cust_name,cust_state,
(select count(*) from orders where orders.cust_id = customers.cust_id) AS orders
from customers
order by cust_name;

这 条 SELECT 语 句 对 customers 表 中 每 个 客 户 返 回 3 列 ： cust_name、 cust_state和orders。 orders是一个计算字段， 它是由圆括号中的子查询建立的。
该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出了5个客户。

• 表联结
  如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？
  答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结

  通常表中的每一行都有一个唯一标识用于标识这一行数据，称为主键，
  在这个表中的主键数据可以放到其他表中作为普通列数据，那么在其他表中称为外键，
  主键和外键是联结实现成功的内在数据因素。

比如下面的示例代码：

select vend_name,prod_name,prod_price 
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;

此处select 所指定的两个列（prod_name和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。

现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM 子句列出了两个表，分别是vendors和products。它们就是这条SELECT 语句联结的两个表的名字。这两个表用WHERE子句正确联结， WHERE子句 指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

上述联结方式称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。
对于这种联结可以使用稍 微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据：

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;

此语句中的SELECT与前面的SELECT语句相同，但FROM子句不 同。
这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。
在使用这种语法时，联结条件用特定的ON子句而不是WHERE 子句给出。传递给ON的实际条件与传递给WHERE的相同。

• 联结多个表
  SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

select vend_name,prod_name,prod_price,quantity
from vendors,products,orderitems
where vendors.vend_id = products.vend_id and
orderitems.prod_it = products.prod_id
order by vend_name,prod_name;

---

---

• 其他类型的联结方式

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

# 首先看一下 子查询
select prod_id,prod_name 
from products 
where vend_id = (select vend_id from products where prod_id = 'DTNR');

# 然后使用联结的方式解决
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 = 'DTNR'; 

products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名进行联结，这种方式就是自联结。

2.外部联结
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行
联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

select vend_name,prod_name,prod_price 
from vendors left outer join products
on vendors.vend_id = products.vend_id
order by vend_name,prod_name;

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