数据库笔记整理

1、01 数据库课程导入一、数据库：是指存储在计算机内的可组织可共享的信息的集合。数据库特征： 数据按一定的数据模型组织、描述和储存 可为各种用户共享 冗余度较小 数据独立性较高 易扩展二、数据库的逻辑结构1）层次型2）网状型3）关系型4）面向对象型三、提高数据库效率方式1）缓存数据2）索引3）高性能硬件四、数据库模型1）概念模型：从用户角度分析，绘制E-R图2）逻辑模型：从计算机角度分析的，绘制数据库模型图3) 物理模型：指的是数据库的存储结构，以及索引。五、数据库设计步骤1）需求分析阶段（收集分析需求）2） 概要设计阶段 (从用户角度分析，绘制E-R图）3）详细设计阶段（从数据库角度分析的，绘

2、制数据库模型图）4）实施阶段（创建数据库、数据表、建立关联、索引，并对数据表进行规范化检查）六、学好数据库能做什么1）DBA数据库管理员2）数据挖掘02 数据模型一、数据模型分类1）概念模型2）逻辑模型（分为层次型、网状型、关系型、面向对象型）3）物理模型二、数据模型的三大要素1）数据结构2) 数据操作3）完整性约束（包括实体完整性、参照完整性和用户定义的完整性）三、概念模型组成部分1）实体：用矩形表示，表示客观存在的可以描述的事物。2）属性:用椭圆形表示，表示实体的特征。3）码：用于唯一标识实体的属性。4）域：代表属性的取值范围，例如Sex取值范围是男，女。5）实体型：用实体名及其属性名集合

3、来抽象同类实体的（例如学生（学号，姓名，性别，出生年份，系，入学时间）6）实体集：同一类型实体的集合7）关系：表示实体间的关系（有1:1 ，1：n，n:1 ，m:n）用菱形表示。四、绘制E-R图步骤1）标识实体2）标识实体的属性3）标识实体关联五、逻辑模型层次模型：是最早的逻辑模型a）每个节点的父节点都是唯一的b）只能表示1对多的关系c）每个记录类型可以选择一个属性排序，叫做码字段b）任何记录只有按照路径查看才有意义e）任何子女记录都不能脱离父记录而存在f）根节点没有父节点1）多对对关系在层次模型中的表示a) 采用冗余节点法：也就是通过增加2个节点，把多对多关系分解成2个1对多关系。例如: 学

4、生与课程是m:n关系，分解成:学生到课程1:n,课程到学生1:n。b) 采用虚拟节点法:是通过增加2个虚拟节点，把多对多关系分解成2个1对多关系。所谓虚拟节点就是一个引用，指向所引用的节点。2)对层次模型的操作a) 增加、修改、删除、查询，同时必须满足完整性约束（指的是数据记录能够正确的反应实际情况）b) 层次模型的完整性约束: 1）添加的子节点必须拥有父节点 2）当删除1个节点时，会同时删除该节点所有子节点 3）更新时，必须保证一致性3）层次模型的存储结构a）邻接法（又叫层次序列链接法):按照树的前序遍历结果实现相邻存储。b）子女-兄弟链接发：是通过给每个节点设置2个指针，分别指向做左边的子

5、节点和最近的兄弟节点。4)层次模型的优缺点a)优点: 结构清晰、查询效率高、提供了较好的完整性支持b)缺点: 对m:n关系表示不自然、插入和删除限制多、访问子女节点必须通过父节点、命令复杂网状模型:1)特点a)可以有多个节点无父节点b)一个节点可以由多个父节点2）网状跟层次的区别 a)可以有多个节点无父节点 b)一个节点可以由多个父节点 c)网状模型允许两个结点之间有多种联系（复合联系） d)网状模型可以更直接地去描述现实世界 e)层次模型实际上是网状模型的一个特例3)网状模型存储结构：常用的方法为：单向链接、双向链接、环状链接、向首链接4)优缺点 优点： a)能够直观的描述数据 b)具有较高

6、的性能。 缺点: a)结构复杂，规模越大越复杂。 b)操作复杂，不易用户使用。关系模型1)术语a)关系：就是表b)元组: 就是一行c)属性: 就是一列d)主码：就是主键，用于唯一标示一条记录（例如学号)03 关系数据库一、笛卡尔乘积：是n个关系相乘的结果。例如：D1为学生集合（T）= 张群，徐晶，王刚D2为性别集合（S）= 男，女 D1与D2的笛卡尔乘积共6条记录，是拿D1中每条记录与D2中所有记录匹配。二、关系代数运算：集合运算、关系运算、比较运算、逻辑运算1)集合运算a)并(U)例如：A U B,返回A表中全部数据，B表中全部数据，对于2表重复的内容只保留一个。b)差(-)例如：A - B

7、,返回A表有并且B表没有的记录。c)交(n)例如：A n B,返回A表、B表共有的记录。2）术语1)元组的连串:表示元祖中包含的分量的集合。例如：若r = (r1 ，rn)，s = (s1 ，sm)，则定义r 与s的连串为：定义rs的连串 = (r1 ，rn ，s1 ，sm)2)关系的度：就是关系包含的属性的数量。3）象集Zx：假设x=张三,表示求x列中值等于张三的分量所对应的Z列中分量的集合（消除重复)4）选择：A5(R)，表示从R中选择A列小于5的行。注意:选择是根据条件筛选，返回的表的结构与原表一致。5）投影.(R)：从关系R中取若干列组成新的关系。例如:A(R)表示从R中选择A列，返回

8、新的关系，只包含A列。6）连接: R 连接 S = rs连串| r R s S rA SB ，表示对R*S进行筛选，筛选条件为rA Sb;6.1)自然链接:从笛卡尔乘积中自动匹配列名相同且分量值相等的记录，并消除重复的列。6.2）外链接:把舍弃的记录也保存到新关系中，对于不匹配的列显示NULL。 a)左外链接:例如A left join B:返回左表中全部记录，对于未匹配的右表中的列设置为NULL。 b)右外链接:例如A right join B:返回右表中全部记录，对于未匹配的左表中的列设置为NULL。 c)完整链接:例如A full join B:返回2表中全部记录，对于未匹配的列设置为N

9、ULL。完整链接的结果为:(左外结果+右外结果-重复行）7）除法例如:关系R(X,Y),S(Y,Z)R S = TrX | trR y(S) Yx返回R中符合条件的分量集合。条件：R中X列分量对应的象集Yx，包含S中Y列的投影y(S)04 关系数据库标准语言SQL一、SQL:结构化查询语言，86年成为ANSI标准，87成为ISO标准。可以使用Sql完成数据库生命周期内全部任务。二、SQL特点:1)综合统一2)高度非过程化3)面向集合的操作方式4)以同一种语法结构提供多种使用方式5)语言简洁，易学易用三、SQL组成部分1)数据定义语言(DDL):用于创建、修改、删除数据库对象（例如数据库、表、视

10、图、索引），动词create、alter、drop。2)数据查询语言(DQL):用于查询数据,动词select。3)数据操纵语言(DML):用于添加、修改、删除数据。动词insert、delete、update。4)数据控制语言(DCL):用于权限控制。动词grant、revoke。四、SqlServer服务控制1）使用SqlServer配置管理器2）使用命令 启动服务:net start 服务名停止服务:net stop 服务名五、关系数据库的三个模式1、外模式:指的是为满足某一应用定义的视图，是对整体数据的部分展示。外模式可以有多个。2、模式：指的是数据库的逻辑结构。是对数据的整体展示。只

11、有一个。3、内模式：指的是数据文件的组织和存储，只有一个。六、数据库分类1)系统数据库(master、model、msdb、tempdb)2)用户数据:指用户自己创建的七、数据库文件组成1）数据文件,扩展名是mdf、ndf。2）日志文件，记录对数据库所做的操作，扩展名是ldf。八、创建数据库create database databaseName on primary( Name=逻辑名称, FileName=物理路径, Size=10MB, MaxSize=100MB, FileGrowth:1M|10%)log on Name=逻辑名称, FileName=物理路径, Size=10MB,

12、 MaxSize=100MB, FileGrowth:1M|10%go- 删除数据库drop database databaseName九、数据库对象1）Schema:架构，用于分类管理数据库对象，便于权限控制。2）表:用于存储数据3) 视图:是虚拟的表，不存储数据。4）索引:用于加快查找效率5）存储过程：由多条sql语句组成的一个代码块，类似于C语言函数，存储在数据库中。6）函数:为实现某个功能编写的代码块7) 触发器:监控某动作的发生，并做出反应十、Schema1)创建： CREATE SCHEMA name AUTHORIZATION userName2)删除: drop schema

13、name cascadesqlserver不支持cascade十一、集合操作1)union：并，把2个查询的结果合并，默认会消除重复，使用All可以保留重复。2)intersect：交,返回2个结果集共同的部分。3)except：差，返回2个结果的茶差集。数 据 表 设 计一、常用数据类型1）数字类型：int、float2）文本类型：char、varchar3）时间类型：datetime4）布尔类型：bit5）货币类型: money二、创建表1）创建列（定义列名、数据类型）2）设置约束建表语法:create table tableName( columnName dbType constrai

14、nt)修改表：alter table tableNameadd|alter column|drop column-添加一列ALTER TABLE ADD 约束-修改一列ALTER TABLE ALTER COLUMN 约束-删除一列ALTER TABLE DROP COLUMN -修改表名EXEC sp_rename , -修改列名EXEC sp_rename , , column删除表drop table tableName三、数据完整性指数据表中的数据能够正确的反应实际情况。约束类型：1、主键约束：用于唯一标识一条记录，前缀pk。2、唯一键约束：用于设置某列的值是唯一的，允许为null，前

15、缀uq。3、外键约束：用于设置子表中的某列引用主表中的某列，前缀fk。4、检查约束：用于检查某列的数据有效性，前缀ck5、默认约束：用于设置某列默认值，前缀df。添加约束的方式：1、建表的同时添加（create table)示例：create table userInfo( uid uniqueidentifier not null primary key, account varchar(20) not null unique, password varchar(2) not null, userName varchar(20),userSex char(2) not null check(

16、userSex=男 or userSex=女) default 男, userAge int not null check(userAge=18 and userAge18 and userAge55)alter table userInfoadd constraint fk_userinfo_deptInfo_deptUid foreign key (deptUid) references MgrDemo_DeptInfo(departId)删除约束alter table tableNamedrop constraint constraint_name数 据 操 作一、插入数据语法Inser

17、t into tableName(columNameList) values(valueList)注意事项：1、列名列表的长度、顺序必须与值列表一致。2、不允许为空的列一定要插入数据。3、有默认值的列可以不插入。4、标识列不能插入数据。插入多行数据1、select . into:把查询到的数据导出到一张新表（自动创建）select * into departInfo2from MgrDemo_DeptInfo2、insert into . select:把查询到的数据导入到某表（现有的表）Insert into MgrDemo_DeptInfo(uid,deptName,deptAddress

18、)select NEWID(),deptName,deptAddress from departInfo2二、修改数据-更新数据 SQL语法UPDATE SET =，= WHERE 如果没有where将修改所有数据行，通常是根据主键修改。三、删除-删除部分数据 SQL语法DELETE FROM WHERE -删除全部数据 SQL语法TRUNCATE TABLE 四、查询一）select1、单表查询SELECT *|列名列表 FROMWHERE 2、多表查询SELECT 列名列表FROM , WHERE . = . AND 二）常用关键字1、distinct：消除重复行2、top n：返回限制的

19、行数3、order by：排序(asc(默认)|desc)4、like：模糊查询(通配符:%(任意长度字符）_(一个字符) (一个范围，代表1个字符） .(排除1个范围，代表1个字符)5、between . and .:表示一个连续的范围6、in(.):表示具体的范围7、is null columnName:判断某列是否为null8、exists(select.):判断一条件是否成立，里面的查询返回结果不为空则结果为true,否则为false。9、group by columnName:根据某列进行分组10、having .:用于分组后的筛选三）聚合函数1、count:统计数量，根据列名统计会

20、忽略null。2、sum:求某列求和3、avg:求某列的平均数4、max:求某列的最大值5、min:求某列的最小值四）子查询语法:select . from 表名where . （子查询）子查询通常是包含在父查询的where子句当中，充当筛选的条件。如果子查询返回的结果不止1个，应该使用in。例如：- 查询年龄比张三大的,显示userName,userAgeselect userName,userAge from MgrDemo_UserInfowhere userAge(select userAge from MgrDemo_UserInfo where userName=张三)视 图一、视

21、图：是虚拟的表，并不存储数据，对视图所做的操作会转化成对表的操作。优点:1、便于筛选数据2、便于授权注意点:1、视图主要用于查询2、当视图数据来源于多张表时不能添加、删除。二、创建视图create view view_namecolumnName.as -select定义语句 with check optiongo三、修改视图Alter view view_nameas -select定义语句 with check optiongo四、删除视图drop view view_name一、数据依赖：指的是关系内部属性之间的约束关系。1）函数依赖：是唯一确定关系，例如学号-姓名。2）多值依赖：多值依

22、赖不能唯一确定，例如教师号多值依赖课程号，因为1门课程可以由多个老师来上。二、数据库设计不规范造成的问题：1）数据冗余2）插入异常、更新异常、删除异常三、范式1）第一范式：表中的每一列都不能再分割。2）第二范式：表中各列完全依赖与主键。3）第三范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性05 数据存储与索引一、存储介质磁盘：由磁道、扇区、扇面、柱面组成。格式化：就是把磁盘分成指定大小的块(4kb，8b)并编号。二、数据库文件的存储数据库文件以“块”的方式存储在磁盘“块”上，更小的“块”能够提高磁盘的利用率三、SQL Server1）在SQL Ser

23、ver中，数据存储的基本单位是页，页的大小是 8 KB，则1MB的数据文件 有 128个 页（1MB=128*8KB）2）每页的开头 96 字节，用于存储有关页的系统信息，包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID 3）单记录最大8060字节4）在页中通过行偏移表定位记录5）区是SqlServer管理空间的基本单位，由8个连续的页组成（64Kb)6) 统一区：由单个对象使用。7）混合区：最多可由8个对象使用。8）SQL Server 数据库文件由文件页组成，每个文件有文件Id，每页有页编号，例如01:0001,表示01文件编号为0001的页。9）SQL Server表包含

24、在一个或多个分区中，每个分区在一个堆或一个聚集索引结构包含数据行四、Oracle1)oracle最小存储单位是块（大小可以是2kb,4kb，8kb,16kb)2)数据扩展：由一些连续的块组成3)段：由多个数据扩展组成4）Oracle中的数据逻辑上存储于表空间（tablespace）中，而物理上则存储于属于表空间的数据文件（datafile）中5）Oracle数据库中每个表空间是多个物理数据文件构成的。一个数据文件只能属于一个表空间。 五、索引1）用于对数据进行编排，有利于提高查询效率。2）适合建立索引的情况：经常用于查询的列。3）不适合建立索引：表中数据较少，或者某列的值重复较多，例如性别。六

25、、索引类型1)聚集索引：索引表的顺序与数据表的顺序相同，一张表只能有一个聚集索引。2)非聚集索引:索引表的顺序与数据表的顺序只有逻辑上的关系，可以有多个。3)主键索引4)唯一索引06 数据库设计一、数据库设计特点:1）数据库设计=干件（技术与管理的界面）+软件+硬件2）数据库设计=结构（数据设计）+行为（处理设计）二、数据库设计阶段1）需求分析阶段：产物是数据字典。2）概要设计阶段：产物是ER图。3) 逻辑设计阶段：产物是数据库模型图。4）物理设计阶段5）实施阶段:产物是数据库6）运行维护阶段三、需求分析阶段1）重点是分析“数据”和“处理2) 难点：用户缺少计算机知识，分析人员缺少用户的专业知

26、识3）结构化分析方法（Structured Analysis，简称SA方法）：从最上层的系统组织机构入手， 自顶向下、逐层分解分析系统。4）数据字典：是数据分析与处理的主要产物由数据项、数据结构、数据流、数据存储、处理过程组成。5）数据流图用图形化方式描述数据流动过程。DFD(数据流图），组成部分：1）正方形:外部实体2）圆角矩形：加工过程3）右开口矩形：数据存储4）箭头：数据流四、概要设计阶段将需求分析得到需求抽象为概念模型的过程4种设计策略:1)自顶向下 2）自下向上 3）逐步扩展 4)混合策略ER图组成：1）矩形：实体2）椭圆: 属性3）菱形: 关系五、逻辑设计把概念设计阶段设计好的”E

27、-R图”转换为与DBMS所支持的数据模型相符合的”逻辑结构”产物：数据库模型图逻辑结构设计的步骤将概念结构转化为一般的关系、网状、层次模型将”关系、网状、层次模型”向特定DBMS支持下”数据模型”转换对数据模型进行优化设计用户子模式ER图转数据库模型图规则：1、实体名对应表名。2、属性名就是列名。3、关系就是外键。数据模型优化步骤:1）确定依赖关系2）消除冗余关系3）确定所属范式4）审核数据处理要求5）分解关系模式六、物理设计设计关系、索引等文件的物理存储结构为关系模式选择存取方法(建立存取路径)七、实施阶段1）创建数据库（包括表、视图、约束等）2）组织数据入库3）数据库设计可以与数据库程序设

28、计并行4）试运行(包括功能测试、性能测试)八、维护阶段07 事物一、事务：作为一个逻辑单元，是把一系列sql语句作为一个整体，要么成功，要么失败，保证数据的完整性。二、特性：1）原子性：事务作为一个整体，是不可分割的。2）一致性：事务对于数据的影响是一致的。3）隔离性：事务与事务时间是隔离的。4）持久性：事务一旦成功对数据库的影响是持久的。四个特性称为ACID特性。三、事务的类型1）自动事务：默认，每条语句都是一个事务。2）隐式事务：通过SET IMPLICIT_TRANSACTIONS ON开启，自动创建事务，当事务提交或撤消后会自动再创建事务。3）显式事务：明确制定事务的开始。4)事务控制

29、语句a)begin trans|transactionb)commitc)rollback四、异常处理begin tryend trybegin catchend catch五、事务并发1）事务串行执行：当一个事务执行完成才可以执行下一个事务。T1-T22）事务并行执行：系统采用分时的方式交叉执行事务，例如:T1-T2-T1-T23)脏读：一个事务读取到了另外一个事务没有提交的数据4)不可重复读：在同一事务中，两次读取同一数据，得到内容不同5)丢失更新:A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果6)并发控制的主要技术封锁(Locking)时间戳(Times

30、tamp)乐观控制法商用的DBMS一般都采用锁机制 7)封锁就是事务T在对某个数据对象（例如表、记录等）操作之前，先向系统发出请求，对其加锁基本封锁类型排它锁（Exclusive Locks，简记为X锁）其他用户不能访问共享锁（Share Locks，简记为S锁）8)封锁的问题死锁：多个事务相互等待解决办法：a)一次封锁法 b)顺序封锁法活锁：某事务可能长期处于等待解决办法：先到先服务的策略。08 T-SQL基础一、变量定义变量declare 变量名称 类型=默认值;变量赋值1、set 变量名称=value; 一次只能给1个变量赋予标量值2、select 变量名称=value;1次可以给多个变

31、量赋值二、输出信息1、print:1次只能输出1项信息2、select:1次可以输出多项信息三、控制结构1、begin.end:表示一个语句块，类似于2、if.else3、while(条件) begin.break|continue end4、case 表达式when .else . end五、局部变量与全局变量1、局部变量：用户自己所定义，使用一个作为前缀。2、全局变量:系统所提供的，使用作为前缀。3、常用全局变量前加selectidentity:获取标识列的值rowcount:获取受影响的行数error:获取错误号（判断最近的一行语句），0表示上一行语句无错误fetch_status:获取

32、游标的状态六、常用函数1、日期函数getDate():获取当前日期year(时间):获取年数month(时间):获取月份day(时间）：获取今天是几号DateAdd(datePart,number,date):在date上加上一个日期例如：DATEADD(day,7,regTime)GETDATE():表示1周以内DATEADD(day,7,regTime)GETDATE()：表示1周以外DateDiff(datepart,startTime,endTime):获取2个时间的差值DatePart(detepart,time):获取日期的某一部分2、数学函数abs(number):返回绝对值fl

33、oor(number):返回小于等于当前数的整数ceiling(number):返回大于等于当前数的整数rand():返回0-1之间的随机数3、字符函数charIndex(searchStr,sourceStr,startLength):起始为1，找不到返回0str(number):把数字转换成字符串（转换成长度为10的字符串，不足时在前面加空格）convert(targetType,参数):把参数转换成目标类型cast(expression1 as targetType):把表达式转换成目标类型replace(sourceStr,searchStr,replaceStr):替换字符串subs

34、tring(sourceStr,startPosition,length):从statrPosition位置开始截取length个字符left(sourceStr,length):从左边截取指定长度字符right(sourceStr,length):从右边截取指定长度字符datalength(sourceStr):获取字符串的长度LEN（）获取字符串的长度，但字符串后面的空格不计入总长度之中ltrim(sourceStr):去除左边空格rtrim(sourceStr):去除右边空格reverse(sourceStr):反转4、系统函数isDate(param):判断参数是否是日期isNumeric(param):判断参数是否是数字isNull(param1,newValue):判断参数是为为Null,如果为Null就把newValue赋值给参数5、自定义函数1)创建函数1、标量函数（返回基本类型)create function function_name(参数列表)returns 返回类型with encryption|schemabindingasbegin statementsend2、表值函数（返回类型为table)2.1语法：内联表值函数CREATE FUNCTION ()Returns TABLEWITH ENCRYPTION | SCHEMAB