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

《部分常见ORACLE面试题以及SQL注意事项》由会员分享，可在线阅读，更多相关《部分常见ORACLE面试题以及SQL注意事项（47页珍藏版）》请在装配图网上搜索。

1、一、表的创建：一个通过单列外键联系起父表和子表的简单例子如下：CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE)建表时注意不要用关键字当表名或字段名，如insert,use等。CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (i

2、d) TYPE=INNODB;InnoDB Tables概述 InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant)型表。 InnoDB提供了行锁(locking on row level)，提供与 Oracle类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB的列

3、锁定(row level locks)适宜非常小的空间。 InnoDB是 MySQL上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。 InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。从一个表中查询出数据插入到另一个表中的方法：select * into destTbl from srcTbl ;insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl ;以上两句都是将srcTbl的数据插入到destTbl，但两句又有区别的。第一句（select i

4、nto from）要求目标表（destTbl）不存在，因为在插入时会自动创建。第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。如果只想要结构而不要数据。 create table s_emp_42 as select * from s_emp where 1=2;/永假式SQL查询练习题1.表1：book表，字段有id(主键)，name (书名)；表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间

5、），state（1.借出2.归还）。id name1 English2 Math3 JAVA id bookId dependDate state1 1 2009-01-02 12 1 2009-01-12 23 2 2009-01-14 14 1 2009-01-17 15 2 2009-02-14 26 2 2009-02-15 17 3 2009-02-18 18 3 2009-02-19 2要求查询结果应为：(被借出的书和被借出的日期)Id Name dependDate1 English 2009-01-172 Math 2009-02-15Select e.bookId,b.nam

6、e,e.dependDate from book b,bookEnrol e where第二个表是用来登记的，不管你是借还是还，都要添加一条记录。请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。参考语句：select book.id,book.name,max(dependDate)from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1group by book.id ;2第(1)题练习使用group by /having子句。类似的笔试题还有：表一：各种产品年销售量统计表 sa

7、le年产品 销量 2005 a 700 2005 b 550 2005 c 600 2006 a 340 2006 b 500 2007 a 220 2007 b 350要求得到的结果应为：年产品 销量 2005 a 700 2006 b 500 2007 b 350即：每年销量最多的产品的相关信息。参考答案： Select * from sale a where not exists(select * from sale where 年=a.年and 销量a.销量); -or: select * from sale a inner join (select年,max(销量) as销量from

8、 sale group by年) b on a.年=b.年and a.销量=b.销量3.查询语句排名问题：名次姓名 月积分(char)总积分（char）1 WhatIsJava 1 992 水王76 9813 新浪网65 964 牛人22 95 中国队64 896 北林信息 66 667 加太阳53 668 中成药11 339 西洋参 25 2610 大拿33 23如果用总积分做降序排序.因为总积分是字符型，所以排出来是这样子(9,8,7,6,5.)，要求按照总积分的数字大小排序。select * from tablename order by cast(总积分as int) desc表tb

9、uid mark 1 7 1 6 2 3 2 2 2 5 3 4 3 3 4 8 4 1 4 3 想查出uid=4的名次： uid mc 4 3select uid, sum(mark) as total from tab_name group by uid order by total desc;4表A字段如下month name income月份人员收入1 a 10002 a 20003 a 3000要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入要求列表输出为月份当月收入上月收入下月收入 2 2000 1000 3000Select (Select M

10、onth From Table Where Month = To_Char(Sysdate, mm)月份,(Select Sum(Income) From Table Where Month = To_Char(Sysdate, mm)当月收入,(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate) - 1)上月收入,(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(

11、Month From Sysdate) + 1)下月收入From Dual5.删除重复记录方法原理：1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。实现方法：SQL create table a (2 bm char(4), -编码3 mc varchar2(20) -名称4 )5 /SQL select rowid,bm,mc from a;ROWID

12、 BM MC- - -000000D5.0000.0002 1111 1111000000D5.0001.0002 1112 1111000000D5.0002.0002 1113 1111000000D5.0003.0002 1114 1111000000D5.0004.0002 1111 1111000000D5.0005.0002 1112 1111000000D5.0006.0002 1113 1111000000D5.0007.0002 1114 1111查询到8记录.查出重复记录SQL select rowid,bm,mc from a where a.rowid!=(select

13、 max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);ROWID BM MC- - -000000D5.0000.0002 1111 1111000000D5.0001.0002 1112 1111000000D5.0002.0002 1113 1111000000D5.0003.0002 1114 1111删除重复记录SQL delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);删除4个记录.SQL select ro

14、wid,bm,mc from a;ROWID BM MC- - -000000D5.0004.0002 1111 1111000000D5.0005.0002 1112 1111000000D5.0006.0002 1113 1111000000D5.0007.0002 1114 1111其他组合函数Group by子句Distinct关键字伪列ROWNUM，用于为子查询返回的每个行分配序列值注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where后只能跟单行函数，不能有组函数。使用TOP-N分析法TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录TOP-N查询包含以

15、下内容：1，一个用于排序数据的内联视图2，使用ORDER BY子句或DESC参数的子查询3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句/语法：SELECT ROWNUM,column_listFROM (SELECT column_list FROM table_name ORDER BY Top-n-column_name)WHERE ROWNUM = N例1：查询Employee表的顶部10条记录/方法1：单表时可以用select cEmployeeCode,vFirstName,vLastName from employee wher

16、e rownum = 10/方法2：较复杂的查询，建议使用这种select * from (select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)where num select concat(010-,88888888)|转23高乾竞电话 from dual;高乾竞电话-010-88888888转232.LTRIM和RTRIMLTRIM删除左边出现的字符串RTRIM删除右边出现的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual;LTRIM(R

17、TRIM(-gao qian jing3.SUBSTR(string,start,count)取子字符串,从start开始,取count个SUBSTR(-088888884日期函数如: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返回字符串,并将所有的

18、字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH返回字符串的长度。ORALCE常识及SQL基本语法1，ORACLE安装完成后的初始口令？internal/oraclesys/change_on_installsystem/managerscott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字sysman/oem_temp例：connscott/tigerjspdev; connsystem/managerjspdevas sysdba;2，IBM的Codd（Edgar Frank Codd）博士提出大型共享数据库数据的关系模型3,ORA

19、CLE 9i中的i（internet）是因特网的意思4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件5,ORACLE的数据库的逻辑结构：表空间表段区间块表空间类似于SQLSERVER中数据库的概念6,SYSDATE返回当前系统日期（说明：当函数没有参数时可以省略括号）7,在SQL PLUS中执行缓冲区中的SQL命令的方式：SQL runSQL rSQL /8,在SQL PLUS中修改当前会话的日期显示格式SQL alter session set nls_date_format = YYYY-MM-DD9,使用临时变量，提高输入效率SQL insert into emp(emp

20、no,ename,sal) values(&employeeno,&employeename,&employeesal);10,从其他表中复制数据并写入表SQL insert into managers(id,name,salary,hiredate)SQL select empno,ename,sal,hiredateSQL from empSQL where job = MANAGER;11,修改表中的记录SQL update table set column = value ,column = value, where condition;12,删除表中的记录SQL delete fro

21、m table where condition;13,数据库事务，事务是数据库一组逻辑操作的集合一个事务可能是：多个DML语句单个DDL语句单个DCL语句14,事务控制使用savepoint,rollback,commit关键字SQL savepoint aaa;SQL rollback to aaa;SQL commit;15,查询表中的数据select * from table_name;select column_list from table_name;16,Number and Date可以用于算术运算因为Date类型其实存储为Number类型17,用运算表达式产生新列SQL sel

22、ect ename,sal,sal+3000 from emp;SQL select ename,sal,12*sal+100 from emp;18,算术表达式中NULL值错误的处理因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值例如：SQL select ename 姓名,12*sal+comm 年薪 from emp where ename = KING;姓名薪水- -KING因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL注意函数nvl的使用NVL(原值,新值)SQL select ename 姓名,12*sal+NV

23、L(comm,0) 年薪 from emp where ename = KING;员工姓名员工薪水- -KING 6000019,使用友好的列名,有下面三种形式SQL select ename as姓名, sal月薪, sal*12 年薪 from emp20,过滤重复行，使用关键字distinctSQL select distinct * from emp;21,SQL PLUS访问ORACLE数据库的原理SQL*Plus Buffer Server Query Result22,where子句中字符型是区分大小写的，最好都转成大写因为在ORACLE库中，字符会转换成大写来保存23,比较运算

24、符：等于=,不等于有两种或者!=24,复杂的比较运算符：between and in (value list)like（%代表匹配至多个任意字符，_代表单个任意字符）null（与NULL进行比较时，需要使用is null或者is not null）25,逻辑运算符，按优先级从高到低排列Not , And , Or26,Order by子句中（asc表示升序，desc表示降序）27,ORACLE函数，分为单行函数：每条记录返回一个结果值多行函数：多条记录返回一个结果值28,字符函数转换函数LOWER：转为小写UPPER：转为大写INITCAP：将每个单词的首字母大写，其他字母小写29,字符函数操

25、纵函数（注意：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 SELECT ROUND(45.923,2),ROUN

26、D(45.923,0),ROUND(45.923,-1) FROM DUAL;ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)- - -45.92 46 5031,数值截取函数trunctSQL SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM DUAL;TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)- - -45.92 45 4032,求模函数MOD(a,b)返回a被b整除后的余数33,Oracle内部默认的日期格式: DD

27、-MON-YY（24-9月-06）34,DUAL：哑元系统表，是名义表，只能范围唯一值35,Date类型的算术运算，以天为单位例如：部门编号为10的员工分别工作了多少年SQL select ename,(sysdate-hiredate)/365 as years from emp where deptno = 10;ENAME YEARS- -CLARK 25.3108341KING 24.8697382MILLER 24.686176636,日期函数MONTHS_BETWEEN返回两个日期之间相差多少个月ADD_MONTHS在日期上加上月份数NEXT_DAY下一个日子select next

28、_day(sysdate,星期一) from dual;LAST_DAY该月的最后一天ROUND四舍五入日期round(sysdate,year)或者round(sysdate,month)TRUNC截取日期trunc(sysdate,year)或者trunc(sysdate,month)37,数据类型转换 Oracle可隐式转换的情况有：From Tovarchar2 or char number（当字符串是数字字符时）varchar2 or char datenumber varchar2date varchar238,数据类型转换 Oracle数据类型转换函数to_charto_numb

29、erto_date39,日期格式模型字符YYYY代表完整的年份YEAR年份MM两位数的月份MONTH月份的完整名称DY每星期中天的三个字符缩写DAY表示星期日星期六另外还有D,DD,DDD等。40,NVL(value,substitute)value:是可能有null的列，substitute是缺省值这个函数的作用就是当出现null值的时候，后缺省值替换null41,Coalesce(exp_name1,exp_name2exp_n)42,Decode函数：Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)例如，根据国家名称显

30、示相应的国家代码:1创建国家表create table countrys(vCountryName varchar2(50);2写入几行，分别为中国、日本、韩国insert into countrys values (&name);3用DECODE函数，进行匹配和显示select vCountryName as 国家名称,DECODE(vCountryName,中国,086,日本,116) as 国家编号 from countrys;国家名称国家编号- -中国086日本116韩国结果，在DECODE中存在且成功匹配的值将会被显示，否则显示为NULLSQL语句书可以提高执行效率的方法1、操作符号

31、：NOT IN操作符此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS或（外连接+判断为空）方案代替IS NULL, , !=, !, !假设返回110秒中后,再次做Select PEAK_NO.NEXTVAL FROM DUAL -返回多少?(3) SQL connect sys as sysdbaConnected.SQL insert into dual values ( Y);1 row created.SQL commit;Commit complete.SQL select count(*) from dual;COUNT(*)-2SQL delet

32、e from dual;commit;-DUAL里还剩几条记录?JUST TRY IT一些高难度的SQL面试题以下的null代表真的null，写在这里只是为了让大家看清楚根据如下表的查询结果，那么以下语句的结果是（知识点：not in/not exists+null）SQL select * from usertable;USERID USERNAME- 1user1 2null 3user3 4null 5user5 6user6SQL select * from usergrade;USERID USERNAME GRADE- - - 1user190 2null 80 7user780

33、8user890执行语句：select count(*) from usergrade where username not in (select username from usertable);select count(*) from usergrade g where not exists(select null from usertable t where t.userid=g.userid and t.username=g.username);结果为：语句1(0 )语句2(3 )A: 0 B:1 C:2 D:3E:NULL2在以下的表的显示结果中，以下语句的执行结果是（知识点：in/

34、exists+rownum）SQL select * from usertable;USERID USERNAME- 1user1 2user2 3user3 4user4 5user5SQL select * from usergrade;USERNAME GRADE- -user990user880user780user290user1100user180执行语句Select count(*) from usertable t1 where username in(select username from usergrade t2 where rownum =1);Select count

35、(*) from usertable t1 where exists(select x from usergrade t2 where t1.username=t2.username and rownum =1);以上语句的执行结果是：()()A: 0B: 1C: 2 D:3根据以下的在不同会话与时间点的操作，判断结果是多少，其中时间T1原始表记录为；select * from emp;EMPNO DEPTNO SALARY- - -1001 551011 50select * from dept;DEPTNO SUM_OF_SALARY- -11052可以看到，现在因为还没有部门2的员工，所

36、以总薪水为null，现在，有两个不同的用户（会话）在不同的时间点（按照特定的时间顺序）执行了一系列的操作，那么在其中或最后的结果为：timesession 1session2- -T1 insert into emp values(102,2,60)T2 update emp set deptno =2where empno=100T3update dept set sum_of_salary = (select sum(salary) from emp where emp.deptno=dept.deptno) where dept.deptno in(1,2);T4 update dept

37、 set sum_of_salary =(select sum(salary) from empwhere emp.deptno=dept.deptno)where dept.deptno in(1,2);T5commit;T6select sum(salary) from emp group by deptno;问题一：这里会话2的查询结果为：T7commit;=到这里为此，所有事务都已完成，所以以下查询与会话已没有关系=T8select sum(salary) from emp group by deptno;问题二：这里查询结果为T9 select * from dept;问题三：这里查询的结果为问题一的结果()问题二的结果是()问题三的结果是()A:B:- -150 150260 255C:D:- -150 1 1152 115 250E:F:-