第5章 表的创建与管理及其索引 本章导读： 在SQL Server环境中，表是存储数据和操作数据的逻辑结构，是数据库中存储的基本对象，对数据库的操作大多数依赖于某个或某些特定的表进行的，因而对数据库的管理和操作，实质是对表的管理和操作。 知识要点： 数据表概述 数据类型 创建表 修改表 删除表 数据操作.

Presentation on theme: "第5章 表的创建与管理及其索引 本章导读： 在SQL Server环境中，表是存储数据和操作数据的逻辑结构，是数据库中存储的基本对象，对数据库的操作大多数依赖于某个或某些特定的表进行的，因而对数据库的管理和操作，实质是对表的管理和操作。 知识要点： 数据表概述 数据类型 创建表 修改表 删除表 数据操作."— Presentation transcript:

1 第5章 表的创建与管理及其索引 本章导读： 在SQL Server环境中，表是存储数据和操作数据的逻辑结构，是数据库中存储的基本对象，对数据库的操作大多数依赖于某个或某些特定的表进行的，因而对数据库的管理和操作，实质是对表的管理和操作。 知识要点： 数据表概述 数据类型 创建表 修改表 删除表 数据操作 索引

2 5.1 数据库表概述 SQL Server的每个数据库最多提供20亿个表，表是数据库中具体组织和存储数据的对象，每个表都代表着一个实体集或者实体集之间的联系。表由行和列组成，最多可以有1024列，每行对应实体集的一个个体，称为记录，每列代表一个属性，称为字段。 数据表概述 建表步骤 完整性约束类型

3 表类型 在SQL server中，表分为系统表和用户表。 1.系统表 系统表是SQL Server 系统的数据字典，记录了服务器所有活动信息，一般以sys开头。任何用户都不应直接修改系统表，也不允许直接访问表中的信息，如要访问其内容，最好通过系统存储过程或系统函数。 2.用户表 用户表是由用户自行建立的数据表，用于存储用户数据，又分为永久表和临时表两种。其中，永久表通常存储在用户数据库中，除非删除永久表，否则永久表及其数据将永久存在。临时表存储在tempdb数据库中，当不再使用时，会被系统自动删除。临时表又分为本地临时表和全局临时表。本地临时表名以#开头，仅对当前数据库用户有效，一旦断开连接，就自动删除。全局临时表名以##开头，对所有数据库用户有效，所有用户断开连接后才自动删除。

4 建表步骤 设计表时，要事先确定需要什么样的表，各表有哪些数据，表中各字段的数据类型及其属性。建表一般经过定义表结构、设置完整性约束、输入数据记录等步骤，其中设置完整性约束既可以在定义表结构时进行，也可以在表结构定义完成之后进行。 （1）定义表结构：确定表名和各列名及列属性（数据类型、数据长度、是否允许空等）； （2）设置完整性约束：限制列数据值输入的范围，保证数据的完整性； （3）输入数据记录：表结构完成之后，就可以向表中输入数据了。

5 完整性约束类型 为了维护数据的正确性、有效性和相容性，防止错误信息的输入和输出，SQL Server提供了实体完整性、参照完整性和用户自定义完整性等实现关系数据库的完整性约束。 1．实体完整性 实体完整性：又称行完整性，要求表中有一个主键，其值不能取空值且能唯一地标识每一行。主要包括Primary Key约束、Unique约束、列Identity属性、唯一索引等。 （1）主键约束（Primary Key）：限制主键约束列中不能输入重复的列值，组成Primary Key约束的各列值都不能为空值（Null）。一个表中只允许定义一个主键约束，且image和text类型的字段不能指定为主关键字，主键约束自动建立主键聚集索引。 主键约束的语法格式如下： [constraint <约束名>] primary key [clustered|nonclustered][(<列名>[,…16])] （2）惟一约束（Unique）：限制非主键约束列中不能输入重复的列值，组成Unique约束的各列值可以为空。一个表允许定义多个唯一约束，唯一约束自动建立唯一非聚集索引，因为Unique 约束优先唯一索引。 唯一约束的语法格式如下： [constraint <约束名>] unique [nonclustered|clustered][(<列名>[,…16])] （3）标识列（Identity）：标识列自动生成能唯一标识表中每一行数据的序列值（默认初始值为1，增量值为1），每个表都允许有一个标识列，该列的数据类型必须是decimal、numeric、int、smallint或tinyint，且不允许为空值，也不能修改和人为输入值，也不能有默认值。 标识列的语法格式如下： Identity [(初始值,增量值)]

6 完整性约束类型 2．参照完整性 参照完整性：又称引用完整性，用于保证两个相关表的数据一致性，主要通过定义主表（被参照表）Primary Key或Unique约束和从表（参照表）Foreign Key约束来实现。 （1）外键约束（Foreign Key）：根据主表主键的数据集合来限制从表外键的数据相容性，作为外键的值要么是空值，要么是主表主键存在的值。相应地，包含外键的表也称为外键表，主表也称主键表或引用表，常用外键和主键来强制参照完整性，以维护两个表之间的关系。 外键约束的语法格式如下： [constraint <约束名>] foreign key[(<列名>[,…16])] references <主表名>(<列名>[,…16]) 当创建外键约束时，必须遵循以下原则： 主表中（引用）列名必须是主表中的候选键（通常为主键）。 外键约束分为表级约束或列级约束。创建为列级约束时，只能包括一列；创建为表级约束时，可以包括一列或多列。 从表外键的列数和主表主键的列数必须相同，并且对应列的数据类型也必须相同，但是外键（被引用）列名与主键（引用）列名不必相同。 创建外键约束时，如果没有指定外键（被引用）列名，那么默认为外键（被引用）列名与主键定义的（引用）列名同名。

7 完整性约束类型 3．用户自定义的完整性 在SQL Server 2000中，用户自定义的完整性是指域完整性（也称列完整性），用于保证列数据输入的有效性和合理性。其主要包括Default约束、Check约束、Not Null约束等。 （1）默认值约束（Default）：当输入数据时若没有为某列提供值，则将所定义的默认值提供给该列。默认值可以是常量，也可以是表达式，如getdate()返回系统日期。 默认约束的语法格式如下： [constraint <约束名>] default <默认值> [for <列名>] （2）检查约束（Check）：通过限制列的取值范围来强制域的完整性，这与外键约束中的数据相容性规则相似，不过外键约束是依据主表主键的数据集合，而检查约束则是利用逻辑表达式来限制列上可接受的数据范围，而非基于其它表的数据集合。不能在text、ntext、image列上定义check约束。 检查约束的语法格式如下： [constraint <约束名>] check(<列名条件表达式>) （3）非空值约束（Not Null）：限制字段不接受Null值，即当对表进行插入（Insert）操作时，必须给出确定的值。空值是指未填写、未知、不可用或将在以后添加的数据，并不等价于空白（空字符串）或数值0。列默认属性为空（Null）。 非空值约束的语法格式如下： <列名> Not Null

8 5.2 数据类型 数据类型用来表现数据的特征，它决定了数据存储格式、存储长度、取值范围（含数据精度和小数位数），以及可参与的运算法则。在SQL Server 系统中，数据类型分为系统数据类型和用户自定义数据类型。 系统数据类型 用户自定义数据类型

9 系统数据类型 在SQL Server 2000中，系统数据类型包括字符类型、数值类型、二进制类型、货币类型和日期/时间数据类型等。而数值类型又分为整数类型、精确小数类型和 浮点类型。 1．字符串数据类型 字符串数据类型是用单引号定界的字符串，包括char、varchar和text数据类型，如表5-1所示。char字符串若小于定义长度，则填入空格字符，其长度不变。 表5-1 字符串数据类型 数据类型 长度 取值范围 char(n) 长度不变，最多8000个字符 固定长度的非unicode字符 varchar(n) 长度可变，最多8000个字符 可变长度的非unicode字符 text 长度可变，最多 个字符

10 5.2.1 系统数据类型 2．Unicode字符串数据类型
系统数据类型 2．Unicode字符串数据类型 Unicode字符串数据类型采用双字节存储每个字符。Unicode数据类型包括nchar，nvarchar和ntext类型，如表5-2所示。nchar字符串小于定义长度时，以0x00填入。 表5-2 Unicode字符串数据类型 数据类型 长度 取值范围 nchar(n) 长度不变，最多8000个字符 固定长度的unicode字符 nvarchar(n) 长度可变，最多8000个字符 可变长度的unicode字符 ntext 长度可变，最多 个字符

11 系统数据类型 3．整型数据类型 整型数据类型是指不含小数的数值数据，包括bit，tinyint，smallint，int，bigint类型，他们之间的区别主要在于存储的数值范围不同，如表5-3所示。 表5-3 整型数据类型 数据类型 长度 取值范围 bit 1位 1或0 tinyint 1字节 0~255 smallint 2字节 -32768~32767（-215~215-1） int 4字节 ~ （-231~231-1） bigint 8字节 ~ （-263~263-1）

12 系统数据类型 4．浮点数据类型 浮点数据类型是一种近似小数的数值数据，通常采用科学计数法近似存储十进制小数，包括real和float类型，如表5-6所示。 表5-4 浮点数据类型 数据类型 长度 取值范围 float 8字节 -1.79E+308~-1.79E+308 real 4字节 -3.40E+38~-3.40E+38

13 系统数据类型 5．精确小数数据类型 精确小数类型是指包含小数的位数确定的数值数据，包括decimal，numeric类型，如表5-5所示。 表5-5 精确小数数据类型 数据类型长度取值范围 Decimal(p,s) 精度1~9位时，占5字节 精度10~19位时，占9字节 精度20~28位时，占13字节 精度29~38位时，占17字节 -238+1~238-1 p（精度）表示小数点两边的总位数 s（刻度）表示小数点右边的位数 1≤p≤38，0≤s≤p numeric(p,s) 同decimal-238+1~238-1

14 系统数据类型 6．二进制数据类 二进制数据类型是指用16进制（0x开头）表示的数据，包括binary，varbinary和image类型，如表5-4所示。 表5-6 二进制数据类型 数据类型 长度 取值范围 binary(n) n+4，长度不变，最多8000个字节 固定长度的二进制数据 varbinary(n) 实际长度+4，长度可变，最多8000个字符 可定长度的二进制数据 image 长度可变，最多 个字符 一般说来，最好使用binary和varbinary存储二进制数据，除非存储字节数超过了8K，才使用image类型。二进制数据指Word文档、Excel电子表格、图像等文件。

15 系统数据类型 7．货币数据类型 货币数据类型是用于表示货币和现金值的数值数据，精确到小数点后4位。货币数据类型包括money，smallmoney类型，如表5-7所示。 表5-7 货币数据类型 数据类型 长度 取值范围 money 8字节 ~ smallmoney 4字节 ~

16 系统数据类型 8．日期和时间数据类型 日期和时间数据类型是指表示日期和时间的数据类型。日期和时间数据类型包括datetime，smalldatetime类型，如表5-8所示。 表5-8 日期和时间数据类型 数据类型 长度 取值范围 datetime 8字节（精确到百分之三秒） :00:00~ :59:59 smalldatetime 4字节（精确到分钟） :00:00~ :59:59 注意：在SQL Server中，word文档、excel电子表格以及.bmp、.gif、.jpg等图像格式的文件用text、ntext和image数据类型来存储。

17 系统数据类型 9．其它数据类型 其它数据类型用于表示一些特殊的数据，包括cursor，sql_variant、table、timestamp和uniqueidentifier类型，如表5-9所示。 表5-9 其它数据类型 数据类型 长度 取值范围 cursor 长度不变，最多8000个字节 保存查询结果集 sql_variant 长度可变，最多8000个字符 存储非text，ntext，image，timestamp数据 table 长度可变，最多 个字符 存储对表和视图处理后的结果集 timestamp 8字节 时间戳数据类型，产生的唯一数据类型 uniqueidentifier 16字节 存储计算机网络和cpu全球唯一标识的数据类型

18 用户自定义数据类型 SQL server提供了两种创建用户自定义数据类型的方法：使用企业管理器创建用户自定义数据类型和使用系统存储过程创建用户自定义数据类型。 1.使用企业管理器创建用户自定义数据类型 【例5-1】 创建用户自定义数据类型“zipcode”，数据类型来源char(6)，且允许空值。 （1）在企业管理器中，展开要创建用户自定义数据类型的数据库（jxgl），右击“用户自定义的数据类型”节点，弹出快捷菜单，选择“新建用户定义数据类型”命令，如图5-1所示。 （2）单击，弹出“用户定义数据类型属性”对话框，在其中输入数据类型的名称“zipcode”，数据类型和长度char(6)，是否允许空值（允许），规则和默认值等，如图5-2所示。

19 图5-1 “企业管理”对话框① 图5-2 “用户定义数据类型属性”对话框
用户自定义数据类型 图5-1 “企业管理”对话框① 图5-2 “用户定义数据类型属性”对话框

20 用户自定义数据类型 （3）单击“确定”按钮返回企业管理，如图5-3所示，在此窗口中，选择刚创建的用户自定义数据类型“zipcode”可以将其删除。 图5-3 “企业管理”对话框② 图5-4 “例5-2”运行结果

21 用户自定义数据类型 2．使用系统存储过程创建用户自定义数据类型 （1）使用系统存储过程创建用户自定义数据类型的语法格式如下： sp_addtype 用户自定义数据类型名称,系统数据类型[,’null’|’not null’] （2）使用系统存储过程删除用户自定义数据类型的语法格式如下： sp_droptype 用户自定义数据类型名称 【例5-2】 在“jxgl”数据库中创建一个用户自定义数据类型名称为“birthday”，数据类型来源于系统数据类型“smalldatetime”，不允许为空值。 use jxgl exec sp_addtype 'birthday','smalldatetime','not null' 运行结果如图5-4所示。 图5-4 “例5-2”运行结果

22 5.3 创建数据库表 SQL server 提供了两种创建表的方法：使用企业管理器设计数据库表和使用T-SQL语句设计数据表。
5.3 创建数据库表 SQL server 提供了两种创建表的方法：使用企业管理器设计数据库表和使用T-SQL语句设计数据表。 数据库表逻辑结构 使用企业管理器设计数据库表 使用T-SQL语句设计数据表

23 5.3.1 数据库表逻辑结构 数据库“jxgl”中存在如下几个表，各表的逻辑结构分别定义如下： 字段名 数据类型 备注 班级号
数据库表逻辑结构 数据库“jxgl”中存在如下几个表，各表的逻辑结构分别定义如下： 表5-10 班级信息表 班级 字段名 数据类型 备注 班级号 char(6) 主键 班级名称 varchar(20) 唯一约束 班级人数 tinyint 学制 char(1) 默认值为4 招生性质 char(4)

24 5.3.1 数据库表逻辑结构 字段名 数据类型 备注 成绩编码 int 标识，主键 学号 char(8) 非空、外键 课程号 char(2)
数据库表逻辑结构 表5-11 选修信息表 选修 字段名 数据类型 备注 成绩编码 int 标识，主键 学号 char(8) 非空、外键 课程号 char(2) 成绩 numeric(4,1) 100≥成绩≥0 text

25 5.3.1 数据库表逻辑结构 字段名 数据类型 备注 学号 char(8) 非空、主键 姓名 char(6) 非空 性别 char(2)
数据库表逻辑结构 表5-12 学生信息表 学生 字段名 数据类型 备注 学号 char(8) 非空、主键 姓名 char(6) 非空 性别 char(2) 默认值男 出生日期 datetime 总分 int 籍贯 char(4) 默认值安徽 text 照片 image

26 5.3.1 数据库表逻辑结构 字段名 数据类型 备注 工号 char(6) 非空、主键 姓名 非空、 性别 char(2) 默认值男
数据库表逻辑结构 表5-13 教师信息表 教师 字段名 数据类型 备注 工号 char(6) 非空、主键 姓名 非空、 性别 char(2) 默认值男 出生日期 datetime 工作日期 职称 基本工资 int 婚否 bit 默认值0

27 5.3.1 数据库表逻辑结构 字段名 数据类型 备注 课程号 char(2) 主键 课程名称 varchar(20) 唯一约束 课程类型
数据库表逻辑结构 表5-14 课程信息表 课程 字段名 数据类型 备注 课程号 char(2) 主键 课程名称 varchar(20) 唯一约束 课程类型 char(4) 学时 smallint 学时≥0 学分 tinyint 学分≥0 text

28 5.3.1 数据库表逻辑结构 字段名 数据类型 备注 工号 char(6) 非空、外键 课程号 char(2) 班级号 课酬 int
数据库表逻辑结构 表5-15 授课信息表 授课 字段名 数据类型 备注 工号 char(6) 非空、外键 课程号 char(2) 班级号 课酬 int 3000≥课酬≥0 学期 char(1) 评价 text 注意：本书程序如果特别说明均以以上表内容作为基本表结构，表中数据见第6章。

29 使用企业管理器设计数据库表 【例5-3】 用企业管理器设计表“班级”，并设置班级号为主键，设置学制默认值为4。 操作步骤如下： （1）在企业管理器中选择并展开要创建表的数据库（jxgl），选择“表”节点，右击之弹出快捷菜单，选择“新建表”，如图5-5所示。 （2）单击后释放，弹出“新建表”对话框，并在“新建表”对话框中设置各列的属性：列名、数据类型、长度、精度、小数位数、是否允许空、默认值等，如图5-6所示。

30 图5-5 “企业管理”对话框 图5-6 “新建表”对话框
使用企业管理器设计数据库表 图5-5 “企业管理”对话框 图5-6 “新建表”对话框

31 图5-7 “选择名称”对话框 图5-8 “企业管理器”对话框
使用企业管理器设计数据库表 （3）在编辑完各列属性后，单击“保存”按钮，弹出“选择名称”对话框，输入“班级”名称，如图5-7所示。 （4）单击“确定”按钮，返回“新建表”对话框，单击该对话框右上角的关闭按钮，返回“企业管理器”对话框，拖动右框格垂直滚动条，可看到“班级”表，如图5-8所示。 图5-7 “选择名称”对话框 图5-8 “企业管理器”对话框

32 使用企业管理器设计数据库表 注意： ①在“表设计器”对话框中，选中列名“班级号”后，在该对话框中任意位置右击，弹出快捷菜单，选择“设置主键”即可将“班级号”设置为主键。“班级号”左侧有一把钥匙标记即表示已经设置为主键。 ②在“新建表”对话框中，右击列名如“班级号”所在行的任何位置，弹出快捷菜单，选择“插入列”可以在当前列前位置增加列；选择“删除列”可以删除当前列；另外列的上下位置也可以调整：选择要移动的列，然后拖动该列到要释放的位置。

33 使用T-SQL语句设计数据表 在T-SQL语句中，创建表可用create table语句创建表，其完整语法格式如下： create table [数据库名.[所有者].| 所有者.] <表名> ( {<列定义说明> | [<列名> as <计算机列表达式>] | [<表约束说明>]} |[{primary key|unique}] [,…n]) [on {文件组名|default}] /*指定存储表的文件组*/ [textimage_on{文件组名|default}] /*指定存储text、ntext和image类型数据的文件组*/ 功能：在指定的数据库的指定用户上创建一个表，缺省值为当前数据库的现有用户。

34 使用T-SQL语句设计数据表 （1）<列定义说明>：用来定义一列，列的定义如下： <列定义说明>::= {列名 列数据类型} /*指定列名、数据类型*/ |[ not null | null] /*指定列空值非空值约束*/ [ collate <排序规则名>] /*指定排序规则*/ [ [ default 常量表达式] /*指定默认值*/ | [identity[(初始值,增量)[not for replication]]]/*指定列为标识列*/ ] [rowguidcol] /*指定列为全局标识符列*/ [<列约束说明>][,…n] /*指定列的约束*/

35 使用T-SQL语句设计数据表 （4）<表约束说明>：定义表约束，约束格式如下： <表约束说明>::= [constraint <约束名>] { [{ primary key|unique} /*定义主键，唯一性约束*/ {(列名[asc|desc][,…16])} /*定义索引升序或降序的列名*/ [clustered|nonclustered] /*定义聚集索引，非聚集索引*/ [with fillfactor= fillfactor] /*唯一性约束为非聚集*/ [on {文件组|default }]] |[foreign key[(列名[,…16])] /*定义外键约束*/ references 参照表名 [(参照列名[,…n])] /*外键约束引用的表及其列*/ [on delete {cascade|on action}] /*删除行是否级联删除子表相关行*/ [on update {cascade|on action}] /*更新行是否级联更新子表相关行*/ [not for replication]] |check(逻辑表达式) [not for replication]}

36 使用T-SQL语句设计数据表 （5）<列约束说明>：定义列约束说明，约束格式如下： <列约束说明>：：= [constraint 约束名] { [null|not null] |[{primary key|unique}[clustered|nonclustered][with fillfactor= fillfactor] [on {文件组名 |default }]] |[foreign key refrences 参照表名 [(参照列名)] [on delete {cascade|on action}] [on update {cascade|on action}] [not for replication]] |check(逻辑表达式) [not for replication]} （6）on {文件组名|default}：用来指定存储表的文件组。没有指定on参数值或缺省时均为默认文件组。 （7）textimage_on{文件组名|default}：用来指定存储text、ntext和image类型数据的文件组，没有指定textimage_on参数值时或缺省时均与表存储在同一文件组中。

37 使用T-SQL语句设计数据表 【例5-4】 用T-SQL语句设计表“授课”，但不设置其相关约束。 use jxgl create table 授课 (工号 char(6),课程号 char(6),班级号 char(6),课酬 int,学期 char(1)) 【例5-5】用T-SQL语句设计表“教师”，仅设置列“工号”为主键，不设置其它约束。 create table 教师 (工号 char(6) constraint Pk_教师_工号 primary key ,姓名 char(6) ,性别 char(2) ,出生日期 datetime ,工作日期 datetime ,职称 char(6) ,基本工资 int ,婚否 bit) 注意：代码中的第三行中的子句constraint Pk_教师_工号可以省略时，省略时会产生一个随机分配的约束标志名。

38 使用T-SQL语句设计数据表 【例5-6】 用T-SQL语句设计表“学生”，仅设置学号为非空、主键，姓名非空，性别默认值为“男”，但不设置籍贯的默认约束。 use jxgl create table 学生( 学号 char(8) not null constraint pk_学生 primary key, 姓名 char(6) not null, 性别 char(2) constraint df_学生_性别 default '男', 出生日期 datetime, 总分 int, 籍贯 char(4), 备注 text, 照片 image)

39 使用T-SQL语句设计数据表 在查询分析器左侧的“对象浏览器”窗口中展开数据库“jxgl”用户表，可以查看“学生”表的信息，如图5-9所示。当然在企业管理器中也能查看该表相关属性。 图5-9 “学生”表信息 图5-10 “选修”表信息

40 使用T-SQL语句设计数据表 【例5-7】 用T-SQL语句设计表“选修”，并设置成绩编码为标识列，学号为非空、外键约束于“学生”表“学号”列，学号和课程号共同建立主键，成绩检查约束0~100之间。 use jxgl create table 选修( 成绩编码 int identity(1,1) ,学号 char(8) not null constraint fk_学号 references 学生(学号) ,课程号 char(2) not null ,constraint pk_选修 primary key(学号,课程号) ,成绩 tinyint check(成绩>=0 and 成绩<=100) ,备注 text) 在查询分析器左侧的“对象浏览器”窗口中展开数据库“jxgl”用户表，可以查看“选修”表的信息，如图5-10所示。当然在企业管理器中也能查看该表相关属性。

41 5.4 修改数据库表 修改表不仅能修改表的结构，如增加和删除列，修改现有列的属性，还能增加、删除、启动和暂停约束。但是修改表时，不能破坏表原有的数据完整性，如不能为有主关键字列的表再增加一个主关键字列，不能为有空值的列设置主键约束等。 修改表，SQL Server 同样提供了两种的方法：即使用企业管理器设计数据库表和使用T-SQL语句修改数据表。 利用企业管理修改表 利用T-SQL语句修改表

42 图5-11 “设计表”对话框 图5-12 “属性”对话框的“关系”界面①
利用企业管理修改表 在企业管理器中展开控制台目录，选择要修改的表，如表“班级”，打开“设计表”对话框，即可修改表的结构和列的属性等，使用方法参照创建表的方法。 【例5-8】用企业管理修改表“授课”，将其列“班级号”设置为外键，对应关联于表“班级”的列“班级号”。 （1）打开表“授课”的“设计表”对话框，选择列“班级号”，右击弹出快捷菜单，选择“关系”命令，如图5-11所示。 （2）单击释放后，弹出“属性”对话框的“关系”界面①，如图5-12所示。 图5-11 “设计表”对话框 图5-12 “属性”对话框的“关系”界面①

43 5.4.1 利用企业管理修改表 （1）单击“新建”按钮，弹出“属性”对话框的“关系”界面②，如图5-13所示。
利用企业管理修改表 （1）单击“新建”按钮，弹出“属性”对话框的“关系”界面②，如图5-13所示。 （2）分别选择主键表“班级”和外键表“授课”的列“班级号”，如图5-14所示。 图5-13 “属性”对话框的“关系”界面② 图5-14 “属性”对话框的“关系”界面③

44 利用企业管理修改表 注意： ①如果勾选“创建中检查现有数据”复选框，则表示检查两表之间现有数据是否符合参照完整性，如果符合参照完整性就准许建立关系，否则不允许建立； ②如果勾选“对复制强制关系”复选框，则表示复制两表时也要遵循参照完整性； ③如果勾选“对insert和update强制关系”，则表示插入或更新时要符合参照完整性，否则拒绝插入或更新操作； ④在③基础上，如果继续勾选“级联更新相关的字段”，则表示更新主键表的键值时，自动更新外键表的关联列值； ⑤在③基础上，如果继续勾选“级联删除相关的记录”，则表示删除主键表的记录时，自动删除外键表的关联记录。

45 图5-15 “保存”提示对话框 图5-16 “保存”对话框
利用企业管理修改表 （5）单击“关闭”按钮，返回“设计表”对话框，单击“设计表”右上角的“关闭”按钮，弹出保存提示对话框，如图5-15所示。 （6）单击“是”按钮，弹出保存方式对话框，如图5-16所示，单击“是”按钮即可。 图5-15 “保存”提示对话框 图5-16 “保存”对话框

46 利用企业管理修改表 注意： （1）如果要设置列“课酬”的check约束，参照步骤（1）打开“设计表”对话框，选中列“课酬”，右击弹出快捷菜单，单击“check约束”命令，弹出“属性”对话框的“check约束”界面①，如图5-17所示。 图5-17 “属性”对话框的“check约束”界面①

47 图5-18 “属性”对话框的“check约束”界面②
利用企业管理修改表 （2）单击“新建”按钮，弹出“属性”对话框的“check约束”界面②，在约束表达式文本框中输入“课酬>=0 and 课酬<=3000”，如图5-18所示。 图5-18 “属性”对话框的“check约束”界面②

48 利用企业管理修改表 （3）后续步骤重复（5）~（7）。在查询分析器的“对象浏览器中”可以查看相关信息。 注意：右击表名，如“班级”表，弹出快捷菜单，选择“重命名”命令还可以更改表名。

49 5.4.2 利用T-SQL语句修改表 alter table <表名>{
[[with { check | nocheck }]add{ <列定义> | <列名> as <计算列表达式>}[,…n]] | [[with { check | nocheck }]add{ <列约束>[,…n] }] | [drop{ [constraint]<约束名> }[,…n]] | [drop{column<列名>}[,…n]] | [alter column <列名>{ 新数据类型[(小数精度[,小数范围])] [collate<排序规则>] [null | not null] | { add | drop }rowguidcol } ] |[{ check | nocheck }constraint{ all | 约束名[,…n] }] |[{ enable | disable }trigger{ all | 触发器名[,..n] }]

50 利用T-SQL语句修改表 说明： （1）add{<列定义>|<列名> as <计算列定义>}[,…n]：添加一个新列或计算列表达式，其中with {check|nocheck}表示是否检查表中现有数据的约束规则； （2）add{<列约束>[,…n]}：添加列约束； （3）drop{[constraint]<约束名>}[,…n]：删除指定的约束； （4）drop{column<列名>}[,…n]：删除指定的列名； （5）alter column <列名> {新数据类型[(小数精度[,小数范围])] }：更改指定列的数据类型，小数精度，小数范围，其中collate<排序规则>用于重新指定列的排序规则，{add|drop}rowguidcol用于添加或删除列上rowguidcol属性； （6）{check|nocheck}constraint{all|约束名[,…n]}：启用或禁止外键或check约束； （7）{enable|disable}trigger{all|触发器名[,..n]}：启用或禁止指定触发器。 完整的T-SQL看起来比较复杂，不容易理解，但正如前文所说，修改表也就是对表结构的增、删、改操作，所以为了讲解方便，可以将修改表的T-SQL语句简化成以下几种形式：

51 利用T-SQL语句修改表 将修改表的T-SQL语句可以简化成以下几种形式： 1．增加列 增加列同时还可以附加列约束定义信息，使用alter table命令语句添加列的基本语法格式如下： alter table <表名> add <列名> <数据类型和长度> [列约束] 【例5-9】 向“班级”表中添加一列，列名为“班主任”并具有唯一性约束。 use jxgl alter table 班级 add 班主任 varchar(6) constraint Uk_班级_班主任 unique 【例5-10】 向“选修”表中添加一列，列名为“等级”，数据类型为varchar(6)，默认约束是“合格”。 alter table 选修 add 等级 varchar(6)constraint df_选修_等级 default '合格' 【例5-11】 为“学生”表中增加一列，列名为“邮政编码”，并附加一个check约束，限制输入到列的数据范围为6位0~9之间数字且以“23”开头。 alter table 学生 add 邮政编码 char(6) constraint Ck_学生_邮政编码 check(邮政编码 like '23[0-9][0-9][0-9]')

52 利用T-SQL语句修改表 2．增加约束 增加约束时，如果新增约束与表中原有数据有冲突时，将导致异常，终止命令执行。如果想忽略新增约束对原有数据的检查，可以使用使用with nocheck选项，使新增约束只对以后数据起作用。使用alter table命令语句添加列约束的基本语法格式如下： alter table <表名> [with nocheck|check] add constraint <约束名> <约束定义> 【例5-12】 为“教师”表中的列名为“性别”的列设置默认约束，默认值是“男”。 use jxgl alter table 教师 add constraint df_教师_性别 default '男' for 性别 【例5-13】 为“班级”表中的列名为“班级人数”的列添加一个check约束，但不检查表中现有数据，限制输入到列的数据范围为40~60之间。 alter table 班级 with nocheck add constraint Ck_班级_班级人数 check(班级人数>=40 and 班级人数<=60) 【例5-14】 为“选修”表中列名为“课程号”的列添加一个“外键约束”于课程表。 alter table 选修 add constraint Fk_选修_课程号 foreign key(课程号) references 课程(课程号)

53 利用T-SQL语句修改表 3．修改列 使用alter table命令修改列时，只能修改列的数据类型及其列值是否为空等属性，使用alter table命令语句修改表中列定义的基本语法格式如下： alter table <表名> alter column <列名> <新数据类型和长度> [(<精度>[,<小数位数>])] 【例5-15】 将“班级”表中列名为“班级名称”的列的数据类型修改为char(20)。 use jxgl alter table 班级 alter column 班级名称 char(20) null 注意：如将一个原来允许为空的列修改为不允许为空（not null），必须确保该列中没有存放空值且该列上没有建立索引。

54 利用T-SQL语句修改表 4．删除约束 使用alter table命令语句删除列约束定义的基本语法格式如下： alter table <表名> drop constraint <约束名> 【例5-16】 将“选修”表中主键约束（pk_选修）删除。 use jxgl alter table 选修 drop constraint pk_选修 【例5-17】 将“选修”表中默认值约束（df_选修_等级）删除。 alter table 选修 drop constraint df_选修_等级

55 利用T-SQL语句修改表 5．删除列 有约束的列或者与其它列有关联的列不能直接删除，删除列之前必须先删除约束或列关联信息。使用alter table命令语句删除列定义的基本语法格式如下： alter table <表名> drop column <列名>[,…n] 【例5-18】 将“选修”表中列名为“等级”的列删除。 use jxgl alter table 选修 drop column 等级 【例5-19】 将“学生”表中的列“邮政编码”删除（“邮政编码”的约束没有删除）。 alter table学生 drop column 邮政编码 注意：列的附加约束没有删除之前，无法删除该列。

56 利用T-SQL语句修改表 6．启用和暂停约束 使用check和nocheck选项可以启动或暂停SQL Server的某个或全部约束对新数据的约束检查，但不适用主键约束和唯一约束。 【例5-20】 暂停“选修”表中的外键约束（Fk_选修_课程号）。 use jxgl alter table 选修 nocheck constraint Fk_选修_课程号 【例5-21】 暂停“学生”表中的check约束和默认约束。 go alter table 学生 nocheck constraint all 注意：使用系统存储过程sp_rename也可以修改表名或表列名，或使用带参数’column’的系统存储过程sp_rename修改列名。 【例5-22】 使用系统存储过程修改表名“学生”为“student”。 exec sp_rename 学生,student 【例5-23】 使用系统存储过程将学生的“姓名”列修改为“学生姓名”。 exec sp_rename '学生.姓名','学生姓名','column'

57 5.5 删除数据表 使用企业管理删除数据表 使用T-SQL语句删除数据表

58 图5-19 选择“班级”表对话框 图5-20 “除去对象”对话框
使用企业管理删除数据表 在企业管理器中展开控制台目录，直到数据库“jxgl”的“表”节点，然后选择要删除的表，如表“班级”，如图5-19所示。然后执行“操作”→“删除”命令后，弹出“除去对象”对话框，如图5-20所示，单击“全部除去”按钮，就可以删除表。 图5-19 选择“班级”表对话框 图5-20 “除去对象”对话框

59 使用T-SQL语句删除数据表 也可以使用T-SQL删除数据表，其语法格式如下： 格式：drop table <表名> 【例5-24】 删除“班级”表。 use jxgl drop table 班级

60 5.6 索引 在SQL Server系统中，影响数据库性能的因素很多，其中，设计良好的索引可以显著提高数据库的使用性能。索引的作用类似于书的目录，使得用户对数据库的查询无需逐行扫描，而是通过遍历索引树结构的方法查找所需行的存储位置，并通过查询结果提取所需的数据行。 索引概述 索引建立

61 索引概述 索引的优势是加快访问速度和实施行的唯一性。但并非索引越多越好，这是因为当频繁地增加、删除和更新数据时，除了对表中数据进行处理外，还会占用额外开销来维护索引而引起磁盘存储空间不足。通常情况下，只有数据量大、更新较少而查询较多的数据列，才需要在表上创建该列的索引。 1．索引的概念 索引是对数据表中的一列或多列的值进行排序而创建的一种结构分散的数据库对象。索引中包含索引列的键值（搜索码），以及映射到相应数据行的指针。索引依据用户指定顺序（升序或降序）对键值进行排序，从而使程序无需浏览整个表，就可以快速找到所需的数据。索引提供了逻辑顺序，可以提高数据的查询响应速度，另外索引也能加快连接和select语句中group by 与order by子句的检索速度。

62 2．索引的类型 在SQL Server中，根据索引的存储结构来分，可以将索引分为聚集索引和非聚集索引两大类；根据索引的列值是否允许重复值的性质来分，可以将索引分为主键索引、唯一性索引和普通索引三大类；根据索引的引用列数多少来分，可以将索引分为单列索引和复合列索引。

63 索引建立 创建索引的过程实质就是确立索引名、索引类型及其针对的表名及其列名。创建索引既可以使用企业管理器，也可以使用Transact-SQL语句。 1．使用企业管理器创建索引 利用企业管理器建立索引有多种方法，既可以使用表设计器建立索引，也可以使用工具向导建立索引，还可以使用索引管理器建立索引。这里我们介绍最常用的方法，即利用表设计器建立索引。

64 索引建立 【例5-25】 为“班级”表的列“班级名称”建立唯一索引。 操作步骤如下： （1）打开“班级”表的表设计器，右击“班级名称”列，弹出快捷菜单，选择“索引/键”命令，如图5-21所示。

65 图5-23 “属性”对话框② 图5-24 “属性”对话框③
索引建立 （2）单击“新建”按钮，自动建立一个名为“IX_班级”索引，如图5-24所示，等待用户进一步修改。 图5-23 “属性”对话框② 图5-24 “属性”对话框③

66 索引建立 （3）修改索引名为“Uk_班级名称”，列名选择“班级名称”，如图5-25所示。表示建立以“班级名称”唯一约束，同时自动建立相应的唯一索引。 （4）如果选择单选“索引”项目，意味着创建“唯一”索引，但不包括唯一约束，如图5-26所示。

67 5.6.2 索引建立 【例5-26】创建“课程”表，并建立“课程号”主键，“课程名称”唯一约束。 create table 课程(
索引建立 【例5-26】创建“课程”表，并建立“课程号”主键，“课程名称”唯一约束。 create table 课程( 课程号 char(2) constraint pk_课程_课程号 primary key, 课程名称 varchar(20) constraint uk_课程_课程名称 unique, 课程类型 char(4), 学时 smallint, 学分 tinyint, 备注 text)

68 索引建立 2．通过企业管理器创建索引 利用企业管理器建立索引有多种方法，既可以使用表设计器建立索引，也可以使用工具向导建立索引，还可以使用索引管理器建立索引。这里我们介绍最常用的方法，即利用表设计器建立索引。 【例5-31】 为“班级”表的列“班级名称”建立唯一索引。 操作步骤如下： （1）打开“班级”表的表设计器，右击“班级名称”列，弹出快捷菜单，选择“索引/键”命令，如图5-44所示。 （2）单击“索引/键”命令后，弹出“属性”对话框，如图5-45所示，显示由主键约束（“例5-3”建表时创建）自动建立的“pk_班级”主键索引信息。

69 运行程序后，用户可以在企业管理器中通过表设计器查看索引信息。操作步骤如下：
（1）展开“企业管理器”控制台目录，右击“课程”表，弹出快捷菜单，选择“设计表”命令，如图5-27所示。 （2）单击“设计表”命令，弹出“设计表”对话框，右击列名“课程号”，弹出快捷菜单，选择“索引/键”命令，如图5-28所示。

70 （3）单击“索引/键”命令后，弹出“属性”对话框①，如图5-29所示，显示系统自动建立的“pk_课程_课程号”主键索引信息。单击“选择的索引”下拉列表框选择“uk_课程_课程名称”后，弹出“属性”对话框②，如图5-30所示，显示系统自动建立的“uk_课程_课程名称”唯一索引信息。

71 【例5-27】 利用系统存储过程查看“课程”表索引信息。 use jxgl go exec sp_helpindex ‘课程’
运行结果如图5-31所示。 图5-31 “例5-26”运行结果

72 图5-44 “设计表”对话框 图5-45 “属性”对话框①
索引建立 2．使用T-SQL语句创建索引 可以使用T-SQL语句的create index 命令的语法形式如下： create [unique][clustered|nonclustered] index <索引名> on {表名|视图名}(列名[asc|desc][,…n]) [with <索引选项>[,…16]] [on 文件组名] 功能：在指定表的指定列上创建索引。 说明： （1）unique：指明创建唯一索引，默认为普通索引； （2）clustered|nonclustered：可选项目，其中，clustered表示创建聚集索引，nonclustered表示创建非聚集索引，默认为非聚集索引； （3）index <索引名>：必选项目，指明新创建的索引名称； （4）on{表名|视图名}：必选项目，指明创建索引的引用表名或视图名； （5）(列名[asc|desc][,…16])：必选项目，指明创建索引的引用列名，其中，asc表示升序，desc表示降序，默认为升序； （6）[with <索引选项>[,…n]]：指明索引选项设置，其格式如下： 图5-44 “设计表”对话框 图5-45 “属性”对话框①

73 【例5-27】 为“选修”表按照“成绩”列降序创建一个普通索引。
create index ix_选修_成绩 on 选修(成绩 desc)

74 索引删除 （1）使用企业管理器删除索引，可参照5.6.2节的图5-29，选择要删除的索引，然后单击“删除”按钮即可。 （2）使用T-SQL语句的drop index命令删除索引的语法格式如下： 格式：drop index <表名.索引名> 功能：删除指定表指定名称的索引。 注意：当删除聚集索引时，所有非聚集索引将被重建，不能删除系统表中的索引。

75 索引维护-----1．维护统计信息 随着更新操作不断的执行，数据存储空间会变得非常凌乱，妨碍了数据并行扫描，降低了数据查询性能，为了有效提高索引查询性能，必须维护统计信息和对索引进行维护。 1．维护统计信息 SQL Server 2005提供了以下检查和更新统计信息的语句。 （1）使用dbcc show_statistics命令显示指定索引的统计信息。 【例5-29】 显示“课程”表中“pk_课程_课程号”索引的统计信息。 use jxgl go dbcc show_statistics(课程,pk_课程_课程号) （2）使用update statistics命令更新表或视图中的索引的统计信息。 【例5-30】 更新“课程”表中所有索引的统计信息。 update statistics课程

76 5.8.4 索引维护-----1．维护统计信息 （3）使用sp_updatestats命令更新当前数据库中所有用户定义的表索引统计信息。
索引维护-----1．维护统计信息 （3）使用sp_updatestats命令更新当前数据库中所有用户定义的表索引统计信息。 【例5-31】 更新“jxgl”数据库中所有用户表的索引统计信息。 use jxgl go excute sp_updatestats （4）使用dbcc showconfig命令显示指定的表或视图的数据和索引的碎片信息。 【例5-32】 更新“课程”表中“pk_课程_课程号”索引的碎片统计信息。 dbcc showconfig(课程,pk_课程_课程号)

77 索引维护-----2．重建和整理索引 删除表上的聚集索引，随后再重新建立聚集索引，将对数据重新组织，能够使数据页填满，并且除去数据碎片。但是会导致重建非聚集索引，从而增加了开支。为此SQL SERVER提供了以下方法。 （1）使用dbcc indexdefrag 命令清理碎片，但不必重建每个索引。 【例5-33】 清理“课程”表的“pk_课程_课程号”索引碎片。 use jxgl go dbcc indexdefrag(jxgl,课程,pk_课程_课程号) （2）使用with drop_existing子句优化索引。 【例5-34】 重新创建“选修”表的“成绩”列索引。 create index ix_成绩 on 选修(成绩)with drop_existing fillfactor = 90

78 本章小结 表是数据库中的基本对象，是用户组织、管理和存储数据的逻辑结构，本章主要介绍数据库表的创建和管理。创建和管理数据库表主要包括表名及列属性的定义和维护，其中完整性约束的定义相对比较复杂。创建和管理数据库表既可以通过企业管理器执行，也可以通过T-SQL语句执行，其中，T-SQL语句在执行和维护数据表的完整性约束方面显得尤为简洁便利。