java面试之数据库部分（面试必看）

面试是进入一家公司的门槛，每一个求职者都需要经历，但是最苦逼最头疼的就是面试题，我帮大家整理了一些数据库部分的面试题，希望对你们有所帮助。整理不易，感谢支持！

1.三大范式？

①.第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）
②.第二范式的要求在满足第一范式的基础上，每个表只描述一件事情（2NF）
③.第三范式要求表中各列必须和主键依赖相关，不能间接依赖（3NF）
在开发中，有时候会违反第三范式增加冗余字段往往为了实现单表查询，规避多表查询带来的效率低的问题。
选择使用冗余字段的两个标准：
A、往往用来查询显示频率比较高的字段。
B、冗余字段读的概率大于写的概率。

2.RBAC模型是什么？（权限模块的理论基础）

RBAC：基于角色的权限访问控制（Role-Based Access Control）
①.RBAC是一套成熟的权限模型。在传统权限模型中，我们直接把权限赋予用户。而在RBAC中，增加了“角色”的概念，我们首先把权限赋予角色，再把角色赋予用户。这样，由于增加了角色，授权会更加灵活方便。
②.在RBAC中，根据权限的复杂程度，又可分为RBAC0、RBAC1、RBAC2、RBAC3。其中，RBAC0是基础，RBAC1、RBAC2、RBAC3都是以RBAC0为基础的升级。我们可以根据自家产品权限的复杂程度，选取适合的权限模型。

RBAC模型表设计示例：

t_user (id,角色ID,用户名，用户密码）—用户表
u001 r001 （u001拥有会员角色）
u002 r002 (u002拥有商家角色）

用户-角色表（id，用户ID，角色ID）
t_role(id,角色名称）—角色表
r001 会员角色
r002 商家角色
项目中角色的往往来源于这个用户在这个组织中职位（例如：张三在某个局是局长，系统就会有个局长的角色）

t_menu(id, 菜单名称，菜单URL）—菜单表
m001 订单查看 http://IP:端口/orders
m002 商品管理 http://IP:端口/goods

t_auth（id，角色ID，菜单ID）—角色-菜单-表
a001 r001 m001 (会员角色拥有订单查看菜单的权限）
a002 r002 m002（商家角色拥有商品管理菜单的权限）

表命名规则：
t_模块名_XXXXX
例如：t_sys_user,t_sys_menu, t_sys_role,t_sys_auth
字段命名规则：
1、单词小写，单词与单词之间一般用“_”分割；
2、命名的时候尽可能英文，实在没法翻译就用拼音的缩写，例如：ykq(遥控器的拼音的缩写);
3、字段的备注要有；
4、增加4个字段create_user(创建者—Insert使用),create_date(创建时间—Insert使用),modify_user(修改者—Update使用),modify_date(修改时间—Update使用) ，目的想知道这行数据谁什么时间创建的，谁什么时间最后修改的，便于数据的追溯；
5、同样含义的字段名称要一致。

3.Truncate和Delete的区别是？

区别1：delete逐条删除，truncate整个表截断即删除表中所有记录。（当确定一张表的数据不再使用，应该使用truncate）
区别2：最根本的区别是：delete是DML（数据操纵语言,可以回滚）truncate是DDL（数据定义语言，不可以回滚）
区别3：Delete需要事务删除大量数据的时候速度慢，Truncate不需要事务，删除大量数据快
区别4：Delete不会释放空间，truncate会
区别5：delete会产生碎片，truncate不会。

4.Sql中去重的关键字是什么？

在select语句中，可以使用distinct关键字删除重复的行。

5.Sql中如何判断是否为null？

使用where子句判断where 列名 is null

6.Sql中模糊查询怎么写？

可以在where子句中使用like运算符。
（不答）下划线（_）：表示匹配某个位置的字符（模糊查询不用下划线）。
（模糊查询只用%）百分号（%）：表示在某个位置的任意个字符。
模糊查询：当数据量比较大的时候，针对于模糊查询需要采用类似搜索引擎技术进行实现，例如Elasticsearch或者solr，优先选择Elasticsearch（简称ES）。

7.having和where的区别？

1.Where 子句是用来指定 “行” 的条件的,对from的数据进行筛选，而Having 子句是指定 “组” 的条件的，对分组后的数据进行筛选。
2.不能在where子句中使用聚合函数，可以在having中使用聚合函数。
3.Having子句不能离开group by子句单独使用，where比having效率更高。
扩展：
1.Group by 和Order by 后面的字段，一般是大范围向小范围过渡。
例如：省—》城市—》县（区）。
2.数据筛选顺序：From—>Where—>Group by—>Having

8.常用的聚合函数有哪些？

AVG(x)：返回x的平均值
COUNT(x)：返回统计的行数
MAX(x)：返回x的最大值
MIN(x)：返回x的最小值
SUM(x)：返回x的总计值
聚合函数可以用在group by 、having、order by后面，不能用在where后面。

9.左外连接和右外连接的区别？

a.左外链接,以左表为参照,显示所有数据;左边有的，右边没有的,右边的为null。
b.右外链接,以右表为参照,显示所有数据;左边没有的，右边有的,左边的为null。

扩展补充：
①.一定要有主表和从表这个概念，分清那张是主表，哪张是从表。
②.把你想查询基础表当成左表。想把谁全部都查询出来就当成主表。
到底哪张是主表哪张是从表？最终还看你的需求。
③.一般我们把主表放在左边，使用左外连接。
④.一般情况下，我们就用左连接就行了。

10.Rowid和Rownum的区别？

ROWID（记录编号）：是用来唯一标识表中的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式，是唯一的，使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。rownum不是表中原本的数据，每一次查询ROWNUM都会重新生成。（查询的结果中Oracle给你增加的一个编号，根据结果来重新生成）。
ROWNUM永远按照默认的顺序生成。（不受order by的影响）
rowid 用于定位数据表中某条数据的位置，是唯一的、也不会改变。(面试背)
rownum 表示查询某条记录在整个结果集中的位置， 同一条记录查询条件不同对应的 rownum 是不同的而 rowid 是不会变的。(面试背)
扩展：
1、主键命名要么id要么表名_id;
2、主键的值要么是UUID（32位全球唯一码），要么自增型的数字；
3、java中UUID.randomUUID()生成uuid是36位的，需要去掉-，成为32位。
UUID.randomUUID().toString().replaceAll("-", “”)
4、oracle中select sys_guid() from dual;

11.Oracle中分页如何实现？Mysql中分页如何实现？

使用分页目的：是为了避免将大量的数据返回给用户。
Oracle 中使用 rownum 来进行分页, 这个是效率最好的分页方法。从1开始
select * from(select a.*,rownum rn from (select * from TABLENAME) a where rownum <=end ) where rn >= begin;
Select * from (select rownum rn,字段1,字段2 from 表名) t where t.rn>=某页开始行数 and t.rn <=某一页结束的行数
Mysql使用limit的关键字可以实现分页。从0开始
select * from TABLENAME limit start , end;

12.Oracle的索引有哪些？

B树——默认的索引类型，平衡树索引，适用于高基数(不同值的程度高）的列。除非有特殊原因需要使用不同的索引类型或功能，否则用正常的B树索引即可
唯一索引——B树索引的一种形式，用于强制执行列值的唯一性.经常与主键和唯一键约束一起使用，但也可以独立于约束而创建
位图索引——对于包含低基数列（存储数据少的列，比如性别列）以及在SQL语句的WHERE子句中使用许多AND或OR运算符的数据仓库环境，非常适合使用这种索引，只做查询和分析。
函数索引——适用于应用了SQL函数的列。可与B树索引类型或位图索引类型结合使用，避免索引失效建立的函数索引。
索引说明：

1.索引的内容是oracle数据库自己维护的，他会根据索引字段自动进行分组排列，将其rowid（地址）存储起来。
2.通过条件查询，oracle会自动拿条件的字段，自动搜索有没有这个字段的索引，如果有，那么先从索引中寻找符合条件的值的一组地址，将其拿出来，然后根据地址，直接到数据库获取数据。（表只需要扫描有限次，大大提升了效率。）
3.上面索引的数据结构类似Map(key=部门编号，value=List<rowid1,rowid2,rowid3…rowidn>)。
4.当索引列使用了函数或者在like “%输入条件%”,会造成索引失效！！

13.视图和表的区别？

①.视图是基于已知的一张或多张表的查询，一个视图可以对应一个基本表，也可以对应多个基本表
②.视图是虚拟的内存表，视图实际并不存储数据，表是物理存在的，数据存储实际是存储在表中
③.查询视图与查询普通表的查询方式是一样的，使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

14.横纵表的区别？

纵表和横表的概念，纵表方便数据的存储(字典表)，横表方便数据的展示;
①.横表
优点：一行表示了一个实体记录，清晰可见，一目了然。
缺点：如果现在要给这个表加一个字段，那么就必须重建表结构。
②.纵表
优点：如果现在要给这个表加一个字段，只需要添加一些记录。
缺点：数据描述不是很清晰，而且会造成数据库数据很多。另如果需要分组统计，要先group by，较繁琐。
③.结论 ：应该把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。

扩展补充：
create table SCORE_VERTICAL
(
student_name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER
);

insert into SCORE_VERTICAL (student_name, subject, score)
values (‘张三’, ‘语文’, 78);
insert into SCORE_VERTICAL (student_name, subject, score)
values (‘张三’, ‘数学’, 80);
insert into SCORE_VERTICAL (student_name, subject, score)
values (‘李四’, ‘语文’, 67);
insert into SCORE_VERTICAL (student_name, subject, score)
values (‘李四’, ‘英语’, 90);
insert into SCORE_VERTICAL (student_name, subject, score)
values (‘王五’, ‘数学’, 100);
insert into SCORE_VERTICAL (student_name, subject, score)
values (‘王五’, ‘英语’, 80);

将纵表数据转换成横表的显示：
select
s.student_name,
sum(case s.subject when ‘语文’ then s.score end) as 语文,
sum(case s.subject when ‘数学’ then s.score end) as 数学,
sum(case s.subject when ‘英语’ then s.score end) as 英语
from score_vertical s
group by s.student_name;
结果：


调SQL注意事项：
1、先梳理清楚数据的来源，来源于哪几个表
2、编写SQL。
3、验证查询的数据对不对？
4、出现问题，看看SQL的问题，数据也可能存在问题。

15.数据库级联删除？

当数据库中的表，有存在主子表关系的时候：
No Action：如果子表有对应记录，主表不能删除。
Cascade：如果子表有对应记录，主表删除时候，会连带把子表的记录删除掉。
Set null：如果子表有对应记录，主表删除时候，会连带把子表的外键置为NULL。

16.union all和union的区别？

union all：两个以上表连接查询不去重不排序。
union：两个以上表连接查询去重，排序。

17.事物的特点ACID？

原子性(atomicity)：事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行。比如银行转账，转出跟转入这两个包含在一个事务里的动作就是原子的。要么不转出也不转入，转出了就要转入。
一致性(consistency)：在事务处理执行前后，数据必须处于一致状态。比如转账前与转账后两人存款的总和始终不变。
隔离性(isolcation)：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务。比如转账，代码怎么写？就两行代码，是先转出扣钱，再转入加钱。两行代码中间，也就是转出之后，转入之前，此时数据是不一致的。那怎样始终保证数据一致？那就用一个类似自欺欺人的办法，让转账这个事务在完成之前对别人都不可见，事务完成之前别人看到的都是转账前的状态，看不到转账步骤中间不一致的状态，所谓”隔离”。
持续性(durability)：事务处理的效果能够被永久保存下来。就像钱转给别人后当前这比转账交易就结束了，不可能再倒回来。
事务的总结：
最初，为了保证数据的完整性—》于是，数据库提供了事务功能—》但是，在实际中，事务的并发执行又产生了脏读、不可重复读、幻读的问题—》于是，数据库又采用事务隔离级别在不同程度上解决并发产生的问题。

18.Oracle的存储过程如何创建（定义，优点，语法）？

存储过程（Stored Procedure ）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数**（如果该存储过程带有参数）来执行它。
优点**：
(1)允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
(2)允许更快执行，如果某操作需要执行大量 SQL 语句或重复执行，存储过程比 SQL 语句执行的要快。
(3)减少网络流量，例如一个需要数百行的 SQL 代码的操作由一条执行语句完成，不需要在网络中发送数百行代码。
(4)更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。
存储过程创建语法：
Create or replace procedure XXX (参数1，参数2) as 声明变量
Begin
查询
Exception
异常处理
End;

19.存储过程和函数的区别？

①.函数必须有返回值，而过程没有。
②.函数可以单独执行，而过程必须通过 call，execute 执行。
③.函数可以嵌入到 SQL 语句中执行，而过程不行。
扩展补充：
如何选择存储过程和存储函数？
原则上，如果只有一个返回值，用存储函数，否则，就用存储过程。
但是，一般我们会直接选择使用存储过程，原因是：
①.函数是必须有返回值，存储可以有也可以没有，存储的更灵活！
②.既然存储也可以有输出参数,可以代替存储函数。.
③.Oracle的新版本中，已经不推荐使用存储函数了。

20.如何使用Oracle的游标？

①.Oracle 中的游标分为显示游标和隐式游标
②.显示游标是用 cursor…is 命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理；
③.隐式游标是在执行插入 (insert)、删除(delete)、修改(update) 和返回单条记录的查询(select)语句时由 PL/SQL 自动定义的。
④.显式游标的操作：定义游标、打开游标、操作游标、关闭游标；隐式游标的操作PL/SQL 隐式地打开 SQL 游标，并在它内部处理 SQL语句，然后关闭它。

21.触发器的定义，语法，作用？

定义：
触发器是指被隐含执行的存储过程，通常用的针对于Insert、Delete、Update之类的DML触发器。在进行特定的增删改操作时触发触发器。
语句级触发器和行级触发器区别：
1．在语法上：
行级触发器就多了一句话：for each row
2．在表现上
行级触发器，在每一行的数据进行操作的时候都会触发。
语句级触发器，对表的一个完整操作才会触发一次。
简单的说：行级触发器，是对应行操作的；语句级触发器，是对应表操作的。

语法：
create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
End

作用：
1.数据确认；
2.实施复杂的安全性检查；
3.做审计，跟踪表上所做的数据操作等；
4.数据的备份和同步。
（备份：在删除时，触发触发器。创建一个新的表将要删除的数据先插入这个新表中，然后再删除数据）

触发器注意事项：
①.触发器会引起锁，降低效率！使用时要慎重。如无必要，尽量不要使用触发器。
②.行级触发器会引发行级锁（锁行数据）
③.语句级触发器可能会引起表级锁（锁表）

22.如何进行数据库的优化？

从三个方面解答即可：
1、合理的数据库设计；
2、索引的使用；
3、SQL语句的调优
具体如下：

1、合理的数据库设计（优化的根本）
要点1：合理数据库设计非常重要，好的设计对优化很重要，采用第1,2,3范式进行数据库的设计。
要点2：对比较频繁的数据表关联查询应适当提高数据冗余设计（违反了范式要求，但利大于弊）。
要点3：通过复杂计算的列，在存储就计算好存放。例如：存放语文，数学，英语成绩时，在insert时候，顺带把三个科目的总分存进去。
要点4：把频繁被访问的数据同较少被访问的数据分开存储（分库实现，读写分离）。

2、索引的使用
要点1：索引是改进查询性能的最简单、最有效的方法，尤其是当您总是对几个关键字字段进行搜索时，这种方法最合适。
要点2：索引并不是越多越好，索引固然可以提高相应的select 的效率，但同时也降低了insert 及update 的效率，因为insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
要点3：WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
要点4：分离表和索引。

3、SQL语句的调优
要点1：在FROM子句中包含几个表的情况下，记录少的放在右边，如果是三个以上的表，交叉表放在右边。
要点2：可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
要点3：SELECT子句中避免使用*。
要点4：减少访问数据库的次数，尽量sql一次读取数据，避免多次读取。
要点5：使用DECODE减少处理时间。
要点6：用Where子句替换HAVING子句
要点7：含有子查询的SQL语句中，要特别注意减少对表的查询
要点8：使用表的别名(Alias)

4、其他优化策略
要点1：尽量多使用COMMIT
要点2：用TRUNCATE替代DELETE

23.MyISAM和InnoDB的区别？

MyISAM：不需事务，不支持数据行锁定和外键约束，空间小，以查询访问为主
InnoDB：多删除、更新操作，安全性高，事务处理及并发控制

24.MySQL的索引有哪些？

从用途上可分为：
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点；
唯一索引：索引列中的值必须是唯一的，但是允许为空值；
主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）
组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
全文索引：只有在MyISAM引擎上才能使用，只能在char,varchar,text
类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大学生，程序员 …" 通过大学生，可能就可以找到该条记录。（可以认为保存的是一篇文章，在这篇文章中查询关键字）

25.原生的JDBC开发的步骤？

1：Class.forName()加载数据库连接驱动;
2：DriverManager.getConnection()获取数据连接对象;
3：使用Statement、PreparedStatement 根据SQL获取sql会话对象。
4：执行SQL处理结果集，执行SQL前如果有参数值就设置参数setXXX();
5：关闭结果集、关闭会话、关闭连接。
JDBC在开发过程中，容易犯的问题就是生成SQL有问题，通过查看生成的SQL，来修复问题。

26.为什么使用PreparedStatement？

1.最重要的一点是极大地提高了安全性。比如Statement容易被SQL注入（只是安全中的一方面），而 PreparedStatement传入的内容不会和sql语句发生任何匹配关系。
2.PreparedStatement尽最大可能提高性能。DB有缓存机制，相同的预编译语句再次被调用不会再次需要编译，所以其执行速度要快于 Statement 对象。
3.提高了代码的可读性和可维护性。Statement需要不断地拼接，而 PreparedStatement不会。
补充：
1.对sql进行预编译处理，sql的格式固定。可以放置预编译。
2.PrepareStatment 进行了预编译的处理，当下次执行相同格式的sql的时候，sql不会在进行编译。比statement的效率高。
3.statement每执行一次sql进行一次编译。
项目中原始生JDBC开发优先使用PrepareStatment！！！

27.关系数据库中连接池的机制是什么？

前提：为数据库连接建立一个缓冲池(缓存Connection对象、缓存Statement及PreparedStatement)。
1.从连接池获取或创建可用连接
2.使用完毕之后，把连接返回给连接池
3.在系统关闭前，断开所有连接并释放连接占用的系统资源
(能够处理无效连接，限制连接池中的连接总数不低于或者不超过某个限定值。)
(为了复用连接，代替传统的频繁占用系统资源和耗费时间的方式)

28.什么是Dao模式？

Dao模式极简介绍：
封装了对单表的增删改查。
Dao模式的组成部分：
1.实体类
2.Dao接口
3.Dao实现类
4.Dao实现类的父类(BaseDao)

29.说说你对3层架构的理解？

表示层（H5、CSS、JSP）：主要负责获得数据以及为用户展现数据
业务逻辑层：（Service，将相关的Dao作为其属性成员，封装了对多表的增删改查，需要事务）
数据访问层：（Dao）