资源描述：
《sql-谓词与量词（sql- predicates and quantifiers）》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、sql-谓词与量词（Sql-predicatesandQuantifiers）===================================1,predicatePredicatesallowyoutoconstructconditionstohandleonlythoserowsthatsatisfytheseconditions.BasicpredicateinSelectrowdiscussion.ThissectiondiscussesIN,BETWEEN,LIKE,EXISTS,andquantitativepredic
2、ates.2,usetheINpredicateUsetheINpredicatetocompareavaluewithseveralothervalues.Forexample:SELECTNAMEFROMSTAFFWHEREDEPTIN(20,15)Thisexampleisequivalentto:SELECTNAMEFROMSTAFFWHEREDEPT=20ORDEPT=15Ifthequeryreturnsasetofvalues,youcanusetheINandNOTINoperators.Forexample,thefol
3、lowingqueryliststhenamesofemployeeswhoareresponsibleforprojectMA2100andOP2012:SELECTLASTNAMEFROMEMPLOYEEWHEREEMPNOIN(SELECTRESPEMPFROMPROJECTWHEREPROJNO='MA2100'ORPROJNO='OP2012')Calculationofasubquery,andtabulatetheresultsdirectlyintotheouterquery.Forexample,theuppersubq
4、ueryselectsemployeenumbers10and330,andcalculatestheouterqueryasiftheWHEREclauseisasfollows:WHEREEMPNOIN(10,330)Alistofvaluesreturnedbyasubquerycancontainzero,oneormorevalues.3,usetheBETWEENpredicateUsetheBETWEENpredicatetocompareavaluewiththevaluewithinarange.Thevaluesonb
5、othsidesoftherangeareincluded,andthetwoexpressionsusedforcomparisonintheBETWEENpredicateareconsidered.Thenextexamplelooksforthenamesofemployeeswhoearnbetween$10000and$20000:SELECTLASTNAMEFROMEMPLOYEEWHERESALARYBETWEEN10000AND20000Thisisequivalentto:SELECTLASTNAMEFROMEMPLO
6、YEEWHERESALARYor10000ANDSALARY20000Thenextexamplelooksforthenamesofemployeeswhoearnlessthan$10000ormorethan$20000:SELECTLASTNAMEFROMEMPLOYEEWHERESALARYNOTBETWEEN10000AND200004,usetheLIKEpredicateUsingLIKEpredicatestosearchforstringswithcertainpatterns.Thespecifiedmodebype
7、rcentandunderscore.Theunderscorecharacter(_)saidthatanysinglecharacter.Thepercent(%)saidastringofzeroormorecharacters.Anyothercharacterthatrepresentsitself.Thefollowingexampleselectsanemployeenamebeginningwiththeletter'S'withalengthof7letters:SELECTNAMEFROMSTAFFWHERENAMEL
8、IKE'S______'Thenextexampleselectstheemployeenamethatdoesnotstartwiththeletter'S':SELECTNAMEFROMS
