软考备战-数据库篇

三级模式-两级映像

三级模式

内模式

管理如何存储物理数据，对应具体物理存储文件。

模式（概念模式）

即使用的基本表，根据应用、需求将物理数据划分成一张张表。

外模式

对应数据库中的视图，将表进行一定的处理后再提供给用户使用。

两级映像

外模式-模式映像

表和视图之间的映射（概念模式和外模式之间），表中数据发生修改只需修改此映射，无需修改应用程序。

模式-内模式

表和数据的物理存储之间的映射（概念模式和内模式之间），修改了数据存储方式只需修改此映射，无需修改应用程序。

数据库设计

设计步骤

需求分析

分析数据存储的要求，产出物有数据流图、数据字典、需求说明书。

概念结构设计

设计E-R图（实体-属性图），与物理实现无关，说明有哪些实体，实体有哪些属性。

逻辑结构设计

将E-R图转换为关系模式（即实际的表和表中的列属性），考虑很多规范化的东西。

物理设计

根据生成的表等概念，生成物理数据库。

数据模型

数据模型三要素：数据结构（所研究的对象类型的集合）、数据操作（对数据库中各种对象的实例允许执行操作的集合）、数据的约束条件（一组完整性规则的集合）

E-R模型（实体-联系模型）

椭圆--属性、长方形--实体、菱形--联系（联系两端标注联系类型）1:1 1:N M:N

关系模型

即数据库中常用的表，包括实体的属性、实体的主键和外键。

模型转换

E-R图转换为关系模式：每个实体都对应一个关系模式，分三种联系：

①1:1联系：

a) 将1:1联系转换为一个独立的关系：与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，且每个实体的码均是该关系的候选码。
b) 将1:1联系与某一端实体集所对应的关系合并，则需要在被合并关系中增加属性，其新增的属性为联系本身的属性和与联系相关的另一个实体集的码。

职工表（职工号，姓名，年龄）主码：职工号

产品表（产品号，产品名，价格）主码：产品号

负责（职工号，产品号）主码：职工号或产品号

合并方案1：“负责”与“职工”两关系合并：

    职工（职工号，姓名，年龄，产品号）

    产品（产品号，产品名，价格）

合并方案2：“负责”与“产品”两关系合并：

    职工（职工号，姓名，年龄）

    产品（产品号，产品名，价格，职工号）

②1:N联系：

a)一种方法是将联系转换为一个独立的关系，其关系的属性由与该联系相连的各实体集的码以及联系本身的属性组成，而该关系的码为n端实体集的码；

 b)另一种方法是在n端实体集中增加新属性，新属性由联系对应的1端实体集的码和联系自身的属性构成，新增属性后原关系的码不变。

仓库（仓库号，地点，面积）

 主码：仓库号

产品（产品号，产品名，价格）

 主码：产品号

仓储（仓库号，产品号，数量）主码：产品号

合并后方案：联系形成的关系与n端对象合并。

 仓库（仓库号，地点，面积）

③M:N联系：

在向关系模型转换时，一个m:n联系转换为一个关系。转换方法为：与该联系相连的各实体集的码以及联系本身的属性均转换为关系的属性，新关系的码为两个相连实体码的组合（该码为多属性构成的组合码）。

该模型包含两个实体集（学生、课程）和一个m:n联系

该模型可转换为三个关系模式：

–学生（学号，姓名，性别，年龄）主码：学号

-课程（课程号，课程名，学分）主码：课程号

–选课（学号，课程号，成绩）主码：学号+课程号

关系代数运算

并

结果是两张表中的记录合并，相同的记录只显示一次。

交

结果是两张表中相同的记录。

差

S1-S2，结果是S1中有但S2中没有的记录。

笛卡尔积

S1*S2，结果包括S1和S2的所有属性列，且S1中的每条记录和S2中的所有记录组合成一条记录。

最终属性列为S1+S2的属性列，最终记录数为S1*S2的记录数。

投影

按条件选择某关系模式中的某列。

选择

按条件选择某关系模式中的某条记录。

自然连接

结果显示所有属性列，相同的属性列只显示一次（显示两个关系模式种，属性相同且值相同的记录）

范式

函数依赖

部分函数依赖

A->C, (A,B)->C

传递函数依赖

A->B,B->C,A不等价于B

键

超键/超码

能标识此表的属性的组合

候选键（码）

超键中去掉冗余的属性，剩余的属性就是候选键

主键

任选一个候选键，既可作为主键

外键

其他表中的主键

主属性

候选键内的属性为主属性，其余属性为非主属性

约束

实体完整性约束（主键约束）

主键值不能为空，也不能重复

参照完整性约束（外键约束）

外键必须是其他表中存在的主键的值，或者为空

用户自定义完整性约束

自定义表达式约束（eg、年龄值必须在1-50之间）

第一范式1NF

关系中每一个分量都必须是不可再分的数据项（表中不允许有小表）

如下学生信息表(student)：

id、name(姓名)、sex_code(性别代号)、sex_desc(性别描述)、contact(联系方式)

primary key(id)

如果在查询学生表时经常用到学生的电话号，则应该将联系方式(contact)这一列分为电话号(phone)和地址(address)两列，这样才符合第一范式。

修改使表满足1NF后：

判断表是否符合第一范式，列是否可以再分，得看需求，如果将电话号和地址分开才能满足查询等需求时，那之前的表设计就是不满足1NF的，如果电话号和地址拼接作为一个字段也可以满足查询、存储等需求时，那它就满足1NF。

第二范式2NF

①满足第一范式②不存在部分依赖（即非主属性必须完全依赖于主属性）

(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

如下学生成绩表(score)：

stu_id(学生id)、kc_id(课程id)、score(分数)、kc_name(课程名)

primary key(stu_id, kc_id)

表中主键为stu_id和kc_id组成的联合主键。满足1NF；非主键列score完全依赖于主键，stu_id和kc_id两个值才能决定score的值；而kc_name只依赖于kc_id，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。

修改使表满足2NF后：

成绩表(score)   primary key(stu_id)

课程表(kc)   primary key(kc_id)

将原来的成绩表(score)拆分为成绩表(score)和课程表(kc)，而且两个表都符合2NF。

第三范式3NF

①满足第二范式②不存在传递依赖（非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（eg、A -> B, B ->C, A -> C））

如下学生信息表(student)：

primary key(id)

表中sex_desc依赖于sex_code，而sex_code依赖于id(主键)，从而推出sex_desc依赖于id(主键)；sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。

修改表使满足3NF后：

学生表(student)   primary key(id)

性别代码表(sexcode)   primary key(sex_code)

将原来的student表进行拆分后，两个表都满足3NF。

BC范式

要满足BC范式，需要先满足第三范式。

只要属性或属性组A能够决定任何一个属性B，则A的子集中必须有候选键（或者说A必须为超键）。

属性A决定（确定）属性B，
即是 A ----> B , 表示 B依赖于A
那么， A（或A的子集）一定要有候选关键字

如上表，将 学号 + 课题 设计为组合主键
由于课题编号->课程，所以要满足BC范式，那么课程编号必须为候选关键字，由表可知课程编号并不是候选关键字，所以违反了 BC范式。

正确示例

模式分解 

范式之间的转化一般都是通过拆分属性，即模式分解。将具有部分函数依赖和传递依赖的属性分离出来，来达到一步步优化，分两种：

保持函数依赖分解

对于关系模式R，有依赖集F，若对R进行分解，分解出来的多个关系模式保持原来的依赖集不变，则为保持函数依赖的分解。此外，要消除掉冗余依赖（如传递依赖）

实例:设原关系模式R(A,B,C),依赖集F(A->B, B->C,A->C),

将其分解为两个关系模式R1(A,B)和R2(B,C), 此时R1中保持依赖A->B,R2保持依赖B->C,

说明分解后的R1和R2是保持函数依赖的分解,因为A->C这个函数依赖实际是一个冗余依赖,可以由前两个依赖传递得到,因此不需要管。

无损分解

分解后的关系模式能够还原出原关系模式，就是无损分解。不能还原就是有损分解。

判定方法分两种：

①表格法

STEP 1

 分解后关系模式所具有的属性 打√ 不具有 打×

STEP 2 看函数依赖

 学号->姓名，学号、姓名都在学生关系模式中， 所以 成绩->姓名

课程号->课程名，课程号、课程名都在课程关系模式中， 所以 成绩->课程名

②定理

如果R的分解为p={R1,R2},F为R所满足的函数依赖集合,分解具有无损连接性的充分
必要条件是R1nR2->(R1-R2)或者R1nR2->(R2-R1)。
 

事务管理

事务提交commit 事务回滚 rollback

1、事务：由一系列操作组成，这些操作要么全做，要么全不做。

2、事务的四种特性：

①操作原子性：要么全做，要么全不做。

②数据一致性：事务发生后数据是一致的。（eg、银行转账，不存在从A转出B没有收到的情况）

③执行隔离性：任一事务的更新操作直到其成功提交的整个过程，对其他事务不可见。不同事务之间是隔离的，互不干涉。

④改变持续性：事务操作的结果是持续的。

并发控制

事务是并发控制的前提条件。

并发控制即控制不同的事务并发执行，提高系统效率。

并发控制中存在如下三个问题：

①丢失更新：

事务1对数据A进行修改并写回，事务2对A也进行修改并写回，

此时事务2写回的数据会覆盖事务1写回的数据，就丢失了事务1对数据A的更新，即数据A的更新会被覆盖。

②不可重复读：

事务2读数据A，而后事务1对数据A进行了修改并写回，

此时若事务2再读数据A会发现数据不对。即一个事务重复读A两次，会发现数据有误。

③读脏数据：

事务1对数据A修改后，事务2读数据A，而后事务1回滚，数据A恢复原来的数值，那么事务2对数据A做的事是无效的，读到了脏数据。

封锁协议

X锁是排它锁（写锁）：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务都不能对A加任何类型的锁，直到T释放A上的锁。

S锁是共享锁（读锁）：若事务T对对象A加上S锁，则只允许T读取A，但不能修改A，其他事务只能再对A加S锁，直到T释放A上的S锁。

①一级封锁协议

事务在修改数据R之前必须先对其加X锁，直到事务结束才释放。

可解决数据丢失更新问题。

②二级封锁协议

一级封锁协议的基础上加 事务T在读数据R之前必须先对其加S锁，读完后即可释放S锁。

可解决丢失更新、读脏数据的问题。

③三级封锁协议

一级封锁协议的基础上加 事务T在读数据R之前必须先对其加S锁，直到事务结束才可释放。

可解决丢失更新、读脏数据、数据重复读问题。、

数据库安全

安全措施和级别

数据故障

 数据库备份

静态转储：即冷备份，在转储期间不允许对数据库进行任何存取、修改操作。

        优点：快速备份、易归档（直接物理复制操作）

        缺点：只能提供到某一时间点上的恢复，不能做其他工作、不能按表或按用户恢复。

动态转储：即热备份，在转储期间允许对数据库进行存取、修改操作（转储和用户事务可并发执行）

        优点：可在表空间或数据库文件级备份，数据库仍可使用，可达到秒级恢复。

        缺点：不能出错，否则后果很严重。若热备份不成功，所得结果几乎全部无效。

完全备份：备份所有数据。

差量备份：仅备份上一次完全备份之后变化的数据。

增量备份：备份上一次备份之后变化的数据。

日志文件：在事务的处理过程中，DBMS把事务开始、结束、对数据库的插入、修改、删除的每一次操作写入日志文件。一旦发生故障，DBMS的恢复子系统利用日志文件撤销事务对数据库的改变，回退到事务的初始状态。

分布式数据库

局部数据库位于不同物理位置，使用一个全局DBMS将所有局部数据库联网管理，体系结构如图：

分片模式

①水平分片

将表中水平的记录分别存放在不同的地方

②垂直分片

将表中垂直的列值分别存放在不同的地方

分布透明性

①分片透明性

用户或应用程序不需要知道逻辑上访问的表具体如何分块存储

②位置透明性

应用程序不关心数据存储物理位置的改变

③逻辑透明性

用户或应用程序无需知道局部使用的是哪种数据模型

④复制透明性

用户或应用程序不关心复制的数据从何而来

数据仓库

数据仓库

数据仓库是一种特殊的数据库，按数据库形式存储数据，但目的不同。

数据库经过长时间运行里面保存的数据会越来越多，影响系统运行效率。对某些系统来说，很久之前的数据并非必要的，可以删掉减少数据，增加效率。考虑删掉这些数据比较可惜，一般将这些数据从数据库提取出来保存到另一个数据库中，称为数据仓库。

数据仓库不是为了，是面向主题的，用来做数据分析，集成不同的表，而且相对稳定，一般不会做出修改，同时会在特定的时间点做大量的插入，反应历史的变化。

数据仓库的形成如下：

数据挖掘 

数据仓库有两个作用：

①用来做数据的查询、分析、生成报表

②使用数据挖掘工具对这些历史数据进行挖掘，查找数据间的关系，发掘剩余价值。

数据挖掘的分析方法：

①关联分析：主要用于发现不同事件之间的关联性（即一个事件发生的同时，另一个时间也经常发生）

②序列分析：主要用于发现一定时间间隔内接连发生的事件，这些事件构成一个序列，发现的序列应该具有普遍的意义。

③分类序列：通过分析具有类别的样本特点，得到决定样本属于各种类别的规则或方法。

④聚类分析：通过“物以类聚”的原理，将本身没有类别的样本聚集成不同的组，并且对每个组进行描述的过程。

商业智能

BI系统主要包括数据预处理、建立数据仓库、数据分析和数据展现四个主要阶段。

①数据预处理

整合企业原始数据的第一步，包括数据的 抽取、转换、加载 三个过程。

建立数据仓库是处理海量数据的基础。

SQL语句

DDL（数据定义语言）

操作库

-- 创建库
create database db1;

-- 创建库是否存在，不存在则创建
create database if not exists db1;

-- 查看所有数据库
show databases;

-- 查看某个数据库的定义信息 
show create database db1; 

-- 修改数据库字符信息
alter database db1 character set utf8; 

-- 删除数据库
drop database db1; 

操作表

--创建表
create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);
 
-- 查看表结构
desc 表名;

-- 查看创建表的SQL语句
show create table 表名;

-- 修改表名
alter table 表名 rename to 新的表名;

-- 添加一列
alter table 表名 add 列名 数据类型;

-- 删除列
alter table 表名 drop 列名;

-- 删除表
drop table 表名;
drop table  if exists 表名 ;

DML（数据操作语言 ）

增加 insert into

-- 写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

-- 不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);

-- 插入部分数据
insert into 表名(列名1,列名2) values(值1,值2); 

 删除 delete

-- 删除表中数据
delete from 表名 where 列名  = 值;

-- 删除表中所有数据
delete from 表名;

-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

修改 update 

-- 不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;

-- 带条件的修改
update 表名 set 列名 = 值 where 列名=值;

 DQL（数据查询语言）

排序查询、聚合函数、模糊查询、分组查询、分页查询、内连接、外连接、子查询

基础关键字

BETWEEN...AND...  、 IN( 集合)

-- 查询年龄大于等于20 小于等于30                
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
                
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

 is not null(不为null值) 、 like（模糊查询）、distinct（去除重复值） 

-- 查询英语成绩不为null
SELECT * FROM student WHERE english  IS NOT NULL;
    
 _:单个任意字符
 %：多个任意字符
-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的人            
SELECT * FROM student WHERE NAME LIKE "_化%";                
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';                    
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
 
-- 关键词 DISTINCT 用于返回唯一不同的值。
-- 语法：SELECT DISTINCT 列名称 FROM 表名称
SELECT DISTINCT NAME FROM  student ; 

 排序查询 order by

SELECT * FROM person ORDER BY math; --默认升序
SELECT * FROM person ORDER BY math desc; --降序

聚合函数 

将一列数据作为一个整体，进行纵向的计算 

1.count：计算个数

2.max：计算最大值

3.min：计算最小值

4.sum：计算和

5.avg：计算平均数

分组查询 group by

-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
 
-- 按照性别分组。分别查询男、女同学的平均分,人数
 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
 
--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
 
 --  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2; 

多表查询

1、内连接（查两张表交集部分）

①隐式内连接

SELECT 字段列表 FROM 表1，表2 WHERE 条件（表1.id=表2.id）；

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
 
SELECT 
    t1.name, -- 员工表的姓名
    t1.gender,-- 员工表的性别
    t2.name -- 部门表的名称
FROM
    emp t1,
    dept t2
WHERE 
    t1.`dept_id` = t2.`id`;

②显示内连接

-- 语法： 
select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 例如：
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;   
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`; 

2、外连接

①左外连接 -- 查询的是左表所有数据＋左右表交集部分

-- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT  t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

②右外连接  -- 查询的是右表所有数据＋左右表交集部分

-- 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；
-- 例子：
SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

子查询

-- 查询工资最高的员工信息
-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
 
-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.`salary` = 9000;
 
 -- 一条sql就完成这个操作。这就是子查询
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

1、子查询的结果是单行单列

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp); 

2、子查询的结果是多行单列

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
 
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'); 

3、子查询的结果是多行多列

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
 
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'