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

1、部分常见ORACLE面试题以及SQL注意事项一、表的创建：一个通过单列外键联系起父表和子表的简单例子如下：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

2、 NULL,PRIMARY KEY (id) 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表中不需要扩大锁定(loc

3、k escalation)， 因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。 InnoDB 是 MySQL 上第一个提供外键约束(FOREIGNKEYconstraints)的表引擎。InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。 三、从一个表中查询出数据插入到另一个表中的方法：select * into destTbl from srcTbl ;（在oracle中不适用）insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl insert

4、into aa select * from dept 第三句同第二句。create table s_emp_42 as select * from s_emp where 1=2;/ 永假式 只想要结构而不要数据。以上三句都是将 srcTbl 的数据到 destTbl，但两句又有区别的。 第一句（select into from）要求目标表（destTbl）不存在，因为在插入时会自动创建。 第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5，或者插入字符 字符四

5、、SQL查询练习题(1) 表1：book表，字段有id(主键)，name (书名)；表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出 2.归还）。 id name 1 English2 Math3 JAVAiddependDate state bookId12009-01-02 1 122009-01-12 2 13 2009-01-14 1 242009-01-17 1 152009-02-14 2 262009-02-15 1 272009-02-18 1 382009-02-19 2 3要求查询结果应

6、为：(被借出的书和被借出的日期)Id Name dependDate1 English 2009-01-172 Math 2009-02-15第二个表是用来登记的，不管你是借还是还，都要添加一条记录。请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。参考语句：select book.id,,max(dependDate)from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1 group by book.id ;（这个答案是错误的）正确的语句一：select boo

7、k.id,,dependdate from book,bookEnrol where dependdate in（select max(dependdate) from bookEnrol group by bookid） and book.id=bookEnrol.bookid and bookEnrol.state=1;正确的语句二：select book.id,,dependdate from book inner join bookEnrol on book.id=bookEnrol.bookid where dependdate in(select

8、 max(dependdate) from bookEnrol group by bookid) and bookEnrol.state=1;（2）表一：各种产品年销售量统计表 sale 年 产品 销量 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

9、* from sale where 年=a.年 and 销量a.销量);参考答案二： select * from sale a inner join (select 年,max(销量) as 销量from sale group by 年) b on a.年=b.年 and a.销量=b.销量 参考答案三（我的答案）：select * from sale where 销量 in (select max(销量) from sale group by 年);（3）.查询语句排名问题：表一：名次 姓名 月积分(char) 总积分（char） 1 WhatIsJava 1 99 2 水王 76 981

10、3 新浪网 65 96 4 牛人 22 9 5 中国队 64 89 6 北林信息 66 66 7 加太阳 53 66 8 中成药 11 33 9 西洋参 25 26 10 大拿 33 23 如果用总积分做降序排序.因为总积分是字符型，所以排出来是这样子(9,8,7,6,5.)，要求按照总积分的数字大小排序。参考答案：select * from tablename order by cast(总积分 as int) desc 参考答案（我的答案）：select * from tablename order by to_number(总积分) desc；表tb uid mark 1 7 1 6 2

11、 3 2 2 2 5 3 4 3 3 4 8 4 1 4 3 想查出uid=4的名次： uid he mc 412 2 我的答案：select * from (select rownum mc,ui,he from (select ui,sum(mark) he from tb group by ui order by he desc) where ui=4;表A字段如下 month name income 月份 人员 收入 1 a 10002 a 20003 a 3000要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入 要求列表输出为 月份 当月收入 上月

12、收入 下月收入 2 2000 1000 3000Select (Select Month 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

13、Where To_Number(Month) = To_Number(Extract(Month From Sysdate) + 1) 下月收入From Dual算排名列出每一行的排名是一个常见的需求，可惜 SQL 并没有一个很直接的方式达到这个需求。要以 SQL 列出排名，基本的概念是要做一个表格自我连结 (self join)，将结果依序列出，然后算出每一行之前 (包含那一行本身) 有多少行数。这样讲读者听得可能有点困惑，所以最好的方式是用一个实例来介绍。假设我们有以下的表格： NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20Tot

14、al_Sales 表格要找出每一行的排名，我们就打入以下的 SQL 语句：SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;结果:Name Sales Sales_Rank Greg 50 1 Soph

15、ia 40 2 Stella 20 3 Jeff 20 3 Jennifer 15 5 John 10 6 我们先来看 WHERE 子句。在字句的第一部分 (a1.Sales = a2.Sales)，我们算出有多少笔资料 Sales 栏位的值是比自己本身的值小或是相等。如果在 Sales 栏位中没有同样大小的资料，那这部分的 WHERE 子句本身就可以产生出正确的排名。 子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，则是让我们在 Sales 栏位中有同样大小的资料时 (像 Stella 及 Jeff 这两笔资料)，仍然能够产生正确的排名。

16、算中位数要算出中位数，我们必须要能够达成以下几个目标： 将资料依序排出，并找出每一行资料的排名。 找出中间的排名为何。举例来说，如果总共有 9 笔资料，那中间排名就是 5 (有 4 笔资料比第 5 笔资料大，有 4 笔资料比第 5 笔资料小)。 找出中间排名资料的值。 来看看以下的例子。假设我们有以下的表格： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20要找出中位数，我们就键入：SELECT Sales Median FROM (SELECT a1.Name, a1.Sales, COUNT(a1.Sal

17、es) Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) group by a1.Name, a1.Sales order by a1.Sales desc) a3 WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales); 结果:Median 20 读者将会发现，第 2 行到第 6 行是跟产生 排名 的语句完全一样。第 7 行则是算出中间的排名。DIV 是在 MySQ

18、L 中算出商的方式。在不同的数据库中会有不同的方式求商。第 1 行则是列出排名中间的资料值。 算累积总计算出累积总计是一个常见的需求，可惜以 SQL 并没有一个很直接的方式达到这个需求。要以 SQL 算出累积总计，基本上的概念与列出排名类似：第一是先做个表格自我连结 (self join)，然后将结果依序列出。在做列出排名时，我们算出每一行之前 (包含那一行本身) 有多少行数；而在做累积总计时，我们则是算出每一行之前 (包含那一行本身) 的总合。 来看看以下的例子。假设我们有以下的表格： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia

19、40Greg50Jeff20要算出累积总计，我们就键入：SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;结果:Name Sales Running_Total Greg 50 50 Sophia 4

20、0 90 Stella 20 110 Jeff 20 130 Jennifer 15 145 John 10 155 在以上的 SQL 语句中， WHERE 子句和 ORDER BY 子句让我们能够在有重复值时能够算出正确的累积总计。 算总合百分比要用 SQL 算出总合百分比，我们需要用到算排名和累积总计的概念，以及运用子查询的做法。在这里，我们把子查询放在外部查询的 SELECT 子句中。让我们来看以下的例子： Total_Sales 表格NameSalesJohn10Jennifer15Stella20Sophia40Greg50Jeff20要算出总合百分比，我们键入：SELECT a1.

21、Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales = a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) GROUP BY a1.Name, a1.Sales ORDER BY a1.Sales DESC, a1.Name DESC;结果:Name Sales Pct_To_Total Greg 50 0.3226 Sophia 40 0.

22、2581 Stella 20 0.1290 Jeff 20 0.1290 Jennifer 15 0.0968 John 10 0.0645 SELECT SUM(Sales) FROM Total_Sales 这一段子查询是用来算出总合。总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比。 算累积总合百分比要用 SQL 累积总合百分比算出，我们运用类似总合百分比的概念。两者的不同处在于在这个情况下，我们要算出到目前为止的累积总合是所有总合的百分之几，而不是光看每一笔资料是所有总合的百分之几。让我们来看看以下的例子： Total_Sales 表格NameSalesJohn10J

23、ennifer15Stella20Sophia40Greg50Jeff20要算出累积总合百分比，我们键入：SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales create table a ( 2 bm char(4), -编码 3 mc varchar2(20) -名称 4 ) 5 / SQL select rowid,bm,mc from a; ROWID BM M

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

25、(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); ROWID BM MC - - - 000000D5.0000.0002 1111 1111 000000D5.0001.0002 1112 1111 000000D5.0002.0002 1113 1111 000000D5.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个

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

27、表中最上面N条记录或最下面N条记录TOP-N查询包含以下内容：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,v

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

29、 jing , ), ) from dual;LTRIM(RTRIM(-gao qian jing3.SUBSTR(string,start,count)取子字符串,从start开始,取count个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 要替

30、换的字符串；LOWER 返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH返回字符串的长度。ORALCE常识 及 SQL 基本语法1，ORACLE安装完成后的初始口令？internal/oraclesys/change_on_installsystem/managerscott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字sysman/oem_temp例：conn scott/tigerjspdev; conn system/managerjspdev as sysdba;2，IBM的Codd （Edgar Fran

31、k Codd）博士提出大型共享数据库数据的关系模型3,ORACLE 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

32、,使用临时变量，提高输入效率SQL insert into emp(empno,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, whe

33、re condition;12,删除表中的记录SQL delete from 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 可以用于算术运

34、算因为 Date 类型 其实存储为 Number 类型17,用运算表达式产生新列SQL select 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注

35、意函数nvl的使用 NVL(原值,新值)SQL select ename 姓名,12*sal+NVL(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,whe

36、re 子句中 字符型 是区分大小写的，最好都转成大写因为在ORACLE库中，字符会转换成大写来保存23,比较运算符：等于=,不等于有两种或者!=24,复杂的比较运算符：between and in (value list)like （% 代表匹配至多个任意字符，_ 代表单个任意字符）null （与NULL进行比较时，需要使用 is null 或者 is not null）25,逻辑运算符，按优先级从高到低排列Not , And , Or26,Order by 子句 中 （ asc 表示 升序，desc 表示降序）27,ORACLE 函数，分为单行函数：每条记录返回一个结果值多行函数：多条记录返

37、回一个结果值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不够numb

38、er位，从左起用vlaue字符串填充(不支持中文)30,四舍五入函数 round(数值,小数位)SQL SELECT ROUND(45.923,2),ROUND(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)

39、TRUNC(45.923,-1)- - -45.92 45 4032,求模函数 MOD(a,b) 返回a被b整除后的余数33,Oracle内部默认的日期格式: DD-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,日期函