【文档说明】分布式数据库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包括完整性与安全性•DMLSQ

LDDL•需要创建的结构–Table–View–IndexCreate{table,view,index}<name><descriptionforthat>E.g.CreateTableDEPT(DEPT#Number,DNAMEChar(5),BudgetNumber(

7,2));SQLDDL–续•索引Createindex<name>on<tablename>(<indexattrnamelist>)E.g.CreateindexI1onEMP(E#);CreateindexI2onEMP(Ename);•唯一性索引E.g.Createunique

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

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

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

同名的部门名字selectdistinctdnamefromDEPTSelect子句-续•select短语中可以包含数学表达式.•例如:selectS#,Sname,Status2fromS.Where子

句•where短语由给出谓词，其谓词由出现在from短语中的关系的属性组成.•查找所有居住在London并且状态大于20的供应商的供应商号selectS#fromSwherecity=‘London’ANDstatus>20•比较操作结果可以用

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

30From子句•from短语列出的关系在执行时要被扫描.•查找employee×department的结果selectfromEMP,DEPTwhereemp.D#=dept.D#重命名操作•SQL使用别名（aliasname）对关系和属性重命名:old-namenew-name•查找所

有供应商的名字、商号和状态;将S#重命名为number、将sname重命名为nameselectsnamename,s#number,statusfromS元组变量•from短语使用别名定义元组变量.•查找所有已供应零件的供应商名字和零件号.selec

tsx.sname,spx.P#fromSsx,SPSpxwheresx.S#=spx.s#串操作•SQL含有串匹配操作.末拌有两个特殊的符号描述:–%.代表任意长的子串.–_.代表任意的单字符.•Findthenamesofallsuppliers

whosecitynameincludesthesubstring“Main”.selectsnamefromswherecitylike‘%Main%’串操作-续•SQL包括其他串操作例如–concatenation(using“||”)–convertingfromuppertolowe

rcase(andviceversa)–findingstringlength,extractingsubstrings,etc.排序•ListinalphabeticorderthenamesofallsupplierslocatinginLondoncitysel

ectdistinctsnamefromSwherecity=„London’orderbysname•desc表示降序，asc表示升序；缺省时升序–E.g.orderbysnamedesc集合操作•union,intersect,和except•集合操作自动消

重复集合操作-续•Findallcitieswherehaveasupplier,apart,orboth:(selectcityfromS)union(selectcityfromP)•Findallcitieswherehav

ebothasupplierandapart.(selectcityfromS)intersect(selectcityfromP)•FindallcitieswherehaveasupplierbutnoP.(selectcityfromS)exc

ept(selectcityfromP)聚集函数•avg•min•max•sum•count聚集函数-续•FindtheaverageQTYthatissuppliedbysuppliers1.selec

tavg(QTY)fromSPwheres#=„s1‟•Findthenumberoftuplesinthesupplierrelation.selectcount(*)fromS•Findthenum

berofsupplierswhosupplypart.selectcount(distincts#)fromSP聚集函数-续•Findthenumberofpartforeachsupplier.selectsname,coun

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

than600.selects#,avg(QTY)fromSPgroupbys#havingavg(QTY)>600聚集函数-续•Note:having短语和where短语的不同处selectd#,av

g(SAL)fromEMPwhereage<40groupbyd#havingavg(SAL)>600空值•元组的某些属性有可能取空值,记为null•null表示一个未知的值或者表示一个不存在的值.•任何涉及null的算术运算的结果是null–E.g.5+nullreturnsnull•聚集

函数计算中将忽略空值空值-续•isnull谓词用于判断空值.–E.g.FindallEmployeenumberwhichappearintheEMPrelationwithnullvaluesford#.selectE#fromEMPwhered#isnull•任何与n

ull的比较运算结果是unknown–E.g.5<nullornull<>nullornull=null空值-续•Totalallpartquantityselectsum(QTY)fromSP–上述语句忽略空值QTY–如果没有非空的QTY，结果是null•除了count(*)，所有聚集函数计

算都忽略nullvalues.嵌套子查询•SQLprovidesamechanismforthenestingofsubqueries.•Asubqueryisaselect-from-whereexpres

sionthatisnestedwithinanotherquery.•Acommonuseofsubqueriesistoperformtestsforsetmembership,setcomparisons,ands

etcardinality.举例•Findallemployeeswhohaveworkedinsalesdepartment.selectdistinctEnamefromEMPwhered#in(selectd#fromDEPTwhereDname=„sale‟)集合比较•

Findallemployeeswhosesalarygreaterthansomemanager‟ssalary.selectEnamefromEMPwheresal>some(selectsalfromEMPwhereE#in(selectmgrfromDEPT))集合比较-

续•Findthenamesofallemployeeswhosesalarygreaterthanallmanager‟ssalary.selectEnamefromEMPwheresal>all(selectsalfromE

MPwhereE#in(selectmgrfromDEPT))集合比较-续•Definitionofsetcomparison•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<sometupleintherelat

ion)(5<some)=true(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

Ø•notexistsrr=Ø•NotethatX–Y=ØXY举例•FindallsupplierswhohavesuppliedallpartslocatedinLondon.selectdistinctSx.sname

fromSSxwherenotexists((selectpnamefromPwherecity=„London‟)except(selectPx.pnamefromSPTx,PPxwhereTx.p#=Px.p#andSx.S#=Tx.s#))•Note:C

annotwritethisqueryusing=allanditsvariants唯一元组测试•Theuniqueconstructtestswhetherasubqueryhasanyduplicatetuplesinitsresult.•F

indallsupplierswhoareatmostonedepartmentmanager.selectT.EnamefromEMPTwhereunique(selectR.DnamefromDEPTRwhereT.e#=R.MGR)•Findalldepartmentsw

hichhaveatleasttwoemployeeswhosesalarygraterthan$5000.selectdistinctT.DnamefromDEPTTwherenotunique(sele

ctR.EnamefromEMPRwhereT.d#=R.d#andR.SAL>=5000)删除•DeleteallsupplierswhoareinLondondeletefromSwherecity=‘London’•Deleteal

lsupplierswhosupplypartp2.deletefromSwheres#in(selects#fromSPwherep#=‘p2’)Note:Herehassomeproblemwithconstraints

thatwillexplainedinChapter8•Deletetherecordofallemployeeswithsalarybelowtheaverage.deletefromEMPwhereSAL<(selectavg(SAL)fromEMP)–Problem:

aswedeletetuplesfromEMP,theaveragesalarychanges–SolutionusedinSQL:1.First,computeavgsalaryandfindalltuplestodelete2.Next,deletealltuplesfoundabove(

withoutrecomputingavgorretestingthetuples)插入•AddanewtupletoSinsertintoSvalues(„s6‟,„wangping‟,20,„shanghai)orequivalentlyinsertintoS(status,cit

y,s#,sname)values(20,„shanghai‟,„s6‟,„wangping‟,)•AddanewtupletoSwithcitysettonullinsertintoSvalues(

„s7‟,„Lihong‟,30,null)插入-续•ProvideasagiftforallloancustomersofthePerryridgebranch,a$200savingsaccount.insertintoaccountselectl

oan-number,branch-name,200fromloanwherebranch-name=„Perryridge‟•Theselectfromwherestatementisfullyevaluatedbeforeanyofitsresults

areinsertedintotherelation(otherwisequerieslikeinsertintotable1select*fromtable1wouldcauseproblems)更新•Increaseallemployeeswithsalaryover$4

,000by6%,allotheremployeesreceive5%.–Writetwoupdatestatements:updateEMPsetSAL=SAL1.06whereSAL>4000updateEMPsetSAL=SAL1.05

whereSAL4000–Theorderisimportant更多举例•Findallemployeeswhohavethelowestsalaryineachdepartment.SelectEname,d

#,SALFromEMPWhereSALin(Selectmin(SAL)FromEMPGroupByd#)Note:Abovestatementhaserror,thecorrectis:SelectEname,d#,SALFromEMPWhere(d#,SAL)in(Selectd

#,min(SAL)FromEMPGroupByd#)更多举例-续•FindallpartnumberanditstotalquantitySelectp#,sum(QTY)totqtyFromSPGroupByp#;orequivalentlySelectp#,

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

sPascal,PL/I,Fortran,C,andCobol.•AlanguagetowhichSQLqueriesareembeddedisreferredtoasahostlanguage,andtheSQLstructurespermittedinthehostlang

uagecompriseembeddedSQL.嵌入SQL-续•ThebasicformoftheselanguagesfollowsthatoftheSystemRembeddingofSQLintoPL/I.•EXE

CSQLstatementisusedtoidentifyembeddedSQLrequesttothepreprocessorEXECSQL<embeddedSQLstatement>Note:thisvariesbylanguage

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

edismachedproblembetweenhostlanguagewithsub-language,usingmiddlerelationtosolvethisquestion.Note:“:ename”calledhostvariablewhichneeddeclare

dbyspecialstatement.嵌入SQL-续EXECSQLBEGINDECLARESECTION;CharSQLSTATE[6];CharP#[6];intWeight;EXECSQLENDDECLARESECTION;P#=‘P2;EXECSQLSelectP.weightINT

O:weightFROMPWHEREP.P#=:P#;IfSQLSTATE=‘00000’Then….Else…..;嵌入SQL-续•ThestatementforSQLSTATE–EXECSQLWHENEVER<con

dition><action>•Conditon–Notfoundnodatawasfound‘02000’–Sqlerroranerroroccurred举例•SpecifythequeryinSQLanddeclareacursorforitEXECSQLdecla

reccursorforselectsname,cityfromS,SPwhereS.s#=SP.s#andSP.QTY>:amountFromwithinahostlanguage,findthenamesandcitiesofsupplierssupplymorethanth

evariableamountquantitypart.嵌入SQL-续•TheopenstatementcausesthequerytobeevaluatedEXECSQLopencThefetchstatement

causesthevaluesofonetupleinthequeryresulttobeplacedonhostlanguagevariables.EXECSQLfetchcinto:cn,:ccRepeatedcallstofetchgetsuccessi

vetuplesinthequeryresult嵌入SQL-续•AvariablecalledSQLSTATEintheSQLcommunicationarea(SQLCA)getssetto„02000‟toindicatenomoredataisavailable•Theclose

statementcausesthedatabasesystemtodeletethetemporaryrelationthatholdstheresultofthequery.EXECSQLclos

ecNote:abovedetailsvarywithlanguage.E.g.theJavaembeddingdefinesJavaiteratorstostepthroughresulttuples.游

标更新Canupdatetuplesfetchedbycursorbydeclaringthatthecursorisforupdatedeclareccursorforselect*fromEMPwherecity=„Parise‟forupdateTo

updatetupleatthecurrentlocationofcursorupdateEMPsetSAL=SAL+100wherecurrentofc动态SQL•Allowsprogramstoconstr

uctandsubmitSQLqueriesatruntime.•ThedynamicSQLprogramcontainsa?,whichisaplaceholderforavaluethatisprovidedwhentheSQL

programisexecuted.动态SQL-续•ExampleoftheuseofdynamicSQLfromwithinaCprogram.char*sqlprog=“updateEMPsetSAL=SAL*1.05wh

ered#=?”EXECSQLpreparedynprogfrom:sqlprog;characcount[10]=“A-101”;EXECSQLexecutedynprogusing:account;ODBC•OpenDataBaseConnectivity(OD

BC)standard–standardforapplicationprogramtocommunicatewithadatabaseserver.–applicationprograminterface(API)to•openaconnectionwithadatabase,

•sendqueriesandupdates,•getbackresults.•ApplicationssuchasGUI,spreadsheets,etc.canuseODBCODBC-续•EachdatabasesystemsupportingODBCprovidesa"driver"li

brarythatmustbelinkedwiththeclientprogram.•WhenclientprogrammakesanODBCAPIcall,thecodeinthelibrarycommunica

teswiththeservertocarryouttherequestedaction,andfetchresults.•ODBCprogramfirstallocatesanSQLenvironment,thenadatabaseconnectio

nhandle.ODBC-续•OpensdatabaseconnectionusingSQLConnect().•ParametersforSQLConnect:–connectionhandle,–theservertowh

ichtoconnect–theuseridentifier,–password•Mustalsospecifytypesofarguments:–SQL_NTSdenotespreviousargume

ntisanull-terminatedstring.ODBC编程•intODBCexample(){RETCODEerror;HENVenv;/*environment*/HDBCconn;/*databaseconnection*/SQLAllocEnv(&e

nv);SQLAllocConnect(env,&conn);SQLConnect(conn,"aura.bell-labs.com",SQL_NTS,"avi",SQL_NTS,"avipasswd",SQL_NTS);{….Doactua

lwork…}SQLDisconnect(conn);SQLFreeConnect(conn);SQLFreeEnv(env);}ExerciseWritethefollowingqueries,basedonthefollowingdat

abaseexampleMovie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,strName)MovieStar(name,address,gend

er,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)Classes(class,type,coun

try,numGuns,bore,displacement)Ships(name,class,launched)Battles(name,date)Outcomes(ship,battle,result)InSQL.1.FindSandraBullock

’sbirthdate2.Findallexecutivesworthatleast$10,000,0003.FindallthestarswhoeitheraremaleorliveinMalibu4.

WhichstarsappearedinmoviesproducedbyMGMin1995?5.WhoisthepresidentofMGMstudio?6.Findthecountrieswhoseshipshadthelargestnumberofguns.7.Findthen

amesoftheshipwith16-inchbore.8.Findtheaveragenumberofgunsofbattleshipclasses.