本文是《SQL必知必会》一书的精华总结,帮助读者快速入门SQL或者MySQL,主要内容包含:
- 数据库基础知识
- 库表的相关操作
- 检索数据的方法
- …
本文中介绍的第8到13章,前面的章节请看SQL必知必会总结1-第1到7章
汇总数据
聚集函数
聚集函数指的是对某些行运行的一个函数,并且返回一个值,常用的聚集函数有:
函数 | 作用 |
---|---|
AVG() | 返回列的平均值 |
COUNT() | 返回列的函数 |
MAX() | 返回列的最大值 |
MIN() | 返回列的最小值 |
SUM() | 返回某列值之和 |
1、AVG()函数
1 | SELECT AVG(prod_price) AS avg_price -- 求平均值 |
上面求解的是所有行各自的平均值,也可以指定某个特定的行来求解:
1 | SELECT AVG(prod_price) AS avg_price -- 求平均值 |
笔记:AVG()函数会忽略掉值NULL的行
2、COUNT()函数
COUNT()函数进行计数,可以使用它来确定表中的函数或者符合特定条件的行的数目,两种使用情况:
- count(*):不管是空值(NULL)还是非空值,都会统计进去
- count(column):对特定的列进行计数,会忽略表该列的NULL值
1 | SELECT COUNT(*) AS num_cust |
笔记:如果指定列名,则COUNT()函数会忽略指定列的值为空的行,但是如果COUNT()函数使用的是星号,则不会忽略
3、MAX()/MIN()函数
返回指定列中的最大值或者最小值
1 | SELECT MAX(prod_price) AS MAX_price -- 求最大值 |
笔记:上面的两个最值函数会自动忽略掉值为NULL的行
4、SUM()函数
返回指定列值的和(总计)
1 | SELECT SUM(quantity) AS items_ordered |
SUM()函数也可以用来合计计算值:
1 | SELECT SUM(item_price * quantity) AS total_price -- 返回所有物品的价钱之和 |
笔记:SUM()函数会自动忽略值为NULL的行
聚集不同值
上面的5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定ALL参数或不指定参数(因为ALL是默认行为)
- 只包含不同的值,指定DISTINCT参数,表示去重之后再进行计算
笔记:ALL参数不需要指定,是默认行为
1 | SELECT AVG(DISTINCT prod_price) AS avg_price -- 去重之后再求平均值 |
笔记:
1、DISTINCT不能用于COUNT(*);如果指定列名,则DISTINCT只能用于COUNT()
2、DISTINCT必须使用列名,不能用于计算或者表达式
3、DISTINCT用于MAX()和MIN()意义不大,因为最值不管是否考虑去重,都是一样的
组合聚集函数
在SELECT子句中是可以包含多个聚集函数
1 | SELECT |
分组数据
分组使用的是两个子句:
- GROUP BY()
- HAVING()
创建分组
分组是使用SELECT子句的GROUP BY子句建立的,看个例子:
1 | SELECT |
GROUP BY子句使用时候的常见规定:
- GROUP BY子句可以包含任意数目的列,可以对分组进行嵌套
- GROUP BY子句中列出的每一列都是检索列或者有效的表达式(但是不能是聚集函数)
- 如果在SELECT中使用表达式,则必须在GROUP BY子句中使用相同的表达式,而不是使用别名
- 除了聚集函数外,SELECT语句中的每列都必须在GROUP BY子句中列出
- 如果分组中包含具有NULL的行,则NULL将作为一个分组返回;如果列中出现多个NULL,它们将分成一个组
- GROUP BY子句必须在WHERE子句之后,ORDER BY子句之前
- GROUP BY子句中可以使用相对位置:GROUP BY 2, 1 表示先根据第二个列分组,再根据第一个列分组
过滤分组
在WHERE子句中指定过滤的是行而不是分组;实际上WHERE种并没有分组的概念。在SQL使用HAVING来实现过滤分组;
笔记:WHERE过滤行,HAVING过滤分组
1 | SELECT |
WHERE和HAVING的区别:
- WHERE在数据过滤前分组,排除的行不在分组统计中
- HAVING在数据分组后进行过滤
1 | SELECT |
分组和排序
ORDER BY 和GROUP BY的差异:
ORDER BY | GROUP BY |
---|---|
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(非选择的列也可以使用) | 只可能使用选择列或者表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果和聚集函数一起使用列,则必须使用 |
1 | SELECT |
SELECT子句顺序
在这里总结一下SELECT子句的相关顺序:
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或者表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按照组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
使用子查询
任何SELECT语句都是查询,SQL还允许在查询中嵌套查询。
1 | SELECT cust_id -- 再根据子查询中的order_num找出符合要求的cust_id |
笔记:子查询总是从内向外处理
1 | SELECT Customers -- 最后根据找出的cust_id查询Customers |
作为计算字段使用子查询
使用子查询的另一个方法是创建计算字段
1 | SELECT |
联结表
SQL最强大的功能就是数据查询的过程中使用联结表(join)。
创建联结
通过指定要联结的表和它们的联结方式即可创建联结。
1 | SELECT |
如果上面的代码中没有WHERE子句来指定联结条件,则返回的是笛卡尔积,返回出来数的行就是第一个表中的行乘以第二个表中的行。
笔记:返回笛卡尔积的联结,也称做叉联结cross join
内联结inner join
使用最广泛的联结是等值联结,也称之为内联结inner join。实现上面语句的内联结代码:
1 | SELECT |
联结多个表
1 | SELECT |
我们通过联结方式来实现子查询的结果:
1 | -- 子查询 |
创建高级联结
使用表别名
在SQL语句中可以给表取别名:
1 | SELECT |
使用不同类型的联结
介绍3种不同的联结:
- 自联结self join
- 自然联结natural join
- 外联结outer join
1、自联结self join
1 | -- 子查询 |
上面使用了Customers表两次,为了避免歧义,必须使用不同的别名加以区分。
2、自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。自然联结排除多次出现,是每一列只返回一次。
1 | SELECT |
3、外联结
有时候我们需要将一个表中的行和另一个表中行相关联,但是有时候也需要包含那些没有关联行的行记录,比如下面的场景中:
- 对每个顾客下的订单数进行统计,包含那些至今尚未下单的顾客
- 列出所有产品以及订购数量,包含没有人订购的产品
- 计算平均销售规模,包含那些至今尚未下订单的顾客
当联结中包含了那些在相关表中没有关联行的行,这种联结称之为外联结。比如:检索出包括没有订单顾客在内的所有顾客。
1 | SELECT |
上面的代码中表示包含左边所有行的记录;如果是右边,使用RIGHT OUTER。因此外联结实际上有两种形式,它们之间可以互换
- 左外联结
- 右外联结
还有一种比较特殊的外联结,叫做全外联结full outer join,它检索的是两个表中的所有行并关联那些可以关联的行。全外联结包含两个表的不关联的行
1 | SELECT |
带有聚集函数的联结
检索所有顾客及每个顾客所有的订单数:
1 | SELECT |
使用联结和联结条件
总结一下联结和使用要点:
- 注意使用联结的类型:一般是使用内联结,有时候外联结有有效
- 要保证使用正确的联结条件,否则会返回不正确的数据
- 记得提供联结条件,否则返回的是笛卡尔积
- 一个联结中可以包含多个表,甚至可以对不同的表使用不同的联结类型。要注意测试每个联结