数据库篇

一、JDBC连接数据库步骤(以MYSQL为例)

1、加载JDBC驱动程序：

通过Class类的forName方法实现，并将驱动地址放进去

成功加载后，会将Driver类的实例注册到DriverManager类中。

2、提供JDBC连接的URL 、创建数据库的连接

?要连接数据库，需要向java.sql.DriverManager请求并获得Connection对象，

该对象就代表一个数据库的连接。

?使用DriverManager的getConnectin()方法传入指定的欲连接的数据库的路径、数据库的用户名和密码。

Connection con=DriverManager.getConnection(url , username , password);

&&&:"jdbc:mysql://localhost/test?user=root&password=123&useUnicode=true&characterEncoding=utf-8”;

3、创建一个Statement

?要执行SQL语句，必须获得java.sql.Statement实例

?执行静态SQL语句。通常通过Statement实例实现。

?执行动态SQL语句。通常通过PreparedStatement实例实现。

String sql = “”;

Statement st = con.createStatement() ;

PreparedStatement pst = con.prepareStatement(sql) ;

4、执行SQL语句

Statement接口提供了executeQuery、executeUpdate、execute三种方法

executeQuery：执行select语句，返回ResultSet结果集

ResultSet rst = pst.executeQuery();

? executeUpdate：执行insert、update、delete语句

pst.executeUpdate();

5、关闭JDBC对象

操作完成以后要把所有使用的JDBC对象全都关闭，以释放JDBC资源。

二、数据库连接池

数据库连接池的优点运行原理:在我们不使用数据库连接池的时候，每次访问数据库都需要创建连接，使用完成之后需要释放关闭连接，而这样是很耗费资源的。当我们使用数据库连接池的时候，在tomcat启动的时候就创建了指定数量的连接，之后当我们程序使用的时候就直接从连接池里面取，而不需要创建，同理，当我们使用完的时候也不需要关闭连接，而是将连接返回到连接池中，供其他请求继续使用。

DBCP：比较稳定。

C3P0: 性能比较高。

三、mysql的数据库导入导出

配置：

首先找到mysql的安装目录，进入bin目录下复制路径

将mysql的bin目录粘贴在计算机环境变量的path中

授权：

登录mysql

将某张表的某个权限赋给某个用户

grant [select,insert,update,delete,create,drop] on [databaseName].[tableName] to [userName]@[userIP] identified by [‘连接口令’]

grant select,insert,update,delete,create,drop on oa_ssh.user to root@[IP] identified by ‘root’;

将所有库的所有权限赋给某个用户

grant all privileges on . to [userName]@[userIp] identified by [‘连接口令’]

grant all privileges on . to root@[IP] identified by ‘root’;

将所有库的所有权限赋给所有用户

grant all privileges on . to root@’%’ identified by ‘root’;

导出本地数据库：

mysqldump -u 用户名 -p 数据库名 > 磁盘：导出的文件名(加后缀)

远程导出数据库：

mysqldump -h IP -u 用户名 -p 数据库名称 >导出的文件名(加后缀)

远程导出数据表：

mysqldump -u root -p -d --add-drop-table 数据库名称 > 导出文件

名(加后缀)

导入数据：

mysql -u root -p登录成功后 ==》 source 磁盘：导入的文件名(加后缀)

四、jdbc分段批量提交的时候出现异常怎么处理?

通过Map来解决性能问题。首先在分段批量提交的时候，我们不采用事务，这样就保证了合法的数据就自动提交，不合法的数据就自己自动进行回滚，为了避免不合法数据影响后续合法数据的提交，采用定义业务规则字典表，实现对数据的验证，将不合法的数据记录下来，供用户进行后续处理，而合法的数据就全部提交。

五、jdbc批量处理数据

批量处理数据:(代码优化:提高程序执行性能)

降低了java程序代码(客户端)和数据库之间的 网络通信的次数。

在jdbc中进行批量插入的核心API为 addBatch,executeBatch

大数据量的插入问题:（jdbc,hibernate,ibatis）

1.每次只插入一条和数据库交互多次(很耗时间)

2.批量插入和数据库只交互一次(内存溢出)

3.分段批量插入(推荐)

jdbc批量处理数据是通过PreparedStatement对象的 addbatch(), executebatch（） clearbatch()进行和数据库的交互。通常我们使用分段批量处理的方式 这样可以提高程序的性能 ，防止内存溢出。

1.每个sql语句都和数据库交互一次(非批量操作)

2.只和数据库交互一次(批量操作)(内存溢出)

当数据达到一定额度的时候就和数据库进行交互，分多次进行(分段批量操作)

(500或者1000)

pst.addBatch();

if (i > 0 && i%1000 == 0) {

pst.executeBatch();

pst.clearBatch();

}

六、Oracle分页

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

七、Oracle的基本数据类型

Oracle的基本数据类型（常用）：

1、字符型

Char 固定长度字符串 占2000个字节

Varchar2 可变长度字符串 占4000个字节

Nvarchar2 占2000个字符（最多能存2000个字母/中文）

2、大对象型（lob）

Blob ：二进制数据 最大长度4G

Blob 用于存一些图片，视频，文件。

比如：当我们在进行文件上传时，我们一般把上传的文件存在硬盘上，可以不占用 数据库，下载时，如果项目迁移时，文件也要跟着迁移。因此我们可以把用blob把它存在数据库中。但这样也增加了数据库的负担。

Clob ：字符数据 最大长度4G，可以存大字符串 varchar2和nvarchar2都具有一定的局限性，它们长度有限，但数据库中无论用varchar2或nvarchar2类型，还是用clob，在java端都使用String接收。

3、数值型

Integer 整数类型，小的整数。

Float 浮点数类型。

Real 实数类型。

Number（p,s）包含小数位的数值类型。P表示精度，s表示小数后的位数。

Eg: number(10,2) 表示小数点之前可有8位数字，小数点后有2位。

4、日期类型

Date 日期（日-月-年） DD-MM-YY(HH-MI-SS)

Timestamp 跟date比 它可以精确到微秒。精确范围0~9 默认为6.

八、id、rowid、rownum的区别

rowid物理位置的唯一标识。

而id是逻辑上的唯一标识，所以rowid查找速度要快于id,是目前最快的

定位一条记录的方式

rowid和rownum都是"伪数列"

所谓“伪数列”也就是默认隐藏的一个数列。

rownum用于标记结果集中结果顺序的一个字段，

它的特点是按顺序标记，而且是连续的，

换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。

rownum关键字只能和<或者<=直接关联

如果是>或者=则需要给他起个别名

九、主键和唯一索引的区别？

在创建主键的同时会生成对应的唯一索引，主键在保证数据唯一性的同时不允许为 空，而唯一可以有一个为空数据项，一个表中只能有一个主键，但是一个主键可以 有多个字段，一个表中可以有多个唯一索引。

十、Preparedstatement和statement的区别

用Prepared statement进行开发。Prepared statement是预编译的，而statement不是，在每次执行sql语句的增删改时，如果是一条数据两者没差距，但如果数据量大于1，那么每次执行sql语句statement都要重新编译一次，而Prepared statement不用，Prepared statement的运行效率大于statement；从代码的可维护性和可读性来说，虽然用Prepared statement来代替statement会使代码多出几行，但这样的代码无论从可读性还是可维护性来说，都比直接使用statement的代码高很多档次；最重要的一点，从安全角度来说，使用Prepared statement可以大大提高程序的安全性，因为Prepared statement是用‘？’传参,可以防止sql注入，具有安全性，而statement用的是‘+’字符串拼接，安全性较低。

十一、数据库三范式

第一范式：数据库表中的所有字段值都是不可分解的原子值。

第二范式：需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）

第三范式：需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

十二、视图概述

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

视图的优点：

提供了另外一种级别的表安全性:隐藏了一些关键的字段

简化的用户的SQL命令

隔离基表结构的改变

十三、存储过程概述

存储过程（Stored Procedure）

可以包含逻辑判断的sql语句集合。

是经过预编译，存在于数据库中。

通过调用指定存储过程的名字（可有参，可无参）来执行。

优点：

简化了复杂的业务逻辑，根据需要可重复使用

屏蔽了底层细节，不暴露表信息即可完成操作

降低网络的通信量，多条语句可以封装成一个存储过程来执行

设置访问权限来提高安全性

提高执行效率，因为它是预编译以及存储在数据库中

缺点：

可移植性差，相同的存储过程并不能跨多个数据库进行操作

大量使用存储过程后，首先会使服务器压力增大，而且维护难度逐渐增加

存储过程的语法：

–下面是在oracle数据库下最基本的语法

–仅创建一个名为testProcedure 的无参的存储过程

–IS也可以是AS

–如果已经存在名为 testProcedure 的存储过程，下面的语法会出现 名称已被使用的错误

–解决办法：

–第一句可以写成 create or replace procedure testProcedure

–这样会替换原有的存储过程

–NULL表示任何可以正确执行的sql 语句，但至少一句

create procedure testProcedure
IS
BEGIN
NULL
END;
存储过程的参数的分类:
IN
OUT
INOUT

注意：

存储过程之间可相互调用

存储过程一般修改后，立即生效。

十四、索引概述

1、索引的概念

索引就是为了提高数据的检索速度。

数据库的索引类似于书籍的索引。

在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。

在数据库中，索引也允许数据库程序迅速地找到表中的数据，

而不必扫描整个数据库.

2、索引的优点

1.创建唯一性索引，保证数据库表中每一行数据的唯一性

2.大大加快数据的检索速度，这也是创建索引的最主要的原因

3.减少磁盘IO（向字典一样可以直接定位）

3、索引的缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2.索引需要占用额外的物理空间

3.当对表中的数据进行增加、删除和修改的时候，

索引也要动态的维护，降低了数据的维护速度

4、索引的分类

1.普通索引和唯一性索引

普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)

唯一性索引：保证在索引列中的全部数据是唯一的

CREATE unique INDEX mycolumn_index ON mytable (myclumn)

1. 单个索引和复合索引
2. 单个索引：对单个字段建立索引
3. 复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，
4. 最多16个字段
5. CREATE INDEX name_index ON userInfo(firstname,lastname)

3.顺序索引，散列索引,位图索引

十五、必背的sql语句

1：oracle 分页

select * from (select t.*, rownum rn from (select * from menu order by id desc) t where rownum < 10) where rn >=5

2: mysql 分页

select * from music where id limit 5,5

3：oracle中如何快速将一张表的数据复制到另外一张表中(另外一张表不存在，另外一张 表存在，但数据为空)

1、.不存在另一张表时：

create table 新表 as select * from 将要复制的表

2、存在另一张表时：

insert into 新表名 select 字段 from 将要复制的表名

4：音乐专辑

查询出special app:ds:special表中的id 专辑名 并下面有多少首歌曲

Select s.id , min(s.sname),count(m.mid) from special s inner
join ms m on s.id=m.id group by s.id

5：快速删除一张表（不可事物回滚，也就是没有日志记录）

TRUNCATE from 表名

6：inner join

select 查找信息 from 表名 1 inner join 表名2 on 表名1.列名 = 表名2.列名

7：left join

左外连接 select 查找信息 from 表名1 left join 表名2 on 表名1.列名 = 表名2.列名

8：right join

右外连接 select 查找信息 from 表名1 right join 表名2 on 表名1.列名 = 表名2.列名

9：oracle中查询遍历树形结构（start with）

select * from extmenu
start with pid=1
connect by prior id = pid
快速删除父节点以及父节点下的所有节点：
Delete from extmenu where id in (
elect * from extmenu
start with pid=1
connect by prior id = pid
)

10：查询出来60-70,80-90,95-100学生的信息

select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100
select * from stu where chengji > 60 and chengji < 70 or chengji > 80 and chengji < 90 or chengji > 95 and chengji < 100

11：用exists替换in------进行联表查询

select * from dept where exists(select * from emp where emp.deptno=dept.deptno);

或

select * from dept d inner join emp e on d.deptno = e.deptno(只查询出两表共同拥有的字段数据)

12：删除表中的重复数据：

delete from xin a where a.rowid != (
select max(b.rowid) from xin b
where a.name = b.name
);

13：row_number()，rank() over ，dense_rank() over 按工资排序

select sal,
row_number() over(order by sal desc) rank1,
rank() over(order by sal desc) rank,
dense_rank() over(order by sal desc) drank
from emp

14：

select * from (select emp.* from(
dense_rank() over(partition by departNo order by sal desc)
rk from emp )
Where rk=4

十六、ibatis批量

this.getSqlMapClientTemplate().execute(
new SqlMapClientCallback() {
public Object doInSqlMapClient(
SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
for (int i = 0, n = list.size(); i < n; i++) {
executor.insert(
"productAttach.insertProductAttach",
list.get(i));
}
executor.executeBatch();
return null;
}
});
ibatis,jdbc,hibernate的分段的实现:
都应该在组装list的时候进行拆分（如：action层加入）
if(list.size() % 1000 == 0)
{
productAttachService.addBatch(list);
list.clear();
}
if (list.size() > 0)
productAttachService.addBatch(list);