第3章数据表的创建和管理

3.1数据表的创建和管理3.2表数据的管理3.3数据完整性管理。第3章SQLServer数据表SQLServer2000有两类表，一类是永久表，这类表一旦创建将一直存储在硬盘上，除非被用户删除；另一类是临时表，这类表在用户退出时自动被系统删除。临时表又分为局部临时表与全局临时表。局部临时表的名称以＃开头，仅可由创建者本人使用，一旦创建者断开连接，该表将会消失；全局临时表的名称以＃＃开头，创建者在创建后可以由多个授权用户立即使用，一旦最后使用的用户断开连接，该表将会消失。3.1数据表的创建和管理3.1在企业管理器中创建数据表每个表都代表一个特定的实体。创建好数据库后，就可以向数据库中添加数据表。数据通常存储在表中，表存储在数据库文件中，任何有相应权限的用户都可以对之进行操作。例：使用企业管理器在数据库Student_db创建数据表St_info。标识列IDENTITY（自动编号）当需要对某一列输入的数据是有规律的顺序编号且保证每个编号惟一时，最好采用IDENTITY。具有IDENTITY属性的列称为标识列，其取值称为标识值，具有如下特点：IDENTITY列的数据类型只能为整型、numeric、decimal。当为numeric、decimal时，不允许有小数位。当用户向表中插入新记录时，系统将自动在该列添加一个按规定递增（或递减）的数据。该列值由系统自动产生每个表至多有一列具有IDENTITY属性，该列不能为空、不允许具有默认值、不能由用户更新。因此，IDENTITY列常可作为主键使用。使用IDENTITY时，可设置种子（seed）与增量（increment）。如果未指定，则取默认值（1,1）。当对表中数据进行删除操作后，在标识值之间可能会产生数量不等的差值。3.1.2使用Transact-SQL语言创建数据表CREATETABLE[database_name.[owner.]|owner.]table_name({column_namedata_type[NULL|NOTNULL]|[DEFAULTconstant_expression]|[IDENTITY[(seed,increment)]]}[,…n])[ONfilegroup|DEFAULT}][TEXTIMAGE_ON{filegroup}]3.2.2利用Transact-SQL语言创建表其中各参数的说明如下：database_name：创建新表所属的数据库名称。如果不指定，database_name默认为当前数据库。owner：指定新表所有者的用户名，如果不指定，owner默认为当前注册用户名。table_name：用于指定新建表的名称。表名必须符合标识符规则。column_name：用于指定新建表的字段名。data_type：用于指定列的数据类型。DEFAULT：用于指定列的默认值。IDENTITY[(seed,increment)：是否为标识列及标识列的种子和增量。ON{filegroup|DEFAULT}：用于指定存储表的文件组名。如果使用了DEFAULT选项，或者省略了ON子句，则新建的表会存储在默认的文件组中。TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。如果表中没有text、ntext或image列，则不能使用TEXTIMAGE_ON。如果没有指定TEXTIMAGE_ON子句，则text、ntext和image列的数据将与数据表存储在相同的文件组中。3.2.2利用Transact-SQL语言创建表例3-1：使用查询分析器在数据库Student_db中创建Student学生信息表，要求包含S_NO（学号）、NAME（姓名）、AGE（年龄）、SEX（性别）信息，其中学号不能为空。（1）启动SQLServer服务器，运行查询分析器，并连接到数据库（2）在查询窗口中输入创建表的语句。创建数据表Student的语句如下：CREATETABLEStudent(S_NOCHAR(7)NOTNULL,NAMEVARCHAR(8),AGESMALLINT,SEXCHAR(2))（3）单击工具栏上的“分析查询”按钮，若结果窗口无错误信息，再单击“执行查询”按钮，将返回“命令已成功完成”的信息。例3-2：使用查询分析器创建Student_1学生信息表，要求包含S_NO（学号）、NAME（姓名）、AGE（年龄）信息，并限制年龄在15到25周岁之间，指定学号为主关键字。创建数据表Student_1的语句如下：CREATETABLEStudent_1(S_NOCHAR(7)NOTNULL,NAMECHAR(10),AGESMALLINT,PRIMARYKEY(S_NO),CHECK(AGEBETWEEN15AND25))例3-3：使用查询分析器创建临时表temp_student。创建表temp_student的语句如下：CREATETABLE#temp_student(学号SMALLINTNOTNULL,姓名VARCHAR(30)NOTNULL,年龄INTNOTNULL,PRIMARYKEY(学号))在Transact-SQL中可用CREATETABLE来创建临时表，只要在表名前加“#”或“##”符号。其中“#”表示本地临时表，在当前数据库内使用；“##”表示全局临时表，可在所有数据库内使用。这些表存储在系统数据库tempdb中，它们在与服务器的交互结束时会自动删除。3.1.3使用企业管理器对数据表进行管理创建数据表之后，可以使用企业管理器或使用Transact-SQL语言对数据表和数据表的结构进行操作使用企业管理器管理表使用企业管理器更改数据表名称。使用企业管理器删除数据表。在企业管理器中，打开要修改数据表结构的数据库，右击要进行修改的数据表，从弹出的快捷菜单中选择“设计表”命令。在此窗口中，可以完成增加、删除和修改字段的操作。使用ALTERTABLE命令修改表结构语法格式：ALTERTABLEtable_name{[ALTERCOLUMNcolumn_name{new_data_type[NULL|NOTNULL]}]|ADDcolumn_namedata_type[NULL|DEFAULT]|DROPCOLUMNcolumn_name[,...n]}删除数据表可使用DROPTABLE语句语法格式：DROPTABLEtable_name参数说明：table：要删除的数据表的名称。注意：（1）DROPTABLE不能删除由FOREIGNKEY约束引用的表。必须先删除引用的FOREIGNKEY约束或引用的表。（2）在系统表上不能使用DROPTABLE语句。3.4管理数据表中记录当一个数据表的表结构设计完成后，就可以在数据表中录入数据了。可以使用企业管理器或使用SQL语言中的INSERT语句插入数据。3.4管理数据表中记录在企业管理器中，打开指定的数据库，右击要录入数据的数据表，从弹出的快捷菜单中选择“打开表”命令，该命令还有三个子命令。“返回所有行”：用于显示数据表中的所有记录“返回首行”：用于显示前N条记录，N可以自己输入。“查询”：用于查询具体某条记录。3.2.2使用Transact-SQL语言管理表数据表数据的添加，可使用INSERTINTO语句，语法格式：INSERT[INTO]table|view[(字段名1,字段名2,……)]Values(表达式1,表达式2,……)参数说明：[INTO]：一个可选的关键字，可以将它用在INSERT和目标表之间。Table|view：要插入数据的表名或视图名。(表达式1,表达式2,……)：与字段名列表相对应的字段的值，字符型和日期型值插入时要加单引号。使用INSERT语句一次只能插入一行数据。如果某一字段的值违反了约束或规则，或者它有与数据类型不兼容的值，该语句的操作就会失败。例3-13：假设数据库Student_db中已经创建好了课程信息数据表C_Info，试将新课程记录（9720044，网络技术与应用，选修，3）添加到课程信息数据表C_Info中。添加新课程记录的语句如下：INSERTINTOC_InfoVALUES('9720044','网络技术与应用','选修',3,null)注意：在插入数据时，对于允许为空的列可使用NULL插入空值；对于具有默认值的列可使用DEFAULT插入默认值。例3-14：在C_Info表中添加一条新记录，课程类别和课程说明暂缺。使用如下语句：注意：提供值的个数与表中列的个数不一致，必须列出列名；提供值的个数与表中列的个数相同，可省略列名，此时要求值的顺序与定义的字段顺序一致。C_Info中的课程类别和课程说明列必须允许为NULL，因为系统实际插入的数据为：('9720045','Web开发技术',null,2,null)。INSERTINTOC_Info(C_NO,C_Name,C_Credit)VALUES('9720045','Web开发技术',2)3.2.2使用Transact-SQL语言管理表数据表数据修改，使用UPDATE语句语法格式：UPDATE{table_name|view_name}SET{column_name=expression|DEFAULT|NULL}[,...n][where条件表达式]说明：table_name：要更新数据的表名。column_name：要更新数据的列名。expression|DEFAULT|NULL：更新后的数据值。其值可为表达式(expression)的值，或默认值(DEFAULT)，或NULL。where条件表达式：要更新数据的记录，只对表中满足该条件的记录进行更新。例3-15：将数据表C_Info中的所有学分加1。语句如下：例3-16：将数据表C_Info中课程号为“9710011”的学分减1，课程类型改为“必修”。语句如下：UPDATEC_InfoSETC_Credit=C_Credit+1UPDATEC_InfoSETC_Credit=C_Credit-1，c_type=‘必修’WHEREC_NO=‘9710011’3.1.4数据表中当确定有些记录不需要时，就可以将其删除。其语法格式：DELETEFROM{table_name|view_name}[WHERE条件]参数说明：table_name|view_name：要删除行的表名或视图名。WHERE条件：要删除的记录（满足条件的记录）。例3-17：在student表中删除所有学生记录，但保留数据表结构。这是无条件的删除记录，使用语句如下：DELETEFROMstudent例3-18：在C_Info表中删除课程号为“9720045”的记录。这是有条件的删除记录，使用语句如下：DELETEFROMC_InfoWHEREC_NO='9720045'使用数据库关系图创建关系数据库关系图是以图形方式显示部分或全部数据库结构，将表和表间关系以及其他对象以图形方式表现出来。数据库关系图工具并不是只能提供创建关系图的功能，还可用来创建和修改表、列、关系、键、索引和约束。可创建一个或更多的关系图，以显示数据库中的部分或全部表、列、键和关系。数据完整性是指数据的正确性、一致性和有效性，是衡量数据库中数据质量好坏的重要标准。正确性：指数据的合法性，取值与数据类型要一致。有效性：表内数据不相互矛盾，在所定义的范围之内。一致性：表间数据不相互矛盾，指数据的关联性不被破坏。当用户用INSERT、DELETE或UPDATE语句修改数据库内容时，数据的完整性就可能会遭到破坏。为了解决这些问题，保证数据的完整性，SQLServer提供了实施数据完整性的方法：约束、缺省、规则等。如：字段值不得重重、字段值必须介于1到100之间、甲数据表的A字段值必须参照乙数据表的B字段等。3.3.1数据完整性概述数据的完整性大致分为以下几种类型：实体完整性：要求数据表中的每一行在表中