T-SQL建表 - 中国科学技术大学.ppt

在SSMS中建表用T-SQL建表,SQLServer2005中的表,列每一列代表由表建模的对象的某个属性，例如，学生选课登记表有学号列、姓名列、专业列选修课程列、任课教师列。行每一行代表由表建模的对象的一个单独的实例。例如，每一个学生选修的一门课程记录在表中均占一行。,表是包含数据库中所有数据的数据库对象。表定义是一个列集合。SQLServer2005中的表包括下列主要组件：,SQLServer2005中的表,数据类型指定表域的第一步是确定列数据类型。域是列中允许的值的集合。数据完整性表列中除了具有数据类型和大小属性之外，还有其他属性。其他属性是保证数据库中数据完整性和表的引用完整性的重要部分。约束规则向后兼容功能，应避免使用默认值DML触发器,域是列中允许的值的集合。,SQLServer2005的数据类型,字符型Char(n)：固定长度字符，n的取值范围为1至8,000，输入数据长度不够的补空格；存储大小是n个字节。varchar(n|max)：变长字符，n为18000，不够不补；max指示最大存储大小是231-1个字节。存储大小是输入数据的实际长度加2个字节。nchar、nvarchar：unicode字符集，双倍存储空间来存储字符；text：可达2GB容量，一般数据本身存储在数据库之外，以文件形式存放；用varchar(max)代替；ntext：最多只能存储1GB的字符,指定表域的第一步是确定列数据类型。,SQLServer2005的数据类型,整型数值int：存储整数，-21474836482147483647；Bigint：-9223372036854775808-9223372036854775807；Smallint：-3276832767；Tinyint：0255；,SQLServer2005的数据类型,浮点小数数值Decimal/numeric：-10381038-1；共38位，decimal(p,q)共p位（含小数点），小数点后q位Float：存储小数点不固定的数值，-1.79E3081.79E308Real：-3.4E3083.4E308,SQLServer2005的数据类型,货币型money：-922,337,203,685,477.5808922,337,203,685,477.5807,存储到小数点后四位，不存储货币符号smallmoney:-214,748.3648214,748.3647日期型Datetime：日期型，1753.1.19999.12.31Smalldatetime：1900.1.12079.6.6,SQLServer2005的数据类型,二进制字符binary(n)：固定长度二进制数据，n为18000，主要用于存储作为标记或标记组合的数据Varbinary(n|max):可变长度二进制数据，n可以取从1到8,000的值。max指示最大的存储大小为231-1字节。bit：只存储0、1Image：用于存储任何形式的二进制数据，如图像、视频、音乐等，用varbinary(max)代替；程序中的数据类型cursortablesql_varinat,列的完整性约束可选项，默认为NULL，NULL|NOTNULL|PRIMARYKEY|DEFAULT|CHECK|UNIQUE|NOTNULLUNIQUENULLNOTNULLPRIMARYKEY，当表中只有一个列是主键，在该列数据类型说明后添加PRIMARYKEY例：专业关系表SS（专业代码，专业名称）CREATETABLESS(SCODE#CHAR(5)PRIMARYKEY,SSNAMEVARCHAR(30)NOTNULL);,约束,DEFAULT设置缺省值，对暂时不输入数据的数字列施加DEFAULT约束，以避免数据操作错误，格式：DEFAULT（）CHECK指出该列取值约束条件内的值，格式：CHECK（）例：学生关系表S（学号,姓名,性别,生日,籍贯,专业号,班级）CREATETABLES(S#CHAR(10)PRIMARYKEY,SNAMECHAR(8)NOTNULL,SEXCHAR(2)CHECK(SEXIN(男,女),BIRTHDAYDATETIMENOTNULL,PLACECHAR(20),SCODECHAR(4)NOTNULL,CLASSCHAR(4)NOTNULL);,约束,UNIQUE惟一性约束NOTNULLUNIQUE该列非空，且惟一表的完整性约束主键约束子句用于主键多于一个属性PRIMARYKEY（，）外键约束子句FOREIGNKEY（）REFERENCES（）,约束,约束,用T-SQL创建表,CREATETABLE语句语法如下。CREATETABLEdatabase_name.schema_name.|schema_name.table_name(|,.n)ONpartition_scheme_name(partition_column_name)|filegroup|defaultTEXTIMAGE_ONfilegroup|default;,指定数据表名称定义数据表字段定义字段约束定义数据表约束定义计算字段定义文件组,创建表CREATETABLE语句,指定数据表名称,SQLServer2000的数据表的完整名称包括数据库、所有者和数据表名3部分。database_name包含数据表的数据库名称。数据库名称可以省略，在应用程序中，默认为当前连接的数据库名称；owner指定数据表所有者，默认为数据表创建者在数据库中的名称。例如，sa在每个数据库中的默认用户名称为dbo。指定的数据表所有者必须在数据库中具有创建表权限，否则不能创建数据表。如果是以sysadmin、db_ownwer或db_ddladmin角色成员身份创建的数据表，则可执行除自己以外的其他用户名称作为数据表所有者。sysadmin角色或别名为dbo的用户创建数据表时，默认的所有者为dbo。,table_name指定数据表名称，名称最多128个字符。在SQLServer2005中，数据表属于所有者，所以数据库中的owner.table_name组合必须惟一。在使用CREATETABLE语句创建本地临时表（表名称以#开头）时，表名称最多为116个字符。,定义数据表字段,数据表字段定义语法格式如下。:=column_namedata_typeCOLLATEcollation_nameDEFAULTconstant_expression|IDENTITY(seed,increment)NOTFORREPLICATIONROWGUIDCOL.n,column_name：定义字段名称。data_type：定义字段数据类型和长度，长度需要使用括号()括起来，例如，char(8)，numeric(3,2)。COLLATE：指定排序规则，默认为数据库的默认值。只有字符型字段才能指定排序规则。DEFAULTconstant_expression：定义字段的默认值。IDENTITY(seed,increment)：定义字段标识属性，允许字段自动编号。seed为自动编号的开始值，默认为1。increment为自动编号的增量，默认为1。一个表中只允许有一个标识字段。,NOTFORREPLICATION：用在IDENTITY之后，说明自动编号不用于复制操作，即向表复制数据时，复制的数据不自动编号。ROWGUIDCOL：设置字段由SQLServer自动产生一个全局惟一值，只有字段数据类型为uniqueidentifier时，才可设置该选项。：定义字段约束。例如：CREATETABLEtest1(编号intIDENTTITY,学号char(8)成绩numeric(3,1)DEFAULT0),定义字段约束,可以为单个字段定义约束，语法如下。:=CONSTRAINTconstraint_nameNULL|NOTNULL|PRIMARYKEY|UNIQUECLUSTERED|NONCLUSTEREDWITHFILLFACTOR=fillfactor|FOREIGNKEYREFERENCESref_table(ref_column)ONDELETECASCADE|NOACTIONONUPDATECASCADE|NOACTIONNOTFORREPLICATION|CHECKNOTFORREPLICATION(logical_expression),CONSTRAINTconstraint_name：定义约束名称，可以省略。如果省略，则由SQLServer产生一个惟一的约束名称。约束名称在数据库中必须是惟一的。NULL或NOTNULL：设置字段是否允许空值，NULL表示允许空值，NOTNULL表示不允许空值。例如：CREATETABLEtest2(编号intNOTNULL,学号char(8),成绩numeric(3,1)DEFAULT0NULL)PRIMARYKEY或UNIQUE：定义字段为主关键字或UNIQUE约束。例如：CREATETABLEtest2(编号intPRIMARYKEY,学号char(8),CLUSTERED或NONCLUSTERED：定义PRIMARYKEY或UNIQUE约束为聚集（CLUSTERED）或非聚集索引（NONCLUSTERED）。PRIMARYKEY约束默认为CLUSTERED，UNIQUE约束默认为NONCLUSTERED。在CREATETABLE语句中只能为一个约束指定CLUSTERED，如果在为UNIQUE约束指定CLUSTERED的同时又指定了PRIMARYKEY约束，则PRIMARYKEY将默认为NONCLUSTEREDWITHFILLFACTOR=fillfactor：定义SQLServer存储索引数据时每个索引页的充满程度。fillfactor取值范围从1到100，默认值为0。FOREIGNKEY：定义外键约束，即创建表之间的关联。REFERENCESref_table(ref_column)：指定外键约束引用的表和引用字段。ONDELETECASCADE|NOACTION：指定在删除主键表中的被引用字段数据时，是否级联删除外键表关联记录。默认为ONDELETENOACTION，即不删除外键表记录.,ONUPDATECASCADE|NOACTION：指定主键表中的关联字段被修改时是否级联更新关联字段，默认为ONUPDATENOACTION，即不级联修改外键表关联字段。NOTFORREPLICATION：设置在将数据复制到表中时，不执行外键约束检验。CHECK：定义CHECK约束。NOTFORREPLICATION表示在复制数据时不执行CHECK约束，例如：CREATETABLEtest2(编号intPRIMARYKEY,学号char(8),成绩numeric(3,1),CONSTRAINTck_test2_成绩CHECK(成绩=0AND成绩=0and语文=0and数学=0and外语=100),删除约束和字段,删除约束和字段的语法如下。ALTERTABLEtableDROPCONSTRAINTconstraint_name|COLUMNcolumn,.n删除约束例如，在前面为“成绩”数据表创建了一个名为“ck_成绩_语文_数学_外语”的约束，现在将其删除。ALTERTABLE成绩DROPCONSTRAINTck_成绩_语文_数学_外语删除字段例如，下面的语句可以删除“成绩”数据表中的“平均成绩”字段。ALTERTABLE成绩DROPCOLUMN平均成绩,关闭和启用约束,关闭和启用约束的语法如下。ALTERTABLEtableCHECK|NOCHECKCONSTRAINTALL|constraint_name,.n关闭和启用约束指针对数据表的FOREIGNKEY和CHECK约束。CHECK|NOCHECK：CHECK表示启用约束，NOCHECK表示关闭约束。ALL：表示数据表中所有的FOREIGNKEY和CHECK约束。例如，test2数据表有一个约束，其名称为“ck_test2_成绩”，下面的语句可以用于关闭该约束。ALTERTABLEtest2NOCHECKCONSTRAINTck_test2_成绩下面的语句则可重新启用“ck_test2_成绩”约束。ALTERTABLEtest2CHECKCONSTRAINTck_test2_成绩,修改数据表和字段名称,ALTERTABLE语句不能修改数据表的名称和字段名称，如果要修改数据表和字段名称，可使用系统存储过程sp_rename。sp_rename语法如下。sp_renameold_name,new_name,object_type其中，old_name为要修改的对象名称，new_name为对象的新名称，object_type用于指定要修改的对象的类型，可以为下列值。COLUMN：指定修改字段名称。DATABASE：指定修改数据库名称。INDEX：指定修改索引名称。OBJECT：指定修改约束（CHECK、FOREIGNKEY、PRIMARYKEY、UNIQUE）、用户表、视图、存储过程、触发器和规则等对象。USERDATATYPE：指定修改用户自定义数据类型