《MySQL數(shù)據(jù)庫原理與應(yīng)用案例教程》 課件 項(xiàng)目7 索引與視圖

《MySQL數(shù)據(jù)庫項(xiàng)目化教程》項(xiàng)目7索引與視圖索引是提高數(shù)據(jù)庫查詢效率的重要手段，索引關(guān)鍵字是由數(shù)據(jù)庫中數(shù)據(jù)表的一列或多列組合而成。視圖是一種虛擬的數(shù)據(jù)表。視圖可以從原有的數(shù)據(jù)表或者已有的視圖上選取對(duì)用戶有用的信息，那些對(duì)用戶沒用，或者用戶沒有權(quán)限了解的信息，都可以直接屏蔽掉。既保障了數(shù)據(jù)的安全性，又大大提高了查詢效率，方便一般用戶操作數(shù)據(jù)庫。本項(xiàng)目通過典型任務(wù)學(xué)習(xí)如何創(chuàng)建索引、查看索引和刪除索引，以及如何創(chuàng)建視圖、查看視圖、修改視圖、更新視圖中的數(shù)據(jù)和刪除視圖。前言掌握如何創(chuàng)建索引、查看索引和刪除索引。掌握如何創(chuàng)建視圖、查看視圖、修改視圖。掌握如何更新視圖中的數(shù)據(jù)和刪除視圖。學(xué)習(xí)目標(biāo)知識(shí)重點(diǎn)創(chuàng)建索引。創(chuàng)建視圖。知識(shí)難點(diǎn)修改視圖。

更新視圖中的數(shù)據(jù)。知識(shí)重點(diǎn)與難點(diǎn)創(chuàng)建和刪除索引任務(wù)1任務(wù)2創(chuàng)建和管理視圖學(xué)習(xí)任務(wù)創(chuàng)建和刪除索引任務(wù)1任務(wù)描述任務(wù)目標(biāo)任務(wù)分析任務(wù)實(shí)施123知識(shí)鏈接45為提高數(shù)據(jù)庫查詢速度，經(jīng)常需要對(duì)數(shù)據(jù)表創(chuàng)建索引，可以在創(chuàng)建數(shù)據(jù)表時(shí)直接創(chuàng)建索引，也可以在已經(jīng)存在的數(shù)據(jù)表上創(chuàng)建索引。根據(jù)實(shí)際需求，選擇合適的創(chuàng)建索引的語句，完成創(chuàng)建索引，并查看索引。索引在提高查詢速度的同時(shí)會(huì)降低數(shù)據(jù)表的更新速度，影響數(shù)據(jù)庫的性能，應(yīng)該對(duì)不用的索引及時(shí)進(jìn)行刪除。任務(wù)描述（1）會(huì)在已存在的數(shù)據(jù)表上創(chuàng)建索引。（2）會(huì)在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建索引。（3）會(huì)查看索引。（4）會(huì)刪除索引。（5）通過創(chuàng)建索引的學(xué)習(xí)，激發(fā)學(xué)生提高工作效率的思路。任務(wù)目標(biāo)學(xué)習(xí)創(chuàng)建索引、查看索引和刪除索引的語句，選擇合適的創(chuàng)建索引的語句，完成索引的創(chuàng)建、查看和刪除操作。任務(wù)分析創(chuàng)建索引查看索引刪除索引索引任務(wù)實(shí)施步驟1在已存在的數(shù)據(jù)表上創(chuàng)建索引（1）使用createindex語句，在數(shù)據(jù)表tb_student的sname字段上創(chuàng)建普通索引index_sname。mysql>usechjgl_db;mysql>createindexindex_snameontb_student(sname);任務(wù)實(shí)施任務(wù)實(shí)施（2）使用createindex語句，在數(shù)據(jù)表tb_student的sno字段上創(chuàng)建普通索引index_sno，并且索引按照降序排列。mysql>createindexindex_snoontb_student(snodesc);（3）使用altertable語句，在數(shù)據(jù)表tb_grade的sno，cno字段上創(chuàng)建唯一索引index_sno_grade。mysql>altertabletb_gradeadduniqueindexindex_sno_grade(sno,cno);步驟2創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建索引（1）創(chuàng)建數(shù)據(jù)表tb_index，在該數(shù)據(jù)表中的name字段上創(chuàng)建普通索引index_name。mysql>createtabletb_index->(->idintprimarykeyauto_increment,->namechar(10)notnull,->index(name)->);任務(wù)實(shí)施小貼士：創(chuàng)建索引時(shí)，如果不指定索引的名稱，默認(rèn)索引的名稱為索引字段的名稱。（2）創(chuàng)建數(shù)據(jù)表tb_index1，在該數(shù)據(jù)表的id字段上創(chuàng)建唯一索引index_id。mysql>createtabletb_index1->(->idintprimarykeyauto_increment,->namechar(10)notnull,->uniqueindexindex_no(id)->);任務(wù)實(shí)施

步驟3查看索引查看數(shù)據(jù)表tb_index1的索引信息。mysql>showindexfromtb_index1\G;任務(wù)實(shí)施步驟4刪除索引（1）使用dropindex語句，刪除數(shù)據(jù)表tb_index1中名稱為index_no的索引。mysql>dropindexindex_noontb_index1;任務(wù)實(shí)施（2）使用altertable語句，刪除數(shù)據(jù)表tb_index中名稱為name的索引。mysql>altertabletb_indexdropindexname;小貼士：修改索引，可以通過刪除原索引，再根據(jù)需要?jiǎng)?chuàng)建一個(gè)同名的索引實(shí)現(xiàn)。索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，是根據(jù)數(shù)據(jù)表中的一列或多列按照一定順序建立的列值與記錄行之間的對(duì)應(yīng)關(guān)系表，實(shí)質(zhì)上是一張描述索引列的列值與原表中記錄行之間一一對(duì)應(yīng)關(guān)系的有序表。索引是MySQL數(shù)據(jù)庫性能調(diào)優(yōu)技術(shù)的基礎(chǔ)，常用于實(shí)現(xiàn)數(shù)據(jù)的快速檢索。在MySQL中，所有的數(shù)據(jù)類型都可以被索引。MySQL索引主要包括：普通索引、唯一索引、全文索引和空間索引。（1）普通索引普通索引時(shí)MySQL中的基本索引類型，是由index或key定義的索引，它沒有任何限制，可以在任何數(shù)據(jù)類型中創(chuàng)建，普通索引允許在定義索引的字段中插入重復(fù)值和空值。知識(shí)鏈接——索引（2）唯一索引唯一索引是由unique定義的索引，唯一索引字段的值必須唯一，允許有空值。如果是組合索引，則字段值的組合必須唯一。（3）全文索引全文索引是由fulltext定義的索引，主要用來查找文本中的關(guān)鍵字，只能在char、varchar或text類型的字段上創(chuàng)建。全文索引允許在索引字段中插入重復(fù)值和空值。（4）空間索引空間索引是由spatial定義的索引，是對(duì)空間數(shù)據(jù)類型的字段建立的索引。支持的數(shù)據(jù)類型有4種，分別是geometry、point、linestring和polygon。創(chuàng)建空間索引的字段必須將其聲明為notnull。知識(shí)鏈接——索引索引的優(yōu)點(diǎn)：（1）通過創(chuàng)建唯一索引可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。（2）可以大大提高數(shù)據(jù)查詢的速度，這是使用索引最主要的原因。（3）在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面可以加速數(shù)據(jù)表與數(shù)據(jù)表之間的連接。（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時(shí)使用索引也可以顯著減少查詢中分組和排序的時(shí)間。索引的缺點(diǎn)：（1）創(chuàng)建和維護(hù)索引要耗費(fèi)時(shí)間。（2）索引需要占磁盤空間。（3）當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)維護(hù)，降低了數(shù)據(jù)的維護(hù)速度。知識(shí)鏈接——索引MySQL提供了三種創(chuàng)建索引的方法，分別為：在創(chuàng)建數(shù)據(jù)表（createtable）時(shí)創(chuàng)建索引、使用createindex語句在已存在的數(shù)據(jù)表上創(chuàng)建索引，使用altertable語句在已存在的數(shù)據(jù)表上創(chuàng)建索引。（1）在創(chuàng)建數(shù)據(jù)表（createtable）時(shí)創(chuàng)建索引知識(shí)鏈接——?jiǎng)?chuàng)建索引CREATETABLE：創(chuàng)建數(shù)據(jù)表的關(guān)鍵字。tbl_name：數(shù)據(jù)表名。col_name：字段名。知識(shí)鏈接——?jiǎng)?chuàng)建索引column_definition：由字段的數(shù)據(jù)類型，以及可能的空值說明、完整性約束或數(shù)據(jù)表索引組成。[UNIQUE|FULLTEXT|SPATIAL]表示索引類型，其中UNIQUE表示唯一性索引，F(xiàn)ULLTEXT表示全文索引、SPATIAL表示空間索引，省略表示普通索引。{INDEX|KEY}：創(chuàng)建索引關(guān)鍵字，key是index的同義詞。index_name：索引名。index_type：索引類型，包括BTREE索引和HASH索引兩種類型。{col_name[(length)]|(expr)}：字段名（長度|表達(dá)式）。[ASC|DESC]：索引字段排列順序，ASC表示升序排列，DESC表示降序排列。知識(shí)鏈接——?jiǎng)?chuàng)建索引（2）使用createindex語句在已存在的數(shù)據(jù)表上創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX：創(chuàng)建索引關(guān)鍵字，[UNIQUE|FULLTEXT|SPATIAL]表示索引類型，其中UNIQUE表示唯一性索引，F(xiàn)ULLTEXT表示全文索引、SPATIAL表示空間索引，省略表示普通索引。index_name：索引名。index_type：索引類型，包括BTREE索引和HASH索引兩種類型。tbl_name：數(shù)據(jù)表名。{col_name[(length)]|(expr)}：字段名（長度|表達(dá)式）。[ASC|DESC]：索引字段排列順序，ASC表示升序排列，DESC表示降序排列。知識(shí)鏈接——?jiǎng)?chuàng)建索引（3）使用altertable語句在已存在的數(shù)據(jù)表上創(chuàng)建索引ALTERTABLE：修改數(shù)據(jù)表的關(guān)鍵字。

tbl_name：數(shù)據(jù)表名。ADD[UNIQUE|FULLTEXT|SPATIAL]{INDEX|KEY}：創(chuàng)建索引關(guān)鍵字，[UNIQUE|FULLTEXT|SPATIAL]表示索引類型，其中UNIQUE表示唯一性索引，F(xiàn)ULLTEXT表示全文索引、SPATIAL表示空間索引，省略表示普通索引。key是index的同義詞。index_name：索引名。index_type：索引類型，包括BTREE索引和HASH索引兩種類型。{col_name[(length)]|(expr)}：字段名（長度|表達(dá)式）。[ASC|DESC]：索引字段排列順序，ASC表示升序排列，DESC表示降序排列。知識(shí)鏈接——查看索引語法分析：SHOWINDEXFROM：查看索引關(guān)鍵字。tbl_name：數(shù)據(jù)表名。db_name：數(shù)據(jù)庫名。知識(shí)鏈接——?jiǎng)h除索引刪除索引是指將表中已經(jīng)存在的索引刪除掉。索引會(huì)降低數(shù)據(jù)表的更新速度，影響數(shù)據(jù)庫的性能。對(duì)于不用的索引，應(yīng)該將其刪除。DROPINDEX：刪除索引關(guān)鍵字。index_name：索引名。tbl_name：數(shù)據(jù)表名。創(chuàng)建和管理視圖任務(wù)2任務(wù)描述任務(wù)目標(biāo)任務(wù)分析任務(wù)實(shí)施123知識(shí)鏈接45

在數(shù)據(jù)查詢過程中，經(jīng)常需要隱藏一些數(shù)據(jù)，或者使復(fù)雜的查詢易于理解和使用，這時(shí)需要?jiǎng)?chuàng)建視圖，根據(jù)實(shí)際需求，完成創(chuàng)建視圖、查看視圖、修改視圖、更新視圖中的數(shù)據(jù)和刪除視圖。任務(wù)描述（1）會(huì)創(chuàng)建視圖，并查看視圖。（2）會(huì)修改視圖的結(jié)構(gòu)。（3）會(huì)更新視圖中的數(shù)據(jù)。（4）會(huì)刪除視圖。（5）通過創(chuàng)建視圖的學(xué)習(xí)，提高學(xué)生信息安全意識(shí)。任務(wù)目標(biāo)視圖的基本操作同數(shù)據(jù)表類似，學(xué)習(xí)創(chuàng)建視圖、查看視圖、修改視圖、更新視圖和刪除視圖的語句，完成視圖的各種操作。任務(wù)分析創(chuàng)建視圖查看視圖結(jié)構(gòu)修改視圖任務(wù)實(shí)施視圖查看視圖結(jié)構(gòu)修改視圖步驟1創(chuàng)建視圖、查看視圖（1）在tb_grade數(shù)據(jù)表上創(chuàng)建一個(gè)名為view_grade的視圖，視圖字段包括tb_grade數(shù)據(jù)表的所有字段。mysql>usechjgl_db;mysql>createviewview_gradeasselect*fromtb_grade;任務(wù)實(shí)施（2）查看視圖view_grade的結(jié)構(gòu)。mysql>descview_grade;可以看出，默認(rèn)情況下創(chuàng)建的視圖和基本表的字段是一樣的。任務(wù)實(shí)施小貼士：DESCRIBE一般情況下可以簡寫成DESC。（3）在tb_grade數(shù)據(jù)表上創(chuàng)建一個(gè)名為view_grade1的視圖，并指定視圖的字段名稱。其中，視圖的字段包括id，sno，cno，totalgrade，對(duì)應(yīng)的字段名稱為v_id，v_sno，v_cno，v_totalgrade，數(shù)據(jù)為綜合成績高于90分的記錄。mysql>createviewview_grade1->(v_id,v_sno,v_cno,v_totalgrade)->asselectid,sno,cno,totalgrade->fromtb_gradewheretotalgrade>90;任務(wù)實(shí)施（4）查看視圖view_grade1的數(shù)據(jù)。mysql>select*fromview_grade1;創(chuàng)建視圖時(shí)，可以指定字段的名稱，視圖的字段名可以和基本數(shù)據(jù)表的字段不同，但是數(shù)據(jù)卻來源于基本數(shù)據(jù)表。因此，在使用視圖時(shí)，可能用戶不需要了解基本數(shù)據(jù)表的結(jié)構(gòu)，更接觸不到實(shí)際表中的數(shù)據(jù)，從而保證數(shù)據(jù)庫的安全。任務(wù)實(shí)施（5）查看視圖view_grade1的詳細(xì)結(jié)構(gòu)。mysql>showcreateviewview_grade1\G;任務(wù)實(shí)施任務(wù)實(shí)施步驟2修改視圖的結(jié)構(gòu)修改視圖view_grade的結(jié)構(gòu)，只包括學(xué)號(hào)，課程編號(hào)和綜合成績字段。mysql>alterviewview_gradeasselectsno,cno,totalgradefromtb_grade;小貼士：修改視圖的結(jié)構(gòu)，除了可以通過alterview語句外，還可以使用dropview語句先刪除視圖，再使用createview語句創(chuàng)建新的視圖來實(shí)現(xiàn)。步驟3更新視圖中的數(shù)據(jù)（1）向視圖view_grade插入一條記錄。mysql>insertintoview_gradevalues('202115010203','202107',100);任務(wù)實(shí)施任務(wù)實(shí)施（2）分別在視圖view_grade和基本數(shù)據(jù)表tb_grade查看（1）中插入的記錄。mysql>select*fromview_gradewheretotalgrade=100;mysql>select*fromtb_gradewheretotalgrade=100步驟4刪除視圖刪除視圖view_grade。mysql>dropviewview_grade;任務(wù)實(shí)施視圖是一種虛擬存在的表，數(shù)據(jù)庫中只存放視圖的定義，并沒有存放視圖中的數(shù)據(jù)，這些數(shù)據(jù)都存放在定義視圖時(shí)所引用的基本數(shù)據(jù)表中，是在使用視圖時(shí)動(dòng)態(tài)生成的。視圖中的數(shù)據(jù)依賴于基本數(shù)據(jù)表中的數(shù)據(jù)。一旦基本數(shù)據(jù)表中的數(shù)據(jù)發(fā)生改變，則這種變化也可以自動(dòng)地反映到視圖中。當(dāng)對(duì)通過視圖看到的數(shù)據(jù)進(jìn)行修改時(shí)，相應(yīng)的基本數(shù)據(jù)表中的數(shù)據(jù)也會(huì)發(fā)生變化。視圖的建立和刪除只影響視圖本身，不影響對(duì)應(yīng)的基本數(shù)據(jù)表。視圖是查看數(shù)據(jù)表的一種方法，視圖可以從基本數(shù)據(jù)表上選取對(duì)用戶有用的信息，那些對(duì)用戶沒用，或者用戶沒有權(quán)限了解的信息，都可以直接屏蔽掉。使用視圖的用戶不接觸數(shù)據(jù)表，不知道數(shù)據(jù)表的結(jié)構(gòu)。這樣可以簡化數(shù)據(jù)操作，同時(shí)提高了數(shù)據(jù)的安全性。知識(shí)鏈接——視圖知識(shí)鏈接——?jiǎng)?chuàng)建視圖創(chuàng)建視圖是指基于已經(jīng)存在的MySQL數(shù)據(jù)庫數(shù)據(jù)表或者視圖上建立視圖。視圖可以建立在一張數(shù)據(jù)表或者源視圖中，也可以建立在多張數(shù)據(jù)表或者源視圖上。創(chuàng)建視圖的基本語法格式為：CREATEVIEW：創(chuàng)建視圖的關(guān)鍵字。view_name：表示要?jiǎng)?chuàng)建的視圖名稱。column_list：字段列表。指定視圖中各個(gè)字段的名稱，默認(rèn)情況下，與select語句中查詢的字段名稱相同。AS：表示指定視圖要執(zhí)行的操作。select_statement：指定創(chuàng)建視圖的select語句，可用于查詢多個(gè)基本數(shù)據(jù)表或者源視圖。（1）查看視圖的基本結(jié)構(gòu)語句查看視圖的基本結(jié)構(gòu)與查看數(shù)據(jù)表的基本結(jié)構(gòu)的語句一樣，都是使用describe關(guān)鍵字來查看。基本語法格式為：知識(shí)鏈接——查看視圖結(jié)構(gòu)語法分析：DESCRIBE：查看視圖基本結(jié)構(gòu)的命令，DESCRIBE命令的簡寫形式是DESC。view_name：必選項(xiàng)，指定要查看的視圖的名稱。(2)查看視圖創(chuàng)建語句的SQL信息查看視圖創(chuàng)建語句的SQL信息的基本語法格式為：知識(shí)鏈接——查看視圖結(jié)構(gòu)語法分析：SHOWCREATEVIEW：查看視圖創(chuàng)建語句的SQL信息的命令。view_name：必選項(xiàng)，指定要查看的視圖的名稱。\G:使顯示結(jié)果格式化，如果不適用\G，顯示的結(jié)果會(huì)比較混亂。修改視圖是指修改數(shù)據(jù)庫