概要
注:定义性质的我都用斜体 标注
数据库系统的特点和概念
三层模式
三层模式是指ANSI-SPARC体系结构
三层体系结构指的是外部层、概念层和内部层
外部层提供视图,提供用户观察数据的视图
概念层提供内、外部层的映射和必要的独立性
内部层提供DBMS和操作系统观察和存储数据的结构。
三层模式的优点:
- 每个用户都可以操作自己的数据视图而不影响其他用户
- 将数据库物理存储的细节隔离出来
- DBA能够在不影响用户视图的情况下修改数据库存储结构
- 数据库的内部结构不受存储的物理变化的影响
外部层
外部层是数据库的用户视图,这一层描述了每一个用户相关的数据库部分
外部层由数据库中的若干个不同视图组成。每个用户都有自己的视图,该视图用其最熟悉的方式表示现实世界
概念层
概念层是数据库的整体视图。这一层描述了哪些数据被存储在数据库中,以及这些数据之间的联系
比如:
- 所有实体、实体的属性和实体间的联系‘
- 数据的约束
- 数据的语义信息
- 安全性和完整性信息
概念层支持每一个外部视图,凡是用户可以访问的数据必定包含在概念层或者由概念层数据可导出。
内部层
内部层是数据库在计算机上的物理表示。这一层描述数据是如何存储在数据库中。
内部层包括为了得到数据库运行时的最佳性能而采用的物理实现方法
内部层与如下工作相关:
- 数据和索引的存储空间分配
- 用于存储的记录描述(数据项的存储大小)
- 记录放置
- 数据压缩和数据加密技术
内部层之下是物理层,物理层可能在DBMS的指导下受操作系统的控制。
DB/DBMS/DBS
DB
为满足某个组织机构的信息要求而设计的一个逻辑相关数据及其描述的共享集
逻辑相关数据:包含实体,属性,组织机构信息的关系
系统目录(元数据):提供数据的描述去使数据与程序独立
DBMS
一个支持用户对数据库进行定义、创建、维护及控制访问的软件系统
提供以下功能:
- 数据定义语言(DDL)
- 数据控制语言(DML)
- 数据库的受控访问
DBS
数据库系统由
- DB:一个特定于应用程序的数据库
- DBMS:一个可以使用户定义,创造,维护,控制数据库访问权限的软件系统
- AP:控制数据库的应用程序
关系模型
对象关系模型
一个描述数据、数据间的关系,约束的集成集合
术语
关系:关系是由行和列组成的表
属性:属性是关系中命名的列
域:域是一个或多个属性的取值集合
元组:关系中的每一行称为元组
维数:关系的维数是指关系所包含属性的个数(不严谨的说就是列数)
基数:关系的基数是指它所包含的元组的个数
(不严谨的说就是行数)
关系数据库:关系数据库是具有不同关系名的规范化关系的集合
关系模式:用一组属性和域名对定义的具名的关系,比如$$(A_1:d_1, A_2:d_2,…, A_n:d_n),其中d1\in D_1, d_2\in D_2, …, d_n\in D_n$$
$A_i$是属性,$d_i$是值,$D_i$是域
约束
超关键字
一个属性或属性集合,它能唯一地表示出关系中每个元组
候选关键字
本身就是超关键字但其任何子集都不再是超关键字
主关键字
被选用于唯一表示关系中各元组的候选关键字
实际上就相当于NOT NULL + UNIQUE
可替换关键字
没有被选于唯一标识关系中各元组的候选关键字
外部关键字
当一个关系中的某个属性或属性集合域另一个关系的候选关键字匹配时,就称这个属性或属性集合为外部关键字
完整性约束
实体完整性
在基本关系中,主关键字属性不能为空
引用完整性
如果在关系中存在某个外部关键字,则它的值与主关系中某个元组的候选关键字取值相等,或者全为空
注意一定是候选关键字而不是一般属性
运算操作
八运算操作,其中五个基本运算操作
- 集合并
- 集合差
- 笛卡尔集
- 选择
- 投影
其余三个
- 连接
- 集合交
- 除
注意一下他们的符号
选择:$\sigma$
选择工资多余10000的员工:$\sigma_{salary>1000}(Staff)$
投影:$\Pi$
选出员工的staffNo,fName,lName,salary:
$\Pi_{staffNo, fName, lName, salary}(Staff)$
并:$\cup$
$\Pi_{city}(Branch) \cup \Pi_{city}(PropertyForRent)$
差:$-$
$\Pi_{city}(Branch) - \Pi_{city}(PropertyForRent)$
交:$\cap$
$\Pi_{city}(Branch) \cap \Pi_{city}(PropertyForRent)$
笛卡尔集:$\times$
$\Pi_{clientNo, fName, lName}(Client) \times \Pi_{clientNo, propertyNo, comment}(Viewing)$
注意两点
如果两个表中有相同的属性,则应该用表名唯一表示
用书上的例子
client.clientNo | fName | lName | Viewing.clientNo | propertyNo | comment |
---|---|---|---|---|---|
… | … | … | … | … | … |
其中Client表和View表都有clientNo这个属性(具体什么意思不用管了)
但是为了唯一标识,要把表名列出来
这个笛卡尔集使任意交叉,没有经过筛选,要筛选需要再用次选择
$\sigma_{Client.clientNo = Viewing.clientNo}(\Pi_{clientNo, fName, lName}(Client) \times \Pi_{clientNo, propertyNo, comment}(Viewing)$
连接
连接有很多种
$\theta$连接
$\theta$连接时最简单的连接
$R\Join_{F}S = \sigma_{F}(R \times S)$
带谓语的连接
自然连接
自然连接是在公共属性上连接,
在上面的例子上可以是
$$\Pi_{client.clientNo, fName, lName, propertyNo, comment}(\sigma_{Client.clientNo = Viewing.clientNo}(\Pi_{clientNo, fName, lName}(Client) \times \Pi_{clientNo, propertyNo, comment}(Viewing))$$
外连接
外连接是两张表,展示一张表所有内容,如果另一张表没有对应内容,则填null。
左外连接:$\rtimes$
右外连接:$\ltimes$
开口指向展示所有内容的一边
半连接算了吧
除法运算
除法运算问题很大
我觉得除法运算和分组有点像
具体可以看
例如$R\div S$
R(X, Y),S(Y, F)
- 找到R,S两个集合公共属性Y
- 对着除的集合(右边的)S找它的Y,对每一个Y中的元素找被除的集合(左边的)R的X
- 如果S对给定的Y的X都是一样的,加入结果集,否则不加入
- 获取结果集
PS:说不清楚啊……
除此之外,还可以提一下分组运算,但不如后面的SQL种讲
SQL语言
Structured Query Language
SQL是DDL、DML、DQL、DCL的集成(扯什么犊子,都一箩筐)
SQL 不区分大小写
DDL
数据定义语言(Data Definition Language,DDL)用于定义数据库结构和数据的访问控制
几个命令
- CREATE
- ALTER
- DROP
- TRUNCATE
DML
数据操作语言(Data Manipulation Language,DML)用于检索和更新数据
几个命令
- SELECT
- DELETE
- UPDATE
- INSERT
- CALL(调用PLSQL那货)
- LOCK TABLE
DCL
数据控制语言(Data Control Language,DCL),用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等
- COMMIT
- SAVEPOINT
- ROLLBACK
- SET TRASACTION
- GRANT
- REVOKE
处理事务用
不具体讲,注意几个小点
SELECT DISTINCT * 语序不要搞反
LIKE的%是0个或多个,_是一个
1
LIKE 'Hel__ %d'
当然我隐约记得Sql lite支持^和$,
也可以使用NOT LIKE表示不遵循表达式数据项
在where中,如果对另一张表的查询结果只有一个,可以用=的,如果有多个,用
IN
1
2
3
4SELECT * FROM STUDENT WHERE STUDENTID = (SELECT STUDENTID FROM TEACHER)
-- 确保后面的查询只有一个结果(假设是一对一老师教学(滑稽))
SELECT * FROM TEACHER WHERE TEACHERID IN (SELECT TEACHERID FROM STUDENT)
-- 当后面查询结果有多个的时候GROUP
GROUP BY的意思是将GROUP BY后面的属性相同的列并起来,并且呈现
SELECT中只有GROUP BY后面的属性可以被SELECT,其他都得被聚合函数聚合
GROUP中的约束用HAVING,HAVING中操作的属性在GROUP BY可以呈现的属性集合中
1
2
3
4
5SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;老生常谈的问题——效率
一张大表比外键连接的效率是好的,虽然大表可能会产生占内存问题,而且很多一个元组中有过多的属性也会产生低效率,但是这个可以被索引解决。
但是我自己实践的过程中肯定是喜欢外键的,最大的原因之一是很多框架对外键的支持比较好,可以把外键当成List查询。听说有些公司喜欢不做外键(为了规避外键约束),但是我也不懂。
索引
索引是一种结构,它提供了基于一个或多个列值快速访问表中元组的方法
1
2CREATE [UNIQUE] INDEX indexName ON
Tablename(columnName[ASC|DESC][,...])索引只能基于表而非视图建立
自主访问控制
两种授权机制
- 自主访问机制:每个用户被授予对特定的数据库对象的适当的访问权利。
- 强制访问机制:每个数据库对象被赋予特定分级,主体被赋予特定的许可证级别。主体需要必要的许可证级别来读写数据库对象。
控制访问用
GRANT
和REVOKE
操作。可授权的操作:
- SELECT
- INSERT
- UPDATE
- DELETE
- REFERENCES可以允许在约束中引用这些列
- USAGE(使用域/序列/字符集和转变规则(暂时跳过))
授权
1
2
3
4GRANT {PrivilegeList|ALL PRIVIEGES}
ON ObjectName
TO {AuthorizationIdList|PUBLIC}
[WITH GRANT OPTION]注意对于Privilege,SELECT, DELETE USAGE后面不用接列,INSERT,UPDATE和REFERENCE后面接列
WITH GRANT OPTION允许AuthorizationIdList给别人授权
撤销权限语法相近
1
2REVOKE [GRANT OPTION FOR]{PrivilegeList|ALL PRIVILEGES} ON ObjectName
FROM {AuthorizationIdList|PUBLIC} [RESTRICT|CASCADE]GRANT OPTION FOR允许GRANT语句中的WITH GRANT OPTION传递的权限被撤销
RESTRICT 和CASCADE是针对REVOKE说的,在RESTRICT下,如果REVOKE导致一个对象(比如视图)被抛弃,则不能执行。
但是在CASCADE下所有因REVOKE而被抛弃的对象都将被依次抛弃
PLSQL
淦,内容怎么这么多,我都快没耐心了
PLSQL就是你懂的~Programming Language SQL
声明
1 |
|
以及
1 | CASE(x) |
循环
1 | label: |
1 | label: |
PLSQL游标
游标:游标就像一个指向查询结果中特定行的指针,游标可以前进一格,以便访问下一行
语法
1 | DECLARE |
这和JDBC中的ResultSet
一个逻辑
存储过程
就整一函数
1 | create or replace procedure {procedureName} |
注意参数的IN /OUT /IN OUT
IN只能作为输入值
OUT只能作为输出值
IN OUT既可作为输入值,也可作为输出值
触发器
触发器如果是习惯作UI的同学应该都是秒懂的
就是根据事件做出反应
语法为
1 | CREATE TRIGGER triggerName |
之前实践的时候想到一点:我觉得hibernate对PLSQL原生触发器的支持非常差。相对的,hibernate支持Java程序编写的事件监听器EventListener
和拦截器Interceptor
所以使用hibernate的时候就可以放弃触发器了
DB设计
视图
视图是为了得到另一个关系而对基关系作一次或多个关系操作的动态结果
视图是
- 一个虚拟关系,并不存在于数据库中,需要特定用户的要求临时生成
- 一个或多个关系操作的动态结果
CREATE代码:
1 | CREATE VIEW VIEWNAME [(COLUMNNAME1, ...)] |
视图就像个筛子,只会筛选出符合条件的元组(WHERE
中的条件)
迁移行:如果对视图进行插入和更新时有新行满足或不满足WHERE
条件则这些新行就会出现或消失在视图中
通过WITH CHECK OPTION
限制迁移行。
有两个选项CASCADED
和LOCAL
,默认情况下都是CASCADED
CASCADED
使任何对这个视图或其他的从这个视图创建的视图的插入与修改操作不得使行消失
LOCAL
使任何对这个视图或其他的从这个视图中创建的视图的插入和修改操作不得使行消失,除非在从这个视图中父的所有视图这一行也消失了(不满足所有派生视图(derived view)的条件)
视图分解
视图分解时在查询视图的过程中,将用作视图定义的查询与在视图上的查询合并
1 | CREATE VIEW view1 AS |
1 | SELECT A0 FROM A WHERE A1 = 3 |
可以转化为
1 | SELECT A0 FROM A WHERE A0 = 1 AND A1 = 3 |
视图的限制
- 如果视图中某个列时由聚合函数产生的,那它只能被SELECT和ORDER BY
- 分组视图不能和基表或视图连接
视图可更新的限制
- 没有指定DISTINCT
- 定义的SELECT列表中的元素都是列名而不是聚合函数和常量和表达式。
- FROM只指定一个表
- WHERE子句不能引用FROM子句中的表的嵌套SELECT操作
- 不能有GROUP BY
不能违反基表约束
ER模型
实体
能够独立存在的一组具有相同属性的对象
关系
实体类型键的一组有意义的关联
度
参与联系的实体类型的个数
两个实体之间的关系就是二元关系,度为二
三个实体之间的关系就是三元关系,度为三
度为二以上的关系都被称作复杂关系
实际上多元关系都可以转化为二元关系
属性,属性域,简单属性,复杂属性,单值属性,组合属性,单值属性,多值属性
以上我都懒得讲
导出属性
导出属性是从相关的一个或一组属性的值导出来的属性
注意导出属性算属性不算函数(别问我为什么说这个)
候选关键字
主关键字
合成关键字
包括两个或两个以上属性的候选关键字
陷阱
- 扇形陷阱:当一个实体连接了两个一对多实体时,会产生扇形陷阱
解决方案就是重新理解需求并且重构,做出1:M:N的结构出来
断层陷阱:当一个实体与一些实体时1…M:1…N,但是与另一些实体产生了0…1: 0…*(有点说不清楚),几句产生了断层陷阱
解决方案是跳过中间的断层直接作连接
(陷阱有点讲不清楚)
增强ER模型就是在其基础上添加了OOP的继承和多态
泛化的约束
泛化有两种约束:参与约束和不相交约束
参与约束:限制每个超类的成员是否一定是某个子类的成员,是则Mandatory,不一定则Optional
不相交约束:说明了超类的某一个成员是仅为一个还是同时为多个子类的成员,仅为一个则为Or,可为多个则为And
这个可理解为支持多继承与否
假设:中国人,程序员,大学生都继承于人,但是一个人可以既是中国人又是程序员有时大学生,所以这个关系是And。
但是对于都继承于人的中国人美国人日本人来说,这个关系是互斥的(别和我说JOJO,谢谢),所以这个关系是Or。
规范化
生成一组既具有所期望的特性又能瞒住企业数据需求的关系的技术
性质:
- 属性的个数最少
- 具有紧密逻辑联系的属性均在同一个关系中
- 最少的冗余,即每个属性只出现一次
优点:
- 易于用户访问
- 数据易于维护
- 计算机存储空间小
缺点:
- 性能相对降低
非规范化的数据库的异常
假设一张表记录了公司员工于所属公司的关系
它的函数依赖可以表示为
$staffNo, companyNo\rightarrow staffName, staffAge, staffBirthDay, companyName, companyAge$
其为部分函数依赖,即
$staffNo\rightarrow staffName, staffAge, staffBirthDay$
$companyNo\rightarrow companyName, companyAge$
那么当我插入staff的时候我就不得不填入company相关信息
如果修改staff信息也不得不加上company信息
如果删掉了一个公司中所有的staff之后,这个公司也不存在于表格中了
这在现实是极其难操作甚至说是做不到的
以上三个分别是插入异常、修改异常和删除异常
函数依赖
描述一个关系中属性之间的联系。例如,假设A和B均为关系R的属性,若A的每个值都和B中的一个唯一的值,则称B函数依赖于A,记为$A\rightarrow B$
在实际操作中,A为超关键字组成,且多为主键
函数依赖分两种,完全函数依赖与部分函数依赖
完全函数依赖:假设A和B是某一关系的属性(组),若B函数依赖于A,但是不函数依赖于A的某一真子集,则称B完全函数依赖于A
具体表示为
$c_0\rightarrow c_1, c_2, c_3, …, c_n$
部分函数依赖:如果B函数依赖于A,但是B不是完全函数依赖于A,则是B部分依赖于A
具体表示为
$a_0, b_0\rightarrow a_1, a_2, a_3, b_1, b_2, b_3$
其中$b_i$与$a_0$无关,$a_i$与$b_0$无关
传递依赖:假设A、B、C是某一关系的属性,若$A\rightarrow B$,$B\rightarrow C$, 则称C通过B依赖传递于A
范式
主要介绍第一范式,第二范式,第三范式
第一范式:每一行和每一列相交的位置有且仅有一个值
直接理解的话,可以看成是避免ER模型中的组合属性,将组合属性拆成多个单值属性
(现实中的数据库系统应该都是默认遵循第一范式的吧……)
第二范式:满足第一范式的要求并且每个非主关键字属性都完全依赖于主关键字
就是将上述的
$staffNo, companyNo\rightarrow staffName, staffAge, staffBirthDay, companyName, companyAge$
拆成
$staffNo\rightarrow staffName, staffAge, staffBirthDay, companyNo$
$companyNo\rightarrow companyName, companyAge$
两张表
这样可以避免多个无关的实体混合在一张表中。并使它们的关系通过外键连接
第三范式:满足第一范式和第二范式的要求并且所有非主关键字属性都不传播依赖于主属性的关系
我认为这个是将第二范式无法拆分的表中实体的关系拆分出来。
第二范式就是拆分直接函数依赖于主键的实体关系,但是无法拆分间接依赖于主键的实体关系,第三范式就是处理这个问题。
事务
事务定义:由单个用户或者应用程序执行的,完成读取或者更新数据库内容的一个或多个操作
事务时数据库的逻辑处理单位
事务的性质:
- 原子性:一个事务就是一个原子单位
- 一致性:事务必须将数据库从一种一致的状态转化为另一种一致的状态
- 隔离性:在有并发的时候,事务的执行时相互独立的。
- 持久性:事务完成的结果要永久保存在数据库中(在外存保存)。
隔离性和持久性保证了原子性和一致性
三个问题(四个)
丢失更新
一个用户的更新操作被另一个用户所取代了
脏读
前提:允许一个事务看到另一个未提交事务的中间结果(违背隔离性)
一个事务看到另一个事务在提交之前的中间结果,产生的读取数值误差
不可重读问题
在一个事务多次读取的时候另一个事务修改了数值,导致多次读取结果不一样
幻读
和不一致问题很像,但是是在query过程中
可串行化
为了保证一致性,事务使用了可串行化方法
可串行化中冲突操作的顺序是非常重要的:
- 如果两个事务只读,那就没有冲突,顺序也不重要
- 如果两个事务都读且写,但是读写的是完全不相干的两个数据项,也没有冲突,顺序也不重要
- 如果一个事务写入一个数据项,另一个事务读或写相同的数据项,他们就会有冲突,顺序就很重要
当一个事务于其他事务冲突的时候,使其延迟(传统方法)
可串行化是DBMS默认的正确性准则(correctness criterion)
缺点:可串行化计算开销大,可能会做一些无用的延迟
加锁
共享锁:事务只能读不能修改数据项的锁
互斥锁:事务可以读可以改数据项的锁
2PL两段锁协议
这个锁只有两个阶段:
- 扩展阶段:所有事务可以获取它们要的锁,但是不能释放任何锁
- 收缩阶段:所有事务可以释放它所拥有的锁,但是不能获取它们要的锁
这个协议可以达到满足事务安全(解决那三个问题)的最快效率
死锁
定义大家都懂的~
解决方案
- 超时
设定一个极限时间,如果一个事务等待加锁的事务的等待时间超过它,则回滚此事务并重启
问题是:极限实践设置长了之后对解决死锁帮助不大,设置断了对长事务不友好
- Waiting-for Graph(WFG)
通过WFG可以展示事务的依赖关系并且判断死锁
算法流程: - 对每一个事务做一个节点
- 创造边$T_i\rightarrow T_j$如果$T_i$等待$T_j$锁住的资源
- 如果图中存在一个环,则死锁存在
孤立等级
由于序列化有一些缺陷,SQL允许程序员设定孤立等级
SQL标准中有四个孤立等级
- READ UNCOMMITTED:允许脏读
- READ COMMIT:不允许脏读
- REPEATABLE READ:避免脏读和不可重读问题,但是无法避免幻读
- SERIALIZABILITY:避免所有的问题
注意:ORACLE只实现了SQL标准中的两个,即READ COMMITTED和 SERIALIZABLE
数据恢复
自己看