java面试题汇总(四)-----数据库

1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。

employee: eid, ename, salary, dept_id

select * from employee order by dept_id desc, salary;

2、ORACLE和MySQL的区别。

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

2. Oracle支持大并发，大访问量，是OLTP最好的工具。(OLTP[联机事务处理])(OLAP[数据仓库处理 有了基础大数据后 根据数据分析 市场定位])。

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

4.Oracle和Mysql操作上的一些区别【开发人员接触的】

①主键Mysql一般使用自动增长类型，在创建表时只要指定表的主键为auto_increment,插入记录时，不需要再指定该记录的主键值，Mysql将自动增长；Oracle没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值付给该字段即可；只是ORM框架是只要是native主键生成策略即可。

②单引号的处理MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

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

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

⑤空字符的处理MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构,导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

⑥字符串的模糊比较MYSQL里用 字段名 like '%字符串%',ORACLE里也可以用 字段名like '%字符串%'但这种方法不能使用索引,速度不快。【like ‘%’开头 无法使用索引 不使用开头 可以使用索引】

⑦Oracle实现了ANSII SQL中大部分功能，如，事务的隔离级别、传播特性等而Mysql在这方面还是比较的弱

3、存储过程和函数的区别。

1). 可以理解函数是存储过程的一种 ，都是预编译的 【块语句每次运行都会编译 存储过程块 一次编译多次运行 效率更高】
   Plsql块语句
       Begin
       End  
       存储过程块
       Create procedure prg_add()
       As
     Begin
     End；
2). 函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值
3). 函数return返回值没有返回参数模式，存储过程通过out参数返回值,如果需要返回多个参数则建议使用存储过程 【函数oracle 在函数可以使用in和out mysql不能使用out】
4). 在sql数据操纵（DML）语句中只能调用函数而不能调用存储过程

4、Oracle导入和导出方式

使用oracle工具exp/imp   

使用plsql相关工具
1. 导入/导出的是二进制的数据，

2.plsql导入/导出的是sql语句的文本文件

5、Oracle分页方法

Oracle中使用rownum来进行分页,这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的select * from
  ( select rownum r,a from tabName where rownum <= 20 )where r > 10

6、mysql的分页方法

select * from content order by id desc limit 0, 10

在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：

select * from content order by id desc limit 10000, 10

一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过2种方式：

一，子查询的分页方式来提高分页效率，飘易用的SQL语句如下：

SELECT * FROM `content` WHERE id (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。（via）通过explain SQL语句发现：子查询使用了索引！

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where

2 SUBQUERY content index NULL PRIMARY 4 NULL 27085 Using index
 

7、解释什么是死锁，如何解决Oracle中的死锁？ 

简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行

解决办法：

1). 查找出被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode

from v$locked_object a,dba_objects b

where b.object_id = a.object_id;

select b.username,b.sid,b.serial#,logon_time

from v$locked_object a,v$session b

where a.session_id = b.sid order by b.logon_time;

2). 杀进程中的会话

alter system kill session "sid,serial#";

8、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

employee: eid, ename, salary, dept_id

 

select count(*), a.dept_id

from employee a

where

a.salary > (select avg(b.salary) from employee b where b.dept_id = '本部门')

group by a.dept_id

order by a.dept_id

9、存储过程与触发器必须讲，经常被面试到?

create or replace procedure insert_Student (_name varchar, _age int , out_id int)

declear

       a varchar

begin

       insert into studentvalue(null,_name,_age);

       select max(stuId)into _id from student;

end;

 

call insert_Student('wfz',23,@id);

select @id;

 

mysql> create trigger update_Student BEFORE update on student FOR EACH ROW

-> select * from student;

触发器不允许返回结果

 

create trigger update_StudentBEFORE update on student FOR EACH ROW

insert into  student value(null,'zxx',28);

mysql的触发器目前不能对当前表进行操作

 

create trigger update_StudentBEFORE update on student FOR EACH ROW

delete from articles  where id=8;

这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子

这里要注意使用OLD.id

 

触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。而UCH没有用触发器，效率和数据处理能力都很低。

存储过程的实验步骤：

mysql> delimiter |

mysql> create procedure insertArticle_Procedure (pTitle varchar(50),pBid int,out

 pId int)

    -> begin

    -> insert into article1value(null,pTitle,pBid);

    -> select max(id) into pId fromarticle1;

    -> end;

    -> |

Query OK, 0 rows affected (0.05sec)

 

mysql> callinsertArticle_Procedure('传智播客',1,@pid);

    -> |

Query OK, 0 rows affected (0.00sec)

 

mysql> delimiter ;

mysql> select @pid;

@pid

3

 

 

 

1 row in set (0.00 sec)

mysql> select * fromarticle1;

id	Title	bid
1	Test	1
2	chuanzhiboke	1
3	传智播客	1

 

 

 

 

3 rows in set (0.00 sec)

 

触发器的实验步骤：

create table board1(id intprimary key auto_increment,name varchar(50),ar

ticleCount int);

 

create table article1(id intprimary key auto_increment,title varchar(50)

,bid int referencesboard1(id));

 

delimiter |

 

create triggerinsertArticle_Trigger after insert on article1 for each ro

w begin

    -> update board1 setarticleCount=articleCount+1 where id= NEW.bid;

    -> end;

    -> |

 

delimiter ;

 

insert into board1 value(null,'test',0);

 

insert into article1value(null,'test',1);

还有，每插入一个帖子，都希望将版面表中的最后发帖时间，帖子总数字段进行同步更新，用触发器做效率就很高。下次课设计这样一个案例，写触发器时，对于最后发帖时间可能需要用declare方式声明一个变量，或者是用NEW.posttime来生成。

 

10、数据库三范式是什么?

第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式。

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

 

第二范式（2NF）：在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据库表中的每个实例或行必须可以被唯一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。

 

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

 

第三范式的要求如下：

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征：

   1，每一列只有一个值

   2，每一行都能区分。

   3，每一个表都不包含其他表已经包含的非主关键字信息。

例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

 

11、说出一些数据库优化方面的经验?

1. 程序优化，用PrepareedStatement进行增删改查

2. 程序优化，尽量批量处理，避免逐条处理，减小IO数

3. 查询结果不要用*来查询所有字段，要明确指明结果字段

4. 减少多表连接数，尽量少的表进行连接

5. 表连接时，尽量用主键进行连接或用唯一索引

6. 表的查询多时，一定建立索引

7. 根据查询条件，建立索引，如果查询条件不止一个时，使用组合索引

8. 在查询条件表达式的左侧尽量不要使用函数，否则索引失效

9. 如果不得不用函数，则建立函数索引

10. 使用合适的索引，例如时间索引、哈希索引、聚簇索引

11. 如果有like话，尽量避免%xxx%两侧都有%的条件，单侧%可以使用索引，多侧不可以

12. 尽量不用数据库，使用缓存

13. 可以考虑用nosql数据库提高效率

14. SQL的条件表达式，在Oracle中，是按倒序使用索引的

15. 如果用DDL改动了数据库表字段，需要重建索引，不然索引失效

16. SQL尽量不要有多余的空格和换行

17.使用分布式数据库

18. 合理创建表分区表空间

19.建立索引时字段不能有null值

20.使用数据库连接池

12、union和union all有什么不同?

union和union all都是合并结果集

区别是：

1. union去除两个结果集的重复记录，union all不去除重复记录，是两个结果集的加和

2. union效率低，union all效率高

13.用一条SQL语句查询出每门课都大于80分的学生姓名

name   kecheng   fenshu

张三    语文      81

张三    数学      75

李四    语文      76

李四    数学      90

王五    语文      81

王五    数学      100

王五    英语      90

 

答案：

A：select distinct name from score where name not in (select distinct name from score where fenshu <=80)

B：select distinct name t1 from score where 80< all (select fenshu from score where name=t1);

14.所有部门之间的比赛组合

一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

select a.name, b.name

from team a, team b

where a.name > b.name

15.显示文章标题，发帖人、最后回复时间

文章表：article_id, title, post_user, post_date

回复表：reply_id, article_id, reply_time, content

select

a.title, a.post_user, r.reply_time

from reply r

left join article a on a.article_id = r.article_id

where

r.reply_id =

(

   select max(re.reply_id)

   from reply re

   where

   re.article_id = r.article_id

)

16.删除除了id号不同,其他都相同的学生冗余信息

学生表(student)如下:

id号  学号      姓名   课程编号 课程名称  分数

id    sid        name  cno      cname    score

1     2005001  张三   0001     数学      69

2     2005002  李四   0001     数学      89

3     2005001  张三   0001     数学      69

A: delete from student where id not in(select min(id) from student group by sid, name, cno, cname, score)

18.求出发帖最多的人：

select max(post_count), b.post_user_id, u.name

from

(

  select count(*) as post_count, a.post_user_id

  from article a

  group by a.post_user_id

) b

left join user u on u.user_id = b.post_user_id。

19、一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?

方案一：update user set score=0;

方案二：假设上面的代码要执行好长时间，超出我们的容忍范围，使用alter table：

drop columnscore;alter table user add column score int。

在Oracle中，动了表结构，索引失效

方案三：使用Java程序，for循环，效率最差

方案四：使用存储过程loop循环，效率其次差

21、注册Jdbc驱动程序的三种方式

1. Class.forName(driver)

2. ClassLoader.loadClass(driver)

3. new XXXDriver();

22、用JDBC如何调用存储过程

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql:///test";

Connection cn = DriverManager.getConnection(url, "root", "root");

String sql = "{call insert_student(?,?,?)}";

CallableStatement cstmt = cn.prepareCall(sql);

cstmt.registerOutParameter(3, Types.INTEGER);

cstmt.setString(1, "wangwu");

cstmt.setInt(2, 25);

cstmt.execute();

// get第几个，不同的数据库不一样，建议不写

System.out.println(cstmt.getString(3));

23、JDBC中的PreparedStatement相比Statement的好处

一个sql命令在数据库执行的步骤为：语法检查，语义分析，编译成内部指令，缓存指令，执行指令等过程。

1. PrepareStatement第一次执行某SQL时可以把最终结果缓存到数据中，以后再执行同一格式的SQL时，不再进行优化，直接使用缓存中的优化结果，效率比较高。

2.参数传值，可以防止SQL注入

24、Class.forName的作用?为什么要用?

答：按参数中指定的字符串形式的类名去搜索并加载相应的类，如果该类字节码已经被加载过，则返回代表该字节码的Class实例对象，否则，按类加载器的委托机制去搜索和加载该类，如果所有的类加载器都无法加载到该类，则抛出ClassNotFoundException。加载完这个Class字节码后，接着就可以使用Class字节码的newInstance方法去创建该类的实例对象了。

有时候，我们程序中所有使用的具体类名在设计时（即开发时）无法确定，只有程序运行时才能确定，这时候就需要使用Class.forName去动态加载该类，这个类名通常是在配置文件中配置的，例如，spring的ioc中每次依赖注入的具体类就是这样配置的，jdbc的驱动类名通常也是通过配置文件来配置的，以便在产品交付使用后不用修改源程序就可以更换驱动类名。

25、大数据量下的分页解决方法。

查询结果集如果记录数比较多时，服务器内存和浏览器内存都可能溢出，另外，数据量太大客户端的性能会降低，滚动条较小，操作也不方便，需要数据库分页查询。

SQL Server分页：

select top #pageSize# * from students where id not in

(select top #pageSize# * (#pageNumber#-1) id from students order by id) order by id

My SQL分页：

select * from students order by id limit #pageSize#*(#pageNumber#-1),#pageSize#

Oracle分页：

select * from

(

    select *, rownum rid

    from

    (

        select * from students order by postime desc

    )

    where

    rid<=#pagesize#*#pagenumber#

) as t

where t.rid>#pageSize#*(#pageNumber#-1)

27、说出数据连接池的工作机制是什么?

J2EE服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量有配置参数决定。当使用的池连接调用完成后，池驱动程序将此连接标记为空闲，其他调用就可以使用这个连接。

实现方式，返回的Connection是原始Connection的代理，代理Connection的close方法不是真正关连接，而是把它代理的Connection对象还回到连接池中。

28、为什么要用 ORM? 和 JDBC有何不一样?

ORM是对象和关系型数据库映射，是把Java中的JavaBean对象和数据库表进行映射，使数据库表中的记录和JavaBean对象一一对应，从而大大简化原来直接使用JDBC时，手工拼写SQL带来的不便。

ORM通过配置文件，使数据库表和JavaBean类对应起来，提供简便的操作方法，增、删、改、查记录，不再拼写字符串生成SQL，编程效率大大提高，同时减少程序出错机率，增强数据库的移植性，方便测试。但是原生的JDBC具有更强的灵活性，适合复杂多变的SQL应用。

常用的ORM框架有：Hibernate、MyBatis、TopLink、OJB

29. 数据库大数据处理

1. 大数据可以采用分布式数据库和建立分区表(PARTITION)

2. 建立有效索引：主键索引、联合索引、倒序索引、函数索引(INDEX)

3. 使用物化视图(MATERIALIZED VIEW)

4. 使用存储过程(PROCDUDER)

5. 读写分离(golden gate软件实现)

6. 归档旧数据(新旧数据查询，保证新数据的效率提高)，程序做调整，旧数据和新数据查询页面分离

 

31. 什么是事务？什么是锁？

答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

  锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

32. 维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

我是这样做的，尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

33. 什么是内存泄漏？

答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

34.触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

35. 常用的oracle操作的命令

操作的命令：

    Sqlplus  登陆用户

    Conn    连接数据库

    Disconn  断开数据库连接

    Exit或quit退出sql*plus

    Pass     修改用户口令

    Show user 查看当前用户

    List      列出sql缓冲区的内容

    Run或/   执行缓冲区中的所有内容

    Save 文件名 把缓冲区的内容保存到sql脚本文件

    Get 文件名   将sql脚本文件中的内容加载到缓冲区

    Start或@文件名  将指定sql脚本文件加载到缓冲区并执行

    Edit      编辑缓冲区或sql脚本文件的内容

    Spool 文件名 把sql*plus中的输入结果复制到指定文件中

    Spool off 停止sql*plus中的输出结果复制，并关闭文件

    Help 命令名  查看某个命令的详细帮助信息。

36， 索引的优缺点
   优点：
  1 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
   2 可以大大加快数据的检索速度，这也是创建索引的最主要的原因
   3  可以加速表和表之间的连接，
   4  在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。
  缺点：
   1  降低了dml操作的速度
   2  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
   3  索引需要占据物理空间，创建索引会加大储存空间

37.使用oracle伪列删除表中重复记录：？
Delete  table t  where t.rowid!=(select  max(t1.rowid)  from  table t1 where  t1.name=t.name)