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

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

(2)視圖名稱必須遵循標(biāo)識符的規(guī)則，且對每個用戶必須唯一。(3)用戶可以在其它視圖之上建立視圖。(4)如果視圖中的某一列是一個算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生而來，或者視圖中兩個或者更多的不同列擁有一個相同的名字，此時，需要為視圖的這些列指定特定的名稱。7.1.2創(chuàng)建視圖創(chuàng)建視圖的SQL語句語法：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ī)科學(xué)與技術(shù)專業(yè)的學(xué)生的學(xué)號、姓名，和他們選修的課程號、課程名和成績。USEteaching;CREATEVIEWv_computerASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDmajor='計算機(jī)科學(xué)與技術(shù)';7.1.2創(chuàng)建視圖【例7-3】在bankcard數(shù)據(jù)庫創(chuàng)建名為acc_count的賬戶統(tǒng)計視圖，求每個儲戶的賬戶個數(shù)，要求包括身份證號和姓名。USEbankcard;CREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,Dname;7.1.3修改視圖修改視圖，語法格式如下：ALTERVIEW[database_name.]view_name[(column_name[,...n])]AS{select_statement}[WITHCHECKOPTION]注：語句中的參數(shù)與CREATEVIEW語句中的參數(shù)相同。7.1.3修改視圖【例7-4】修改acc_count視圖，求每個儲戶的賬戶個數(shù)和總存款余額，要求包括身份證號和姓名。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)計視圖，求總存款余額少于5000的儲戶信息，包括身份證號和姓名和手機(jī)號。USEbankcard;CREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000;7.1.4使用視圖可以如同查詢基本表一樣通過視圖查詢所需要的數(shù)據(jù)，也可以通過視圖更新基本表中的數(shù)據(jù)。1．使用視圖進(jìn)行數(shù)據(jù)查詢查詢各儲戶的統(tǒng)計信息：SELECT*FROMacc_count;

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

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

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

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

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

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

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

……[UNIQUE|FULLTEXT|SPATIAL]

INDEX|KEY

[索引名]

(屬性名1[(長度)]

[ASC|DESC][,…n])

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

TABLE

表名

ADD

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

[索引名](屬性名1

[

(長度)

]

[

ASC

|

DESC][,…n]);

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

TABLE

studentADDINDEXindex_sname(sname);

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

[

UNIQUE

|

FULLTEXT

|

SPATIAL

]

INDEX|KEY

索引名ON表名(屬性名1

[

(長度)

]

[

ASC

|

DESC][,…n]);

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

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