面試官常問的資料庫問題(一)

面試官常問的資料庫問題 1–10

1. 觸發器的作用?
2. 什麼是儲存過程?用什麼來呼叫?
3. 索引的作用及優缺點是什麼?
4. 什麼叫檢視?遊標是什麼?
5. delete、drop、truncate 區別
6. 如何進行 SQL 最佳化?
7. MySQL 中的 varchar 和 char 有什麼區別?(重點)
8. 在哪些情況下會發生針對該列建立了索引,但是在查詢的時候並沒有使用呢?
9. 為什麼要儘量設定一個主鍵?
10.Mysql 資料庫與 Oracle 資料庫有什麼區別?

1. 觸發器的作用?

特殊的儲存過程。可強化約束,維護資料完整性與統一性。

何為觸發器?

在SQLServer裡面也就是對某一個表的一定的操作,觸發某種條件,從而執行的一段程式。
觸發器是一種特殊的儲存過程,主要是透過事件來觸發而被執行的。
它可以強化約束,來維護資料的完整性和一致性,可以跟蹤資料庫內的操作從而不允許未經許可的更新和變化,可以聯級運算。
常見的觸發器有三種:分別應用於Insert,Delete,Update事件。
如,某表上的觸發器上包含對另一個表的資料操作,而該操作又會導致該表觸發器被觸發。

2. 什麼是儲存過程?用什麼來呼叫?

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

呼叫:
1)可以用一個命令物件來呼叫儲存過程。
2)可以供外部程式呼叫,比如:java程式。

3. 索引的作用及優缺點是什麼?

索引的作用:建立索引可提高系統的效能
優:加快資料檢索;可保證資料唯一;
缺:建立和維護索引需要時間;佔空間;

索引的作用:建立索引能夠大大的提高系統的效能
優點:
①透過建立唯一性索引,可以保證資料庫表中每一行資料的唯一性
②大大加快資料的檢索速度,這也是建立索引的最主要原因
③保證了表與表之間的連線,在實現資料的參考完整性方面特別有意義
④在使用分組和排序,子句進行資料檢索時,同樣可以顯著減少查詢中分組和排序的時間
⑤透過使用索引,可以在查詢的過程中,使用最佳化隱藏器,提高系統的效能。

缺點:
①建立索引和維護索引需要時間,這種時間隨著資料量的增加而增加
②索引需要佔物理空間,除了資料表佔資料空間之外,每一個索引還要佔物理空間,如果要建立聚簇索引,需要的空間更大
③當對錶中的資料進行增加、刪除和修改的時候,索引也要動態的維護,這就降低了資料的維護速度。

索引是建立在資料庫的表中的列上。因此,在建立索引的時候,要考慮哪些列上適合加索引,那些列上不適合加索引。
搜尋引擎技術es

4. 什麼叫檢視?遊標是什麼?

檢視:一種虛擬的表,具有和物理表相同的功能,我們一般做查詢(檢視查詢相較於多表查詢更容易獲取資料)。

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

遊標:是對查詢出來的結果集作為一個單元來有效的處理。可定在該單元中的特定行,從結果集的當前行檢索一行或多行;可對結果集當前行做修改。

一般不使用遊標,但是需要逐條處理資料的時候,遊標顯得十分重要。

5. delete、drop、truncate 區別

delete 刪除後可恢復,一般刪除部分資料(刪全部太慢了)
truncate、delete 只刪除資料,不刪除表結構,drop 刪除表結構,且釋放所佔的空間;
刪除資料的速度,drop>truncate>delete ;
delete 屬於 DML 語言,需要事務管理,commit 之後才能生效,drop 和 truncate 屬於 DDL 語言,操作立刻生效,不可回滾;
使用場合:
不再需要該表時, 用drop;刪除所有記錄但保留該表時, 用truncate;刪除部分記錄時用delete。

6. 如何進行 SQL 最佳化?

建儲存索引;最佳化欄位資料型別;為欄位新增索引;不使用select * ;使用ENUM;使用NOTNULL;固定表長度;

(1)選擇正確的儲存引
以 MySQL 為例,包括有兩個儲存引擎 MyISAM 和 InnoDB,每個引擎都有利有弊。MyISAM 適合於一些需要大量查詢的應用,但其對於有大量寫操作並不是很好。甚至你只是需要 update 一個欄位,整個表都會被鎖起來,而別的程序,就算是讀程序都無法操作直到讀操作完成。另外,MyISAM 對於 SELECTCOUNT(*) 這類的計算是超快無比的。InnoDB 的趨勢會是一個非常複雜的儲存引擎,對於一些小的應用,它會比 MyISAM 還慢。但是它支援“行鎖” ,於是在寫操作比較多的時候,會更優秀。並且,他還支援更多的高階應用,比如:事務。

(2)最佳化欄位的資料型別。
記住一個原則,越小的列會越快。如果一個表只會有幾列罷了(比如說字典表,配置表),那麼,我們就沒有理由使用 INT 來做主鍵,使用 MEDIUMINT,SMALLINT 或是更小的 TINYINT 會更經濟一些。
如果你不需要記錄時間,使用 DATE 要比 DATETIME 好得多。當然,你也需要留夠足夠的擴充套件空間。

(3)為搜尋欄位新增索引,索引並不一定就是給主鍵或是唯一的欄位。
如果在你的表中,有某個欄位你總要會經常用來做搜尋,那麼最好是為其建立索引,
除非你要搜尋的欄位是大的文字欄位,那應該建立全文索引。

(4)避免使用 Select * 。
從資料庫裡讀出越多的資料,那麼查詢就會變得越慢。並且,如果你的資料庫伺服器和 WEB 伺服器是兩臺獨 立的伺服器的話,這還會增加網路傳輸的負載。即使你要查詢資料表的所有欄位,也儘量不要用*萬用字元,善用內建提供的欄位排除定 義也許能給帶來更多的便利。

(5)使用 ENUM 而不是 VARCHAR 。
ENUM 型別是非常快和緊湊的。在實際上,其儲存的是 TINYINT,但其外表上顯示為字串。這樣一來,用這個欄位來做一些選 項列表變得相當的完美。例如,性別、民族、部門和狀態之類的這些欄位的取值是有限而且固定的,那麼,你應該使用 ENUM 而不是 VARCHAR。

(6)儘可能的使用 NOTNULL 。
除非你有一個很特別的原因去使用 NULL 值,你應該總是讓你的欄位保持 NOTNULL。NULL 其實需要額外的空間,並且,在你進行比較的時候,你的程式會更復雜。當然,這裡並不是說你就不能使用 NULL 了,現實情況是很複雜的,依然會有些情況下,你需要 使用 NULL 值。

(7)固定長度的表會更快。
如果表中的所有欄位都是“固定長度”的,整個表會被認為是 “static” 或 “fixed-length”。例如,表中沒有如下型別的欄位:VARCHAR,TEXT,BLOB。只要你包括了其中一個這些欄位,那麼這個表就不是“固定長度靜態表”了,這樣,MySQL 引擎會用另一種方法來處理。
固定長度的表會提高效能,因為 MySQL 搜尋得會更快一些,因為這些固定的長度是很容易計算下一個資料的偏移量的,所以讀取的自然也會很快。而如果欄位不是定長的,那麼,每一次要找下一條的話,需要程式找到主鍵。
並且,固定長度的表也更容易被快取和重建。不過,唯一的副作用是,固定長度的欄位會浪費一些空間,因為定長的欄位無論你
用不用,他都是要分配那麼多的空間。

7. MySQL 中的 varchar 和 char 有什麼區別?(重點)

char:定長欄位;varchar:可變欄位;
檢索效率:char>varchar(確定某欄位值的長度時,用char);

char 是一個定長欄位,假如申請了 char(10)的空間,那麼無論實際儲存多少內容,該欄位都佔用 10 個字元;
而 varchar 是變長的,也就是說申請的只是最大長度,佔用的空間為實際字元長度+1,最後一個字元儲存使用了多長的空間。

在檢索效率上來講,char>varchar;
因此在使用中,如果確定某個欄位的值的長度(shiro 鹽加密),可以使用char,否則應該儘量使用varchar。例如儲存用 戶 MD5 加密後的密碼,則應該使用 char。

8. 在哪些情況下會發生針對該列建立了索引,但是在查詢的時候並沒有使用呢?

使用不等於查詢;
列參與了數學運算或者函式;
使用 like 時 ,左邊是萬用字元,類似於’%aaa’;
當mysql分析全表掃描比使用索引快的時候不使用索引;
當使用聯合索引,前面一個條件為範圍查詢,後面的即使符合最左字首原則,也無法使用索引;

以上情況,MySQL無法使用索引!

9. 為什麼要儘量設定一個主鍵?

保證資料的唯一性
主鍵是資料庫確保資料行在整張表唯一性的保障,即使業務上本張表沒有主鍵,也建議新增一個自增長的 ID 列作為主鍵,設定了主鍵 之後,在後續的刪改查的時候可能更加快速以及確保操作資料範圍安全。

10.Mysql 資料庫與 Oracle 資料庫有什麼區別?

MySql:中小型資料庫;可自動增長(有自動增長的=資料型別);group by 方法可隨意用;可用單引號、雙引號包起字串;
Oracle:大型資料庫;無自動增長(需要建立自增序列);當查詢語句中有組函式,則其他列必須是組函式處理過的或者是 group by 子句中的列,否則會報錯;只可用單引號包起字串;

﹣應用方面:Mysql 是中小型應用的資料庫。一般用於個人和中小型企業。Oracle 屬於大型資料庫,一般用於具有相當規模的企 業應用。
﹣自動增長的資料型別方面:MySQL 有自動增長的資料型別。Oracle 沒有自動增長的資料型別。需要建立一個自增序列。
﹣group by 用法:Mysql 中 group by 在 SELECT 語句中可以隨意使用,但在 ORACLE 中如果查詢語句中有組函式,那麼其他列必須是組函式處理過的或者是 group by 子句中的列,否則會報錯。
﹣引導方面:MySQL 中可以用單引號、雙引號包起字串,Oracle 中只可以用單引號包起字串

※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

我是「數據分析那些事」。常年分享數據分析乾貨,不定期分享好用的職場技能工具。各位也可以關注我的Facebook,按讚我的臉書並私訊「10」,送你十週入門數據分析電子書唷!期待你與我互動起來~

文章推選

推薦10個鮮為人知的免費學習網站

從0到1,掌握資料視覺化的基本技巧

靠著零程式碼報表工具,轉行報表開發後月薪漲一倍

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/