关于软考中级数据库的一些讨论

数据库

1.三级模式两层映射

模式映射概况

用户级数据库 外模式 用户视图 概念模式映射 视图级 用户模式 逻辑描述 用户看到的是接口

概念级数据库 概念模式 链接

DBA视图 概念模式-内模式映射 表级 概念模式 简称模式

物理级数据库 内模式

内模式 内部视图 文件级 内模式 储存结构 物理查询方式 非软件设计师掌握

操作系统 下 物理数据库

相关

映射的好处：数据具有独立性，数据与程序分离

物理独立性（内模式），物理层修改。改变内模式映射。

业务变化，用户层修改，改变外模式映射。

2.数据库设计过程

四个计阶段：

当前和未来应用的数据要求，数据处理要求

*需求分析：数据流图，数据字典，需求说明书

*概念结构设计，ER模型

用户的数据模型（即与DBMS无关的概念模型)

结合转换规则、规范化理论进行*逻辑结构设计得关系模式

视图、完整性约束及应用处理说明书

并结合DBMS特性、硬件、OS将性进行*物理设计

（详见“系统设计与开发”相关内容）

3.E-R模型

ER模型是概念设计向逻辑设计进行。方框：实体，椭圆：属性，菱形：联系，方框加线：弱实体 实体的特殊化 与实体连线时加圆圈。

一个实体型转换为一个关系模式联系转关系模式:

1:1联系：两个模式表述，可将联系合并至任一端的实体关系模式中，关系属性可任意放，同时放另一实体得主键。

1:n联系：两个模式表述，可将联系合并至n端实体关系模式中，关系属性置于n，n放1得主键。多的表示有：m，n，*等，根据题干描述。

m:n联系：三个模式表述，联系必须单独转成关系模式，联系单独建立模式，两个实体的主键作为其主键。

三个以上实体间的一个多元联系关系模式数量是实体数+联系数

4.关系代数

并、交、差考的比较少，在关系模式分解中，判断是否有损时有用。合并，找共同，前去后自留。

笛卡尔积，两个表一对一匹配，组合，形成新的记录存在新表之中。类似矩阵乘法？

投影，用 $ \pi $ _{属性1，属性2} (表名) 表示，选字段，选列。

选择，用 $ \sigma $ _属性=值 (表名) 表示，选记录，选行。

自然联接，用▷◁表示，手写时画无穷一样连写。在原来的基础上，补齐没有的字段。

两个表做笛卡尔积，选择，投影的结果。

$ \pi $ _{原来的属性，原来没有的属性} ( $ \sigma $ _{表1主键=表2主键} (表1*表2) )

5.规范化理论

数据冗余会带来三大异常：修改异常、插入异常、删除异常

需要对表结构进行规范化，消除冗余，常用操作是拆表。

设R(U)是属性U上的一个关系模式，X和Y是U的子集，r为R的任一关系，如果对于r中的任意两个元组u，v，只要有u[X]=v[X]，就有u[Y]=v[Y]，则称X函数决定Y，或称Y函数依赖于X，记为X→Y。这种关系可以使用有向图来表示。

键

候选键：唯一标识元组，且无冗余。

主键：任选一个（组）候选键。

外键：其他关系的主键。

求候选键

图示法求候选键

1、将关系的函数依赖关系，用“有向图”的方式表示。

2、找出入度为0的属性，并以该属性集合为起点，尝试遍历有向图，若能正常遍历图中所有结点，则该属性集即为关系模式的候选键。

3、若入度为0的属性集不能遍历图中所有结点，则需要尝试性的将一些中间结点（既有入度，也有出度的结点）并入入度为0的属性集中，直至该集合能遍历所有结点，集合为候选键。

类似于拓扑排序的求法？

主属性定义：组成候选码的属性就是主属性，其它的就是非主属性。

范式

重点掌握一二三与BC范式，不符合范式的一般解决办法是拆表。

第一范式1NF：在关系模式R中，当且仅当所有域只包含原子值，即每个属性都是不可再分的数据项则称关系模式R是第一范式。属性值都是不可分的原子值。

第二范式2NF：当且仅当关系模式R是第一范式（1NF），且每一个非主属性完全依赖候选键（没有不完全依赖）时，则称关系模式R是第二范式。消除非主属性对候选键的部分依赖。

第三范式3NF：当且仅当关系模式R是第二范式（2NF），且R中没有非主属性传递依赖于候选键时，则称关系模式R是第三范式。消除非主属性对候选键的传递依赖。

BC范式BCNF：设R是一个关系模式，F是它的依赖集，R属于BCNF当且仅当其F中每个依赖的决定因素必定包含R的某个候选码。消除非主属性对候选键的部分和传递依赖。

从1到BC，逐步优化，解决：数据冗余导致的修改异常、插入异常、删除异常。

6.模式分解

保持函数依赖分解

设数据库模式p={R1，R2，...，Rk)是关系模式R的一个分解，F是R上的函数依赖集，p中每个模式Ri上的FD集是Fi。如果{F1，F2 ,...，Fk}与F是等价的(即相互逻辑蕴涵），那么称分解p保持FD。对于分解后的模式，用原关系描述带入到新关系中检验，观察是否可行。

无损分解

有损：不能还原；无损：可以还原。

无损联接分解：指将一个关系模式分解成若干个关系模式后，通过自然联接和投影等运算仍能还原到原来的关系模式。

判断方式：方法1、一步一步合并分解的模式，与原模式对比。方法2、列出原模式所有属性的表格，分解的属性在表格中打勾，根据各种依赖关系看能否勾出一个完整的模式。

7.SQL

（具体语法略）

索引

从本质上看，索引是根据表的一个或者多个字段生成的子表，该子表中的数据已经进行了排序。子表除了包含指定字段中的数据，还包含一个 rowid 列，用于存储当前记录在原始表中的位置。用户无法看到索引，它只是用来加快查询速度。不使用索引，数据库引擎将遍历整个表。[C语言中文网]

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。[菜鸟教程]

聚簇（聚类）索引，类似于字典拼音检字表，字典上的字是按照拼音排序的，一个字母对应一群字，拼英对应字实际的存储位置[老Key]。上面一个比喻重点不在于一个索引值是否对应多个记录，而是在于索引是否以记录的物理存储顺序组织的。

查询

不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 下限 和 上限 之间的人，但不包括 下限 和 上限 ；某些数据库会列出介于 下限 和 上限 之间并包括 下限 和 上限 的人；而另一些数据库会列出介于 下限 和 上限 之间的人，包括 下限 ，但不包括 上限 。所以，请检查你的数据库是如何处理 BETWEEN....AND 操作符的！[w3school]

ESCAPE短语，后面用于指定转义字符，将WHERE子句中的DBMS自带的特殊符号转义为普通字符。一般的，使用\和#来作转义字符，也可以自行做其他的定义，在MySQL中使用\作标记时，需要\\，以免误转义反引号。

查询中指定DISTINCT短语，可以取消查询出来的列中的重复值，如果不使用，则默认为ALL保留查询中重复的值，并参与到计数、求和、求平均等计算中。

GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，如果有用Excel比较多的话，GROUP BY比较类似Excel里面的透视表。GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等[AwesomeTang]。不使用的话分组就没意义，相当于DISTINCT的作用了。对于WHERE和HAVING，都是筛选用的短语，在SQL中，不同点在于，WHERE的对象是元组，HAVING的对象由元组构成的组。在分组查询中，聚合函数传入的参数我认为可以理解为发生了变化，和没有使用分组查询相比，由整张查询完的表为参数，变成了由查询完并完成了分组的表为参数。

嵌套查询时，子查询在筛选短语中需要写在比较符号（包括等于）之后。在使用ANY和ALL的子查询和使用某些聚合函数结果比较查询的结果是一样的、等价的，但他们的查询实现方式有所不同。对于子查询的实现方式，数据库将遍历所有数据，判断是否符合条件，在ANY情况下，如果有一个符合条件则停止遍历，继续下一子查询；在ALL情况下，如果有一个不符合条件则停止遍历，继续下一子查询。而聚合函数比较法则是先求出极值，再来比较。在不考虑聚合函数的实现复杂度的情况下，可以粗略理解为子查询方式时间复杂度为O(m*n)，聚合函数为O(m+n)，所以一般来说子查询方式效率较低。

嵌套查询分为相关子查询和不相关子查询。相关子查询可以理解为2层循环，要想执行内层的查询，需要先从外层查询到1个值出来，执行的顺序是，父查询1个值，子查询对这个得到的值进行1轮查询；不想管子查询的子查询不需要父查询把结果传进来，所以叫不相关子查询[真真]。

CASE... WHEN... THAN... ELSE... END是SQL中触发器相关的语句，类似于变成语言中的条件分支语句。可以实现简单的逻辑判断。一个多分枝的逻辑判断实例，分别查看不同年龄段用户的明细情况：

SELECT device_id, gender, 
    (CASE 
     WHEN age < 20 THEN "20岁以下" 
     WHEN age >= 20 AND age <= 24 THEN "20-24岁"
     WHEN age >= 25 THEN "25岁及以上"
     ELSE "其他" END
    ) AS age_cut
FROM user_profile;

多重条件组合查找、且需要分别查看时适合使用UNION关键字进行集合查找，将不同条件下的查找结果组合起来。需要注意的是，当出现“不去重”之类的表述时，要使用UNION ALL来组合查找结果。

链接查找时，需要理解链接查找的基本过程。当SQL语句中使用到FROM table1, table2的时候，DBMS是将table1和table2通过笛卡尔积链接成了一个新的中间表，后续再通过WHERE子句中的条件和SELECT field1, field2的需求进行筛选，完成自然连接等。这样虽然方便快捷，但可能会影响到性能，笛卡尔积会带来较大的复杂度。可能是DBMS对内连接有特殊的优化，一般使用专门的INNER JOIN短语进行连接。例如：

SELECT 
	U.university, 
	COUNT(Q.device_id) / COUNT(DISTINCT Q.device_id) AS avg_answer_cnt
FROM 
	user_profile U,
	question_practice_detail Q
WHERE 
	U.device_id = Q.device_id
GROUP BY 
	U.university

可以改写成：

SELECT 
	U.university,
	COOUNT(Q.question_id) / COUNT(DISTINCT Q.device_id) AS avg_answer_cnt
FROM 
	question_practice_detail AS Q
INNER JOIN 
	user_profile AS U
ON 
	Q.device_id = U.device_id
GROUP BY 
	U.university

总的来说，可以将SELECT子句理解成面向过程和函数式编程中的return语句，整个SELECT查询就是就是一个类似的函数代码块，代码块的参数是来自于FROM子句，返回值就是SELECT子句，和一般编程语言函数的传参是反着的。例如

SELECT COUNT(DISTINCT Q1.date) / (
    SELECT COUNT(*) 
    FROM question_practice_detail 
) AS avg_ret

左右链接查询时，其左右表将在连接后全部保留，以左连接为例，取左表所有数据，在右表中查询匹配，右表中没有的，用NULL补全；也可以理解为，在右表中添加一行全空记录，用于匹配没有匹配成功的左表记录。

子查询中的SELECT的作用就是返回COUNT(*)的值，注意不要写成了COUNT(SELECET *)。关于DISTINCT关键字，在SELECT子句中使用时是作用于SELECT子句的，而不是DISTINCT后紧接着的字段的。即正确的断句是SELECT DISTINT| filed1, filed2...而非SELECT| DISTINT filed1,| filed2...

日期相关函数

GETDATE()        # 获取当前日期
NOW()            # 获取当前时间
YEAR(date)       # 获取date的年份
MONTH(date)      # 获取date的月份
DAY(date)        # 获取date的日期

8.并发

事务具有原子性、一致性、隔离性、持续性的特点。原子性说明事务是一个整体，是若干条指令，但这些指令不可分开，要么都做，要么都不做，不可分割。

并发会带来丢失更新、不可重复读、读脏数据等问题。

通过封锁协议（S、X封锁，两段锁协议）形成死锁来解决。

何为不可重复读：题目中通常会有“验算错误”的提示。

封锁：部分文献会描述为“释放”之类的用语。

X封锁，排他，不可加其他锁，只允许指定事务读取和修改数据对象；

S封锁，共享，不可加排他锁，只允许指定事务读取数据对象。（详见“操作系统-死锁”）

9.完整性约束

实体完整性：主属性不可以为空值，且需要唯一

参照完整性：外键如果存在，就需要保证正确，明确索引

用户定义完整性：根据语义设置约束条件

触发器：不便于直接约束的，通过编写脚本，形成触发器控制