数据库系统概念课件ch3-SQL

Chapter3:SQLChapter3:SQL1Chapter3:SQLDataDefinitionBasicQueryStructureSetOperationsAggregateFunctionsNullValuesNestedSubqueriesComplexQueriesViewsModificationoftheDatabaseJoinedRelations**Chapter3:SQLDataDefinition2HistoryIBMSequellanguagedevelopedaspartofSystemRprojectattheIBMSanJoseResearchLaboratoryRenamedStructuredQueryLanguage(SQL)ANSIandISOstandardSQL:SQL-86SQL-89SQL-92SQL:1999(languagenamebecameY2Kcompliant!)SQL:2003Commercialsystemsoffermost,ifnotall,SQL-92features,plusvaryingfeaturesetsfromlaterstandardsandspecialproprietaryfeatures.Notallexamplesheremayworkonyourparticularsystem.HistoryIBMSequellanguagedev3DataDefinitionLanguageTheschemaforeachrelation.Thedomainofvaluesassociatedwitheachattribute.IntegrityconstraintsThesetofindicestobemaintainedforeachrelations.Securityandauthorizationinformationforeachrelation.Thephysicalstoragestructureofeachrelationondisk.Allowsthespecificationofnotonlyasetofrelationsbutalsoinformationabouteachrelation,including:DataDefinitionLanguageThesc4DomainTypesinSQLchar(n).Fixedlengthcharacterstring,withuser-specifiedlengthn.varchar(n).

Variablelengthcharacterstrings,.

Integer(afinitesubsetoftheintegersthatismachine-dependent).smallint.Smallinteger(amachine-dependentsubsetoftheintegerdomaintype).numeric(p,d).Fixedpointnumber,withuser-specifiedprecisionofpdigits,withndigitstotherightofdecimalpoint.real,doubleprecision.Floatingpointanddouble-precisionfloatingpointnumbers,withmachine-dependentprecision.float(n).Floatingpointnumber,withuser-specifiedprecisionofatleastndigits.MorearecoveredinChapter4.DomainTypesinSQLchar(n).F5CreateTableConstructAnSQLrelationisdefinedusingthe

createtable

command:

createtabler(A1

D1,A2

D2,...,AnDn,

(integrity-constraint1),

...,

(integrity-constraintk))risthenameoftherelationeachAiisanattributenameintheschemaofrelationrDiisthedatatypeofvaluesinthedomainofattributeAiExample:

createtablebranch

(branch_name char(15)notnull,

branch_city char(30),

assets integer)CreateTableConstructAnSQLr6IntegrityConstraintsinCreateTablenotnullprimarykey(A1,...,An)Example:Declarebranch_nameastheprimarykeyforbranch.

createtablebranch

(branch_name char(15),

branch_city char(30),

assets integer,

primarykey(branch_name))primarykeydeclarationonanattributeautomaticallyensuresnotnullinSQL-92onwards,needstobeexplicitlystatedinSQL-89IntegrityConstraintsinCreat7DropandAlterTableConstructsThedroptable

commanddeletesallinformationaboutthedroppedrelationfromthedatabase.Thealtertablecommandisusedtoaddattributestoanexistingrelation:

altertableraddAD

whereAisthenameoftheattributetobeaddedtorelationrandDisthedomainofA.Alltuplesintherelationareassignednullasthevalueforthenewattribute.Thealtertablecommandcanalsobeusedtodropattributesofarelation:

altertablerdropA

whereAisthenameofanattributeofrelationrDroppingofattributesnotsupportedbymanydatabasesDropandAlterTableConstruct8BasicQueryStructureSQLisbasedonsetandrelationaloperationswithcertainmodificationsandenhancementsAtypicalSQLqueryhastheform:

selectA1,A2,...,An

from

r1,r2,...,rm

whereP

AirepresentsanattributeRirepresentsarelationPisapredicate.Thisqueryisequivalenttotherelationalalgebraexpression.

TheresultofanSQLqueryisarelation.BasicQueryStructureSQLisb9TheselectClauseTheselectclauselisttheattributesdesiredintheresultofaquerycorrespondstotheprojectionoperationoftherelationalalgebraExample:findthenamesofallbranchesintheloanrelation:

selectbranch_name

fromloanIntherelationalalgebra,thequerywouldbe:

branch_name(loan)NOTE:SQLnamesarecaseinsensitive(i.e.,youmayuseupper-orlower-caseletters.)E.g.Branch_Name≡BRANCH_NAME≡branch_nameSomepeopleuseuppercasewhereverweuseboldfont.TheselectClauseTheselectcl10TheselectClause(Cont.)SQLallowsduplicatesinrelationsaswellasinqueryresults.Toforcetheeliminationofduplicates,insertthekeyworddistinctafterselect.Findthenamesofallbranchesintheloanrelations,andremoveduplicates

selectdistinctbranch_name

fromloan

Thekeywordallspecifiesthatduplicatesnotberemoved.

selectall

branch_name

fromloanTheselectClause(Cont.)SQLa11TheselectClause(Cont.)Anasteriskintheselectclausedenotes“allattributes” select*

fromloanTheselectclausecancontainarithmeticexpressionsinvolvingtheoperation,+,–,,and/,andoperatingonconstantsorattributesoftuples.Thequery: select

loan_number,branch_name,amount100

fromloan

wouldreturnarelationthatisthesameastheloanrelation,exceptthatthevalueoftheattributeamountismultipliedby100.TheselectClause(Cont.)Anas12ThewhereClauseThewhere

clausespecifiesconditionsthattheresultmustsatisfyCorrespondstotheselectionpredicateoftherelationalalgebra.TofindallloannumberforloansmadeatthePerryridgebranchwithloanamountsgreaterthan$1200. selectloan_number

fromloan

wherebranch_name=

'Perryridge'

andamount>1200Comparisonresultscanbecombinedusingthelogicalconnectivesand,or,andnot.

Comparisonscanbeappliedtoresultsofarithmeticexpressions.ThewhereClauseThewhereclau13ThewhereClause(Cont.)SQLincludesabetweencomparisonoperatorExample:Findtheloannumberofthoseloanswithloanamountsbetween$90,000and$100,000(thatis,$90,000and$100,000)selectloan_number

fromloan

whereamount

between90000and100000ThewhereClause(Cont.)SQLin14ThefromClauseThefrom

clauseliststherelationsinvolvedinthequeryCorrespondstotheCartesianproductoperationoftherelationalalgebra.FindtheCartesianproductborrowerXloan select

fromborrower,loanFindthename,loannumberandloanamountofallcustomers

havingaloanatthePerryridgebranch.selectcustomer_name,borrower.loan_number,amount

fromborrower,loan

where

borrower.loan_number=loan.loan_numberand

branch_name='Perryridge'ThefromClauseThefromclause15TheRenameOperationTheSQLallowsrenamingrelationsandattributesusingtheasclause: old-nameasnew-nameFindthename,loannumberandloanamountofallcustomers;renamethecolumnnameloan_numberasloan_id.selectcustomer_name,borrower.loan_numberasloan_id,amount

fromborrower,loan

whereborrower.loan_number=loan.loan_numberTheRenameOperationTheSQLal16TupleVariablesTuplevariablesaredefinedinthefromclauseviatheuseoftheasclause.Findthecustomernamesandtheirloannumbersforallcustomershavingaloanatsomebranch.Findthenamesofallbranchesthathavegreaterassetsthan

somebranchlocatedinBrooklyn.selectdistinctT.branch_name

frombranchasT,branchasS

whereT.assets>S.assetsandS.branch_city='Brooklyn'Keywordasisoptionalandmaybeomitted

borrowerasT≡borrower

Tselectcustomer_name,T.loan_number,S.amount

fromborrowerasT,loanasS

whereT.loan_number=S.loan_numberTupleVariablesTuplevariables17StringOperationsSQLincludesastring-matchingoperatorforcomparisonsoncharacterstrings.Theoperator“like”usespatternsthataredescribedusingtwospecialcharacters:percent(%).The%charactermatchesanysubstring.underscore(_).The_charactermatchesanycharacter.Findthenamesofallcustomerswhosestreetincludesthesubstring“Main”. selectcustomer_name

fromcustomer

where

customer_streetlike'%Main%'Matchthename“Main%”

like'Main\%'

escape'\'SQLsupportsavarietyofstringoperationssuchasconcatenation(using“||”)convertingfromuppertolowercase(andviceversa)findingstringlength,extractingsubstrings,etc.StringOperationsSQLincludes18OrderingtheDisplayofTuplesListinalphabeticorderthenamesofallcustomershavingaloaninPerryridgebranch

selectdistinctcustomer_name

fromborrower,loan

whereborrowerloan_number=loan.loan_numberand

branch_name='Perryridge'

orderbycustomer_nameWemayspecifydescfordescendingorderorascforascendingorder,foreachattribute;ascendingorderisthedefault.Example:orderby

customer_name

descOrderingtheDisplayofTuples19DuplicatesInrelationswithduplicates,SQLcandefinehowmanycopiesoftuplesappearintheresult.Multisetversionsofsomeoftherelationalalgebraoperators–givenmultisetrelationsr1andr2:1. (r1):Iftherearec1copiesoftuplet1inr1,andt1satisfiesselections,,thentherearec1copiesoft1in

(r1).2. A(r):Foreachcopyoftuplet1

inr1,thereisacopyoftuple

A(t1)inA(r1)whereA(t1)denotestheprojectionofthesingletuplet1.3. r1xr2:Iftherearec1copiesoftuplet1

inr1andc2copiesoftuplet2inr2,therearec1xc2copiesofthetuplet1.t2inr1xr2DuplicatesInrelationswithdu20Duplicates(Cont.)Example:Supposemultisetrelationsr1(A,B)andr2(C)areasfollows:

r1={(1,a)(2,a)}r2={(2),(3),(3)}ThenB(r1)wouldbe{(a),(a)},whileB(r1)xr2wouldbe {(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)}SQLduplicatesemantics:

selectA1,,A2,...,An

fromr1,r2,...,rm

whereP

isequivalenttothemultisetversionoftheexpression:

Duplicates(Cont.)Example:Sup21SetOperationsThesetoperationsunion,intersect,andexcept

operateonrelationsandcorrespondtotherelationalalgebraoperationsEachoftheaboveoperationsautomaticallyeliminatesduplicates;toretainallduplicatesusethecorrespondingmultisetversionsunionall,intersectall

andexceptall.

Supposeatupleoccursmtimesinrandntimesins,then,itoccurs:m

+ntimesinrunionallsmin(m,n)timesinr

intersectallsmax(0,m–n)timesinr

exceptallsSetOperationsThesetoperatio22SetOperationsFindallcustomerswhohavealoan,anaccount,orboth:(select

customer_namefromdepositor)

except

(select

customer_namefromborrower)(select

customer_namefromdepositor)

intersect

(select

customer_namefromborrower)Findallcustomerswhohaveanaccountbutnoloan. (select

customer_namefromdepositor)

union

(select

customer_namefromborrower)Findallcustomerswhohavebothaloanandanaccount.SetOperationsFindallcustome23AggregateFunctionsThesefunctionsoperateonthemultisetofvaluesofacolumnofarelation,andreturnavalue

avg:averagevalue

min:minimumvalue

max:maximumvalue

sum:sumofvalues

count:numberofvaluesAggregateFunctionsThesefunct24AggregateFunctions(Cont.)FindtheaverageaccountbalanceatthePerryridgebranch.Findthenumberofdepositorsinthebank.Findthenumberoftuplesinthecustomerrelation.selectavg(balance)

fromaccount

wherebranch_name='Perryridge'selectcount(*)

fromcustomerselectcount(distinctcustomer_name)

fromdepositorAggregateFunctions(Cont.)Fin25AggregateFunctions–GroupByFindthenumberofdepositorsforeachbranch.Note:Attributesinselectclauseoutsideofaggregatefunctionsmust

appearingroupbylistselectbranch_name,count(distinct

customer_name)

fromdepositor,account

wheredepositor.account_number=account.account_number

groupbybranch_nameAggregateFunctions–GroupBy26AggregateFunctions–HavingClauseFindthenamesofallbrancheswheretheaverageaccountbalanceismorethan$1,200.

Note:predicatesinthehavingclauseareappliedafterthe

formationofgroupswhereaspredicatesinthewhere

clauseareappliedbeforeforminggroupsselectbranch_name,avg(balance)

fromaccount

groupbybranch_name

havingavg

(balance)>1200AggregateFunctions–HavingC27NullValuesItispossiblefortuplestohaveanullvalue,denotedbynull,forsomeoftheirattributesnullsignifiesanunknownvalueorthatavaluedoesnotexist.Thepredicateisnullcanbeusedtocheckfornullvalues.Example:Findallloannumberwhichappearintheloanrelationwithnullvaluesforamount.

selectloan_number

fromloan

whereamountisnullTheresultofanyarithmeticexpressioninvolvingnullisnullExample:5+nullreturnsnullHowever,aggregatefunctionssimplyignorenullsMoreonnextslideNullValuesItispossiblefor28NullValuesandThreeValuedLogicAnycomparisonwithnullreturnsunknownExample:5<nullornull<>nullornull=nullThree-valuedlogicusingthetruthvalueunknown:OR:(unknown

or

true)=true,

(unknown

or

false)=unknown

(unknownorunknown)=unknownAND:(trueandunknown)=unknown,

(falseandunknown)=false,

(unknownandunknown)=unknownNOT:(notunknown)=unknown“Pisunknown”

evaluatestotrueifpredicatePevaluatestounknownResultofwhereclausepredicateistreatedasfalseifitevaluatestounknownNullValuesandThreeValuedL29NullValuesandAggregatesTotalallloanamounts

selectsum(amount)

fromloanAbovestatementignoresnullamountsResultisnullifthereisnonon-nullamountAllaggregateoperationsexceptcount(*)ignoretupleswithnullvaluesontheaggregatedattributes.NullValuesandAggregatesTota30NestedSubqueriesSQLprovidesamechanismforthenestingofsubqueries.Asubqueryisaselect-from-whereexpressionthatisnestedwithinanotherquery.Acommonuseofsubqueriesistoperformtestsforsetmembership,setcomparisons,andsetcardinality.NestedSubqueriesSQLprovides31ExampleQueryFindallcustomerswhohavebothanaccountandaloanatthebank.Findallcustomerswhohavealoanatthebankbutdonothave

anaccountatthebankselectdistinctcustomer_name

fromborrower

wherecustomer_namenotin(selectcustomer_name

fromdepositor)selectdistinctcustomer_name

fromborrower

wherecustomer_namein(selectcustomer_name

from

depositor)ExampleQueryFindallcustomer32ExampleQueryFindallcustomerswhohavebothanaccountandaloanatthePerryridgebranchNote:Abovequerycanbewritteninamuchsimplermanner.The

formulationaboveissimplytoillustrateSQLfeatures.selectdistinct

customer_name

fromborrower,loan

whereborrower.loan_number=loan.loan_numberand

branch_name='Perryridge'and

(branch_name,customer_name)

in

(selectbranch_name,customer_name

fromdepositor,account

wheredepositor.account_number=

account.account_number)ExampleQueryFindallcustomer33SetComparisonFindallbranchesthathavegreaterassetsthansomebranchlocatedinBrooklyn.Samequeryusing>someclauseselectbranch_name

frombranch

whereassets>some

(selectassets

frombranch

wherebranch_city='Brooklyn')selectdistinct

T.branch_name

frombranchasT,branchasS

whereT.assets>S.assetsand

S.branch_city='Brooklyn'SetComparisonFindallbranche34DefinitionofSomeClauseF<comp>somertrsuchthat(F<comp>t)

Where<comp>canbe:056(5<some)=true050)=false505(5

some)=true(since05)(read:5<sometupleintherelation)(5<some)=true(5=some(=some)inHowever,(some)notinDefinitionofSomeClauseF<c35ExampleQueryFindthenamesofallbranchesthathavegreaterassetsthanallbrancheslocatedinBrooklyn.selectbranch_name

frombranch

whereassets>all

(selectassets

frombranch

wherebranch_city='Brooklyn')ExampleQueryFindthenamesof36DefinitionofallClauseF<comp>allrtr(F<comp>t)056(5<all)=false6104)=true546(5

all)=true(since54and56)(5<all)=false(5=all(

all)notinHowever,(=all)inDefinitionofallClauseF<com37TestforEmptyRelationsTheexistsconstructreturnsthevaluetrueiftheargumentsubqueryisnonempty.existsrrØnotexistsrr=ØTestforEmptyRelationsTheex38ExampleQueryFindallcustomerswhohaveanaccountatallbrancheslocatedinBrooklyn.selectdistinctS.customer_name

fromdepositorasS

wherenotexists(

(selectbranch_name

frombranch

wherebranch_city='Brooklyn')

except

(selectR.branch_name

fromdepositorasT,accountasR

whereT.account_number=R.account_numberand

S.customer_name=T.customer_name))NotethatX–Y=ØXYNote:Cannotwritethisqueryusing

=all

anditsvariantsExampleQueryFindallcustomer39TestforAbsenceofDuplicateTuplesTheuniqueconstructtestswhetherasubqueryhasanyduplicatetuplesinitsresult.FindallcustomerswhohaveatmostoneaccountatthePerryridgebranch. selectT.customer_namefromdepositorasTwhereunique( selectR.customer_name

fromaccount,depositorasR

whereT.customer_name=R.customer_nameand

R.account_number=account.account_numberand

account.branch_name='Perryridge')TestforAbsenceofDuplicate40ExampleQueryFindallcustomerswhohaveatleasttwoaccountsatthePerryridgebranch.selectdistinctT.customer_namefromdepositorasTwherenotunique(

selectR.customer_name

fromaccount,depositorasR

whereT.customer_name=R.customer_nameand

R.account_number=account.account_numberand

account.branch_name=

'Perryridge')Variablefromouterlevelisknownasacorrelationvariable

ExampleQueryFindallcustomer41DerivedRelationsSQLallowsasubqueryexpressiontobeusedinthefromclauseFindtheaverageaccountbalanceofthosebrancheswheretheaverageaccountbalanceisgreaterthan$1200.

selectbranch_name,avg_balance

from(selectbranch_name,avg(balance)

fromaccount

groupbybranch_name)

asbranch_avg(branch_name,avg_balance)

whereavg_balance>1200 Notethatwedonotneedtousethehavingclause,sincewecomputethetemporary(view)relationbranch_avginthefromclause,andtheattributesofbranch_avgcanbeuseddirectlyinthewhereclause.DerivedRelationsSQLallowsa42WithClauseThewithclauseprovidesawayofdefiningatemporaryviewwhosedefinitionisavailableonlytothequeryinwhichthewith

clauseoccurs.Findallaccountswiththemaximumbalance

with

max_balance(value)as

select

max(balance)

from

account

select

account_number

from

account,max_balance

where

account.balance=max_balance.valueWithClauseThewithclausepro43ComplexQueriesusingWithClauseFindallbrancheswherethetotalaccountdepositisgreaterthantheaverageofthetotalaccountdepositsatallbranches.with

branch_total(branch_name,value)as

select

branch_name,sum(balance)

from

account

group

by

branch_name

with

branch_total_avg(value)as

select

avg(value)

from

branch_total

selectbranch_name

from

branch_total,branch_total_avg

where

branch_total.value>=branch_total_avg.valueComplexQueriesusingWithCla44ViewsInsomecases,itisnotdesirableforalluserstoseetheentirelogicalmodel(thatis,alltheactualrelationsstoredinthedatabase.)Considerapersonwhoneedstoknowacustomer’sname,loannumberandbranchname,buthasnoneedtoseetheloanamount.Thispersonshouldseearelationdescribed,inSQL,by

(selectcustomer_name,borrower.loan_number,branch_name

fromborrower,loan

whereborrower.loan_number=loan.loan_number)Aviewprovidesamechanismtohidecertaindatafromtheviewofcertainusers.Anyrelationthatisnotoftheconceptualmodelbutismadevisibletoauserasa“virtualrelation”iscalledaview.ViewsInsomecases,itisnot45ViewDefinitionAviewisdefinedusingthecreateviewstatementwhichhastheform

createviewvas<queryexpression> where<queryexpression>isanylegalSQLexpression.Theviewnameisrepresentedbyv.Onceaviewisdefined,theviewnamecanbeusedtorefertothevirtualrelationthattheviewgenerates.Whenaviewiscreated,thequeryexpressionisstoredinthedatabase;theexpressionissubstitutedintoqueriesusingtheview.ViewDefinitionAviewisdefin46ExampleQueriesAviewconsistingofbranchesandtheircustomersFindallcustomersofthePerryridgebranchcreateviewall_customeras

(selectbranch_name,customer_name

fromdepositor,account

wheredepositor.account_number= account.account_number)

union

(selectbranch_name,customer_name

fromborrower,loan

whereborrower.loan_number=loan.loan_number)selectcustomer_name

fromall_customer

wherebranch_name='Perryridge'ExampleQueriesAviewconsisti47ViewsDefinedUsingOtherViewsOneviewmaybeusedintheexpressiondefininganotherviewAviewrelationv1issaidtodependdirectly

onaviewrelationv2

ifv2isusedintheexpressiondefiningv1Aviewrelationv1issaidtodependonviewrelationv2

ifeitherv1dependsdirectlytov2orthereisapathofdependenciesfromv1tov2

Aviewrelationvissaidtoberecursive

ifitdependsonitself.ViewsDefinedUsingOtherView48ViewExpansionAwaytodefinethemeaningofviewsdefinedintermsofotherviews.Letviewv1bedefinedbyanexpressione1thatmayitselfcontainusesofviewrelations.Viewexpansionofanexpressionrepeatsthefollowingreplacementstep:

repeat

Findanyviewrelationviine1

Replacetheviewrelationvibytheexpressiondefiningvi

untilnomoreviewrelationsarepresentine1Aslongastheviewdefinitionsarenotrecursive,thisloopwillterminateViewExpansionAwaytodefine49ModificationoftheDatabase–DeletionDeleteallaccounttuplesatthePerryridgebranch

deletefromaccount

wherebranch_name='Perryridge'Deleteallaccountsateverybranchlocatedinthecity‘Needham’.

deletefromaccount

wherebranch_namein(selectbranch_name

frombranch

wherebranch_city='Needham')

ModificationoftheDatabase–50ExampleQueryDeletetherecordofallaccountswithbalancesbelowtheaverageatthebank.deletefromaccount

wherebalance<(selectavg(balance)

fromaccount)Problem:aswedeletetuplesfromdeposit,theaveragebalancechangesSolutionusedinSQL:1.First,computeavgbalanceandfindalltuplestodelete2.Next,deletealltuplesfoundabove(withoutrecomputingavgor

retestingthetuples)ExampleQueryDeletetherecord51ModificationoftheDatabase–InsertionAddanewtupletoaccount

insertintoaccount

values('A-9732','Perryridge',1200)

orequivalently

insertintoaccount(branch_name,balance,account_number)

values('Perryridge',1200,'A-9732')Addanewtupletoaccountwithbalancesettonull

insertintoaccount

values('A-777','Perryridge',null)ModificationoftheDatabase–52ModificationoftheDatabase–InsertionProvideasagiftforallloancustomersofthePerryridgebranch,a$200savingsaccount.Lettheloannumberserveastheaccountnumberforthenewsavingsaccount

insertintoaccount

selectloan_number,branch_name,200

fromloan

wherebranch_name='Perryridge'

insertintodepositor

selectcustomer_name,loan_number

fromloan,borrower

wherebranch_name='Perryridge'

andloan.account_number=borrower.account_numberTheselectfromwherestatementisevaluatedfullybeforeanyofitsresultsareinsertedintotherelation(otherwisequerieslike

insertinto

table1select*from

table1

wouldcauseproblems)ModificationoftheDatabase–53ModificationoftheDatabase–UpdatesIncreaseallaccountswithbalancesover$10,000by6%,allotheraccountsreceive5%.Writetwoupdatestatements:

updateaccount

setbalance=balance1.06

wherebalance>10000

updateaccount

setbalance=balance1.05

wherebalance10000TheorderisimportantCanbedonebetterusingthecasestatement(nextslide)ModificationoftheDatabase–54CaseStatementforConditionalUpdatesSamequeryasbefore:Increaseallaccountswithbalancesover$10,000by6%,allotheraccountsreceive5%.

update

account

set

balance=case

when

balance<=10000