《MySQL必知必会》学习笔记七(复杂查询)------掌握部分

MySQL必知必会知识预览
第一章——了解SQL
第二章——MySQL简介
第三章——使用MySQL
第四章——检索数据
第五章——排序检索数据
第六章——过滤数据
第七章——数据过滤
第八章——用通配符进行过滤
第九章——用正则表达式进行搜索
第十章——创建计算字段
第十一章——使用数据处理函数
第十二章——汇总数据
第十三章——分组数据
第十四章——使用子查询
第十五章——联结表
第十六章——创建高级联结
第十七章——组合查询
第十八章——全文本搜索
第十九章——插入数据
第二十章——更新和删除数据
第二十一章——创建和操纵表
第二十二章——使用视图
第二十三章——使用储存过程
第二十四章——使用游标
第二十五章——使用触发器
第二十六章——管理事务处理
第二十七章——全球化和本地化
第二十八章——安全管理
第二十九章——数据库维护
第三十章——改善性能

————————————– 华丽的分隔符 ————————————————–

第十四章子查询

子查询版本要求：MySQL4.1引入了对子查询的支持。

查询：任何SQL都是查询。但是此术语一般指select语句。
子查询：嵌套在其他查询中的查询。

利用子查询进行过滤

思考：假如需要列出订购物品TNT2的所有客户，应该怎么检索？

————————————–————————————–————————————–————————————–

1）检索包含TNT2的订单编号；
2）检索具有第一步骤列出的订单编号的所有客户的ID ；
3）检索前一步骤返回的所有客户ID的客户信息；

上述每一个步骤都可以进行单独查询，可以把一条select语句返回的结果用于另外一条select语句的where子句。
select order_num from orderitems where prod_id = ‘tnt2’;
select cust_id from orders where order_num IN(20005,20007);
select cust_name,cust_contact from customers where cust_id IN(10001,10004); // 属于硬编码ID

可以使用子查询把3个查询组合成一条语句

将1）2）联合起来
select cust_id from orders where order_num IN(select order_num from orderitems where prod_id = ‘tnt2’);

将1）2）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’));

子查询总是从内向外处理。为了执行上述子查询，MySQL实际上必须执行3条select语句，最里面的子查询返回订单号列表，此列表用于其外面的子查询的where子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的where子句。最外层查询确实返回所需要的数据。

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

使用子查询，一般与IN操作符结合使用，但可以用于测试相等(=)、不相等(<>)等。

作为计算字段使用子查询

思考：检索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;

select语句对customers表中每个客户返回3列，cust_name,cust_state，orders ，orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索出5个客户。

在子查询中，一般使用完全限定名，必须注意有歧义的列名。

用子查询测试和调试很有技巧性，特别是这些语句的复杂性不断增加的情况下更是如此，用子查询建立(和测试)查询的最可靠的方法就是逐渐进行，这与MySQL处理他们的方法非常相同，首先，建立和测试最内层的查询，然后，用硬编码数据建立和测试外层查询，并且仅在确定它完全正常后才嵌入子查询。

第十五章联结表

等值联结

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

创建联结非常简单：只需要规定要联结的表以及他们如何关联即可。

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

select语句与前面的所有语句一样，指定要检索的列，这里，最大的差别是所指定的两个列(prod_name和prod_price)在一个表中，而另一个列(vend_name)在另外一个表中。

黑体部分则是给出了select语句要联结的两个表的名字，用where子句正常联结。where子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
完全限定名：在肯能引起二义性时，必须使用完全限定名

联结表中where子句的重要性
在一条select语句中，要联结几个表时，相应的关系时在运行中进行构造的，在数据库表的定义中不存在能指示MYSQL如何对表进行联结的东西。你必须自己做这件事情，在联结两个表时，你实际上要做的是将第一个表中的每一行与第二个表中的每一行进行配对。where子句作为过滤条件，他只包含哪些匹配给条件(这里是联结条件)的行。没有where子句，第一个表中的每一行将第二个表中的每个行配对，而不管他们逻辑上是否可以配在一起。这也是笛卡儿积产生的原因。
笛卡儿积
由没有联结条件的表关系返回的结果为笛卡儿积，检索的行数将是第一个表中的行乘以第二个表中的行数

不要忘记where子句
应该保证所有的联结都有where子句，否则MySQL将返回比所需要的多得多的数据，同时，也应该保证where子句的正确性，不正确的过滤条件，将导致MySQL返回不正确的数据。

叉联结返回称为叉联结的笛卡儿积的联结类型。

内部联结

内部联结与等值联结
等值联结是基于两个表之间的相等测试，这种联结也称为内部联结。
但是，对于这种联结，我们可以使用稍微不同的语法进行来明确指定联结的类型。

select vend_name,prod_name,prod_price from vendors inner join products on
vendors.vend_id = products.vend_id order by vend_name;
查询结果同上，利用inner join on子句代替where子句查询，此效率远远大于上面查询。
注意：下面两个图片每次都是重新进行连接数据库下的测试。

注意：首选Inner Join语法。尽管where子句定义联结比较简单，但是使用明确的联结语法能够保证不会忘记联结条件，而且前者的性能也是比较好的。

联结多表

select vend_name,prod_name,prod_price,quantity from vendors,products,orde
ritems where vendors.vend_id = products.vend_id and orderitems.prod_id = product
s.prod_id and order_num=20005 order by vend_name;

将子查询转化为联结查询
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’));// 最开始的例子

select cust_name,cust_contact from customers ，orders ，orderitems where customers.cust_id =orders.cust_id and orders.order_num =orderitems.order_num and orderitems.prod_id=’tnt2’

多做实验：为执行任一给定的sql操作，一般存在不同的方法。性能可能会受操作类型，表中数据量，是否存在索引或键以及一些其他因素的影响。因此，有必要在不同的选择机制进行实验，以找出最适合具体情况的方法。

联结是SQL中最强大最重要的特性，有效地使用联结需要对关系数据库设计有基本的了解。
等值联结是最常用的联结方式。