产品经理从0开始学SQL(三)

这是从0开始学SQL的第三课：查询进阶。包括聚集查询、嵌套子查询和基本运算。

一、聚集查询

1、基本聚集

聚集函数是以值的一个集合为输入、返回单个值的函数。SQL提供了五个固有聚集函数：

- 平均值：avg

- 最小值：min

- 最大值：max

- 总和：sum

- 计数：count

注意：sum和avg的输入必须为数字类型，其他的函数输入可以是其他数据类型，比如字符串。举几个例子：

假设有这样一张学生信息表t_user:

还有一张课程信息表t_user_course:

1）找出专业是软件工程和计算机专业的平均学费：

select avg(tuition) 
from t_user 
where major = "软件工程" or major = "计算机"

查询结果：

我们可以给avg(tuition)属性一个有意义的名字:

select avg(tuition) as tuition

在计算平均值时保留重复元组是很重要的。有些情况下在计算聚集函数事需要先删除掉重复元组。可用关键词distinct删除掉重复的元组。例如：找出2019秋季上过高等数学的学生人数。一个学生无论在2019秋季上了多少次高等数学，都应该只计算一次。

select count(distinct user_id) as total
from t_user_course 
where season='秋季' and year='2019'

查询结果：

2、分组聚集

有时候我们希望聚集函数作用到一组元组集上。那么就可以用到分组聚集。SQL提供了group by 子句，group by 子句中的所有属性取值相同的元组被分在一个组里。

语法：

group by 属性1,属性2,属性3

举个例子：找出每个专业的学生人数

select count(id) as total,major
from t_user
group by major

该句子的查询过程是这样的：先按照group by来分组，具有相同专业的学生被分成一个组，如下图：注意看下相同专业的id=2和id=4，被分到了同一组。

然后在每个小组里面，统计人数。查询结果：

需要注意的是需要保证出现在select语句中但没有被聚集的属性只能出现在group by 子句中的那些属性，否则查询是错误的。比如下面这个错误查询：(id不应该出现在select中，因为id没有被分组)

select count(id) as total,major,id
from t_user
group by major

‍

如果想对分组再加点限定条件，比如找出每个专业人数超过2人的学生人数。那么可以使用having子句，having子句是对分组后的结果再限定条件。

select count(id) as total,major
from t_user
group by major
having total > 2

二、嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式。任何select-from-where表达式的返回结果都是一个关系，因此可以被插入到另一个select-from-where中任何关系可以出现的位置。

1、where嵌套

这里介绍一个新的关键词 in 和 not in。表示元组是否是集合中的成员。

假设有一张课程信息表：t_course

举个例子，找出2019年秋季和2020年秋季同时开课的所有课程：我们先找出2020年秋季开课的所有课程，然后需要从子查询中找出那些同时在2019年秋季开课课程。

select distinct id
from t_course
where season="秋季" and year="2020" and id
in (select id 
    from t_course
    where season="秋季" and year="2019"
);

2、from嵌套

还记得上面的having子句查询吗？找出每个专业人数超过2人的学生人数。这次我们不用having，在from中用子查询。

select total,major
from (
  select count(id) as total,major
  from t_user
  group by major  
)
where total > 2

from子句查询出来的结果就像一个新的中间表，select的字段名必须来自from子查询结果表的字段名。

还有select嵌套，having子句嵌套等，由于实际中用到的不是很多，在这里就不展开讲了。😆