《MySQL數(shù)據(jù)庫應(yīng)用與管理（第3版）》 課件 魯大林 第4章 索引的創(chuàng)建和使用

索引的創(chuàng)建和使用

索引概述

本節(jié)內(nèi)容1.使用索引提高查詢效率的原理2.索引的優(yōu)點(diǎn)3.索引的缺點(diǎn)4.引用索引的原則索引概述索引是一種與數(shù)據(jù)表相關(guān)的類似于目錄的一種數(shù)據(jù)結(jié)構(gòu)，MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。索引也類似一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。索引也可以分為單列索引和組合索引，單列索引表示一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引，但這不是組合索引；組合索引表示一個(gè)索引包含多個(gè)列。索引概述索引包含由列生成的鍵值+數(shù)據(jù)頁地址的指針組成的。索引的鍵值是排序的，排序的數(shù)據(jù)可以利用各種高效的查找算法（例如，折半查找等）。1.使用索引提高查詢效率的原理索引概述提高查詢速度。提高表與表之間連接的效率。唯一性索引還可以保證數(shù)據(jù)記錄的唯一性。2.索引的優(yōu)點(diǎn)索引概述雖然索引大大提高了查詢速度，但卻會(huì)降低更新表的速度，因?yàn)樵趯?duì)數(shù)據(jù)表進(jìn)行INSERT、UPDATE、DELETE的操作時(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。因此，索引并不是創(chuàng)建的越多越好。3.索引的缺點(diǎn)索引概述科學(xué)地設(shè)計(jì)索引，在提高查詢效率的同時(shí)，應(yīng)盡量減少索引帶來的副作用?？紤]設(shè)置索引的情況如下：經(jīng)常檢索的列（在WHERE子句中使用的列）。主鍵列、外鍵列（事實(shí)上，主鍵約束列、唯一性約束列會(huì)自動(dòng)創(chuàng)建索引）。經(jīng)常用于表間連接的列。4.引用索引的原則索引概述考慮不設(shè)置索引的情況如下：檢索中幾乎不涉及到的列。重復(fù)值太多的列。數(shù)據(jù)類型為text、blob的列。行數(shù)極少的表沒必要?jiǎng)?chuàng)建索引。插入、更新效率比查詢效率更重要的情況。4.引用索引的原則謝謝索引的創(chuàng)建和使用

創(chuàng)建索引

本節(jié)內(nèi)容1.使用Navicat對(duì)話方式創(chuàng)建索引2.在CREATETABLE語句中創(chuàng)建索引3.在ALTERTABLE語句中創(chuàng)建索引4.使用CREATEINDEX語句創(chuàng)建索引5.使用SHOWINDEX語句查看索引創(chuàng)建索引以在學(xué)生表（student）的sNo字段上創(chuàng)建唯一性索引、sName字段上創(chuàng)建普通索引為例，操作步驟如下：在Navicat控制臺(tái)中，打開表結(jié)構(gòu)設(shè)計(jì)窗口。1.使用Navicat對(duì)話方式創(chuàng)建索引創(chuàng)建索引點(diǎn)擊“索引”欄切換到索引頁面。1.使用Navicat對(duì)話方式創(chuàng)建索引創(chuàng)建索引可以使用CREATETABLE語句在創(chuàng)建數(shù)據(jù)表的同時(shí)創(chuàng)建索引，其語法格式如下：CREATETABLE<表名>( <字段名1>[,……n]|索引項(xiàng))其中索引項(xiàng)的語法格式為： PRIMARYKEY[<索引名>](<字段名1>[ASC|DESC][,……n])| UNIQUE[INDEX][<索引名>](<字段名1>[ASC|DESC][,……n])| INDEX|KEY[<索引名>](<字段名1>[ASC|DESC][,……n])| FULLTEXT[INDEX][<索引名>](<字段名1>[ASC|DESC][,……n])2.在CREATETABLE語句中創(chuàng)建索引創(chuàng)建索引說明：PRIMARYKEY：主鍵索引。UNIQUE：唯一性索引。INDEX|KEY：普通索引，KEY通常是INDEX的同義詞，二選一即可。FULLTEXT：全文索引。2.使用CREATETABLE語句創(chuàng)建數(shù)據(jù)表操作案例知識(shí)點(diǎn)概述示例4-1運(yùn)行結(jié)果如下：創(chuàng)建學(xué)生表（student），在id字段上創(chuàng)建主鍵索引、在sNo字段上創(chuàng)建唯一性索引、在sName字段上創(chuàng)建普通索引。操作案例知識(shí)點(diǎn)概述示例4-2運(yùn)行結(jié)果如下：創(chuàng)建課程表（course），在id字段上創(chuàng)建主鍵索引。操作案例知識(shí)點(diǎn)概述示例4-3運(yùn)行結(jié)果如下：創(chuàng)建成績(jī)表（score），在id字段上創(chuàng)建主鍵索引。創(chuàng)建索引如果數(shù)據(jù)表已經(jīng)存在，可以使用ALTERTABLE語句創(chuàng)建索引，其語法格式如下： ALTERTABLE<表名> ADD索引項(xiàng)3.在ALTERTABLE語句中創(chuàng)建索引說明：語法中除了多一個(gè)ADD關(guān)鍵詞，其他與在CREATETABLE語句中創(chuàng)建索引類似。操作案例知識(shí)點(diǎn)概述示例4-4運(yùn)行結(jié)果如下：修改課程表（course），在cNo字段上創(chuàng)建唯一性索引、在cName字段上創(chuàng)建普通索引。創(chuàng)建索引創(chuàng)建索引的語句使用CREATEINDEX，其語法格式如下： CREATE[UNIQUE]|[FULLTEXT]INDEX<索引名> ON<表名>(<字段名1>[ASC|DESC][,……n])4.使用CREATEINDEX語句創(chuàng)建索引說明：若無UNIQUE、FULLTEXT關(guān)鍵詞則是創(chuàng)建普通索引。操作案例知識(shí)點(diǎn)概述示例4-5運(yùn)行結(jié)果如下：在成績(jī)表（score）的sId、cId字段上創(chuàng)建唯一性索引（組合索引）。創(chuàng)建索引查看索引使用語句SHOWINDEX，其語法格式如下： SHOWINDEXFROM<表名>[FROM<數(shù)據(jù)庫名>]5.使用SHOWINDEX語句查看索引操作案例知識(shí)點(diǎn)概述示例4-6運(yùn)行結(jié)果如下：查看學(xué)生表（student）中的索引。謝謝索引的創(chuàng)建和使用

刪除索引

本節(jié)內(nèi)容1.使用Navicat對(duì)話方式刪除索引2.使用DROP

INDEX語句刪除索引刪除索引以刪除學(xué)生表（student）中的唯一性索引和普通索引為例，操作步驟如下：在Navicat控制臺(tái)中，打開表結(jié)構(gòu)設(shè)計(jì)窗口，點(diǎn)擊“索引”欄切換到索引頁面。1.使用Navicat對(duì)話方式刪除索引創(chuàng)建索引刪除索引使用語句DROPINDEX，其語法格式如下：DROPINDEX<索引名>ON<表名>2.使用DROPINDEX語句刪除索引操作案例知識(shí)點(diǎn)概述示例4-7運(yùn)行結(jié)果如下：刪除課程表（course）中的索引ix_cName。謝謝索引的創(chuàng)建和使用

約束管理

本節(jié)內(nèi)容1.主鍵約束（PRIMARYKEY）2.唯一性約束（UNIQUE）3.默認(rèn)值約束（DEFAULT）4.外鍵約束（FOREIGNKEY）約束管理約束是對(duì)列進(jìn)行限制的規(guī)則，以確保輸入數(shù)據(jù)的一致性和正確性。約束是實(shí)現(xiàn)數(shù)據(jù)完整性的主要途徑。常見的約束有：主鍵約束、唯一性約束、默認(rèn)值約束、外鍵約束、非空約束等。約束可以在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建，也可以在修改數(shù)據(jù)表時(shí)創(chuàng)建。約束管理主鍵約束（簡(jiǎn)稱為主鍵）是MySQL中使用最為頻繁的約束，在設(shè)計(jì)數(shù)據(jù)表時(shí)，一般情況下，都會(huì)要求表中設(shè)置一個(gè)主鍵。主鍵值必須唯一標(biāo)識(shí)表中的每一行，且不能為NULL值，即表中不可能存在有相同主鍵值的兩行數(shù)據(jù)。每張表只能定義一個(gè)主鍵，主鍵可以是單字段主鍵，也可以是多字段組合主鍵。創(chuàng)建主鍵約束后，會(huì)自動(dòng)創(chuàng)建一個(gè)與約束同名的主鍵索引。1.主鍵約束（PRIMARYKEY）約束管理（1）在創(chuàng)建表時(shí)設(shè)置主鍵約束在CREATETABLE語句中，通過PRIMARYKEY關(guān)鍵字來指定主鍵約束。在定義字段的同時(shí)指定主鍵約束，其語法格式如下：<字段名><數(shù)據(jù)類型>PRIMARYKEY也可以在定義完所有字段之后指定主鍵約束，其語法格式如下： [CONSTRAINT<約束名>]PRIMARYKEY(字段名)若是組合主鍵約束，其語法格式如下： [CONSTRAINT<約束名>]PRIMARYKEY(字段名1,字段名2[,…])1.主鍵約束（PRIMARYKEY）操作案例知識(shí)點(diǎn)概述示例4-8運(yùn)行結(jié)果如下：創(chuàng)建學(xué)生表（student1），設(shè)置id字段為主鍵約束。操作案例知識(shí)點(diǎn)概述示例4-9運(yùn)行結(jié)果如下：創(chuàng)建課程表（course1），設(shè)置id字段為主鍵約束。操作案例知識(shí)點(diǎn)概述示例4-10運(yùn)行結(jié)果如下：創(chuàng)建成績(jī)表（score1），設(shè)置sId、cId字段為組合主鍵約束。約束管理（2）在修改表時(shí)添加主鍵約束主鍵約束不僅可以在創(chuàng)建表的同時(shí)創(chuàng)建，也可以在修改表時(shí)添加。但是需要注意的是，設(shè)置成主鍵約束的字段中不允許有空值和重復(fù)值。在修改數(shù)據(jù)表時(shí)添加主鍵約束的語法格式如下：ALTERTABLE<表名> ADD[CONSTRAINT<約束名>]PRIMARYKEY(<字段名>);1.主鍵約束（PRIMARYKEY）操作案例知識(shí)點(diǎn)概述示例4-11運(yùn)行結(jié)果如下：修改學(xué)生表（student1），給id字段添加主鍵約束。說明：首先要確保學(xué)生表（student1）中無主鍵約束，還要確保設(shè)置成主鍵約束的字段中值不能夠有重復(fù)的，并且要保證是非空的。否則，無法設(shè)置主鍵約束。約束管理（3）刪除主鍵約束當(dāng)一張表中不需要主鍵約束時(shí)，就需要從表中將其刪除。刪除主鍵約束的語法格式如下：ALTERTABLE<表名>DROPPRIMARYKEY;說明：由于主鍵約束在一張表中只能有一個(gè)，因此不需要指定主鍵名就可以刪除一張表中的主鍵約束。1.主鍵約束（PRIMARYKEY）操作案例知識(shí)點(diǎn)概述示例4-12運(yùn)行結(jié)果如下：刪除學(xué)生表（student1）中的主鍵約束。約束管理唯一性約束與主鍵約束有相似的地方，就是它們都能夠確保列的唯一性。不同的是，唯一性約束在一張表中可以有多個(gè)，并且設(shè)置唯一性約束的列允許有空值，但是只能有一個(gè)空值；而主鍵約束在一張表中只能有一個(gè)，而且是不允許有空值的。2.唯一性約束（UNIQUE）約束管理（1）在創(chuàng)建表時(shí)設(shè)置唯一性約束在CREATETABLE語句中，通過UNIQUE關(guān)鍵字來指定唯一性約束，唯一性約束通常設(shè)置在除了主鍵以外的其它列上。在定義字段的同時(shí)指定唯一性約束，其語法格式如下：<字段名><數(shù)據(jù)類型>UNIQUE也可以在定義完所有字段之后指定唯一性約束，其語法格式如下： [CONSTRAINT<約束名>]UNIQUE(字段名)2.唯一性約束（UNIQUE）操作案例知識(shí)點(diǎn)概述示例4-13運(yùn)行結(jié)果如下：創(chuàng)建學(xué)生表（student1），設(shè)置id字段為主鍵約束、sNo字段為唯一性約束。操作案例知識(shí)點(diǎn)概述示例4-14運(yùn)行結(jié)果如下：創(chuàng)建課程表（course1），設(shè)置id字段為主鍵約束、cNo為唯一性約束。約束管理（2）在修改表時(shí)添加唯一性約束在修改數(shù)據(jù)表時(shí)添加唯一性約束的語法格式如下：ALTERTABLE<表名> ADD[CONSTRAINT<約束名>]UNIQUE(<字段名>);2.唯一性約束（UNIQUE）操作案例知識(shí)點(diǎn)概述示例4-15運(yùn)行結(jié)果如下：修改課程表（course1），給cName字段添加唯一性約束。約束管理（3）刪除唯一性約束刪除唯一性約束的語法格式如下：ALTERTABLE<表名>

DROPINDEX<唯一性約束名>;2.唯一性約束（UNIQUE）操作案例知識(shí)點(diǎn)概述示例4-16運(yùn)行結(jié)果如下：刪除課程表（course1）中的唯一性約束ux_cName。約束管理默認(rèn)約束是用來給表中某列賦予一個(gè)常量值（默認(rèn)值），當(dāng)向該表插入一條新的記錄時(shí)，如果用戶沒有明確給出該列的值，MySQL會(huì)自動(dòng)為該列插入所設(shè)置的默認(rèn)值。3.默認(rèn)值約束（DEFAULT）約束管理（1）在創(chuàng)建表時(shí)設(shè)置默認(rèn)值約束在CREATETABLE語句中，通過DEFAULT關(guān)鍵字來指定默認(rèn)值約束，其語法格式如下：<字段名><數(shù)據(jù)類型>DEFAULT<默認(rèn)值>說明：“默認(rèn)值”是指該字段所設(shè)置的默認(rèn)值，如果是字符類型，需要用單引號(hào)括起來。3.默認(rèn)值約束（DEFAULT）操作案例知識(shí)點(diǎn)概述示例4-17運(yùn)行結(jié)果如下：創(chuàng)建學(xué)生表（student1），設(shè)置sex字段設(shè)置默認(rèn)值為“男”。約束管理（2）在修改表時(shí)添加默認(rèn)值約束在修改數(shù)據(jù)表時(shí)添加默認(rèn)值約束的語法格式如下：ALTERTABLE<表名> MODIFY<字段名><數(shù)據(jù)類型>DEFAULT<默認(rèn)值>;3.默認(rèn)值約束（DEFAULT）操作案例知識(shí)點(diǎn)概述示例4-18運(yùn)行結(jié)果如下：修改課程表（course1），給credit字段添加默認(rèn)值“4”。約束管理（3）刪除默認(rèn)值約束刪除默認(rèn)值約束的語法格式如下：ALTERTABLE<表名> MODIFY<字段名><數(shù)據(jù)類型>DEFAULTNULL;3.默認(rèn)值約束（DEFAULT）操作案例知識(shí)點(diǎn)概述示例4-19運(yùn)行結(jié)果如下：刪除課程表（course1）中credit字段的默認(rèn)值約束。約束管理（4）使用Navicat對(duì)話方式創(chuàng)建默認(rèn)約束以給學(xué)生表（student）的sex字段添加默認(rèn)值“男”為例。在Navicat控制臺(tái)中，打開表結(jié)構(gòu)設(shè)計(jì)窗口。3.默認(rèn)值約束（DEFAULT）約束管理外鍵約束經(jīng)常與主鍵約束一起使用。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的表而言，相關(guān)聯(lián)字段中主鍵所在的表就是主表（父表），外鍵所在的表就是從表（子表）。在MySQL中，目前只有InnoDB存儲(chǔ)引擎支持外鍵約束。外鍵用來建立主表與從表的關(guān)聯(lián)關(guān)系，為兩個(gè)表的數(shù)據(jù)建立連接，約束兩個(gè)表中數(shù)據(jù)的一致性和完整性。例如，主表刪除某條記錄時(shí)，從表中與之對(duì)應(yīng)的記錄也必須有相應(yīng)的改變。一個(gè)表可以有一個(gè)或多個(gè)外鍵，外鍵可以為空值，若不為空值，則每一個(gè)外鍵的值必須等于主表中主鍵的某個(gè)值。4.外鍵約束（FOREIGNKEY）約束管理定義外鍵時(shí)，需要遵守下列規(guī)則：主表必須已經(jīng)存在于數(shù)據(jù)庫中，或者是當(dāng)前正在創(chuàng)建的表。如果是后一種情況，則主表與從表是同一個(gè)表，這樣的表稱為自參照表，這種結(jié)構(gòu)稱為自參照完整性。必須為主表定義主鍵。主鍵不能包含空值，但允許在外鍵中出現(xiàn)空值。也就是說，只要外鍵的每個(gè)非空值出現(xiàn)在指定的主鍵中，這個(gè)外鍵的內(nèi)容就是正確的。在主表的表名后面指定列名或列名的組合。這個(gè)列或列的組合必須是主表的主鍵或候選鍵。外鍵列的數(shù)目必須和主表中主鍵列的數(shù)目相同；外鍵列的數(shù)據(jù)類型必須和主表中主鍵列的數(shù)據(jù)類型相同。4.外鍵約束（FOREIGNKEY）約束管理（1）在創(chuàng)建表時(shí)設(shè)置外鍵約束在CREATETABLE語句中，通過FOREIGNKEY關(guān)鍵字來指定外鍵約束，其語法格式如下：[CONSTRAINT<約束名>]FOREIGNKEY(字段名) REFERENCES<主表名>(主鍵列) [ONDELETE{RESTRICT|NOACTION|CASCADE|SETNULL}] [ONUPDATE{RESTRICT|NOACTION|CASCADE|SETNULL}]4.外鍵約束（FOREIGNKEY）約束管理（1）在創(chuàng)建表時(shí)設(shè)置外鍵約束說明：RESTRICT：不執(zhí)行任何操作。主表的更新或者刪除企圖會(huì)被拒絕。NOACTION：與RESTRICT相同。主表的更新或者刪除企圖會(huì)被拒絕。CASCADE：從表隨主表級(jí)聯(lián)更新或級(jí)聯(lián)刪除。即，刪除或更新主表中的行，同時(shí)自動(dòng)地刪除或更新從表中對(duì)應(yīng)匹配的行。SETNULL：從表的外鍵列設(shè)置為NULL，即，刪除或更新主表中的行，同時(shí)將從表中的外鍵列設(shè)置為NULL。注意，只有在從表的外鍵列沒有被設(shè)為NOTNULL時(shí)才有效。4.外鍵約束（FOREIGNKEY）操作案例知識(shí)點(diǎn)概述示例4-20運(yùn)行結(jié)果如下：創(chuàng)建成績(jī)表（score1），設(shè)置sId字段為外鍵約束，參考的是學(xué)生表（student1）中的id字段（不執(zhí)行任何操作）。約束管理（2）在修改表時(shí)添加外鍵約