Sql学习笔记3——集合运算、空值和聚集函数

1、集合运算

SQL作用在关系上的union、intersect和except（Oracle中用minus代替except）运算，对应于数学集合论中的并、交和差运算。这三个运算都自动去除重复。

eg：

找出在2009年秋季学期开课但不在2010年春季开课的所有课程

　　（select course_id

　　from section

　　where semester=‘Fall’ and year=2009）

　　except

　　（select course_id

　　from section

   　where semester=‘Spring’ and year=2010）;

2、空值问题

空值给关系运算带来了特殊的问题，包括算术运算、比较运算和集合运算。

如果算术表达式中的任一输入为空，则该算术表达式（涉及诸如+ - * /）结果为空。例如，R.a+5,且R.a为空，则该表达式结果也空。

在比较运算中，考虑比较“1<null”，则不知道用什么结果好。SQL将涉及到空值的任何比较运算的结果视为unknown，这创建了除了true和false之外的第三个逻辑值。“1<null”结果是unknown。

 

由于where子句的谓词中可以对比较结果使用诸如and、or和not的布尔运算，所以这些定义也可以扩展到处理unknown。

对and：true and unknown结果是unknown，false and unknown结果是false，unknown and unknown结果是unknown。

对or：true or unknown结果是true，false and unknown结果是unknown，unknown and unknown结果是unknown。

 

 对not：not unknown结果是unknown。

*如果where子句谓词对一个元组计算出false或unknown，那么该元组不写入结果集中。

SQL使用特殊谓词 null来测试空值。

eg：找出instructor关系中salary为空值的所有教师。可以写成

　　select name

　　from instructor

 　 where salary is null;

空值在聚集函数中的问题在下面详尽解释。

3、聚集函数

聚集函数是以值得一个集合（集或多集）为输入，返回单个值的函数。SQL 提供了5个固有的聚集函数：

平均值：avg

最小值：min

最大值：max

求和：sum

计数：count

1）基本聚集

考虑“找出computer sci系教师的平均工资”。我们写该查询如下：

　　select avg（salary） as avg_salary 　　/*更名运算常用场合之一*/

　　from instructor

　　where dept_name='comp.sci';

聚集函数默认保留重复记录，在计算平均值保留重复元组是非常重要的。但是有些情况下，我们要删除重复，则可在聚集表达式中使用distinct

考虑“找出在2010年春季学期讲授一门课程的教师总数”，该例子中，无论一个教师讲授了几个课程段，他只应该被计算一次，查询如下：

　　select count（distinct id）

　　from teaches

　　where semester=‘Spring’ and year=2010;

我们常使用count来计算一个关系中元组的总数。写法如下:

　　select count(*)

 　  from r;

注：SQL不允许在用count（*）时使用distinct，而max和min中使用distinct合法，尽管使用与不使用结果一样。

2）分组聚集： group by子句

group by子句中给出的一个或多个属性时用来构造分组的。在group by子句中所有属性取值一样的元组将会被分在同一个组中。

考虑查询”找出每个系的平均工资“，该查询写法如下：

　　select dept_name，avg（salary） as avg_salary

　　from instructor

　　group by dept_name;

注：SQL使用分组聚集时，一个很重要的事情是需要保证出现在select子句中但没有被聚集的属性只能是出现在group by子句的那些属性。即是说，出现在group by子句中的属性必然是出现在select子句中的非聚集的属性。如下例子为错误查询：

　　select dept_name,id,avg(salary)

　　from instructor

　　group by dept_name;   /*本语句通过dept_name分组，id 不能出现在select子句中*/

3）having子句

有时，对分组限定条件比对元组限定条件更加有用。例如，我们也许只对教师平均工资超过42000美元的系感兴趣。该条件不针对单个元组而是针对group by 子句构成的元组，这时需要用到having子句。

必须注意，having子句不能单独使用，必须配合group by子句使用。

所举例子查询如下：

　　select dept_name,avg(salary）as avg_salary

　　from instructor

　　group by dept_name

　　having avg（salary）>42000;

4)对空值和布尔值的聚集

假设instructor关系中有些元组在salary上取空值。考虑以下语句：

　　select sum（salary）

　　from instructor;

按照之前所说，这里将会有问题，即上述查询待求和的值包含了空值。因此，在此运算中，SQL需要忽略其中的null值。

在SQL标准中，除了count（*）外，所有的聚集函数都忽略输入集合中的空值。

转载于:https://www.cnblogs.com/Dezhong-chen/p/4439011.html