数据库复习1Word下载.docx
《数据库复习1Word下载.docx》由会员分享，可在线阅读，更多相关《数据库复习1Word下载.docx（17页珍藏版）》请在冰豆网上搜索。
最基本的规范形式；
如果关系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的学生姓名改为“张三”，年龄院系改为“管理”
SETSN='
张三'
DEPT='
管理'
WHERESNO='
S1
将刘伟老师转回计算机系
写出SQL语句。
3）删除数据
删除刘伟老师的记录。
DELETE
FROMT
WHERETN='
刘伟'
删除学生赵亦的记录
写出删除SQL语句
FROMs
WHEREsn='
Sno为S表的主键，是SC表的外键，删除S表的内容要先把SC表对应内容删掉。
删除“信息系”的学生记录
WHEREdept=’信息’
先把SC表中的相关内容删掉。
5．数据查询
首先还原备份的数据。
●简单查询
1）查询所有学生
SELECT*FROMS
2）查询全体学生的姓名、学号和年龄。
SELECTSN,SNo,Age
FROMS
3）查询全体学生的姓名、学号和年龄。
并将查询出来的SN,SNO字段标题重命名
SELECTSNas姓名,SNoas学号,Age
●条件查询
3）查询选修课程号为‘C1’的学生的学号和成绩
SELECTSNo,Score
FROMSC
WHERECNo='
C1'
4）查询成绩高于85分的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
WHEREScore>
85
5）查询选修C1或C2的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHERECNoIN（'
，'
C2'
6）查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
FROMSC
WHERE（CNo='
ORCNo='
）AND（Score>
=85）
7）查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。
SELECTSNo,CNo,Score
WHERECNoNOTIN（'
8）查询工资在1000至1500元之间的教师的教师号、姓名及职称。
SELECTTNo,TN,Prof
FROMT
WHERESalBETWEEN1000AND1500
WHERESal>
=1000ANDSAL<
=1500
比较BETWEEN1000AND1500与Sal>
=1500的效果
9）查询工资不在1000至1500之间的教师的教师号、姓名及职称。
WHERESalNOTBETWEEN1000AND1500
用>
=<
=运算符改写SalNOTBETWEEN1000AND1500
10）查询所有姓张的教师的教师号和姓名。
SELECTTNo,TN
WHERETNLIKE'
张%'
11）查询姓名中第二个汉字是“力”的教师号和姓名。
WHERETNLIKE'
_力%'
12）查询没有考试成绩的学生的学号和相应的课程号。
SELECTSNo,CNo
WHEREScoreISNULL
13）求学号为S1学生的总分和平均分。
SELECTSUM（Score）ASTotalScore,AVG（Score）ASAveScore
FROMSC
WHERESNo='
14）求选修C1号课程的最高分、最低分及之间相差的分数。
SELECTMAX（Score）ASMaxScore,MIN（Score）ASMinScore,
MAX（Score）－MIN（Score）ASDiff
WHERECNo='
15）求学校中共有多少个系。
SELECTCOUNT（DISTINCTDept）ASDeptNum
FROMS
16）统计有成绩同学的人数。
SELECTCOUNT（Score）
查看SELECTCOUNT（sno）FROMSC的执行结果，与上述结果是否一样，为什么？
17）利用特殊函数COUNT（*）求计算机系学生的总数。
SELECTCOUNT（*）FROMS
WHEREDept='
●分组查询
1）查询各个教师的教师号及其任课的门数。
SELECTTNo,COUNT（*）ASC_Num
FROMTC
GROUPBYTNo
2）查询选修两门以上课程的学生的学号和选课门数。
SELECTSNo,COUNT（*）ASSC_Num
GROUPBYSNo
HAVING（COUNT（*）>
=2）
●查询的排序
3）查询选修C1的学生学号和成绩，并按成绩降序排列。
SELECTSNo,Score
ORDERBYScoreDESC
4）查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。
WHERE（CNoIN（'
'
C3'
C4'
C5'
））
ORDERBYSNo,ScoreDESC
5）求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
SELECTSNo,SUM（Score）ASTotalScore
WHERE（Score>
=60）
=3）
ORDERBYSUM（Score）DESC
●子查询
6）查询与“刘伟”老师职称相同的教师号、姓名
SELECTTNo,TN
FROMT
WHEREProf=（SELECTProf
FROMT
WHERETN='
7）查询讲授课程号为C05的教师姓名。
方法1：
SELECTTN
WHERE（TNo=ANY（SELECTTNo
FROMTC
WHERECNo='
C05'
方法2：
WHERETNoIN（SELECTTNo
方法3：
selecttn
fromt
whereexists（
select*fromtc
wheretno=t.tnoandcno='
c05'
）
如果要求查询没有讲授C05课程的教师姓名，语句如何写，有哪些写法？
8）查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。
SELECTTN,Sal
WHERE（Sal>
ALL（SELECTSalFROMT
WHEREDept='
AND（Dept<
>
'
（SELECTMAX（Sal）FROMT
9）查询其他系中比计算机系某一教师工资高的教师的姓名和工资。
WHERE（Sal>
ANY（SELECTSal
FROMT
AND（Dept<
WHERESal>
（SELECTMIN（Sal）
WHEREDept='
）
ANDDept<
10）查询选修了全部课程的学生姓名。
注：
首先在SC表中补充一些记录，使得某些学生选修了所有课程。
然后执行查询，比较查询的结果是否和实际一致。
SELECTSNFROMS
WHERENOTEXISTS（
select*fromc
whereNOTexists（select*fromsc
whereSNO=S.SNOANDcno=o
）
看看下述语句是否能够达到同样的查询效果，试比较两种查询思路的特点。
WHERESNOIN（
SELECTSNO
GROUPBYSNO
HAVINGCOUNT（*）=（SELECTCOUNT（*）FROMC）
思考：
统计没有任何学生选修的课程名称，如何查询？
●合并查询
11）从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。
SELECTSNoAS学号,SUM（Score）AS总分
WHERE（SNo='
GROUPBYSNo
UNION
S5'
GROUPBYSNo
●连接查询
12）查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。
SELECTT.TNo,TN,CNo
FROMT,TC
WHERE（T.TNo=TC.TNo）AND（TN='
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）查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。
LEFTOUTERJOINSC
ONS.SNo=SC.SNo
LEFTOUTERJOINC
ONC.CNo=SC.CNo
16）查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。
SELECTX.TN,X.SalAS
Sal_a,Y.SalASSal_b
FROMTASX,TASY
WHEREX.Sal>
Y.Sal
ANDY.TN='
SELECTX.TN,X.Sal,Y.Sal
FROMTASXINNERJOIN
TASY
ONX.Sal>
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）所有的学生，选了那些课程
froms
leftjoinsconsc.sno=s.sno
leftjoincono=o
19）所有学生的选课门数
selects.sn,count（s.sn）,count（o）
leftjoinscons.sno=sc.sno
groupbys.sn
20）已选课学生的选课门数
selects.snas学生,count（*）as'
选课数'
●其他常用函数
建立如下结构表，表名命名为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（'
f_s））asf_3
数据编辑
●求出各系教师的平均工资，把结果存放在新表AvgSal中。
1）首先建立新表
升级会员 