面试题 之 数据库部分 八

面试题 之 Java基础部分 八 1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。

select * from emp order by deptid desc, salary asc;

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

empno ename sal deptno 1 zs 1000 1 2 ls 1100 1 3 ww 1100 1 4 zl 900 1 5 lz 1000 2 1 zss 1340 2 2 lss 1300 2 3 wdw 1340 2 4 zlf 440 1 5 lsz 1440 2 编写sql语句：

select empno, ename , sal , deptno from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno) ;
或
select empno, ename , sal , deptno from emp e1 where sal > (select avg(sal) from emp  e2 group by e2.deptno having e2.deptno=e1.deptno);

另外一种方式：关联查询

select e1.ename , e1.sal , e1.deptno 
from emp e1
join (select deptno,avg(sal) avgsal from emp  group by deptno ) t
on e1.deptno=t.deptno and e1.sal>t.avgsal;

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

create procedure insert_Student (_name varchar(50),_age int ,out _id int)
begin
	insert into student value(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_Student BEFORE update on student FOR EACH ROW  
insert into  student value(null,'zxx',28);

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

create trigger update_Student BEFORE 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 article1 value(null,pTitle,pBid);
    -> select max(id) into pId from article1;
    -> end;
    -> |
Query OK, 0 rows affected (0.05 sec)

mysql> call insertArticle_Procedure('软件开发',1,@pid);
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select @pid;
+------+
| @pid |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

mysql> select * from article1;
+----+--------------+------+
| id | title        | bid  |
+----+--------------+------+
| 1  | test         | 1    |
| 2  | chuanzhiboke | 1    |
| 3  | 软件开发     | 1    |
+----+--------------+------+
3 rows in set (0.00 sec)

触发器的实验步骤：

create table board1(id int primary key auto_increment,name varchar(50),ar
ticleCount int);

create table article1(id int primary key auto_increment,title varchar(50)
,bid int references board1(id));

delimiter |

create trigger insertArticle_Trigger after insert on article1 for each ro
w begin
    -> update board1 set articleCount=articleCount+1 where id= NEW.bid;
    -> end;
    -> |

delimiter ;

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

insert into article1 value(null,'test',1);

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

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

第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式） 数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

第二范式（2NF）： 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。

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

第三范式的要求如下： 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 所以第三范式具有如下特征： 1，每一列只有一个值 2，每一行都能区分。 3，每一个表都不包含其他表已经包含的非主关键字信息。 例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

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

用PreparedStatement 一般来说比Statement性能高：一个sql 发给服务器去执行，涉及步骤：语法检查、语义分析， 编译，缓存

“inert into user values(1,1,1)” -- 二进制
“inert into user values(2,2,2)” -- 二进制
“inert into user values(?,?,?)” -- 二进制

有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商） （对于hibernate来说，就应该有一个变化：empleyee->Deptment对象，现在设计时就成了employeedeptid）

看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：

1.  select e.name,e.salary where e.managerid=(select id from employee where name='zxx');
2.  select e.name,e.salary,m.name,m.salary from employees e,employees m where
 e.managerid = m.id and m.name='zxx';

表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等 将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！

sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句发给oracle服务器语法检查和编译成为内部指令缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment

还有索引对查询性能的改进也是值得关注的。 备注：下面是关于性能的讨论举例 4航班 3个城市

m*n
select * from flight,city where flight.startcityid=city.cityid and city.name='beijing';
m + n
select * from flight where startcityid = (select cityid from city where cityname='beijing');
select flight.id,'beijing',flight.flightTime from flight where startcityid = (select cityid from city where cityname='beijing')

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

假设我们有一个表Student，包括以下字段与数据：

drop table student;
create table student
(
id int primary key,
name nvarchar2(50) not null,
score number not null
);
insert into student values(1,'Aaron',78);
insert into student values(2,'Bill',76);
insert into student values(3,'Cindy',89);
insert into student values(4,'Damon',90);
insert into student values(5,'Ella',73);
insert into student values(6,'Frado',61);
insert into student values(7,'Gill',99);
insert into student values(8,'Hellen',56);
insert into student values(9,'Ivan',93);
insert into student values(10,'Jay',90);
commit;
Union和Union All的区别。 
select *
from student
where id  2 and id binary directive
	PreparedStatement pstmt = cn.prepareSatement(sql);
	ResultSet rs = pstmt.executeQuery()
	for(int j=0;jt.avgSalary;

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

代码如下：

public static void main(String[] args) {
    Connection cn = null;
    CallableStatement cstmt = null;
    try {
        //这里最好不要这么干，因为驱动名写死在程序中了
        Class.forName("com.mysql.jdbc.Driver");
        //实际项目中，这里应用DataSource数据，如果用框架，
        //这个数据源不需要我们编码创建，我们只需Datasource ds = context.lookup()
        //cn = ds.getConnection();			
        cn = DriverManager.getConnection("jdbc:mysql:///test", "root", "root");
        cstmt = cn.prepareCall("{call insert_Student(?,?,?)}");
        cstmt.registerOutParameter(3, Types.INTEGER);
        cstmt.setString(1, "wangwu");
        cstmt.setInt(2, 25);
        cstmt.execute();
        //get第几个，不同的数据库不一样，建议不写
        System.out.println(cstmt.getString(3));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
		/*try{cstmt.close();}catch(Exception e){}
		try{cn.close();}catch(Exception e){}*/
        try {
            if (cstmt != null)
                cstmt.close();
            if (cn != null)
                cn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

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

答：一个sql命令发给服务器去执行的步骤为：语法检查，语义分析，编译成内部指令，缓存指令，执行指令等过程。

select * from student where id =3 ----缓存--xxxxx二进制命令
select * from student where id =3 ----直接取-xxxxx二进制命令
select * from student where id =4 --- -会怎么干？

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

答：按参数中指定的字符串形式的类名去搜索并加载相应的类，如果该类字节码已经被加载过，则返回代表该字节码的Class实例对象，否则，按类加载器的委托机制去搜索和加载该类，如果所有的类加载器都无法加载到该类，则抛出ClassNotFoundException。加载完这个Class字节码后，接着就可以使用Class字节码的newInstance方法去创建该类的实例对象了。 有时候，我们程序中所有使用的具体类名在设计时（即开发时）无法确定，只有程序运行时才能确定，这时候就需要使用Class.forName去动态加载该类，这个类名通常是在配置文件中配置的，例如，spring的ioc中每次依赖注入的具体类就是这样配置的，jdbc的驱动类名通常也是通过配置文件来配置的，以便在产品交付使用后不用修改源程序就可以更换驱动类名。

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

答：最好的办法是利用sql语句进行分页，这样每次查询出的结果集中就只包含某页的数据内容。再sql语句无法实现分页的情况下，可以考虑对大的结果集通过游标定位方式来获取某页的数据。 sql语句分页，不同的数据库下的分页方案各不一样，下面是主流的三种数据库的分页sql：

• sql server:

	String sql = 
	"select top " + pageSize + " * from students where id not in" +
 "(select top " + pageSize * (pageNumber-1) + " id from students order by id)" + 
 "order by id";

• mysql:

String sql = 
"select * from students order by id limit " + pageSize*(pageNumber-1) + "," + pageSize;

• oracle:

String sql = 
 "select * from " +  
 (select *,rownum rid from (select * from students order by postime desc) where rid" + pageSize*(pageNumber-1);

4、用 JDBC 查询学生成绩单, 把主要代码写出来（考试概率极大）.

Connection cn = null;
PreparedStatement pstmt =null;
Resultset rs = null;
try{
	Class.forname(driveClassName);
	cn =  DriverManager.getConnection(url,username,password);
	pstmt = cn.prepareStatement(“select  score.* from score ,student “ + 
		“where score.stuId = student.id and student.name = ?”);
	pstmt.setString(1,studentName);
	Resultset rs = pstmt.executeQuery();
	while(rs.next()){
		system.out.println(rs.getInt(“subject”)  +  “    ” + rs.getFloat(“score”) );
	}
}catch(
	Exception e){e.printStackTrace();
}finally{
	if(rs != null) try{ rs.close() }catch(exception e){}
	if(pstmt != null) try{pstmt.close()}catch(exception e){}
	if(cn != null) try{ cn.close() }catch(exception e){}
}

22、这段代码有什么不足之处?

try {
	Connection conn = ...;
	Statement stmt = ...;
	ResultSet rs = stmt.executeQuery("select * from table1");
	while(rs.next()) {
	}
} catch(Exception ex) {
} 

答：没有finally语句来关闭各个对象，另外，使用finally之后，要把变量的定义放在try语句块的外面，以便在try语句块之外的finally块中仍可以访问这些变量。

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

J2EE服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量有配置参数决定。当使用的池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用就可以使用这个连接。 实现方式，返回的Connection是原始Connection的代理，代理Connection的close方法不是真正关连接，而是把它代理的Connection对象还回到连接池中。

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

orm是一种思想，就是把object转变成数据库中的记录，或者把数据库中的记录转变成objecdt，我们可以用jdbc来实现这种思想，其实，如果我们的项目是严格按照oop方式编写的话，我们的jdbc程序不管是有意还是无意，就已经在实现orm的工作了。 现在有许多orm工具，它们底层调用jdbc来实现了orm工作，我们直接使用这些工具，就省去了直接使用jdbc的繁琐细节，提高了开发效率，现在用的较多的orm工具是hibernate。也听说一些其他orm工具，如toplink,ojb等。