数据库程序员面试分类真题27.docx

《数据库程序员面试分类真题27.docx》由会员分享，可在线阅读，更多相关《数据库程序员面试分类真题27.docx（12页珍藏版）》请在课桌文档上搜索。

1、数据库程序员面试分类真题27倚答题1.Oracle和MySQ1.中的分组（GROUPBY）有什么区别?正确答案：OraCIe对于GROUPBY是严格的,所有要SE1.ECT出来的字段必须在GRO（江南博哥）UPBY后边出现，否则会报错：u0R-00979:notaGROUPBYexpressiono而MySQ1.则不同，如果SE1.ECT出来的字段在GRoUPBY后面没有出现，那么会随机取出一个值，而这样查询出来的数据不准确，语义也不明确。所以，作者建议在写SQ1.语句的时候，应该给数据库一个非常明确的指令，而不是让数据库去猜测，这也是写SQ1.语句的一个非常良好的习惯。下面给出一个示例。有一

2、张T_MAX_1.HR表，数据如下图所示，有3个字段ARTIC1.E.AUTHOR和PRICE。请选出每个AUTHOR的PRICE最高的记录（要包含所有字段）。ARTIC1.EAUTHORPRICE0001B3.99000210.990003C1.690004B19.9500056.96首先给出建表语句：CREATETAB1.ET_MAX_1.HR(ARTIC1.EVRCHR2(30),AUTHORVARCHR2(30),PRICENUMBER)OracteCREATETAB1.ET_MAX_1.HK(ARTIC1.EVARCHAR(30),AUTHORVARCHAR(30),PRICEF1.

3、OAT);MySQ1.OraCIe通用INSERTINTOT_MAX_1.HRVA1.UES(0001,B,3.99);INSERTINTOT_MAX_1.HKVA1.UES(,0002,A,10,99);INSERTINTOT_MAX_1.HRVA1.UESC0003,C,.1.69);INSERTINTOT_MAX_1.HRVA1.UESC0004,B,19.95);INSERTINTOT_MAX_1.HRVA1.UESC0005,A,6.96);COMMIT;SE1.ECT*FROMTMAX1.HR;在OraCIe中的数据：-AUTHORBRlCE1.HRgOrchSnSE1.ECTFR

4、OM1.MAX_1.HRlAKiK1.EAUTHORPRICEOOOIB3.99OOO2A10.990003CIJ0004B19.95啷A6.96在MySQ1.中的数据:mysqlSE1.ECT,FROMT_MAX_1.HR;IARn(IE：.U三Fffl(IIIIMIselnw11O;wsion5.6.21-HiteTprise-Coeercial-advanced-logmjqSE1.ECTTAKnC1.ElAIJrHOKMAXlT,PR1CE)FROMT_MAX_1.HRTGROUPBY1.ITIIOR:三IOEA11三WX(T-PRICE)MO2A10.99MOIB19.95W03!C

5、1.693rowsint(Q(Osa)11i)SlSE1.ECT*FROMT_MAX_1.HRTGROUPBYTA(JTHOR;IARIiafIIIM三IKICE100Q2IA,111!10.99,100oiIBI199I三!C1.693Wiinjet(0.00sec)虽然执行不报错，可以查询出数据，但是从结果来看数据并不是最终想要的结果，甚至数据是错乱的。卜.面给出几种正确的写法(在OraCle和IMySQ1.中均可执行：(1)使用相关子查询SE1.ECT*1ROMT_MAX_1.HKTWHERE(T.AUTHOR.T.PRICE)IN(SE1.ECTNT.AUTHOR,MAX(NT.PRI

6、CE)PRICE1ROMT_MAX_1.HRNTGROUPBYNT.AUTHOR)ORDERBYT.ARTIC1.E;SE1.ECT*FROMT_MAX_1.HRTWHERET.PRICE=(SE1,ECTMAX(NT.PRICE)PRICEFROMT_MAX_1.HRNTWHERET.AUTlIOR=NT.AUTHOR)ORDERBYT.ARTIC1.E;(2)使用非相关子查询SE1.ECTT.*FROMT_MAX_IJIRTJOIN(SE1.ECTNT.AUTHOR,MAX(NT.PRICE)PRICEFROMT_MAX_1.HRNTGROUPBYNT.AUTHOR)TlONT.AUTHO

7、R=T1.AUTHORANDT.PRlCE=Tl.PRICEORDERBYT.ARTIC1.E;(3)使用1.EFTJOIN语句SE1.ECTT.*FR0MT_MAX_1.HRT1.EFTOUTERJOINT_MAX_1.HRTlONT.AUTHOR=T1.AUTHORANDT.PRICESE1.三T.2 FRoM1.MW1.HRT3 1.EFTourERJOlN1.MAX-UiRTI4 ONrAirnKS-TlJWnKOR5 ANDTJ,RiaSE1.ECTT.,- FRoM1.MAX一1.HRT- 1.HT0U三JOINT_MAX_1.HRTI- ONT.AIIHOR-TlAUTHOR-

8、Andtprkktlpiuce- Wheretiarticieisnull- ORDERBYTAKnC1.E;，IiARTIQfIMWIPRICEI(三Ia10.99;Ic1.69OW!B务i119.96，Jnwshsct(O.OOjcc)2.Oracle和MySQ1.中的分组(GRO1.PBY)后的聚合函数分别是什么？正确答案：在OraCIe中,可以用Wb1.CoNCAT函数或1.lSTAGG分析函数:在MySQ1.中可以使用GRoUP_C0NCAT函数。示例如下：首见给出建表语句：CREATETAB1.ETJIXJJIR(ARTIC1.EVRCHAR2(30),AUTHORVRCHAR2(

9、30),PRICENUMBER);Oracle-CREATETAB1.ET_MAX_1.HR(ARTIC1.EVARCHAR(30),AUTHORVRCHAR(30),PRICEF1.OAT)MySQ1.OraCIe通用INSERTINTOTMAX1.HRVA1.UES(,000,B,3.99);INSERTINTOTMAX1.HRVA1.UES(,0002,A,10.99)INSERTINTOT_MAX_1.HRVA1.UES(,00031,C,1.69);INSERTINTOT_MAX_1.HRVA1.UES(,00041,B,19.95)INSERTINTOT_MAX_1.HRVA1.U

10、ES(,0005,*/V,6.96);COMMIT;SE1.ECT*ROMT_MAX_1.HK;在MySQ1.中：11wSelecttauihor,GROUPlONCATfrARn(IE)IGROU”oNcAT(TpRKl)-FROM1.MAX_1.HRT-Groupbytauthor;.TOGROl?.(三T(T.ARTIOD；三P.三T(T.PRICE)00Q2,W)5I1(199,6.%0001.0CW,I199,19.950003I1.69Srousinitt(O-Wsoc)在OraCle中:1.HRgcrdBnPSaECrTAUnIOR.啊。赋机TARME)ARTIeIEtWM一CO

11、NCATn,PR1CT)PRICE2 FROM1.M必1.HRT3 GRoIPBYTA加OR;AlTOARTIC1.EPRICEAOXe.000510.W,6.96B0!,00043.99.19.95COKe1.691.HRgorcUsnSE1.ECT!AUTHOR.2 UsTAGG(1.ARTIC1.E.)WlnHNGROtTORDERBYTfRlCE).ARTIC1.E,3 USTAOHT.PRKI,：iWTTHINGRWrderBYTfRlCE)PRICE4 FRoM1.MAI1.HRT5 GRO1.TBYTOfcMWARTIC1.EPRIdAOoO5.00026.96,Id99B000

12、1,0013.99,19.95C00031.693. MySQ1.的分区表是什么？如何查询MySQ1.是否支持分区？正确答案：表分区是指根据一定规则，将数据库中的一张表分解成多个更小的、容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成，每个分区都是一个独立的对象。分区有利于管理大表，体现了“分而治之”的理念。一个表最多支持1024个分区。在MySQ1.5.6.1之前可以通过命令showvariableslike,%have_partitioning%,来查看MySQ1.是否支持分区。若have_partintioning的值为YES,则表示支持分区。从VySQ1.5.6.

13、1开始，该参数已经被去掉1.而是用SHOWP1.UGINS来代替0若有partition行且STATUS列的值为ACTIVE,则表示支持分区，如下：0ldbSHOWP1.UGINS;o?:SuusIType1.ihrarr1.iceisepartitionAaDESKRAGEDCINEXlllPKffiIETARY此外，也可以使用表INFORMATlON_SCHEMA.P1.UGINS来查询,如下:m)SE1.ECTP1.UG(NKMEASNAME- P1.1.GW_VTRSIONASVERSION.- PUG凡STATUSASSTA1US- FROMINFORMXnON_SCHEMA.P1.

14、UGINSA- WHEREP1.UGINjYPEnlSTORAGEENGiNF- ANDAT1.UGNAME=卿i(MNAJE郦STARSIpartition1.0ACTDE4. MySQ1.的分区表有哪些？正确答案：MySQ1.支持的分区类型主要包括RANGE分区、1.lST分区、HASH分区和KEY分区。分区表中对每个分区再次分割就是子分区(SUbPartitiOning),又称为复合分区。在MySQ1.5.5中引入了CO1.UMNS分区,细分为RANGECO1.UMNS1.ISTCO1.UMNS分区。引入CO1.UMNS分区解决了MySQ1.5.5版本之前RANGE分区和1.IST分区只

15、支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题.KEY分区类似HASH分区，HASH分区允许使用用户自定义的表达式，但KEY分区不允许使用用户自定义的表达式。HASH仅支持整数分区，而KEY分区支持除了B1.OB和TEXT的其他类型的列作为分区键。KEY分区语法为PARTITIONBYKEY(EXP)PARTlTK)NS4;EXP是零个或多个字段名的列表在进行KEY分区的时候，EXP可以为空，如果为空，那么默认使用主键作为分区键。若没有主键则会选择非空唯一键作为分区键。MySQ1.允许分区键值为NU1.1.,分区键可能是个字段或者一个用户定义的衣达

16、式。一股情况下，MySQ1.在分区的时候会把NU1.1.值当作零值或者个最小值进行处理。需要注意以下几点：I)RANGE分区：NU1.1.值被当作最小值来处理。2) 1.lST分区：NU1.1.值必须出现在列表中，否则不被接受。3)I1SHKEY分区：NU1.1.值会被当作零值来处理。通过A1.TERTAB1.E命令可以对分区进行添加、删除、重定义、合并、拆分等操作：通过IFORMATION_SCHEVA.PARTITIONS可以查询分区数、行数等信息：通过EXP1.AlNPARTITIONS可以查看分区表的执行计划。5. MySQ1.的分库分表和表分区(PartitiOning)有什么区别？

17、正确答案：分库分发是指把数据库中的数据物理地拆分到多个实例或多台机器上去。分表指的是通过一定规则，将一张表分解成多张不同的表。表分区(PartitiOning)可以将一张表的数据分别存储为多个文件。如果在写SQ1.的时候，遵从了分区规则，那么就能把原本需要遍历全表的工作转变为只需要遍历表里某一个或某些分区的工作。这样降低了查询对服务器的压力，提升了查询效率。如果分区表使用得当，那么也可以大规模地提升MySQ1.的服务能力。但是这种分区方式，一方面，在使用的时候必须遵从分区规则写SQ1.语句,如果不符合分区规则，性能反而会非常低下：另一方面，Partitioning的结果受到MySQ1.实例，或

18、者说MySQ1.单实例的数据文件无法分倘式存储的限制，不管怎么分区，所有的数据还是都在一个服务器上，没办法通过水平扩展物理服务的方法把压力分摊出去分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。6. 如果MySQ1.密码丢了，那么如何找回密码？正确答案：步骤如卜丁1)关闭MySQ1.,/data3306mysq1StoP或Pkillmysqld02) nysqldsafe-defaults-file=data3306f-skip-grant-tabIe&。3)mysq1-uroot-p-Sdata3306zysq1.sock,按ENTER进入。4)修改密码,U

19、PDATEmysql.userSETPaSSWOrd=PASSWORD(oIdIhr123”)WHEREuser=*root*andhost,Iocalhost;7. mysq!dump备份mysqHhr库及MySQ1.库的命令是什么？正确答案：mysq!dump-uroot-plhrl23-Sdata3306mysql.sock-B-events-XMySQ1.mysqllhroptS(date+F).SqIo8. 如何不进入MySQ1.客户端，执行一条SQ1.命令，帐号USer,密码Passwd,库名DBName,SQ1.为SE1.ECTSySdate()。正确答案：采用-e选项，命令为m

20、ysql-uUser-pPasswd-DDBName-eSE1.ECTsysdate9. 一个给定数据库中，有办法查询所有的存储过程和存储函数吗？正确答案：有。比如给定的数据库名为名为b,可以对INFoRMATION_SCHEMA.ROUTINES表上进行查询。对于存储例程内包体的查询，可通过SHOIYCREATEFUNeTION(对于存储函数)和SHOWCREATEPROCEDlJRE(对于存储例程)语句来查询，如下：町*SE1.ECTRoUTINEJYPE1ROimNE-NAMEFROMINFOR(A11ON.S三MAJlOUTl三WHEREROrn阻SCHEMA=IrJeCROenjEj

21、?K111SE三PMcSlilEoo.iremJMnneterlPROCtIXKdew/njanae&rPMOCREdeo,SHOWCREATEPROCEDUREIhraprodGProaxkcfroclSq1.mOdeSTOCTJRANSJAB1.ES(No_ENGlNE_SUBSTnUnoNCraneProccircCREATEDEFINERjMPROCEDURE,pa(out51)tselectn(*)toSf三W)sus.endch三?CI吟SetjIE1.戚就曲山咄加：mJeneraIFiDjClbaeCoUiimhtinIserdishci10. MySQ1.5.7支持语句级或行级

22、的触发器吗？正确答案：在MySQ1.5.7中，触发器是针对行级的，即触发那在对插入、更新、删除的行级操作时被触发。但是,MySQ1.5.7不支持FoREACHSTATEMENT。H.VySQ1.的注释符号有哪些？正确答案：MySQ1.注释符有三种:1) .2)”注意一后面有一个空格)3)/*.*/il：确界案.查询所有质据的大小：selectconcat(round(sum(data_length/1024/1024/1024),2),GB)asdatafrominformation_schema.tables;查看指定数据库的关小，例如，查看数据库Ihrdb的大小：selectconcat(

23、round(sum(datalength/1024/1024/1024),2),GB,)asdatafromInformafion_schema.tableswheretable_schema=,lhrdb,;查看指定数据库的橐个表的大小，例如，查看数场库Ihrdb中Jlhr表的大小：selectconcat(round(sum(data_length/1024/1024),2),MB,)asdamfrominformation_schema.tableswheretable_schema=，IbrdbandtabIejiame=t-lhr,;13. 如何查看VySQ1.的位数？正确答案：有如

24、下几种办法:Dmysql-V2)nysqlshowvariableslike,%version%,；3) whichmysqlxargsfile(1.inux/UniX系统)4)echoSTATUSmysql-uroot-ppasswordIgrepVer14. MySQ1.有关权限的表有哪几个？正确答案：MySQ1.服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_inStaI1.db脚本初始化。这些权限表包括USer、db、tableS-Priv、COIUmnS_Priv、PrOCe-PriV和host。MySQ1.启动的时候读取这些信息到向存中，或者

25、在权限变更生效M时候，重新读取到内存中。这些表的作用如下：Duser：记录允许连接到服务器的用户账号信息，里面的权限是全局级的。2)db：记录各个账号在各个数据库上的操作权限。3)tables_priv：记录数据表级的操作权限。4)columns_priv：记录数据列级的操作权限。5)host：配合db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。6)PrOCS_Priv：规定谁可以执行哪个存储过程。15. 如果MySQ1.数据库的服务器CPUlF.常高，那么该如何处理?正确答案：当服务器CPU很高时，可以先用操作系统命令top观察是否是m

26、ysqId占用导致的，如果不是，那么找出占用高的进程，并进行相关处理：如果是mysqld造成的，那么可以使用ShOWPrOCeSSIiSt命令查看里面数据库的会话情况，是否有非常消耗资源的SQ1.在运行。找出消耗高的SQ1.,看看执行计划是否准确，INDEX是否缺失，或者是否由于数据量太大造成。一般来说，肯定要kill掉这些线程（同时观察CPU使用率是否下降）,等进行相应的调整（例如,加索引、改写SQ1.、改内存参数）之后，再重新运行这些SQ1.。也有可能是每个SQ1.消耗资源并不多，但是突然之间，有大量的会话连接数据库导致CPU飙升，这种情况就需要与应用一起来分析为何连接数会激增，再做出相应

27、的调整，例如，限制连接数等。16. 如何查询某个表属于哪个库？正确答案：可以通过INFORMATION_SCHEMA库来看询，例如，若想查询表TVAX_1.HR是属于哪个库的，则可以执行：11i）SE1.ECTTABlENAMEJABIEScHEMAFKOMINK）RMA710心CHEMA.TAB1.ESWHERETAB1.ENME=TJAAX_1.HR;T三.三,W-SQBtt1.wV册dbiInminstt（OJTsec）以上结果说明,T_MAX1.HR表属于dbl库。17. 一张表里面有IDH增主键，当INSERT了17条记录之后，删除了第15、16、17条记录，再把MySQ1.重启，再

28、INSERT一条记录，这条记录的ID是18还是15?正确答案：根据表的类型不同而不同：1）如果表的类型是MyISAM,那么是18。因为MyISAM表会把自主键的最大ID记录到数据文件里，市启MySQ1.自增主键的最大ID也不会丢失。2）如果表的类型是InnODB,那么是15。InnODB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTlMIZE操作，都会导致最大ID丢失。18. MySQ1.中的mysql_fetch_now()mysq1_fetch-array()数的区别是什么?正确答案：这两个晟窗返回的都是一个数组，区别就是第一个函数返回的数组是只包含值,只能以r

29、ow0、rowl这样数组下标来读取数据：而mysql_fetch_array()返回的数组既包含第一种，也包含键值对的形式，可以这样读取薮据，确如，数据库的字段是USemame、passwd,则可以row,username,row,PaSSWd。19. 什么是MySQ1.的GTID?正确答案：GTlD(GlobalTransactionID,全局事务ID)是全局事务标识符，是个已提交事务的编号，并且是一个全局唯一的编号。GTlD是从MySQ1.5.6版本开始在主从复制方面推出的重量级特性。GTID实际上是由UUlD+TID组成的，其中UUTD是一个MySQ1.实例的唯一标识：TlD代表了该实例

30、上已经提交的事务数量，并且随着事务提交单调递增。下面是一个GTlD的具体形式：3E11FA47-71CA-11E1-9E33-C809429562:23GTlD有如卜.几点作用：D根据GTID可以知道事务最初是在哪个实例上提交的。2)GTID的存在方便了Replication的Failover0因为不用像传统模式复制那样去找masteJIOg_file和master_log_pose3)基于GTID法建关从复制更加简M，加保每个事务只会被执行一次。20. 在MySQ1.中如何有效地删除一个大表？正确答案：在Oracle中对于大表的删除可以通过先TRUNCATE+REUSESTORAGE参数，再

31、使用DEA1.1.oCATE逐步缩小，最后DROP掉表。在MySQ1.中,对于大表的删除,可以通过建立硬链接(Hard1.ink)的方式来删除。建立硬链接的方式如下：Inbigtable.ibdbig_table.ibd.hdlk建立硬逐接之后就可以祉用DROPTAB1.E删除表了，最后在OS级别删除硬链接的文件即可。为什么通过这种方式可以快速删除呢？当多个文件名同时指向同一个IM)DE时，此时这个NODE的引用数Nl,删除其中任何一个文件都会很快。因为其宜接的物理文件块没有被删除，只是删除了一个指针而已。当INODE的引用数N=I时，删除文件时需要把与这个文件相关的所有数据块清除，所以会比较耗时。