Mastering Oracle SQL学习笔记

MasteringOracleSQL学习笔记一、第2章：Where子句................................................................................................................................1二、第3章：连接...........................................................................................................................................2三、第4章：分组操作：...............................................................................................................................9四、第5章：子查询(Subquery)...................................................................................................................12五、第6章：处理时间数据.........................................................................................................................20六、第7章：集合运算(SetOperation):.......................................................................................................30七、第8章：HierachicalQuerys(级联查询)...............................................................................................37八、第9章Decode和Case...........................................................................................................................45九、第13章AdvancedAnalyticSQL(高级分析函数)：.............................................................................50十、第14章SQLBestPractise(SQL昀佳实践)：......................................................................................55一、第2章：Where子句***************************************************************************************1．Between…And子句：WhenusingBETWEEN,makesurethefirstvalueisthelowestofthetwovaluesprovided.WhileBETWEEN1AND10andBETWEEN10AND1mightseemlogicallyequivalent,specifyingthehighervaluefirstguaranteesthatyourconditionwillalwaysevaluatetoFALSE2．NULL关键字：WhenworkingwithNULL,theconceptofequalitydoesnotapply;acolumnmaybeNULL,butitwillneverequalNULL.Oracledoesn'tcomplainifyoumistakenlyusetheequalityoperatorwhenevaluatingforNULL,Thefollowingquerywillparseandexecutebutwillneverreturnrows:SELECTorder_nbr,cust_nbr,sale_price,order_dtFROMcust_orderWHEREship_dt=NULL;NVLsubstitutesaspecifiedvalueforcolumnsthatareNULL,asin:SELECTfname,lname,manager_emp_idFROMemployeeWHERENVL(manager_emp_id,-999)!=11;Inthisexample,thevalue-999issubstitutedforallNULLvalues,which,since-999isneverequalto11,guaranteesthatallrowswhosemanager_emp_idcolumnisNULLwillbeincludedintheresultset.Thus,allemployeeswhosemanager_emp_idcolumnisNULLorisnotNULLandhasavalueotherthan11willberetrievedbythequery.3.如何充分利用WHERE子句：hereareafewtipstohelpyoumakethemostofyourWHEREclauses:A．Checkyourjoinconditionscarefully.MakesurethateachdatasetintheFROMclauseisproperlyjoined.Keepinmindthatsomejoinsrequiremultipleconditions.SeeChapter3formoreinformation.B．Avoidunnecessaryjoins.JustbecausetwodatasetsinyourFROMclausecontainthesamecolumndoesnotnecessitateajoinconditionbeaddedtoyourWHEREclause.Insomedesigns,redundantdatahasbeenpropagatedtomultipletablesthroughaprocesscalleddenormalization.C．Useparentheses.Oraclemaintainsbothoperatorprecedenceandconditionprecedence,meaningthereareclearlydefinedrulesfortheorderinwhichthingswillbeevaluatedbutthesafestrouteforyouandforthosewhowilllatermaintainyourcodeistodictateevaluationorderusingparentheses.Foroperators,specifying(5*p.inventory_qty)+2ratherthan5*p.inventory_qty+2makestheorderinwhichtheoperationsshouldbeperformedclear.Forconditions,useparenthesesanytimetheORoperatorisemployed.D．HandleNULLsproperly.AfterwritingyourWHEREclause,inspecteachconditionwithrespecttoitsabilitytoproperlyhandleNULLvalues.TakethetimetounderstandthetabledefinitionsinyourdatabasesothatyouknowwhichcolumnsallowNULLs.***************************************************************************************二、第3章：连接***************************************************************************************1．JOIN的基本概念：AjoinisaSQLquerythatextractsinformationfromtwoormoretablesorviews.WhenyouspecifymultipletablesorviewsintheFROMclauseofaquery,Oracleperformsajoin,linkingrowsfrommultipletablestogether.Thereareseveraltypesofjoinstobeawareof:A．InnerjoinsInnerjoinsaretheregularjoins.Aninnerjoinreturnstherowsthatsatisfythejoincondition.Eachrowreturnedbyaninnerjoincontainsdatafromalltablesinvolvedinthejoin.B．OuterjoinsOuterjoinsareanextensiontotheinnerjoins.Anouterjoinreturnstherowsthatsatisfythejoinconditionandalsotherowsfromonetableforwhichnocorrespondingrowsexistintheothertable.C．SelfjoinsAselfjoinisajoinofatabletoitself.2．笛卡儿积：Ifyoudon'tspecifythejoinconditionwhilejoiningtwotables,Oraclecombineseachrowfromthefirsttablewitheachrowofthesecondtable.ThistypeofresultsetiscalledasaCartesianproduct.ThenumberofrowsinaCartesianproductistheproductofthenumberofrowsineachtable.3．JOIN的条件：Usuallythejoinconditionisspecifiedontheforeignkeycolumnsofonetableandtheprimarykeyoruniquekeycolumnsofanothertable.However,youcanspecifyothercolumnsaswell.Eachjoinconditioninvolvescolumnsthatrelatetwotables.Ajoinconditionmayinvolvemorethanonecolumn.Thisisusuallythecasewhenaforeignkeyconstraintconsistsofmultiplecolumns.Ajoinconditionmustinvolvecolumnswithcompatibledatatypes.Notethatthedatatyp