数据库知识点整理(全)

1、四个根本概念1 .数据Data:数据库中存储的根本对象2 .数据库的定义：数据库Database,简称DB是长期J储存在计算机内、有组织的、可共享的大量数据集合3 .数据库治理系统简称DBMS：位于用户与操作系统之间的一层数据治理软件系统软件.用途:科学地组织和存储数据;高效地获取和维护数据主要功能：数据定义功能；数据操纵功能；数据库的运行治理；数据库的建立和维护功能实用程序4 .数据库系统DatabaseSystem,简称DBS：指在计算机系统中引入数据库后的系统数据库系统的构成数据库数据库治理系统及其开发工具应用系统数据库治理员DBA和用户数据治理技术的开展过程人工治理阶段文件系统阶段数据

2、库系统阶段数据库系统治理数据的特点如下1数据共享性高、冗余少；2数据结构化；3数据独立性高：4由DBMS进行统一的数据限制功能数据模型用来抽象、表示和处理现实世界中的数据和信息的工具.通俗地讲数据模型就是现实世界数据的模拟.数据模型三要素.数据结构：是所研究的对象类型的集合,它是刻画一个数据模型性质最重要的方而；数据结构是对系统静态特性的描述数据操作：对数据库中数据允许执行的操作及有关的操作规那么；对数据库中数据的操作主要有查询和更改包括插入、修改、删除；数据操作是对系统动态特性的描述数据的约束条件:数据及其联系应该满足的条件限制E-R图实体：矩形框表示属性：椭圆形或圆角矩形表示联系：菱形表示

3、班级组织层数据模型层次模型网状模型关系模型用“二维表来表示数据之间的联系根本概念：关系Relation：一个关系对应通常说的一张表元组记录：表中的一行属性字段：表中的一列,给每一个属性名称即属性名分量：元组中的一个属性值,分量为最小单位,不可分主码Key：表中的某个属性组,它可以唯一确定一个元组.域Domain：属性的取值范围.关系模式：对关系的描述.一般表示为：关系名属性1,属性2,属性n关系模型的数据完整性约束实体完整性参照完整性用户定义的完整性DBS三级模式结构：外模式、概念模式、内模式一个数据库只有一个内模式数据库系统的模式结构在进行数据库的操作时,会出现以下几方面的问题:1.数据冗余

4、；2.插入异常；3.删除异常；4.更新异常好的关系模式应预防以上问题函数依赖设有关系模式斤Gu月2,山2),乃和y均为,留,曲的子集如果2:匕但y不包含于乂那么称AN是非平凡的函数依赖.如果广匕那么称x为决定因子.如果y函数不依赖于乂那么记作xy0如果X-Y.并且Y-X,那么记作XT.范式从外到里依此增加模式分解的准那么:模式分解具有无损连接性；模式分解能够保持函数依赖标准化理论主要是研究关系中各属性之间的依赖关系,根据依赖关系的不同,我们介绍了不包含子属性的第一范式,到消除了属性间的局部依赖关系的第二范式,再到消除了属性间的传递依赖关系的第三范式,最后到每个决定因子都必须是候选码的BCNFe

5、范式的每一次升级都是通过模式分解实现的,在进行模式分解时应注意保持分解后的关系能够具有无损连接性并能保持原有的函数依赖关系.对于一般的数据库应用来说,设计到第三范式就足够了.由于标准化程度越高,分解得越细,表的个数越多,那么在检索操作时会因连接而降低检索效率.例：S-D-L(Sno,Dept,Loc)有函数依赖：SnofDept,DeptfLoc不是第三范式的.至少可以有三种分解方案,分别为：方案1：S-L(Sno,系ISnoLocSOIS02_IS03J17)4a_1S-L3Pt3i»r一-D21船二1D-LSnoDeptLocSOI»1LISOI113IASO2D21.

6、2SO3D2L2S04DILISO4113LI自然连接表22结论：方案1不满足无捌连接性Loc),D-L(Dept,Loc),将S-D-L分解投影得到S-L和D-L关方案2：S-D(Sno,Dept),S-L(Sno,Loc)方案2:将S-D-L分解投影得到S-D和S-L关系SnoDeptSOIl>1S02T>2S03D2S04L>3S-l)SnoLocSOILiSO2L2SO3L2S04DISnoDeptLocSOIDI11S02D21.2S03D2L2S04D3LI表23如果假设学生S03从D2系转到了D3系,那么需在表S-D(SO3,D2)改为(SO3,D3),同时还需

7、袈在表S-LCS03,L2)改为(S03,L1>°如果这两个修改没有同时进行,那么数如:库中就会HI现不一致信息.这是由于谊杆分解得到的两个关系模式没有保持原来的函数依检关系造成的.原育的函数依籁DeptLoc在分解后踏在了两个关系模式上:因此分布方案碎有保持原有的函数依敕关去,也不越好的分解方法.结论：方案2满足无损连接性,但没有保持原有的函数依赖关系.方案3：S-D(Sno,Dept)9D-L(Dept>Loc)方案3:将S-D-L分解投影得到S-D和D-L关系SnoDeptSOIDlS02D2S03D2S04D3SnoDeptLocDeptLoc自然连接SOIDlL

8、lDlLlS02D2L2D2L2S03D2L2D3LlS04D3LlS-D表24结论：方案3既满足无损连接性,又保持原有的函数依赖关系.故它是一个好的分解方法UNIT3Sql功能命令动词数据定义CREATEDROPALTER数据查询SELECT数据操纵INSERTUPDATEDELETE数据限制GRANTREVOKESQL的数据类型:数值型：字符串型：日期时间类型；货币类型数据定义功能操作对象创立语句删除语句修改语句根本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEWCREATETUTARASFD

9、ROPMTARAVFALTERDATABASE建立表CREATETABLE列名说明数树类型约束列名说明约束Snc学号一岳符如长度为7：oursc:Cno操程号字符木.K度为io前Snanr姓名字符串,长度为Cnam座即名字符中,粒度为Sf；CX性别字符串,长度为相"我'男'*'女尸CcrcditMZJ取伯旺)Sa«u年龄»»Senesutr孑期8ft取也大于0Sdept所在看7符市.K度为20默认为计R机系Period学Mlift取价大尸0CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamec

10、har(10)NOTNULL,Ssexchar(2)CHECK(Ssex以ORSsex=ef).SagetinyintCHECK(Sage>=15ANDSage<=45),1Sdeptchm如CREATETABLECourse(Cnochar(10)NOTNULL.Cnamechar(20)NOTNULL.CcredittinyintCHECK(Ccredit>0).SemestertinyintCHECK(Semester>0),PGrk>dintCHECK(Period>0).PRIMARY;*名说明数据夹不约束Sno学弓I字铃长度为71期,引用st但喧

11、也Cnu.Grwde,谡程叮字制长良务回99耳瓦$1州Coue的外向CREATETABLESC(Snochar(7)NOTNULL.Cnochar(10)NOTNULL,GradetinyintCHECK(Grade>=0andGrade<=100).PRIMARYKEY(Sno,Cno).FOREIGNKEY(Sno)REFERENCESStudent(Sn*FOREIGNKEY(Cno)REFERENCES)例L为SC表添加“修课类别列,此列的定义为：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL例2.将新添加的XKLB的类型改为char(6

12、)oALTERTABLESCALTERCOLUMNXKLBchar(6)例3.删除Course表的Period列ALTERTABLECourseDROPCOLUMNPeriod索引:聚簇索引、非聚簇索引：复合索引是将两个字段或多个字段组合起来建立的索引,而单独的字段允许有重复的值.建立索引格式：createuniqueclustered,index索弓I名on表名(列名1>ascdesc,)unique：用于指定为表创立唯一索引,即不允许存在索引值相同的两行.clustered：用于指定创立的索引为聚族索引.默认是非聚簇索引.asc升序,desc降序,默认为asc例1:为学生表建立按学号

13、升序索引createindexstudent_snoonstudent(sno)例2：为选课表按学号升序和课程号降序建唯一索引createuniqueindexSCIonsc(sno,cnodesc)例3:为学生表按系升序建立聚簇索引.createclusteredindexTIonstudent(sdept)删除索引语句根本格式：dropindex表名.索引名?例4：删除表SC的索引SCKdropindex注：索引名前一定要加：表名需要哪些列来自于哪些表一根据什么条件查询对查i旬结果进行分组-指定组的选择条件对查询结果进行排序SELECT目标列名序列?FROM数据源?WHERE检索条件表达式

14、GROUPBY分组依据列HAVING组提取条件ORDERBY排序依据列?ORDERBY排序依据列简单查询单表查询例1.查询全体学生的学号与姓名SELECTSno»SnameFROMStudent比拟大小例8.查询所有年龄在20岁以下的学生的姓名及年龄.SELECTSname,SageFROMStudentWHERESage20或：SELECTSname,SageFROMStudentWHERENOTSage=20确定范围BETWEEN-AND和NOTBETWEEN-AND确定集合ININ是一个逻辑运算符,可以用来查找属性值属于指定集合的元组使用IN的格式为：列名NOTIN常量1,常量

15、2,常量nN的含义为：当列中的值与IN中的某个常量值相等时,那么结果为True,说明此记录为符合查询条件的记录；NOTIN的含义正好相反：当列中的值与某个常量值相同时,那么结果为False,说明此记录为不符合查询条件的记录；例12.查询信息系、数学系和计算机系学生的姓名和性别.SELECTSname,SsexFROMStudentWHERESdeptIN'信息系','数学系',计算机系'此句等价于：SELECTSname,SsexFROMStudentWHERESdept=,信息系'ORSdept=,数学系'ORSdept=,计算机系字符

16、匹配LIKE用于查找指定列名与匹配串常量匹配的元组模糊查i旬匹配串是一种特殊的字符串,它不仅包含普通字符,还可以包括通配符.通配符用于表示任意的字符或字符串._：匹配任意一个字符：%：匹配0个或多个字符：匹配中的任意一个字符；1:不匹配中的任意一个字符.LIKE运算符的一般形式为：列名NOTLIKE匹配串?在LIKE运算符前边也可以使用NOT运算符,表示对结果取反.例14.查询姓'张'的学生的详细信息.SELECT*FROMStudentWHERESnameLIKE'张犷例15.查询学生表中姓张、姓李'和姓'刘'的学生的情况.SELECT*FRO

17、MStudentWHERESnameLIKE'张李刘居'例16.查询名字中第2个字为,小或大字的学生的姓名和学号.SELECTSname,SnoFROMStudentWHERESnameLIKE小大蠕例17.查询所有不姓“刘的学生.SELECTSnameFROMStudentWHERESnameNOTLIKE'刘舟'例18.从学生表中查询学号的最后一位不是2、3、5的学生情况.SELECT*FROMStudentWHERESnoLIKE飞235'涉及空值的查询判断取值为空的语句格式为：列名ISNULL判断取值不为空的语句格式为：列名ISNOTNULL例1

18、9.查询无测试成绩的学生的学号和相应的课程号.SELECTSno,CnoFROMSCWHEREGradeISNULL不可写为WHEREGrade二null多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询用AND连接的条件表示必须全部满足所有的条件的结果才为True用OR连接的条件表示只要满足其中一个条件结果即为True对查询结果进行排序排序子句的格式为：ORDERBY列名ASCDESC,-nASC表示对列进行升序排序,DESC表示对列进行降序排序.默认为升序排序.使用计算函数汇总数据例26.计算9512101号学生的测试总成绩之和SELECTSUM(Grade)FR

19、OMSCWHERESno='9512101'对查询结果进行分组计算一般形式为：GROUPBY分组依据列,nHAVING组提取条件?例28.统计每门课程的选课人数,列出课程号和人数.SELECTCnoas课程号,COUNT(Sno)as选课人数FROMSCGROUPBYCno多表连接查询L内连接内连接的格式为：SELECT属性或表达式列表)FROM表1INNERJOIN表2ON连接条件?例32.查询每个学生及其修课的情况SELECT*FROMStudentINNERJOINSCON=外连接外连接是只限制一张表中的数据必须满足连接条件,而另一张表中数据可以不满足连接条件.ANSI方

20、式的外连接的语法格式为：FROM表1LEFTRIGHTOUTERJOIN表2ON连接条件?例39.查询学生的修课情况,包括修了课程的学生和没有修课的学生.SELECT,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCON=也可以用右外连接实现：SELECT,Sname,Cno,GradeFROMSCRIGHTOUTERJOINStudentON=使用子查询进行基于集合的测试例40.查询与“刘晨在同一个系学习的学生.SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESn

21、ame='刘晨')使用子查询进行基于集合的测试例41.查询成绩为大于90分的学生的学号、姓名.SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHEREGrade90)使用子查询进行比拟泅试通过比拟运算符(=、:、=),将一个表达式的值与子查询返回的值进行比拟.例43.查询修了c02'课程且成绩高于此课程的平均成绩的学生的学号和成绩.SELECTSno,GradeFROMSCWHERECno='c02'andGrade(SELECTAVG(Grade)fromSCWHERECno='c02&

22、#39;)使用子查询进行存在性测试例44.查询选修了cOl'号课程的学生姓名.SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=ANDCno="cOf)数据更改功能插入数据INSERT语句的格式为:INSERTINTO表名)(列名表?)VALUES(值列表)例1.将新生记录(9521105,陈冬,男,信息系,18岁)插入到Student表中.INSERTINTOStudentVALUES(19521105,陈冬','男',18,'信息系')更新数据(UPDATE)UPDATE

23、语句的语法格式为:UPDATE表名?SET列名=表达式,nWHERE更新条件)例2.将9512101'学生的年龄改为.岁.UPDATEStudentSETSage=21WHERESno='95121011删除数据(DELETE)DELETE语句的语法格式为：DELETEFROM表名WHERE删除条件?例3.删除计算机系不及格学生的修课记录用子查询实现：DELETEFROMSCWHEREGrade<60ANDSnoIN(SELECTSnoFROMStudeWHERESdept='计算机系')定义视图用多表连接实现：DELETEFROMSCFROMSCJOIN

24、StudentON=WHERESdept='计算机系'ANDGrade<60CREATEVIEW视图名?视图列名表AS子查询语句例1.建立信息系学生的视图.CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='信息系'例4.定义一个反映学生出生年份的视图CREATEVIEWBT_SSn.,Sname,SbirthASSELECTSno,Sname,2006-SageFROMStudent删除视图DROPVIEW视图名视图可简化查询语句例：将信息系学生的视图中学号为9512102的学

25、生姓名改为“刘呈UPDATEIS.StudentSETSname='刘呈'WHERESno='9512102'例：在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,SageFROMIS.StudentWHERESage20Unit4数据库设计的根本步骤需求分析阶段信息要求、处理要求、平安性与完整性要求一一概念结构设计阶段一一逻辑结构设计阶段一一物理结构设计阶段一一数据库实施阶段一一数据库运行和维护阶段DBMS常用存取方法x索引方法,目前主要是B+树索引方法聚簇Cluster方法HASH方法UNIT5DBMS对数据库的平安保护功能是通过四方而实现的,

26、即平安性限制、完整性限制、并发性限制和数据库恢复.完整性限制数据库的完整性是指保护数据库中数据的正确性、有效性和相容性,预防错误的数据进入数据库造成无效操作.关系模型的完整性包括实体完整性,参照完整性和用户定义完整性.对于违反实体完整性和用户定义完整性规那么的操作一般都是采用也纲i的方式进行处理.完整性约束条件的作用对象可以是表、元组和列.并发限制数据库的并发操作导致的数据库不一致性主要有以下四种：丧失修改LostUpdate：当两个事务和L：读入同一数据做修改,并发执行时,T：把T：或T：把的修改结果覆盖掉.污读DirtyRead:事务L更新了数据R,事务T：读取了更新后的数据R,事务.由于

27、某种原因被撤消,修改无效,数据R恢复原值.事务L得到的数据与数据库的内容不一致,这种情况称为“污读.不可重读UnrepeatableRead：事务或读取了数据后,事务T：更新了1读取的数据,当事务兀再读取数据以进行相同操作时,得到的两次值不一致,这种情况称为“不可重读产生“幽灵数据:指当事务T1按一定条件从数据库中读取了某些数据记录后,事务T2删除了其中的局部记录,或者在其中添加了局部记录,那么当T1再次按相同条件读取数据时,发现其中莫名其妙地少了对删除或多了对插入一些记录.这样的数据对T1来说就是“幽灵数据或称“幻影数据.并发限制的主要方式是封锁机制,即加锁Locking.排它锁Exclus

28、iveLock,又称写锁,简称为X锁事务T对数据对象A加了X锁,那么允许T读取和修改A,但不允许其它事务再对A加任何类型的锁,直到T释放了A上的锁.共享锁ShareLock,又称读锁,简称为S锁事务T对数据对象A加了S锁,那么事务T可以读A,但不能修改A,其它事务只能再对A加S锁,而不能加X锁,直到T释放了A上的S锁.三个封锁协议的主要区别在于X锁对写数据S锁对只读数据不丧失修改写不读脏数据读可重复读读一级事务全程加锁不加V二级事务全程加锁事务开始加,读完放VV三级事务全程加锁事务全程加锁VV预防死锁两种方法：一次封锁法、顺序封锁法数据库的恢复数据库运行故障：事务故障可以利用日志文件撤消此事务

29、对数据库已进行的修改系统故障介质故障重装数据库,然后利用备份或镜像设备恢复数据库.UNIT6使用Transact-SQL语言创立数据库CREATEDATABASE数据库名ON文件格式,nLOGON文件格式?,n例2：创立一个数据库,数据库名称为：“人事信息数据库,此数据库包含一个数据文件和一个事务日志文件.数据文件只有主数据文件,其逻辑文件名为“人事信息数据庠,其物理文件名为“人事信息数据库.mdf,存放位置在默认目录下,其初始大小为10MB,最大大小为30MB,自动增长时的递增量为5MB.事务日志文件的逻辑文件名为“人事信息日志,物理文件名为“人事信息日志.ldf,也存放在默认目录下,初始大

30、小为3MB,最大大小为12MB,自动增长时的递增量为2MB.CREATEDATABASE人事信息数据库ON(NAME二人事信息数据库,FILENAME二'C:programfilesMicrosoftSQLServerMssqlData人事信息数据库.mdfSIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME二人事信息日志,FILENAME=C：programfilesMicrosoftSQLServerMssqlData人事信息日志.Idf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)使用Transact-SQL语句删除数据库语法格式为：DROPDATABASE数据库名,n例：用SQL语句删除Testi和Test2数据库：DROPDATABASETesti,Test2使用Transact-SQL语句修改数据库(ALTERDATABASE)位维触发癖(CREATETRIGGER)使用SQL语句治理权限(1)授权语句GRANT例1：为用户userl授予Student表的查询权.GRANTSELECTONSt