数据库系统原理(英文)-4-09.pptVIP免费

PART2RELATIONALDATABASESChapter4AdvancedSQLMarch2009DatabaseSystemConcepts-Chapter4AdvancedSQL-3IntegrityconstraintsinSQL§4.1-4.2SecurityinSQL(§4.3)authorization,grant/revokeEmbeddedanddynamicSQL§4.4-4.5ThreePartsinChapter4March2009DatabaseSystemConcepts-Chapter4AdvancedSQL-4CasestudyusedinthischapterreturnThreePartsinChapter4(cont.)March2009DatabaseSystemConcepts-Chapter4AdvancedSQL-54.1.1Built-inDataTypesinSQLInadditiontothebasicdatatypes,e.g.char(n),varchar(n),int,smallint,numeric(p,d),real,doubleprecision,float(n),describedin§3.2,theotherbuilt-indatatypesincludedate:Dates,containinga(4digit)year,monthanddatee.g.date‘2005-7-27time:Timeofday,inhours,minutesandsecondse.g.time‘09:00:30.75timestamp:dateplustimeofdaye.g.timestamp‘2005-7-2709:00:30.75’interval:periodoftimee.g.interval‘1’day§4.1SQLDataTypesandSchemasMarch2009DatabaseSystemConcepts-Chapter4AdvancedSQL-6Subtractingadate/time/timestampvaluefromanothergivesanintervalvalueintervalvaluescanbeaddedtodate/time/timestampvaluesValuesofindividualfieldscanbeextractedfromdate/time/timestampe.g.extract(yearfromr.starttime)Wecancast/convertstringtypestodate/time/timestampe.g.castasdatee.g.castastime4.1.1Built-inDataTypesinSQL(cont.)March2009DatabaseSystemConcepts-Chapter4AdvancedSQL-7Thecreatetypeclausecanbeusedtodefinenewuser-definedtypese.g.createtypeDollarsasnumeric(12,2)finalThecreatedomainclausecanbeusedtodefinenewuser-defineddomainse.g.createdomainperson_namechar(20)notnull4.1.2User-definedTypesMarch2009DatabaseSystemConcepts-Chapter4AdvancedSQL-84.1.3Large-ObjectTypesLargeobjects(photos,videos,CADfiles,etc.)arestoredasalargeobjectblob:binarylargeobjecttheobjectisalargecollectionofuninterpretedbinarydata,whoseinterpretationislefttoanapplicationoutsideofthedatabasesystemclob:characterlargeobjecttheobjectisalargecollectionofcharacterdataWhenaqueryreturnsalargeobject,apointerisreturned...