Java开发面试题整理 -- 数据库

一、Mysql

1. SQL 的 select 语句完整的执行顺序

（1）from 子句组装来自不同数据源的数据；
（2）where 子句基于指定的条件对记录行进行筛选；
（3）group by 子句将数据划分为多个分组；
（4）使用聚集函数进行计算；
（5）使用 having 子句筛选分组；
（6）计算所有的表达式；
（7）select 的字段；
（8）使用 order by 对结果集进行排序。

SQL 语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按编码顺序被处理。但在 SQL 语句中，第一个被处理的子句式 FROM，而不是第一出现的 SELECT。

2. SQL 之连接查询

（1）左连接（左外连接）：以左表为基准进行查询,左表数据会全部显示出来, 右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;
（2）右连接（右外连接）：以右表为基准进行查询,右表数据会全部显示出来, 右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;
（3）全连接是先以左表进行左外连接，然后以右表进行右外连接。
内连接：
显示表之间有连接匹配的所有行，返回两张表都满足条件的部分。

3. SQL 之聚合函数

聚合函数是对一组值进行计算并返回单一的值的函数，它经常与 select 语句中的 group by 子句一同使用。
1)avg()：返回的是指定组中的平均值，空值被忽略。
2)count()：返回的是指定组中的项目个数。
3)max()：返回指定数据中的最大值。
4)min()：返回指定数据中的最小值。
5)sum()：返回指定数据的和，只能用于数字列，空值忽略。
6)group by()：对数据进行分组，对执行完 group by 之后的组进行聚合函数的运算，计算每一组的值。最后用 having 去掉不符合条件的组，having 子句中的每一个元素必须出现在 select 列表中（只针对于 mysql）。

4. SQL 之 SQL 注入

通过在 Web 表单中输入（恶意）SQL 语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行 SQL 语句。
防止 sql 注入的方式：
举例：

1. 预编译语句：如，select * from user where username = ？，sql 语句语义不会发生改变，sql 语句中变量用？表示，即使传递参数时为“admin or ‘a’= ‘a’”，也会把这整体当做一个字符创去查询。
2. Mybatis 框架中的 mapper 方式中的 # 也能很大程度的防止 sql 注入（$无法防止 sql 注入）。

5. 数据库事务

5.1 事务(TRANSACTION)

是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行.要么都不执行。事务是一个不可分割的工作逻辑单元 事务必须具备以下四个属性，简称 ACID 属性：
A  原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
B 一致性（Consistency）：当事务完成时，数据必须处于一致状态。
C  隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独 立的，它不应以任何方式依赖于或影响其他事务。
D  永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务 的永久性。

5.2 Mysql 中四种隔离级别分别是什么？

1）Read uncommitted
未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。
2）Read committed
读已提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数
3）Repeatable read
可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作，保证同一个事务中的多次相同的查询的结果是一致的
4）Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读.不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read (可重复读)；而在 Oracle 数据库中，只支持 Serializable (串行化)级别和 Read committed (读已提交)这两种级别，其中默认的为 Read committed 级别。

6. 存储过程

6.1 什么是存储过程以及特点？

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程特点
　　1）业务逻辑可以封装存储过程中，容易维护且执行效率非常快！
　　2）存储过程是在数据库的服务器端执行
　　3）移植性很差！不同的数据库的存储过程是不能移植的。

6.2 MySQL 怎么创建并执行存储过程？

-- 创建存储过程
DELIMITER $ --声明存储过程的结束符
CREATE PROCEDURE stu_test() --存储过程名称（参数列表）
BEGIN    --开始
    -- 可以写多个sql语句          -- sql语句+流程控制    
END $    --结束 结束符

-- 执行存储过程
call stu_test()   --call 存储过程名称（参数）

参数：
IN:  表示输入参数，可以携带数据带存储过程中
OUT: 表示输出参数，可以从存储过程中返回结果 
INOUT: 表示输入输出参数，两者结合

7. MySQL 常用 SQL 查询语句优化方法

	1.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
	2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：
		select id from t where num is null
	 	可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：
			select id from t where num=0
	3.尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表	扫描，如：
		select id from t where num=10 or num=20 
		可以这样查询：
			select id from t where num=10
			union all
			select id from t where num=20
	4.in 和 not in 也要慎用，否则会导致全表扫描，如： 
			select id from t where num in(1,2,3)
		对于连续的数值，能用 between 就不要用 in 了：
			select id from t where num between 1 and 3
	5.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了
	 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以
	 怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过 6 个，
	 若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
	6.任何地方都不要使用  select * from  ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
	7.避免频繁创建和删除临时表，以减少系统表资源的消耗。
	8.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。
	9.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
	10. 尽量避免大事务操作，提高系统并发能力。

8. 数据库优化方案整理

8.1 优化说明

（1）因为数据库结构是系统的基石，基础打不好，使用各种优化策略，也不能达到很完美的效果。

（2）数据结构.SQL.索引是成本最低，且效果最好的优化手段。
（3）性能优化是无止境的，当性能可以满足需求时即可，不要过度优化

8.2 优化方向

1)SQL 以及索引的优化
2)合理的数据库设计

根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询。
数据库三范式：
i.第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
ii.第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键； 
iii.第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。

3)硬件优化
4)系统配置的优化

例如：MySQL 数据库 my.cnf

8.3 优化方案

1.代码优化
2.定位慢 SQL，并优化

这是最常用.每一个技术人员都应该掌握基本的 SQL 调优手段（包括方法. 工具.辅助系统等）。这里以 MySQL 为例，最常见的方式是，由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的 SQL，然后使用 explain.profile等工具来逐步调优，最后经过测试达到效果后上线。

3.具体优化手段
（1）连接池调优
（2）合理使用索引
（3）索引类型

主键索引 （PRIMARY KEY） 唯一索引 （UNIQUE）
普通索引 （INDEX）        组合索引 （INDEX）
全文索引 （FULLTEXT） 

可以应用索引的操作符 
大于等于           Between 
IN                 LIKE不以%开头
不能应用索引的操作符NOT IN
LIKE %_ 开 头

4.读写分离

5.GVM 调优

6.缓存

9. oracle和mysql的分页语句？

oracle

select * from (select * from (select s.*,rownum rn from student s ) where rn<=5) where rn>0              

mysql

select * from table_name where 1= 1 limit （start,end）                

10. Mysql 存储引擎有哪些？

（1）、InnoDB 存储引擎
InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB 是默认的 MySQL引擎。
（2）、MyISAM 存储引擎
MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。
（3）、MEMORY 存储引擎
MEMORY 存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。
（4）、NDB 存储引擎
DB 存储引擎是一个集群存储引擎，类似于 Oracle 的 RAC，但它是 ShareNothing 的架构，因此能提供更高级别的高可用性和可扩展性。NDB 的特点是数据全部放在内存中，因此通过主键查找非常快。关于 NDB，有一个问题需要注意，它的连接(join)操作是在 MySQL 数据库层完成，不是在存储引擎层完成，这意味着，复杂的 join 操作需要巨大的网络开销，查询速度会很慢。
（5）、Memory (Heap) 存储引擎
Memory 存储引擎（之前称为 Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。
（6）、Archive 存储引擎
正如其名称所示，Archive 非常适合存储归档数据，如日志信息。它只支持INSERT 和 SELECT 操作，其设计的主要目的是提供高速的插入和压缩功能。
（7）、Federated 存储引擎
Federated 存储引擎不存放数据，它至少指向一台远程 MySQL 数据库服务器上的表，非常类似于 Oracle 的透明网关。
（8）、Maria 存储引擎
Maria 存储引擎是新开发的引擎，其设计目标是用来取代原有的 MyISAM 存储引擎，从而成为 MySQL 默认的存储引擎。
上述引擎中，InnoDB 是事务安全的存储引擎，设计上借鉴了很多 Oracle 的架构思想，一般而言，在 OLTP应用中，InnoDB 应该作为核心应用表的首先存储引擎。InnoDB 是由第三方的 Innobase Oy 公司开发，现已被Oracle 收购，创始人是 Heikki Tuuri，芬兰赫尔辛基人，和著名的 Linux 创始人 Linus 是校友。