sql语句教程双语

Chapter5

SQL:DataManipulation

(SQL:数据操纵)5.1IntroductiontoSQL5.2WritingSQLCommands5.3DataManipulation5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology1.ObjectivesofSQLIdeally,databaselanguageshouldallowuserto:createthedatabaseandrelationstructures;perform

insertion,modification,deletionofdatafromrelations;performsimpleandcomplexqueries.Itmustperformthesetaskswithminimalusereffort,andmustbeeasytolearn(必须易学易用).Itmustbe

portable(可移植的):conformtosomerecognized(公认的)standardsothatitcanbeusedindifferentDBMSs.1.ObjectivesofSQLSQLisatransform-orientedlanguagewith2majorcomponents:ADDLfordefiningdatabasestructure.ADMLforretrievingandupdatingdata.UntilSQL3,SQLdidnotcontainflowofcontrolcommands(流程控制).

Thesemustbeimplementedusingaprogramminglanguage,orinteractivelybythedecisionsofuser.SQLisrelativelyeasytolearn:itisnon-procedural-youspecifywhatinformationyourequire,ratherthanhowtogetit;itisfree-format:case-insensible(不区分大小写);part-omissible,one-linedormultiple-lined.1.ObjectivesofSQLSQLincludesonly8

commandverbs:SQLFunctions

CommandVerbs

DataQuery

SELECTDataDefinitionCREATE，DROP，ALTERDataModificationINSERT，UPDATE，DELETEDataControlGRANT，REVOKE1.ObjectivesofSQLCanbeusedbyvarioususers,including:

DBAs,Managementpersonnel,Applicationdevelopers,othertypesofendusers.AnISOstandardnowexistsforSQL,makingitboththeformalanddefacto(事实上的)standardlanguageforrelationaldatabases.5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology2.HistoryofSQLIn1974,D.Chamberlin(IBM)definedlanguagecalled‘StructuredEnglishQueryLanguage’(SEQUEL).In1976,SEQUEL/2wasdefinedandrenamedtoSQL.IBMsubsequentlyproducedaprototypeDBMScalledSystemR,basedonSEQUEL/2(随后IBM开发了基于SEQUEL/2的原型DBMS:SystemR).RootsofSQLareinSQUARE(SpecifyingQueriesasRelationalExpressions,1975),whichpredatesSystemR(SQL的最初原型是比SystemR更早的SQUARE).Inlate1970s,ORACLEappearedandwasprobablythefirstcommercialRDBMSbasedonSQL(ORACLE可能是第一个基于SQL的商用RDBMS,于1970s晚期出现).2.HistoryofSQLIn1987,ANSI&ISOpublishedaninitialSQLstandard.In1989,ISOpublishedanaddendum(补遗)thatdefinedan‘IntegrityEnhancementFeature’.In1992,firstmajorrevision(修改)toISOstandardoccurred,referredtoasSQL2orSQL/92.In1999,SQL3wasreleasedwithsupportforobject-orienteddatamanagement.5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology3.ImportanceofSQLSQLhasbecomepartofapplicationarchitecturessuchasIBM’sSystemsApplicationArchitecture(已成为IBM系统应用架构的一部分).Itisstrategicchoiceofmanylargeandinfluentialorganizations(e.g.X/OPENconsortium(联盟)forUNIXstandards)(是许多颇具影响力的大组织的战略选择).SQLisFederalInformationProcessingStandard(FIPS)bywhichalldatabasessoldtoAmericanGovernmentmustbeabided.(是联帮信息处理标准:卖给美国政府的数据库都必须遵守)

SQLisusedinotherstandardsandeveninfluencesdevelopmentofotherstandards.Examplesinclude:(用于其他标准，甚至影响其他标准的开发，例如:)ISO’sInformationResourceDirectorySystem(IRDS)Standard(信息资源字典系统标准),RemoteDataAccess(RDA)Standard(远程数据存取标准).5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology4.TerminologySQLstandarddoesnotuseformaltermsofrelations,attributes,andtuples,insteadusingthetermsoftables,columns,androws(不用关系、属性、元组，用表、列、行).ThiscoursemostlyusestheISOterminology(本课程尽量采用国家标准术语).SQLdoesnotadherestrictlytotherelationalmodeldescribedinChapter3.Forexample:(SQL不严格遵守关系模型的规定，例如：)SQLallowstheresulttableofaquerytocontainduplicaterows(允许查询结果表含重复行),itimposesanorderingonthecolumns(规定列顺序),itallowstheusertoordertherowsofatable(允许对行进行排序).Chapter5

SQL:DataManipulation

(SQL:数据操纵)5.1IntroductiontoSQL5.2WritingSQLCommands5.3DataManipulation5.2WritingSQLCommandsSQLstatementconsistsofreservedwordsanduser-definedwords.(SQL命令含保留字和用户定义的字)ReservedwordsareafixedpartofSQLandmustbespeltexactlyasrequiredandcannotbesplitacrosslines.(保留字是SQL的固定部分，必须正确拼写且不允许跨行书写)

User-definedwordsaremadeupbyuser,andrepresentnamesofdatabaseobjectssuchasrelations,columns,views.(用户定义的字由用户给出，通常是数据库对象名，如关系名、列名、视图名等)MostcomponentsofanSQLstatementarecaseinsensitive,exceptforliteralcharacterdata(字符数据).(除字符数据外，SQL语句不区分大小写)5.2WritingSQLCommandsMorereadablewithindentation(缩进)

andlineation(分行):Eachclausebeginsonanewline.Startofaclauselinesupwith(对齐)startofotherclauses.Ifaclausehasseveralparts,theyshouldeachappearonaseparatelineandbeindentedunderstartoftheclause.UseextendedformofBNFnotation(采用扩展的BNF记法):Upper-caselettersrepresentreservedwords.Lower-caselettersrepresentuser-definedwords.|indicatesachoiceamongalternatives.Curlybracesindicatearequiredelement.Squarebracketsindicateanoptionalelement.…indicatesoptionalrepetition(0ormore).E.g.,{a|b}[,…]meanstherepetitionsofeitheraorbseparatedbycommas(e.g.:a,b,a,a,b).5.2WritingSQLCommandsLiterals(文字)areconstantsusedinSQLstatements.Allnon-numericliterals(字符串常量)mustbeenclosedinsinglequotes(e.g.'London').Allnumericliterals(数值常量)mustnotbeenclosedinquotes(e.g.650.00).Forexample:INSERT

SC(Sno,Cno,Grade)VALUES('95001','1',90)Ifanon-numericliteralcontainsquote,itmustbedoubledintheliteral.Forexample:INSERT

Viewing(clientNo,propertyNo,comment)VALUES('C001','P001','It''stoosmall')Chapter5

SQL:DataManipulation

(SQL:数据操纵)5.1IntroductiontoSQL5.2WritingSQLCommands5.3DataManipulation5.3DataManipulationThissectiondiscussesSQLDMLstatements,namely:SELECT:toquerydatainthedatabase;INSERT:toinsertdataintoatable;UPDATE:toupdatedatainatable;DELETE:todeletedatafromatable.WedevotemostofthissectiontoSELECTstatementanditsvariousformats.5.3DataManipulationWeillustrateSQLstatementsusingtheDreamHomecasestudy,whichconsistsofthefollowing6tables:BranchStaffPropertyForRentClientPrivateOwnerViewing(branchNo,street,city,postcode)(staffNo,fName,1Name,position,sex,DOB,salary,branchNo)(propertyNo,street,city,postcode,type,rooms,rent,ownerNo,staffNo,branchNo)(clientNo,fName,lName,telNo,prefType,maxRent)(ownerNo,fName,lName,address,telNo)(clientNo,propertyNo,viewDate,comment)WealsouseStudent-JoinIn-Courseexampledatabase,whichconsistsofthefollowing3tables:Student(Sno,Sname,Ssex,Sage,Sdept)Course(Cno,Cname,Cpno,Ccredit)SC(Sno,Cno,Grade)5.3DataManipulation1.SimpleQueries2.SortingResults(ORDERBYClause)3.UsingtheSQLAggregateFunctions4.GroupingResults(GROUPBYClause)5.Subqueries

6.ANYandALL7.Multi-TableQueries8.EXISTSandNOTEXISTS9.CombiningResultTables(UNION,INTERSECT,EXCEPT)10.DatabaseUpdates1.SimpleQueriesSELECTStatement,generalformat:SELECT[DISTINCT|ALL]{*|[colExp[AS

newName]][,...]}FROM

TableName[alias][,...][WHERE condition1][GROUPBY colList1][HAVINGcondition2][ORDERBY colList2]Π

(

)σ

()根据WHERE子句的条件表达式，从FROM子句的基本表或视图的笛卡儿积中，找出满足条件的元组，按SELECT子句的目标列表达式，进行投影，形成结果表。Fr×s×…X1,

X2,…X1X2…………manyclauses.含义：1.SimpleQueriesSELECTStatement,generalformat:SELECT[DISTINCT|ALL]{*|[colExp[AS

newName]][,...]}FROM

TableName[alias][,...][WHERE condition1][GROUPBY colList1][HAVINGcondition2][ORDERBY colList2]manyclauses.含义：如果有GROUPBY子句，则：最后将这条“记录”投影到目标列，产生结果表的一条记录。演示然后将(聚)集函数作用到组，得到一条“记录”，演示先将选择结果σF(r×

s×…)，按<colList1>的值分组(值相等的元组分为一组)，演示如果有ORDERBY，则结果表还要按<colList2>的值升序或降序排列。如果GROUPBY带HAVING，则只输出满足HAVING条件的组1.SimpleQueriesSELECTStatement,generalformat:SELECT[DISTINCT|ALL]{*|[colExp[AS

newName]][,...]}FROM

TableName[alias][,...][WHERE condition1][GROUPBY colList1][HAVINGcondition2][ORDERBY colList2]manyclauses.Orderoftheclausescannotbechanged.OnlySELECTandFROMaremandatory(必须的).1.SimpleQueriesExample5.1AllColumns,AllRows

SELECT

staffNo,fName,lName,address, position,sex,DOB,salary,branchNo

FROMStaff;Canuse*asanabbreviationfor‘allcolumns’:

SELECT*

FROMStaff;Result:Table5.1StaffstaffNofNamelNamepositionsexDOBsalarybranchNoListfulldetailsofall

staff.1.SimpleQueriesExample5.2SpecificColumns,AllRows

SELECT

staffNo,fName,lName,salaryFROMStaff;Result:Table5.2StaffstaffNofNamelNamepositionsexDOBsalarybranchNoProducealistofsalariesforallstaff,showingonlystaffnumber,firstandlastnames,andsalary.1.SimpleQueriesExample5.3UseofDISTINCT

SELECT

propertyNoFROMViewing;Listthepropertynumbersofallpropertiesthathavebeenviewed.ViewingctientNopropertyNoviewDatecommentDuplicatelinesUseDISTINCTtoeliminateduplicates:

SELECT

DISTINCT

propertyNoFROMViewing;1.SimpleQueriesExample5.3UseofDISTINCT注意：DISTINCT必须紧跟SELECT关键字书写。例如以下SELECT语句是错误的：

SELECT

Ssex,DISTINCT

Sname

FROMStudent正确的语句为：

SELECT

DISTINCT

Ssex,Sname

FROMStudent1.SimpleQueriesExample5.4CalculatedFieldsProducealistofmonthlysalariesforallstaff,showingstaffnumber,firstandlastnames,andsalarydetails.StaffstaffNofNamelNamepositionsexDOBsalarybranchNo

SELECT

staffNo,fName,lName,salary/12FROMStaff;CalculatedFieldhasnopropername1.SimpleQueriesExample5.4CalculatedFieldsProducealistofmonthlysalariesforallstaff,showingstaffnumber,firstandlastnames,andsalarydetails.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoTonamecolumn,useASclause:

SELECT

staffNo,fName,lName, salary/12AS

monthlySalary

FROMStaff;说明：1)AS可以省略；2)当别名中含空格时用单引号括起；例如：

SELECT

Sname

AS

NAME,'BirthIn'BIRTH,2007-SageAS'YearofBirth',

LOWER(Sdept)DEPARTMENT

FROMStudent1.SimpleQueriesRowselection(WHEREclause)Therearefivebasic

search

conditions(orpredicates)inWHEREclause:ConditionsOperatorsandMeaningComparison=,>,<,>=,<=,!=,<>,!>,!<CompareoneexpressiontoanotherexpressionRangeBETWEENAND,NOT

BETWEENANDTestifanexpressionfallswithinaspecifiedrange.SetmembershipIN,NOT

INTestifanexpressionequalsoneofasetofvalues.PatternmatchLIKE,NOT

LIKETestifastringmatchesaspecifiedpattern.NullISNULL,IS

NOT

NULLTestifacolumnhasanullvalue.1.SimpleQueriesExample5.5Comparisonsearchconditions:<e1>q<e2>ListstaffNo,fName,lName,positionandsalaryofeachstaffwithasalarygreaterthan10,000.StaffstaffNofNamelNamepositionsexDOBsalarybranchNo

SELECT

staffNo,fName,lName,position,salaryFROMStaffWHERE

salary>10000;1.SimpleQueriesExample5.6Compoundcomparison(UsingAND,OR,NOT)ListallinformationofallbranchofficesinLondonorGlasgow.BranchbranchNostreetcitypostcode

SELECT*FROMBranchWHERE

city='London'ORcity='Glasgow';1.SimpleQueriesRowselection(WHEREclause)Therearefivebasicsearchconditions(orpredicates)inWHEREclause:ConditionsOperatorsandMeaningComparison=,>,<,>=,<=,!=,<>,!>,!<CompareoneexpressiontoanotherexpressionRange<e1>[NOT]

BETWEEN<e2>AND<e3>Testifanexpressionfallswithinaspecifiedrange.SetmembershipIN,NOT

INTestifanexpressionequalsoneofasetofvalues.PatternmatchLIKE,NOT

LIKETestifastringmatchesaspecifiedpattern.NullISNULL,IS

NOT

NULLTestifacolumnhasanullvalue.1.SimpleQueriesExample5.7RangesearchconditionListstaffNo,fName,lName,positionandsalaryofeachstaffwithasalarybetween20,000and30,000.StaffstaffNofNamelNamepositionsexDOBsalarybranchNo

SELECT

staffNo,fName,lName,position,salaryFROMStaffWHEREsalaryBETWEEN20000AND30000;BETWEENtest

includestheendpointsofrange.1.SimpleQueriesExample5.7RangesearchconditionListstaffNo,fName,lName,positionandsalaryofeachstaffwithasalarybetween20,000and30,000.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoAlsoanegatedversionNOT

BETWEEN…AND….[NOT]BETWEEN…AND…doesnotaddmuchtoSQL’sexpressivepower.Wecouldalsowrite:

SELECT

staffNo,fName,lName,position,salaryFROMStaffWHERE

salary>=20000ANDsalary<=30000;1.SimpleQueriesRowselection(WHEREclause)Therearefivebasicsearchconditions(orpredicates)inWHEREclause:ConditionsOperatorsandMeaningComparison=,>,<,>=,<=,!=,<>,!>,!<CompareoneexpressiontoanotherexpressionRangeBETWEENAND,NOT

BETWEENANDTestifanexpressionfallswithinaspecifiedrange.Setmembership<exp>[NOT]

IN<set>Testifanexpressionequalsoneofasetofvalues.PatternmatchLIKE,NOT

LIKETestifastringmatchesaspecifiedpattern.NullISNULL,IS

NOT

NULLTestifacolumnhasanullvalue.1.SimpleQueriesExample5.8SetmembershipListstaffNo,fName,lNameandpositionofeachmanagerandsupervisor.StaffstaffNofNamelNamepositionsexDOBsalarybranchNo

SELECT

staffNo,fName,lName,positionFROMStaffWHERE

position

IN('Manager','Supervisor');1.SimpleQueriesExample5.8SetmembershipListstaffNo,fName,lNameandpositionofeachmanagerandsupervisor.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoThereisanegatedversion:NOTIN.INdoesnotaddmuchtoSQL’sexpressivepower.Wecouldalsowrite:SELECT

staffNo,fName,lName,positionFROMStaffWHERE

position='Manager'ORposition='Supervisor';

INismoreefficientwhensetcontainsmanyvalues.1.SimpleQueriesRowselection(WHEREclause)Therearefivebasicsearchconditions(orpredicates)inWHEREclause:ConditionsOperatorsandMeaningComparison=,>,<,>=,<=,!=,<>,!>,!<CompareoneexpressiontoanotherexpressionRangeBETWEENAND,NOT

BETWEENANDTestifanexpressionfallswithinaspecifiedrange.SetmembershipIN,NOT

INTestifanexpressionequalsoneofasetofvalues.Patternmatch<char-exp>[NOT]

LIKE<pattern>Testifastringmatchesaspecifiedpattern.NullISNULL,IS

NOT

NULLTestifacolumnhasanullvalue.1.SimpleQueriesExample5.9PatternmatchingFindallownerswiththestring‘Glasgow’intheiraddress.PrivateOwnerownerNoownerNofNamelNameaddresstelNo

SELECT

clientNo,fName,lName,address,telNoFROM

PrivateOwnerWHERE

address

LIKE

'%Glasgow%';1.SimpleQueriesExample5.9PatternmatchingFindallownerswiththestring‘Glasgow’intheiraddress.PrivateOwnerownerNoownerNofNamelNameaddresstelNoSQLhastwospecialpatternsymbols:%:representsasequenceofzeroormorecharacters;_(underscore):representsanysinglecharacter.LIKE‘%Glasgow%’meansasequenceofcharactersofanylengthcontaining‘Glasgow’.Ifweneedstorepresenttheoriginalmeaningof‘%’or‘_’inthepattern,wemustuseanescapecharacter.Forexample,tocheckwhether<exp>contains'15%':

<exp>

LIKE'%15#%%'ESCAPE'#'1.SimpleQueriesRowselection(WHEREclause)Therearefivebasicsearchconditions(orpredicates)inWHEREclause:ConditionsOperatorsandMeaningComparison=,>,<,>=,<=,!=,<>,!>,!<CompareoneexpressiontoanotherexpressionRangeBETWEENAND,NOT

BETWEENANDTestifanexpressionfallswithinaspecifiedrange.SetmembershipIN,NOT

INTestifanexpressionequalsoneofasetofvalues.PatternmatchLIKE,NOT

LIKE:Testifastringmatchesaspecifiedpattern.Null<exp>IS[NOT]

NULLTestifacolumnhasanullvalue.1.SimpleQueriesExample5.9NULLsearchconditionListdetailsofallviewingsonpropertyPG4whereacommenthasnotbeensupplied.Havetotestwhethercommentisnull:

SELECT

clientNo,viewDateFROMViewingWHERE

propertyNo='PG4'AND

comment

ISNULL;Negatedversion:ISNOTNULL:cantestnon-nullvalues.ViewingctientNopropertyNoviewDatecomment5.3DataManipulation1.SimpleQueries2.SortingResults(ORDERBYClause)3.UsingtheSQLAggregateFunctions4.GroupingResults(GROUPBYClause)5.Subqueries

6.ANYandALL7.Multi-TableQueries8.EXISTSandNOTEXISTS9.CombiningResultTables(UNION,INTERSECT,EXCEPT)10.DatabaseUpdates2.SortingResults(ORDERBYClause)QueryresultscanbesortedaccordingtovaluesofsomecolumnsspecifiedusinganORDERBYclause:ORDERBY{colname|colnum}[ASC|DESC][,…]colname:columnname,includesaliasinSELECTClause;colnum:

columnnumberinSELECTClause(from1on);ASC:sorted

ascendingly

(thedefaultsetting)DESC:sorted

descendingly.2.SortingResults(ORDERBYClause)Example5.11SingleColumnOrderingListstaffNo,

fName,lName,salaryforeachstaff,arrangedindescendingorderofsalary.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoSELECT

staffNo,fName,lName,salaryFROMStaffORDERBYsalaryDESC;

2.SortingResults(ORDERBYClause)Example5.12MultipleColumnOrdering(a)ListpropertyNo,type,rooms,rentofeachpropertyinorderofpropertytype.SELECT

propertyNo,type,rooms,rentFROM

PropertyForRentORDERBYtype;

PropertyForRentpropertyNostreetcitypostcodetyperoomsrentownerNostaffNobranchNoForatype,therearemanyproperties.ThisgivesExample5.12(b).Sometimeswewanttosortpropertiesofatypeaccordingtotheirrents:2.SortingResults(ORDERBYClause)Example5.12MultipleColumnOrdering(b)ListpropertyNo,type,rooms,rentofeachpropertyinorderofpropertytypeandrent.SELECT

propertyNo,type,rooms,rentFROM

PropertyForRentORDERBYtype,rentDESC;

PropertyForRentpropertyNostreetcitypostcodetyperoomsrentownerNostaffNobranchNo5.3DataManipulation1.SimpleQueries2.SortingResults(ORDERBYClause)3.UsingtheSQLAggregateFunctions4.GroupingResults(GROUPBYClause)5.Subqueries

6.ANYandALL7.Multi-TableQueries8.EXISTSandNOTEXISTS9.CombiningResultTables(UNION,INTERSECT,EXCEPT)10.DatabaseUpdates3.UsingtheSQLAggregateFunctionsInSQL,fivestandardaggregatefunctionscanbeusedtoperformstatisticalquery.Theyare:COUNT([DISTINCT]*),COUNT([DISTINCT]colname)returnsnumberofrowsthatsatisfyquerycondition(*);

returnsnumberofvaluesinspecifiedcolumn.(e.g.,

COUNT(1,3,5)=3)SUM([DISTINCT]colname

)returnssumofvaluesinspecifiednumericalcolumn.(e.g.,

SUM(1,3,5)=9).

AVG([DISTINCT]colname

)returnsaverageofvaluesinspecifiednumericalcolumn.(e.g.,

AVG(1,3,5)=4.5).MAX([DISTINCT]colname

)returnsmaximumofvaluesinspecifiedcolumn.(e.g.,

MAX(1,3,5)=5)MIN([DISTINCT]colname

)returnsminimumofvaluesinspecifiedcolumn.(e.g.,

MIN(1,3,5)=1)3.UsingtheSQLAggregateFunctionsIfDISTINCTisused,

thenduplicatesareeliminated.eliminatesnullsandoperatesonlyonnon-nullvalues.canbeusedonlyinSELECTlistandHAVINGclause.IfSELECTlistincludesanaggregatefunctionandthereisnoGROUPBYclause,SELECTlistcannotreferenceacolumnoutofanaggregatefunction.Forexample,thefollowingisillegal:

SELECT

staffNo,COUNT(salary)FROMStaff;3.UsingtheSQLAggregateFunctionsExample5.13UseofCOUNT(*)Howmanypropertiescostmorethan£350permonthtorent?SELECTCOUNT(*)AScountFROM

PropertyForRentWHERErent>350;

PropertyForRentpropertyNostreetcitypostcodetyperoomsrentownerNostaffNobranchNo3.UsingtheSQLAggregateFunctionsExample5.14UseofCOUNT(DISTINCT)HowmanydifferentpropertiesviewedinMay2001?SELECTCOUNT(DISTINCT

propertyNo)AScountFROMViewingWHERE

viewDate

BETWEEN'1-May-2001'AND'31-May-2001';ViewingctientNopropertyNoviewDatecomment3.UsingtheSQLAggregateFunctionsExample5.15UseofCOUNTandSUMFindnumberofManagersandsumoftheirsalaries.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoSELECT

COUNT(staffNo)AScount,

SUM(salary)ASsumFROMStaffWHEREposition='Manager';

3.UsingtheSQLAggregateFunctionsExample5.16UseofMIN,MAX,AVGFindminimum,maximum,andaverage

staffsalary.StaffstaffNofNamelNamepositionsexDOBsalarybranchNoSELECT

MIN(salary)ASmin,

MAX(salary)ASmax,

AVG(salary)AS

avgFROMStaff;

5.3DataManipulation1.SimpleQueries2.SortingResults(ORDERBYClause)3.UsingtheSQLAggregateFunctions4.GroupingResults(GROUPBYClause)5.Subqueries

6.ANYandALL7.Multi-TableQueries8.EXISTSandNOTEXISTS9.CombiningResultTables(UNION,INTERSECT,EXCEPT)10.DatabaseUpdates4.GroupingResults(GROUPBYClause)GROUPBYisusedtodividequeryresultsinto

groups,accordingtovaluesofcolumnsinGROUPBYclause.GROUPBY班级

twogroupsGROUPBY性别

twogroupsGROUPBY班级,性别

fourgroupsGroupedQuery:aquerythatincludesGROUPBYclause.GroupingColumn:acolumnthatappearsinGROUPBYclause.4.GroupingResults(GROUPBYClause)SELECTandGROUPBYarecloselyintegrated:eachiteminSELECTlistmustbesingle-valuedpergroup,i.e.,onlycontaincolumnsthatappearineitherGROUPBYclauseoraggregatefunctions.SELECTclausemayonlycontain:columnnames(aggregate)functionsconstantsexpressioninvolvingcombinationsoftheabove.IfWHEREisusedwithGROUPBY,WHEREisappliedfirst,thengroupsareformedfromrowsthatsatisfyquerycondition.4.GroupingResults(GROUPBYClause)Example5.17UseofGROUPBYFindnumberofstaffineachbranchandtheirtotalsalaries.FROMStaffGROUPBY

branchNoStaffstaffNofNamelNamepositionsexDOBsalarybranchNoSL21JohnWhiteManagerM1-Oct-4530000B005SG37AnnBeechAssistantF10-Nov-6012000B003SG14DavidFordSupervisorM24-Mar-5818000B003SA9MaryHoweAssistantF19-Feb-709000B007SG5SusanBrandManagerF3-Jun-4024000B003SL41JulieLeeAssistantF13-Jun-659000B005SELECT

branchNo,

COUNT(staffNo)AScount,

SUM(salary)ASsumTable5.17ResultforExample5.17branchNocountsumB00719000.00B003354000.00B005239000.00ORDERBY

branchNo;4.GroupingResults(GROUPBYClause)RestrictingGroupings(HAVINGclause)HAVINGclauseisdesignedtorestrictgroupsthatappearinfinalresulttable.SimilartoWHERE,butWHEREfiltersrows,whereasHAVINGfiltersgroups.ColumnsinHAVINGclausemustalsoappearintheGROUPBYlistorbecontainedwithinanaggregatefunction.4.GroupingResults(GROUPBYClause)Example5.18UseofHAVINGForeachbranchwithmorethan1memberofstaff,findnumberofstaffineachbranchandsumoftheirsalaries.FROMStaffGROUPBY

branchNoStaffstaffNofNamelNamepositionsexDOBsalarybranchNoSL21JohnWhiteManagerM1-Oct-4530000B005SG37AnnBeechAssistantF10-Nov-6012000B003SG14DavidFordSupervisorM24-Mar-5818000B003SA9MaryHoweAssistantF19-Feb-709000B007SG5SusanBrandManagerF3-Jun-4024000B003SL41JulieLeeAssistantF13-Jun-659000B005SELECT

branchNo,COUNT(staffNo)AScount,

SUM(salary)ASsumTable5.18ResultforExample5.18branchNocountsumB003354000.00B005239000.00ORDERBY

branchNo;HAVING

COUNT(staffNo)>1

×

5.3DataManipulation1.SimpleQueries2.SortingResults(ORDERBYClause)3.UsingtheSQLAggregateFunctions4.GroupingResults(GROUPBYClause)5.Subqueries

6.ANYandALL7.Multi-TableQueries8.EXISTSandNOTEXISTS9.CombiningResultTables(UNION,INTERSECT,EXCEPT)10.DatabaseUpdates5.Subqueries

SomeSQLstatementscanhaveaSELECTembeddedwithinthem.Forexample:SELECT

Sname

FROMStudentWHEREOuterselectSubselect(innerSelect)

Sno

IN

(

SELECT

Sno

FROMSC

WHERE

Cno='2'

)AsubselectcanbeusedinWHERE,HAVING,SELECTandFROMclausesofanouterSELECT,whereitiscalledasubqueryornestedquery.5.Subqueries

Therearethreetypesofsubquery:Ascalarsubqueryreturnsasinglecolumnandasinglerow;thatis,asinglevalue(标量型子查询).Ascalarsubquerycanbeusedwhereverasinglevalueisneeded(例如用于算术表达式中).Arowsubqueryreturnsmultiplecolumns,butagainonlyasinglerow(元组型子查询).Arowsubquerycanbeusedwhereverarowvalueconstructorisneeded,typically