MySQL分组查询与子查询 + MySQL表的联结操作

1 MySQL分组查询与子查询

1.1 数据分组查询

1.2 过滤分组

1.3 分组结果排序

1.4 select语句中子句的执行顺序

1.5 子查询

2 MySQL表的联结操作

2.1 关系表

2.2 表联结

2.3 笛卡尔积

2.4 内部联结

2.5 外联结

2.6 自联结

2.7 组合查询

1 MySQL分组查询与子查询

1.1 数据分组查询

分组允许把数据表中的数据按照某个字段，分为多个组，字段值相同的为一组。分组是为了便于对每个组进行聚集计算；

分组是在select语句的group by子句中建立；

注意：group by只是创建分组，但并不保证分组里面的数据的排列顺序，需要使用order by子句对分组里面的数据进行排序。

使用group by语句对select查询的结果进行分组，以便统计：

select语句指定了两个列，vend_id为产品供应商的ID，prod_num为计算字段（使用count（）函数）；

group by子句按vend_id分组数据，这导致对每个vend_id而不是整个表计算prod_num一次，从而按照vend_id为单位，来计算每个组的总和；

注意：group by子句必须出现在where子句之后，order by子句在group by之后。where XXX group by YYY order by ZZZ

1.2 过滤分组

除了能用group by分组数据外，MySQL还允许对分组指定条件，规定包含哪些分组，排除哪些分组；

MySQL使用having子句来完成该操作；

where子句过滤指定的行，having子句过滤指定的分组。

如，过滤总数大于等于2的分组：

where与having同时使用，列出具有2个（含）以上，价格为10（含）以上的产品的供应商：

1.3 分组结果排序

select后面只能查看group by子句后有的列；
group by可以根据多个列进行分组，多个列分组即对多个列的组合值相同的进行分组；

1.4 select语句中子句的执行顺序

select语句中使用的子句必须按照一定的次序，下图列出来各个子句在select语句中出现的次序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

1.5 子查询

前面我们讲的所有查询都是在单个数据库表中执行；
SQL还允许建立子查询，即嵌套在其他查询中的查询；
MySQL从4.1版本开始支持子查询，要使用子查询必须使用MySQL4.1或更高版本。

订单存储在两个表中：orders保存订单号，客户ID、订单日期；

各订单的物品信息保存在orderitems表中，order表不保存客户信息，它只保存客户的ID。实际的客户信息存储在customers表中；

如果需要列出订购物品编号TNT2的所有客户姓名，可以采用下列步骤：

查询包含物品TNT2的所有订单的编号；
查找前一步骤列出的订单编号的所有客户的ID；
查找前一步返回的所有客户ID的客户姓名；

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

方法一：用多个查询语句实现

（1）查找包含物品TNT2的所有订单的编号

（2）查找前一步骤列出的订单编号的所有客户的ID

（3）查找前一步列出的客户ID的客户姓名及其他信息

方法二：用子查询把多个查询语句组合起来

使用子查询语句完成

在Where子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。比子查询更优的查询方法是联结。

2 MySQL表的联结操作

2.1 关系表

为什么需要关系表？

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

现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如：供应商名、地址、联系方式等）呢？将这些数据与产品信息分开存储还是存储在同一张表里呢？通常的原则是分开存储，理由：

因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间
如果供应商信息改变（如：供应商搬家或电话号码变动，只需要改动一次即可）
如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同，不一致的数据在报表中很难利用。

在这个例子中，可建立两个表，一个存储供应商信息（vendors表），另一个存储产品信息（products表）。

关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

2.2 表联结

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

数据是存储在关系表中的，关系表的设计原则是保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值互相关联。

2.3 笛卡尔积

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

2.4 内部联结

创建链接：

where子句：

在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中并不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情；
在联结两个表时，你实际上做得是将第一个表中的每一行与第二个表中的每一行配对；
Where子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有Where子句，第一个表中的每个行将与第二个表中的每个行配对，而不管逻辑上是否可以配在一起。

上述语句中的select与前面的select语句相同，但from子句不同，这里两个表之间的关系是from子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是where子句。传递给ON的实际条件与传递给where的相同。

2.5 外联结

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

统计每个客户下了哪些订单，包括那些至今尚未下订单的客户；
列出所有产品以及订购数量，包括没有人订购的产品；

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

外部联结分为左外部联结和右外部联结；

左外部联结：以左边表为基准，按照过滤条件查找右边表的记录，如果匹配到，那么就组合成一行，并显示结果，如果没有匹配到，那么只显示左边表的字段，右边表中不存在的字段用空值NULL来表示。

右外部联结：作用和左联结刚好相反，以右边表为基准，去匹配左边的表，如果左边表字段为空，那么就用空值NULL来表示。

左右外连接实例：

2.6 自联结

自联结为在同一个表中做联结操作；

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

效率高于子查询

连结的注意事项：

注意所使用的联结类型，一般我们使用内部联结，但使用外部联结也是有效的；
保证使用正确的联结条件，否则将返回不正确的数据；
应该总是指定联结条件，否则会得到笛卡尔积；
在一个联结中可以包含多个表，甚至对每个联结可以采用不同的联结类型，虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结，这样使故障排除更为简单。