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

1、3.1 数据表的创建和管理数据表的创建和管理3.2 表数据的管理表数据的管理3.3 数据完整性管理数据完整性管理。第第3章章 SQL Server数据表数据表SQL Server 2000有两类表，一类是永久表永久表，这类表一旦创建将一直存储在硬盘上，除非被用户删除；另一类是临时表临时表，这类表在用户退出时自动被系统删除。临时表又分为局部临时局部临时表与全局临时表表与全局临时表。局部临时表的名称以开头，仅可由创建者本人使用，一旦创建者断开连接，该表将会消失；全局临时表的名称以开头，创建者在创建后可以由多个授权用户立即使用，一旦最后使用的用户断开连接，该表将会消失。3.1 数据表的创建和管理数据

2、表的创建和管理3.1 在企业管理器中创建数据表n每个表都代表一个特定的实体。创建好数据库后，就可以向数据库中添加数据表。数据通常存储在表中，表存储在数据库文件中，任何有相应权限的用户都可以对之进行操作。n例：使用企业管理器在数据库Student_db创建数据表St_info。标识列标识列IDENTITY （自动编号）（自动编号）当需要对某一列输入的数据是有规律的顺序编号且保证每当需要对某一列输入的数据是有规律的顺序编号且保证每个编号惟一时，最好采用个编号惟一时，最好采用IDENTITY。具有。具有IDENTITY属性属性的列称为标识列，其取值称为标识值，的列称为标识列，其取值称为标识值，具有如

3、下特点：具有如下特点：IDENTITY列的数据类型只能为整型、列的数据类型只能为整型、numeric、decimal。当为。当为numeric、decimal时，不允许有小数位。时，不允许有小数位。当用户向表中插入新记录时，系统将自动在该列添加一当用户向表中插入新记录时，系统将自动在该列添加一个按规定递增（或递减）的数据。该列值由系统自动产生个按规定递增（或递减）的数据。该列值由系统自动产生每个表至多有一列具有每个表至多有一列具有IDENTITY属性，该列不能为空、属性，该列不能为空、不允许具有默认值、不能由用户更新。因此，不允许具有默认值、不能由用户更新。因此，IDENTITY列列常可作为主

4、键使用。常可作为主键使用。使用使用IDENTITY时，可设置种子（时，可设置种子（seed）与增量）与增量（increment）。如果未指定，则取默认值（）。如果未指定，则取默认值（1,1）。）。当对表中数据进行删除操作后，在标识值之间可能会产当对表中数据进行删除操作后，在标识值之间可能会产生数量不等的差值。生数量不等的差值。3.1.2 使用使用Transact-SQL语言创建数据表语言创建数据表CREATE TABLEdatabase_name.owner.| owner .table_name (column_name data_type NULL|NOT NULL | DEFAULT c

5、onstant_expression | IDENTITY (seed,increment) ,n )ON filegroup | DEFAULTTEXTIMAGE_ONfilegroup3.2.2 利用Transact-SQL语言创建表其中各参数的说明如下其中各参数的说明如下：ndatabase_name：创建新表所属的数据库名称。如果不指定，database_name 默认为当前数据库。nowner：指定新表所有者的用户名，如果不指定，owner 默认为当前注册用户名。ntable_name：用于指定新建表的名称。表名必须符合标识符规则。ncolumn_name：用于指定新建表的字段名。n

6、data_type：用于指定列的数据类型。nDEFAULT：用于指定列的默认值。nIDENTITY (seed,increment)：是否为标识列及标识列的种子和增量。nON filegroup | DEFAULT：用于指定存储表的文件组名。如果使用了DEFAULT选项，或者省略了ON子句，则新建的表会存储在默认的文件组中。nTEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。如果表中没有text、ntext或image列，则不能使用TEXTIMAGE_ON。如果没有指定TEXTIMAGE_ON子句，则text、ntext和image列的数据将与数据表存储在

7、相同的文件组中。3.2.2 利用Transact-SQL语言创建表例例3-1：使用查询分析器在数据库使用查询分析器在数据库Student_db中创建中创建Student学生学生信息表，要求包含信息表，要求包含S_NO（学号）、（学号）、NAME（姓名）、（姓名）、AGE（年（年龄）、龄）、SEX（性别）信息，其中学号不能为空。（性别）信息，其中学号不能为空。（1）启动）启动SQL Server服务器，运行查询分析器，并连接到数据库服务器，运行查询分析器，并连接到数据库（2）在查询窗口中输入创建表的语句。）在查询窗口中输入创建表的语句。创建数据表创建数据表Student的语句如下：的语句如下：C

8、REATE TABLE Student(S_NO CHAR(7) NOT NULL,NAME VARCHAR(8),AGE SMALLINT,SEX CHAR(2)（3）单击工具栏上的）单击工具栏上的“分析查询分析查询”按钮，若结果窗口无错误信息，按钮，若结果窗口无错误信息，再单击再单击“执行查询执行查询”按钮，将返回按钮，将返回“命令已成功完成命令已成功完成”的信息。的信息。例例3-2：使用查询分析器创建使用查询分析器创建Student_1学生信息表，学生信息表，要求包含要求包含S_NO（学号）、（学号）、NAME（姓名）、（姓名）、AGE（年（年龄）信息，并限制年龄在龄）信息，并限制年龄在

9、15到到25周岁之间，指定学号周岁之间，指定学号为主关键字。为主关键字。创建数据表创建数据表Student_1的语句如下：的语句如下：CREATE TABLE Student_1 (S_NO CHAR(7) NOT NULL, NAME CHAR(10), AGE SMALLINT, PRIMARY KEY(S_NO), CHECK(AGE BETWEEN 15 AND 25)例例3-33-3：使用查询分析器创建临时表使用查询分析器创建临时表temp_student 。创建。创建表表temp_student的语句如下：的语句如下：CREATE TABLE #temp_student(学号学号

10、SMALLINT NOT NULL , 姓名姓名 VARCHAR(30) NOT NULL, 年龄年龄 INT NOT NULL, PRIMARY KEY (学号学号)在在Transact-SQL中可用中可用CREATE TABLE来创建临时来创建临时表，只要在表名前加表，只要在表名前加“#”或或“#”符号。其中符号。其中“#”表示表示本地临时表，在当前数据库内使用；本地临时表，在当前数据库内使用；“#”表示全局临表示全局临时表，可在所有数据库内使用。这些表存储在系统数时表，可在所有数据库内使用。这些表存储在系统数据库据库tempdb中，它们在与服务器的交互结束时会自动中，它们在与服务器的交互

11、结束时会自动删除。删除。 3 .1.3使用企业管理器对数据表进行管理使用企业管理器对数据表进行管理 创建数据表之后，可以使用企业管理器或使用创建数据表之后，可以使用企业管理器或使用Transact-SQL语言对数据表和数据表的结构进行操作语言对数据表和数据表的结构进行操作 使用企业管理器管理表使用企业管理器管理表n使用企业管理器更改数据表名称。使用企业管理器更改数据表名称。n使用企业管理器删除数据表。使用企业管理器删除数据表。n在企业管理器中，打开要修改数据表结构的数据库，在企业管理器中，打开要修改数据表结构的数据库，右击要进行修改的数据表，从弹出的快捷菜单中选右击要进行修改的数据表，从弹出的

12、快捷菜单中选择择“设计表设计表”命令。在此窗口中，可以完成增加、命令。在此窗口中，可以完成增加、删除和修改字段的操作。删除和修改字段的操作。使用使用ALTER TABLEALTER TABLE命令修改表结构命令修改表结构语法格式：语法格式：ALTER TABLE table_nameALTER TABLE table_name ALTER COLUMN column_name ALTER COLUMN column_name new_data_type new_data_type NULL | NOT NULL NULL | NOT NULL |ADD column_name data_typ

13、e |ADD column_name data_type NULL|DEFAULT NULL|DEFAULT |DROP COLUMN column_name |DROP COLUMN column_name ,.n ,.n 删除数据表可使用删除数据表可使用DROP TABLE语句语句语法格式：语法格式：DROP TABLE table_name参数说明：参数说明：table：要删除的数据表的名称。：要删除的数据表的名称。注意：注意：（1）DROP TABLE 不能删除由不能删除由 FOREIGN KEY 约束引用的表。必须先删除引用的约束引用的表。必须先删除引用的 FOREIGN KEY 约

14、束或引用的表。约束或引用的表。（2）在系统表上不能使用）在系统表上不能使用 DROP TABLE 语句。语句。3.4 管理数据表中记录n当一个数据表的表结构设计完成后，就可以在数据表中录入数据了。可以使用企业管理器或使用SQL语言中的INSERT语句插入数据。3.4 管理数据表中记录 在企业管理器中，打开指定的数据库，右击要录在企业管理器中，打开指定的数据库，右击要录入数据的数据表，从弹出的快捷菜单中选择入数据的数据表，从弹出的快捷菜单中选择“打开打开表表”命令，该命令还有三个子命令。命令，该命令还有三个子命令。“返回所有行返回所有行”：用于显示数据表中的所有记用于显示数据表中的所有记录录“返

15、回首行返回首行”：用于显示前用于显示前N条记录，条记录，N可以自可以自己输入。己输入。“查询查询”：用于查询具体某条记录。用于查询具体某条记录。3.2.2使用使用Transact-SQL语言管理表数据语言管理表数据表数据的添加，可使用表数据的添加，可使用INSERT INTO语句，语法格式：语句，语法格式：INSERT INTO table|view (字段名字段名1,字段名字段名2,) Values(表达式表达式1,表达式表达式2,)参数说明：参数说明： INTO：一个可选的关键字，可以将它用在一个可选的关键字，可以将它用在 INSERT 和目标表之间。和目标表之间。Table|view：要

16、插入数据的表名或视图名。要插入数据的表名或视图名。(表达式表达式1,表达式表达式2,)：与字段名列表相对应的字段与字段名列表相对应的字段的值，的值，字符型和日期型值插入时要加单引号。字符型和日期型值插入时要加单引号。使用使用INSERT语句一次只能插入一行数据语句一次只能插入一行数据。 如果某一字段的值违反了约束或规则，或者它有与数据如果某一字段的值违反了约束或规则，或者它有与数据类型不兼容的值，该语句的操作就会失败。类型不兼容的值，该语句的操作就会失败。例例3-13：假设数据库假设数据库Student_db中已经创建好了课中已经创建好了课程信息数据表程信息数据表C_Info，试将新课程记录（

17、，试将新课程记录（9720044，网络技术与应用，选修，网络技术与应用，选修，3）添加到课程信息数据）添加到课程信息数据表表C_Info中。中。添加新课程记录的语句如下：添加新课程记录的语句如下：INSERT INTO C_InfoVALUES (9720044,网络技术与应用网络技术与应用,选修选修,3,null) 注意：注意：在插入数据时，对于允许为空的列可使用在插入数据时，对于允许为空的列可使用NULL插入空值；插入空值；对于具有默认值的列可使用对于具有默认值的列可使用DEFAULT插入默认值插入默认值 。例例3-14：在在C_Info表中添加一条新记录，课程类别和课程表中添加一条新记录

18、，课程类别和课程说明暂缺。说明暂缺。使用如下语句：使用如下语句：注意：注意：提供值的个数与表中列的个数不一致，必须列出列名；提供值的个数与表中列的个数不一致，必须列出列名；提供值的个数与表中列的个数相同，可省略列名，此时提供值的个数与表中列的个数相同，可省略列名，此时要求值的顺序与定义的字段顺序一致。要求值的顺序与定义的字段顺序一致。C_Info中的课程类别和课程说明列必须允许为中的课程类别和课程说明列必须允许为NULL，因为系统实际插入的数据为：因为系统实际插入的数据为： (9720045,Web开发技术开发技术,null,2,null)。INSERT INTO C_Info(C_NO,C_

19、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 ：更新后的数据值。其值可为表达式(expressi

20、on)的值，或默认值(DEFAULT)，或NULL。where ：要更新数据的记录，只对表中满足该条件的记录进行更新。例例3-15：将数据表将数据表C_Info中的所有学分加中的所有学分加1。语句如下：语句如下： 例例3-16：将数据表将数据表C_Info中课程号为中课程号为“9710011”的学分减的学分减1，课程类型改为，课程类型改为“必修必修”。语句如下：语句如下： UPDATE C_Info SET C_Credit=C_Credit+1UPDATE C_Info SET C_Credit=C_Credit-1，c_type=必修必修WHERE C_NO=97100113 .1.4数据

21、表中当确定有些记录不需要时，就可数据表中当确定有些记录不需要时，就可以将其删除。其语法格式：以将其删除。其语法格式： DELETE FROM table_name | view_name WHERE 参数说明：参数说明：table_name | view_name：要删除行的表：要删除行的表名或视图名。名或视图名。WHERE：要删除的记录（满足条件：要删除的记录（满足条件的记录）。的记录）。例例3-17：在在student表中删除所有学生记录，但表中删除所有学生记录，但保留数据表结构保留数据表结构 。这是无条件的删除记录，使用语句如下：这是无条件的删除记录，使用语句如下： DELETE FRO

22、M student例例3-18：在在C_Info表中删除课程号为表中删除课程号为“9720045”的记录。的记录。 这是有条件的删除记录，使用语句如下：这是有条件的删除记录，使用语句如下： DELETE FROM C_Info WHERE C_NO=9720045使用数据库关系图创建关系使用数据库关系图创建关系 数据库关系图是以图形方式显示部分或全部数数据库关系图是以图形方式显示部分或全部数据库结构，将表和表间关系以及其他对象以图据库结构，将表和表间关系以及其他对象以图形方式表现出来。形方式表现出来。数据库关系图工具并不是只能提供创建关系图数据库关系图工具并不是只能提供创建关系图的功能，还可用

23、来创建和修改表、列、关系、的功能，还可用来创建和修改表、列、关系、键、索引和约束。键、索引和约束。可创建一个或更多的关系图，以显示数据库中可创建一个或更多的关系图，以显示数据库中的部分或全部表、列、键和关系。的部分或全部表、列、键和关系。n数据完整性是指数据的正确性、一致性和有效性，是衡量数据完整性是指数据的正确性、一致性和有效性，是衡量数据库中数据质量好坏的重要标准。数据库中数据质量好坏的重要标准。正确性：指数据的合法性，取值与数据类型要一致。正确性：指数据的合法性，取值与数据类型要一致。有效性：表内数据不相互矛盾，在所定义的范围之内。有效性：表内数据不相互矛盾，在所定义的范围之内。一致性：

24、表间数据不相互矛盾，指数据的关联性不被破一致性：表间数据不相互矛盾，指数据的关联性不被破坏。坏。n当用户用当用户用INSERT、DELETE或或UPDATE语句修改数据库语句修改数据库内容时，数据的完整性就可能会遭到破坏。内容时，数据的完整性就可能会遭到破坏。n为了解决这些问题，保证数据的完整性，为了解决这些问题，保证数据的完整性，SQL Server提提供了实施数据完整性的方法：约束、缺省、规则等供了实施数据完整性的方法：约束、缺省、规则等。 如：字段值不得重重、字段值必须介于如：字段值不得重重、字段值必须介于1到到100之间、甲数之间、甲数据表的据表的A字段值必须参照乙数据表的字段值必须参

25、照乙数据表的B字段等。字段等。3.3.13.3.1数据完整性概述数据完整性概述数据的完整性数据的完整性大致分为大致分为以下几以下几种类型种类型：实体完整性：实体完整性：要求数据表中的每一行在表中是要求数据表中的每一行在表中是唯唯一的实体一的实体，不存在重复的数据行。不存在重复的数据行。 域完整性：域完整性：要求某一列的任何值都是要求某一列的任何值都是有效的有效的数数据类型与取值范围据类型与取值范围引用完整性：也叫参照完整性。引用完整性：也叫参照完整性。指对数据库进指对数据库进行添加、删除、修改数据时，用来维护表间数行添加、删除、修改数据时，用来维护表间数据的一致性。据的一致性。用户定义完整性：

26、用户定义完整性：用于实现用户特殊要求的数用于实现用户特殊要求的数据规则或格式。据规则或格式。 实体完整性的设计与实现实体完整性的设计与实现实体完整性：实体完整性：要求数据表中的每一行在表中是要求数据表中的每一行在表中是唯唯一的实一的实体。体。采用以下方法实现：采用以下方法实现：主键约束（主键约束（ PRIMARY KEY ）：）：是最重要的一种约束，是最重要的一种约束，通过定义主键，唯一地确定表中的每一条记录；定义通过定义主键，唯一地确定表中的每一条记录；定义主键的列不允许为空，且主键的列不允许为空，且text、image 类型的列不能类型的列不能定义为主键。定义为主键。（P18T2_1） 特

27、性：特性： IDENTITY属性：属性：在数据表中创建一个标识列，该列的在数据表中创建一个标识列，该列的数据由系统自动生成，且不会有重复的数据。数据由系统自动生成，且不会有重复的数据。唯一性约束：唯一性约束：唯一性约束：唯一性约束：用来限制不受主键约束用来限制不受主键约束的列上的数据的唯一性，一个表上可定义多个的列上的数据的唯一性，一个表上可定义多个UNIQUE约束。约束。惟一约束与主键约束的主要区别在于：惟一约束与主键约束的主要区别在于：唯一约束用于唯一约束用于非主键列，唯一约束允许非主键列，唯一约束允许NULL值，而主键不允许。值，而主键不允许。域完整性的设计与实现域完整性的设计与实现域完

28、整性域完整性要求表中某一列的任何值都是要求表中某一列的任何值都是有效的有效的数据类型与取值范围。数据类型与取值范围。采用以下方法实现：采用以下方法实现：定义字段的数据类型、设置字段非空；定义字段的数据类型、设置字段非空；检查（检查（CHECK）约束：）约束：用来指定某列可取值的用来指定某列可取值的范围。对输入的数据值进行检查，满足条件的数据可范围。对输入的数据值进行检查，满足条件的数据可以保存在数据表中，对于不满足条件的数据则拒绝接以保存在数据表中，对于不满足条件的数据则拒绝接受；受； （P18T2_1） 定义默认值（定义默认值（DEFAULT）：用于给表中指定列用于给表中指定列赋予一个常量值

29、（默认值）。默认值有两种设置方式，赋予一个常量值（默认值）。默认值有两种设置方式，默认值约束与默认对象。默认值约束与默认对象。规则对象规则对象：是对存储在数据表中的数据值的规定和是对存储在数据表中的数据值的规定和限制，规则的功能与限制，规则的功能与CHECK约束的功能相似，规则约束的功能相似，规则的好处是只创建一次，但可绑定到多个字段上。的好处是只创建一次，但可绑定到多个字段上。默认值默认值DEFAULTu当向表中插入一行数据时，如果用户没有明确给出该当向表中插入一行数据时，如果用户没有明确给出该列的值，列的值，SQL Server自动为该列按如下次序取值：自动为该列按如下次序取值：若该字段定

30、义有默认值，则系统将默认值填入该字段若该字段定义有默认值，则系统将默认值填入该字段若该字段定义没有默认值，但允许空，则填入空值若该字段定义没有默认值，但允许空，则填入空值若该字段定义没有默认值，又不允许空，则报错若该字段定义没有默认值，又不允许空，则报错。u显然，对于非空字段，默认值极为重要。默认值有两种设显然，对于非空字段，默认值极为重要。默认值有两种设置方式，置方式，默认值约束与默认对象。默认值约束与默认对象。u默认值约束其作用范围仅限于一个表，删除表则删除默认默认值约束其作用范围仅限于一个表，删除表则删除默认值约束。值约束。u默认值对象作为数据库对象单独存在于数据库中，需要时默认值对象作

31、为数据库对象单独存在于数据库中，需要时可以绑定到任何一个表中的列，也可以绑定到用户自定义数可以绑定到任何一个表中的列，也可以绑定到用户自定义数据类型上。据类型上。 规则对象规则对象（P21T三三_3、4） u约束是作用于表，离开了表，约束将不再存在。约束是作用于表，离开了表，约束将不再存在。除了约束外，除了约束外，SQL Server还提供了一个与还提供了一个与CHECK约束功能相似数据库对象用以维护数据库的完整性，约束功能相似数据库对象用以维护数据库的完整性，即即规则规则。u规则是对存储在数据表中的数据值的规定和限制。规则是对存储在数据表中的数据值的规定和限制。其作用其作用与与CHECK约束

32、的功能相似约束的功能相似，但规则作为一，但规则作为一个独立的数据库对象存储在数据库中。个独立的数据库对象存储在数据库中。u规则的优点是仅创建一次就可以绑定到数据库的规则的优点是仅创建一次就可以绑定到数据库的多个表的列上，使同一数据库中所有表的不同列共多个表的列上，使同一数据库中所有表的不同列共享。享。参照完整性的设计与实现参照完整性的设计与实现设置参照完整性设置参照完整性是为了保证数据表之间的数据的一是为了保证数据表之间的数据的一致性，通过在两个数据表的主键和外键之间或者唯致性，通过在两个数据表的主键和外键之间或者唯一键和外键之间建立一键和外键之间建立外键（外键（FOREIGN）约束）约束来实现。来实现。（P18T2_2） 外键约束用于与其他表（称为参照表）中的列（称外键约束用于与其他表（称为参照表）中的列（称为参照列）建立连接。为参照列）建立连接。设置了参照的完整性可实现以下功能设置了参照的完整性可实现以下功能：不能在从表中插入主表中不存在的数据值；不能在从表中插入主表中不存在的数据值；如果从表中有对应的数据值，则不能修改主表中如果从表中有对应的数据值，则不能修改主表中对应值，除非设置成级联修改；对应值，除非设置成级联修改；如果从表中有对应的数据值，则不能删除主表中如果从表中有对应的数据值，则不能删除主表中对应值，除非设