数据库知识点整理全.docx

数据库知识点整理全.docx

《数据库知识点整理全.docx》由会员分享，可在线阅读，更多相关《数据库知识点整理全.docx（17页珍藏版）》请在冰豆网上搜索。

数据库知识点整理全

UNIT1

四个基本概念

1.数据（Data）:

数据库中存储的基本对象

2.数据库的定义:

数据库（Database,简称DB）是长期储存在计算机内、有组织的、可共享的大量数据集合

3.数据库管理系统（简称DBMS）：

位于用户与操作系统之间的一层数据管理软件（系统软件）。

用途:

科学地组织和存储数据;高效地获取和维护数据

主要功能:

数据定义功能;数据操纵功能;数据库的运行管理;数据库的建立和维护功能（实用程序）

4.数据库系统（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，则记作X↔Y。

"

范式

从外到里依此增加

模式分解的准则：

模式分解具有无损连接性；模式分解能够保持函数依赖

规范化理论主要是研究关系中各属性之间的依赖关系，根据依赖关系的不同，我们介绍了不包含子属性的第一范式，到消除了属性间的部分依赖关系的第二范式，

再到消除了属性间的传递依赖关系的第三范式，最后到每个决定因子都必须是候选码的BCNF。

范式的每一次升级都是通过模式分解实现的，在进行模式分解时应注意保持分解后的关系能够具有无损连接性并能保持原有的函数依赖关系。

-

对于一般的数据库应用来说，设计到第三范式就足够了。

因为规范化程度越高,分解得越细,表的个数越多,则在检索操作时会因连接而降低检索效率。

例：

S-D-L（Sno，Dept，Loc）有函数依赖：

Sno→Dept，Dept→Loc

不是第三范式的。

至少可以有三种分解方案，分别为：

方案1：

S-L（Sno，Loc），D-L（Dept，Loc）,将S-D-L分解投影得到S-L和D-L关系

方案2：

S-D（Sno，Dept），S-L（Sno，Loc）

.

方案3：

S-D（Sno，Dept），D-L（Dept，Loc）

UNIT3

Sql功能

命令动词

数据定义

）

CREATEDROPALTER

数据查询

SELECT

数据操纵

INSERTUPDATEDELETE

数据控制

GRANTREVOKE

\

SQL的数据类型：

数值型；字符串型；日期时间类型；货币类型

数据定义功能

—

/

建立表CREATETABLE

例1．为SC表添加“修课类别”列，此列的定义为：

XKLBchar（4）

ALTERTABLESCADDXKLBchar（4）NULL

"

例2．将新添加的XKLB的类型改为char（6）。

ALTERTABLESCALTERCOLUMNXKLBchar（6）

例3．删除Course表的Period列

ALTERTABLECourseDROPCOLUMNPeriod

索引：

聚簇索引、非聚簇索引:

复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。

建立索引

格式：

create[unique][clustered]index索引名on表名

\

（<列名１>[asc|desc][，…]）

unique：

用于指定为表创建唯一索引，即不允许存在索引值相同的两行。

clustered：

用于指定创建的索引为聚簇索引。

默认是非聚簇索引。

asc升序，desc降序，默认为asc

例1：

为学生表建立按学号升序索引

createindexstudent_snoonstudent（sno）

例2：

为选课表按学号升序和课程号降序建唯一索引

）

createuniqueindexSCIonsc（sno，cnodesc）

例3:

为学生表按系升序建立聚簇索引。

createclusteredindexTIonstudent（sdept）

删除索引

语句基本格式：

dropindex<表名.索引名>

例4：

删除表SC的索引SCI。

;

dropindex

注：

索引名前一定要加：

表名

SELECT<目标列名序列>

FROM<数据源>

[WHERE<检索条件表达式>]

[GROUPBY<分组依据列>]

[HAVING<组提取条件>][ORDERBY<排序依据列>]

[ORDERBY<排序依据列>]

--需要哪些列

--来自于哪些表

--根据什么条件查询

--对查询结果进行分组

--指定组的选择条件

--对查询结果进行排序

简单查询（单表查询）

例1.查询全体学生的学号与姓名

SELECTSno，SnameFROMStudent

比较大小

例8．查询所有年龄在20岁以下的学生的姓名及年龄。

SELECTSname,SageFROMStudentWHERESage<20

或：

SELECTSname,SageFROMStudentWHERENOTSage>=20

确定范围

BETWEEN…AND和NOTBETWEEN…AND

确定集合（IN）

IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组

使用IN的格式为：

列名[NOT]IN（常量1,常量2,…常量n）

IN的含义为：

当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；

NOTIN的含义正好相反：

当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录；

例12．查询信息系、数学系和计算机系学生的姓名和性别。

SELECTSname,SsexFROMStudent

WHERESdeptIN（'信息系','数学系','计算机系'）

此句等价于：

SELECTSname,SsexFROMStudent

WHERESdept=‘信息系’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,GradeFROMStudent

LEFTOUTERJOINSC

ON=

也可以用右外连接实现：

SELECT,Sname,

Cno,GradeFROMSC

RIGHTOUTERJOINStudent

ON=

使用子查询进行基于集合的测试

例40．查询与“刘晨”在同一个系学习的学生。

SELECTSno,Sname,SdeptFROMStudent

WHERESdeptIN

（SELECTSdeptFROMStudent

WHERESname='刘晨'）

使用子查询进行基于集合的测试

例41．查询成绩为大于90分的学生的学号、姓名。

SELECTSno,SnameFROMStudent

WHERESnoIN（SELECTSnoFROMSCWHEREGrade>90）

使用子查询进行比较测试

通过比较运算符（=、<>、<、>、<=、<=），将一个表达式的值与子查询返回的值进行比较。

例43．查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。

SELECTSno,GradeFROMSC

WHERECno='c02'andGrade>（

SELECTAVG（Grade）fromSC

WHERECno='c02'）

使用子查询进行存在性测试

例44．查询选修了‘c01’号课程的学生姓名。

SELECTSnameFROMStudent

WHEREEXISTS

（SELECT*FROMSC

WHERESno=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．删除计算机系不及格学生的修课记录

用子查询实现:

DELETEFROMSC

WHEREGrade<60ANDSnoIN

（SELECTSnoFROMStude

WHERESdept=‘计算机系’）

用多表连接实现:

DELETEFROMSC

FROMSCJOINStudentON=

WHERESdept=‘计算机系’ANDGrade<60

定义视图

CREATEVIEW<视图名>[（视图列名表）]AS子查询语句

例1．建立信息系学生的视图。

CREATEVIEWIS_Student

AS

SELECTSno,Sname,Sage

FROMStudentWHERESdept='信息系'

例4．定义一个反映学生出生年份的视图

CREATEVIEWBT_S（Sno,Sname,Sbirth）

AS

SELECTSno,Sname,2006-Sage

FROMStudent

删除视图

DROPVIEW<视图名>

视图可简化查询语句

例：

将信息系学生的视图中学号为9512102的学生姓名改为“刘呈”

UPDATEIS_StudentSETSname=‘刘呈’

WHERESno=‘9512102’

例：

在信息系学生的视图中找出年龄小于20岁的学生

SELECTSno,Sage

FROMIS_StudentWHERESage<20

Unit4

数据库设计的基本步骤

需求分析阶段（信息要求、处理要求、安全性与完整性要求）——概念结构设计阶段——逻辑结构设计阶段——物理结构设计阶段——数据库实施阶段——数据库运行和维护阶段

DBMS常用存取方法：

索引方法，目前主要是B+树索引方法

聚簇（Cluster）方法

HASH方法

UNIT5

DBMS对数据库的安全保护功能是通过四方面实现的，即安全性控制、完整性控制、并发性控制和数据库恢复。

完整性控制

数据库的完整性是指保护数据库中数据的正确性、有效性和相容性，防止错误的数据进入数据库造成无效操作。

关系模型的完整性包括实体完整性，参照完整性和用户定义完整性。

对于违反实体完整性和用户定义完整性规则的操作一般都是采用拒绝执行的方式进行处理。

完整性约束条件的作用对象可以是表、元组和列。

并发控制

数据库的并发操作导致的数据库不一致性主要有以下四种：

丢失修改（LostUpdate）：

当两个事务T1和T2读入同一数据做修改，并发执行时，T2把T1或T1把T2的修改结果覆盖掉。

污读（DirtyRead）:

事务T1更新了数据R，事务T2读取了更新后的数据R，事务T1由于某种原因被撤消，修改无效，数据R恢复原值。

事务T2得到的数据与数据库的内容不一致，这种情况称为“污读”。

不可重读（UnrepeatableRead）:

事务T1读取了数据后，事务T2更新了T1读取的数据，当事务T1再读取数据以进行相同操作时，得到的两次值不一致，这种情况称为“不可重读”

产生“幽灵”数据:

指当事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中的部分记录，或者在其中添加了部分记录，则当T1再次按相同条件读取数据时，发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录。

这样的数据对T1来说就是“幽灵”数据或称“幻影”数据。

并发控制的主要方式是封锁机制，即加锁（Locking）。

排它锁（ExclusiveLock，又称写锁，简称为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锁（对只读数据）

不丢失修改（写）

不读脏数据（读）

可重复读（读）

一级

事务全程加锁

不加

√

二级

事务全程加锁

事务开始加，读完放

√

√

三级

事务全程加锁

事务全程加锁

√

√

√

预防死锁

两种方法：

一次封锁法、顺序封锁法

数据库的恢复

数据库运行故障：

事务故障（可以利用日志文件撤消此事务对数据库已进行的修改）

系统故障

介质故障（重装数据库，然后利用备份或镜像设备恢复数据库。

）

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\人事信息日志.ldf',

SIZE=3,

MAXSIZE=12,

FILEGROWTH=2）

使用Transact-SQL语句删除数据库

语法格式为：

DROPDATABASE数据库名[,…n]

例：

用SQL语句删除Test1和Test2数据库：

DROPDATABASETest1,Test2

使用Transact-SQL语句修改数据库（ALTERDATABASE）

创建触发器（CREATETRIGGER）

使用SQL语句管理权限

（1）授权语句GRANT

例1：

为用户user1授予Student表的查询权。

GRANTSELECTONStudentTOuser1

例2：

为用户user1授予SC表的查询权和插入权。

GRANTSELECT，INSERTONSCTOuser1

例3：

授予user1和user2具有创建数据库表和视图的权限。

GRANTCREATETABLE,CREATEVIEWTOuser1,user2

（2）收回权限语句REVOKE

例1：

收回用户user1授予Student表的查询权。

REVOKESELECTONStudentFROMuser1

例2：

收回授予user1创建数据库表的权限。

REVOKECREATETABLEFROMuser1

（3）拒绝权限语句DENY

例1：

拒绝用户user1对Student表的修改权。

DENYUPDATEONStudentTOuser1

例2：

拒绝user1创建视图的权限。

DENYCREATEVIEWTOuser1

使用Transact-SQL的备份语句进行备份

（1）备份数据库的语句格式为：

BACKUPDATABASE数据库名TO{<备份设备名>}|{DISK|TAPE}={‘物理备份文件名’}[WITH[DIFFERENTIAL][[,]{INIT|NOINIT}]]

（2）备份数据库日志的语句格式为：

BACKUPLOG数据库名TO{<备份设备名>}|{DISK|TAPE}={‘物理备份文件名’}[WITH[{INIT|NOINIT}][{[,]NO_LOG|TRUNCATE_ONLY|NO_TRUNCA