数据库面试题

-DBA 数据库管理员JA V A 程序员架构师必看

1、 如何查找和删除数据库中的重复数据

法一: 用Group by语句 此查找很快的

select count(num), max(name) from student –查找表中num 列重复的，列出重复的记录数，并列出他的name 属性

group by num

having count(num) >;1 –按num 分组后找出表中num 列重复，即出现次数大于一次

delete from student(上面Select 的)

这样的话就把所有重复的都删除了。—–慎重

2、说一下数据表设计要遵守的三范式是什么？除了这些你觉得数据表的设计还要注意哪些规则？

1、无重复列，实际上就是一个表中一个列不允许有多值属性、复合属性或者嵌套的表，即列的值域必须是原子值，表必须有主键。

2、属性完全依赖于主键[消除部分子函数依赖]。第二范式其实是在第一范式上的扩展，所以第二范式必须符合第一范式，表中列不存在部分函数依赖。

3、属性不依赖于其它非主属性[消除传递依赖]。第三范式是在第二范式的基础上进行的扩展，所以必须符合第二范式且表中不存在传递依赖。

3、写一个SQL 存储过程，建立一个表USER 字段是姓名，年龄，职位，权限，然后向里面插入6条数据，然后查询出年龄大于18的所有信息。

Create table user(

Name varchar(20),

Age int,

Position varchar(20)

Quanxian varchar(20)

)

Insert into user values(‘111’,11,’11111’,’1111111’)

Insert into user values(‘112’,12,’11112’,’1111112’)

Insert into user values(‘113’,13,’11113’,’1111113’)

Insert into user values(‘114’,14,’11114’,’1111114’)

Insert into user values(‘115’,15,’11115’,’1111115’)

Insert into user values(‘116’,16,’11116’,’1111116’)

Create procedure selectUser

As

Select * from user where age >18

4、1. 你对哪种数据库最熟悉? 使用该数据库多少年? 简单描述对该数据库的理解.

答：SQL ，适合中小型企业使用

2. 对其它较熟悉的商业数据库的了解及使用年限.

3. 如何将数据库(实例) 备份和还原?(针对自己最熟悉的)

答：SQL ，在企业管理器上右点要备份或还原的数据，从所有任务选择备份或还原。

4. 如何知道一个表或视图的结构?

答：查询系统表sysobjects

5. 描述INNER JOIN的功能和已在何种情况下使用INNER JOIN.

答：内联接是用比较运算符比较要联接列的值的联接，连接的2个表都要满足、

5、存储过程和SQL 语句的优缺点

存储过程的优缺点：

优点：

1. 由于应用程序随着时间推移会不断更改，增删功能，T －SQL 过程代码会变得更复杂，StoredProcedure 为封装此代码提供了一个替换位置。

2. 执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－ 实际上是 Microsoft SQL Server 为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。）缓存改善性能。

„„..但sql server新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。

3. 存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql 语句的代码流量。

4. 使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。

5. 可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

6. 代码精简一致，一个存储过程可以用于应用程序代码的不同位置。

7. 更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。

8. 增强安全性：

a 、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；

b 、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML ，附加到输入参数）； c 、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

缺点：

1. 如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

2. 可移植性差

由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

Sql 语句灵活，可移植性强，查询速度比存储过程慢些

6、为数据库创建索引都需要注意些什么？

1. 索引能提高速度的关键就是索引所占的空间要比表小得多

2. 注意索引的大小，有一些表可以建成索引组织表

3. 索引的列不要太多，要选择一些selective 比较低的列建B-tree 索引，选择selective 高的列建bitmap 索引(在更新比较多的表不不要建bitmap 索引)

4. 将selective 较低的列放在前面

5. 在更新不多的表上建索引时，可以考虑用compress 选择，以节约索引的空间

7、一组SQL 相关操作面试题

1. 在表A 中有数据

ID MO

1 Y

2 N

请用一个SELECT 语句写出，如果MO 的值为“Y”，返回“YES”，为N 返回“NO”

效果如下:

ID MO

1 YES

2 NO

SELECT ID,MO=CASE

WHEN MO=‟Y‟ THEN „YES‟

WHEN MO=‟N‟ THEN „NO‟

END

FROM Az

2. 在表A 中查询出自动增长列中31到40之间的数据(注意可能不是连续的)

select * from A where id between 31 and 40

3. 有一个表table 中有一个自动增长字段ID, 如果在存储过程中向这个表插入一条记录后, 如何获得新记录的ID.(写出获取新记录ID 的函数即可)

CREATE FUNCTION c_currentId()

RETURNS int

AS

BEGIN

declare @lastId int

select @lastId=max(cid) from c

RETURN (@lastId)

END

select tempdb.dbo.c_currentId() as „当前C 表中最新的编号‟

4. having的用法, 是用来做什么的 having用来对group by 分组后的记录进行过滤。

5. sql 中的什么函数可以转换为时间 select convert(datetime,‟2000/01/01′) select cast(‟2001/02/02′ as datetime)

6. 查询数据放入一张临时表 select * into #A from Test select * from #A

8、如何使用SQL 进行模糊查询

LIKE 条件一般用在指定搜索某字段的时候, 通过”% _” 通配符的作用实现模糊查找功能，通配符可以在前面也可以在后面或前后都有。

搜索以mian 开头：

SELECT * FROM teble WHERE title LIKE ‘mian%’

搜索以mian 结束：

SELECT * FROM teble WHERE title LIKE ‘%mian’

搜索包含mian ：

SELECT * FROM teble WHERE title LIKE ‘%mian%’

注释：%表示0个或多个字符构成的字符串

_表示单个字符

9、说一下mysql, oracle等常见数据库的分页实现方案？

1.Oracle:

select * from ( select row_.*, rownum rownum_ from ( query_SQL ) row_ where rownum == min

2.SQL Server:

select top @pagesize * from tablename where id not in (select top @pagesize*(@page-1) id from tablename order by id) order by id

3.MySQL

select * from tablename limit position, counter

4.DB2

select * from (select *,rownumber() as ROW_NEXT from tablename) where ROW_NEXT between min and max

1. 分页方案一：(利用Not In和SELECT TOP分页) 效率次之

语句形式：

SELECT TOP 10 * FROM TestTable

WHERE(ID NOT IN (SELECT TOP 20 id FROM TestTable ORDERBY id)) ORDERBYID

SELECT TOP 页大小 * FROM TestTable

WHERE( ID NOT IN (SELECT TOP 每页大小-1*待查询页数-1 id FROM 表 ORDERBY id)) ORDERBYID 思路:先查询出待查询页之前的全部条数的id, 查询ID 不在这些ID 中的指定数量条数

2. 分页方案二：(利用ID 大于多少和SELECT TOP分页) 效率最高

语句形式：

SELECT TOP 10 * FROM TestTable

WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP20 id FROM TestTable ORDERBYid)AS T))ORDERBY ID SELECT TOP 页大小* FROM TestTable

WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP 每页大小*待查询页数-1 id FROM 表 ORDERBY id)AS T)) ORDERBY ID

思路:先获得待查询页的之前全部条数id, 获得它们当中最大的ID 号, 以此最大ID 号为标志, 查找比这个ID 号大的指定条数

3. 分页方案三：

SELECT TOP PageSize * FROM(SELECT TOP nPage*PageSize * from YOURTABLE order by id)as a order by id desc

SELECT TOP 每页条数 * FROM (SELECT TOP 待查询页*每页条数) * from YOURTABLE order by id)as a order by id desc

思路:先正排序查询出待查询页之前(包括当前页) 的全部条数, 然后将其倒排序, 取指定条数

10、几道SQL 面试题

1. 解释一下SQL 里面的null

答案：null 代表一个unknown 的值或者一个不存在的值

2. 如何用SQL 判断一个值是不是null?

答案：可以用is null来判断一个值是不是null

3. 所有包含null 的计算表达式的返回结果都是null, 这句话正确吗？比如5+ null返回null 答案：正确

4. 所有与null 得比较运算都返回unknown 正确吗？

答案：正确

5. (unknown or true) = true正确吗？

答案：正确

6. (unknown or false) = unknown正确吗？

答案：正确

7. (unknown or unknown) = unknown正确吗？

答案：正确

8. (true and unknown) = unknown正确吗？

答案：正确

9. (false and unknown) = false正确吗？

答案：正确

10. (unknown and unknown) = unknown正确吗？

答案：正确

11. (not unknown) = unknown 正确吗？

答案：正确

11、介绍一下游标

游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch 语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。

由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。 游标通过以下方式扩展结果处理：

允许定位在结果集的特定行。

从结果集的当前位置检索一行或多行。

支持对结果集中当前位置的行进行数据修改。

为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

提供脚本、存储过程和触发器中使用的访问结果集中的数据的 Transact-SQL 语句。

12、介绍一下SQL Server的全文索引

全文索引可以对存储在SQL Server数据库中的文本数据执行快速检索功能。同LIKE 谓词不同，全文索引只对字符模式进行操作，对字和语句执行搜索功能。全文索引对于查询非结构化数据非常有效。一般情况下，可以对char 、varchar 和nvarchar 数据类型的列创建全文索引，同时，还可以对二进制格式的列创建索引，如image 和varbinary 数据类型列。对于这些二进制数据，无法使用LIKE 谓词。

为了对表创建全文索引，表必须包含单个、唯一、非空列。当执行全文检索的时候，SQL Server搜索引擎返回匹配搜索条件的行的键值。一般情况，使用sql server中的全文索引，经过大体4个步骤： 安装全文索引服务；

为数据表建立全文索引目录；

使全文索引与数据表内容同步；

使用全文索引进行查询。

13、什么是唯一索引

唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。例如，如果在 last_name、first_name 和 middle_initial 列的组合上创建了唯一索引 full_name，则该表中任何两个人都不可以具有相同的全名。

聚集索引和非聚集索引都可以是唯一的。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。

只有当唯一性是数据本身的特征时，指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性，则应在列上创建 UNIQUE 或 PRIMARY KEY 约束，而不要创建唯一索引。例如，如果打算经常查询雇员表（主键为 emp_id）中的社会安全号码 (ssn) 列，并希望确保社会安全号码的唯一性，则在 ssn 列上创建 UNIQUE 约束。如果用户为一个以上的雇员输入了同一个社会安全号码，则会显示错误。

14、几道数据库笔试题

1. 张表, 学生表S, 课程C, 学生课程表SC, 学生可以选修多门课程, 一门课程可以被多个学生选修, 通过SC 表关联；(SQL)

1) 写出建表语句；

答：建表语句如下（mysql 数据库）：

create table s(id integer primary key, name varchar(20));

create table c(id integer primary key, name varchar(20));

create table sc(

sid integer references s(id),

cid integer references c(id),

primary key(sid,cid)

);

2) 写出SQL 语句, 查询选修了所有选修课程的学生；

答：SQL 语句如下：

select stu.id, stu.name from s stu

where (select count(*) from sc where sid=stu.id) =

(select count(*) from c);

3) 写出SQL 语句, 查询选修了至少5门以上的课程的学生。

答：SQL 语句如下：

select stu.id, stu.name from s stu

where (select count(*) from sc where sid=stu.id)>=5;

2. 数据库表(Test)结构如下：(SQL)

IDNAMEAGEMANAGER(所属主管人ID)

106A30104

109B19104

104C20111

107D35109

112E25120

119F45NULL

3. 有3个表（15分钟）：(SQL)

Student 学生表 (学号，姓名，性别，年龄，组织部门)

Course 课程表 (编号，课程名称)

Sc 选课表 (学号，课程编号，成绩)

表结构如下：

1) 写一个SQL 语句，查询选修了’计算机原理’的学生学号和姓名（3分钟）

答：SQL 语句如下：

select stu.sno, stu.sname from Student stu

where (select count(*) from sc where sno=stu.sno and cno =

(select cno from Course where cname=’计算机原理’)) != 0;

2) 写一个SQL 语句，查询’周星驰’同学选修了的课程名字（3分钟）

答：SQL 语句如下：

select cname from Course where cno in (select cno from sc where sno=(select sno from Student where sname=’周星驰’));

3) 写一个SQL 语句，查询选修了5门课程的学生学号和姓名（9分钟）

答：SQL 语句如下：

select stu.sno, stu.sname from student stu

where (select count(*) from sc where sno=stu.sno) = 5;

15、ORACLE 面试题

1． 解释FUNCTION ，PROCEDURE 和PACKAGE 区别

答：function 和procedure 是PL/SQL代码的集合，通常为了完成一个任务。procedure 不需要返回任何

值而function 将返回一个值在另一方面，Package 是为了完成一个商业功能的一组function 和procedure 的集合。

2． 取某个序列的当前值的PL/SQL语句怎么写？

答：SELECT 序列名.CURRVAL FROM DUAL；

3． 说明ORACLE 数据库实例与ORACLE 用户的关系？

答：实例可以包含多个用户，一个用户只能在一个实例下

4． 创建数据库时，自动建立的tablespace 名称？

答：SYSTEM tablespace

5． 创建用户时，需要赋予新用户什么权限才能使它连上数据库？

答：CONNECT

6． IMPORT和SQL*LOADER这2个工具的不同点？

答：这两个ORACLE 工具都是用来将数据导入数据库的。

区别是：IMPORT 工具只能处理由另一个ORACLE 工具EXPORT 生成的数据。而SQL*LOADER可以导入不同的ASCII 格式的数据源。

7．解释冷备份和热备份的不同点以及各自的优点？

答：热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下, 数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）

8．比较truncate 和delete 命令？

答：两者都可以用来删除表中所有的记录。区别在于：truncate 是DDL （data defining language数据定义语言），它移动HWK ，不需要rollback segment （处理事务回滚操作）而Delete 是DML （data manufacturing language 数据操作语言）操作，需要rollback segment（处理事务回滚操作）且花费较长时间。

9．给出数据的相关约束类型？

答：主键约束，外键约束，非空约束，唯一约束，检查约束。

10．说明索引的类型与作用？

答：索引类型上分为聚集索引，非聚集索引其作用是加快查询速度。

11．解释归档和非归档模式之间的不同和它们各自的优缺点

答：归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高。

12．解释$ORACLE_HOME和$ORACLE_BASE的区别？

答：ORACLE_BASE是oracle 的根目录，ORACLE_HOME是oracle 产品的目录。

13．获取某个字符字段的最后3个字符的函数是什么？

答：select substr (字段,(length(字段)-3)) from 表

14．取当前系统时间点日期（不包括年月）的SQL 写法是怎样的？

答：Select substr (to_char(sysdate,’YYYYMMDDh24hh:MM:SS’),5) from dual;

15．返回大于等于N 的最小整数值？

答：select ceil(N) from dual;

16．将根据字符查询转换后结果，规则为：’A’转换为’男’，’B’转换为’女’，其他字符转换为’未知’，请用一个SQL 语句写出。

答：select decode(字符,’A’,’男’,’B’,’女’,’未知’) from dual;

17．如何搜索出前N 条记录？

答：select * from 表 where Rownum

18．如何搜索出第N ～M 条记录？

答：select * from 表 where Rownum

Minus

select * from 表 where Rownum

19．有一个数据表（TEST ），字段如下：

ID number

PARENT_ID number

NAME Varchar(20)

请使用PL/SQL来按父子层次关系查询出该表的所有数据

答：Select * from test a, test b Where a.parent_id = b.id;

20．怎样用SQL 语句实现查找一列中的第N 大值？

答：select * from (select * from 表 order by 列名 Desc) where Rownum

Minus

select * from (select * from 表 order by 列名 Desc) where Rownum

16、什么是事物，事物有哪些性质。

答:事务：是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。一个事务可以是一条SQL 语句，也可以是一组SQL 语句。

性质：

(1)原子性:事务是数据库的逻辑工作单位，被看做一个单一的、不可分割的操作单元。事务中包括的所有操作要么都做，要么都不做。

(2)一致性:事务执行的结果必须是使数据库从一个一致状态变到另一个一致状态。

(3)隔离性:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

(4)持续性:指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。

17、什么是数据抽象，举例说明

答:所谓数据抽象，是在对现实世界有一定的认识基础上，对实际的人、物、事进行人为的处理，忽略非本质的细节，抽取关心的共同和本质特征，并把这些特征用各种概念精确地加以描述。

18、数据库设计包括那两种，请分别说明。

答:广义的数据库设计，是指建立数据库及其应用系统，包括选择合适的计算机平台和数据库管理系统、设计数据库、以及开发数据库应用系统等。这种数据库设计实际是“数据库系统”的设计，其成果有二：一是数据库，二是以数据库为基础的应用系统。

狭义的数据库设计，是指根据一个组织的信息需求、处理需求和相应的数据库支撑环境，设计出数据库，包括概念结构、逻辑结构和物理结构。其成果主要是数据库，不包括应用系统。

19、简述数据库设计过程

答:数据库设计分为五个阶段:

需求分析：主要是准确收集用户信息需求和处理需求，并对收集的结果进行整理和分析，形成需求说明。 概念结构设计：对用户需求进行综合、归纳、抽象，形成一个与与具体的DBMS 无关概念模型（一般为ER 模型）。

逻辑结构设计：将概念结构设计的概念模型转化为某个特定的DBMS 所支持的数据模型，建立数据库逻辑模式，并对其进行优化，同时为各种用户和应用设计外模式。

物理结构设计：为设计好的逻辑模型选择物理结构，包括存储结构和存取方法等，建立数据库物理模式。 实施和维护：实施就是使用DLL 语言建立数据库模式，将实际数据载入数据库，建立真正的数据库。维护阶段是对运行中的数据库进行评价、调整和修改。

20、SQL Server 2000有哪些文件，分别进行描述

答案:SQL Server 2000 数据库有三种类型的文件：

主要数据文件

主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。

次要数据文件

次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。

日志文件

日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。

21、查询优化的一般准则有哪些

1. 选择运算应尽可能先做。这是优化策略中最重要最基本的一条

2. 在执行连接前对关系适当的预处理，方法有两种：在连接属性上建立索引和对关系排序，然后执行连接，第一种称为索引连接方法，第二种称为排序合并连接方法

3. 把投影运算和选择运算同时进行。

4. 把投影同其前或者其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一下关系

5. 把某些选择同它前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积节省很多时间

6. 找出公共子表达式，先计算出公共子表达式的值才参与运算。

22、Oracle 10问

14. 如何查看系统被锁的事务时间？

select * from v$locked_object ;

15. 如何以archivelog 的方式运行oracle 。

init.ora

log_archive_start = true

RESTART DATABASE

16. 怎么获取有哪些用户在使用数据库

select username from v$session;

17. 数据表中的字段最大数是多少?

表或视图中的最大列数为 1000

18. 怎样查得数据库的SID ?

select name from v$database;

也可以直接查看 init.ora文件

19. 如何在Oracle 服务器上通过SQLPLUS 查看本机IP 地址 ?

select sys_context(’userenv’,'ip_address’) from dual;

如果是登陆本机数据库，只能返回127.0.0.1，呵呵

20. unix 下怎么调整数据库的时间？

su -root

date -u 08010000

23、几道数据库概念性面试题

这是几道数据库的概念性面试题，有的面试题在之前收录的面试题里面已经出现过了，不过还是要拿来强化一下概念：

1、触发器的作用？

答：触发器是一种特殊的存储过程，它主要是通过事件来触发而被执行的。它的作用是可以强化约束，维

护数据的完整性和一致性，也可以可以跟踪数据库内的操作从而不允许未经许可的更新和变化。也可以联级运算。如某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

2、什么是存储过程？如何来调用存储过程？

答：存储过程是一个预编译的SQL 语句，他的优点是允许模块化的设计，也就是说只需创建一次，在该程序中就可以调用多次。例如某次操作需要执行多次SQL ，就可以把这个SQL 做一个存储过程，因为存储过程是预编译的，所以使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

3、什么是内存泄漏？

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

4、什么是事务？

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

5、索引的作用？和它的优点缺点是什么？

答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。索引很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

6、什么是锁？

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

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

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

以上的答案不是很严谨，但是有利于理解，大家可以参考其他数据库的资料进行记忆。

24、如果高效查询出一个月内的数据

进行时间比较要尽量避免用sysdate. 比如：如果使用select * from eventtable where

eventdate>sysdate-30进行查找, 当数据量小的时候看不出来，数据量大一些就会发现执行很慢，但日期型字 段上也是有索引的，为什么会慢呢？ 原来是Oracle 在进行查找的时候不断地去取sysdate 这个不断变化的值，而不是我们想象中的一次产生一个条件语句然后进行查找。为了加快速度，我 们可以先把当天的日期取出来，然后转成字符串后再用如下语句查，select * from eventtable where eventdate > to_date(’2001-12-1′,’yyyy-mm-dd’)。速度相差几十倍。

25、主键(Primary Key)约束和唯一性(UNIQUE)约束有什么区别？

一个表只能由一个主键，一个表可以有很多个唯一键(UNIQUE Key)

主键不允许有null 值，UNIQUE 允许null 值

26、Oracle 中delete, truncate 和 drop的区别？

Delete 命令用来删除表的全部或者一部分数据行，执行delete 之后，用户需要提交(commmit)或者回滚(rollback) transaction 来执行删除或者撤销删除， delete命令会触发这个表上所有的delete 触发器。 Truncate 删除表中的所有数据， 这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE 比delete 更快，占用的空间更小。

Drop 命令从数据库中删除表， 所有的数据行，索引和权限也会被删除，所有的DML 触发器也不会被触发，这个命令也不能回滚。

27、MYSQL 面试题：简单叙述一下MYSQL 的优化

1. 数据库的设计

尽量把数据库设计的更小的占磁盘空间.

1). 尽可能使用更小的整数类型.(mediumint就比int 更合适).

2). 尽可能的定义字段为not null,除非这个字段需要null.

3). 如果没有用到变长字段的话比如varchar, 那就采用固定大小的纪录格式比如char.

4). 表的主索引应该尽可能的短. 这样的话每条纪录都有名字标志且更高效.

5). 只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段. 如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。

6). 所有数据都得在保存到数据库前进行处理。

7). 所有字段都得有默认值。

8). 在某些情况下, 把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。

2. 系统的用途

1). 尽量使用长连接.

2).explain 复杂的SQL 语句。

3). 如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.

4).LIMIT 语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.

5). 如果想要清空表的所有纪录, 建议用truncate table tablename而不是

delete from tablename.

6). 能使用STORE PROCEDURE 或者 USER FUNCTION的时候.

7). 在一条insert 语句中采用多重纪录插入格式. 而且使用load data infile来导入大量数据，这比单纯的indert 快好多.

8). 经常OPTIMIZE TABLE 来整理碎片.

9). 还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

3. 系统的瓶颈

1). 磁盘搜索.

并行搜索, 把数据分开存放到多个磁盘中，这样能加快搜索时间.

2). 磁盘读写(IO)

可以从多个媒介中并行的读取数据。

3).CPU 周期

数据存放在主内存中. 这样就得增加CPU 的个数来处理这些数据。

4). 内存带宽

当CPU 要将更多的数据存放到CPU 的缓存中来的话, 内存的带宽就成了瓶颈.

28、一套经典的SQL 面试题

1. 一道SQL 语句面试题，关于group by

表内容：

2005-05-09 胜

2005-05-09 胜

2005-05-09 负

2005-05-09 负

2005-05-10 胜

2005-05-10 负

2005-05-10 负

如果要生成下列结果, 该如何写sql 语句?

胜 负

2005-05-09 2 2

2005-05-10 1 2

——————————————

create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values(’2005-05-09′,’胜’)

insert into #tmp values(’2005-05-09′,’胜’)

insert into #tmp values(’2005-05-09′,’负’)

insert into #tmp values(’2005-05-09′,’负’)

insert into #tmp values(’2005-05-10′,’胜’)

insert into #tmp values(’2005-05-10′,’负’)

insert into #tmp values(’2005-05-10′,’负’)

1)select rq, sum(case when shengfu=’胜’ then 1 else 0 end)’胜’,sum(case when shengfu=’负’ then 1 else 0 end)’负’ from #tmp group by rq

2) select N.rq,N.勝,M. 負 from (

select rq,勝=count(*) from #tmp where shengfu=’胜’group by rq)N inner join

(select rq,負=count(*) from #tmp where shengfu=’负’group by rq)M on N.rq=M.rq

3)select a.col001,a.a1 胜,b.b1 负 from

(select col001,count(col001) a1 from temp1 where col002=’胜’ group by col001) a,

(select col001,count(col001) b1 from temp1 where col002=’负’ group by col001) b

where a.col001=b.col001

2. 请教一个面试中遇到的SQL 语句的查询问题

表中有A B C三列, 用SQL 语句实现：当A 列大于B 列时选择A 列否则选择B 列，当B 列大于C 列时选择B 列否则选择C 列。

——————————————

select (case when a>b then a else b end ),

(case when b>c then b esle c end)

from table_name

3. 面试题：一个日期判断的sql 语句？

请取出tb_send表中日期(SendTime字段) 为当天的所有记录?(SendTime字段为datetime 型，包含日期与时间)

——————————————

select * from tb where datediff(dd,SendTime,getdate())=0

4. 有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql 语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

显示格式：

语文 数学 英语

及格 优秀 不及格

——————————————

select

(case when 语文>=80 then ‘优秀’

when 语文>=60 then ‘及格’

else ‘不及格’) as 语文,

(case when 数学>=80 then ‘优秀’

when 数学>=60 then ‘及格’

else ‘不及格’) as 数学,

(case when 英语>=80 then ‘优秀’

when 英语>=60 then ‘及格’

else ‘不及格’) as 英语,

from table

5. 在sqlserver2000中请用sql 创建一张用户临时表和系统临时表，里面包含两个字段ID 和IDValues, 类型都是int 型，并解释下两者的区别?

——————————————

用户临时表:create table #xx(ID int, IDValues int)

系统临时表:create table ##xx(ID int, IDValues int)

区别:

用户临时表只对创建这个表的用户的Session 可见, 对其他进程是不可见的.

当创建它的进程消失时这个临时表就自动删除.

全局临时表对整个SQL Server实例都可见, 但是所有访问它的Session 都消失的时候, 它也自动删除.

6.sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。

——————————————

它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的. SQL Server 2000 数据库有三种类型的文件：

主要数据文件

主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。

次要数据文件

次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。

日志文件

日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。

7. 请用一个sql 语句得出结果

从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。

如使用存储过程也可以。

table1

月份mon 部门dep 业绩yj

——————————-

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8

table2

部门dep 部门名称dname

——————————–

01 国内业务一部

02 国内业务二部

03 国内业务三部

04 国际业务部

table3 （result ）

部门dep 一月份 二月份 三月份

————————————–

01 10 null null

02 10 8 null

03 null 5 8

04 null null 9

——————————————

1)

select a.部门名称dname,b. 业绩yj as ‘一月份’,c.业绩yj as ‘二月份’,d.业绩yj as ‘三月份’ from table1 a,table2 b,table2 c,table2 d

where a.部门dep = b.部门dep and b.月份mon = ‘一月份’ and

a. 部门dep = c.部门dep and c.月份mon = ‘二月份’ and

a. 部门dep = d.部门dep and d.月份mon = ‘三月份’ and

2)

select a.dep,

sum(case when b.mon=1 then b.yj else 0 end) as ‘一月份’,

sum(case when b.mon=2 then b.yj else 0 end) as ‘二月份’,

sum(case when b.mon=3 then b.yj else 0 end) as ‘三月份’,

sum(case when b.mon=4 then b.yj else 0 end) as ‘四月份’,

sum(case when b.mon=5 then b.yj else 0 end) as ‘五月份’,

sum(case when b.mon=6 then b.yj else 0 end) as ‘六月份’,

sum(case when b.mon=7 then b.yj else 0 end) as ‘七月份’,

sum(case when b.mon=8 then b.yj else 0 end) as ‘八月份’,

sum(case when b.mon=9 then b.yj else 0 end) as ‘九月份’,

sum(case when b.mon=10 then b.yj else 0 end) as ‘十月份’,

sum(case when b.mon=11 then b.yj else 0 end) as ‘十一月份’,

sum(case when b.mon=12 then b.yj else 0 end) as ‘十二月份’,

from table2 a left join table1 b on a.dep=b.dep

8. 华为一道面试题

一个表中的Id 有多个记录，把所有这个id 的记录查出来，并显示共有多少条记录数。

——————————————

select id, Count（*) from tb group by id having count(*)>1

select * from(select count(ID) as count from table group by ID)T where T.count>1

29、为什么group by 和 order by会使查询变慢

group by 和 order by操作通常需要创建一个临时表来处理查询的结果，所以如果查询结果很多的话会严重影响性能。

30、SQL 一些基础语法

alter table docdsp add dspcode

char(200)

2. 删除字段

ALTER TABLE table_NAME DROP COLUMN

column_NAME

3. 修改字段类型

ALTER TABLE table_name ALTER COLUMN

column_name new_data_type

4.sp_rename 改名

更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。 语法

sp_rename [ @objname = ]

'object_name' ,

[ @newname = ] 'new_name'

[ , [ @objtype =

] 'object_type' ]

如：EXEC sp_rename

'newname','PartStock'

5.sp_help 显示表的一些基本情况

sp_help 'object_name'

如：EXEC sp_help 'PartStock'

6. 判断某一表PartStock 中字段PartVelocity 是否存在

exists (select * from syscolumns where id=object_id('PartStock') and name='PartVelocity')

print

'PartVelocity exists'

else print 'PartVelocity not

exists'

另法：

判断表的存在性：

select count(*) from sysobjects where type='U'

and name='你的表名'

判断字段的存在性：

select count(*) from syscolumns

where

id = (select id from sysobjects where type='U' and name='你的表名')

and name = '你要判断的字段名'

一个小例子

--假设要处理的表名为:

tb

--判断要添加列的表中是否有主键

if exists(select 1 from sysobjects where

parent_obj=object_id('tb') and xtype='PK')

begin

print

' 表中已经有主键, 列只能做为普通列添加'

--添加int 类型的列, 默认值为0

alter table tb add 列名 int default

end

else

begin

print '表中无主键, 添加主键列'

--添加int 类型的列, 默认值为0

alter

table tb add 列名 int primary key default 0

end

7. 随机读取若干条记录

Access语法：SELECT top 10 * From 表名 ORDER BY

Rnd(id)

Sql server:select top n * from 表名 order by newid()

mysql select * From 表名 Order By rand() Limit n

8. 说明：日程安排提前五分钟提醒

SQL:

select * from 日程安排 where datediff(minute,f开始时间,getdate())>5

9. 前10条记录

select top 10 * form table1 where 范围

10. 包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a

from tableA ) except (select a from tableB) except (select a from tableC)

11. 说明：随机取出10条数据

select top 10 * from tablename order by newid()

12. 列出数据库里所有的表名

select name from sysobjects where type=U

13. 列出表里的所有的字段名

select name from syscolumns where

id=object_id(TableName)

14. 说明：列示type 、vender 、pcs 字段，以type 字段排列，case 可以方便地实现多重选择，类似select 中的case 。

select type,sum(case vender when A then pcs else 0

end),sum(case vender when C then pcs else 0 end),sum(case vender when B then pcs

else 0 end) FROM tablename group by type

15. 说明：初始化表table1

TRUNCATE

TABLE table1

16. 说明：几个高级查询运算词

A ： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如

TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL ），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B ： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2

中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C ： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2

中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL) ，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

17. 说明：在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where

t.a > 1;

18. 说明：between 的用法,between 限制查询数据范围时包括了边界值,not between不包括

select * from

table1 where time between time1 and time2

select a,b,c, from table1

where a not between 数值1 and 数值2

19. 说明：in 的使用方法

select * from table1 where a [not] in

(‘值1’, ’值2’, ’值4’, ’值6’)

20. 说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists (

select * from table2 where table1.field1=table2.field1

)

21. 说明：复制表(只复制结构, 源表名：a 新表名：b) (Access可用)

法一：select * into b from a

where 11

法二：select top 0 * into b from a

22. 说明：拷贝表(拷贝数据, 源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c)

select d,e,f from b;

23. 说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c)

select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in

"&Server.MapPath(".")&"\data.mdb" &" where..

24. 创建数据库

CREATE DATABASE database-name

25. 说明：删除数据库

drop database dbname

26. 说明：备份sql server

--- 创建

备份数据的 device

USE master

EXEC sp_addumpdevice disk, testBack,

c:\mssql7backup\MyNwind_1.dat

--- 开始 备份

BACKUP DATABASE pubs TO testBack

27. 说明：创建新表

create table tabname(col1 type1 [not null] [primary

key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new

like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2„

from tab_old definition only

28. 说明：

删除新表：drop table tabname

29. 说明：

增加一个列：Alter table tabname add column col

type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar 类型的长度。

30. 说明：

添加主键：Alter table tabname add primary key(col)

说明：

删除主键：Alter table tabname drop primary key(col)

31. 说明：

创建索引：create [unique] index idxname on tabname(col„.)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

创建视图：create view viewname as select statement

删除视图：drop view

viewname

33. 说明：几个简单的基本的sql 语句

选择：select * from table1 where 范围

插入：insert into

table1(field1,field2) values(value1,value2)

删除：delete from table1 where

范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1

where field1 like ’%value1%’ ---like的语法很精妙，查资料!

排序：select * from table1

order by field1,field2 [desc]

总数：select count * as totalcount from

table1

求和：select sum(field1) as sumvalue from table1

平均：select

avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from

table1

最小：select min(field1) as minvalue from table1

select name from bbc where population>=200000000; --查询显示出人口不小于2亿的国家名字

select name ,(gdp/population)'人均GDP' from bbc where population>=200000000; --人口不小于200000000国家名称及人均gdp

select name , round((populatio/1000000))'人口数（百万）' from bbc where name in(south asia); select name,popultion from bbc where name ='France', 'Germany', 'Italy'; --查询出france,germany,italy 三国的人口数

select name from bbc where name like '%united%' 查询出国家名称包括united 字符的的所有国家名称；

表获得诺贝尔奖

select yr,subject winner from nobel where yr = 1950; --显示1950年获得诺贝尔奖的人. 的所有信息

select winner from nobel where yr =1962; --1962 文学奖获得者

select yr,subject from nodel where winner ='albert einstin'; --显示'Albert Einstein'获奖的年份和奖项．

select winner from nodel where yr>=2000 and subject = '和平奖';--2000年以来（包含2000年）和平奖的得主．

select * from nodel where yr between 1980 and 1989 and subject = '文学奖'; --1980 到 1989间文学奖获得者的所有信息

select *from nodel where winner=('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter');

select winner from nodel where winner like 'john%'

-DBA 数据库管理员JAVA 程序员架构师必看