Oracle面试题整理
目录
2.Oracle中function和procedure的区别？
7. 描述oracle中tablespace和datafile之间的关系
30.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
31.语法：case … when … then … when … then … else … end
33.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
Oracle面试题整理
1.MySQL和Oracle的区别：
- MySQL是开源的项目，免费。中小企业首选MySQL，Oracle是Oracle公司推出的一款成熟的数据库产品是闭源同时也是收费的，但是Oracle官网是不限制下载的（只要你有账号）一般的开发测试是没问题。
- SQL语法的不同，MySQL较为灵活，Oracle较为严格（具体就是单引号，分页的处理，主键的自动增长）
- 对于事务的支持。MySQL对于事务默认是不支持的，只有存储引擎innodb支持。而Oracle对于事务是完全支持。
- 并发性。MySql默认表级锁，Oracle行级锁，所以oracle并发行高很多。
2.Oracle中function和procedure的区别？
1). 函数function是存储过程的一种
2). 函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值
3). 函数return返回值没有返回参数模式，存储过程通过out参数返回值, 如果需要返 回多个参数则建议使用存储过程
4). 在sql数据操纵语句中只能调用函数而不能调用存储过程
3. 比较truncate和delete命令 ？
1). Truncate 和delete都可以将数据实体删掉，truncate 的操作并不记录到 rollback日志，所以操作速度较快，但同时这个数据不能恢复
2). Delete操作不腾出表空间的空间
3). Truncate 不能对视图等进行删除
4). Truncate是数据定义语言（DDL），而delete是数据操纵语言(DML)
4.oralce中 rowid, rownum的定义
1). rowid和rownum都是伪列
2). rowid是物理地址，用于定位oracle中具体数据的物理存储位置
3). rownum则是sql的输出结果排序
5. 事务的特性（ACID）是指什么
事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为 ACID(原子性、一致性、隔离性和持久性)属性。
1）原子性（Atomic）：事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败
2）一致性（Consistent）：事务结束后系统状态是一样的
3）隔离性（Isolated）：并发执行的事务彼此无法看到对方的中间状态
4）持久性（Durable）：事务完成后，即使发生灾难性的故障，通过日志和同步备份可以在故障发生后重建数据
6. 列举几种表连接方式
等值连接（内连接）、非等连接、自连接、外连接（左、右、全）
内连接又称等值连接，筛选完全匹配的行
左连接以左表为主，筛选完全匹配的行之外还会把左表中没有匹配的行也筛选，找不到匹配的数据用null填充
右连接以右表为主，筛选完全匹配的行之外还会把有表中没有匹配的行也帅选，找不到匹配的数据用null填充
7. 描述oracle中tablespace和datafile之间的关系
一个表空间可包含一个或多个数据文件。表空间利用增加或扩展数据文件扩大表空间，表空间的大小为组成该表空间的数据文件大小的和。一个datafile只能属于一个表空间;一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内, table中的数据,通过hash算法分布在tablespace中的各个datafile中,tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。
8. 什么是逻辑备份
1) 逻辑备份(exp/imp)用于实现数据库对象的恢复。但不是基于时间点可完全恢复的备份策略。只能作为联机备份和脱机备份的一种补充。
2) 完全逻辑备份
完全逻辑备份是将整个数据库导出到一个数据库的格式文件中，该文件可以在不同的数据库版本、操作系统和硬件平台之间进行移植。
3) 指定表的逻辑备份
通过备份工具，可以将指定的数据库表备份出来，这可以避免完全逻辑备份所带来的时间和财力上的浪费。
9. 什么是物理备份
物理备份是最主要的备份方式。用于保证数据库在最小的数据库丢失或没有数据丢失的情况下得到恢复.物理备份分为热备份和冷备份
1) 热备份：主要是指备份过程在数据库打开并且用户可以使用的情况下进行。需要执行热物理备份的情况有：由于数据库性质要求不间断工作，因而此时只能采用热物理备份。
2) 冷备份：冷物理备份提供了最简单和最直接的方法保护数据库因物理损坏丢失。
对一个已经存在大最数据量的数据库，在晚间数据库可以关闭，此时应用冷物理。
当对数据库服务器进行升级，（如更换硬盘），此时需要备份数据库信息需要采用冷备。
10. NVL与NVL2两个函数的使用方法和差别？
1）NVL (expr1, expr2)：expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
2） NVL2 (expr1, expr2, expr3) ：expr1不为NULL。返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话。expr3会转换为expr2的类型
11.union和union all的区别
1) UNION：由每一个查询 选择的 全部不反复的行组成。并集不包括反复值， 默认按第 1 个查询的第 1 列升序排列。
2) UNION ALL： 由每一个查询 选择的 全部的行。全然并集包括反复值。 不排序。
12. minus和intersect区别
1）MINUS: 在第一个查询中但不在后面查询中的行，不包括重复行。 按第1 个查询的第 1 列升序排列。
2）INTERSECT:取每一个查询结果的交集。 不包括重复行。 按第1 个查询的第 1 列升序排列。
13.什么是存储过程，优点是什么？
存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)。存储过程的优点：
- 能够将代码封装起来保存在数据库之中
- 让编程语言进行调用
- 存储过程是一个预编译的代码块，执行效率比较高
- 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
14.什么是三范式
第一范式：字段是最小的的单元不可再分
第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。
第三范式：满足第二范式，非主键外的所有字段必须互不依赖既不存在传递依赖
15.什么是视图？以及视图的使用场景有哪些？
- 视图是一种基于数据表的一种虚表
- 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
- 向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
- 视图向用户提供基表数据的另一种表现形式
- 视图没有存储真正的数据，真正的数据还是存储在基表中
- 程序员虽然操作的是视图，但最终视图还会转成操作基表
- 一个基表可以有0个或多个视图
16.索引是什么？有什么作用以及优缺点？
（1）是一种快速查询表中内容的机制，类似于新华字典的目录
（2）运用在表中某个些字段上，但存储时，独立于表之外
优缺点：
- 索引加快数据库的检索速度
- 索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
- 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
- 索引需要占物理和数据空间
17.数据库的乐观锁和悲观锁是什么？
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作.在查询数据的时候就把事务锁起来，直到提交事务.实现方式：使用数据库中的锁机制。
执行select xxx for update操作时，数据会被锁定，只有执行commit或rollback才会释放.执行select xxx for update nowait操作时，数据也会被锁定，其他人访问时或返回ORA-00054错误，内容是资源正忙，需要采取相应的业务措施进行处理。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定.实现方式：使用version版本或者时间戳.
1）：在数据取得的时候把整个数据都copy到应用中，在进行提交的时候比对当前数据库中的数据和开始的时候更新前取得的数据。当发现两个数据一模一样以后，就表示没有冲突可以提交，否则就是并发冲突，需要去用业务逻辑进行解决。
2)：新增一个Table的Column，这个column是采用timestamp型，存储数据最后更新的时间。这种Timestamp的数据精度在Oracle的时间类型中是最高的，精确到微秒，是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
18.数据库结构优化1)范式优化：
1)比如消除冗余（节省空间）
2)反范式优化：比如适当加冗余等（减少join）
3)拆分表： 垂直拆分和水平拆分
19.解释oralce中的游标
游标就是指向一个结果集的记录定位器（指示器），用于定位记录。游标的使用方式：声明游标，打开游标，提取游标数据，关闭游标
Declare
cursor cur is select * from category;
record category%rowtype;
begin
open cur;
loop
fetch cur into record;
if cur%notfound then
exit;
end if;
dbms_output.put_line(to_char(record.id) || ',' || record.name);
end loop;
close cur;
end;
20.描述oracle中的触发器
触发器是一种特殊的存储过程，存储过程由用户显式调用，而触发器是当用户对表
进行update,delete,insert是自动调用。触发器类型通常包括（插入，删除，修改）
CREATE OR REPLACE TRIGGER SAL_EMP
BEFORE UPDATE ON scott.EMP
FOR EACH ROW
BEGIN
IF :OLD.SAL > :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资减少');
ELSIF :OLD.SAL < :NEW.SAL THEN
DBMS_OUTPUT.PUT_LINE('工资增加');
ELSE
DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
END IF;
DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.SAL);
DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.SAL);
END;测试代码：
UPDATE scott.emp SET sal = 3000 WHERE empno = '7788';
21. 使用oracle 伪列删除表中重复记录：
delete table t where t.rowid not in
(select max(t1.rowid) from table1 t1 where t1.name=t.name)
根据表结构scott.emp如下图:
22.列出所有员工的姓名及其直接上级的姓名。
分析：表自映射，为表起别名，进行关联 t1 表模拟员工表 t2 表保存直接上级信息
select t1.ename 员工姓名, t2.ename 直接上级 from emp t1,emp t2 where t1.MGR = t2.empno;
23.列出受雇日期早于其直接上级的所有员工。
select t1.*,t2.hiredate from emp t1,emp t2 where t1.MGR = t2.empno and t1.hiredate < t2.hiredate;
24.列出最低薪金大于1500的各种工作。
分析：工作的最低薪金 —- 按工作分组，求最低薪金
select min(sal) from emp group by job;
大于1500 是一个分组条件 — having
select job,min(sal) from emp group by job having min(sal) > 1500;
25.列出所有员工的年工资,按年薪从低到高排序。
select ename, sal*12 from emp order by sal*12 asc;
26.列出与“SCOTT”从事相同工作的所有员工。
分析：先用子查询查出SCOTT的工作 : select job from emp where ename =’SCOTT';
select * from emp where ename <> ‘SCOTT’ and job = (select job from emp where ename =’SCOTT’);
27.删除10号部门薪水最高的员工。
delete from emp where deptno=10 and sal >= all(select sal from emp where deptno=10 ); // MYSQL 不支持
Mysql 规范，修改或者删除 表中记录，不允许在子查询中 查询相同表
mysql解决方案：临时表
delete from emp where deptno=10 and sal >= all(select t.sal from (select sal from emp where deptno=10) t );
28.将薪水最高的员工的薪水降30%。
oracle：update emp set sal = sal*0.7 where sal = (select max(sal) from emp);
mysql: update emp set sal = sal*0.7 where sal = (select t.maxsal from (select max(sal) maxsal from emp) t);
29.示出薪水最高人的职位。
select job from emp where sal = (select max(sal) from emp);
select job from emp where sal >= all(select sal from emp);30.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
分析：
select ename,sal, case when sal>=3000 then ‘3级’ when sal>2000 then ‘2级’ else ‘1级’ end 级别 from emp;
31.语法：case … when … then … when … then … else … end
行列互换
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
变成(得到如下结果)：
姓名 语文 数学 物理
—- —- —- —-
李四 74 84 94
张三 74 83 93
——————-
select name,max(case when cource =’语文’ then score else 0 end) from scores group by name;
select name,max(case when cource =’语文’ then score else 0 end) 语文,max(case when cource =’数学’ then score else 0 end) 数学,
max(case when cource =’英语’ then score else 0 end) 英语 from scores group by name;
32. Oracle中的分页
- 使用rownum函数
SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
FROM(SELECT * FROM tmp t ORDER BY id desc
) r
where ROWNUM <= page*size
) a
WHERE a.rowno > (page-1)*size
- 使用row_number()over()函数
Select * from (
Select * ,row_number() over(order by id desc) num
) a where num between (page-1)*size and page*size
33.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
select a.name, b.name
from team a, team b
where a.name < b.name