前言
基本SQL查询的另一块内容
正文
一、分组查询
- 分组:SQL可以将检索到的元组按照某一条件进行分类,具有相同条件值的元组划到一个组或一个集合中,同时处理多个组或集合的聚集运算
示例: 求每一个学生的平均成绩
Select S#, AVG(Score)
From SC
Group by S#;
上例是按学号进行分组,即学号相同的元组划到一个组中并求平均值
示例:求每一门课程的平均成绩
Select C#, AVG(Score)
From SC
Group by C#;
上例是按课号进行分组,即课号相同的元组划到一个组中并求平均值
- 聚集函数是不允许用于Where子句中的:Where子句是对每一元组进行条件过滤,而不是对集合进行条件过滤
示例:求不及格课程超过两门的同学的学号
Select S# From SC
Where Score < 60 and Count(*)>2
Group by S#;
错误写法!!!
二、分组过滤
- 分组过滤:若要对集合(即分组)进行条件过滤,即满足条件的集合/分组留下,不满足条件的集合/分组剔除。
- Having子句,又称分组过滤子句。需要有Groupby子句支持,换句话说,没有Groupby子句,便不能有Having子句。
示例:求不及格课程超过两门的同学的学号
Select S# From SC
Where Score < 60
Group by S#
Having Count(*)>2;
示例:求有10人以上不及格的课程号
Select C# From SC
Where Score < 60
Group by C#
Having Count(*)>10;
- HAVING子句与WHERE子句表达条件的区别
- 分组查询仍需要注意语义问题
示例:求有两门以上不及格课程同学的学号及其平均成绩
Select S#, Avg(Score)
From SC
Where Score < 60
Group by S#
Having Count(*)>2;
上例SQL语句求出的是“该同学那几门不及格课程的平均成绩”,而不是
“该同学所有课程的平均成绩” 。因此正确写法为:
Select S#, Avg(Score)
From SC
Where S#
in
( Select S#
From SC
Where Score < 60
Group by S#
Having Count(*)>2 )
Group by S# ;
三、集合运算
SQL语言:并运算 UNION, 交运算INTERSECT, 差运算EXCEPT
注意的是,除了UNION运算可能无法替代,其他两者可以由其他方式来表达。
并运算 UNION
示例:求学过002号课的同学或学过003号课的同学学号
Select S# From SC Where C# = ‘002’
UNION
Select S# From SC Where C# = ‘003’;
上述语句也可采用如下不用UNION的方式来进行
Select S# From SC Where C# = ‘002’ OR C# = ‘003’
但有时也不能完全转换成不用UNION的方式
示例:已知两个表,求客户所在的或者代理商所在的城市
Customers(CID, Cname, City, Discnt)
Agents(AID, Aname, City, Percent)
Select City From Customers
UNION
Select City From Agents ;
交运算INTERSECT
示例:求既学过002号课,又学过003号课的同学学号
Select S# From SC Where C# = ‘002’
INTERSECT
Select S# From SC Where C# = ‘003’;
上述语句也可采用如下不用INTERSECT的方式来进行
Select S# From SC Where C# = ‘002’ and S# IN
(Select S# From SC Where C# = ‘003’);
交运算符Intersect并没有增强SQL的表达能力,没有Intersect, SQL也可以用其他方式表达同样的查询需求。只是有了Intersect更容易表达一些,但增加了SQL语言的不唯一性。
差运算EXCEPT
示例: 假定所有学生都有选课,求没学过002号课程的学生学号
不能写成如下形式:
Select S# From SC Where C# <> ‘002’
上述写法,只能排斥选修一门且是‘002’号课程的同学
可写成如下形式:所有学生 减掉 学过002号课的学生
Select DISTINCT S# From SC
EXCEPT
Select S# From SC Where C# = ‘002’;
前述语句也可不用EXCEPT的方式来进行
Select DISTINCT S# From SC SC1
Where not exists ( Select * From SC
Where C# = ‘002’ and S# = SC1.S#) ;
差运算符Except也没有增强SQL的表达能力,没有Except, SQL也可以用其他方式表达同样的查询需求。只是有了Except更容易表达一些,但增加了SQL语言的不唯一性。
四、空值处理
- 空值是其值不知道、不确定、不存在的值
- 数据库中有了空值,会影响许多方面,如影响聚集函数运算的正确性,不
能参与算术、比较或逻辑运算等 - 在SQL标准中和许多现流行的DBMS中,空值被用一种特殊的符号Null来
标记,使用特殊的空值检测函数来获得某列的值是否为空值。 - 空值检测
is [not ] null
测试指定列的值是否为空值
示例:找出年龄值为空的学生姓名
Select Sname From Student
Where Sage is null ;
注意:上例条件不能写为Where Sage = null; 空值是不能进行运算的
现行DBMS的空值处理小结
- 除is[not]null之外,空值不满足任何查找条件
- 如果null参与算术运算,则该算术表达式的值为null
- 如果null参与比较运算,则结果可视为false。在SQL-92中可看成
unknown - 如果null参与聚集运算,则除count(*)之外其它聚集函数都忽略null
(2) 空值的处理
示例:
Select AVG(Score) From SC;
上例的值(参考右图)为73.5 = (92 + 55)/2。
示例:
Select COUNT(*) From SC;
上例的值为3。
五、内连接、外连接
关系代数运算中,有连接运算,又分为 θ 连接和外连接
上例的连接运算由两部分构成:连接类型和连接条件
连接类型
- Inner Join: 即关系代数中的θ-连接运算
- Left Outer Join, Right Outer Join, Full Outer Join: 即关系代数中的外连
如“表1 Left Outer Join 表2”,则连接后,表1的任何元组t都会出现在结果表中,如表2中有满足连接条件的元组s, 则t与s连接;否则t与空值元组连接;
如“表1 Right Outer Join 表2”,则连接后,表2的任何元组s都会出现在结果表中,如表1中有满足连接条件的元组t, 则t与s连接;否则s与空值元组连接;
如“表1 Full Outer Join 表2”,是前两者的并。
连接条件
- 连接中使用 natural
出现在结果关系中的两个连接关系的元组在公共属性上取值相等,且公共属性只出现一次 - 连接中使用 on <连接条件>
出现在结果关系中的两个连接关系的元组取值满足连接条件,且公共属性出现两次 - 连接中使用 using (Col1, Col2, …, Coln)
(Col1, Col2, …, Coln)是两个连接关系的公共属性的子集,元组在(Col1,Col2, …, Coln)上取值相等,且(Col1, Col2, …, Coln)只出现一次
示例: 求所有教师的任课情况并按教师号排序(没有任课的教师也需列在表中)
Select Teacher. T#, Tname, Cname
From Teacher Left Outer Join Course
ON Teacher.T# = Course.T#
Order by Teacher.T# ASC ;