【文档说明】分布式数据库sql语句课件.ppt，共(64)页，136.512 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92354.html

以下为本文档部分文字说明：

第四章SQL引言•IBMSYSTEMRSEQUEL•ANSI标准SQL1990•ISO标准SQL1992•SQL3(SQL99)体系结构•View•Table•FileSQL•DDL包括完整性与安全性•DMLSQLDDL•需要创建的结构–Table–V

iew–IndexCreate{table,view,index}<name><descriptionforthat>E.g.CreateTableDEPT(DEPT#Number,DNAMEChar(5),BudgetNumber(7,2));S

QLDDL–续•索引Createindex<name>on<tablename>(<indexattrnamelist>)E.g.CreateindexI1onEMP(E#);CreateindexI2onEMP(Ename)

;•唯一性索引E.g.CreateuniqueindexI1onEMP(E#);SQLDDL–续•聚集索引元组按照索引值顺序，物理上尽可能的存储在一起,在索引值上执行扫描（scan）操作时可以减少I/O.E.g.Creat

eclusterindexCI1onEMP(E#);基本查询块•典型的SQL查询语句格式:selectA1,A2,...,Anfromr1,r2,...,rmwhereP–Ais代表属性–ris代表关系–P是

谓词.Select子句•select短语用于列出所有要查询的结果属性.•例如查找DEPT关系中所有部门名字selectdnamefromDEPT•注意:SQL大小写无关Select子句-续•SQL的查询结果中允许有重复.•使用distinc

t消重复.•例如：查找DEPT关系中所有不同名的部门名字selectdistinctdnamefromDEPTSelect子句-续•select短语中可以包含数学表达式.•例如:selectS#,Sname,Status

2fromS.Where子句•where短语由给出谓词，其谓词由出现在from短语中的关系的属性组成.•查找所有居住在London并且状态大于20的供应商的供应商号selectS#fromSwherecity=‘London’ANDstatu

s>20•比较操作结果可以用逻辑操作and,or,和not相连.Where子句-续•between比较操作.•查找状态在20和30之间的供应商的商号(也就是说要,20并且30)selectS#fromSwheresta

tusbetween20and30From子句•from短语列出的关系在执行时要被扫描.•查找employee×department的结果selectfromEMP,DEPTwhereemp.D#=dept.D#重命

名操作•SQL使用别名（aliasname）对关系和属性重命名:old-namenew-name•查找所有供应商的名字、商号和状态;将S#重命名为number、将sname重命名为nameselectsnamename,s#number,statu

sfromS元组变量•from短语使用别名定义元组变量.•查找所有已供应零件的供应商名字和零件号.selectsx.sname,spx.P#fromSsx,SPSpxwheresx.S#=spx.s#串操作•SQL含有串匹配操作.末拌有两个特殊的符号描述:–

%.代表任意长的子串.–_.代表任意的单字符.•Findthenamesofallsupplierswhosecitynameincludesthesubstring“Main”.selectsnamefromswherecitylike‘%Main%’串操作-续•SQL包括其他串操

作例如–concatenation(using“||”)–convertingfromuppertolowercase(andviceversa)–findingstringlength,extractingsubstrings,etc.排序•Listinalphabetico

rderthenamesofallsupplierslocatinginLondoncityselectdistinctsnamefromSwherecity=„London’orderbysname•desc表示降序，asc表示升序

；缺省时升序–E.g.orderbysnamedesc集合操作•union,intersect,和except•集合操作自动消重复集合操作-续•Findallcitieswherehaveasupplier,apart,orboth:(selectcity

fromS)union(selectcityfromP)•Findallcitieswherehavebothasupplierandapart.(selectcityfromS)intersect(selectcityfromP)•

FindallcitieswherehaveasupplierbutnoP.(selectcityfromS)except(selectcityfromP)聚集函数•avg•min•max•sum•count聚

集函数-续•FindtheaverageQTYthatissuppliedbysuppliers1.selectavg(QTY)fromSPwheres#=„s1‟•Findthenumberoftuplesinthe

supplierrelation.selectcount(*)fromS•Findthenumberofsupplierswhosupplypart.selectcount(distincts#)fromSP聚集函数-续•Findthenumberofpartforea

chsupplier.selectsname,count(distinctp#)fromS,SPwhereS.s#=SP.s#groupbysname注意:select短语中出现在聚集函数外面的属性必须要在groupby列表

中聚集函数-续•Findthenumberofallsupplierswhohavesuppliedpartmorethan600.selects#,avg(QTY)fromSPgroupbys#havingavg(QTY)>600聚集函数-续•Note:having

短语和where短语的不同处selectd#,avg(SAL)fromEMPwhereage<40groupbyd#havingavg(SAL)>600空值•元组的某些属性有可能取空值,记为null•null表示一个未知的值或者表示一个不存在的值

.•任何涉及null的算术运算的结果是null–E.g.5+nullreturnsnull•聚集函数计算中将忽略空值空值-续•isnull谓词用于判断空值.–E.g.FindallEmployeenumberwhichappearintheEMPrela

tionwithnullvaluesford#.selectE#fromEMPwhered#isnull•任何与null的比较运算结果是unknown–E.g.5<nullornull<>nullornull=null空值-续•Totalallpartq

uantityselectsum(QTY)fromSP–上述语句忽略空值QTY–如果没有非空的QTY，结果是null•除了count(*)，所有聚集函数计算都忽略nullvalues.嵌套子查询•SQLprovide

samechanismforthenestingofsubqueries.•Asubqueryisaselect-from-whereexpressionthatisnestedwithinanotherquery.•

Acommonuseofsubqueriesistoperformtestsforsetmembership,setcomparisons,andsetcardinality.举例•Findallemployeeswho

haveworkedinsalesdepartment.selectdistinctEnamefromEMPwhered#in(selectd#fromDEPTwhereDname=„sale‟)集合比较•Findallemployeeswhosesalarygreaterthansom

emanager‟ssalary.selectEnamefromEMPwheresal>some(selectsalfromEMPwhereE#in(selectmgrfromDEPT))集合比较-续•Findthenamesofallemployeeswh

osesalarygreaterthanallmanager‟ssalary.selectEnamefromEMPwheresal>all(selectsalfromEMPwhereE#in(selectmgrfromDEPT))集合比较-续•Definitionofsetc

omparison•F<comp>somertrs.t.(F<comp>t)•F<comp>allrtr(F<comp>t)Where<comp>canbe:

集合比较-续056(5<some)=true050)=false505(5some)=true(since05)(read:5<sometupleintherelation)(5<some)=t

rue(5=some(=some)inHowever,(some)notin集合比较-续056(5<all)=false6104)=true546(5all)=true(since54and56)(5

<all)=false(5=all(all)notinHowever,(=all)in测试空关系•Theexistsconstructreturnsthevaluetrueiftheargumentsubqueryisnonempty.•existsrrØ•no

texistsrr=Ø•NotethatX–Y=ØXY举例•FindallsupplierswhohavesuppliedallpartslocatedinLondon.selectdistinctSx.snamefromSSx

wherenotexists((selectpnamefromPwherecity=„London‟)except(selectPx.pnamefromSPTx,PPxwhereTx.p#=Px.p#andSx.S

#=Tx.s#))•Note:Cannotwritethisqueryusing=allanditsvariants唯一元组测试•Theuniqueconstructtestswhetherasubqueryhasanyduplicat

etuplesinitsresult.•Findallsupplierswhoareatmostonedepartmentmanager.selectT.EnamefromEMPTwhereunique(selectR.Dn

amefromDEPTRwhereT.e#=R.MGR)•Findalldepartmentswhichhaveatleasttwoemployeeswhosesalarygraterthan$5000.selectdi

stinctT.DnamefromDEPTTwherenotunique(selectR.EnamefromEMPRwhereT.d#=R.d#andR.SAL>=5000)删除•DeleteallsupplierswhoareinLon

dondeletefromSwherecity=‘London’•Deleteallsupplierswhosupplypartp2.deletefromSwheres#in(selects#fromSPwherep#=‘p2’)Note:Herehassomeproblemwithcons

traintsthatwillexplainedinChapter8•Deletetherecordofallemployeeswithsalarybelowtheaverage.deletefromEMPwher

eSAL<(selectavg(SAL)fromEMP)–Problem:aswedeletetuplesfromEMP,theaveragesalarychanges–SolutionusedinSQL:1.First,computeavgsalaryandfindalltuplestod

elete2.Next,deletealltuplesfoundabove(withoutrecomputingavgorretestingthetuples)插入•AddanewtupletoSinsertintoSvalues(„s6‟,„wangping‟,20,„shanghai)o

requivalentlyinsertintoS(status,city,s#,sname)values(20,„shanghai‟,„s6‟,„wangping‟,)•AddanewtupletoSwithcityse

ttonullinsertintoSvalues(„s7‟,„Lihong‟,30,null)插入-续•ProvideasagiftforallloancustomersofthePerryridgebranch,a$200savingsacc

ount.insertintoaccountselectloan-number,branch-name,200fromloanwherebranch-name=„Perryridge‟•Theselectfromwherestatementisfullyevaluatedbeforeanyofit

sresultsareinsertedintotherelation(otherwisequerieslikeinsertintotable1select*fromtable1wouldcauseproblems)

更新•Increaseallemployeeswithsalaryover$4,000by6%,allotheremployeesreceive5%.–Writetwoupdatestatements:updateEMPsetSAL=SAL1.06whereSAL>4000u

pdateEMPsetSAL=SAL1.05whereSAL4000–Theorderisimportant更多举例•Findallemployeeswhohavethelowestsalaryineachdepartment

.SelectEname,d#,SALFromEMPWhereSALin(Selectmin(SAL)FromEMPGroupByd#)Note:Abovestatementhaserror,thecorrectis:SelectEname,d#,SALFromEMPW

here(d#,SAL)in(Selectd#,min(SAL)FromEMPGroupByd#)更多举例-续•FindallpartnumberanditstotalquantitySelectp#,sum(QTY)totqtyFromSPGroupByp#;orequivalentlySe

lectp#,(Selectsum(QTY)FromSPWhereSP.p#=P.p#)totqtyFromP;嵌入SQL•TheSQLstandarddefinesembeddingsofSQLinavarietyofprogramminglang

uagessuchasPascal,PL/I,Fortran,C,andCobol.•AlanguagetowhichSQLqueriesareembeddedisreferredtoasahostlanguage,andtheSQLstructurespermittedinthehostla

nguagecompriseembeddedSQL.嵌入SQL-续•ThebasicformoftheselanguagesfollowsthatoftheSystemRembeddingofSQLintoPL/I.•EXECSQLstat

ementisusedtoidentifyembeddedSQLrequesttothepreprocessorEXECSQL<embeddedSQLstatement>Note:thisvariesbylanguag

e.E.g.theJavaembeddinguses#SQL{….};嵌入SQL-续•QuerysingletupleEXECSQLSelectEnameINTO:enameFromEMPWheree#=„e1‟•QuerysettuplesTherearedismached

problembetweenhostlanguagewithsub-language,usingmiddlerelationtosolvethisquestion.Note:“:ename”calledhostvariable

whichneeddeclaredbyspecialstatement.嵌入SQL-续EXECSQLBEGINDECLARESECTION;CharSQLSTATE[6];CharP#[6];intWeight;EXECSQLENDDECLARESE

CTION;P#=‘P2;EXECSQLSelectP.weightINTO:weightFROMPWHEREP.P#=:P#;IfSQLSTATE=‘00000’Then….Else…..;嵌入SQL-续•ThestatementforSQLSTATE

–EXECSQLWHENEVER<condition><action>•Conditon–Notfoundnodatawasfound‘02000’–Sqlerroranerroroccurred举例•Spe

cifythequeryinSQLanddeclareacursorforitEXECSQLdeclareccursorforselectsname,cityfromS,SPwhereS.s#=SP.s#andSP.Q

TY>:amountFromwithinahostlanguage,findthenamesandcitiesofsupplierssupplymorethanthevariableamountquantitypart.嵌入SQL

-续•TheopenstatementcausesthequerytobeevaluatedEXECSQLopencThefetchstatementcausesthevaluesofonetupleinthequeryresulttobeplacedonhostlanguagevariable

s.EXECSQLfetchcinto:cn,:ccRepeatedcallstofetchgetsuccessivetuplesinthequeryresult嵌入SQL-续•AvariablecalledSQLSTATEintheSQLcomm

unicationarea(SQLCA)getssetto„02000‟toindicatenomoredataisavailable•Theclosestatementcausesthedatabasesystemtodeletethetemporaryrelationthatholdsther

esultofthequery.EXECSQLclosecNote:abovedetailsvarywithlanguage.E.g.theJavaembeddingdefinesJavaiteratorstostep

throughresulttuples.游标更新Canupdatetuplesfetchedbycursorbydeclaringthatthecursorisforupdatedeclareccursorforselect*fromEMPwherecity=„Parise‟forupda

teToupdatetupleatthecurrentlocationofcursorupdateEMPsetSAL=SAL+100wherecurrentofc动态SQL•AllowsprogramstoconstructandsubmitSQLqueriesat

runtime.•ThedynamicSQLprogramcontainsa?,whichisaplaceholderforavaluethatisprovidedwhentheSQLprogramisexecuted.动态SQL-续•Exa

mpleoftheuseofdynamicSQLfromwithinaCprogram.char*sqlprog=“updateEMPsetSAL=SAL*1.05whered#=?”EXECSQLpreparedy

nprogfrom:sqlprog;characcount[10]=“A-101”;EXECSQLexecutedynprogusing:account;ODBC•OpenDataBaseConnectivity(ODBC)standard–standardforapplic

ationprogramtocommunicatewithadatabaseserver.–applicationprograminterface(API)to•openaconnectionwithadatabase,•sendqueriesandupdates,•getbackresu

lts.•ApplicationssuchasGUI,spreadsheets,etc.canuseODBCODBC-续•EachdatabasesystemsupportingODBCprovide

sa"driver"librarythatmustbelinkedwiththeclientprogram.•WhenclientprogrammakesanODBCAPIcall,thecodeinth

elibrarycommunicateswiththeservertocarryouttherequestedaction,andfetchresults.•ODBCprogramfirstallocatesanSQLenvironment,thenada

tabaseconnectionhandle.ODBC-续•OpensdatabaseconnectionusingSQLConnect().•ParametersforSQLConnect:–connectionhandle,–theservertow

hichtoconnect–theuseridentifier,–password•Mustalsospecifytypesofarguments:–SQL_NTSdenotespreviousargumentisanull-terminatedstring.ODBC编程•intODBCe

xample(){RETCODEerror;HENVenv;/*environment*/HDBCconn;/*databaseconnection*/SQLAllocEnv(&env);SQLAll

ocConnect(env,&conn);SQLConnect(conn,"aura.bell-labs.com",SQL_NTS,"avi",SQL_NTS,"avipasswd",SQL_NTS);{….Doactualwork…}SQL

Disconnect(conn);SQLFreeConnect(conn);SQLFreeEnv(env);}ExerciseWritethefollowingqueries,basedonthefollowingdatabaseexampleMovie(title

,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,strName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cer

t#,netWorth)Studio(name,address,presC#)Classes(class,type,country,numGuns,bore,displacement)Ships(name,class,launched)Battles

(name,date)Outcomes(ship,battle,result)InSQL.1.FindSandraBullock’sbirthdate2.Findallexecutivesworthatleast$10,0

00,0003.FindallthestarswhoeitheraremaleorliveinMalibu4.WhichstarsappearedinmoviesproducedbyMGMin1995?5.WhoisthepresidentofMGMstudio?6.Findthecount

rieswhoseshipshadthelargestnumberofguns.7.Findthenamesoftheshipwith16-inchbore.8.Findtheaveragenumberofgunsofbattleshipclasses.