面试刷题——数据库

面试刷题——数据库

1、请回答一下什么是事务?

概念：数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

性质：

• 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行。

• 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

• 夺隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

• 夺持久性(Durability):对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。

  事务的ACID特性是由关系数据库系统(DBMS)来实现的，DBMS采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所作的更新，如果某个事务在执行过程中发生错误，就可以根据日志撤销事务对数据库已做的更新，使得数据库同滚到执行事务前的初始状态。

  对于事务的隔离性，DBMS是采用锁机制来实现的。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

作用：

• 一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的：
  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

模型：

• 显式事务

  显式事务又称自定义事务，是指用显式的方式定义其开始和结束的事务，当使用start transaction和 commit语句时则表示发生显式事务。

• 隐式事务

  隐式事务是指每一条数据操作语句都自动地成为一个事务，事务的开始是隐式的，事务的结束有明确的标记。即当用户进行数据操作时，系统自动开启一个事务，事务的结束则需手动调用 commit或 rollback语句来结束当前事务，在当前事务结束后又自动开启一个新事务。

• 自动事务

  自动事务是指能够自动开启事务并且能够自动结束事务。在事务执行过程中，如果没有出现异常，事务则自动提交;当执行过程产生错误时，则事务自动回滚。

优点：

• 以事务的方式对数据库进行访问，有如下的优点：

  1、把逻辑相关的操作分成了一个组；

  2、在数据永久改变前，可以预览数据变化；

  3、能够保证数据的读一致性。

2、内连接和外连接有什么区别?

内连接：

• 内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。也就是左表和右表相同的数据

• 最常见的为等值连接。内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

**外连接：**外连接分为三种：左外连接，右外连接，全外连接。

• 连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。
• 左外连接：左外连接就是以左表为准，去匹配右表，左表有多少条数据，结果就是多少条数据
• 右外连接：左外连接相反，以右表为准，去匹配左表，右表有多少条数据，结果就是多少条数据
• 全外连接：全外连接数据条数不一定，相当与是左外连接和右外连接的综合

3、什么是主键？什么是外键？

• 主键

  是数据库表中的一个或多个字段，是数据表中记录的唯一标识符，是一个不能重复的非空值

  • **实体完整性：**数据库中必须有主键，而且主键必须是唯一的，并且是一个非空值
  • 主要遵循原则
    • 主键对用户而言没有意义
    • 主键不能为空
    • 主键应保持不变
    • 主键不应包含动态的时间戳
    • 主键原则上应是由计算机自动生成的，也不应该是用户可以修改的

• 外键

  也称之为外码，表示的是两个关系之间的联系，当公共关键字在一个关系中是主键的时候，在另一个关系中他就是外键。外键可以为空也可以是多个

  • 作用：和其它表建立联系，实现表之间的关联，实现表之间的一致性。
  • 参照完整性:在引用一个实体的时候一定要保证这个实体是真实存在的，要么是真实存在，要么是一个空值

4、什么是触发器?

• 概念

  触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

• 好处：和外部设备、存储过程相比，触发器可以更好更高效的维护数据。

• 作用

  • 可在写入数据表前，强制检验或转换数据。
  • 触发器发生错误时，异动的结果会被撤销。
  • 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
  • 可依照特定的情况，替换异动的指令 (INSTEAD OF)。

• 分类

  • DML触发器
    • 当数据库中表中的数据发生变化时，包括insert,update,delete任意操作，如果我们对该表写了对应的DML触发器，那么该触发器自动执行。DML触发器的主要作用在于强制执行业务规则，以及扩展Sql Server约束，默认值等。因为我们知道约束只能约束同一个表中的数据，而触发器中则可以执行任意Sql命令
  • DDL触发器
    • 它是Sql Server2005新增的触发器，主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。
  • 登录触发器
    • 登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

• 拓展：慎用触发器

  • 触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

5、什么是索引?索引有几类?索引有什么好处，索引会不会带来相关问题?

• 概念
  • 索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓（name）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。
  • 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。在数据库关系图中，您可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。
• 类型
  • 根据数据库的功能，可以在数据库设计器中创建四种索引：唯一索引、非唯一索引、主键索引和聚集索引。 尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。
  • 唯一索引
    • 唯一索引是不允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。
  • 非唯一索引
    • 非唯一索引是相对唯一索引，允许其中任何两行具有相同索引值的索引。 当现有数据中存在重复的键值时，数据库是允许将新创建的索引与表一起保存。这时数据库不能防止添加将在表中创建重复键值的新数据。
  • 主键索引
    • 数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
  • 聚集索引（也叫聚簇索引）
    • 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度
• 优点
  • 大大加快数据的检索速度
  • 创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 加速表和表之间的连接
  • 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
• 索引带来的问题
  • 确实，索引能够极大地提高数据检索效率，也能够改善排序分组操作的性能，但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假 设在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同时，都须要更新Column ca 的索引数据，调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引，MySQL要做的仅仅是更新表中 Column ca 的信息。这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca须要占用存储空间，而且随着 Table ta 数据量的增加，idx_ta_ca 所占用的空间也会不断增加，所以索引还会带来存储空间资源消耗的增加。
  • 如何优化
    • 较频繁的作为查询条件的字段应该创建索引
    • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    • 更新非常频繁的字段不适合创建索引
    • 不会出现在 WHERE 子句中的字段不该创建索引

6、一二三四范式有何区别?

范化是在识别数据库中的数据元素、关系、以及定义所需的表和各表中的项目这些初始化工作之后的一个细化的过程。常见的范式有1NF 2NF 3NF BCNF以及4NF。

• 1NF，第一范式

  第一范式是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，即可能需要定义一个新的实体。新的实体由重复的属性构成，新的实体和原来的实体之间为一对多的关系。第一范式的模式要求属性值不可再分裂成更小部分，即属性项不能是属性组合或由组属性组成。简而言之，第一范式就是无重复的列。例如，由“职工号”、“姓名”、“电话号码”组成的表（一个人即可能有一个办公电话和一个移动电话），这时将其范化化为1NF可以将电话号码分为“办公电话”和移动电话两个属性，即职工（职工号，姓名，办公电话，移动电话）。

• 2NF，第二范式

  第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每一个实例或行必须可以被唯一的区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。如果关系模式R为第一范式，并且R中每一个非主属性完全依赖于R的某个候选键，则称R为第二范式模式。例如，在选课关系表（学号、课程号、成绩，学分）中，关键字为组合关键字（学号，课程号），但由于非主属性学分仅依赖于课程号，对关键字（学号，课程号）只是部分依赖，而不是全部依赖，所以此中方式会导致数据冗余以及更新异常等问题，解决办法是将其分为两个关系模式：学生表（学号，课程号，分数）和课程表（课程号，学分）。新关系通过学生表的外关键字课程号关联，在需要时进行连接。

• 3NF，第三范式

  如果关系模式R是第二范式，且每个非主属性都不依赖于R的候选键，则称R是第三范式的模式。例如，学生表（学号，姓名，课程号，成绩），其中学生姓名无重名，所以该表有两个候选码（学号，课程号）和（姓名，课程号），则存在函数依赖：学号->姓名，（学号，课程号）->成绩，（姓名，课程号）->成绩，唯一的非主属性成绩对码不在部分依赖，也不存在传递依赖，属于第三范式。

• 4NF，第四范式

  一个表中可以进行多值操作。