目錄

• 1.MySQL和Oracle的區(qū)別：
• 2.Oracle中function和procedure的區(qū)別？
• 3. 比較truncate和delete命令 ？
• 4.oralce中 rowid, rownum的定義
• 5. 事務(wù)的特性（ACID）是指什么
• 6. 列舉幾種表連接方式
• 7. 描述oracle中tablespace和datafile之間的關(guān)系
• 8. 什么是邏輯備份
• 9. 什么是物理備份
• 10. NVL與NVL2兩個函數(shù)的使用方法和差別？
• 11.union和union all的區(qū)別
• 12. minus和intersect區(qū)別
• 13.什么是存儲過程，優(yōu)點是什么？
• 14.什么是三范式
• 15.什么是視圖？以及視圖的使用場景有哪些？
• 16.索引是什么？有什么作用以及優(yōu)缺點？
• 17.數(shù)據(jù)庫的樂觀鎖和悲觀鎖是什么？
• 18.數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化
• 19.解釋oralce中的游標
• 20.描述oracle中的觸發(fā)器
• 21. 使用oracle 偽列刪除表中重復(fù)記錄：
• 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,對應(yīng)四個球?qū)ΓF(xiàn)在四個球?qū)M行比賽，用一條sql語句顯示所有可能的比賽組合.
• 總結(jié)

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ù)不能恢復(fù)

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ù)中的各項操作，要么全做要么全不做，任何一項操作的失敗都會導(dǎo)致整個事務(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ù)庫對象的恢復(fù)。但不是基于時間點可完全恢復(fù)的備份策略。只能作為聯(lián)機備份和脫機備份的一種補充。

2) 完全邏輯備份

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

3) 指定表的邏輯備份

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

9. 什么是物理備份

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

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

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

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

當(dā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：由每一個查詢 選擇的 全部不反復(fù)的行組成。并集不包括反復(fù)值， 默認按第 1 個查詢的第 1 列升序排列。

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

12. minus和intersect區(qū)別

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

2）INTERSECT:取每一個查詢結(jié)果的交集。 不包括重復(fù)行。 按第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ù)的速度(雖然索引可以提高查詢速度，但是它們也會導(dǎo)致數(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)用中，在進行提交的時候比對當(dāng)前數(shù)據(jù)庫中的數(shù)據(jù)和開始的時候更新前取得的數(shù)據(jù)。當(dāng)發(fā)現(xiàn)兩個數(shù)據(jù)一模一樣以后，就表示沒有沖突可以提交，否則就是并發(fā)沖突，需要去用業(yè)務(wù)邏輯進行解決。

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

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

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

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

2)反范式優(yōu)化：比如適當(dāng)加冗余等（減少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 thenexit;      end if;      dbms_output.put_line(to_char(record.id) || "," || record.name);    end loop;    close cur; end;

20.描述oracle中的觸發(fā)器

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

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

CREATE OR REPLACE TRIGGER SAL_EMP BEFORE UPDATE ON scott.EMP FOR EACH ROWBEGIN 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 偽列刪除表中重復(fù)記錄：

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ù)學(xué) 83

張三 物理 93

李四 語文 74

李四 數(shù)學(xué) 84

李四 物理 94

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

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

—- —- —- —-

李四 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ù)學(xué)" then score else 0 end) 數(shù)學(xué),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  ) aWHERE 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ū)ΓF(xiàn)在四個球?qū)M行比賽，用一條sql語句顯示所有可能的比賽組合.

select a.name, b.namefrom team a, team bwhere a.name < b.name

總結(jié)

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