4.4 数据表的创建和管理
4.4.1 数据表结构的创建
数据表是数据库中的一个数据对象，主要存储各种类型的数据。创建数据表，首先要规划数据内容，定义数据结构。基本表的创建定义中包含了若干列的定义和若干完整性约束。在SQL Server 2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。
SQL Server 2000提供了两种方法创建数据库表：第一种方法是利用对象资源管理器（Enterprise Manager）创建表，另一种方法是利用Transact-SQL语句中的CREATE命令创建表。
1.CREATE TABLE 语句
语法格式：
CREATE TABLE ＜表名＞
（
＜字段名＞ ＜数据类型＞[列级完整性约束条件]
[,＜字段名＞ ＜数据类型＞[列级完整性约束条件] ...]
[,＜表级完整性约束条件＞]
）
参数说明：
①＜表名＞是所要定义的基本表的名字。一个表可以由一个或多个属性组成。
②＜字段名＞一般取有实际意义的名字。
③＜数据类型＞可以是前面介绍的数据类型。
④在SQL Server 2000中有如下几种完整性约束条件：空值约束（NULL or NOT NULL）、主键约束（PRIMARY KEY CONSTRAINT）、唯一性约束（UNIQUE CONSTRAINT）、检查约束（CHECK CONSTRAINT）、缺省约束（DEFAULT CONSTRAINT）、外部键约束（FOREIGN KEY CONSTRAINT）等。另外还有用规则对象和默认值对象实现约束。因规则的功能可以用CHECK约束实现，默认值的功能也可以用缺省约束实现，故下面对其不赘述。
2.关于创建表时运用约束的说明
（1）空值约束（NULL或NOT NULL）
空值NULL约束决定属性值是否允许为空值（NULL）。NULL表示没有输入任何内容，它不是零和空白。不允许为空值则用NOT NULL表示。
（2）主键约束（PRIMARY KEY CONSTRAINT）
主键约束要求主键属性取值必须唯一，一个表只能包含一个主键约束。如果没有在主键约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该主键约束用CLUSTERED。
主键约束SQL的语法形式如下：
[CONSTRAINT 约束名] PRIMARY KEY [CLUSTERED|NONCLUSTERED]（列名[,…n]）
（3）唯一性约束（UNIQUE CONSTRAINT）
唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下因素：
①使用唯一性约束的字段允许为空值；
②一个表中可以允许有多个唯一性约束；
③可以把唯一性约束定义在多个字段上；
④唯一性约束用于强制在指定字段上创建一个唯一性索引；
⑤在默认情况下，创建的索引类型为非聚簇索引。
创建唯一性约束的SQL语句如下：
[CONSTRAINT 约束名] UNIQUE [CLUSTERED|NONCLUSTERED]（列名[,...n]）
（4）检查约束（CHECK CONSTRAINT）
使用检查约束时，应该注意以下几点：
①一个列级检查约束只能与限制的字段有关，一个表级检查约束只能与限制的表中字段有关；
②一个表中可以定义多个检查约束；
③在每个CREATE TABLE语句中，每个字段只能定义一个检查约束；
④在多个字段上定义检查约束，必须将检查约束定义为表级约束；
⑤检查约束中不能包含子查询。
创建检查约束的SQL语法格式如下：
CONSTRAINT CONSTRAINT_name CHECK [NOT FOR REPLICATION] （logical_expression）
（5）默认约束（DEFAULT CONSTRAINT）
使用默认约束时，应该注意以下几点：
①每个字段只能定义一个默认约束；
②如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；
③不能加入到带有IDENTITY属性或数据类型为timestamp的字段上；
④如果字段定义为用户定义的数据类型，并且该数据类型绑定到这个字段上，则不允许该字段有默认约束。
（6）外部键约束
外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。当使用外部键约束时，应该考虑以下几个因素：
①外部键约束提供了字段参照完整性；
②外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配；
③外部键约束不能自动创建索引，需要用户手动创建；
④一个表中最多可以有31个外部键约束；
⑤在临时表中，不能使用外部键约束；
⑥主键和外部键的数据类型必须严格匹配。
⑦如果需要级联修改和删除，要使用ON UPDATE CASCADE ON DELETE CASCADE
外部键约束SQL的语法形式如下：
[CONSTRAINT 约束名] FOREIGN KEY （外键列名）REFERENCES 参照表（参照列名） [ON UPDATE CASCADE ON DELETE CASCADE]
3.数据表结构创建实例
【例4-1】创建数据库“教学管理”的数据表，包括学生表、课程表、教师表、开课表和选课表，数据表结构如表1-3、表1-4、表1-5、表1-6和表1-7所示，各表的完整性约束如表1-8、表1-9、表1-10、表1-11、表1-12所示。
方法一：使用对象资源管理器创建。
（1）第一步 设置学生表的结构。
①在树状目录中找到要建表的数据库。
②在该数据库上单击鼠标右键，在弹出的快捷菜单中选择“新建”→“表”命令，如图4-4所示。出现创建数据表结构的表设计器窗口，如图4-5所示。
图4-4 新建数据表
图4-5 利用表设计器创建表结构
③表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行设置，其中前3项是必须在建表时给出的。
- 列名：也称为属性名，可以直接输入。
- 数据类型：数据类型是一个下拉列表框，其中包括了所有的系统数据类型和数据库中的用户自定义数据类型。
- 长度：如果选择的数据类型需要长度，则指定长度。
- 允许空：单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。
- 如果该列有默认值，可在列属性的“默认值或绑定”处输入。例如，性别的默认值是“男”。
④设置完成后，单击工具栏上的“保存”按钮。在出现的选择名称对话框中输入表名“学生表”。
⑤单击“确定”按钮退出。
（2）第二步 设置学生表完整性约束。
①展开对象资源管理器的数据库“教学管理”，单击“表”结点，选定学生表，单击右键，在快捷菜单上单击“修改”，出现如图4-5所示的创建表结构窗口。
②设置主键约束（PRIMARY KEY），将光标移到需要设置主键的“学号”字段，单击右键，出现下拉菜单，如图4-6所示。然后选择“设置主键”，“学号”列名左侧出现“钥匙”图标。
图4-6 表设计器下拉快捷菜单
如果要设置多属性作为主键，可以按住Ctrl键，用鼠标左键依次单击要选定的列，选定多列后，单击右键出现下拉菜单，如图4-6所示。然后选择“设置主键”。取消主键设置的方法是，选定主键字段，单击右键出现下拉菜单，然后选择“移除主键”。
设置主键后，系统自动建立一个索引。
③在图4-5所示的表设计器上右击，出现下拉快捷菜单，如图4-6所示。
④选择“索引/键”选项卡，如图4-7所示，系统设置“学生表.学号”属性为主键，因此自动在表中建立一个根据学号值的大小升序排列的索引，主键索引名为“PK_学生表”。
⑤设置检查约束（CHECK），学生表定义了三个CHECK约束，第一个约束是学号，第二个约束是身份证号，第三个约束是移动电话。
单击图4-6所示的表设计器下拉快捷菜单中的“CHECK约束”，出现如图4-8所示的“CHECK约束”属性对话框，在其中新建约束。
图4-7 “索引/键”选项卡
图4-8 “CHECK约束”属性对话框
建立学号的约束：单击“添加”按钮，系统自动给定一个约束名，可在“标识-（名称）”处改名为“CK_学生表_学号”，然后在“常规-表达式”行单击
，出现约束表达式文本框，输入“学号 LIKE 'S[0-9][0-9][0-9][0-9][0-9][0-9]'”。
建立身份证约束：单击“添加”按钮，系统自动给定一个约束名，同上，改名为“CK_学生表_身份证号”，然后在约束表达式文本框中输入“身份证号 LIKE '[0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9][0−9] [0−9][0−9][0−9][0−9]'”。
建立移动电话的约束与上类似。（考虑到后续数据的模拟性，身份证号和移动电话约束可暂不定义）。
（3）第三步 创建课程表结构，设置相应的约束，过程同学生表。
（4）第四步 创建教师表结构，设置相应的约束。
（5）第五步 创建开课表结构，设置相应的约束。
（6）第六步 创建选课表结构，设置相应的约束。
（7）第七步 设置各表之间的关系，建立相应的外键约束。
“教学管理”数据库中有5个表。其中，教师表、课程表和开课表有联系，开课表中的“课号”和“工号”是关于课程表和教师表的外键。同理，学生表、开课表和选课表有联系，选课表中的“学号”和“开课号”是关于学生表和开课表的外键。
设置步骤如下：
①明确开课表和教师表关于工号的参照关系。开课表中的属性工号参照教师表中的属性工号的值，则开课表为外键表，开课表.工号为外键，教师表为主键表，教师表.工号是主键。
②单击图4-6所示表设计器下拉快捷菜单中的“关系”，出现“外键关系”对话框，单击“添加”按钮，系统自动给出一个关系名，然后在“常规-表和列规范”行单击，出现如图4-9所示的教师表“关系”选项卡。在“关系名”处修改关系名称，选择主键表为教师表，外键表为开课表，选择两表的属性为工号。设置情况如图4-9所示。如果两个数据表有引用关系，那么只要在其中的一个表中建立外键约束（FOREIGN KEY），与其有关的另一个数据表中就会出现同名的FOREIGN KEY约束。
打开开课表的属性对话框，单击“关系”选项卡，就可以看到已经建立好的开课表和教师表的外键约束，设置情况如图4-10所示。在“常规-表和列规范”行单击，出现与图4-9一样的选项卡。
图4-9 教师表“关系”选项卡
图4-10 开课表“关系”对话框
③单击“关闭”按钮退回到表设计器。
④同理，选择课程表，建立课程表和开课表的外键约束关系。
至此，开课表的两个外键全部建好。
⑤选择学生表，建立学生表和选课表的外键约束关系。
⑥选择开课表，建立开课表和选课表的外键约束关系。
至此，选课表的两个外键也全部建好。
方法二：使用SQL命令创建。
创建表并包含完整性约束定义，同时定义各个约束名。考虑到后续数据的模拟性，身份证号、移动电话的检查约束不进行定义，实际中需要时可参考学号、工号等进行定义。
①创建学生表的语句如下：
CREATE TABLE 学生表 （ 学号 CHAR（7） NOT NULL, 身份证号 CHAR（18） NOT NULL, 姓名 CHAR（8） NOT NULL, 性别 CHAR（2） DEFAULT '男', 移动电话 CHAR（11）, 籍贯 VARCHAR（10）, 专业 VARCHAR（20） NOT NULL, 所在院系 VARCHAR（20） NOT NULL, 累计学分 INT, CONSTRAINT PK_学生表_学号 PRIMARY KEY（学号）, CONSTRAINT CK_学生表_学号 CHECK（学号 LIKE 'S[0-9][0-9][0-9][0-9][0-9][0-9]'） ）
说明：约束名可以不定义；约束可以直接跟在列后。例如：
CREATE TABLE 学生表 （ 学号CHAR（7） NOT NULL PRIMARY KEY（学号） CHECK（学号LIKE 'S）0-9）[0-9][0-9][0-9] [0-9][0-9]'）, 身份证号CHAR（18） NOT NULL, 姓名CHAR（8） NOT NULL, 性别CHAR（10） DEFAULT '男', 移动电话CHAR（11）, 籍贯VARCHAR（10）, 专业VARCHAR（20） NOT NULL, 所在院系VARCHAR（20） NOT NULL, 累计学分INT ）
②创建课程表的语句如下：
CREATE TABLE 课程表 （ 课号 CHAR（6） NOT NULL, 课名 VARCHAR（30） NOT NULL, 学分 INT CHECK（学分＞=1 and 学分＜=5）, 教材名称 VARCHAR（30）, 编著者 CHAR（8）, 出版社 VARCHAR（20）, 版号 VARCHAR（20）, 定价 money, CONSTRAINT PK_课程表_课号 PRIMARY KEY（课号）, CONSTRAINT CK_课程表_课号 CHECK（课号 LIKE 'C[0-9][0-9][0-9][0-9][0-9]'） ）
③创建教师表的语句如下：
CREATE TABLE 教师表 （ 工号 CHAR（6） NOT NULL, 身份证号 CHAR（18） NOT NULL, 姓名 CHAR（8） NOT NULL, 性别 CHAR（2） DEFAULT '男', 移动电话 CHAR（11）, 籍贯 VARCHAR（10）, 所在院系 VARCHAR（20） NOT NULL, 职称 CHAR（6）, 负责人 CHAR（6）, CONSTRAINT PK_教师表_工号 PRIMARY KEY（工号）, CONSTRAINT CK_教师表_工号 CHECK（工号 LIKE 'T[0-9][0-9][0-9][0-9][0-9]'） ）
④创建开课表的语句如下：
CREATE TABLE 开课表 （ 开课号 CHAR（6） NOT NULL, 课号 CHAR（6） NOT NULL, 工号 CHAR（6） NOT NULL, 开课地点 CHAR（6）, 开课学年 CHAR（9）, 开课学期 INT , 开课周数 INT DEFAULT 17, 开课时间 VARCHAR（20）, 限选人数 INT, 已选人数 INT, CONSTRAINT PK_开课表_开课号 PRIMARY KEY（开课号）, CONSTRAINT FK_开课表_工号 FOREIGN KEY（工号） REFERENCES 教师表（工号） ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT FK_开课表_课号 FOREIGN KEY（课号） REFERENCES 课程表（课号） ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT CK_开课表_开课号 CHECK（开课号 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'）, CONSTRAINT CK_开课表_工号 CHECK（工号 LIKE 'T[0-9][0-9][0-9][0-9] [0-9]'）, CONSTRAINT CK_开课表_课号 CHECK（课号 LIKE 'C[0-9][0-9][0-9][0-9][0-9]'） ）
⑤创建选课表的语句如下：
CREATE TABLE 选课表 （ 学号 CHAR（7） NOT NULL, 开课号 CHAR（6） NOT NULL, 成绩 INT CHECK（成绩＞=0 and 成绩＜=100）, CONSTRAINT PK_选课表_学号_开课号 PRIMARY KEY（学号,开课号）, CONSTRAINT FK_选课表_学号 FOREIGN KEY（学号） REFERENCES 学生表（学号） ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT FK_选课表_开课号 FOREIGN KEY（开课号） REFERENCES 开课表（开课号） ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT CK_选课表_学号 CHECK（学号 LIKE 'S[0-9][0-9][0-9][0-9][0-9][0-9]'）, CONSTRAINT CK_选课表_开课号 CHECK（开课号 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'） ）
4.4.2 数据表结构的管理
1.修改数据表结构
表结构创建以后，在使用的过程中经常会发现原来创建的表可能存在结构、约束等方面的问题，在这种情况下，需要对原表进行修改。如果用创建一个新表的方法替换原表，将造成表中数据的丢失，而通过修改表则可以在保留表中原有数据的基础上修改表结构，打开、关闭或删除已有约束，或增加新的约束等。
修改表结构有两种方法：一种是利用对象资源管理器，另一种是使用SQL命令。
利用对象资源管理器修改表结构的过程如图4-11所示。单击“修改”命令，将弹出表设计器。
图4-11 修改学生表结构
使用SQL命令修改表，语法格式如下：
ALTER TABLE ＜表名＞
ADD＜新列名＞＜数据类型＞[完整性约束]
|ALTER COLUMN 列名 新类型
|DROP COLUMN 列名
|ADD PRIMARY KEY（列名[,...]）
|ADD FOREIGN KEY（列名） REFERENCES 表名（列名）
|ADD CONSTRAINT 约束名....
|DROP CONSTRAINT 约束名
图4-12 修改学生表某些属性长度
【例4-2】将教学管理数据库中学生表的“性别”属性的长度改为2。
方法一：使用对象资源管理器，如图4-12所示。
方法二：使用SQL命令，程序如下。
USE 教学管理 --打开教学管理数据库
GO
ALTER TABLE 学生表
ALTER COLUMN 性别 CHAR（2） --修改属性列“性别”
【例4-3】向教学管理数据库中的学生表增加“入学时间”属性，其数据类型为日期型，增加“年龄”属性，其类型为整型，取值为13～70。
方法一：使用对象资源管理器，如图4-13所示。
然后，单击右键，选择“CHECK约束”，出现图4-14所示对话框，建立年龄的约束：单击“添加”按钮，系统自动给定一个约束名，可在“标识-（名称）”处改名为“CK_学生表_年龄”，然后在“常规-表达式”行单击，出现约束表达式文本框，输入“年龄＞=13 AND年龄＜70”。
方法二：使用SQL命令。
USE 教学管理
GO
ALTER TABLE 学生表
ADD 入学时间 DATETIME,
年龄 INT CONSTRAINT CK_学生表_年龄CHECK（年龄＞=13 AND年龄＜70）
图4-13 向学生表增加新属性列
图4-14 选项卡中输入约束名和约束表达式
注意：新增加的属性列不能定义为“NOT NULL”；新增加的属性可以带有主键约束、参照约束、CHECK约束和默认值。
【例4-4】删除学生表中的属性列年龄。
方法一：使用对象资源管理器。
①展开对象资源管理器的数据库结点，过程如图4-11所示。选择学生表，单击“修改”命令，将弹出表设计器。
②首先删除“年龄”属性的CHECK约束。在设计器窗口单击右键，在快捷菜单上选择“CHECK约束”，选择CK_学生表_年龄约束，如图4-14所示，单击“删除”按钮即可删除。
③选定要删除的“年龄”属性列，如图4-15所示，单击右键，再单击快捷菜单中的“删除”按钮即可删除该属性列。
图4-15 删除数据表的属性列
方法二：使用SQL命令。
USE 教学管理 GO ALTER TABLE 学生表 DROP CONSTRAINT CK_学生表_年龄 --删除约束 ALTER TABLE 学生表 DROP COLUMN年龄 --删除去除了约束的属性
或者
ALTER TABLE 学生表
DROP CONSTRAINT CK_学生表_年龄, --删除约束
COLUMN年龄 --删除去除了约束的属性
注意：跟属性列有关的约束和索引删除后，指定的属性才能删除。
2.数据表结构的删除
可以用对象资源管理器或SQL语句删除基本表。
SQL命令的一般格式如下：
DROP TABLE ＜表名＞
方法一：使用对象资源管理器。
①选中要删除的数据表，单击右键，在快捷菜单（如图4-10所示）上选择“删除”。
②在“除去对象”对话框中，单击“全部除去”。
方法二：使用SQL命令。
【例4-5】创建一个教室信息表，然后删除它。
USE 教学管理 GO --创建教室表 CREATE TABLE 教室表 （ 编号 INT IDENTITY, 名称 VARCHAR（20） NOT NULL, 位置 CHAR（6） NULL, ） GO --删除教室表 DROP TABLE 教室表
注意：当删除一个表时，表的定义和表中的所有数据，以及该表的索引、权限设置、约束等均被自动删除，与该表相绑定的规则和默认对象失去与它的绑定关系；但是，使用DROP TABLE语句不能删除系统表和被FOREIGN KEY约束所参照的用户表，必须先删除引用的外键约束或引用的表。