數(shù)據(jù)庫原理與應(yīng)用 課件 孟凡榮 第7-9章 數(shù)據(jù)組織與存儲(chǔ)管理、MySQL數(shù)據(jù)庫操作、數(shù)據(jù)庫使用案例

第7章數(shù)據(jù)組織與存儲(chǔ)管理7.1B+樹7.2跳躍表7.3LSM樹7.4布隆過濾器第7章數(shù)據(jù)組織與存儲(chǔ)管理三種基本數(shù)據(jù)存儲(chǔ)引擎？第7章數(shù)據(jù)組織與存儲(chǔ)管理1、哈希存儲(chǔ)引擎（高效隨機(jī)查找）2、B樹存儲(chǔ)引擎（高效范圍查找）3、LSM樹存儲(chǔ)引擎第7章數(shù)據(jù)組織與存儲(chǔ)管理如果需要滿足下述要求，需要怎樣的數(shù)據(jù)結(jié)構(gòu)：寫多讀少能夠高速寫入大量數(shù)據(jù)能夠高速讀取大量數(shù)據(jù)著名計(jì)算機(jī)科學(xué)家N.Wirth說過：程序=算法+數(shù)據(jù)結(jié)構(gòu)HBase代碼行數(shù)超過150萬，仍然是算法和數(shù)據(jù)結(jié)構(gòu)組成HBase的一個(gè)列族本質(zhì)上就是一棵LSM樹（Log-StructuredMergeTree）。該樹分為內(nèi)存部分和磁盤部分內(nèi)存部分是一個(gè)維護(hù)有序數(shù)據(jù)集合的數(shù)據(jù)結(jié)構(gòu)，可以選擇平衡二叉樹、紅黑樹、跳躍表等維護(hù)有序數(shù)據(jù)集合的結(jié)構(gòu)。HBase選擇了跳躍表為了避免不必要的IO耗時(shí)，HBase選擇了布隆過濾器，用來快速判斷key值是否存儲(chǔ)在當(dāng)前塊中7.1B+樹7.1B+樹B+樹可以定義一個(gè)m值作為預(yù)定范圍，即m路(階)B+樹。一個(gè)節(jié)點(diǎn)的結(jié)構(gòu)是：（K1，P1，K2，...Km-1,Pm),其中P為指向子節(jié)點(diǎn)（子樹）的指針，K為索引鍵值。對(duì)于根節(jié)點(diǎn)，如果它本身不是葉子節(jié)點(diǎn)，則至少擁有1個(gè)關(guān)鍵字，即至少有2個(gè)子樹除了根節(jié)點(diǎn)，非葉子節(jié)點(diǎn)至少擁有

個(gè)指針；葉子節(jié)點(diǎn)最少

個(gè)索引鍵值除了根節(jié)點(diǎn)，所有節(jié)點(diǎn)最多包含

個(gè)子樹，即

個(gè)索引鍵值7.1B+樹非葉子節(jié)點(diǎn)不保存數(shù)據(jù)，只保存關(guān)鍵字用作索引，所有數(shù)據(jù)都保存在葉子節(jié)點(diǎn)中。非葉子節(jié)點(diǎn)有若干子樹指針，如果非葉子節(jié)點(diǎn)索引鍵值為k1,k2,…km，那么第一個(gè)子樹索引鍵值判斷條件為小于k1，第二個(gè)為大于等于k1而小于k2，以此類推，最后一個(gè)為大于等于km，總共可以劃分出m個(gè)區(qū)間，即可以有m個(gè)分支。7.1B+樹

插入鍵值7的情況（父節(jié)點(diǎn)無分裂)1.插入操作7.1B+樹圖中給出了一個(gè)節(jié)點(diǎn)的分裂情況：向樹中插入鍵值7。首先通過查找算法定位插入的位置，在葉節(jié)點(diǎn)的6和9兩個(gè)鍵值之間插入7。設(shè)當(dāng)前葉節(jié)點(diǎn)的階數(shù)為4，每個(gè)葉節(jié)點(diǎn)最多可以存放3個(gè)鍵值，因此需要進(jìn)行節(jié)點(diǎn)的分裂操作，右圖所示。以位置為m/2位置的鍵值，即6為分裂點(diǎn)，5和6保留在當(dāng)前節(jié)點(diǎn)，7和9傳輸?shù)叫聞?chuàng)建的節(jié)點(diǎn)中，同時(shí)將鍵值7提升至父節(jié)點(diǎn)中，增加指向父節(jié)點(diǎn)的指針和同級(jí)節(jié)點(diǎn)的指針。1.插入操作7.1B+樹插入鍵值2的情況(父節(jié)點(diǎn)有分裂）1.插入操作7.1B+樹圖中給出了一個(gè)非葉節(jié)點(diǎn)的分裂情況：若向圖中的B+樹中插入鍵值2時(shí)，在包含（1，3，4）的葉子節(jié)點(diǎn)中插入該值以后，該節(jié)點(diǎn)超過了最大鍵值個(gè)數(shù)3，因此需要進(jìn)行分裂。選擇鍵值2處作為分裂點(diǎn)，將鍵值3提升至父節(jié)點(diǎn)，并將（1，3，4）節(jié)點(diǎn)分裂成兩部分，1和2保留在原始節(jié)點(diǎn)中，3和4放在新創(chuàng)建的節(jié)點(diǎn)中。之后，由于非葉節(jié)點(diǎn)（5，7，11）在插入鍵值3后，超過了最大鍵值個(gè)數(shù)，需要再次進(jìn)行分裂，提升鍵值7，創(chuàng)建新的根節(jié)點(diǎn)，樹的高度由2變?yōu)?，此處因?yàn)槭欠侨~節(jié)點(diǎn)分裂，所以新創(chuàng)建的節(jié)點(diǎn)（11）中不用重復(fù)保留鍵值7。最后，增加新節(jié)點(diǎn)到父節(jié)點(diǎn)的指針。7.1B+樹刪除鍵值2的情況（合并）2.刪除操作7.1B+樹刪除鍵值9的情況（重新分配）2.刪除操作思考題請(qǐng)自己查找B+樹的構(gòu)造、插入、刪除、查詢等操作的代碼，搞懂執(zhí)行過程。7.2跳躍表跳躍表（skipList）是一種能高效實(shí)現(xiàn)插入、刪除、查找的內(nèi)存數(shù)據(jù)結(jié)構(gòu)，這些操作的期望復(fù)雜度都是O(logN).這種數(shù)據(jù)結(jié)構(gòu)是由WilliamPugh發(fā)明的，最早出現(xiàn)于他在1990年發(fā)表的論文《SkipLists:AProbabilisticAlternativetoBalancedTrees》。跳躍表被廣泛地使用在KV數(shù)據(jù)庫中，如Redis、LevelDB、HBase都是把跳躍表作為一種維護(hù)有序數(shù)據(jù)集合的基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)。7.2

跳躍表鏈表的復(fù)雜度是多少？O(n)7.2跳躍表每條鏈表都是有序的每條鏈表都有兩個(gè)元素：正無窮大（+∞）和負(fù)無窮大（-∞），分別表示鏈表的頭部和尾部從上至下，上層鏈表元素集合是下層鏈表元素集合的子集跳表的高度定義為水平鏈表的層數(shù)1.定義7.2跳躍表(1)由很多層結(jié)構(gòu)組成，level是通過一定的概率隨機(jī)產(chǎn)生的；(2)每一層都是一個(gè)有序的鏈表，默認(rèn)是升序；(3)最底層(Level1)的鏈表包含所有元素；(4)如果一個(gè)元素出現(xiàn)在Leveli的鏈表中，則它在Leveli之下的

鏈表也都會(huì)出現(xiàn)；

(5)每個(gè)節(jié)點(diǎn)包含兩個(gè)指針，一個(gè)指向同一鏈表中的下一個(gè)元素，一個(gè)指向下面一層的元素。2.性質(zhì)7.2跳躍表以左上角元素（設(shè)為currentNode）作為起點(diǎn)如果發(fā)現(xiàn)currentNode后繼節(jié)點(diǎn)的值小于等于待查詢值，則沿著這條鏈表向后查詢，否則，切換到當(dāng)前節(jié)點(diǎn)的下一層鏈表。繼續(xù)查找，直到找到待查詢值為止（或者currentNode為空節(jié)點(diǎn)）。查找元素15的流程7.2跳躍表每?jī)蓚€(gè)結(jié)點(diǎn)會(huì)抽出一個(gè)結(jié)點(diǎn)作為上一級(jí)索引的結(jié)點(diǎn)，那第一級(jí)索引的結(jié)點(diǎn)個(gè)數(shù)大約就是n/2，第二級(jí)索引的結(jié)點(diǎn)個(gè)數(shù)大約就是n/4，第三級(jí)索引的結(jié)點(diǎn)個(gè)數(shù)大約就是n/8，依次類推，也就是說，那第k級(jí)索引結(jié)點(diǎn)的個(gè)數(shù)就是n/(2^k).假設(shè)索引有k級(jí)，最高級(jí)的索引有2個(gè)結(jié)點(diǎn)。通過上面的公式，我們可以得到n/(2^k)=2，從而求得k=logn-1,如果包含原始鏈表層，整個(gè)跳表高度是logn.如果在跳表中每一層索引需要遍歷m個(gè)節(jié)點(diǎn)，則在跳表中查詢一個(gè)數(shù)據(jù)的時(shí)間復(fù)雜度為O(m*logn).查詢的時(shí)間復(fù)雜度7.2跳躍表那這個(gè)m的值是多少呢？按照前面這種索引結(jié)構(gòu)，我們每一級(jí)索引都最多只需要遍歷3個(gè)結(jié)點(diǎn)，也就是說m=3所以在跳表中查詢?nèi)我鈹?shù)據(jù)的時(shí)間復(fù)雜度就是O(logn)也就是這些多級(jí)索引把一個(gè)單鏈表分成了一個(gè)個(gè)的區(qū)間段，每次索引層的查找都確定了一個(gè)小的區(qū)間段，于是就提高了效率，但是卻浪費(fèi)了建立索引需要的空間，這就是空間換時(shí)間的思路7.2跳躍表假設(shè)單鏈表的長(zhǎng)度為n，第一級(jí)索引大概n/2個(gè)節(jié)點(diǎn)，第二級(jí)索引大概n/4個(gè)節(jié)點(diǎn)，第三級(jí)索引大概n/8個(gè)節(jié)點(diǎn)。依次類推直到剩下2個(gè)索引節(jié)點(diǎn)。于是組成了一個(gè)等比數(shù)列：n/2、n/4、n/8、.........8、4、2.這些級(jí)的索引加起來等于n-2,所以跳表的空間復(fù)雜度O(n),即長(zhǎng)度為n單鏈表在每隔兩個(gè)節(jié)點(diǎn)抽取一個(gè)索引節(jié)點(diǎn)時(shí)，組成的跳表需要額外n個(gè)節(jié)點(diǎn)來存儲(chǔ)索引?？臻g復(fù)雜度7.2跳躍表如果每隔三個(gè)節(jié)點(diǎn)抽取一個(gè)索引節(jié)點(diǎn)，那么第一層索引節(jié)點(diǎn)數(shù)大概為n/3,第二層索引的節(jié)點(diǎn)數(shù)大概為n/9，依次類推假設(shè)最后一層索引節(jié)點(diǎn)數(shù)為1，則組成的等比數(shù)列是n/3、n/9、n/27、.......9、3、1.加起來的和為(n-1)/2,空間占用差不多減少了一半的存儲(chǔ)。實(shí)際上索引占用空間相比原始單鏈表本身節(jié)點(diǎn)小巫見大巫了，畢竟一半節(jié)點(diǎn)中存儲(chǔ)的是對(duì)象，而索引中只是id,占不了多少空間。如何降低空間復(fù)雜度7.2跳躍表1、給定一個(gè)有序的鏈表。

2、選擇鏈表中最大和最小的元素，然后從其他元素中按照一定算法（隨機(jī)）隨即選出一些元素，將這些元素組成有序鏈表。這個(gè)新的鏈表稱為一層，原鏈表稱為其下一層。

3、為剛選出的每個(gè)元素添加一個(gè)指針域，這個(gè)指針指向下一層中值同自己相等的元素。Top指針指向該層首元素

4、重復(fù)2、3步，直到不再能選擇出除最大最小元素以外的元素。跳躍表的構(gòu)建7.2跳躍表第一步，需要按照查找流程找到待插入元素的前驅(qū)節(jié)點(diǎn)9第二步，在鏈表中插入節(jié)點(diǎn)10第三步，按照如下隨機(jī)算法生成一個(gè)高度值：插入操作插入元素10的流程7.2跳躍表//p是一個(gè)(0,1)之間的常數(shù)，一般取p=1/4或1/2PublicintrandomHeight(doublep){intheight=0;while{random.nextDouble()<preturnheight+1;}random.nextDouble()返回一個(gè)大于或等于0.0且小于1.0的隨機(jī)浮點(diǎn)數(shù)。隨機(jī)函數(shù)的選擇是非常有講究的，從概率上講，能夠保證跳表的索引大小和數(shù)據(jù)大小平衡性，不至于性能的過度退化。至于隨機(jī)函數(shù)的選擇，見上面的代碼實(shí)現(xiàn)過程，而且實(shí)現(xiàn)過程并不是重點(diǎn)，掌握思想即可。插入操作7.2跳躍表第四步，將待插入節(jié)點(diǎn)按照高度值生成一個(gè)垂直節(jié)點(diǎn)（這個(gè)節(jié)點(diǎn)的層數(shù)等于高度值），之后插入到跳躍表的

多條鏈表中。假設(shè)height=randomHeight(p)，這里分為

兩種情況：

如果height大于跳躍表的高度,則跳躍表的高度被提升為height，同時(shí)需要更新頭部節(jié)點(diǎn)和尾部節(jié)點(diǎn)的指針指向。如果height小于等于跳躍表的高度，則只需要更新待插入元素前驅(qū)和后繼的指針指向。插入操作7.2跳躍表7.2跳躍表7.2跳躍表刪除操作7.3LSM樹LSM樹本質(zhì)上和B+樹一樣，是一種磁盤數(shù)據(jù)的索引結(jié)構(gòu)。和B+樹不同的是，LSM樹的索引對(duì)寫入請(qǐng)求更友好。因?yàn)闊o論是何種寫入請(qǐng)求，LSM樹都會(huì)將寫入操作理解為一次順序?qū)懀鳫DFS擅長(zhǎng)順序?qū)懭耄℉DFS不支持隨機(jī)寫入），因此，基于HDFS實(shí)現(xiàn)的HBase采用LSM樹作為索引是一種合適的選擇。7.3LSM樹LSM樹的索引結(jié)構(gòu)ConcurrentSkipListMemStorehfilehfilehfilehfilehfileflushMemoryDisk7.3LSM樹LSM樹的索引有兩部分構(gòu)成：內(nèi)存部分和磁盤部分。內(nèi)存部分是一個(gè)SkipList（跳表），Key就是HBase中KeyValue的Key的構(gòu)成（rowkey,family,Qualifier,Timestamp,Type)Data數(shù)據(jù)塊Block’TypeCell（KeyValue）CellCell……BlockType:塊的類型Cell：KeyValue鍵值對(duì)KeyLenValueLenRowLenRowCFLenCFColTimeStampKeyTypeValue7.3LSM樹一般來說，LSM中存儲(chǔ)的是多個(gè)KeyValue組成的集合，每個(gè)KeyValue都會(huì)用一個(gè)字節(jié)數(shù)組來表示。字節(jié)數(shù)組設(shè)計(jì)如下：KeyValue存儲(chǔ)格式KeyLenValLenRowkeyLenRowkeyBytesfamilyLenfamilyBytesqualifierBytesTimestamp(8B)Type(1B)valueBytes(1B)qualifierBytes:存儲(chǔ)Qualifier的二進(jìn)制內(nèi)容。注意：HBase并沒有單獨(dú)分配字節(jié)用來存儲(chǔ)qualifierLen，但可以計(jì)算出來qualifierLen=keyLen-rowkeyLen-familyLen-8B-1B7.3LSM樹type:表示這個(gè)keyValue操作的類型，HBase內(nèi)由Put、Delete、DeleteColumn、DeleteFamily等。該字段表明了LSM樹內(nèi)存的不只是數(shù)據(jù)，而是每一次操作記錄.Value部分直接存儲(chǔ)在valueBytes中，所以，字節(jié)數(shù)組主要是key部分的設(shè)計(jì)。1.KeyValue存儲(chǔ)格式keyLen(4B)valLen(4B)rowkeyLen(2B)rowkeyBytesfamilyLen(1B)familyBytesqualifierBytestimestamp(8B)type(1B)valueBytes(1B)7.3LSM樹LSM樹中KeyValue的Key部分，有3個(gè)字段必不可少：RowKey的二進(jìn)制內(nèi)容一個(gè)表示版本號(hào)的64位long值，在HBase中對(duì)應(yīng)timestamp；這個(gè)版本號(hào)通常表示數(shù)據(jù)的寫入先后順序，版本號(hào)越大的數(shù)據(jù)，越優(yōu)先被用戶讀取。Type,表示這個(gè)KeyValue的操作類型。本質(zhì)上，LSM存儲(chǔ)的并非只有數(shù)據(jù)，而是操作記錄。這正是LSM（LogStructuredMerge-Tree）中l(wèi)og的含義。7.3LSM樹LSM樹的索引結(jié)構(gòu)ConcurrentSkipListMemStorehfilehfilehfilehfilehfileflushMemoryDiskCompaction7.3LSM樹為了避免flush操作影響寫入性能，會(huì)先把當(dāng)前寫入的MemStore設(shè)為Snapshot，不再容許新的寫入操作寫入這個(gè)MemStore，另外一個(gè)內(nèi)存空間作為MemStore。Snapshot的MemStore寫入完畢，對(duì)應(yīng)內(nèi)存空間就可釋放。因此，可以通過兩個(gè)MemStore來實(shí)現(xiàn)穩(wěn)定的寫入性能。整個(gè)寫入過程，LSM樹都是append操作（磁盤順序?qū)懭耄瑳]有seek+write（磁盤隨機(jī)寫入）。LSM樹是一種對(duì)寫入極為友好的索引結(jié)構(gòu)。7.3LSM樹磁盤上的數(shù)據(jù)文件隨著數(shù)據(jù)的增加不斷增多，對(duì)用戶的讀取請(qǐng)求，需要進(jìn)行大量的多路歸并操作。需要將key值相同的數(shù)據(jù)全局綜合起來(Compaction)，選擇合適的版本返回給用戶。/u010761477/article/details/898863397.3LSM樹compaction（異步）操作分為兩種類型：majorcompact：不宜頻繁使用優(yōu)點(diǎn)：合并之后只有一個(gè)文件，讀取性能最高缺點(diǎn)：合并所有的文件需要很長(zhǎng)時(shí)間，消耗大量帶寬。minorcompact優(yōu)點(diǎn)：局部的compact操作，少了IO減少文件個(gè)數(shù)，提升讀取性能缺點(diǎn)：全局操作，無法在合并過程中完成。7.3LSM樹寫入操作轉(zhuǎn)換為磁盤的順序?qū)懭?，極大提高了寫入的效率。對(duì)讀取操作不十分友好設(shè)計(jì)了異步的compaction操作，提高讀取性能HBase選擇LSM樹這種索引結(jié)構(gòu)是最合適的總結(jié)7.4布隆過濾器在使用新聞客戶端看新聞時(shí)，它會(huì)給我們不停地推薦新的內(nèi)容，它每次推薦時(shí)要去重，去掉那些已經(jīng)看過的內(nèi)容。當(dāng)推薦系統(tǒng)推薦新聞時(shí)會(huì)從每個(gè)用戶的歷史記錄里進(jìn)行篩選，只將不在歷史記錄中的新聞推薦給用戶。如何快速查找呢？將哈希與位圖結(jié)合，即布隆過濾器7.4布隆過濾器如何高效判斷元素w是否存在于集合A中？哈希表對(duì)于小數(shù)據(jù)集是比較有效的解決策略布隆過濾器由一個(gè)長(zhǎng)度為N的0，1數(shù)組組成。首先將數(shù)組array每個(gè)元素都設(shè)置為0.對(duì)集合A中的每個(gè)元素w,做K次哈希，第i次哈希值對(duì)N取模得到一個(gè)index(i)，index(i)=HASH_i(w)%N，將array數(shù)組中的array[index(i)]置為1。7.4布隆過濾器舉例A={x,y,z},M=18,K

=3

初始化array=000000000000000000對(duì)元素x，HASH_0(x)%M=1,HASH_1(x)%M=5,HASH_2(x)%M=1301234567891011121314151617010001000000010000012345678910111213141516177.4布隆過濾器對(duì)元素y，HASH_0(y)%M=4,HASH_1(y)%M=11,HASH_2(y)%M=1601001100000101001001234567891011121314151617對(duì)元素z，HASH_0(z)%M=3,HASH_1(z)%M=5,HASH_2(z)%M=11010111000001010010012345678910111213141516177.4布隆過濾器若有一個(gè)元素w，K次HASH值為：HASH_0(w)%M=4,HASH_1(w)%M=13,HASH_2(w)%M=15由于布隆過濾器的第15位為0，因此可以確定w肯定不在集合A中010111000001010010012345678910111213141516177.4布隆過濾器若有一個(gè)元素t，K次HASH值為：HASH_0(t)%M=5,HASH_1(t)%M=11,HASH_2(t)%M=13布隆過濾器的各位都為1，但是不能確定t是否在A中010111000001010010012345678910111213141516177.4布隆過濾器對(duì)于任意給定的元素w，運(yùn)用布隆過濾器可得到兩個(gè)結(jié)論w可能存在于A中w肯定不存在于A中010111000001010010012345678910111213141516177.4布隆過濾器k為哈希函數(shù)個(gè)數(shù)，m為布隆過濾器長(zhǎng)度，n為插入的元素個(gè)數(shù)，p為誤報(bào)率?？臻g復(fù)雜度是O(m)，時(shí)間復(fù)雜度是O(k)，插入、查詢的時(shí)間復(fù)雜度都是O(k)數(shù)據(jù)庫原理TheTheoryofDatabaseSystem第八章MySQL數(shù)據(jù)庫操作MySQL簡(jiǎn)介MySQL查詢語句MySQL數(shù)據(jù)庫的基本操作開發(fā)平臺(tái)與MySQL的連接MySQL數(shù)據(jù)庫的備份、恢復(fù)和安全本講主要內(nèi)容8.1MySQL簡(jiǎn)介8.1.1MySQL（1）MySQL是目前最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。（2）MySQL是最好的RDBMS應(yīng)用軟件之一。（3）MySQL將數(shù)據(jù)存于表中，通過外鍵建立關(guān)聯(lián)。

（1）查詢速度快，靈活性高。

（2）社區(qū)版性能卓越。

（3）軟件體積小、運(yùn)行速度快、開放源碼。8.1.2MySQL特點(diǎn)

8.1MySQL簡(jiǎn)介MySQL采用的是客戶機(jī)/服務(wù)器（Client/Server）體系結(jié)構(gòu)：（1）一個(gè)是在數(shù)據(jù)服務(wù)器上的數(shù)據(jù)庫服務(wù)程序。（對(duì)應(yīng)Server端）（2）連接到數(shù)據(jù)庫服務(wù)器、用于用戶和服務(wù)器交互的客戶機(jī)程序。（對(duì)應(yīng)Client端）8.2MySQL的體系結(jié)構(gòu)客戶機(jī)/服務(wù)器體系結(jié)構(gòu)優(yōu)點(diǎn)：（1）服務(wù)器提供并發(fā)控制，使兩個(gè)用戶不能同時(shí)修改相同的記錄。（2）不必在數(shù)據(jù)庫所在的機(jī)器上注冊(cè)。8.2MySQL的體系結(jié)構(gòu)

8.3MySQL的查詢語言

MySQL使用結(jié)構(gòu)化查詢語言（StructuredQueryLanguage，SQL）與服務(wù)器通訊。8.3.1表、列和數(shù)據(jù)類型表是數(shù)據(jù)在一個(gè)MySQL數(shù)據(jù)庫中的存儲(chǔ)機(jī)制。8.3.1表、列和數(shù)據(jù)類型3.2表中的列描述該表所表示的實(shí)體的屬性，每個(gè)列都有一個(gè)名字及各自的特性。列由兩部分組成：數(shù)據(jù)類型(datatype)和長(zhǎng)度(length)。8.3.1表、列和數(shù)據(jù)類型 MySQL常用的數(shù)據(jù)類型有數(shù)值類型、字符串類型及日期類型等。函數(shù)：（1）函數(shù)(function)是存儲(chǔ)在數(shù)據(jù)庫中的代碼塊。（2）函數(shù)可以把計(jì)算機(jī)結(jié)果直接返回給調(diào)用的SQL。8.3.2函數(shù)和SQL語句SQL語句：（1）非過程化程序設(shè)計(jì)語言。（2）簡(jiǎn)單易學(xué)8.4MySQL數(shù)據(jù)庫的安裝

（1）可以官方獲取安裝文件。（2）推薦下載MySQL社區(qū)版服務(wù)器。（3）最新版本和歷史版本各有優(yōu)點(diǎn)。（4）注意最新版本需要python3.6支持。8.5MySQL數(shù)據(jù)庫的基本操作

8.5.1數(shù)據(jù)庫操作（1）登錄：mysql-h-uroot-p（2）創(chuàng)建：createdatabase數(shù)據(jù)庫名（3）顯示存在數(shù)據(jù)庫：showdatabases（4）刪除數(shù)據(jù)庫：dropdatabase數(shù)據(jù)庫名創(chuàng)建表：創(chuàng)建表之前需要用“use數(shù)據(jù)庫名”來選擇當(dāng)前操作的數(shù)據(jù)庫。createtable表名(屬性名數(shù)據(jù)類型[完整性約束條件],

屬性名數(shù)據(jù)類型[完整性約束條件], ......

屬性名數(shù)據(jù)類型[完整性約束條件]);8.5.2數(shù)據(jù)庫表的操作8.5.2數(shù)據(jù)庫表的操作創(chuàng)建表的時(shí)候創(chuàng)建索引：createtable表名(屬性名數(shù)據(jù)類型[完整性約束條件],

屬性名數(shù)據(jù)類型[完整性約束條件], ......

屬性名數(shù)據(jù)類型[完整性約束條件]，[unique|fulltext|spatial]index|key [別名](屬性名1[(長(zhǎng)度)][asc|desc]));8.5.2數(shù)據(jù)庫表的操作【例7-1】假定需要在學(xué)生數(shù)據(jù)庫（StudentDB）中創(chuàng)建一張學(xué)生表，要求包括學(xué)生的標(biāo)識(shí)信息(id，自增)，學(xué)號(hào)（code，varchar(9)，主鍵），姓名（name，varchar(20)，非空），性別（sex，varchar(2)），出生日期（birth，date），所在學(xué)院（school，school，非空），所在班級(jí)（class，varchar(10)，非空），戶籍所在地（location，varchar(16)，非空），入學(xué)成績(jī)（score，float，非空，默認(rèn)值為0）。createtablestudent(idintnotnullauto_incrementprimarykey, codevarchar(10)notnull, namevarchar(20)notnull, sexvarchar(2), birthdate, schoolvarchar(20)notnull, classvarchar(10)notnull, locationvarchar(16)notnull, scorefloatnotnulldefault0.0, uniqueindexcodeidx(codeasc));8.5.2數(shù)據(jù)庫表的操作8.5.2數(shù)據(jù)庫表的操作查看表的基本定義:describe表名;查看表默認(rèn)的存儲(chǔ)引擎和字符編碼:showcreatetable表名;修改表名:showcreatetable表名;修改字段的數(shù)據(jù)類型:altertable表名modify屬性名數(shù)據(jù)類型;增加字段:altertable表名add屬性名1數(shù)據(jù)類型[完整性約束條件][first|after屬性名2];刪除字段:altertable表名drop屬性名;修改字段的排列位置:altertable表名modify屬性名1數(shù)據(jù)類型first|after屬性名2;….8.5.3數(shù)據(jù)庫視圖操作創(chuàng)建視圖：CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]刪除視圖：DROPVIEW[IFEXISTS]view_name[,view_name2]...修改視圖：參考創(chuàng)建視圖語法中的ORREPLACE關(guān)鍵字。查看視圖：showcreateviewview_name8.5.3數(shù)據(jù)庫視圖操作（1）普通查詢select*from表名8.5.4數(shù)據(jù)操作語言8.5.4數(shù)據(jù)操作語言（2）查詢特定行select*from表名where屬性=實(shí)體屬性名8.5.4數(shù)據(jù)操作語言（3）查詢特定列select屬性名from表名where屬性=實(shí)體屬性名8.5.4數(shù)據(jù)操作語言（4）條件查詢使用WHERE或者HAVING從句（HAVING表達(dá)的是第二條件，需要與GROUPBY從句配合使用，不能在WHERE子句中的項(xiàng)目使用HAVING。）算術(shù)運(yùn)算符比較運(yùn)算符邏輯運(yùn)算符運(yùn)算符說明運(yùn)算符說明+加*乘-減/除運(yùn)算符說明運(yùn)算符說明<

小于!=或<>不等于<=小于等于>=大于等于=等于>

大于運(yùn)算符說明NOT或！邏輯非OR或||邏輯或AND或&&邏輯與8.5.4數(shù)據(jù)操作語言（4）條件查詢查找性別為男并且分?jǐn)?shù)大于528的學(xué)生情況

8.5.4數(shù)據(jù)操作語言（5）模糊查詢

對(duì)于查詢條件不明確的情況下，可以使用’like’關(guān)鍵字對(duì)帶有通配符的條件進(jìn)行查詢。常用的通配符查詢姓名中帶有“李”字的學(xué)生情況。通配符說明%包含零個(gè)或多個(gè)字符組成的任意字符串_(下劃線)任意一個(gè)字符8.5.4數(shù)據(jù)操作語言（6）查詢排序

使用orderby子句對(duì)查詢返回的結(jié)果按一列或多列排序。orderby子句的語法格式為：ORDERBYcolumn_name[ASC|DESC][,…]

按分?jǐn)?shù)升序排序8.5.4數(shù)據(jù)操作語言（7）查詢分組與統(tǒng)計(jì)groupby關(guān)鍵字可以將查詢結(jié)果按照某個(gè)字段或多個(gè)字段進(jìn)行分組。

GROUPBY屬性名[HAVING條件表達(dá)式][WITHROLLUP]GROUPBY關(guān)鍵字通常與集合函數(shù)一起使用。函數(shù)說明COUNT()用于統(tǒng)計(jì)記錄的條數(shù)SUM()用于計(jì)算字段的值的總和AVG()用于計(jì)算字段的值的平均值MAX()用于查詢字段的最大值MIN()用于查詢字段的最小值8.5.4數(shù)據(jù)操作語言（7）查詢分組與統(tǒng)計(jì)

統(tǒng)計(jì)每班學(xué)生姓名。8.5.4數(shù)據(jù)操作語言（8）多表查詢

內(nèi)連接（innerjoin）：select*from表1[inner]join表2on表1和表2之間的連接條件在student表和class_table表中查詢姓名為王靜的學(xué)生情況8.5.4數(shù)據(jù)操作語言（8）多表查詢

外連接：分為左連接、右連接和完全連接。左連接：select*from表1leftjoin表2on表1和表2之間的連接條件右連接：select*from表1rightjoin表2on表1和表2之間的連接條件

8.6開發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

MySQL通過連接器（Connector）和API為Java、C++、.NET、PHP、Python、C等語言開發(fā)的客戶端提供訪問數(shù)據(jù)庫提供驅(qū)動(dòng)程序和訪問接口。同時(shí)MySQL提供了ODBC、Java(JDBC)、Perl、Ruby等語言（連接驅(qū)動(dòng)）的數(shù)據(jù)庫訪問實(shí)例程序。8.6開發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

8.6.1Java語言訪問MySQL（1）建立lib文件夾，將連接的JAR文件拷貝到lib目錄下。（2）在Eclipse上右鍵單擊工程項(xiàng)目名稱，在菜單中選擇Javabuildpath項(xiàng)目。（3）在彈出的界面中選擇Libraries分頁，點(diǎn)擊AddJARs...按鈕，將拷貝進(jìn)去的JAR文件加入項(xiàng)目中。（4）在需要訪問數(shù)據(jù)庫的代碼中引入MySQLforJavaConnector的類庫。8.6開發(fā)平臺(tái)與MySQL數(shù)據(jù)的連接

8.6.2.NET平臺(tái)訪問MySQL（1）創(chuàng)建相關(guān)工程項(xiàng)目。（2）添加對(duì)MySQLConnector的引用。（3）在彈出對(duì)話框中，選擇“瀏覽”，找到MySQLConnectorforNet組件的安裝位置，打開Assemblies文件夾下的v4.0或v4.5文件夾，選擇MySql.Data.dll動(dòng)態(tài)鏈接庫，選擇完成之后點(diǎn)擊“確定”按鈕。（4）在需要訪問MySQL數(shù)據(jù)庫的頁面中使用“usingMySql.Data.MySqlClient;”。8.7MySQL數(shù)據(jù)庫的備份與恢復(fù)

最常用的MySQL數(shù)據(jù)庫備份與恢復(fù)方式：mysqldump（1）mysqldump備份mysqldump–hhostname-P端口-u用戶名-p密碼(–database)數(shù)據(jù)庫名>文件名.sql（2）還原數(shù)據(jù)庫mysql–hhostname–uusername–ppassworddatabasename<文件名.sql（3）導(dǎo)入數(shù)據(jù)庫mysql>source/home/sa/文件名.sqlMySQL的安全性主要由系統(tǒng)的內(nèi)部安全和網(wǎng)絡(luò)安全兩部分來決定。

保障MySQL數(shù)據(jù)庫的安全性需要以下配置：（1）修改root用戶口令，刪除空口令

（2）刪除默認(rèn)數(shù)據(jù)庫和數(shù)據(jù)庫用戶

（3）改變默認(rèn)MySQL管理員帳號(hào)

（4）關(guān)于密碼的管理

（5）使用獨(dú)立用戶運(yùn)行MySQL

（6）禁止遠(yuǎn)程連接數(shù)據(jù)庫8.8MySQL數(shù)據(jù)庫的安全

（7）限制連接用戶的數(shù)量（8）用戶目錄權(quán)限限制（9）命令歷史記錄保護(hù)（10）禁止MySQL對(duì)本地文件存取（11）MySQL服務(wù)器權(quán)限控制（12）使用Chroot方式來控制MySQL的運(yùn)行目錄（13）客戶程序的安全性保障8.8MySQL數(shù)據(jù)庫的安全

數(shù)據(jù)庫原理TheTheoryofDatabaseSystem第九章數(shù)據(jù)庫應(yīng)用案例本章重點(diǎn)以煤礦采掘銜接計(jì)劃管理系統(tǒng)為例介紹數(shù)據(jù)庫的應(yīng)用開發(fā)本講主要內(nèi)容9.1引言本章以煤礦采掘銜接計(jì)劃管理系統(tǒng)和企業(yè)管理信息支撐平臺(tái)為應(yīng)用實(shí)例，展示基于C/S和基于B/S結(jié)構(gòu)的數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)過程。兩個(gè)項(xiàng)目主要從項(xiàng)目需求概要、數(shù)據(jù)流圖、數(shù)據(jù)庫設(shè)計(jì)、系統(tǒng)流程、功能設(shè)計(jì)等角度展開，立足于工程項(xiàng)目的具體業(yè)務(wù)需求，介紹基于數(shù)據(jù)庫的信息管理系統(tǒng)開發(fā)過程。（1）煤礦生產(chǎn)劃分為瓦斯抽放、綜機(jī)配套、巷道掘進(jìn)、原煤開采（回采）等工程項(xiàng)目。（2）以回采為龍頭的各專業(yè)的高效組織和有序銜接，以及對(duì)生產(chǎn)區(qū)隊(duì)、生產(chǎn)設(shè)備等資源的合理配置。9.2.1需求概要

9.2煤礦采掘銜接計(jì)劃管理系統(tǒng)（1）頂層數(shù)據(jù)流圖9.2.2數(shù)據(jù)流圖（2）一層數(shù)據(jù)流圖9.2.2數(shù)據(jù)流圖9.2.2數(shù)據(jù)流圖（3）二層數(shù)據(jù)流圖（以生產(chǎn)區(qū)隊(duì)管理為例）二層數(shù)據(jù)流圖-生產(chǎn)區(qū)隊(duì)管理（4）數(shù)據(jù)字典：數(shù)據(jù)字典是描述數(shù)據(jù)的信息集合，是對(duì)系統(tǒng)中使用的所有數(shù)據(jù)元素的定義的集合。9.2.2數(shù)據(jù)流圖

（1）功能設(shè)計(jì)

根據(jù)二層數(shù)據(jù)流圖，對(duì)煤礦采掘銜接計(jì)劃系統(tǒng)的業(yè)務(wù)進(jìn)一步分解為功能模塊圖。煤礦采掘銜接計(jì)劃系統(tǒng)的業(yè)務(wù)功能主要由兩大模塊組成：基礎(chǔ)數(shù)據(jù)管理系統(tǒng)和計(jì)劃編制管理系統(tǒng)。9.2.3