3.1创建数据表

CREATE TABLE Product
(product_id CHAR(4)  NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32)  NOT NULL,
sale_price INTEGER  ,
purchase_price INTEGER  ,
regist_date DATE  ,
PRIMARY KEY (product_id));


START TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T 恤衫 ', ' 衣服 ',
1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', ' 打孔器 ', ' 办公用品 ',
500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', ' 运动 T 恤 ', ' 衣服 ',
4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', ' 菜刀 ',  ' 厨房用具 ', 
3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', ' 高压锅 ', ' 厨房用具 ',
6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', ' 叉子 ',  ' 厨房用具 ',
500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', ' 擦菜板 ', ' 厨房用具 ',
880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', ' 圆珠笔 ', ' 办公用品 ',
100, NULL,'2009-11-11');
COMMIT;

3.2聚合查询

● 只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能够使用聚合函数。
● COUNT (*) 会得到包含 NULL 的数据行数，而 COUNT (< 列名 >) 会得到 NULL 之外的数据行数。该特性是 COUNT 函数所特有的，其他函数并不能将星号作为参数（如
果使用星号会出错）

● 聚合函数会将 NULL 排除在外。但 COUNT (*)例外，并不会排除 NULL 。

● SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

SELECT COUNT(*)
FROM product;

SELECT COUNT(purchase_price)
FROM product;

SELECT COUNT(product_name)
FROM product;

• 注意AVG函数，合计值/合计行数，无论是合计值还是合计行数，都是排除NULL行的

SELECT SUM(purchase_price)/8,SUM(purchase_price)/6,AVG(purchase_price)
FROM product;

SELECT COUNT(DISTINCT product_type)
FROM product;

3.3 分组

/*
● 使用 GROUP BY 子句可以像切蛋糕那样将表分割。通过使用聚合函数和
GROUP BY 子句，可以根据“商品种类”或者“登记日期”等将表分割后再
进行汇总。
● 使用聚合函数和 GROUP BY 子句时需要注意以下4点。
① 只能写在 SELECT 子句之中
② GROUP BY 子句中不能使用 SELECT 子句中列的别名
③ GROUP BY 子句的聚合结果是无序的
④ WHERE 子句中不能使用聚合函数

● 在 GROUP BY 子句中指定的列称为聚合键或者分组列
● 当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据
*/

3.3.1GROUP BY 子句

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

3.3.2聚合键中包含 NULL 的情况

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

3.3.3使用 WHERE 子句时 GROUP BY 的执行结果

● GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序:
FROM → WHERE → GROUP BY → SELECT

SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = ' 衣服 '
GROUP BY purchase_price;

3.3.4与聚合函数和 GROUP BY 子句有关的常见错误

/*常见错误① ——在 SELECT 子句中书写了多余的列

实际上，使用聚合函数时， SELECT 子句中只能存在以下三种
元素。
● 常数
● 聚合函数
● GROUP BY 子句中指定的列名（也就是聚合键）*/

/*常见错误② ——在 GROUP BY 子句中写了列的别名
SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是，在 GROUP BY 子句中是不能使用别名的(目前所知在mysql和PostgreSQL是可以的)。

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

3.4 为聚合结果指定条件

/*
● 使用 COUNT 函数等对表中数据进行汇总操作时，为其指定条件的不是
WHERE 子句，而是 HAVING 子句。
● 聚合函数可以在 SELECT 子句、 HAVING 子句和 ORDER BY 子句中使用。
● HAVING 子句要写在 GROUP BY 子句之后。
● WHERE 子句用来指定数据行的条件， HAVING 子句用来指定分组的条件。*/

3.4.1HAVING 子句

/*
使用 HAVING 子句时 SELECT 语句的顺序:
SELECT → FROM → WHERE → GROUP BY → HAVING
*/

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

3.4.2HAVING 子句的构成要素

/*
HAVING 子句中能够使用的 3 种要素如下所示。
● 常数
● 聚合函数
● GROUP BY 子句中指定的列名（即聚合键）
*/

SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING product_type=' 厨房用具 ';

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

3.4.3相对于 HAVING 子句，更适合写在 WHERE 子句中的条件

/*以下两个sql语句实现的结果是一致的，但是聚合键所对应的
条件还是应该书写在 WHERE 子句之中。即以下两个sql语句中的第二个是比较好的。
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件

通常情况下，为了得到相同的结果，将条件写在 WHERE 子句
中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短
*/

SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = ' 衣服 ';

SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = ' 衣服 '
GROUP BY product_type;

3.5 对查询结果进行排序

/*
● 使用 ORDER BY 子句对查询结果进行排序。
● 在 ORDER BY 子句中列名的后面使用关键字 ASC 可以进行升序排序，使用 DESC 关键字可以进行降序排序。
● ORDER BY 子句中可以指定多个排序键。
● 排序健中包含 NULL 时，会在开头或末尾进行汇总。
● ORDER BY 子句中可以使用 SELECT 子句中定义的列的别名。
● ORDER BY 子句中可以使用 SELECT 子句中未出现的列或者聚合函数。
● ORDER BY 子句中不能使用列的编号。
*/

3.5.1 ORDER BY 子句

/*
ORDER BY子句中书写的列名称为排序键

ORDER BY子句的书写顺序

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →
2. HAVING 子句 → 6. ORDER BY 子句

降序排序在排序建后面加上DESC关键字，升序排序在排序建后面加上ASC关键字
未指定 ORDER BY 子句中排列顺序时会默认使用升序进行排列。
*/

3.5.2 NULL的顺序

/*使用含有 NULL 的列作为排序键时，
NULL 会在结果的开头或末尾汇总显示
*/

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;

3.5.3 在排序键中使用显示用的别名

/*
在 GROUP BY 子句中不能使用SELECT 子句中定义的别名，但是在 ORDER BY 子句中却是允许使用别名的

使用 HAVING 子句时 SELECT 语句的顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
*/

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;

3.5.4 ORDER BY 子句中可以使用的列

/在 ORDER BY 子句中可以使用 SELECT 子句中未使用的列和聚合函数。/

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