《MySQL必知必会》学习笔记——使用子查询、联结表、创建高级联结

本文链接：https://blog.csdn.net/qq_42325416/article/details/108004143

文章目录

第14章使用子查询
第15章联结表
第16章创建高级联结

第14章使用子查询

1. 子查询

SQL允许创建子查询，即嵌套在其他查询中的查询。

2. 使用子查询进行过滤

现在，假如需要列出订购物品TNT2的所有客户，可以有如下使用子查询：

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id = 'TNT2');

在SELECT语句中，子查询总是从内向外处理。首先，它执行下面的查询：

SELECT order_num FROm orderitems WHERE prod_id = 'TNT2';

此查询返回两个订单号：20005和20007。然后这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变为：

SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

列必须匹配： 在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于、不等于等。

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

3. 作为计算字段使用子查询

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

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是一个计算字段，它是由圆括号里的子查询建立的。该子查询对检索出的每个客户执行一次。

第15章联结表

1. 联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。

1.1 关系表

在《MySQL必知必会》这本书的样例表中，有一个表只存储供应商信息（vendors表），另一个表存储产品信息（products表）。这样做可以避免把所有信息都存在一个表里，降低了存储信息的耦合性，方便管理信息。

vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。products表只存储产品信息，它除了存储供应商ID外不存储其他的供应商信息。vendors表的主键又被称为products表的外键（foreign key），它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

维护引用完整性： 在使用关系表时，仅在关系列中插入合法的数据非常重要。如果在products表中插入非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。

2. 创建联结

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

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

这条SELECT语句与之前SELECT语句最大的差别是所指定的两个列（prod_name和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。两个表使用WHERE子句正确联结。注意，此处WHERE语句必须使用完全限定列名，不然会出现二义性。

不要忘记WHERE子句： 应该保证所有联结都有WHERE子句，否则将会返回两个表的笛卡儿积。

2.1 内部联结

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

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

在这条SELECT语句中，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。

2.2 联结多个表

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;

性能考虑： 不用联结不必要的表，联结的表越多，性能下降越厉害。此外，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

第16章创建高级联结

1. 使用表别名

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

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

注意，表别名只在查询中使用。与列别名不一样，表别名不返回到客户机。

2. 使用不同类型的联结

之前我们使用了内部联结（等值联结），现在来看另外3种联结。

2.1 自联结

假如发现某物品（其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';

这里使用了表别名，WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

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

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

2.3 外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候需要包含没有关联行的那些行，这种类型的联结称为外部联结。下面给出一个内部联结和外部联结的例子：

-- 内部联结
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id;

输出
在这里插入图片描述

-- 外部联结
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

输出
在这里插入图片描述
可以看到，供应商ID为10002的供应商没有订单，使用内部联结的结果没有将其包含在内，而使用外部联结的结果将其包含在内了。

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

3. 使用带聚集函数的联结

聚集函数也可以和联结一起使用。如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

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;