SQL重要知識點梳理！
數(shù)據(jù)庫的話我只對MySQL有些熟悉，因此我們以MySQL為主進行知識點的整理。
MySQL知識點學(xué)習(xí)的開源教程：
https://github.com/datawhalechina/team-learning-sql/
由于作者水平的限制和可能的整理疏漏，有錯誤和不足的地方煩請指出。
MySQL數(shù)據(jù)庫-基礎(chǔ)知識
1.說說主鍵、外鍵、超鍵、候選鍵的差別并舉例
超鍵(super key): 在關(guān)系中能唯一標(biāo)識元組的屬性集稱為關(guān)系模式的超鍵。 候選鍵(candidate key): 不含有多余屬性的超鍵稱為候選鍵。也就是在候選鍵中，若再刪除屬性，就不是鍵了！ 主鍵(primary key): 用戶選作元組標(biāo)識的一個候選鍵程序主鍵。 外鍵(foreign key)：如果關(guān)系模式R中屬性K是其它模式的主鍵，那么k在模式R中稱為外鍵。
舉個例子，對于 學(xué)生信息（學(xué)號 身份證號 性別 年齡 身高 體重 宿舍號）和 宿舍信息（宿舍號 樓號）：
超鍵：只要含有“學(xué)號”或者“身份證號”兩個屬性的集合就叫超鍵，例如R1（學(xué)號 性別）、R2（身份證號 身高）、R3（學(xué)號 身份證號）等等都可以稱為超鍵！ 候選鍵：不含有多余的屬性的超鍵，比如（學(xué)號）、（身份證號）都是候選鍵，又比如R1中學(xué)號這一個屬性就可以唯一標(biāo)識元組了，而有沒有性別這一屬性對是否唯一標(biāo)識元組沒有任何的影響！ 主鍵：就是用戶從很多候選鍵選出來的一個鍵就是主鍵，比如你要求學(xué)號是主鍵，那么身份證號就不可以是主鍵了！ 外鍵：宿舍號就是學(xué)生信息表的外鍵。
2.為什么一般用自增列作為主鍵？
如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當(dāng)前索引節(jié)點的后續(xù)位置，當(dāng)一頁寫滿，就會自動開辟一個新的頁。 如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機，因此每次新記錄都要被插到現(xiàn)有索引頁的中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，這增加了很多開銷，同時會增加大量的碎片。
3.觸發(fā)器的作用？
安全性，可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。 審計，可以跟蹤用戶對數(shù)據(jù)庫的操作。 實現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則，觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個與其主鍵不匹配的外部鍵時，這種觸發(fā)器會起作用。
4.什么是存儲過程？優(yōu)缺點是什么？與函數(shù)的區(qū)別是什么？
存儲過程是一個預(yù)編譯的SQL語句，優(yōu)點是允許模塊化的設(shè)計，就是說只需創(chuàng)建一次，以后在該程序中就可以調(diào)用多次。
優(yōu)點：
存儲過程是預(yù)編譯過的，執(zhí)行效率高。 存儲過程的代碼直接存放于數(shù)據(jù)庫中，通過存儲過程名直接調(diào)用，減少網(wǎng)絡(luò)通訊。 安全性高，執(zhí)行存儲過程需要有一定權(quán)限的用戶。 存儲過程可以重復(fù)使用，可減少工作量冗余。
缺點：移植性差
與函數(shù)的區(qū)別:
存儲過程用戶在數(shù)據(jù)庫中完成特定操作或者任務(wù)（如插入，刪除等），函數(shù)用于返回特定的數(shù)據(jù)。 存儲過程聲明用procedure，函數(shù)用function。 存儲過程不需要返回類型，函數(shù)必須要返回類型。 存儲過程可作為獨立的pl-sql執(zhí)行，函數(shù)不能作為獨立的plsql執(zhí)行，必須作為表達式的一部分。 存儲過程只能通過out和in/out來返回值，函數(shù)除了可以使用out，in/out以外，還可以使用return返回值。 sql語句（DML或SELECT)中不可用調(diào)用存儲過程，而函數(shù)可以。
5.什么是視圖，優(yōu)缺點是什么？
視圖：是一種虛擬的表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改會影響基本表。
優(yōu)點：
對數(shù)據(jù)庫的訪問，因為視圖可以有選擇性的選取數(shù)據(jù)庫里的一部分。 用戶通過簡單的查詢可以從復(fù)雜查詢中得到結(jié)果。 維護數(shù)據(jù)的獨立性，試圖可從多個表檢索數(shù)據(jù)。 對于相同的數(shù)據(jù)可產(chǎn)生不同的視圖。
缺點：
查詢視圖時，必須把視圖的查詢轉(zhuǎn)化成對基本表的查詢，如果這個視圖是由一個復(fù)雜的多表查詢所定義，那么，那么就無法更改數(shù)據(jù)。
6.說說drop、truncate、 delete區(qū)別
drop直接刪掉表。 truncate刪除表中數(shù)據(jù)，再插入時自增長id又從1開始。 delete刪除表中數(shù)據(jù)，可以加where字句。
7.什么是臨時表，臨時表什么時候刪除？
臨時表可以手動刪除：
DROP TEMPORARY TABLE IF TEXITS temp_tb
臨時表只在當(dāng)前連接可見，當(dāng)關(guān)閉連接時，MySQL會自動刪除表并釋放所有空間。因此在不同的連接中可以創(chuàng)建同名的臨時表，并且操作屬于本連接的臨時表。創(chuàng)建臨時表的語法與創(chuàng)建表語法類似，不同之處是增加關(guān)鍵字TEMPORARY：
CREATE TEMPORARY TABLE tmp_table (
NAME VARCHAR (10) NOT NULL,
time date NOT NULL
);
select * from tmp_table;
8.關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫的優(yōu)劣？
非關(guān)系型數(shù)據(jù)庫以redis為例，NOSQL是基于鍵值對的，而且不需要經(jīng)過SQL層的解析，所以性能高，查詢速度快。同時由于是鍵值對，數(shù)據(jù)之間沒有耦合，容易水平擴展。
關(guān)系數(shù)據(jù)庫：使用SQL語句方便在多個表之間做復(fù)雜查詢，同時有較好的事務(wù)支持，支持對安全性有一定要求的數(shù)據(jù)訪問。
9.什么是數(shù)據(jù)庫范式？
第一范式:(確保每列保持原子性)所有字段值都是不可分解的原子值。 第二范式:(確保表中的每列都和主鍵相關(guān))在一個數(shù)據(jù)庫表中，一個表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中，數(shù)據(jù)表里的非主屬性都要和這個數(shù)據(jù)表的候選鍵有完全依賴關(guān)系。 第三范式:(確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)) 數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。 第四范式:要求把同一表內(nèi)的多對多關(guān)系刪除。 第五范式:從最終結(jié)構(gòu)重新建立原始結(jié)構(gòu)。
需要注意的是，遵循數(shù)據(jù)庫范式會一定程度影響數(shù)據(jù)庫的查詢效率，因此會存在反范式的優(yōu)化。
10.什么是 內(nèi)連接、外連接、交叉連接、笛卡爾積等?
內(nèi)連接: 只連接匹配的行。 左外連接: 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行。 右外連接: 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行。 全外連接: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。 交叉連接: 生成笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個數(shù)據(jù)源中的每個行與另一個數(shù)據(jù)源的每個行都一一匹配。
11.varchar和char的區(qū)別？
char的長度是不可變的，而varchar的長度是可變的。 char的存取速度還是要比varchar要快得多，因為其長度固定，方便程序的存儲與查找。 char的存儲方式是：對英文字符（ASCII）占用1個字節(jié)，對一個漢字占用兩個字節(jié)。varchar的存儲方式是：對每個英文字符占用2個字節(jié)，漢字也占用2個字節(jié)。
12.說說like % - 的區(qū)別
%百分號通配符:表示任何字符出現(xiàn)任意次數(shù)(可以是0次)。 下劃線通配符:表示只能匹配單個字符,不能多也不能少,就是一個字符。 like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配進行比較。
13.索引、索引的作用和索引的優(yōu)缺點是什么，什么樣的字段適合建索引？
數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，索引的實現(xiàn)通常使用B樹及其變種B+樹。在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。
索引的作用：協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。
索引的優(yōu)點：
索引可以保證數(shù)據(jù)每一行的唯一性 加快數(shù)據(jù)的檢索速度
缺點：
創(chuàng)建和維護索引需要時間 索引需要占用物理空間，增加空間成本 對數(shù)據(jù)進行增、刪、改的時候需要動態(tài)維護
唯一的，不為空的，經(jīng)常被查詢的字段適合建立索引
14.B+樹的索引和Hash索引的區(qū)別？
hash索引是鍵值對的索引，檢索效率非常高；B+樹索引需要從根節(jié)點到枝節(jié)點索引，最后才能訪問到數(shù)據(jù)。
為什么不都用Hash索引而使用B+樹索引？
Hash索引僅僅能滿足"=","IN"和""查詢，不能使用范圍查詢，hash是索引也不能用來做排序操作，hash的索引不能利用部分索引鍵查詢。
15.B樹和B+樹的區(qū)別，為什么MySQL會用B+樹?
B樹，每個節(jié)點都存儲key和data，所有節(jié)點組成這棵樹，并且葉子節(jié)點指針為nul，葉子結(jié)點不包含任何關(guān)鍵字信息。
B+樹，所有的葉子結(jié)點中包含了全部關(guān)鍵字的信息，及指向含有這些關(guān)鍵字記錄的指針，且葉子結(jié)點本身依關(guān)鍵字的大小自小而大的順序鏈接，所有的非終端結(jié)點可以看成是索引部分，結(jié)點中僅含有其子樹根結(jié)點中最大（或最小）關(guān)鍵字。
為什么MySQL用B+樹呢？
B+ 樹查詢效率更穩(wěn)定（因為因為B+ 樹每次只有訪問到葉子節(jié)點才能找到對應(yīng)的數(shù)據(jù) 而在 B 樹中 非葉子節(jié)點也會存儲數(shù)據(jù)） B+ 樹的查詢效率更高 （因為通常B+ 樹比 B 樹更矮胖 階數(shù)更大 深度更低 查詢所需要的磁盤 I/O 也會更少 。同樣的磁盤頁大小 B+ 樹可以存儲更多的節(jié)點關(guān)鍵字） 對索引進行范圍查詢時 B+ 樹效率也更高（因為所有關(guān)鍵字都出現(xiàn)在B+ 樹的葉子節(jié)點中 并通過有序鏈表進行了鏈接 。而在 B 樹中則需要通過中序遍歷才能完成范圍查找 效率要低很多）
關(guān)于B+樹的索引可以參考五分鐘學(xué)算法的講解：【面試現(xiàn)場】為什么MySQL數(shù)據(jù)庫要用B+樹存儲索引？
MySQL數(shù)據(jù)庫-專業(yè)知識
16.Mysql中有哪幾種鎖？
行級鎖：鎖定力度小，發(fā)生鎖沖突概率低，實現(xiàn)并發(fā)度高，開銷大，加鎖慢，并發(fā)度高。 頁級鎖：加鎖時間比行鎖長，頁級鎖開銷介于表鎖和行鎖之間，會出現(xiàn)死鎖，并發(fā)度一般。 表級鎖：開銷小，加鎖快。
17.Mysql中默認事務(wù)隔離級別是？
讀未提交(RU): 一個事務(wù)還沒提交時, 它做的變更就能被別的事務(wù)看到。 讀提交(RC): 一個事務(wù)提交之后, 它做的變更才會被其他事務(wù)看到。 可重復(fù)讀(RR): 一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù), 總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級別下, 未提交變更對其他事務(wù)也是不可見的。 串行化(S): 對于同一行記錄, 讀寫都會加鎖. 當(dāng)出現(xiàn)讀寫鎖沖突的時候, 后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成才能繼續(xù)執(zhí)行。
18.Mysql數(shù)據(jù)庫表類型有哪些？
MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。
MyISAM：成熟、穩(wěn)定、易于管理，快速讀取。一些功能不支持（事務(wù)等），表級鎖。
InnoDB：支持事務(wù)、外鍵等特性、數(shù)據(jù)行鎖定。空間占用大，不支持全文索引等。
19.簡述mysql的MVCC機制
MVCC是一種多版本并發(fā)控制機制，是MySQL的InnoDB存儲引擎實現(xiàn)隔離級別的一種具體方式，用于實現(xiàn)提交讀和可重復(fù)讀這兩種隔離級別。
MVCC實現(xiàn)原理。通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)該機制，其在每行記錄后面保存兩個隱藏的列，分別保存這個行的創(chuàng)建版本號和刪除版本號，然后Innodb的MVCC使用到的快照存儲在Undo日志中，該日志通過回滾指針把一個數(shù)據(jù)行所有快照連接起來。
20.簡述MySQL 兩種常見存儲引擎：MyISAM與InnoDB
目前MySQL默認的存儲引擎是InnoDB。
現(xiàn)在大多數(shù)時候我們使用的都是InnoDB，但是在某些情況下使用MyISAM更好，比如：MyISAM更適合讀密集的表，而InnoDB更適合寫密集的的表。在數(shù)據(jù)庫做主從分離的情況下，經(jīng)常選擇MyISAM作為主庫的存儲引擎。
二者的常見對比
count運算上的區(qū)別：因為MyISAM緩存有表meta-data（行數(shù)等），因此在做COUNT(*)時對于一個結(jié)構(gòu)很好的查詢是不需要消耗多少資源的。而對于InnoDB來說，則沒有這種緩存。 是否支持事務(wù)和崩潰后的安全恢復(fù)：MyISAM 強調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持。但是InnoDB 提供事務(wù)支持事務(wù)，外部鍵等高級數(shù)據(jù)庫功能。具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全(transaction-safe (ACID compliant))型表。 是否支持外鍵：MyISAM不支持，而InnoDB支持。