MOOC战德臣数据库课程自用笔记_5_SQL复杂查询与视图

SQL复杂查询与视图

一. 子查询

出现在 Where 子句中的 Select 语句被称为子查询（subquery）
三种类型的子查询：

1. (NOT) IN
2. θ-Some/θ-ALL ，θ 为比较运算符
3. (NOT) EXISTS

1. (NOT) IN子查询

判断某一表达式的值是否存在于子查询的结果中

表达式 [not] in (子查询)

例子：列出张三，王三同学的所有信息

Select * From Student 
Where Sname in ("张三", "王三");

2. θ some 子查询

如果表达式的值至少与子查询结果的某一个值比较并满足 θ 关系，则结果为真

表达式 θ some(子查询);

例子：找出001好课程不是最高的所有学生的学号

Select Sno From SC
Where Cno = '001' and
	  Score < some(Select Score From SC where Cno = '001')；

如下两种表达式含义相同：

表达式 = some(子查询)

表达式 in (子查询)

3. θ all 子查询

如果表达式的值至少与子查询结果的所有值比较都满足 θ 关系，则结果为真

表达式 θ all(子查询);

例子1：找出工资最低的教师姓名

Select Tname From Teacher
Where Salary <= all(Select Salary From Teacher);

例子2：找出张三同学成绩最低的课程号（相关子查询）

Select Cno From SC, Student S
Where Sname = '张三' and S.Sno = SC.Sno and
	  Score <= all(Select Score From SC
	  							Where Sno = S.Sno);

如下两种表达式含义相同：

表达式 <> all(子查询)

表达式 not in (子查询)

3. (NOT) EXISTS 子查询

子查询结果中有无元组存在

[not] exists (子查询)

二. （非）相关子查询

1. 非相关子查询

内层查询独立进行，没有涉及任何外层查询相关信息的子查询

2. 相关子查询

内层查询需要依靠外层查询的某些参量作为限定条件才能进行的子查询
外层向内层传递的参量需要使用外层的表名或表别名来限定

• 注意：相关子查询只能由外层查询向内层传递参数，反之则不能；这也称变量的作用域原则

三. 结果计算与聚集计算

Select-From-Where 语句中，Select 子句后面不仅可是列名，而且可是一些计算表达式或者聚集函数，表明在投影的同时直接进行一些运算

Select 列名|expr|agfunc(列名), [[, 列名|expr|agfunc(列名)]...]
From 表名1[, 表名2...]
[Where 检索条件];

• expr 可以是常量，列名，或者由常量，列名特殊函数及算术运算符构成的算术运算式。其中特殊函数的使用需结合各自 DBMS 的说明书
• agfunc() 是一些聚集函数

聚集函数

SQL 提供了 5 个作用在简单列值集合上的内置聚集函数 agfunc，分别是：

1. COUNT ：求个数
2. SUM ：求和
3. AVG ：求平均
4. MAX ：求最大
5. MIN ：求最小

四. 分组查询和分组过滤

1. Group by 分组查询

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

Select 列名|expr|agfunc(列名) [[, 列名|expr|agfunc(列名)]...]
From 表名1 [, 表名2...]
[Where 检索条件]
[Group by 分组条件];

• 分组条件可以是：列名1, 列名2, ...

聚集函数不允许用于 Where 子句中，Where 子句是对每一元组进行条件过滤，而不是对集合进行条件过滤的。

2. Group by ... Having 分组过滤

若要对集合（即分组）进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除
Having 子句，又称为分组过滤子句。需要有 Group by 子句支持。

聚集函数可以用在 Having 子句中。

Select 列名|expr|agfunc(列名) [[, 列名|expr|agfunc(列名)]...]
From 表名1 [, 表名2...]
[Where 检索条件]
[Group by 分组条件 [Having 分组过滤条件]];

例子1：求不及格课程超过两门的同学的学号

Select Sno From SC
Where Score < 60
Group by Sno Having Count(*) > 2;

例子2：求有两门以上不及格课程同学的学号及其平均成绩

Select Sno, Avg(Score) From SC
Where Sno in 
	  (Select Sno From SC
	   Where Score < 60
	   Group by Sno Having Count(*) > 2)
Group by Sno;

3. Having 子句与 Where 子句表达条件的区别

每一个分组检查满足与否的条件要用 Having 子句表达，使用前提是要有 Group by 子句

每一行都要检查满足与否的条件要用 Where 子句表达

五. SQL关系代数（交并差）操作

基本语法形式

子查询 {Union[ALL] | Intersect[ALL] | Except[ALL] 子查询}

• 不带 ALL ：自动删除重复元组
• 带 ALL ：保留重复元组

示例

SQL差运算，假定所有学生都有选课，求没学过 002 号课程的学生学号

Select Distinct Sno From SC
Except
Select Sno From SC Where Cno = '002';

六. 空值处理

在 SQL 标准中和许多现流行的 DBMS 中，空值被用一种特殊的符号 Null 来标记，使用特殊的空值检测函数来获得某列的值是否为空值

is [not] null

null 特点

1. 除了 is [not] null 之外，空值不满足任何查找条件
2. 如果 null 参与算术运算，则该算术表达式的值为 null
3. 如果 null 参与比较运算，则结果可视为 false。在 SQL-92 中可看成 unknown
4. 如果 null 参与聚集运算，则除 Count(*) 之外其他聚集函数都忽略 null

七. 视图

在三级模式两层映像结构中，
对应概念模式的数据在 SQL 中被称为基本表（Table）
对应外模式的数据称为视图（View）

视图不仅包括外模式，而且包括其 E-C 映像

0. SQL 数据库结构

• 基本表是实际存储在存储文件的表，基本表中的数据是需要存储的
• 视图在 SQL 中只存储其由基本表导出视图所需要的公式，即由基本表产生视图的映像信息，其数据并不存储，而是在运行过程中动态产生与维护的
• 对视图数据的更改最终要反映在对基本表的更改上

1. 定义视图

create view 视图名 [(列名[, 列名]...)]
	   as 子查询 [with check option];

• 如果视图的属性名缺省，则默认为子查询结果中的属性名；也可以显式指明其所拥有的列名
• with check option 指明对当前视图进行 insert，update，delete 时，要检查进行 insert/update/delete 的元组是否满足视图定义中子查询中定义的条件表达式

定义好的视图可以像 Table 一样在 SQL 各种语句中使用

2. 视图更新

因为视图不保存数据，对视图的更新最终要反映到对基本表的更新上，而有时，视图定义的映射不是可逆的

例子1（不可更新）：
创建视图

create view S_G(Sno, Savg)
	   as (select Sno, AVG(Score)
	   	   from SC group by Sno);

如下 SQL 不能由 S_G 的更新而更新 SC

update S_G
set Savg = 85
where Sno = '98030101';

例子2（可更新）：
创建视图

create view CStud(Sno, Sname, Sclass)
	   as (select Sno, Sname, Sclass
	       from Student where Dno = '03');

如下 SQL 可以更新

insert into CStud 
values ("98030104", "张三丰", "980301");

3. SQL 视图更新的可执行性

1. 如果视图的 select 目标列包含聚集函数，不能更新
2. 如果视图的 select 子句使用了 unique 或 distinct，不能更新
3. 如果视图中包括 group by 子句，不能更新
4. 如果视图中包括经算术表达式计算出来的列，不能更新
5. 如果视图是由单个表的列构成，但并没有包括主键，不能更新
6. 对于由单个表子集构成的视图，即如果视图是从单个基本表使用选择或者投影操作导出的，并且包含了基本表的主键，则可以更新

4. 撤销视图

Drop View 视图名;