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

1、電子課件MySQL數(shù)據(jù)庫應(yīng)用4.1 索引的創(chuàng)建和管理1項目4：創(chuàng)建數(shù)據(jù)庫對象數(shù)據(jù)庫應(yīng)用任務(wù)1 創(chuàng)建索引與視圖 索引的創(chuàng)建和管理(1) 教學(xué)目標(biāo) 素質(zhì)目標(biāo) 知識目標(biāo)1.養(yǎng)成對知識歸納、總結(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)建索引的意義及在哪些情況下需要創(chuàng)建索引；2.理解普通索引、唯一索引、主鍵索引、多列索引和全文索引的作用及應(yīng)用場景；3.掌握創(chuàng)建普通索引、唯一索引、主鍵索引、多列索引和全文索引的方法； 能力目標(biāo)1.能夠理解創(chuàng)建索引的意義和作用；2.能夠根據(jù)應(yīng)用需求的不同創(chuàng)建

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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