部分常见ORACLE面试题以及SQL注意事项
一、表的创建：
一个通过单列外键联系起父表和子表的简单例子如下：
CREATETABLEparent(idINTNOTNULL,
PRIMARYKEY(id)
)
CREATETABLEchild(idINT,parent_idINT,
INDEXpar_ind(parent_id),
FOREIGNKEY(parent_id)REFERENCESparent(id)
ONDELETECASCADE
建表时注意不要用关键字当表名或字段名，如insert,use等。
)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(书名)；
idname
1English
2Math
3JAVA
idbookIddependDatestate
112009-01-021
212009-01-122
322009-01-141
412009-01-171
522009-02-142
622009-02-151
732009-02-181
832009-02-192
要求查询结果应为：(被借出的书和被借出的日期)
IdNamedependDate
1English2009-01-17
2Math2009-02-15
Selecte.bookId,b.name,e.dependDatefrombookb,bookEnrolewhere
第二个表是用来登记的，不管你是借还是还，都要添加一条记录。
参考语句：
selectbook.id,book.name,max(dependDate)
frombookinnerjoinbookEnrolonbook.id=bookEnrol.bookidANDbooker.state=1
groupbybook.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的名次：uidmc43
selectuid,sum(mark)astotalfromtab_namegroupbyuidorderbytotaldesc;
4
表A字段如下monthnameincome月份人员收入
1a1000
2a2000
3a3000要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入要求列表输出为月份当月收入上月收入下月收入2200010003000
Select(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的就可以了，其余全部删除。实现方法：SQL>createtablea(2bmchar(4),--编码3mcvarchar2(20)--名称4)5/
SQL>selectrowid,bm,mcfroma;ROWIDBMMC-----------------------------000000D5.0000.000211111111000000D5.0001.000211121111000000D5.0002.000211131111000000D5.0003.000211141111000000D5.0004.000211111111000000D5.0005.000211121111000000D5.0006.000211131111000000D5.0007.000211141111查询到8记录.
查出重复记录SQL>selectrowid,bm,mcfromawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);ROWIDBMMC------------------------------------------000000D5.0000.000211111111000000D5.0001.000211121111000000D5.0002.000211131111000000D5.0003.000211141111删除重复记录SQL>deletefromaawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);删除4个记录.SQL>selectrowid,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)wherenumbetween1and10
SQL注入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,salary*1.2,salaryfromemployee.
SQL中的单记录函数
2.LTRIM和RTRIMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQL>selectltrim(rtrim('gaoqianjing',''),'')fromdual;LTRIM(RTRIM('-------------gaoqianjing
3..SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL>selectsubstr('13088888888',3,8)fromdual;SUBSTR('--------08888888
4日期函数
如:LAST_DAY返回本月日期的最后一天
具体参见oracle笔记.
其他主要函数：.TRUNC按照指定的精度截取一个数；SQRT返回数字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2)返回一个n1除以n2的余数；FLOOR对给定的数字取整数；REPLACE('string','s1','s2')string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串；LOWER返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH返回字符串的长度。
ORALCE常识及SQL基本语法
1，ORACLE安装完成后的初始口令？internal/oraclesys/change_on_installsystem/managerscott/tigerscott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字sysman/oem_temp
2，IBM的Codd（EdgarFrankCodd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE9i中的i（internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间——表——段——区间——块表空间类似于SQLSERVER中数据库的概念
6,SYSDATE返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQLPLUS中执行缓冲区中的SQL命令的方式：SQL>runSQL>rSQL>/
8,在SQLPLUS中修改当前会话的日期显示格式SQL>altersessionsetnls_date_format='YYYY-MM-DD'
9,使用临时变量，提高输入效率SQL>insertintoemp(empno,ename,sal)values(&employeeno,'&employeename',&employeesal);
10,从其他表中复制数据并写入表SQL>insertintomanagers(id,name,salary,hiredate)SQL>selectempno,ename,sal,hiredateSQL>fromempSQL>wherejob='MANAGER';
11,修改表中的记录SQL>updatetablesetcolumn=value[,column=value,……][wherecondition];
12,删除表中的记录SQL>delete[from]table[wherecondition];13,数据库事务，事务是数据库一组逻辑操作的集合一个事务可能是：多个DML语句单个DDL语句单个DCL语句
14,事务控制使用savepoint,rollback,commit关键字SQL>savepointaaa;SQL>rollbacktoaaa;SQL>commit;
15,查询表中的数据select*fromtable_name;selectcolumn_listfromtable_name;
16,NumberandDate可以用于算术运算因为Date类型其实存储为Number类型
17,用运算表达式产生新列SQL>selectename,sal,sal+3000fromemp;SQL>selectename,sal,12*sal+100fromemp;
18,算术表达式中NULL值错误的处理因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值例如：SQL>selectename"姓名",12*sal+comm"年薪"fromempwhereename='KING';姓名薪水--------------------KING因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL注意函数nvl的使用NVL(原值,新值)SQL>selectename"姓名",12*sal+NVL(comm,0)"年薪"fromempwhereename='KING';员工姓名员工薪水--------------------KING60000——————————————
19,使用友好的列名,有下面三种形式SQL>selectenameas姓名,sal月薪,sal*12"年薪"fromemp
20,过滤重复行，使用关键字distinctSQL>selectdistinct*fromemp;21,SQLPLUS访问ORACLE数据库的原理SQL*Plus—>Buffer—>Server—>QueryResult
22,where子句中字符型是区分大小写的，最好都转成大写因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：between……and……in(……valuelist……)like（%代表匹配至多个任意字符，_代表单个任意字符）null（与NULL进行比较时，需要使用isnull或者isnotnull）
25,逻辑运算符，按优先级从高到低排列Not,And,Or
26,Orderby子句中（asc表示升序，desc表示降序）
27,ORACLE函数，分为单行函数：每条记录返回一个结果值多行函数：多条记录返回一个结果值
28,字符函数——转换函数LOWER：转为小写UPPER：转为大写INITCAP：将每个单词的首字母大写，其他字母小写
29,字符函数——操纵函数（注意：ORACLE以UNICODE存储字符）CONCAT:连接两个字符串，与并置运算符“||”类似SUBSTR:substr(string,position,length)从string中的position开始取length个字符LENGTH:返回字符串的长度INSTR:instr(string,value)返回value在string的起始位置LPAD:lpad(string,number,value)若string不够number位，从左起用vlaue字符串填充(不支持中文)
30,四舍五入函数round(数值,小数位)SQL>SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;
ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650
31,数值截取函数trunctSQL>SELECTTRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROMDUAL;
TRUNC(45.923,2)TRUNC(45.923,0)TRUNC(45.923,-1)----------------------------------------------45.924540
32,求模函数MOD(a,b)返回a被b整除后的余数
33,Oracle内部默认的日期格式:DD-MON-YY（24-9月-06）
34,DUAL：哑元系统表，是名义表，只能范围唯一值
35,Date类型的算术运算，以天为单位例如：部门编号为10的员工分别工作了多少年SQL>selectename,(sysdate-hiredate)/365asyearsfromempwheredeptno=10;
ENAMEYEARS--------------------CLARK25.3108341KING24.8697382MILLER24.6861766
36,日期函数MONTHS_BETWEEN返回两个日期之间相差多少个月ADD_MONTHS在日期上加上月份数NEXT_DAY下一个日子selectnext_day(sysdate,'星期一')fromdual;LAST_DAY该月的最后一天ROUND四舍五入日期round(sysdate,'year')或者round(sysdate,'month')TRUNC截取日期trunc(sysdate,'year')或者trunc(sysdate,'month')
37,数据类型转换——Oracle可隐式转换的情况有：FromTovarchar2orchar——number（当字符串是数字字符时）varchar2orchar——datenumber——varchar2date——varchar2
38,数据类型转换——Oracle数据类型转换函数to_charto_numberto_date
39,日期格式模型字符YYYY代表完整的年份YEAR年份MM两位数的月份MONTH月份的完整名称DY每星期中天的三个字符缩写DAY表示星期日——星期六
另外还有D,DD,DDD等。。。
40,NVL(value,substitute)value:是可能有null的列，substitute是缺省值这个函数的作用就是当出现null值的时候，后缺省值替换null
41,Coalesce(exp_name1,exp_name2……exp_n)
42,Decode函数：Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)例如，根据国家名称显示相应的国家代码:1>创建国家表createtablecountrys(vCountryNamevarchar2(50));2>写入几行，分别为中国、日本、韩国insertintocountrysvalues('&name');3>用DECODE函数，进行匹配和显示selectvCountryNameas"国家名称",DECODE(vCountryName,'中国','086','日本','116')as"国家编号"fromcountrys;
国家名称国家编号-----------------------------------------------------中国086日本116韩国
结果，在DECODE中存在且成功匹配的值将会被显示，否则显示为NULL
SQL语句书可以提高执行效率的方法
1、操作符号：NOTIN操作符此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替"ISNULL","<>","!=","!>","!<","NOT","NOTEXISTS","NOTIN","NOTLIKE","LIKE'%500'"，因为他们不走索引全是表扫描。NOTIN会多次扫描表，使用EXISTS、NOTEXISTS、IN、LEFTOUTERJOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。
2、注意union和unionall的区别。union比unionall多做了一步distinct操作。能用unionall的情况下尽量不用union。如:两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
selectmax(id)asmax_idfrom(selectidfrom表Aunionallselectidfrom表B)t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
7、什么是反射、序列化、反序列化？事务有几种级别？反射是在程序运行时动态访问DDL的一种方式。序列化是将对象对二进制、XML等方式直接向文件的存储。反序列化是将存储到文件的对象取出的过程。事务的级别的三种：页面级、应用程序级、数据库级。8、数据测试如何测试？在PLSQL里对过程或函数可能通过专用的测试工具，通过对9、用事务的时候，如果在业务逻辑层中，调用数据库访问层中的方法，访问层中有很多类，类又有很多方法，每个方法都要实现，那么如何处理？通用数据访问层的实现10、什么时候会用到触发器A安全管理、B日志管理、C复杂业务逻辑实现11、如何在数据库中显示树控制？用父ID与子ID来实现12、如何实现数据库的优化？A、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。
B、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。
C、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（OracleOptimizer）和行锁管理器（row-levelmanager）来调整优化SQL语句。
D、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。
E、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。
F、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。
3、BLOB和CLOB的区别在于
ACLOB只能存放字符类型的数据，而BLOB没有任何限制
BBLOB只能存放字符类型的数据，而CLOB没有任何限制
CCLOB只能存放小于4000字节的数据，而BLOB可以存放大于4000字节的数据
DBLOB只能存放小于4000字节的数据，而CLOB可以存放大于4000字节的数据
4、BFILE属于（）数据类型
A简单
B标量
C大对象
D文件
三号题选A，blob和clob都是大对象数据类型，4000字节数据限制是在已被建议不再使用的long/long-raw中的限制，lob类型没有4000字节限制。clob指charactorlob，blob指binarylob，因此clob只能存放字符型数据，而blob没有限制。
四号题应该选D,bfile代表文件，但是不想lob类型内容是存放在数据库表内部的，而是存放在数据库所在主机的文件系统中，因此bfile不是大对象。
完成下列操作，写出相应的SQL语句
创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)
属性
类型(长度)
默认值
约束
含义
CLASSNO
数值(2)
无
主键
班级编号
CNAME
变长字符(10)
非空
班级名称
STUNO
数值(8)
学号
SNAME
变长字符(12)
姓名
SEX
字符(2)
男
性别
BIRTHDAY
日期
生日
变长字符(20)
唯一
电子邮件
SCORE
数值(5,2)
检查
成绩
外键，关联到表CLASS的CLASSNO主键
答：createtableclass(classnonumber(2)constraintclass_classno_pkprimarykey,cnamevarchar2(10)notnull);createtablestudent(stunonumber(8)constraintstudent_stuno_pkprimarykey,snamevarchar2(12)notnull,sexchar(2)default‘男’,birthdaydate,emailvarchar2(20)constraintstudent_email_ukunique,scorenumber(5,2)constraintstudent_score_ckcheck(score>=0andscore<=100),classnonumber(2)constraintstudent_classno_fkreferencesclass(classno));5.在表student的SNAME属性上创建索引student_sname_idx（5分）答：createindexstudent_sname_idxonstudent(sname);6.创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。（6分）答：createsequencestuseqincrementby1startwith20050001maxvalue20059999nocachenocycle;7.向表student中插入如下2行。（5分）
从stuseq取值
tom
1979-2-314:30:25
tom@163.net
89.50
1
jerry
空
面试sql题
中软面试的一个sql题。题目:1、每个科目的最高分。2、java成绩最高的姓名3、java成绩第二高的姓名第一个没什么好说的。对第二题和第三题，取得成绩可能有多的。可以使用分析函数。DENSE_RANKSELECTt.name,t.kemu,t.score,DENSE_RANK()OVER(PARTITIONBYt.kemuORDERBYt.score)seqFROMke_chengjit;一道SQL面试题
有两个表,table1,table2,Tabletable1:SELLER|NON_SELLER----------ABACADBABCBDCACBCDDADBDCTabletable2:SELLER|COUPON|BAL-----------------------A9100B9200C9300D9400A9.5100B9.520A1080要求用SELECT语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......且用的方法不要增加数据库负担,如用临时表等.NON-SELLER|COUPON|SUM(BAL)---------------A9900B9800C9700D9600A9.520B9.5100C9.5120D9.5120A100B1080C1080D1080下面是我的方法，不知道哪位高手有更好的方法请出招。
selectdistinct(a.seller),b.coupon,
nvl((selectsum(bal)fromtable2wheresellerin
(selectnon_sellerfromtable1whereseller=a.seller)
andcoupon=b.coupon),0)assumbal
fromtable1a
leftjointable2bon1=1orderbyb.coupon;
题目:
表B
C1C22005-01-0112005-01-0132005-01-025
要求的输出数据
C1C22005-01-0142005-01-025合计9试用一个Sql语句完成。
该题目主要考的是分析函数函数over(partitionby)的使用
--创建表B
createtableb
(c1varchar2(14),c2number);
insertintob
values('2005-01-01',1);
values('2005-01-01',3);
values('2005-01-02',5);
commit;
selectc1,sum(c2)over(partitionbyc1)
fromb
union
select'合计:',sum(c2)over(partitionbynull)
fromb;
C1SUM(C2)OVER(PARTITIONBYC1)
----------------------------------------
2005-01-014
2005-01-025
合计:9
一.SQL问答题SELECT*FROMTABLE和SELECT*FROMTABLEWHERENAMELIKE'%%'ANDADDRLIKE'%%'AND(1_ADDRLIKE'%%'OR2_ADDRLIKE'%%'OR3_ADDRLIKE'%%'OR4_ADDRLIKE'%%')的检索结果为何不同(1).like通配符一个个比较肯定影响效率，(2).数据库中存在null的时候，如果字段中有null存在select*fromtable可以显示所有的内容，但是like不会通配null，所以字段为null它显示不出来！！二.selectcount(*)fromtable和selectcount(1)fromtalbe的区别？？只明白count(字段)时它是不检索null的！但是在count中1和*现在还没有明确答案，求高手！！！~