MySQL索引與視圖項目8【能力目標】

掌握MySQL索引的概念及作?用。熟悉MySQL索引的創建、刪除操?作。掌握MySQL中視圖的定義及功?能。掌握MySQL視圖的各種操作。【素養目標】引導學生建立使用正確、先進的方法解決問題的意識，培養遵守規則的職業素?養。目標學習導航圖8-1項目8所講內容在數據庫系統開發中的位置認識索引8.18.1.1理解索引的概念

索引是將數據表中的某列或某些列與記錄的位置建立一個對應關系，在查找內容之前可以先在目錄中查找索引位置，從而運用索引快速定位查詢數據。MySQL中的索引會保存在額外的文件中。索引類似于字典中的目錄，查找字典內容時可以根據目錄查找到記錄所在位?置。如果沒有索引，那么將從數據表的第一條記錄一條條往下查找，直到找到該條信息為止。有了索引，特定字段將會通過一定的方法存儲，以便在查詢該字段的信息時，快速找到對應的數據，而不必從頭遍歷數據。但是需要注意的是，MySQL的索引單獨存放在文件中會產生額外的開?銷。8.1.2了解索引的分類根據索引定義的語法格式不同，可以將索引大致分為普通索引、唯一索引、主鍵索引、全文索引和空間索引5類。1．普通索引普通索引是MySQL中最基本、最常用的索引類型。索引的建立沒有限制條件，可以實現加速查詢。2.唯一索引唯一索引對建立索引的列有唯一性要求，即列值必須唯一，但建立唯一索引的列值可以取空值（NULL）。唯一索引使用UNIQUEINDEX定?義。3.主鍵索引主鍵索引的建立要求對應列的取值必須唯一且不能為NULL，默認名為PRIMARY。4.全文索引全文索引用于實現記錄的全文搜索。僅適用于CHAR、VARCHAR和TEXT數據類型?列。5.空間索引空間索引是基于空間數據類型的字段建立的索引，MySQL允許使用SPATIALINDEX在非空的幾何值列上創建索?引。管理索引8.28.2.1創建索引

在MySQL中創建索引的常見方法有：直接使用CREATEINDEX語句創建、在創建數據表（CREATETABLE）語句中創建或對已經創建的數據表添加索引（ALTERTABLE）。

1．直接創建索引使用CREATEINDEX語句是建立索引最直接、最簡單快捷的方式，但該語句不能創建主鍵。其語法格式如?下。CREATEINDEX索引名ON數據表名(列名[，…][ASC|DESC])

其中，各參數的含義如?下。索引名：要創建的索引的名字。一個表可以創建多個索引，但每個索引在該表中的名字是唯一?的。數據表名：指定要創建索引的數據表?名。列名：指定要創建索引的列?名。ASC|DESC：可選項，指定索引按照升序或降序排?列。

USEssms;CREATEINDEXxh_stuONstudent(S_ID(5)DESC);【例8-1】在表student中的列S_ID上創建一個降序索引xh_stu。【例8-2】在表course中創建C_ID和C_Name兩個字段的組合索引c_info。CREATEINDEXc_infoONcourse(C_ID,C_name);8.2.1創建索引

2．在創建表時創建索引

在創建數據表時建立索引的語法格式如?下，其中，索引類型通常表示為USING{BTREE|HASH}，字段列表指代字段名[(長度)[ASC|DESC]]。8.2.1創建索引CREATETABLE數據表名(列定義,…PRIMARYKEY[索引類型](字段列表),[UNIQUE|FULLTEXT|SPATIAL]{INDEX|KEY}[索引名][索引類型](字段列表),…)[表選項];

【例8-3】在數據庫ssms中創建表user_info,在列username上建立索引，并在列u_card上建立唯一索?引。8.2.1創建索引CREATETABLEuser_info(user_idchar(6)notnull,usernamechar(8)notnull,u_cardchar(12)null,INDEX(username),UNIQUEINDEX(u_card));

3．在修改表時創建索引

針對已經存在的數據表，要為表添加索引，可以使用直接創建索引的方法，但是直接創建索引的方法不能向數據表中添加主鍵索引；此外可以使用ALTERTABLE語句實現，其基本語法格式如?下。8.2.1創建索引ALTERTABLE數據表名ADD[UNIQUE|FULLTEXT|SPATIAL]{INDEX|KEY}[索引類型](字段列表);【例8-4】在表user_info的列user_id上創建主鍵索?引。ALTERTABLEuser_infoADDPRIMARYKEY(user_id);

【例8-5】在表elective的S_ID與C_ID兩列上創建唯一索?引。8.2.1創建索引ALTERTABLEelectiveADDunique(s_id,C_ID);用戶一旦在數據表上完成索引的創建，通常會查看數據表中已有索引的情況，在MySQL中查看索引最簡單的方法是使用SHOWINDEXFROM數據表?名。【例8-6】查看表user_info中已經創建的索引的情?況。SHOWINDEXFROMuser_info;

如果用戶不再需要數據表中的索引，則應及時將其刪除，以免占用系統資源，影響數據庫自身性能。MySQL提供了兩種方法用于刪除索引。與創建索引相對應的是可以直接使用DROP語句刪除，也可以使用ALTERTABLE語句刪除索引。相較于前者，后者可以用于刪除主鍵索?引。1．直接刪除索引可以直接對數據表使用DROP語句來刪除索引，其語法格式如?下。8.2.2

刪除索引DROPINDEX索引名ON數據表名；【例8-7】刪除表student中的索引xh_stu。DROPINDEXxh_stuONstudent;

2．使用修改表語句刪除索引使用ALTERTABLE語句中的DROP子句也可以刪除索引，相較于直接刪除，該方法實現起來稍復雜，但功能更強大。其語法格式如?下。8.2.2

刪除索引ALTERTABLE數據表名DROPINDEX索引名,|DROPPRIMARYKEY,|DROPFOREIGNKEY外鍵約束名;使用修改表的方法，不僅可以刪除索引，還可以刪除主鍵（主鍵索引）及外鍵約?束。【例8-8】刪除表elective中S_ID與C_ID兩列的唯一索?引。ALTERTABLEelectiveDROPindexS_ID;【例8-9】刪除表user_info中的主鍵及主鍵索?引。ALTERTABLEuser_infoDROPprimarykey;使用命令SHOWINDEXFROMuser_info查看表user_info中的索引情況，執行結果如圖8-4所?示。圖8-4刪除表user_info的主鍵及索引后的結果8.2.2

刪除索引1．索引的優缺點索引的優點創建索引能大大提高數據的檢索速?度。創建唯一索引，能夠保證數據庫表中每一行記錄的唯一?性。通過使用索引，可以在查詢的過程中使用優化隱藏器，提高系統的性?能。索引的缺點創建索引和維護索引都要耗費時間，并且隨著數據量的增大所耗費的時間也會增?加。索引需要占用空間。因為數據表中的數據也會有上限設置，如果大量創建索引，則索引文件可能會比數據文件更快達到上限?值。當對數據表中的數據進行增加、刪除、修改時，索引也需要動態維護，這降低了數據的維護速?度。8.2.3索引的優缺點及使用原則2．索引的使用原則

通過對索引優點和缺點的分析可知，并不是每個字段都設置索引才好，也不是索引越多越好，而是要根據需要合理使?用。對于需要經常更新的數據表，應避免對其設置過多的索引，對于經常用于查詢的字段，則應該創建索?引。數據量小的數據表最好不要使用索引，因為數據較少，可能查詢全部數據花費的時間比遍歷索引的時間還要短，此時索引不會產生優化效?果。不同值少的列（字段上）不要建立索引，例如，學生表的“性別”字段只有“男”“女”兩個不同值。相反，一個字段有較多不同值時可以建立索?引。8.2.3索引的優缺點及使用原則認識視圖8.3

視圖是虛擬表或邏輯表，它被定義為具有連接的SELECT查詢語句。視圖是一個基于一個表或多個表的邏輯表，它只是一個查詢語句的結果，本身并不包含任何數據，它的數據最終是從表中獲取的，這些表通常稱為源表或基表。當基表的數據發生變化時，視圖里的數據同樣會發生變?化。1．視圖的概念說明視圖僅僅是表的結構,沒有表的數?據。視圖的結構和數據都是建立在已有數據表的基礎上?的。視圖中的數據可以來自一個表或者多個?表。視圖中數據的添加、更新和刪除都會影響到其對應的數據?表。8.3.1理解視圖的概念2．視圖與數據表的區別與聯系與數據庫中數據表的靜態存儲不同，視圖是動態的且與物理模式無關。數據庫系統將視圖存儲為具有連接的SELECT語句。當表的數據發生變化時，視圖也會反映這些數據的變?化。視圖可以看作數據庫數據的特定子集。運用視圖可以實現所有用戶不直接訪問數據庫的數據表，而要求用戶只能通過視圖操作數據的目的。運用這種方法可以保護用戶和應用程序不受某些數據庫修改的影?響。8.3.1理解視圖的概念

與直接操作基表相比，視圖具有明顯的優勢，主要表現在以下5個方?面。視圖可以簡化用戶的數據操作過?程。

視圖可以實現用戶根據相同的數據集進行多角度查看所需數據的目?的。

視圖對重構數據庫提供了一定程度的邏輯功?能。視圖能夠對機密數據提供安全保?護。適當利用視圖可以更加清晰地表達查?詢。8.3.2了解視圖的優點管理視圖8.4

視圖就是一條SELECT語句執行后返回的結果集。所以創建視圖的主要工作是創建這條SQL查詢語?句。創建視圖的語法格式如?下。CREATE[ORREPLACE]VIEW<視圖名>[(<列名>[,<列名>]…)]

ASSELECT語句

[WITH

CHECK

OPTION];

該語句能創建新的視圖，如果指定了ORREPLACE子句，則表示如果已存在同名視圖就替換已有視?圖。

其中AS后面的子查詢可以為任意復雜的SELECT語句，但通常不允許包含ORDERBY子句和DISTINCT子?句。8.4.1創建視圖2．視圖的分類創建視圖時應根據數據表及查詢建立4種視?圖。（1）行列子集視圖若一個視圖是從單數據表中導出的，僅選取了基表的部分行或列，并保留了主鍵，則將這類視圖稱為行列子集視?圖。【例8-10】在表student中創建軟件工程專業學生的視圖sw_stu。CREATEVIEWsw_stuASSELECTS_ID,Name,BirthdayFROMstudentWHEREMajor='軟件工程';8.4.1創建視圖【例8-11】建立信息安全專業學生的視圖is_stu，并要求后續的更新等操作僅作用于信息安全專業的學?生。CREATEVIEWis_stuASSELECTS_ID,Name,Major,Sex,BirthdayFROMstudentWHEREMajor='信息安全'WITHCHECKOPTION;

語法格式中的WITHCHECKOPTION表示在對視圖執行UPDATE、INSERT和DELETE操作時，要保證進行更新、插入或刪除的行必須滿足視圖定義中的謂詞條件（即子查詢中的條件表達式）。（2）多表視圖創建視圖不僅可以基于單個數據表，還可以基于多個數據表，甚至可以在已有的視圖基礎上再創建視圖，這類視圖稱為多表視?圖。【例8-12】創建選修了課程101的軟件工程專業的學生視?圖。CREATEVIEWsw_101ASSELECTstudent.S_ID,Name,GradeFROMstudent,electiveWHEREMajor='軟件工程'ANDC_ID='101'ANDstudent.S_ID=elective.S_ID;

8.4.1創建視圖

【例8-13】創建軟件工程專業選修了課程101，且課程成績在90分以上的學生視?圖。CREATEVIEWsw_101_90ASSELECTS_ID,Name,GradeFROMsw_101WHEREGrade>=90;（3）帶表達式的視圖

MySQL的數據表為降低數據庫中數據的冗余度，在表中僅存放基礎數據，而可經過運算所得的數據列一般不存儲在數據表?中。

MySQL的視圖并不在數據庫中進行物理存儲，所以在創建視圖時可以根據應用需要計算得到派生屬性?列。由于計算所得派生列在數據庫的數據表中并不存在，所以也稱為虛擬列，帶虛擬列的視圖稱為帶表達式的視?圖。【例8-14】創建包含學生年齡列的視?圖。CREATEVIEWage_stuASSELECTS_ID,Name,YEAR(NOW())-YEAR(Birthday)ASageFROMstudent;8.4.1創建視圖（4）分組視圖如果在視圖創建過程中運用到了聚合函數或帶有GROUPBY子句的查詢，則創建的視圖稱為分組視?圖。【例8-15】創建一個視圖顯示學生學號及平均成?績。CREATEVIEWstu_avg(學號,平均成績)ASSELECTS_ID,AVG(Grade)FROMelectiveGROUPBYS_ID;8.4.1創建視圖

【例8-15】中AS子句中的SELECT語句的目標列平均成績需要通過AVG()聚合函數得到，因此CREATEVIEW語句中必須明確定義組成視圖stu_avg的所有屬性列名。最終得到的視圖stu_avg是一個分組視?圖。

一旦定義好了視圖，就可以像查詢數據表中的數據那樣查詢視圖了。因此，從用戶的角度而言，查詢視圖和查詢數據表的操作是相同的。

查詢視圖的語法格式與查詢數據表的語法格式類似，只需將數據表名修改為視圖名即?可。SELECT視圖列名1，視圖列名2，…FROM視圖名WHERE條件定義語句8.4.2查詢視圖【例8-16】在軟件工程專業的學生視圖sw_stu中查找出2002年出生的學生的姓名及出生日?期。SELECTName,BirthdayFROMsw_stuWHEREYEAR(Birthday)='2002';

在MySQL中，修改視圖有兩種方式：第一種是使用CREATEORREPLACEVIEW語句；第二種是使用ALTERVIEW語?句。1．使用CREATEORREPLACEVIEW語句修改視圖使用CREATEORREPLACEVIEW語句修改視圖時，如果修改的視圖已經存在，則將修改已有視圖，如果操作對象不存在，那么將創建一個視?圖。語法格式如?下。CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(視圖列名1，視圖列名2…)]ASSELECT語句[WITH[CASCADED|LOCAL]CHECKOPTION]8.4.3修改視圖【例8-17】使用CREATEORREPLACEVIEW語句修改視圖sw_stu。CREATEORREPLACEVIEWsw_stuASSELECT*FROMstudentWHEREMajor='軟件工程';

【例8-17】中的視圖sw_stu是在【例8-10】中已經建立完成的，所以為已存在的視圖。此處使用REPLACE修改了原有視圖的定?義。使用SELECT語句查詢視圖，結果如圖8-6所示，其中包括軟件工程專業學生的所有字段內?容。8.4.3修改視圖使用CREATEORRPLACEVIEW語句修改后的視圖

2．使用ALTERVIEW語句修改視圖ALTERVIEW語句是MySQL提供的另一種修改視圖的方法，其語法格式如?下。ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(視圖列名1，視圖列名2…)]ASSELECT語句[WITH[CASCADED|LOCAL]CHECKOPTION]8.4.3修改視圖【例8-18】使用ALTERVIEW語句修改視圖sw_stu。ALTERVIEWsw_stuASSELECTS_ID,Name,Major,BirthdayFROMstudentWHEREMajor='軟件工程';

MySQL中的更新視圖是指通過視圖插入、刪除和更新數據。由于視圖是MySQL中并未實際存儲數據的虛擬表，因此更新視圖在本質上更改的是數據表中的數據。但從用戶角度來看，更新視圖與更新數據表的操作結果是完全相同的，在關系DBMS中對視圖的更新操作實際也是對數據表的更新操?作。1．使用INSERT語句通過視圖插入數據在更新視圖的過程中，可以對視圖使用INSERT語句向源數據表中插入數?據。8.4.4

更新視圖【例8-19】利用視圖is_stu插入一條數據（'201112','王小明','信息安全',1,'2003-06-18'）。INSERTINTOis_stuVALUES('201112','王小明','信息安全',1,'2003-06-18');

通常，在MySQL中，為了防止用戶通過視圖對數據進行更新時無意中更改不在視圖范圍內的源數據表中的數據內容，可以在定義視圖時加上WITHCHECKOPTION子句。對帶有WITHCHECKOPTION子句的視圖進行增、刪、改數據的操作時，MySQL會檢查視圖定義中的條件，若不滿足條件，則拒絕執行該操作。例如，在【例8-11】中定義視圖is_stu時，就帶有WITHCHECKOPTION子句，如果在【例8-19】中將插入數據中的專業修改為“軟件工程”，則會出現圖8-9所示的錯?誤。8.4.4

更新視圖圖8-9WITHCHECKOPTION的限制

2．使用UPDATE語句通過視圖更新數據可以使用UPDATE語句操作視圖更新源數據表中的數據，操作語句與更新表數據的操作相?同。【例8-20】更新信息安全專業學生視圖is_stu，將學號為201112的學生的姓名修改為“王森”。8.4.4

更新視圖UPDATEis_stuSETName='王森'WHERES_ID='201112';查詢視圖is_stu，結果如圖8-10所示，可以看到，學號為201112的學生姓名已由原來的“王小明”更新為“王森”。3．使用DELETE語句通過視圖刪除數據可以使用DELETE語句通過視圖刪除源數據表中的數?據。【例8-21】利用視圖is_stu刪除學號為201112的學生的數?據。8.4.4

更新視圖DELETEFROMis_stuWHERES_ID='201112';執行查詢視圖操作，結果如圖8-11所示，可以看到源數據表中學號為201112的學生記錄已經被刪除。

刪除視圖是指刪除數據庫中已存在的視圖。由于視圖本身并沒有實際的物理存儲，所以刪除視圖時只是刪除視圖的定義，并不會刪除數據本身，即對生成視圖的源數據表沒有任何影?響。刪除視圖的語法格式如?下。8.4.5刪除視圖DROPVIEW[IFEXISTS]視圖名1[,視圖名2,…];其中，IFEXISTS用于判斷視圖是否已經存在，若視圖存在，則執行刪除操作；若不存在，則不執行。如果需要同時刪除多個視圖，則視圖名之間使用逗號分?隔。【例8-22】刪除【例8-14】中創建的包含學生年齡列的視圖age_stu。DROPVIEWage_stu;【小結】本項目重點講解了MySQL數據庫中索引與視圖的概念及作用，并對索引的創建、刪除及視圖的創建、查詢、修改、刪除等操作進行了介紹。其中索引的作用與操作、視圖的創建及查詢是本項目的重點內容，在實際應用中會經常使用到，并在安全層面保證了數據表的使用安全，避免用戶直接修改數據表本?身。【任務訓練8】圖書管理系統數據庫中索引與視圖的操作1．實驗目的掌握運用MySQL中索引與視圖的操作方?法。掌握索引的創建、查看和刪除的方?法。掌握創建、修改、更新和刪除視圖的操?作。2．實驗內容在bms數據庫的表中執行索引的創建、查看、刪除操?作。在bms數據庫中按照要求完成視圖的創建、修改、更新及刪除操?作。3．實驗步驟（1）執行索引的創建、查看、刪除操作

計算18+2.75、25/3、34%7的結?果。CREATEINDEXcid_rdONreaderinfo(card_id(8));

在表readerinfo的列card_id的前8個字符建立前綴索引cid_rd。

在表bookinfo的book_id及category_id兩列上建立組合索引bk_ic。CREATEINDEXbk_icONbookinfo(book_id,category_id);在數據庫bms中創建表book_reader，定義主鍵為br_id，建立列c_id并將其作為唯一索引uc_id。CREATETABLEbook_reader(br_idchar(10)notnullprimarykey,c_idchar(12)null,readerNamevarchar(8)notnull,uniquekeyuc_id(c_id));【任務訓練8】圖書管理系統數據庫中索引與視圖的操作

運用ALTER語句，將表borrowinfo的book_id與card_id兩列創建為組合索引bc_borrow。ALTERTABLEborrowinfoADDINDEXbc_borrow(book_id,card_id);SHOWINDEXFROMborrowinfo;查看表borrowinfo中索引的建立情?況。【任務訓練8】圖書管理系統數據庫中索引與視圖的操作刪除表book_reader中的所有索引并查看結?果。ALTERTABLEbook_readerDROPprimarykey,DROPindexuc_id;SHOWINDEXFROMbook_reader;【任務訓練8】圖書管理系統數據庫中索引與視圖的操作（2）建立、修改、更新及刪除視圖在數據庫bms中建立、修改、更新及刪除視?圖。創建男性讀者的視圖m_reader。CREATEVIEWm_readerASSELECT*FROMreaderinfoWHEREsex='男';

創建視圖reader_2017，內容為需要在2017年11月前還書的讀者的信?息。ALTERVIEWreader_2017ASSELECTreaderinfo.card_id,name,telFROMreaderinfoJOInborrowinfoUSING(card_id)WHEREreturn_date>='2017-7-1';CREATEVIEWreader_2017ASSELECTreaderinfo.card_id,name,telFROMreaderinfojOINborrowinfoUSING(card_id)WHEREreturn_dateBETWEEN'201