数据库方面面试题-DBA数据库管理员JAVA程序员架构师必看.doc

-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的)这样的话就把所有重复的都删除了。慎重法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录SELECT * FROM EM5_PIPE_PREFABWHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D D相当于First,SecondWHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING ANDEM5_PIPE_PREFAB.DSNO=D.DSNO);- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录DELETE FROM EM5_PIPE_PREFABWHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB DWHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING ANDEM5_PIPE_PREFAB.DSNO=D.DSNO);2、说一下数据表设计要遵守的三范式是什么？除了这些你觉得数据表的设计还要注意哪些规则？1、无重复列，实际上就是一个表中一个列不允许有多值属性、复合属性或者嵌套的表，即列的值域必须是原子值，表必须有主键。2、属性完全依赖于主键消除部分子函数依赖。第二范式其实是在第一范式上的扩展，所以第二范式必须符合第一范式，表中列不存在部分函数依赖。3、属性不依赖于其它非主属性消除传递依赖。第三范式是在第二范式的基础上进行的扩展，所以必须符合第二范式且表中不存在传递依赖。数据量大的情况下如：百万级数据肯定不能按第三范式来了，必须适当增加冗余数据，减少查询的关联次数，当然做好索引是大前提。还可以考虑使用分区表。还不行的话那就换个牛叉点的database。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 selectUserAsSelect * from user where age 184、1. 你对哪种数据库最熟悉?使用该数据库多少年?简单描述对该数据库的理解.答：SQL，适合中小型企业使用2. 对其它较熟悉的商业数据库的了解及使用年限.3. 如何将数据库(实例)备份和还原?(针对自己最熟悉的)答：SQL，在企业管理器上右点要备份或还原的数据，从所有任务选择备份或还原。4. 如何知道一个表或视图的结构?答：查询系统表sysobjects5. 描述INNER JOIN的功能和已在何种情况下使用INNER JOIN.答：内联接是用比较运算符比较要联接列的值的联接，连接的2个表都要满足、5、存储过程和SQL语句的优缺点存储过程的优缺点：优点：1.由于应用程序随着时间推移会不断更改，增删功能，TSQL过程代码会变得更复杂，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 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,如果在存储过程中向这个表插入一条记录后,如何获得新记录的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,2000/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：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 = min2.SQL Server:select top pagesize * from tablename where id not in (select top pagesize*(page-1) id from 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 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 TestTable 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 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, 这句话正确吗？比如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, from s stuwhere (select count(*) from sc where sid=stu.id) =(select count(*) from c);3)写出SQL语句,查询选修了至少5门以上的课程的学生。答：SQL语句如下：select stu.id, from s stuwhere (select count(*) from sc where sid=stu.id)=5;2.数据库表(Test)结构如下：(SQL)IDNAMEAGEMANAGER(所属主管人ID)106A30104109B19104104C20111107D35109112E25120119F45NULL要求:列出所有年龄比所属主管年龄大的人的ID和名字?答：SQL语句如下：select from test employee where employee.age(select manager.age from test manager where manager.id=employee.manager);3.有3个表（15分钟）：(SQL)Student 学生表 (学号，姓名，性别，年龄，组织部门)Course 课程表 (编号，课程名称)Sc 选课表 (学号，课程编号，成绩)表结构如下：1)写一个SQL语句，查询选修了计算机原理的学生学号和姓名（3分钟）答：SQL语句如下：select stu.sno, stu.sname from Student stuwhere (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 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 说明ORACLE数据库实例与ORACLE用户的关系？答：实例可以包含多个用户，一个用户只能在一个实例下4 创建数据库时，自动建立的tablespace名称？答：SYSTEM tablespace5 创建用户时，需要赋予新用户什么权限才能使它连上数据库？答：CONNECT6 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 = N;18如何搜索出第NM条记录？答：select * from 表 where Rownum = MMinusselect * from 表 where Rownum = N;19有一个数据表（TEST），字段如下：ID numberPARENT_ID numberNAME 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 = NMinusselect * from (select * from 表 order by 列名 Desc) where Rownum 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 22005-05-10 1 2create 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 rq2) 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.rq3)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) bwhere a.col001=b.col0012.请教一个面试中遇到的SQL语句的查询问题表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。select (case when ab then a else b end ),(case when bc then b esle c end)from table_name3.面试题：一个日期判断的sql语句？请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)select * from tb where datediff(dd,SendTime,getdate()=04.有一张表，里面有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 table5.在sqlserve