7 数据库

DBS 数据库系统

冗余度：全局描述数据，数据冗余度小，节省存储空间，避免数据之间的不一致性

独立性：应用程序与数据库的相互独立性，数据存储由DBMS管理，应用程序不需了解

共享性：数据库可共享给不同应用程序

控制功能

安全性：防止数据泄露/破坏

完整性：数据在一定范围内有效，数据之间满足一定关系

并发性：并发进程不会相互干扰

恢复功能：从错误状态恢复到正常状态

User 用户

DBA 数据库管理员

全面管理、控制DBMS

系统分析员

需求分析、规范说明、数据库结构设计

Application Programmer应用程序员

为终端用户设计和编制应用程序

End User终端用户

利用应用程序接口使用数据库

DB数据库

Database File数据文件

存放数据库数据和数据库对象的文件

Primary Database File 主要数据文件，只能有一个，扩展名为.mdf，存储数据库的启动信息和部分或全部数据

Secondary Database File 次要数据文件，扩展名为.ndf，存储主要数据文件没有存储的其他数据

Transation Log File 事务日志文件

扩展名为.ldf，记录数据库更新情况的文件

File Group 文件组

与数据文件一样分为主要文件组和次要文件组。

集成性：将数据及其之间的联系集中按照一定结构形式进行存储

共享性：为多个不同的用户共享

DBMS 数据库管理系统

分布式数据库

数据存储在不同计算机

分类（根据是否全局）

逻辑数据库集合，分为全局数据库、局部数据库

透明

分片透明(最高层次):不关心分片，在全局关系上操作

复制透明:被复制数据更新由系统自动完成

位置透明:不知道数据存放何处，数据转移程序无需改写

逻辑透明(局部透明):提供局部影像，与局部DBMS无关

其他

集中式数据库：数据存储在同一台计算机

ADBS主动数据库：除了完成传统数据库服务，还具有主动服务功能

多媒体数据库：提供多媒体数据的查询、播放、编辑等功能

Web数据库=因特网+数据库

知识数据库：AI人工智能技术与数据库技术的结合，提供输入数据的推断结果

数据仓库：从不同源数据中抽取数据，面向主题，支持管理决策过程

OLAP联机分析处理时决策分析的关键

数据挖掘：从大量不全模糊随即数据中提取概念、规则、规律等

过程：数据集成、数据选择、预处理、结果表达、解析

技术：人工神经网络、决策树、遗传算法、规则归纳、分类、聚类、模式识别

XML数据库、实时数据库、移动数据库、并行数据库等

RDBMS关系数据库系统

数据模型

概念模型

对现实世界事物及联系的第一级抽象

实体联系模型E-R包括：实体、属性、联系

基本模型

属于计算机世界中的模型

关系模型：最常用的数据模型。

实体属性：

学生（学号，姓名，年龄，性别）

课程（课程号，课程名，学分）

选修（学号，课程号，成绩）

实体联系：学生选修课程的n：m“选修”联系，“选修”联系有一个成绩属性

层次模型：采用树型结构表示数据与数据间的联系

数据结构

数据类型(型)

整数数据类型

bigint：占用8字节

int：占用4字节

smallint：占用2字节

tinyint：占用1字节

精确数值类型：定义可带小数部分的数字

numeric

decimal（建议使用）：123.0,8000.56

近似浮点数值数据类型：数值的位数太多时使用，取近似值

float：占用8字节

real：占用4字节

日期时间数据类型

datetime：占用8字节，格式为1753/1/1~9999/12/31

smalldatetime：占用4字节，格式为1900/1/1~2079/6/6

字符串数据类型

char

varchar

text：最大存储2G字节

Unicode字符串数据类型：类似字符串数据类型，但Unicode的一个字符用2字节存储

nchar

nvarchar

ntext：最大存储2G字节

二进制数据类型：用十六进制表示

binary

varbinary

image

货币数据类型

money

smallmoney

标记数据类型：识别每一笔数据的唯一性

timestamp（时间标记）：占用8字节

uniqueidentifier（唯一标识码）：占用16字节。全局唯一标志，可用NEWID()函数生成一个该种类型的字段值

关系实例(值)

关系数据库的实例，与数据类型一一对应，例：

关系的键

Candidate Key候选键

（候选关键字、候选码）唯一标识关系中元组的一个属性或属性级，可以有多个

Primary Key主键

（主键、主码、关系键、关键字）从候选键中选择，作为唯一标识

Prime Attribute主属性：主键中的各个属性

Non-Prime Attribute非码属性：不包含在任何候选键中的属性

Foregn Key外键

（外码）R2中，若一（组）属性X，非R2主键，而是R1的主键，则X为R2外键

Referencing Relation参照关系：R2

Referenced Relation被参照关系：R1

关系完整性

实体完整性

主键不能为空或部分为空，保证每一行都能被由称为主键的属性来标识

参照完整性

从表外键是否为空，主表主键是否级联删除/修改

注意：外键可以删除，但是如果外键不为空，那么被参照关系R1表中对应的记录不能删除

域完整性

保证在效范围内的值才能存储到相应列中

用户自定义完整性

由用户指定的一组规则

关系代数

关系术语

R关系模式

例如：学生(Sno,Sname,Department,Sex)

属性/字段/数据项

例如：Sex

n目/度/元数

即属性个数

例如： 4

Di域/集合

Di(i=1,2,3,…,n)，即属性取值范围，例如：{男,女}

(d1,d2,…,di,…,dn)元组/记录/元素

(d1,d2,…,di,…,dn)，例如：(1001,林一,软件,男)

di分量

di，即Di域的一个取值，例如：男

mi基数

mi(i=1,2,3,…,n)，例如：5

D1xD2笛卡尔积

D1xD2xD3={(0,a,c),(0,a,d),(0,b,c),(0,b,d),(1,a,c),(1,a,d),(1,b,c),(1,b,d)}

基本运算

并

关系R与S的元数相同，由属于R/S的元组构成的集合，记作：

R∪S={t|t∈R∨t∈S}

差

关系R与S的元数相同，由属于R且不属于S的元组构成的集合，记作：

R-S={t|t∈R∧t∉S}

广义笛卡儿积

关系R(元数n)与S(元数m)

投影

选择

扩展运算

交

连接

除

广义投影

外连接

关系规范化

逻辑设计阶段，要注意以下两个方面：

对属性取值范围的限定

数据依赖：属性之间的相互联系(主要体现在值是否相等)

包括函数依赖、多值依赖、连接依赖

函数依赖

属性之间有三种关系，但并不是每一种关系都存在函数依赖

如果X和Y之间是1：1关系（一对一关系），如：

学校和校长之间就是1:1关系，则存在函数依赖X → Y和Y →X。

如果X和Y之间是1：n关系（一对多关系），如：

年龄和姓名之间就是1:n关系，则存在函数依赖Y → X。

如果X和Y之间是m：n关系（多对多关系），如：

学生和课程之间就是m:n关系，则X和Y之间不存在函数依赖。

函数依赖

假设给定一个属性的值，能唯一确定另一个属性的值，称为函数依赖

例：知道学号，查询系别，则系别依赖学号（非指一个，而是泛指任何记录）

对于R(U)，若r1[x]=r2[x],则r1[y]=r2[y]，称X->Y，或者Y依赖X

平凡的函数依赖(推内)

若 X → Y，但 Y 为 X 的子集, 则称 X → Y 是平凡的函数依赖

例：(Sno, Cno) → Sno ，(Sno, Cno) → Cno

非平凡的函数依赖(推外)

如果 X → Y，但 Y 不为 X 的子集，称 X → Y 是非平凡的函数依赖

例： SC(Sno, Cno, Grade)， (Sno, Cno) → Grade

F完全函数依赖(合推)

若X → Y并且，对于X的任何真子集X 1，不存在X 1 →Y, 则Y完全依赖于X

例：成绩表（学号，课程号，成绩）

（学号，课程号）→ 成绩，学号 －\→ 成绩，课程号 －\→ 成绩

所以（学号，课程号）→ 成绩，是完全函数依赖

P部分函数依赖(部推)

若X → Y并且，存在X的真子集X 1，使得X 1 →Y, 则Y部分依赖于X

例：Student(Sno, Sname, Sex, Class, Age)

(Sno,Sname)->Sex，Sno->Sex

所以(Sno,Sname)->Sex是部分函数依赖

T传递函数依赖(跳推)

若x → y并且y → z，而y －\→ x，则有x → z

例：关系S1（学号，系名，系主任），

学号 → 系名，系名 → 系主任，并且系名 －\→ 学号

所以学号 → 系主任为传递函数依赖

码、主属性和非主属性、外码、函数依赖的公理系统

六范式

数据库设计所满足的规范，规范化程度越高的是子集

5NF⊆4NF⊆BCNF⊆3NF⊆2NF⊆1NF

1NF原子性(最低)

关系模式的最低要求：R的所有属性是原子数据，则R∈1NF

所有域是原子数据，例如：整数、字符串

不能是非原子数据，例如：集合、数组、记录

2NF依赖主键

R的非主属性完全函数依赖于R的候选键，则R∈2NF

例如将1NF学生(学号，所在系，系主任姓名，课程名，成绩)分解

学生-系(学号，所在系，系主任姓名)

选课(学号，课程名，成绩)

分解后的关系均满足非主属性完全依赖主属性(也能依赖非主)

3NF消除非主键传递(基本)

R的每个非主属性都不传递函数依赖于任何候选键，则R∈3NF

例如将2NF学生-系(学号，所在系，系主任姓名)分解

学生(学号，所在系)

教学系(所在系，系主任姓名)

分解后的模式满足非主属性不传递函数依赖(只依赖候选键)

BCNF消除主键传递

在3NF的基础上，消除主属性间的依赖

若3NF只有一个主键，则自然满足BCNF

4NF一一对应

在3NF基础上，主键与每个多值属性独立组成一个关系模式

把同一个表内的多对多关系删除

5NF

从最终结构重新建立原始结构

模式分解及分解应具有的特性

分解

分解后的模式与原来的模式等价，有3种情况：

• 分解具有无损连接性
• 分解要保持函数依赖
• 分解既要无损连接性，又要保持函数依赖

无损连接

分解R关系后，子关系r1与r2做自然连接，得到结果r1*r2=R，即无损连接

判断：若R1∩R2是R1/R2的主键，则无损分解

保持函数依赖

函数依赖集F的全部函数依赖，在分解后的关系上全部成立

判断：F的函数依赖链，各自都在同一个子模式，则保持函数依赖

SQL结构化查询语言

数据定义DDL

对数据库结构进行描述，包括外模式、模式、内模式的定义；数据完整性定义；安全保密定义，如口令、级别、权限等。

涉及命令：create、drop、alter

三级模式结构

外模式/用户模式：用户用到部分数据的描述

对应视图，是从基本表导出的虚拟表。

数据库存放视图定义，不重复存数据，数据都来自基本表

模式/概念模式：全部逻辑结构和特征的描述

对应基本表，一个关系对应一个基本表

内模式/存储模式：数据物理结构和存储方式的描述

对应存储文件，一个或多个基本表对应一个存储文件

两级映像

外模式-模式映像：实现外模式、模式的相互转换

模式-内模式映像：实现模式、内模式之间的相互转换

定义数据库

创建数据库 create

CREATE DATABASE TestDB --创建数据库TestDB

ON --允许显式定义用于存储数据的文件

-- 定义数据库主文件

(NAME = 'TestDB_Data', -- 主文件逻辑名称

FILENAME = 'D:\DATA\TransactTestDB.mdf', -- 操作系统文件名称，包含路径

SIZE = 20 MB, -- 主文件初始大小

FILEGROWTH = 0) -- 未启用自动增长

-- 定义数据库日志文件

LOG ON

(NAME = 'TestDB_Log', -- 日志文件逻辑名称

FILENAME = 'D:\DATA\TestDB_Log.ldf', -- 包含路径的操作系统文件名称

SIZE = 5 MB, -- 日志文件初始大小

FILEGROWTH = 0) – 未启用自动增长

修改数据库 alter

ALTER DATABASE Northwind --修改数据库Northwind

MODIFY FILE --修改指定文件的文件名、容量大小等，一次只能修改一个文件的属性

(NAME=Northwind, FILEGROWTH=2mb)

删除数据库drop

DROP DATABASE Teach --删除Teach数据库

定义表

创建数据表create table 表名(列名 数据类型[列级完整性约束] [,表级完整性约束] );

CREATE TABLE S(

SNO CHAR(4) NOT NULL,

SNAME VARCHAR(8) NOT NULL,

AGE SMALLINT,

SEX CHAR(2)

);

修改数据表alter table表名 ADD/DROP/MODIFY列名 数据类型 [列级完整性约束];

--在S表中增加一个班号和住址列

ALTER TABLE S ADD Class_No CHAR(6), Address CHAR(40);

删除数据表drop

DROP TABLE S;

定义索引

创建索引create UNIQUE [CLUSTER] INDEX 索引名ON 表名(列名[次序][，列名 次序]);

CREATE UNIQUE INDEX S-SNO ON S(Sno);

删除索引DROP INDEX 索引名

DROP INDEX StudentIndex

定义视图

创建索引create view 视图名(列表名) as select 查询子句[with check option];

create view CS-STUDENT as select Sno,Sname,Sage,Sex from Student where SD=’CS’ with check option;

删除索引drop view 视图名

drop view CS-STUDENT;

定义约束

实现数据完整性的方式

PRIMARY KEY主键约束：确保字段值不为NULL

FOREIGN KEY外键约束：确保字段值必须来自于指定表

UNIQUE唯一约束：某字段不重复，即使为NULL也只有一个

CHECK检查约束：确保字段值的取值范围

Default缺省约束：给相应字段提供默认值

NOT NULL非空约束

次序

ASC升序，默认

DSC降序

Constraint约束

Rule规则

Trigger触发器

Stored Procedure存储过程

数据操纵DML

对数据库数据的基本操作，例如检索、插入、删除、修改

涉及命令：insert、updata、delete

insert命令

insert [into] 表名(列名,列名,…)value(值,值,…)

delete命令

delete from 表名[where 列名=值]

update命令

update表名set列名=值,列名=值…[where 列名=值]

数据查询

涉及命令：select

select命令

select 列名,列名 from 表名[where 列名=值]，例如：

--查询全体同学的学号、姓名和年龄

select SNo,SN,Age from S

--查询学生的全部信息

select*from S

条件查询

比较大小：=,>,<,>=,<=,!=,<>

–查询选修课程号为’C1’的学生的成绩

select SNo,Score from SC where CNo=’C1’

多重条件：AND,OR,NOT

–查询选修C1或C2，且分数大于85分学生的学号、课程号、成绩

select SNo,CNo,Score from SC

where(CNo=’C1’ OR CNo=’C2’)AND(Score>=85)

确定范围：BETWEEN AND

–查询工资不在1000到1500之间的教师的教师号、姓名及职称

select TNo,TN,Prof from T

where Sal NOT BETWEEN 1000 AND 1500–如果是在1000到1500，去掉NOT

确定集合：IN

--查询没有选修C1或C2的学生的学号、课程号和成绩

select SNo,CNo,Score from SC

where CNo NOT IN(‘C1’,’C2’)

--如果是查询选修C1或C2的学生，则去掉NOT

部分匹配查询：LIKE

%代表0个或多个字符

_代表一个字符

[]表示在某一范围的字符

[^]表示不再某一范围的字符

–查询所有姓张的教师的教师号和姓名

select TNo,TN from T where TN LIKE ‘张%’

空值查询：IS NULL

–查询没有考试成绩的学生学号和相应的课程号

select SNo,CNo from SC where Score IS NULL

常用库函数及统计汇总查询

AVG按列计算平均值

SUM按列计算值的总和

–求学号为S1的学生的总分TotalScore和平均分AveScore

select SUM(Score) as TotalScore AVG(Score) as AveScore

from SC where(SNo=’S1’)

MAX求一列中的最大值

MIN求一列中的最小值

--计算选修C1号课程最高分、最低分及之间相差的分数

select MAX(Score) as MaxScore,MIN(Score) as MinScore,MAX(Score)-MIN(Score) as Diff

form SC where(CNo=’C1’)

COUNT按列值统计个数

--求计算机系学生总数

select COUNT(SNo) from S where Dept=’计算机’

--计算学校共有多少个系，DISTINCT消除重复行，计算Dept不同值的数目，对空值不计算，对0计算

select COUNT(DISTINCT Dept) as DeptNum from s

--统计有成绩同学的人数，成绩为0有累加，成绩为空值不累加

select COUNT(Score) from SC

--统计计算机系学生总数，*用来统计元组的个数

select COUNT(*) from S where Dept=’计算机’

分组查询

GROUP BY子句：分组

--查询每个教师的教师号及其任课的门数

select TNo COUNT(*) as C_Num from TC

group by TNo --将TNo相同的分为一组，先执行的

HAVING子句：筛选

--查询选修2门以上（含2门）课程的学生的学号和选课门数

select SNo,COUNT(*) as SC_Num from SC

group by SNo having(COUNT(*)>=2)

查询的排序

--查询选修C1的学生学号和成绩，并按成绩降序排列

select SNo,Score from SC where(CNo=’C1’) order by Score DESC

--查询选修C1、C3、C4课程的学号、课程号、成绩

--查询结果按学号升序排列，学号相同再按成绩降序排列

select SNo,CNo,Score from SC

where(CNo IN(’C1’,’C3’,’C4’)) order by SNo,Score DESC

--查出选课在3门以上且各门课程均及格的学生的学号及其总成绩

--查询结果按总成绩降序排列

select SNo,SUM(Score) as TotalScore from SC

where(Score>=60) having(count(*)>=3) order by SUM(Score) DESC

数据表链接及连接查询（多表）

等值链接与非等值链接

--查询刘伟老师的教师号、教师姓名、所讲授的课程号

select T.TNo,TN,CNo from T,TC where(T.TNo=TC.TNo)and(TN=’刘伟’)

--查询所有选课学生的学号、姓名、选课名称及成绩

select S.SNo,SN,CN,Score from S,C,SC where S.SNo=SC.SNo and SC.CNo=C.CNo

--查询每门课程的课程名、任课教师名及其职务、选课人数

select CN,TN,Prof,COUNT(SC.SNo) from C,T,TC,SC

where T.TNo=TC.TNo and C.CNo=TC.CNo and SC.CNo=C.CNo

group by SC.CNo

自身链接

--查询所有比刘伟工资高的教师姓名、工资，还有刘伟的工资

select X.TN,X.Sal as Sal_a,Y.Sal as Sal_b

from T as X,T as Y

where X.Sal>Y.Sal and Y.TN=’刘伟’

--检索所有学生姓名、年龄和选课名称

select SN,Age,CN from S,C,SC

where S.SNo=SC.SNo and SC.CNo=C.CNo

子查询

where子句中包含（select-from-where）查询块

返回一个值的子查询

--查询与刘伟职称相同的教师号、姓名

select TNo,TN from T

where Prof=(select Prof from T where TN=’刘伟’)

返回一组值的子查询

使用ANY

--查询讲授课程号为C5的教师姓名

select TN from T

where(TNo=ANY(select TNo from TC where CNo=’C5’))

使用IN

select TN from T where(TNo IN(select TNo from TC where CNo=’C5’))

使用ALL

select TN,Sal from T

where(Sal>ALL(select Sal from T where Dept=’计算机’)) and(Dept<>’计算机’)

合并查询

存储查询结果到表中

数据控制

涉及命令：grant、revork

安全保密

grant 、revoke