直擊資料庫面試題:資料庫查詢語句

Student(S#,Sname,Sage,Ssex) 學生表

Course(C#,Cname,T#) 課程表

SC(S#,C#,score) 成績表

Teacher(T#,Tname) 教師表

問題：

1、查詢「001」課程比「002」課程成績高地所有學生的學號

select a.S#

from (select s#,score from SC where C#=』001′) a,

(select s#,score from SC where C#=』002′) b

where a.score>b.score and a.s#=b.s#;

2、查詢平均成績大於60分的同學的學號和平均成績

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數、總成績

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

4、查詢姓「李」的老師的個數

select count(distinct(Tname))

from Teacher

where Tname like 『李%』;

5、查詢沒學過「葉平」老師課的同學的學號、姓名

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=』葉平』);

6、查詢學過「001」並且也學過編號「002」課程的同學的學號、姓名

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# and SC.C#=』001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=』002′);

7、查詢學過「葉平」老師所教的所有課的同學的學號、姓名

select S#,Sname

from Student

where S# in

(select S#

from SC ,Course ,Teacher

where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=』葉平』 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=』葉平』));

8、查詢所有課程成績小於60分的同學的學號、姓名

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

9、查詢沒有學全所有課的同學的學號、姓名

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S#

group by Student.S#,Student.Sname having count(C#)

10、查詢至少有一門課與學號為「1001」的同學所學相同的同學的學號和姓名

select S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）;

11、刪除學習「葉平」老師課的SC表記錄

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平';

12、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

SELECT L.C# 課程ID,L.score 最高分,R.score 最低分

FROM SC L ,SC R

WHERE L.C# = R.C#

and

L.score = (SELECT MAX(IL.score)

FROM SC IL,Student IM

WHERE IL.C# = L.C# and IM.S#=IL.S#

GROUP BY IL.C#)

and

R.Score = (SELECT MIN(IR.score)

FROM SC IR

WHERE IR.C# = R.C#

GROUP BY IR.C# );

13、查詢學生平均成績及其名次

SELECT 1+(SELECT COUNT( distinct 平均成績)

FROM (SELECT S#,AVG(score) 平均成績

FROM SC

GROUP BY S# ) T1

WHERE 平均成績 > T2.平均成績) 名次, S# 學生學號,平均成績

FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) T2

ORDER BY 平均成績 desc;

14、查詢各科成績前三名的記錄:(不考慮成績並列情況)

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC)

ORDER BY t1.C#;

15、查詢每門功課成績最好的前兩名

SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC )

ORDER BY t1.C#;

補充：

已經知道原表

year salary

——————

2000 1000

2001 2000

2002 3000

2003 4000

解：

select b.year,sum(a.salary)

from salary a,salary b

where a.year

group by b.year

order by b.year;

在面試過程中多次碰到一道SQL查詢的題目，查詢A(ID,Name)表中第31至40條記錄，ID作為主鍵可能是不是連續增長的列，完整的查詢語句如下：

方法一：

select top 10 *

from A

where ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID

方法二：

select top 10 *

from A

where ID not In (select top 30 ID from A order by ID)

order by ID

資料庫面試題

1. 在一個查詢中，使用哪一個關鍵字能夠除去重複列值？

答案：使用distinct關鍵字

2. 什麼是快照？它的作用是什麼？

答案：快照Snapshot是一個文件系統在特定時間裡的鏡像，對於在線實時數據備份非常有用。快照對於擁有不能停止的應用或具有常打開文件的文件系統的備份非常重要。對於只能提供一個非常短的備份時間而言，快照能保證系統的完整性。

3. 解釋存儲過程和觸發器

存儲過程是一組Transact-SQL語句，在一次編譯後可以執行多次。因為不必重新編譯Transact-SQL語句，所以執行存儲過程可以提高性能。

觸發器是一種特殊類型的存儲過程，不由用戶直接調用。創建觸發器時會對其進行定義，以便在對特定表或列作特定類型的數據修改時執行。

4. SQL Server是否支持行級鎖，有什麼好處？

支持動態行級鎖定。

SQL Server 2000動態地將查詢所引用的每一個表的鎖定粒度調整到合適的級別。當查詢所引用的少數幾行分散在一個大型表中時，優化數據並行訪問的最佳辦法是使用粒度鎖，如行鎖。但是，如果查詢引用的是一個表中的大多數行或所有行，優化數據並行訪問的最佳辦法可以是鎖定整個表，以儘量減少鎖定開銷並儘快完成查詢。

SQL Serve 2000通過為每個查詢中的每個表選擇適當的鎖定級別，在總體上優化了數據並發訪問。對於一個查詢，如果只引用一個大型表中的幾行，則資料庫引擎可以使用行級鎖定；如果引用一個大型表的幾頁中的多行，則使用頁級鎖定；如果引用一個小型表中的所有行，則使用表級鎖定。

5. 資料庫日誌幹什麼用，資料庫日誌滿的時候再查詢資料庫時會出現什麼情況？

每個資料庫都有事務日誌，用以記錄所有事務和每個事務對資料庫所做的修改。

6. 存儲過程和函數的區別？

存儲過程是用戶定義的一系列SQL語句的集合，涉及特定表或其它對象的任務，用戶可以調用存儲過程，而函數通常是資料庫已定義的方法，它接收參數並返回某種類型的值並且不涉及特定用戶表。

7. 事務是什麼？

事務是作為一個邏輯單元執行的一系列操作，一個邏輯工作單元必須有四個屬性，稱為 ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個事務：

(1) 原子性

事務必須是原子工作單元；對於其數據修改，要麼全都執行，要麼全都不執行。

(2) 一致性

事務在完成時，必須使所有的數據都保持一致狀態。在相關資料庫中，所有規則都必須應用於事務的修改，以保持所有數據的完整性。事務結束時，所有的內部數據結構（如 B 樹索引或雙向鍊表）都必須是正確的。

(3) 隔離性

由並發事務所作的修改必須與任何其它並發事務所作的修改隔離。事務查看數據時數據所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是另一事務修改它之後的狀態，事務不會查看中間狀態的數據。這稱為可串行性，因為它能夠重新裝載起始數據，並且重播一系列事務，以使數據結束時的狀態與原始事務執行的狀態相同。

(4) 持久性

事務完成之後，它對於系統的影響是永久性的。該修改即使出現系統故障也將一直保持。

8. 遊標的作用？如何知道遊標已經到了最後？

遊標用於定位結果集的行，通過判斷全局變量@@FETCH_STATUS可以判斷是否到了最後，通常此變量不等於0表示出錯或到了最後。

9. 觸發器分為事前觸發和事後觸發，這兩種觸發有何區別？語句級觸發和行級觸發有何區別？

事前觸發器運行於觸發事件發生之前，而事後觸發器運行於觸發事件發生之後。通常事前觸發器可以獲取事件之前和新的欄位值。

語句級觸發器可以在語句執行前或後執行，而行級觸發在觸發器所影響的每一行觸發一次。

10. 解決死鎖的方法有哪些？

SQL Server自動檢測和消除死鎖

設置死鎖優先級

設置鎖定超時

使用更新鎖避免死鎖

11. 提高資料庫運行效率的辦法有哪些？

在給定的系統硬體和系統軟體條件下，提高資料庫系統的運行效率的辦法是：

(1) 在資料庫物理設計時，降低範式，增加冗餘, 少用觸發器, 多用存儲過程。

(2) 當計算非常複雜、而且記錄條數非常巨大時(例如一千萬條)，複雜計算要先在資料庫外面，以文件系統方式用C++語言計算處理完成之後，最後才入庫追加到表中去。這是電信計費系統設計的經驗。

(3) 發現某個表的記錄太多，例如超過一千萬條，則要對該表進行水平分割。水平分割的做法是，以該表主鍵PK的某個值為界線，將該表的記錄水平分割為兩個表。若發現某個表的欄位太多，例如超過八十個，則垂直分割該表，將原來的一個表分解為兩個表。

(4) 對資料庫管理系統DBMS進行系統優化，即優化各種系統參數，如緩衝區個數。

(5) 在使用面向數據的SQL語言進行程序設計時，儘量採取優化算法。總之，要提高資料庫的運行效率，必須從資料庫系統級優化、資料庫設計級優化、程序實現級優化，這三個層次上同時下功夫。

12. 通俗地理解三個範式

通俗地理解三個範式，對於資料庫設計大有好處。在資料庫設計中，為了更好地應用三個範式，就必須通俗地理解三個範式(通俗地理解是夠用的理解，並不是最科學最準確的理解)

第一範式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；

第二範式：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的唯一性；

第三範式：3NF是對欄位冗餘性的約束，即任何欄位不能由其他欄位派生出來，它要求欄位沒有冗餘。沒有冗餘的資料庫設計可以做到。但是，沒有冗餘的資料庫未必是最好的資料庫，有時為了提高運行效率，就必須降低範式標準，適當保留冗餘數據。具體做法是：在概念數據模型設計時遵守第三範式，降低範式標準的工作放到物理數據模型設計時考慮。降低範式就是增加欄位，允許冗餘。

13. 簡述存儲過程的優缺點

優點：

1. 更快地執行速度：存儲過程只在創造時進行編譯，以後每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高資料庫執行速度；

2. 與事務的結合，提供更好的解決方案：當對資料庫進行複雜操作時(如對多個表進行Update、Insert、Query和Delete時），可將此複雜操作用存儲過程封裝起來與資料庫提供的事務處理結合一起使用；

3. 支持代碼重用：存儲過程可以重複使用,可減少資料庫開發人員的工作量；

4. 安全性高：可設定只有某此用戶才具有對指定存儲過程的使用權。

缺點：

1. 如果更改範圍大到需要對輸入存儲過程的參數進行更改，或者要更改由其返回的數據，則你仍需要更新程序集中的代碼以添加參數、更新 GetValue() 調用，等等，這時候估計比較繁瑣了。

2. 可移植性差由於存儲過程將應用程式綁定到 SQL Server，因此使用存儲過程封裝業務邏輯將限制應用程式的可移植性。如果應用程式的可移植性在您的環境中非常重要，則將業務邏輯封裝在不特定於 RDBMS 的中間層中可能是一個更佳的選擇。

14. 主鍵和唯一索引有什麼區別？

相同點：它們都屬於實體完整性約束。

不同點：

(1) 唯一性約束所在的列允許空值，但是主鍵約束所在的列不允許空值。

(2) 可以把唯一性約束放在一個或者多個列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列並不是表的主鍵列。

(3) 唯一性約束強制在指定的列上創建一個唯一性索引。在默認情況下，創建唯一性的非聚簇索引，但是，也可以指定所創建的索引是聚簇索引。

(4) 建立主鍵的目的是讓外鍵來引用。

(5) 一個表最多只有一個主鍵，但可以有很多唯一鍵。

15. 簡述索引存取的方法的作用和建立索引的原則

作用：加快查詢速度。

原則：

(1) 如果某屬性或屬性組經常出現在查詢條件中，考慮為該屬性或屬性組建立索引；

(2) 如果某個屬性常作為最大值和最小值等聚集函數的參數，考慮為該屬性建立索引；

(3) 如果某屬性經常出現在連接操作的連接條件中，考慮為該屬性或屬性組建立索引；

16. 簡述資料庫的設計過程

資料庫設計分為五個階段：

需求分析：主要是準確收集用戶信息需求和處理需求，並對收集的結果進行整理和分析，形成需求說明。

概念結構設計：對用戶需求進行綜合、歸納、抽象，形成一個與與具體的DBMS無關概念模型（一般為ER模型）。

邏輯結構設計：將概念結構設計的概念模型轉化為某個特定的DBMS所支持的數據模型，建立資料庫邏輯模式，並對其進行優化，同時為各種用戶和應用設計外模式。

物理結構設計：為設計好的邏輯模型選擇物理結構，包括存儲結構和存取方法等，建立資料庫物理模式。

實施和維護：實施就是使用DLL語言建立資料庫模式，將實際數據載入資料庫，建立真正的資料庫。維護階段是對運行中的資料庫進行評價、調整和修改。

17. 什麼是內存洩漏？

一般我們所說的內存洩漏指的是堆內存的洩漏。堆內存是程序從堆中為其分配的，大小任意的，使用完後要顯示釋放內存。當應用程式用關鍵字new等創建對象時，就從堆中為它分配一塊內存，使用完後程序調用free或者delete釋放該內存，否則就說該內存就不能被使用，我們就說該內存被洩漏了。

18. 什麼是基本表？什麼是視圖？

基本表是本身獨立存在的表，在 SQL 中一個關係就對應一個表。

視圖是從一個或幾個基本表導出的表。視圖本身不獨立存儲在資料庫中，是一個虛表

19. 試述視圖的優點

(1) 視圖能夠簡化用戶的操作

(2) 視圖使用戶能以多種角度看待同一數據；

(3) 視圖為資料庫提供了一定程度的邏輯獨立性；

(4) 視圖能夠對機密數據提供安全保護。

20. 所有的視圖是否都可以更新？為什麼？

不是。視圖是不實際存儲數據的虛表，因此對視圖的更新，最終要轉換為對基本表的更新。因為有些視圖的更新不能惟一有意義地轉換成對相應基本表的更新，所以，並不是所有的視圖都是可更新的。

21. 哪類視圖是可以更新的？哪類視圖是不可更新的？

基本表的行列子集視圖一般是可更新的。若視圖的屬性來自集函數、表達式，則該視圖肯定是不可以更新的。

22. 維護資料庫的完整性、一致性、你喜歡用觸發器還是自寫業務邏輯？為什麼?

儘可能用約束（包括CHECK、主鍵、唯一鍵、外鍵、非空欄位）實現，這種方式的效率最好；其次用觸發器，這種方式可以保證無論何種業務系統訪問資料庫都能維持資料庫的完整性、一致性；最後再考慮用自寫業務邏輯實現，但這種方式效率最低、編程最複雜，當為下下之策。

23. 在為視圖創建索引前，視圖本身必須滿足哪些條件？

(1) 視圖以及視圖中引用的所有表都必須在同一資料庫中，並具有同一個所有者

(2) 索引視圖無需包含要供優化器使用的查詢中引用的所有表。

(3) 必須先為視圖創建唯一群集索引，然後才可以創建其它索引。

(4) 創建基表、視圖和索引以及修改基表和視圖中的數據時，必須正確設置某些 SET 選項（在本文檔的後文中討論）。另外，如果這些 SET 選項正確，查詢優化器將不考慮索引視圖。

(5) 視圖必須使用架構綁定創建，視圖中引用的任何用戶定義的函數必須使用 SCHEMABINDING 選項創建。

(6) 另外，還要求有一定的磁碟空間來存放由索引視圖定義的數據。

24. 什麼是SQL Server的確定性函數和不確定性函數？

只要使用特定的輸入值集並且資料庫具有相同的狀態，不管何時調用，始終都能範圍相同結果的函數叫確定性函數。幾十訪問的資料庫的狀態不變，每次書用特定的輸入值都可能範圍不同結果的函數叫非確定性函數。