数据库中表的管理

数据库中表的管理课程目标数据类型创建表修改表删除表１．数据类型在SQLServer2005中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。１．数据类型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和Varcharchar[(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和Varbinarybinary[(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的列可能包含不同数据类型的行。uniqueidentifier16字节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既可用于列约束，也可用于表约束。设置主键的语法形式如下：CONSTRAINT约束名PRIMARYKEY[CLUSTERED|NONCLUSTERED][（列名）]主键（PRIMARYKEY）约束如果是在列级约束中指定主键约束，则直接在列的定义后写主键约束的代码，不需要再指定列名了。如果是在表级约束中指定主键约束，则需要指明列名。如果要将某些列的组合定义为主键，只能在表级约束中写，其语法格式如下：CONSTRAINT约束名PRIMARYKEY[CLUSTERED|NONCLUSTERED]（列名1，列名2，…）主键（PRIMARYKEY）约束主键（PRIMARYKEY）约束以下代码建立一个SC学习选课表，定义SNO学生学号，CNO课程编号共同组成SC的主键：CREATETABLESC(SNOCHAR(5)NOTNULL,CNOCHAR(5)NOTNULL,SCORENUMERIC(3),CONSTRAINTSC_PRIMPRIMARYKEY(SNO,CNO))惟一性约束惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。惟一性约束当使用惟一性约束时，需要考虑以下几个因素：使用惟一性约束的字段允许为空值；一个表中可以允许有多个惟一性约束；可以把惟一性约束定义在多个字段上；惟一性约束用于强制在指定字段上创建一个惟一性索引；默认情况下，创建的索引类型为非聚集索引。惟一性约束创建唯一性约束的语法形式如下：CONSTRAINT约束名UNIQUE[CLUSTERED|NONCLUSTERED]（列名[,…n]）惟一性约束以下代码创建一个学生信息表，其中name字段具有惟一性。Createtablestudent（idchar（8）,namechar（10）,sexchar（2）,constraintpk_idprimarykey（id）,constraintuk_identityunique（name））惟一性约束也可以在SQLServer管理平台中完成创建和修改惟一性约束的操作，方法是在列上点右链，从弹出的菜单中选择“索引/键”

检查约束检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。检查约束当使用检查约束时，应该考虑和注意以下几点：一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关；一个表中可以定义多个检查约束；每个CREATETABLE语句中每个字段只能定义一个检查约束；在多个字段上定义检查约束，则必须将检查约束定义为表级约束；当执行INSERT语句或者UPDATE语句时，检查约束将验证数据；检查约束中不能包含子查询。检查约束创建检查约束的语法形式如下：CONSTRAINT约束名CHECK[NOTFORREPLICATION](用于检查的逻辑表达式)检查约束以下代码建立一个SC表，定义SCORE的取值范围为0到100之间。CREATETABLESC(SNOCHAR(5),CNOCHAR(5),SCORENUMERIC(5,1)CONSTRAINTSCORE_CHKCHECK(SCORE>=0ANDSCORE<=100))检查约束在ManagementStudio中创建检查约束的方法是，从右键菜单中选择“CHECK约束”

默认（DEFAULT）约束默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。默认（DEFAULT）约束使用默认约束时，应该注意以下几点：（1）每个字段只能定义一个默认约束；（2）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；（3）不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上；（4）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。默认（DEFAULT）约束创建默认约束的语法形式如下：CONSTRAINT约束名DEFAULT约束表达式[FOR列名]以下代码为dept字段创建默认约束，使其默认值是“计算机”。constraintcon_deptdefault‘计算机’fordept默认（DEFAULT）约束外键约束外键(FOREIGNKEY)是用于建立和加强两个表数据之间的链接的一列或多列。外键约束用于强制参照完整性。外键约束当使用外键约束时，应该考虑以下几个因素：外键约束提供了字段参照完整性；外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配；外键约束不能自动创建索引，需要用户手动创建；用户想要修改外键约束的数据，必须有对外键约束所参考表的SELECT权限或者REFERENCES权限；参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外键子句；一个表中最多可以有31个外键约束；在临时表中，不能使用外键约束；主键和外键的数据类型必须严格匹配。外键约束创建外键约束的语法形式如下：CONSTRAINT约束名FOREIGNKEY（列名[,…n]）REFERENCES参照表名[（参照列名[,…n]）]外键约束以下代码建立一个SC表，定义SNO,CNO为SC的外部键。CREATETABLESC(SNOCHAR(5)NOTNULLCONSTRAINTS_FOREFOREIGNKEYREFERENCESS(SNO),CNOCHAR(5)NOTNULLCONSTRAINTC_FOREFOREIGNKEYREFERENCESC(CNO),SCORENUMERIC(3),CONSTRAINTS_C_PRIMPRIMARYKEY(SNO,CNO))外键约束在ManagementStudio中创建外键约束的方法是，从右键菜单中选择“关系”。空值（NULL）约束空值（NULL）约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为NOTNULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。空值（NULL）约束只能用于定义列约束。空值（NULL）约束创建空值（NULL）约束的语法形式如下：[CONSTRAINT<约束名>][NULL|NOTNULL]以下代码建立一个S表，对SNO字段进行NOTNULL约束。CREATETABLES(SNOCHAR(10)CONSTRAINTS_CONSNOTNULL,SNVARCHAR(20),AGEINT,SEXCHAR(2)DEFAULT’男’,DEPTVARCHAR(20))空值（NULL）约束在ManagementStudio中创建空值约束的方法是，在需要设置允许为空的列中，勾选对应的“允许空”复选框，如果不允许为空则取消该复选框的选择。如下图：3.修改表当数据库中的表创建完成后，可以根据需要改变表中原先定义的许多选项，以更改表的结构。用户可以增加、删除和修改列，增加、删除和修改约束，更改表名以及改变表的所有者等。在ManagementStudio中修改表的属性与创建表的界面一致，这里就只讲解使用SQL语句修改表的方法。3.1修改列属性（1）修改列的数据类型；（2）修改列的数据长度；（3）修改列的精度；（4）修改列的小数位数；（5）修改列的为空性。3.1修改列属性ALTERTABLE表名ALTERCOLUMN列名{列修改项}如以下代码创建表t1，然后将其列b从char(10)修改为char(20)：CREATETABLEt1(aint,bchar(10))Altertablet1Altercolumnbchar(20)3.2添加和删除列添加列的语法如下：ALTERTABLE表名ADD列定义