數(shù)據(jù)庫(kù)原理與應(yīng)用教程-MySQL8.0第7章

視圖和索引7.1視圖數(shù)據(jù)庫(kù)的基本表是由數(shù)據(jù)庫(kù)設(shè)計(jì)人員根據(jù)所有用戶的需求、按照規(guī)范化設(shè)計(jì)方法設(shè)計(jì)的，并不一定符合用戶的應(yīng)用需求。MySQL可以根據(jù)各個(gè)用戶的應(yīng)用需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖。7.1.1視圖概述視圖可以視為另一種形式的表，是從一個(gè)或多個(gè)表中使用SELECT語(yǔ)句導(dǎo)出的虛表，那些用來(lái)導(dǎo)出視圖的表稱為基本表。數(shù)據(jù)庫(kù)中只存儲(chǔ)視圖的定義，而不存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存儲(chǔ)在導(dǎo)出視圖的基本表中，視圖實(shí)際上是一個(gè)查詢結(jié)果。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時(shí)，視圖中的數(shù)據(jù)也隨之改變。7.1.1視圖概述視圖的優(yōu)點(diǎn)如下：(1)為用戶集中數(shù)據(jù)，簡(jiǎn)化用戶的數(shù)據(jù)查詢和處理。(2)保證數(shù)據(jù)的邏輯獨(dú)立性。(3)重新定制數(shù)據(jù)，使得數(shù)據(jù)便于共享。(4)提高了數(shù)據(jù)的安全性。7.1.2創(chuàng)建視圖必須遵循的原則：(1)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建視圖。

(2)視圖名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須唯一。(3)用戶可以在其它視圖之上建立視圖。(4)如果視圖中的某一列是一個(gè)算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生而來(lái)，或者視圖中兩個(gè)或者更多的不同列擁有一個(gè)相同的名字，此時(shí)，需要為視圖的這些列指定特定的名稱。7.1.2創(chuàng)建視圖創(chuàng)建視圖的SQL語(yǔ)句語(yǔ)法：CREATEVIEW[database_name.]view_name

[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]7.1.2創(chuàng)建視圖【例7-1】創(chuàng)建名為v_male的視圖，包括所有男生的基本信息。USEteaching;CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男';7.1.2創(chuàng)建視圖【例7-2】創(chuàng)建名為v_computer的視圖，包括計(jì)算機(jī)科學(xué)與技術(shù)專業(yè)的學(xué)生的學(xué)號(hào)、姓名，和他們選修的課程號(hào)、課程名和成績(jī)。USEteaching;CREATEVIEWv_computerASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDmajor='計(jì)算機(jī)科學(xué)與技術(shù)';7.1.2創(chuàng)建視圖【例7-3】在bankcard數(shù)據(jù)庫(kù)創(chuàng)建名為acc_count的賬戶統(tǒng)計(jì)視圖，求每個(gè)儲(chǔ)戶的賬戶個(gè)數(shù)，要求包括身份證號(hào)和姓名。USEbankcard;CREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改視圖修改視圖，語(yǔ)法格式如下：ALTERVIEW[database_name.]view_name[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]注：語(yǔ)句中的參數(shù)與CREATEVIEW語(yǔ)句中的參數(shù)相同。7.1.3修改視圖【例7-4】修改acc_count視圖，求每個(gè)儲(chǔ)戶的賬戶個(gè)數(shù)和總存款余額，要求包括身份證號(hào)和姓名。USEbankcard;ALTERVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumber,SUM(Balance)SumBalanceFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改視圖【例7-5】在視圖上創(chuàng)建視圖：創(chuàng)建Few_Balance余額統(tǒng)計(jì)視圖，求總存款余額少于5000的儲(chǔ)戶信息，包括身份證號(hào)和姓名和手機(jī)號(hào)。USEbankcard;CREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000;7.1.4使用視圖可以如同查詢基本表一樣通過(guò)視圖查詢所需要的數(shù)據(jù)，也可以通過(guò)視圖更新基本表中的數(shù)據(jù)。1．使用視圖進(jìn)行數(shù)據(jù)查詢查詢各儲(chǔ)戶的統(tǒng)計(jì)信息：SELECT*FROMacc_count;

【例7-6】查詢v_computer視圖，統(tǒng)計(jì)計(jì)算機(jī)應(yīng)用基礎(chǔ)課程的總分和平均分。SELECTSUM(score)sumscore,AVG(score)avgscoreFROMv_computerWHEREcname='計(jì)算機(jī)應(yīng)用基礎(chǔ)';

7.1.4使用視圖2．使用視圖更新基本表中數(shù)據(jù)更新視圖的數(shù)據(jù)，其實(shí)就是對(duì)基本表中數(shù)據(jù)進(jìn)行更新，因?yàn)檎嬲鎯?chǔ)數(shù)據(jù)的地方是基本表，同樣使用INSERT、UPDATE、DELETE語(yǔ)句來(lái)完成。但并不是所有的視圖都可以進(jìn)行數(shù)據(jù)更新，只有滿足以下可更新條件的視圖才能進(jìn)行數(shù)據(jù)更新。(1)任何通過(guò)視圖的數(shù)據(jù)更新都只能引用一個(gè)基本表的列。①如果視圖中數(shù)據(jù)為一個(gè)表的行、列子集，則此視圖可更新；但如果視圖中沒(méi)有包含表中某個(gè)不允許取空值又沒(méi)有默認(rèn)值約束的列，則不能插入數(shù)據(jù)。7.1.4使用視圖②如果視圖所依賴的基本表有多個(gè)時(shí)，完全不能向該視圖添加（INSERT）數(shù)據(jù)。③如果視圖所依賴的基本表有多個(gè)時(shí)，那么一次修改只能修改（UPDATE）一個(gè)基本表中的數(shù)據(jù)。④如果視圖所依賴的基本表有多個(gè)時(shí)，那么不能通過(guò)視圖刪除（DELETE）數(shù)據(jù)。(2)視圖中被修改的列必須直接引用表中基礎(chǔ)數(shù)據(jù)，不能是通過(guò)任何其他方式派生而來(lái)的，比如通過(guò)聚合函數(shù)、計(jì)算（如表達(dá)式計(jì)算）、集合運(yùn)算等。(3)被修改的列不應(yīng)是在創(chuàng)建視圖時(shí)受GROUPBY、HAVING、DISTINCT子句影響的。7.1.4使用視圖根據(jù)以上可更新條件分析例7-1~例7-3中各視圖是否為可更新視圖，即能否通過(guò)此視圖更新基本表中數(shù)據(jù)？很明顯，例7-1的視圖完全可更新例7-2的視圖只可以修改數(shù)據(jù)，不能插入和刪除例7-3的視圖完全不可更新7.1.4使用視圖通常有可能插入并不滿足視圖查詢的WHERE子句條件中的一行。為了限制此操作，可以在創(chuàng)建視圖時(shí)使用WITHCHECKOPTION選項(xiàng)?！纠?-8】通過(guò)v_male視圖向student表中插入一個(gè)男生。INSERTINTOv_maleVALUES('2021010006','張三','男','2003-8-1','電子信息','2021級(jí)');7.1.4使用視圖通過(guò)v_male視圖向student表中插入一個(gè)女生，也可以完成。如果不希望用戶通過(guò)v_male視圖插入女生，在創(chuàng)建v_male視圖時(shí)應(yīng)該使用WITHCHECKOPTION選項(xiàng)。CREATEVIEWv_maleASSELECTsno,sname,ssex,sbirthday,major,gradeFROMstudentWHEREssex='男'

WITHCHECKOPTION;7.1.5刪除視圖在不需要該視圖或想清除視圖定義及與之相關(guān)聯(lián)的權(quán)限時(shí)，可以刪除該視圖。視圖的刪除不會(huì)影響所依附的基本表的數(shù)據(jù)。1．在Navicat中選菜單界面刪除視圖2．使用SQL語(yǔ)句刪除視圖語(yǔ)法：DROPVIEWview_name【例7-9】刪除例7-2創(chuàng)建的v_computer視圖。USEteaching;DROPVIEWv_computer;7.2索引索引(Index)是對(duì)數(shù)據(jù)庫(kù)表中一個(gè)或多個(gè)列的值進(jìn)行排序的結(jié)構(gòu)，其主要目的是提高M(jìn)ySQL系統(tǒng)的性能，加快數(shù)據(jù)的查詢速度和減少系統(tǒng)的響應(yīng)時(shí)間。7.2.1索引簡(jiǎn)介數(shù)據(jù)庫(kù)的索引就類(lèi)似于書(shū)籍的目錄，如果想快速查找而不是逐頁(yè)查找指定的內(nèi)容，可以通過(guò)目錄中章節(jié)的頁(yè)號(hào)快速找到其對(duì)應(yīng)的內(nèi)容。在表中建立索引，然后在索引中找到符合查詢條件的索引值，最后通過(guò)保存在索引中的ROWID(行號(hào)，相當(dāng)于頁(yè)碼)快速找到表中對(duì)應(yīng)的記錄。7.2.1索引簡(jiǎn)介索引的優(yōu)點(diǎn)：（1）大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。（2）創(chuàng)建唯一性索引，可以保證表中每一行數(shù)據(jù)的唯一性。（3）可以加速表和表之間的連接。

（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。

（5）查詢優(yōu)化器可以提高系統(tǒng)的性能，但它是依靠索引起作用的。

7.2.1索引簡(jiǎn)介缺點(diǎn)：創(chuàng)建索引和維護(hù)索引都會(huì)消耗時(shí)間，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改操作時(shí)，索引就要進(jìn)行維護(hù)，否則索引的作用就會(huì)下降。另外，每個(gè)索引都會(huì)占用一定的物理空間，如果占用的物理空間過(guò)多，就會(huì)影響到整個(gè)MySQL系統(tǒng)的性能。7.2.2索引分類(lèi)1.兩大索引方法MySQL主要有HASH和B+樹(shù)索引兩大索引方法。（1）HASHHASH索引可以一次定位，不需要像樹(shù)形索引那樣逐層查找，具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對(duì)于范圍查詢、排序及組合索引仍然效率不高。（2）B+樹(shù)B+樹(shù)索引就是一個(gè)中序遍歷有序的樹(shù)形結(jié)構(gòu)。這是MySQL里默認(rèn)和最常用的索引類(lèi)型，實(shí)例中只按默認(rèn)創(chuàng)建B+樹(shù)索引。7.2.2索引分類(lèi)2.索引類(lèi)別MySQL的索引類(lèi)別包括普通索引、主鍵索引、唯一索引、全文索引和空間索引。另外，還有一種前綴索引。如果索引是根據(jù)單列創(chuàng)建的，這樣的索引稱為單列索引，根據(jù)多列組合創(chuàng)建的索引則稱為組合索引。7.2.3創(chuàng)建索引哪些列適合創(chuàng)建索引，哪些列不適合創(chuàng)建索引，需要進(jìn)行詳細(xì)的考察。1．創(chuàng)建索引時(shí)應(yīng)考慮的問(wèn)題（1）對(duì)一個(gè)表中建大量的索引，應(yīng)進(jìn)行權(quán)衡。對(duì)于SELECT查詢，大量索引可以提高性能，可以從中選擇最快的查詢方法；但是，會(huì)影響INSERT、UPDATE和DELETE語(yǔ)句的性能。因?yàn)閷?duì)表中的數(shù)據(jù)進(jìn)行修改時(shí)，索引也要?jiǎng)討B(tài)的維護(hù)，維護(hù)索引耗費(fèi)的時(shí)間會(huì)隨著數(shù)據(jù)量的增加而增加，所以應(yīng)避免對(duì)經(jīng)常更新的表建立過(guò)多的索引，而對(duì)更新少而且數(shù)據(jù)量大的表創(chuàng)建多個(gè)索引，可以大大提高查詢性能。7.2.3創(chuàng)建索引（2）對(duì)于小型表（行數(shù)較少）進(jìn)行索引可能不會(huì)產(chǎn)生優(yōu)化效果。（3）對(duì)于主鍵和外鍵列應(yīng)考慮建索引，因?yàn)榻?jīng)常通過(guò)主鍵查詢數(shù)據(jù)，而外鍵用于表間的連接。（4）很少在查詢中使用的列以及值很少的列（比如“性別”列）不應(yīng)考慮建索引。（5）如果char或varchar類(lèi)型的列字符數(shù)很多，可視情況選擇前n個(gè)字符值進(jìn)行索引，即創(chuàng)建前綴索引。7.2.3創(chuàng)建索引2．在Navicat中通過(guò)菜單界面創(chuàng)建索引3．利用SQL語(yǔ)句創(chuàng)建索引（1）CREATETABLE語(yǔ)句創(chuàng)建索引語(yǔ)法格式：CREATETABLE

表名(屬性名數(shù)據(jù)類(lèi)型[完整性約束條件],

……[UNIQUE|FULLTEXT|SPATIAL]

INDEX|KEY

[索引名]

(屬性名1[(長(zhǎng)度)]

[ASC|DESC][,…n])

);7.2.3創(chuàng)建索引【例7-10】在teaching1庫(kù)中創(chuàng)建student1表，包含sno、sname、ssex和idno列，含義分別為學(xué)號(hào)、姓名、性別、身份證號(hào)，并按姓名列創(chuàng)建一個(gè)名為index_sname的普通索引。USEteaching1;CREATETABLEstudent1(snochar(10)PRIMARYKEY,snamevarchar(10)NOTNULL,ssexchar(1),idnochar(18),INDEXindex_sname(sname));7.2.3創(chuàng)建索引（2）ALTERTABLE語(yǔ)句創(chuàng)建索引ALTER

TABLE

表名

ADD

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

[索引名](屬性名1

[

(長(zhǎng)度)

]

[

ASC

|

DESC][,…n]);

【例7-11】修改teaching庫(kù)中student表，按姓名列創(chuàng)建一個(gè)名為index_sname的普通索引。USEteaching;ALTER

TABLE

studentADDINDEXindex_sname(sname);

7.2.3創(chuàng)建索引（3）CREATEINDEX語(yǔ)句創(chuàng)建索引語(yǔ)法格式如下：CREATE

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

索引名ON表名(屬性名1

[

(長(zhǎng)度)

]

[

ASC

|

DESC][,…n]);

【例7-12】在teaching1庫(kù)中student1表上，按身份證號(hào)列創(chuàng)建一個(gè)名為index_idno的唯一性索引。USEteaching1;CREATEUNIQUEINDEXindex_idnoONstudent1(idno);

7.2.3創(chuàng)建索引【例7-13】根據(jù)teaching庫(kù)中student表的專業(yè)、年級(jí)創(chuàng)建一個(gè)名為major_grade的組合索引，其中專業(yè)稱為升序，年級(jí)為降序。USEteaching;CREATEINDEXmajor_gradeONstudent(major,gradeDESC);7.2.3創(chuàng)建索引4．間接創(chuàng)建索引在定義表結(jié)構(gòu)或修改表結(jié)構(gòu)時(shí)，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創(chuàng)建索引?！纠?-15】在teaching1庫(kù)中創(chuàng)建一個(gè)teacher教師表，并定義主鍵約束和唯一性約束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(18)UNIQUE,tnamevarchar(10));7.2.3創(chuàng)建索引4．間接創(chuàng)建索引在定義表結(jié)構(gòu)或修改表結(jié)構(gòu)時(shí)，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創(chuàng)建索引?！纠?-15】在teaching1庫(kù)中創(chuàng)建一個(gè)teacher教師表，并定義主鍵約束和唯一性約束。USEteaching1;CREATETABLEteacher(tnochar(6)PRAMARYKEY,tidchar(1