搜索
第三章关系数据库标准语言SQL3.3数据定义一、SQLServer2000中的数据类型1、整数bigint:从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据（所有数字）int:从-2^31(-2,147,483,648)到2^31–1(2,147,483,647)的整型数据（所有数字）。smallint:从-2^15(-32,768)到2^15-1(32,767)的整数数据。tinyint:从0到255的整数数据。2、decimalandnumericdecimal：从-10^38+1到10^38–1的固定精度和小数位的数字数据。numeric：功能上等同于decimal。第三章关系数据库标准语言SQL3.3数据定义3、moneyandsmallmoneymoney：货币数据值介于-2^63(-922,337,203,685,477.5808)与2^63-1(+922,337,203,685,477.5807)之间，精确到货币单位的千分之十。smallmoney：货币数据值介于-214,748.3648与+214,748.3647之间，精确到货币单位的千分之十4、ApproximateNumericsfloat：从-1.79E+308到1.79E+308的浮点精度数字real：从-3.40E+38到3.40E+38的浮点精度数字。第三章关系数据库标准语言SQL3.3数据定义5、datetimeandsmalldatetimedatetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒（或3.33毫秒）。smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。6、CharacterStringschar：固定长度的非Unicode字符数据，最大长度为8,000个字符。varchar：可变长度的非Unicode数据，最长为8,000个字符。text：可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。第三章关系数据库标准语言SQL3.3数据定义7、BinaryStringsbinary：固定长度的二进制数据，其最大长度为8,000个字节。varbinary：可变长度的二进制数据，其最大长度为8,000个字节。image：可变长度的二进制数据，其最大长度为2^31-1(2,147,483,647)个字节。8、创建和删除用户自定义的数据类型EXECsp_addtypetelephone,'varchar(24)','NOTNULL'EXECsp_addtypebirthday,datetime,'NULL'EXECsp_droptypebirthdayEXECsp_droptypetelephone第三章关系数据库标准语言SQL3.3数据定义二、创建数据库CREATEDATABASEStudentsONPRIMARY(NAME=Students_dat,FILENAME='c:\mssql7\data\students.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)LOGON(NAME=Students_log,FILENAME='c:\mssql7\data\students.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)第三章关系数据库标准语言SQL3.3数据定义三、创建、修改和删除表CREATETABLE表名(列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]);如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。第三章关系数据库标准语言SQL3.3数据定义1、创建表带实体完整性CREATETABLEStudent(SnoINT[CONSTRAINTPK_Student]PRIMARYKEY,--列级约束SnameCHAR(8)UNIQUE,SageTINYINT)上述语句与下面的语句等价CREATETABLEStudent(SnoINT,SnameCHAR(8)UNIQUE,SageTINYINT,--下面的是表级约束CONSTRAINTPK_StudentPRIMARYKEY(Sno))当主码是由多个属性组成时，必须用表级约束第三章关系数据库标准语言SQL3.3数据定义2、创建表带参照完整性CREATETABLECourse(CnoINTPRIMARYKEY,CnameCHAR(12),CpnoINTFOREIGNKEYREFERENCESCourse(Cno),CcreditTINYINT)这里参照表与被参照表是同一张表SQL2000中被参照的表的列上必须有UNIQUE约束或者是主码第三章关系数据库标准语言SQL3.3数据定义CREATETABLESC(SnoINT,CnoINT,GradeTINYINT,--下面的是表级约束CONSTRAINTPK_SCPRIMARYKEY(Sno,Cno),CONSTRAINTFK_SnoFOREIGNKEY(Sno)REFERENCESStudent(Sno),CONSTRAINTFK_CnoFOREIGNKEY(Cno)REFERENCESCourse(Cno))本例中也可以用列级约束定义参照完整性本例中定义主码必须用表级约束不能用列级约束第三章关系数据库标准语言SQL3.3数据定义3、创建表带用户自定义完整性CREATETABLEStudent(SnoINTPRIMARYKEY,SnameCHAR(8)NOTNULL，SageTINYINTCHECK(Sage=10))4、修改表①ALTERTABLEStudentALTERCOLUMNSnameCHAR(20)②ALTERTABLEStudentADDSsexCHAR(2)③ALTERTABLEStudentDROPCOLUMNSsex④ALTERTABLEStudentADDCONSTRAINTSname_uniUNIQUE(Sname)⑤ALTERTABLEStudentDROPCONSTRAINTSname_uni第三章关系数据库标准语言SQL3.3数据定义5、删除表DROPTABLE表名[RESTRICT|CASCADE];RESTRICT：删除该表时是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表时没有限制。在删除基本表的同时，相关的依赖对象一起删除例如：DROPTABLEStudentCASCADE;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除第三章关系数据库标准语言SQL3.3数据定义DROPTABLE时，SQL99与3个RDBMS的处理策略比较序号标准及主流数据库的处理方式依赖基本表的对象SQL99KingbaseESORACLE9iSQLServer2000RCRCC1.索引无规定√√√√√2.视图×√×√√保留√保留√保留3.DEFAULT，PRIMARYKEY，CHECK（只含该表的列）NOTNULL等约束√√√√√√√4.ForeignKey×√×√×√×5.TRIGGER×√×√√√√6.函数或存储过程×√√保留√保留√保留√保留√保留R表示RESTRICT,C表示CASCADE'×'表示不能删除基本表，'√'表示能删除基本表，‘保留’表示删除基本表后，还保留依赖对象第三章关系数据库标准语言SQL3.3数据定义四、建立和删除索引1、建立索引CREATEUNIQUECLUSTEREDINDEXsno_indexONstudent(sno)CREATENONCLUSTEREDINDEXsname_indexONstudent(sname)SQL2000中对PRIMARYKEY约束缺省建立簇索引SQL2000中对UNIQUE约束缺省建立非簇索引SQL2000中每个表最多可有1个簇索引249个非簇索引2、删除索引DROPINDEXsno_indexSQL2000中本语句不能DROP由PRIMARYKEY或UNIQUE约束自动创建的索引有关索引更多的信息参见OnlineHelp第三章关系数据库标准语言SQL3.4查询七、小结1、有些查询只能用连接不能用嵌套2、有些查询只能用嵌套不能用连接3、有些查询既可用连接也可用嵌套4、IN子查询一定能用EXISTS子查询代替5、EXISTS子查询不一定能用IN子查询代替6、ANY和ALL子查询不一定能用集函数子查询代替第三章关系数据库标准语言SQL3.4查询八、常见错误1、多表连接时，对不唯一的属性忘记了表名前缀2、WHERE条件中出现统计函数（集函数）3、有HAVING，但没有GROUPBYSELECTCOUNT(*)FROMStudentHAVINGSdept='IS'4、非GROUPBY中的属性出现在SELECT后面，且未用统计函数SELECTSno,Cno,AVG(Grade)FROMSCGROUPBYSno第三章关系数据库标准语言SQL3.4查询5、HAVING中出现没有统计函数的属性SELECTCOUNT(*)FROMStudentGROUPBYSdeptHAVINGSdept='IS'SELECTAVG(Grade)FROMSCGROUPBYSnoHAVINGCno‘1’6、子查询（还包括视图定义中，导出关系中）中出现ORDERBY子句对本例MSSQL-Server2000报错!对本例MSSQL-Server2000不报错！第三章关系数据库标准语言SQL3.6视图•什么是视图，视图与基本表的联系与区别从一个或几个基本表(或视图)导出的表，是一个虚表，在数据库中只存放它的定义，不存放对应的数据。视图的数据存放在对应的基表中，若基表的数据发生变化，从视图中查询出的数据也随之发生变化。•使用时与基本表类似一、视图的定义1、建立视图CREATEVIEW视图名[(列名[,列名,…])]AS子查询[WITHCHECKOPTION]第三章关系数据库标准语言SQL3.6视图CREATEVIEWIS_SASSELECTSno,Sname,Sage,SdeptFROMStudentWHERESdept='IS'CREATEVIEWIS_S_CHECKASSELECTSno,Sname,Sage,SdeptFROMStudentWHERESdept='IS'WITHCHECKOPTIONCREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno行列子集视图分组视图第三章关系数据库标准语言SQL3.6视图2、删除视图DROPVIEW视图名例：DROPVIEWIS_S二、查询视图SELECTSno,SageFROMIS_SWHERESage20SELECT*FROMS_GWHEREGavg86视图消解后SELECTSno,SageFROMStudentWHERESdept=‘IS’ANDSage20SQL-Server2000视图消解后SELECTSno,AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)86第三章关系数据库标准语言SQL3.6视图三、更新视图对视图的更新最终是转换为对基表的更新UPDATEIS_SSETSdept='MA'WHERESno=15002SELECT*FROMStudentUPDATEIS_SSETSdept='IS'WHERESno=15003SELECT*FROMStudent对本例MSSQL-Server2000执行成功因为15002原来的系科是IS对本例MSSQL-Server2000不报错,但150