数据库面试知识总结

1 / 79 数据库面试知识总结 【数据库基础知识总结】 1 / 15 1. 数据抽象：物理抽象、概念抽象、视图级抽象，内模式、模式、外模式 提示： (1). 概念 模式： (面向单个用户的 ) 是数据中全部数据的整体逻辑结构的描述。它由若干个概念记录类型组成。 (2). 外模式： (面向全局的 ) 是用户与数据库系统的接口，是用户用到的那部 分数据的描述。它由若干个外部记录类型组成。 (3). 内模式： (面向存储的 ) 是数据库在物理存储方面的描述，它定义所有的内部记录类型、索引、和文件的组织方式，以及数据控制方面的细节。 2 / 79 模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。对应与同一个模式可以有任意多个外模式。在数据库中提供两级映像功能，即外模式 /模式映像和模式 /内模式映像。对于没一个外模式，数据库系统都有一个外模式 /模式映像它定义了该外模式与模式之间的对应关系。这些映像定义通常包括在各自外模式的描述中，当模式改变时，由数据库管理员对各个外模式 /模式的映像做相应改变，可以使外模式保持不变，从而应用程序不必修改，保证了数据的逻辑独立性。数据库中只有一个模式，也只有一个内模式，所以模式 /内模式映像是唯 一的，它定义了数据全局逻辑结构与存储结构之间的对应关系。当数据库的存储结构改变了，由数据库管理员对模式 /内模式映像做相应改变，可以使模式保持不变，从而保证了数据的物理独立性。 2. SQL语言包括数据定义、数据操纵 (Data Manipulation)，数据控制 (Data Control) 数据定义： Create Table， Alter Table， Drop Table， Craete/Drop Index 等 数据操纵： Select ， insert， update， delete， 3 / 79 数据控制： grant， revoke 3. SQL 常用命令 CREATE TABLE Student( ID NUMBER PRIMARY KEY， NAME VARCHAR2(50) NOT NULL);/建表 CREATE VIEW view_name AS Select * FROM Table_name;/建视图 Create UNIQUE INDEX index_name ON TableName(col_name);/建索引 INSERT INTO tablename column1 ， column2 ， ? values(exp1， exp2， ?);/插入 INSERT INTO Viewname column1， column2， ? values(exp1，exp2， ?);/插入视图实际影响表 UPDATE tablename SET name= zang 3 condition;/更新数据 4 / 79 DELETE FROM Tablename WHERE condition;/删除 GRANT (Select， delete， ?) ON (对象 ) TO USER_NAME WITH GRANT OPTION;/授权 REVOKE (权限表 ) ON(对象 ) FROM USER_NAME WITH REVOKE OPTION /撤权 列出工作人员及其领导的名字： Select ， FROM EMPLOYEE E S WHERE = 4. 视图 提示： 计算机数据库中的视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的 【数据库基础知识总结】 2 / 15 查询所引用的表，并且在引用视图时动态生成。 从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数5 / 79 据。从数据库系统内部来看，一个视图是由 SELECT 语句组 成的查询定义的虚拟表。从数据库系统内部来看， 视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。 5. 完整性约束：实体完整性、参照完整性、用户定义完整性 提示： 定义：关系完整性是为保证数据库中数据的正确性和相容性，对关系模型提出的某种约束条件或规则。完整性通常包括域完整性，实体完整性、参照完整性和用户定义完整性，其中域完整性，实体 完整性和参照完整性，是关系模型必须满足的完整性约束条件。 (1). 域完整性约束：域完整性是保证数据库字段取值的合理性。属性值应是域中的值，这是关系模式规定了的。除此之外，一个属性能否为 NULL，这是由语义决定的，也是域完整性约束的主要内容。域完整性约束是最简单、最基本的约束。在当今的关系 DBMS 中，一般都有域完整性约束检查功能。包括检查 (CHECK)、默认值 (DEFAULT)、不为空 (NOT NULL)等。 6 / 79 (2). 实体完整性 实体完整性是指关系的主关键字不能重复也不能取 空值 。 一个关系对应现实世界中一个实体集。现实世界中的实体是可以相互区分、识别的，也即它们应具有某种惟一性标识。在关系模式中，以主关键字作为惟一性标识，而主关键字中的属性 (称为主属性 )不能取空值，否则，表明关系模式中存在着不可标识的实体 (因空值是 不确定 的 )，这与现实世界的实际情况相矛盾，这样的实体 就不是一个完整实体。按实体完整性规则要求，主属性不得取空值，如主关键字是多个属性的组合，则所有主属性均不得取空值。 如表 1 1 将编号作为主关键字，那么，该列不得有空值，否则无法对应某个具体的职工，这样的表格不完整，对应关系不符合实体完整性规则的约束条件。 (3). 参照完整性 参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。关系数据库中通常都包含多个存在相互联系 的关系，关系与关系之间的联系是通过公共属性来实现的。所谓公共属性，它是一个关系R(称为被参照关系或目标关系 )的主关键字，同时又是另一关系 K(称为参照关系 )的外部关键字。如果参照关系 K 中外部关键字的取值，要么与被参照关系 R中某元组主关键字的值相同，要么取空值，那么，在这两个关系间建立关联的主7 / 79 关键字和外部关键字引用，符合参照完整性规则要求。如果参照关系 K的外部关键字也是其主关键字，根据实体完整性要求，主关键字不得取空值，因此，参照关系 K外部关键字的取值实际上只能取相应被参照关系 R中已经存在的主关键字值。 在学生管理数据库中，如果将选课表作为参照关系，学生表作为被参照关系，以 学号 作为两个关系进行关联的属性，则 学号 是学生关系的主关键字，是选课关系的外部关键字。选课关系通过外部关键字 学号 参照学生关系。 (4). 用户定义完整性 实体完整性和参照完整性适用于任何关系型数据库 系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束。用户定义完整性则是根据应用环境的要求和实际的需要，对某一具体应用所涉及的数据提出约束性条件。这一约束机制一般不应由应用程序提供，而应有由关系模型提供定义并检验，用户定义完整性主要包括字段有效性约束和记录有效性。 【数据库基础知识总结】 3 / 15 8 / 79 6. 第三范式： 提示： (1).第一范式 (1NF)：在关系模式 R 中的每一个具体关系 r中，如果每个属性值都是不可再分的最小数据单位，则称 R是第一范式的关系。 例：如职工号，姓名，电话号码组成一个表 (一个人可能有一个办公室电话和一个家里电话号码 )规范 成为 1NF 有三种方法： 一是重复存储职工号和姓名。这样，关键字只能是电话号码。 二是职工号为关键字，电话号码分为单位电话和住宅电话两个属性 三是职工号为关键字，但强制每条记录只能有一个电话号码。 以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。 (2).第二范式 (2NF)：如 果关系模式 R(U， F)中的所有非主属性都完全依赖于任意一个候选关键字，则称关系 R 是属于第二范式的。 例：选课关系 SCI(SNO， CNO， GRADE， CREDIT)其中 SNO 为学号， CNO 为课程号， GRADEGE 为成绩， CREDIT 为学分。由以上条件，关键字为组合关键字 (SNO， CNO) 在应用中使用以上关系模式有以下问题： a.数据冗余，假设同一门课9 / 79 由 40个学生选修，学分就重复 40次 。 b.更新异常，若调整了某课程的学分，相应的元组 CREDIT 值都要更新，有可能会出现同一门课学分不同。 c.插入异常，如计划开新课，由于没人选修，没有学号关键字，只能等有人选修才能把课程和学分存入。 d.删除异常，若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保存。 原因：非关键字属性 CREDIT 仅函数依赖于 CNO，也就是CREDIT 部分依赖组 合关键字 (SNO， CNO)而不是完全依赖。 解决方法：分成两个关系模式 SC1(SNO， CNO， GRADE)，C2(CNO， CREDIT)。新关系包括两个关系模式，它们之间通过 SC1中的外关键字 CNO 相联系，需要时再进行自然联接，恢复了原来的关系 (3).第三范式 (3NF)：如果关系 模式 R(U， F)中的所有非主属性对任何候选关键字都不存在传递信赖，则称关系 R 是属于第三范式的。 例：如 S1(SNO， SNAME， DNO， DNAME， LOCATION)各属性分别代表学号， 姓名，所在系，系名称，系地址。 10 / 79 关键字 SNO决定各个属性。由于是单个关键字，没有部分依赖的问题， 肯定是 2NF。但这关系肯定有大量的冗余，有关学生所在的几个属性 DNO， DNAME， LOCATION 将重复存储，插入，删除和修改时也将产生类似以上例的情况。 原因：关系中存在传递依赖造成的。即 SNO - DNO。而 DNO - SNO却不存在， DNO - LOCATION，因此关键辽 SNO 对 LOCATION 函数决定是通过传递依赖 SNO - LOCATION 实现的。也就是说， SNO 不直接决定非主属性 LOCATION。 解决目地：每个关系模式中不能留有传递依赖。 解决方法：分为两个关系 S(SNO， SNAME， DNO)， D(DNO， DNAME， LOCATION) 注意：关系 S 中不能没有外关键字 DNO。否则两个关系之间失去联系。 【数据库基础知识总结】 4 / 15 7. ER(实体 /联系 )模型 提示： 实体 -关系模型是面向现实世界，而不是面向实现方法的，它主要是用于 描述现实信息世界中数据的静态特性，而不涉及数据的处理过程。 基本概念： 实体：现实世界中任何可以相互区分的事物。 11 / 79 解释：实体可以是人，也可以是物；可以指实际的对象，也可以指某些概念。例如，一个职工、一个学生、一门课，学生的一些选课。 属性：实体 (或联系 )所具有的某方面特征 (相当于数据库表的字段 (列 )。例如：学生实体，可由学号、姓名、性别、年龄、系、年级。 (9673101，王平，男， 22，计算机系，三年级 )这些属性组合起来就表现了一个学生的情况。 (相当于记录，特性为字段 )。 联系：发生在实体之间具有特定含义的对应关系 PS：实体、属性一般是名词，联系一般是动词 ER图中的四个基本成分： 1.矩形框，表示实体 2.菱形框，表示实体之间的联系 3.椭圆形框，表示实体或联系的属性 4.直线，连接实体、属性、和联系。直线端部标注联系的种类 (1： 1、 1： N 或 M：N) 连个实体 (或表 )之间的联系分为三类： (1). 一对一联系 (1： 1)如果实体集合 A 中的每一个实体，实体集合 B中至少都一个实体与之联系，反之亦然，则称为实体集合 A与实体集合 B 具有一对一联系，记为 1： 1。例如，一个班级有一个班长。 (2). 一对多联系 (1： n)如果实体集合 A 中的每一个实体，12 / 79 实体集合 B 中至少都 n(n=0)个实体与之联系，反之，对于实体集合 B中每一个实体，实体集合 A中的至多有一个实体与之联系，则称为实体集合 A与实体集合 B具有一对多联系，记为 1： n。例如，一个班级有多个 班干部。 (3). 多对多联系 (m： n)如果实体集合 A 中的每一个实体，实体集合 B 中至少有 n(n=0)个实体与之联系，反之，对于实体集合 B 中每一个实体，实体集合 A 中的至少有 m(m=0)个实体与之联系，则称为实体集合 A 与实体集合 B 具有多对多联系，记为 m： n。例如，一个学生可以选修多门课，一门课可以有多个学生选修。 8. 索引的作用 提示： 可以利用索引快速访问数据库表中的特定信息。索引是对数据库表中一个或多个列 (例如， employee 表的姓氏 (lname) 列 )的值进行排序的结构。如果想按特定职员的姓来查找他或她， 则与在表中搜索所有的行相比，索引有助于更快地获取信息。 13 / 79 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。 在数据库关系图中，可以为选定的表创建、编辑或删除索引 /键属性页中的每个索引类型。当保存附加在此索引上的表或包含此表的数据库关系图时，索引同时被保存。有关详细信息，请参见创建索引。 通常情况下，只有 当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。 在创建索引前，必须确定要使用的列和要创建的索引类型。 9. 事务 提示： 数据库事务 (Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使14 / 79 应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的 ACID(原子性、一致性、隔离性和持久性 )属性。 数据库事务的 ACID 特性 (1). 原子性 (atomic)(atomicity) 事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。 (2). 一致性 (consistent)(consistency) 事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构 (如 B 树索引或双向链表 )都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。 (3). 隔离性 (insulation)(isolation) 由并发事务所15 / 79 作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可 并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。防止数据丢失 (4). 持久性 (Duration)(durability) 事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。 10. 数据库中的锁 提示： 在数据库中引入锁的原因： 多个用户同时对数据库的并发操作时会带来以下数据不一致的问题 : (1). 丢失更新： A， B 两个用户读同一数据并进行修改，其中一个用户的修改结果破坏了另一个修改的结果，比如订票系统。 (2). 脏 读： A 用户修改了数据，随后 B用户又读出该数据，16 / 79 但 A用户因为某些原因取消了对数据的修改，数据恢复原值，此时 B得到的数据就与数据库内的数据产生了不一致。 数据库经典笔试题总结 面试穿什么着装合适，这里找答案！ 1，范式 7 大范式： 1NF, 2NF,3NF,BCNF,4NF,5NF,6NF 什么叫 normalization？ Denormalization? Normalization 是数据库规范化， denormalization 是数据库逆 规范化。 在设计和操作维护数据库时，关键的步骤就是要确保数据正确地分布到数据库的表中。使用正确的数据结构，不仅便于对数据库进行相应的存取操作，而且可以极大地简化应用程序的其他内容。正确进行表设计的正式名称就是 数据库规范化 。目的：减少数据库中数据冗余，增进数据的一致性。 范式概念： 17 / 79 1） 1NF：目标就是表中每列都不可分割； 2） 2NF：目标就是表中的每行都是有标识的。前提是满足了1NF. 当关键字为单 field 时，一定满足 2NF。当关键字为组合 field 时 ,不能存在组合关键字中有某个字段能够决定非关键字段的某部分。非主 field 非部分依赖于主 field，即非关 键字段必须完全依赖于一组 组合关键字，而不是组合关键字的某一部分。 3） 3NF：目标是一个 table里面所有的列不依赖于另外一个table 里面非关键的列。前提是满足了 2NF，不存在某个非关键字段决定另外一个非关键字段。即：不存在传递依赖 4） BCNF：前提是满足了 2NF，不存在某个非 关键字段决定另外一个非关键字段。也不存在某个关键字段决定另外一个关键字段。即：在 3NF基础上，加上约束：不存在某个关键字段决定另外一个关键字段。 1 第一范式 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系数据库。所谓第一范式是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或18 / 79 者不能有重复的属性。如果出现重 复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式中表的每一行只包含一个实例的信息。例如，对于图 3-2 中的员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。 2 第二范式 第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如图 3-2 员工信息表中加上了员工编号列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。第二范式要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性， 如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存19 / 79 储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。 3 第三范式 满足第三范式必须先满足第二范式。简而言之，第三范式 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号 、部门名称、部门简介等信息。那么在图 3-2 的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式也应该构 建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。 例子： 第一范式：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。 20 / 79 例如，如下的数据库表是符合第一范式的：字段 1 字段 2 字段 3 字段 4 而这样的数据库表是不符合第一范式的：字段 1 字段 2 字段 3 字段 4 字段 31字段 32 很显然，在当前的任何关系数据库管理系统中，傻瓜也不可能做出不符合第一范式的数据库，因为这些 S 不允许你把数据库表的一列再分成二列或多列。因此，你想在现有的 S 中设计出不符合第一范式的数据库都是不可能的。 第二范式：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，也即所有非关键字段都完全依赖于任意一组候选关键字。 假定选课关系表为 Ss(学号 , 姓名 , 年龄 , 课程名称 , 成绩 , 学分 )，关键字为组合关键字 (学号 , 课程名称 )，因为存在如下决定关系： (学号 , 课程名称 ) ( 姓名 , 年龄 , 成绩 , 学分 ) (转载于 : 海 达 范 文网 :数据库面试知识总结 ) 这21 / 79 个数据库表不满足第二范式，因为存在如下决定关系： (课程名称 ) ( 学分 ) (学号 ) ( 姓名 , 年龄 ) 即存在组合关键字中的字段决定非关键字的情况。 由于不符合 2NF，这个选课关系表会存在如下问题： 1) 数据冗余：同一门课程由 n个学生选修， 学 分 就重复 n-1次；同一个学生选修了门课程，姓名和年龄就重复了 -1次。 2) 更新异常：若调整了某门课程的学分，数据表中所有行的 学分 值都要更新，否则会出现同一门课程学分不同的情况。3) 插入异常：假设要开设一门新的课程，暂时还没有人选修。由于还没有 学号 关键字，课程名称和学分也无法记录入数据库。 4) 删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同 时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。 把选课关系表 Ss改为如下三个表： 22 / 79 学生： Sn(学号 , 姓名 , 年龄 )； 课程： s(课程名称 , 学分 )； 选课关系： Ss(学号 , 课程名称 , 成绩 )。 这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。 另外，所有单关键字的数据库表都符合第二范式，因 为不可能存在组合关键字。 第三范式：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在 A 的决定关系，则传递函数依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 非关键字段 x 非关键字段 y 假定学生关系表为 Sn(学号 , 姓名 , 年龄 , 所在 学院 , 学院地点 , 学院电 话 )，关键字为单一关键字 学号 ，因为存在如下决定关系： 23 / 79 (学号 ) ( 姓名 , 年龄 , 所在 学院 , 学院 地点 , 学院 电话 ) 这个数据库是符合 2NF 的，但是不符合 3NF，因为存在如下决定关系： (学号 ) ( 所在 学院 ) ( 学院 地点 , 学院 电话 ) 即存在非关键字段 学院 地点 、 学院 电话 对关键字段 学号 的传递函数依赖。 它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自 行分析得知。 把学生关系表分为如下两个表： 学生： (学号 , 姓名 , 年龄 , 所在 学院 )； 学院 ： (学院 , 地点 , 电话 )。 这样的数据库表是符合第三范式的，消除了数据冗余、更新24 / 79 异常、插入异常和删除异常。 鲍依斯 -科得范式：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合 BCNF. 假设仓库管理关系表为 Ssanag(仓库 , 存储物品 , 管理员 , 数量 )，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系： (仓库 , 存储物品 ) ( 管理员 , 数量 ) (管理员 , 存储物品 ) ( 仓库 , 数量 ) 所以， (仓库 , 存储物品 )和 (管理员 , 存储物品 )都是 Ssanag的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系： (仓库 ) ( 管理员 ) (管理员 ) ( 仓库 ) 25 / 79 即存在关键字段决定关键字段的情况，所以其不符合 BCNF范式。它会出现如下异常情况： 1) 删除异常：当仓库被清空后，所有 存储物品 和 数量 信息被删除的同时， 仓库 和 管理员 信息也被删除了。 2) 插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员。 3) 更新异常：如果仓库换了管理员，则表中所有行的管理员都要修改。 把仓库管理关系表分解为二个关系表： 仓库管理： Ssanag(仓库 , 管理员 )； 仓库： Ss(仓库 , 存储物品 , 数量 )。 这样的数据库表是符合 BCNF 范式的，消除了删除异常、插入异常和更新异常。 简言之数据库五大范式： 第一范式：对于表中的每一行，必须且仅仅有唯一的行值 .在一行中的每一列仅有唯一的值并且具有原子性 . 26 / 79 第二范式：第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素 ,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系 . 第三范式：第三范式要求非主键列互不依赖 . 第四范式：第四范式禁止主键列和非主键列一对多关系不受约束 第五范式：第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余。 面试穿什么着装合适，这里找 答案！ 2，索引： 什么叫 revised key index? 反键索引是 B*Tree 索引的一个分支，它的设计是为了运用在某些特定的环境下的。 Oracle推出它的主要目 的就是为了降低在并行服务器环境下索引叶块的争用。当 B*Tree 索引27 / 79 中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用 . 例子：有一个字段 id，他的值落在一个很小的区间，比如从9000-9999，如果建 b-tree索引，那么值过于 紧密，反键的原理是把值取反，那么 id 的区间就从0009-9999，区间就被放大，这个时候通过索引来查找数据效率会比较高。 好处是 :解决了树的倾斜问题，而且可以解决在大量 IO操作的情况下 ,防止硬盘在某个区域操作过于频繁 ,引起 热点 问题。 树的分支：因为索引一般是按树这个数据结构来组织，所以有很多分支，把不同类别或范围的数据存放在分支里，在符合条件的分支里查询比在全表查询效率高很多。 树的倾斜：树的某个分支过与庞大 ,而其他分支内容却很少，这样的索引非常不健康的，查询速度也很慢，如上面的示例28 / 79 数据，都 在 10000-20000 的分支，而 20000-30000或者以上的分支是空的。反转后把这些数据均匀分布到不同的分支，可以使索引更加健康，也更有效率。 热点问题：由于系统在表数据的增删改查的同时，同时要承担索引开支，而这主要是硬盘的 IO操作，如果树是倾斜的，而且数据的增加是按一定顺序增长的，这种情况会导致硬盘对某一固定区域操作频繁，会出现热点问题，而且出现瓶颈。 Oracle 五种索引： 1） b*tree index：几乎所有的关系型数据库中都有 b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据 rid 快速定位所访问的行。 B-Tree 索引是基于二叉树的，由分支块和叶块组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的 rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列范围和另一索引块的地址。 2）反向索引：反转了 b*tree 索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。 反向索引又一个缺点就是不能在所有使用常规索引29 / 79 的地方使用。在范围搜索中其不能被使用。 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 以来的新产物，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件： 必须拥有 QUERY REWRITE 或 GLOBAL QUERY REWRITE 权限。 必须使用基于成本的优化器，基于规则的优化器将被忽略。 30 / 79 必须设置以下两个系统参数： QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED 可以通过 alter system set,alter session set 在系统级或线程级设置， 也可以通过在添 加实现。 五种索引的创建： *Tree索引。 Create index indexname on tablename(columnnamecolumnname.) 反向索引。 Create index indexname on tablename(columnnamecolumnname.) reverse 31 / 79 降序索引。 Create index indexname on tablename(columnname DESCcolumnname.) 位图索引。 Create BITMAP index indexname on tablename(columnnamecolumnname.) 函数索引。 Create index indexname on tablename(functionname(columnname) 注意：创建索引后分析要索引才能起作用。 五种索引的使用场所： B*Tree 索引 。 常规索引，多用于 oltp 系统，快速定位行，应建立于高32 / 79 cardinality 列。 反向索引。 B*Tree 的衍生产物，应用于特殊场合，在 ops 环境加序列增加的列上建立， 不适合做区域扫描。 降序索引。 B*Tree 的衍生产物，应用于有降序排列的搜索语句中，索引中储存了降序排列的索引码，提供了快速的降序搜索。 位图索引。 位图方式管理的索引，适用于 OLAP 和 DSS 系统，应建立于低 cardinality 列，适合集中读取，不适合插入和修改，提供比 B*Tree 索引更节省的空间。 函数索引。 B*Tree 的衍生产物 ，应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改33 / 79 应用程序的基础上能提高查询效率。 索引不管用的时候： RBO&CBO。 Oracle 有两种执行优化器，一种是 RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于 sql语句写法选择执行路径的；另一种是 CBO基于规则的优化器，这种优化器是 Oracle 根据统计分析信息来选择执行路径，如果表和索引没有进行分析， Oracle 将会使用 RBO代替 CBO；如果表和索引很久未分析， CBO也有可能选择错误执行路径，不过 CBO 是 Oracle 发展的方向，自 8i版本来已经逐渐取代RBO. AUTOTRACE。 要看索引是否被使用我们要借助 Oracle 的一个叫做AUTOTRACE 功能 ,它显示了 sql语句的执行路径，我们能看到Oracle内部是怎么执行 sql的，这是一个非常好的辅助工具，在 sql 调优里广泛被运用。我们来看一下怎么运用AUTOTRACE： 34 / 79 由于 AUTOTRACE自动为用户指定了 Execution Plan,因此该用户使用 AUTOTRACE 前必须已经建立了 PLAN_TABLE。如果没有的话，请运行脚本。 AUTOTRACE 可以通过运行脚本 (它在 $ORACLE_HOME/sqlplus/admin 目录中 )来设置，用 sys 用户登陆然后运行后会建立一个 PLUSTRACE 角色，然后给相关用户授予 PLUSTRACE 角色，然后这些用户就可以使用AUTOTRACE 功能了。 AUTOTRACE 的默认使用方法是 set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly 提供了只查看统计信息而不查询数据的功能。 面试穿什么着装合适，这里找答案！ ，死锁 是指两个或两个以上的进程在执行过程中 ,因争夺资源而造成的一种互相等待的现象 ,若无外力作用 ,它们 1、 2、 3、 4、 5、 6、 7、 8、 9、 10、 什么是第三范式 请说出 delete,truncate,drop 的区别 怎35 / 79 么样查询特殊字符，如通配符 %与 _ 如何插入单引号到数据库表中 怎么获得今天是星期几，还关于其它日期函数用法 知道出生日期，如何求年龄？ 求上个月月底的日期 数据库类型中 Varchar和 char 的区别是 ？ 已知两张表：人员表 (person) 和部门表 ,表结构如下： 数据库 Teacher 属性： name,tid,desc,表 Student 属性：name,sid,related_tid ,desc 查询 所属老师名称为 like 的全部学生。 11、 设有一个关系表 Student (学号 stu_id，姓名 stu_name，系名 stu_dept，课程号 stu_courseid，成绩 grade) 12、 13、 查询至少选修了四门课程的学生的学号、姓名及平均成绩的select语句？ 将选修课程数小于 5的学生名字后面增加一36 / 79 个 #号 用一条 sql语句实现下面结果：怎么把这样一个表 Testcol： 有两个表 T_STU表和 T_CLASS表和一个序列 sequence序列，T_STU表里有如下字段： . 查询入学年龄在 18-20的女生或者未输入性别的 ,实际年龄小的要排在后面 查询班级名称、学生姓名、性别、缴费 (要求显示单位：元 ),相同班级的要放在一起 ,姓名根 据字典顺序排列。 14、 查询各班名称和人数 查询各班名称和人数 ,但人数必须不少于 2,人数多的放在前面 查询 1980年出生的有哪些学生。 查询男生和女生人数 ,没有输入性 别的当作男 查询没有人员的班级 查询入学年龄在 20以上的同学信息 查询班级平均入学年龄37 / 79 在 20及以上的班级 有工资表 salary(e_id,e_date,e_money),求本月发了 2笔以上工资的员工信息。 有部门表、人员表、工资表。表名和字段名 ,如下： 查询：人员名称、部门名称、个 人总工资 查询所有部门的总工资 查询 XX 年 8 月份各部门工资最高的员工信息：部门名称、员工姓名、员工总工资 15、 表 warehousestorage 数据库笔试面试题汇总 1. 什么是第三范式 第一范式：所谓第一范式是指数据库表的每一列都是不可 分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。 第二范式：如果关系模式 R 是 1NF，且每个非主属性完全函数依赖于候选键，那么就称 R是第二范式。 第三范式：如38 / 79 果关系模式 R 是 2NF，且关系模式 R 中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系 R 是属于第三范式。 2. 请说出 DELETE,TRUNCATE,DROP 的区别 delete 和 truncate 区别： delete：从数据库的缓存区清除该数据 truncate：把数据删除了，然后清空所占用的空间 delete可以撤销 truncate 不能撤销 truncate=delete+commit truncate和 drop 区别 drop：删除表的定义，整个对象删掉，删除的是对象的本身，全部 truncate：删除表的内容，只是删除数据，表的结果会保留 3. 怎么样查询特殊字符，如通配符 %与 _ select * from table where name like A_% escape _ 4. 如何插入单引号到数据库表中 39 / 79 可以用 ASCII码处理，其它特殊字符如 &也一样，如： insert into t values(ichr(39)m); - chr(39)代表字符 或者用两个单引号表示一个 or insert into t values(Im); - 两个 可以表示一个 5. 怎么获得今天是星期几，还关于其它日期函数用法 可以用 to_char来解决，如： select to_char(to_date(2002-08-26,yyyy-mm-dd),day) from dual; 在获取之前可以设置日期 语言，如： ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN; 在函数中指定，如： select to_char(to_date(2002-08-26,yyyy-mm-dd),day,N40 / 79 LS_DATE_LANGUAGE = American) from dual; (4)其它更多用法，可以参考 to_char 与 to_date函数 如获得完整的时间格式 select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual; (5)随便介绍几个其它函数的用法： 本月的天数 SELECT to_char(last_day(SYSDATE),dd) days FROM dual 今年的天数 select add_months(trunc(sysdate,year), 12) - trunc(sysdate,year) from dual 下个星期一的日期 SELECT Next_day(SYSDATE,monday) FROM dual 6. 知道出生日期，如何求年龄？ 41 / 79 比如 1984 年 1月 23日出生，求现在的年龄 : Foolr() 取比你输入的值小的最大的整 数 Months_between()两个时间的之间的月数，而且这个数是一个 浮 点 数 select floor(months_between(sysdate, birthday) / 12) yyyy, floor(months_between(sysdate, birthday) - floor(months_between(sysdate, birthday) / 12) * 12) mm, trunc(sysdate) - add_months(birthday, floor(months_between(sysdate, birthday) dd from datetest; select name 姓名 ,floor(months_between(sysdate,birthday)/12) 岁 from person; select name 姓名 , floor(months_between(sysdate,birthday)- floor(months_between(sysdate,birthday)/12)*12) from person; 42 / 79 select trunc(sysdate, yyyy ) from person; select trunc(sysdate, mm ) from person; select trunc(sysdate) from person; s