数据库知识点整理全

UNIT1四个基本概念.数据（Data）:数据库中存储的基本对象.数据库的定义:数据库（Database,简称DB）是长期储存在计算机内、有组织的、可共享的大量数据集合.数据库管理系统（简称DBMS）：位于用户与操作系统之间的一层数据管理软件（系统软件）。用途:科学地组织和存储数据;高效地获取和维护数据主要功能：数据定义功能；数据操纵功能；数据库的运行管理；数据库的建立和维护功能（实用程序）.数据库系统（DatabaseSystem，简称DBS）：指在计算机系统中引入数据库后的系统数据库系统的构成数据库数据库管理系统（及其开发工具）应用系统数据库管理员（DBA）和用户数据管理技术的发展过程人工管理阶段文件系统阶段数据库系统阶段数据库系统管理数据的特点如下（1）数据共享性高、冗余少；（2）数据结构化；（3）数据独立性高；（4）由DBMS进行统一的数据控制功能数据模型用来抽象、表示和处理现实世界中的数据和信息的工具。通俗地讲数据模型就是现实世界数据的模拟。数据模型三要素。数据结构：是所研究的对象类型的集合，它是刻画一个数据模型性质最重要的方面;数据结构是对系统静态特性的描述数据操作:对数据库中数据允许执行的操作及有关的操作规则；对数据库中数据的操作主要有查询和更改（包括插入、修改、删除）；数据操作是对系统动态特性的描述数据的约束条件:数据及其联系应该满足的条件限制E-R图实体：矩形框表示属性：椭圆形（或圆角矩形）表示

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

UNIT2在进行数据库的操作时，会出现以下几方面的问题：1.数据冗余；2.插入异常；3.删除异常；4.更新异常好的关系模式应避免以上问题函数依赖设有关系模式R(A1,A2,…,An)，X和Y均为｛A1,A2,…,An｝的子集如果X-Y,但Y不包含于X,则称X-Y是非平凡的函数依赖。如果X-Y,则称X为决定因子。如果Y函数不依赖于X,则记作XY。如果X-Y,并且Y-X,则记作XY。范式从外到里依此增加模式分解的准则：模式分解具有无损连接性；模式分解能够保持函数依赖规范化理论主要是研究关系中各属性之间的依赖关系，根据依赖关系的不同，我们介绍了不包含子属性的第一范式，到消除了属性间的部分依赖关系的第二范式，再到消除了属性间的传递依赖关系的第三范式，最后到每个决定因子都必须是候选码的BCNF。范式的每一次升级都是通过模式分解实现的，在进行模式分解时应注意保持分解后的关系能够具有无损连接性并能保持原有的函数依赖关系。对于一般的数据库应用来说，设计到第三范式就足够了。因为规范化程度越高，分解得越细,表的个数越多,则在检索操作时会因连接而降低检索效率。例：S-D-L(Sno,Dept,Loc)有函数依赖:Sno—Dept,DeptfLoc不是第三范式的。至少可以有三种分解方案，分别为:方案1：S-L(Sno,Loc),D-L(Dept,Loc)，将S-D-L分解投影得到S-L和D-L关系SnoLocSOILIS02L2S03SnoLocSOILIS02L2S03L2S04LIS-LDeptLoc自然速接T>1LID2L2l>31,1D-LSnoLzSOIl>1LlSOI・33LIS021.2S03L2S04mLlSO403Ll表2—2结论：方案1不据足无损建接性方案2：S-D(Sno,Dept),S-L(Sno,Loc)方案2将D-L分解投就得到S0和S-L关系SnoDeptSaioLoc口燃连接SnoDeptLocSUILlSOIDISOII>1LlS02r>2SO2L2号ONnzL2SO3L2S03D2S03D3T.2SU4L>3S041L1SO403LlS-L 表之一3如I果假设学生SOM从D2系钟到了D3索，则需住表行-ncSQ3,1>2>改为匚的孔D3),同时足霜冬在表S-LtS03,L2>改为<S03,Ll>.如果这两个修改没有同时进行.则融切库中就会出现不一致信息b在是由于蜒样分解得到的两个发晶模式渔行保持原来的咕致依蜓关赛此.成的.原有的函数依蛆Dtml-Leu在分解后踣在了阿个关系模式」“因此分解方案之没有保持原有的函数依赖关系，也不是好的分解石法.结论：方案Z满足无捌连接性,但没宥保拘原有的函数依赖关集.方案3：S-D(Sno,Dept),D-L(Dept,Loc)方案3:将S-D-L分解投影得到S-D和D-L关系SnoD叩tSnoD叩tSOIDIS02D2S03D2S04D3SnoDeptLocDeptLoc自然连接SOIDILlDILlSOND2L2D2L2S03D2L2D3LlS04D3LlD-L 表2—4结论：方案3既满足无损连接性，又保持原有的函数依赖关系.

故它是一个好的分解方法UNIT3Sql功能命令动词数据定义CREATEDROPALTER数据查询SELECT数据操纵INSERTUPDATEDELETE数据控制SQL的数据类型：数值型；数据定义功能GRANTREVOKE字符串型；日期时间类型；货币类型操作对象创建语句删除语句修改语句基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX视图CREATEVIEWDROPVIEWALTERDATABASE建立表CREATETABLE列名.说明约束Fdurse; a为束Snn-华号鼻符串，性鹿为7Crw>*程十厚海1胸布Snimn姓名字符丰，性度为心洋生Cnnnr:吃理名Ssex-性别字符串，氏靛为2靠2r或CcTttdiIt学分职值大干口白一年龄翰卷一由UiSe-aesLer中期*sr、取值大¥Sdept在茶任符■巾，民胆为出默温为“讨株机厩PeMod/值*T0CREATETABLESludent(⑺PRIMARYKEY,事naEF NIQTMULL,Swxcher|2)CHECK(Sflex='即CRSsex='女)SagstinyinlCHECK(Sage>=l&ANt)Sage<=45)★】CREATETAiBLECoufeo(CnoGhar110JNOTNULL,Cnamech^ar(20l)NOTNULL,CuredirttinyirrlCHECK(Ceredrt>0)rSemestertinylntCHECK(Semester^Period耐CHECK**★JSdeptcharran青5。+11k5PRIMARY丹名说明数掘夷用的市&II0 ;学号，带rfh皖度为7!■夙引印用小!而外巴事挎而।唬度为1。；T：叫引冏30由外码GrW取俏6700CREATETABLESC(Sno加叼MOTNULL,Cno叫NOTNULLGradetln^iriit,CHECKiGrade>=0andGrade<=10。)，PRIMARYKEY(Snu,Cno}BFOREIGNKEY(Srw)REFERENCESShide□“落木.FOREIGNKEY(Cm)REFERENCESCcursd^nc]1例1.为SC表添加“修课类别”歹u，此列的定义为：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL例2.将新添加的XKLB的类型改为char(6)。ALTERTABLESCALTERCOLUMNXKLBchar(6)例3.删除Course表的Period列ALTERTABLECourseDROPCOLUMNPeriod索引：聚簇索引、非聚簇索引：复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。建立索引格式：create[unique][clustered]index索弓|名on表名(〈列名l>[asc|desc][，…])unique：用于指定为表创建唯一索引，即不允许存在索引值相同的两行。clustered：用于指定创建的索引为聚簇索引。默认是非聚簇索引。asc升序，desc降序，默认为asc例1：为学生表建立按学号升序索引createindexstudent_snoonstudent(sno)例2：为选课表按学号升序和课程号降序建唯一索引createuniqueindexSCIonsc(sno,cnodesc)例3:为学生表按系升序建立聚簇索引。createclusteredindexTIonstudent(sdept)删除索引语句基本格式：dropindex〈表名.索引名〉例4:删除表SC的索引SCI。dropindex注：索引名前一定要加：表名

--需要哪些列--来自于哪些表--需要哪些列--来自于哪些表--根据什么条件查询--对查询结果进行分组--指定组的选择条件--对查询结果进行排序FROM〈数据源〉［WHERE＜检索条件表达式〉］［GROUPBY（分组依据列〉］［HAVING＜组提取条件〉］［ORDERBY＜排序依据列〉］［ORDERBY（排序依据列〉］简单查询（单表查询）例1.查询全体学生的学号与姓名SELECTSno，SnameFROMStudent比较大小例8.查询所有年龄在20岁以下的学生的姓名及年龄。SELECTSname,SageFROMStudentWHERESage＜20或：SELECTSname,SageFROMStudentWHERENOTSage＞=20确定范围BETWEEN-AND和NOTBETWEEN-AND确定集合QN）IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组使用IN的格式为：列名［NOT］IN（常量1,常量2,…常量n）IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True,表明此记录为符合查询条件的记录；NOTIN的含义正好相反：当列中的值与某个常量值相同时，则结果为False,表明此记录为不符合查询条件的记录；例12.查询信息系、数学系和计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN（'信息系'，'数学系'，’计算机系'）此句等价于：SELECTSname,SsexFROMStudentWHERESdept='信息系'ORSdept=’数学系'ORSdept='计算机系’字符匹配LIKE用于查找指定列名与匹配串常量匹配的元组（模糊查询）匹配串是一种特殊的字符串，它不仅包含普通字符，还可以包括通配符。通配符用于表示任意的字符或字符串。_：匹配任意一个字符；%:匹配0个或多个字符；［］：匹配［］中的任意一个字符；厂］:不匹配［］中的任意一个字符。LIKE运算符的一般形式为：列名［NOT］LIKE（匹配串〉在LIKE运算符前边也可以使用NOT运算符，表示对结果取反。例14.查询姓'张'的学生的详细信息。SELECT*FROMStudentWHERESnameLIKE'张％’例15.查询学生表中姓‘张‘、姓‘李'和姓‘刘’的学生的情况。SELECT*FROMStudentWHERESnameLIKE'［张李刘］%’例16.查询名字中第2个字为‘小‘或‘大’字的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'例17.查询所有不姓“刘”的学生。SELECTSnameFROMStudentWHERESnameNOTLIKE'刘％'例18.从学生表中查询学号的最后一位不是2、3、5的学生情况。SELECT*FROMStudentWHERESnoLIKE'%「235]’涉及空值的查询判断取值为空的语句格式为：列名ISNULL判断取值不为空的语句格式为：列名ISNOTNULL例19.查询无考试成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL不可写为WHEREGrade=null多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询用AND连接的条件表示必须全部满足所有的条件的结果才为True用OR连接的条件表示只要满足其中一个条件结果即为True对查询结果进行排序排序子句的格式为：ORDERBY＜列名＞[ASC|DESC][，…n]ASC表示对列进行升序排序，DESC表示对列进行降序排序。默认为升序排序。使用计算函数汇总数据例26.计算9512101号学生的考试总成绩之和SELECTSUM(Grade)FROMSCWHERESno='9512101'对查询结果进行分组计算一般形式为：GROUPBY＜分组依据列〉[，…n][HAVING〈组提取条件〉]例28.统计每门课程的选课人数，列出课程号和人数。SELECTCnoas课程号，COUNT(Sno)as选课人数FROMSCGROUPBYCno多表连接查询1.内连接内连接的格式为：SELECT＜属性或表达式列表〉FROM表1[INNER]JOIN表2ON〈连接条件〉例32.查询每个学生及其修课的情况SELECT*FROMStudentINNERJOINSCON=外连接外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。ANSI方式的外连接的语法格式为：FROM表1LEFT|RIGHT[OUTER]JOIN表2ON〈连接条件〉例39.查询学生的修课情况，包括修了课程的学生和没有修课的学生。SELECT,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCON=也可以用右外连接实现：SELECT,Sname,Cno,GradeFROMSCRIGHTOUTERJOINStudentON=使用子查询进行基于集合的测试例40.查询与“刘晨”在同一个系学习的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘晨')使用子查询进行基于集合的测试例41.查询成绩为大于90分的学生的学号、姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHEREGrade＞90)使用子查询进行比较测试通过比较运算符。、＜＞、＜、＞、＜=、＜=),将一个表达式的值与子查询返回的值进行比较。例43.查询修了‘。02'课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECTSno,GradeFROMSCWHERECno='c02'andGrade＞(SELECTAVG(Grade)fromSCWHERECno=，c02，)使用子查询进行存在性测试例44.查询选修了‘。01'号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=ANDCno='c01')数据更改功能插入数据INSERT语句的格式为：INSERT[INTO]＜表名＞[(＜列名表〉)]VALUES(值列表)例1.将新生记录(9521105,陈冬，男，信息系，18岁)插入到Student表中。INSERTINTOStudentVALUES(‘9521105',‘陈冬‘，'男'，18,'信息系')更新数据(UPDATE)UPDATE语句的语法格式为:UPDATE＜表名〉SET(列名=表达式＞[，…n][WHERE＜更新条件〉]例2.将‘9512101’学生的年龄改为21岁。UPDATEStudentSETSage=21WHERESno='9512101'删除数据(DELETE)DELETE语句的语法格式为：DELETE[FROM]＜表名＞[WHERE＜删除条件＞]例3.删除计算机系不及格学生的修课记录用子查询实现：DELETEFROMSCWHEREGrade<60ANDSnoIN(SELECTSnoFROMStudeWHERESdept='计算机系’)定义视图用多表连接实现：DELETEFROMSCFROMSCJOINStudentON=WHERESdept=’计算机系’ANDGrade<60AS子查询语句CREATEVIEW（视图名〉［（视图列名表）］例1.建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='信息系'例4.定义一个反映学生出生年份的视图AS子查询语句SELECTSno,Sname,2006-SageFROMStudent删除视图DROPVIEW（视图名>视图可简化查询语句例：将信息系学生的视图中学号为9512102的学生姓名改为“刘呈”UPDATEIS_StudentSETSname='刘呈'WHERESno='9512102’例：在信息系学生的视图中找出年龄小于20岁的学生SELECTSno,SageFROMIS_StudentWHERESage<20Unit4数据库设计的基本步骤需求分析阶段（信息要求、处理要求、安全性与完整性要求）一一概念结构设计阶段一一逻辑结构设计阶段一一物理结构设计阶段一一数据库实施阶段一一数据库运行和维护阶段DBMS常用存取方法索引方法，目前主要是B+树索引方法聚簇（Cluster）方法HASH方法UNIT5DBMS对数据库的安全保护功能是通过四方面实现的，即安全性控制、完整性控制、并发性控制和数据库恢复。完整性控制数据库的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。关系模型的完整性包括实体完整性，参照完整性和用户定义完整性。对于违反实体完整性和用户定义完整性规则的操作一般都是采用拒绝执行的方式进行处理。完整性约束条件的作用对象可以是表、元组和列。并发控制数据库的并发操作导致的数据库不一致性主要有以下四种：丢失修改（LostUpdate）：当两个事务;和12读入同一数据做修改，并发执行时，1/巴］或\把12的修改结果覆盖掉。 12 21污读（DirtyRead）:事务T1更新了数据R,事务T2读取了更新后的数据R,事务T1由于某种原因被撤消，修改无效，数据R恢复原值。事务T：得到的数据与数据库的内容不一致，这种情况称为“污读”。 2不可重读（皿^口6@=@员6Read）:事务、读取了数据后，事务工更新了T1读取的数据，当事务T1再读取数据以进行相同操作时，得到的两次值不一致，这种情况称为“不可重读”产生“幽灵”数据:指当事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中的部分记录，或者在其中添加了部分记录，则当T1再次按相同条件读取数据时，发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录。这样的数据对T1来说就是“幽灵”数据或称“幻影”数据。并发控制的主要方式是封锁机制，即加锁L6cking）。排它锁（ExclusiveLock，又称写锁，简称为X锁）——事务T对数据对象A加了*锁，则允许T读取和修改A，但不允许其它事务再对A加任何类型的锁，直到T释放了人上的锁。共享锁（ShareLock，又称读锁，，简称为S锁）——事务T对数据对象A加了$锁，则事务T可以读A，但不能修改A，其它事务只能再对A加S锁，而不能加X锁，直到T释放了A上的S锁。三个封锁协议的主要区别在于X锁（对写数据）S锁（对只读数据）不丢失修改（写）不读脏数据（读）可重复读（读）一级事务全程加锁不加J二级事务全程加锁事务开始加，读完放JJ三级事务全程加锁事务全程加锁JJJ预防死锁两种方法：一次封锁法、顺序封锁法数据库的恢复数据库运行故障：事务故障（可以利用日志文件撤消此事务对数据库已进行的修改）系统故障介质故障（重装数据库，然后利用备份或镜像设备恢复数据库。）UNIT6使用Transact-SQL语言创建数据库CREATEDATABASE数据库名[ON[〈文件格式〉[，…n]]][LOGON{〈文件格式〉[，…n]}]例2：创建一个数据库，数据库名称为：“人事信息数据库”，此数据库包含一个数据文件和一个事务日志文件。数据文件只有主数据文件，其逻辑文件名为“人事信息数据库”，其物理文件名为“人事信息数据库.mdf”，存放位置在默认目录下，其初始大小为10MB，最大大小为30MB，自动增长时的递增量为5MB。事务日志文件的逻辑文件名为“人事信息日志”，物理文件名为“人事信息日志.ldf”，也存放在默认目录下，初始大小为3MB,最大大小为12MB,自动增长时的递增量为2MB。CREATEDATABASE人事信息数据库ON(NAME:人事信息数据库，FILENAME='C:\programfiles\MicrosoftSQLServer\Mssql\Data\人事信息数据库.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=人事信息日志，FILENAME='C:\programfiles\MicrosoftSQLServer\Mssql\Data\人事信息日志."£',SIZE=3,MAXSIZE=12,FILEGROWTH=2)使用Transact-SQL语句删除数据库语法格式为：DROPDATABASE数据库名[，…n]例：用SQL语句删除Testi和Test2数据库：DROPDATABASETesti,Test2使用Transact-SQL语句修改数据库(ALTERDATABASE)创建触发署(CREATETRIGGER)使用SQL语句管理权限(1)授权语句GRANT例1：为用户useri授予Student表的查询权。GRANTSELECTONStudentTOuser1例2：为用户user1授予SC表的查询权和插入权。GRANTSELECT,INSERTONSCTOuser1例