Oracle面试题整理

目录
Oracle面试题整理
1.MySQL和Oracle的区别：
2.Oracle中function和procedure的区别？ 
3. 比较truncate和delete命令 ？ 
4.oralce中 rowid, rownum的定义 
5. 事务的特性（ACID）是指什么
6. 列举几种表连接方式
7. 形貌oracle中tablespace和datafile之间的关系
8. 什么是逻辑备份
9. 什么是物理备份
10. NVL与NVL2两个函数的使用方法和差别？
11.union和union all的区别
12. minus和intersect区别
13.什么是存储过程，优点是什么？
14.什么是三范式
15.什么是视图？以及视图的使用场景有哪些？
16.索引是什么？有什么作用以及优缺点？
17.数据库的乐观锁和悲观锁是什么？
18.数据库布局优化1)范式优化：
 19.解释oralce中的游标
20.形貌oracle中的触发器
21.  使用oracle 伪列删除表中重复记载： 
22.列出所有员工的姓名及其直接上级的姓名。
23.列出受雇日期早于其直接上级的所有员工。
24.列出最低薪金大于1500的各种工作。
25.列出所有员工的年工资,按年薪从低到高排序。 
26.列出与“SCOTT”从事相同工作的所有员工。
27.删除10号部门薪水最高的员工。
28.将薪水最高的员工的薪水降30%。
29.示出薪水最高人的职位。
30.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
31.语法：case … when … then … when … then … else … end
32. Oracle中的分页
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中的游标

游标就是指向一个结果集的记载定位器（指示器），用于定位记载。游标的使用方式：声明游标，打开游标，提取游标数据，关闭游标

1. Declare
3.  cursor cur is select * from category;
5.     record category%rowtype;
7.   begin
9.     open cur;
11.     loop
13.       fetch cur into record;
15.       if cur%notfound then
17.         exit;
19.       end if;
21.       dbms_output.put_line(to_char(record.id) || ',' || record.name);
23.     end loop;
25.     close cur;
27.  end;

复制代码

20.形貌oracle中的触发器

触发器是一种特殊的存储过程，存储过程由用户显式调用，而触发器是当用户对表
进行update,delete,insert是自动调用。触发器类型通常包括（插入，删除，修改）

1. CREATE OR REPLACE TRIGGER SAL_EMP
3.  BEFORE UPDATE ON scott.EMP
5.  FOR EACH ROW
7. BEGIN
9.  IF :OLD.SAL > :NEW.SAL THEN
11.   DBMS_OUTPUT.PUT_LINE('工资减少');
13.  ELSIF :OLD.SAL < :NEW.SAL THEN
15.   DBMS_OUTPUT.PUT_LINE('工资增加');
17.  ELSE
19.   DBMS_OUTPUT.PUT_LINE('工资未作任何变动');
21.  END IF;
23.  DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.SAL);
25.  DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.SAL);
27. 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%。

1. oracle：update emp set sal = sal*0.7 where sal = (select max(sal) from emp);
2. mysql:  update emp set sal = sal*0.7 where sal = (select t.maxsal  from (select max(sal) maxsal from emp) t);

复制代码

29.示出薪水最高人的职位。

1. select job from emp where sal = (select max(sal) from emp);
2. select job from emp where sal >= all(select sal from emp);

复制代码

30.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)


分析：

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
——————-

1. select name,max(case when cource =’语文’ then score else 0 end) from scores group by name;
3. select name,max(case when cource =’语文’ then score else 0 end)  语文,max(case when cource =’数学’ then score else 0 end) 数学,
4. max(case when cource =’英语’ then score else 0 end) 英语  from scores group by name;

复制代码

32. Oracle中的分页

• 使用rownum函数
  

1. SELECT *
3. FROM (SELECT ROWNUM AS rowno,r.*
5.            FROM(SELECT * FROM tmp t  ORDER BY id desc
7.                    ) r
9.            where ROWNUM <= page*size
11.           ) a
13. WHERE a.rowno > (page-1)*size

复制代码

• 使用row_number()over()函数
  

1.   Select * from (
3.   Select * ,row_number() over(order by id desc) num
5.   ) a where num between (page-1)*size and page*size

复制代码

33.一个叫department的表，内里只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，如今四个球对进行比赛，用一条sql语句表现所有大概的比赛组合.

1. select a.name, b.name
2. from team a, team b
3. where a.name < b.name

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。