数据库实验2：表的创建与管理

1 实验2： 创建数据库表与索引

----表的创建与管理

1.实验目的：

掌握表和索引的建立方法及表结构的修改方法，了解表关系建立的条件和作用，并实践数据库管理系统提供的数据完整性功能，加深对数据完整性的理解。要求建立表、修改表结构、建立索引、进行数据完整性描述。

2.实验要求：

⑴在已经建立的数据库中创建表

运用Microsoft SQL Server2000企业管理器和Transact-SQL语句两种方法建立“课程信息表” 、“教学任务表” 、“教师档案表” 、“学生档案表” 、“学生选课表” 、“学生成绩表” 。

⑵了解表与表之间的逻辑依赖关系

学会在Microsoft SQL Server2000企业管理器中，创建表之间的关联关系，通过外键的拖曳建立表的基本依赖关系，从而直白的表达整个数据库的表间联系，但是，注意：过程中，主键与外键对应的源数据表。

⑶修改表结构

将学生档案表“姓名”字段的数据类型修改为varchar（8）。为学生成绩表增减一个新的字段——“总成绩”，类型为real，默认是空值。学生成绩表“平时成绩”字段的取值为小于100的正数。

⑷建立索引

2 运用Transact—SQL语句建立以下索引：

◆在学生档案表的“姓名”字段上建立普通升序索引；

◆在学生档案表的“学号”字段上建立唯一索引；

◆在学生档案表的“学号”字段上建立聚集索引；

◆在学生档案表的“学号”（升序）、“姓名”（升序）和“籍贯”（升序）三个字段上建立一个普通索引；

◆运用SQL企业管理器在学生档案表中的“籍贯”字段上创建普通升序索引。

3.实验环境与实验器材：计算机，网络环境，投影设备。

实验相关软件：Window xp、SQL Server 2000。

4.实验内容与步骤

使用企业管理器和Transact-SQL语句创建“课程信息表”、“教学任务表”、“教师档案表”、“学生档案表”、“学生选课表”、“学生成绩表”6张表，6张表的具体设计内容：“课程信息表”包括课程名称、课程类别、学分和学时等；“教师档案表”和“学生档案表”记录了教师和学生的基本情况；“教学任务表”包括课程ID、任课教师ID、学期和年度；“学生选课表”给出了学生与课程之间的关系；“学生成绩表”则给出了学生成绩的计算方法。

4.1 在“教学管理”数据库中使用Transact-SQL命令创建表

使用Transact-SQL命令创建“学生档案表”，包含字段“学号” 、“姓名” 、“性别” 、“出生日期” 、“籍贯” 、“专业” 、“班级”。

第1步：从“开始”菜单中打开“查询分析器”，连接进入“查询”窗口。

3 第2步：在“查询”窗口中，创建指定数据文件和事务日志文件的“教学管理”数据库，要注意这两个文件路径所需的文件夹应先创建好，不然会有错误提示。

⑴ 在“教学管理”数据库中使用Transact-SQL命令创建表

①创建“学生档案表”

第1步：在“查询分析器”窗口的上部，选择“教学管理”数据库。并在查询框中输入建立“学生档案表”的命令：

create table 学生档案

(学号 char(8)primary key

Check（学号like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),

姓名 varchar(8) not null,

性别 char(8),

出生日期 datetime check(出生日期>='1970'),

籍贯 char(8),

专业 char(8),

班级 char(8))

第2步：执行该命令，出现如下窗口：

4 第3步：刷新“SQL查询分析器”界面左边的“对象浏览器”中的“教学管理”数据库，这时在“教学管理”数据库的“用户表”中可以看到新建的“学生档案表”

②创建“教师档案表”

第1步：输入建立“教师档案表”的命令，如下：

create table 教师档案表

(任课教师ID char(8)primary key

check(任课教师ID

like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),

姓名 varchar(8) not null,

性别 char(8),

check(性别='M'or 性别='F'),

职称 varchar(8),

单位 varchar(20))

第2步：执行，结果如下：

第3步：刷新，查看表：

5 ③创建“教学任务表”

第1步：输入建立“教学任务表”的命令，执行

6

第2步：刷新，查看表

④创建“课程信息表”

第1步：输入建立“课程信息表”的命令，执行：

7

第2步：刷新，查看表

⑤创建“学生选课表”

第1步：输入建立“学生选课表”的命令，执行

8

第2步：刷新，查看表

⑥创建“学生成绩表”

第1步：输入建立“学生成绩表”的命令，执行

9

第2步：刷新，查看表

3.2 在“教学管理”数据库中使用企业管理器创建表

使用SQL企业管理器创建“教师档案表”，如图示：

10

3.3 修改表的结构

3.3.1 修改数据类型

1）将“学生档案表”中的“姓名”字段的数据类型改为varchar(8)，则命令如下：

alter table 学生档案

alter column 姓名 varchar(8)

2）将“课程信息表”中的“课程名称”字段的数据类型改为varchar(50)，则命令如下：

alter table 课程信息

11 alter column 课程名称 varchar(50)

第1步：打开“SQL查询分析器”并连接，输入对表结构修改的命令，如图：

第2步：执行，查看结果：

3.3.2增加一个新字段

1）为“学生成绩表”增加一个新字段“总成绩”，类型为real，默认是空值，命令如下：

alter table 学生成绩

add 总成绩 real default null

2）为“教学任务表”增加一个新字段“教师姓名”，类型为

12 varchar(8)，默认是空值命令如下：

alter table 教学任务

add 教师姓名 varchar(8)default null

第1步：打开“SQL查询分析器”并连接，输入对表结构修改的命令，如图：

第2步：执行，查看结果：

3.3.3增加约束条件

1）为“学生成绩表”增加一个约束，“平时成绩”字段的取值为大于等于0小于等于100的正数命令如下：

alter table 学生成绩

add constraint shuliang1 check(平时成绩>=0 and 平时成绩<=100)

2）为“教学任务表”中的“年度”添加约束条件，使得年度大于2000年，命令如下：

13 alter table 教学任务

add constraint shuliang2 check(年度>2000)

第1步：打开“SQL查询分析器”并连接，输入对表结构修改的命令，如图：

第2步：执行，查看结果：

3.4 使用Transact-SQL命令创建索引

3.4.1 在“学生档案表”的“姓名”字段上建立普通升序索引；

3.4.2 在“学生档案表”的“学号”字段上建立唯一索引；

3.4.3 在“学生档案表”的“学号”（升序）、“姓名”（升序）、和“籍贯”(降序)三个字段上建立普通索引；

3.4.4 在“学生成绩表”的“学号”字段上建立聚集索引；

3.4.5 在“教学任务表”的“任课教师ID”字段上创建名为“任课教师ID索引”的唯一索引；在“教师档案表”中的“教师ID”（降

14 序）、“姓名”（升序）、“职称”（升序）三个字段上建立一个普通索引。

第1步：打开“SQL查询分析器”并连接，输入创建索引的命令，代码如下：

create index 姓名索引 on 学生档案(姓名)

create unique index 学号索引 on 学生档案(学号)

create index 多列索引 on 学生档案(学号,姓名,籍贯desc)

create clustered index 学号索引 on学生成绩(学号)

create unique index 任课教师ID索引 on 教学任务(任课教师ID)

create index 多列索引 on 教师档案(学号教师IDdesc,姓名,职称)

第2步：执行，查看结果：

第3步：索引创建成功，在对象浏览器中的“教学管理”数据库下，可以看到刚创建的“姓名索引”“学号索引”“多列索引”，如图：

3.5 使用SQL企业管理器创建索引

3.5.1 使用SQL企业管理器在“学生档案表”中的“籍贯”字段上创建普通升序索引。

第1步：打开“企业管理器”， 在“控制台根目录”中，，点开“教学管理”数据库中的表，如图：

15 第2步：在“学生档案”上单击右键，选择“所有任务” →“管理索引”，弹出如下窗口：

16

第3步：单击“新建”，出现“新建索引”对话框，输入索引名“籍贯索引”，选择索引列“籍贯”，如图：