《MySQL 必知必会》C13-C16

第十三章 分组数据

1 数据分组

上述例子返回了供应商1003提供的产品数目，但如果要返回每个供应商提供的产品数目应该怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商则呢么办？

这时可以使用分组，分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

2 创建分组–group by

分组是在select语句的group by子句中创建的，观察下面示例：

group by子句指示MySQL按vend_id排序并分组数据，group by子句指示MySQL分组数据，然后对每个组而不是整个结果进行聚集

使用group by的一些规定：
1，group by子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供了更精细的控制
2，如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总，即建立分组时所有的列都一起计算
3，group by子句中的每个列都必须是检索列或有效的表达式，不能使用别名
4，除聚集计算语句外，select语句中的每个列都必须在group by子句中给出
5，如果分组列中具有null值，则null将作为一个分组返回，如果有多行null值，则将它们分为一组
6，group by子句必须出现在where子句后，order by子句前

3 过滤分组–having

除了能用group by分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组，例如想要列出至少有两个订单的所有顾客。为了得到这种数据，必须基于完整的分组而不是个别的行进行过滤

过滤分组不能使用where完成任务，因为where过滤的是行而不是分组，where没有分组的概念，为了完成过滤分组的任务，MySQL提供了having子句，having类似于where，where过滤行，having过滤分组

having支持所有的where操作符

having 和 where的区别：where在数据分组前进行过滤，having在数据分组后进行过滤，where排除的行不包括在分组中，这可能影响having子句中基于这些值过滤掉的分组

4 分组和排序

虽然group by和order by经常完成相同的工作，但它们是非常不同的

我们经常发现group by分组的数据确实是以分组顺序输出的，但情况并不总是这样，应该提供明确的order by子句，即使其效果等同于group by子句

一般在使用group by子句时，应该也给出order by子句，这是保证数据正确的唯一做法，千万不要过度依赖于group by排序数据

尽管group by进行了排序，但为了按照总计订单价格排序输出还需要添加order by子句

5 select子句顺序

子句		说明					是否必须使用
select 		要返回的列或表达式		是
from		从中检索数据的表			仅在从表选择数据时使用
where		行级过滤					否
group by	分组说明					仅在按组计算聚集时使用
having		组级过滤					否
order by	输出排序顺序				否
limit		要检索的行				否

第十四章 使用子查询

1 子查询

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

查询：任何SQL语句都是查询，但此术语一般指的是select语句

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

2 使用子查询进行过滤

现在假如需要列出订购物品TNT2的所有客户，因该执行下列的步骤：
1，检索包括物品TNT2的所有订单的编号
2，检索具有前一步骤列出的订单编号的所有客户的ID
3，检索前一步骤返回的所有客户ID的客户信息

上述每个步骤都可以单独作为一个查询来执行，可以把一条select语句返回的结果用于另一条select语句的where子句，也可以使用子查询把3个查询合成一条语句

第一条select语句用于对prod_id为TNT2的所有订单，它检索其order_num列

下一步查询订单20005和20007的客户ID

现在把第一查询作为子查询组合两个查询

在select语句中，子查询总是从内向外处理

下来通过cust_id检索顾客的详细信息

把其中的where子句转换成子查询

对于上述嵌套子查询语句，最里面的子查询返回订单号列表，此列表用于其外面的where子句，外面的子查询返回客户ID列表，此客户ID列表用于外面的where子句，最外层查询返回所需的数据

在where子句中能使用子查询编写出功能很强且很灵活的SQL语句，对于能嵌套的子查询的数目没有限制，不过实际使用时由于性能的限制，不能嵌套太多的子查询，虽然子查询一般与 in 操作符结合使用，但也可以用于测试等于，不等于等操作

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

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

假如需要显示customers中每个客户的订单总数，订单与相应的顾客ID存储在orders表中，为了执行这个操作，需要以下步骤：
1，从customers表中检索出客户列表
2，对于检索出的每个客户，统计其在orders表中的订单数目

相关子查询：涉及外部查询的子查询

orders是一个计算字段，它是由圆括号中的子查询建立的，该子查询对检索出的每个客户执行一个，子查询中的where子句与前面使用的where子句稍有不同，它使用了完全限定列名，告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id: where orders.cust_id = customers.cust_id

这种类型的查询称为相关子查询，任何时候列名可能有多义性，就必须使用这种语法(表名和列名间加一个.)，涉及外部表的子查询必须使用完全限制列名

第十五章 联结表

1 联结

SQL最强大的功能之一就是能在数据检索查询的过程中执行联结，联结是利用SQL的select能执行的最重要的操作

(1) 关系表

理解关系表先来看一个例子：

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行，对于每种物品要存储的信息包括产品的描述和价格，以及生产该产品的供应商信息

现在假如有同一供应商生产的多种物品，那么在何处存储供应商信息(供应商名，地址，联系方式等)，将这些数据与产品信息分开存储的理由如下：
1，因为同一供应商生产每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间
2，如果供应商信息改变，只需改动一次即可
3，如果有重复数据，很难保证每次输入该数据的方式都相同

关键是，相同数据出现多次绝不是一件好事，此因素是关系数据表设计的基础，关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值(关系)互相关联

对上面的例子，可以建立两个表，一个存储供应商信息，一个存储产品信息，vendors表包含所有的供应商，每个供应商占一行，供应商具有唯一的标识，此标识称为主键，可以是供应商ID或任何其它唯一值

products表除了存储产品信息，它还存储供应商ID，vendors表的主键又叫做products的外键，它将products和vendors关联，利用供应商ID能从vendors中找到相应供应商的详细信息

这样做的好处：
1，供应商信息不重复，从而不浪费时间和空间
2，如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动
3，由于数据无重复，使得处理数据更简单
总之，关系数据可以有效地存储和方便地处理，关系数据库地可伸缩性远比非关系数据库要好

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

可伸缩性：能够适应不断增加的工作量而不失败

(2) 为什么使用联结

分解数据为多个表能更有效地存储，更方便地处理，并具有更好的可伸缩性，但这些好处需要代价

如果数据存储在多个表中，怎样使用单条select语句检索数据？

答案是使用联结，联结是一种机制，用来在一条select语句中关联表，使用特殊的语法可以联结多个表返回一组输出，联结在运行时关联表中正确的行

联结在实际的数据库表中并不存在，联结是由MySQL根据需要建立，它存在于查询的执行当中

2 创建联结

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

与先前的select语句不同，这里from出现了两个表vendors和products，它们就是这条select语句联结的两个表的名字，这两个表通过where子句正确联结，where指示MySQL匹配vendors表中的vend_id和products表中的vend_id

这里需要使用完全限制列名，因为如果只给出vend_id，MySQL不知道指的是哪一个，引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误

(1) where子句的重要性

在一条select语句中联结几个表时，相应的关系是在运行中构造的，在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西，必须自己完成这件事

在联结两个表时，实际上是通过where作为过滤条件将第一个表的每一行和第二个表的每一行配对，没有了where子句，第一个表中的每个行都将与第二个表中的每个行直接配对，而不管它们逻辑上是否可以配在一起

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

笛卡尔积的返回数据用每个供应商匹配了每个产品，它包含了供应商不正确的产品，实际上有的供应商根本没有产品

应该保证所有的联结都有where子句，且保证where过滤的正确性

(2) 内部联结

目前使用的联结称为等值联结，它基于两个表之间的相等测试，这种联结也称为内部联结，也可以使用不同的语法完成：

这里两个表之间的关系是from子句的组成部分，以inner join指定，在使用这种语法时，联结条件用特地的on子句而不是where子句给出，传递给on的实际条件与传递给where的相同

使用联结时最好使用明确的xxx inner join xxx on 条件

(3) 联结多个表

SQL对一条select语句中可以联结的表的数目没有限制，创建联结的基本规则也相同，首先列出所有的表，然后定义表之间的关系

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

第十六章 创建高级联结

1 使用表别名

别名除了用于列名和计算字段外，SQL还允许给表起别名这样有两个理由：
1，缩短SQL语句
2，允许在单条select语句中多次使用相同的表

select cust_name, cust_contact
from customers as c, orders as 0, orderitems as oi
where c.cust_id = o.cust_id
  and oi.order_num = o.cust_num
  and prod_id = 'tnt2';

表别名不仅能用于where子句，还可以用于select的列表，order by子句等，表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机

2 使用不同类型的联结

迄今为止，使用的都是内部联结(等值联结)，现在看其它3种联结，自联结，自然联结，外部联结

(1) 自联结

使用表别名的主要原因之一是能在单条select语句种不止一次的引用相同表

假如发现某物品(ID为dinter)存在问题，因此想知道生产该物品的供应商生产的其它物品是否存在类似的问题，因此需要先查询到生产ID为dinter的供应商，再找出这个供应商生产的商品

使用子查询的方式：

使用自联结的方式：

使用自联结方式查询的两个表实际上是完全相同的表，products表在from出现了两次，但由于使用了别名所以避免了出现二义性导致MySQL报错

(2) 自然联结

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

自然联结只能选择那些唯一的列，这一般通过对表使用通配符，对所有其它表的列使用明确的子集完成的

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

这个例子中，通配符只对第一个表使用，所有其他列明确列出，所以没有重复列被检索出来，事实上迄今为止建立的每个内部联结都是自然联结

(3) 外部联结

许多联结将一个表中的行与另一个表中的行关联，但有时会需要包含没有关联行的那些行，如：
1，计算平均销售规模，包括没下单的客户
2，列出所有的产品以及订购数量，包括没人订购的产品

上述例子中，联结了那些在相关表中没有关联的行，这种类型的联结称为外部联结

使用外部联结检索所有客户及没下订单的客户：

outer join来指定联结的类型，与内部联结不同的是外部联结还需要包括没有关联的行，在使用outer join时，必须使用right或left关键字指定包括其所有行的表

3 使用带聚集函数的联结

聚集函数用来汇总数据，但也可以和联结一起使用

此select语句使用inner join将customers和orders表相互关联，group by子句按客户分组数据，函数调用count(orders.order_num)对每个客户的订单计数，将它们作为num_ord返回

4 使用联结和联结条件

联结及其使用的要点：
1，注意所使用的联结的类型，一般使用内部联结，但使用外部联结也是有效的
2，保证正确的联结条件
3，应该总是提供联结条件，避免笛卡尔积
4，一个联结中可以包含多个表，甚至对每个联结都可以采用不同的联结类型，虽然这样是合法的，但在使用它们前，测试好每个联结，这使得排除故障更为简单