自强不息厚德载物第一章数据库中表的管理课程目标数据类型创建表修改表删除表1.数据类型•在SQLServer2005中,每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性,用于指定对象可保存的数据的类型:整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。1.数据类型•SQLServer2005中的数据类型归纳为下列类别:•精确数字、近似数字、日期和时间、字符串、Unicode字符串、二进制字符串、其他数据类型。整数类型数据类型范围存储bigint-263到263-18字节int-231到231-14字节smallint-215到215-12字节tinyint0到2551字节BIT类型•可以取值为1、0或NULL的整数数据类型。•MicrosoftSQLServer2005DatabaseEngine优化了bit列的存储。如果表中的列为8bit或更少,则这些列作为1个字节存储。如果列为9到16bit,则这些列作为2个字节存储,以此类推。•字符串值TRUE和FALSE可以转换为以下bit值:TRUE转换为1,FALSE转换为0。Decimal和numeric类型•带固定精度和小数位数的数值数据类型。•decimal[(p[,s])]和numeric[(p[,s])]•p(精度)•最多可以存储的十进制数字的总位数,包括小数点左边和右边的位数。该精度必须是从1到最大精度38之间的值。默认精度为18。•s(小数位数)•小数点右边可以存储的十进制数字的最大位数。小数位数必须是从0到p之间的值。仅在指定精度后才可以指定小数位数。默认的小数位数为0;因此,0=s=p。最大存储大小基于精度而变化。Decimal和numeric类型精度存储字节数1-9510-19920-281329-3817Money和smallmoney数据类型范围存储money-922,337,203,685,477.5808到922,337,203,685,477.58078字节smallmoney-214,748.3648到214,748.36474字节Float和real•用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值;因此,并非数据类型范围内的所有值都能精确地表示。•float[(n)]•其中n为用于存储float数值尾数的位数,以科学记数法表示,因此可以确定精度和存储大小。如果指定了n,则它必须是介于1和53之间的某个值。n的默认值为53。datetime和smalldatetime数据类型范围精确度datetime1753年1月1日到9999年12月31日3.33毫秒smalldatetime1900年1月1日到2079年6月6日1分钟Char和Varchar•char[(n)]•固定长度,非Unicode字符数据,长度为n个字节。n的取值范围为1至8,000,存储大小是n个字节。char的SQL2003同义词为character。•varchar[(n|max)]•可变长度,非Unicode字符数据。n的取值范围为1至8,000。max指示最大存储大小是231-1个字节。ntext•长度可变的Unicode数据,最大长度为230-1(1,073,741,823)个字符。存储大小是所输入字符个数的两倍(以字节为单位)。ntext的SQL2003同义词为nationaltext。text•服务器代码页中长度可变的非Unicode数据,最大长度为231-1(2,147,483,647)个字符。当服务器代码页使用双字节字符时,存储仍是2,147,483,647字节。根据字符串,存储大小可能小于2,147,483,647字节。image•长度可变的二进制数据,从0到231-1(2,147,483,647)个字节。•在MicrosoftSQLServer的未来版本中将删除ntext、text和image数据类型。请避免在新开发工作中使用这些数据类型,并考虑修改当前使用这些数据类型的应用程序。Nchar和Nvarchar类型•nchar[(n)]–n个字符的固定长度的Unicode字符数据。n值必须在1到4,000之间(含)。存储大小为两倍n字节。•nvarchar[(n|max)]–可变长度Unicode字符数据。n值在1到4,000之间(含)。Binary和Varbinary•binary[(n)]–长度为n字节的固定长度二进制数据,其中n是从1到8,000的值。存储大小为n字节。•varbinary[(n|max)]–可变长度二进制数据。n可以取从1到8,000的值。cursor类型•这是变量或存储过程OUTPUT参数的一种数据类型,这些参数包含对游标的引用。使用cursor数据类型创建的变量可以为空。timestamp•公开数据库中自动生成的唯一二进制数字的数据类型。timestamp通常用作给表行加版本戳的机制。存储大小为8个字节。•每个数据库都有一个计数器,当对数据库中包含timestamp列的表执行插入或更新操作时,该计数器值就会增加。该计数器是数据库时间戳。•一个表只能有一个timestamp列。每次修改或插入包含timestamp列的行时,就会在timestamp列中插入增量数据库时间戳值•使用某一行中的timestamp列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改,就会更新该时间戳值。如果没有对行进行更改,则该时间戳值将与以前读取该行时的时间戳值一致。•在CREATETABLE或ALTERTABLE语句中,不必为timestamp数据类型指定列名,例如:CREATETABLEExampleTable(PriKeyintPRIMARYKEY,timestamp);sql_variant•一种数据类型,用于存储SQLServer2005支持的各种数据类型(不包括text、ntext、image、timestamp和sql_variant)的值。•sql_variant可以用在列、参数、变量和用户定义函数的返回值中。sql_variant使这些数据库对象能够支持其他数据类型的值。•类型为sql_variant的列可能包含不同数据类型的行。uniqueidentifier•16字节GUID。•uniqueidentifier数据类型的列或局部变量可通过以下方式初始化为一个值:•使用NEWID函数。•从xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx形式的字符串常量转换,其中,每个x是一个在0-9或a-f范围内的十六进制数字。–例如,6F9619FF-8B86-D011-B42D-00C04FC964FF为有效uniqueidentifier值。table•一种特殊的数据类型,用于存储结果集以进行后续处理。table主要用于临时存储一组行,这些行是作为表值函数的结果集返回的。xml•存储XML数据的数据类型。可以在列中或者xml类型的变量中存储xml实例。自定义数据类型•SQLServer允许用户自定义数据类型,用户自定义数据类型是建立在SQLServer系统数据类型基础上的,当用户定义一种数据类型时,需要指定该类型的名称、建立在其上的系统数据类型以及是否允许为空等。自定义数据类型•使用SQLServer管理平台创建用户自定义数据类型:•在SQLServer管理平台中,打开指定的服务器和数据库项,选择并展开“可编程性→类型”项,接下来用右键单击“用户自定义数据类型”选项,从弹出的快捷菜单中选择“新建”命令,出现用户定义的数据类型属性对话框,如下图。自定义数据类型•可以创建规则后添加上去如:CREATERULErange_ruleAS@range=$1000AND@range$20000;自定义数据类型•系统存储过程sp_addtype为用户提供了用T_SQL语句创建自定义数据类型的途径,其语法形式如下:•sp_addtype[@typename=]自定义类型名称,•[@phystype=]对应的系统数据类型名称•[,[@nulltype=]‘是否允许为空’]•[,[@owner=]‘所有者’]2.创建表•与创建数据库类似,创建表也可以通过ManagementStudio来创建,或者使用SQL语句创建。使用ManagementStudio创建表利用create命令创建表•使用create命令创建表非常灵活,它允许对表设置几种不同的选项,包括表名、存放位置和列的属性等。•其完整语法形式如下:•CREATETABLE[数据库名.[所有者.]]表名•(列定义|列名AS计算机列表达式|•表级约束[,…n])•[ON文件组名|DEFAULT]•[TEXTIMAGE_ON文件组名|DEFAULT]列定义的写法•其中,列定义的写法如下:•列名列的数据类型•[COLLATE列的排序规则]•[[DEFAULT默认值]|[IDENTITY[(基数,增量)•[NOTFORREPLICATION]]]]•[ROWGUIDCOL]•[列级约束][...n]参数写法•数据库名:用于指定所创建表的数据库名称。•所有者:用于指定新建表的所有者的用户名。•表名:用于指定新建表的名称。•列名:用于指定新建表的列名。参数写法•计算机列表达式:用于指定计算列的列值表达式。•ON{文件组名|DEFAULT}:用于指定存储表的文件组名。•TEXTIMAGE_ON:用于指定text、ntext和image列的数据存储的文件组。•列的数据类型:用于指定列的数据类型。参数写法•默认值:用于指定列的默认值的常量表达式,可以为一个常量或NULL或系统函数。•IDENTITY:用于将列指定为标识列。•基数:用于指定标识列的初始值。•增量:用于指定标识列的增量值。参数写法•NOTFORREPLICATION:用于指定列的IDENTITY属性,在把从其他表中复制的数据插入到表中时不发生作用,即不生成列值,使得复制的数据行保持原来的列值。•ROWGUIDCOL:用于将列指定为全局惟一标识行号列。–rowguidcol属性不会使列值具有惟一性,也不会自动生成一个新的数据值给插入行。需要在INSERT语句中使用NEWID()函数或指定列的默认值为NEWID()函数。参数写法•COLLATE:用于指定表的校验方式。•列级约束:指定列的约束规则。•表级约束:指定表的约束规则,通常用于涉及多个列的约束。标识列•标识列是可以自动增加的列。在表中添加新行时,数据库引擎将为标识列提供一个唯一的增量值。标识列通常与PRIMARYKEY主键约束一起用作表的唯一行标识符。可以将IDENTITY属性分配给tinyint、smallint、int、bigint、decimal(p,0)或numeric(p,0)列。•每个表只能创建一个标识列。不能对标识列使用绑定默认值和DEFAULT约束。计算列•计算列是指没有物理地存储在表中的虚拟列。该列由同一表中的其他列通过表达式计算得到。例如,假设表中有两列“姓”和“名”用于保存人员的姓和名,则计算列可以定义为•姓名AS姓+名约束•约束是SQLServer提供的自动保持数据库完整性的一种方法,它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQLSERVER中,对于基本表的约束分为列约束和表约束。6种约束•在SQLServer2005中有6种约束:•主键约束(primarykeyconstraint)•惟一性约束(uniqueconstraint)•检查约束(checkconstraint)•默认约束(defaultconstraint)•外部键约束(foreignkeyconstraint)•空值(NULL)约束主键(PRIMARYKEY)约束•PRIMARYKEY主键约束用于定义基本表的主键,它是惟一确定表中每一条记录的标识符,其值不能为NULL,也不能重复,以此来保证实体的完整性。主键(PRIMARYKEY)约束•PRIMARYKEY既可用