数据库方面面试题

1、如何查找和删除数据库中的重复数据法一:用Groupby语句此查找很快的selectcount(num),max(name)fromstudent–查找表中num列重复的，列出重复的记录数，并列出他的name属性groupbynumhavingcount(num);1–按num分组后找出表中num列重复，即出现次数大于一次deletefromstudent(上面Select的)这样的话就把所有重复的都删除了。—–慎重法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:—-执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录SELECT*FROMEM5_PIPE_PREFABWHEREROWID!=(SELECTMAX(ROWID)FROMEM5_PIPE_PREFABD–D相当于First,SecondWHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGANDEM5_PIPE_PREFAB.DSNO=D.DSNO);—-执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录DELETEFROMEM5_PIPE_PREFABWHEREROWID!=(SELECTMAX(ROWID)FROMEM5_PIPE_PREFABDWHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGANDEM5_PIPE_PREFAB.DSNO=D.DSNO);2、说一下数据表设计要遵守的三范式是什么？除了这些你觉得数据表的设计还要注意哪些规则？1、无重复列，实际上就是一个表中一个列不允许有多值属性、复合属性或者嵌套的表，即列的值域必须是原子值，表必须有主键。2、属性完全依赖于主键[消除部分子函数依赖]。第二范式其实是在第一范式上的扩展，所以第二范式必须符合第一范式，表中列不存在部分函数依赖。3、属性不依赖于其它非主属性[消除传递依赖]。第三范式是在第二范式的基础上进行的扩展，所以必须符合第二范式且表中不存在传递依赖。数据量大的情况下如：百万级数据肯定不能按第三范式来了，必须适当增加冗余数据，减少查询的关联次数，当然做好索引是大前提。还可以考虑使用分区表。还不行的话那就换个牛叉点的database。3、写一个SQL存储过程，建立一个表USER字段是姓名，年龄，职位，权限，然后向里面插入6条数据，然后查询出年龄大于18的所有信息。Createtableuser(Namevarchar(20),Ageint,Positionvarchar(20)Quanxianvarchar(20))Insertintouservalues(‘111’,11,’11111’,’1111111’)Insertintouservalues(‘112’,12,’11112’,’1111112’)Insertintouservalues(‘113’,13,’11113’,’1111113’)Insertintouservalues(‘114’,14,’11114’,’1111114’)Insertintouservalues(‘115’,15,’11115’,’1111115’)Insertintouservalues(‘116’,16,’11116’,’1111116’)CreateprocedureselectUserAsSelect*fromuserwhereage184、1.你对哪种数据库最熟悉?使用该数据库多少年?简单描述对该数据库的理解.答：SQL，适合中小型企业使用2.对其它较熟悉的商业数据库的了解及使用年限.3.如何将数据库(实例)备份和还原?(针对自己最熟悉的)答：SQL，在企业管理器上右点要备份或还原的数据，从所有任务选择备份或还原。4.如何知道一个表或视图的结构?答：查询系统表sysobjects5.描述INNERJOIN的功能和已在何种情况下使用INNERJOIN.答：内联接是用比较运算符比较要联接列的值的联接，连接的2个表都要满足、5、存储过程和SQL语句的优缺点存储过程的优缺点：优点：1.由于应用程序随着时间推移会不断更改，增删功能，T－SQL过程代码会变得更复杂，StoredProcedure为封装此代码提供了一个替换位置。2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－实际上是MicrosoftSQLServer为在存储过程中获取由T-SQL指定的结果而必须采取的步骤的记录。）缓存改善性能。……..但sqlserver新版本，执行计划已针对所有T-SQL批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用(RPC)处理服务器上的存储过程而提高性能。RPC封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。7.更好的版本控制，通过使用MicrosoftVisualSourceSafe或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。8.增强安全性：a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；b、提高代码安全，防止SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；c、SqlParameter类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新GetValue()调用，等等，这时候估计比较繁琐了。2.可移植性差由于存储过程将应用程序绑定到SQLServer，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于RDBMS的中间层中可能是一个更佳的选择。Sql语句灵活，可移植性强，查询速度比存储过程慢些6、为数据库创建索引都需要注意些什么？1.索引能提高速度的关键就是索引所占的空间要比表小得多2.注意索引的大小，有一些表可以建成索引组织表3.索引的列不要太多，要选择一些selective比较低的列建B-tree索引，选择selective高的列建bitmap索引(在更新比较多的表不不要建bitmap索引)4.将selective较低的列放在前面5.在更新不多的表上建索引时，可以考虑用compress选择，以节约索引的空间7、一组SQL相关操作面试题1.在表A中有数据IDMO1Y2N请用一个SELECT语句写出，如果MO的值为“Y”，返回“YES”，为N返回“NO”效果如下:IDMO1YES2NOSELECTID,MO=CASEWHENMO=’Y’THEN‘YES’WHENMO=’N’THEN‘NO’ENDFROMAz2.在表A中查询出自动增长列中31到40之间的数据(注意可能不是连续的)select*fromAwhereidbetween31and403.有一个表table中有一个自动增长字段ID,如果在存储过程中向这个表插入一条记录后,如何获得新记录的ID.(写出获取新记录ID的函数即可)CREATEFUNCTIONc_currentId()RETURNSintASBEGINdeclare@lastIdintselect@lastId=max(cid)fromcRETURN(@lastId)ENDselecttempdb.dbo.c_currentId()as‘当前C表中最新的编号’4.having的用法,是用来做什么的having用来对groupby分组后的记录进行过滤。5.sql中的什么函数可以转换为时间selectconvert(datetime,’2000/01/01′)selectcast(’2001/02/02′asdatetime)6.查询数据放入一张临时表select*into#AfromTestselect*from#A8、如何使用SQL进行模糊查询LIKE条件一般用在指定搜索某字段的时候,通过”%_”通配符的作用实现模糊查找功能，通配符可以在前面也可以在后面或前后都有。搜索以mian开头：SELECT*FROMtebleWHEREtitleLIKE‘mian%’搜索以mian结束：SELECT*FROMtebleWHEREtitleLIKE‘%mian’搜索包含mian：SELECT*FROMtebleWHEREtitleLIKE‘%mian%’注释：%表示0个或多个字符构成的字符串_表示单个字符9、说一下mysql,oracle等常见数据库的分页实现方案？1.Oracle:select*from(selectrow_.*,rownumrownum_from(query_SQL)row_whererownum=max)whererownum_=min2.SQLServer:selecttop@pagesize*fromtablenamewhereidnotin(selecttop@pagesize*(@page-1)idfromtablenameorderbyid)orderbyid3.MySQLselect*fromtablenamelimitposition,counter4.DB2select*from(select*,rownumber()asROW_NEXTfromtablename)whereROW_NEXTbetweenminandmax——————————————————————————————–1.分页方案一：(利用NotIn和SELECTTOP分页)效率次之语句形式：SELECTTOP10*FROMTestTableWHERE(IDNOTIN(SELECTTOP20idFROMTestTableORDERBYid))ORDERBYIDSELECTTOP页大小*FROMTestTableWHERE(IDNOTIN(SELECTTOP每页大小-1*待查询页数-1idFROM表ORDERBYid))ORDERBYID思路:先查询出待查询页之前的全部条数的id,查询ID不在这些ID中的指定数量条数2.分页方案二：(利用ID大于多少和SELECTTOP分页)效率最高语句形式：SELECTTOP10*FROMTestTableWHERE(ID(SELECTMAX(id)FROM(SELECTTOP20idFROMTestTableORDERBYid)AST))ORDERBYIDSELECTTOP页大小*FROMTestTableWHERE(ID(SELECTMAX(id)FROM(SELECTTOP每页大小*待查询页数-1idFROM表ORDERBYid)AST))ORDERBYID思路:先获得待查询页的之前全部条数id,获得它们当中最大的ID号,以此最大ID号为标志,查找比这个ID号大的指定条数3.分页方案三：SELECTTOPPageSize*FROM(SELECTTOPnPage*PageSize*fromYOURTABLEorderbyid)asaorderbyiddescSELECTTOP每页条数*FROM(SELECTTOP待查询页*每页条数)*fromYOURTABLEorderbyid)asaorderbyiddesc思路:先正排序查询出待查询页之前(包括当前页)的全部条数,然后将其倒排序,取指定条数10、几道SQL面试题1.解释一下SQL里面的null答案：null代表一个unknown的值或者