13. 分组数据

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套；嵌套分组时，数据将在最后规定的分组上进行汇总

如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回

除聚集计算语句外，SELECT 的每个列都需要在 GROUP BY 子句中给出

GROUP BY子句必须出现在 WHERE 子句之后，ORDER BY 子句之前

  • 利用GROUP BY子句创建分组

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

  • HAVING子句用于过滤分组

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

注：HAVING 与 WHERE 的区别：WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
// 对 prod_price 不小于 10 的行进行分组

GROUP BY 还可以结合 ORDER BY 语句对输出结果进行排序

SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

小结：依书写顺序列出已经涉及过的子句：

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

14. 使用子查询

子查询是嵌套在其他查询中的查询

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');
// 进行了两个查询
// 子查询可以有任意的深度

子查询应该保证SELECT语句具有与WHERE子句相同数目的列。通常，子查询将返回单个列并且与单个列匹配。

  • 作为计算字段的子查询
    // 相关子查询
SELECT cust_name,
     cust_state,
     (SELECT COUNT(*) 
      FROM orders
      WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
// 这里使用了完全限定的列名

15. 联结表

SQL最强大的功能就是能在数据检索查询的执行中联结(join)表
如何理解联结？
外键(foreign key)：

　　某个表中的一列，它包含另一个表的主键值

  • 使用WHERE子句创建联结
    SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
// 两个表用 WHERE 子句正确联结
// 列名可能出现二义性时，必须使用完全限定的列名

在联结两个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结）的行

如不使用联结，则返回的是笛卡尔积

  • 使用JOIN子句创建等值联结（内部联结）
    SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
// 与上例的结果相同
// 联结条件使用 ON 子句给出
  • 联结多个表
    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;

16. 创建高级联结

还有其他三种联结，分别是自联结、自然联结和外部联结