【数据库】期末复习：SQL语句、关系代数的运算、范式的定义和判断、求最小函数依赖集、ER图转关系模式

SQL语句

查询select

学生表s，课程表c，学生选课表sc。

查询被学生(学号为A)选修的课程有几个？

select count(cno) from sc
where sno='A'

查询年龄在18-19之间的学生信息。

select * from s
where sage between 18 and 19

查询年龄不在20-23之间的学生姓名、系别和年龄。

select Sname,Sdept,Sage from s
where Sage not between 20 AND 23;

查询CS系、MA系、IS系学生的姓名和性别。

写法1：

select Sname,Ssex from s 
where Sdept in('CS','MA','IS')

写法2：

select Sname,Ssex from s 
where Sdept='CS' or Sdept='MA' or Sdept='IS'

查询姓刘的同学的信息。

select * from s
where sname like '刘%'

如果名字是两个字且姓刘，则为’刘_'。

查询没有先修课的课程信息。

select * from c
where cpno is null

求各门课学生的平均成绩，显示课程号及相应的平均成绩

select cno,avg(grade) from sc
group by cno

查询全体学生情况，查询结构按所在系的系号升序排列，同一系中的学生按年龄降序排列。

select * from s 
order by Sdept ASC,Sage DESC

查询选修了课程的学生人数。

select COUNT(DISTINCT Sno) from sc

查询学生20120512选修课程的总学分数。

select SUM(Credit) from sc,c where Sno='20120512'AND c.cno=sc.cno

求各个课程号及相应选修的人数。

select Cno,COUNT(Sno) from sc GROUP BY Cno

查询有2个以上学生选修的课程的课程号。

select cno from sc
group by cno
having count(sno)>2

group by了就不能where 要having

查询选修了3门以上课程的学生学号。

select Sno from sc 
GROUP BY Sno
HAVING COUNT(*)>3

查询平均成绩大于等于90的学生学号和平均成绩。

select Sno,AVG(Grade) from sc
GROUP BY Sno HAVING AVG(Grade)>=90

查询每个学生及其选修课程的情况。

select S*,SC* from s,sc
where s.sno=sc.sno

查询选修了2号课程并且成绩在90以上的所有学生的学号和姓名。

select s.Sno,Sname from s,sc
where sc.cno='2' AND sc.Grade>90 AND sc.Sno=s.Sno

查询学生选修课程的情况要求，显示学生名、课程名、成绩（三表查询)。

select sname,cname,grade from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno

查询选修了全部课程的学生姓名

select sname from s//这里是全选课的
where not exists
(
	//这里是没选课的
	select * from c
	where not exists
	(
		//这里是选了课的
		select * from sc
		where s.sno=sc.sno and c.cno=sc.cno
	)
)

找出每个学生超过他自己选修课程平均成绩的课程号。

select Sno,Cno
from SC x
where Grade>=
(
	select AVG(Grade) from SC y
	where x.Sno=y.Sno
)

查询非CS系中比CS系中任意一个学生年龄小的学生姓名和年龄。

select Sname,Sage from s
where Sage < ANY(
					select Sage from s
					where Sdept='CS'
				)
				AND Sdept <> 'CS'

查询非CS系中比CS系所有学生年龄小的学生姓名和年龄。

select Sname,Sage from s
where Sage < ALL(
					select Sage from s where Sdept='CS'
				)
				AND Sdept <> 'CS';

查询所有选修了1号课程的学生姓名。

方法1：

select Same from s where EXISTS
(
	select * from sc
	where Sno=S.Sno AND Cno='1'
);

方法2：

select Sname from s,sc where Cno='1' AND s.Sno=sc.Sno

查询没有选修1号课程的学生姓名。

select Sname from s where NOT EXISTS
(
	select * from sc
	where Cno='1' AND Sno=s.Sno
)

集合操作：

• 并UNION
• 交INTERSECT——集合交集其实就是条件AND
• 差EXCEPT

查询CS系的学生和年龄不大于19的学生。

select * from s where Sdept='CS'
UNION 
select * from s where Sage<=19;

查询既选修了课程1又选修了课程2的学生学号。

写法1：

select Sno from sc where 
Cno='1' AND Sno IN
(
	select Sno from sc where Cno='2';
)

写法2：

select Sno from s where Cno='1'
INTERSECT
select Sno from s where Cno='2'

查询CS系学生与年龄不大于19岁学生的学号的差集。

写法1：

select Sno from s where Sdept='CS' 
EXCEPT
select Sno from s where Sage<=19;

写法2：

select Sno from s where Sdept='CS' AND Sage>19;

检索至少学过课程号为“1”和“2”的学生信息，包括学号、姓名和专业；

select Sno,Sname,Sdept from s where Sno in
(
	select x.Sno from sc as x,sc as y
	where x.Cno=1 AND y.Cno=2 AND x.Sno=y.Sno
)

其他

创建表dept_age，包含2个字段：系别和平均年龄。按系分组求平均年龄，再把系名和平均年龄存入新表中。

create table dept_age
(
	dept char(20) primary key,/*是主码*/
	avg-age smallint
)

如果是外码，参照的是A表中的dept，则要这样：dept char(20) references A(dept)

删除S表。

drop table s

将女生的年龄减去1岁。

update sage     /*表名*/
set sage=sage-1  /*对属性sage的操作*/
where ssex='女'

将信息科学系的全体学生成绩设为100分。

update sc
set grade=100
where sno in
(
	select sno from s
	where dept='信息科学'
)

删除200215121学生的选课信息。

delete from sc
where sno='200215121'

将成绩表中计算机科学系的学生信息删除。

delete from sc
where sno in
(
	select sno from s
	where dept='计算机科学'
)

建立视图view grade，内容为:学生姓名、课程名、成绩。

create view as select 
sname,cname,grade
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno

视图

定义视图
建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

Create view IS_STUDENT
AS
select * from s where Sdept='IS'
WITH CHECK OPTION

建立信息系选修了1号课程的学生的视图（学号、姓名、成绩）。

Create view IS_INFO(Sno,Sname,Grade)
AS
select Sno,Sname,Grade from s,sc 
where S.dept='IS' AND s.Sno=sc.Sno AND Cno='1'

将学生的学号和平均成绩定义为一个视图。

create view S_G(Sno,Gavg)
AS
select Sno,AVG(Grade) from sc
Group by Sno

查询视图
跟查询基本表相似，视图是虚表，直接当作表查询即可。

在信息系学生的视图中找出年龄小于20岁的学生。

select Sno,Sage from IS_Student where Sage<20

修改视图

关系代数的运算

来自【数据库】关系代数基本运算

并、差、笛卡尔积、投影和选择

（a）R∪S 并 （b）R-S 差 （c）R×S 笛卡尔积 （d）πC,A® 投影 （e）σB>’4’ ® 选择

连接

连接是从关系R和S的笛卡尔积中选取属性值满足某一个操作的元组。

例1：选择σ2=4 (R×S)，即R.B=S.D.

自然连接

自然连接：把共同属性进行等值连接。
例1：

例2：

笛卡尔积

来自【数据库系统概论】第一、二章：绪论、关系数据库
类似于运动员握手。
R的第1行要跟S的每一行握手。
R的第2行要跟S的每一行握手。
…
R的第n行要跟S的每一行握手。

详细版（如果完全不了解就看这里）

2.2 《数据库系统概论》之关系操作、关系完整性、关系代数
并、差、交、笛卡尔积、象集

连接：

举个例子：



自然连接：把共同属性进行等值连接。

左连接、右连接、外连接



左外连接：左边有空值的删去——保留左边的，即保留R的
右外连接：右边有空值的删去——保留右边的，即保留S的

除

范式的定义和判断

步骤：

1. 求闭包（左边元素的组合）
2. 候选码：当上一步的左边元素的组合的闭包可以推出所有元素的时候，它就是候选码
3. 主属性：组成候选码的属性就是主属性
4. 非主属性：不是主属性的就是非主属性
5. 判断是哪个范式：NF，2NF，3NF，BCNF

1NF：每个元素不可分割。
如：学生（姓名，性别，家庭成员）——其中姓名和性别是不可分割的（如性别：要么男要么女），但家庭成员是多个元素的mixed（爸妈姐妹兄弟），所以“学生”不是NF。

1NF VS 2NF——2NF：不存在非主属性对候选码的部分依赖。
翻译：在候选码中选出一部分，它可以推出非主属性。
如：
有候选码BC，非主属性D。若有B->D或C->D，则这就是“非主属性对候选码的部分依赖”，所以它不满足2NF，只会是NF。

2NF VS 3NF——3NF：不存在非主属性对候选码的传递依赖。
如：
有候选码AB，且AB->C,C->D,那么其实AB->D，即D传递依赖于候选码AB，则它不满足3NF，最多是2NF。

3NF VS BCNF——BCNF：不存在主属性对候选码的部分依赖和传递依赖

如何求候选码

对候选码的理解：
有这样一个集合，它可以推出所有的属性，但它的任意一个真子集无法推出所有属性。
——刚好这一整个集合才能推出所有属性。

举例：

有F={A->B,B->C,D->E};

则候选码为AD，因为AD可以推出ABCDE，而它的真子集A或D都不能退出ABCDE。

一个F中可以有多个候选码，候选码不唯一。

举例：

有F={A->B,B->C,D->E,E->D};

则候选码为AD、AE。

例题：

解：
注意：U表示所有属性，F表示关系。

步骤：（对字母）

1. 只出现在左边的一定是候选码
2. 只出现在右边的一定不是候选码
3. 左右边都出现的不一定
4. 左右边都不出现的一定是候选码

按照步骤来分析：
A：左右边都出现了，可能
B：只有左边出现，一定是
C：左右边都出现了，可能
D：只有左边出现，一定是
E：左右边都出现了，可能
G：只有右边出现，一定不是

则：
一定是：BD
一定不是：G
可能：ACE

接下来求一定是的闭包。
BD的闭包：BD能推出来的所有属性。表示为(BD)+=BD。(BD的闭包就是BD)

BD的闭包不是全体，则接下来把所有可能的加进去求闭包。
即求(ABD)+、(BCD)+、(BDE)+

(ABD)+=ABCDEG
(BCD)+=ABCDEG
(BDE)+=ABCDEG

所以候选码为ABD、BCD、BDE

一些例题

例题1：
R(A,B,C),F={AB->C}。

解：
候选码：AB。
主属性：A、B。
非主属性：C。

是1NF，2NF（不存在A->C或B->C）,3NF（不存在非主属性对候选码的传递依赖），BCNF（不存在主属性对候选码的部分依赖或传递依赖）。

所以是一个BCNF。

例题2：
R(A,B,C),F={B->C,AC->B}。

解：
候选码：AB、AC。
主属性：A、B、C
非主属性：无。（所以它最少就是一个3NF）

对于B->C，C是主属性，B是候选码的一部分，存在主属性对候选码的部分依赖，因此不是BCNF。

所以是一个3NF。

例题3：
R(A,B,C),F={B->C,B->A,A->BC}。

候选码：A、B
主属性：A、B
非主属性：C

对于B->C，这是直接依赖（因为候选码是B，若候选码是BD，则B是BD的一部分，那就是部分依赖了），所以是2NF。
不存在非主属性对候选码的传递依赖，是3NF。
不存在主属性对候选码的部分/传递依赖，是BCNF。

所以是BCNF。
注意：
A->B->A不是传递依赖。
若要有部分依赖，则候选码不能是单元的。（此题候选码就是单元的）

例题4：
R(A,B,C),F={A->C,A->B}。

解：
候选码：A。
主属性：A。
非主属性：B、C。

答：BCNF。

小技巧：
当一个关系是二元关系组时，则它就是一个BCNF。

对于此题，F={A->C,A->B}可以合成为A->BC，这就是一个二元关系组。

例题5：
R(A,B,C,D),F={A->C,AD->B}。

解：
候选码：AD
主属性：A、D
非主属性：B、C

对于A->C，是非主属性对候选码的部分依赖，故不符合2NF。
是NF。

例题6：
R(A,B,C,D),F={A->C,BC->D}。

解：

如何求候选码可以看这里：第一步，左边元素的组合
候选码：AB
主属性：A、B
非主属性：C、D

对于A->C，是非主属性对候选码的部分依赖，则不满足2NF，则是1NF。

求最小函数依赖集

【数据库系统概论】第六章：关系数据理论

概念：

一个很清晰的讲解：关系数据库理论之最小函数依赖集：下面的最小函数依赖的图都来自这个链接，举例也是

解：

模式分解

模式分解有两个准则：

1. 无损连接性
2. 保持函数依赖

step3中左边一样的是指：
若：F={A->B,A->C…}，则有{ABC}，这里A就是一样的左边。
step4：CE是候选码，且没有出现在分类AD、ED、DB、BCD、DCA中所以就单独分一类（如果出现了就不管它）；GH也要单独分一类。

数据依赖的公理系统

公理系统
理解：

• 自反律：Y属于X，则X可以推出Y——小明在A班，如果能拿到A班所有人的名单，则可以找到小明——大可以找小。
• 增广律：已知X->Y，则XZ->YZ；
• 传递律：X->Y,Y->Z——X->Z;

三个定理

ER图转关系模式

图中下划线（直线）是主码，波浪线是外码。

书p232

有1:1、1:n、n:m三种对应关系。
1:1
任选一个属性添加另一个的主码即可。
如果关系上也有属性，也把它加上。

1:n
在n端加上1端的主码——这是n端的外码。
若关系（三角形）上也有属性，则把它加到n端的属性上。

n:m
关系转换成一个实体，其中的属性是两端的主码，他们共同是这个实体的主码。画下划线+波浪线（但教材上是直接画下划线，所以各位根据自己老师的要求来画哈）。

视频中的例题：

关系模式：

下面截图的理论和例题来自：浅析如何把ER模型转换为关系模式
理论：

例题1

解：

例题2

其他例题：案例讲解如何将ER图转化为关系模型

解：

参考资料

查询语句例题
其他语句例题
范式的定义和判断
ER图转关系模式
数据库系统原理------ER图转化成关系模式
【数据库】关系代数基本运算
【数据库系统概论】第一、二章：绪论、关系数据库
2.2 《数据库系统概论》之关系操作、关系完整性、关系代数
【数据库系统概论】第六章：关系数据理论