部分常见ORACLE面试题以及SQL注意事项

一、表的创建：一个通过单列外键联系起父表和子表的简单例子如下：CREATETABLEparent(idINTNOTNULL,PRIMARYKEY(id))CREATETABLEchild(idINT,parent_idINT,INDEXpar_ind(parent_id),FOREIGNKEY(parent_id)REFERENCESparent(id)ONDELETECASCADE)建表时注意不要用关键字当表名或字段名，如insert,use等。CREATETABLEparent(idINTNOTNULL,PRIMARYKEY(id))TYPE=INNODB;InnoDBTables概述InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crashrecoverycapabilities)的事务安全(transaction-safe(ACIDcompliant))型表。InnoDB提供了行锁(lockingonrowlevel)，提供与Oracle类型一致的不加锁读取(non-lockingreadinSELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lockescalation)，因为InnoDB的列锁定(rowlevellocks)适宜非常小的空间。InnoDB是MySQL上第一个提供外键约束(FOREIGNKEYconstraints)的表引擎。InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。从一个表中查询出数据插入到另一个表中的方法：select*intodestTblfromsrcTbl;insertintodestTbl(fld1,fld2)selectfld1,5fromsrcTbl;以上两句都是将srcTbl的数据插入到destTbl，但两句又有区别的。第一句（selectintofrom）要求目标表（destTbl）不存在，因为在插入时会自动创建。第二句（insertintoselectfrom）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。如果只想要结构而不要数据。createtables_emp_42asselect*froms_empwhere1=2;//永假式SQL查询练习题1.表1：book表，字段有id(主键)，name(书名)；表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出2.归还）。idname1English2Math3JAVAidbookIddependDatestate112009-01-021212009-01-122322009-01-141412009-01-171522009-02-142622009-02-151732009-02-181832009-02-192要求查询结果应为：(被借出的书和被借出的日期)IdNamedependDate1English2009-01-172Math2009-02-15Selecte.bookId,b.name,e.dependDatefrombookb,bookEnrolewhere第二个表是用来登记的，不管你是借还是还，都要添加一条记录。请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。参考语句：selectbook.id,book.name,max(dependDate)frombookinnerjoinbookEnrolonbook.id=bookEnrol.bookidANDbooker.state=1groupbybook.id;2第(1)题练习使用groupby/having子句。类似的笔试题还有：表一：各种产品年销售量统计表sale年产品销量2005a7002005b5502005c6002006a3402006b5002007a2202007b350要求得到的结果应为：年产品销量2005a7002006b5002007b350即：每年销量最多的产品的相关信息。参考答案：Select*fromsaleawherenotexists(select*fromsalewhere年=a.年and销量a.销量);--or:select*fromsaleainnerjoin(select年,max(销量)as销量fromsalegroupby年)bona.年=b.年anda.销量=b.销量3.查询语句排名问题：名次姓名月积分(char)总积分（char）1WhatIsJava1992水王769813新浪网65964牛人2295中国队64896北林信息66667加太阳53668中成药11339西洋参252610大拿3323如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。select*fromtablenameorderbycast(总积分asint)desc表tbuidmark17162322253433484143想查出uid=4的名次：uidmc43selectuid,sum(mark)astotalfromtab_namegroupbyuidorderbytotaldesc;4表A字段如下monthnameincome月份人员收入1a10002a20003a3000要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入要求列表输出为月份当月收入上月收入下月收入2200010003000Select(SelectMonthFromTableWhereMonth=To_Char(Sysdate,'mm'))月份,(SelectSum(Income)FromTableWhereMonth=To_Char(Sysdate,'mm'))当月收入,(SelectSum(Income)FromTableWhereTo_Number(Month)=To_Number(Extract(MonthFromSysdate))-1)上月收入,(SelectSum(Income)FromTableWhereTo_Number(Month)=To_Number(Extract(MonthFromSysdate))+1)下月收入FromDual5.删除重复记录方法原理：1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。实现方法：SQLcreatetablea(2bmchar(4),--编码3mcvarchar2(20)--名称4)5/SQLselectrowid,bm,mcfroma;ROWIDBMMC-----------------------------000000D5.0000.000211111111000000D5.0001.000211121111000000D5.0002.000211131111000000D5.0003.000211141111000000D5.0004.000211111111000000D5.0005.000211121111000000D5.0006.000211131111000000D5.0007.000211141111查询到8记录.查出重复记录SQLselectrowid,bm,mcfromawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);ROWIDBMMC------------------------------------------000000D5.0000.000211111111000000D5.0001.000211121111000000D5.0002.000211131111000000D5.0003.000211141111删除重复记录SQLdeletefromaawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);删除4个记录.SQLselectrowid,bm,mcfroma;ROWIDBMMC------------------------------------------000000D5.0004.000211111111000000D5.0005.000211121111000000D5.0006.000211131111000000D5.0007.000211141111其他组合函数Groupby子句Distinct关键字伪列ROWNUM，用于为子查询返回的每个行分配序列值注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where后只能跟单行函数，不能有组函数。使用TOP-N分析法TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录TOP-N查询包含以下内容：1，一个用于排序数据的内联视图2，使用ORDERBY子句或DESC参数的子查询3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句//语法：SELECTROWNUM,column_listFROM(SELECTcolumn_listFROMtable_nameORDERBYTop-n-column_name)WHEREROWNUM=N例1：查询Employee表的顶部10条记录//方法1：单表时可以用selectcEmployeeCode,vFirstName,vLastNamefromemployeewhererownum=10//方法2：较复杂的查询，建议使用这种select*from(selectrownumasnum,cEmployeeCode,vFirstName,vLastNamefromemployee)wherenum=10例2:查询Employee表的第1到第10条记录，可以用于分页显示//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownumselect*from(selectrownumasnum,Employee.*fromEmployee)wherenumbetween10and20select*from(selectrownumasnum,Employee.*fromEmployee)wherenumbetween1and10SQL注入1=1永远成立,相当于查询所有记录select*fromperson_zdkwhere1=1ornamelike'%a%'andage=13;DECODE函数是ORACLEPL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)该函数的含义如下:IF条件=值1THENRETURN(翻译值1)ELSIF条件=值2THENRETURN(翻译值2)......ELSIF条件=值nTHENRETURN(翻译值n)ELSERETURN(缺省值)ENDIF假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：selectdecode(sign(salary-8000),1,salary*1.15,-1,salar