數(shù)據(jù)庫(kù)應(yīng)用技術(shù)（MySQL）（第二版） 課件 模塊3任務(wù)4視圖及索引的應(yīng)用

模塊三

使用數(shù)據(jù)庫(kù)數(shù)據(jù)表中存儲(chǔ)了數(shù)據(jù)后，為了提高數(shù)據(jù)的查詢速度，可以為數(shù)據(jù)表創(chuàng)建索引。為了提高數(shù)據(jù)的存取性能，保障數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性，可以結(jié)合實(shí)際需求靈活地運(yùn)用視圖。本任務(wù)主要介紹索引和視圖的相關(guān)概念及基本操作。任務(wù)描述任務(wù)4視圖及索引的應(yīng)用理解索引的概念以及索引的作用12掌握使用圖形化工具和SQL語(yǔ)句創(chuàng)建和管理索引的方法3理解視圖的概念以及視圖的作用學(xué)會(huì)通過(guò)視圖操作基本表中的數(shù)據(jù)5任務(wù)目標(biāo)理解掌握掌握理解學(xué)會(huì)4掌握使用圖形化工具和SQL語(yǔ)句創(chuàng)建和管理視圖的方法1.索引的基本概念索引是對(duì)數(shù)據(jù)表中一列或多列的值進(jìn)行排序后的一種結(jié)構(gòu)。當(dāng)執(zhí)行“SELECT*FROMtable1WHEREid=10000;”語(yǔ)句時(shí)，如果沒(méi)有索引，必須從第1行開(kāi)始遍歷，直到id等于10000的這一行被找到為止，這樣的查詢效率非常低。如果在id這一列上創(chuàng)建索引，就可以快速地找到了。數(shù)據(jù)庫(kù)索引的作用相當(dāng)于圖書(shū)的目錄，通過(guò)索引可以迅速找到表中指定的數(shù)據(jù)。索引概述知識(shí)點(diǎn)1知識(shí)準(zhǔn)備2.索引的類型MySQL中的索引主要有以下幾種類型：普通索引：是MySQL中的基本索引類型，沒(méi)有任何限制條件，通常使用關(guān)鍵字INDEX或KEY來(lái)定義。唯一索引：與普通索引基本相同，所不同的是，唯一索引所在列的值不能重復(fù)，即必須是唯一的，通常使用關(guān)鍵字UNIQUE來(lái)定義。主鍵：也是一種唯一索引，一個(gè)表只能有一個(gè)主鍵。主鍵一般是在創(chuàng)建表時(shí)指定，也可以通過(guò)修改表來(lái)設(shè)置主鍵，使用關(guān)鍵字PRIMARYKEY來(lái)定義。全文索引：只能在CHAR、VARCHAR或TEXT類型的列上創(chuàng)建全文索引，并且現(xiàn)在只有MyISAM和InnoDB存儲(chǔ)引擎支持全文索引。使用關(guān)鍵字FULLTEXT來(lái)定義。空間索引：只能創(chuàng)建在空間數(shù)據(jù)類型的列上，目前只有MyISAM存儲(chǔ)引擎支持空間索引，使用關(guān)鍵字SPATIAL來(lái)定義。對(duì)于初學(xué)者來(lái)說(shuō)，空間索引很少會(huì)用到。索引概述知識(shí)點(diǎn)1知識(shí)準(zhǔn)備1.創(chuàng)建索引可以在創(chuàng)建數(shù)據(jù)表的同時(shí)創(chuàng)建索引，也可以在已有的數(shù)據(jù)表上添加索引。使用SQL語(yǔ)句創(chuàng)建索引主要有以下幾種方法：（1）使用CREATEINDEX語(yǔ)句創(chuàng)建索引基本語(yǔ)法格式如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name[(length)][ASC|DESC],…)

索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備是可選項(xiàng)，UNIQUE指定創(chuàng)建唯一索引，F(xiàn)ULLTEXT指定創(chuàng)建全文索引，SPATIAL指定創(chuàng)建空間索引。指定要?jiǎng)?chuàng)建的索引名稱，一個(gè)表中索引名必須是唯一的。指定要?jiǎng)?chuàng)建索引的表名。指定要?jiǎng)?chuàng)建索引的列名。是可選項(xiàng)，指定索引按升序ASC還是降序DESC排列，默認(rèn)為ASC。是可選項(xiàng)，指定使用列的前l(fā)ength個(gè)字符來(lái)創(chuàng)建索引。說(shuō)明：使用CREATEINDEX語(yǔ)句不能創(chuàng)建主鍵。（2）使用ALTERTABLE語(yǔ)句創(chuàng)建索引基本語(yǔ)法格式如下：ALTERTABLEtable_nameADD[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name(column_name[(length)][ASC|DESC],…)索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備（3）使用CREATETABLE語(yǔ)句創(chuàng)建索引基本語(yǔ)法格式如下：CREATETABLEtable_name(column_namedata_type[column_constraint],…[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[index_name](column_name[(length)][ASC|DESC],…))索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備關(guān)鍵字KEY與INDEX是同義詞2.查看索引可以使用SHOWINDEX語(yǔ)句查看表中創(chuàng)建的索引?；菊Z(yǔ)法格式如下：SHOWINDEXFROMtable_name[FROMdatabase_name]索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備指定需要查看索引的表名指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)，可省略3.刪除索引為了避免影響數(shù)據(jù)庫(kù)性能，應(yīng)及時(shí)刪除不再使用的索引。刪除索引的方式有兩種：（1）使用DROPINDEX語(yǔ)句刪除索引基本語(yǔ)法格式如下：DROPINDEXindex_nameONtable_name索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備指定要?jiǎng)h除的索引名。指定索引所在的表的名稱。（2）使用ALTERTABLE語(yǔ)句刪除索引刪除表中的索引，其基本語(yǔ)法格式如下：刪除表中的主鍵索引，其基本語(yǔ)法格式如下：ALTERTABLEtable_nameDROPINDEXindex_name索引的基本操作知識(shí)點(diǎn)2知識(shí)準(zhǔn)備ALTERTABLEtable_nameDROPPRIMARYKEY視圖是從一個(gè)或多個(gè)基本表（或視圖）導(dǎo)出的表，是一個(gè)虛表。數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，并不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍然存放在原來(lái)的基本表中。基本表中的數(shù)據(jù)發(fā)生變化時(shí)，從視圖中查詢出的數(shù)據(jù)也會(huì)發(fā)生變化。與直接操作基本表相比，使用視圖具有以下優(yōu)點(diǎn)：簡(jiǎn)化操作：視圖不僅可以簡(jiǎn)化用戶對(duì)數(shù)據(jù)的理解，也可以簡(jiǎn)化對(duì)數(shù)據(jù)的操作。用戶不用詳細(xì)了解數(shù)據(jù)庫(kù)中復(fù)雜的表結(jié)構(gòu)和表連接，將經(jīng)常使用的查詢定義為視圖，可以有效地避免大量重復(fù)的操作。保護(hù)數(shù)據(jù)安全：用戶通過(guò)視圖只能查詢和修改他們所能見(jiàn)到的數(shù)據(jù)，數(shù)據(jù)庫(kù)中的其他數(shù)據(jù)既看不見(jiàn)也不可以訪問(wèn)，從而提高數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性。數(shù)據(jù)獨(dú)立：創(chuàng)建視圖后，應(yīng)用程序通過(guò)視圖訪問(wèn)數(shù)據(jù)表，如果數(shù)據(jù)表有變化，只需要調(diào)整視圖的定義，不用修改應(yīng)用程序代碼，使應(yīng)用程序和數(shù)據(jù)表在一定程度上獨(dú)立。視圖概述知識(shí)點(diǎn)3知識(shí)準(zhǔn)備1.創(chuàng)建視圖CREATEVIEW語(yǔ)句用于建立視圖，其基本語(yǔ)法格式如下：視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備CREATEVIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]指定要?jiǎng)?chuàng)建的視圖名稱，該名稱在數(shù)據(jù)庫(kù)中必須是唯一的可選項(xiàng)，指定視圖的列名列表，列名之間用逗號(hào)隔開(kāi)。默認(rèn)情況下，與SELECT語(yǔ)句中的列名相同。指定視圖要執(zhí)行的操作。定義視圖的SELECT語(yǔ)句，該語(yǔ)句可以使用多個(gè)表和其他視圖?？蛇x項(xiàng)，指定在可更新視圖上進(jìn)行修改時(shí)要滿足所指定的限制條件。有兩種方式：CASCADED為默認(rèn)方式，表示要滿足所有相關(guān)視圖和表定義的條件；LOCAL表示只要滿足該視圖本身定義的條件。重要提示：WITHCHECKOPTION只能和可更新視圖一起使用，雖然是可選項(xiàng)，為了保證數(shù)據(jù)的安全性，建議創(chuàng)建視圖最好加上。2.查看視圖（1）用DESCRIBE語(yǔ)句查看視圖DESCRIBE語(yǔ)句不僅可以查看數(shù)據(jù)表的列信息，還可以查看視圖的列信息，其基本語(yǔ)法格式如下：{DESCRIBE|DESC}view_name[column_name]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備DESC是DESCRIBE的簡(jiǎn)寫(xiě)，二者用法相同。指定要查看的視圖名。指定要查看的列名。（2）用SHOWTABLESTATUS語(yǔ)句查看視圖使用SHOWTABLESTATUS語(yǔ)句不僅可以查看數(shù)據(jù)表的狀態(tài)信息，還可以查看視圖的狀態(tài)信息，其基本語(yǔ)法格式如下：SHOWTABLESTATUSLIKE'pattern'視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要匹配的視圖名。（3）用SHOWCREATEVIEW語(yǔ)句查看視圖可以使用SHOWCREATEVIEW語(yǔ)句來(lái)查看已有視圖的定義，其基本語(yǔ)法格式如下：SHOWCREATEVIEWview_name視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要查看視圖的名稱。3.修改視圖可以使用ALTERVIEW語(yǔ)句對(duì)已有視圖的定義進(jìn)行修改，其基本語(yǔ)法格式如下：ALTERVIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備說(shuō)明：ALTERVIEW語(yǔ)句的各項(xiàng)參數(shù)與CREATEVIEW語(yǔ)句中的含義相同。4.刪除視圖可以使用DROPVIEW語(yǔ)句刪除視圖，其基本語(yǔ)法格式如下：DROPVIEW[IFEXISTS]view_name1[,view_name2…]視圖的基本操作知識(shí)點(diǎn)4知識(shí)準(zhǔn)備指定要?jiǎng)h除的視圖名。可以一次刪除多個(gè)視圖。任務(wù)實(shí)施1使用圖形化工具創(chuàng)建和管理索引2使用SQL語(yǔ)句創(chuàng)建和管理索引3使用圖形化工具創(chuàng)建和管理視圖4使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖【例1】在專家信息表texpertinfo中的專家姓名sName列上創(chuàng)建一個(gè)唯一索引“index_expert_name”。分析：在專家信息表texpertinfo中，經(jīng)常以專家姓名作為查詢條件，因此可以在專家姓名sName列上創(chuàng)建索引，以提高查詢速度。任務(wù)實(shí)施使用圖形化工具創(chuàng)建和管理索引1說(shuō)明：如果表中已有數(shù)據(jù)，在創(chuàng)建唯一索引時(shí)，MySQL將自動(dòng)檢驗(yàn)索引列是否存在重復(fù)的值，若存在重復(fù)的值，則創(chuàng)建唯一索引失敗?！纠?】創(chuàng)建表的時(shí)候創(chuàng)建索引。創(chuàng)建一個(gè)新表texperttemp，在表texperttemp的sExpertCode列上創(chuàng)建一個(gè)普通索引“index_expert_code”。任務(wù)實(shí)施使用圖形化工具創(chuàng)建和管理索引1字段名稱數(shù)據(jù)類型是否允許為空說(shuō)明iExpertIDint否專家編號(hào)(主鍵，自動(dòng)遞增)sExpertCodechar(6)是專家編碼sNamevarchar(50)是專家姓名1.創(chuàng)建索引【例3】使用CREATEINDEX語(yǔ)句在部門信息表tdeptinfo中的部門名稱sDeptName列上創(chuàng)建一個(gè)唯一索引“index_dept_name”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>CREATEUNIQUEINDEXindex_dept_name

->ONtdeptinfo(sDeptNameASC);QueryOK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0【例4】使用ALTERTABLE語(yǔ)句在項(xiàng)目信息表tprojectinfo上，根據(jù)項(xiàng)目名稱sProjectName列的前20個(gè)字符創(chuàng)建一個(gè)普通索引“index_project_name”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>ALTERTABLEtprojectinfo->ADDINDEXindex_project_name(sProjectName(20));QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0【例5】使用CREATETABEL語(yǔ)句創(chuàng)建索引。創(chuàng)建一個(gè)新表texperttemp，在表texperttemp的sExpertCode列上創(chuàng)建一個(gè)普通索引“index_expert_code”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>DROPTABLEIFEXISTStexperttemp;QueryOK,0rowsaffected(0.04sec)

mysql>CREATETABLEtexperttemp->(->iExpertIDintNOTNULLAUTO_INCREMENTPRIMARYKEY,->sExpertCodechar(6),->sNamevarchar(50),->INDEXindex_expert_code(sExpertCode)->);QueryOK,0rowsaffected(0.05sec)為了演示使用CREATETABEL語(yǔ)句創(chuàng)建索引的方法，先使用DROPTABLE語(yǔ)句刪除texperttemp表。字段名稱數(shù)據(jù)類型是否允許為空說(shuō)明iExpertIDint否專家編號(hào)(主鍵，自動(dòng)遞增)sExpertCodechar(6)是專家編碼sNamevarchar(50)是專家姓名2.查看索引【例6】查看部門信息表tdeptinfo中的索引。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>SHOWINDEXFROMtdeptinfoFROMbeems\G***************************1.row***************************Table:tdeptinfoNon_unique:0Key_name:PRIMARYSeq_in_index:1Column_name:iDeptIDCollation:ACardinality:37Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:Visible:YESExpression:NULL***************************2.row***************************Table:tdeptinfoNon_unique:0Key_name:index_dept_nameSeq_in_index:1Column_name:sDeptNameCollation:ACardinality:37Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:NULL2rowsinset(0.01sec)部門編號(hào)iDeptID列上的主鍵索引部門名稱sDeptName列上的唯一索引表示該索引是唯一索引3.刪除索引【例7】使用DROPINDEX語(yǔ)句刪除部門信息表tdeptinfo中的索引“index_dept_name”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>DROPINDEXindex_dept_nameONtdeptinfo;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0【例8】使用ALTERTABLE語(yǔ)句刪除項(xiàng)目信息表tprojectinfo中的索引“index_project_name”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建和管理索引2mysql>ALTERTABLEtprojectinfo->DROPINDEXindex_project_name;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0【例9】創(chuàng)建視圖“view_user_department”，要求顯示部門為“信息工程系”的用戶。任務(wù)實(shí)施使用圖形化工具創(chuàng)建和管理視圖31.創(chuàng)建視圖【例10】使用SQL語(yǔ)句創(chuàng)建視圖“view_expert_postgraduate”，要求顯示學(xué)歷為“碩士研究生”或“博士研究生”的專家信息。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖4mysql>CREATEVIEWview_expert_postgraduate->AS->SELECTsExpertCode,sName,sDiploma,sWorkUnitFROMtexpertinfo->WHEREsDiploma='碩士研究生'ORsDiploma='博士研究生'->WITHCHECKOPTION;QueryOK,0rowsaffected(0.01sec)創(chuàng)建視圖時(shí)沒(méi)有指定視圖的列名，因此視圖的列名與SELECT語(yǔ)句中的列名相同?！纠?1】使用SQL語(yǔ)句創(chuàng)建一個(gè)統(tǒng)計(jì)各學(xué)歷專家人數(shù)的視圖“view_expert_num”，要求顯示學(xué)歷和各學(xué)歷專家人數(shù)。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖4mysql>CREATEVIEWview_expert_num(sDiploma,expertNum)->AS->SELECTsDiploma,COUNT(iExpertID)FROMtexpertinfo->GROUPBYsDiploma;QueryOK,0rowsaffected(0.01sec)來(lái)自聚合函數(shù)COUNT()，則該視圖不允許更新，不能加WITHCHECKOPTION。2.查看視圖【例12】使用DESCRIBE語(yǔ)句查看視圖“view_expert_num”。任務(wù)實(shí)施mysql>DESCRIBEview_expert_num;+---------------+----------------------+----------+-----------+----------+----------+|Field |Type |Null |Key |Default |Extra |+---------------+----------------------+----------+-----------+----------+----------+|sDiploma |varchar(20) |YES | |NULL | ||expertNum |bigint |NO | |0 | |+---------------+----------------------+----------+-----------+----------+----------+2rowsinset(0.08sec)使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖4【例13】使用SHOWTABELSTATUS語(yǔ)句查看視圖“view_expert_num”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖4mysql>SHOWTABLESTATUSLIKE'view_expert_num'\G***************************1.row***************************Name:view_expert_numEngine:NULLVersion:NULLRow_format:NULLRows:NULLAvg_row_length:NULLData_length:NULLMax_data_length:NULLIndex_length:NULLData_free:NULLAuto_increment:NULLCreate_time:2022-06-1817:21:29Update_time:NULLCheck_time:NULLCollation:NULLChecksum:NULLCreate_options:NULLComment:VIEW1rowinset(0.00sec)【例14】使用SHOWCREATEVIEW語(yǔ)句查看視圖“view_expert_postgraduate”。任務(wù)實(shí)施使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖4mysql>SHOWCREATEVIEWview_expert_postgraduate\G***************************1.row***************************View:view_expert_postgraduate CreateView:CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQLSECURITYDEFINERVIEW`view_expert_postgraduate`ASselect`texpertinfo`.`sExpertCode`AS`sExpertCode`,`texpertinfo`.`sName`AS`sName`,`texpertinfo`.`sDiploma`AS`sDiploma`,`texpertinfo`.`sWorkUnit`AS`sWorkUnit`from`texpertinfo`where((`texpertinfo`.`sDiploma`='碩士研究生')or(`texpertinfo`.`sDiploma`='博士研究生'))WITHCASCADEDCHECKOPTIONcharacter_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ci1rowinset(0.00sec)3.修改視圖【例15】使用SQL語(yǔ)句修改視圖“view_expert_postgraduate”，增加一個(gè)新條件，要求只顯示男專家，視圖列名為專家編碼、專家姓名、學(xué)歷、工作單位。任務(wù)實(shí)施mysql>ALTERVIEWview_expert_postgraduate(專家編碼,專家姓名,學(xué)歷,工作單位)->AS->SELECTsExpertCode,sName,sDiploma,sWorkUnitFROMtexpertinfo->WHEREsDiploma='碩士研究生'ORsDiploma='博士研究生'ANDsSex='男'->WITHCHECKOPTION;QueryOK,0rowsaffected(0.02sec)使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖44.刪除視圖【例16】使用SQL語(yǔ)句刪除視圖“view_expert_postgraduate”。任務(wù)實(shí)施mysql>DROPVIEWview_expert_postgraduate;QueryOK,0rowsaffected(0.01sec)使用SQL語(yǔ)句創(chuàng)建、管理和應(yīng)用視圖45.應(yīng)用視圖使用視圖不僅可以方便地查詢所需要的數(shù)據(jù)信息，還可以更新基本表的數(shù)據(jù)，其方式與使用UPDATE、INSERT和DELETE語(yǔ)句在數(shù)據(jù)表中更新數(shù)據(jù)一樣。并非所有的視圖都可以進(jìn)行更新操作，使用視圖更新數(shù)據(jù)時(shí)，需要注意以下幾點(diǎn)：

使用視圖更新數(shù)據(jù)時(shí)，視圖必須只能引用一個(gè)基本表的列。

如果視圖的列是通過(guò)聚合函數(shù)或表達(dá)式計(jì)算出來(lái)的，則此視