第5章 SQL Server 2000数据库表的创建和管理

1、第5章SQLServer2000数据库表的创建和管理,51表的创建与修改52添加数据信息53对已有数据进行管理54约束,5.1表的创建与修改,5.1.1字段的属性5.1.2使用EnterpriseManager创建表【例5.1】在demo数据库中创建一个用户信息表，表名为users，表结构如表5.1所示。返回目录,5.1.3使用T-SQL语句创建表其语法格式如下：CREATETABLEdatabase_name.owner.|owner.table_name/*指定表名*/(/*字段的定义*/|column_nameAScomputed_column_expression/*定义计算字段*/|

2、/*指定表的约束*/)ONfilegroup|DEFAULT/*指定存储表的文件组*/TEXTIMAGE_ONfilegroup|DEFAULT/*指定存储text、ntext和image类型数据的文件组*/以CREATETABLE语句创建用户信息表users返回目录,5.1.4修改表1使用EnterpriseManager修改表2.使用ALTERTABLE命令修改表ALTERTABLE语句的语法格式如下所示：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD,n|DR

3、OPCONSTRATINTconstraint_name|COLUMN,n返回目录,table_name：此参数是指定要修改的表的名称。ALTERCOLUMN子句：是指定要进行修改表中的字段的属性。要修改的字段名由column_name给出。new_data_type：为被修改字段的新的数据类型。Precision：此参数是指定其数据类型的有效位数。Scale：此参数是指定数据类型的小数位数。NULL|NOTNULL：此参数是指定其字段是否可接受NULL。ADD子句：向表中增加新字段。新字段的定义方法与CREATETABLE语句中定义字段的方法相同。DROP子句：从表中删除字段或约束。COLU

4、MN参数中指定的是被删除的字段名，constraint_name是被删除的约束名。【例5.2】在用户信息表users中增加一个新字段sex，说明用户的性别，并将username字段的长度由原来的20改为15。返回目录,5.1.4修改表3使用DROPTABLE命令删除表ALTERTABLE语句的语法格式如下所示：ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD,n|DROPCONSTRATINTconstraint_name|COLUMN,n返回目录,3使用DROPTA

5、BLE命令删除表DROPTABLE命令可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象（与表相关的视图和存储过程，需要用DROPVIEW和DROPPROCEDURE命令来删除）。DROPTABLE命令的语法如下DROPTABLEtable_name【例5.3】删除demo数据库中的表usersdroptabledemo.dbo.users返回目录,5.2添加数据信息,可以通过企业管理器工具和T-SQL语句来进行添加数据信息。5.2.1通过企业管理器添加数据信息5.2.2使用T-SQL语句添加表数据INSERT语句通常有两种形式：一种是添加一条记录，另一种是添加子查询的结

6、果；后者可以一次添加多条记录。INSERT语句语法格式如下所示：INSERTINTOtable_nameWITH(.n)|view_name|rowset_function_limited(column_list)VALUES(DEFAULT|NULL|expression,.n)|derived_table|execute_statement|DEFAULTVALUES返回目录,下面说明主要参数与子句的含义：INTO：用在INSERT关键字和目标表之间的可选关键字。table_name：指目标数据表的表名。WITH(.n)：指定一个或多个目标表允许的表线索，可选。view_name：指定视图

7、名称。rowset_function_limited：行集合函数限制即使用OPENQUERY或OPENROWSET函数column_list：指定要添加数据的字段，字段之间用逗号隔开。VALUES：指定对应于column_list的值DEFAULT指定SQLServer使用为此字段指定的缺省值Expression：指定一个常数、变量或表达式，表达式中不能含有SELECT或EXECUTE语句。derived_table：指定一个返回数据行的SELECT语句。execute_statement：指定一个返回SELECT或READTEXT语句的EXECUTE语句，其返回集必须与column_list

8、中指定的字段一致或兼容。DEFAULTVALUES：让所有的字段使用缺省值作为添加数据。返回目录,【例5.4】添加数据到用户信息表users中usedemoinsertusers(id,username,phone)values(20033506,zhang使用T-SQL的INSERTSELECT语句来添加多条记录。【例5.5】将留言簿数据库表guestbook_admin中的数据添加到guestbook_users用户信息表。usedemoinsertguestbook_usersselectusername，Passwordfromguestbook_admins

9、elect*fromguestbook_users返回目录,5.3.1修改数据信息其语法如下：UPDATEtable_nameWITH(.n)|view_name|rowset_function_limitedSETcolumn_name=expression|DEFAULT|NULL|variable=expression|variable=column=expression,.nFROM,.nWHERE|WHERECURRENTOFGLOBALcursor_name|cursor_variable_nameOPTION(,.n)返回目录,5.3对已有数据进行管理,其中主要参数说明如下：ta

10、ble_name：此参数是用来指定要进行修改记录的表名。WHERE：此参数是用来设置筛选要进行修改记录的条件式。SET子句：用于指定更新的方法。FROM：此参数是用来指定提供修改作业条件的表名。column_name：指定要更新数据的字段。IDENTITY字段不能被更新。expression：可以是常量、变量、表达式或返回单个值的子查询。DEFAULT：指定使用已经定义的缺省值作为字段的值。NULL：指定使用NULL值作为字段的值。variable：事先定义的变量，用于存储表达式的值。应注意，SETvariable=column=expression将变量和字段设定为与表达式相同的值，它与SE

11、Tvariable=column,column=expression语句不同，后者将字段的值赋给变量后再将表达式的值赋给字段。,【例5.6】将users表内的性别字段内容，都改为“男”usedemoUPDATEusersSETsex男wheresex女Go【例5.7】修改users表内的所有用户的username后缀为user。Usedemoupdateuserssetusername=username+userGO返回目录,5.3.2删除数据信息在TSQL语句中，提供了DELETE和TRUNCATETABLE命令来删除表中的数据信息。DELETE语句的语法如下所示：DELETEFROMtab

12、le_nameWITH(.n)|view_name|rowset_function_limitedFROM,.nWHERE|CURRENTOFGLOBALcursor_name|cursor_variable_nameOPTION(,.n)返回目录,其中主要参数说明如下FROM：此参数为可选选项，用于连接DELETE关键字和要删除数据的对象名称。table_name：指定要删除数据的表名。WITH(.n)：指定一个或多个目标表允许的表提示。view_name：指定用于删除数据的视图。rowset_function_limited：行集合函数限制。即使用OPENQUERY或OPENROWSET函

13、数。FROM：指定一个额外的FROM子句。WHERE：指定限制数据删除的条件。如果不指定WHERE子句，就会删除表中的所有数据。在WHERE子句中指定两种形式的删除操作：一种是搜索删除，即使用搜索条件来限定要删除的数据行。另一种是定位删除，即使用CURRENTOF子句来指定一个游标，删除操作会在游标的当前位置产生定位删除，比搜索删除更精确。返回目录,【例5.8】删除users表中Id为20033506的用户记录。usedemoDELETEFROMuserswhereId=20033506GO【例5.9】删除users表中所有用户的信息记录。usedemoDELETEFROMusersGO返回目

14、录,5.4表的约束,约束(Constraint)是MicrosoftSQLServer提供的自动保持数据库完整性的一种方法。在SQLServer中创建一个表时，有5种约束类型：主关键字约束（PrimaryKeyConstraint）外关键字约束（ForeignKeyConstraint）惟一性约束（UniqueConstraint）检查约束（CheckConstraint）缺省约束（defaultConstraint）1.主关键字约束也称主键约束，能惟一地指定一行记录。每一个主键字段的定义不允许为NULL属性。一个表的定义只能有一个主键约束。定义主关键字约束的语法如下CONSTRAINTcon

15、straint_namePRIMARYKEYCLUSTERED|NONCLUSTEREDcolumn_name1,column_name2,column_name16返回目录,各参数说明如下：constraint_nam：指定约束的名称。CLUSTERED|NONCLUSTERED：指定索引类别，CLUSTERED为缺省值。column_name：指定组成主关键字的字段名，主关键字最多由16个字段组成。【例5.11】在留言簿数据库（库名为demo）中,创建一个留言信息表guestbook_messages，以留言编号为主关键字。CREATETABLEdbo.guestbook_messages

16、(idbigintIDENTITY(1,1)NOTNULL,usernamechar(20)COLLATEChinese_PRC_CI_ASNULL,subjectchar(100)COLLATEChinese_PRC_CI_ASNULL,contentchar(1000)COLLATEChinese_PRC_CI_ASNULL,datedatetimeNULLconstraintpk_idprimarykey(id)ONPRIMARYGO返回目录,2.外关键字约束当一个表中的一个字段或多个字段的组合和其它表中的主关键字定义相同时，就可以将这些字段或字段的组合定义为外关键字。作用：1.在更新字

17、段值时,其它表中有与之相关联的外关键字约束的表中的外关键字字段也将被相应地做相同的更新.2.插入数据时,如果与之相关联的表的字段中无与插入的外关键字字段值相同的值时,系统会拒绝插入数据.定义外关键字约束的语法如下CONSTRAINTconstraint_nameFOREIGNKEYcolumn_name1,column_name2,column_name16REFERENCESref_tableref_column1,ref_column2,ref_column16ONDELETECASCADE|NOACTIONONUPDATECASCADE|NOACTIONNOTFORREPLICATION

18、返回目录,各参数说明如下REFERENCES：指定要建立关联的表的信息ref_table：指定要建立关联的表的名称ref_column指定要建立关联的表中的相关字段的名称ONDELETECASCADE|NOACTION：指定在删除表中数据时，对关联表所做的相关操作。ONUPDATECASCADE|NOACTION：指定在更新表中数据时，对关联表所做的相关操作。NOTFORREPLICATION：指定字段的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。【例5.12】创建一个留言用户信息表guestbook_users，与前面创建的留言信息表相关联。CREATETABLEdbo.gu

19、estbook_users(useridbigintIDENTITY(1,1)NOTNULL,usernamechar(20)COLLATEChinese_PRC_CI_ASNULL,passwordchar(20)COLLATEChinese_PRC_CI_ASNULLconstraintpk_useridprimarykey(userid),foreignkey(id)referencesproducts(id)ONPRIMARYGO返回目录,3.惟一性约束惟一性约束指定一个或多个字段的组合的值具有惟一性，以防止在字段中输入重复的值。惟一性约束指定的字段可以有NULL属性。由于主关键字值是

20、具有惟一性的，因此主关键字字段不能再设定惟一性约束。惟一性约束最多由16个字段组成，并且一个表最多可有250个唯一性约束。定义惟一性约束的语法如下CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTEREDcolumn_name1,column_name2,column_name16返回目录,【例5.13】创建一个用户信息表，其中用户的手机号码具有惟一性。CREATETABLEdbo.users(idbigintIDENTITY(1,1)NOTNULL,usernamevarchar(20)COLLATEChinese_PRC_CI_ASNULL,passwordvarchar(20)COLLATEChinese_PRC_CI_ASNULL,emailvarchar(50)COLLATEChinese_PRC_CI_ASNULL,resumevarchar(1000)COLLATEChinese_PRC_CI_ASNULL,phonevarchar(20)COLLATEChinese_PRC_CI_ASNULLconstraintpk_idprimarykey(id),constraintuk_phoneunique(phone)ONPRIMARYGO返回目录,4.检查约束检查约束对输入字段或整个表中的值设置检查条件，