oracle面试

部分常见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 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表中不需要扩大锁定(lock escalation)，
因为 InnoDB 的列锁定(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 into 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（变更时间），state（1.借出 2.归还）。
id name
1 English
2 Math
3 JAVA

id bookId dependDate state
1 1 2009-01-02 1
2 1 2009-01-12 2
3 2 2009-01-14 1
4 1 2009-01-17 1
5 2 2009-02-14 2
6 2 2009-02-15 1
7 3 2009-02-18 1
8 3 2009-02-19 2

要求查询结果应为：(被借出的书和被借出的日期)

Id Name dependDate
1 English 2009-01-17
2 Math 2009-02-15

Select e.bookId,b.name,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=1
group by book.id ;

2
第(1)题练习使用group by /having 子句。类似的笔试题还有：
表一：各种产品年销售量统计表 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 * from sale where 年=a.年 and 销量>a.销量);
--or:
select * from sale a inner join (select 年,max(销量) as 销量from sale group by 年) b
on a.年=b.年 and a.销量=b.销量
3.查询语句排名问题：
名次 姓名 月积分(char) 总积分（char）
1 WhatIsJava 1 99
2 水王 76 981
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

表tb
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 3

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

Select (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 Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) + 1) 下月收入
From Dual

5.删除重复记录

方法原理：
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 BM MC
------------------ ---- -------
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!=(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个记录.

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分析法基于条件显示表中最上面N条记录或最下面N条记录
TOP-N查询包含以下内容：
1，一个用于排序数据的内联视图
2，使用ORDER BY子句或DESC参数的子查询
3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句
//语法：
SELECT ROWNUM,column_list
FROM (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 where rownum <= 10
//方法2：较复杂的查询，建议使用这种
select * from (select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)
where num <= 10
例2: 查询Employee表的 第1 到 第10条 记录，可以用于分页显示
//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownum
select * from (select rownum as num,Employee.* from Employee) where num between 10 and 20
select * from (select rownum as num,Employee.* from Employee) where num between 1 and 10

SQL注入 1=1永远成立,相当于查询所有记录
select * from person_zdk where 1=1 or name like '%a%' and age=13;

 

DECODE函数
是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

该函数的含义如下:

IF 条件=值1 THEN

RETURN(翻译值1)

ELSIF 条件=值2 THEN

RETURN(翻译值2)

......

ELSIF 条件=值n THEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

END IF

假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee.

 

SQL中的单记录函数
1.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;

高乾竞电话
----------------
010-88888888转23
2.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

LTRIM(RTRIM('
-------------
gao qian jing
3..SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;

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/oracle
sys/change_on_install
system/manager
scott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字
sysman/oem_temp
例：conn scott/tiger@jspdev;
conn system/manager@jspdev as sysdba;
2，IBM的Codd （Edgar Frank Codd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE 9i 中的 i （internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间--表--段--区间--块
表空间 类似于SQLSERVER中数据库的概念
6,SYSDATE 返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQL PLUS中 执行缓冲区中的SQL命令的方式：
SQL> run
SQL> r
SQL> /
8,在SQL PLUS中 修改当前会话的日期显示格式
SQL> alter session set nls_date_format = 'YYYY-MM-DD'
9,使用临时变量，提高输入效率
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,hiredate
SQL> from emp
SQL> where job = 'MANAGER';
11,修改表中的记录
SQL> update table set column = value [,column = value,......] [where 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 可以用于算术运算
因为 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
注意函数nvl的使用 NVL(原值,新值)
SQL> select ename "姓名",12*sal+NVL(comm,0) "年薪" from emp where ename = 'KING';
员工姓名 员工薪水
---------- ----------
KING 60000
--------------
19,使用友好的列名,有下面三种形式
SQL> select ename as 姓名, sal 月薪, sal*12 "年薪" from emp
20,过滤重复行，使用关键字 distinct
SQL> select distinct * from emp;

21,SQL PLUS访问ORACLE数据库的原理
SQL*Plus -> Buffer -> Server -> Query Result
22,where 子句中 字符型 是区分大小写的，最好都转成大写
因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：
between ...... and ......
in (......value list......)
like （% 代表匹配至多个任意字符，_ 代表单个任意字符）
null （与NULL进行比较时，需要使用 is null 或者 is not null）
25,逻辑运算符，按优先级从高到低排列
Not , And , Or
26,Order by 子句 中 （ 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> 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 50

31,数值截取函数 trunct
SQL> 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 40
32,求模函数 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.3108341
KING 24.8697382
MILLER 24.6861766
36,日期函数
MONTHS_BETWEEN 返回两个日期之间相差多少个月
ADD_MONTHS 在日期上加上月份数
NEXT_DAY 下一个日子 select next_day(sysdate,'星期一') from dual;
LAST_DAY 该月的最后一天
ROUND 四舍五入日期 round(sysdate,'year') 或者 round(sysdate,'month')
TRUNC 截取日期 trunc(sysdate,'year') 或者 trunc(sysdate,'month')
37,数据类型转换 -- Oracle 可隐式转换的情况有：
From To
varchar2 or char -- number （当字符串是数字字符时）
varchar2 or char -- date
number -- varchar2
date -- varchar2
38,数据类型转换 -- Oracle 数据类型转换函数
to_char
to_number
to_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>创建国家表
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中存在且成功匹配的值将会被显示，否则显示为NULL

SQL语句书可以提高执行效率的方法
1、操作符号： NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替 "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", "LIKE '%500'"，因为他们不走索引全是表扫描。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。

2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
如: 两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
select max(id) as max_id
from(
select id from 表A
union all
select id from 表B ) t

3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。

4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。

6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。

7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。由于in会在比较多次，所以有时会慢些。

8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

9、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，如果dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的 比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。所以尽量 将范围小的条件放在前面。。

10、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用 UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。

11、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。

12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数

13、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中 SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。

14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

15、一次更新多条记录比分多次更新每次一条快,就是说批处理好

16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。

17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。

18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。

19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。