SQLServer2000数据库表的创建和管理

SQLServer2000数据库表的创建和管理5．1表的创建与修改•5．2添加数据信息•5．3对已有数据进行管理•5．4约束5.1表的创建与修改•5.1.1字段的属性•5.1.2使用EnterpriseManager创建表•【例5.1】在demo数据库中创建一个用户信息表，表名为users，表结构如表5.1所示。•返回目录字段名数据类型长度允许空Idbigint8Usernamevarchar20√Passwordvarchar20√Emailvarchar50√Resumevarchar1000√Phonevarchar20√•5.1.3使用T-SQL语句创建表•其语法格式如下：•CREATETABLE•[database_name.[owner.]|owner.]table_name/*指定表名*/•({column_definition/*字段的定义*/•|column_nameAScomputed_column_expression/*定义计算字段*/•|table_constrain}/*指定表的约束*/•)•[ON{filegroup|DEFAULT}]/*指定存储表的文件组*/•[TEXTIMAGE_ON{filegroup|DEFAULT}]/*指定存储text、ntext和image类型数据的文件组*/•以CREATETABLE语句创建用户信息表users•返回目录5.1.4修改表1．使用EnterpriseManager修改表2.使用ALTERTABLE命令修改表ALTERTABLE语句的语法格式如下所示：ALTERTABLEtable_name{[ALTERCOLUMNcolumn_name{new_data_type[(precision[,scale])][NULL|NOTNULL]]}|ADD{[column_definition]}[,…n]|DROP{[CONSTRATINT]constraint_name|COLUMN}[,…n]返回目录•table_name：此参数是指定要修改的表的名称。•ALTERCOLUMN子句：是指定要进行修改表中的字段的属性。要修改的字段名由column_name给出。•new_data_type：为被修改字段的新的数据类型。•Precision：此参数是指定其数据类型的有效位数。•Scale：此参数是指定数据类型的小数位数。•NULL|NOTNULL：此参数是指定其字段是否可接受NULL。•ADD子句：向表中增加新字段。新字段的定义方法与CREATETABLE语句中定义字段的方法相同。•DROP子句：从表中删除字段或约束。COLUMN参数中指定的是被删除的字段名，constraint_name是被删除的约束名。•【例5.2】在用户信息表users中增加一个新字段sex，说明用户的性别，并将username字段的长度由原来的20改为15。•返回目录•5.1.4修改表3．使用DROPTABLE命令删除表•ALTERTABLE语句的语法格式如下所示：•ALTERTABLEtable_name•{[ALTERCOLUMNcolumn_name•{new_data_type[(precision[,scale])]•[NULL|NOTNULL]•]}•|ADD{[column_definition]}[,…n]•|DROP{[CONSTRATINT]constraint_name|COLUMN}[,…n]•返回目录•3．使用DROPTABLE命令删除表•DROPTABLE命令可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象（与表相关的视图和存储过程，需要用DROPVIEW和DROPPROCEDURE命令来删除）。•DROPTABLE命令的语法如下•DROPTABLEtable_name•【例5.3】删除demo数据库中的表users•droptabledemo.dbo.users•返回目录5.2添加数据信息•可以通过企业管理器工具和T-SQL语句来进行添加数据信息。•5.2.1通过企业管理器添加数据信息•5.2.2使用T-SQL语句添加表数据•INSERT语句通常有两种形式：一种是添加一条记录，另一种是添加子查询的结果；后者可以一次添加多条记录。•INSERT语句语法格式如下所示：•INSERT[INTO]•{table_nameWITH(table_hint_limited[...n])•|view_name•|rowset_function_limited}•{[(column_list)]•{VALUES({DEFAULT|NULL|expression}[,...n])•|derived_table•|execute_statement}}•|DEFAULTVALUES•返回目录•下面说明主要参数与子句的含义：•INTO：用在INSERT关键字和目标表之间的可选关键字。•table_name：指目标数据表的表名。•WITH(table_hint_limited[...n])：指定一个或多个目标表允许的表线索，可选。•view_name：指定视图名称。•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中指定的字段一致或兼容。•DEFAULTVALUES：让所有的字段使用缺省值作为添加数据。•返回目录•【例5.4】添加数据到用户信息表users中usedemo•insertusers•(id,username,phone)•values(20033506,'zhang’,13022567899)•使用T-SQL的INSERT…SELECT语句来添加多条记录。•【例5.5】将留言簿数据库表guestbook_admin中的数据添加到guestbook_users用户信息表。usedemo•insertguestbook_users•selectusername，Password•fromguestbook_admin•select*fromguestbook_users返回目录•5.3.1修改数据信息•其语法如下：•UPDATE•{table_nameWITH(table_hint_limited[...n])•|view_name•|rowset_function_limited}•SET•{column_name={expression|DEFAULT|NULL}•|@variable=expression•|@variable=column=expression}[,...n]•{{[FROM{table_source}[,...n]]•[WHEREsearch_condition]}•|[WHERECURRENTOF•{{[GLOBAL]cursor_name}|cursor_variable_name}]}•[OPTION(query_hint[,...n])]•返回目录5.3对已有数据进行管理•其中主要参数说明如下：•table_name：此参数是用来指定要进行修改记录的表名。•WHERE：此参数是用来设置筛选要进行修改记录的条件式。•SET子句：用于指定更新的方法。•FROM：此参数是用来指定提供修改作业条件的表名。•column_name：指定要更新数据的字段。IDENTITY字段不能被更新。•expression：可以是常量、变量、表达式或返回单个值的子查询。•DEFAULT：指定使用已经定义的缺省值作为字段的值。•NULL：指定使用NULL值作为字段的值。•@variable：事先定义的变量，用于存储表达式的值。应注意，SET@variable=column=expression将变量和字段设定为与表达式相同的值，它与SET@variable=column,column=expression语句不同，后者将字段的值赋给变量后再将表达式的值赋给字段。•【例5.6】将users表内的性别字段内容，都改为“男”•usedemo•UPDATEusers•SETsex＝’男’•wheresex＝’女’•Go•••【例5.7】修改users表内的所有用户的username后缀为user。••Usedemo•updateusers•setusername=username+user•GO返回目录5.3对已有数据进行管理•5.3.2删除数据信息•在T－SQL语句中，提供了DELETE和TRUNCATETABLE命令来删除表中的数据信息。DELETE语句的语法如下所示：•DELETE[FROM]•{table_nameWITH(table_hint_limited[...n])•|view_name•|rowset_function_limited}•[FROM{table_source}[,...n]]•[WHERE•{search_condition•|{[CURRENTOF•{[GLOBAL]cursor_name}•|cursor_variable_name}]}]•[OPTION(query_hint[,...n])]•返回目录•其中主要参数说明如下•FROM：此参数为可选选项，用于连接DELETE关键字和要删除数据的对象名称。•table_name：指定要删除数据的表名。•WITH(table_hint_limited[...n])：指定一个或多个目标表允许的表提示。•view_name：指定用于删除数据的视图。•rowset_function_limited：行集合函数限制。即使用OPENQUERY或OPENROWSET函数。•FROMtable_source：指定一个额外的FROM子句。•WHERE：指定限制数据删除的条件。如果不指定WHERE子句，就会删除表中的所有数据。在•WHERE子句中指定两种形式的删除操作：一种是搜索删除，即使用搜索条件来限定要删除的数据行。另一种是定位删除，即使用CURRENTOF子句来指定一个游标，删除操作会在游标的当前位置产生定位删除，比搜索删除更精确。•返回目录•【例5.8】删除users表中Id为20033506的用户记录。••usedemo•DELETE•FROMusers•whereId=’20033506’•GO••【例5.9】删除users表中所有用户的信息记录。••usedemo•DELETE•FROMusers•GO返回目录5.4表的约束•约束(Constraint)是MicrosoftSQLServer提供的自动保持数据库完整性的一种方法。在SQLServer中创建一个表时，有5种约束类型：•●主关键字约束（PrimaryKeyConstraint）•●外关键字约束（ForeignKeyConstraint）•●惟一性约束（UniqueConstraint）•●检查约束（CheckConstraint）•●缺省约束（defaultConstraint）•1.主关键字约束•也称主键约束，能惟一地指定一行记录。每一个主键字段的定义不允许为NULL属性。一个表的定义只能有一个主键约束。•定义主关键字约束的语法如下•CONSTRAINTconstraint_name•PRIMARYKEY[CLUSTERED|NONCLUSTERED]•column_name1[