数据库管理系统概述英文版课件：6 SQLPPT课件下载推荐.pptx

数据库管理系统概述英文版课件：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