数据库知识点整理全

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

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

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

4、据冗余；2.插入异常；3.删除异常；4.更新异常好的关系模式应预防以上问题函数依赖设有关系模式R(A1,A2,An),X和丫均为A1,A2,An的子集XfY,(1Y不包含于X,那么称XfY是非平凡的函数依赖.X-Y,那么称X为决定因子.Y函数不依赖于X,那么记作X从外到里依此增加模式分解的准那么：模式分解具有无损连接性；模式分解能够保持函数依赖?标准化理论主要是研究关系中各属性之间的依赖关系,根据依赖关系的不同,我们介绍了不包含子属性的第一范式,到消除了属性间的局部依赖关系的第二范式,?再到消除了属性间的传递依赖关系的第三范式,最后到每个决定因子都必须是候选码白BCNE?范式的每一次升级都是通

5、过模式分解实现的,在进行模式分解时应注意保持分解后的关系能够具有无损连接性并能保持原有的函数依赖关系.?对于一般的数据库应用来说,设计到第三范式就足够了.由于标准化程度越高得越细,表的个数越多,那么在检索操作时会因连接而降低检索效率.例：S-D-L(Sno,Dept,Loc)有函数依赖：Sno-Dept,Dept-Loc不是第三范式的.至少可以有三种分解方案,分别为：方案1:SL(Sno,Loc),D-L(Dept,Loc),将S-D-L分解投影得到SL和D-L关系方案2：SD(Sno,Dept),S-L(Sno,Loc)将s sI:I:i i一分解投影得到snsn和5-L5-L美毫如果假跄挈

6、生 40403 3 从 D D2 2 岳 M M 到了 IXIXA A 系,那么需在表 m-Dm-DC CSO3,D2SO3,D2J J改为 4so4so3,3,LKOLKO,M,M 时坯需矍在表b-Lb-Lt.SEjmt.SEjm 改为 CSO3,Ll.CSO3,Ll.如梁这两个修改没有同时进行.图数堀库中就会出现不一致 G 轼厂延是由于这样分解将到的两不人惠榄 W 没有小监 MiMi 来的的曲他*关第玷眼的.座白的函她依 JftgptJftgpt-*Sc-*Sc 在分后*在了两个关第模式上因此分解方案 E E 没有保林原有的函数依箱式奉,也不是好的分*方昧“结论：方案2 2满足无损连接性曰

7、没衣保持倒仃的函救依赖关凝.SnoSnoTsinTsinSOISOI：Sft2Sft2ririS03S03T2T2S04S04L3L3SnoSnoLoc-Loc-SUISUILlLlS03S031.21.2SO4SO4T.lT.l自燃在接SnoSnoDeptDeptLocSOISOImLlLlSO2SO2D2D21,2l2l2LNLNS04S04D3D3LlLl衣2 23 3如 果如 果如 果如 果范式SOISOIMJSnoSno1 15eji5ejiLocLocSOISOIDlDlLlLlSOISOI03031111SO2SO2D2D2L2L2号 Q3Q3D2D2L2L2S4!4S4!411

8、11111111SO4SO41.11.1结论二方宰1 1不泄足无拗让按rtrtXfY,并且X,那么记作X?Y.,分解S-I,S-I,门然然接树2 22 2方案3:SD(Sno,Dept),D-L(Dept,Loc)方案3:将S-D-L分解投影得到S-D和D-L关系结论：方案3既满足无损连接性,又保持原有的函数依赖关系.故它是一个好的分解方法UNIT3Sql功能命令动词数据定义CREATEDROPALTER数据查询SELECT数据操纵INSERTUPDATEDELETE数据限制GRANTREVOKESQL的数据类型：数值型；字符串型；日期时间类型；货币类型数据定义功能操作对象创立语句删除语句修改

9、语句根本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEW数据库CREATEDATABASEDROPDATABASEALTERDATABASEDeptLocDIDILlLl1)21)2L2L2D3D3LlLl自然连接SnoDeptLocSOISOIDIDILlLlS02S02D2D2L2L2S03S03D2D2L2L2S04S04D3D3LlLl表建立表CREATETABLE1网名谀明救鼾集昭. .ISno-聿号字母中,titi立为T TShjar!Shjar!ft*字符中,住地为来空MgMg字料罪

10、,恂更为2 2W W才或.女.年甘*瞅他|全一心窕/PlPl厮在基字符靠4 4帐原为整默认为审事机墨1 1CREATETABLECREATETABLEStudStud电M(M(SroSroch9F(7ch9F(7jPRIMARYKEYPRIMARYKEYB BSn3frwSn3frwchar(10)char(10)NOTNULL,NOTNULL,S SMXMXth*.(2)th*.(2)CHECKStoCHECKSto$ $I I K K女)SoSo；骐Uni/inlUni/inlCHECK(SagoCHECK(Sago1 115ANDSage15ANDSageCHCH45i45isdwisd

11、wi计K 抬通那么软隹兵H H反收0w0w, ,*由g g字拘中,fcJLMKJfcJLMKJ:训dtiiMta1曜也名|字符事,RDURDU出Ccnidal%餐里恒入Kl_Kl_kirrsEE中中ifiAiflifiAiflPvTiludPvTilud学H HCREATECREATETABLETABLECourtCourt ( (CnoCnochartW)NOTNULL,chartW)NOTNULL,CnflintCnflintu uMrMr建第NOTNULLNOTNULLCdrtdlECdrtdlEtlnyinttlnyintCHCHL LCKCK(Lcredtt0,(Lcredtt0,f

12、ifien*eBleren*eBlerUmyinlUmyinlCHECKSvne1.1cCHECKSvne1.1c,0),0),PorlPorlododkHkHCHECK(P4r|od0).CHECK(P4r|od0).J JCREATETft&LECREATETft&LESC(SC(33ChaitTJMOTNIULL,ChaitTJMOTNIULL,CnoCnoch4r(lD|lNOTNULL,ch4r(lD|lNOTNULL,&0&0加tiny!nt,tiny!nt,CHECK(Grad#0andGrade-100),PRIMARYKEY(CHECK(Gra

13、d#0andGradeFOREIGNKEY(SmREFERENCESStuderit|REFERENCESStuderit|Sn?1.Sn?1.i i_十I I例1.为SC表添加“修课类别歹U,此列的定义为：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL例2.将新添加的XKLB的类型改为char(6).ALTERTABLESCALTERCOLUMNXKLBchar(6)例3.删除Course表的Period列ALTERTABLECourseDROPCOLUMNPeriod索引：聚簇索引、非聚簇索引：复合索引是将两个字段或多个字段组合起来建立的索引,而单独的字

14、段允许有重复的值.建立索引格式：createuniqueclusteredindex索弓I名on表名(列名1asc|desc,)unique：用于指定为表创立唯一索引,即不允许存在索引值相同的两行.clustered：用于指定创立的索引为聚簇索引.默认是非聚簇索引.asc升序,desc降序,默认为asc例1：为学生表建立按学号升序索引createindexstudent_snoonstudent(sno)例2:为选课表按学号升序和课程号降序建唯一索引createuniqueindexSCIonsc(sno,cnodesc)例3:为学生表按系升序建立聚簇索引.createclusteredind

15、exTIonstudent(sdept)删除索引语句根本格式：dropindex表名.索引名例4:删除表SC的索引SCIdropindexSC.SCI注：索引名前一定要加：表名fMu豆:中朴,KrtKrt为T TJLWJLWL L明印细皿理地朴FlFlC1I0ilWSilWSvn*.Kitiovn*.Kition n酬啕即sizifnsizifn错刷底博安曹.-100100I ISELECT目标歹U名序歹UFROM嗷据源WHERE检索条件表达式GROUPBY分组依据列HAVING组提取条件ORDERBYjE序依据列刁ORDERBY排序依据列简单查询单表查询例1.查询全体学生的学号与姓名SELE

16、CTSnoSnameFROMStudent比拟大小例8.查询所有年龄在20岁以下的学生的姓名及年龄.SELECTSname,SageFROMStudentWHERESage20或：SELECTSname,SageFROMStudentWHERENOTSage=20确定范围BETWEENAND和NOTBETWEEN-AND确定集合ININ是一个逻辑运算符,可以用来查找属性值属于指定集合的元组使用IN的格式为：列名NOTIN常量1,常量2,常量nIN的含义为：当列中的值与IN中的某个常量值相等时,那么结果为True,说明此记录为符合查询条件的记录；NOTIN的含义正好相反：当列中的值与某个常量值相

17、同时,那么结果为False,说明此记录为不符合查询条件的记录；例12.查询信息系、数学系和计算机系学生的姓名和性别.SELECTSname,SsexFROMStudentWHERESdeptIN信息系,数学系,计算机系此句等价于：SELECTSname,SsexFROMStudentWHERESdept=信息系ORSdept=数学系ORSdept=计算机系字符匹配LIKE用于查找指定列名与匹配串常量匹配的元组模糊查询匹配串是一种特殊的字符串,它不仅包含普通字符,还可以包括通配符.通配符用于表示任意的字符或字符串.:匹配任意一个字符；%:匹配0个或多个字符；:匹配中的任意一个字符；人：不匹配口中

18、的任意一个字符.LIKE运算符的一般形式为：列名NOTLIKE匹配串在LIKE运算符前边也可以使用NOT运算符,表示对结果取反.例14.查询姓张的学生的详细信息.SELECT*FROMStudentWHERESnameLIKE张%例15.查询学生表中姓张、姓李和姓刘的学生的情况.SELECT*FROMStudentWHERESnameLIKE张李刘%例16.查询名字中第2个字为小或大字的学生的姓名和学号.SELECTSname,SnoFROMStudentWHERESnameLIKE/出%例17.查询所有不姓“刘的学生.SELECTSnameFROMStudentWHERESnameNOTLI

19、KE%-需要哪些列-来自于哪些表-根据什么条件查询-对查询结果进行分组-指定组的选择条件-对查询结果进行排序例18.从学生表中查询学号的最后一位不是2、3、5的学生情况.SELECT*FROMStudentWHERESnoLIKE%A235涉及空值的查询判断取值为空的语句格式为：列名ISNULL判断取值不为空的语句格式为：列名ISNOTNULL例19.查询无测试成绩的学生的学号和相应的课程号.SELECTSno,CnoFROMSCWHEREGradeISNULL不可写为WHEREGrade=null多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询用AND连接的条件表

20、示必须全部满足所有的条件的结果才为True用OR连接的条件表示只要满足其中一个条件结果即为True对查询结果进行排序排序子句的格式为：ORDERBY名ASC|DESC,nASC表示对列进行升序排序,DESM示对列进行降序排序.默认为升序排序.使用计算函数汇总数据例26.计算9512101号学生的测试总成绩之和SELECTSUM(Grade)FROMSCWHERESno=9512101,对查询结果进行分组计算一般形式为：GROUPBY分组依据列,nHAVING却提取条件例28.统计每门课程的选课人数,列出课程号和人数.SELECTCnoasSM程号,COUNT(Sno)as选课人数FROMSCG

21、ROUPBYCno多表连接查询1.内连接内连接的格式为：SELECT属性或表达式列表FROM表1INNERJOIN表2ON属连接条件例32.查询每个学生及其修课的情况SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno外连接外连接是只限制一张表中的数据必须满足连接条件,而另一张表中数据可以不满足连接条件.ANSI方式的外连接的语法格式为：FROM表1LEFT|RIGHTOUTERJOIN表2ON属连接条件例39.查询学生的修课情况,包括修了课程的学生和没有修课的学生.SELECTStudent.Sno,Sname,Cno,GradeFROMStud

22、entLEFTOUTERJOINSCONStudent.Sno=SC.Sno也可以用右外连接实现：SELECTStudent.Sno,Sname,Cno,GradeFROMSCRIGHTOUTERJOINStudentONStudent.Sno=SC.Sno使用子查询进行基于集合的测试例40.查询与“刘晨在同一个系学习的学生.SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)使用子查询进行基于集合的测试例41.查询成绩为大于90分的学生的学号、姓名.SELECTSno,SnameFR

23、OMStudentWHERESnoIN(SELECTSnoFROMSWHEREGrade90)使用子查询进行比拟测试通过比拟运算符(=、=),将一个表达式的值与子查询返回的值进行比拟.例43.查询修了C02课程且成绩高于此课程的平均成绩的学生的学号和成绩.SELECTSno,GradeFROMSCWHERECno=c02andGrade(SELECTAVG(Grade)fromSCWHERECno=c02)使用子查询进行存在性测试例44.查询选修了C01号课程的学生姓名.SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Studen

24、t.SnoANDCno=c01)数据更改功能插入数据INSERTS句的格式为：INSERTINTO袭名(列名表)VALUES(值列表)例1.将新生记录(9521105,陈冬,男,信息系,18岁)插入到Student表中.INSERTINTOStudentVALUES(9521105,陈冬,男,18,信息I系)更新数据(UPDATE)UPDATED句的语法格式为：UPDATE表名SET列名=表达式,nWHEREM新条件例2.将9512101学生的年龄改为21岁.UPDATEStudentSETSage=21WHERESno=9512101删除数据(DELETE)DELETED句的语法格式为：DE

25、LETEFROM表名WHERE0除条件例3.删除计算机系不及格学生的修课记录用子查询实现：DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMStudeWHERESdept=计算机系)用多表连接实现：DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=计算机系ANDGrade视图列名表AS子查询语句例1.建立信息系学生的视图.CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=信息系例4.定义一个反映学生出生年份

26、的视图CREATEVIEWBT_SSno,Sname,SbirthASSELECTSno,Sname,2006SageFROMStudent删除视图DROPVIEW硼图名视图可简化查询语句例：将信息系学生的视图中学号为9512102的学生姓名改为“刘呈UPDATEIS_StudentSETSnameW呈WHERESno=9512102例：在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,SageFROMIS_StudentWHERESage,nLOGON,n例2:创立一个数据库,数据库名称为：“人事信息数据库,此数据库包含一个数据文件和一个事务日志文件.数据文件只有主数据文件,其

27、逻辑文件名为“人事信息数据库,其物理文件名为“人事信息数据库.mdf,存放位置在默认目录下,其初始大小为i0MB,最大大小为30MB,自动增长时的递增量为5MB.事务日志文件的逻辑文件名为“人事信息日志,物理文件名为“人事信息日志.ldf,也存放在默认目录下,初始大小为3MB,最大大小为12MB,自动增长时的递增量为2MB.CREATEDATABASE人事信息数据库ON(NAME=人事信息数据库,人事信息数据库.mdf,SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=人事信息日志,人事信息日志.ldf,SIZE=3,MAXSIZE=12,FILEGROWTH=2)使用Transact-SQL语句删除数据库语法格式为：DROPDATABAS瞰据库名,n例：用SQL语句删除Testi和Test2数据库：DROPDATABASETesti,Test2使用Transact-SQL语句修改数据库(ALTERDATABASE)创立触发器(CREATETRIGGER)使用SQL语句治理权限(1)授权语句GRANT例1:为用户useri授予Student表的查询权.GRANTSELECTONStudentTOuser1例2：为用户user1授予SC表的查询权和插入权.GRANTSELECTINSERTONSCTOuser1例3:授予user1和