Mysql语法之使用子查询和联结表

一、使用子查询

迄今为止我们所看到的的所有select语句都是简单查询，即从单个数据库表中检索数据的单条语句。

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

二、利用子查询过滤

订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers中。

现在需要列出订购物品TNT2的所有客户：

1. 检索包含物品TNT2的所有订单的编号
2. 检索具有前一步骤列出的订单标号的所有客户的ID。
3. 检索前一步骤返回的所有客户ID的客户信息。

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

mysql>select order_num from orderitems where prod_id=‘TNT2’; 得到20005,20007

mysql>select cust_id from orders where order_num in (20005,20007); 得到10001,10004

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

也可以使用子查询：

mysql>select cust_id from orders where order_num IN (select order_num from orderitems where prod_id=‘TNT2’);

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

三、作为计算字段使用子查询

mysql>select cust_name,cust_state , (select count(*) from orders where orders.cust_id= customers.cust_id) AS orders

from customers

order by cust_name;

四、联结

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

举个例子：这里两张表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）,可以是供应商ID或任何其他唯一值。

products 表只存储产品信息，它除了存储供应商ID（venders 表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

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

如果数据存储在多个表中，怎样用单挑SELECT语句检索出数据？答案就是使用联结。

五、创建联结

十分简单，规定要联结的所有表以及它们如何关联即可。

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

现在考察一下此代码：

• 所指定的两个列(prod_name,prod_price)在一个表中，而另一个列(vend_name)在另外一个表中
• form子句列出了两个表
• where子句指示MYSQL匹配vendors表中的vend_id和products表中的vend_id。

由此可得出where语句的重要性

应该保证所有的联结查询都有where子句，否则就成为了笛卡尔积。

mysql>select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

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

来看看内部联结

目前为止所用的联结称为等值联结(equijoin)，它基于两个表之间的相等测试。这种联结也称为**内部联结。**

对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

mysql>select vend_name, prod_name, prod_price from vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

联结多张表其实也就不言而喻。