【文档说明】分布式数据库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•需要创建的结构–Tab

le–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(E

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

减少I/O.E.g.CreateclusterindexCI1onEMP(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.Wher

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

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

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

用别名（aliasname）对关系和属性重命名:old-namenew-name•查找所有供应商的名字、商号和状态;将S#重命名为number、将sname重命名为nameselectsnamename,s#number,statusfromS元组变量•from短语

使用别名定义元组变量.•查找所有已供应零件的供应商名字和零件号.selectsx.sname,spx.P#fromSsx,SPSpxwheresx.S#=spx.s#串操作•SQL含有串匹配操作.末拌有两个特殊的符号描述:–%.代表任意长的子串.–_.代表任意的单字符.•Findt

henamesofallsupplierswhosecitynameincludesthesubstring“Main”.selectsnamefromswherecitylike‘%Main%’串操作-续•SQL包括其他串操作例如–

concatenation(using“||”)–convertingfromuppertolowercase(andviceversa)–findingstringlength,extractingsubstrings,etc.排序•Listinalphabetic

orderthenamesofallsupplierslocatinginLondoncityselectdistinctsnamefromSwherecity=„London’orderbysname•desc表示降序，asc表示升序；缺省时升序–

E.g.orderbysnamedesc集合操作•union,intersect,和except•集合操作自动消重复集合操作-续•Findallcitieswherehaveasupplier,apart,orboth:(selectcityfrom

S)union(selectcityfromP)•Findallcitieswherehavebothasupplierandapart.(selectcityfromS)intersect(selectcityfromP)•Findallcitieswherehaveasupplierbu

tnoP.(selectcityfromS)except(selectcityfromP)聚集函数•avg•min•max•sum•count聚集函数-续•FindtheaverageQTYthatissuppliedbysu

ppliers1.selectavg(QTY)fromSPwheres#=„s1‟•Findthenumberoftuplesinthesupplierrelation.selectcount(*)fromS•Findthenumberofsup

plierswhosupplypart.selectcount(distincts#)fromSP聚集函数-续•Findthenumberofpartforeachsupplier.selectsname,count(disti

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

vesuppliedpartmorethan600.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.FindallEmployeenumberwhichappe

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

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

mechanismforthenestingofsubqueries.•Asubqueryisaselect-from-whereexpressionthatisnestedwithinanotherquery.•Acommonuseofsubqueriesistope

rformtestsforsetmembership,setcomparisons,andsetcardinality.举例•Findallemployeeswhohaveworkedinsalesdepartment.selectdistin

ctEnamefromEMPwhered#in(selectd#fromDEPTwhereDname=„sale‟)集合比较•Findallemployeeswhosesalarygreaterthansomemanager‟ssalary.selectEn

amefromEMPwheresal>some(selectsalfromEMPwhereE#in(selectmgrfromDEPT))集合比较-续•Findthenamesofallemployeeswhosesalarygreaterthanallmanager‟ss

alary.selectEnamefromEMPwheresal>all(selectsalfromEMPwhereE#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<sometupleintherelation)(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Ø•not

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

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

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

tuplesinitsresult.•Findallsupplierswhoareatmostonedepartmentmanager.selectT.EnamefromEMPTwhereunique(selectR.DnamefromDEPTRwhereT.e#=R

.MGR)•Findalldepartmentswhichhaveatleasttwoemployeeswhosesalarygraterthan$5000.selectdistinctT.Dnamefr

omDEPTTwherenotunique(selectR.EnamefromEMPRwhereT.d#=R.d#andR.SAL>=5000)删除•DeleteallsupplierswhoareinLondondeletefromSwherecit

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

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

omEMP,theaveragesalarychanges–SolutionusedinSQL:1.First,computeavgsalaryandfindalltuplestodelete2.Next,del

etealltuplesfoundabove(withoutrecomputingavgorretestingthetuples)插入•AddanewtupletoSinsertintoSvalues

(„s6‟,„wangping‟,20,„shanghai)orequivalentlyinsertintoS(status,city,s#,sname)values(20,„shanghai‟,„s6‟,„

wangping‟,)•AddanewtupletoSwithcitysettonullinsertintoSvalues(„s7‟,„Lihong‟,30,null)插入-续•ProvideasagiftforallloancustomersofthePer

ryridgebranch,a$200savingsaccount.insertintoaccountselectloan-number,branch-name,200fromloanwherebranch-name=„Perryridge‟•Theselectfromwherestatement

isfullyevaluatedbeforeanyofitsresultsareinsertedintotherelation(otherwisequerieslikeinsertintotable1select*fromtable1wouldcauseproblems)更新•Increase

allemployeeswithsalaryover$4,000by6%,allotheremployeesreceive5%.–Writetwoupdatestatements:updateEMPsetSAL=SAL1.06whereSAL>4

000updateEMPsetSAL=SAL1.05whereSAL4000–Theorderisimportant更多举例•Findallemployeeswhohavethelowestsalaryineachdepartment.SelectEname,d#,SALFr

omEMPWhereSALin(Selectmin(SAL)FromEMPGroupByd#)Note:Abovestatementhaserror,thecorrectis:SelectEname,d#,SALFromEMP

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

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

varietyofprogramminglanguagessuchasPascal,PL/I,Fortran,C,andCobol.•AlanguagetowhichSQLqueriesareembeddedisreferredtoasaho

stlanguage,andtheSQLstructurespermittedinthehostlanguagecompriseembeddedSQL.嵌入SQL-续•Thebasicformoftheselang

uagesfollowsthatoftheSystemRembeddingofSQLintoPL/I.•EXECSQLstatementisusedtoidentifyembeddedSQLrequesttothepreprocessorEXECSQL<embedded

SQLstatement>Note:thisvariesbylanguage.E.g.theJavaembeddinguses#SQL{….};嵌入SQL-续•QuerysingletupleEXECSQLSelectEnameINTO:enameFromEMPWh

eree#=„e1‟•QuerysettuplesTherearedismachedproblembetweenhostlanguagewithsub-language,usingmiddlerelationtosolvethisquestion.Note:“:e

name”calledhostvariablewhichneeddeclaredbyspecialstatement.嵌入SQL-续EXECSQLBEGINDECLARESECTION;CharSQLSTATE[6];CharP#[6];intWeigh

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

tforSQLSTATE–EXECSQLWHENEVER<condition><action>•Conditon–Notfoundnodatawasfound‘02000’–Sqlerroranerroroccurr

ed举例•SpecifythequeryinSQLanddeclareacursorforitEXECSQLdeclareccursorforselectsname,cityfromS,SPwhereS.s#=SP.s#andSP.QTY

>:amountFromwithinahostlanguage,findthenamesandcitiesofsupplierssupplymorethanthevariableamountquantitypart.嵌入

SQL-续•TheopenstatementcausesthequerytobeevaluatedEXECSQLopencThefetchstatementcausesthevaluesofonetupleinthequeryresulttobeplacedonho

stlanguagevariables.EXECSQLfetchcinto:cn,:ccRepeatedcallstofetchgetsuccessivetuplesinthequeryresult嵌入SQL-续•Avariab

lecalledSQLSTATEintheSQLcommunicationarea(SQLCA)getssetto„02000‟toindicatenomoredataisavailable•Theclosestate

mentcausesthedatabasesystemtodeletethetemporaryrelationthatholdstheresultofthequery.EXECSQLclosecNote:abovedetailsvar

ywithlanguage.E.g.theJavaembeddingdefinesJavaiteratorstostepthroughresulttuples.游标更新Canupdatetuplesfetchedbycursorbydeclaringthatthecurso

risforupdatedeclareccursorforselect*fromEMPwherecity=„Parise‟forupdateToupdatetupleatthecurrentlocationofcursorupdateEMPsetSAL=SAL+100wherecurrentof

c动态SQL•AllowsprogramstoconstructandsubmitSQLqueriesatruntime.•ThedynamicSQLprogramcontainsa?,whichisaplaceholderforavaluethatisprovidedwhe

ntheSQLprogramisexecuted.动态SQL-续•ExampleoftheuseofdynamicSQLfromwithinaCprogram.char*sqlprog=“updateEMPsetSAL=SAL*1.05whered#=?”EXECSQLpr

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

dforapplicationprogramtocommunicatewithadatabaseserver.–applicationprograminterface(API)to•openaconnectionwithadatabase,•sendqueries

andupdates,•getbackresults.•ApplicationssuchasGUI,spreadsheets,etc.canuseODBCODBC-续•EachdatabasesystemsupportingODBCprovidesa"d

river"librarythatmustbelinkedwiththeclientprogram.•WhenclientprogrammakesanODBCAPIcall,thecodeinthelibrarycommunicateswiththeser

vertocarryouttherequestedaction,andfetchresults.•ODBCprogramfirstallocatesanSQLenvironment,thenadatabaseconnectionhandle.ODBC-续•Opensdatabasecon

nectionusingSQLConnect().•ParametersforSQLConnect:–connectionhandle,–theservertowhichtoconnect–theuseridentifier,–password•Must

alsospecifytypesofarguments:–SQL_NTSdenotespreviousargumentisanull-terminatedstring.ODBC编程•intODBCexample(){RETCODEerror;HENVenv;/*environment*/HDBCc

onn;/*databaseconnection*/SQLAllocEnv(&env);SQLAllocConnect(env,&conn);SQLConnect(conn,"aura.bell-labs.com",SQL_NTS,"avi",SQL_NTS,"avipasswd",SQ

L_NTS);{….Doactualwork…}SQLDisconnect(conn);SQLFreeConnect(conn);SQLFreeEnv(env);}ExerciseWritethefollowingqueries,basedonthefollowingdata

baseexampleMovie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,strName)MovieStar(name,address,gender,birthd

ate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)Classes(class,type,country,numGuns,bore,displa

cement)Ships(name,class,launched)Battles(name,date)Outcomes(ship,battle,result)InSQL.1.FindSandraBullock’sbirthdate2.F

indallexecutivesworthatleast$10,000,0003.FindallthestarswhoeitheraremaleorliveinMalibu4.Whichstarsappearedinmovie

sproducedbyMGMin1995?5.WhoisthepresidentofMGMstudio?6.Findthecountrieswhoseshipshadthelargestnumberofguns.7.Findthenamesofthesh

ipwith16-inchbore.8.Findtheaveragenumberofgunsofbattleshipclasses.