资源描述：
《数据库系统基础教程课件.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、ViewsandIndexesVirtualViewModifyingViewsIndexesinSQLSelectionofIndexesContents：Views:“VirtualRelations”.AnotherclassofSQLrelationsthatdonotexistphysically.Theyaredefinedbyanexpressionmuchlikeaquery.Theycanbequeriedasiftheyexistedphysically,andinsomecases,wecanevenmodifyviews.Wecandefines
2、everalviewsinadatabase.Theviewisaportionofthedatabaseschema.Whyweneedviews?Simplifycomputation.Differentuserconcernaboutdifferentattributesinarelation.Safety.8.1VirtualViewsDeclaringviewsCREATEVIEW[owner.]view-name[(column-name,...)] ASselect-without-order-by[withcheckoption]Example:CREA
3、TEViewParamountMoviesAsSELECTtitle,yearFROMMoviesWHEREstudioNameLIKE'%Paramount%'Theresultisavirtualrelation(view):ParamountMovies(title,year)8.1.1DeclaringViewsExample:DefinearelationMovieProdwithmoviestitlesandthenamesoftheirproducers.CREATEViewMovieProdAsSELECTtitle,nameFROMMovies,Mov
4、ieExecWHEREproducerC#=cert#8.1.1DeclaringViewsThegrammarsofqueryingviewsandbasetablesaresame.SELECT*FROMParamountMovies;Itispossibletowritequeriesinvlovingbothviewsandbasetables.SELECTDISTINCTstarNameFROMParamountMovies,StarsInWHEREtitle=movieTitleANDyear=movieYearThequerycanbethoughtofa
5、sthesame:SELECTDISTINCTstarNameFROM(SELECTtitle,yearFROMMoviesWHEREstudioNameLIKE‘%Paramount%')Pm,StarsInWHEREPm.title=movieTitleANDyear=movieYear8.1.2QueryingViewsWecangiveaview’sattributesnewnames.Example:CREATEViewMovieProd(movieTitle,prodName)AsSELECTtitle,nameFROMMovies,MovieExecWHE
6、REproducerC#=cert#Theresultisavirtualtable:MovieProd(movieTitle,prodName)8.1.3RenamingAttributesIfthereareexpressionsintheSELECTclause,Wemustgiveaview’sattributesnewnames.Example:CREATEViewMovieProdCount(execName,movieQuantity)ASSELECTname,COUNT(*)FROMMovies,MovieExecWHEREproducerC#=cert
7、#GROUPBYMovieExec.name8.1.3RenamingAttributes字段改名Exercise:AviewRichExecgivingthename,address,certificatenumberandnetworthofallexecutiveswithanetworthofatleast$10000000.Findthenamesoffemaleswhoarebothstarsandexecutives.8.1VirtualViewsCREATEVIEWv_RichExecASSELECTname,addres
