數(shù)據(jù)庫基礎(chǔ)知識整理與復習總結(jié)

1、數(shù)據(jù)庫基礎(chǔ)知識整理與復習總結(jié)1、數(shù)據(jù)庫底層MySQL數(shù)據(jù)庫的底層是B+樹。說到B+樹，先說下B樹，B樹也叫多路平衡查找樹，所有的 葉子節(jié)點位于同一層，具有以下特點：1） 一個節(jié)點可以容納多個值；2）除非數(shù)據(jù)已 滿，不會增加新的層，B樹追求最少的層數(shù)；3）子節(jié)點中的值與父節(jié)點的值有嚴格的 大小對應(yīng)關(guān)系。一般來說，如果父節(jié)點有a個值，那么就有a+1個子節(jié)點；4）關(guān)鍵字集 合分布在整棵樹中；5）任何一個關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個節(jié)點中；6）搜索可能在 葉子結(jié)點結(jié)束，其搜索性能等價于在關(guān)鍵字全集做一次二分查找。B+樹是基于B樹和葉子節(jié)點順序訪問指針進行實現(xiàn)，它具有B樹的平衡性，并且通過順 序訪問指針來提

2、高區(qū)間查詢的性能，一個葉子節(jié)點中的key從左至右非遞減排列。特點 在于：1）非葉子節(jié)點中含有n個關(guān)鍵字，關(guān)鍵字不保存數(shù)據(jù)，只作為索引，所有數(shù)據(jù) 都保存在葉子結(jié)點；2）有的葉子節(jié)點中包含了全部關(guān)鍵字的信息及只想這些關(guān)鍵字記 錄的指針，即葉子節(jié)點包含鏈表結(jié)構(gòu)，能夠方便進行區(qū)間查詢；3）所有的非葉子結(jié)點 可以看成是索引部分，節(jié)點中僅包含其子樹中的最大（或最?。╆P(guān)縫字；4）同一個數(shù) 字會在不同節(jié)點中重復出現(xiàn)，根節(jié)點的最大元素就是B+樹的最大元素。MySQL中的InnoDB引擎是以主鍵ID為索引的數(shù)據(jù)存儲引擎。I nnoDB通過B+樹結(jié)構(gòu)對ID建 立索引，在葉子節(jié)點存儲數(shù)據(jù)。若建索引的字段不是主鍵ID,

3、則對該字段建索引，然 E再葉子節(jié)點中存儲的是該記錄的主鍵，然后通過主鍵索引找到對應(yīng)的記錄。因為不 再需要全表掃描，只需要對樹進行搜索即可，所以查找速度很快，還可以用于排序和 分組。InnoDB和My ISAM引擎都是基于B+樹，InnoDB是聚簇索引，數(shù)據(jù)域存放的是完整的數(shù)據(jù) 記錄；My I SAM是非聚簇索引，數(shù)據(jù)域存放的是數(shù)據(jù)記錄的地址。I nnoDB支持表鎖、行 鎖、間隙鎖、外鍵以及事務(wù)，My I SAM僅支持表鎖，同時不支持外鍵和事務(wù)。I nnoDB注 重事務(wù),My I SAM注重性能。2、SQL語言之DQL、DML、DDL和DCLDQL指的是Data Query Language,數(shù)

4、據(jù)庫查詢語言，主要是seIect命令：DML 指的是 Data Mani pul at io n Language,數(shù)據(jù)庫操作語言，主要有 i nsert > delete, update等命令；DDL指的是Data Defined Language,數(shù)據(jù)庫定艾語言，主要是對數(shù)據(jù)庫的菜些對象， 如database和table進行管理，主要有create、alter、drop等命令，比如創(chuàng)建數(shù)據(jù)庫 和表格、更改表結(jié)構(gòu)和設(shè)置約束、刪除表和數(shù)據(jù)庫；DCL指的是Data Control Language,數(shù)據(jù)庫控制語言，主要是用于授予或回收訪問數(shù) 據(jù)庫的某種特權(quán)，并控制數(shù)據(jù)庫操縱事務(wù)發(fā)生的時間及

5、效果，主要有g(shù)rant > rol I back 等命令。DML和DDL的區(qū)別：DML操作可以手動控制事務(wù)的開啟、提交和回滾，而DDL是隱形提交 不能回滾。3、數(shù)據(jù)庫連接協(xié)議JDBC驅(qū)動協(xié)議：默認TCP協(xié)議客戶端和Oracle服務(wù)器之間通信協(xié)議是TCP的，但是一個數(shù)據(jù)庫連接也會其一個UDP端 口。MySQL的連接方式有兩種：Socket和TCP/IP-Socket連接方式mysq I-uroot-padm i n-S/appIicat i on/mysqI/tmp/mysqI sockTCP/1P連接方式mysq I一uoot一padm i n一hi 92.11 204、索引和主鍵的區(qū)別

6、主鍵用于標識數(shù)據(jù)庫記錄的唯一性，不允許記錄重復且鍵值不能為空。主鍵是特殊索 引，但索引不一定是主鍵。索引可以提高查詢速度，可以不需要進行全表掃描而快速查詢到結(jié)果。使用主鍵，數(shù)據(jù)庫會自動創(chuàng)建主鍵索引，同時也可以再非主鍵上創(chuàng)建索引。 數(shù)據(jù)表中只能由一個主鍵，但可以有多個索引。5、數(shù)據(jù)庫四范式范式：創(chuàng)建數(shù)據(jù)庫的過程中必須遵循的準則。其作用在于減少數(shù)據(jù)庫中的數(shù)據(jù)冗余, 以增加數(shù)據(jù)的一致性。候選鍵：唯一識別該表的屬性或?qū)傩员怼５谝环妒剑?NF）:屬性不可拆分或無重復的列；第二范式（2NF）:數(shù)據(jù)庫中的每一行必須被唯一地區(qū)分，即表中字段必須完全依賴于 全部主鍵而非部分主鍵；第三范式（3NF）:消除傳遞依賴

7、，數(shù)據(jù)庫非主鍵外的所有字段僅能以來于候選鍵，不 存在與其他非主鍵關(guān)聯(lián)；第四范式（4NF）: 一個表的主鍵只對應(yīng)一個多值，即消除多值依賴。6、SQL基礎(chǔ)數(shù)據(jù)庫的創(chuàng)建與使用：create database test; use test; 創(chuàng)建表：createtablemytabIe(idnotnu I Iauto一i ncrement,namevarchar (20)）；修改表:altertab I emytableaddsexvarchar (20)增刪改：insertintomytable(id, name,sex)vaIues (1,'zhangsan', 'male

8、')de Ietefrommytablewhereid= 1updatemytabIesetname='I i s i'whereid=1清空表：truncatetablemytabIedist i net:用于返回唯一不同的值selectdist i netnamefrommytabIelimit:檢索記錄行檢索前5個記錄行select*frommytabIeI imit5;檢索記錄行1-5行seIect*frommytabIelimit0,5;檢索6-1 ast行seIect*frommytableI imit5,-1;排序：默認升序select*frommytab

9、Ieorderby collDESC ,co 12ASC;I ike匹配： %匹配大于等于1個任意字符seIect*frommytablewherename二'z%'匹配1個任意字符select*frommytabIewherename二 111S ;- 匹配集合內(nèi)的字符select*frommytabIewheren ame一 !匹配除開集合內(nèi)的字符seIect*frommytabIewheren ame='!z%；concat ():用于將多個字符串連接成一個字符串selectconcat (tr im(col1),'C, tr im(col2),'

10、)')as newfrom mytable;函數(shù)：均值se1ectavg(col)ascol_avgfrommytable;計數(shù)se1ectcount (co I)ascol countfrommytable;類似的sum ().max ()min ()等時間selectnow ();分組：se1ectcol,count (*) as numfrom mytable where co I> 2 groupby col havingbynum>2;子查詢中只能返回一個字段的數(shù)據(jù)：seIect * from mytablel where col 1 i n (seIect co

11、 12 from mytable2); 組合查詢：seIect * from mytable where col = 1 uni on seIect col from mytabIe where col = 2; 視圖：crea te v i ew as my v i ew as selec t * from myt ab I e where id >2;存儲過程：createproceduremyprocedure(outretint)beg i n end7> 刪除操作de I ete、truncate和dropdelete:直接刪除表中的某一行數(shù)據(jù)，并且同時將該行的刪除操作作

12、為事務(wù)記錄在日 志中保存便于進行回滾，因此delete操作更加占用資源，數(shù)據(jù)空間不釋放。delete可 以對tab I e和v i ew對象進行操作。de IetefrommytabIewherei d=1;truncate: 一次性從數(shù)據(jù)表中刪除所有數(shù)據(jù)（釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù) 據(jù)），因此不能回滾，占用資源更加少，速度更快。數(shù)據(jù)空間釋放后，表和索引所占 用的空間會回復到初始大小。只能對沒有關(guān)聯(lián)視圖的table進行操作，對于外鍵約束引 用的表，不能使用truncate,需要使用delete。truncatemytable;drop:刪除整個表，包括表的結(jié)構(gòu)、數(shù)據(jù)、定義等。屬于永久抹

13、去，空間會釋放，無 法恢復，對tab I e和v i ew都能操作。dropmytable;總結(jié)：在速度上，drop > truncate > de I ete：在操作對象上,de I ete和dr op可以對tab I e和v i ew操作，truncate只能對tab I e操作;在表和索引所占空間上，delete操作不會減少表和索引占用的空間，truncate操作E 表和索引所占用的空間會恢復至初始大小，drop將表所占用的空間全部釋放： 在回滾上方面，delete操作為DML語句可以回滾，truncate和drop為DDL語句，隱式提 交無法回滾：在刪除限制上，delete

14、可以操作帶有外鍵約束引用的表，而truncate不可以；8、char和varchar的區(qū)別char的長度是不可變的，而varchar的長度是可變的。例如，創(chuàng)建表時定義一個 char 10和varchar 10,當存入一個字符串sq I時，char所占的長度依然為10,除了 字符sql外后面跟7個空格，而varchar所占的長度變?yōu)樽址膶嶋H長度3。在取數(shù)據(jù) 時，char類型的要用tr im（）函數(shù)去掉多余的空格，而varchar類型不需要。char類型的存取速度比varchar快得多，因為其長度固定方便存儲與查找：但char類型 會付出空間的代價，是以空間換時間來爭取高的時間效率，而varc

15、har是以空間效率為 首。char對英文字符（ASCII）占用1個字節(jié)，對漢字占用2個字節(jié)；varchar對英文字符和 漢字都是占用2個字節(jié)。9、數(shù)據(jù)庫的冷備份與熱備份冷備份（off,慢，時間點上恢復）：需要數(shù)據(jù)庫正常關(guān)閉，會提供一個完整的數(shù)據(jù)庫; 將關(guān)鍵性文件拷貝到另外位置；對于備份數(shù)據(jù)庫信息而言，冷備份是最快最安全的方 法。優(yōu)點：易歸檔、能夠回復到某個時間點；缺點：數(shù)據(jù)庫必須處于關(guān)閉狀態(tài)；熱備份（on,塊）：數(shù)據(jù)庫運行的情況下，備份數(shù)據(jù)庫操作的sql語句，當數(shù)據(jù)庫發(fā)生 問題時可以重新執(zhí)行一遍備份的sql語句。優(yōu)點：備份時數(shù)據(jù)庫仍可以使用、快速恢復、屬于表或數(shù)據(jù)庫級別的備份，并且時間 短缺點

16、：不能出錯10、數(shù)據(jù)庫的事務(wù)及ACID屬性事務(wù)：邏輯上的一組操作，要么都執(zhí)行，要么都不執(zhí)行。四大特性：A原子性：事務(wù)是最小的執(zhí)行單位，不可分割，要么全部執(zhí)行，要么都不執(zhí)行；C一致性：事務(wù)執(zhí)行前后，數(shù)據(jù)庫的數(shù)據(jù)保持一致，多個事務(wù)對同一數(shù)據(jù)讀取的結(jié)果是 相同的；I隔離性：并發(fā)訪問數(shù)據(jù)庫時，事務(wù)之間互不打擾，各并發(fā)事務(wù)之間的數(shù)據(jù)庫是獨立的; D持久性：一個事務(wù)被提交之后，該事務(wù)對數(shù)據(jù)庫所做的改變是持久的，不會被回滾。11. 并發(fā)事務(wù)的問題臟讀：一個事務(wù)讀取到了另一個事務(wù)還未提交的修改數(shù)據(jù)，如果另一個事務(wù)進行了回 滾，這個數(shù)據(jù)就是臟數(shù)據(jù)。修改丟失：一個事務(wù)讀取數(shù)據(jù)并進行了修改，另一個事務(wù)頁讀取了該數(shù)據(jù)

17、進行了修改, 這樣第一個事務(wù)的修改結(jié)果就丟失了，也就是修改丟失問題。不可重復讀：一個事務(wù)對同一數(shù)據(jù)進行多次修改，期間另一事務(wù)也讀取了該數(shù)據(jù)并進 行了修改，這樣第一個事務(wù)讀取到的數(shù)據(jù)可能不一致，稱為不可重復讀?；米x：一個事務(wù)在讀取多行數(shù)據(jù)時，另一個并發(fā)事務(wù)插入了一些新數(shù)據(jù)，后續(xù)查詢中 第一個事務(wù)會查找到一些原本不存在的數(shù)據(jù)記錄，稱為幻讀。在Mysql中利用MVCC解決 了快照讀幻讀，利用間隙鎖解決了當前讀幻讀。12. 隔離級別讀未提交：最低隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，會導致臟讀、不可重復讀 和幻讀；讀已提交：允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，會導致不可重復讀和 幻讀；可重復

18、讀：對同一字段多次讀取的結(jié)果都是一樣的，可以阻止臟讀和不可重復讀，會 導致幻讀；串行化：最高隔離界別，可以阻止臟讀、不可重復讀和幻讀。MySQL中InnoDB引擎默認支持的隔離級別是可重復讀，使用的是next-key Lock算法，可以避免幻讀的產(chǎn)生，可以完全保證事務(wù)的隔離性要求13. 多表查詢一對多關(guān)系：從表使用主表的主鍵作為外鍵；主表中有的數(shù)據(jù)，從表中可以沒有；主 表必須有數(shù)據(jù)，才能向從表中添加數(shù)據(jù)；要先刪除從表的相應(yīng)數(shù)據(jù)才能刪除主表的數(shù) 據(jù)：多對多關(guān)系：老師與學生，一個老師可以教多個學生，一個學生也可以從多個老師那 里學習知識。創(chuàng)建表格時，將多對多的關(guān)系拆分為多個一對多關(guān)系。14. 數(shù)據(jù)

19、庫表的連接方式內(nèi)連接：inner joinon:取交集外連接：1) left joinon:以左表為準，查詢出左表的所有數(shù)據(jù)，右表中有對應(yīng) 的則顯示出來，沒有對應(yīng)的則顯示為null;2) right joinon:以右表為準，查詢出右表的所有數(shù)據(jù)，左表中有對 應(yīng)的則顯示出來，沒有對應(yīng)的則顯示為null：3) fu I I joinon : I eft和r i ght的集合，某表中某一行在另一表中無匹配行，則相應(yīng)列的內(nèi)容為null;交叉連接：cross join:笛卡爾積，相當于兩個表中的所有行進行排列組合。15. 存儲過程與存儲函數(shù)存儲過程：為以后的使用而保存的一條或多條SQL語句的集合，相當

20、于批處理。存儲過 程被編譯后會被直接保存在數(shù)據(jù)庫中，成為數(shù)據(jù)庫的一部分，以E就可以反復調(diào)用、 運行速度快。in表示輸入變量，out表示輸出變量，inout輸入輸出均可 create procedure myprocedure (IN|0UT| INOUT參數(shù)名 數(shù)據(jù)類型，IN|OUT| INOUT參 數(shù) 名數(shù) 據(jù) 類 型 ,)begin end存儲函數(shù)：create funct i on myfunct i on (參數(shù)名 數(shù)據(jù)類型， )returns 返回類型 begin end二者區(qū)別：存儲函數(shù)限制較多，例如不能使用臨吋表，只能用表變量，而存儲過程限制較少; 存儲過程可以實現(xiàn)復雜的功能，存

21、儲函數(shù)針對性比較強；返回值不同，存儲過程可以沒有返回值，也可以返回單個或多個結(jié)果集，而存儲 函數(shù)有且僅有一個返回值；調(diào)用不同。存儲過程通過cal I語句調(diào)用，存儲函數(shù)通過select調(diào)用；參數(shù)不同。存儲過程的參數(shù)類型可以是in、out、inout,而存儲函數(shù)的參數(shù)類型 只有in類型。16、觸發(fā)器SQL觸發(fā)器是一種特殊類型的存儲過程，不由用戶調(diào)用。它在指定的表中的數(shù)據(jù)發(fā)生變 化時自動生效。喚醒調(diào)用觸發(fā)器以響應(yīng)Insert、Update和Delete語句。他可以查詢其 它表，并可以包含復雜的Transact-SQL語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā) 器內(nèi)回滾的單個事務(wù)對待。如果檢測到嚴重錯誤

22、，整個事務(wù)自動回滾。優(yōu)點：觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改；觸發(fā)器可以強制比用check約束定艾的約束更為復雜的約束；在約束所支持的功能無法滿足應(yīng)用程序的功能要求時，觸發(fā)器就極為有用。create tr igger tr igger_order after insert on orders for each row beginupdate product set pnum 二 pnum - new. onum where p i d = new. p i d; end17、數(shù)據(jù)庫的鎖機制My I SAM和InnoDB存儲引擎使用的鎖:My I SAM 采用表級鎖(table-level

23、 I ock i ng ) , I nnoDB 支持行級鎖(row-level locking)和表級鎖，默認為行級鎖。二者對比：表級鎖：MySQL中粒度最大的鎖，對當前操作的整張表加鎖，實現(xiàn)簡單，資源消耗 少，加鎖快，不會出現(xiàn)死鎖。鎖沖究的概率高，并發(fā)度低。行級鎖：MySQL中粒度最小的鎖，只針對當前操作的行進行加鎖。行級鎖能大大減 少數(shù)據(jù)庫操作的沖究。如鎖慢，開銷大，并發(fā)度高，會出現(xiàn)死鎖。InnoDB引擎的鎖算法:Record lock:記錄鎖。條件為精準匹配時，鎖住具體的索引項；Gap lock:間隙鎖。鎖定一個范圍，不包括記錄本身。Next-key lock:臨鍵鎖。鎖定一個范圍，包含

24、記錄本身。按思想分為樂觀鎖和悲觀鎖樂觀鎖：事務(wù)并發(fā)操作時認為不會發(fā)生沖突，對數(shù)據(jù)進行更新并提交，如果檢測 到?jīng)_突就返回。悲觀鎖：事務(wù)并發(fā)操作使認為會發(fā)生沖突，先進行如鎖操作。18、日志bin Iog:二進制日志（服務(wù)層）：涉及到主從復制； redo log:重做日志（引擎層）：數(shù)據(jù)的災(zāi)后重新提交，物理日志。包括兩部分，內(nèi) 存中的日志緩存（redo log buffer）（易失性）和磁盤上的重做日志（redo log f i le） （持久性），需要將redo log buffer通過操作系統(tǒng)內(nèi)核空間的OS buffer刷到牘盤上 的 log f i le中。undo log:回滾日志（引擎層）

25、：主要用于數(shù)據(jù)修改的回滾，邏輯日志。會給予與操 作相反的語句，當事務(wù)回滾時從undo log中反向讀取內(nèi)容。MVCC: Multiversion concurrency controI，多版本并發(fā)控制，實現(xiàn)并發(fā)和回滾的重 要功能。它指的是數(shù)據(jù)庫中的每一條數(shù)據(jù)，會存在多個版本。對同一條數(shù)據(jù)而言， MySQL會通過一定的手段（ReadView機制）控制每一個事務(wù)看到不同版本的數(shù)據(jù)，這 樣也就解決了不可重復讀的問題。19、解決幻讀在Innodb引擎中，每條聚集索引都會有兩個隱藏字段：trx_id和rol I .pointer,每次 事務(wù)對一條記錄進行改動時，就會將事務(wù)id賦值給trx_id,并且會將

26、舊數(shù)據(jù)寫入一條 undo日志，每條undo日志都有rol l_pointer屬性，可以將這些undo日志都連起來，串 成一個鏈表，undo日志的寫入采用頭插法，新數(shù)據(jù)在前?？煺兆x：MVCC中有一個ReadV i ew的概念，其中記錄了生成ReadV i ew時的活躍事務(wù) id列表:m_ids、最小事務(wù)id:min_trx_id、將要分配給下一個事務(wù)的id:max_trx_id、 生成ReadView的事務(wù)id:creator trx id。如果被訪問版本的trx_id與creator trx_id 相同或者小于m in_trxd,則可以訪問；如果被訪問版本的trx_id大于等于 max_trx_id,則不能訪問;如果被訪問版本的trx_id在min_trx一id和max_trx_id之間, 則當tr