数据库管理系统概述英文版课件:6 SQLPPT课件下载推荐.pptx
《数据库管理系统概述英文版课件:6 SQLPPT课件下载推荐.pptx》由会员分享,可在线阅读,更多相关《数据库管理系统概述英文版课件:6 SQLPPT课件下载推荐.pptx(101页珍藏版)》请在冰点文库上搜索。
COMP231,COMP231,1,SQL,Outline,IntroductionDataDefinitionLanguage(DDL)FundamentalconceptsDataManipulationLanguage(DML)DataDefinitionLanguage(DDL)Advancedconcepts,COMP231,2,Introduction,COMP231,3,StructuredQueryLanguage(SQL)结构化查询语言themostwidelyusedrelationaldatabaselanguage.ItwasoriginallydevelopedatIBMintheSEQUEL-XRMandSystem-Rprojects(1974-1977).SEQUEL(StructuredEnglishQueryLanguage)Almostimmediately,othervendorsintroducedDBMSproductsbasedonSQL,anditisnowadefactostandard,Introduction,COMP231,4,TheSQLcontinuoustoevolveinresponsetothechangingneed.ThecurrentANSI/ISOstandardforSQLiscalledSQL:
@#@1999NotallDBMSproductssupportthefullSQL:
@#@1999yet.,Outline,IntroductionDataDefinitionLanguage(DDL)FundamentalconceptsDataManipulationLanguage(DML)DataDefinitionLanguage(DDL)Advancedconcepts,COMP231,5,DataDefinitionLanguage(DDL),COMP231,6,Allowsthespecificationof,TheschemaforeachrelationThedomainofvaluesassociatedwitheachattributeIntegrityconstraints(ICs)ThephysicalstoragestructureofeachrelationondiskThesetofindicestobemaintainedforeachrelationSecurityandauthorizationinformationforeachrelation,DomainTypesinSQL,COMP231,7,char(n)Fixedlengthcharacterstring,withuser-specifiedlengthn.varchar(n)Variablelengthcharacterstring,withuser-specifiedmaximumlengthn.intinteger(afinitesubsetoftheintegersthatismachine-dependent).smallintSmallinteger(amachine-dependentsubsetoftheintegerdomaintype).Numeric(p,d)Fixedpointnumber,withuser-specifiedprecisionofpdigits,withddigitstotherightofdecimalpoint.小数点,COMP231,8,real,doubleprecisionFloatingpointanddouble-precisionfloatingpointnumbers,withmachine-dependentprecision.float(n)Floatingpointnumber,withuser-specifiedprecisionbyparametern.,dateDates,containinga(4digits)year,monthanddate.timeTimeofday,inhours,minutesandseconds.,Nullvaluesareallowedinallthedomaintypes.Declaringanattributetobenotnullprohibitsnullvaluesforthatattribute.,COMP231,9,SchemaDefinitioninSQL,createtablecustomer,(customer-namecustomer-streetcustomer-city,char(20)notnull,char(30),char(30),primarykey(customer-name),Customercustomer-namecustomer-street,customer-city,COMP231,10,DropTable,COMP231,11,ToremovearelationfromaSQLdatabase,weusethedroptablecommand:
@#@droptabler,AlterTable,COMP231,12,Weusethealtertablecommandtoaddordeleteattributestoanexistingrelation.Alltuplesintherelationareassignednullforanewattribute.altertablecustomeraddphonechar(10)altertablecustomerdropphone,IntegrityConstraints(ICs)完整性约束,COMP231,13,IC:
@#@conditionthatmustbetrueforanyinstanceofthedatabase.,E.g.Thenotnullconstraintmeansthevalueoftheattributecannotbenull.ICsarespecifiedwhentheschemaisdefined.ICsarecheckedwhenrelationsaremodified.,COMP231,14,Moredetailswillbediscussedlater.,Outline,IntroductionDataDefinitionLanguage(DDL)FundamentalconceptsDataManipulationLanguage(DML)DataDefinitionLanguage(DDL)Advancedconcepts,COMP231,15,DataManipulationLanguage(DML),BasicSQLQueryArithmeticOperationRenameOperationStringOperationOrderingtheDisplayofTuplesAggregateOperatorSetOperationDivisionGroupByNULLvalueSequence,COMP231,16,BasicSQLQuery,ThebasicformofSQL,relation-list,Alistofrelationnames(possiblywitharange-variableaftereachname).target-list,Alistofattributesofrelationsinrelation-list.,DISTINCTtarget-,COMP231,17,SELECTlistFROM,relation-list,WHERE,qualification,qualificationComparisons(AttropconstorAttr1opAttr2,),whereopisoneofcombinedusingAND,ORandNOT.DISTINCT,anoptionalkeywordindicatingthattheanswershouldnotcontainduplicates.Defaultisthatduplicatesarenoteliminated.,DISTINCTtarget-,COMP231,18,SELECTlistFROM,relation-list,WHERE,qualification,COMP231,19,SELECTclause,specifiescolumnstoberetainedintheresult.FROMclausespecifiesacross-productoftables.WHEREclause(optional),specifiesselectionconditionsonthetablesmentionedintheFROMclause.ASQLqueryintuitivelycorrespondstoarelationalalgebraexpressioninvolvingselections,projections,andcross-products.,SELECTDISTINCTa1,a2,an,FROMR1,R2,RmWHEREP,a1,a2,an(,P(R1xR2xxRm),COMP231,20,Note:
@#@forourexamplesweusethetables:
@#@,Branch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)Loan(loan-number,amount,branch-name)Account(account-number,balance,branch-name)Borrower(customer-name,loan-number)Depositor(customer-name,account-number),Assumethatcustomer-nameisUNIQUEinthisexample.(Inreallifeapplications,(customer)nameisNOTunique.),COMP231,21,Example:
@#@Findthenamesofallbranchesintheloanrelation.,SELECTbranch-nameFROMLoan,Loan,Result,COMP231,22,Toremoveduplications,SELECTDISTINCTbranch-nameFROMLoanLoan,Result,COMP231,23,branch_name(Loan),DataManipulationLanguage(DML),BasicSQLQueryArithmeticOperationRenameOperationStringOperationOrderingtheDisplayofTuplesAggregateOperatorSetOperationDivisionGroupByNULLvalueSequence,COMP231,24,ArithmeticOperationsonRetrievedResults,Theselectclausecancontainarithmeticexpressionsinvolvingtheoperators,andandoperatingonconstantsorattributesoftuples.Thequery:
@#@selectbranch-name,loan-number,amount*100fromloan,wouldreturnarelationwhichisthesameastheloanrelations,exceptthattheattributeamountismultipliedby100,COMP231,25,ThewhereClause,Thewhereclausespecifiesconditionsthattuplesintherelationsinthefromclausemustsatisfy.FindallloannumbersforloansmadeatthePerryridgebranchwithloanamountsgreaterthan$1200.selectloan-numberfromloanwherebranch-name=“Perryridge”andamount1200SQLallowslogicalconnectivesand,or,andnot.Arithmeticexpressionscanbeusedinthecomparisonoperators.Note:
@#@attributesusedinaquery(bothselectandwhereparts)mustbedefinedintherelationsinthefromclause.,COMP231,26,ThewhereClause(Cont.),COMP231,27,SQLincludesthebetweenoperatorforconvenience.Findtheloannumberofthoseloanswithloanamountsbetween$90,000and$100,000(thatis,$and$selectloan-numberfromloanwhereamountbetween90000and100000,ThefromClause,ThefromclausecorrespondstotheCartesianproductoperationoftherelationalalgebra.,FindtheCartesianproductborrowerloanselect*fromborrower,loanItisrarelyusedwithoutawhereclause.FindthenameandloannumberofallcustomershavingaloanatthePerryridgebranch.selectdistinctcustomer-name,borrower.loan-numberfromborrower,loanwhereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”,COMP231,28,Note:
@#@forourexamplesweusethetables:
@#@,Branch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)Loan(loan-number,amount,branch-name)Account(account-number,balance,branch-name)Borrower(customer-name,loan-number)Depositor(customer-name,account-number),Assumethatcustomer-nameisUNIQUEinthisexample.(Inreallifeapplications,(customer)nameisNOTunique.),COMP231,29,DataManipulationLanguage(DML),BasicSQLQueryArithmeticOperationRenameOperationStringOperationOrderingtheDisplayofTuplesAggregateOperatorSetOperationDivisionGroupByNULLvalueSequence,COMP231,30,TheRenameOperation,COMP231,31,Renamingrelationsandattributesusingtheasclause:
@#@old-nameasnew-nameFindthenameandloannumberofallcustomershavingaloanatthePerryridgebranch;@#@replacethecolumnnameloan-numberwiththenameloan-id.selectdistinctcustomer-name,borrower.loan-numberasloan-idfromborrower,loanwhereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”,TheRenameOperation,COMP231,32,Forconvenience,usually,“as”isomitted.InOracle,“as”isomitted.,selectdistinctcustomer-name,borrower.loan-numberloan-idfromborrower,loanwhereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”,TupleVariables/Alias,COMP231,33,Tuplevariablesaredefinedinthefromclauseviatheuseofthe“as”clause.Findthecustomernamesandtheirloannumbersforallcustomershavingaloanatsomebranch.selectdistinctcustomer-name,T.loan-numberfromborrowerasT,loanasSwhereT.loan-number=S.loan-number,COMP231,34,TupleVariables/Alias,FindthenamesofallbranchesthathavegreaterassetsthansomebranchlocatedinBrooklyn.,selectdistinctT.branch-namefrombranchasT,branchasSwhereT.assetsS.assetsandS.branch-city=“Brooklyn”,branch,branch,T,S:
@#@abranchinBrooklyn,branchesinBrooklyn,DoesitreturnsbrancheswithinBrooklyn?
@#@,DataManipulationLanguage(DML),BasicSQLQueryArithmeticOperationRenameOperationStringOperationOrderingtheDisplayofTuplesAggregateOperatorSetOperationDivisionGroupByNULLvalueSequence,COMP231,35,StringOperations,COMP231,36,Characterattributescanbecomparedtoapattern:
@#@%matchesanysubstring._matchesanysinglecharacter.FindthenameofallcustomerswhosestreetincludesthesubstringMain.(E.g.,Mainroad,SmallMainRoad,AMainroad,)selectcustomer-namefromcustomerwherecustomer-streetlike“%Main%”,DataManipulationLanguage(DML),BasicSQLQueryArithmeticOperationRenameOperationStringOperationOrderingtheDisplayofTuplesAggregateOperatorSetOperationDivisionGroupByNULLvalueSequence,COMP231,37,OrderingtheDisplayofTuples,COMP231,38,Listinalphabeticorderthenamesofallcustomersselectdistinctcustomer-namefromcustomerorderbycustomer-name,selectdistinctcustomer-namefromcustomerorderbycustomer-namedescascforascendingorder(default)descfordescendingorderSQLmustperformasorttofulfillanorderbyrequest.Sincesortingalargenumberoftuplesmaybecostly,itisdesirabletosortonlywhennecessary.,OrderingtheDisplayofTuples,COMP231,39,Listthenamesofallcustomersintheascendingorderofcustomer-cityandthendescendingord