《數(shù)據(jù)庫原理及MySQL應(yīng)用（微課版）》課件 第11章視圖

第11章

視圖教學(xué)內(nèi)容11.1

視圖簡介11.2

創(chuàng)建視圖11.3查看視圖11.4修改視圖11.5刪除視圖11.6更新視圖>>視圖簡介

1)視圖的概念視圖是一個(gè)從單張或多張基礎(chǔ)數(shù)據(jù)表或其他視圖中構(gòu)建出來的虛擬表。同基礎(chǔ)表一樣，視圖中也包含了一系列帶有名稱的列和行數(shù)據(jù)，但是數(shù)據(jù)庫中只是存放視圖的定義，也就是動(dòng)態(tài)檢索數(shù)據(jù)的查詢語句，而并不存放視圖中的數(shù)據(jù)，這些數(shù)據(jù)依舊存放于構(gòu)建視圖的基礎(chǔ)表中，只有當(dāng)用戶使用視圖時(shí)才去數(shù)據(jù)庫請求相對(duì)應(yīng)的數(shù)據(jù)，即視圖中的數(shù)據(jù)是在引用視圖時(shí)動(dòng)態(tài)生成的。因此視圖中的數(shù)據(jù)依賴于構(gòu)建視圖的基礎(chǔ)表，如果基本表中的數(shù)據(jù)發(fā)生了變化，視圖中相應(yīng)的數(shù)據(jù)也會(huì)跟著改變。使用視圖能夠帶來許多好處1.視圖能簡化用戶操作2.視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)3.視圖提供了一定程度上的數(shù)據(jù)邏輯獨(dú)立性2)為什么使用視圖創(chuàng)建視圖的關(guān)鍵字createview，SQL語法如下所示：create[orreplace][algorithm={undefined|merge|temptable}]viewview_name[(column_list)]asselect_statement[with[cascaded|local]checkoption];>>創(chuàng)建視圖【示例11-1】查看root用戶的權(quán)限示例11-1運(yùn)行效果圖selectselect_priv,create_view_priv,show_view_priv,drop_priv,super_privfrommysql.userwhereuser='root';>>創(chuàng)建視圖“select_priv”、“create_view_priv”、“show_view_priv”、“drop_priv”、“super_priv”分別表示SELECT權(quán)限、CREATEVIEW權(quán)限、SHOWVIEW權(quán)限、DROP權(quán)限以及SUPER權(quán)限；root用戶具有以上三種權(quán)限【示例11-2】單表上創(chuàng)建view1_emp視圖示例11-2運(yùn)行效果圖createviewview1_empasselectempno,ename,job,mgr,hiredate,deptnofromemp;>>創(chuàng)建視圖1)單表上創(chuàng)建視圖【示例11-3】查詢view1_emp視圖中的數(shù)據(jù)示例11-3運(yùn)行效果圖select*fromview1_emp;上述案例可以加深我們對(duì)視圖的理解：視圖就是一條SELECT語句執(zhí)行后返回的結(jié)果集，只不過是使用視圖將這條SELECT語句進(jìn)行了封裝，下次再需要進(jìn)行相同的查詢操作時(shí)，便可以直接使用該視圖，而不需要再次編寫相同的SQL語句，方便重用?！臼纠?1-4】多表上創(chuàng)建view2_emp視圖【示例11-5】查詢view2_emp視圖中的數(shù)據(jù)createviewview2_empasselecte.empno,e.ename,e.job,d.deptno,d.dname,d.locfromempeinnerjoindeptdone.deptno=d.deptno;2)多表上創(chuàng)建視圖select*fromview2_emp;【示例11-5】運(yùn)行結(jié)果：【示例11-6】其他視圖上創(chuàng)建view3_emp視圖【示例11-7】查詢view3_emp視圖中的數(shù)據(jù)createviewview3_empasselectempno,ename,job,deptno,dnamefromview2_empwhereloc='NewYork';3)其他視圖上創(chuàng)建視圖select*fromview3_emp;【示例11-7】運(yùn)行結(jié)果：【示例11-8】使用SHOWTABLES語句查看視圖【示例11-8】運(yùn)行效果：showtables;>>查看視圖1)SHOWTABLES語句查看視圖SHOWTABLESTATUS的使用語法格式如下所示：【示例11-9】使用SHOWTABLESTATUS語句查看視圖showtablestatus[{from|in}db_name][like'pattern'];2)SHOWTABLESTATUS語句查看視圖showtablestatuslike'view1_emp';DESCRIBE或者DESC不僅能夠查看表的設(shè)計(jì)信息，還能查看視圖的設(shè)計(jì)信息，其具體的語法格式如下所示：或者可以使用簡寫的方式，如下所示：describeview_name;3)DESCRIBE語句查看視圖descview_name;【示例11-10】使用DESCRIBE語句查看視圖或者：describeview1_emp;descview1_emp;使用SHOWCREATEVIEW語句，語法格式如下所示：【示例11-11】使用SHOWCREATEVIEW語句查看視圖showcreateviewview_name;4)SHOWCREATEVIEW語句查看視圖showcreateviewview1_emp;【示例11-12】在views表中查看視圖【示例11-12】運(yùn)行效果：select*frominformation_schema.viewswheretable_name='view1_emp';5)在views表中查看視圖【示例11-13】使用CREATEORREPLACE語句修改視圖【示例11-13】運(yùn)行效果：createorreplaceviewview1_empasselectename,job,mgr,hiredate,deptnofromemp;>>修改視圖1)CREATEORREPLACE語句修改視圖【示例11-14】使用DESC查看view1_emp視圖中的字段【示例11-14】運(yùn)行效果：descview1_emp;ALTER語句修改視圖的SQL語法如下所示：alter[algorithm={undefined|merge|temptable}]viewview_name[(column_list)]asselect_statement[with[cascaded|local]checkoption];2)ALTERVIEW語句修改視圖【示例11-15】使用ALTERVIEW語句修改視圖alterviewview1_empasselectename,job,mgr,deptnofromemp;【示例11-16】使用DESC再次查看view1_emp視圖中的字段descview1_emp;DROPVIEW語句刪除視圖的SQL語法如下所示：dropview[ifexists]view_name1[,view_name2]...>>刪除視圖【示例11-17】使用DROPVIEW語句刪除視圖【示例11-18】使用SHOWTABLES語句查看視圖是否刪除成功dropviewview2_emp,view3_emp;showtables;【示例11-18】運(yùn)行結(jié)果：數(shù)據(jù)庫中已經(jīng)不存在view2_emp和view3_emp兩張視圖，說明其已經(jīng)被成功刪除?！臼纠?1-19】使用INSERT語句更新視圖【示例11-20】使用SELECT語句查看view1_emp視圖中是否存在添加的數(shù)據(jù)insertintoview1_empvalues(8000,'Tom','analyst',7566,'1982-10-12',20);>>更新視圖1)使用INSERT語句更新視圖select*fromview1_empwheredeptno=20;【示例11-20】執(zhí)行結(jié)果【示例11-21】使用SELECT語句查看emp表中是否存在添加的數(shù)據(jù)【示例11-21】執(zhí)行結(jié)果：select*fromwheredeptno=20;示例20和示例21查詢結(jié)果，說明使用INSERT語句更新視圖的操作實(shí)際上影響的是創(chuàng)建視圖的基本表。【示例11-22】使用DELETE語句更新視圖【示例11-22】執(zhí)行結(jié)果：deletefromview1_empwhereename='Tom';2)使用DELETE語句更新視圖【示例11-23】使用SELECT語句查看view1_emp視圖中是否已經(jīng)刪除數(shù)據(jù)【示例11-23】執(zhí)行結(jié)果：select*fromview1_empwheredeptno=20;【示例11-24】使用SELECT語句查看emp表中是否已經(jīng)刪除數(shù)據(jù)【示例11-24】執(zhí)行結(jié)果：select*fromwheredeptno=20;示例23和示例24查詢結(jié)果顯示，使用DELETE語句更新視圖的操作實(shí)際上影響的也是創(chuàng)建視圖的基本表?！臼纠?1-25】使用UPDATE語句更新視圖【示例11-25】執(zhí)行結(jié)果：updateview1_empsetjob='analyst'whereename='Smith';3)使用UPDATE語句更新視圖【示例11-26】使用SELECT語句查看view1_emp視圖中是否已經(jīng)修改數(shù)據(jù)【示例11-26】執(zhí)行結(jié)果：select*fromview1_empwhereename='Smith';【示例11-27】使用SELECT語句查看emp表中是否已經(jīng)修改數(shù)據(jù)【示例11-27】執(zhí)行結(jié)果：select*fromempwhereename='Smith';使用UPDATE語句更新視圖操作實(shí)際上影響的也是創(chuàng)建視圖的基本表。雖然使用INSERT、DELETE以及UPDATE語句可以實(shí)現(xiàn)視圖的更新操作，但并不是所有的視圖都可以執(zhí)行更新操作，因?yàn)橐晥D的更新操作具有很多限制條件，當(dāng)視圖中包含如下所示的一種或者多種情況時(shí)視圖是不可以更新的：4)更新視圖時(shí)的限制條件(1)視圖中包含多行函數(shù)，如SUM()、MIN()、MAX()、COUNT()等?！臼纠?1-28】不可更新視圖—使用COUNT()函數(shù)【示例11-28】執(zhí)行結(jié)果：createviewview4_empasselectcount(*)fromemp;【示例11-29】不可更新視圖—驗(yàn)證view4_emp視圖是否能被更新【示例11-29】執(zhí)行結(jié)果：updateview4_empsettotal_num=10wheretotal_num=14;(2)視圖中包含DISTINCT、GROUPBY、HAVING、UNION或者UNIONALL關(guān)鍵字。【示例11-30】不可更新視圖—使用GROUPBY關(guān)鍵字【示例11-30】執(zhí)行結(jié)果：createviewview5_empasselectdeptnofromempgroupbydeptno;【示例11-31】不可更新視圖—驗(yàn)證view5_emp視圖是否能被更新【示例11-31】執(zhí)行結(jié)果：deletefromview5_empwheredeptno=10;(3)視圖中的SELECT語句包含子查詢。【示例11-32】不可更新視圖—在SELECT語句中使用子查詢【示例11-32】執(zhí)行結(jié)果：createviewview6_empasselectename,job,deptnofromempwheresal>(selectsalfromempwhereename='Clark');【示例11-33】不可更新視圖—驗(yàn)證view6_emp視圖是否能被更新【示例11-33】執(zhí)行結(jié)果：deletefromview6_empwhereename='Jones';(4)視圖引用的只是文字值（也成為常量視圖，這種情況下，根本沒有要更新的基礎(chǔ)表）。【示例11-34】不可更新視圖—常量視圖【示例11-34】執(zhí)行結(jié)果：createviewview7_empasselectpi()pi;【示例11-35】不可更新視圖—驗(yàn)證view7_emp視圖是否能被更新【示例11-35】執(zhí)行結(jié)果：deletefromview7_empwherepilike3.141593;(5)視圖是根據(jù)不可更新視圖構(gòu)建的。【示例11-36】不可更新視圖—使用不可更新視圖構(gòu)建新視圖【示例11-36】執(zhí)行結(jié)果：createviewview8_empasselect*fromview4_emp;【示例11-37】不可更新視圖—驗(yàn)證view8_emp視圖是否能被更新【示例11-37】執(zhí)行結(jié)果：updateview8_empsettotal_num=10wheretotal_num=14;(6)創(chuàng)建視圖時(shí)指定了ALGORITHM=TEMPTABLE。【示例11-38】不可更新視圖—ALGORITHM=TEMPTABLE【示例11-38】執(zhí)行結(jié)果：createalgorithm=temptableviewview9_empasselectempno,ename,job,mgr,hiredate,deptnofromemp;【示例11-39】不可更新視圖—驗(yàn)證view9_emp視圖是否能被更新【示例11-39】執(zhí)行結(jié)果：deletefromview9_empwhereename='Jones';（7）如果視圖是連接視圖（在創(chuàng)建視圖時(shí)使用了JOIN），那么在更新視圖時(shí)需要謹(jǐn)慎處理。1)首先創(chuàng)建測試表和視圖，其SQL語句如下所示：createtablet1(xinteger);createtablet2(cinteger);createviewvmatasselectsum(x)assfromt1;createviewvupasselect*fromt2;createviewvjoinasselect*fromvmatjoinvuponvmat.s=vup.c;2)INSERT更新視圖的注意事項(xiàng)：如果要更新的視圖是連接視圖，那么必須保證組成該視圖的所有組件都是可更新的。insertintovjoin(c)values(1);系統(tǒng)會(huì)提示錯(cuò)誤，因?yàn)榻M成vjoin視圖的兩個(gè)視圖中的vmat視圖是不可更新視圖。3)UPDATE更新視圖的注意事項(xiàng)：如果要更新的視圖是連接視圖，那么至少要保證組成該視圖的一個(gè)組件必須是可更新的（這與INSERT不同）。updatevjoinsetc=c+1;如下所示的SQL語句是正確的，因?yàn)樽侄蝐來自于連接視圖中的可更新視圖。updatevjoinsetx=x+1;但是下面的SQL語句系統(tǒng)會(huì)提示錯(cuò)誤，因?yàn)樽侄蝬來自于連接視圖中的不可更新視圖。4)DELETE更新視圖的注意事項(xiàng)：如果要更新的視圖是連接視圖，那么不允許進(jìn)行刪除操作（這與INSERT和UPDATE不同）。deletevjoinwhere...;如下所示的SQL語句（偽代碼）統(tǒng)會(huì)提示錯(cuò)誤