第7章

視圖和索引《數(shù)據(jù)庫原理及應(yīng)用教程（MySQL）》主要內(nèi)容視圖01OPTION03OPTION索引02OPTIONMySQL視圖管理ViewMySQLViewIndex04OPTIONMySQL索引管理MySQLIndex7.1視圖1.視圖定義

視圖是在一個、多個基本表或視圖基礎(chǔ)上，通過查詢語句定義的虛擬表格?；颈硐嗨疲阂晥D可用于SELECT語句中進行查詢VS視圖不同：視圖只存儲定義語句，不存儲其數(shù)據(jù)當使用視圖進行查詢時，視圖包含的數(shù)據(jù)才會臨時生成。7.1視圖2.視圖的作用視圖提升了數(shù)據(jù)的邏輯獨立性視圖可用于數(shù)據(jù)集成視圖提升了數(shù)據(jù)操作的便攜性視圖提升了數(shù)據(jù)的安全性3.視圖的工作機制視圖中的內(nèi)容總是與基本表中數(shù)據(jù)保持一致，即當基本表中數(shù)據(jù)發(fā)生變化時，相關(guān)視圖的數(shù)據(jù)也隨之變化。主要內(nèi)容視圖01OPTION03OPTION索引02OPTIONMySQL視圖管理ViewMySQLViewIndex04OPTIONMySQL索引管理MySQLIndex7.2MySQL視圖管理1.創(chuàng)建視圖CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER={user|CURRENT_USER}]VIEW視圖名[(視圖字段列表)]AS查詢語句[WITH[CASCADED|LOCAL]CHECKOPTION];添加ORREPLACE可選參數(shù)表明可以在創(chuàng)建視圖時替換數(shù)據(jù)庫已有同名視圖。ALGORITHM表示視圖的使用方法。其中，UNDEFINE表示由MySQL自行決定使用方法。當省略視圖字段時，使用查詢語句的字段名稱作為視圖的字段名稱。AS指明視圖的定義，其后由一個完整的SELECT語句構(gòu)成WITHCHECKOPTION表示更新、修改和插入視圖數(shù)據(jù)時，只有滿足檢查條件操作才會執(zhí)行。7.2MySQL視圖管理例子創(chuàng)建信息學院學生視圖s_view（1）在一張基本表上創(chuàng)建視圖CREATEVIEWs_viewASSELECT*FROMsWHEREdept='信息學院';（2）在多張基本表上創(chuàng)建視圖創(chuàng)建學生選課情況視圖s_sc_c_view，視圖字段列表為學號sno、姓名sname、課程名cname及成績score例子CREATEVIEWs_sc_c_view(sno,sname,cname,score)ASSELECTs.sno,sn,cn,scoreFROMs,c,scWHEREs.sno=sc.snoANDo=o;7.2MySQL視圖管理例子在信息學院的學生視圖s_view基礎(chǔ)上，創(chuàng)建計算機專業(yè)學生視圖s_maj_view（3）在視圖上創(chuàng)建視圖CREATEVIEWs_maj_viewASSELECT*FROMs_viewWHEREmaj='計算機';不能在SELECT語句中包含系統(tǒng)、用戶變量以及處理語句參數(shù)，同時不能在FROM子句中包含子查詢。視圖創(chuàng)建的注意事項刪除視圖依賴的基本表后，視圖使用會報錯，可通過“CHECKTABLE表名”檢查基本表狀態(tài)。不能為臨時表創(chuàng)建視圖。創(chuàng)建視圖時，ALGORITHM參數(shù)指定對部分查詢結(jié)果具有影響。7.2MySQL視圖管理（4）使用MySQLWorkbench創(chuàng)建視圖1、可在Navigator窗格的Schema選項卡下，右鍵點擊視圖所在數(shù)據(jù)庫的Views節(jié)點，從菜單中選擇CreateView選項。2、將創(chuàng)建視圖語句輸入到右側(cè)DDL后的文本框中。Name文本框的內(nèi)容會按照輸入語句自動調(diào)整。語句檢查無誤后，點擊Apply按鈕提交語句。7.2MySQL視圖管理2.查看視圖的定義DESCRIBE視圖名稱;查看視圖s_view的結(jié)構(gòu)信息例子DESCRIBEs_view;SHOWCREATEVIEW視圖名稱;查看視圖s_view的結(jié)構(gòu)信息例子SHOWCREATEVIEWs_view;7.2MySQL視圖管理2.查看視圖的定義使用MySQLWorkbench查看視圖的定義信息1、在Navigator窗格的Schema選項卡下，展開視圖所在Views節(jié)點，右鍵點擊查看定義信息的視圖名稱，從彈出菜單中選擇CopyToClipboard（粘貼到剪切板）選項.2、然后再選擇CreateStatement（創(chuàng)建語句）選項，將視圖語句粘貼到剪切板上。7.2MySQL視圖管理3.修改視圖的定義使用CREATEORREPLACEVIEW語句修改視圖的定義ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER={user|CURRENT_USER}]VIEW視圖名[(視圖列表)]AS查詢語句[WITH[CASCADED|LOCAL]CHECKOPTION];修改s_view視圖，按s_id、s_name和s_maj字段名稱顯示理學院學生的學號、姓名和專業(yè)例子ALTERVIEWs_view(s_id,s_name,s_maj)ASSELECTsno,sn,majFROMsWHEREdept='理學院';使用CREATEORREPLACEVIEW修改視圖時，需要檢查修改視圖名稱的正確性，若名稱錯誤，則不會替換原視圖，反而會按照錯誤名稱新建視圖。.7.2MySQL視圖管理3.修改視圖的定義使用MySQLWorkbench修改視圖的定義1、在Navigator窗格的Schema選項卡下，右鍵點擊視圖所在數(shù)據(jù)庫的Views節(jié)點，右鍵點擊需要修改的視圖名稱，從彈出菜單中選擇AlterView選項.2、在Workbench右側(cè)的SQL腳本編輯器中會出現(xiàn)當前視圖的定義語句，修改該視圖的定義并點擊Apply按鈕即可提交修改后的視圖定義。7.2MySQL視圖管理4.刪除視圖DROPVIEW[IFEXISTS]視圖名稱1[,...][RESTRICT|CASCADED];刪除視圖s_view例子DROPVIEWs_view;使用DROPVIEW語句可刪除一個或多個已有視圖。在Navigator窗格的Schema選項卡下，展開視圖所在Views節(jié)點，右鍵點擊需要刪除的視圖名稱，從彈出菜單中選擇DropView選項.7.2MySQL視圖管理5.更新視圖的數(shù)據(jù)由于視圖是一張由基本表建立的虛表，所以對視圖進行數(shù)據(jù)更新操作將轉(zhuǎn)換成對基本表中數(shù)據(jù)的更新操作。視圖的本質(zhì)是方便查詢或保護數(shù)據(jù)，因此，當對視圖執(zhí)行數(shù)據(jù)更新操作時，有些情況是不可能的，如視圖依賴于多張基本表。注意：構(gòu)建用于更新數(shù)據(jù)的學生s_update_view，提供學生表中的所有信息CREATEVIEWs_update_viewASSELECT*FROMs;例子使用INSERT向視圖中插入數(shù)據(jù)INSERTINTOs_update_viewVALUES('s10','韓義','男',19,'計算機','信息學院');使用UPDATE修改視圖中數(shù)據(jù)UPDATEs_update_viewSETage=20WHEREsno='s10';使用DELETE刪除視圖中數(shù)據(jù)DELETEFROMs_update_viewWHEREsno='s10';7.2MySQL視圖管理5.更新視圖的數(shù)據(jù)的限制注意：視圖字段列表或查詢語句中包含了聚合函數(shù)。視圖字段列表或查詢語句是通過表達式或計算得到的。視圖定義語句中包含DISTINCT關(guān)鍵字或GROUPBY、ORDERBY、HAVING子句。視圖定義的查詢語句中使用了集合查詢UNION或UNIONALL。視圖的列來自于查詢語句中子查詢的列。創(chuàng)建視圖時ALGORITHM為TEMPTABLE類型。視圖依賴于其他不可更新視圖建立。主要內(nèi)容視圖01OPTION03OPTION索引02OPTIONMySQL視圖管理ViewMySQLViewIndex04OPTIONMySQL索引管理MySQLIndex7.3索引１.索引的作用在很多數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫讀取的次數(shù)多于數(shù)據(jù)庫寫的次數(shù)如果把數(shù)據(jù)庫看做字典，可以將索引的鍵看做是字典的拼音，值為該拼音所在的第一個漢字的位置，借助拼音檢索可以縮小目標漢字查找范圍，避免逐頁查找。索引是一種以空間代價提升時間效率的方法。索引一旦創(chuàng)建，將由MySQL自動管理和維護，索引的維護需要消耗計算資源和存儲資源，如何設(shè)計索引，是提升數(shù)據(jù)庫使用效率的關(guān)鍵。如何優(yōu)化數(shù)據(jù)庫讀取數(shù)據(jù)效率是數(shù)據(jù)庫優(yōu)化的主要工作之一索引采用鍵值對的數(shù)據(jù)結(jié)構(gòu)，加快檢索速度7.3索引２.索引的類型可以從多種角度對索引進行分類：01020304根據(jù)索引與數(shù)據(jù)物理存儲關(guān)系進行分類聚集型索引、非聚集型索引根據(jù)索引涉及列數(shù)進行分類單列索引、復合索引根據(jù)索引特征進行分類普通索引、唯一索引、主鍵索引、全文索引和空間索引根據(jù)索引存儲方式進行分類B-Tree索引、Hash索引7.3索引３.索引的設(shè)置原則嚴格限制同一個表或視圖上的索引數(shù)量。索引增多將會嚴重影響INSERT、UPDATE和DELETE語句的執(zhí)行性能。對于表中使用頻度較低或者不再使用的索引，需及時刪除。對于重復值較多的列，不建議建立索引。對排序、分組或者表連接涉及的字段建立索引，可提升數(shù)據(jù)檢索效率。對視圖建立索引將提升使用視圖的檢索效率。注意唯一索引和全文對于NULL的處理方式。7.3索引4.索引原理概述索引本質(zhì)上是一種數(shù)據(jù)結(jié)構(gòu)，通過預先存儲數(shù)據(jù)的物理位置，避免查找時逐行掃描數(shù)據(jù)表，提升檢索效率。Hash索引B+樹索引核心分類7.3索引4.索引原理概述哈希索引是一種基于哈希表（HashTable）的索引結(jié)構(gòu)，其核心思想是通過哈希函數(shù)將查詢條件中的數(shù)值快速映射到存儲位置，適合在等值條件查詢（=或in）中實現(xiàn)接近常數(shù)級的時間復雜度。7.3索引4.索引原理概述B+樹索引是一種基于B樹擴展的數(shù)據(jù)結(jié)構(gòu)，其結(jié)構(gòu)特點以平衡多叉樹的形式組織索引數(shù)據(jù)，樹中非葉子節(jié)點存儲索引的數(shù)值，葉子節(jié)點存儲實際數(shù)據(jù)記錄的位置且葉子節(jié)點之間通過指針串聯(lián)成鏈表。主要內(nèi)容視圖01OPTION03OPTION索引02OPTIONMySQL視圖管理ViewMySQLViewIndex04OPTIONMySQL索引管理MySQLIndex7.4MySQL索引管理１.索引的創(chuàng)建CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名稱ON表名稱(字段名稱[(索引字符長度)[ASC|DESC]][,...]);UNIQUE|FULLTEXT|SPATIAL為可選參數(shù)，用于指明索引類型。ON關(guān)鍵字指明索引針對的表。ASC和DESC表明索引的排序方式。當字段名稱[(索引字符長度)[ASC|DESC]]只有一項時，將建立單列索引。當字段名稱[(索引字符長度)[ASC|DESC]]有多項時，將建立復合索引。使用CREATEINDEX語句創(chuàng)建索引7.４

MySQL索引管理１.索引的創(chuàng)建為學生表s的姓名字段（sn）建立普通索引s_name_index，索引針對sn的前6個字節(jié)且以降序方式排列CREATEINDEXs_name_indexONs(sn(6)DESC);例子為課程表c的課程名（cn）和學時（ct）字段建立復合唯一索引c_cn_ct_index。例子CREATEUNIQUEINDEXc_cn_ct_indexONc(cn,ct);7.4MySQL索引管理１.索引的創(chuàng)建CREATETABLE表名(屬性名1數(shù)據(jù)類型[列完整性約束],...屬性名n數(shù)據(jù)類型[列完整性約束],[表約束],[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名1](字段名稱[(索引字符長度)[ASC|DESC]]),...[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名n](字段名稱[(索引字符長度)[ASC|DESC]]));創(chuàng)建表格時直接附帶創(chuàng)建索引7.４

MySQL索引管理１.索引的創(chuàng)建創(chuàng)建教室表classroom，包含自增主鍵cid，教室編號crno（非空字符串），教室教學樓名稱cbn（非空字符串）。創(chuàng)建classroom表時，附加由教室編號（crno）和教室名稱（cbn）構(gòu)成的普通唯一索引cn_cb_index。CREATETABLEclassroom(cidINTAUTO_INCREMENT,crnoVARCHAR(10)NOTNULL,cbnVARCHAR(10)NOTNULL,PRIMARYKEY(cid),UNIQUEINDEXcn_cb_index(crno,cbn));例子7.4MySQL索引管理１.索引的創(chuàng)建ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名1](字段名稱[(索引字符長度)[ASC|DESC]]),...ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名n](字段名稱[(索引字符長度)[ASC|DESC]])；通過ALTERTABLE為已有表添加索引為教師表t中的教師姓名（tn）添加索引tn_index，索引長度為6且使用降序排列。ALTERTABLEtADDINDEXtn_index(tn(6)DESC);例子7.4MySQL索引管理１.索引的創(chuàng)建在Navigator窗格的Schema選項卡下，展開Tables節(jié)點下索引所在表，右鍵Indexs下的PRIMARY節(jié)點，從彈出的右鍵菜單中，選擇CREATEINDEX選項使用MySQLWorkbench創(chuàng)建索引在MySQLWorkbench右側(cè)窗口中，選擇需要創(chuàng)建索引的列，然后點擊CreateIndexForSelectedColumns按鈕，輸入索引名稱、索引字段的長度、索引類型、索引算法和并發(fā)鎖管理策略等信息，7.4MySQL索引管理２.索引的查看SHOWINDEXFROM表名[FROM數(shù)據(jù)庫名];使用SHOWINDEX語句查看已有表或視圖上的索引信息查看當前數(shù)據(jù)庫下學生表s的索引信息。S