MYSQL必知必会 再捋[13-16]（三）

(十三) 分组数据

1 数据分组

SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 10003

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

 

2 创建分组

分组是在SELECT语句的GROUP BY子句中建立：

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

 

GROUP BY 子句前使用的一些重要规定（认真阅读）：

1 GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。（不能从个别的列取回数据）。
3 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
4 除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
5 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
6 GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

 

注意：使用ROLLUP 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值

SELECT vend_id, COUNT(*) AS num_prods FROM products

GROUP BY vend_id WITH ROLLUP;

 

3 过滤分组

WHERE 和 HAVING 的区别：

WHERE 用来过滤行  而 HAVING过滤分组

WHERE 在数据分组前进行过滤

HAVING在数据分组后进行过滤

WHERE排除的行不包括在分组中，这可能会改变计算值，从而影响HAVNG子句中基于这些值过滤掉的分组。

用法一致

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

 

返回过去12个月内具有两个以上订单的顾客：

SELECT vend_id, COUNT(*) AS num_prods

FROM prod_price >=10

WHERE prod_price >= 10

GROUP BY vend_id

HAVING COUNT(*) >=2;

 

4 分组和排序

ORDER BY :

1 排序产生的输出

2 任意列都可以使用（甚至非选择列也可以使用）

3 不一定需要

GROUP BY

1 分组行，但输出可能不是分组的顺序

2 只可能使用选择列或表达式列，而且必须使用每个选择列表达式

3 如果与聚集函数一起使用列（或表达式） 则必须使用

 

注意事项：

不要忘记ORDER BY 一般在使用GROUP BY子句时，应该也给出ORDER BY 子句，这是保证数据正确排序的唯一办法。千万不要仅依赖GROUP BY排序数据

 

SELECT order_num,

SUM(quantity * item_price) AS ordertota

FROM orderitems

GROUP BY order_num

HAVING SUM(quantity*item_price) >= 50;

 

5 SELECT 子句顺序

重点啊  好好看

SELECT    要返回的列或表达式   必须使用

FROM    从中检索数据的表     仅在从表选择数据时使用

WHERE    行级过滤             否

GROUP BY    分组说明          仅在安祖计算聚集时使用

HAVING      组级过滤          否

ORDER BY    输出排序顺序      否

LIMIT         要检索的行数      否

 

（十四章） 使用子查询

1 子查询

SELECT 语句时sql的查询

查询： 任何sql语句都是查询

SQL还允许创建子查询（subquery）即嵌套在其他查询中的查询

需要列出订购物品TNT2的所有客户。检索步骤

（1）检索包含物品TNT2的所有订单的编号。

SELECT order_num

FROM orderitems

WHERE prod_id = ‘TNT2’;

(2) 检索具有前一步骤列出的订单编号的所有客户的ID。

SELECT cust_id

FROM orders

WHERE order_num IN (20005,20007);

(3) 检索前一步骤返回的所有客户ID的客户信息。 

SELECT cust_id

FROM orders

WHERE order_num IN （

SELECT order_num

FROM orderitems

WHERE prod_id = ‘TNT2’

）;

在SELECT语句中，子查询总是从内向外处理，在处理上面的SELECT语句时，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’ 

)）;

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

子查询和性能：这里给出的代码有效并获得所需的结果，但是，使用查询并不总是执行这种类型的数据检索的最有效的方法。

 

2 最为计算字段使用子查询

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

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

订单与相应的客户ID存储在orders表中。

步骤：

1 从customers表中检索客户列表

2 对于检索出的每个客户，统计其在orders表中的订单数目。

对客户1001的订单进行计数：

SELECT COUNT(*) AS orders

FROM orders

WHERE cust_id = 10001

 

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为子查询

SELECT cust_name,

  cust_state,

  (SELECT COUNT(*)

  FROM orders

  WHERE orders.cust_id = customers.cust_id)

AS orders

FROM customers

ORDER BY cust_name;

 分析：（记住完全限定列名）

WHERE orders.cust_id = customers.cust_id)

比较orders表中的cust_id 与当前从customers表中检索的cust_id。

 

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

逐渐增加子查询来建立查询

用子查询建立（和测试）查询的最可靠的方法时捉奸进行，这与mysql处理他们的方法非常相同，首先，建立和测试最内层的查询，然后，用硬编码数据建立和测试外层查询，并仅在确认他正常后才嵌入子查询。

 

（十五） 联结表

1 联结

1.1 关系表

Vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。即主键 （primary key）

 

Products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商的信息。

Vendors 表的主键又叫做products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值。

优点：

供应商的信息不重复，不浪费时间和空间

如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动

由于数据不重复，显然数据是一致的，这使得处理数据更简单

 

可伸缩性： 能够适应不断增加的工作量而不失败。设计良好的数据或应用程序称之为可伸缩性好（scale well）

 

1.2 为什么要使用联结

分解数据为多个表能更有效的存储，更方便地处理，并且具有更大的可伸缩性，但都是有代价的。

 

数据存储在多个表，怎样用单条SELECT语句检索出数据？

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

 

维护引用完整性：

联结由MYSQL根据需要建立，存在于查询的执行当中。

在使用关系表时，仅在关系列中插入合法的数据非常重要 。（不允许插入非法数据，即没有关联的数据）

 

2 创建联结

 

SELECT vend_name, prod_name, prod_price

FROM vendors, products

WHERE vendors.vend_id  = products.vend_id

ORDER BY vend_name, prod_name;

分析：关键所在  WHERE子句知识MYSQL匹配vendors表中的vend_id 和products表中的vend_id.（完全限定列名）

 

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

 

3 WHERE 子句的重要性

联结两个表： 实际上是将第一个表中的每一行与第二表中的每一行配对。

WHERE子句作为过滤条件，它只包含那些匹配给定条件的行。

没有WHERE子句，第一个表中的每个行将于第二个表中的每个行配对，而不管逻辑上是否可以配在一起。

 

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

 

 

无WHERE演示：

SELECT vend_name, prod_name, prod_price

FROM vendors, products

ORDER BY vend_name, prod_name;

 

不要忘了WHERE子句： 应该保证所有的连接都有WHERE子句，否则会返回很多不正确的数据。同理保证WHERE子句的正确性。不正确的过滤条件将导致MYSQL返回不正确的数据。

 

叉联结：有时我们会听到返回称为叉联结的笛卡尔积的联结类型。

 

4 内部联结

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

不同的语法实现：

SELECT vend_name, prod_name, prod_price

FROM vendors INNER JOIN products

ON vendors.vend_id = products.vend_id;

 

使用哪种语法 ANSI SQL规范首选INNER JOIN语法。此外，
尽管使用WHERE子句定义联结的确比较简单，但是使用明确的
联结语法能够确保不会忘记联结条件，有时这样做也能影响性能。

 

5 联结多个表

步骤：首先列出所有表，然后定义表之间的关系

SELECT prod_name, vend_name, prod_price, quantity

FROM orderitems, products, vendors

WHERE products.vend_id = vendors.vend_id

AND orderitems.prod_id = products.prod_id

AND order_num = 20005

 

性能考虑： 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’

)

);

使用联结查询：

SELECT cust_name, cust_contact

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id

AND orderitems.order_num = orders.order_num

AND prod_id = ‘TNT2’;

 

(十六) 创建高级联结

1 使用表别名

SELECT Concat(RTrim(vend_name),

‘(’, RTrim(vend_country)), ‘)’) AS vend_title

FROM vendors

ORDER BY ven_name

别名除了用于列名和计算字段外，SQL还允许给表名起别名。

主要理由：

缩短SQL语句

允许在单条SELECT语句中多次使用相同的表

 

SELECT cust_name, cust_contact

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 = ‘TNT2’;

 

注意事项： 表列名只在查询行中使用，与死别名不一样，表别名不返回到客户机。

 

2 使用不同类型的联结

学习过了等值联结（内部联结）

还有三种联结： 自联结  自然联结  外部联结

2.1 自联结

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

查询ID为DTNTR的供应商生产的所有物品

使用子查询

SELECT prod_id, prod_name

FROM products

WHERE vend_id = (

SELECT vend_id

FROM products

WHERE  prod_id = ‘DTNTR’);

 

 

 

使用联结查询

SELECT p1.prod_id,p1.prod_name

FROM products AS p1, products AS p2

WHERE p1.vend_id = p2.vend_id

AND p2.prod_id = ‘DTNTR’;

分析：products出现两次别名p1, p2

SELECT语句    P1前缀明确给出所需列的全名

如果不这样，mysql将返回错误，因为分别存在两个名prod_id,prod_name的列。Mysql不知想要的是哪一个。

WHERE首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

 

用自联结而不用子查询： 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果是相同的，但是有时候处理联结远比处理子查询快得多，应该试一试两种方法，以确定哪一种性能更好。

 

2.2 自然联结

对表联结，至少有一个列出现在不止一个表中

标准的联结返回所有的数据，甚至相同的列多次出现

自然联结排除多次出现，使每个列只返回一次

 

自然联结：由你自己完成，只能选择那些唯一的列，这一版是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成。

 

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

分析： 通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来。

迄今为止我们建立的每个内部联结都是自然联结，很可能
我们永远都不会用到不是自然联结的内部联结

 

2.3 外部联结

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

举例：

对每个客户下了多少订单进行计数，包括至今尚未下订单的客户；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的客户。

 

检索所有客户及其订单：

SELECT customers.cust_id, orders.order_num

FROM customers INNER JOIN orders

ON customers.cust_id = orders.cust_id;

 

外部联结语法（LEFT OUTER JOIN ...ON）为了检索所有客户，包括那些没有订单的客户。

SELECT customers.cust_id, orders.order_num

FROM customers LEFT OUTER JOIN orders

ON customers.cust_id = orders.cust_id;

 

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字
指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，
而LEFT指出的是OUTER JOIN左边的表）。

 

SELECT custoemrs.cust_id, orders.order_num

FROM custoemrs RIGTH OUTER JOIN orders

ON orders.cust_id = customers.cust_id;

 

重点注意：

没有*=操作符 MySQL不支持简化字符*=和=*的使用，这两
种操作符在其他DBMS中是很流行的。

 

外部联结的类型

两种基本的外部联结形式：左外部联结和右外部联结。

唯一差别是所关联的表的顺序不同

左外部联结可通过颠倒FROM或WHERE子句中

表的顺序转换为右外部联结

因此，两种类型的外部联结可互换使用

 

3 使用带聚集函数的联结

聚集函数用来汇总数据

SELECT customers.cust_name,

        Customers.cust_id,

  COUNT(orders.order_num) as num_ord

FROM customers INNER JOIN orders

ON customers.cust_id = orders.cust_id

GROUP By customers.cust_id;

分析：此SELECT语句使用INNER JOIN将customers和orders表互相关联。
GROUP BY 子 句 按 客 户 分 组 数 据 ， 函 数 调 用 COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

SELECT customers.cust_name,

Customers.cust_id,

COUNT(orders.order_num) AS num_ord

FROM customers LEFT OUTER JOIN orders

ON customers.cust_id = orders.cust_id

Group by customers.cust_id;

分析：这个例子使用左外部联结来包含所有客户，甚至包含那些没有
任何下订单的客户。结果显示也包含了客户Mouse House，它
有0个订单

 

4 使用联结和联结条件

汇总一下关于联结及其使用的某些要点。

1 注意所使用的联结类型。一般我们使用内部联结，但使用外部联
结也是有效的。

2 保证使用正确的联结条件，否则将返回不正确的数据。

3 应该总是提供联结条件，否则会得出笛卡儿积。
4 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。