部分来源(范式)
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 ⇒ f \xRightarrow{f} fY )、部分FD ( X ⇒ p \xRightarrow{p} pY )
X ⇒ f \xRightarrow{f} fY | X → Y,且 X 的任一真子集 X’ 都不能决定 Y,则 Y 对 X 是 完全函数依赖(完全FD) |
X ⇒ p \xRightarrow{p} pY | 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
⇒
f
\xRightarrow{f}
f U,则 K 是 R 的 1 个候选码
K
⇒
p
\xRightarrow{p}
p 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