資料庫面試題目

1  關聯式資料庫的設計範式有哪些？

第一範式： 關聯式資料庫表的每一列都是不可分割的基本資料項目。第一範式是確保列中的值是不重複。

第二範式：必須是第一範式。而且資料庫表中的每一行必須被唯一的區分。 通常將表的一個或者多個屬性作為行的主鍵。（另一種說法：而且R中每一個非主屬性完全函數依賴於R的某個候選索引鍵）

第三範式：必須是第二範式。一個表中的列不能依賴於另一個表中的非主鍵列。（另一種說法：每個非主屬性都不傳遞依賴於R的候選索引鍵）

BCF： 必須是第一範式，而且每個屬性都不傳遞依賴於關係R的候選索引鍵。

第四範式：設R是一個關係模式，D是R上的多值依賴集合。如果D中成立非平凡多值依賴X->->Y時，X必是R的超鍵。

2 預存程序和函數的區別是什嗎？

預存程序是使用者定義的一系列SQL語句的集合，涉及特定表或其他對象的任務，使用者可以調用預存程序。而函數通常是它接收參數並返回某種類型的值，並且不涉及特定使用者表。

如下表格是在Oracle中裡面的：

預存程序的定義：

CREATE [OR REPLACE] Procedure [模式名.]過程名

                      [(參數名 [IN | OUT| IN OUT ] 資料類型 ...)]

{IS | AS}

                  [說明部分]

<PL/SQL塊>;

函數的定義：

CREATE [OR REPLACE]  FUNCTION  [模式名.]過程名

                      [(參數名 [IN ] 資料類型 ...)]

                      RETURN 資料類型

{IS | AS}

                  [說明部分]

<PL/SQL塊>;

3  什麼是資料庫事務？

答案：資料庫事務是指作為單個邏輯工作單元執行的一系列操作，這些操作要麼全做，要麼全不做，是一個不可分割的工作單位。 滿足ACID。

A 原子性： 事務的原子性是指一個事務要麼全部執行，要麼不執行。

C 一致性： 事務的一致性是指事務的運行並不改變資料庫中資料的一致性。 (銀行轉賬)

I 獨立性：事務的獨立性是指並發執行的事務相互之間不能干擾。

D 持久性： 事務的持久性是指 事務運行成功以後，系統的更新是永久的。

4 索引基本問題

索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速存取資料庫表中的特定資訊。建立索引的目的是加快對錶中記錄的 尋找或 排序。為表設定索引要付出代價的：一是增加了資料庫的儲存空間，二是在插入和修改資料時要花費較多的時間(因為索引也要隨之變動)。建立索引可以大大提高系統的效能。 建立索引的優點：第一，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。第二，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。第三，可以加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。
也許會有人要問：增加索引有如此多的優點，為什麼不對錶中的每一個列建立一個索引呢？因為，增加索引也有許多不利的方面。 建立索引的缺點：第一，建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。第二，索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。第三，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。
索引是建立在資料庫表中的某些列的上面。在建立索引的時候，應該考慮在哪些列上可以建立索引，在哪些列上不能建立索引。 什麼情況下建立索引？？一般來說，應該在這些列上建立索引：1  在經常需要搜尋的列上，可以加快搜尋的速度；2  在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；3  在經常用在串連的列上，這些列主要是一些外鍵，可以加快串連的速度；4   在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；5   在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間； 6   在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。同樣，對於有些列不應該建立索引。什麼情況下不宜建立索引？？一般來說，不應該建立索引的的這些列具有下列特點：第一，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。第二，對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。第三，對於那些定義為text, image和bit資料類型的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少,不利於使用索引。第四，當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改操作遠遠多於檢索操作時，不應該建立索引。

5 解釋叢集索引和非叢集索引之間的區別

答案： 叢集索引的順序就是資料的實體儲存體順序，而對非叢集索引是索引順序與資料的實體儲存體順序無關。正因為如此，一個表最多隻能有一個叢集索引。

叢集索引確定了資料的物理順序。叢集索引類似於電話薄（按姓氏排序）。 由於叢集索引規定資料在表中的實體儲存體順序，因此一個表只能包含一個叢集索引。但該索引可以包含多個列（複合式索引），就像電話薄按照姓氏和名字進行組織一樣。 叢集索引對於那些經常要搜尋範圍值的列特別有效。使用叢集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理上相鄰。

非叢集索引：非叢集索引與課本中索引類似。資料存放區在一個地方，索引儲存在另一個地方，索引帶有指標指向資料的儲存位置。索引中的項目按照索引索引值的順序儲存，而表中的資訊按另一種順序儲存。

eg: 在sql Server中，索引是通過二叉樹的資料結構來描述的，我們可以這麼理解叢集索引： 索引的分葉節點就是資料節點。 而非叢集索引的分葉節點仍然是索引節點，只不過有一個指標指向對應的資料區塊。

6  角色

資料庫角色是被命名的一組與資料庫操作相關的許可權，角色是許可權的集合。因此可以為一組具有相同許可權的使用者建立一個角色，使用角色來管理資料庫許可權可以簡化授權的過程。

 

1 建立一個角色

Create ROLE R1;

 

2 使用GRANT語句，使角色R1擁有Student表的SELECT、UPDATE、INSERT許可權

GRANT SELECT ，PDATE，INSERT

ON TABLE Student

TO R1;

 

3  將這個角色授予王平，張明，趙玲。使他們具有角色R1所包含的全部許可權

GRANT R1

TO 王平,張明, 趙玲;

 

4  當然，也可以一次性的通過R1來回收王平的這3個許可權

REVOKE R1

FROM 王平；

 

角色的修改

GRANT DELETE

ON TABLE Student

TO R1

使角色R1在原來的基礎上增加了Student表的DELETE許可權

 

REVOKE SELECT

ON TABLE Student

FROM R1;

 

7  求成績第二高的學生的名字

求：1、每個科目的最高分。 
      2、java成績最高的姓名 
      3、java成績第二高的姓名

1.每個科目的最高分：
    SELECT kemu,max(score) FROM table GROUP BY kemu;

    分析：找出成績最高的人後，根據科目分組就可以得到每組的最高分的人。

2.java成績最高的姓名：
     SELECT name FROM table WHERE kemu='java' and 
           score=(SELECT max(score) FROM table WHERE kemu='java');

     分析：從表格中查詢名稱時，提出兩點條件1.科目是java，2.科目是java的最高分

3.java成績第二高的姓名：
     SELECT name FROM table WHERE kemu='java' GROUP BY name 
           ORDER BY SCORE DESC LIMIT 1,1；

    分析：在查詢姓名時根據[color=blue]  kemu='java'  [/color]這個條件，然後根據姓名分組，然後根據成績排序，最後在列表中根據TOP 方法 LIMIT 選取從0開始的第二個姓名，只取1個人。OK了！這個題目主要考察了分組，排名和TOP取值方面的知識，難度相對最高。如果遇到這樣的題，應該把大的問題一個個分散成小問題，然後一個個的解決小問題，然後拼湊起來，大問題就可以解決了。

8  稠密索引和稀疏索引

一、稠密索引如果記錄是排好序的，我們就可以在記錄上建立稠密索引，它是這樣一系列儲存塊：塊中只存放記錄的鍵以及指向記錄本身的指標，指標就是一個指向記錄或儲存塊地址。稠密索引檔案中的索引塊保持鍵的順序與檔案中的排序次序一致。既然我們假定尋找鍵和指標所佔儲存空間遠小於記錄本身，我們就可以認為儲存索引檔案比儲存資料檔案所需儲存塊要少得多。當記憶體容納不下資料檔案，但能容納下索引檔案時，索引的優勢尤為明顯。這時，通過使用索引檔案，我們每次查詢只用一次I/O操作就能找到給定索引值的記錄。
  www.2cto.com  所示為一個建立在循序檔上的稠密索引。圖1 循序檔（右）上的稠密索引（左）第一個索引塊存放指向前四個記錄的指標，第二個索引塊存放指向接下來的四個記錄的指標，依此類推。稠密索引支援按給定索引值尋找相應記錄的查詢。給定一個索引值K，我們先在索引塊中尋找K。當找到K後，我們按照K所對應的指標到資料檔案中找到相應的記錄。似乎在找到K之前我們需要檢索索引檔案的每個儲存塊，或平均一半的儲存塊。然而，由於有下面幾個因素，基於索引的尋找比它看起來更為有效：
 1.索引塊數量通常比資料區塊數量少。2.由於鍵被排序，我們可以使用二分尋找法來尋找K。若有n個索引塊，我們只需尋找log2n個塊。3.索引檔案可能足夠小，以至可以永久地存放在主存緩衝區中。要是這樣的話，尋找鍵K時就只涉及主存訪問而不需執行I/O操作。
 二、稀疏索引稀疏索引只為資料檔案的每個儲存塊設一個鍵-指標對,它比稠密索引節省了更多的儲存空間，但尋找給定值的記錄需更多的時間。只有當資料檔案是按照某個尋找鍵排序時，在該尋找鍵上建立的稀疏索引才能被使用，而稠密索引則可以應用在任何的尋找鍵。2所示，稀疏索引只為每個儲存塊設一個鍵-指標對。索引值是每個資料區塊中第一個記錄的對應值。圖2 循序檔上的稀疏索引同圖1執行個體一樣，我們假定資料檔案已排序，且其索引值為連續的10的倍數，直至某個較大的數。我們還繼續假定每個儲存塊可存放四個鍵-指標對。這樣，第一個索引儲存塊中為前四個資料存放區塊的第一個索引值的索引項目，它們分別是10、30、50和70。按照前面假定的索引值模式，第二個索引儲存塊中為第五至第八個資料存放區塊的第一個索引值的索引項目，它們分別是90、110、130和150。圖中我們還列出第三個索引儲存塊存放的索引值，它們分別是假設的第九至第十二個資料存放區塊的第一個索引值。
  www.2cto.com  在已有稀疏索引的情況下，要找出尋找索引值為K的記錄，我們得在索引中尋找到索引值小於或等於K的最大索引值。由於索引檔案已按鍵排序，我們可以使用二分尋找法來定位這個索引項目，然後根據它的指標找到相應的資料區塊。現在我們必須搜尋這個資料區塊以找到索引值為K的記錄。當然，資料區塊中必須有足夠的格式化資訊來標明其中的記錄及記錄內容，可以採用2.5節和2.7節中的任何技術。

10  B-Tree索引和Hash索引的區別  

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高於 B-Tree 索引。

可能很多人又有疑問了，既然 Hash 索引的效率要比 B-Tree 高很多，為什麼大家不都用 Hash 索引而還要使用 B-Tree 索引呢？任何事物都是有兩面性的，Hash 索引也一樣，雖然 Hash 索引效率高，但是 Hash 索引本身由於其特殊性也帶來了很多限制和弊端，主要有以下這些。

（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢。

由於 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 Hash 演算法處理之後的 Hash 值的大小關係，並不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免資料的排序操作。

由於 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關係並不一定和 Hash 運算前的索引值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算；

（3）Hash 索引不能利用部分索引鍵查詢。

對於複合式索引，Hash 索引在計算 Hash 值的時候是複合式索引鍵合并後再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過複合式索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。

（4）Hash 索引在任何時候都不能避免表掃描。

前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 索引值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際資料進行相應的比較，並得到相應的結果。

（5）Hash 索引遇到大量Hash值相等的情況後效能並不一定就會比B-Tree索引高。

對於選擇性比較低（即選擇度  %）的索引鍵，如果建立 Hash 索引，那麼將會存在大量記錄指標資訊存於同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的訪問，而造成整體效能低下。

hash相當於把key通過hashFunction Compute，得到key的hash值,再用這個hash值做指標，尋找hash表中是否存在key，如果存在就返回 key所對應的value，選定一個好的hash函數很重要，好的hash函數可以使計算出的hash值分布均勻，降低衝突，只有衝突減小了，才會降低 hash表的尋找時間。

b-tree完全基於key的比較，和二叉樹相同的道理，相當於建個排序後的資料集，使用二分法尋找演算法，實際上也非常快，而且受資料量增長影響非常小。