数据库实验2 创建数据库表与索引Word文件下载.docx

数据库实验2 创建数据库表与索引Word文件下载.docx

《数据库实验2 创建数据库表与索引Word文件下载.docx》由会员分享，可在线阅读，更多相关《数据库实验2 创建数据库表与索引Word文件下载.docx（13页珍藏版）》请在冰豆网上搜索。

CREATETABLE

[database_name_[owner].]table_name

（

{<

column_dafinition>

|column_nameAScomputed_column_expression

|<

table_constraint>

}[,…n]

）

[ON{filegroup|DEFAULT}]

[TEXTIMAGE_ON{filegroup|DEFAULT}]

:

={column_namedate_type}

[DEFAULTconstant_expression]

|[IDENTITY[（seed,increment）]

]

[ROWGUIDCOL]

column_constraint>

][,…n]

例1创建一个表名为student的表

USEtest

createtablestudent

snochar（5）NOTNULL,

snamechar（8）NULL,

sexchar

（2）NULL,

sbirthsmalldatetimeNULl,

sdeptchar（20）NULL

首先创建数据库test

CREATEDATABASEtest

ONPRIMARY

（NAME=student1_dat,

FILENAME='

e:

\jsj1\test\student1_dat.mdf'

SIZE=10,

MAXSIZE=50,

FILEGROWTH=20%）

LOGON（

NAME=student_log,

\jsj1\test\student1_log.ldf'

SIZE=5MB,

MAXSIZE=25MB,FILEGROWTH=5MB）

例2创建一个名为tal的表，此表中有3列，第一列pid定义为主键，并且自动增长。

第2列name默认值为Unknown，第3列定义一个约束（日期不能大于输入当天的日期），插入一条记录并进行查询：

createtabletal

pidintidentity（1,1）primarykey,

namechar（10）DEFAULT（'

unknow'

）,

birthdayDATETIMECHECK（birthday<

getdate（））

inserttal（[name],birthday）values（'

lam'

'

1977-03-20'

select*fromtal

2）利用Transact_SQL语句修改表

使用Transact_SQL语句可以在更大范围内修改表格的定义，可以修改的内容包括：

修改、增加、删除列或约束，使约束和触发器无效等

使用Transact_SQL修改表格定义的语法如下：

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name

{new_date_type[NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL

|}

|ADD

{[<

column_definition>

TABLE_CONSTRAINT>

}[,…N]

|DROP

{[CONSTRAINT]constraint_name

|COLUMNcolumn

|{CHECK|NOCHECK}CONSTRAINT

{ALL|constraint_name[,…n]

|{ENABLE|DISABLE}TRIGGER

{ALL|trigger_name[,…n]}

例3使用Transact_SQL语句对学生表进行各种修改

（1）向表中添加新的字段：

在学生表中添加一个“班级”字段，数据类型为字符型。

ALTERTABLEstudentadd班级char

（2）删除表中的旧列：

将学生表中“Sdept”字段删除。

ALTERTABLEstudentDROPcolumnSdept

（3）更改表以添加具有约束的列

给学生表增加“grade”字段并加上CHECK约束，让其不大于100。

ALTERTABLEstudentADDgradeint

Constraintchnamecheck（grade<

100）

Execsp_helpchname

给学生表添加“birthday”字段，并且这个日期不能在录入当天的日期后。

ALTERTABLEstudentADDbirthdayDATETIMENULL

Constraintch_birthdaycheck（birthday<

（4）添加具有默认值的可以为空的：

在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。

ALTERTABLEstudentADDmatriculationdaysmalldatetimeNULL

Constraintadddatefltdefaultgetdate（）

3）、利用Transact_SQL语句删除表

可以用Transact_SQL语句DROP删除表的定义及表中所有的数据、索引、触发器、约束和权限规范。

如果要删除表，语法为：

DROPTABLEtable_name

例4删除当前连接的数据库中的一个名为tal的表格和删除pubs数据库中的example的表格。

DROPTABLEtal

DROPTABLEpubs.dbo.example

注意：

删除表格的权利只属于表格的拥有者，并且不能转移，不能使用DROPTABLE语句删除系统表

3.实验内容与步骤

使用企业管理器和Transact-SQL语句创建“课程信息表”、“教学任务表”、“教师档案表”、“学生档案表”、“学生选课表”、“学生成绩表”6张表，各表的逻辑结构如P36表3.1、表3.2、表3.3、表3.4、表3.5、表3.6。

且各表之间的逻辑依赖关系如图3.1。

并对关系表进行管理。

具体内容如下。

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

使用Transact-SQL命令创建“学生档案表”、“课程信息表”、“教学任务表”。

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

使用SQL企业管理器创建“学生成绩表”、“教师档案表”、“学生选课表”。

3.3修改表的结构

3.3.1修改数据类型

1）将“学生档案表”中的“姓名”字段的数据类型改为varchar（8）。

代码：

altertable学生档案

altercolumn姓名varchar（8）

结果：

2）将“课程信息表”中的“课程名称”字段的数据类型改为varchar（50）。

altertable课程信息表

altercolumn课程名称varchar（50）

命令已成功完成。

3.3.2增加一个新字段

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

altertable学生成绩表

add总成绩realdefaultnull

2）为“教学任务表”增加一个新字段“教师姓名”，类型为varchar（8），默认是空值。

altertable教学任务表

add教师姓名varchar（8）defaultnull

3.3.3增加约束条件

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

addconstraintshuliang1check（平时成绩>

=0and平时成绩<

=100）

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

addconstraintshuliang2check（年度>

2000）

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

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

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

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

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

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

在“教师档案表”中的“教师ID”（降序）、“姓名”（升序）、“职称”（升序）三个字段上建立一个普通索引。

createindex姓名索引on学生档案（姓名）

createuniqueindex学号索引on学生档案（学号）

createindex多列索引on学生档案（学号,姓名,籍贯desc）

createclusteredindex学号索引on学生成绩表（学号）

createuniqueindex任课教师ID索引on教学任务表（任课教师ID）

createindex多列索引1on教师档案表（教师IDdesc,姓名,职称）

结果：

如图所示

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

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

3.5.2使用SQL企业管理器在“课程信息表”上创建“课程ID”唯一索引。

3.6创建数据库表之间的关系图

创建上述6张表之间的关系图。

一、完成拓展实验

二、

4．思考与总结