SQL(MySQL、Oracle等)基础速览：聚合函数、复杂查询

聚合函数

先来看一下聚合函数

• 聚合函数（包括COUNT(<列名>)）会对NULL以外的对象进行汇总，COUNT(*)除外
• 只有COUNT能用 * 作为参数
• MAX/MIN 适用于任何数据类型的列，SUM/AVG只能对数值类型的列使用
• 所有聚合函数都能用 DISTINCT
• WHERE子句中不能使用聚合函数，MySQL以外的都不支持
• 只有SELECT、HAVING、ORDER BY子句能使用聚合函数
  计算去除重复数据后的数据行数：

SELECT COUNT(DISTINCT product_id) FROM Product;

复杂函数

GROUP BY

• 聚合键中有NULL时，在结果中会以空行的形式表现出来
• 只能用于SELECT
• 不能使用列的别名
• 聚合结果无序
• 不能把GROUP BY聚合键之外的列名写在SELECT 子句中
  按照商品种类统计数据行数(=商品数量)：

SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;

HAVING

• 指定分组的条件
• 写在GROUP BY 之后
• 不能把 SELECT 子句 之外的列名写在HAVING聚合键中
  从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组：
  (COUNT() 可以换成 AVG(sale_price))

SELECT product_type, COUNT(*) FROM Product GROUP BY product_type
HAVING COUNT(*)=2;

ORDER BY

• ASC 升序，DESC 降序
• 会- 对 NULL 排序，NULL在开头或结尾不一定
• 可以使用 SELECT 中定义的列的别名
• 可以用 SELECT 中未出现的列或聚合函数
• 子句中不能用列的编号

视图

• 保存的不是实际的数据，实际上保存的是SELECT 语句，是个临时表
• 数据会随着原表的变化自动更新
• 可以把常用的SELECT 语句做成视图来使用
• 创建视图时不能用 ORDER BY，视图是没顺序的，个别DBMS可以用
• 可以在视图的基础上再创建视图，但不提倡，影响性能
• 可以对视图插入、删除、更新，只适用于以下请况：创建视图时SELECT 子句未使用 DISTINCT，单表查询，未使用GROUP BY和 HAVING。视图变化后原表也会变

CREATE VIEW：（创建视图）

CREATE VIEW <视图名> (<视图列名1>, <试图列名2>,...) AS <SELECT 语句>;

DROP VIEW：（删除视图）

DROP VIEW <视图名>;

子查询

普通子查询

• 相当于一个一次性视图
• 原则上必须给子查询设置别名

SELECT product_type,cnt_product 
    FROM (SELECT product_type,COUNT(*) AS cnt_product
        FROM Product
        GROUP BY product_type) AS ProductSum;

标量子查询

• 只能返回一行一列的结果。如: 10, "东京都"
• 用在 = 或者 <> 等比较运算符中
• 需要单一值的位置都可以用标量子查询

SELECT product_id, product_name, sale_price 
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
        FROM Product);

关联子查询

• 内部的别名，只能在内部使用
  选出各商品种类中高于该商品种类的平均销售单价的商品：

SELECT product_id, product_name, sale_price 
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
        FROM Product AS P2
        WHERE P2.product_type=P1.procuct_type
        GROUP BY product_type);