Chapter5

SQL:DataManipulation

(SQL:數(shù)據(jù)操縱)5.1IntroductiontoSQL5.2WritingSQLCommands5.3DataManipulation5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology1.ObjectivesofSQLIdeally,databaselanguageshouldallowuserto:createthedatabaseandrelationstructures;perform

insertion,modification,deletionofdatafromrelations;performsimpleandcomplexqueries.Itmustperformthesetaskswithminimalusereffort,andmustbeeasytolearn(必須易學(xué)易用).Itmustbe

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

Thesemustbeimplementedusingaprogramminglanguage,orinteractivelybythedecisionsofuser.SQLisrelativelyeasytolearn:itisnon-procedural-youspecifywhatinformationyourequire,ratherthanhowtogetit;itisfree-format:case-insensible(不區(qū)分大小寫);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開發(fā)了基于SEQUEL/2的原型DBMS:SystemR).RootsofSQLareinSQUARE(SpecifyingQueriesasRelationalExpressions,1975),whichpredatesSystemR(SQL的最初原型是比SystemR更早的SQUARE).Inlate1970s,ORACLEappearedandwasprobablythefirstcommercialRDBMSbasedonSQL(ORACLE可能是第一個基于SQL的商用RDBMS,于1970s晚期出現(xiàn)).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系統(tǒng)應(yīng)用架構(gòu)的一部分).Itisstrategicchoiceofmanylargeandinfluentialorganizations(e.g.X/OPENconsortium(聯(lián)盟)forUNIXstandards)(是許多頗具影響力的大組織的戰(zhàn)略選擇).SQLisFederalInformationProcessingStandard(FIPS)bywhichalldatabasessoldtoAmericanGovernmentmustbeabided.(是聯(lián)幫信息處理標(biāo)準(zhǔn):賣給美國政府的數(shù)據(jù)庫都必須遵守)

SQLisusedinotherstandardsandeveninfluencesdevelopmentofotherstandards.Examplesinclude:(用于其他標(biāo)準(zhǔn)，甚至影響其他標(biāo)準(zhǔn)的開發(fā)，例如:)ISO’sInformationResourceDirectorySystem(IRDS)Standard(信息資源字典系統(tǒng)標(biāo)準(zhǔn)),RemoteDataAccess(RDA)Standard(遠(yuǎn)程數(shù)據(jù)存取標(biāo)準(zhǔn)).5.1IntroductiontoSQL1.ObjectivesofSQL2.HistoryofSQL3.ImportanceofSQL4.Terminology4.TerminologySQLstandarddoesnotuseformaltermsofrelations,attributes,andtuples,insteadusingthetermsoftables,columns,androws(不用關(guān)系、屬性、元組，用表、列、行).ThiscoursemostlyusestheISOterminology(本課程盡量采用國家標(biāo)準(zhǔn)術(shù)語).SQLdoesnotadherestrictlytotherelationalmodeldescribedinChapter3.Forexample:(SQL不嚴(yán)格遵守關(guān)系模型的規(guī)定，例如：)SQLallowstheresulttableofaquerytocontainduplicaterows(允許查詢結(jié)果表含重復(fù)行),itimposesanorderingonthecolumns(規(guī)定列順序),itallowstheusertoordertherowsofatable(允許對行進(jìn)行排序).Chapter5

SQL:DataManipulation

(SQL:數(shù)據(jù)操縱)5.1IntroductiontoSQL5.2WritingSQLCommands5.3DataManipulation5.2WritingSQLCommandsSQLstatementconsistsofreservedwordsanduser-definedwords.(SQL命令含保留字和用戶定義的字)ReservedwordsareafixedpartofSQLandmustbespeltexactlyasrequiredandcannotbesplitacrosslines.(保留字是SQL的固定部分，必須正確拼寫且不允許跨行書寫)

User-definedwordsaremadeupbyuser,andrepresentnamesofdatabaseobjectssuchasrelations,columns,views.(用戶定義的字由用戶給出，通常是數(shù)據(jù)庫對象名，如關(guān)系名、列名、視圖名等)MostcomponentsofanSQLstatementarecaseinsensitive,exceptforliteralcharacterdata(字符數(shù)據(jù)).(除字符數(shù)據(jù)外，SQL語句不區(qū)分大小寫)5.2WritingSQLCommandsMorereadablewithindentation(縮進(jìn))

andlineation(分行):Eachclausebeginsonanewline.Startofaclauselinesupwith(對齊)startofotherclauses.Ifaclausehasseveralparts,theyshouldeachappearonaseparatelineandbeindentedunderstartoftheclause.UseextendedformofBNFnotation(采用擴(kuò)展的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(數(shù)值常量)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:數(shù)據(jù)操縱)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]Π

(

)σ

()根據(jù)WHERE子句的條件表達(dá)式，從FROM子句的基本表或視圖的笛卡兒積中，找出滿足條件的元組，按SELECT子句的目標(biāo)列表達(dá)式，進(jìn)行投影，形成結(jié)果表。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子句，則：最后將這條“記錄”投影到目標(biāo)列，產(chǎn)生結(jié)果表的一條記錄。演示然后將(聚)集函數(shù)作用到組，得到一條“記錄”，演示先將選擇結(jié)果σF(r×

s×…)，按<colList1>的值分組(值相等的元組分為一組)，演示如果有ORDERBY，則結(jié)果表還要按<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‘a(chǎn)llcolumns’:

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關(guān)鍵字書寫。例如以下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)當(dāng)別名中含空格時用單引號括起；例如：

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(標(biāo)量型子查詢).Ascalarsubquerycanbeusedwhereverasinglevalueisneeded(例如用于算術(shù)表達(dá)式中).Arowsubqueryreturnsmultiplecolumns,butagainonlyasinglerow(元組型子查詢).Arowsubquerycanbeusedwhereverarowvalueconstructorisneeded,typically