數(shù)據(jù)庫面試問題匯總

數(shù)據(jù)庫面試問題匯總數(shù)據(jù)庫管理命令1.1查詢所有數(shù)據(jù)庫----showdatabases;1.2創(chuàng)建數(shù)據(jù)庫，并將數(shù)據(jù)庫字符集默認(rèn)為utf-8----createdatabaseday814----defaultcharactersetutf8;1.3查看數(shù)據(jù)庫的默認(rèn)字符集showcreatedatabaseday814;1.4刪除數(shù)據(jù)庫----dropdatabaseday15;1.5修改數(shù)據(jù)庫(即修改數(shù)據(jù)庫的默認(rèn)字符集)——alertdatabaseday814defaultcharactersetgbk;表管理命令2.1選擇數(shù)據(jù)庫命令useday814;2.2查看所有表showtables;2.3創(chuàng)建表----createtablestudent(——sidint,——sageint,snamevarchar(20)-----);2.4查看表結(jié)構(gòu)descstudent;2.5刪除表droptablestudent;2.6修改表添加字段altertablestudentaddcolumnsgendervarchar(2);刪除字段altertablestudnetdropcolumnsgender;修改字段類型——altertablestudentmodifycolumnsgendervarchar(100);修改字段名稱——altertablestudentchangecolumnsgendergendervarchar(100);修改表名稱——altertablestudentrenametoteacher;數(shù)據(jù)管理3.1增刪改數(shù)據(jù)-----增加數(shù)據(jù)INSERTINTOstudentVALUES（1,’tom’,20,2）;/要嚴(yán)格按照表的字段順序插入數(shù)據(jù)增加部分?jǐn)?shù)據(jù)INSERTINTOstudent（id,name）VALUES（2,’tina’）；-----刪除數(shù)據(jù)條件刪除DELETEFROMstudentWHEREid=2;注意：DELETEFROM和TRUNCATEFROM的區(qū)另ljDELETEFROM:可以全表刪除，也可以條件刪除，只能刪除數(shù)據(jù)，不能刪除約束，最后刪除之后，可以回滾事務(wù)；TRUNCATEFROM:只能全表刪除，不能條件刪除，即可以刪除表的數(shù)據(jù)，也可以刪除表的約束，不能回滾事務(wù)；-----修改數(shù)據(jù)帶條件修改UPDATEstudentSETage=1,name='tom'WHEREid=2;3.2查詢數(shù)據(jù)（重點(diǎn)）3.2.1查詢所有列（*通配符）---SELECT*FROMstudent;3.2.2查詢指定列---SELECTsid,sname,genderFROMstudent;3.2.3查詢時添加常量列---SELECTsid,sname,gender,sage,'java_Class'AS‘sgrade’FROMstudent;//在查詢student表時，添加一個sgrade列，內(nèi)容為java_Class;3.2.4查詢時合并列---SELECTsid,sname,（servlet+jsp）AS‘總成績’FROMstudent;3.2.5查詢時去除重復(fù)記錄---SELECTDISTINCTgenderFROMstudent;---SELECTDISTINCTaddressFROMstudent;3.2.6條件查詢（模糊條件查詢）---邏輯條件AND（與）OR（或）SELECT*FROMstudentWHEREsid=2ANDsname=’c++’；〃交集SELECT*FROMstudentWHEREsid=2ORsname=’張三’；//并集---比較條件<,>,<=最重要的。不等于，另外BETWEENANDSELECT*FROMstudentWHEREjsp<=90ANDjsp>=75;---判空條件需求：查詢地址為null的學(xué)生（包括null和空字符串）注意：NULL表示沒有值空字符串表示有值的；----模糊條件LIKE表示模糊條件；％表示任意字符SELECT*FROMstudentWHEREsnameLIKE‘j%’；3.2.7聚合查詢常用的聚合函數(shù)：sum()\avg()\max()\min()\count()\SELECTMAX(jsp)AS‘max_score’FROMstudent;SELECTAVG(jsp)AS‘a(chǎn)veragescore‘FROMstudent;這里注意：count統(tǒng)計(jì)數(shù)據(jù)不包含null的數(shù)據(jù)3.2.8分頁查詢查詢第1,2條記錄SELECT*FROMstudentLIMIT0,2;//注意：LIMIT0:起始行行數(shù)，2：總行數(shù)；3.2.9查詢排序ASC:升序；DESC降序SELECT*FROMstudentORDERBYsidASC;SELECT*FROMstudentORDERBYservletASC;SELECT*FROMstudentORDERBYservletASC,jspDESC;3.2.10分組查詢--2.11分組查詢(groupby)--需求：查詢男女的人數(shù)--預(yù)期結(jié)果：--男3---女2--算法步驟：1)把學(xué)生按照性別分組(GROUPBYgender)--2)統(tǒng)計(jì)每組的人數(shù)(COUNT(*))SELECTgender,COUNT(*)FROMstudentGROUPBYgender;3.2.11分組查詢后篩選--2.12分組查詢后篩選--需求：查詢總?cè)藬?shù)大于2的性別--算法：1)查詢男女的人數(shù)--2)篩選出人數(shù)大于2的記錄(having)---注意：分組之前條件使用where關(guān)鍵字，分組之后條件使用having關(guān)鍵字SELECTgender,COUNT(*)FROMstudentWHEREGROUPBYgenderHAVINGCOUNT(*)>2;綜上，所有的SQL語句指令，主要包含3個部分:數(shù)據(jù)庫管理、表管理、數(shù)據(jù)管理。SQL語句分類：數(shù)據(jù)定義語言、數(shù)據(jù)查詢語言、數(shù)據(jù)操作語言。數(shù)據(jù)約束4.1默認(rèn)值4.2非空4.3唯一4.4主鍵（==非空+唯一）數(shù)據(jù)庫設(shè)計(jì)：三大范式第一范式：要求表的每個字段必須是不可分割的獨(dú)立單元；比如student:nameJava\狗娃應(yīng)該改為nameoldnameJava狗娃第二范式：在第一張范式基礎(chǔ)上，要求每張表只表達(dá)一個意思，表的每個字段都和主鍵有依賴；employee（員工）：員工編號員工姓名部門名稱訂單名稱.?違反第二范式員工表：員工編號員工姓名部門名稱訂單表：訂單編號訂單名稱--符合第二范式第三范式：在第二范式的基礎(chǔ)上，要求每張表的主鍵之外的其他字段都只能和主鍵有直接決定依賴關(guān)系；員工表：員工編號（主鍵）員工姓名部門編號部門名--符合第二范式，違反第三范式（數(shù)據(jù)冗余高）員工表：員工編號（主鍵）員工姓名部門編號--符合第三范式（降低數(shù)據(jù)冗余）部門表：部門編號部門名解析：個人對數(shù)據(jù)庫三大范式的理解：首先數(shù)據(jù)表中的每個字段都必須是不可分割的獨(dú)立單元；其次，在第一范式的基礎(chǔ)上，要求每張表只表達(dá)一個意思，表的每個字段都和主鍵有依賴；最后，在第二范式的基礎(chǔ)上，要求每張表的主鍵之外的其他字段都只能和主鍵有直接決定依賴關(guān)系。存儲過程帶有邏輯的SQL語句，存儲過程帶上流過程控制語句（ifelseforwhile）;（1）存儲過程執(zhí)行效率非?？?，因?yàn)槭窃诜?wù)端執(zhí)行；（2）存儲過程移植性比較差，不同數(shù)據(jù)的存儲過程是不能移植的；數(shù)據(jù)庫索引實(shí)現(xiàn)原理關(guān)于數(shù)據(jù)庫索引采用的數(shù)據(jù)結(jié)構(gòu)題目1：Mysql數(shù)據(jù)庫用過吧？l里面的索引是基于什么數(shù)據(jù)結(jié)構(gòu)。答：主要是基于Hash表和B+樹題目2：很好請你說一下B+樹的實(shí)現(xiàn)細(xì)節(jié)是什么樣的？B-樹和B+樹有什么區(qū)別？聯(lián)合索引在B+樹中如何存儲？答：首先，數(shù)據(jù)庫使用樹型結(jié)構(gòu)來增加查詢效率，并保持有序。那么，為什么不使用二叉樹來實(shí)現(xiàn)數(shù)據(jù)結(jié)構(gòu)呢，二叉樹算法時間復(fù)雜度是lg（N），查詢速度和比較次數(shù)都是較小的。實(shí)際上，查詢索引操作最耗資源的不在內(nèi)存中，而是磁盤IO。索引是存在磁盤上的，當(dāng)數(shù)據(jù)量比較大的時候，索引的大小可能達(dá)到幾個G。那么，我們利用索引進(jìn)行查詢的時候，不可能把索引直接加載到內(nèi)存中，只能一次讀取一個磁盤頁，一個磁盤頁對應(yīng)著一個節(jié)點(diǎn)，一次讀取操作時一個磁盤io。在二叉樹查詢時，最壞的情況下查找的次數(shù)是樹的高度，即io次數(shù)為樹的高度。B-樹就是比二叉樹“矮胖”的樹。二叉樹的特征如下：根節(jié)點(diǎn)至少有兩個子女每個中間節(jié)點(diǎn)包含k-1個元素和k個孩子，其中m/2<=k<=m每個葉子節(jié)點(diǎn)包含k-1個元素，其中m/2<=k<=m所有葉子節(jié)點(diǎn)位于同一層節(jié)點(diǎn)中的元素從小到大排列，正好是孩子節(jié)點(diǎn)的值域。（就是孩子節(jié)點(diǎn)的元素都比父節(jié)點(diǎn)中元素的最小值大，比父節(jié)點(diǎn)元素的最大值?。〣-樹查詢的次數(shù)并不比二叉樹的次數(shù)小，但是相比起磁盤io速度，內(nèi)存中比較的耗時就不足為提了。所以只要樹的高度足夠低，io次數(shù)少，就可以提升查找性能。而每個節(jié)點(diǎn)中有多個元素，都只在內(nèi)存中操作。而B+樹是基于B-樹的，增加了如下規(guī)則：有k個子樹的中間節(jié)點(diǎn)包含有k個元素（B樹中是k-1個元素），每個元素不保存數(shù)據(jù)，只用來索引，所有數(shù)據(jù)都保存在葉子節(jié)點(diǎn)。所有的葉子結(jié)點(diǎn)中包含了全部元素的信息，及指向含這些元素記錄的指針，且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而大順序鏈接。所有的中間節(jié)點(diǎn)元素都同時存在于子節(jié)點(diǎn)，在子節(jié)點(diǎn)元素中是最大（或最?。┰亍Ｋ裕珺+樹對比B-樹有如下好處：io次數(shù)少：b+樹中間節(jié)點(diǎn)只存索引，不存在實(shí)際的數(shù)據(jù)，所以可以存儲更多的數(shù)據(jù)。索引樹更加的矮胖，io次數(shù)更少。性能穩(wěn)定：b+樹數(shù)據(jù)只存在于葉子節(jié)點(diǎn)，查詢性能穩(wěn)定范圍查詢簡單：b+樹不需要中序遍歷，遍歷鏈表即可。B樹：有序數(shù)組+平衡多叉樹；B+樹：有序數(shù)組鏈表+平衡多叉樹；舉個物孑來對吸明：該圖以及下T圖均來自博文《淺談甕法和教據(jù)結(jié)構(gòu):十腌登找樹之B樹為「鏈接文章最后：比如說，我們要查艾關(guān)槌宇范圍在3到7的美健字，在找到給一個符合斜圈數(shù)字3后訪問芫第一個關(guān)摧字所在的塊后,與迥歷成彳￡樹，荻取Ff塊，點(diǎn)到遇至0—個不符合融的美倍字，遍歷的過程是t匕較豆雜的。B+樹：相ttZ~.B十蜿的基于苞圍的查t司簡潔很寥“由于葉子節(jié)點(diǎn)行捐巨下一亍葉子節(jié)見的指針，因此從塊1到塊2的訪問，通過煥1捂向功2的指針即瓦,從煥2到塊3也是祀T指針即虱.數(shù)據(jù)庫索引采用B+樹的主要原因是B樹在提高了磁盤IO性能的同時并沒有解決元素遍歷的效率低下的問題。正是為了解決這個問題，B+樹應(yīng)運(yùn)而生。B+樹只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹的遍歷。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的，而B樹不支持這樣的操作(或者說效率太低)。數(shù)據(jù)庫三大引擎之間差異MyISAM特性：不支持事務(wù)、MylSAM存儲引擎不支持事務(wù)，所以對事務(wù)有要求的業(yè)務(wù)場景不能適應(yīng);表級鎖定：其鎖定機(jī)制是表級索引，這雖然可以讓鎖定的實(shí)現(xiàn)成本很小但也同時大大降低了并發(fā)性能；讀寫互相阻塞：不僅會在寫入時阻塞讀取，還會在讀取時，阻塞寫入，但讀本身并不會阻塞另外的讀。只會緩存索引：MylSAM可以通過key_Buffer緩存以大大提高訪問性能減少磁盤IO，但是這個緩存區(qū)只會緩存索引，而不會緩存數(shù)據(jù)。（2）InnoDB特性：具有較好的事務(wù)支持，支持4個事務(wù)隔離級別，支持多版本讀；行級鎖定：通過索引實(shí)現(xiàn)，全表掃描仍然會是表鎖，注意間隙鎖的影響；讀寫阻塞與事務(wù)隔離級別相關(guān)；具有非常高效的緩存特性，能緩存特性，也能緩存數(shù)據(jù)；整個表和主鍵以Cluster方式存儲，組成一顆平衡樹；所有SecondaryIndex都會保存主鍵信息；（3）NDBCluster分布式：分布式存儲引擎，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分；支持事務(wù)：和Innodb一樣，支持事務(wù)；可與MySqld不在同一主機(jī)：可以和MysqlId分開存在于獨(dú)立的主機(jī)上，然后通過網(wǎng)絡(luò)和MysqlId通信交互；內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中，老版本所有數(shù)據(jù)和索引必須存放在與內(nèi)存中；臟讀和幻讀在說數(shù)據(jù)庫的臟讀和幻讀時，首先要說數(shù)據(jù)庫的事務(wù)，事務(wù)的4大特性：（1）原子性：事務(wù)是數(shù)據(jù)庫的邏輯工作單位，它對數(shù)據(jù)庫的修改，要么全部執(zhí)行，要么全部不執(zhí)行；（2）一致性：事務(wù)前后，數(shù)據(jù)庫的狀態(tài)都滿足所有的完整性約束；（3）隔離性：并發(fā)執(zhí)行的N個事務(wù)是隔離的，一個不影響一個，一個事務(wù)在沒有commit之前，被修改的數(shù)據(jù)不可能被其他事務(wù)看到（如何實(shí)現(xiàn)的呢，通過設(shè)置數(shù)據(jù)庫的隔離級別）（4）持久性：持久性意味著當(dāng)系統(tǒng)或介質(zhì)發(fā)生故障時，確保已提交事務(wù)的更新不能丟失，持久性主要在于DBMS的恢復(fù)性能；（系統(tǒng)故障，數(shù)據(jù)不丟失）臟讀：又稱無效數(shù)據(jù)讀出，一個事務(wù)讀取另外一個事務(wù)還沒有提交的數(shù)據(jù)叫做臟讀；例如：事務(wù)T1修改了一行數(shù)據(jù)，但是還沒有提交，這時候事務(wù)T2讀取了被事務(wù)T1修改后的數(shù)據(jù)，之后T1因某種原因回滾了，那么事務(wù)T2讀取的數(shù)據(jù)就是臟的；解決辦法：把數(shù)據(jù)庫的事務(wù)隔離級別調(diào)整到READ_COMMITTED；不可重復(fù)讀：不可重復(fù)讀是指同一個事務(wù)內(nèi)，兩個相同的查詢返回了不同的結(jié)果；例如：事務(wù)T1讀取某一數(shù)據(jù)，事務(wù)T2讀取并修改了該數(shù)據(jù)，T1為了對讀取值進(jìn)行檢驗(yàn)而在此讀取該數(shù)據(jù)，那么T1發(fā)現(xiàn)兩次讀取的結(jié)果不同；解決辦法：把數(shù)據(jù)庫的事務(wù)隔離級別調(diào)整到REPEATABLE_COMMITTED幻讀：例如：當(dāng)系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分?jǐn)?shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這個時候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這叫幻讀。解決辦法：把數(shù)據(jù)庫的事務(wù)隔離級別調(diào)整到SERIALIZABLEREAD；綜上：重點(diǎn)注意：不可重復(fù)讀和幻讀的區(qū)別，不可重復(fù)讀強(qiáng)調(diào)的是查詢修改，幻讀強(qiáng)調(diào)的是插入和刪除。i0.Spring事務(wù)管理3什么是事務(wù)：事務(wù)(transaction)是由一系列操作序列構(gòu)成的程序執(zhí)行單元，這些操作要么都做，要么都不做，是一個不可分割的工作單位3事務(wù)的4個基本性質(zhì)：原子性、一致性、隔離性、持久性3數(shù)據(jù)庫并發(fā)操作可能引起的問題：臟讀：事務(wù)A讀取了事務(wù)B中尚未提交的數(shù)據(jù)，如果事務(wù)B回滾，則A讀取使用了錯誤的數(shù)據(jù)(讀取了未提交的數(shù)據(jù))不可重復(fù)讀：事務(wù)A多次的讀取過程中，事務(wù)B對數(shù)據(jù)進(jìn)行了修改，導(dǎo)致事務(wù)A多次讀取的數(shù)據(jù)不一致。丟失更新：在事務(wù)A期間，事務(wù)B對數(shù)據(jù)進(jìn)行了更新，在事務(wù)A撤銷之后，覆蓋了事務(wù)B提交的數(shù)據(jù)。(又叫做回滾丟失)幻讀：在事務(wù)A多次讀取過程中，事務(wù)B對數(shù)據(jù)進(jìn)行了新增操作，導(dǎo)致事務(wù)A多次讀取的數(shù)據(jù)不一致。(幻讀和不可重復(fù)讀的區(qū)別在于：不可重復(fù)讀是針對數(shù)據(jù)的更新操作，只要再記錄上加上寫鎖，就可避免，幻讀是對記錄的insert操作，要禁止幻讀必須加上全局的寫鎖，比如在表上加寫鎖。)3SQL92定義的4中隔離級別：未提交讀(Readuncommitted):寫操作加寫鎖，讀操作不加鎖。禁止丟失更新(回滾丟失)，但是會出現(xiàn)所有其他數(shù)據(jù)并發(fā)問題。提交讀(Readcommitted):寫操作加寫鎖，讀操作加讀鎖。禁止丟失更新(回滾丟失)和臟讀，是大多數(shù)關(guān)系型數(shù)據(jù)庫的默認(rèn)隔離級別?？芍貜?fù)讀(Readrepeatable):對于讀操作加讀鎖到事務(wù)結(jié)束，其他事務(wù)的更新操作只能等到事務(wù)結(jié)束之后進(jìn)行。和提交讀的區(qū)別在于，提交讀的讀操作時加讀鎖到本次讀操作結(jié)束，可重復(fù)讀的粒度更大。禁止兩類丟失更新，禁止臟讀和不可重復(fù)讀，但可能出現(xiàn)幻讀。序列化(Serializable):讀操作加表級讀鎖至事務(wù)結(jié)束?？山够米x。3隔離級別越高，數(shù)據(jù)一致性的保證越強(qiáng)，但同時數(shù)據(jù)庫的并發(fā)能力也越差。存儲過程的作用？存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。存儲過程是SQL語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。可以出于任何使用SQL語句的目的來使用存儲過程，它具有以下優(yōu)點(diǎn)：可以在單個存儲過程中執(zhí)行一系列SQL語句?？梢詮淖约旱拇鎯^程內(nèi)