SQL常見面試題

sql常見面試題

sql理論題

1．觸發器的作用？

 

  答：觸發器是一中特殊的儲存過程，主要是通過事件來觸發而被執行的。它可以強化約束，來維護資料的完整性和一致性，可以跟蹤資料庫內的操作從而不允許未經許可的更新和變化。可以聯級運算。如，某表上的觸發器上包含對另一個表的資料操作，而該操作又會導致該表觸發器被觸發。

 

2。什麼是儲存過程？用什麼來呼叫？

 

答：儲存過程是一個預編譯的SQL語句，優點是允許模組化的設計，就是說只需建立一次，以後在該程式中就可以呼叫多次。如果某次操作需要執行多次SQL，使用儲存過程比單純SQL語句執行要快。可以用一個命令物件來呼叫儲存過程。

 

3。索引的作用？和它的優點缺點是什麼？

答：索引就一種特殊的查詢表，資料庫的搜尋引擎可以利用它加速對資料的檢索。它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的資料。索引可以是唯一的，建立索引允許指定單個列或者是多個列。缺點是它減慢了資料錄入的速度，同時也增加了資料庫的尺寸大小。

 

3。什麼是記憶體洩漏？

答：一般我們所說的記憶體洩漏指的是堆記憶體的洩漏。堆記憶體是程式從堆中為其分配的，大小任意的，使用完後要顯示釋放記憶體。當應用程式用關鍵字new等建立物件時，就從堆中為它分配一塊記憶體，使用完後程式呼叫free或者delete釋放該記憶體，否則就說該記憶體就不能被使用，我們就說該記憶體被洩漏了。

 

4。維護資料庫的完整性和一致性，你喜歡用觸發器還是自寫業務邏輯？為什麼？

 

答：我是這樣做的，儘可能使用約束，如check,主鍵，外來鍵，非空欄位等來約束，這樣做效率最高，也最方便。其次是使用觸發器，這種方法可以保證，無論什麼業務系統訪問資料庫都可以保證資料的完整新和一致性。最後考慮的是自寫業務邏輯，但這樣做麻煩，程式設計複雜，效率低下。

 

5。什麼是事務？什麼是鎖？

答：事務就是被繫結在一起作為一個邏輯工作單元的SQL語句分組，如果任何一個語句操作失敗那麼整個操作就被失敗，以後操作就會回滾到操作前狀態，或者是上有個節點。為了確保要麼執行，要麼不執行，就可以使用事務。要將有組語句作為事務考慮，就需要通過ACID測試，即原子性，一致性，隔離性和永續性。

 

  鎖：在所以的DBMS中，鎖是實現事務的關鍵，鎖可以保證事務的完整性和併發性。與現實生活中鎖一樣，它可以使某些資料的擁有者，在某段時間內不能使用某些資料或資料結構。當然鎖還分級別的。

 

6。什麼叫檢視？遊標是什麼？

答：檢視是一種虛擬的表，具有和物理表相同的功能。可以對檢視進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對檢視的修改不影響基本表。它使得我們獲取資料更容易，相比多表查詢。

 

  遊標：是對查詢出來的結果集作為一個單元來有效的處理。遊標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用遊標，但是需要逐條處理資料的時候，遊標顯得十分重要。

 

7。為管理業務培訓資訊，建立3個表：

     S(S#,SN,SD,SA)S#,SN,SD,SA分別代表學號，學員姓名，所屬單位，學員年齡

     C(C#,CN)C#,CN分別代表課程編號，課程名稱

     SC(S#,C#,G) S#,C#,G分別代表學號，所選的課程編號，學習成績

 （1）使用標準SQL巢狀語句查詢選修課程名稱為’稅收基礎’的學員學號和姓名?

      答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and  cn=’稅收基礎’)

  (2) 使用標準SQL巢狀語句查詢選修課程編號為’C2’的學員姓名和所屬單位?

答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’

 (3) 使用標準SQL巢狀語句查詢不選修課程編號為’C5’的學員姓名和所屬單位?

答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)

 

 (4)查詢選修了課程的學員人數

答：select 學員人數=count(distinct s#) from sc

 

(5) 查詢選修課程超過5門的學員學號和所屬單位?

答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct  c#)>5)

 

是查詢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 A

) T) order by A

 

要求是查詢表A中存在ID重複三次以上的記錄,完整的查詢語句如下：

select * from(select count(ID) as count from table group by ID)T where T.count>3

 

create table testtable1

(

 id int IDENTITY,

 department varchar(12)

)

 

select * from testtable1

insert into testtable1 values('設計')

insert into testtable1 values('市場')

insert into testtable1 values('售後')

/*

結果

id  department

1   設計

2   市場

3   售後

*/

create table testtable2

(

 id int IDENTITY,

 dptID int,

 name varchar(12)

)

insert into testtable2 values(1,'張三')

insert into testtable2 values(1,'李四')

insert into testtable2 values(2,'王五')

insert into testtable2 values(3,'彭六')

insert into testtable2 values(4,'陳七')

/*

用一條SQL語句，怎麼顯示如下結果

id  dptID  department  name

1   1      設計        張三

2   1      設計        李四

3   2      市場        王五

4   3      售後        彭六

5   4      黑人        陳七

*/

 

答案是：

 

SELECT testtable2.*  , ISNULL(department,'黑人')

FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

 

 

 

在面試應聘的SQL Server資料庫開發人員時，我運用了一套標準的基準技術問題。下面這些問題是我覺得能夠真正有助於淘汰不合格應聘者的問題。它們按照從易到難的順序排列。當你問到關於主鍵和外來鍵的問題時，後面的問題都十分有難度，因為答案可能會更難解釋和說明，尤其是在面試的情形下。

 

你能向我簡要敘述一下SQL Server 2000中使用的一些資料庫物件嗎?

 

你希望聽到的答案包括這樣一些物件:表格、檢視、使用者定義的函式，以及儲存過程;如果他們還能夠提到像觸發器這樣的物件就更好了。如果應聘者不能回答這個基本的問題，那麼這不是一個好兆頭。

 

NULL是什麼意思?

NULL(空)這個值是資料庫世界裡一個非常難纏的東西，所以有不少應聘者會在這個問題上跌跟頭您也不要覺得意外。 NULL這個值表示UNKNOWN(未知):它不表示“”(空字串)。假設您的SQL Server資料庫裡有ANSI_NULLS，當然在預設情況下會有，對NULL這個值的任何比較都會生產一個NULL值。您不能把任何值與一個 UNKNOWN值進行比較，並在邏輯上希望獲得一個答案。您必須使用IS NULL操作符。

 

什麼是索引?SQL Server 2000裡有什麼型別的索引?

 

任何有經驗的資料庫開發人員都應該能夠很輕易地回答這個問題。一些經驗不太多的開發人員能夠回答這個問題，但是有些地方會說不清楚。簡單地說，索引是一個資料結構，用來快速訪問資料庫表格或者檢視裡的資料。在SQL Server裡，它們有兩種形式:聚集索引和非聚集索引。聚集索引在索引的葉級儲存資料。這意味著不論聚集索引裡有表格的哪個(或哪些)欄位，這些欄位都會按順序被儲存在表格。由於存在這種排序，所以每個表格只會有一個聚集索引。非聚集索引在索引的葉級有一個行識別符號。這個行識別符號是一個指向磁碟上資料的指標。它允許每個表格有多個非聚集索引。

 

什麼是主鍵?什麼是外來鍵?

 

主鍵是表格裡的(一個或多個)欄位，只用來定義表格裡的行;主鍵裡的值總是唯一的。外來鍵是一個用來建立兩個表格之間關係的約束。這種關係一般都涉及一個表格裡的主鍵欄位與另外一個表格(儘管可能是同一個表格)裡的一系列相連的欄位。那麼這些相連的欄位就是外來鍵。

 

什麼是觸發器?SQL Server 2000有什麼不同型別的觸發器?

 

讓未來的資料庫開發人員知道可用的觸發器型別以及如何實現它們是非常有益的。觸發器是一種專用型別的儲存過程，它被捆綁到SQL Server 2000的表格或者檢視上。在SQL Server 2000裡，有INSTEAD-OF和AFTER兩種觸發器。INSTEAD-OF觸發器是替代資料操控語言(Data Manipulation

Language，DML)語句對錶格執行語句的儲存過程。例如，如果我有一個用於TableA的INSTEAD-OF-UPDATE

觸發器，同時對這個表格執行一個更新語句，那麼INSTEAD-OF-UPDATE觸發器裡的程式碼會執行，而不是我執行的更新語句則不會執行操作。

 

AFTER觸發器要在DML語句在資料庫裡使用之後才執行。這些型別的觸發器對於監視發生在資料庫表格裡的資料變化十分好用。

 

您如何確一個帶有名為Fld1欄位的TableB表格裡只具有Fld1欄位裡的那些值，而這些值同時在名為TableA的表格的Fld1欄位裡?

 

這個與關係相關的問題有兩個可能的答案。第一個答案(而且是您希望聽到的答案)是使用外來鍵限制。外來鍵限制用來維護引用的完整性。它被用來確保表格裡的欄位只儲存有已經在不同的(或者相同的)表格裡的另一個欄位裡定義了的值。這個欄位就是候選鍵(通常是另外一個表格的主鍵)。

 

另外一種答案是觸發器。觸發器可以被用來保證以另外一種方式實現與限制相同的作用，但是它非常難設定與維護，而且效能一般都很糟糕。由於這個原因，微軟建議開發人員使用外來鍵限制而不是觸發器來維護引用的完整性。

 

對一個投入使用的線上事務處理表格有過多索引需要有什麼樣的效能考慮?

 

你正在尋找進行與資料操控有關的應聘人員。對一個表格的索引越多，資料庫引擎用來更新、插入或者刪除資料所需要的時間就越多，因為在資料操控發生的時候索引也必須要維護。

 

你可以用什麼來確保表格裡的欄位只接受特定範圍裡的值?

 

這個問題可以用多種方式來回答，但是隻有一個答案是“好”答案。您希望聽到的回答是Check限制，它在資料庫表格裡被定義，用來限制輸入該列的值。

 

觸發器也可以被用來限制資料庫表格裡的欄位能夠接受的值，但是這種辦法要求觸發器在表格裡被定義，這可能會在某些情況下影響到效能。因此，微軟建議使用Check限制而不是其他的方式來限制域的完整性。

 

如果應聘者能夠正確地回答這個問題，那麼他的機會就非常大了，因為這表明他們具有使用儲存過程的經驗。

 

返回引數總是由儲存過程返回，它用來表示儲存過程是成功還是失敗。返回引數總是INT資料型別。

 

OUTPUT引數明確要求由開發人員來指定，它可以返回其他型別的資料，例如字元型和數值型的值。(可以用作輸出引數的資料型別是有一些限制的。)您可以在一個儲存過程裡使用多個OUTPUT引數，而您只能夠使用一個返回引數。

 

什麼是相關子查詢?如何使用這些查詢?

 

經驗更加豐富的開發人員將能夠準確地描述這種型別的查詢。相關子查詢是一種包含子查詢的特殊型別的查詢。查詢裡包含的子查詢會真正請求外部查詢的值，從而形成一個類似於迴圈的狀況。

 

什麼是SQL隱碼攻擊式攻擊？

所謂SQL隱碼攻擊式攻擊，就是攻擊者把SQL命令插入到Web表單的輸入域或頁面請求的查詢字串，欺騙伺服器執行惡意的SQL命令。在某些表單中，使用者輸入的內容直接用來構造（或者影響）動態SQL命令，或作為儲存過程的輸入引數，這類表單特別容易受到SQL隱碼攻擊式攻擊。常見的SQL隱碼攻擊式攻擊過程類如：

⑴ 某個ASP.NET Web應用有一個登入頁面，這個登入頁面控制著使用者是否有權訪問應用，它要求使用者輸入一個名稱和密碼。

⑵ 登入頁面中輸入的內容將直接用來構造動態的SQL命令，或者直接用作儲存過程的引數。下面是ASP.NET應用構造查詢的一個例子：

System.Text.StringBuilder query = new System.Text.StringBuilder(
   "SELECT * from Users WHERE login = '")
   .Append(txtLogin.Text).Append("' AND password='")
   .Append(txtPassword.Text).Append("'");

⑶ 攻擊者在使用者名稱字和密碼輸入框中輸入"'或'1'='1"之類的內容。

⑷ 使用者輸入的內容提交給伺服器之後，伺服器執行上面的ASP.NET程式碼構造出查詢使用者的SQL命令，但由於攻擊者輸入的內容非常特殊，所以最後得到的SQL命令變成：SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'。

⑸ 伺服器執行查詢或儲存過程，將使用者輸入的身份資訊和伺服器中儲存的身份資訊進行對比。

⑹ 由於SQL命令實際上已被注入式攻擊修改，已經不能真正驗證使用者身份，所以系統會錯誤地授權給攻擊者。

如果攻擊者知道應用會將表單中輸入的內容直接用於驗證身份的查詢，他就會嘗試輸入某些特殊的SQL字串篡改查詢改變其原來的功能，欺騙系統授予訪問許可權。

系統環境不同，攻擊者可能造成的損害也不同，這主要由應用訪問資料庫的安全許可權決定。如果使用者的帳戶具有管理員或其他比較高階的許可權，攻擊者就可能對資料庫的表執行各種他想要做的操作，包括新增、刪除或更新資料，甚至可能直接刪除表

如何防範SQL隱碼攻擊式攻擊？

好在要防止ASP.NET應用被SQL隱碼攻擊式攻擊闖入並不是一件特別困難的事情，只要在利用表單輸入的內容構造SQL命令之前，把所有輸入內容過濾一番就可以了。過濾輸入內容可以按多種方式進行。

⑴ 對於動態構造SQL查詢的場合，可以使用下面的技術：

第一：替換單引號，即把所有單獨出現的單引號改成兩個單引號，防止攻擊者修改SQL命令的含義。再來看前面的例子，"SELECT * from Users WHERE login = ''' or ''1''=''1' AND password = ''' or ''1''=''1'"顯然會得到與"SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'"不同的結果。

第二：刪除使用者輸入內容中的所有連字元，防止攻擊者構造出類如"SELECT * from Users WHERE login = 'mas' -- AND password =''"之類的查詢，因為這類查詢的後半部分已經被註釋掉，不再有效，攻擊者只要知道一個合法的使用者登入名稱，根本不需要知道使用者的密碼就可以順利獲得訪問許可權。

第三：對於用來執行查詢的資料庫帳戶，限制其許可權。用不同的使用者帳戶執行查詢、插入、更新、刪除操作。由於隔離了不同帳戶可執行的操作，因而也就防止了原本用於執行SELECT命令的地方卻被用於執行INSERT、UPDATE或DELETE命令。

⑵ 用儲存過程來執行所有的查詢。SQL引數的傳遞方式將防止攻擊者利用單引號和連字元實施攻擊。此外，它還使得資料庫許可權可以限制到只允許特定的儲存過程執行，所有的使用者輸入必須遵從被呼叫的儲存過程的安全上下文，這樣就很難再發生注入式攻擊了。

⑶ 限制表單或查詢字串輸入的長度。如果使用者的登入名字最多隻有10個字元，那麼不要認可表單中輸入的10個以上的字元，這將大大增加攻擊者在SQL命令中插入有害程式碼的難度。

⑷ 檢查使用者輸入的合法性，確信輸入的內容只包含合法的資料。資料檢查應當在客戶端和伺服器端都執行——之所以要執行伺服器端驗證，是為了彌補客戶端驗證機制脆弱的安全性。

在客戶端，攻擊者完全有可能獲得網頁的原始碼，修改驗證合法性的指令碼（或者直接刪除指令碼），然後將非法內容通過修改後的表單提交給伺服器。因此，要保證驗證操作確實已經執行，唯一的辦法就是在伺服器端也執行驗證。你可以使用許多內建的驗證物件，例如 RegularExpressionValidator，它們能夠自動生成驗證用的客戶端指令碼，當然你也可以插入伺服器端的方法呼叫。如果找不到現成的驗證物件，你可以通過CustomValidator自己建立一個。

⑸ 將使用者登入名稱、密碼等資料加密儲存。加密使用者輸入的資料，然後再將它與資料庫中儲存的資料比較，這相當於對使用者輸入的資料進行了"消毒"處理，使用者輸入的資料不再對資料庫有任何特殊的意義，從而也就防止了攻擊者注入SQL命令。 System.Web.Security.FormsAuthentication類有一個 HashPasswordForStoringInConfigFile，非常適合於對輸入資料進行消毒處理。

⑹ 檢查提取資料的查詢所返回的記錄數量。如果程式只要求返回一個記錄，但實際返回的記錄卻超過一行，那就當作出錯處理