mysql 子查询与连接表详情

1、什么是子查询？

列出订购物品TNT2的所有客户：

select cust_idfrom orderswhere order_num IN (SELECT order_numfrom orderitemswhere prod_id = 'TNT2')

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

注:

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

除了子查询可以放在where 中，还可以放到select中去。

假如需要显示customers表中每个客户的订单总数。

select cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) as ordersfrom customersORDER BY cust_name

逐渐增加子查询来建立查询用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性

下面介绍一下联结:

SELECT vend_name,prod_name,prod_priceFROM vendors,productsWHERE  vendors.vend_id=products.vend_idORDER BY vend_name, prod_name

完全限定列名在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。

这里使用where 语句进行联接的作用:

利用WHERE子句建立联结关系似乎有点奇怪，但实际上，有一个很充分的理由。请记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

注:

笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

下面的SELECT语句返回与前面例子完全相同的数据：

SELECT vend_name,prod_name,prod_priceFROM vendors INNER JOIN products on vendors.vend_id = products.vend_idORDER BY vend_name, prod_name

性能考虑 MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结
不必要的表。联结的表越多，性能下降越厉害。

多做实验正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能
会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找
出最适合具体情况的方法。我们同样可以使用多张表的联接，但是有一个问题，因为表名多个地方使用，故而表名很长，那么可以使用表的别名。

如：

下面介绍一下几种特殊的连接。

2、自联接

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

下面是解决此问题的一种方法：

你可能使用子查询，这样做:

select prod_id,prod_namefrom productswhere vend_id = (SELECT vend_id from products WHERE prod_id ='DTNTR')

select t1.prod_id,t2.prod_namefrom products t1, products t2where t1.vend_id = t2.vend_id and t1.prod_id='DTNTR'

3、自然联接

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

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT * ）对所有其他表的列使用明确的子集来完成的。

4、外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：

比如：对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

SELECT customers.cust_id,order_numfrom customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

使用带聚集函数的联结:

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

SELECT customers.cust_id, COUNT(order_num) as numfrom customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_idGROUP BY cust_id

到此这篇关于mysql 子查询与连接表详情的文章就介绍到这了,更多相关mysql 子查询与连接表内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

1、什么是子查询？

2、自联接

3、自然联接

4、外部联结

更多相关文章

随机推荐