Task02:基础查询与排序(二)
教程地址
https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql
4. 对表进行聚合查询
SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数:
COUNT:计算表中的记录数(行数)
SUM:计算表中数值列中数据的合计值
AVG:计算表中数值列中数据的平均值
MAX:求出表中任意列中数据的最大值
MIN:求出表中任意列中数据的最小值
mysql> select * from product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
mysql> -- 计算全部数据的行数(包含NULL)
mysql> SELECT COUNT(*)
-> FROM product;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.02 sec)
mysql> -- 计算NULL以外数据的行数
mysql> SELECT COUNT(purchase_price)
-> FROM product;
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
| 6 |
+-----------------------+
1 row in set (0.00 sec)
mysql> -- 计算销售单价和进货单价的合计值
mysql> SELECT SUM(sale_price), SUM(purchase_price)
-> FROM product;
+-----------------+---------------------+
| SUM(sale_price) | SUM(purchase_price) |
+-----------------+---------------------+
| 16780 | 12210 |
+-----------------+---------------------+
1 row in set (0.00 sec)
mysql> -- 计算销售单价和进货单价的平均值
mysql> SELECT AVG(sale_price), AVG(purchase_price)
-> FROM product;
+-----------------+---------------------+
| AVG(sale_price) | AVG(purchase_price) |
+-----------------+---------------------+
| 2097.5000 | 2035.0000 |
+-----------------+---------------------+
1 row in set (0.00 sec)
mysql> -- MAX和MIN也可用于非数值型数据
mysql> SELECT MAX(regist_date), MIN(regist_date)
-> FROM product;
+------------------+------------------+
| MAX(regist_date) | MIN(regist_date) |
+------------------+------------------+
| 2009-11-11 | 2008-04-28 |
+------------------+------------------+
1 row in set (0.00 sec)
mysql> -- 计算去除重复数据后的数据行数
mysql> SELECT COUNT(DISTINCT product_type)
-> FROM product;
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
| 3 |
+------------------------------+
1 row in set (0.00 sec)
mysql> -- 是否使用DISTINCT时的动作差异(SUM函数)
mysql> SELECT SUM(sale_price), SUM(DISTINCT sale_price)
-> FROM product;
+-----------------+--------------------------+
| SUM(sale_price) | SUM(DISTINCT sale_price) |
+-----------------+--------------------------+
| 16780 | 16280 |
+-----------------+--------------------------+
1 row in set (0.00 sec)
COUNT函数的结果根据参数的不同而不同。COUNT()会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外。但COUNT()例外,并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时,可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT,可以删除重复数据。
5. 对表进行分组
GROUP BY 可以将现有的数据按照某列来汇总统计
mysql> -- 按照商品种类统计数据行数
mysql> SELECT product_type, COUNT(*)
-> FROM product
-> GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+--------------+----------+
3 rows in set (0.00 sec)
mysql> -- 不含GROUP BY
mysql> SELECT product_type, COUNT(*)
-> FROM product;
ERROR 1140 (42000): In aggregated query without GROUP BY,
expression #1 of SELECT list contains nonaggregated column
'shop.product.product_type';
this is incompatible with sql_mode=only_full_group_by
对于 NULL 而言,聚合函数把它们当作是独特的一类。
mysql> SELECT purchase_price, COUNT(*)
-> FROM product
-> GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 320 | 1 |
| 2800 | 2 |
| 5000 | 1 |
| NULL | 2 |
| 790 | 1 |
+----------------+----------+
6 rows in set (0.00 sec)
mysql>
一般的顺序是1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY,如:
mysql> SELECT purchase_price, COUNT(*)
-> FROM product
-> WHERE product_type = '衣服'
-> GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 2800 | 1 |
+----------------+----------+
2 rows in set (0.00 sec)
mysql>
在使用 group by 的时候,可能会出现一些错误,如下:
- 在聚合函数的SELECT子句中写了聚合健以外的列使用COUNT等聚合函数时,SELECT子句中如果出现列名,只能是GROUP BY子句中指定的列名(也就是聚合键)。即,若GROUP BY选中purchase_price字段进行分组,则在SELECT语句中只能选中purchase_price字段,其它字段如product_id等均不行,但是可以有SUM(sale_price)。
- 在GROUP BY子句中使用列的别名SELECT子句中可以通过AS来指定别名,但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
- 在WHERE中使用聚合函数原因是聚合函数的使用前提是结果集已经确定,而WHERE还处于确定结果集的过程中,所以相互矛盾会引发错误。 如果想指定条件,可以在SELECT,HAVING(下面马上会讲)以及ORDER BY子句中使用聚合函数。
关于第一条,我们再详细解释一下:
假如我们对商品类型进行分组,然后选取了商品价格这一列,那要怎么显示呢?不行的
每个商品的类型只有一个,也就是只有一行,但是该类型有多个商品,对应的价格也是多个,需要多行显示
但如果我们对价格求均值,这样该类型对应的价格均值就只有一行了,就能够显示出来了
6. 为聚合结果指定条件
如同上文所说,where 并不能够对 group by 的结果进行筛选,因为其实际执行顺序如下:
FROM → WHERE → GROUP BY → SELECT
所以我们将采用 having 来对分组结果进行筛选
mysql> -- 数字
mysql> SELECT product_type, COUNT(*)
-> FROM product
-> GROUP BY product_type
-> HAVING COUNT(*) = 2;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
+--------------+----------+
2 rows in set (0.00 sec)
但是 having 所处理的列,一定要是 group by 所采用的
mysql> -- 错误形式(因为product_name不包含在GROUP BY聚合键中)
mysql> SELECT product_type, COUNT(*)
-> FROM product
-> GROUP BY product_type
-> HAVING product_name = '圆珠笔';
ERROR 1054 (42S22): Unknown column 'product_name' in 'having clause'
mysql>
7. 对查询结果进行排序
GROUP BY 子句中不能使用 SELECT 子句中定义的别名,但是在 ORDER BY 子句中却可以,因为其运行位置在 SELECT 之后:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
mysql> -- 降序排列
mysql> SELECT product_id, product_name, sale_price, purchase_price
-> FROM product
-> ORDER BY sale_price DESC;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005 | 高压锅 | 6800 | 5000 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0001 | T恤 | 1000 | 500 |
| 0007 | 擦菜板 | 880 | 790 |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
mysql> -- 多个排序键
mysql> SELECT product_id, product_name, sale_price, purchase_price
-> FROM product
-> ORDER BY sale_price, product_id;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦菜板 | 880 | 790 |
| 0001 | T恤 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
mysql> -- 当用于排序的列名中含有NULL时,NULL会在开头或末尾进行汇总。
mysql> SELECT product_id, product_name, sale_price, purchase_price
-> FROM product
-> ORDER BY purchase_price;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0001 | T恤 | 1000 | 500 |
| 0007 | 擦菜板 | 880 | 790 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)
在MySQL中,NULL 值被认为比任何 非NULL 值低,因此,当顺序为 ASC(升序)时,NULL 值出现在第一位,而当顺序为 DESC(降序)时,则排序在最后。
先建表:
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(5),
date_login DATE,
PRIMARY KEY (id)
);
INSERT INTO user(name, date_login) VALUES
(NULL, '2017-03-12'),
('john', NULL),
('david', '2016-12-24'),
('zayne', '2017-03-02');
对于数字或者日期,在非 NULL 值升序排列时
如果想要使得 NULL 排在末尾,可以通过反向升序排列
即对于数字或者日期添加负号之后,再降序排列
这样的结果为:-1,-2,-3,……-∞
mysql> SELECT *
-> FROM user
-> ORDER BY
-> - date_login DESC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 3 | david | 2016-12-24 |
| 4 | zayne | 2017-03-02 |
| 1 | NULL | 2017-03-12 |
| 2 | john | NULL |
+----+-------+------------+
4 rows in set (0.00 sec)
mysql> SELECT *
-> FROM user
-> ORDER BY
-> date_login DESC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 1 | NULL | 2017-03-12 |
| 4 | zayne | 2017-03-02 |
| 3 | david | 2016-12-24 |
| 2 | john | NULL |
+----+-------+------------+
4 rows in set (0.00 sec)
如果不添加负号的话,其他字段也是就按降序排列的
但是对于字符型,就需要使用 is null 和 isnull 函数
mysql> SELECT * FROM user
-> ORDER BY name IS NULL, name ASC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 3 | david | 2016-12-24 |
| 2 | john | NULL |
| 4 | zayne | 2017-03-02 |
| 1 | NULL | 2017-03-12 |
+----+-------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM user
-> ORDER BY ISNULL(name), name ASC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 3 | david | 2016-12-24 |
| 2 | john | NULL |
| 4 | zayne | 2017-03-02 |
| 1 | NULL | 2017-03-12 |
+----+-------+------------+
4 rows in set (0.00 sec)
is null 函数返回的是一个布尔值,即 0 或者 1 。
那就是在这个结果上再对非 null 值进行二次排序
mysql> SELECT ISNULL(name) FROM user;
+--------------+
| ISNULL(name) |
+--------------+
| 1 |
| 0 |
| 0 |
| 0 |
+--------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM user;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 1 | NULL | 2017-03-12 |
| 2 | john | NULL |
| 3 | david | 2016-12-24 |
| 4 | zayne | 2017-03-02 |
+----+-------+------------+
4 rows in set (0.00 sec)
还可以用 coalesce 函数去替换 null,然后再排序
mysql> SELECT * FROM user
-> ORDER BY COALESCE(name, 'zzzzz') ASC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 3 | david | 2016-12-24 |
| 2 | john | NULL |
| 4 | zayne | 2017-03-02 |
| 1 | NULL | 2017-03-12 |
+----+-------+------------+
4 rows in set (0.00 sec)
mysql>
也就是在排序的时候把 null 换成 「zzzzz」
如果想要使得 null 在首行,同时非 null 值降序排列
对于数字或者日期类型,可以加符号,然后升序
达到 -∞,……-3,-2,-1 这样的效果
mysql> SELECT * FROM user
-> ORDER BY - date_login ASC;
+----+-------+------------+
| id | name | date_login |
+----+-------+------------+
| 2 | john | NULL |
| 1 | NULL | 2017-03-12 |
| 4 | zayne | 2017-03-02 |
| 3 | david | 2016-12-24 |
+----+-------+------------+
4 rows in set (0.00 sec)
对于字符型,同样的先用 is not null 降序,再用字段降序
或者也可以将 null 替换为 「zzzzz」然后降序
B. 练习题
B.1
SELECT product_id, SUM(product_name)
--本SELECT语句中存在错误。
FROM product
GROUP BY product_type
WHERE regist_date > '2009-09-01';
select 的字段必须是 group by 出现的
where 应该在 group by 之前
sum 好像不能对字符串求和
sum 的括号是中文的
B.2
请编写一条SELECT语句,求出销售单价( sale_price 列)合计值大于进货单价( purchase_price 列)合计值1.5倍的商品种类。执行结果如下所示。
product_type | sum | sum
-------------+------+------
衣服 | 5000 | 3300
办公用品 | 600 | 320
mysql> SELECT product_type, SUM(sale_price) AS sum, SUM(purchase_price) AS sum
-> FROM product
-> GROUP BY product_type
-> HAVING SUM(sale_price) > 1.5 * SUM(purchase_price)
-> ;
+--------------+------+------+
| product_type | sum | sum |
+--------------+------+------+
| 衣服 | 5000 | 3300 |
| 办公用品 | 600 | 320 |
+--------------+------+------+
2 rows in set (0.00 sec)
B.3
此前我们曾经使用SELECT语句选取出了product(商品)表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序,但现在已经无法记起当时如何指定的了。请根据下列执行结果,思考 ORDER BY 子句的内容。
首先观察大部分值,好像只有日期列比较明显,是降序
而且需要注意的是 null 值排在第一行
所以我们可以用 coalesce 函数去替换 null 为「zzzzz」然后降序
或者是 is null 先把 null 变为 1,降序放在第一个,然后再对其他值降序
或者也可以加负号,然后升序,即 -∞,……-3,-2,-1 这样