面试常问数据库原理

1 E-R图转换关系模型原则

• E-R图：实体-联系图，在数据库系统概论中属于概念设计阶段，提供了表示实体、属性和联系的方法， 用来描述现实世界的概念模型。

• 关系模型：关系其实就是数据库表，关系模型就是对关系的描述（一张表里有哪些属性，各属性依赖关系），关系模型也就是一个数据库。

• 转换原则：将每个实体类型转换成一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码

  • 1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。

    • 可以在一张表里加上另一张表的主键来建立联系
    • 建一张新的表，分别用两张表的主键来作为属性

  • 1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。

  • m:n联系的转换方法，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合。

2 数据库并发机制

​ 事务是数据库的一个不可分割操作序列，这些操作要么全部执行，要么全部不执行，也是数据库并发控制的基本单位。事务具有四个重要特征，即原子性（Atomicity）、一致性（Consistency）、隔离性 (Isolation)和持久性 (Durability)

• 原子性：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

• 隔离性：隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3 数据库的三范式是什么？

• 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。确保数据库表中每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
• 第三范式：任何非主属性不依赖于其它非主属性。用于消除数据库中依赖传递的问题。每一列数据都和主键直接相关，而不能间接相关。比如说一张歌曲表有歌名，歌手，唱片公司三个数据项，假设主键是歌名，但唱片公司和歌名间接相关和歌手直接相关，此时就违反了第三范式，我们要做的就是把唱片公司拆出去生成另一张表。

4 mysql 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

5 说一下数据库的事务隔离？

mysql可用的配置值

• READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
• READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
• REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
• SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。

6 说一下数据库并发控制？

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。适用于读多写少，可以解决更新丢失问题。
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。适用于写多读少，可以解决脏读、幻读、不可重复读问题。
• MVCC多版本并发控制：是通过保存数据在某个时间点的快照来实现的。而写操作不覆盖已有数据项，而是创建一个新的版本，直至所在事务提交时才变为可见。

7 sql sever的事务语句

• Begin Transaction：标记事务开始。
• Commit Transaction：事务已经成功执行，数据已经处理妥当。
• Rollback Transaction：数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
• Save Transaction：事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。

8 数据库delete和drop的作用

• drop主要用于删除数据结构,包括内部的数据内容。

• delete主要用于删除数据内容,不删除数据结构。

9 数据库活锁和死锁

• 死锁

  • 数据库死锁是指两个资源互相等待，如果需要修改一条数据，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。

  • 表现：一个用户 A 访问表 A(锁住了表 A)，然后又访问表 B。另一个用户 B 访问表 B(锁住了表 B)，然后企图访问表 A。

  • 解决方法：1.一次性封锁法 2.顺序封锁法

• 活锁

  • 数据库活锁就是饥饿，某一事务因为优先级问题永远无法访问某表
  • 解决方法：先来先服务策略

10 数据库的视图

​ 视图是一个虚拟表，其查询的数据来自于视图定义时的 as select xx 查询语句。视图的列来自于一个表或多个表，所以视图不可以和表名重名。
数据多用作查询，一般不会通过视图去修改数据。

11 数据库的锁

• 共享锁，又称为读锁和S锁

  ​ 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。

• 排它锁，又称为写锁和X锁

  ​ 若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

12 数据库的三级模式和两级映像

• 内模式：对数据库的物理存储结构和存储方式的描述，是数据库在数据库内部的存储方式。拿MySQL来讲，每建一个表，都会在文件系统上生成一个或多个文件，这些文件存储了数据、表信息、索引信息，这就称为内模式

• 模式：对内模式的抽象，即数据库

• 外模式：对模式的抽象，即用户直接使用的应用程序

• 外模式-模式映像：保证数据的逻辑独立性。当模式改变时（增加表，增加表的结构），可以保证外模式不变

• 模式-内模式映像：保证数据的物理独立性。当内模式改变时（比如MySQL切换了存储引擎），可以保证模式不变，从而外模式也不会变

13 数据库的完整性

• 域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.

• 实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人。

• 参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性。

14 数据库的触发器

​ 触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法.它被分配给某个特定的表,当对这个表进行插入、更新或删除操作时，自动调用触发器执行触发器里规定的操作。

​ 种类：插入型触发器、删除型触发器、更新型触发器

create trigger 触发器名
        on 表名
        for delete /insert /update
        as 
        触发器要执行的操作

15 左连接和右连接

• 笛卡尔积

  将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

• 内连接：INNER JOIN

  内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。

select * from t_blog INNER JOIN t_type ON t_blog.typeId = t_type.id
select * from t_blog,t_type WHERE t_blog.typeId=t_type.id

• 左连接：LEFT JOIN

  左表的记录会全部表示出来，而右表只会显示符合搜索条件的记录，A表中有B表中不足的地方均用NULL

select * from A left join B on A.aID = B.bID 

• 右连接：RIGHT JOIN

  和左连接的结果刚好相反，这次以B表为基础，B表中有左表中不足的地方均用NULL

select * from A right join B on A.aID = B.bID 
elect * from A left join B on A.aID = B.bID 

• 右连接：RIGHT JOIN

  和左连接的结果刚好相反，这次以B表为基础，B表中有左表中不足的地方均用NULL

select * from A right join B on A.aID = B.bID