数据库学习笔记

DEAD

SQL

DBMS,DBS,XML

DBMS:数据库管理系统(包含相关企业信息 收集相关数据 访问的程序集 方便管理的开发环境)
DBS:数据库系统
XML:可拓展标记语言

DDL,DML,DCL

DDL:Data Defination Lang(数据定义语言)
DML:Data Manuplation Lang(数据操作语言)
DCL:Data Control Lang(数据控制语言)

码=键

键=超键:能唯一标识一行的属性集合
候选键:最小属性个数的键
主键:人为从候选键中选出来标识该行的键(不一定非要有一个主键,主键绝对不可以有空值).
外键:(属性)是另一个表的主键.

关系代数

选择: $\sigma_{条件}$ (选出来不会去重)
投影: $\prod$ (选好子列之后会进行去重)
并集: $\cup$ (条件:属性个数相同,属性域兼容)
差集: $-$ (属于前面不属于后面的元素)
笛卡尔积: $\times$
重命名: $\rho$

拓展运算符:(简化查询,不能增强关系运算能力)
交集: $\cap$
自然连接: $\bowtie$
除法: $\div$ (匹配:找出R(A,B),S(B)中所有匹配过所有S(B)的A)
赋值: $\leftarrow$

函数 过程 触发器

特性 函数 存储过程 触发器
返回值 必须返回一个值 可选 不返回值
调用方式 在表达式中调用 显式调用 自动触发
参数 只有输入参数 输入和输出参数 无参数
执行 不能修改数据库 可以修改数据库 可以修改数据库
用途 计算 业务逻辑 数据完整性/审计
事务 在调用者事务中运行 可以控制事务 在触发操作事务中运行

varchar和char的区别

varchar是可变长度,声明时是最大长度,实际占用空间可变.
char是固定长度,实际占用空间不变,多余用空格填充.

几种数据模型

关系数据模型 基于对象数据模型 图数据模型 树数据模型 半结构化数据模型

子查询

子查询可以引用外部查询的表.

关系代数与SQL

1
2
3
SELECT A1,A2,...,An
FROM r1,r2,...,rm
WHERE P

等价于

可以携带算术表达式(多少除多少那种).

字符串

用单引号 ' 保住,如果字符串本身有单引号就双写 '' 表示字符串.
% 匹配字符串任意子串(长度>=0)
_ 匹配任意一个精确字符(长度==1)
ESCAPE 转义字符(该字符后面的一个字符只会被当成普通的字符处理)
LIKE 'Save 20#% Today' ESCAPE '#'; 这个会匹配 20% 而不是 20xxx .

别名(AS)

通常是可以直接省略的,如:

1
2
FROM account AS R
FROM account R     --这两句话等同效力

排序

ASC 升序(默认), DESC 降序.

1
2
3
4
SELECT cname,bname,account
FROM b AS T,l AS S
WHERE T.num=S.num
ORDER BY cname ASC,account DESC

保留重复

用 ALL .即 UNION ALL,INTERSECT ALL,EXCEPT ALL .
(并集UNION,交集INTERSECT,差集EXCEPT)
例如选出所有有贷款或有存款的用户:

1
2
3
SELECT nname FROM loan
UNION ALL
SELECT nname FROM borrow

分组(group by,having)

按照什么规则分组之后让聚集函数分别生效
例:统计各银行储户数量(注意去重):

1
2
3
4
SELECT branch_name,COUNT(DISTINCT customer_name)
FROM depositor AS R,account AS S
WHERE R.account_number=S.account_number
GROUP BY branch_name

having:对于分组后的谓词开条件:
查询储户余额超过1200美元的银行和平均余额:
1
2
3
4
select branch_name,avg(balance)
from account
group by branch_name
having avg(balance)>1200

空值

判断: IS NULL 和 IS NOT NULL .
聚合函数除了 COUNT() 都是直接忽略空值的.
空值比较:sql中bool实际上是三结果的:true false和unknown,按照你想的比较方式进行即可.

嵌套子查询

用 IN 表示集合中的 $\in$ 关系.
相对的是 NOT IN .

集合比较

< SOME () < > = <= >= <>
找出资产高于A市某个银行的银行:

1
2
3
4
5
6
7
SeLeCt DiStInCt bankname
FROM branch
WHERE assets > some (
    select assets
    from branch
    where branchname='A'
)

同时还有 < ALL 的运算,类似.

存在(EXIST)

判定有没有返回数据.
例:找出既有存款又有贷款的顾客:

1
2
3
4
5
6
7
SELECT DISTINCT cname
FROM borrower R
where exists(
    select *
    from depositor S
    where S.cname=R.cname
)

  1. 子查询可以直接使用关系R,可以简化查询语言书写.
  2. exists只会判定有没有,所以直接返回*即可.

同理,不存在用 NOT EXISTS .

重复(UNIQUE)

如果返回的表不包含重复元组返回true.

from子句

允许查询,即 from (select xxx) .

with子句

定义临时关系,简化查询.

视图(view)

1
2
3
4
5
CREATE VIEW A(a,b,c) as(
    (select xxx)
    union all
    (xxx)
)
  1. view是一个虚拟表,简化查询.
  2. 可以根据权限分配视图,不让其知道数据库的真实结构.
  3. 底层表发生变化只需要改变视图就可以兼容原有应用程序.

物化视图(真实存储数据的视图)

删除数据

1
2
DELETE from r
where p

插入数据

1
2
3
4
insert into account
values('a','b',1200) --默认顺序
insert into account(cname,cnnn,numbers)
values('a','b',1200) --默认顺序

数据更新(update)

1
2
3
UPDATE account
SET balance=balance*1.05
WHERE balance>5000

带case结构:

1
2
3
4
5
6
UPDATE account
SET balance=CASE
        WHEN balance<=1000 THEN balance*1.05
        WHEN balance<=2000 THEN balance*1.06
        ELSE balance
    END

数据合并(JOIN)

1
2
3
4
5
6
7
NATURAL JOIN                  --自动找到相同的行
JOIN = INNER JOIN             --(自己指定条件on)
RIGHT JOIN = RIGHT OUTER JOIN --(自己指定条件on)
LEFT JOIN = LEFT OUTER JOIN   --(自己指定条件on)

ON xx=xx --自己指定join的条件,可以不是等式
USING xx --也可以用某个指定的共有的数据行的相等作为join对象.

数据类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
int                       --
decimal(p,s)=numeric(p,s) --p总位数,s小数点后位数
float(p)                  --p精度(总位数)
blob(2GB)                 --二进制大文件
clob(2KB)                 --字符型大对象类型
date
time
timestamp
year

create Dollars as numeric(12,2)
create table mm(
    mmoney Dollars
)

数据转换

1
cast(a.a to numeric(12,2))

完整性约束

1
2
3
not null
unique(a,b,...)
check(num >2000)

domain:用户自定义数据类型,里面可以附带一个check,避免重复约束,让代码可修改,减少错误,增加数据完整性,简化模式定义.

1
2
3
4
5
6
7
8
9
CREATE DOMAIN US_PHONE_NUMBER AS VARCHAR(15)
CHECK (VALUE SIMILAR TO '[0-9]{3}-[0-9]{3}-[0-9]{4}');

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    PhoneNumber US_PHONE_NUMBER, -- 直接使用域
    -- ... 其他列
);

函数 过程 触发器

赌一手不考.

ER图

实体集:矩形:上面写名字,下划线是主码(类似主键???)
联系集:菱形:里面写名字,联系集上的属性用矩形加虚线脸上.

约束:

1
2
3
========= 完全参与,也就是>=1
--------- 不完全参与,也就是>=0
--------> 严格是1

弱实体集:没主码,里面全部虚线,用两个矩形.
然后出去的也是双菱形.

ER转化

实体集

强实体集可以直接转化.
弱实体集转化之后添加依赖的强实体集的主码.
联系集写上联系的两个主码.

实体有一个多值属性:单拎出来,记录属性和父属性的主码.

联系集

映射基数:几对几.

一对多:选择多的那边添加另一边主码.
多对多:单拎出来作为一个实体,记录两边主码.
一对一:任选一边添加对方主码.

参与约束:主键和外键约束 唯一和检查约束.

多值依赖

属性集之间的一种约束:
课程->->老师->->课本
每个课程由多个老师教授,每个老师有多个课本,且相互独立.

函数

函数依赖

$A\to B$ 直观理解函数依赖:

A B C
1 1 1
1 1 2
2 2 3
2 2 4
3 2 5

关系模式: $R(A,B,C)$
函数依赖集: $F={A\to B,B\to C}$

典1:设 $A\to B,A\to C$ ,证明 $A\to BC$ .

  1. 由增广律有 $A\to AB$
  2. 由传递律有 $AB\to CB$
  3. 由传递律有 $A\to BC$ .

范式

1NF

所有列都是原子的(不可分割).

2NF

  1. 必须满足1NF.
  2. 非主键属性必须完全依赖于主键.

部分函数依赖:属性只依赖于主键的一部分,而不是整个主键.
2NF旨在消除部分函数依赖.

举例:假设订单表由 订单ID,产品ID,产品数量,物品名称 组成,因为一单可以买很多东西,所以主键必须由 (订单ID,产品ID) 共同构成.

产品的名称等信息只依赖产品ID不依赖订单,同理,所以产生了部分函数依赖,需要拆表:拆成订单表(谁订了,订单ID,…),物品表(物品ID,名字,…),订购记录表(订单ID,物品ID,物品数量).

2NF分解:找出候选码,然后对所有部分依赖的每一个字母单独开一个集合.

最小函数依赖集(正则覆盖换名字)

  1. 把所有箭头右侧的多个字母直接拆开.
  2. 对每个箭头考虑,去掉这个箭头能不能推出来(也就是对箭头左侧的字母求闭包),能推出来就删掉箭头,否则保留.
  3. 最后保留的箭头再尝试拆一下:箭头左侧可以是两三个字母,但是必须不可分.

找候选码

首先把不出现在箭头右侧的所有字母列下来,他们是必须出现的.
然后对这个字母集合每次遍历所有箭头找能不能匹配的.
直到没有之后break,然后判断是不是全集.是就是候选码,不是就再加条件.

无损链接判断

画一个表,上面是元素,左边是给出来的数据分解.
填表:对所有指向标an,没有标的填bij.

主循环:对每个箭头去推.
最后不变了之后如果存在一行全是a那就具有无损连接性,否则不具有.
这个

3NF判定

对任意 非平凡函数依赖 ($A\to AB$这种就是平凡的)有

  1. X是一个键
  2. Y是一个主属性,即Y的每个属性都包含在R的候选码之中.

BCNF判定

对于任意 $X\to Y$ 有X必须是R的一个超码(键).

3NF

不允许传递函数依赖.

求最小依赖关系,然后对每个箭头都单独开一个划分集合即可,最后把候选码加上(保证无损),如果候选码在里面有就不用管了.

BCNF

找BCNF:首先对于整个RF求候选码.
然后找到候选码之后再找一个不含候选码任意元素的箭头,把这个箭头整个拿下来作为一个新关系Rx.
然后删除箭头右边的元素,然后对于F求出所有关于删掉元素的传递关系,得到一个新F和新R,对子任务递归使用该算法.
(最后不存在就直接全拿下来)

保持依赖

在关系分解过程中,确保分解后的子关系模式仍然能够保持原有的依赖关系.

事务

概念:访问并可能修改数据项的一个程序执行单元.
模型:ACID模型:
原子性(把事务中的操作当成一个整体,要么都执行,要么都不执行)
一致性(隔离执行事务时保持数据库的一致性)
隔离性(并发执行事务不知道对方存在执行也不受彼此影响)
持久性(事务执行完毕后对数据库的改变是永久的)

其他模型:BASE 模型 (基本可用性、软状态、最终一致性)
XA 模型 (eXtended Architecture)
Saga 模型

修改:影子拷贝(生成一个副本进行修改,改好了提交(改变),或者滚回(丢弃))

并发执行

冲突:调度S含有分别属于T1和T2的两条连续指令I1和I2:
I1,I2分别等于read或write

属于调度S的操作相同的数据项的指令I1或I2中至少有一个是write时,称I1和I2是冲突的.

如果调度S可以经过一系列非冲突指令转换成S’,称S和S’是冲突等价的.

串行调度 :来自各个事务的指令序列中同一事务的指令紧挨在一起.

可串行化

如果一个调度与一个串行调度冲突等价,称该调度为冲突可串行化的.

画图:Ti和Tj是两个事务,连边(Ti->Tj):

  1. 在Tj读之前Ti写了
  2. 在Tj写之前Ti读了
  3. 在Tj写之前Ti写了

然后对图拓扑排序即可,有环代表不是冲突可串行化的.

视图可串行化

如果一个调度视图等价于一个串行调度,称该调度为视图可串行化的.

可恢复调度

对于任意事务Ti和Tj,如果Tj读取了Ti所写的项,则Ti先于Tj提交.

无级联调度

对于任意事务Ti和Tj,如果Tj读取了Ti所写的项,则Ti必须在Tj读取之前提交.

并发控制

lock-S(Q) S锁,可读不可写Q.
lock-X(Q) X锁,可读可写Q.

锁相容性矩阵(假如这个东西还有别的锁,你可以再拿到一个锁吗?)

\ S X
S Yes No
X No No

所以只要出现了X就不能再给锁了…

两阶段锁

  • 保证冲突可串行化.
  • 不保证不发生死锁.
  • 可能出现级联滚回.

严格两阶段锁

不仅要求封锁是两阶段的,还要求事务持有的所有排他锁必须在事务提交后才能释放.

强两阶段锁

要求事务提交之前不得释放任何锁.

死锁的检测与恢复

等待图:等待图中包含环时,系统存在死锁.(连边:i->j表示j在等待i释放所需数据项)

恢复:回滚一个或多个事务.

选择牺牲者:
已用和还需要的时间
已用和还需要的数据量
回滚涉及的事务数量

回滚:
全部回滚
部分回滚

饿死:
选择牺牲者的因素增加”回滚次数”

八股

中国有哪些自主研发的数据库?

  1. OceanBase蚂蚁集团
  2. OpenGauss华为开源
  3. 达梦数据库 D M武汉达梦
  4. PolarDB 阿里云
  5. TDSQL 腾讯云
  6. TiDB PingCAP
  7. GaussDB 华为云
  8. GoldenDB 中兴通讯

    如何实现多表更新?

  9. 对每个表使用UPDATE子句
  10. 使用事务
  11. 使用特定语法支持的多表更新语句
  12. 创建存储过程

做题

第一套

  1. 为什么3NF范式分解能够保证函数依赖一定能够得到保持?

    因为3NF分解能够保证函数的无损连接性和函数依赖保持性,同时尽量减少数据冗余和数据异常.

  2. 在sql语句中,where子句可以嵌入子查询,那么该子查询的作用有哪些?(至少两种)

    1. 作为筛选的数据来源
    2. 作为条件判断的逻辑依据
    3. 提供动态的逻辑值方便过滤
    4. 提供存在性的逻辑判断方便筛选

  3. 冲突可串行化调度(如何给出等价可串行化调度?)

第二套

  1. 主键叫做 primary key .

  2. 复合主键: p key(A,B)

  3. 复合主键是可以被外键约束的,且如果存在多个外键,一个一个来.

    1
    2
    3
    primary key (customer_name, account_number),
    foreign key(customer_name) references customer(customer_name),
    foreign key(account_number) references account(account_number)

  4. 主键会自动检查去重非空,并不需要额外的 not null 和 unique ,但是外键可以加上.

  5. (才发现有这么个运算符)新运算:分组聚集运算:

    按照A分组对b进行函数F(x)的运算,后面写清楚什么新名字.

    然后分组之后按照什么计算之后判定条件加在having这里.

    1
    2
    3
    4
    select branch_name,AVG(balance)
    from account
    group by branch_name
    having AVG(balance)<5000

  6. 什么是数据库索引?数据库索引一般采用什么结构?

    一种特殊的数据结构,能够实现高效检索数据,占用额外空间,减小查找的时间.
    索引结构都是平衡树,如B树,B+树,哈希索引等.

  7. 如果张三想通过汇款的方式给李四200元,张三的账户已经减掉200元后系统发生故障,并没有在李四的账户中增加200元,请问数据库出现了什么样的状态?这个问题该怎么解决?数据库通过什么手段实现该操作?

    出现事务处理失败后数据不一致的状态.
    事务并未成功提交,也没有被完全回滚.
    关键在于事务回滚或补偿.

第三套

  1. 在数据库中,实体的完整性是如何被保证的?

    通过主键约束保证.主键不能为空,不能重合.

  2. 如何降低数据库中数据的冗余度?

    关系数据库的规范化,也就是进行范式分解.
    数据中心化和共享.

  3. 关系代数中,与等值连接相比,自然连接的缺点是什么?

    1. 无法处理非等值连接.
    2. 当两个表有多列名字相同时,自然连接可能会出现意料之外的结果.

  4. 为什么要对数据库的调度就行可串行化判别,其实际意义是什么?

    为了防止意料之外的错误(脏读,更新丢失,幻读,不可重复读)等,要对并发的事务进行串行化检查

劳伦斯

  1. 数据抽象

    1. 物理层 数据在物理存储介质上的结构
    2. 逻辑层 数据在应用逻辑上面的形式
    3. 视图层 用户实际可交互的节面,减少逻辑的泄露

  2. 两层映射

    1. 外层映射 实现逻辑数据独立性
    2. 内层映射 实现物理数据独立性

ONS

  1. 什么是数据模型?

    数据模型是描述数据关系,数据操作,数据定义的形式化工具,用来组织,存储,管理数据.

  2. 为什么在设计数据模型时要把数据做得精确?

    提高数据质量,优化存储性能,简化管理维护,减少开发和维护成本.

  3. 主键的类型有哪些?如何选择主键?

    业务主键,复合主键,代理主键.
    首选自增长系数(如ID).
    谨慎使用复合主键.

  4. 举例说明不可重复读异常产生的原因以及其在数据库管理系统中的解决办法进行描述.

    不可复读异常:某个事务要多次读取同一行数据,但是这个数据被修改过导致两次读取结果不一致.
    可以使用四种隔离级别来有效避免这种异常:
    读未提交:允许读取某个事务尚未提交的数据(脏读),不解决不可复读.
    读已提交:允许读取某个事务已提交的数据,解决脏读,但仍未解决不可重复读.
    可重复读:保证在多行的多次读取结果是一致的,解决了不可重复读.
    串行化:解决了所有并发异常,但是由于牺牲并发性导致性能开销很大.

  5. 并发控制的主要方法是( )机制.

    锁机制.

  6. 在数据库中,只存放视图的( ),不存放视图的数据.

    定义(结构,SQL语句等)

  7. 简述数据库系统和文件系统的区别.

    数据库的文件更加集中,数据共享性很高,数据管理更加方便快捷,数据冗余更低,数据一致性高,数据安全性高,相比文件和目录以结构化的方式存储数据.

  8. 判断分解后的关系模式是否合理的两个重要的标志是什么?

    无损链接性和保持函数依赖.

  9. 基于多表的视图能够完成哪些操作?不能完成哪些操作?

    SELECT.可以简化复杂查询.
    通常不能进行数据修改,即INSERT,UPDATE,DELETE,因为多表的语义一般是不明确的.

  10. 为什么要实现不同粒度的锁?

    为了实现并发处理中效率和精度的平衡.
    粗粒度锁:如表锁(锁定范围大,并发度低)
    细粒度锁:如行锁(锁定范围小,开销大,并发度高)
    除此之外还有数据库锁,页锁等.
    为了防止死锁和活锁,提高吞吐量.