数据库面试题概况.docx

1、数据库面试题概况-DBA数据库管理员JAVA程序员架构师必看1、 如何查找和删除数据库中的重复数据法一: 用Group by语句 此查找很快的select count(num), max(name) from student 查找表中num列重复的，列出重复的记录数，并列出他的name属性group by numhaving count(num) ;1 按num分组后找出表中num列重复，即出现次数大于一次delete from student(上面Select的)这样的话就把所有重复的都删除了。慎重2、说一下数据表设计要遵守的三范式是什么？除了这些你觉得数据表的设计还要注意哪些规则？1、无重

2、复列，实际上就是一个表中一个列不允许有多值属性、复合属性或者嵌套的表，即列的值域必须是原子值，表必须有主键。2、属性完全依赖于主键消除部分子函数依赖。第二范式其实是在第一范式上的扩展，所以第二范式必须符合第一范式，表中列不存在部分函数依赖。3、属性不依赖于其它非主属性消除传递依赖。第三范式是在第二范式的基础上进行的扩展，所以必须符合第二范式且表中不存在传递依赖。3、写一个SQL存储过程，建立一个表USER 字段是姓名，年龄，职位，权限，然后向里面插入6条数据，然后查询出年龄大于18的所有信息。Create table user(Name varchar(20),Age int,Position

3、 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,111111

4、6)Create procedure selectUserAsSelect * from user where age 184、1. 你对哪种数据库最熟悉?使用该数据库多少年?简单描述对该数据库的理解.答：SQL，适合中小型企业使用2. 对其它较熟悉的商业数据库的了解及使用年限.3. 如何将数据库(实例)备份和还原?(针对自己最熟悉的)答：SQL，在企业管理器上右点要备份或还原的数据，从所有任务选择备份或还原。4. 如何知道一个表或视图的结构?答：查询系统表sysobjects5. 描述INNER JOIN的功能和已在何种情况下使用INNER JOIN.答：内联接是用比较运算符比较要联接列的值

5、的联接，连接的2个表都要满足、5、存储过程和SQL语句的优缺点存储过程的优缺点：优点：1.由于应用程序随着时间推移会不断更改，增删功能，TSQL过程代码会变得更复杂，StoredProcedure为封装此代码提供了一个替换位置。2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。）缓存改善性能。.但sql server新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。3.存储过程可以用于降低网络流量，存储过程代

6、码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。7.更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。8.增强安

7、全性：a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言DML，附加到输入参数）；c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。2.可移植性差由于存储过程将应用程序绑定到 SQL

8、Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。Sql语句灵活，可移植性强，查询速度比存储过程慢些6、为数据库创建索引都需要注意些什么？1. 索引能提高速度的关键就是索引所占的空间要比表小得多2. 注意索引的大小，有一些表可以建成索引组织表3. 索引的列不要太多，要选择一些selective比较低的列建B-tree索引，选择selective高的列建bitmap索引(在更新比较多的表不不要建bitmap索引)4. 将selective较低的列放在前面5. 在更

9、新不多的表上建索引时，可以考虑用compress选择，以节约索引的空间7、一组SQL相关操作面试题1. 在表A中有数据ID MO1 Y2 N请用一个SELECT 语句写出，如果MO的值为“Y”，返回“YES”，为N返回“NO”效果如下:ID MO1 YES2 NOSELECT ID,MO=CASEWHEN MO=Y THEN YESWHEN MO=N THEN NOENDFROM Az2. 在表A中查询出自动增长列中31到40之间的数据(注意可能不是连续的)select * from A where id between 31 and 403. 有一个表table中有一个自动增长字段ID,如果

10、在存储过程中向这个表插入一条记录后,如何获得新记录的ID.(写出获取新记录ID的函数即可)CREATE FUNCTION c_currentId()RETURNS intASBEGINdeclare lastId intselect lastId=max(cid) from cRETURN (lastId)ENDselect tempdb.dbo.c_currentId() as 当前C表中最新的编号4. having的用法, 是用来做什么的 having用来对group by 分组后的记录进行过滤。5. sql中的什么函数可以转换为时间 select convert(datetime,200

11、0/01/01) select cast(2001/02/02 as datetime)6. 查询数据放入一张临时表 select * into #A from Test select * from #A8、如何使用SQL进行模糊查询LIKE条件一般用在指定搜索某字段的时候, 通过”% _” 通配符的作用实现模糊查找功能，通配符可以在前面也可以在后面或前后都有。搜索以mian开头：SELECT * FROM teble WHERE title LIKE mian% 搜索以mian结束：SELECT * FROM teble WHERE title LIKE %mian 搜索包含mian：SEL

12、ECT * FROM teble WHERE title LIKE %mian%注释：%表示0个或多个字符构成的字符串_表示单个字符 9、说一下mysql, oracle等常见数据库的分页实现方案？1.Oracle:select * from ( select row_.*, rownum rownum_ from ( query_SQL ) row_ where rownum = min2.SQL Server:select top pagesize * from tablename where id not in (select top pagesize*(page-1) id from

13、tablename order by id) order by id3.MySQLselect * from tablename limit position, counter4.DB2select * from (select *,rownumber() as ROW_NEXT from tablename) where ROW_NEXT between min and max1.分页方案一：(利用Not In和SELECT TOP分页)效率次之语句形式：SELECT TOP 10 * FROM TestTableWHERE(ID NOT IN (SELECT TOP 20 id FROM

14、TestTable ORDERBY id) ORDERBYIDSELECT TOP 页大小 * FROM TestTableWHERE( ID NOT IN(SELECT TOP 每页大小-1*待查询页数-1 id FROM 表ORDERBY id) ORDERBYID思路:先查询出待查询页之前的全部条数的id,查询ID不在这些ID中的指定数量条数2.分页方案二：(利用ID大于多少和SELECT TOP分页)效率最高语句形式：SELECT TOP 10 * FROM TestTableWHERE(ID(SELECT MAX(id) FROM(SELECT TOP20 id FROM TestT

15、able ORDERBYid)AS T)ORDERBY IDSELECT TOP 页大小* FROM TestTableWHERE(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

16、 a order by id descSELECT 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来判断一个值是不是null3.所有包含null的计算表达式的返回结果都是null, 这句话正确吗？比如

17、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. (no

18、t unknown) = unknown 正确吗？答案：正确11、介绍一下游标游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。游标通过以下方式扩展结果处

19、理：允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中使用的访问结果集中的数据的 Transact-SQL 语句。12、介绍一下SQL Server的全文索引全文索引可以对存储在SQL Server数据库中的文本数据执行快速检索功能。同LIKE谓词不同，全文索引只对字符模式进行操作，对字和语句执行搜索功能。全文索引对于查询非结构化数据非常有效。一般情况下，可以对char、varchar和nvarchar数据类型的列创建全文索引，同时，还可以对

20、二进制格式的列创建索引，如image和varbinary数据类型列。对于这些二进制数据，无法使用LIKE谓词。为了对表创建全文索引，表必须包含单个、唯一、非空列。当执行全文检索的时候，SQL Server搜索引擎返回匹配搜索条件的行的键值。一般情况，使用sql server中的全文索引，经过大体4个步骤：安装全文索引服务；为数据表建立全文索引目录；使全文索引与数据表内容同步；使用全文索引进行查询。13、什么是唯一索引唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。例如，如果在 last_name、first_name 和 middle_

21、initial 列的组合上创建了唯一索引 full_name，则该表中任何两个人都不可以具有相同的全名。聚集索引和非聚集索引都可以是唯一的。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。只有当唯一性是数据本身的特征时，指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性，则应在列上创建 UNIQUE 或 PRIMARY KEY 约束，而不要创建唯一索引。例如，如果打算经常查询雇员表（主键为 emp_id）中的社会安全号码 (ssn) 列，并希望确保社会安全号码的唯一性，则在 ssn 列上创建 UNIQUE 约束。如果用户为一个以上的雇员输入

22、了同一个社会安全号码，则会显示错误。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(i

23、d),primary key(sid,cid);2)写出SQL语句,查询选修了所有选修课程的学生；答：SQL语句如下：select stu.id, stu.name from s stuwhere (select count(*) from sc where sid=stu.id) =(select count(*) from c);3)写出SQL语句,查询选修了至少5门以上的课程的学生。答：SQL语句如下：select stu.id, stu.name from s stuwhere (select count(*) from sc where sid=stu.id)=5;2.数据库表(Te

24、st)结构如下：(SQL)IDNAMEAGEMANAGER(所属主管人ID)106A30104109B19104104C20111107D35109112E25120119F45NULL3.有3个表（15分钟）：(SQL)Student 学生表 (学号，姓名，性别，年龄，组织部门)Course 课程表 (编号，课程名称)Sc 选课表 (学号，课程编号，成绩)表结构如下：1)写一个SQL语句，查询选修了计算机原理的学生学号和姓名（3分钟）答：SQL语句如下：select stu.sno, stu.sname from Student stuwhere (select count(*) from

25、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 fr

26、om student stuwhere (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 说明OR

27、ACLE数据库实例与ORACLE用户的关系？答：实例可以包含多个用户，一个用户只能在一个实例下4 创建数据库时，自动建立的tablespace名称？答：SYSTEM tablespace5 创建用户时，需要赋予新用户什么权限才能使它连上数据库？答：CONNECT6 IMPORT和SQL*LOADER这2个工具的不同点？答：这两个ORACLE工具都是用来将数据导入数据库的。区别是：IMPORT工具只能处理由另一个ORACLE工具EXPORT生成的数据。而SQL*LOADER可以导入不同的ASCII格式的数据源。7解释冷备份和热备份的不同点以及各自的优点？答：热备份针对归档模式的数据库，在数据库仍

28、旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）8比较truncate和delete命令？答：两者都可以用来删除表中所有的记录。区别在于：truncate是DDL（data defining language数据定义语言），它移动HWK，不需要rollback segment（处理事务回滚操作）而Delete是DM

29、L（data manufacturing language数据操作语言）操作，需要rollback segment（处理事务回滚操作）且花费较长时间。9给出数据的相关约束类型？答：主键约束，外键约束，非空约束，唯一约束，检查约束。10说明索引的类型与作用？答：索引类型上分为聚集索引，非聚集索引其作用是加快查询速度。11解释归档和非归档模式之间的不同和它们各自的优缺点答：归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高。12解释$ORACLE_HOME和$ORACLE_BA

30、SE的区别？答：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 = N;18如何搜索出第NM条记录？答：select * from 表 where Rownum = MMinusselect * from 表 where Rownum = N;19有一个数据表（TEST），字段如