数据库面试题整理.doc

数据库部分1、数据库三范式是什么?第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。第三范式（3NF）：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征：1，每一列只有一个值2，每一行都能区分。3，每一个表都不包含其他表已经包含的非主关键字信息。例如，帖子表中只能出现发帖人的 id，而不能出现发帖人的 id，还同时出现发帖人姓名，否则，只要出现同一发帖人 id 的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。2、说出一些数据库优化方面的经验?1、关键字段建立索引。2、使用存储过程，它使SQL变得更加灵活和高效。 3、备份数据库和清除垃圾数据。4、SQL语句语法的优化。5、清理删除日志。3、union 和 union all 有什么不同?UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表 UNION。这个 SQL 在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。而 UNION ALL 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。从效率上说，UNION ALL 要比 UNION 快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用 UNION ALL，4.分页语句取出 sql 表中第31到40的记录（以自动增长 ID 为主键）sql server 方案1：selecttop 10 * from t where id not in (select top 30 id from t order by id ) orde byid sql server 方案2：selecttop 10 * from t where id in (select top 40 id from t order by id) order by iddescmysql 方案：select * from t order by id limit 30,10oracle 方案：select * from (select rownum r,* from t where r30分页技术（直接利用 sql 语句进行分页，效率最高和最推荐的）mysql:sql = select * from articles limit +(pageNo-1)*pageSize + , + pageSize; oracle: sql = select * from +(selectrownum r,* from + (select* from articles order by postime desc) +whererownum +(pageNo-1)*pageSize;sqlserver:sql = select top 10 * from id not id(select top + (pageNo- 1)*pageSize + id from articles)5.用一条 SQL 语句查询出每门课都大于 80 分的学生姓名namekechengfenshu张三语文81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90准备数据的 sql 代码：create table score(id int primary key auto_increment,namevarchar(20),subject varchar(20),score int);insert into score values (null,张三,语文,81), (null,张三,数学,75), (null,李四,语文,76), (null,李四,数学,90), (null,王五,语文,81),(null,王五,数学,100),(null,王五 ,英语,90);A: select distinct name from score where name not in (selectdistinct name from score where score=80)B:select distince name t1 from score where 80 all (selectscore from score where name=t1);6.所有部门之间的比赛组合一个叫 department 的表，里面只有一个字段 name,一共有4条纪录，分别是 a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条 sql 语句显示所有可能的比赛组合.select , from team a, team b where 7、注册 Jdbc 驱动程序的三种方式第一种方式DriverManager.registerDriver(newcom.microsoft.sqlserver.jdbc.SQLServerDriver();jdbc是使用桥的模式进行连接的。DriverManager就是管理数据库驱动的一个类，java.sql.Driver就 是一个提供注册数据库驱动的接口，而com.microsoft.sqlserver.jdbc.SQLServerDriver()是 java.sql.Driver接口的一个具体实现。第二种方式System.setProperty(jdbc.drivers,com.microsoft.sqlserver.jdbc.SQLServerDriver); 多个驱动使用冒号分隔开，在连接时JDBC会按顺序搜索，直到找到第一个能成功连接指定URL的驱动程序。第三种方式（推荐）Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver);好处在于能够在编译时不依赖于特定的JDBCDriver库，也就是减少了项目代码的依赖性，而且也很容易改造成从配置文件读取JDBC配置，从而可以在运行时动态更换数据库连接驱动。8、用 JDBC 如何调用存储过程代码如下：package erview.lym;import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types;publicclass JdbcTest public static voidmain(String args) Connection cn = null;CallableStatement cstmt =null;try /这里最好不要这么干，因为驱动名写死在程序中了Class.forName(com.mysql.jdbc.Driver);/实际项目中，这里应用 DataSource 数据，如果用框架，/这个数据源不需要我们编码创建，我们只需 Datasource ds =context.lookup()cn = DriverManager.getConnection(jdbc:mysql:/test,root,root); cstmt=cn.prepareCall(callinsert_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) / TODO Auto-generated catchblocke .printStackTrace();finallytry if(cstmt !=null)cstmt.close();if(cn !=null)cn.close(); catch (SQLException e) / TODO Auto-generatedcatch blocke.printStackTrace();9、JDBC 中的 PreparedStatement 相比 Statement 的好处一个 sql 命令发给服务器去执行的步骤为：语法检查，语义分析，编译成内部指令，缓存指令，执行指令等过程。select * from student where id =3-缓存-xxxxx 二进制命令select * from student where id =3-直接取-xxxxx 二进制命令select * from student where id =4- - 会怎么干？如果当初是 select * from student where id =?- - 又会怎么干？上面说的是性能提高可以防止 sql 注入。10、写一个用 jdbc 连接并访问 oracle 数据的程序代码import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCDemo public static void main(String args)Connection conn = null;ResultSet rs = null;try /加载驱动Class.forName(oracle.jdbc.OracleDriver);/获得连接conn=DriverManager.getConnection(jdbc:oracle:thin:localhost:1521:orcl,scott,tiger);Statement stat = conn.createStatement();String sql = SELECT * FROM emp;/执行语句获得结果集rs = stat.executeQuery(sql);/遍历结果集while(rs.next()String name = rs.getString(name);System.out.println(name); catch (Exception e) e.printStackTrace(); finally /关闭连接try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();11、Class.forName 的作用?为什么要用?按参数中指定的字符串形式的类名去搜索并加载相应的类，如果该类字节码已经被加载过，则返回代表该字节码的 Class 实例对象，否则，按类加载器的委托机制去搜索和加载该类，如果所有的类加载器都无法加载到该类，则抛出 ClassNotFoundException。加载完这个 Class 字节码后，接着就可以使用 Class 字节码的 newInstance 方法去创建该类的实例对象了。有时候，我们程序中所有使用的具体类名在设计时（即开发时）无法确定，只有程序运行时才能确定，这时候就需要使用 Class.forName 去动态加载该类，这个类名通常是在配置文件中配置的，例如，spring 的 ioc 中每次依赖注入的具体类就是这样配置的，jdbc 的驱动类名通常也是通过配置文件来配置的，以便在产品交付使用后不用修改源程序就可以更换驱动类名。12、大数据量下的分页解决方法。答：最好的办法是利用 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 * fromstudents order by id limit + pageSize*(pageNumber-1) + , +pageSize;oracle:String sql =select * from +(select *,rownum rid from (select * fromstudents order by postime desc) where rid +pageSize*(pageNumber-1);13、用 JDBC 查询学生成绩单,把主要代码写出来（考试概率极大）.Connection cn = null;PreparedStatement pstmt =null;Resultset rs = null;tryClass.forname(driveClassName);cn = DriverManager.getConnection(url,username,password); pstmt =cn.prepareStatement(“select score.* fromscore ,student “ +“wherescore.stuId = student.id and = ?”);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();finallyif(rs != null) tryrs.close() catch(exception e)if(pstmt != null)trypstmt.close()catch(exception e)if(cn != null) trycn.close() catch(exception e)14、这段代码有什么不足之处?try Connection conn = .; Statement stmt = .;ResultSet rs =stmt.executeQuery(select * from table1);while(rs.next() catch(Exception ex) 没有 finally 语句来关闭各个对象，另外，使用 finally 之后，要把变量的定义放在 try 语句块的外面，以便在 try 语句块之外的 finally 块中仍可以访问这些变量。15、说出数据连接池的工作机制是什么?J2EE 服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量有配置参数决定。当使用的池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用就可以使用这个连接。实现方式，返回的 Connection 是原始 Connection 的代理，代理 Connection 的 close 方法不是真正关连接，而是把它代理的 Connection 对象还回到连接池中。16、为什么要用 ORM? 和 JDBC 有何不一样?orm 是一种思想，就是把 object 转变成数据库中的记录，或者把数据库中的记录转变成objecdt，我们可以用 jdbc 来实现这种思想，其实，如果我们的项目是严格按照 oop 方式编写的话，我们的 jdbc 程序不管是有意还是无意，就已经在实现 orm 的工作了。现在有许多 orm 工具，它们底层调用 jdbc 来实现了 orm 工作，我们直接使用这些工具，就省去了直接使用 jdbc 的繁琐细节，提高了开发效率，现在用的较多的 orm 工具是 hibernate。也听说一些其他 orm 工具，如 toplink,ojb 等。17.主键自动增长不同数据库之间的差异MySQL：使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。默认地，AUTO_INCREMENT 的开始值是 1，每条新纪录递增 1。要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：ALTER TABLE Persons AUTO_INCREMENT=100 （创建时会自动添加一个唯一的值）SQL Server：使用 IDENTITY 关键字来执行 auto-increment 任务，默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。要规定 P_Id 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10) （创建时会自动添加一个唯一的值）Oracle：必须通过 sequence 对创建 auto-increment 字段（该对象生成数字序列）。18、sqlserver与oracle的区别： sql server 与 oracle的区别：-1.数据类型不同。-sql server 的数据类型：int ,smallint ,char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima,float,bit -oracle 的数据类型：number(p,s),char,varchar2,Date,LOB -2.获得当前系统时间的函数不同。 -sql server :getdate() -oracle:sysdate -例如：设定日期格式的函数：to_char(sysdate,yyy-mm-dd);-3.在oracle中没有默认约束的说法 -sql server 中添加默认约束：alter table talbe_name add DF_table_name default(男) for sex; -oracle 中添加默认值：alter table table_name modify(sex default(男)；-4.连接变量和字符串的方式不一样 -sql server 中连接:使用“+”连接，例如：print aaaa+name;-oracle 中连接：使用“|”连接，例如：dbms_output.put_line(aaa|name);-name为变量-5.oracle没有identity自动增长列，而是使用序列实现增长 -sql server 自动增长：在表的主键列中可直接使用identity（1,1）实现增长 -oracle 使用序列自动增长： create sequence se_id start with 1 increment by 1 -使用序列实现自动增长：se_id.nextval-6.条件语句ifelse的语法不同 -sql server中： if 条件 begin end else begin end -oracle中： if 条件1 then ; elsif 条件2 then ; else ; end if; -7.case语句的语法不同 -sql server中： -select .case.（else）.end.语句 select stuno 学号,case when grade=90 and grade=80 and grade=70 and grade=60 and grade70 then else 差 end as 等级 from score go -oracle中： declare nums number:=&nos;-&nos表示提示传入值 begin case nums when 100 then dbms_output.put_line(满分也，不错); when 90 then dbms_output.put_line(90分页很不错了); end case; end;-8.触发器创建语法不同 -sql server中： -首先判断触发器是否已经存在 if exists (select * from sys.sysobjects where name=tr_delete) -如果存在先删除 drop trigger tr_delete go -创建触发器 create trigger tr_delete on bookInfo instead of delete as -定义变量 declare bookid int select bookid=Bookid from deleted-deleted执行删除语句（ delete from BookInfo where BookId=1），自动生成的deleted表 -删除与该图书的相关记录（先删除从表再删除主表） delete from borrowinfo where bookid=bookid delete from backinfo where bookid=bookid delete from BookInfo where BookId=bookid -判断 if error0 begin print 删除失败 rollback transaction end else begin print 删除成功 end go delete from BookInfo where BookId=1 -oracle中： -创建触发器 create or replace trigger tri_test before insert or update or delete on table_name for each row-如果要使用 :new /:old 就必须使用行触发器 declare nums varchar2(20); begin selectF|lpad(aa,5,0)into nums from dual; end; -9.oracle中的存储过程 -sql server中存储过程： -判断存储过程是否已经存在 if exists(select * from sys.sysobjects where name=proc_name) -如果存在先删除 drop proc proc_name go -创建存储过程语句 create proc/procedure proc_name 参数名1 数据类型 out/output, 参数名2 数据类型 out/output as go -调用存储过程 -如果有输出参数，则需定义变量(假设参数2为输出参数) declare 变量名 数据类型 exec proc_name 参数名1=aaa,参数名2=变量名 out -oracle中带游标及循环的存储过程 create or replace procedure proc_selCurrent ( names varchar2 ) as cursor cursor_sel is select DepositSum,cardType,name,state from CurrentAccount where name like %|names|%; dd number; cc number; nn varchar2(20); sta number; begin open cursor_sel; loop fetch cursor_sel into dd,cc,nn,sta; dbms_output.put_line(存款金额：|dd|姓名：|nn); exit when cursor_sel%notfound; end l