数据库面试题整理

数据库部分

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 iddesc mysql 方案：

select * from t order by id limit 30,10

oracle 方案：

select * from (select rownum r,* from t where r<=40) wherer>30

分页技术（直接利用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*pageSize +") tmp " +"wherer>"

+(pageNo-1)*pageSize;

sqlserver:

sql = "select top 10 * from id not id(select top" + (pageNo- 1)*pageSize + "id from articles)"

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

name kecheng fenshu

张三语文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 https://www.360docs.net/doc/ad4641577.html,,https://www.360docs.net/doc/ad4641577.html, from team a, team b where https://www.360docs.net/doc/ad4641577.html, < https://www.360docs.net/doc/ad4641577.html,

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

第一种方式

DriverManager.registerDriver(new com.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.SQL ServerDriver"); 多个驱动使用冒号分隔开，在连接时JDBC会按顺序搜索，直到找到第一个能成功连接指定URL的驱动程序。

第三种方式（推荐）

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");好处在于能够在编译时不依赖于特定的JDBC Driver库，也就是减少了项目代码的依赖性，而且也很容易改造成从配置文件读取JDBC配置，从而可以在运行时动态更换数据库连接驱动。

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

代码如下：

package com.huawei.interview.lym;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

publicclass JdbcT est {

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 = 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 catchblock

e .printStackTrace();

}

finally

{

try {

if(cstmt !=null)

cstmt.close();

if(cn !=null)

cn.close();

} catch (SQLException e) {

// TODO Auto-generatedcatch block

e.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","scot t","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 block

e.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 +") as t" +"where t>" +pageSize*(pageNumber-1);

13、用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.* fromscore ,student “ + “wherescore.stuId = student.id and https://www.360docs.net/doc/ad4641577.html, = ?”);

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){}

}

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.条件语句if……else……的语法不同

--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<=100 then '★★★★'

when grade>=80 and grade<90 then '★★★'

when grade>=70 and grade<80 then '★★'

when grade>=60 and grade<70 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 @@error<>0

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

select 'F'||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 loop;

close cursor_sel;

end;

--调用存储过程

begin

proc_selCurrent('a');

end;

--10.创建用户的方式不同

--sql server中

--1、创建登陆账号：sa-----123456

create Login 登陆名称 with password='登陆密码'

--修改登陆账户：

alter Login 登陆名称with name='新登录名称' and password='新登录密码'

--禁用/启用登陆账号

alter Login 登录名称 disable(禁用)/enable(启用)

--删除登陆账号

drop Login 登录名称

--2、创建用户：

create user 用户名 for/from Login 登陆名称

--修改用户名

alter user 用户名 with name='新用户名'

--删除用户名

drop user 用户名

---授权限

grant select/update/delete/insert on 表名 to 用户名

---oracle中：

---创建用户语法：

create user 用户名

identified by 密码

default tablespace users

temporary tablespace temp

quota 10M on users

--修改密码：

alter user 用户名 identified by 新密码

--授予权限：

grant create session to 用户名

--删除用户

drop user 用户名 c ascade;

19、索引的作用：

1.通过创建唯一索引，可以保证数据记录的唯一性。

2.可以大大加快数据检索速度。

3.可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。

4.在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。

5.使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

20、存储过程如何与后台连接

你是需要定时执行吗？你说的后台执行应该就是存储过程自动执行吧：

1、定时执行你可以创建job，可以定时每个多久执行一次存储过程（job中可以执行多个存储过程，只需要使用‘；’隔开）；

2、你可以将你所有的存储过程打包（pkg）；

21、如何还原数据库已删除的数据：

1、如果能够备份“置疑”数据库的话，现备份出来，然后删除该数据库，最后由备份出来的文件恢复。

2、如果无法备份可以采取先停止sql server，然后到sql安装目录的data （系统默认时这里，也可能在其他你放置的目录下）目录下找到该“置疑”数据库文件和日志文件拷贝到其他目录，启动sql server，删除该数据库，将考出的数据库文件和日志文件考回原目录，最后用这两个文件通过数据库附加的方法恢复原数据库