资源描述：
《如何写出高效率的sql语句》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、如何写出高效率的sql语句Where,havingfinally,becauseonisthefirsttodonotmeettheconditionsoftherecordfilteringonlyafterthestatistics,itcanreducetheintermediateoperationsofdatatobeprocessed,supposedlyisthefastest,whereshouldalsobefasterthanhaving,becauseitfiltersthe
2、dataaftersum,inthetwotablewhenconnectingwithon,soinatablewhencomparedwithhavingwhereontheleft・Inthissingletablequerystatistics,ifnofilteringconditioninvolvescalculatingthefield,thattheirresultsarethesame,onlywherecanuseRushmoretechnology,andhavingcarm
3、ot,inthespeedofthelatterissloweriftheneedtocalculatethefield,saidinnocalculationbeforethisthevalueofthefieldisuncertain,accordingtothewrittenprocedures,wheretimeiscompletedbeforethecalculation,whilethehavingisinthecalculationafterwork,sointhiscase,the
4、resultswillbedifferent.Inmultitablejoinqueries,ongetsupearlierthanwhere・Accordingtotheconnectionconditionofeachtable,thesystemfirstsynthesizesthemultipletablesintoatemporarytable,thenfiltersthembywhere,thencalculatesandthenfiltersthembyhaving・Thus,ino
5、rdertofiltertheconditionstoplayacorrectrole,wemustfirstunderstandwhenthisconditionshouldplayarole,andthendecidewheretoputit(12)reducetablequeries:IntheSQLstatementcontainingsubquery,particularattentionshouldbepaidtoreducingthequeryonthetable:SELECT,TA
6、BNAME,FROM,TABLES,WHERE(TABNAME,DBVER)二(SELECTTAB_NAME,DB_VER,FROM,TAB_COLUMNS,WHERE,VERSION二604)(12)improveSQLefficiencythroughinternalfunctions:ComplexSQLoftensacrificesexecutionefficiency.Masteringtheaboveapplicationfunctionsandsolvingproblemsisver
7、ymeaningfulinpracticalwork(13)usethealiasname(Alias)ofthetable:WhenyouconnectmultipletablesinaSQLstatement,usethealiasofthetableandprefixthealiasoneachColumn,thusreducingparsingtimeandreducingsyntaxerrorscausedbyColumnambiguity(14)replaceINwithEXISTSa
8、ndreplaceNOTINwithNOTEXISTS:Basedonmanytablesinthequery,inordertosatisfyacondition,usuallyneedtoanothertableconnection.Inthiscase,theuseofEXISTS(NOTorEXISTS)willusuallyimprovethequeryefficiency・Inasubquery,NOTINclausewillexecuteainternalsortin
