A first course in database systems(6).ppt

6TheDatabaseLanguageSQL SQL StructuredQueryLanguageSQLisavery high levellanguage Say whattodo ratherthan howtodoit Avoidalotofdata manipulationdetailsneededinprocedurallanguageslikeC orJava Databasemanagementsystemfiguresout 计算出 best waytoexecutequery Called queryoptimization 查询优化 6TheDatabaseLanguageSQL SQLandrelationalalgebra Animportantcore 核心 ofSQLisequivalenttorelationalalgebra TherearemanyimportantfeaturesofSQLthatgobeyond 超越 whatisfoundinrelationalalgebra suchasaggregation 聚合 anddatabaseupdates Dialects 方言 ofSQL TheoriginalANSI AmericanNationalStandardInstitute SQL 92orSQL2SQL 99orSQL3 6TheDatabaseLanguageSQL VersionsofSQLproducedbytheprincipalDBMSvendors AllincludethecapabilitiesofANSIstandard ConformtoalargeextenttoSQL2IncludesomeofthefeaturesintheSQL3standardEachhasitsvariationsandextensionsMaincontentsofSQL DQL select DML insert delete update DDL create drop alter DCL grant revoke 6 1SimpleQueriesinSQL Select From Wherestatements SELECT ALL DISTINCT select listFROMR WHEREcond ORDERBY expression integer ASC DESC select list column name expression ASalias name 6 1SimpleQueriesinSQL TheorderofreadingqueriesBeginwiththerelationintheFROMclause ApplytheselectionindicatedbytheWHEREclause ApplytheextendedprojectionindicatedbytheSELECTclause TheruleofwritingqueriesCaseInsensitivity 大小写不敏感 Endwith 6 1SimpleQueriesinSQL AllourSQLquerieswillbebasedonthefollowingdatabaseschema Customer custid name prov city phone company product prodid factory type spec price desc Salesman empid idno name gender phone deptid Department deptid name headerid Salesorder orderno signdate empid custid Salesitem orderno lineno prodid unitprice quantity Underlineindicateskeyattributes 6 1 1ProjectioninSQL SELECTclause presentsallattributeswithdefaultorderSELECT FROMcustomer NecessaryattributescanbelistedinSELECTclauseSELECTcustid nameFROMcustomer ThekeywordASandanalias AScanbeomitted SELECTcustid nameAScustnameFROMcustomer ExpressionSELECTorderno lineno unitprice quantityASaccountFROMsalesitem ConstantSELECToderno lineno unitprice quantityASaccount Yuan AS RMB FROMsalesitem 6 1 2SelectioninSQL WHEREclause Operands attributes constants aliasesArithmeticoperators Comparisonoperators 5000ORquantity 3000 6 1 3ComparisonofStrings Comparisonofstrings Twostringsareequaliftheyarethesamesequenceofcharacters Whenwecomparetwostrings weareaskingwhetheroneprecedestheotherindictionaryorder Emptystringisleast SELECT FROMsalesmanWHEREempid A0054 6 1 3ComparisonofStrings Patternmatch 模式匹配 LIKE s NOT LIKEp wheresisastring pisapattern thatis astringwiththeoptionaluseofthetwospecialcharacters and Matchingrules Ordinarycharactersinpmatchonlythemselvesins inpmatchesanysequenceof0ormorecharactersins inpmatchesanyonecharacterins SELECT FROMsalesmanWHEREnameLIKE 王 SELECT FROMsalesmanWHEREnameLIKE 王 6 1 4DatesandTimes Thegeneralformatofdate yyyy mm dd mm dd yyyy or ddmmyyyy Thegeneralformatoftime hh mm ss sssss d1 d1ANDd d2 SELECT FROMsalesorderWHEREsigndateBETWEEN 2008 10 1 AND 2008 10 31 6 1 5NullValues Nullvaluesrepresentunknownornonexistentvalues SometimesweneedoperateonNULLvalues Outerjoinisavariantofthejoinoperationthatdoesnotloseinformationinsituationswhereatupleofonerelationfailstojoinwithanytupleoftheotherrelation AnimportantapplicationofNULLvaluesisindefininganouterjoin 6 1 5NullValues TwoimportantrulesofoperationsonNulls WhenweoperateonaNULLandanyothervalue theresultisNULL WhenwecompareaNULLvalueandanyvalue theresultisUNKNOWN NotethatNULLisnotaconstant anditcan tbeanoperandexplicitly suchasNULL 3 ThewaytoaskifaexpressionhasthevalueNULL ExpressionIS NOT NULLTheresultisTUREorFALSE Example select fromsalesmanwheredeptidisnull NotethataggregatefunctionsSUMandAVGcanbeappliedtotheattributeswithNullvalues whilealltupleswithNulldonotparticipate 参与 totheseaggregateoperations 6 1 6TheTruth valueUnknown Threetruth values TRUE FALSEandUNKNOWNThelogicaloperators AND ORandNOT SetTrue 1 False 0 Unknown 0 5ThenaANDb Min a b aORb Max a b 6 1 6TheTruth valueUnknown Comparisonoftruth values ExpressionIS NOT TRUEExpressionIS NOT FALSEExpressionISUNKNOWNTheresultiseitherTRUEorFALSE NOTa 1 a 6 1 6TheTruth valueUnknown SQLconditions asappearinWhere Havingclauses applytoeachtuple thevaluecanbeTrue False orUnknown OnlythetupleforwhichtheconditionhasthevalueTRUEbecomepartoftheanswer Example Select fromsalesorderwheresigndate 2008 10 1 6 1 7OrderingtheOutput ORDERBYclauses ORDERBY expression integer ASC DESC Adjusttheorderofrows ASCforascending default andDESCfordescending SELECT FROMsalesorderORDERBYsigndateDESCWecanusethenumbersoftheattributesinsteadofthenames orderno 6 1SimpleQueriesinSQL Exercise P2526 1 3 6 2QueriesInvolvingMorethanOneRelation Interestingqueriesoftencombinedatafrommorethanonerelation WecanaddressseveralrelationsinonequerybylistingthemallintheFROMclause Distinguishattributesofthesamenameby Selecta1 a2 FromR1 R2 WherecondOrderByb1 6 2 1ProductsandJoinsinSQL Selecta1 a2 FromR1 R2Wherecondisequaltofollowingrelational algebraqueries a1 a2 R1condR2 a1 a2 cond R1 R2 Notethattheresultisabaginsteadofasetbydefault SELECTDISTINCT 6 2 1ProductsandJoinsinSQL Example Findthenamesandphonenumbersofallcustomerswhohavetransacted 交易 withthesalesmanwhoseIDis A0043 Step1 Select Fromsalesorder customer Step2 Select Fromsalesorder customerWheresalesorder custid customer custidANDempid A0043 Step3 Selectname phoneFromsalesorder customerWheresalesorder custid customer custidANDempid A0043 6 2 1ProductsandJoinsinSQL Example FindthetypesandspecificationsofallproductssoldbythesalesmanwhoseIDis A0043 Selecttype specFromsalesitem salesorder productWheresalesitem orderno salesorder ordernoANDsalesitem prodid product prodidANDsalesorder empid A0043 BythewayofnaturaljoinSelecttype specFromsalesitemNaturalJoinsalesorderNaturalJoinproductWheresalesorder empid A0043 6 2 2Disambiguatingattributes Whyambiguity 歧义 Aqueryinvolvesseveralrelations andamongtheserelationshavetwoormoreattributeswiththesamename Howtodisambiguateattributes R AreferstotheattributeAofrelationR IfthenameofarelationoracolumnisequaltoakeywordinSQL howtosolvethisproblem Labelthenameofarelationoracolumnbydoublequotationmarks SELECTnameFROM user 6 2 3TupleVariables Atuplevariable AnaliasofarelationSelecta1 a2FromRASS WherecondExample FindtheemployeeIDsandnamesofallemployeeswhohavethesamenames Selects1 empid s1 nameFromsalesmanass1 salesmanass2Wheres1 empids2 empidANDs1 name s2 nameOrderbys1 empid 6 2 4InterpretingMultirelationQueries Selecta1 a2 FromR1 R2 WherecondOrderByb1 Almostthesameasforsingle relationqueries StartwiththeproductofalltherelationsintheFROMclause ApplytheselectionconditionfromtheWHEREclause ProjectontothelistofattributesandexpressionsintheSELECTclause 6 2 4InterpretingMultirelationQueries Select FromR SWhereR A S B isequivalenttoFORR r1TOrnFORS s1TOsmIFR A S BTHENevaluatetheattributesoftheselectclauseandproducethetuplesofresults 6 2 5Union Intersection andDifferenceofQueries UnionmostDBMSsupport select without order by UNION ALL select without order by UNION ALL select without order by ORDERBYinteger ASC DESC IntersectmostDBMSdon tsupportExcept MinusmostDBMSdon tsupport 6 2 5Union Intersection andDifferenceofQueries Example findnamesandphonenumbersofallcustomersandsalesmen Selectname phoneFromcustomer UnionAll Selectname phoneFromsalesman Orderbyname ALLmeansthattheresultcanhaveduplicatetuples customer astype salesman 6 2QueriesInvolvingMorethanOneRelation Exercise p2626 2 2 6 3Subqueries Asubquery Anexpressionthatevaluatestoarelation ItusuallyappearsinaWHEREclause Example Findthenamesandphonenumbersofallcustomerswhohavetransactedwiththesalesman A0043 Selectname phoneFromcustomerWherecustidIN SelectcustidFromsalesorderWhereempid A0043 6 3 1SubqueriesthatProduceScalarValues Scalarvalues 标量 Asingleattributehasonlyasinglevalue Example Findthenameandphonenumberofthedepartmentmanagerofsalesman A0043 Salesman empid idno name gender phone deptid Department deptid name headerid Selectname phoneFromSalesmanWhereempid SelectheaderidFromDepartmentWheredeptid SelectDeptidFromSalesmanWhereempid A0043 6 3 2ConditionsInvolvingRelations HowtousesubqueriesinWHEREclauses NOT EXISTS subquery theconditionistrueiffsubqueryisnotempty Example SelectnameFromstudentWhereExists Select FromenrollmentWherecno c1 ANDsno student sno 6 3 2ConditionsInvolvingRelations expressioncompareALL Subquery everyvalueintheresultofsubquery Example SelectsnoFromenrollmentWherecno c1 ANDscore ALL SelectscoreFromenrollmentWherecno c1 6 3 2ConditionsInvolvingRelations expressioncompareANY Subquery anyonevalueintheresultofsubquery Example SelectsnoFromenrollmentWherecno c1 ANDscore ANY SelectscoreFromenrollmentWherecno c1 6 3 2ConditionsInvolvingRelations expression NOT IN Subquery theconditionistrueifftheresultofexpressionisequaltothe no value s insubquery expressionIN Subquery expression ANY Subquery expressionNOTIN Subquery expressionALL Subquery 6 3 2ConditionsInvolvingRelations Example FindIDsandnamesofthesalesmenwithoutorderform Selectempid nameFromsalesmanWhereempidALL SelectempidFromsalesorder or Selectempid nameFromsalesmanWhereempidNOTIN SelectempidFromsalesorder 6 3 2ConditionsInvolvingRelations Example FindIDsandsigndateofalltheorderformswhichcontaintheproduct MT2226 Selectorderno signdateFromsalesorderWhereordernoIN Selectordernofromsalesitemwhereprodid MT2226 or Selectorderno signdateFromsalesorderWhereorderno ANY Selectordernofromsalesitemwhereprodid MT2226 6 3 2ConditionsInvolvingRelations or Selectsalesorder orderno signdateFromsalesorder salesitemWheresalesorder orderno salesitem ordernoANDprodid MT2226 or Selectorderno signdateFromsalesorderassWhereExists Select fromsalesitemWheresalesitem orderno s ordernoANDprodid MT2226 6 3 2ConditionsInvolvingRelations SubqueriescanrealizeintersectionanddifferenceofsetsR S RALL S RNOTIN S R S R ANY S RIN S Notethatexpressioncompare Subquery requiresthatsubqueryreturnssinglevalue Example FindtheSalesmenwithoutorderform 6 3 2ConditionsInvolvingRelations Example Findthenamesusedbybothsalesmenandcustomers SelectnameFromsalesmanWherenameIN SelectnameFromcustomer 6 3 2ConditionsInvolvingRelations Example Findthenamesandphonenumbersofallcustomerswhohavetransactedwiththesalesman A0043 Selectname phoneFromcustomerWherecustidIN SelectcustidFromsalesorderWhereempid A0043 6 3 3ConditionsInvolvingTuples Example Findthenameofproducerwhoproducedmoviesactedby HarrisonFord SelectnameFromMovieExecWherecert IN SelectproducerC FromMovieWhere title year IN SelectmovieTitle movieYeayFromStarsINWherestarName HarrisonFord 6 3 4CorrelatedSubqueries Correlatedsubqueries 相关子查询 Thesubqueryisevaluatedmanytimes onceforeachassignmentofavaluetosometerminthesubquerythatcomesfromatuplevariableoutsidethesubquery 子查询条件中含有对外层关系的引用对外层关系的每个元组 计算一次子查询 6 3 4CorrelatedSubqueries Example FindtheIDsandnamesofallemployeeswhohavethesamenames Selectempid nameFromsalesmanassWhereempidANY SelectempidFromsalesmanWherename s name isequivalentto Selects1 empid s1 nameFromsalesmanass1 salesmanass2Wheres1 empids2 empidANDs1 name s2 name 6 3 5SubqueriesinFROMClauses 子查询结果可作为关系 直接用于FROM子句此时 子查询应当括起并给予别名Example FindtheIDsandsigndateofalltheorderformswhichcontaintheproduct MT2226 Selectt orderno signdateFromsalesorder Selectordernofromsalesitemwhereprodid MT2226 astWheresalesorder orderno t orderno 复杂的子查询用于FROM子句会影响语句的可理解性 6 3Subqueries Exercise p2746 3 1Writethefollowingqueries basedonEnrollment Sno Cno score inSQL Findnamesofthestudentswhohavelearnedcourse C1 usingEXISTS Findnamesofthestudentswhohavelearnedcourse C1 usingIN Findnamesofthestudentswhosescoreof C1 ishigherthanthatofallotherstudents Findnamesofthestudentswhohavelearnedboth C1 and C2 Findnamesofthestudentswhohavenotlearned C1 Exercise 求选修了 C1 课的学生姓名 用EXISTS表示 求选修了 C1 课的学生姓名 用IN表示 求既选 C1 课 又选 C2 课的学生姓名 求 C1 课成绩最好的学生姓名 求没选 C1 课的学生姓名 Select姓名From学生Where SelectFrom选修Where课号 C1 Exists 学号IN 学号 AND学号 学生 学号 AND学号IN Select学号From选修Where课号 C2 学号NOTIN AND成绩 ALL Select成绩From选修Where课号 C1 Exercise Findnamesofthestudentswhohavelearnedeverycourse SELECTnameFROMstudentWHERENOTEXISTS SELECTcnoFROMcourseWHERENOTEXISTS SELECT FROMenrollmentWHEREsno student snoANDcno o 6 4Full RelationOperations Inthissection weshallstudysomeoperationsthatactonrelationsasawhole EliminatingDuplicatesDuplicatesinUnionsAggregationOperatorsGroupingHavingClauses 6 4 1EliminatingDuplicates Whyduplicates SQLusesrelationsthatarebagsratherthansets andatuplecanappearmorethanonceinabag Eliminateduplicates SELECTDISTINCT Example Findtypesandspecificationsofproductswhicharesoldbythesalesman A0044 SelectDistincttype specFromsalesitem salesorder productWheresalesitem orderno salesorder ordernoANDsalesitem prodid product prodidANDsalesorder empid A0044 6 4 2DuplicatesinUnions Duplicatesinunions Unionoperationsnormallyeliminateduplicates Inordertopreventtheeliminationofduplicates wemustfollowtheoperatorUNIONbythekeywordALL R1UnionALLR2 6 4 3GroupingandAggregationinSQL Aggregation 聚合 Anoperationthatformsasinglevaluefromthelistofvaluesappearinginthecolumn Wemayalsogroupthetuplesofarelationaccordingtosomecriterion andthenaggregatewithineachgroup Selecta1 a2 FromR1 R2 Wherecond1GroupByc1 c2 Havingcond2OrderByb1 b2 6 4 4AggregationOperators Fiveaggregationoperators SUM C thesumofthevaluesincolumnC AVG C theaverageofthevaluesincolumnCMIN C theleastvalueinthecolumnCMAX C thegreatestvalueinthecolumnCCOUNT C thenumberofvalues Example Findthemaximumunitpriceofproduct FR8633 insalesitem SelectMax unitprice FromsalesitemWhereprodid FR8633 orderno 6 4 4AggregationOperators Example FindIDoftheorderformsthathavethemaximumunitpriceofproduct FR8633 SelectordernoFromsalesitemWhereprodid FR8633 ANDunitprice SelectMax unitprice FromsalesitemWhereprodid FR8633 Example FindthenumberoforderformsinOctober2008 SelectCount FromsalesorderWheresigndateBetween 2008 10 1 AND 2008 10 31 Example Findaggregateamountoforderform 4001 SelectSum unitprice quantity AsamountFromsalesitemWhereorderno 4001 6 4 5Grouping GroupByclause groupthetuplesofarelationaccordingtosomecriterion 标准 andthenaggregatewithineachgroupthenumberofrowsisthenumberofgroupsGroupBy column name expression Example Enrollment Selectsno Sum scroe FromEnrollmentGroupbysno 6 4 5Grouping Enrollment Selectcno Sum scroe FromEnrollmentGroupbycno 6 4 5Grouping Example Foreachorderform findtheaggregateamount orderedbyitsIDascendingly Selectorderno Sum unitprice quantity asamountFromsalesitemGroupbyordernoOrderbyorderno Example Findforeachproduct thesalesquantityandmoneyorderedbyproduct sIDascendingly Selectprodid Sum quantity asqty Sum unitprice quantity asamountFromsalesitemGroupbyprodidOrderbyprodid 6 4 5Grouping Notes theSELECTclauseonlyhastwokindsofterms aggregationsandattributesappearingintheGROUPBYclausewhilequeriesinvolvingGROUPBYclause Example Findforeachorderform thesigningdate salesquantityandamountorderedbyitsIDascendingly Selectsalesorder orderno signdate Sum quantity asqty Sum unitprice quantity asamountFromsalesitem salesorderWheresalesorder orderno salesitem ordernoGroupbysalesorder orderno signdateOrderbysalesorder orderno WecannotusethealiasinGROUPBYclause 6 4 5Grouping Example FindtheIDsandnamesofsalesmenhavingoneormoreorderforms theresulthasnoduplicate Selectsalesorder empid nameFromsalesorder salesmanWheresalesorder empid salesman empidGroupbysalesorder empid name isequivalentto Selectdistinctsalesorder empid nameFromsalesorder salesmanWheresalesorder empid salesman empid 6 4 6HavingClauses Sometimeswewanttochooseourgroupsbasedonsomeaggregatepropertyofthegroupitself ThenthekeyHAVINGisfollowedbyaconditionaboutthegroup Example Findforeachorderformwithsalesamountlargerthan10000yuan thesigningdate salesquantityandsalesamountorderedbyitsIDascendingly Selectsalesorder orderno signdate Sum quantity asqty Sum unitprice quantity asamountFromsalesitem salesorderWheresalesorder orderno salesitem ordernoGroupbysalesorder orderno signdateHavingSum unitprice quantity 10000Orderbysalesorder orderno 6 4 6HavingClauses Notethat WecannotusethealiasinHavingclause WecannotusetheaggregationinWhereclause TheHavingclauseonlyhastwokindsofterms aggregationsandattributesappearingintheGROUPBYclause SometimeswecansubstitutetheconditionsforconstantsinHAVINGclausesforthoseinWHEREclauses 6 4 6HavingClauses Selectsalesorder orderno signdate Sum quantity qty Sum unitprice quantity asamountFromsalesitem salesorderWheresalesorder orderno salesitem ordernoandsigndatebetween 2008 10 1 and 2008 10 31 Groupbysalesorder orderno signdateOrderbysalesorder orderno isequivalentto Selectsalesorder orderno signdate Sum quantity qty Sum unitprice quantity asamountFromsalesitem salesorderWheresalesorder orderno salesitem ordernoGroupbysalesorder orderno signdateHavingsigndatebetween 2008 10 1 and 2008 10 31 Orderbysalesorder orderno 6 4 6HavingClauses ButwecannotsubstituteanyWHEREclauseforaHAVINGclausewithaggregateconditions UsuallyitdoesnotneedHAVINGclauseifthereisnoGROUPBYclause TheaggregateoperationignoretheNull Fullquerygrammar Selecta1 a2 FromR1 R2 Wherecond1GroupByC1 C2 Havingcond2OrderByb1 b2 6 4 6HavingClauses Exercise FindNo andnamesofthestudentswhohaveatleastthreecoursesfailedtopass SELECTSno snameFROMStudentWHERESnoIN SELECTSnoFROMEnrollmentWHEREscore 3 6 4Full RelationOperations Exercise P2846 4 6 6 3 6SQLJoinExpressions Wecanconstructrelationsbyanumberofvariationsonthejoinoperatorappliedtotworelations JoinExpressionscanbeusedinFROMclause SQLJoinExpressions R1CROSSJOINR2笛卡尔积R1JOINR2ONcond 连接R1NATURALJOINR2自然连接 6 3 6SQLJoinexpressions R1CROSSJOINR2 Cartesianproduct Example SELECT FROMsalesmanCROSSJOINsalesorder isequivalentto SELECT FROMsalesman salesorder NotethatCROSSJOINcannotaddjoinconditionswithON 6 3 6SQLJoinexpressions R1JOINR2ONcond theta joinExample Select FromsalesmanJOINsalesorderONsalesman empid salesorder empid isequivalenttoSelect Fromsalesman salesorderWheresalesman empid salesorder empid 6 3 7NaturalJoins Naturaljoinshaveimpliedjoinattributesandconditions R1NATURALJOINR2 NaturaljoinsThejoinconditionisthatallpairsofattributesfromthetworelationshavingacommonnameareequ