数据库经典笔试题总结

1，范式

7大范式：1NF, 2NF,3NF,BCNF,4NF,5NF,6NF

什么叫normalization？Denormalization?

Normalization是数据库规范化，denormalization是数据库逆规范化。

在设计和操作维护数据库时，关键的步骤就是要确保数据正确地分布到数据库的表中。数据分析师使用正确的数据结构，不仅便于对数据库进行相应的存取操作，而且可以极大地简化应用程序的其他内容（查询、窗体、报表、代码等）。正确进行表设计的正式名称就是”数据库规范化”。目的：减少数据库中数据冗余，增进数据的一致性。

范式概念：

1）1NF：目标就是表中每列都不可分割；
2）2NF：目标就是表中的每行都是有标识的。前提是满足了1NF. 当关键字为单field时，一定满足2NF。当关键字为组合field时（即超过一个field）,不能存在组合关键字中有某个字段能够决定非关键字段的某部分。非主field非部分依赖于主field，即非关键字段必须完全依赖于一组 组合关键字，而不是组合关键字的某一部分。
3）3NF：目标是一个table里面所有的列不依赖于另外一个table里面非关键的列。前提是满足了2NF，不存在某个非关键字段决定另外一个非关键字段。即：不存在传递依赖（关键字x->非关键属性y->非关键属性z）
4）BCNF：前提是满足了2NF，不存在某个非关键字段决定另外一个非关键字段。也不存在某个关键字段决定另外一个关键字段。即：在3NF基础上，加上约束：不存在某个关键字段决定另外一个关键字段。
1 第一范式（1NF）
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。例如，对于图3-2 中的员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。
2 第二范式（2NF）
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如图3-2 员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。
3 第三范式（3NF）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。
例子：
第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
例如，如下的数据库表是符合第一范式的：字段1 字段2 字段3 字段4
而这样的数据库表是不符合第一范式的：字段1 字段2 字段3 字段4 字段31字段32
很显然，在当前的任何关系数据库管理系统（S）中，傻瓜也不可能做出不符合第一范式的数据库，因为这些S不允许你把数据库表的一列再分成二列或多列。因此，你想在现有的S中设计出不符合第一范式的数据库都是不可能的。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
假定选课关系表为Ss(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
　　(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
　　这个数据库表不满足第二范式，因为存在如下决定关系：
　　(课程名称) → (学分)
　　(学号) → (姓名, 年龄)
　　即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题：1) 数据冗余：同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了门课程，姓名和年龄就重复了-1次。2) 更新异常：若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。3) 插入异常：假设要开设一门新的课程，暂时还没有人选修。由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。4) 删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
　　把选课关系表Ss改为如下三个表：
　　学生：Sn(学号, 姓名, 年龄)；
　　课程：s(课程名称, 学分)；
　　选课关系：Ss(学号, 课程名称, 成绩)。
　　这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。
　　另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在”A → → “的决定关系，则传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y
　　假定学生关系表为Sn(学号, 姓名, 年龄, 所在[]学院[], 学院地点, 学院电话)，关键字为单一关键字”学号”，因为存在如下决定关系：
　　(学号) → (姓名, 年龄, 所在[]学院[], 学院[]地点, []学院[]电话)
　　这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：
　　(学号) → (所在[]学院[]) → ([]学院[]地点, []学院[]电话)
　　即存在非关键字段”[]学院[]地点”、”[]学院[]电话”对关键字段”学号”的传递函数依赖。
　　它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。
　　把学生关系表分为如下两个表：
　　学生：(学号, 姓名, 年龄, 所在[]学院[])；
　　[]学院[]：([]学院[], 地点, 电话)。
　　这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。
鲍依斯-科得范式（BCNF）：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BCNF.
假设仓库管理关系表为Ssanag(仓库, 存储物品, 管理员, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
　　(仓库, 存储物品) →(管理员, 数量)
　　(管理员, 存储物品) → (仓库, 数量)
　　所以，(仓库, 存储物品)和(管理员, 存储物品)都是Ssanag的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
　　(仓库) → (管理员)
　　(管理员) → (仓库)
　　即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：1) 删除异常：当仓库被清空后，所有”存储物品”和”数量”信息被删除的同时，”仓库”和”管理员”信息也被删除了。2) 插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员。3) 更新异常：如果仓库换了管理员，则表中所有行的管理员都要修改。
　　把仓库管理关系表分解为二个关系表：
　　仓库管理：Ssanag(仓库, 管理员)；
　　仓库：Ss(仓库, 存储物品, 数量)。
　　这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。
简言之数据库五大范式：
第一范式：对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.
（第一范式是通过把重复的组放到每个独立的表中，把这些表通过一对多关联联系起来这种方式来消除重复组的）
第二范式：第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.（第二范式处理冗余数据的删除问题。当某张表中的信息依赖于该表中其它的不是主键部分的列的时候，通常会违反第二范式）
第三范式：第三范式要求非主键列互不依赖.（第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键）
第四范式：第四范式禁止主键列和非主键列一对多关系不受约束
第五范式：第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余。

2，索引：

什么叫 revised key index?

反键索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用.

例子：有一个字段id，他的值落在一个很小的区间，比如从9000-9999，如果建b-tree索引，那么值过于紧密，反键的原理是把值取反，那么id的区间就从0009-9999，区间就被放大，这个时候通过索引来查找数据效率会比较高（oracle这么说的）。  

好处是:解决了树的倾斜问题，而且可以解决在大量IO操作的情况下,防止硬盘在某个区域操作过于频繁,引起”热点”问题。

树的分支：因为索引一般是按树这个数据结构来组织，所以有很多分支，把不同类别或范围的数据存放在分支里，在符合条件的分支里查询比在全表查询效率高很多。

树的倾斜：树的某个分支过与庞大,而其他分支内容却很少，这样的索引非常不健康的，查询速度也很慢，如上面的示例数据，都在10000-20000 的分支，而20000-30000或者以上的分支是空的。反转后把这些数据均匀分布到不同的分支，可以使索引更加健康，也更有效率。

热点问题：由于系统在表数据的增删改查的同时，同时要承担索引开支，而这主要是硬盘的IO操作，如果树是倾斜的，而且数据的增加是按一定顺序增长的，这种情况会导致硬盘对某一固定区域操作频繁，会出现热点问题，而且出现瓶颈。

Oracle五种索引：

1）b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。 B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址。

2）反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用。

3）降序索引：8i中新出现的索引类型，针对逆向排序的查询。

4）位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。位图以一种压缩格式存放，因此占用的磁盘空间比B-Tree索引要小得多。

5）函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function(column)=value这种类型的语句中起作用。基于函数的索引也是8i以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件：

（1）必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。

（2）必须使用基于成本的优化器，基于规则的优化器将被忽略。

（3）必须设置以下两个系统参数：

QUERY_REWRITE_ENABLED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

可以通过alter system set,alter session set在系统级或线程级设置，也可以通过在init.ora添 加实现。

五种索引的创建：

（1）*Tree索引。

Create index indexname on tablename(columnname[columnname...])

（2）反向索引。

Create index indexname on tablename(columnname[columnname...]) reverse

（3）降序索引。

Create index indexname on tablename(columnname DESC[columnname...])

（4）位图索引。

Create BITMAP index indexname on tablename(columnname[columnname...])

（5）函数索引。

Create index indexname on tablename(functionname(columnname))

注意：创建索引后分析要索引才能起作用。

五种索引的使用场所：

（1）B*Tree索引。

常规索引，多用于oltp系统，快速定位行，应建立于高cardinality列（即列的唯一值除以行数为一个很大的值，存在很少的相同值）。

（2）反向索引。

B*Tree的衍生产物，应用于特殊场合，在ops环境加序列增加的列上建立，不适合做区域扫描。

（3）降序索引。

B*Tree的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。

（4）位图索引。

位图方式管理的索引，适用于OLAP（在线分析）和DSS（决策处理）系统，应建立于低cardinality列，适合集中读取，不适合插入和修改，提供比B*Tree索引更节省的空间。

（5）函数索引。

B*Tree的衍生产物，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

索引不管用的时候：

（1）RBO&CBO。

Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.

（2）AUTOTRACE。

要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：

① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。

② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。

③ AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

３，死锁

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

产生死锁的原因主要是：

（1） 因为系统资源不足。

（2） 进程运行推进的顺序不合适。

（3） 资源分配不当等。

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。

（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。

（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

例子：

运行事务 1 的线程 T1 具有学生基本信息表上的排它锁。运行事务2的线程 T2 具有系部表上的排它锁，并且之后需要学生基本信息表上的锁。事务2 无法获得这一锁，因为事务 1 已拥有它。事务2 被阻塞，等待事务 1。然后，事务1 需要系部表的锁，但无法获得锁，因为事务 2 将它锁定了。事务在提交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续操作，所以它们不能提交或回滚。

4，BYTE[] buf = BYTE[1024];in.read(buf)；

in是一个接收图像数据的网络IO流，请指出这段代码有什么问题，并请用java代码改进它。

答：流操作都可能会跑出IOException，应该对该异常进行捕获处理。且当buf没有被初始化的时候使用会抛出NullPointerException。

byte [] buf = new byte[1024];

try {

         System.in.read(buf);

} catch (IOException e) {           

         e.printStackTrace();

}

5，设计模式：Facade

你正在分析一个子系统的接口，发现接口很多。然后你同事劝你用Fecade, 问你用Fecade有什么好处？

Facade（外观）模式为子系统中的各类（或结构与方法）提供一个简明一致的界面，隐藏子系统的复杂性，使子系统更加容易使用。Facade模式正是这样一个“门面”：我们本来需要与后台的多个类或者接口打交道，而Facade模式是客户端和后台之间插入一个中间层——门面，这个门面跟后台的多个类或接口打交道，而客户端只需要跟门面打交道即可。使用Facade模式可以说是后台设计和编码人员的一个必备素质。我不止碰到过一个这样的后台开发人员，他们认为只要把后台功能完成了就万事大吉，而没有站在后台使用者的角度来看一看自己写出来的代码。其实，我们写出来的后台代码是要给别人使用的，所以我们提供给使用者的接口要越简单越好，这不单是对使用者好，同时对开发者也是好处多多的，至少你的接口简单了，你和使用者的交流就容易了。

区分Façade模式、Adapter模式、Bridge模式与Decorator模式。Façade模式注重简化接口，Adapter模式注重转换接口，Bridge模式注重分离接口（抽象）与其实现，Decorator模式注重稳定接口的前提下为对象扩展功能

在遇到以下情况使用Facade模式：

1）当你要为一个复杂子系统提供一个简单接口时。子系统往往因为不断演化而变得越来越复杂。大多数模式使用时都会产生更多更小的类。这使得子系统更具可重用性，也更容易对子系统进行定制，但这也给那些不需要定制子系统的用户带来一些使用上的困难。　Facade可以提供一个简单的缺省视图，这一视图对大多数用户来说已经足够，而那些需要更多的可定制性的用户可以越过Facade层。

2）客户程序与抽象类的实现部分之间存在着很大的依赖性。引入Facade将这个子系统与客户以及其他的子系统分离，可以提高子系统的独立性和可移植性。

3）当你需要构建一个层次结构的子系统时，使用Facade模式定义子系统中每层的入口点，如果子系统之间是相互依赖的，你可以让它们仅通过Facade进行通讯，从而简化了它们之间的依赖关系。

优缺点：

1）它对客户屏蔽子系统组件，因而减少了客户处理的对象的数目并使得子系统使用起来更加方便。

2）它实现了子系统与客户之间的松耦合关系，而子系统内部的功能组件往往是紧耦合的。

松耦合关系使得子系统的组件变化不会影响到它的客户。Facade模式有助于建立层次结构系统，也有助于对对象之间的依赖关系分层。Facade模式可以消除复杂的循环依赖关系。这一点在客户程序与子系统是分别实现的时候尤为重要。在大型软件系统中降低编译依赖性至关重要。在子系统类改变时，希望尽量减少重编译工作以节省时间。用Facade可以降低编译依赖性，限制重要系统中较小的变化所需的重编译工作。Facade模式同样也有利于简化系统在不同平台之间的移植过程，因为编译一个子系统一般不需要编译所有其他的子系统。数据分析师认证

6，冷备份与热备份

冷备份：

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份Oracle信息而言，冷备份是最快和最安全的方法。

冷备份的优点是：

1．是非常快速的备份方法（只需拷贝文件）

2．容易归档（简单拷贝即可）

3．容易恢复到某个时间点上（只需将文件再拷贝回去）

4．能与归档方法相结合，作数据库“最新状态”的恢复。

5．低度维护，高度安全。

冷备份也有如下不足：

1．单独使用时，只能提供到“某一时间点上”的恢复。

2．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

3．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。

4．不能按表或按用户恢复。

如果可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将所备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。

冷备份中必须拷贝的文件包括：

1．所有数据文件

2．所有控制文件

3．所有联机REDO LOG文件

4．Init.ora文件（可选）。

下面是做冷备份的完整例子：

（1） 关闭数据库＄sqldba lmode=y

SQLDBA >connect internal;

SQLDBA >shutdown normal;

（2） 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件

SQLDBA >! cp < file > < backup directory >

（3） 重启Oracle数据库

＄sqldba lmode=y

SQLDBA >connect internal;

SQLDBA >startup;

热备份

热备份是在数据库运行的情况下，采用archivelog mode方式备份数据的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。

热备份的要求是：

1. 热备份工作必需要求数据库在Archivelog 方式下操作,在SQLDBA状态下用alter database archivelog|noarchivelog命令可改变备份的模式。数据分析师培训