数据库复习1.docx
- 文档编号:7598192
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:17
- 大小:27.85KB
数据库复习1.docx
《数据库复习1.docx》由会员分享,可在线阅读,更多相关《数据库复习1.docx(17页珍藏版)》请在冰点文库上搜索。
数据库复习1
一、第四章
规范化问题包括:
函数依赖、范式、模式
好的关系模式:
尽可能少的数据冗余、没有插入异常、没有删除异常、没有更新异常;
数据依赖包括:
函数依赖、多值依赖、连接依赖。
函数依赖:
关系模式中的各属性之间相互依赖、相互制约的联系称为函数依赖。
SCD(SNo,SN,Age,Dept,MN,CNo,Score)
设关系模式R(U,F),U是属性全集,F是U上的函数依赖集合,X,Y是U的子集,在R中的任意可能关系r中,对于X的每一具体值,Y都有唯一的具体值与之对应,则称X决定函数Y,或Y函数依赖于X,记作X→Y。
说明:
函数依赖是语义范畴的概念
一个关系中,函数依赖成立与否只能由关系中的具体语义来确定,而不能按照其形式化定义来证明。
因为函数依赖是对现实客观事物的一种描述。
如:
SN→Age只在没有重名的条件下成立。
平凡函数依赖与非平凡函数依赖:
对于X→Y,若Y包含于X,则是平凡函数依赖,否则是非平凡依赖。
完全函数依赖与部分函数依赖
设有关系模式R(U),U是属性全集,X和Y是U的子集:
如果X→Y,并且对于X的任何一个真子集X′,都有X′不能决定Y,则称Y对X完全函数依赖,记作X→Y。
箭头上f
如果X→Y,并且对于X的某个真子集X′,有
X′→Y,则称Y对X部分函数依赖,记作X→Y。
箭头上p
传递函数依赖:
设有关系模式R(U),U是属性全集,X,Y,Z是U的子集
若X→Y,但Y不能决定X,而Y→Z(Y不属于X,Z不属于Y),则称Z对X传递函数依赖,记作:
X→Z。
箭头上加t。
如果Y→X,则X相互决定Y,这时称Z对X直接函数依赖,而不是传递函数依赖。
关系模式的分解:
SCD(SNo,SN,Age,
Dept,MN,CNo,Score):
S(SNo,SN,Age,Dept)
SC(SNo,CNo,Score)
D(Dept,MN)
范式:
1NF—2NF—3NF—BCNF—4NF—5NF
1NF包含2NF,以此类推。
第一范式1NF:
最基本的规范形式;如果关系R中所有的属性都为简单属性,即每个属性都不可再分,则称R属于第一范式。
第二范式:
如果R属于第一范式,且每个非主属性(不包含在任何候选键的属性)都完全函数依赖于R的主关系键,则称R属于第二范式。
推论:
(1)、从1NF关系中消除非主属性对主关系键的部分函数依赖,则可得到2NF关系。
(2)、如果R的关系键为单属性,或R的全体属性均为主属性,则R属于2NF。
第三范式:
如果关系模式R属于2NF,且每个非主属性都不传递函数依赖于R的主关系键。
则R属于第三范式。
BC范式:
如果关系模式R属于1NF,且所有的函数依赖X决定Y(Y不属于X),决定因素X都包含了R的一个候选键,则称R属于BC范式。
说明:
1、满足BCNF的关系将消除任何属性(主属性或非主属性)对键的部分函数依赖和传递函数依赖。
2、如果R属于第三范式,则R不一定是BCNF。
如果R属于BCNF,则R也属于第三范式。
第四范式:
多值依赖:
设有关系模式R(U),U是属性全集,X、Y、Z是属性集U的子集,且Z=U-X-Y;如果对于R的任一关系,对于X的一个确定值,存在Y的一组值与之对应,且Y的这组值仅仅决定于X的值而与Z值无关,此时称Y多值依赖于X,或X多值决定Y,记作X→→Y。
若X→→Y且Z=U-X-Y≠Φ,则称X→→Y是非平凡的多值依赖,否则称为平凡的多值依赖。
P166例子
设有一关系模式R(U),U是其属性全集,X、Y是U的子集,D是R上的数据依赖集。
如果对于任一多值依赖X→→Y,此多值依赖是平凡的,或者X包含了R的一个候选关键字,则称R是第四范式的关系模式,记为R∈4NF。
说明:
一个BCNF的关系模式不一定是4NF
4NF的关系模式必定是BCNF的关系模式
关系模式规范化:
一事一地
第5章:
数据库的安全性是指保护数据库以防止非法使用所造成的数据泄露、更改或破坏。
安全性控制是指要尽可能地杜绝所有可能的数据库非法访问。
安全性控制的一般方法
用户标识和鉴定、用户存取权限控制、定义视图、数据加密、审计(Audit)
事务:
事务是数据库系统中执行的一个工作单位,它是由用户定义的一组操作序列。
一个事务可以是一组SQL语句、一条SQL语句或整个程序,一个应用程序可以包括多个事务。
事物的特征:
原子性、一致性、隔离性、持久性
数据库并发性的含义:
为了充分利用数据库资源,很多时候数据库用户都是对数据库系统并行存取数据,这样就会发生多个用户并发存取同一数据块的情况,如果对并发操作不加控制可能会产生不正确的数据,破坏数据的完整性。
数据库的并发操作导致的数据库不一致性主要有以下三种:
丢失更新
当两个事务T1和T2读入同一数据,并发执行修改操作时,T2把T1或T1把T2的修改结果覆盖掉,造成了数据的丢失更新问题,导致数据的不一致。
污读
事务T1更新了数据R,事务T2读取了更新后的数据R,事务T1由于某种原因被撤销,修改无效,数据R恢复原值。
事务T2得到的数据与数据库的内容不一致,这种情况称为“污读”。
不可重读
事务T1读取了数据R,事务T2读取并更新了数据R,当事务T1再读取数据R以进行核对时,得到的两次读取值不一致,这种情况称为“不可重读”。
实现并发控制的方法主要有两种:
封锁(Lock)技术和时标(Timestamping)技术。
基本的封锁类型有两种
排它型封锁(ExclusiveLock)
排它型封锁又称写封锁,简称为X封锁,它采用的原理是禁止并发操作。
共享封锁(ShareLock)
共享封锁又称读封锁,简称为S锁,它采用的原理是允许其他用户对同一数据对象进行查询,但不能对该数据对象进行修改。
活锁(Livelock)
当某个事务请求对某一数据进行排它性封锁时,由于其他事务对该数据的操作而使这个事务处于永久等待状态,这种状态称为活锁。
死锁(Deadlock)
在同时处于等待状态的两个或多个事务中,其中的每一个在它能够进行之前,都等待着某个数据,而这个数据已被它们中的某个事务所封锁,这种状态称为死锁。
死锁的预防
一次加锁法:
每个事物必须将所有要使用的数据对象全部依次加锁,并要求加锁成功,只要一个加锁不成功,表示本次加锁失败,则应该立即释放所有加锁成功的数据对象,然后重新开始加锁。
顺序加锁法:
是预先对所有可加锁的数据对象规定一个加锁顺序,每个事务都需要按此顺序加锁,在释放时,按逆序进行。
数据库的恢复:
系统必须具有检测故障并把数据从错误状态中恢复到某一正确状态的功能,这就是数据库的恢复。
数据库恢复的基本原理就是利用存储在系统其他地方的冗余数据来修复。
恢复系统应该提供两种类型的功能:
生成冗余数据:
1、登记日记文件、数据转储
对可能发生的故障作某些准备
冗余重建
利用这些冗余数据恢复数据库
第3章:
Ø建立一个选课SC表,定义SNo+CNo为SC的主键,定义SNo,CNo为SC的外部键
CREATETABLESC
(SNoVARCHAR(50)NOTNULLCONSTRAINTS_ForeFOREIGNKEYREFERENCESS(SNo),
CNoVARCHAR(50)NOTNULLCONSTRAINTC_ForeFOREIGNKEYREFERENCESC(CNo),
ScoreDECIMAL(4,1),
CONSTRAINTS_C_PrimPRIMARYKEY(SNo,CNo)
表格修改
1).在S表中增加一个班号列和住址列。
ALTERTABLES
ADD
Class_NoVARCHAR(6),
AddressVARCHAR(40)
2).在SC表中增加完整性约束定义,使Score在0~100之间。
执行下列语句添加约束
ALTERTABLESC
ADD
CONSTRAINTScore_ChkCHECK(ScoreBETWEEN0AND100)
数据编辑:
Ø增加数据;在S表中添加一条学生记录(学号:
S21、姓名:
郑冬、性别:
女、年龄:
、系别:
计算机)。
insertS(AGE,DEPT,SEX,SN,SNO)values(17,'计算机','女','赵亦','S1')
2)修改数据
Ø把刘伟老师转到信息系
UPDATET
SETDept='信息'
WHERESN='刘伟
Ø将所有学生的年龄增加岁
UPDATES
SETAge=Age+1
Ø将编号为S1的学生姓名改为“张三”,年龄院系改为“管理”
UPDATES
SETSN='张三',DEPT='管理'
WHERESNO='S1
Ø将刘伟老师转回计算机系
写出SQL语句。
3)删除数据
Ø删除刘伟老师的记录。
DELETE
FROMT
WHERETN='刘伟'
Ø删除学生赵亦的记录
写出删除SQL语句
DELETE
FROMs
WHEREsn='赵亦'
Sno为S表的主键,是SC表的外键,删除S表的内容要先把SC表对应内容删掉。
Ø删除“信息系”的学生记录
写出删除SQL语句
DELETE
FROMs
WHEREdept=’信息’
先把SC表中的相关内容删掉。
5.数据查询
首先还原备份的数据。
●简单查询
1)查询所有学生
SELECT*FROMS
2)查询全体学生的姓名、学号和年龄。
SELECTSN,SNo,Age
FROMS
3)查询全体学生的姓名、学号和年龄。
并将查询出来的SN,SNO字段标题重命名
SELECTSNas姓名,SNoas学号,Age
FROMS
●条件查询
3)查询选修课程号为‘C1’的学生的学号和成绩
SELECTSNo,Score
FROMSC
WHERECNo='C1'
4)查询成绩高于85分的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHEREScore>85
5)查询选修C1或C2的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHERECNoIN('C1','C2')
6)查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHERE(CNo='C1'ORCNo='C2')AND(Score>=85)
7)查询没有选修C1,也没有选修C2的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHERECNoNOTIN('C1','C2')
8)查询工资在1000至1500元之间的教师的教师号、姓名及职称。
SELECTTNo,TN,Prof
FROMT
WHERESalBETWEEN1000AND1500
SELECTTNo,TN,Prof
FROMT
WHERESal>=1000ANDSAL<=1500
比较BETWEEN1000AND1500与Sal>=1000ANDSAL<=1500的效果
9)查询工资不在1000至1500之间的教师的教师号、姓名及职称。
SELECTTNo,TN,Prof
FROMT
WHERESalNOTBETWEEN1000AND1500
用>=<=运算符改写SalNOTBETWEEN1000AND1500
10)查询所有姓张的教师的教师号和姓名。
SELECTTNo,TN
FROMT
WHERETNLIKE'张%'
11)查询姓名中第二个汉字是“力”的教师号和姓名。
SELECTTNo,TN
FROMT
WHERETNLIKE'_力%'
12)查询没有考试成绩的学生的学号和相应的课程号。
SELECTSNo,CNo
FROMSC
WHEREScoreISNULL
13)求学号为S1学生的总分和平均分。
SELECTSUM(Score)ASTotalScore,AVG(Score)ASAveScore
FROMSC
WHERESNo='S1'
14)求选修C1号课程的最高分、最低分及之间相差的分数。
SELECTMAX(Score)ASMaxScore,MIN(Score)ASMinScore,
MAX(Score)-MIN(Score)ASDiff
FROMSC
WHERECNo='C1'
15)求学校中共有多少个系。
SELECTCOUNT(DISTINCTDept)ASDeptNum
FROMS
16)统计有成绩同学的人数。
SELECTCOUNT(Score)
FROMSC
查看SELECTCOUNT(sno)FROMSC的执行结果,与上述结果是否一样,为什么?
17)利用特殊函数COUNT(*)求计算机系学生的总数。
SELECTCOUNT(*)FROMS
WHEREDept='计算机'
●分组查询
1)查询各个教师的教师号及其任课的门数。
SELECTTNo,COUNT(*)ASC_Num
FROMTC
GROUPBYTNo
2)查询选修两门以上课程的学生的学号和选课门数。
SELECTSNo,COUNT(*)ASSC_Num
FROMSC
GROUPBYSNo
HAVING(COUNT(*)>=2)
●查询的排序
3)查询选修C1的学生学号和成绩,并按成绩降序排列。
SELECTSNo,Score
FROMSC
WHERE(CNo='C1')
ORDERBYScoreDESC
4)查询选修C2、C3、C4或C5课程的学号、课程号和成绩,查询结果按学号升序排列,学号相同再按成绩降序排列。
SELECTSNo,CNo,Score
FROMSC
WHERE(CNoIN('C2','C3','C4','C5'))
ORDERBYSNo,ScoreDESC
5)求选课在三门以上且各门课程均及格的学生的学号及其总成绩,查询结果按总成绩降序列出。
SELECTSNo,SUM(Score)ASTotalScore
FROMSC
WHERE(Score>=60)
GROUPBYSNo
HAVING(COUNT(*)>=3)
ORDERBYSUM(Score)DESC
●子查询
6)查询与“刘伟”老师职称相同的教师号、姓名
SELECTTNo,TN
FROMT
WHEREProf=(SELECTProf
FROMT
WHERETN='刘伟')
7)查询讲授课程号为C05的教师姓名。
方法1:
SELECTTN
FROMT
WHERE(TNo=ANY(SELECTTNo
FROMTC
WHERECNo='C05'))
方法2:
SELECTTN
FROMT
WHERETNoIN(SELECTTNo
FROMTC
WHERECNo='C05')
方法3:
selecttn
fromt
whereexists(
select*fromtc
wheretno=t.tnoandcno='c05'
)
如果要求查询没有讲授C05课程的教师姓名,语句如何写,有哪些写法?
8)查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。
方法1:
SELECTTN,Sal
FROMT
WHERE(Sal>ALL(SELECTSalFROMT
WHEREDept='计算机'))
AND(Dept<>'计算机')
方法2:
SELECTTN,Sal
FROMT
WHERE(Sal>(SELECTMAX(Sal)FROMT
WHEREDept='计算机'))
AND(Dept<>'计算机')
9)查询其他系中比计算机系某一教师工资高的教师的姓名和工资。
方法1:
SELECTTN,Sal
FROMT
WHERE(Sal>ANY(SELECTSal
FROMT
WHEREDept='计算机'))
AND(Dept<>'计算机')
方法2:
SELECTTN,Sal
FROMT
WHERESal>(SELECTMIN(Sal)
FROMT
WHEREDept='计算机')
ANDDept<>'计算机'
10)查询选修了全部课程的学生姓名。
注:
首先在SC表中补充一些记录,使得某些学生选修了所有课程。
然后执行查询,比较查询的结果是否和实际一致。
方法1:
SELECTSNFROMS
WHERENOTEXISTS(
select*fromc
whereNOTexists(select*fromsc
whereSNO=S.SNOANDcno=o
)
)
看看下述语句是否能够达到同样的查询效果,试比较两种查询思路的特点。
SELECTSNFROMS
WHERESNOIN(
SELECTSNO
FROMSC
GROUPBYSNO
HAVINGCOUNT(*)=(SELECTCOUNT(*)FROMC)
)
思考:
统计没有任何学生选修的课程名称,如何查询?
●合并查询
11)从SC数据表中查询出学号为“S1”同学的学号和总分,再从SC数据表中查询出学号为“S5”的同学的学号和总分,然后将两个查询结果合并成一个结果集。
SELECTSNoAS学号,SUM(Score)AS总分
FROMSC
WHERE(SNo='S1')
GROUPBYSNo
UNION
SELECTSNoAS学号,SUM(Score)AS总分
FROMSC
WHERE(SNo='S5')
GROUPBYSNo
●连接查询
12)查询“刘伟”老师所讲授的课程,要求列出教师号、教师姓名和课程号。
方法1:
SELECTT.TNo,TN,CNo
FROMT,TC
WHERE(T.TNo=TC.TNo)AND(TN='刘伟')
方法2:
SELECTT.TNo,TN,CNo
FROMTINNERJOINTC
ONT.TNo=TC.TNo
WHERE(TN='刘伟')
13)查询所有选课学生的学号、姓名、选课名称及成绩。
SELECTS.SNo,SN,CN,Score
FROMS,C,SC
WHERES.SNo=SC.SNoANDSC.CNo=C.CNo
14)查询每门课程的课程名、任课教师姓名及其职务、选课人数。
SELECTCN,TN,Prof,COUNT(SC.SNo)
FROMC,T,TC,SC
WHERET.TNo=TC.TNoANDC.CNo=TC.CNoANDSC.CNo=C.CNo
GROUPBYSC.CNo
15)查询所有学生的学号、姓名、选课名称及成绩(没有选课的同学的选课信息显示为空)。
SELECTS.SNo,SN,CN,Score
FROMS
LEFTOUTERJOINSC
ONS.SNo=SC.SNo
LEFTOUTERJOINC
ONC.CNo=SC.CNo
16)查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。
方法1:
SELECTX.TN,X.SalAS
Sal_a,Y.SalASSal_b
FROMTASX,TASY
WHEREX.Sal>Y.Sal
ANDY.TN='刘伟'
方法2:
SELECTX.TN,X.Sal,Y.Sal
FROMTASXINNERJOIN
TASY
ONX.Sal>Y.Sal
ANDY.TN='刘伟'
方法3:
SELECTR1.TN,R1.Sal,R2.Sal
FROM
(SELECTTN,SalFROMT)ASR1
INNERJOIN
(SELECTSalFROMT
WHERETN='刘伟')ASR2
ONR1.Sal>R2.Sal
17)有选课的学生,分别选了那些课程
selects.sn,
fromsc
leftjoinsonsc.sno=s.sno
leftjoincono=o
18)所有的学生,选了那些课程
selects.sn,
froms
leftjoinsconsc.sno=s.sno
leftjoincono=o
19)所有学生的选课门数
selects.sn,count(s.sn),count(o)
froms
leftjoinscons.sno=sc.sno
groupbys.sn
20)已选课学生的选课门数
selects.snas学生,count(*)as'选课数'
fromsc
leftjoinsonsc.sno=s.sno
groupbys.sn
●其他常用函数
建立如下结构表,表名命名为t_ys
表中的内容如下:
20)字段间的代数运算
查看以下语句的执行效果:
selectf_i2/f_iasf_1,整除保留整数f_i2的类型为int
f_d/f_iasf_2,正常除f_d的类型为decimal
cast(f_i2asdecimal(18,2))/f_iasf_3把f_i2的类型转换
fromt_ys
查看f_1与f_2、f_3的差别
21)ceiling,floor,round函数的使用
selectceiling(1.3)selectfloor(1.9)selectround(1.564,1)
22)常用字符串函数
selectsubstring(f_s,1,3)asf_1,
charindex('c',f_s)asf_2,
substring(f_s,1,charindex('c',f_s))asf_3
fromt_ys
数据编辑
●求出各系教师的平均工资,把结果存放在新表AvgSal中。
1)首先建立新表
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 复习