MySQL必知必会(四)阅读笔记

第十四章 使用子查询

子查询

例:查询订购物品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);
//3检索上一步所有客户id的全部信息
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (10001,10004);
//将上面3步合并
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'));

作为计算字段使用子查询

显示customers表中每一个客户的订单总数

SELECT cust_id,
		cust_name,
		cust_state,(SELECT count(*)
					FROM orders
					WHERE orders.cust_id = customers.cust_id) AS num_order
FROM customers
ORDER BY num_order

联结表

创建联结

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
优点

1. 供应商信息不重复，从而不浪费时间和空间；
2. 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
3. 由于数据无重复，显然数据是一致的，这使得处理数据更简单。

SELECT vend_name,prod_name,prod_price
FROM vendors v,products p
WHERE v.vend_id = p.vend_id
ORDER BY vend_name,prod_name

笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT vend_name,prod_name,prod_price
FROM vendors v,products p
//将显示笛卡儿积

等值联结

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

SELECT vend_name,prod_name,prod_price
FROM vendors v
INNER JOIN products p ON v.vend_id = p.vend_id
ORDER BY vend_name,prod_name

联结多个表

SELECT prod_name,vend_name,prod_price,quantity
FROM products p, orderitems o,vendors v
WHERE p.vend_id =v.vend_id
	AND o.prod_id = p.prod_id
	AND o.order_num = 20005;

第十六章 创建高级联结

别名除了用于列名和计算字段外， SQL还允许给表名起别名。这样做有两个主要理由：

1. 缩短SQL语句；
2. 允许在单条SELECT语句中多次使用相同的表。

使用不同类型的联结

四种联结:等值联结、自联结、自然联结和外部联结。

自联结

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

SELECT p1.prod_id,p1.prod_name
FROM products p1, products p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

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

外部联结

SELECT c.cust_id,o.order_num
FROM customers c 
LEFT JOIN orders o ON c.cust_id = o.cust_id

使用带聚集函数的联结

SELECT c.cust_id,c.cust_name,COUNT(o.order_num)
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_id

使用联结的要点

1. 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
2. 保证使用正确的联结条件，否则将返回不正确的数据。
3. 应该总是提供联结条件，否则会得出笛卡儿积。
4. 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。