作者：牧小熊，華中農業大學，Datawhale成員

有讀者留言面試有點虛，數據庫都忘的差不多了，與其臨時抱佛腳，不如我們把MySQL的知識點梳理一遍，心中有知識點，面試不慌。

數據庫的話我只對MySQL有些熟悉，因此我們以MySQL為主進行知識點的整理。

MySQL知識點學習的開源教程：
https://github.com/datawhalechina/team-learning-sql/

由于作者水平的限制和可能的整理疏漏，有錯誤和不足的地方煩請指出。

MySQL數據庫-基礎知識

1.說說主鍵、外鍵、超鍵、候選鍵的差別并舉例

• 超鍵(super key): 在關系中能唯一標識元組的屬性集稱為關系模式的超鍵。

• 候選鍵(candidate key): 不含有多余屬性的超鍵稱為候選鍵。也就是在候選鍵中，若再刪除屬性，就不是鍵了！

• 主鍵(primary key): 用戶選作元組標識的一個候選鍵程序主鍵。

• 外鍵(foreign key)：如果關系模式R中屬性K是其它模式的主鍵，那么k在模式R中稱為外鍵。

舉個例子，對于 學生信息（學號 身份證號 性別 年齡 身高 體重 宿舍號）和 宿舍信息（宿舍號 樓號）：

• 超鍵：只要含有“學號”或者“身份證號”兩個屬性的集合就叫超鍵，例如R1（學號 性別）、R2（身份證號 身高）、R3（學號 身份證號）等等都可以稱為超鍵！

• 候選鍵：不含有多余的屬性的超鍵，比如（學號）、（身份證號）都是候選鍵，又比如R1中學號這一個屬性就可以唯一標識元組了，而有沒有性別這一屬性對是否唯一標識元組沒有任何的影響！

• 主鍵：就是用戶從很多候選鍵選出來的一個鍵就是主鍵，比如你要求學號是主鍵，那么身份證號就不可以是主鍵了！

• 外鍵：宿舍號就是學生信息表的外鍵。

2.為什么一般用自增列作為主鍵？

• 如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁。

• 如果使用非自增主鍵（如果身份證號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新記錄都要被插到現有索引頁的中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數據，這增加了很多開銷，同時會增加大量的碎片。

3.觸發器的作用？

• 安全性，可以基于數據庫的值使用戶具有操作數據庫的某種權利。

• 審計，可以跟蹤用戶對數據庫的操作。

• 實現復雜的非標準的數據庫相關完整性規則，觸發器可以對數據庫中相關的表進行連環更新。

• 觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。當插入一個與其主鍵不匹配的外部鍵時，這種觸發器會起作用。

4.什么是存儲過程？優缺點是什么？與函數的區別是什么？

存儲過程是一個預編譯的SQL語句，優點是允許模塊化的設計，就是說只需創建一次，以后在該程序中就可以調用多次。

優點：

• 存儲過程是預編譯過的，執行效率高。

• 存儲過程的代碼直接存放于數據庫中，通過存儲過程名直接調用，減少網絡通訊。

• 安全性高，執行存儲過程需要有一定權限的用戶。

• 存儲過程可以重復使用，可減少工作量冗余。

缺點：移植性差

與函數的區別:

• 存儲過程用戶在數據庫中完成特定操作或者任務（如插入，刪除等），函數用于返回特定的數據。

• 存儲過程聲明用procedure，函數用function。

• 存儲過程不需要返回類型，函數必須要返回類型。

• 存儲過程可作為獨立的pl-sql執行，函數不能作為獨立的plsql執行，必須作為表達式的一部分。

• 存儲過程只能通過out和in/out來返回值，函數除了可以使用out，in/out以外，還可以使用return返回值。

• sql語句（DML或SELECT)中不可用調用存儲過程，而函數可以。

5.什么是視圖，優缺點是什么？

視圖：是一種虛擬的表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改會影響基本表。

優點：

• 對數據庫的訪問，因為視圖可以有選擇性的選取數據庫里的一部分。

• 用戶通過簡單的查詢可以從復雜查詢中得到結果。

• 維護數據的獨立性，試圖可從多個表檢索數據。

• 對于相同的數據可產生不同的視圖。

缺點：

查詢視圖時，必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，那么就無法更改數據。

6.說說drop、truncate、 delete區別

• drop直接刪掉表。

• truncate刪除表中數據，再插入時自增長id又從1開始。

• delete刪除表中數據，可以加where字句。

7.什么是臨時表，臨時表什么時候刪除？

臨時表可以手動刪除：

DROP TEMPORARY TABLE IF TEXITS temp_tb

臨時表只在當前連接可見，當關閉連接時，MySQL會自動刪除表并釋放所有空間。因此在不同的連接中可以創建同名的臨時表，并且操作屬于本連接的臨時表。創建臨時表的語法與創建表語法類似，不同之處是增加關鍵字TEMPORARY：

CREATE TEMPORARY TABLE tmp_table ( NAME VARCHAR (10) NOT NULL, time date NOT NULL ); select * from tmp_table;

8.關系型數據庫和非關系型數據庫的優劣？

非關系型數據庫以redis為例，NOSQL是基于鍵值對的，而且不需要經過SQL層的解析，所以性能高，查詢速度快。同時由于是鍵值對，數據之間沒有耦合，容易水平擴展。

關系數據庫：使用SQL語句方便在多個表之間做復雜查詢，同時有較好的事務支持，支持對安全性有一定要求的數據訪問。

9.什么是數據庫范式？

• 第一范式:(確保每列保持原子性)所有字段值都是不可分解的原子值。

• 第二范式:(確保表中的每列都和主鍵相關)在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中，數據表里的非主屬性都要和這個數據表的候選鍵有完全依賴關系。

• 第三范式:(確保每列都和主鍵列直接相關,而不是間接相關) 數據表中的每一列數據都和主鍵直接相關，而不能間接相關。

• 第四范式:要求把同一表內的多對多關系刪除。

• 第五范式:從最終結構重新建立原始結構。

需要注意的是，遵循數據庫范式會一定程度影響數據庫的查詢效率，因此會存在反范式的優化。

10.什么是 內連接、外連接、交叉連接、笛卡爾積等?

• 內連接: 只連接匹配的行。

• 左外連接: 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行。

• 右外連接: 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行。

• 全外連接: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。

• 交叉連接: 生成笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個數據源中的每個行與另一個數據源的每個行都一一匹配。

11.varchar和char的區別？

• char的長度是不可變的，而varchar的長度是可變的。

• char的存取速度還是要比varchar要快得多，因為其長度固定，方便程序的存儲與查找。

• char的存儲方式是：對英文字符（ASCII）占用1個字節，對一個漢字占用兩個字節。varchar的存儲方式是：對每個英文字符占用2個字節，漢字也占用2個字節。

12.說說like % - 的區別

• %百分號通配符:表示任何字符出現任意次數(可以是0次)。

• 下劃線通配符:表示只能匹配單個字符,不能多也不能少,就是一個字符。

• like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配進行比較。

13.索引、索引的作用和索引的優缺點是什么，什么樣的字段適合建索引？

數據庫索引，是數據庫管理系統中一個排序的數據結構，索引的實現通常使用B樹及其變種B+樹。在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據，這樣就可以在這些數據結構上實現高級查找算法。這種數據結構，就是索引。

索引的作用：協助快速查詢、更新數據庫表中數據。

索引的優點：

• 索引可以保證數據每一行的唯一性

• 加快數據的檢索速度

缺點：

• 創建和維護索引需要時間

• 索引需要占用物理空間，增加空間成本

• 對數據進行增、刪、改的時候需要動態維護

唯一的，不為空的，經常被查詢的字段適合建立索引

14.B+樹的索引和Hash索引的區別？

hash索引是鍵值對的索引，檢索效率非常高；B+樹索引需要從根節點到枝節點索引，最后才能訪問到數據。

為什么不都用Hash索引而使用B+樹索引？

Hash索引僅僅能滿足"=","IN"和""查詢，不能使用范圍查詢，hash是索引也不能用來做排序操作，hash的索引不能利用部分索引鍵查詢。

15.B樹和B+樹的區別，為什么MySQL會用B+樹?

B樹B+樹

B樹，每個節點都存儲key和data，所有節點組成這棵樹，并且葉子節點指針為nul，葉子結點不包含任何關鍵字信息。

B+樹，所有的葉子結點中包含了全部關鍵字的信息，及指向含有這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大的順序鏈接，所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。

為什么MySQL用B+樹呢？

• B+ 樹查詢效率更穩定（因為因為B+ 樹每次只有訪問到葉子節點才能找到對應的數據 而在 B 樹中 非葉子節點也會存儲數據）

• B+ 樹的查詢效率更高 （因為通常B+ 樹比 B 樹更矮胖 階數更大 深度更低 查詢所需要的磁盤 I/O 也會更少 。同樣的磁盤頁大小 B+ 樹可以存儲更多的節點關鍵字）

• 對索引進行范圍查詢時 B+ 樹效率也更高（因為所有關鍵字都出現在B+ 樹的葉子節點中 并通過有序鏈表進行了鏈接 。而在 B 樹中則需要通過中序遍歷才能完成范圍查找 效率要低很多）

關于B+樹的索引可以參考五分鐘學算法的講解：【面試現場】為什么MySQL數據庫要用B+樹存儲索引？

MySQL數據庫-專業知識

16.Mysql中有哪幾種鎖？

• 行級鎖：鎖定力度小，發生鎖沖突概率低，實現并發度高，開銷大，加鎖慢，并發度高。

• 頁級鎖：加鎖時間比行鎖長，頁級鎖開銷介于表鎖和行鎖之間，會出現死鎖，并發度一般。

• 表級鎖：開銷小，加鎖快。

17.Mysql中默認事務隔離級別是？

• 讀未提交(RU): 一個事務還沒提交時, 它做的變更就能被別的事務看到。

• 讀提交(RC): 一個事務提交之后, 它做的變更才會被其他事務看到。

• 可重復讀(RR): 一個事務執行過程中看到的數據, 總是跟這個事務在啟動時看到的數據是一致的。當然在可重復讀隔離級別下, 未提交變更對其他事務也是不可見的。

• 串行化(S): 對于同一行記錄, 讀寫都會加鎖. 當出現讀寫鎖沖突的時候, 后訪問的事務必須等前一個事務執行完成才能繼續執行。

18.Mysql數據庫表類型有哪些？

MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。

MyISAM：成熟、穩定、易于管理，快速讀取。一些功能不支持（事務等），表級鎖。

InnoDB：支持事務、外鍵等特性、數據行鎖定。空間占用大，不支持全文索引等。

19.簡述mysql的MVCC機制

MVCC是一種多版本并發控制機制，是MySQL的InnoDB存儲引擎實現隔離級別的一種具體方式，用于實現提交讀和可重復讀這兩種隔離級別。

MVCC實現原理。通過保存數據在某個時間點的快照來實現該機制，其在每行記錄后面保存兩個隱藏的列，分別保存這個行的創建版本號和刪除版本號，然后Innodb的MVCC使用到的快照存儲在Undo日志中，該日志通過回滾指針把一個數據行所有快照連接起來。

20.簡述MySQL 兩種常見存儲引擎：MyISAM與InnoDB

目前MySQL默認的存儲引擎是InnoDB。

現在大多數時候我們使用的都是InnoDB，但是在某些情況下使用MyISAM更好，比如：MyISAM更適合讀密集的表，而InnoDB更適合寫密集的的表。在數據庫做主從分離的情況下，經常選擇MyISAM作為主庫的存儲引擎。

二者的常見對比

• count運算上的區別：因為MyISAM緩存有表meta-data（行數等），因此在做COUNT(*)時對于一個結構很好的查詢是不需要消耗多少資源的。而對于InnoDB來說，則沒有這種緩存。

• 是否支持事務和崩潰后的安全恢復：MyISAM 強調的是性能，每次查詢具有原子性,其執行數度比InnoDB類型更快，但是不提供事務支持。但是InnoDB 提供事務支持事務，外部鍵等高級數據庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

• 是否支持外鍵：MyISAM不支持，而InnoDB支持。

往期精彩回顧適合初學者入門人工智能的路線及資料下載機器學習及深度學習筆記等資料打印機器學習在線手冊深度學習筆記專輯《統計學習方法》的代碼復現專輯 AI基礎下載機器學習的數學基礎專輯 本站qq群851320808，加入微信群請掃碼：

總結

以上是生活随笔為你收集整理的【数据分析】数据分析基础：SQL重要知识点梳理！的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。