SQL语言之查询与视图

重点与难点

SQL-SELECT: IN | NOT IN, some | all, Exists | NOT Exists
SQL_SELECT: 聚集函数, DROUP BY, HAVING
视图及其应用

一. 子查询

集合成员资格：某一元素是否是某一个集合的成员
集合之间的比较：某一个集合是否包含另一个集合
集合基数的测试：测试集合是否为空、是否存在重复元组

子查询返回一个集合，是出现在Where子句中的Select语句

三种类型的子查询：(NOT) IN—子查询, some/ all—子查询, (NOT) Exists—子查询

二. SQL语言复杂查询— (NOT) IN子查询

1. (NOT) IN 子查询

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

例：求既学过001号课程，又学过002号课程的学生的学号

1
2
3

Select Sid From SC
Where Cid='001' and Sid in
	(Select Sid From SC Where Cid='002')

例：列出没学过李明老师讲授课程的所有同学的姓名

Select Sname From Student
Where Sid not in
	(Select Sid From Course C, SC, Teacher T
	Where T.Tname='李明' and SC.Cid=C.Cid and T.Tid=C.Tid)

2. 非相关子查询

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

3. 相关子查询

相关子查询：内层查询需要依靠外层查询的某些参量作为限定条件才能进行的子查询。

外层向内层传递的参量需要使用外层的表名或表别名来限定。

只能由外层向内层传递参数。

三. SQL语言复杂查询— Some 与 All 子查询

1. Some 与 All 子查询

1	表达式 θ some (子查询)

如果表达式的值至少与子查询结果的某一个值相比较满足关系，则上面表达式的结果便为真；

1	表达式 θ all (子查询)

如果表达式的值与子查询结果的所有值相比较都满足 关系，则上面表达式的结果便为真。

2. 示例

例：找出001号课成绩不是最高的所有学生的学号

1
2
3

Select Sid From SC
Where Cid = “001” and
	Score < some (Select Score From SC Where Cid = “001”);

例：找出所有课程都不及格的学生姓名（相关子查询）

1
2
3

Select Sname From Student
Where 60 > all
	(Select Score From SC Where Sid = Student.Sid );

例：找出张三同学成绩最低的课程号

1
2
3

Select Cid From SC, Student S
Where Sname = “张三” and S.Sid=SC.Sid and Score <= all
	(Select Score From SC Where Sid=S.Sid);

3. 注意

等价表达式：

表达式 = some (子查询) 与 表达式 in (子查询)
表达式 not in (子查询) 与 表达式 <> all (子查询)

四. SQL语言复杂查询— (NOT) EXISTS 子查询

1. EXISTS 与 NOT EXISTS 谓词子查询

1	[not] Exists (子查询)

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

2. 示例

例： 检索选修了赵三老师主讲课程的所有同学的姓名

Select DISTINCT Sname From Student
Where exists (Select * From SC, Course, Teacher
		Where SC.Cid = Course.Cid	and SC. Sid = Student.Sid
		and Course.Tid = Teacher.Tid and Tname = ‘赵三’ ) ;

不加not形式的Exists谓词可以不用，写作：

1
2
3

Select DISTINCT Sname From Student, SC, Course, Teacher
Where SC.Cid = Course.Cid and SC.Sid = Student.Sid
	and	Course.Tid = Teacher.Tid and Tname = ‘赵三’;

例： 检索学过001号教师主讲的所有课程的所有同学的姓名

即，检索不存在有一门001号教师主讲的课程没学过的同学???????????

1
2
3

Select Sname From Student Where not exists
	(Select * From Course Where Course.Tid='001' and not exists
    	(Select * From SC Where Sid=Student.Sid and Cid=Course.Cid));

例： 列出没学过李明老师讲授任何一门课程的所有同学的姓名

Select Sname From Student Where not exists
	(Select * From Course, SC, Teacher
	Where Tname=‘李明’ and Course.Tid = Teacher.Tid
		and Course.Cid = SC.Cid and Sid = Student.Sid);

例： 列出至少学过98030101号同学学过所有课程的同学的学号

Select DISTINC Sid From SC SC1
Where not exists
	(Select * From SC SC2
	Where SC2.Sid = ‘98030101’ and not exists
		(Select * From SC Where Cid = SC2.Cid and Sid = SC1.Sid));

$$ {Sid,Cid}(SC) {Cid}(_{Sid='98030101'}(SC)) \

{ t[Sid]| tSC (u SC u[Sid]='98030101')((wSC)(w[Sid]=t[Sid]w[Cid]=u[Cid])) } $$

例： 已知SPJ (Sno, Pno, Jno, Qty)，其中Sno供应商号，Pno零件号，Jno工程号，Qty数量，列出至少用了供应商S1供应的全部零件的工程号

Select DISTINCT Jno From SPJ SPJ1
Where not exists
	(Select * From SPJ SPJ2 Where SPJ2.Sno = ‘S1’ and not exists
		(Select * From SPJ SPJ3 Where SPJ3.Pno = SPJ2.Pno and SPJ3.Jno = SPJ1.Jno));

五. 利用SQL语言进行结果计算与聚集计算

1. 计算结果

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

expr可以是常量、列名、或由常量、列名、特殊函数及算术运算符构成的算术运算式
agfunc()是一些聚集函数

例： 求有差额(差额>0)的任意两位教师的薪水差额

1
2
3

Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary – T2.Salary
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary;

例： 依据学生年龄求学生的出生年份，当前是2015年

1 2	Select S.Sid, S.Sname, 2015 – S.Sage+1 as Syear From Student S;

2. 聚集函数

SQL提供了五个作用在简单列值集合上的内置聚集函数agfunc，分别是： COUNT、SUM、AVG、MAX、MIN

例： 求计算机系教师的工资总额

1 2	Select Sum(Salary) From Teacher T, Dept Where Dept.Dname = ‘计算机’ and Dept.Did = T.Did;

例： 求数据库课程的平均成绩

1 2	Select AVG(Score) From Course C, SC Where C.Cname = ‘数据库’ and C.Cid = SC.Cid;

六. 利用 SQL 语言进行分组查询与分组过滤

1. 分组查询

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

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

2. 分组查询示例

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

1 2	Select Sid, AVG(Score) From SC Group by Sid;

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

1 2	Select Cid, AVG(Score) From SC Group by Cid;

3. 分组过滤

分组过滤：若要对集合（即分组）进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除。

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

Having子句，又称分组过滤子句，需要有Group by子句支持。

4. 分组过滤示例

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

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

例： 求有10人以上不及格的课程号

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

5. 分组过滤条件与WHERE条件之对比

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

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

例： 该同学那几门不及格课程的平均成绩

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

七. SQL实现现代关系代数操作

1. 并交差的处理

SQL 语言：并运算 UNION，交运算 INTERSECT，差运算 EXCEPT

1	子查询 Union \| Intersect \| Except [ALL] 子查询

不带ALL自动删除重复元组，若保留ALL则也保留重复元组。（对集合和对包的操作的区别）

假设子查询1的一个元组出现m次，子查询2的一个元组出现n次，则该元组在：

子查询1 Union ALL 子查询2 ，出现次
子查询1 Intersect ALL 子查询2 ，出现次
子查询1 Except ALL 子查询2 ，出现次

例： 求学过002号课的同学或学过003号课的同学学号

1
2
3

Select Sid From SC Where Cid='002'
UNION
Select Sid From SC Where Cid='003';

例： 求既学过002号课，又学过003号课的同学学号

1
2
3

Select Sid From SC Where Cid='002'
INTERSECT
Select Sid From SC Where Cid='003';

Intersect 没有增加SQL语言的表达能力（可以用 IN 来代替）

例： 假定所有学生都有选课，求没学过002号课程的学生学号

1
2
3

Select DISTINCT	Sid From SC
EXCEPT
Select Sid From SC Where	Cid = ‘002’;

Except 也没有增加SQL语言的表达能力（可以用 Not Exists 来代替）

但有些DBMS并不支持这些运算

2. 空值 NULL 的处理

空值检测：注意不是等于（=）！！！

1	is [not] null

例： 找出年龄值为空的学生姓名

1 2	Select Sname From Student Where Sage is null;

DBMS 空值处理：

除 is [not] null 之外，空值不满足任何查找条件
null参与算术运算，则该算术结果为null
null参与比较运算，则结果可视为false（在SQL-92中可看成 unknown）
null参与聚集运算，则**除 count(*) 之外其它聚集函数都忽略** null

3. 内连接、外连接

Select 列名 [[, 列名]...]
From 表名1 [NATURAL]
		  [INNER | {LEFT | RIGHT | FULL} [OUTER]] JOIN 表名2
	{ON 连接条件 | Using (Colname {, Colname...})}
[Where	检索条件]...;

natural：自然连接，公共属性取值相同，且只出现一次
on <连接条件>：满足条件，公共属性出现两次
using (Col1, Col2, ..., Coln)：连接关系的公共属性子集

例： 求所有教师的任课情况并按教师号排序

Select Teacher.Tid, Tname, Cname
From Teacher Inner Join	Course
	ON Teacher.Tid = Course.Tid
Order by Teacher.Tid	ASC;

例： 求所有教师的任课情况（没有任课的教师也需列在表中）

Select Teacher. Tid, Tname, Cname
From Teacher Left Outer Join Course
	ON Teacher.Tid = Course.Tid
Order by Teacher.Tid	ASC ;

八. SQL 语言之视图及其应用

1. 视图的概念和结构

对应概念模式的数据在SQL中被称为基本表(Table)，而对应外模式的数据称为视图(View)。视图不仅包含外模式，而且包含其E-C映像。

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

2. 视图的定义

1 2	create view view_name [(列名 [，列名]...)] as 子查询 [with check option]

with check option指明当对视图进行insert，update，delete时，要检查进行insert/update/delete的元组是否满足视图定义中子查询中定义的条件表达式

例： 定义一个视图 CompStud 为计算机系的学生，通过该视图可以将 Student 表中其他系的学生屏蔽掉

Create View	CompStud AS
	(Select * From Student
	Where Did in (Select Did From Dept
				Where Dname = ‘计算机’));

例： 定义一个视图Teach为教师任课的情况，把Teacher表中的个人隐私方面的信息屏蔽掉，仅反映其教哪门课及其学分等。

Create View Teach AS
	(Select T.Tname, C.Cname, Credit
    From Teacher T, Course C
    Where T.Tid=C.Tid);

3. 视图的使用

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

例： 检索计算机系的年龄小于20的所有学生

1	Select * From CompStud Where Sage < 20;

例： 检索主讲数据库课程的教师姓名

1	Select T.Tname From Teach T Where T.Cname = ‘数据库’;

定义视图，有时可方便用户进行检索操作

例： 定义视图StudStat，描述学生的平均成绩、最高成绩、最低成绩等，基于视图检索某一学生平均成绩

Create View StudStat(Sid, Sname, AvgS, MinS, MaxS, CNT)
as (Select Sid, Sname, AVG(Score), MIN(Score), Max(Score), Count(*)
    From Student S, SC Where S.Sid = SC.Sid
	Group by S.Sid);

Select Sname, AvgS From StudStat Where Sname = ‘张三’;

4. 视图的更新

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

对于由单一Table子集构成的视图，即如果视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主键，则可以更新

不能更新的情况：

视图的select目标列包含聚集函数
如果视图的select子句使用了unique或distinct
如果视图中包括了group by子句
如果视图中包括经算术表达式计算出来的列
如果视图是由单个表的列构成，但并没有包括主键

5. 视图的撤销

1	Drop View view_name;

FuLucas's blog