第11章

視圖教學內容11.1

視圖簡介11.2

創建視圖11.3查看視圖11.4修改視圖11.5刪除視圖11.6更新視圖>>視圖簡介

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