面试总结---数据库部分

发表于 | 分类于

[TOC]

1. 数据库中一些名词的理解

1.1 事务

是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的一个逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

1.1.1 事务的4个特性

  • 原子性(Atomic)：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

  • 一致性(Consistency)：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。

  • 隔离性(Isolation)：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，到底是另一个事务执行之前的状态还是中间某个状态，相互之间存在什么影响，是可以通过隔离级别的设置来控制的。

  • 持久性(Durability)：事务结束后，事务处理的结果必须能够得到固化，即写入数据库文件中即使机器宕机数据也不会丢失，它对于系统的影响是永久性的。

1.1.2 事务并发控制

我们从另外一个方向来说说，如果不对事务进行并发控制，我们看看数据库并发操作是会有那些异常情形，有些使我们可以接受的，有些是不能接受的，注意这里的异常就是特定语境下的，并不一定就是错误什么的。假设有一个order表，有个字段叫count，作为计数用，当前值为100

  • 第一类丢失更新（Update Lost）：此种更新丢失是因为回滚的原因，所以也叫回滚丢失。此时两个事务同时更新count，两个事务都读取到100，事务一更新成功并提交，count=100+1=101，事务二出于某种原因更新失败了，然后回滚，事务二就把count还原为它一开始读到的100，此时事务一的更新就这样丢失了。

  • 脏读（Dirty Read）：此种异常时因为一个事务读取了另一个事务修改了但是未提交的数据。举个例子，事务一更新了count=101，但是没有提交，事务二此时读取count，值为101而不是100，然后事务一出于某种原因回滚了，然后第二个事务读取的这个值就是噩梦的开始。

  • 不可重复读（Not Repeatable Read）：此种异常是一个事务对同一行数据执行了两次或更多次查询，但是却得到了不同的结果，也就是在一个事务里面你不能重复（即多次）读取一行数据，如果你这么做了，不能保证每次读取的结果是一样的，有可能一样有可能不一样。造成这个结果是在两次查询之间有别的事务对该行数据做了更新操作。举个例子，事务一先查询了count，值为100，此时事务二更新了count=101，事务一再次读取count,值就会变成101，两次读取结果不一样。

  • 第二类丢失更新（Second Update Lost）：此种更新丢失是因为更新被其他事务给覆盖了，也可以叫覆盖丢失。举个例子，两个事务同时更新count，都读取100这个初始值，事务一先更新成功并提交，count=100+1=101，事务二后更新成功并提交，count=100+1=101,由于事务二count还是从100开始增加，事务一的更新就这样丢失了。

  • 幻读（Phantom Read）：幻读和不可重复读有点像，只是针对的不是数据的值而是数据的数量。此种异常是一个事务在两次查询的过程中数据的数量不同，让人以为发生幻觉，幻读大概就是这么得来的吧。举个例子，事务一查询order表有多少条记录，事务二新增了一条记录，然后事务一查了一下order表有多少记录，发现和第一次不一样，这就是幻读。

1.2 索引

索引好比字典的目录，让你按照一定的规则更快的找到目标数据

为什么需要索引？数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

举个例子
我们有一个数据表User.为了简便，这个表没有主键。
Identity | Name | Age | Grade
——–|——-|——|——
1|Robin|28|90
5|Lilei|26|60|
3|Hanmei|25|50|
4|Lucy|27|66|
2|Lily|29|80|

虽然这些数据都存在于一个User表中，但是物理上，这些数据可能存储在分散的数据块中。
查找Lily这个人的信息, 已知Lily的Identity为2， select * fromUser where Identity= 2.

在查找的时候，首先找到这个表的第一条记录所在的数据库地址，然后发现Identity为1，并不是所需要的值，然后在这个数据库的底端，找到了下一个数据块的地址。（这个类似于链表），如此一来，查询了5次才找到了所需要的值。（为了简单起见，我们考虑Identity不能有重复值）

为了加快搜索速度，这里就出现了索引。索引是对某个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。这种索引的数据结构是经过排序的，因而可以对其执行二分查找。

1
2
3
4
/**创建索引*/
create index 索引名 on 表名（列名）
/**删除索引*/
drop index 索引名

2. 数据库三范式

  • 第一范式（1NF）

字段具有原子性,不可再分。

所有关系型数据库系统都满足第一范式）数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

  • 第二范式（2NF）

要有主键，要求其他字段都依赖于主键。

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

  • 第三范式（3NF）

各种信息只在一个地方存储，不出现在多张表中。

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征：

  1. 每一列只有一个值

  2. 每一行都能区分。

  3. 每一个表都不包含其他表已经包含的非主关键字信息。

3. 表连接

3.1 JOIN和UNION的区别

  • join 是两张表做交连后里面条件相同的部分记录产生一个记录集，
  • union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集。

    3.2 JOIN联接

JOIN用于按照ON条件联接两个表，主要有四种：

  • 内联接( inner join)

内部联接两个表中的记录，仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。我理解的是只要记录不符合ON条件，就不会显示在结果集内。

SQL写法：

1
2
3
SELECT msp.name, party.name FROM msp inner JOIN party ON party=code
/**或者*/
SELECT msp.name, party.name FROM msp JOIN party ON party=code
  • 左联接（LEFT JOIN / LEFT OUTER JOIN）

外部联接两个表中的记录，并包含左表中的全部记录。如果左表的某记录在右表中没有匹配记录，则在相关联的结果集中右表的所有选择列表列均为空值。理解为即使不符合ON条件，左表中的记录也全部显示出来，且结果集中该类记录的右表字段为空值。

SQL写法：

1
2
SELECT msp.name, party.name 
FROM msp LEFT JOIN party ON party=code
  • 右联接（RIGHT JOIN / RIGHT OUTER JOIN）

外部联接两个表中的记录，并包含右表中的全部记录。简单说就是和LEFTJOIN反过来。

SQL写法：

1
2
SELECT msp.name, party.name 
FROM msp RIGHT JOIN party ON msp.party=party.code
  • 全联接（FULL JOIN / FULL OUTER JOIN）

完整外部联接返回左表和右表中的所有行。就是LEFTJOIN和RIGHTJOIN和合并，左右两表的数据都全部显示。

SQL写法：

1
2
SELECT msp.name, party.name
FROM msp FULL JOIN party ON msp.party=party.code

3.3 UNION联接

将两个或更多查询的结果集组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。UNION的结果集列名与UNION运算符中第一个Select语句的结果集的列名相同。另一个Select语句的结果集列名将被忽略。

UNION 与 UNION ALL 的区别：

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录.

  • union 检查重复
  • union all 不做检查

SQL写法：

1
2
/**table1和table2的列名一定要相同*/
SELECT * FROM Table1 UNION SELECT * FROM Table2

4.Group by 的使用

语法：select 列a,聚合函数 from 表名 where 过滤条件 group by 列a having 过滤条件

4.1 概念与使用

对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理

注意:group by 是先排序后分组

一般要使用聚集函数时，group by也会使用
如果要用到group by 一般用到的就是“每” 这个字，例如：每个部门有多少人， 就要用到分组的技术，语句如下：

1
select DepartmentID,COUNT(*) from Department group by DepartmentID

4.2 聚集函数

  • SUM 求和
  • MAX 求最大值
  • MIN 求最小值
  • AVG 求平均值

4.3 having的使用

having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

需要注意having和where的用法区别：

  1. having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

  2. where肯定在group by 之前

  3. where后的条件表达式里不允许使用聚合函数，而having可以。

举个例子：
查询每一个班级中年龄大于20，性别为男的人数

1
2
select classid,COUNT(*) from table where sex='男'
group by classid,age having age>20

① 先根据where sex = ‘男’ 拿到所有的男生
② 再根据group by classid，age 将所有的男生根据班级id和年龄进行分组
③ 再通过having age>20 剔除年龄段小于20 的分组
④ 最后通过select count(*)，classid 拿到每个班级的人数

5. SQL分页技术

  • mysql

    1
    2
    3
    /**pageSize:每页显示多少数据，pageNumber：当前是第几页 */
    select * from students order by id 
    limit pageSize*(pageNumber-1),pageSize

  • sql Server

    1
    2
    3
    "select top" + pageSize + " * from students 
    where id not in" +"(select top "+ pageSize * (pageNumber-1) 
    + " id from students order by id)" +"order by id

  • oracle

    1
    2
    3
    4
    String sql ="select * from " 
    +(select *,rownum rid from (select * from students order by postime desc) 
    where rid<=" + pagesize*pagenumber +") as t" 
    +"where t>" +pageSize*(pageNumber-1);

6. ORM与 JDBC的关系

orm 是一种思想，就是把object 转变成数据库中的记录，或者把数据库中的记录转变成objecdt，我们可以用jdbc 来实现这种思想，
用的较多的orm 工具是hibernate、Mybatis、toplink

-------------本文结束感谢您的阅读-------------