前言

基本SQL查询的另一块内容

正文

一、分组查询

  • 分组：SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算

示例： 求每一个学生的平均成绩

Select S#, AVG(Score) 
From SC
Group by S#;

上例是按学号进行分组，即学号相同的元组划到一个组中并求平均值

mysql去掉不满足条件的分组 sql过滤不满足条件的分组_Group

示例：求每一门课程的平均成绩

Select C#, AVG(Score) 
From SC
Group by C#;

上例是按课号进行分组，即课号相同的元组划到一个组中并求平均值

mysql去掉不满足条件的分组 sql过滤不满足条件的分组_Group_02

  • 聚集函数是不允许用于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。

五、内连接、外连接

关系代数运算中，有连接运算，又分为 θ 连接和外连接

mysql去掉不满足条件的分组 sql过滤不满足条件的分组_Group_03


上例的连接运算由两部分构成：连接类型和连接条件

mysql去掉不满足条件的分组 sql过滤不满足条件的分组_mysql去掉不满足条件的分组_04

连接类型
  • 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 ;