第06章 管理数据库表

表操作创建表增加、删除和修改字段创建、删除和修改约束查看表格1创建表在SQLServer2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。

SQLServer2000提供了两种方法创建数据库表，第一种方法是利用企业管理器（EnterpriseManager）创建表；另一种方法是利用Transact-SQL语句中的create命令创建表。2利用企业管理创建表在EnterpriseManager中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择新建表选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。然后根据提示进行设置，并且保存表。3常用属性说明长度：指定字段的长度，即字段所占用的字节数。字段的长度并非越打越好。允许空：指定该字段在表中是否允许空值。空值表示没有输入，并不等于零或零长度的字符串。若指定一列不允许空值，则在相表中写数据时必须在列中输入一个值，否则该行不被接收如数据库。描述：指定字段的注释文本描述。默认值：指定字段的默认值，指在插入记录时，没有指定字段值的情况下，自动使用的默认值。4精度：指定该字段的位数。对于decimal和numeric类型的字段，可以设置精度属性。小数位数：显示该列值小数点右边能出现的最多数字个数。标识：指定一个字段是否为标识字段。只有bigint、int、smallint、tinyint、decimal和numeric可以设置该属性。可能的值是：1、否：不设置该字段为标识字段。2、是：指定该字段为标识字段。表示在插入一个新的数据行时，不必为字段指定数值，系统会根据标识种子和标识递增量自动生成一个字段值。3、是（不适用于复制）：类似于第二个，但是若以复制的方式象表中输入数据，系统将不自动生成字段值。5标识种子：指定标识字段的初始值。标识递增量：指定标识字段的递增值。该选项只使用于其“标识”属性设置为“是”或“是（不适用于复制）”的字段。在一个表中，只能定义一个标识字段。公式：指定用于计算字段的公式排序规则：指定当字段值用于对查询结果的记录进行排序时的排序规则，默认情况下使用数据库默认设置。6向表中添加数据步骤如下：1、选择要添加数据的表2、选择菜单“操作－打开表－返回所有行”命令，打开数据输入窗口。3、输入新记录，同时可以修改和删除操作。4、保存输入。7表操作——创建表同一用户不能建立同一个表名的表，同一表名的表可有多个拥有者。但在使用时，需要在这些表上加上所有者的表名。8利用create命令创建表其语法形式如下：CREATETABLE

[database_name.[owner].|owner.]table_name

({<column_definition>|column_nameAScomputed_column_expression|<table_constraint>}[，…n])

[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]<column_definition>::={column_namedata_type}[[DEFAULTconstant_expression]|[IDENTITY[(seed,increment)]][<column_constraint>][...n]9database_name：用于指定在其中创建表的数据库名称。owner：用于指定新建表的所有者的用户名。table_name：用于指定新建的表的名称。column_name：用于指定新建表的列的名称。computed_column_expression：用于指定计算列的列值的表达式。ON{filegroup|DEFAULT}：用于指定存储表的文件组名。如果指定filegroup，则数据表将存储在指定的文件组中。若指定default，或未指定on参数，则数据表将存储在默认文件组中。10TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。data_type：用于指定列的数据类型。DEFAULT：用于指定列的缺省值。constant_expression：用于指定列的缺省值的常量表达式。IDENTITY：用于指定列为标识列。Seed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。column_constraint和table_constraint：用于指定列约束和表约束。11例：创建一个雇员信息表其SQL语句的程序清单如下：

CREATETABLEemployee(numberintnotnull,namevarchar(20)NOTNULL,sexchar(2)NULL,birthdaydatetimenull,hire_datedatetimeNOTNULLDEFAULT(getdate())professional_titlevarchar(10)null,salarymoneynull,memontextnull)12约束主键约束唯一性约束外键约束核查约束Null约束和default约束13在企业管理器中设置主键约束主键可以用来强制一个字段或多个字段组合值的唯一性，且不允许该字段值为空值。具体步骤如下：1、选中需要设置的表节点。2、在该表节点处，点击右键，并选择“设计表”选项。3、选中需要设置主键的字段，然后点击菜单上的设置主键按钮，即可。在一个表中只能设置一个主键约束，但可以将包含多个字段的字段组合设置为主键，设置时需要按住ctrl键将多个字段选中，然后按照和设置单字段主键相同的方法设置即可。取消方法与设置类似。14在T-SQL中设置主键约束为数据表创建主键约束的句法如下：[constraintconstraint_name]Primarykey[clustered|notclustered][(字段名[,…n])]其中：constraint_name约束名称。若缺省，系统将会自动为创建的约束命名。CLUSTERED|NONCLUSTERED：用于指定约束的类型，即聚集约束或非聚集约束，CLUSTERED为默认值。column_name：用于指定主键的列名。主键最多由16个列组成。15主键约束分为字段级约束和表级约束。字段级约束：为某一个字段设置约束。在设置字段级约束时，只需将创建主键约束的语句添加到该字段的定义子句后面。例：Createtableproject(项目编号intconstraintpk_pnoprimarykey,项目名称varchar(40))16表级约束：指将包含多个字段的字段组合设置为主键。设置表级约束时，需要将创建主键约束的语句添加到各个字段定义语句的后面，并在该语句后面的括号中写入字段组合包含的所有字段，字段之间用逗号分隔。例如：Createtableproject(项目编号int,项目名称varchar(40),Constraintpk_pno_pnameprimarykey(项目编号，项目名称))17唯一性约束在一个表中只能设置一个主键约束，如何限制非主键字段输入值的唯一性呢？唯一性约束的设置可以确保在非主关键字段或字段组合中不输入重复值。唯一性约束和主键约束的区别：1、在一个表中只能定义一个主键约束，但可以定义多个唯一性约束。2、允许空值的字段上不能定义主键约束，但可以定义唯一性约束。18在企业管理器中设置唯一性约束步骤如下：1、为设置唯一性约束的表打开表设计器，选择工具栏中的“表和索引属性”按钮，打开表的属性窗口。2、选择“索引/键”选项。3、选择“新建”命令。为该表创建新的索引，此时系统分配的名称出现在“索引名”框中。4、在“列名”下展开字段的列表，选择需要附加约束的字段。若要将约束附加到多个字段，在后续行中选择其它的字段。5、选择“创建unique”复选项。6、选择“约束选项”，将在该字段上同时创建一个唯一性索引。7、保存表19唯一性约束的删除若想删除唯一性约束，可以在“索引/键”选项中选择索引名，然后选择“删除”命令。20在T-SQL中设置唯一性约束设置唯一性约束的句法如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED][（字段名[,…n]）]21例如：创建一个名为project的表，该表包含二个字段“项目编号”和“项目名称”，将字段“项目编号”设置为主键，主键约束名为pk_Pno,为字段“项目名称”设置唯一性约束，约束名为UN_pname：Createtableproject(项目编号intconstraintpk_pnoprimarykey,项目名称char(20)constraintun_pnameunique)22例：创建一个名为project的表，该表包含3个字段“项目编号”、“项目名称”和“项目负责人”，将字段“项目编号”设置为主键，主键约束名为pk_pno，为包含字段“项目名称”和“项目负责人”的组合字段设置唯一性约束，约束名为un_pname_pm。下面是用来完成这一任务的语句：Createtableproject(项目编号intConstraintpk_pnoprimarykey,项目名称char(20),项目负责人char(20),Constraintun_pname_pmunique(项目名称，项目负责人))23外键约束外关键字约束定义了表之间的关系。当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字，并设定它是和哪个表中哪些列相关联。这样，当在定义主关键字约束的表中更新列值时，其它表中有与之相关联的外关键字约束的表中的外关键字列也将被相应地做相同的更新。外关键字约束的作用还体现在，当向含有外关键字的表插入数据时，如果与之相关联的表的列中没有与插入的外关键字列值相同的值时，系统会拒绝插入数据。24步骤如下：1、打开企业管理器，展开数据库company节点。2、在数据库company的下一级中，右击“关系图”，在弹出菜单中选择“新建数据库关系图”命令。3、关闭创建数据库关系图向导，进入数据库关系图创建窗口。4、在数据库关系图创建窗口的工具栏中单击按钮“在关系图上加表”按钮。打开“添加表”窗口。5、在“添加表”窗口中选择主键表employee，并单击“添加”按钮，再选择外键表project，单击“添加”按钮，然后关闭“添加表”窗口。256、将鼠标指针移动到employee表中主关键字段“编号”左边的选择栏，按下鼠标左键，并将鼠标指针拖动到project表中字段“负责人编号”上。7、松开鼠标左键，此时弹出“创建关系”对话框。根据需要决定是否选择对话框中的如下选项：8、设置完这些选项以后，单击“确定”按钮，关闭“创建关系”对话框。此时创建数据库关系图窗口中的二个表之间出现一条连接线，表示已经建立了这个关系。9、单击“保存”按钮，在“另存为”窗口中输入关系名，单击“确定”按钮。10、在“保存”对话框中，单击“是”按钮确认对主键表和外键表所作的修改。26删除外键约束若想删除该外键约束，可以在关系图中，删除代表要删除的外键约束的关系线。注意：若不删除数据库图中的关系线，即使删除了该关系图，也不会影响设置好的外键约束，因为删除关系图并不对表作相应的修改。27在T-SQL中设置外键约束定义外关键字约束的语法如下:[CONSTRAINTconstraint_name][FOREIGNKEY]REFERENCESref_table[(ref_column)][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}][NOTFORREPLICATION]各参数说明如下:REFERENCES：指定要建立关联的表的信息ref_table：指定要建立关联的表的名称ref_column：指定要建立关联的表中的相关列的名称28ONDELETE{CASCADE|NOACTION}：指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表数据行时，会将子表中对应的数据行删除。如果指定的是NOACTION，则SQLServer会产生一个错误，并将父表中的删除操作回滚。NOACTION是缺省值ONUPDATE{CASCADE|NOACTION}：指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在更新父表数据行时会将子表中对应的数据行更新；如果指定的是NOACTION，则SQLServer会产生一个错误并将父表中的更新操作回滚。NOACTION是缺省值。NOTFORREPLICATION：指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。29例：1）创建一个数据表employees，包含两个字段“员工编号”和“员工姓名”，要求将字段“员工编号”设置为主键，并为字段“员工姓名”设置唯一性约束。2）创建另一个数据表project，该表包含三个字段“项目编号”、“项目名称”和“项目负责人”，将字段“项目编号”设置为主键，将字段“项目负责人”设置为外键，其参照字段是表employees中的字段“员工姓名”。并设置当删除数据表employees中被引用的记录时，数据表project中的引用记录也随之删除。30－－创建数据表employeesCreatetableemployees(员工编号intprimarykey,员工姓名char(20)unique)Go--创建数据表projectCreatetableproject(项目编号intprimarykey,项目名称char(20),项目负责人char(20)foreignkeyReferencesemployees(员工姓名)Ondeletecascade)go31核查约束核查约束通过检查一个或多个字段的输入值是否符合设定的检查条件来强制数据的完整性。如输入数据不符合核查条件，系统将拒绝这条记录。外键约束与核查约束的区别：外键约束是从另一个表中获得有效数值的列表，而核查约束基于逻辑表达式进行判断。32利用企业管理器设置核查约束步骤如下：1、打开企业管理器，展开employee表所在的数据库，并选中表employee。2、单击鼠标右键，在弹出菜单中选择“设计表”，选择工具栏中的“表和索引属性”按钮，打开表的属性窗口。3、在属性窗口选择“Check约束”标签，单击标签中“新建”按钮。4、在约束表达式文本窗口中输入一个条件表达式。5、在约束名对话框中输入所建核查约束名。336、根据需要决定是否选择下列选项：创建中检查现存数据：将确保根据约束对表中所有创建该约束前存在的数据进行验证。对insert、update强制约束：在将数据插入表中或更新表中的数据时，强制该约束。对复制强制约束：在将表复制到另一个数据库中时强制该约束。7、设置完上述选项，关闭“属性”对话框。新建的核查约束将在保存表时予以保存。34删除核查约束若想删除一个核查约束，可在“check约束”中选定该约束，然后单击“删除”按钮即可。35在T-SQL中设置核查约束其语法形式如下：CONSTRAINTconstraint_nameCHECK(logical_expression)

其中：logical_expression是用于核查的逻辑表达式。36例：创建一个数据表employees，包含3个字段“员工编号”、“员工姓名”和“工资”，要求将字段“员工编号”设置为主键，为字段“员工姓名”设置唯一性约束，并使“工资”字段的取值范围限定在1000～20000之间，核查约束名为ck_es。Createtableemployees(员工编号intnotnullprimarykey,员工姓名char(20)unique工资intnotnullConstraintck_esCheck(工资>=1000and工资<=20000))37Null约束和default约束用企业管理器设置：在表设计里进行设置。38用T-SQL设置NULL约束在使用createtable语句创建表时，若没有定义NULL|NotNULL约束，则系统默认允许空值。设置方法是：在定义好字段属性后，直接在后面加上NULL或NOTNull即可。例如：Createtablestudent(学号intnotnull,

姓名char(8))39用T-SQL设置default约束定义缺省约束的语法如下CONSTRAINTconstraint_nameDEFAULTconstant_expression[FORcolumn_name]例：constraintde_order_quantitydefault100fororder_quantity40规则规则是绑定到字段或用户自定义数据类型，并指定字段可接受哪些数据值的数据库对象，它通常用于执行一些与check约束相同的功能。一个字段可以应用多个check约束，但是却只能应用一个规则。规则以单独的对象创建，然后绑定到字段上。41利用企业管理器创建规则步骤如下：1、在企业管理器中展开employee表所在的数据库节点，然后选中该数据库节点下一级菜单中的“规则”节点，并单击鼠标右键，在弹出对话框内选择菜单命令“新建规则”。2、在弹出的“规则属性”对话框中的“文本”框中输入正确的条件表达式。注意：规则中不能引用表中的数据字段名以及别的数据对象。在条件表达式中包含一个局部变量，该变量必须以@开始。3、输入完成后，单击“规则属性”对话框中“绑定列”按钮，这时将弹出“将规则绑定到列”对话框。424、在“表”下拉列表中选择表employee，然后在“未绑定的列”列表中选择字段“职位”，单击“添加”按钮，此时在“绑定列”列表中添加了“职位字段”5、设置完绑定字段后，单击“确定”按钮关闭“将规则绑定到列”对话框，然后再单击“确定”按钮关闭“规则属性”对话框。43在T-SQL中设置规则句法如下：Createrulerule_nameascondition_expression其中：rule_name：指定新规则的名称。Condition_expression：定义规则的条件表达式。规则创建后，就执行sp_bindrule来将规则绑定到字段或用户定义数据类型。执行该过程的句法如下：Execsp_bindrule[@rulename=]’rule_name’,[@objname=]’object_name’其中：[@rulename=]’rule_name’由create语句创建的规则名称[@objname=]’object_name’规则将要绑定到的数据表和字段或是用户定义的数据类型。44例：创建一个数据表employees，包含3个字段“员工编号”、“员工姓名”和“工资”，要求将字段“员工编号”设置为主键，创建一个规则，规则名为ru_salary，将该规则绑定到表employees的字段“工资”上，限定该字段的值为1000～20000之间。Createtableemployees(员工编号intnotnullprimarykey,员工姓名char(20)工资intnotnull)Createruleru_salaryAs@salary>=1000and@salary<=20000GoExecsp_bindrule‘ru_alary’,‘employees.工资’go45默认值默认值可以帮助处理不包含全部数据表字段的insert操作。若在插入记录时没有指定字段的值，则默认值将指定字段中所使用的值。默认值的使用与规则类似，首先需要创建一个默认值，然后将默认值与特定的表字段或用户自定义数据类型进行绑定。46用企业管理器设置默认值步骤如下：1、在企业管理器中展开employee表所在的数据库节点，然后选中该数据库节点下一级菜单中的“默认”节点，并单击鼠标右键，在弹出对话框中选择菜单命令“新建默认”。2、在“名称”文本框中输入默认名，在“值”文本框中输入默认取值。3、输入完成后，单击对话框中“绑定列”按钮，这时弹出“将默认值绑定到列”对话框。4、选择表employee，添加字段“职位”到“绑定列”列表中。5、单击“确定”按钮关闭“将默认绑定到列”对话框，然后再单击“确定”按钮关闭“默认属性”对话框。47在T-SQL中设置默认值语法为：CreateDEFAULTdefaultAsconstant_expression其中：default是默认值的名称。Constrant_expression为只包含常量值的表达式，该表达式中不能包含任何字段或其它数据库对象的名称。创建默认值后，可以使用sp_bindefault将其绑定到字段或用户定义数据类型。执行该操作的sp_bindefault句法为：Sp_bindefault[@defname=]’default’,[@objname=]’object_name’其中：[@defname=]’default’由createdefault语句创建的默认值名称。[@objname=]’object_name’：默认值将要绑定到的数据表和字段的名称或用户定义的数据类型。48例如：创建规则。将上例中的“工资”字段的默认值设置为1000，规则为de_salary.Createdefaultde_alaryas1000GoExecsp_bindefault‘de_salary’,‘employees.工资’49在企业管理器中修改表重新命名修改数据表结构修改表属性50重新命名步骤如下：1、在企业管理器中展开重新命名的表。2、在希望重新命名的表上单击鼠标右键，在弹出菜单上选择命令“重命名”，输入新的表名称。3、按回车键，在弹出的对话框内单击“确定”按钮。51修改数据表结构打开希望修改表结构的表设计窗口，可以执行下列操作：1、修改数据表中字段的定义：包括重新设置字段名、字段类型、长度、允许空、描述、默认值、精度、小数位数、标识、公式、排序规则等字段属性，也可以设置和取消一个字段的主键约束。2、插入新字段：在末尾添加和在字段中间插入新字段。3、调整字段顺序4、删除现有字段。52修改表属性在表设计窗口中单击“表和索引属性”按钮，打开表属性窗口，在该窗口中可以设置与整个表相关的重要属性。属性窗口包含4个标签，分别是“表”、关系、索引/键和check约束选项。下面是表标签中的选项设置。53选定的表：显示选定的表明。如果在关系图中选定的表不止一个，则只有第一个选定表的名称是可见的。可以展开列表选择其它要检查或修改属性的表。所有者：显示表所有者的名称。所有者名称是SQLServer角色或SQLServer用户。表的标识列：显示由SQLServer用作表标识列的字段。若要更改标识列，从下拉列表中选择即可。表的ROWGUID（行标识符）列：显示由SQLServer用作表的ROWGUID列的字段。若要更改，可从下拉列表中选择。表文件组：指定数据表的数据存放的文件组。默认文件组最初是主文件组。文本文件组：选择要在其中存储选定表的TEXT和image类型的字段的文件组名。默认文件组是主文件组。描述：可以在该字段中输入有关数据表的一些说明文字。54在T-SQL中修改表修改字段定义添加字段删除字段添加约束删除约束55修改字段定义格式：ALTERTABLEtableALTERCOLUMNcolumn_namenew_data_type[(precision[,scale])]

[null|notnull]其中：table：需要修改的数据表名称Column_name:需要修改的字段名称New_data_type：修改后将要使用的新的数据类型Precision:字段将要使用的新的精度定义。Scale:字段将要使用的新的小数位数。56例：创建一个名为project的表，该表包含两个字段“项目名称”和“项目编号”，将字段“项目名称”从原来的数据类型char(20)更改为varchar(25)，再将原来的不可为空属性更改为可以为空。Createtableproject(项目编号intprimarykey,

项目名称char(20)notnull)--使用sp_help查看数据表的定义Execsp_helpprojectAltertableprojectAltercolumn项目名称varchar(25)nullExecsp_helpprojectgo57添加字段格式：ALTERTABLEtableADD{[column_definition]|[column_nameascomputed_column_expression]}其中：table：需要添加字段的数据表名称Column_definition：有关添加的字段的定义Column_name：添加字段的名称Computed_column_expression：计算字段表达式。

58例：向刚创建的project表中添加3个字段，分别为“项目负责人”、“项目难度级别”和“预计工期”。要求字段“预计工期”的取值为字段“项目难度级别”的值乘以20。AltertableprojectAdd项目负责人varchar(15),项目难度级别intdefault1,预计工期as项目难度级别*20Execsp_helpproject59删除字段格式：ALTERTABLEtableDROPCOLUMNcolumn_name[,…n]其中：table需要删除字段的数据表名称Column_name：需要删除的字段名称例：删除上面例子中的数据表project中的字段“项目负责人”和“预计工期”AltertableprojectDropcolumn项目负责人，预计工期60添加约束利用altertable命令中的addconstraint子句，可以添加一个或多个约束。语法如下：AlterTABLEtable[withcheck|withnocheck]Addconstraintconstraint_nameConstraint_definition[,…n]其中：table：需要添加约束的数据表名称。constraint_name：需要添加的约束名称。Constraint_definition：添加约束的定义。withcheck|withnocheck：若选择前者，表示将使用新建的数据表约束对数据表中原有的数据进行检查；若选择后者，表示新建的数据表约束追溯应用于数据表中原有的记录。默认值为前者。61例：使用数据库com