typora-copy-images-to: SQL basic
文章目录
- SQL基础教程 Chapter 3
- 3.1 对表进行聚合查询
- 3.2 对表进行分组
- 3.3 为聚合结果指定条件
- 3.4 对查询的结果进行排序
- Appendix
- 法则3.1 COUNT 函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的行数
- 法则3.2 聚合函数会将NULL排除在外。但COUNT(*)例外
- 法则3.3 MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列
- 法则3.4 想要计算值的种类时,可以在COUNT函数的参数中使用DISTINCT
- 法则3.5 在聚合函数的参数中使用DISTINCT,可以删除重复数据
- 法则3.6 GROUP BY就像是切分表的一把刀
- 法则3.7 SQL 子句的顺序不能改变,也不能互相替换
- 法则3.8 聚合键中包含NULL时,在结果中会以“不确定”行(空行)的形式表现出来
- 法则3.9 使用GROUP BY子句时,SELECT子句中不能出现聚合键之外的列名
- 法则3.10 在GROUP BY子句中不能使用SELECT子句中定义的别名
- 法则3.11 GROUP BY 子句结果的显示是无序的
- 法则3.12 只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数
- 法则3.13 HAVING子句要写在GROUP BY子句之后
- 法则3.14 聚合键所对应的条件不应该书写在HAVING子句当中,而应该书写在WHERE子句当中
- 法则3.15 ORDER BY子句通常写在SELECT语句的末尾
- 法则3.16 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列
- 法则3.17 排序键中包含NULL时,会在开头或末尾进行汇总
- 法则3.18 在ORDER BY子句中可以使用SELECT子句中定义的别名
- 法则3.19 在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数
- 法则3.20 在ORDER BY子句中不要使用列编号
SQL基础教程 Chapter 3
本章重点:随着表中记录(数据行)的不断积累,存储数据逐渐增加,有时我们可能希望计算出这些数据的合计值或者平均值等。本章我们将学习使用SQL 语句进
行汇总操作的方法。此外,我们还会学习在汇总操作时指定条件,以及对汇总
结果进行升序、降序的排序方法。
3.1 对表进行聚合查询
- 使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。
- 通常,聚合函数会对NULL以外的对象进行汇总。但是只有COUNT函数
例外,使用COUNT(*)可以查出包含NULL在内的全部数据的行数。 - 使用DISTINCT关键字删除重复值。
- 聚合函数
- COUNT: 计算表中的记录数(行数)
- SUM: 计算表中数值列中数据的合计值
- AVG: 计算表中数值列中数据的平均值
- MAX: 求出表中任意列中数据的最大值
- MIN: 求出表中任意列中数据的最小值
select count(*)
from product;
# 计算全部数据的行数
select count(purchase_price)
from product;
# 计算product_price列的非空行数
- 计算合计值
select sum(sale_price)
from product;
# 计算销售单价的合计值
select sum(sale_price), sum(purchase_price)
from product;
# sum 语句后可加 as 语句
# sum 函数中NULL被无视,而非视为0
- 计算平均值
select avg(sale_price)
from product;
# avg 函数和sum 函数一样,会先删除NULL再计算
- 计算最大值&最小值
select max(sale_price), min(purchace_price)
from product;
# max & min函数可以适用于任何数据类型的列
- 使用聚合函数删除重复值(关键字 DISTINCT)
select count(distinct product_type)
from product;
# distinct 不可以写在括号外面
- 其它聚合函数也可以使用DISTINCT函数
3.2 对表进行分组
- 使用GROUP BY子句可以像切蛋糕那样将表分割。通过使用聚合函数和
GROUP BY子句,可以根据“商品种类”或者“登记日期”等将表分割后再
进行汇总。 - 聚合键中包含NULL时,在结果中会以“不确定”行(空行)的形式表现出来。
- 使用聚合函数和GROUP BY子句时需要注意以下4点。
① 只能写在SELECT子句之中
② GROUP BY子句中不能使用SELECT子句中列的别名
③ GROUP BY子句的聚合结果是无序的
④ WHERE子句中不能使用聚合函数
- GROUP BY 子句
语法:
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;
# 按照商品种类进行切分
select product_type,count(*)
from product
group by product_type;
# 一定要写在from语句之后
-
子句的书写顺序(暂定):1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
-
聚合键包含NULL的情况
select purchase_price, count(*)
from product
group by purchase_price;
# NULL 在这里表示为不确定
- 使用where 子句时group by 执行的结果
语法:
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE # 先进行条件过滤
GROUP BY <列名1>, <列名2>, <列名3>, ……;
# GROUP BY 和WHERE 并用时SELECT 语句的执行顺序
# FROM → WHERE → GROUP BY → SELECT
- 与聚合函数和group by 子句有关的常见错误
- 在SELECT子句中书写了多余的列,select子句下只能存在以下三种元素:
- 常数
- 聚合函数
- group by 子句中指定的列名(也就是聚合键)
- 在SELECT子句中书写了多余的列,select子句下只能存在以下三种元素:
-
上述示例仅限于MySQL语法
-
select 子句中的项目可以通过as语句指定别名,而group by子句中是不能使用别名的,这是由于SQL语句的执行顺序造成的
-
group by 子句的结果是随机的,不可以排序,如果需要排序,需要在select语句中进行指定
-
在where 子句中不可以使用聚合函数
-
DISTINCT 和 GROUP BY
- DISTINCT 用于想要删除选择结果中的重复记录
- GROUP BY 用于想要计算汇总结果
3.3 为聚合结果指定条件
- 使用COUNT函数等对表中数据进行汇总操作时,为其指定条件的不是
WHERE子句,而是HAVING子句 - 聚合函数可以在SELECT子句、HAVING子句和ORDER BY子句中使用
- HAVING子句要写在GROUP BY子句之后
- WHERE子句用来指定数据行的条件,HAVING子句用来指定分组的条件
- HAVING 子句,指定条件来选取特定组的方法
- WHERE 子句只能指定记录的条件,不能指定组的条件
HAVING 子句语法:
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>
# 使用HAVING 子句时SELECT 语句的顺序
# SELECT → FROM → WHERE → GROUP BY → HAVING
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
# 更改条件为 销售单价的平均值大于等于2500日元
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
-
HAVING 子句的构成要素
- 常数
- 聚合函数
- GROUP BY 子句中指定的列名(即聚合键)
-
相对于HAVING子句,更适合写在WHERE子句中的条件(对于聚合键所对应的条件
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;
# 上述两个公式返回结果一致
# 但聚合键所对应的条件应该书写在where语句中
# WHERE 子句 = 指定行所对应的条件
# HAVING 子句 = 指定组所对应的条件
# where子句处理速度比having快
# where紫玉可以指定条件所对应的的列创建索引
3.4 对查询的结果进行排序
- 使用ORDER BY子句对查询结果进行排序
- 在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序,使
用DESC关键字可以进行降序排序 - ORDER BY子句中可以指定多个排序键
- 排序健中包含NULL时,会在开头或末尾进行汇总
- ORDER BY子句中可以使用SELECT子句中定义的列的别名
- ORDER BY子句中可以使用SELECT子句中未出现的列或者聚合函
- ORDER BY子句中不能使用列的编号。
- ORDER BY 子句
# ORDER BY 子句的语法:
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
# 无论如何,ORDER BY 子句都要写在select 子句的末尾
# ORDER BY 子句中书写的列名称为排序键
# 子句的书写顺序
1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句
-
指定升序或降序
- 降序用DESC 关键字
- 升序用ASC 关键字,省略关键字,默认升序
-
指定多个排序键
SELECT product_id, product_name, sale_price,purchase_price
FROM Product
ORDER BY sale_price, product_id;
# 优先使用左边的键,接着参考右边的键
- NULL的顺序,对含有NULL的列作为排序键时,NULL会在结果的开头或者末尾汇总显示
- 排序键中使用显示用的别名,GROUP BY子句中不允许使用别名,但是ORDER BY允许使用别名
-
上述理由在于select子句执行顺序间隔与group by 以及 order by
-
ORDER BY子句中可以使用的列,即使select子句并不包含选中的列
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;
# 也可以使用聚合函数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*) DESC;
- 不要使用列编号
- 阅读起来比较困难
- 该排序功能未来会删除