【java面试】数据库篇

1.SQL语句分为哪几种？

SQL语句主要可以划分为以下几类： 
DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作。 
包括：CREATE、DROP、ALTER、RENAME、 TRUNCATE等 
DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作。 
包括：INSERT、DELETE、UPDATE、SELECT等 
DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。 
包括：GRANT、REVOKE等 
Transaction Control:事务控制 
包括：COMMIT、ROLLBACK、SAVEPOINT等

2.Delete、truncaate、drop都是删除语句，它们有什么分别？

delete 属于DML语句，删除数据，保留表结构，需要commit，可以回滚，如果数据量大，很慢。 
truncate 属于DDL语句，删除所有数据，保留表结构，自动commit，不可以回滚，一次全部删除所有数据，速度相对较快。 
Drop属于 DDL语句，删除数据和表结构,不需要commit，删除速度最快。

3.Where和having都是条件筛选关键字，它们有什么分别？

WHERE是在数据分组前进行条件过滤， HAVING子句是在数据分组后进行条件过滤，WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。

4.数据库事务

事务是最小的逻辑工作单元。

事务特性(ACID)

原子性:一个事务里面的操作要么不做,要么都做;

一致性:事务启动之前和启动之后要保持平衡状态

例如完整性约束a+b=10，一个事务改变了a，那么b也应随之改变。

隔离性:在一个会话里面读取不到另一个会话里未提交的数据.

永久性:事务一经提交永不回退。

5.如何删除表中的重复数据，只保留一条记录？

1.通过创建临时表 
create  cf table tbl_tmp as select distinct * from tbl; 
truncate table tbl;//清空表记录 
insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。

2.利用rowid 
delete from tbl where rowid in 
(select a.rowid from tbl a, tbl b 
where a.rowid>b.rowid 
and a.col1=b.col1 and a.col2 = b.col2)

3.利用max或min函数 
delete from tbl a where rowid not in 
(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2); 
//这里max使用min也可以

delete from tbl where rowid not in 
(select max(rowid) from tbl tgroup by t.col1, t.col2);

4.如何通过sql语句完成分页？

Orcle

1.select t.*,rownum rn from tab t where t.rn<=10(rownum 从1开始)

2.select t.* from (select tab.*,rownum rn  from tab where rn<10) t where t.rn>=5(rownum 不从1开始)

3.select t.* from (select tab.*,rownum rn from tab) t where t.rn bettewen 5 and 10(rownum 不从1开始)

Mysql

1.SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
2. SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目： 
3.SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

5.Oracle数据优化

1.建库:第一步我们先建一个tableaspace(假设表空间名为TEST)， 
然后建立用户TEST(一般情况下用户名和表空间同名)， 
建立的用户TEST使用已建立的tablespace(TEST). 
这样做是避免访问上产生热点。 

2.建表:根据数据库三大范式建表

3.索引:在经常查询的字段建立索引

注意:如果表的列很少,不适合建索引.

当执行过多次的insert,delete,update操作后,会产生索引碎片,影响查询速度

4.sql优化:尽量使sql语句使用到索引,避免全表查询

 产生全表查询的操作符:not in,<>,is null,is not null,like '%%'

5.经常查询的SQL可以写成存储过程

6.当表数据达到十几G到几百G是,可考虑对表进行分区

6.JDBC中的Statement 和PreparedStatement的区别？ 
PreparedStatement是预编译的SQL语句，效率高于Statement。 
PreparedStatement支持?操作符，相对于Statement更加灵活。 
PreparedStatement可以防止SQL注入，安全性高于Statement。

 注意:当sql语句只执行一次时用Statement,因为PreparedStatement开销大

7.索引的缺点

1.需要占用物理空间；

2.当对表数据进行增加，修改和删除时，索引也需要动态维护，降低数据维护速度。

8.日期格式转换

Mysql

字符串转日期，或日期转字符串date_format(dateString,'%Y-%m-%d %H:%i:%s')

Oralce

字符串转日期  to_date('2017-06-08 15:15','YYYY-MM-DD HH24:MI')

日期转字符串  to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') from dual

结果为：

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS PM DY）
-------------------------------------
2017-06-08 15:19:15 下午 星期四

9.数据库连接池

数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接的数量﹑使用情况，为系统开发﹑测试及性能调整提供依据。 
优点：

第一、减少连接创建时间。连接池中的连接是已准备好的、可重复使用的，获取后可以直接访问数据库，因此减少了连接创建的次数和时间。

第二、简化的编程模式。当使用连接池时，每一个单独的线程能够像创建一个自己的JDBC连接一样操作，允许用户直接使用JDBC编程技术。

第三、控制资源的使用。如果不使用连接池，每次访问数据库都需要创建一个连接，这样系统的稳定性受系统连接需求影响很大，很容易产生资源浪费和高负载异常。连接池能够使性能最大化，将资源利用控制在一定的水平之下。连接池能控制池中的连接数量，增强了系统在大量用户应用时的稳定性。

10.union all 和union的区别

Union All操作仅仅是简单的将两个子查询结果集直接求并操作，并不会剔除掉两者结果集中重复的部分，而Union操作除了会剔除掉结果集中重复的部分以外，还会对结果集进行排序（其实执行的实质逻辑应该是先将某一子结果集进行排序，然后再判断是否有重复的数据，若有则删除掉重复的数据）。

小tips：由于Union需要对查询结果集进行排序操作，当数据量较大时，若非特殊需要，尽量不要使用Union操作，而改用Union All操作，然后对Union All出来的结果执行去重操作即可，这样会使得查询的效率大大的增强。

11.mysql和oracle的区别

1.  Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。

2. Oracle支持大并发，大访问量，是OLTP最好的工具。

3. 安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。

4.Oracle也Mysql操作上的一些区别

   ①自动增长类型的处理

     MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

     ②单引号的处理

 MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。

③分页的SQL语句的处理 

MYSQL处理分页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；ORACLE处理分页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80

④ 长字符串的处理 

ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。 

⑤空字符的处理 

MYSQL的非空字段允许为空字符串，ORACLE里定义了非空字段就不容许为空字符串。

⑥字符串的模糊比较 

MYSQL里用 字段名 like '%字符串%',ORACLE里也可以用 字段名 like '%字符串%' 但这种方法不能使用索引, 速度不快。

⑦日期字段的处理

MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒