MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù) 課件 第05章-視圖與索引

MySQL數(shù)據(jù)庫應(yīng)用與開發(fā)技術(shù)第5章視圖與索引本單元教學(xué)目標(biāo)了解數(shù)據(jù)庫的數(shù)據(jù)庫視圖的概念認(rèn)識數(shù)據(jù)表索引理解索引的實(shí)現(xiàn)原理掌握數(shù)據(jù)視圖創(chuàng)建語法掌握索引的創(chuàng)建語法能夠熟練為數(shù)據(jù)表添加索引提升檢索速度3數(shù)據(jù)視圖視圖以物理數(shù)據(jù)為基礎(chǔ)，對已經(jīng)存在的數(shù)據(jù)通過一定的運(yùn)算規(guī)則獲得新的數(shù)據(jù)集合視圖構(gòu)建在數(shù)據(jù)表的基礎(chǔ)上通過對關(guān)系表數(shù)據(jù)檢索來形成新的數(shù)據(jù)集合視圖中的數(shù)據(jù)視圖不同于數(shù)據(jù)庫的物理表數(shù)據(jù)庫環(huán)境中沒有專門的視圖數(shù)據(jù)存儲單元關(guān)系表數(shù)據(jù)改變時(shí)會反映在對應(yīng)的視圖上視圖的權(quán)限視圖提供查詢、檢索操作，即支持讀操作有特殊權(quán)限的視圖可以進(jìn)行寫操作4視圖構(gòu)建可以構(gòu)建在一個(gè)或多個(gè)關(guān)系數(shù)據(jù)表上為視圖提供數(shù)據(jù)的關(guān)系表叫基表可以構(gòu)建在一個(gè)或多個(gè)視圖上可以構(gòu)建在視圖與數(shù)據(jù)表混合體上5視圖構(gòu)建語法視圖創(chuàng)建語法：CREATEVIEW視圖名(列名1,列名2,列名3,…)AS子查詢CREATEVIEWfirst_view(aa,bb,cc)ASSELECTa,b,cFROMabc創(chuàng)建語法分析：CREATEVIEW為創(chuàng)建視圖的關(guān)鍵字CREATEVIE后面為視圖的名稱視圖名稱后面為視圖的列，需用小括號括起來列名后面跟關(guān)鍵字ASAS關(guān)鍵字后面為數(shù)據(jù)的檢索子查詢，即視圖的數(shù)據(jù)來源6視圖檢索-更新-刪除語法視圖數(shù)據(jù)檢索語法SELECT列名1,列名2,列名3FROM視圖名SELECTaa,bb,ccFROMfirst_view可以用星號“*”表示檢索所有數(shù)據(jù)列SELECT*FROMfirst_view視圖刪除語法：DROPVIEW視圖名DROPVIEWfirst_view視圖修改語法：ALTERVIEW視圖名(列名1,列名2,列名3,…)AS子查詢ALTERVIEWfirst_view(ee,ff,gg)ASSELECTe,f,gFROMabc7案例應(yīng)用（視圖構(gòu)建）在my_view庫中存在如下數(shù)據(jù)表Sale、Shop、User表結(jié)構(gòu)如圖所示創(chuàng)建如圖中所示的相關(guān)視圖US_View視圖Shop_View視圖SS_View視圖檢索出相關(guān)視圖的數(shù)據(jù)8關(guān)系表與視圖組成結(jié)構(gòu)關(guān)系

9數(shù)據(jù)庫環(huán)境SQL腳本CREATEDATABASEIFNOTEXISTSmy_view;USEmy_view;DROPTABLEIFEXISTSsale;CREATETABLEsale(

sidvarchar(10)NOTNULL,

uidvarchar(10)DEFAULTNULL,cidvarchar(10)DEFAULTNULL,

stime

datetimeDEFAULTNULL,

smoneyfloatDEFAULTNULL,spaychar(1)DEFAULTNULL,PRIMARYKEY(sid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOsale(sid,uid,cid,stime,smoney,spay)VALUES('S201','U1001','C01','2019-03-0612:24:30',318,'Y'),('S202','U1002','C02','2019-04-0815:28:30',215,'Y'),('S203','U1003','C03','2019-07-1216:30:20',156,'Y');DROPTABLEIFEXISTSshop;CREATETABLEshop(cidvarchar(10)NOTNULL,

cnamevarchar(10)DEFAULTNULL,

ctypevarchar(20)DEFAULTNULL,

csuppliervarchar(45)DEFAULTNULL,amountsmallint(6)DEFAULTNULL,

salervarchar(45)DEFAULTNULL,managervarchar(45)DEFAULTNULL,addressvarchar(45)DEFAULTNULL,PRIMARYKEY(cid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOshop(cid,cname,ctype,csupplier,amount,saler,manager,address)VALUES('C01','大米','食品','食之源',100,'張新靚','孫福平','永和路45號'),('C02','鋼筆','文具','大學(xué)堂',80,'林佳佳','孫福平','永和路45號'),('C03','襯衣','服裝','金利來',75,'劉惠方','孫福平','永和路45號');DROPTABLEIFEXISTSuser;CREATETABLEuser(

uidvarchar(10)NOTNULL,

unamevarchar(45)DEFAULTNULL,

uagesmallint(6)DEFAULTNULL,

usexchar(1)DEFAULTNULL,

uaddressvarchar(50)DEFAULTNULL,PRIMARYKEY(uid))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOuser(uid,uname,uage,usex,uaddress)VALUES('U1001','李小青',25,'女','建國路33號'),('U1002','孫民輝',30,'男','建設(shè)大道15號'),('U1003','林青芳',28,'女','前進(jìn)路08號');案例應(yīng)用-視圖構(gòu)建（1）創(chuàng)建US_View視圖創(chuàng)建語句：CREATEVIEWUS_View(sid,uid,uaddress,smoney,spay)ASSELECTs.sid,u.uid,u.uaddress,s.smoney,s.spayFROMUseru,SalesWHEREs.uid=u.uid;案例應(yīng)用-視圖構(gòu)建（2）創(chuàng)建Shop_View視圖創(chuàng)建語句：CREATEVIEWShop_View(cid,cname,ctype,saler,amount)ASSELECTcid,cname,ctype,saler,amountFROMShop;案例應(yīng)用-視圖構(gòu)建（3）創(chuàng)建SS_View視圖創(chuàng)建語句：CREATEVIEWSS_View(sid,cid,cname,stime,saler)ASSELECTs.sid,sv.cid,ame,s.stime,sv.salerFROMSales,Shop_View

svWHEREs.cid=sv.cid;13數(shù)據(jù)索引定義：索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。14索引優(yōu)點(diǎn)第一，可以大大加快數(shù)據(jù)的檢索速度這也是創(chuàng)建索引的最主要的原因。第二，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第三，在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)可以顯著減少查詢中分組和排序的時(shí)間。第四，在查詢的過程中，使用優(yōu)化隱藏器提高系統(tǒng)的性能。15索引不足第一，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間這種時(shí)間隨著數(shù)據(jù)量的增加而增加第二，索引需要占物理空間每一個(gè)索引還要占一定的物理空間第三，對表中的數(shù)據(jù)進(jìn)行寫操作時(shí)數(shù)據(jù)表上會重建相關(guān)索引降低了數(shù)據(jù)寫操作的速度16索引設(shè)計(jì)原則經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段，應(yīng)該建立索引索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引頻繁進(jìn)行寫操作的表，不要建立太多的索引刪除無用的索引，避免對造成負(fù)面影響17創(chuàng)建單列索引語法格式：ALTERTABLE+表名

+ADDINDEX+

索引名+(索引列)為myorder表的goods_id字段添加名為goods_id_index的單列索引ALTERTABLEmyorderADDINDEXgoods_id_index(goods_id)

18創(chuàng)建混合索引語法格式：ALTERTABLE+表名+ADDINDEX+

索引名

+(列1,列2,列3)為myorder表的price、order_time字段添加名為ordertime_price_index的混合索引

ALTERTABLEmyorderADDINDEXordertime_price_index(price,order_time)

19查詢索引語法格式：SHOWINDEXFROM+

表名查詢?yōu)樯厦鎚yorder表創(chuàng)建的索引信息SHOWINDEXFROMmyorder20刪除索引語法格式(二選一)DROPINDEX+

索引名+ON+

表名ALTERTABLE+表名

+DROPINDEX+索引名刪除myorder表上的ordertime_price_index索引DROPINDEXordertime_price_indexONmyorderALTERTA