数据库系统概念全套配套课件PPT ch3.ppt 78页

'Chapter3:IntroductiontoSQL Chapter3:IntroductiontoSQLOverviewoftheSQLQueryLanguageDataDefinitionBasicQueryStructureAdditionalBasicOperationsSetOperationsNullValuesAggregateFunctionsNestedSubqueriesModificationoftheDatabase HistoryIBMSequellanguagedevelopedaspartofSystemRprojectattheIBMSanJoseResearchLaboratoryRenamedStructuredQueryLanguage(SQL)ANSIandISOstandardSQL:SQL-86,SQL-89,SQL-92SQL:1999,SQL:2003,SQL:2008Commercialsystemsoffermost,ifnotall,SQL-92features,plusvaryingfeaturesetsfromlaterstandardsandspecialproprietaryfeatures.Notallexamplesheremayworkonyourparticularsystem. DataDefinitionLanguageTheschemaforeachrelation.Thedomainofvaluesassociatedwitheachattribute.IntegrityconstraintsAndaswewillseelater,alsootherinformationsuchasThesetofindicestobemaintainedforeachrelations.Securityandauthorizationinformationforeachrelation.Thephysicalstoragestructureofeachrelationondisk.TheSQLdata-definitionlanguage(DDL)allowsthespecificationofinformationaboutrelations,including: DomainTypesinSQLchar(n).Fixedlengthcharacterstring,withuser-specifiedlengthn.varchar(n).Variablelengthcharacterstrings,withuser-specifiedmaximumlengthn.int.Integer(afinitesubsetoftheintegersthatismachine-dependent).smallint.Smallinteger(amachine-dependentsubsetoftheintegerdomaintype).numeric(p,d).Fixedpointnumber,withuser-specifiedprecisionofpdigits,withndigitstotherightofdecimalpoint.real,doubleprecision.Floatingpointanddouble-precisionfloatingpointnumbers,withmachine-dependentprecision.float(n).Floatingpointnumber,withuser-specifiedprecisionofatleastndigits.MorearecoveredinChapter4. CreateTableConstructAnSQLrelationisdefinedusingthecreatetablecommand:createtabler(A1D1,A2D2,...,AnDn,(integrity-constraint1),...,(integrity-constraintk))risthenameoftherelationeachAiisanattributenameintheschemaofrelationrDiisthedatatypeofvaluesinthedomainofattributeAiExample:createtableinstructor(IDchar(5),namevarchar(20)notnull,dept_namevarchar(20),salarynumeric(8,2))insertintoinstructorvalues(‘10211’,’Smith’,’Biology’,66000);insertintoinstructorvalues(‘10211’,null,’Biology’,66000); IntegrityConstraintsinCreateTablenotnullprimarykey(A1,...,An)foreignkey(Am,...,An)referencesrExample:Declaredept_nameastheprimarykeyfordepartment.createtableinstructor(IDchar(5),namevarchar(20)notnull,dept_namevarchar(20),salarynumeric(8,2),primarykey(ID),foreignkey(dept_name)referencesdepartment)primarykeydeclarationonanattributeautomaticallyensuresnotnull AndaFewMoreRelationDefinitionscreatetablestudent(IDvarchar(5),namevarchar(20)notnull,dept_namevarchar(20),tot_crednumeric(3,0),primarykey(ID),foreignkey(dept_name)referencesdepartment));createtabletakes(IDvarchar(5),course_idvarchar(8),sec_idvarchar(8),semestervarchar(6),yearnumeric(4,0),gradevarchar(2),primarykey(ID,course_id,sec_id,semester,year),foreignkey(ID)referencesstudent,foreignkey(course_id,sec_id,semester,year)referencessection);Note:sec_idcanbedroppedfromprimarykeyabove,toensureastudentcannotberegisteredfortwosectionsofthesamecourseinthesamesemester Andmorestillcreatetablecourse(course_idvarchar(8)primarykey,titlevarchar(50),dept_namevarchar(20),creditsnumeric(2,0),foreignkey(dept_name)referencesdepartment));Primarykeydeclarationcanbecombinedwithattributedeclarationasshownabove DropandAlterTableConstructsdroptablestudentDeletesthetableanditscontentsdeletefromstudentDeletesallcontentsoftable,butretainstablealtertablealtertableraddADwhereAisthenameoftheattributetobeaddedtorelationrandDisthedomainofA.Alltuplesintherelationareassignednullasthevalueforthenewattribute.altertablerdropAwhereAisthenameofanattributeofrelationrDroppingofattributesnotsupportedbymanydatabases BasicQueryStructureTheSQLdata-manipulationlanguage(DML)providestheabilitytoqueryinformation,andinsert,deleteandupdatetuplesAtypicalSQLqueryhastheform:selectA1,A2,...,Anfromr1,r2,...,rmwherePAirepresentsanattributeRirepresentsarelationPisapredicate.TheresultofanSQLqueryisarelation. TheselectClauseTheselectclauselisttheattributesdesiredintheresultofaquerycorrespondstotheprojectionoperationoftherelationalalgebraExample:findthenamesofallinstructors:selectnamefrominstructorNOTE:SQLnamesarecaseinsensitive(i.e.,youmayuseupper-orlower-caseletters.)E.g.Name≡NAME≡nameSomepeopleuseuppercasewhereverweuseboldfont. TheselectClause(Cont.)SQLallowsduplicatesinrelationsaswellasinqueryresults.Toforcetheeliminationofduplicates,insertthekeyworddistinctafterselect.Findthenamesofalldepartmentswithinstructor,andremoveduplicatesselectdistinctdept_namefrominstructorThekeywordallspecifiesthatduplicatesnotberemoved.selectalldept_namefrominstructor TheselectClause(Cont.)Anasteriskintheselectclausedenotes“allattributes”select*frominstructorTheselectclausecancontainarithmeticexpressionsinvolvingtheoperation,+,–,,and/,andoperatingonconstantsorattributesoftuples.Thequery:selectID,name,salary/12frominstructorwouldreturnarelationthatisthesameastheinstructorrelation,exceptthatthevalueoftheattributesalaryisdividedby12. ThewhereClauseThewhereclausespecifiesconditionsthattheresultmustsatisfyCorrespondstotheselectionpredicateoftherelationalalgebra.TofindallinstructorsinComp.Sci.deptwithsalary>80000selectnamefrominstructorwheredept_name=‘Comp.Sci."andsalary>80000Comparisonresultscanbecombinedusingthelogicalconnectivesand,or,andnot.Comparisonscanbeappliedtoresultsofarithmeticexpressions. ThefromClauseThefromclauseliststherelationsinvolvedinthequeryCorrespondstotheCartesianproductoperationoftherelationalalgebra.FindtheCartesianproductinstructorXteachesselectfrominstructor,teachesgenerateseverypossibleinstructor–teachespair,withallattributesfrombothrelationsCartesianproductnotveryusefuldirectly,butusefulcombinedwithwhere-clausecondition(selectionoperationinrelationalalgebra) CartesianProduct:instructorXteachesinstructorteaches JoinsForallinstructorswhohavetaughtsomecourse,findtheirnamesandthecourseIDofthecoursestheytaught.selectname,course_idfrominstructor,teacheswhereinstructor.ID=teaches.IDFindthecourseID,semester,yearandtitleofeachcourseofferedbytheComp.Sci.departmentselectsection.course_id,semester,year,titlefromsection,coursewheresection.course_id=course.course_idanddept_name=‘Comp.Sci." TryWritingSomeQueriesinSQLSuggestqueriestobewritten….. NaturalJoinNaturaljoinmatchestupleswiththesamevaluesforallcommonattributes,andretainsonlyonecopyofeachcommoncolumnselect*frominstructornaturaljointeaches; NaturalJoinExampleListthenamesofinstructorsalongwiththecourseIDofthecoursesthattheytaught.selectname,course_idfrominstructor,teacheswhereinstructor.ID=teaches.ID;selectname,course_idfrominstructornaturaljointeaches; NaturalJoin(Cont.)Dangerinnaturaljoin:bewareofunrelatedattributeswithsamenamewhichgetequatedincorrectlyListthenamesofinstructorsalongwiththethetitlesofcoursesthattheyteachIncorrectversion(makescourse.dept_name=instructor.dept_name)selectname,titlefrominstructornaturaljointeachesnaturaljoincourse;Correctversionselectname,titlefrominstructornaturaljointeaches,coursewhereteaches.course_id=course.course_id;Anothercorrectversionselectname,titlefrom(instructornaturaljointeaches)joincourseusing(course_id); TheRenameOperationTheSQLallowsrenamingrelationsandattributesusingtheasclause:old-nameasnew-nameE.g.selectID,name,salary/12asmonthly_salaryfrominstructorFindthenamesofallinstructorswhohaveahighersalarythansomeinstructorin‘Comp.Sci’.selectdistinctT.namefrominstructorasT,instructorasSwhereT.salary>S.salaryandS.dept_name=‘Comp.Sci.’KeywordasisoptionalandmaybeomittedinstructorasT≡instructorTKeywordasmustbeomittedinOracle StringOperationsSQLincludesastring-matchingoperatorforcomparisonsoncharacterstrings.Theoperator“like”usespatternsthataredescribedusingtwospecialcharacters:percent(%).The%charactermatchesanysubstring.underscore(_).The_charactermatchesanycharacter.Findthenamesofallinstructorswhosenameincludesthesubstring“dar”.selectnamefrominstructorwherenamelike"%dar%"Matchthestring“100%”like‘100%"escape"" StringOperations(Cont.)Pattersarecasesensitive.Patternmatchingexamples:‘Intro%’matchesanystringbeginningwith“Intro”.‘%Comp%’matchesanystringcontaining“Comp”asasubstring.‘___’matchesanystringofexactlythreecharacters.‘___%’matchesanystringofatleastthreecharacters.SQLsupportsavarietyofstringoperationssuchasconcatenation(using“||”)convertingfromuppertolowercase(andviceversa)findingstringlength,extractingsubstrings,etc. OrderingtheDisplayofTuplesListinalphabeticorderthenamesofallinstructorsselectdistinctnamefrominstructororderbynameWemayspecifydescfordescendingorderorascforascendingorder,foreachattribute;ascendingorderisthedefault.Example:orderbynamedescCansortonmultipleattributesExample:orderbydept_name,name WhereClausePredicatesSQLincludesabetweencomparisonoperatorExample:Findthenamesofallinstructorswithsalarybetween$90,000and$100,000(thatis,$90,000and$100,000)selectnamefrominstructorwheresalarybetween90000and100000Tuplecomparisonselectname,course_idfrominstructor,teacheswhere(instructor.ID,dept_name)=(teaches.ID,’Biology’); DuplicatesInrelationswithduplicates,SQLcandefinehowmanycopiesoftuplesappearintheresult.Multisetversionsofsomeoftherelationalalgebraoperators–givenmultisetrelationsr1andr2:1.(r1):Iftherearec1copiesoftuplet1inr1,andt1satisfiesselections,,thentherearec1copiesoft1in(r1).2.A(r):Foreachcopyoftuplet1inr1,thereisacopyoftupleA(t1)inA(r1)whereA(t1)denotestheprojectionofthesingletuplet1.3.r1xr2:Iftherearec1copiesoftuplet1inr1andc2copiesoftuplet2inr2,therearec1xc2copiesofthetuplet1.t2inr1xr2 Duplicates(Cont.)Example:Supposemultisetrelationsr1(A,B)andr2(C)areasfollows:r1={(1,a)(2,a)}r2={(2),(3),(3)}ThenB(r1)wouldbe{(a),(a)},whileB(r1)xr2wouldbe{(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)}SQLduplicatesemantics:selectA1,,A2,...,Anfromr1,r2,...,rmwherePisequivalenttothemultisetversionoftheexpression: SetOperationsFindcoursesthatraninFall2009orinSpring2010FindcoursesthatraninFall2009butnotinSpring2010(selectcourse_idfromsectionwheresem=‘Fall’andyear=2009)union(selectcourse_idfromsectionwheresem=‘Spring’andyear=2010)FindcoursesthatraninFall2009andinSpring2010(selectcourse_idfromsectionwheresem=‘Fall’andyear=2009)intersect(selectcourse_idfromsectionwheresem=‘Spring’andyear=2010)(selectcourse_idfromsectionwheresem=‘Fall’andyear=2009)except(selectcourse_idfromsectionwheresem=‘Spring’andyear=2010) SetOperationsSetoperationsunion,intersect,andexceptEachoftheaboveoperationsautomaticallyeliminatesduplicatesToretainallduplicatesusethecorrespondingmultisetversionsunionall,intersectallandexceptall.Supposeatupleoccursmtimesinrandntimesins,then,itoccurs:m+ntimesinrunionallsmin(m,n)timesinrintersectallsmax(0,m–n)timesinrexceptalls NullValuesItispossiblefortuplestohaveanullvalue,denotedbynull,forsomeoftheirattributesnullsignifiesanunknownvalueorthatavaluedoesnotexist.TheresultofanyarithmeticexpressioninvolvingnullisnullExample:5+nullreturnsnullThepredicateisnullcanbeusedtocheckfornullvalues.Example:Findallinstructorswhosesalaryisnull.selectnamefrominstructorwheresalaryisnull NullValuesandThreeValuedLogicAnycomparisonwithnullreturnsunknownExample:5nullornull=nullThree-valuedlogicusingthetruthvalueunknown:OR:(unknownortrue)=true,(unknownorfalse)=unknown(unknownorunknown)=unknownAND:(trueandunknown)=unknown,(falseandunknown)=false,(unknownandunknown)=unknownNOT:(notunknown)=unknown“Pisunknown”evaluatestotrueifpredicatePevaluatestounknownResultofwhereclausepredicateistreatedasfalseifitevaluatestounknown AggregateFunctionsThesefunctionsoperateonthemultisetofvaluesofacolumnofarelation,andreturnavalueavg:averagevaluemin:minimumvaluemax:maximumvaluesum:sumofvaluescount:numberofvalues AggregateFunctions(Cont.)FindtheaveragesalaryofinstructorsintheComputerSciencedepartmentselectavg(salary)frominstructorwheredept_name=’Comp.Sci.’;FindthetotalnumberofinstructorswhoteachacourseintheSpring2010semesterselectcount(distinctID)fromteacheswheresemester=’Spring’andyear=2010Findthenumberoftuplesinthecourserelationselectcount(*)fromcourse; AggregateFunctions–GroupByFindtheaveragesalaryofinstructorsineachdepartmentselectdept_name,avg(salary)frominstructorgroupbydept_name;Note:departmentswithnoinstructorwillnotappearinresult Aggregation(Cont.)Attributesinselectclauseoutsideofaggregatefunctionsmustappearingroupbylist/*erroneousquery*/selectdept_name,ID,avg(salary)frominstructorgroupbydept_name; AggregateFunctions–HavingClauseFindthenamesandaveragesalariesofalldepartmentswhoseaveragesalaryisgreaterthan42000Note:predicatesinthehavingclauseareappliedaftertheformationofgroupswhereaspredicatesinthewhereclauseareappliedbeforeforminggroupsselectdept_name,avg(salary)frominstructorgroupbydept_namehavingavg(salary)>42000; NullValuesandAggregatesTotalallsalariesselectsum(salary)frominstructorAbovestatementignoresnullamountsResultisnullifthereisnonon-nullamountAllaggregateoperationsexceptcount(*)ignoretupleswithnullvaluesontheaggregatedattributesWhatifcollectionhasonlynullvalues?countreturns0allotheraggregatesreturnnull NestedSubqueriesSQLprovidesamechanismforthenestingofsubqueries.Asubqueryisaselect-from-whereexpressionthatisnestedwithinanotherquery.Acommonuseofsubqueriesistoperformtestsforsetmembership,setcomparisons,andsetcardinality. ExampleQueryFindcoursesofferedinFall2009andinSpring2010FindcoursesofferedinFall2009butnotinSpring2010selectdistinctcourse_idfromsectionwheresemester=’Fall’andyear=2009andcourse_idin(selectcourse_idfromsectionwheresemester=’Spring’andyear=2010);selectdistinctcourse_idfromsectionwheresemester=’Fall’andyear=2009andcourse_idnotin(selectcourse_idfromsectionwheresemester=’Spring’andyear=2010); ExampleQueryFindthetotalnumberof(distinct)studentswhohavetakencoursesectionstaughtbytheinstructorwithID10101Note:Abovequerycanbewritteninamuchsimplermanner.TheformulationaboveissimplytoillustrateSQLfeatures.selectcount(distinctID)fromtakeswhere(course_id,sec_id,semester,year)in(selectcourse_id,sec_id,semester,yearfromteacheswhereteaches.ID=10101); SetComparisonFindnamesofinstructorswithsalarygreaterthanthatofsome(atleastone)instructorintheBiologydepartment.Samequeryusing>someclauseselectnamefrominstructorwheresalary>some(selectsalaryfrominstructorwheredept_name=’Biology’);selectdistinctT.namefrominstructorasT,instructorasSwhereT.salary>S.salaryandS.dept_name=’Biology’; DefinitionofSomeClauseFsomertrsuchthat(Ft)Wherecanbe:056(5all(selectsalaryfrominstructorwheredept_name=’Biology’); DefinitionofallClauseFallrtr(Ft)056(542000;NotethatwedonotneedtousethehavingclauseAnotherwaytowriteabovequeryselectdept_name,avg_salaryfrom(selectdept_name,avg(salary)frominstructorgroupbydept_name)asdept_avg(dept_name,avg_salary)whereavg_salary>42000; SubqueriesintheFromClause(Cont.)Andyetanotherwaytowriteit:lateralclauseselectname,salary,avg_salaryfrominstructorI1,lateral(selectavg(salary)asavg_salaryfrominstructorI2whereI2.dept_name=I1.dept_name);Lateralclausepermitslaterpartofthefromclause(afterthelateralkeyword)toaccesscorrelationvariablesfromtheearlierpart.Note:lateralispartoftheSQLstandard,butisnotsupportedonmanydatabasesystems;somedatabasessuchasSQLServerofferalternativesyntax WithClauseThewithclauseprovidesawayofdefiningatemporaryviewwhosedefinitionisavailableonlytothequeryinwhichthewithclauseoccurs.Findalldepartmentswiththemaximumbudgetwithmax_budget(value)as(selectmax(budget)fromdepartment)selectbudgetfromdepartment,max_budgetwheredepartment.budget=max_budget.value; ComplexQueriesusingWithClauseWithclauseisveryusefulforwritingcomplexqueriesSupportedbymostdatabasesystems,withminorsyntaxvariationsFindalldepartmentswherethetotalsalaryisgreaterthantheaverageofthetotalsalaryatalldepartmentswithdept_total(dept_name,value)as(selectdept_name,sum(salary)frominstructorgroupbydept_name),dept_total_avg(value)as(selectavg(value)fromdept_total)selectdept_namefromdept_total,dept_total_avgwheredept_total.value>=dept_total_avg.value; ScalarSubqueryScalarsubqueryisonewhichisusedwhereasinglevalueisexpectedE.g.selectdept_name,(selectcount(*)frominstructorwheredepartment.dept_name=instructor.dept_name)asnum_instructorsfromdepartment;E.g.selectnamefrominstructorwheresalary*10>(selectbudgetfromdepartmentwheredepartment.dept_name=instructor.dept_name)Runtimeerrorifsubqueryreturnsmorethanoneresulttuple ModificationoftheDatabaseDeletionoftuplesfromagivenrelationInsertionofnewtuplesintoagivenrelationUpdatingvaluesinsometuplesinagivenrelation ModificationoftheDatabase–DeletionDeleteallinstructorsdeletefrominstructorDeleteallinstructorsfromtheFinancedepartmentdeletefrominstructorwheredept_name=’Finance’;DeletealltuplesintheinstructorrelationforthoseinstructorsassociatedwithadepartmentlocatedintheWatsonbuilding.deletefrominstructorwheredept_namein(selectdept_namefromdepartmentwherebuilding=’Watson’); Deletion(Cont.)Deleteallinstructorswhosesalaryislessthantheaveragesalaryofinstructorsdeletefrominstructorwheresalary<(selectavg(salary)frominstructor);Problem:aswedeletetuplesfromdeposit,theaveragesalarychangesSolutionusedinSQL:1.First,computeavgsalaryandfindalltuplestodelete2.Next,deletealltuplesfoundabove(withoutrecomputingavgorretestingthetuples) ModificationoftheDatabase–InsertionAddanewtupletocourseinsertintocoursevalues(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);orequivalentlyinsertintocourse(course_id,title,dept_name,credits)values(’CS-437’,’DatabaseSystems’,’Comp.Sci.’,4);Addanewtupletostudentwithtot_credssettonullinsertintostudentvalues(’3003’,’Green’,’Finance’,null); Insertion(Cont.)Addallinstructorstothestudentrelationwithtot_credssetto0insertintostudentselectID,name,dept_name,0frominstructorTheselectfromwherestatementisevaluatedfullybeforeanyofitsresultsareinsertedintotherelation(otherwisequerieslikeinsertintotable1select*fromtable1wouldcauseproblems,iftable1didnothaveanyprimarykeydefined. ModificationoftheDatabase–UpdatesIncreasesalariesofinstructorswhosesalaryisover$100,000by3%,andallothersreceivea5%raiseWritetwoupdatestatements:updateinstructorsetsalary=salary*1.03wheresalary>100000;updateinstructorsetsalary=salary*1.05wheresalary<=100000;TheorderisimportantCanbedonebetterusingthecasestatement(nextslide) CaseStatementforConditionalUpdatesSamequeryasbeforebutwithcasestatementupdateinstructorsetsalary=casewhensalary<=100000thensalary*1.05elsesalary*1.03end UpdateswithScalarSubqueriesRecomputeandupdatetot_credsvalueforallstudentsupdatestudentSsettot_cred=(selectsum(credits)fromtakesnaturaljoincoursewhereS.ID=takes.IDandtakes.grade<>’F’andtakes.gradeisnotnull);Setstot_credstonullforstudentswhohavenottakenanycourseInsteadofsum(credits),use:casewhensum(credits)isnotnullthensum(credits)else0end EndofChapter3 AdvancedSQLFeatures**Createatablewiththesameschemaasanexistingtable:createtabletemp_accountlikeaccount Figure3.02 Figure3.03 Figure3.04 Figure3.05 Figure3.07 Figure3.08 Figure3.09 Figure3.10 Figure3.11 Figure3.12 Figure3.13 Figure3.16 Figure3.17'