数据库原理课件第三章

Chapter3:SQL4.2DatabaseSystemChapter3:SQLDataDefinitionBasicQueryStructureSetOperationsAggregateFunctionsNullValuesNestedSubqueriesComplexQueriesViewsModificationoftheDatabaseJoinedRelations**4.3DatabaseSystem3.1HistoryIBMSequellanguagedevelopedaspartofSystemRprojectattheIBMSanJoseResearchLaboratoryRenamedStructuredQueryLanguage(SQL)ANSIandISOstandardSQL:SQL-86SQL-89SQL-92SQL:1999(languagenamebecameY2Kcompliant!)SQL:2003Commercialsystemsoffermost,ifnotall,SQL-92features,plusvaryingfeaturesetsfromlaterstandardsandspecialproprietaryfeatures.Notallexamplesheremayworkonyourparticularsystem.4.4DatabaseSystem3.2DataDefinitionLanguageTheschemaforeachrelation.Thedomainofvaluesassociatedwitheachattribute.IntegrityconstraintsThesetofindicestobemaintainedforeachrelations.Securityandauthorizationinformationforeachrelation.Thephysicalstoragestructureofeachrelationondisk.Allowsthespecificationofnotonlyasetofrelationsbutalsoinformationabouteachrelation,including:4.5DatabaseSystem1.DomainTypesinSQLchar(n).Fixedlengthcharacterstring,withuser-specifiedlengthn.varchar(n).Variablelengthcharacterstrings,withuser-specifiedmaximumlengthn.Nchar(n),nVarchar(n)(forunicode,2bytecode,1-4000chars)Text.Variablelengthcharacterstrings,1to2GB.=varchar(max)int.Integer(4B,afinitesubsetoftheintegersthatismachine-dependent).smallint.Smallinteger(2B,asubsetoftheintegerdomaintype).Bigint.Biginteger(8B),tinyint(1B),bit(多个位占1B)numeric(p,d).Fixedpointnumber,withuser-specifiedprecisionofpdigits,withddigitstotherightofdecimalpoint.Decimal(p,d).4.6DatabaseSystem1.DomainTypesinSQL(cnt)real,doubleprecision.Floatingpointanddouble-precisionfloatingpointnumbers,withmachine-dependentprecision.float(n).Floatingpointnumber,withuser-specifiedprecisionofatleastndigits.Datatime8B:yyyy.mm.ddh:m:s,smalldatatime4BMoney8BXml(2GB)(不同表单可以存在xml类型的属性字段中)Binary(n)1-8000BImage2GBTimestamp8BSql_variant8016bit，可存储除text、image、timestamp、sql-variantCursor:查询结果的数据集Table：表格式数据4.7DatabaseSystem2.Createdatabase/tabaleCREATEDATABASEdatabase_nameONPRIMARY(NAME=student_dataFILENAME=‘C:\ProgramFiles\Microsofterver\MSSQL.1\MSSQL\Data\student.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%),--FILEGROWTH=10%LOGON(NAME=student_log,FILENAME=C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\student.ldf',SIZE=1MB,MAXSIZE=20MB,FILEGROWTH=10%)4.8DatabaseSystemCreateTableConstructAnSQLrelationisdefinedusingthecreatetablecommand:createtabler(A1D1,A2D2,...,AnDn,(integrity-constraint1),...,(integrity-constraintk))risthenameoftherelationeachAiisanattributenameintheschemaofrelationrDiisthedatatypeofvaluesinthedomainofattributeAiExample:createtablebranch(branch_namechar(15)notnull,branch_citychar(30),assetsinteger)Databaseschama4.9DatabaseSystemIntegrityConstraintsinCreateTablenotnullprimarykey(A1,...,An)Example:Declarebranch_nameastheprimarykeyforbranch.createtablebranch(branch_namechar(15),branch_citychar(30),assetsinteger,primarykey(branch_name))primarykeydeclarationonanattributeautomaticallyensuresnotnullinSQL-92onwards,needstobeexplicitlystatedinSQL-894.10DatabaseSystemcreatetablec_s(student_idchar(8),course_nochar(4),gradeinteger,primarykey(student_id,course_no),foreignkey(student_id)referencesstudent(id),foreignkey(course_no)referencescourse(course_no));名字可以不同域必须相同4.11DatabaseSystem3.DropandAlterTableConstructsThedroptablecommanddeletesallinformationaboutthedroppedrelationfromthedatabase.Thealtertablecommandisusedtoadd/dropattributestoanexistingrelation:altertableraddADwhereAisthenameoftheattributetobeaddedtorelationrandDisthedomainofA.Alltuplesintherelationareassignednullasthevalueforthenewattribute.altertablerdropAaltertablescaltercolumnsnochar(4);whereAisthenameofanattributeofrelationrdroptablerdropdatabasestudentDroppingofattributesnotsupportedbymanydatabases4.12DatabaseSystem3.3BasicQueryStructureSQLisbasedonsetandrelationaloperationswithcertainmodificationsandenhancementsAtypicalSQLqueryhastheform:selectA1,A2,...,Anfromr1,r2,...,rmwherePAirepresentsanattributeRirepresentsarelationPisapredicate.Thisqueryisequivalenttotherelationalalgebraexpression.TheresultofanSQLqueryisarelation.))((21,,,21mPAAArrrn4.13DatabaseSystem3.3.1.TheselectClauseTheselectclauselisttheattributesdesiredintheresultofaquerycorrespondstotheprojectionoperationoftherelationalalgebraExample:findthenamesofallbranchesintheloanrelation:selectbranch_namefromloanIntherelationalalgebra,thequerywouldbe:branch_name(loan)NOTE:SQLnamesarecaseinsensitive(i.e.,youmayuseupper-orlower-caseletters.)E.g.Branch_Name≡BRANCH_NAME≡branch_nameSomepeopleuseuppercasewhereverweuseboldfont.4.14DatabaseSystemTheselectClause(Cont.)SQLallowsduplicatesinrelationsaswellasinqueryresults.Toforcetheeliminationofduplicates,insertthekeyworddistinctafterselect.Findthenamesofallbranchesintheloanrelations,andremoveduplicatesselectdistinctbranch_namefromloanThekeywordallspecifiesthatduplicatesnotberemoved.selectallbranch_namefromloan4.15DatabaseSystemTheselectClaus