1.MySQL和Oracle的區(qū)別：

1. MySQL是開源的項目，免費。中小企業(yè)首選MySQL，Oracle是Oracle公司推出的一款成熟的數(shù)據(jù)庫產(chǎn)品是閉源同時也是收費的，但是Oracle官網(wǎng)是不限制下載的（只要你有賬號）一般的開發(fā)測試是沒問題。
2. SQL語法的不同，MySQL較為靈活，Oracle較為嚴格（具體就是單引號，分頁的處理，主鍵的自動增長）
3. 對于事務(wù)的支持。MySQL對于事務(wù)默認是不支持的，只有存儲引擎innodb支持。而Oracle對于事務(wù)是完全支持。
4. 并發(fā)性。MySql默認表級鎖，Oracle行級鎖，所以oracle并發(fā)行高很多。

2.Oracle中function和procedure的區(qū)別？ 

1). 函數(shù)function是存儲過程的一種 

2). 函數(shù)可以沒有參數(shù),但是一定需要一個返回值，存儲過程可以沒有參數(shù),不需要返回值 

3). 函數(shù)return返回值沒有返回參數(shù)模式，存儲過程通過out參數(shù)返回值, 如果需要返   回多個參數(shù)則建議使用存儲過程 

4). 在sql數(shù)據(jù)操縱語句中只能調(diào)用函數(shù)而不能調(diào)用存儲過程

3. 比較truncate和delete命令 ？ 

1). Truncate 和delete都可以將數(shù)據(jù)實體刪掉，truncate 的操作并不記錄到 rollback日志，所以操作速度較快，但同時這個數(shù)據(jù)不能恢復 

2). Delete操作不騰出表空間的空間 

3). Truncate 不能對視圖等進行刪除 

4). Truncate是數(shù)據(jù)定義語言（DDL），而delete是數(shù)據(jù)操縱語言(DML) 

4.oralce中 rowid, rownum的定義 

1). rowid和rownum都是偽列 

2). rowid是物理地址，用于定位oracle中具體數(shù)據(jù)的物理存儲位置 

3). rownum則是sql的輸出結(jié)果排序

5. 事務(wù)的特性（ACID）是指什么

事務(wù)是作為一個邏輯單元執(zhí)行的一系列操作，一個邏輯工作單元必須有四個屬性，稱為 ACID(原子性、一致性、隔離性和持久性)屬性。

1）原子性（Atomic）：事務(wù)中的各項操作，要么全做要么全不做，任何一項操作的失敗都會導致整個事務(wù)的失敗

2）一致性（Consistent）：事務(wù)結(jié)束后系統(tǒng)狀態(tài)是一樣的

3）隔離性（Isolated）：并發(fā)執(zhí)行的事務(wù)彼此無法看到對方的中間狀態(tài)

4）持久性（Durable）：事務(wù)完成后，即使發(fā)生災(zāi)難性的故障，通過日志和同步備份可以在故障發(fā)生后重建數(shù)據(jù)

6. 列舉幾種表連接方式

等值連接（內(nèi)連接）、非等連接、自連接、外連接（左、右、全）

內(nèi)連接又稱等值連接，篩選完全匹配的行

左連接以左表為主，篩選完全匹配的行之外還會把左表中沒有匹配的行也篩選，找不到匹配的數(shù)據(jù)用null填充

右連接以右表為主，篩選完全匹配的行之外還會把有表中沒有匹配的行也帥選，找不到匹配的數(shù)據(jù)用null填充

7. 描述oracle中tablespace和datafile之間的關(guān)系

一個表空間可包含一個或多個數(shù)據(jù)文件。表空間利用增加或擴展數(shù)據(jù)文件擴大表空間，表空間的大小為組成該表空間的數(shù)據(jù)文件大小的和。一個datafile只能屬于一個表空間;一個tablespace可以有一個或多個datafile,每個datafile只能在一個tablespace內(nèi), table中的數(shù)據(jù),通過hash算法分布在tablespace中的各個datafile中,tablespace是邏輯上的概念,datafile則在物理上儲存了數(shù)據(jù)庫的種種對象。

8. 什么是邏輯備份

1) 邏輯備份(exp/imp)用于實現(xiàn)數(shù)據(jù)庫對象的恢復。但不是基于時間點可完全恢復的備份策略。只能作為聯(lián)機備份和脫機備份的一種補充。

2) 完全邏輯備份

完全邏輯備份是將整個數(shù)據(jù)庫導出到一個數(shù)據(jù)庫的格式文件中，該文件可以在不同的數(shù)據(jù)庫版本、操作系統(tǒng)和硬件平臺之間進行移植。

3) 指定表的邏輯備份

通過備份工具，可以將指定的數(shù)據(jù)庫表備份出來，這可以避免完全邏輯備份所帶來的時間和財力上的浪費。

9. 什么是物理備份

物理備份是最主要的備份方式。用于保證數(shù)據(jù)庫在最小的數(shù)據(jù)庫丟失或沒有數(shù)據(jù)丟失的情況下得到恢復.物理備份分為熱備份和冷備份

  1) 熱備份：主要是指備份過程在數(shù)據(jù)庫打開并且用戶可以使用的情況下進行。需要執(zhí)行熱物理備份的情況有：由于數(shù)據(jù)庫性質(zhì)要求不間斷工作，因而此時只能采用熱物理備份。

2) 冷備份：冷物理備份提供了最簡單和最直接的方法保護數(shù)據(jù)庫因物理損壞丟失。

對一個已經(jīng)存在大最數(shù)據(jù)量的數(shù)據(jù)庫，在晚間數(shù)據(jù)庫可以關(guān)閉，此時應(yīng)用冷物理。

當對數(shù)據(jù)庫服務(wù)器進行升級，（如更換硬盤），此時需要備份數(shù)據(jù)庫信息需要采用冷備。

10. NVL與NVL2兩個函數(shù)的使用方法和差別？

1）NVL (expr1, expr2)：expr1為NULL，返回expr2；不為NULL，返回expr1。注意兩者的類型要一致

2） NVL2 (expr1, expr2, expr3) ：expr1不為NULL。返回expr2；為NULL，返回expr3。expr2和expr3類型不同的話。expr3會轉(zhuǎn)換為expr2的類型 

11.union和union all的區(qū)別

1) UNION：由每一個查詢 選擇的 全部不反復的行組成。并集不包括反復值， 默認按第 1 個查詢的第 1 列升序排列。

2) UNION ALL： 由每一個查詢 選擇的 全部的行。全然并集包括反復值。 不排序。

12. minus和intersect區(qū)別

1）MINUS: 在第一個查詢中但不在后面查詢中的行，不包括重復行。 按第1 個查詢的第 1 列升序排列。

2）INTERSECT:取每一個查詢結(jié)果的交集。 不包括重復行。 按第1 個查詢的第 1 列升序排列。

13.什么是存儲過程，優(yōu)點是什么？

存儲過程就像我們編程語言中的函數(shù)一樣，封裝了我們的代碼(PLSQL、T-SQL)。存儲過程的優(yōu)點：

能夠?qū)⒋a封裝起來保存在數(shù)據(jù)庫之中讓編程語言進行調(diào)用存儲過程是一個預(yù)編譯的代碼塊，執(zhí)行效率比較高一個存儲過程替代大量T_SQL語句 ，可以降低網(wǎng)絡(luò)通信量，提高通信速率

14.什么是三范式

第一范式：字段是最小的的單元不可再分

第二范式：滿足第一范式,表中的字段必須完全依賴于全部主鍵而非部分主鍵。

第三范式：滿足第二范式，非主鍵外的所有字段必須互不依賴既不存在傳遞依賴

15.什么是視圖？以及視圖的使用場景有哪些？

1. 視圖是一種基于數(shù)據(jù)表的一種虛表
2. 視圖建立在已有表的基礎(chǔ)上, 視圖賴以建立的這些表稱為基表

向視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句,可以將視圖理解為存儲起來的 SELECT 語句

視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式

視圖沒有存儲真正的數(shù)據(jù)，真正的數(shù)據(jù)還是存儲在基表中

程序員雖然操作的是視圖，但最終視圖還會轉(zhuǎn)成操作基表

一個基表可以有0個或多個視圖

16.索引是什么？有什么作用以及優(yōu)缺點？

（1）是一種快速查詢表中內(nèi)容的機制，類似于新華字典的目錄

（2）運用在表中某個些字段上，但存儲時，獨立于表之外

優(yōu)缺點：

1. 索引加快數(shù)據(jù)庫的檢索速度
2. 索引降低了插入、刪除、修改等維護任務(wù)的速度(雖然索引可以提高查詢速度，但是它們也會導致數(shù)據(jù)庫系統(tǒng)更新數(shù)據(jù)的性能下降，因為大部分數(shù)據(jù)更新需要同時更新索引)
3. 唯一索引可以確保每一行數(shù)據(jù)的唯一性，通過使用索引，可以在查詢的過程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能
4. 索引需要占物理和數(shù)據(jù)空間

17.數(shù)據(jù)庫的樂觀鎖和悲觀鎖是什么？

確保在多個事務(wù)同時存取數(shù)據(jù)庫中同一數(shù)據(jù)時不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫的統(tǒng)一性，樂觀鎖和悲觀鎖是并發(fā)控制主要采用的技術(shù)手段。

悲觀鎖：假定會發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作.在查詢數(shù)據(jù)的時候就把事務(wù)鎖起來，直到提交事務(wù).實現(xiàn)方式：使用數(shù)據(jù)庫中的鎖機制。

 執(zhí)行select xxx for update操作時，數(shù)據(jù)會被鎖定，只有執(zhí)行commit或rollback才會釋放.執(zhí)行select xxx for update nowait操作時，數(shù)據(jù)也會被鎖定，其他人訪問時或返回ORA-00054錯誤，內(nèi)容是資源正忙，需要采取相應(yīng)的業(yè)務(wù)措施進行處理。

樂觀鎖：假設(shè)不會發(fā)生并發(fā)沖突，只在提交操作時檢查是否違反數(shù)據(jù)完整性。在修改數(shù)據(jù)的時候把事務(wù)鎖起來，通過version的方式來進行鎖定.實現(xiàn)方式：使用version版本或者時間戳.

1）：在數(shù)據(jù)取得的時候把整個數(shù)據(jù)都copy到應(yīng)用中，在進行提交的時候比對當前數(shù)據(jù)庫中的數(shù)據(jù)和開始的時候更新前取得的數(shù)據(jù)。當發(fā)現(xiàn)兩個數(shù)據(jù)一模一樣以后，就表示沒有沖突可以提交，否則就是并發(fā)沖突，需要去用業(yè)務(wù)邏輯進行解決。

2)：新增一個Table的Column，這個column是采用timestamp型，存儲數(shù)據(jù)最后更新的時間。這種Timestamp的數(shù)據(jù)精度在Oracle的時間類型中是最高的，精確到微秒，是在更新提交的時候檢查當前數(shù)據(jù)庫中數(shù)據(jù)的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突。

18.數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

1)范式優(yōu)化：

1)比如消除冗余（節(jié)省空間）

2)反范式優(yōu)化：比如適當加冗余等（減少join）

3)拆分表： 垂直拆分和水平拆分

19.解釋oralce中的游標

游標就是指向一個結(jié)果集的記錄定位器（指示器），用于定位記錄。游標的使用方式：聲明游標，打開游標，提取游標數(shù)據(jù)，關(guān)閉游標

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中的觸發(fā)器

觸發(fā)器是一種特殊的存儲過程，存儲過程由用戶顯式調(diào)用，而觸發(fā)器是當用戶對表

進行update,delete,insert是自動調(diào)用。觸發(fā)器類型通常包括（插入，刪除，修改）

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)

根據(jù)表結(jié)構(gòu)scott.emp如下圖:

22.列出所有員工的姓名及其直接上級的姓名。

分析：表自映射，為表起別名，進行關(guān)聯(lián)  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 規(guī)范，修改或者刪除 表中記錄，不允許在子查詢中 查詢相同表

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

行列互換

姓名 課程 分數(shù)

張三 語文 74

張三 數(shù)學 83

張三 物理 93

李四 語文 74

李四 數(shù)學 84

李四 物理 94

變成(得到如下結(jié)果)：

姓名 語文 數(shù)學 物理

—- —- —- —-

李四 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 ='數(shù)學' then score else 0 end) 數(shù)學,
max(case when cource ='英語' then score else 0 end) 英語  from scores group by name;

32. Oracle中的分頁

使用rownum函數(shù)

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()函數(shù)

  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,對應(yīng)四個球?qū)?，現(xiàn)在四個球?qū)M行比賽，用一條sql語句顯示所有可能的比賽組合.

select a.name, b.name
from team a, team b
where a.name < b.name

總結(jié)

到此這篇關(guān)于Oracle面試題及答案超全整理的文章就介紹到這了,更多相關(guān)Oracle面試題內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！