数据库原理及应用(十五)知识点精简背诵版

• 数据库基础知识

1. 数据库： 长期存储、有组织、可共享的数据集合

按一定的数据模型组织、描述和存储

可共享、冗余度小、数据独立性高、易扩展

1. 数据库系统 = 数据库 + 数据库管理系统 + 应用系统 + 用户（User、Admin）
2. 数据库模式

外模式：局部数据；一对多；保障安全性、独立性（关系的）

外模式/模式映像->逻辑独立性：APP与数据库的逻辑结构独立

模式：唯一；全体数据逻辑结构&特征描述（关系的）

模式/内模式映像->物理独立性：APP与磁盘上的数据独立

内模式：物理结构&存储方式；唯一；（无关系的）

人工管理	APP管理；数据不保存，面向某个APP
文件管理	文件系统管理；数据长期保存，面向某个APP
数据库系统	DBMS管理；面向世界

1. 数据库发展阶段

人工管理	APP管理；数据不保存，面向某个APP
文件管理	文件系统管理；数据长期保存，面向某个APP
数据库系统	DBMS管理；面向世界

1. 数据库设计

需求分析->概念结构设计->逻辑结构设计->物理结构设计->实施->运维

  概念模型 数据模型 选取物理结构

• 关系数据库

1. 关系模型： 数据结构->关系； 逻辑结构->二维表； 建立在关系代数上 关系数据库的基础
2. 关系代数 关系：笛卡尔积的子集 以集合运算为基础

关系操作：集合操作 关系代数语言（非过程化、可嵌入）、关系演算语言

关系表：基本表；查找表；视图表

1. 完整性约束

实体完整性约束	主码唯一且非NULL 主属性非NULL       主码=∑主属性 复合主键：多列组合（不能多余）
参照完整性约束	外键：被引用关系的主码 外码：NULL或被参照表的主码
用户定义完整性约束

1. 关系代数

并、交、差	传统集合运算（笛卡尔积也算）
投影π、选择	投影（去重）；选择不去重
笛卡尔积X	等值连接（不去重）；自然连接（去重）

• SQL语言

1. 表级操作

主键约束	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 表名（删除表（含描述、索引、数据），不会删除视图、但无法引用）

1. 数据操纵语言

单表查询	起列别名 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=’男’

1. 视图（外模式）

创建	Create View V_StuIS As Select Sno,Sname,Sage From Student where Sdept = ‘IS’ 后面添加with check option，透过该视图更新数据的时候只操作where条件中的数据 可以基于多表、视图，仅需对重名的列标注即可
删除	Drop View   如删除基表则需显式删除视图  删除视图是删除视图定义，视图依旧存在但是无法引用
查询&更新	视图实体化法：执行视图定义，将视图临时实体化，生成临时表 将查询视图转换为查询临时表 查询完毕后删除被实体化的临时表 视图消解法：将视图定义中的子查询和用户查询结合起来，转换为等价的基本表查询

视图的优势：简化操作；多角度看数据；保障逻辑独立性；提供安全保护

1. 索引（内模式）

创建(唯一索引)	Create Unique Index idx_stusno On Student(Sno ASC, Cno DESC)--学号升序、课程号降序
删除	Drop Index idx_Stuname;
索引的概念	以某列/某几列建立的对应关系表（基表与物理存储之间）
分类	主键索引、唯一索引、普通索引

创建索引

Create view Stu_View (学号,姓名) As Select 学生表.学号, 姓名 From 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 课程表.课程号=成绩表.课程号

• 数据库设计

1. 数据库设计的步骤

需求分析->概念设计->逻辑设计->物理设计->实施->运维

1. E-R模型

E-R模型 = 实体(矩形) + 属性(椭圆) + 联系(菱形)

设计局部E-R模型->合成全局E-R模型->优化模型（合并实体类；消除冗余属性、联系）

1. R模型转换为关系模型： 转换实体；转换联系

设计步骤 导出初始的关系模式集

规范化处理

模式评价、模式修正

设计子模式

1. 数据依赖

函数依赖：对于X中的每一个值，Y中仅有唯一值与之相对应（类似于函数映射）

分类：

平凡函数依赖 X->Y，Y不属于X

非平凡函数依赖

传递函数依赖 X->Y Y->Z，Y不属于X，Y不决定X

直接函数依赖

部分函数依赖 组合依赖（多个主码）

完全函数依赖

1. 范式

1NF	所有属性不可分
2NF	非主属性完全依赖于主属性
3NF	不存在非主属性的传递依赖
BCNF	属于1NF，对于每个函数依赖X->Y，Y不属于X，X必含有候选码	消除插入异常和删除异常

提升范式的方法是模式分解。 模式分解后可以通过自然连接恢复则称具有无损连接性。（不一定保持函数依赖） 分解后函数依赖保留称为保持函数依赖。

保持函数依赖->3NF 分解无损连接->4NF

• 数据库管理

1. 安全性：防止数据泄露、更改或破坏； 尽可能的杜绝非法访问

用户识别与坚定	帐号+密码 / 约定计算过程	最外层
存取控制	确保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 会收回全部间接获得的权限

1. 完整性：数据的正确性和相容性

完整性约束机制 = 功能定义 + 完整性检查（立即执行/延迟执行） + 违约反应

参照完整性的实现： 外码是否接受NULL

如何删除（级联删除、受限删除、置空删除）

修改被参照关系中主码的考虑

1. 并发控制

显式定义事务：begin transaction commit rollback

ACID特性： Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持续性

多事务并发：串行执行、交叉并发、同时并发 并发会破坏事务的隔离性、一致性

丢失修改、不可重复读、读“脏”数据

可串行化：并行事务正确的唯一准则

排它锁（X锁）	允许读写、不允许再加锁
共享锁（S锁）	仅读、可以再加S锁

封锁

封锁粒度↑，可封锁的对象↓，并发性↓，系统开销↓

三级封锁协议	一级	写前加X锁，结束后方可释放（丢失修改）（读不需要加锁）
	二级	1级基础上、读前加S锁，读后释放（脏读、丢失修改）
	三级	1级基础上、读前加S锁，事务结束后释放（脏读、丢失修改、不可重复读）
两段封锁协议（2LP）	1LP	扩张阶段；读写操作前先加锁
	2LP	收缩阶段；解除一个锁后，任何事务不再获得锁

活锁	某个事务永远处在等待状态	先来先服务（队列）
死锁	两个事务互相等待	预防：一次封锁法、顺序封锁法
		解决：超时法、等待图法

1. 故障与恢复：从不正确的状态恢复到正确的状态

种类：硬件、软件（系统、应用）故障、误操作、恶意破坏

事务故障	事务不正常终止	利用日志文件撤销
系统故障	丢失缓冲区	撤销未完成的事务、重做已完成的事务
介质故障	丢失外存	重装数据库、重做已完成的事务

恢复：利用冗余数据

日志文件：先等级日志文件后写数据库

转储： 静态转储/动态转储：动态转储时还需要登记日志文件

海量转储/增量转储：增量转储只转储更新过的数据

数据复制：对等复制、主/从复制、级联复制

数据库镜像：

提高数据库可用性，应对介质故障

DBMS自动将整个数据库/关键数据复制到另一个磁盘上