SQL基础：查询与聚合的深入解析

# SQL 基础：查询与聚合的深入解析 ## 1. SQL 查询基础 在 SQL 中，`where` 子句起着关键作用。当 `where` 子句的谓词对某个元组的计算结果为 `false` 或 `unknown` 时，该元组不会被添加到查询结果中。例如，若要查找 `instructor` 关系中 `salary` 为 `null` 的所有教师，可使用如下查询： ```sql select name from instructor where salary is null; ``` 这里使用了特殊关键字 `null` 来测试空值。同时，`is not null` 谓词在应用的值不为空时会返回成功结果。部分 SQL 实现还允许使用 `is unknown` 和 `is not unknown` 子句来测试比较结果是否为 `unknown`。 当查询使用 `select distinct` 子句时，需要消除重复元组。在比较两个元组对应属性的值时，如果两个值均不为空且相等，或者两个值都为 `null`，则将它们视为相同。例如，`{(’A’,null), (’A’,null)}` 这两个元组会被视为相同，即使某些属性的值为 `null`。使用 `distinct` 子句后，只会保留此类相同元组的一个副本。需要注意的是，这里对 `null` 的处理方式与谓词中的处理方式不同，在谓词中，“`null=null`” 的比较会返回 `unknown`，而非 `true`。这种将所有属性值相同（即使部分值为 `null`）的元组视为相同的方法，同样适用于集合操作（如 `union`、`intersection` 和 `except`）。 ## 2. 聚合函数 ### 2.1 内置聚合函数 SQL 提供了五个内置聚合函数，它们以一组值（集合或多重集合）作为输入，并返回单个值： - **平均值**：`avg` - **最小值**：`min` - **最大值**：`max` - **总和**：`sum` - **计数**：`count` 其中，`sum` 和 `avg` 的输入必须是一组数字，而其他运算符也可以对非数字数据类型（如字符串）的集合进行操作。 ### 2.2 基本聚合 以查询“查找计算机科学系教师的平均工资”为例，可使用以下查询： ```sql select avg (salary) from instructor where dept name= ’Comp. Sci.’; ``` 此查询的结果是一个具有单个属性的关系，包含一个元组，其数值对应计算机科学系教师的平均工资。数据库系统可能会为聚合生成的结果关系属性赋予任意名称，但我们可以使用 `as` 子句为属性赋予有意义的名称，如下所示： ```sql select avg (salary) as avg salary from instructor where dept name= ’Comp. Sci.’; ``` 假设 `instructor` 关系中计算机科学系教师的工资分别为 $75,000、$65,000 和 $92,000，则平均工资为 $232,000 / 3 = $77,333.33。在计算平均值时，保留重复值非常重要。例如，如果计算机科学系新增了一位工资为 $75,000 的教师，若消除重复值，将得到错误的答案（$232,000 / 4 = $58,000），而正确答案应为 $76,750。 在某些情况下，在计算聚合函数之前必须消除重复值。此时，可在聚合表达式中使用关键字 `distinct`。例如，查询“查找 2010 年春季学期教授课程的教师总数”，可使用以下查询： ```sql select count (distinct ID) from teaches where semester = ’Spring’ and year = 2010; ``` 由于 `ID` 前使用了关键字 `distinct`，即使一位教师教授多门课程，在结果中也只会被计数一次。 我们经常使用聚合函数 `count` 来统计关系中的元组数量，在 SQL 中，该函数的表示法为 `count (*)`。例如，要查找 `course` 关系中的元组数量，可使用以下查询： ```sql select count (*) from course; ``` 需要注意的是，SQL 不允许在 `count (*)` 中使用 `distinct`。虽然使用 `distinct` 与 `max` 和 `min` 是合法的，但结果不会改变。我们可以使用关键字 `all` 代替 `distinct` 来指定保留重复值，但由于 `all` 是默认值，因此无需这样做。 ### 2.3 分组聚合 有时，我们不仅希望对单个元组集应用聚合函数，还希望对一组元组集应用聚合函数。在 SQL 中，可使用 `group by` 子句来实现这一需求。`group by` 子句中指定的属性用于形成分组，具有相同属性值的元组会被放置在同一组中。 例如，查询“查找每个部门的平均工资”，可使用以下查询： ```sql select dept name, avg (salary) as avg salary from instructor group by dept name; ``` 以下是按 `dept name` 属性分组后的 `instructor` 关系元组示例： | ID | name | dept name | salary | | ---- | ---- | ---- | ---- | | 76766 | Crick | Biology | 72000 | | 45565 | Katz | Comp. Sci. | 75000 | | 10101 | Srinivasan | Comp. Sci. | 65000 | | 83821 | Brandt | Comp. Sci. | 92000 | | 98345 | Kim | Elec. Eng. | 80000 | | 12121 | Wu | Finance | 90000 | | 76543 | Singh | Finance | 80000 | | 32343 | El Said | History | 60000 | | 58583 | Califieri | History | 62000 | | 15151 | Mozart | Music | 40000 | | 33456 | Gold | Physics | 87000 | | 22222 | Einstein | Physics | 95000 | 此查询的结果如下： | dept name | avg salary | | ---- | ---- | | Biology | 72000 | | Comp. Sci. | 77333 | | Elec. Eng. | 80000 | | Finance | 85000 | | History | 61000 | | Music | 40000 | | Physics | 91000 | 与之对比，查询“查找所有教师的平均工资”，可使用以下查询： ```sql select avg (salary) from instructor; ``` 在这个查询中，由于省略了 `group by` 子句，整个关系将被视为一个单一的组。 再例如，查询“查找 2010 年春季学期每个部门教授课程的教师数量”，由于需要将 `teaches` 关系中的信息与 `instructor` 关系中的信息进行连接以获取每个教师的部门名称，可使用以下查询： ```sql select dept name, count (distinct ID) as instr count from instructor natural join teaches where semester = ’Spring’ and year = 2010 group by dept name; ``` 此查询的结果如下： | dept name | instr count | | ---- | ---- | | Comp. Sci. | 3 | | Finance | 1 | | History | 1 | | Music | 1 | 当 SQL 查询使用分组时，必须确保 `select` 语句中未进行聚合的属性仅为 `group by` 子句中存在的属性。换句话说，如果某个属性不在 `group by` 子句中，那么它在 `select` 子句中必须仅出现在聚合函数内部，否则查询将被视为错误。例如，以下查询是错误的，因为 `ID` 不在 `group by` 子句中，却在 `select` 子句中未经过聚合就出现了： ```sql /* 错误查询 */ select dept name, ID, avg (salary) from instructor group by dept name; ``` 在特定组（由 `dept name` 定义）中的每个教师可能有不同的 `ID`，由于每个组只输出一个元组，因此没有唯一的方法来选择要输出的 `ID` 值。所以，SQL 不允许此类情况。 ### 2.4 `having` 子句 有时，我们需要对组而不是元组应用条件。例如，我们可能只对教师平均工资超过 $42,000 的部门感兴趣。这种条件不适用于单个元组，而是适用于 `group by` 子句构建的每个组。为了表达此类查询，可使用 SQL 的 `having` 子句。SQL 在形成组后应用 `having` 子句中的谓词，因此可以使用聚合函数。例如，上述查询可使用以下 SQL 表达： ```sql select dept name, avg (salary) as avg salary from instructor group by dept name having avg (salary) > 42000; ``` 此查询的结果如下： | dept name | avg(avg salary) | | ---- | ---- | | Physics | 91000 | | Elec. Eng. | 80000 | | Finance | 85000 | | Comp. Sci. | 77333 | | Biology | 72000 | | History | 61000 | 与 `select` 子句一样，`having` 子句中未进行聚合的属性必须出现在 `group by` 子句中，否则查询将被视为错误。 包含聚合、`group by` 或 `having` 子句的查询的含义由以下操作序列定义： 1. 与无聚合的查询一样，首先计算 `from` 子句以获取一个关系。 2. 如果存在 `where` 子句，则将 `where` 子句中的谓词应用于 `from` 子句的结果关系。 3. 如果存在 `group by` 子句，则将满足 `where` 谓词的元组分组；如果 `group by` 子句不存在，则将满足