MySQL數據庫項目化教程微課版V2項目七：

數據庫索引與視圖項目任務知識目標學習目標任務1：索引的創建與刪除任務2：視圖的創建與管理（1）掌握索引、視圖的含義和作用（2）了解索引的分類（3）掌握創建、刪除索引的方法（4）掌握創建、修改、更新、

刪除視圖的方法能力目標素質目標學習目標（1）能夠創建索引（2）能夠刪除索引（3）能夠創建視圖（4）能夠查看、修改視圖（5）能夠更新、刪除視圖（1）形成自主好學的學習態度（2）養成務實解決問題的習慣（3）培養團隊協作的精神課程思政（素養提示）學習目標（1）不斷更新理念，利用先進思維方法解決問題，引導學生注重先進理論學習及思維拓展。（2）形成對實際數據的保護意識，引導學生注重維護數據安全。知識導圖學習目標任務分析任務實現任務描述Part

2Part

3Part

1任務一

索引創建與刪除過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務一

索引創建與刪除本節的任務是創建和刪除索引。創建索引可以通過兩種不同的方法：一是創建表的時候創建索引；二是在已存在的表中創建索引。對已存在的不必要索引進行刪除，也可以利用兩種方式：一是使用ALTERTABLE刪除索引；二是DROPINDEX刪除索引。任務描述過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務一

索引創建與刪除根據索引的應用范圍和查詢需求的不同，索引分成：（1）普通索引

（2）唯一索引

（3）主鍵索引（4）全文索引

（5）單列索引

（6）多列索引（7）空間索引任務分析索引是對數據庫中單列或者多列的值進行排序后的一種特殊數據庫結構，利用它可以快速指向數據庫中的數據表的特定記錄，索引是提高數據庫性能的重要方式。過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務一

索引創建與刪除1.創建表的時候創建索引CREATETABLE表名(字段名數據類型[約束條件],字段名數據類型[約束條件]……字段名數據類型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[別名](字段名1[(長度)][ASC|DESC]));語法格式任務實現[UNIQUE|FULLTEXT|SPATIAL]可選參數，分別表示唯一索引、全文索引和空間索引。INDEX和KEY為同義詞，兩者作用相同，用來指定創建索引。別名是可選參數，用來給創建的索引取新的名字。字段名為需要創建索引的字段，該字段必須從數據表中定義的多個字段中選擇。長度為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度。ASC或DESC指定升序或降序的索引值存儲。(1)創建普通索引competion數據庫中，創建一張student數據表，并為其字段st_id創建普通索引。對應代碼任務實現(2)唯一索引在competion數據庫中，創建一張department數據表，并為其字段dp_name創建唯一索引。對應代碼任務實現(3)主鍵索引在competion數據庫中，創建一張teacher數據表，并為其字段tc_id創建主鍵索引。對應代碼任務實現(4)單列索引在competion數據庫中，創建一張project數據表，并為其字段pr_name創建單列索引對應代碼任務實現(5)多列索引在competion數據庫中，創建一張class數據表，并為其創建字段class_id和class_name設置多列索引對應代碼任務實現(6)全文索引在competition數據庫中，創建一張st_project數據表，并為其字段remark創建全文索引對應代碼任務實現(7)空間索引在competition數據庫中，創建一張admin數據表，并為其字段ad_name創建空間索引。對應代碼任務實現2.在已存在的表創建索引--使用CREATEINDEX創建索引CREATE

[UNIQUE|FULLTEXT|SPATIAL]INDEX別名ON表名(字段名1[(長度)][ASC|DESC]));語法格式任務實現[UNIQUE|FULLTEXT|SPATIAL]可選參數，分別表示唯一索引、全文索引和空間索引。INDEX和KEY為同義詞，兩者作用相同，用來指定創建索引。屬性名為需要創建索引的字段列，該列必須從數據表中該定義的多個列中選擇。長度為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度。ASC或DESC指定升序或降序的索引值存儲。（1）在已存在的表創建普通索引在competion數據庫中，為已經存在的student1表的字段st_no添加名為index_st_no的普通索引。對應代碼任務實現步驟1：添加索引之前用SHOWCREATETABLE語句查看student1表的結構（1）在已存在的表創建普通索引對應代碼任務實現步驟2：輸入如下代碼：CREATEINDEXindex_st_noONstudent1(st_no);步驟3：應用SHOWCREATETABLE語句再查看student1表結構（2）在已存在的表創建唯一索引對應代碼任務實現步驟1：輸入如下代碼：CREATEUNIQUEINDEXuni_st_idONstudent1(st_id);步驟2：應用SHOWCREATETABLE語句再查看student1表結構在competition數據庫中，為已經存在的student1表的字段st_id添加名為uni_st_id的唯一索引。（3）在已存在的表創建多列索引對應代碼任務實現步驟1：輸入如下代碼：CREATEINDEXmulti_stONstudent1(st_name，st_no);步驟2：應用SHOWCREATETABLE語句再查看student1表結構在competition數據庫中，為已經存在的student1表的字段st_no和st_name添加名為multi_st的多列索引。2.在已存在的表創建索引--使用ALTERTABLE創建索引ALTERTABLE表名

ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名[(長度)][ASC|DESC])語法格式任務實現[UNIQUE|FULLTEXT|SPATIAL]可選參數，分別表示唯一索引、全文索引和空間索引。INDEX和KEY為同義詞，兩者作用相同，用來指定創建索引。屬性名為需要創建索引的字段列，該列必須從數據表中該定義的多個列中選擇。長度為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度。ASC或DESC指定升序或降序的索引值存儲。（1）使用ALTERTABLE創建普通索引在competition數據庫中，為已經存在的student1表的字段st_no添加名為index_st_no的普通索引。對應代碼任務實現步驟1：添加索引之前用SHOWCREATETABLE語句查看student1表的結構，發現該表未對任何字段設置索引。對應代碼任務實現步驟2：輸入如下代碼：altertablestudent1addIndexmulti(st_no,st_name);步驟3：應用SHOWCREATETABLE語句再查看student1表結構（1）使用ALTERTABLE創建普通索引（2）使用ALTERTABLE創建唯一索引在competition數據庫中，為已經存在的student1表的字段st_id添加名為uni_st_id的唯一索引。對應代碼任務實現步驟1：輸入如下代碼：ALTERTABLEstudent1ADDUNIQUEINDEXuni_st_id(st_id);對應代碼任務實現步驟1：輸入如下代碼：ALTERTABLEstudent1ADDINDEXmulti_st(st_name，st_no);（3）使用ALTERTABLE創建多列索引在competition數據庫中，為已經存在的student1表的字段st_no和st_name添加名為multi_st的多列索引。對應代碼任務實現步驟1：輸入如下代碼：ALTERTABLEstudent1ADDFULLTEXTINDEXfull_st_info(st_info);（3）使用ALTERTABLE創建多列索引在competition數據庫中，為已經存在的student1表的字段st_info添加名為full_st_info的全文索引。對應代碼任務實現（3）使用ALTERTABLE創建索引使用SHOWCREATETABLE語句再次查看student1表的結構，可發現，名為uni_st_id的唯一索引、名為multi_st的多列索引和名為full_st_info的全文索引添加成功3.刪除索引DROPINDEX別名ON表名;語法格式任務實現（1）使用DROPINDEX刪除索引在competition數據庫中，將student1表的字段st_no設置的普通索引刪除。例題代碼：DROPINDEXindex_st_noONstudent1;3.刪除索引ALTERTABLE表名

DROPINDEX別名;語法格式任務實現（2）使用

ALTERTABLE刪除索引在competition數據庫中，將student1表的字段st_name和st_no設置的多列索引刪除。例題代碼：ALTERTABLEstudent1DROPINDEXmulti_st;注意事項任務總結本節結合學生技能競賽管理系統中的各個表，講解了MySQL數據庫的索引的基礎知識，通過任務介紹了創建索引和刪除索引的方法。讀者應該重點掌握創建索引的兩種方法。任務總結在實際項目中，讀者應結合查詢速度、磁盤空間、維護開銷等因素嘗試多個不同的索引從而建立最優的索引。過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務二

視圖的創建與管理以學生技能競賽管理系統為例，在MySQL上對單表或多表創建視圖，并對視圖進行查看、修改、更新、刪除等管理，從而快速掌握視圖的相關知識和技能任務描述過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務二

視圖的創建與管理任務分析

視圖和真實的表一樣，包含一系列帶有名稱的列和行數據。在數據庫中視圖并不以存儲的數據值集形式存在。它是一個虛擬表，視圖的數據來自當前或其它數據庫的一個或多個表，或者其它視圖。通過視圖進行查詢沒有任何限制當對視圖的數據進行修改時，相應的基本表也會發生變化，當基本表的數據發生變化時，視圖的數據也會產生變化。任務分析與直接從數據表中讀取對比，視圖有以下幾個優點（1）視圖能簡化操作（2）視圖能增加安全性（3）視圖能增加邏輯數據獨立性視圖的缺點：（1）性能：從數據庫視圖查詢數據可能會很慢，特別是如果視圖是基于其他視圖創建的。（2）表依賴關系：將根據數據庫的基礎表創建一個視圖。每當更改與其相關聯的表的結構時，都必須更改視圖任務分析創建視圖時，需要注意以下幾點：（1）定義視圖的用戶必須對所參照的表或視圖有查詢(可執行SELECT語句)權限。（2）在定義中引用的表或視圖必須存在。（3）創建視圖時默認是在當前數據庫創建的，如果需要在指定數據庫創建需要指定具體數據庫名稱。任務分析以下幾種情況是不能更新視圖（1）視圖中包含COUNT()、SUM()、MAX()和MIN()等函數。（2）視圖中包含UNION、UNIONALL、DISTINCT、GROUPBY和HAVIG等關鍵字。（3）常量視圖。（4）視圖中的SELECT中包含子查詢。（5）創建視圖時，設置ALGORITHM為TEMPTABLE類型。過渡頁任務分析任務實現Part

1Part

2Part

3任務描述任務二

視圖的創建與管理1.創建視圖CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(字段名)]ASSELECT語句[WITH[CASCADED|LOCAL]CHECKOPTION];語法格式任務實現ORREPLACE是可選參數，表示該語句替換已有視圖。ALGORITHM是可選參數，表示視圖選擇的算法屬性名為視圖的列定義明確的名稱，多個列名由逗號隔開。AS表示指定視圖要執行的操作。SELECT語句參數是一個完整的查詢語句，表示從表中查出滿足條件的記錄，將這些記錄導入視圖中,可在SELECT語句中查詢多個表或視圖。WITHCHECKOPTION是可選參數。CASCADED是可選參數WITHCHECKOPTION是可選參數(1)在單表上創建視圖為數據庫competition中的student表創建視圖view_st_man，包含男生的st_no，st_name，st_sex字段的信息。對應代碼任務實現步驟1：在單表上創建視圖前，用SELECT語句查看student表的完整信息。對應代碼任務實現步驟2：輸入如下代碼：CREATEVIEWst_man_viewASSELECTst_no,st_name,st_sexFROMstudentWHEREst_sex='男';步驟3：視圖st_man_view創建后，通過SELECT語句查看視圖中的數據(1)在單表上創建視圖(2)在多表上創建視圖為數據庫competition中的department表和class表創建一個視圖dp_class_view，視圖內容包括dp_name，class_name，class_grade字段。對應代碼任務實現步驟1：在多表上創建視圖前，用SELECT語句查看department表和class表的完整信息。對應代碼任務實現步驟2：輸入如下代碼：CREATEVIEWdp_class_viewASSELECTdepartment.dp_name,class.class_name,class.class_gradeFROMdepartment,classWHEREdepartment.dp_id=class.dp_id;步驟3：視圖dp_class_view創建后，通過SELECT語句可查看視圖中的數據(2)在多表上創建視圖2.查看視圖DESCRIBE視圖名;語法格式任務實現（1）DESCRIBE語句查看視圖使用DESCRIBE語句查看上文中創建的視圖dp_class_view例題代碼：DESCRIBEdp_class_view;2.查看視圖SHOWTABLESTATUSLIKE‘視圖名’;語法格式任務實現（2）SHOWTABLESTATUS語句查看視圖使用SHOWTABLESTATUS語句查看上一節中創建的視圖dp_class_view的定義信息。例題代碼：SHOWTABLEstatusLIKE'dp_class_view'\G2.查看視圖SHOWCREATEVIEW視圖名;語法格式任務實現（3）SHOWCREATEVIEW語句查看視圖使用SHOWCREATEVIEW語句查看上一節中創建的視圖dp_class_view的定義信息。例題代碼：SHOWCREATEVIEWdp_class_view\G3.修改視圖對應代碼任務實現（1）CREATEORREPLACEVIEW語句修改視圖使用CREATEORREPLACEVIEW語句修改視圖view_st_man，由原來的st_no，st_name，st_sex等字段更換為st_no，st_name，st_sex，st_password4個字段。3.修改視圖ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW視圖名[(字段名)]ASSELECT語句[WITH[CASCADED|LOCAL]CHECKOPTION];語法格式任務實現（2）ALTERVIEW語句修改視圖使用ALTERVIEW語句修改上一節中創建的視圖st_man_view，把視圖中的字段改為st_no,st_sex字段的信息4.更新視圖對應代碼任務實現（1）UPDATE語句更新視圖使用UPDATE語句更新視圖dp_class_view中class_name字段對應的數據值，將原來的數值“財務管理”改為“電子商務”。例題代碼：UPDATEdp_class_viewSETclass_name='電子商務'WHEREclass_name='財務管理';4.更新視圖對應代碼任務實現（2）使用INSERT語句更新視圖使用INSERT語句向class表中插入一條記錄，從而使視圖dp_class_view對應增加一條記錄。輸入SELECT*FROMclass語句查看更新后原表class中的數據4.更新視圖對應代碼任務實現（3）使用DELETE語句刪除視圖記錄使用DELETE語句刪除視圖st_man_view中的學號為“2017060205”的一條記錄，從而使student表中對應的記錄也刪除。例題代碼：DELETEFROMst_man_viewWHEREst_no='2017060205';5.刪除視圖DROPVIEW[IFEXISTS]視圖名列表[RESTRICT|CASCADE]語法格式任務實現IFEXISTS是可選參數，判斷視圖是否存在，不存在不執行，存在則執行。“視圖名列表”參數表示要刪除的視圖的名稱和列表，各個視圖名稱之間用逗號隔開。RESTRICT確保只有不存在相關視圖和完整性約束的視圖才能刪除。CASCADE任何相關視圖和完整