SQL查询（专篇）

1.使用子查询

子查询：即嵌套查询

1.1利用子查询进行过滤

例子，假如现在需要列出订购物品TNT2的所有客户，应该如何检索？

（1）检索包含TNT2的所有订单编号

select order_num
from orderitems
where prod_id = 'TNT2';

（2）检索具有前一步骤列出的订单编号的所有用户ID

select cust_id
from orders
where order_num IN (20005,20007)

那么以上两个步骤其实可以组合起来，将第一个查询嵌套到第二个里面

select cust_id
from orders
where order_num IN(
    select order_num
    from orderitems
    where prod_id = 'TNT2'
);

（3）检索前一步骤返回所有用户ID的客户信息

select cust_name,cust_contact
from customers
where cust_id IN (10001,10004);

那么其实第三步也可以继续嵌套

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'
)
);

对于嵌套数目没有限制，但是考虑性能，不宜嵌入过多

1.2作为计算字段使用子查询

使用子查询的另一种方法是创建计算字段。假设需要显示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;

orders.cust_id = customers.cust_id，这里用来完全限定列名，相关子查询：涉及外部查询的子查询。

2.联结表（多表联查）

外键（foreign key）：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

2.1创建联结

联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。

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

where子句：应该保证所有联结都有where子句，否则MySQL将返回比想要的数据多得多的数据，务必保证where子句的正确性。

2.2内部联结(INNER JOIN)

前面所用的联结称为等值联结，它基于两个表之间的相等测试这种联结也称内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。如下：

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;

通过inner join……on……实现

2.3联结多个表

SQL对于一条select语句中可以联结的表的数目没有限制。创建联结表的规则也基本相同。

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

性能考虑：联结的表越多，性能下降越厉害

3.创建高级联结

3.1使用表别名

select cust_name,cust_contact
from customers AS c,orders AS o,orderitems AS oi
where c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'TNT2';

3.2使用不同联结

3.2.1自联结

如前所述，使用表别名的主要原因之一是能在单条select语句中不止一次引用相同的表。下面举一个例子：

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他商品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

法一（子查询嵌套）：

select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_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';

3.2.2自然联结

无论何时对表进行联结，应该至少有一列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *）,对所有其他表的列使用明确的子集来完成。例子：

select c.*,o.order_num,o.order_date,
		oi.prod_id,oi.quantity,oi.item_price
from customers AS c,orders AS o,orderitems AS oi
where c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'FB';

正常情况下，内部联结都是使用自然联结

3.2.3外部联结（LEFT\RIGHT JOIN 左联结和右联结）

联结包含了那些在相关表中没有关联的行，这种类型的联结被称为外部联结。

例子：检索所有客户及其订单

法一（内部联结）：

select customers.cust_id,orders.order_num
from customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

但是该结果不包括NULL

法二（外部联结）：

select customers.cust_id,orders.order_num
from customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

分析：

类似于上一章节所看到的内部联结，这条select语句使用了关键字OUTER JOIN来指定联结的类型（而不是在where子句中来指定）。但是，与内部联结关联两个表不同的是，外部联结还包括没有关联的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定其包括所有行的表**（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。**上面例子使用LEFT OUTER JOIN从from句子的左边表（customers表）中选择所有行。为了从右边的表选择所有行，应该使用RIGHT OUTER JOIN，如下：

select customers.cust_id,orders.order_num
from customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;

外部联结的类型：左联结和右联结，它们之间唯一差别是所关联的表顺序不同。换句话说，左外部联结可以通过from或where子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而决定。

3.3使用带聚集函数的联结

检索所有客户及每个客户所下的订单数

select customers.cust_name,
		customers.cust_id,
		COUNT(orders.order_num) AS num_ord
from customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
group by customers.cust_id;

select customers.cust_name,
		customers.cust_id,
		COUNT(orders.order_num) AS num_ord
from customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
group by customers.cust_id;

3.4使用联结和联结条件

• 注意所使用的联结类型，一般我们使用内部联结，但是使用外部联结也是有效的。

4.组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条select语句。MySQL也允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回。这些组合查询通常称为**并（union）**或复合查询。

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

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

组合查询和多个where实现的效果大多数时候是一致的，但是性能有所差异，下面会说到。

4.1创建组合查询（使用UNION）

可用UNION操作符来组合数条SQL查询。利用UNINO，可以给出多条select语句，将它们的结果组合成单个结果集。

例子：假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002所生产的物品（不考虑价格）

创建UNION涉及编写多条select语句，所以先看单条语句怎么写：

select vend_id,prod_id,prod_price
from products
where prod_price <= 5;

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

作为对比，现给出多个where的查询方法：

select vend_id,prod_id,prod_price
from products
where prod_price <= 5
OR vend_id IN (1001,1002);

在这个简单的例子中，使用UNION可能比where更为复杂，但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据，使用UNION可能会使得处理更加简单。

4.2UNION使用规则

• 两条或者两条以上select
• 每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容

4.3包含或取消重复的行

UNION从查询结果集中自动去除重复的行，这是UNION的默认行文，但如果需要改变，可以改变它。

**如果想匹配所有的行，使用UNION ALL。**在使用多个where子句查询时，也会自动去除重复的行，因此UNION ALL就显得非常重要，可以匹配所有行。

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

4.4对组合查询结果排序

使用union组合查询时，只能使用一条order by子句，并且必须出现在最后一条select语句之后。（对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条order by子句）

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_price;

4.5UNION小结

上述关于union的操作都只是组合同一张表的数据，但实际上union是可以组合不同的表。