数据库笔记整理.docx

数据库笔记整理.docx

《数据库笔记整理.docx》由会员分享，可在线阅读，更多相关《数据库笔记整理.docx（29页珍藏版）》请在冰点文库上搜索。

数据库笔记整理

01数据库课程导入

一、数据库：

是指存储在计算机内的可组织可共享的信息的集合。

数据库特征：

数据按一定的数据模型组织、描述和储存

可为各种用户共享

冗余度较小

数据独立性较高

易扩展

二、数据库的逻辑结构

1）层次型

2）网状型

3）关系型

4）面向对象型

三、提高数据库效率方式

1）缓存数据

2）索引

3）高性能硬件

四、数据库模型

1）概念模型：

从用户角度分析，绘制E-R图

2）逻辑模型：

从计算机角度分析的，绘制数据库模型图

3）物理模型：

指的是数据库的存储结构，以及索引。

五、数据库设计步骤

1）需求分析阶段（收集分析需求）

2）概要设计阶段（从用户角度分析，绘制E-R图）

3）详细设计阶段（从数据库角度分析的，绘制数据库模型图）

4）实施阶段（创建数据库、数据表、建立关联、索引，并对数据表进行规范化检查）

六、学好数据库能做什么

1）DBA数据库管理员

2）数据挖掘

02数据模型

一、数据模型分类

1）概念模型

2）逻辑模型（分为层次型、网状型、关系型、面向对象型）

3）物理模型

二、数据模型的三大要素

1）数据结构

2）数据操作

3）完整性约束（包括实体完整性、参照完整性和用户定义的完整性）

三、概念模型组成部分

1）实体：

用矩形表示，表示客观存在的可以描述的事物。

2）属性:

用椭圆形表示，表示实体的特征。

3）码：

用于唯一标识实体的属性。

4）域：

代表属性的取值范围，例如Sex取值范围是男，女。

5）实体型：

用实体名及其属性名集合来抽象同类实体的（例如学生（学号，姓名，性别，出生年份，系，入学时间））

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对多关系。

例如:

学生与课程是m:

n关系，分解成:

学生到课程1:

n,课程到学生1:

n。

b）采用虚拟节点法:

是通过增加2个虚拟节点，把多对多关系分解成2个1对多关系。

所谓虚拟节点就是一个引用，指向所引用的节点。

2）对层次模型的操作

a）增加、修改、删除、查询，同时必须满足完整性约束（指的是数据记录能够正确的反应实际情况）

b）层次模型的完整性约束:

1）添加的子节点必须拥有父节点

2）当删除1个节点时，会同时删除该节点所有子节点

3）更新时，必须保证一致性

3）层次模型的存储结构

a）邻接法（又叫层次序列链接法）:

按照树的前序遍历结果实现相邻存储。

b）子女-兄弟链接发：

是通过给每个节点设置2个指针，分别指向做左边的子节点和最近的兄弟节点。

4）层次模型的优缺点

a）优点:

结构清晰、查询效率高、提供了较好的完整性支持

b）缺点:

对m:

n关系表示不自然、插入和删除限制多、访问子女节点必须通过父节点、命令复杂

网状模型:

1）特点

a）可以有多个节点无父节点

b）一个节点可以由多个父节点

2）网状跟层次的区别

a）可以有多个节点无父节点

b）一个节点可以由多个父节点

c）网状模型允许两个结点之间有多种联系（复合联系）

d）网状模型可以更直接地去描述现实世界

e）层次模型实际上是网状模型的一个特例

3）网状模型存储结构：

常用的方法为：

单向链接、双向链接、环状链接、向首链接

4）优缺点

优点：

a）能够直观的描述数据b）具有较高的性能。

缺点:

a）结构复杂，规模越大越复杂。

b）操作复杂，不易用户使用。

关系模型

1）术语

a）关系：

就是表

b）元组:

就是一行

c）属性:

就是一列

d）主码：

就是主键，用于唯一标示一条记录（例如学号）

03关系数据库

一、笛卡尔乘积：

是n个关系相乘的结果。

例如：

D1为学生集合（T）={张群，徐晶，王刚}

D2为性别集合（S）={男，女}

D1与D2的笛卡尔乘积共6条记录，是拿D1中每条记录与D2中所有记录匹配。

二、关系代数

运算：

集合运算、关系运算、比较运算、逻辑运算

1）集合运算

a）并（U）

例如：

AUB,返回A表中全部数据，B表中全部数据，对于2表重复的内容只保留一个。

b）差（-）

例如：

A-B,返回A表有并且B表没有的记录。

c）交（n）

例如：

AnB,返回A表、B表共有的记录。

2）术语

1）元组的连串:

表示元祖中包含的分量的集合。

例如：

若r=（r1，…，rn），s=（s1，…，sm），则定义r与s的连串为：

定义rs的连串=（r1，…，rn，s1，…，sm）

2）关系的度：

就是关系包含的属性的数量。

3）象集Zx：

假设x='张三',表示求x列中值等于张三的分量所对应的Z列中分量的集合（消除重复）

4）选择：

σA<5（R），表示从R中选择A列小于5的行。

注意:

选择是根据条件筛选，返回的表的结构与原表一致。

5）投影Π...（R）：

从关系R中取若干列组成新的关系。

例如:

ΠA（R）表示从R中选择A列，返回新的关系，只包含A列。

6）连接:

R连接S={rs连串|r∈R∧s∈S∧r[A]θS[B]}，表示对R*S进行筛选，筛选条件为r[A]θS[b];

6.1）自然链接:

从笛卡尔乘积中自动匹配列名相同且分量值相等的记录，并消除重复的列。

6.2）外链接:

把舍弃的记录也保存到新关系中，对于不匹配的列显示NULL。

a）左外链接:

例如AleftjoinB:

返回左表中全部记录，对于未匹配的右表中的列设置为NULL。

b）右外链接:

例如ArightjoinB:

返回右表中全部记录，对于未匹配的左表中的列设置为NULL。

c）完整链接:

例如AfulljoinB:

返回2表中全部记录，对于未匹配的列设置为NULL。

完整链接的结果

为:

（左外结果+右外结果-重复行）

7）除法

例如:

关系R（X,Y）,S（Y,Z）

R÷S={Tr[X]|tr∈R∧Π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）:

用于创建、修改、删除数据库对象（例如数据库、表、视图、索引），动词create、alter、drop。

2）数据查询语言（DQL）:

用于查询数据,动词select。

3）数据操纵语言（DML）:

用于添加、修改、删除数据。

动词insert、delete、update。

4）数据控制语言（DCL）:

用于权限控制。

动词grant、revoke。

四、SqlServer服务控制

1）使用SqlServer配置管理器

2）使用命令

启动服务:

netstart服务名

停止服务:

netstop服务名

五、关系数据库的三个模式

1、外模式:

指的是为满足某一应用定义的视图，是对整体数据的部分展示。

外模式可以有多个。

2、模式：

指的是数据库的逻辑结构。

是对数据的整体展示。

只有一个。

3、内模式：

指的是数据文件的组织和存储，只有一个。

六、数据库分类

1）系统数据库（master、model、msdb、tempdb）

2）用户数据:

指用户自己创建的

七、数据库文件组成

1）数据文件,扩展名是mdf、ndf。

2）日志文件，记录对数据库所做的操作，扩展名是ldf。

八、创建数据库

createdatabasedatabaseNameon[primary]

（

Name="逻辑名称",

FileName="物理路径",

Size=10MB,

MaxSize=100MB,

FileGrowth:

1M|10%

）

logon

{

Name="逻辑名称",

FileName="物理路径",

Size=10MB,

MaxSize=100MB,

FileGrowth:

1M|10%

}

go

--删除数据库

dropdatabasedatabaseName

九、数据库对象

1）Schema:

架构，用于分类管理数据库对象，便于权限控制。

2）表:

用于存储数据

3）视图:

是虚拟的表，不存储数据。

4）索引:

用于加快查找效率

5）存储过程：

由多条sql语句组成的一个代码块，类似于C语言函数，存储在数据库中。

6）函数:

为实现某个功能编写的代码块

7）触发器:

监控某动作的发生，并做出反应

十、Schema

1）创建：

CREATESCHEMAnameAUTHORIZATIONuserName

2）删除:

dropschemaname[cascade]

sqlserver不支持cascade

十一、集合操作

1）union：

并，把2个查询的结果合并，默认会消除重复，使用All可以保留重复。

2）intersect：

交,返回2个结果集共同的部分。

3）except：

差，返回2个结果的茶差集。

数据表设计

一、常用数据类型

1）数字类型：

int、float

2）文本类型：

char、varchar

3）时间类型：

datetime

4）布尔类型：

bit

5）货币类型:

money

二、创建表

1）创建列（定义列名、数据类型）

2）设置约束

建表语法:

createtabletableName

（

columnNamedbTypeconstraint

）

修改表：

altertabletableName

add|altercolumn|dropcolumn

--添加一列

ALTERTABLE<表名>ADD<列名><数据类型>约束

--修改一列

ALTERTABLE<表名>ALTERCOLUMN<列名><数据类型>约束

--删除一列

ALTERTABLE<表名>DROPCOLUMN<列名>

--修改表名EXECsp_rename<原表名>,<新表名>

--修改列名EXECsp_rename<表名.原列名>,<新列名>,'column'

删除表

droptabletableName

三、数据完整性

指数据表中的数据能够正确的反应实际情况。

约束类型：

1、主键约束：

用于唯一标识一条记录，前缀pk。

2、唯一键约束：

用于设置某列的值是唯一的，允许为null，前缀uq。

3、外键约束：

用于设置子表中的某列引用主表中的某列，前缀fk。

4、检查约束：

用于检查某列的数据有效性，前缀ck

5、默认约束：

用于设置某列默认值，前缀df。

添加约束的方式：

1、建表的同时添加（createtable）

示例：

createtableuserInfo

（

uiduniqueidentifiernotnullprimarykey,

accountvarchar（20）notnullunique,

passwordvarchar

（2）notnull,

userNamevarchar（20）,

userSexchar

（2）notnullcheck（userSex='男'oruserSex='女'）default'男',

userAgeintnotnullcheck（userAge>=18anduserAge<=55）,

regTimedatetimenotnulldefaultgetDate（）,

departIdintforeignkey（departId）referencesdepartInfo（departId）

）

2、以修改表的方式添加（altertable）

示例：

altertableuserInfo

addconstraintpk_userinfo_uidprimarykey（uid）

altertableuserInfo

addconstraintuq_userinfo_accountunique（account）

altertableuserInfo

addconstraintdf_userinfo_sexdefault（'男'）foruserSex

altertableuserInfo

addconstraintck_userinfo_userAgecheck（userage>18anduserAge<55）

altertableuserInfo

addconstraintfk_userinfo_deptInfo_deptUidforeignkey（deptUid）referencesMgrDemo_DeptInfo（departId）

删除约束

altertabletableName

dropconstraintconstraint_name

数据操作

一、插入数据

语法

InsertintotableName（columNameList）values（valueList）

注意事项：

1、列名列表的长度、顺序必须与值列表一致。

2、不允许为空的列一定要插入数据。

3、有默认值的列可以不插入。

4、标识列不能插入数据。

插入多行数据

1、select...into:

把查询到的数据导出到一张新表（自动创建）

select*intodepartInfo2

fromMgrDemo_DeptInfo

2、insertinto...select:

把查询到的数据导入到某表（现有的表）

InsertintoMgrDemo_DeptInfo（uid,deptName,deptAddress）

selectNEWID（）,deptName,deptAddressfromdepartInfo2

二、修改数据

--更新数据SQL语法

UPDATE<表名>

SET<属性列1>=<值1>，<属性列2>=<值2>…

WHERE<条件子句>

如果没有where将修改所有数据行，通常是根据主键修改。

三、删除

--删除部分数据SQL语法

DELETEFROM<表名>WHERE<条件子句>

--删除全部数据SQL语法

TRUNCATETABLE<表名>

四、查询

一）select

1、单表查询

SELECT*|列名列表FROM<表名>

WHERE<条件子句>

2、多表查询

SELECT列名列表FROM<表名1>,<表名2>

WHERE<表名1>.<外键列>=<表名2>.<主键列>

AND<条件子句>

二）常用关键字

1、distinct：

消除重复行

2、topn：

返回限制的行数

3、orderby：

排序（asc（默认）|desc）

4、like：

模糊查询（通配符:

%（任意长度字符）_（一个字符）[]（一个范围，代表1个字符）[^...]（排除1个范围，代表1个字符）

5、between...and...:

表示一个连续的范围

6、in（...）:

表示具体的范围

7、isnullcolumnName:

判断某列是否为null

8、exists（select...）:

判断一条件是否成立，里面的查询返回结果不为空则结果为true,否则为false。

9、groupbycolumnName:

根据某列进行分组

10、having...:

用于分组后的筛选

三）聚合函数

1、count:

统计数量，根据列名统计会忽略null。

2、sum:

求某列求和

3、avg:

求某列的平均数

4、max:

求某列的最大值

5、min:

求某列的最小值

四）子查询

语法:

select...from表名

where...（子查询）

子查询通常是包含在父查询的where子句当中，充当筛选的条件。

如果子查询返回的结果不止1个，应该使用in。

例如：

--查询年龄比张三大的,显示userName,userAge

selectuserName,userAgefromMgrDemo_UserInfo

whereuserAge>（selectuserAgefromMgrDemo_UserInfowhereuserName='张三'）

视图

一、视图：

是虚拟的表，并不存储数据，对视图所做的操作会转化成对表的操作。

优点:

1、便于筛选数据

2、便于授权

注意点:

1、视图主要用于查询

2、当视图数据来源于多张表时不能添加、删除。

二、创建视图

createviewview_name[columnName...]

as

--select定义语句

[withcheckoption]

go

三、修改视图

Alterviewview_name

as

--select定义语句

[withcheckoption]

go

四、删除视图

dropviewview_name

一、数据依赖：

指的是关系内部属性之间的约束关系。

1）函数依赖：

是唯一确定关系，例如学号->姓名。

2）多值依赖：

多值依赖不能唯一确定，例如教师号多值依赖课程号，因为1门课程可以由多个老师来上。

二、数据库设计不规范造成的问题：

1）数据冗余

2）插入异常、更新异常、删除异常

三、范式

1）第一范式：

表中的每一列都不能再分割。

2）第二范式：

表中各列完全依赖与主键。

3）第三范式：

要求实体的属性完全依赖于主关键字。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性

05数据存储与索引

一、存储介质

磁盘：

由磁道、扇区、扇面、柱面组成。

格式化：

就是把磁盘分成指定大小的块（4kb，8b）并编号。

二、数据库文件的存储

数据库文件以“块”的方式存储在磁盘“块”上，更小的“块”能够提高磁盘的利用率

三、SQLServer

1）在SQLServer中，数据存储的基本单位是页，页的大小是8KB，则1MB的数据文件有128个页（1MB=128*8KB）

2）每页的开头96字节，用于存储有关页的系统信息，包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID

3）单记录最大8060字节

4）在页中通过行偏移表定位记录

5）区是SqlServer管理空间的基本单位，由8个连续的页组成（64Kb）

6）统一区：

由单个对象使用。

7）混合区：

最多可由8个对象使用。

8）SQLServer数据库文件由文件页组成，每个文件有文件Id，每页有页编号，例如01:

0001,表示01文件编号为0001的页。

9）SQLServer表包含在一个或多个分区中，每个分区在一个堆或一个聚集索引结构包含数据行

四、Oracle

1）oracle最小存储单位是块（大小可以是2kb,4kb，8kb,16kb）

2）数据扩展：

由一些连续的块组成

3）段：

由多个数据扩展组成

4）Oracle中的数据逻辑上存储于表空间（tablespace）中，而物理上则存储于属于表空间的数据文件（datafile）中

5）Oracle数据库中每个表空间是多个物理数据文件构成的。

一个数据文件只能属于一个表空间。

五、索引

1）用于对数据进行编排，有利于提高查询效率。

2）适合建立索引的情况：

经常用于查询的列。

3）不适合建立索引：

表中数据较少，或者某列的值重复较多，例如性别。

六、索引类型

1）聚集索引：

索引表的顺序与数据表的顺序相同，一张表只能有一个聚集索引。

2）非聚集索引:

索引表的顺序与数据表的顺序只有逻辑上的关系，可以有多个。

3）主键索引

4）唯一索引

06数据库设计

一、数据库设计

特点:

1）数据库设计=干件（技术与管理的界面）+软件+硬件

2）数据库设计=结构（数据设计）+行为（处理设计）

二、数据库设计阶段

1）需求分析阶段：

产物是数据字典。

2）概要设计阶段：

产物是ER图。

3）逻辑设计阶段：

产物是数据库模型图。

4）物理设计阶段

5）实施阶段:

产物是数据库

6）运行维护阶段

三、需求分析阶段

1）重点是分析“数据”和“处理"

2）难点：

用户缺少计算机知识，分析人员缺少用户的专业知识

3）结构化分析方法（StructuredAnalysis，简称SA方法）：

从最上层的系统组织机构入手，自顶向下、逐层分解分析系统。

4）数据字典：

是数据分析与处理的主要产物由数据项、数据结构、数据流、数据存储、处理过程组成。

5）数据流图

用图形化方式描述数据流动过程。

DFD（数据流图），组成部分：

1）正方形:

外部实体

2）圆角矩形：

加工过程

3）右开口矩形：

数据存储

4）箭头：

数据流

四、概要设计阶段

将需求分析得到需求抽象为概念模型的过程

4种设计策略:

1）自顶向下2）自下向上3）逐步扩展4）混合策略

ER图组成：

1）矩形：

实体

2）椭圆:

属性

3）菱形:

关系

五、逻辑设计

把概念设计阶段设计好的”E-R图”转换为与DBMS所支持的数据模型相符合的”逻辑结构”

产物：

数据库模型图

逻辑结构设计的步骤

①将概念结构转化为一般的关系、网状、层次模型

②将”关系、网状、层次模型”向特定DBMS支持下”数据模型”转换

③对数据模型进行优化

④设计用户子模式

ER图转数据库模型图规则：

1、实体名对应表名。

2、属性名就是列名。

3、关系就是外键。

数据模型优化步骤:

1）确定依赖关系

2）消除冗余关系

3）确定所属范式

4）审核数据处理要求

5）分解关系模式

六、物理设计

设计关系、索引等文件的物理存储结构

为关系模式选择存取方法（建立存取路径）

七、实施阶段

1）创建数据库（包括表、视图、约束等）

2）组织数据入库

3）数据库设计可以与数据库程序设计并行

4）试运行（包括功能测试、性能测试）

八、维护阶段

07事物

一、事务：

作为一个逻辑单元，是把一系列sql语句作为一个整体，要么成功，要么失败，保证数据的完整性。

二、特性：

1）原子性：

事务作为一个整体，是不可分割的。

2）一致性：

事务对于数据的影响是一致的。

3）隔离性：

事务与事务时间是隔离的。

4）持久性：

事务一旦成功对数据库的影响是持久的。

四个特性称为ACID特性。

三、事务的类型

1）自动事务：

默认，每条语句都是一个事务。

2）隐式事务：

通过SETIMPLICIT_TRANSACTIONSON开启，