電工與電子技術(shù)電子商務(wù)電子課件MySQL數(shù)據(jù)庫(kù)應(yīng)用4.1 索引的創(chuàng)建和管理1ppt

1、電子課件MySQL數(shù)據(jù)庫(kù)應(yīng)用4.1 索引的創(chuàng)建和管理1項(xiàng)目4：創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)應(yīng)用任務(wù)1 創(chuàng)建索引與視圖 索引的創(chuàng)建和管理(1) 教學(xué)目標(biāo) 素質(zhì)目標(biāo) 知識(shí)目標(biāo)1.養(yǎng)成對(duì)知識(shí)歸納、總結(jié)，并在此基礎(chǔ)上記憶的好習(xí)慣；2.培養(yǎng)學(xué)生養(yǎng)成溫故而知新的良好的學(xué)習(xí)方法；3.思政內(nèi)容：本次課講創(chuàng)建數(shù)據(jù)表的索引，可融入在人類歷史上提高生產(chǎn)效率的經(jīng)歷1.了解創(chuàng)建索引的意義及在哪些情況下需要?jiǎng)?chuàng)建索引；2.理解普通索引、唯一索引、主鍵索引、多列索引和全文索引的作用及應(yīng)用場(chǎng)景；3.掌握創(chuàng)建普通索引、唯一索引、主鍵索引、多列索引和全文索引的方法； 能力目標(biāo)1.能夠理解創(chuàng)建索引的意義和作用；2.能夠根據(jù)應(yīng)用需求的不同創(chuàng)建

2、不同類型的索引。 教學(xué)重點(diǎn)創(chuàng)建普通索引、唯一索引、主鍵索引、多列索引和全文索引 教學(xué)難點(diǎn)對(duì)幾種索引創(chuàng)建方法的歸納、總結(jié)并回顧數(shù)據(jù)表的創(chuàng)建方法教學(xué)重難點(diǎn)1.抽查與評(píng)講作業(yè)；2.復(fù)習(xí)查詢結(jié)果輸出與合并;3.檢查課程預(yù)習(xí)情況；4.互動(dòng):1）索引的作用是什么？2）有哪些情況需要建立索引？ 5索引的創(chuàng)建和管理知識(shí)儲(chǔ)備1什么是索引索引是對(duì)某個(gè)表中一列或若干列值進(jìn)行排序的結(jié)構(gòu)。它由該表的一列或多列的值，以及指向這些列值對(duì)應(yīng)存儲(chǔ)位置的指針?biāo)鶚?gòu)成。索引是依賴于表建立的，一個(gè)表由兩部分組成：一部分用來(lái)存放表的數(shù)據(jù)頁(yè)面，另一部分存放索引頁(yè)面。由于索引頁(yè)面比數(shù)據(jù)頁(yè)面小得多，在進(jìn)行數(shù)據(jù)檢索時(shí)，系統(tǒng)會(huì)先搜索索引頁(yè)面，從中

3、找到所需數(shù)據(jù)的指針，再通過(guò)指針從數(shù)據(jù)頁(yè)面中讀取數(shù)據(jù)。這種操作模式類似于圖書的目錄。（一）索引的作用相對(duì)于沒(méi)有使用索引而言，使用索引可以提高數(shù)據(jù)查詢速度百倍、千倍、萬(wàn)倍以上。 通過(guò)對(duì)多個(gè)字段使用唯一索引，可以保證多個(gè)字段的唯一性。在表與表之間連接查詢時(shí)，如果創(chuàng)建了索引，就可以提高表與表之間連接的速度。 索引的創(chuàng)建和管理 6知識(shí)儲(chǔ)備（二）適合創(chuàng)建索引的情況經(jīng)常被查詢的字段。分組字段。設(shè)置了唯一性約束的字段。另外，指定為主鍵的列會(huì)自動(dòng)創(chuàng)建主鍵索引，外鍵列的索引會(huì)由MySQL根據(jù)參照的主鍵列自動(dòng)創(chuàng)建的。（三）不適合創(chuàng)建索引的情況在查詢中很少用到的字段。具有大量重復(fù)值的字段，例如性別字段。較小的數(shù)據(jù)表，

4、這種情況使用索引并不能改善任何檢索性能。 索引的創(chuàng)建和管理 7知識(shí)儲(chǔ)備2創(chuàng)建索引（一）創(chuàng)建普通索引 （1）創(chuàng)建表時(shí)創(chuàng)建普通索引在MySQL數(shù)據(jù)庫(kù)中，可以在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建普通索引。語(yǔ)法規(guī)則：CREATE TABLE 表名 (列名 數(shù)據(jù)類型， INDEX|KEY 索引名（列名i 長(zhǎng)度ASC|DESC); 索引的創(chuàng)建和管理 8知識(shí)儲(chǔ)備【創(chuàng)建表時(shí)創(chuàng)建普通索引示例】根據(jù)表4-1所示的結(jié)構(gòu)，在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK1表并創(chuàng)建普通索引，相關(guān)列為“學(xué)號(hào)”。mysql use xscj_db;Database changedmysql create table xsqk1( - 學(xué)號(hào) char

5、(10), 表4-1 XSQK1表的結(jié)構(gòu) - 姓名 varchar(10), - 性別 char(2), - index index_xh(學(xué)號(hào)) );其中，index_xh是創(chuàng)建的普通索引名稱。 索引的創(chuàng)建和管理 9列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）索引學(xué)號(hào)char10index_xh姓名varchar10 性別char2知識(shí)儲(chǔ)備互動(dòng)：按下表結(jié)構(gòu)，新建XSQK2表并創(chuàng)建普通索引，相關(guān)列為“學(xué)號(hào)”。 索引的創(chuàng)建和管理 10列名數(shù)據(jù)類型長(zhǎng)度（字節(jié)）索引學(xué)號(hào)char10index_xh姓名varchar10 性別char2知識(shí)儲(chǔ)備（2）在已經(jīng)存在的表上創(chuàng)建普通索引在MySQL數(shù)據(jù)庫(kù)中，可以在已經(jīng)存在的表上創(chuàng)

6、建普通索引。語(yǔ)法規(guī)則：CREATE INDEX 索引名 ON 表名（列名長(zhǎng)度 ASC|DESC）; 【在已經(jīng)存在的表上創(chuàng)建普通索引示例】在XSCJ_db數(shù)據(jù)庫(kù)中的KC_db表的“課程名”上創(chuàng)建索引。mysql create index index_kcm - on kc_db(課程名); 索引的創(chuàng)建和管理 11知識(shí)儲(chǔ)備互動(dòng)：在XSCJ_db數(shù)據(jù)庫(kù)中的KC_db表的“授課教師”上創(chuàng)建索引index_skjs。索引的創(chuàng)建和管理 12知識(shí)儲(chǔ)備（3）通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建普通索引語(yǔ)法規(guī)則：ALTER TABLE 表名 ADD INDEX|KEY 索引名(列名長(zhǎng)度 ASC|DESC);【通過(guò)

7、ALTER TABLE語(yǔ)句創(chuàng)建普通索引示例】在XSCJ_db數(shù)據(jù)庫(kù)中XSQK1表的“姓名”列上創(chuàng)建普通索引。mysql alter table xsqk1 - add index index_xm(姓名); 索引的創(chuàng)建和管理 13知識(shí)儲(chǔ)備互動(dòng)：通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建普通索引：在XSCJ數(shù)據(jù)庫(kù)中XSQK表的“專業(yè)名”列上創(chuàng)建普通索引。索引的創(chuàng)建和管理 14知識(shí)儲(chǔ)備(二)創(chuàng)建唯一索引 唯一索引和普通索引類似，但唯一索引要求索引列的值是唯一的，需要使用關(guān)鍵字UNIQUE來(lái)標(biāo)明。創(chuàng)建唯一索引與創(chuàng)建普通索引一樣也有三種方式。一是建表時(shí)創(chuàng)建唯一索引，語(yǔ)法規(guī)則：CREATE TABLE 表名 (

8、列名 數(shù)據(jù)類型， UNIQUE INDEX|KEY 索引名（列名i 長(zhǎng)度ASC|DESC); 索引的創(chuàng)建和管理 15知識(shí)儲(chǔ)備【在新建表上創(chuàng)建唯一索引示例】根據(jù)表4-2所示的結(jié)構(gòu)，在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK2表，創(chuàng)建唯一索引，相關(guān)列為“學(xué)號(hào)”。 表4-2 XSQK2表的結(jié)構(gòu) mysql create table xsqk2( - 學(xué)號(hào) char(10), - 姓名 varchar(10), - 性別 char(2), - unique index index_xh(學(xué)號(hào));索引的創(chuàng)建和管理 16列 名數(shù) 據(jù) 類 型長(zhǎng) 度（字節(jié)）索 引學(xué)號(hào)char10index_xh姓名varcha

9、r10 性別char2知識(shí)儲(chǔ)備二是在已經(jīng)存在的表上創(chuàng)建唯一索引，語(yǔ)法規(guī)則：CREATE UNIQUE INDEX 索引名 ON 表名(列名長(zhǎng)度 ASC|DESC);【在已存在的表上創(chuàng)建唯一索引示例】在XSCJ_db數(shù)據(jù)庫(kù)中的XSQK_db的姓名列上創(chuàng)建唯一索引，SQL語(yǔ)句：mysql create unique index index_kcm - on xsqk_db(姓名);索引的創(chuàng)建和管理 17知識(shí)儲(chǔ)備三是通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建唯一索引，語(yǔ)法規(guī)則：ALTER TABLE 表名 ADD UNIQUE INDEX|KEY 索引名(列名長(zhǎng)度 ASC|DESC)；【通過(guò)ALTER TA

10、BLE語(yǔ)句創(chuàng)建唯一索引示例】在XSCJ_db數(shù)據(jù)庫(kù)中XSQK2表的“姓名”列上創(chuàng)建唯一索引，SQL語(yǔ)句：mysql alter table xsqk2 - add unique index index_xm(姓名);索引的創(chuàng)建和管理 18知識(shí)儲(chǔ)備互動(dòng)1：根據(jù)如表所示的結(jié)構(gòu)，在測(cè)試數(shù)據(jù)庫(kù)XSCJ中新建XSQK4表，創(chuàng)建唯一索引，相關(guān)列為“學(xué)號(hào)”。 表4-2 XSQK4表的結(jié)構(gòu) 互動(dòng)2：在已存在的表XSQK4上創(chuàng)建唯一索引:在互動(dòng)中創(chuàng)建的XSQK4的姓名列上創(chuàng)建唯一索引互動(dòng)3：通過(guò)ALTER TABLE語(yǔ)句在已存在的表XSQK上創(chuàng)建唯一索引相關(guān)列為“姓名”列上創(chuàng)建唯一索引索引的創(chuàng)建和管理 19列

11、名數(shù) 據(jù) 類 型長(zhǎng) 度（字節(jié)）索 引學(xué)號(hào)char10index_xh姓名varchar10 性別char2知識(shí)儲(chǔ)備（三）創(chuàng)建主鍵索引 每張表都有一個(gè)主鍵索引，并且只有一個(gè)，在創(chuàng)建表的主鍵時(shí)就會(huì)創(chuàng)建主鍵索引，也可以通過(guò)關(guān)鍵字ALTER增加主鍵索引（創(chuàng)建主鍵索引的方法在“項(xiàng)目二”中講過(guò)）.索引的創(chuàng)建和管理 20知識(shí)儲(chǔ)備（四）創(chuàng)建全文索引 索引一般建立在數(shù)字型或長(zhǎng)度比較短的文本型字段上，如編號(hào)、姓名等。如果建立在比較長(zhǎng)的文本型字段上，會(huì)使索引的更新花費(fèi)很多的時(shí)間。在MySQL中，提供了一種稱為“全文索引”的技術(shù)，主要關(guān)聯(lián)在數(shù)據(jù)類型為Char、Varchar和Text等的長(zhǎng)字符字段上。創(chuàng)建全文索引有三

12、種方式:（1）創(chuàng)建表時(shí)創(chuàng)建全文索引在MySQL數(shù)據(jù)庫(kù)中，可以在創(chuàng)建數(shù)據(jù)表的時(shí)候創(chuàng)建全文索引。創(chuàng)建表時(shí)創(chuàng)建全文索引語(yǔ)法規(guī)則是：CREATE TABLE 表名 (列名 數(shù)據(jù)類型， FULLTEXT INDEX|KEY 索引名（列名i 長(zhǎng)度ASC|DESC);索引的創(chuàng)建和管理 21知識(shí)儲(chǔ)備【創(chuàng)建表時(shí)創(chuàng)建全文索引示例】根據(jù)表4-3所示結(jié)構(gòu)，在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK3表，并創(chuàng)建全文索引，相關(guān)列為“備注”列。 表4-3 XSQK3表的結(jié)構(gòu)mysql create table xsqk3( - 學(xué)號(hào) char(10), - 姓名 varchar(10), - 備注 char(100), -

13、Fulltext index index_bz(備注);索引的創(chuàng)建和管理 22列 名數(shù) 據(jù) 類 型長(zhǎng)度（字節(jié)）索 引學(xué)號(hào)Char10 姓名Varchar10 備注Varchar100Index_bz知識(shí)儲(chǔ)備（2）在已經(jīng)存在的表上創(chuàng)建全文索引【在已經(jīng)存在的表上創(chuàng)建全文索引示例】先刪除XSQK3表上的全文索引，然后在備注列上創(chuàng)建全文索引。mysql create fulltext index index_bz - on xsqk3(備注);索引的創(chuàng)建和管理 23知識(shí)儲(chǔ)備（3）通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建全文索引【通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建全文索引示例】先刪除XSQK3表上的全文索引，

14、然后在備注列上創(chuàng)建全文索引：mysql alter table xsqk3 - add fulltext index index_bz(備注);索引的創(chuàng)建和管理 24知識(shí)儲(chǔ)備互動(dòng)操作：在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK3表，并創(chuàng)建全文索引，相關(guān)列為“備注”列。 索引的創(chuàng)建和管理 25列 名數(shù) 據(jù) 類 型長(zhǎng)度（字節(jié)）索 引學(xué)號(hào)Char10 姓名Varchar10 備注Varchar100Index_bz知識(shí)儲(chǔ)備（五）創(chuàng)建多列索引 如果在創(chuàng)建索引時(shí)，所關(guān)聯(lián)的列有兩個(gè)或多個(gè)列，就稱為多列索引。需要注意的是，只有查詢條件中使用了這些列中的第一個(gè)列時(shí)，多列索引才會(huì)被使用。（1）創(chuàng)建表時(shí)創(chuàng)建多列索引

15、語(yǔ)法規(guī)則是：CREATE TABLE 表名 (列名 數(shù)據(jù)類型， INDEX|KEY 索引名（列名1長(zhǎng)度ASC|DESC，列名1長(zhǎng)度ASC|DESC，);索引的創(chuàng)建和管理 26知識(shí)儲(chǔ)備【創(chuàng)建表時(shí)創(chuàng)建多列索引示例】根據(jù)表4-4所示結(jié)構(gòu)，新建XSQK4表并創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”、“姓名”列。 表4-4 XSQK4表的結(jié)構(gòu)mysql create table xsqk4( - 學(xué)號(hào) int, - 姓名 char(10), - 性別 char(2), - 專業(yè)名 varchar(20), - index index_xh_xm(學(xué)號(hào),姓名);索引的創(chuàng)建和管理 27列 名數(shù) 據(jù) 類 型長(zhǎng)度（

16、字節(jié)）索 引學(xué)號(hào)Char10Index_xh_xm姓名Varchar10性別Char2 專業(yè)名Varchar20知識(shí)儲(chǔ)備（2) 在已經(jīng)存在的表上創(chuàng)建多列索引語(yǔ)法規(guī)則是：CREATE INDEX 索引名 ON 表名（列名1長(zhǎng)度ASC|DESC，列名1長(zhǎng)度ASC|DESC，）;【在已經(jīng)存在的表上創(chuàng)建多列索引示例】先刪除XSQK4表上的多列索引，然后在XSQK4表上創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”“姓名”列。mysql create index index_xh_xm - on xsqk4(學(xué)號(hào),姓名);索引的創(chuàng)建和管理 28知識(shí)儲(chǔ)備（3）通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建多列索引語(yǔ)法規(guī)則是：ALTER TABLE 表名 ADD INDEX|KEY 索引名(列名長(zhǎng)度 ASC|DESC，列名長(zhǎng)度 ASC|DESC，);【通過(guò)ALTER TABLE語(yǔ)句創(chuàng)建多列索引示例】先刪除XSQK4表上的多列索引，然后在XSQK4表上創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”“姓名”列。mysql alter table xsqk4 - add key index_xh_xm(學(xué)號(hào),姓名);索引的創(chuàng)建和管理 29