岛屿尽

dyingjin

数据库SQL、数据库设计与管理 3、4、5

发表于 更新于 分类于

SQL

结构化查询语言是一种对关系数据库进行访问的数据操作语言

特点

一体化、使用方式灵活、非过程化、语言语法简单

类型

DDL数据定义语言

  • CREATE DATABASE/TABLE/INDEX
  • DROP DATABASE/TABLE/INDEX
  • ALTER DATABASE/TABLE

DML数据操控语言

  • INSERT
  • UPDATE
  • DELETE
    需要commit

DQL数据查询语言

  • SELECT

DCL数据控制语言

  • GRANT
  • DENY
  • REVOKE 权限收回

TPL事务处理语言

  • BEGIN/START TRANSACTION 开始事务
  • COMMIT 提交事务
  • ROLLBACK 回滚事务

CCL游标控制语言

  • DECLEAR CURSOR 定义游标
  • FETCH INTO 提交游标数据
  • CLOSE CURSOR 关闭游标

视图

SELECT查询结果集的虚拟表，使用储存在基础表的数据

作用

  • 简化复杂SQL查询操作
  • 提高数据访问安全性
  • 提供一定程序的数据逻辑独立性
  • 集中展示用户感兴趣的特定信息

索引

一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构
以支持快速查找

优点

  • 提高关系表数据检索速度
  • 可快速连接关联表
  • 减少分组和排序的时间

开销

  • 创建和维护索引需要开销
  • 占据额外存储空间
  • 数据操纵会因为维护索引而带来开销

数据库设计与实现

数据库设计概述

数据库设计方案

  • 概念数据模型 - 现实世界的抽象，由数据库设计人员和用户共同完成
  • 逻辑数据模型 - 由概念模型抽象，由数据库设计人员完成
  • 物理数据模型 - 由逻辑模型抽象，并可转化为数据定义语句

(标识符是逻辑概念，主键是物理概念)

数据库设计过程与策略

过程
enter description here
策略：
自底向上、自顶向下、由内至外、混合

E-R模型

即实体-关系模型，是设计系统概念模型、逻辑数据模型的有效方法

基本元素

实体、属性、联系(关系)

联系的类型

多重性

联系数量称为联系度数
1:1
1:N
M:N

参与性

即可选/强制
则有如下符号
enter description here

继承性

分为非互斥继承联系、互斥继承联系、完整继承联系、非完整继承联系
enter description here

依赖性

标识符依赖弱实体
非标识符依赖弱实体

数据库建模设计

概念模型设计

  • 抽取与标识实体
  • 分析与标识实体联系：连接与联系符号选择、命名
  • 定义实体属性与标识符：选择标识符
  • 检查与完善

逻辑模型设计

  • CDM/LDM转换 - 在业务实体的基础上，增加数据实体；多对多实体联系通过关联实体转换为一对多实体联系；区分主键标识符、外键标识符
  • 规范化与完善 - 满足3NF范式

物理模型设计

  • 实体到关系表的转换：实体属性对应表的列、实体标识符转换为对应表主键(或设置为代理键)
  • 实体联系的转换：关系表之间的主，外键设置
    （弱实体到关系表的转换：若弱实体依赖于一个强实体时，加入强实体标识符作为外键值）
    在逻辑模型转换成物理模型时， 需要先确定将使用的DBMS。
    enter description here

数据库规范化设计

目标

  • 减少数据库中冗余数据，降低维护一致性的工作量
  • 合理设计关系表之间的依赖关系和约束关系，便于实现一致性
  • 合理设计数据库结构，便于对数据高效访问

非规范化的问题

插入，删除，修改出现异常

函数依赖理论

X->Y，Y依赖于X·
X为决定因子，Y为函数依赖

完全/部分函数依赖

定义：设X、Y是某关系的不同属性集，如X->Y，且不存在X的真子集A使A->Y，则为完全函数依赖，否则为部分函数依赖
enter description here

函数传递依赖

定义：有X->Y,Y-/->X,Y->Z则称Z对X存在函数传递依赖
enter description here

多值函数依赖

定义：对于 R(U,X,Y)中存在(x,y1,z1)和(x,y2,z2)时，也存在(x,y1,z2)和(x,y2,z1)
enter description here

关系规范化范式

关系规范化程度利弊：

  • 利：冗余数据减少；可消除数据访问异常增加
  • 弊：分解出来的关系表增加；实现数据查询访问时需要关联多表效率低
    逆规范化处理
    enter description here

第一范式

如果关系表中的属性不可再细分，则满足第一范式

第二范式

如果关系满足第一范式，并且消除了关系中属性部分函数依赖，则满足第二范式
即要求属性完全函数依赖

第三范式

如果关系满足第二范式，并且切断了关系中的属性传递函数依赖，则满足第三范式

巴斯科德范式BCNF

在关系中所有函数依赖的决定因子都是候选键
enter description here

第四范式

如果关系满足BCNF，并消除了多值函数依赖，该关系满足第四范式
enter description here

第五范式

如果关系满足第四范式，并消除了连接依赖，则满足第五范式

数据库管理

概述

数据库管理是指为保证数据库系统的正常运行和服务质量必须进行的系统管理工作。

目标

为什么需要数据库管理？
因为数据库规模复杂，访问权限，安全隐私，损坏丢失
所以数据库管理目标有：

  • 保障数据库系统正常稳定运行
  • 充分发挥数据库系统的软硬件处理能力
  • 确保数据库系统安全和用户数据隐私性
  • 有效管理数据库用户及其角色权限
  • 解决数据库系统性能优化、系统故障与数据损坏等问题
  • 最大程度地发挥数据库对其所属机构的作用

数据库管理内容

① DBMS系统运行管理
② 数据库性能监控
③ 数据库索引管理
④ 数据库查询优化
⑤ 数据库事务并发控制
⑥ 数据库角色管理
⑦ 数据库用户管理
⑧ 数据库对象权限管理
⑨ 数据安全管理
⑩ 数据库备份
⑪ 数据库恢复

DBMS管理功能结构

enter description here

DBMS结构

enter description here

事务管理

事务的概念

是一个数据库操作序列(一连串操作的集合)，是数据库应用程序的基本逻辑单元
这些操作要么都做，要么都不做，是一个不可分割的执行单位。
事务是DBMS执行的最小任务单元，是最小故障恢复单元，是最小并发控制单元
每个事务都具有ACID特性。

事务的生命周期状态

enter description here

事务的特性ACID

原子、一致、隔离、持久

  • 原子性（Atomicity）：事物要不做完要不回退，如原子一样不可分割，不能半途而废
  • 一致性（Consistency）：例如银行转账，收发方金额总数一致
  • 隔离性（Isolation）：多事务并发执行，不能相互干扰、双回滚
  • 持续性（Durability）： 一旦提交，结果不变

如果在DBMS中各个事务按串行执行很容易满足ACID但是考虑：改善系统的资源利用率，减少事务执行的平均等待时间还是要支持并发

事务的SQL语句

1
2
3
BEGIN;
SQL...;
COMMIT;(ROLLBACK;)

还有SAVEPOINT用于设立事务保存点

并发控制

并发控制问题

  • 脏读 - 指多个事务同时发生时，一个事务读取了被另一个事务所修改后的共享数据
  • 不可重复读 - 指一个事务对同一个共享数据先后读取数据不一致
  • 幻像读 - 指一个事务对同一共享数据读取两次但第二次比第一次新增数据
  • 丢失更新 - 指一个事务对共享数据进行更新后查询发现该数据与更新值不一致

并发事务调度

  • 由于并发控制调度器将各个事务的SQL数据操作请求按照一定顺序进行调度执行
  • 对共享数据的任意顺序访问操作是导致数据库可能产生数据异常的根本原因
  • 所以只有当事务调度顺序的执行结构与事务串行执行的数据结果一样时，才得以保证一致性，即并发事务调度目标

数据库锁机制

数据库锁机制

  • 排他锁，不允许加任何锁，禁止对数据的修改删除及读取
  • 共享锁，只允许加共享锁，即允许读取，禁止修改删除

锁定粒度越大，DBMS管理就越容易，但并发能力就越差；粒度越小，管理越复杂，并发能力越强。

并发控制协议

  • 一级加锁协议 - 任何事务在修改共享数据对象前，必须对该数据单元排他锁定，直到事务完成。以避免丢失更新
  • 二级加锁协议 - 在一级加锁协议的基础上，针对事务读取共享数据时，必须进行共享锁定，只需直到读取完成。以避免丢失更新、脏读
  • 三级加锁协议 - 在一级加锁协议的基础上，针对事务读取共享数据时，必须进行共享锁定，直到事务完成。以避免丢失更新、脏读、不可重复读取
两阶段锁定协议

分为增长阶段和缩减阶段
增长阶段：事务只能加锁，不能释放锁
缩减阶段：事务只能释放锁，不能加锁
以保证并发事务调度可串行化

并发事务死锁解决

死锁的必要条件
  • 互斥条件
  • 请求和保持条件
  • 不剥夺条件
  • 环路等待条件
防范和解决

防范死锁问题的两大策略

  • 一次锁定所有资源
  • 所以事务锁定资源的顺序必须相同

解决方法即选择一个处理代价最小的事务进行回滚撤销

事务隔离级别

隔离级别 脏读 不可重复读 丢失更新
读取未提交 可能 可能 可能
读取已提交 不可能 可能 可能
可重复读 不可能 不可能 可能
可串行化不可能 不可能 不可能 不可能

安全管理

安全模型

多层安全模型
|层面|安全控制与管理|
|-|-|
|身份认证系统|用户身份鉴别|
|DBMS|用户存取权限控制|
|OS|操作系统安全保护|
|数据库|数据加密储存|

用户管理

用户创建

1
2
3
4
5
6
7
8
9
10
CREATE USER name WITH
	SUPERUSER
	CREATEDB
	CREATEROLE
	INHERIT#继承角色权限
	LOGIN
	REPLICATION#复制权限
	BYPASSRLS#绕过安全策略权限
	CONNECTION LIMIT -1#用户最大连接数
	[ ENCRYPTED | UNENCRYPTED ] PASSWORD 123456;

用户修改

1
2
3
4
ALTER USER name WITH 
ALTER USER name RENAME TO
ALTER USER name SET
ALTER USER name RESET

用户删除

1
DROP USER name

权限管理

1
2
3
GRANT 权限 ON 对象 TO 名字
REVOKE 权限 ON 对象 FROM 名字
DENY 权限 ON 对象 TO 名字

角色管理

将一组具有相同权限的用户定义为角色ROLE

1
CREATE USER name IN ROLE name

备份与恢复

数据库备份

  • 备份内容：用户数据库、系统数据库
  • 备份方式：完整数据库备份、差异数据库备份、事务日志备份、文件备份；还分为冷备份、热备份
  • 备份设备：磁盘阵列、磁带库、光盘库

PgSQL备份方法

一般的

1
2
pg_dump -f 输出文件名 数据库名称
psql -d 数据库名称 -f 导入文件名
---------------THEEND---------------