- 数据库基础知识
- 数据库: 长期存储、有组织、可共享的数据集合
按一定的数据模型组织、描述和存储
可共享、冗余度小、数据独立性高、易扩展
- 数据库系统 = 数据库 + 数据库管理系统 + 应用系统 + 用户(User、Admin)
- 数据库模式
外模式:局部数据;一对多;保障安全性、独立性(关系的)
外模式/模式映像->逻辑独立性:APP与数据库的逻辑结构独立
模式:唯一;全体数据逻辑结构&特征描述(关系的)
模式/内模式映像->物理独立性:APP与磁盘上的数据独立
内模式:物理结构&存储方式;唯一;(无关系的)
人工管理 | APP管理;数据不保存,面向某个APP |
文件管理 | 文件系统管理;数据长期保存,面向某个APP |
数据库系统 | DBMS管理;面向世界 |
- 数据库发展阶段
人工管理 | APP管理;数据不保存,面向某个APP |
文件管理 | 文件系统管理;数据长期保存,面向某个APP |
数据库系统 | DBMS管理;面向世界 |
- 数据库设计
需求分析->概念结构设计->逻辑结构设计->物理结构设计->实施->运维
概念模型 数据模型 选取物理结构
- 关系数据库
- 关系模型: 数据结构->关系; 逻辑结构->二维表; 建立在关系代数上 关系数据库的基础
- 关系代数 关系:笛卡尔积的子集 以集合运算为基础
关系操作:集合操作 关系代数语言(非过程化、可嵌入)、关系演算语言
关系表:基本表;查找表;视图表
- 完整性约束
实体完整性约束 | 主码唯一且非NULL 主属性非NULL 主码=∑主属性 复合主键:多列组合(不能多余) |
参照完整性约束 | 外键:被引用关系的主码 外码:NULL或被参照表的主码 |
用户定义完整性约束 |
- 关系代数
并、交、差 | 传统集合运算(笛卡尔积也算) |
投影π、选择 | 投影(去重);选择不去重 |
笛卡尔积X | 等值连接(不去重);自然连接(去重) |
- SQL语言
- 表级操作
主键约束 | Create Table Stu( Sid char(8) primary key )单个定义 Create Table Stu( Cno int, Sno char(5), grade int, primary key(Cno,Sno))复合主键定义 |
外键约束 | Foreigner key (TID) reference ClassT (TID) |
其他约束 | unique唯一、not null非空、check取值约束 |
修改表 | Alter table 表名 动作 Alter table Student Add Ctime DateTime;--添加 Alter table Student Add primary key(Sid);--主键添加 Alter table Student Drop column Ctime;--删除列 Alter table Student alter column Sname Varchar(16);--修改类型 |
删除表 | Drop 表名(删除表(含描述、索引、数据),不会删除视图、但无法引用) |
- 数据操纵语言
单表查询 | 起列别名 select sname, sage as birthday from student --将sage输出为birthday 自动去重 select distinct Sno,Cno from Student 确定集合 select Sname from Student where Sdept in (‘IS’,’MA’,’CS’) 模糊查询 select Sname from Student where Sname Like ‘_阳%’--下划线为空配符、使用\可将其无视 排序 select * from Sutdent order by Sdept DESC--降序 ASC--升序(缺省) |
集函数 | 统计个数 COUNT(列名) --统计时不会统计NULL 求和 SUM(列名) --Count(*)统计行数 求平均 AVG(列名) --列名前+distinct可以去除重复行 求最大值 MAX(列名) 求最小值 MIN(列名) |
连接查询 | 等值连接 select Student.*, Sc.* from Student, SC where Student.Sno = Sc.Sno 自身连接 select a1.CCname, a2.Cname from Course a1, Course a2 where a1.cpno=a2.cno 左连接 select Sutdent.* ,Sc.* from Student Left Join Sc On Student.Sno = Sc.Sno |
插入 | Insert into Student value (‘95020’,’陈东’,’M’,18,’IS’);--默认插入(缺省) Insert into Student(Sno,Cno) value (‘95020’,1)—指定插入 |
修改 | Update Student Set Sage=22 where Sno = ‘95001’—单值修改 Update Student Set Sage = Sage+1 where Sdept = ‘IS’ 多值修改 |
删除 | Delete From Sc where |
组合查询/更新
Update 薪酬 set 工资=工资*1.05 where 工人.项目编号=项目.项目编号 And 项目.项目预算>=10000
Select EName,Pname,Salary from EMP,Pro,SAL where EMP.ENO=SAL.ENO And Pro.Pno=SAL.Pno And ESEX=’男’
- 视图(外模式)
创建 | Create View V_StuIS As Select Sno,Sname,Sage From Student where Sdept = ‘IS’ 后面添加with check option,透过该视图更新数据的时候只操作where条件中的数据 可以基于多表、视图,仅需对重名的列标注即可 |
删除 | Drop View 如删除基表则需显式删除视图 删除视图是删除视图定义,视图依旧存在但是无法引用 |
查询&更新 | 视图实体化法:执行视图定义,将视图临时实体化,生成临时表 将查询视图转换为查询临时表 查询完毕后删除被实体化的临时表 视图消解法:将视图定义中的子查询和用户查询结合起来,转换为等价的基本表查询 |
视图的优势:简化操作;多角度看数据;保障逻辑独立性;提供安全保护
- 索引(内模式)
创建(唯一索引) | Create Unique Index idx_stusno On Student(Sno ASC, Cno DESC)--学号升序、课程号降序 |
删除 | Drop Index idx_Stuname; |
索引的概念 | 以某列/某几列建立的对应关系表(基表与物理存储之间) |
分类 | 主键索引、唯一索引、普通索引 |
创建索引
Create view Stu_View (学号,姓名) As Select 学生表.学号, 姓名 From 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 课程表.课程号=成绩表.课程号
- 数据库设计
- 数据库设计的步骤
需求分析->概念设计->逻辑设计->物理设计->实施->运维
- E-R模型
E-R模型 = 实体(矩形) + 属性(椭圆) + 联系(菱形)
设计局部E-R模型->合成全局E-R模型->优化模型(合并实体类;消除冗余属性、联系)
- R模型转换为关系模型: 转换实体;转换联系
设计步骤 导出初始的关系模式集
规范化处理
模式评价、模式修正
设计子模式
- 数据依赖
函数依赖:对于X中的每一个值,Y中仅有唯一值与之相对应(类似于函数映射)
分类:
平凡函数依赖 X->Y,Y不属于X
非平凡函数依赖
传递函数依赖 X->Y Y->Z,Y不属于X,Y不决定X
直接函数依赖
部分函数依赖 组合依赖(多个主码)
完全函数依赖
- 范式
1NF | 所有属性不可分 | |
2NF | 非主属性完全依赖于主属性 | |
3NF | 不存在非主属性的传递依赖 | |
BCNF | 属于1NF,对于每个函数依赖X->Y,Y不属于X,X必含有候选码 | 消除插入异常和删除异常 |
提升范式的方法是模式分解。 模式分解后可以通过自然连接恢复则称具有无损连接性。(不一定保持函数依赖) 分解后函数依赖保留称为保持函数依赖。
保持函数依赖->3NF 分解无损连接->4NF
- 数据库管理
- 安全性:防止数据泄露、更改或破坏; 尽可能的杜绝非法访问
用户识别与坚定 | 帐号+密码 / 约定计算过程 | 最外层 |
存取控制 | 确保user只能访问有权限访问的数据 分为定义(分配权限)+检查(控制行为) | 授权粒度:库、表、列、行 |
视图 | 隐藏无用数据 | 提供数据独立性 |
审计 | 记录审计日志 | 预防措施,其他的是强制措施 |
数据加密 | 防止存储、传输失密 |
授权 | Grant select On Table Student To U1--一次授权一个用户 全部权限 All Privileges 全体用户 Public 允许再次授权 With Grant Option Grant Update(Sname), Select On Table Student To U4--授予符复合权限 |
收回授权 | Revoke Update(Sname) On Table Student From U4 会收回全部间接获得的权限 |
- 完整性:数据的正确性和相容性
完整性约束机制 = 功能定义 + 完整性检查(立即执行/延迟执行) + 违约反应
参照完整性的实现: 外码是否接受NULL
如何删除(级联删除、受限删除、置空删除)
修改被参照关系中主码的考虑
- 并发控制
显式定义事务:begin transaction commit rollback
ACID特性: Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持续性
多事务并发:串行执行、交叉并发、同时并发 并发会破坏事务的隔离性、一致性
丢失修改、不可重复读、读“脏”数据
可串行化:并行事务正确的唯一准则
排它锁(X锁) | 允许读写、不允许再加锁 |
共享锁(S锁) | 仅读、可以再加S锁 |
封锁
封锁粒度↑,可封锁的对象↓,并发性↓,系统开销↓
三级封锁协议 | 一级 | 写前加X锁,结束后方可释放(丢失修改)(读不需要加锁) |
二级 | 1级基础上、读前加S锁,读后释放(脏读、丢失修改) | |
三级 | 1级基础上、读前加S锁,事务结束后释放(脏读、丢失修改、不可重复读) | |
两段封锁协议(2LP) | 1LP | 扩张阶段;读写操作前先加锁 |
2LP | 收缩阶段;解除一个锁后,任何事务不再获得锁 |
活锁 | 某个事务永远处在等待状态 | 先来先服务(队列) |
死锁 | 两个事务互相等待 | 预防:一次封锁法、顺序封锁法 |
解决:超时法、等待图法 |
- 故障与恢复:从不正确的状态恢复到正确的状态
种类:硬件、软件(系统、应用)故障、误操作、恶意破坏
事务故障 | 事务不正常终止 | 利用日志文件撤销 |
系统故障 | 丢失缓冲区 | 撤销未完成的事务、重做已完成的事务 |
介质故障 | 丢失外存 | 重装数据库、重做已完成的事务 |
恢复:利用冗余数据
日志文件:先等级日志文件后写数据库
转储: 静态转储/动态转储:动态转储时还需要登记日志文件
海量转储/增量转储:增量转储只转储更新过的数据
数据复制:对等复制、主/从复制、级联复制
数据库镜像:
提高数据库可用性,应对介质故障
DBMS自动将整个数据库/关键数据复制到另一个磁盘上