获取java Set集合的值 java获取结果集中所有数据

数据库汇总篇

标签（空格分隔）： 面试

• 数据库汇总篇

• 什么是存储过程有哪些优缺点
• 索引

• 什么是索引
• 优缺点
• 索引是否越多越好

• 事务

• 什么是事务
• 事务的语句
• 关于savepoint

• 乐观锁和悲观锁

• 使用悲观锁
• 使用乐观锁

• dropdelete与truncate
• 视图
• 范式
• SQL注入

• 什么是
• 防止SQL注入
• MyBatis防止SQL注入

---

什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。

更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

• 存储过程是一个预编译的代码块，执行效率比较高
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
• 可以一定程度上确保数据安全

简单步骤如下：

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER ;

索引

什么是索引

排好序的快速查找的数据结构，这些数据结构以某种方式指向数据，一般为B树或B+树，有哈希索引。索引本身也很大，往往以索引文件的形式存储在磁盘上。

优缺点

优点：
1. 降低查找效率，降低IO成本
2. 降低排序成本，降低CPU消耗

劣势：
1. 索引也是一个表，保存主键与索引字段，占空间
2. 降低更新表的速度，不仅需要保存数据，还需要保存更新索引

需要索引的情况：
1. 主键自动建立索引且唯一
2. 频繁作为查询条件的字段应该创建索引
3. 与其他表关联的字段，外键关系建立索引
4. 查询中排序的字段
5. 查询中统计成分组字段

不需要建立索引的情况：
1. 记录太小，一般300万以上，性能才有问题
2. 经常增删改的表
3. 若序列包含许多重复内容，则没有必要

索引是否越多越好

不是！“多”要看数据访问量，要“合理”，并且索引本身也是需要更新和维护的，索引越多，更新数据的速度越慢。

事务

什么是事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务具有以下4个基本特征：
1. Atomic（原子性）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败。
2. Consistency（一致性）：只有合法的数据可以被写入数据库，否则事务应该将其回滚到最初状态。
3. Isolation（隔离性）：事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立。
4. Durability（持久性）：事务结束后，事务处理的结果必须能够得到固化。

事务的语句

• 开始事物：BEGIN TRANSACTION
• 提交事物：COMMIT TRANSACTION
• 回滚事务：ROLLBACK TRANSACTION

关于savepoint

用户在事务（transaction）内可以声明（declare）被称为保存点（savepoint）的标记。保存点将一个大事务划分为较小的片断。

用户可以使用保存点（savepoint）在事务（transaction）内的任意位置作标记。之后用户在对事务进行回滚操作（rolling back）时，就可以选择从当前执行位置回滚到事务内的任意一个保存点。例如用户可以在一系列复杂的更新（update）操作之间插入保存点，如果执行过程中一个语句出现错误，用户可以回滚到错误之前的某个保存点，而不必重新提交所有的语句。

在开发应用程序时也同样可以使用保存点（savepoint）。如果一个过程（procedure）内包含多个函数（function），用户可以在每个函数的开始位置创建一个保存点。当一个函数失败时，就很容易将数据恢复到函数执行之前的状态，回滚（roll back）后可以修改参数重新调用函数，或执行相关的错误处理。

当事务（transaction）被回滚（rollback）到某个保存点（savepoint）后，
Oracle将释放由被回滚语句使用的锁。其他等待被锁资源的事务就可以继续
执行。需要更新（update）被锁数据行的事务也可以继续执行。

乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段：

• 悲观锁：假定会发生并发冲突（悲观），屏蔽一切可能违反数据完整性的操作
• 乐观锁：假设不会发生并发冲突（乐观），只在提交操作时检查是否违反数据完整性。

使用悲观锁

悲观锁流程：
1. 在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4. 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

排它锁：

用法： select … for update;
例如：select * from goods where id = 1 for update;
对 查询结果集 中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

排他锁的申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时。

使用悲观锁：

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。 set autocommit=0;

//0.开始事务
begin;/ begin work; /start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

使用乐观锁

在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

drop、delete与truncate

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

• delete和truncate只删除表的数据不删除表的结构
• 速度,一般来说: drop> truncate >delete
• delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
  如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

drop、delete与truncate分别在什么场景之下使用？

• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表而删除所有数据的时候用truncate，不但将数据全部删除，而且重新定位自增的字段

视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

• 只暴露部分字段给访问者，所以就建一个虚表，就是视图。
• 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

范式

范式就是一张数据表的表结构所符合的某种设计标准的级别。

• 第一范式（1NF）：
  数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
• 第二范式（2NF）：
  2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。就是要有主键，要求其他字段都依赖于主键。为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
• 第三范式（3NF）：
  3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。要消除传递依赖，方便理解，可以看做是“消除冗余”，就是各种信息只在一个地方存储，不出现在多张表中。

https://www.zhihu.com/question/24696366

SQL注入

什么是

SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

具体来说，它是利用现有应用程序，将（恶意）的SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

例如，如果我们要查询数据库中Id值为1的工作信息，而且在页面显示了该工作的Id，Description，Min Lvl和Max Lvl等信息。

SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE (job_id = 1)

假设现在要求我们获取Department表中的所有数据，而且必须保留WHERE语句，那我们只要确保WHERE恒真就OK了，SQL示意代码如下：

SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs
WHERE (job_id = 1) OR 1 = 1

上面我们使得WHERE恒真，所以该查询中WHERE已经不起作用了，其查询结果等同于以下SQL语句。

SELECT job_id, job_desc, min_lvl, max_lvl
FROM jobs

防止SQL注入

对于SQL注入，要注意：
1. 永远不要信任用户的输入，要对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号和双”-“进行转换等。
2. 永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。安全性很高
3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4. 不要把机密信息明文存放，请加密或者hash掉密码和敏感的信息。
5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装，把异常信息存放在独立的表中。

具体方式如下：

第一种方法：使用预编译语句。

Connection conn = getConn();//获得连接  
String sql = "select name from user where id= ?";  
PreparedStatement pstmt = conn.prepareStatement(sql);  
pstmt.setString(1, userId);  
ResultSet rs=pstmt.executeUpdate();

为什么上面的代码就不存在SQL注入了呢？因为使用了预编译语句，预编译语句在执行时会把select name from user where id= ?语句事先编译好，这样当执行时仅仅需要用传入的参数替换掉？占位符即可。而对于不使用预编译的情况，程序会先生成sql语句，然后带着用户传入的内容去编译，这恰恰是问题所在。

第二种方法：使用存储过程

存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过调用存储过程并给定参数（如果该存储过程带有参数）就可以执行它，也可以避免SQL注入攻击

Connection conn = getConn();  
stmt = conn.prepareCall("{call name_from_user(?,?)}");    
stmt.setInt(1,2);    
stmt.registerOutParameter(2, Types.VARCHAR);    
stmt.execute();    
String name= stmt.getString(2);

上面代码中对应的存储过程如下：

use user;  
delimiter //  
create  procedure name_from_user(in user_id int,out user_name varchar(20))  
begin  
    select name into user_name from user where id=user_id;  
end  
//  
delimiter ;

MyBatis防止SQL注入

mapper文件如下：

<select id="getNameByUserId" resultType="String">  
        SELECT name FROM user where id = #{userId}  
</select>

对应的java文件为：

public interface UserMapper{  
    String getNameByUserId(@Param("userId") String userId);  
}

可以看到输入的参数是String类型的userId,当我们传入注入语句userId="34;drop table user;"后，打印的语句是这样的：

select name from user where id = ?

不管输入何种userID，他的sql语句都是这样的。这就得益于mybatis在底层实现时使用预编译语句。数据库在执行该语句时，直接使用预编译的语句，然后用传入的userId替换占位符？就去运行了。不存在先替换占位符？再进行编译的过程，因此SQL注入也就没有了生存的余地了。

简单说，#{}是经过预编译的，是安全的；${}是未经过预编译的，仅仅是取变量的值，是非安全的，存在SQL注入。

• #{}：相当于JDBC中的PreparedStatement
• ${}：是输出变量的值

因此在编写mybatis的映射语句时，尽量采用“#{xxx}”这样的格式。若不得不使用“${xxx}”这样的参数，要手工地做好过滤工作，来防止sql注入攻击。