数据库概念

发表于 | 分类于
字数统计: 4,978

概论

DB、DBMS与DBS的概念区别

数据：载荷信息的符号。如数字、文字、图像、音频、视频等

数据库：长期储存在计算机内、有组织的(数据模型、冗余度小、独立性高、易拓展)、可共享的大量数据的集合。

数据库管理系统：用户和OS之间用于管理数据的基础软件，包括数据定义、组织、存取、联系、操纵、事务管理和运行管理(安全性保护、完整性检查、并发控制、数据库恢复)等功能

数据库系统：DB + DBMS + DBA + 应用程序

数据独立性

物理独立性：应用程序与物理存储——外模式/模式映像(模式修改时对应改变映像，基于外模式的应用无需修改)

逻辑独立性：应用程序与逻辑结构——模式/内模式映像(同理)

从底层保证了应用程序的稳定性

数据模型

对现实世界的模拟/抽象，容易为人理解且易在计算机上实现。数据模型是数据库系统的核心和基础

  • 概念模型(现实 - > 信息)：信息模型，数据库设计人员从用户角度进行数据库设计，要有较强的语义表达能力
    • E-R——要会根据描述信息画E-R图
      • 联系本身也是一种实体型，也可以有属性
  • 逻辑模型和物理模型(信息 -> 机器)
    • 逻辑模型：数据库设计人员借助工具从计算机系统的角度建模，用于DBMS实现
    • 物理模型：物理存取方式，由DBMS完成
数据模型的组成要素
  • 数据结构(静态特征)：对象和联系
  • 数据操作(动态特征)：值的操作
  • 数据完整性约束：制约和依存规则
常用的数据模型
  • 层次：树，一对多，记录必须按路径查看完整意义。如IBM的IMS数据库
    • 完整性约束与树结构有关
    • 用冗余节点法(复制)和虚拟结点法(引用)将多 - 多 转成 一 - 多
    • 存储结构：顺序邻接法，子女-兄弟链接法，层次序列链接法(深度优先遍历)
    • 查询效率优于关系，不低于网状—-于此同时，更新效率较低
    • 网状模型的一个特例
  • 网状：间接表示多对多，如BDTG系统
    • 一对实体间可以有多种联系
    • 用联结记录法将多 - 多 转成 一 - 多
    • 链表存储
    • 操作语言复杂
  • 关系
    • 一个关系对应一张表(不允许嵌套)，一个主码确定一个元组，元组中每一个分量必须是不可分的数据项
    • 关系模式即表头，是对表格的描述
    • 存取路径对用户透明，利于开发但查询效率较低——查询请求优化

数据库系统结构

开发人员角度
  • 三级模式(二级映像)
    • 外模式(子模式/用户模式)——View
      • 局部数据或对同一数据的约束不同，跟具体应用有关的逻辑表示
      • 一个应用只有一个外模式
    • 模式(逻辑模式)——数据库的中心，应最先确定
      • 对所有用户共享全部数据的逻辑结构和特征的描述
      • 一个数据库只有一个模式，一个模式可有多个外模式
    • 内模式(存储模式)
      • 数据库内部的表示方法，如存储方式、索引组织、压缩加密等
      • 一个数据库只有一个内模式

模式(Schema)对应抽象的型，实例对应具体的值。

最终用户角度
  • 单用户
  • 主从式
  • 分布式
  • 客户/服务器
  • 浏览器/服务器

数据库系统组成

DB + DBMS + Application + DBA + Hardware(内存、磁盘、传输率) + Software + User/Developer

DBA职责
  • 决定信息内容和结构，存储结构和存取方式，安全性要求和完整性约束条件——设计
  • 监控数据库的使用和运行，如周期转储，故障恢复，监视审计文件——运维
  • 调优、重组织、重构——优化

关系数据库

关系建立在集合代数的基础上，是笛卡尔积的子集

笛卡尔积：所有域的所有取值的不重复组合，可表示为一个二维表，行为元组，列为域

域：当域的范围相同时，可以用属性名来进行区别

码：候选码可以唯一标识一个元组，对应一个主属性；所有属性都是候选码称为全码；可选定一个候选码为主码

三类关系

  • 基本表：实际存在，数据的逻辑表示。最基本的一条性质：分量必须取原子值
  • 查询表：临时表
  • 视图表：虚表/导出表

关系模式是型，关系是值。笼统称为关系，用上下文区别

关系模式形式化表示：R(U,D,DOM,F) DOM是U = {A1,A2,A3...}中的属性向域D的映象集合，如DOM(Student-Person)=Person，映象通常直接说明为属性的类型、长度，F是属性依赖关系的集合

关系操作

集合操作方式。

  • 查询：选择、投影、连接、除、并、差、交、笛卡尔积
  • 更新：插入、删除、修改

SQL具有关系代数(关系运算)和关系演算(用谓词)的双重特点

关系代数(重要)
  • 集合运算符：只能行操作，要求属性的个数和域相同(笛卡尔积除外）
    • $U$ 并
    • $-$ 差 ：由于不能做≠ 判断，所以一般借助差来实现诸如 “没有”之类的条件
    • $∩$ 交
    • $×$ 笛卡尔积：{m目k1个元组的集合} × { n目k2个元素的集合} = k1 × k2 个 m + n 目新元组
  • 专门关系运算符：行列操作 具体运算参考
    • $σ$ 选择：行，在对列的范围进行控制的情况下选出行
    • $π$ 投影：列，投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）
    • $⋈$ 连接：结果来自多个关系
      • 注意区分自然连接(去掉重复属性)和等值连接(对重复属性按表进行区分)
      • 含空值的称为悬浮元组
    • $÷$ 除
      • 象集的概念
      • 先找出某值的象集，再看该象集是否真包含右边的关系
      • 简单理解：假设集合A中有属性a,b ,集合B有属性b, 则找出的是，B中所有的b都在A中有等值b的对应a。前提是有公共属性
      • 常用于“全部”，先用投影找出全部信息作为被除数；“至少”，一般是至少一个的情况

综合操作是先用连接确定要从哪些表中获取信息，然后对属性的域进行控制选择满足条件的行，再用投影选出所需的属性，生成特定的视图表

交、连接、除可用其余5种运算代替，但是引入可以简化表达

关系演算语言(了解，反正也看不懂..)

刻画客体的性质或几个客体间关系的模式叫谓词

  • 元组关系演算语言 ALPHA
  • 域关系演算语言 QBE

元组演算和关系代数对比应用的例子 真的给我整蒙了…

完整性约束

  • 实体完整性：主属性not null且unique，必须满足的不变性

  • 参照完整性：外码是对应其他表(被参照关系)主码的本表(参照关系)非主码属性，取空值或被参照表的主码，foreign key reference,必须满足的不变性

  • 用户定义完整性：具体领域的语义约束，结合实际情况 如 CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')

SQL

命令一览

SQL是关系数据库语言的工业标准。

分为数据定义、数据查询、数据更新、数据控制四大部分

定义

模式

定义模式：CREATE SCHEMA <模式名> AUTHORIZATION <用户名> 实际上定义了一个命名空间，可包含基本表、视图、索引等数据库对象

删除模式：DROP SCHEMA <模式名> <CASCADE|RESTRICT> CASCADE全部删除，RESTRICT还有对象时无法执行

创建对象时如果没有指定模式，默认根据搜索路径SHOW search_path;使用模式列表的第一个模式

基本表

涉及多个属性列的完整性约束条件要定义在后面的表级完整性约束条件，如对外键的约束FOREIGN KEY (外键属性名) REFERENCES 参照表名(参照表的主键) 或有多个主键 PRIMARY KEY (主键1 ，主键2)

视图

特点：

  • 虚表，是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义，不存放视图对应的数据
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变

作用：

  • 视图能够简化用户的操作——基于视图的视图
  • 视图使用户能以多种角度看待同一数据 ——数据库共享
  • 视图对重构数据库提供了一定程度的逻辑独立性——无需改变外模式就实现某些使用需求
  • 视图能够对机密数据提供安全保护
  • 适当的利用视图可以更清晰的表达查询
创建

CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询> [WITH CHECK OPTION];

WITH CHECK OPTION 代表对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除时的行仍然满足视图定义中的子查询中的条件表达式

DBMS实现视图查询的方法：视图消解法，一般转换成等价的对基本表的查询

删除

DROP VIEW <视图名>[CASCADE];

索引

从如何加快查询速度的角度去考虑，很多算法题也会涉及到这些思路。

  • 顺序文件：按序排列，一个索引对应一条记录
  • B+树：动态平衡，一个索引对应一个块，索引记录该块内最大值
  • 散列：查找快，但是仅能用于判断存在性，不能按范围查找。有同位冲突问题
  • 位图

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名)>[<次序>],....); 次序为升序ASC或降序DESC,默认为升序

DMBS自动选择合适索引作为存取路径，用户不能显式地选择索引

适合建立索引的列：经常被使用，如WHERE 或 ORDER BY ; 主键或外键；值唯一

查询

单表

SELECT..COUNT/AVG/SUM/MIN/MAX..FROM...AS...WHERE...GROUP BY....HAVING.../ORDER BY...ASC|DESC;

WHERE 和 HAVING 的区别：WHERE 用于从基表或视图选择满足条件的元组，HAVING 用于从组中选择满足条件的组

常用条件

  • DISTINCT 去掉重复行
  • [NOT] BETWEEN...AND...
  • [NOT] LIKE '匹配字符串'字符匹配 % _ 如果字符串本身有% 或_ , 要在前面加 \ 并在后面加上一句ESCAPE'\'
  • [NOT] IN(value1,value2...)
  • IS [NOT] NULL IS不能用等号代替
  • AND OR NOT
  • 还有一些比较运算符< = > <= >= !=...
连接

连接条件中字段类型必须是可比的，但名字不必相同

等值与非等值

对同名列要根据 表名.列名 的形式进行区分

连接执行方法

  • 嵌套循环法：穷举
  • 排序合并法：先排序再merge
  • 索引连接：只与索引字段比较
自然连接

根据两个表中同名的列而进行连接的，当列不同名时，自然连接将失去意义。且语法中没有on

即在等值连接的基础上把重复列去掉

自身连接

属性自相关

如：查间接先修课(先修课的先修课)SELECT FORST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno = SECOND.Cno; 要对属性起别名进行区分

外连接

不满足条件的也输出 ，如连接的某表中有些属性是空值。

可有左外连接和右外连接

例：SELECT Student.Sno,Sname,Sage,Cno,Grade FROM Student LEFT OUT JOIN SC ON(Student.Sno = SC.Sno);

嵌套
  • 含IN的子查询：SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname = '计算机');
  • 含比较运算符：SELECT Sno,Cno FROM SC X WHERE Grade >= (SELECT AVG(Grade) FROM SC Y WHERE Y.Sno = X.Sno); 当子查询对父查询有依赖关系时，会将外层查询的元组的属性按顺序传给内层去处理
  • 含ANY/SOME/ALL：与比较运算符搭配使用，放在子查询括号之前。有时候可以用聚集函数MAX/MIN代替实现ANY/ALL之类的查询
  • 含[NOT] EXISTS : 上面的三种都可以用某种形式的EXISTS来实现

子查询不能使用ORDER BY

如果嵌套有很多层的话，其实用连接查询更简单

子查询还可以用在FROM子句中，这时生成的临时派生表是主查询对象

集合

参与查询的列数必须相同，对应的数据类型也相同

  • UNION [ALL] 并集，加上ALL保留重复元组
  • INTERSECT 交集
  • EXCEPT 差集

更新

插入

插入元组：INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)] VALUES (<常量1> [,<常量2>]… );

插入子查询：例如

1
2
3
4
5
INSERT
INTO  Dept_age(Sdept,Avg_age)
      SELECT  Sdept，AVG(Sage)
      FROM     Student
      GROUP BY Sdept;
修改

UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>]…[WHERE <条件>];

主码不允许修改

删除

DELETE FROM <表名> [WHERE <条件>];

空值

约束条件：

  • 有NOT NULL约束条件的不能取空值
  • 加了UNIQUE限制的属性不能取空值
  • 码属性不能取空值

空值与另一个值（包括另一个空值）的算术运算的结果为空值；空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。

完整性

  • 正确性：符合实际
  • 相容性：符合逻辑

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE SC
        (  Sno   CHAR(9)  NOT NULL，
           Cno   CHAR(4)  NOT NULL，
           Grade  SMALLINT,
           PRIMARY KEY(Sno,Cno)， 			
           FOREIGN KEY (Sno) REFERENCES Student(Sno) 
			 ON DELETE CASCADE        /*级联删除SC表中相应的元组*/
             ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/
           FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    
             ON DELETE NO ACTION 	
                 /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
             ON UPDATE CASCADE   
      	  /*当更新course表中的cno时，级联更新SC表中相应的元组*/
           );

完整性约束命名子句：CONSTRAINT <完整性约束条件名><完整性约束条件> 对约束命名更利于后续操作，如删除ALTER TABLE Student DROP CONSTRAINT C4;

断言

CREATE ASSERTION<断言名><CHECK 子句>可以定义涉及多个表的或聚集操作的比较复杂的完整性约束

例

1
2
3
4
5
CREATE ASSERTION ASSE_SC_CNUM2
 	 CHECK(60 >= ALL (SELECT count(*)                                   
          FROM SC
		  GROUP by cno,TERM)
				   );

触发器

一种特殊的存储过程，当表中发生特殊事件时执行。触发器主要用于保证数据的完整性。

只能定义在基本表上，不能定义在视图上

1
2
3
4
5
CREATE TRIGGER <触发器名>  
       {BEFORE | AFTER} <触发事件> ON <表名>
       REFERENCING NEW|OLD ROW AS<变量>
       FOR EACH  {ROW | STATEMENT}
       [WHEN <触发条件>]<触发动作体>

安全性

实现方法

  • 用户身份鉴别
  • 存取控制技术
    • 自主存取控制：限制存取权限
    • 强制存取控制：数据本身设置安全性标记 TS>=S>=C>=P
      • 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体
      • 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
  • 视图技术
  • 审计技术
  • 数据加密存储和加密传输

授权

1
2
3
4
GRANT <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
  • 全部权限：ALL PRIVILIGES
  • 全部用户：PUBLIC
  • 允许再授权：WITH GRANT OPTION 不允许循环授权

权限回收

1
2
3
REVOKE <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];

也可以定义一个具有某种权限角色CREATE ROLE <角色名>，直接将角色赋给用户

范式

  • 1NF: 每个分量必须是不可分开的数据项
  • 2NF: 每一个非主属性都完全函数依赖于任何一个候选码(不存在对主键的部分函数依赖)
    • 解决：对部分依赖属性分解成多个表
  • 3NF: 不存在属性对主键的传递依赖
  • BCNF: 不存在主属性对码的部分依赖和传递依赖——即不能由属性反推出主码

如果一个关系数据库中的所有关系模式都属于BCNF，那么在函数依赖范畴内，它已实现了模式的彻底分解，达到了最高的规范化程度，消除了插入异常和删除异常

不能说规范化程度越高的关系模式就越好。

数据库设计

数据库设计分6个阶段

  • 需求分析
  • 概念结构设计
  • 逻辑结构设计
  • 物理结构设计
  • 数据库实施
  • 数据库运行和维护

联系转换方法：

法一：新建一个联系表，要建立联系的表中的属性组成联系表的主键——多用于多对多

法二：主键+联系写到另一个要联系的表中——外键，多用于一对一，一对多

数据库恢复

事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。

事务是恢复和并发控制的基本单位

事务的ACID特性：

  • 原子性（Atomicity）:操作要么都做，要么都不做
  • 一致性（Consistency）:事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
  • 隔离性（Isolation）:一个事务的执行不能被其他事务干扰
  • 持续性（Durability ）:一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

利用存储在后备副本、日志文件和数据库镜像中的冗余数据来重建数据库

恢复策略：

事务故障：反向扫描文件日志，对该事务的更新操作执行逆操作

系统故障：正向扫描日志文件，重做(REDO) 和撤销 (UNDO)

介质故障：重装数据库，重做已完成的事务

并发控制

事务是并发控制的基本单位

主要任务

  • 对并发操作进行正确调度
  • 保证事务的隔离性
  • 保证数据库的一致性：避免发生丢失修改(修改覆盖)、不可重复读(有改删增)、读脏数据(有回滚)

主要技术

  • 封锁
    • 排它锁(X)：写锁，其他事务不能读或改
    • 共享锁(S)：读锁，其实事务可读不可改，读锁上可再加读锁
    • 封锁协议
      • 一级：修改加X, 避免丢失修改
      • 二级：一级 + 读数加S读完就释放 ，避免丢失修改和读脏数据
      • 三级：一级 + 读数加S事务完才释放，进一步避免了不可重复读
    • 避免活锁(类似饥饿)：FCFS
    • 避免死锁：
      • 死锁预防：一次封锁法–效率低；顺序封锁法–难实现
      • 诊断(超时法–可能误判，不够及时；等待图法检测回路)并解除死锁(kill或抢占)
  • 时间戳
  • 乐观控制法
  • 多版本并发控制

冲突可串行化

一个调度Sc在保证冲突操作的次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度Sc’，如果Sc’是串行的，称调度Sc是冲突可串行化的调度。

冲突可串行化调度是可串行化调度的充分条件