数据库 - 基础3

部分来源(范式)
https://www.cnblogs.com/jiang-jt/p/7717246.html

上一节链接：https://blog.csdn.net/qq_40893824/article/details/106441068

1 视图

视图是什么？
https://www.zhihu.com/question/34740311

用户对数据库中的一张或者多张表的某些字段的组合感兴趣，
又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。
视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列。

视图是虚拟表，不存放数据
用sql使用视图 返回的数据，是从其他表生成的
视图 和 表 在同一命名空间
视图不能创建 触发器，

它的作用：提升性能

视图 增、删、查、改

1.1 创建

1.1.1 普通

格式

create view 视图名 as 子查询

其中视图名后可跟列名，但列名要么全不写，要么全部写
子查询不能用 order by
子查询可以是从其他视图查询

创建信息系学生学号、姓名、年龄，视图名是 is_stu

create view is_stu as
	select Sno,Sname,Sage from Student where Sdept='is';

建立视图 is_s1，显示学号、姓名、分数

create view is_s1(Sno,Sname,Grade) as
	select Student.Sno,Sname,Sage from Student,SC 
    where Sdept='is' and Cno='1' and Student.Sno=SC.Sno;

1.1.2 从子查询是其他视图

学号、姓名、成绩，且成绩 ≥ 90

create view is_s2 as
	select Sno,Sname,Grade from is_s1
    where Grade>=90;

1.1.3 改列名

学号、姓名、出生年份

create view birth(Sno,Sname,birth) as
	select Sno,Sname,2020-Sage from Student;

1.1.4 分组视图

create view grade(Sno,avg) as
	select Sno,avg(Grade) from SC group by Sno;

1.2 删除视图

drop view 视图名;

例子

drop view is_s2;

1.3 查询视图

把视图当表一样用 select 使用即可

select Sno,Sage from is_stu where Sage<20;

1.4 更新视图数据

增、删、改

insert

insert into is_stu 
value('113','张三',20);

delete

delete from is_stu where Sno='asd';

update

把视图当表一样用 update 使用即可
格式

update 视图名
set 要修改的目标 
where 选择条件;

例子

update is_stu 
set Sname='刘晨' 
where Sno='201212';

更新限制

不可更新
视图是从多基本表连接操作导出
视图使用了分组和聚合

可更新
视图是从单个基本表使用选择和投影操作导出

1.5 作用

1 简化用户操作
2 提供安全保护
3 查询更清晰

2 触发器 trigger 增、删、查、改

满足条件就执行
针对每一行

少使用触发器
因为消耗资源，降低总的执行速度=增加执行时间（针对每一行，批量操作会有几万行，执行时间大大增加）

增

格式

delimiter 定义结束符号
create trigger 触发器名 执行时间 触发事件 on 表名 for each row
begin
	sql更新语句，可以有多个
end
结束符
delimiter ;/* ;前有空格！ */

执行时间 after、before
触发事件 insert、update、delete，mysql 仅支持单触发事件

单 更新

delimiter ##
create trigger t1 after insert on Course for each row
begin
	update Course set Ccredit=5 where Cno in(
    select Cno from Course where Ccredit>5);
end
##
delimiter ;

多 更新

delimiter ##
create trigger t1 after insert on Course for each row
begin
	update Course set Ccredit=5 where Cno in(
    select Cno from Course where Ccredit>5);
    update Course set Cpno=1 where Cno in(
    select Cno from Course where Ccredit>5);
end
##
delimiter ;

删

格式

drop trigger 触发器名;

例子

drop trigger t1;

查

show triggers;

改

先删除，后创建

3 数据库设计

应避免问题：
数据冗余、更新异常、插入异常、删除异常

3.1 规范化

函数依赖
码
范式
2NF
3NF
BCNF

3.1.1 函数依赖

函数依赖 FD
平凡函数依赖、非平凡函数依赖
完全函数依赖、部分函数依赖
传递函数依赖

函数依赖 FD

有两个元组 ，两个属性 X、Y
若两元组在 X 上值相同，那么 Y 上的值 也一定相同

X 和 Y 的联系 1:1	X ↔ Y
X 和 Y 的联系 1:n	Y → X
X 和 Y 的联系 m:n	X、Y 不存在 FD

平凡FD、非平凡FD

X → Y，Y ⊈ X，X → Y 是 非平凡FD
X → Y，Y ⊆ X，X → Y 是 平凡FD

SC(Sno, Cno, Grade)

(Sno, Cno) → Grade	非平凡FD
(Sno, Cno) → Sno	平凡FD
(Sno, Cno) → Cno

完全FD ( X$\stackrel{f}{\Rightarrow }$Y )、部分FD ( X$\stackrel{p}{\Rightarrow }$Y )

X$\stackrel{f}{\Rightarrow }$Y	X → Y，且 X 的任一真子集 X’ 都不能决定 Y，则 Y 对 X 是 完全函数依赖（完全FD）
X$\stackrel{p}{\Rightarrow }$Y	X → Y，存在 X 的真子集 X’ 能决定 Y，则 Y 对 X 是 部分函数依赖（不完全FD = 部分FD）

传递函数依赖

X → Y，Y ⊈ X，Y → Z，则 Z 传递依赖于 X
X → Y，Y → X(即 X ↔ Y)，Y → Z，则 Z 直接依赖于 X

3.1.2 码 key

关系模式 R(U, F)，K 是 R 中属性 或 属性组合
K $\stackrel{f}{\Rightarrow }$ U，则 K 是 R 的 1 个候选码
K $\stackrel{p}{\Rightarrow }$ U，则 K 是 R 的 1 个超码
候选码是最小的超码

若有多个候选码，选 1 个为主码
整个属性 的组合 是全码 all-key

任何候选码 中的属性 是主属性
不包含在任何候选码 的属性 是非主属性

3.1.3 范式 NF

要求 严格性 从小到大：
1NF < 2VF < 3NF < BCNF < 4NF < 5NF
第一范式 < 第二范式 < …
越严格，能满足其要求的范围就越小，反之 越大

即 高类聚、低耦合

1NF - 属性不可分割

所有属性不可分割，这是关系数据库最基本的要求
不满足这个要求的 不能称为关系数据库

问题 - 增删存改
学生(学号, 姓名, 系号, 系主任姓名, 课程号, 成绩)
增：
插入异常 ( 系刚成立，没任何学生，那无法把该系信息插入表中)
删：
删除异常 ( 系的学生毕业，删除该系学生信息时，该系信息也被删除)
存：
数据冗余 ( 系学生很多，同一系学生的系主任相同，系主任名会重复出现)
改：
更新复杂 ( 系换系主任后，表必须修改多处相同的系主任名字)

原因
主码的部分依赖

解决
消解部分依赖！
表分成 2 个或多个表，这就是 2NF
学生 ( 学号, 姓名, 系编号)
系 ( 系号, 系名, 系主任)
选课 ( 学号, 课程号, 成绩)

2NF - 非主属性 完全依赖

1NF基础上，每个非主属性 完全依赖关系 R 的码

问题
下面 4 个问题仅仅减轻，没完全消除
数据冗余 (存在相同数据：学号、系、宿舍楼 )
插入异常 (若主属性空就不能插入)
删除异常 (不该删除的也要删除)
修改异常 (修改复杂，因为同数据要修改，就要要改多次)

原因
存在传递依赖

解决
消解传递依赖！
把表分成 2 个或多个表，这就是3NF
SL→ SD + DL
SD(学号, 系)
DL(系, 宿舍楼)

3NF - 非主属性之间 无传递抵赖

在 2NF的基础上，非主属性之间不存在传递依赖

改善：
增：可插入系的信息，即使还没有学生
删：系学生毕业，该系信息仍存在
存：学生系、住处信息
改：系、住处改的更方便

问题
4大问题仍只是减轻，而不是消除
例子
STJ(S, T, J)
STJ(学生, 教师, 课程)
一个教师只教一门课 T → J
(S, J) → T
(S, T) → J
增：某教师开了某门课，但还未有学生，信息无法录入
删：学生毕业，删除学生信息，同时老师的信息也删除了
查 / 存：不同学生，同一门课，同一教师，教师名重复
改：课程改名，修改多处

原因
主属性 J 部分依赖 (S, T)

解决
STJ分成(S, J)、(T, J)
任何属性(包括主属性)无 完全依赖 和 无传递依赖

BCNF - 主属性 完全依赖

任何属性(包括主属性)无 完全依赖 和 无传递依赖

问题减轻
增：
某教师开了某门课，但还未有学生，信息可以录入
删：
学生毕业，删除学生信息，同时老师的信息不会被删除
查 / 存：
不同学生，同一门课，同一教师，教师名不会重复
改：
课程改名，修改一处

问题
供应商(Sno)、零件(Pno)、工程(Eno)
SP(Sno, Pno)、SE(Sno, Eno)

问题
4大问题仍未被消除

4NF - 非平凡FD 不存在多值依赖

即 无多对多关系（默认是非主属性，因为到 4NF，主属性一般无多值依赖）

问题
4大问题仍违背消除

5NF - 无连接依赖

消除连接依赖，且保证数据完整性

缺点

范式越高 = 表的划分更细，表的数量更多，原本关联的数据被分到多个表中
若用户同时需要这些数据，只能用连接表将数据重新合并在一起
同时联接多个表，花费巨大，若表数据非常庞大，表连接操作几乎是噩梦，这会严重降低系统运行性能

3.2 步骤

需求分析

概念结构设计

逻辑结构设计

物理结构设计

数据库实施、运行、维护

3.2.1 需求分析

获取需求：
访谈
问卷调查
跟班作业
建立原型

数据字典：数据收集、数据分析的结果
包含 数据项 和 数据结构
其 内容：数据流 + 数据存储 + 处理过程
数据流：数据结构在系统中的传输路径

3.2.2 概念结构设计 - 自底向上

即 先分后合
E - R 图，消解冲突
属性名称、属性类型、取值范围、取值单位

3.2.3 逻辑结构设计

E - R 图 转为 代码

3.2.4 物理结构设计

存储结构、存取方法 - 索引

3.3 实例

源自
https://max.book118.com/html/2018/0326/158762037.shtm

库存管理

学校行政管理

商店管理

商店 ( 商店编号(主码), 商店名, 地址)
职工 ( 职工编号(主码), 姓名, 性别, 业绩, 商店编号(外码), 聘期, 工资)
商品 ( 商品号(主码), 商品名, 规格, 单价)
销售 ( 商店编号, 商品号, 月销量)，其主码是 ( 商店编号, 商品号)，而分别又是 外码

医院科室管理

4 数据库恢复

4.1 事务 - 4个特性

是操作集合，它们要么全做、要么全不做
4 个特性

特性	解释
原子性	要么全做、要么全不做
一致性	不同表，相同属性 保持一致，同时 修改 或 更新
隔离性	一事物在运行，其他事物不能 干扰 本事物
持续性	事物造成的改变是永久的

事务的隔离性是由DBMS的 并发控制子系统实现的

提交事务

https://www.runoob.com/mysql/mysql-transaction.html

BEGIN; 或 START TRANSACTION; 开启一个事务

insert...
ROLLBACK; 事务回滚

insert...
commit; 提交事务 = 更改永久保存到数据库


SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

4.2 4 个故障

4 个故障	解释
事务故障	运算溢出 或 违反某完整性约束
系统故障	断电、CPU故障、OS故障、代码错误
介质故障	磁盘损坏、磁场干扰
计算机病毒	无

4.3 基本原理 - 冗余

数据库被破坏，用本地别的地方的备份来恢复

方法
数据转储
登记日志文件

数据转储

转储 很耗费 时间、资源，别频繁进行

静态转储	转储期间，数据库不能变化，不可修改、更新
动态转储	转储期间，数据库能变化，可以修改、更新

海量转储	恢复全部数据库
增量转储	恢复上次更新的信息

组合
静态海量、静态增量
动态海量、动态增量

日志文件

日志文件 记录数据库的更新操作
按时间顺序

4.4 恢复策略

不同情况	做法
事务故障恢复	undo - 撤销
系统故障恢复	undo - 故障时 未完成的事物 redo - 已完成的事物
介质故障恢复	重装备份、恢复一致性状态 redo

5 并发控制

存在问题

并发会导致数据的不一致

例子 - 火车票售卖

脏数据 ” 指 未提交的随后又被撤消的数据
先读 后改 - 不可重复读
先读 后删 - 幻影(幻读)
先读 后加 - 幻影(幻读)
后两种叫 幻影

5.1 封锁 - 单数据对象

就是加锁，我在执行时，其他事物不可以来干扰

两种锁	解释
排它锁 X锁	事务 对于 对象，可以读、修改 其他事务对该对象不能加任何锁
共享锁 S锁	事务 对于 对象，仅读 其他事务对该对象 仅能加 S锁
X、S 锁 仅针对单个数据对象

问题 及解决

两个问题	解释	解决
活锁	欺负老实人 最先请求而等待的事物 可能等待的时间最长	先来先服务
死锁	多事务 可能需要多个对象，恰巧我需要的在你那，你要的在我这	撤销代价最小的事务

死锁属于 事务故障
“ 脏数据 ” 指 未提交的随后又被撤消的数据

设有两事务 T1、T2，其并发操作：

则 读了 “ 脏数据 ”，读的数据前后两次不同

5.1.2 两段锁协议

1 对数据读写前，该事务要封锁数据
2 事务释放封锁后，不能再封锁其它任何数据对象

5.2 封锁 - 多数据对象

封锁对象的数量 是封锁粒度

多粒度封锁

多粒度树

显式封锁	锁 加在数据对象上
隐式封锁	锁 加在数据对象的祖先结点

数据对象 加锁时检查
1 数据对象是否已被加锁 ( 显式加锁)
2 其祖先结点有无加锁 ( 隐式加锁)
3 其祖先结点的锁 和本锁是否冲突，SS可以，XS、XX不可以

事务 T 要对关系 R 加 S 锁，系统检查封锁冲突的过程：
1 检查数据库、关系 R 是否加了不相容的锁（X、IX 锁）
2 不再检查 R 元组是否加了不相容的锁（X 锁）

多粒度封锁协议

多粒度树 每个结点可被加锁，同时该结点的子树后裔均被加以相同的锁

意向锁 - 提高 加锁时的 检查效率

对数据对象加基本锁，先对其上层结点 ( 父母 ) 加意向锁
若一个数据对象被加 意向锁，那么其下层结点正被加锁

这样有 3 类锁了！

意向 共享锁 IS 锁	表示 其后裔可以加 S 锁
意向 排它锁 IX 锁	表示 其后裔可以加 X 锁
共享 意向 排它锁 SIX 锁	表示 先对其加 S 锁，后可以对其后裔加 X 锁

封锁方法

申请 = 加锁 时，自上而下
释放 = 解锁 时，自下而上

上一节链接：https://blog.csdn.net/qq_40893824/article/details/106441068