数据库系统概论课程实验指导书V12.docx

数据库系统概论课程实验指导书V12.docx

《数据库系统概论课程实验指导书V12.docx》由会员分享，可在线阅读，更多相关《数据库系统概论课程实验指导书V12.docx（65页珍藏版）》请在冰豆网上搜索。

数据库系统概论课程实验指导书V12

《数据库系统概论》

课程实验指导书

江涛

淮南师范学院计算机信息工程系

2011.09

目录………………………………………………………………………………………

实验一数据库的创建和管理…………………………………………………

实验二表的创建、管理及数据操作……………………………………………

实验三SQL数据查询……………………………………………………………

实验四SQL数据操纵…………………………………………………………

实验五数据库保护……………………………………………………

实验六数据库应用开发……………………………………………………

附加实验事务并发控制操作……………………………………………………

实验一数据库的创建和管理

一、实验目的

⏹熟悉SQLServer2000实验环境的设置、学会独立使用该系统；

⏹熟悉SQLServer2000中企业管理器和查询分析器两个常用管理工具；

⏹熟练掌握SQLServerEnterpriseManager创建和管理数据库；

⏹熟练掌握在查询分析器中使用T-SQL语句创建和管理数据库

二、实验内容

1.熟悉MicrosoftSQLServer/EnterpriseManager

2.熟悉MicrosoftSQL查询分析器

3.利用企业管理器创建和管理数据库

（1）创建数据库

（2）删除数据库

4.在查询分析器中使用T-SQL语句创建和管理数据库

（1）创建数据库

（2）修改数据库

（3）删除数据库

三、实验步骤

1、启动服务器管理器

首先通过【开始】→【程序】→【MicrosoftSQLServers】→【服务器管理器】，在系统托盘中会同时出现SQL服务的图标，单击【开始/继续】前的小三角，此时，在系统托盘中的图标上会有一个绿色箭头，表示服务已经启动。

2、SQLServer2000企业管理器

首先通过【开始】→【程序】→【MicrosoftSQLServers】→【企业管理器】进入企业管理器界面。

在企业管理器的左边列出的是控制台管理目录，依次是数据库类型（SQLServers）、数据库群组名（SQLServers组）、数据库服务器主机名，默认连接为本机）和该数据库服务器提供的所有的管理工具（包括数据库、数据转换服务、管理、复制、安全性、支持服务等）。

在“数据库”选项中列出的是当前服务器中所有的数据库名（master、model、msdb、Northwind等），其中master、model、tempdb等为系统数据库，记录其他数据库的公有属性和某些隐藏信息，用户不要直接对系统数据库（也就是master库）中的表和数据项进行修改和删除，否则将会给数据库系统造成不可挽回的损失。

3、新建数据库

在服务器组的“数据库”上，右击，弹出快捷菜单，选择“新建数据库…”。

在弹出的对话框中常规（General）页框中，要求用户输入数据库名称以及排序规则名称。

点击数据文件（DataFiles）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称，如图

点击事务日志（TransactionLog）页框，该页框用来设置事务日志文件信息，如图：

单击“确定”按钮，则开始创建新的数据库。

4、表操作

新建表在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表…”选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：

列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。

然后根据提示进行设置。

增加、删除和修改字段在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。

创建、删除和修改约束

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项，然后根据提示对主键进行添加、删除和修改操作。

查看表格

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“设计表”选项，可以查看表格的定义

在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择“打开表—返回所有行”选项，查看表格中的数据

删除表格在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“除去表”选项，则会出现除去对象对话框。

单击“全部删除”按钮，即可删除表。

5、查询分析器（QueryAnalyzer）

首先通过【开始】→【程序】→【MicrosoftSQLServers】→【查询分析器】进入查询分析器界面。

窗口的顶部是菜单和工具栏，在工具栏中有一个数据库列表框，列出主机中所有的数据库，用户可直接在列表框中选择进行操作的数据库名（如：

Northwind）。

主窗口的左边是对象浏览器（包括对象和模板两页），右边是Transact-SQL程序编辑器。

查询分析器是SQLServer2000的另一个非常主要的客户端工具，查询分析器用Transact-SQL程序对数据库系统进行分析和管理，企业管理器用图形界面和向导对数据库进行操作。

对于那些更高级的查询分析、批处理创建删除，使用查询分析器就比较灵活。

用户可直接在编辑器中输入查询分析语句“select*fromcategories”，其中categories是Northwind数据库中的一张表，用户可以在左边对象浏览器的数据库用户表中找到。

然后点击工具栏上的绿色三角形按钮进行执行。

查询结果将出现在编辑器的下面，如下图：

查询分析器的有一实用功能—程序模板，选择左边的模板类型，右边编辑框将自动生成相应的Transact-SQL程序。

修改程序中的参数就执行相应的功能。

例如要创建一个数据库，就在左边选择CreateDatabase模板类型，再选择详细类型CreateDatabaseBasicTemplate，右边出现一段程序，其功能是判断主机中是否存在数据库N，如果存在的话，就将其删除，然后再创建数据库N。

用户也可以通过程序模板来进一步学习Transact-SQL语言。

6、在查询分析器使用T-SQL语句管理数据库

⑴利用T-SQL语句创建数据库

打开查询分析器，输入如下语句：

CREATEDATABASEMYDB

ON

（NAME='MY_DB_DATA',

FILENAME='D:

\PROGRAMFILES\MicrosoftSQLServer\MSSQL\data\MY_DB_DATA.MDF',

SIZE=5,MAXSIZE=15,FILEGROWTH=2）

LOGON

（NAME='MY_DB_LOG',

FILENAME='D:

\PROGRAMFILES\MicrosoftSQLServer\MSSQL\data\MY_DB_LOG.LDF',

SIZE=3,MAXSIZE=10,FILEGROWTH=1

）

GO

⑵利用T-SQL语句修改数据库

用T-SQL语句完成修改数据库的操作，语句如下。

ALTERDATABASEMYDB

ADDFILE

（NAME='MY_DB_DATA1',

FILENAME='C:

\ProgramFiles\MicrosoftSQLServer\MSSQL\data\MY_DB_DATA1.NDF',

SIZE=3,MAXSIZE=15,FILEGROWTH=2

）

GO

ALTERDATABASEMYDB

MODIFYFILE

（NAME='MY_DB_LOG',

MAXSIZE=20,FILEGROWTH=2

）

GO

⑶利用T-SQL语句删除数据库

DROPDATABASEMYDB

实验二表的创建、管理及数据操作

一、实验目的

⏹熟练掌握使用企业管理器和T-SQL语句创建、修改和删除表。

⏹熟练掌握使用企业管理器和T-SQL语句插入、修改和删除表数据。

二、实验内容

1、使用企业管理器创建学生选修课程数据库Student_Courses（参照课本）；

2、Student_Courses数据库包含如下三个表：

学生表、选修表、课程表；主要设置表的完整性；

3、在企业管理器中插入、修改和删除表数据。

4、使用T-SQL重复上述操作

5、分别使用企业管理器和T-SQL修改和删除表

三、实验步骤

1、建立studentdb数据库和spjdb数据库；

2、在studentdb数据库中利用企业管理器和查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：

student（学生信息表）：

主码

列名

数据类型

宽度

小数位

空否

取值范围

备注

Pk

sno

char

9

N

学号

sname

char

10

N

姓名

ssex

char

2

Y

性别

sage

smallint

Y

不小于12

年龄

sdept

char

15

Y

系名

course（课程表）：

主码

列名

数据类型

宽度

小数位

空否

备注

Pk

cno

Char

4

N

课程号

cname

Char

20

Y

课程名称

cpno

Char

4

Y

先行课号

ccredit

smallint

Y

学分

sc（学生选课表）：

主码

列名

数据类型

宽度

小数

空否

外码

参照关系

取值范围

备注

Pk

sno

Char

9

N

Fk

student

学号

cno

Char

4

N

Fk

course

课程号

grade

Decimal

5

1

Y

0≤x≤100

成绩

3、在spjdb数据库中利用企业管理器和查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：

S（供应商信息表）：

主码

列名

数据类型

宽度

小数位

空否

取值范围

备注

Pk

sno

char

2

N

供应商号

sname

char

10

N

供应商名称

status

smallint

Y

大于0

供应商状态

city

char

10

Y

所在城市

P（零件信息表）：

主码

列名

数据类型

宽度

小数位

空否

取值范围

备注

Pk

pno

char

2

N

零件号

pname

char

10

N

零件名称

color

char

2

Y

颜色

weight

smallint

Y

大于0

重量

J（工程项目表）：

主码

列名

数据类型

宽度

小数位

空否

取值范围

备注

Pk

jno

char

2

N

工程项目号

jname

char

10

N

工程项目名称

city

char

10

Y

所在城市

SPJ（供应情况表）：

主码

列名

数据类型

宽度

小数

空否

外码

参照关系

取值范围

备注

Pk

sno

Char

2

N

Fk

S

供应商号

pno

Char

2

N

Fk

P

零件号

jno

Char

2

N

Fk

J

工程项目号

qty

smallint

Y

x>0

数量

4、修改表结构，具体要求如下：

（1）将表course的cname列的数据类型改为varchar（40）.

（2）为表student增加一个新列:

birthday（出生日期）,类型为datetime,默认为空值.

（3）将表sc中的grade列的取值范围改为小于等于150的正数.

（4）为Student表的“Sex”字段创建一个缺省约束，缺省值为’男’

（5）为“Sdept”字段创建一个检查约束，使得所在系必须是’CS’、’MA’或’IS’之一。

（6）为Student表的“Sname”字段增加一个唯一性约束

（7）为SC表建立外键,依赖于Student表的fk_S_c约束。

（8）禁止启用Student表的“Sdept”的CHECK约束ck_student。

5、分别建立以下索引（如果不能成功建立,请分析原因）

（1）在student表的sname列上建立普通降序索引.

（2）在course表的cname列上建立唯一索引.

（3）在sc表的sno列上建立聚集索引.

（4）在spj表的sno（升序）,pno（升序）和jno（降序）三列上建立一个普通索引.

Transact-SQL提示:

1.建立表,修改表,建立索引需具有createtable的权限.

2.创建基本表的SQL语句是CREATETABLE.

简单语法格式:

CREATETABLE表名

（列名数据类型[default缺省值][notnull]

[，列名数据类型[default缺省值][notnull]]

……

[，primarykey（列名[，列名]…）]

[，foreignkey（列名[，列名]…）references表名（列名[，列名]…）]

[，check（条件）]）;

一般语法格式:

CREATETABLE[database_name.[owner].]table_name

（{|column_name|}[,…n]）

[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]

:

:

={column_namedata_type}[DEFAULTconstant_expression]

|[IDENTITY[（seed,increment）]][ROWGUIDCOL][][…n]

参数说明：

①[database_name.[owner].]table_name：

定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。

②：

列的定义。

③column_name：

列的名字。

列的命名必须遵守有关数据库对象的命名规则。

④data_type：

列的数据类型。

⑤DEFAULTconstant_expression：

定义该列的默认值。

⑥IDENTITY：

定义该列是一个标识列。

当一个新的数据行插入表中的时候，SQLServer2000为标识列提供一个唯一的、递增的数值。

在一张表格中，只能定义一个标识列。

在定义标识列时，必须同时定义起始值和增量。

⑦Seed：

定义标识列的起始值。

所谓起始值就是插入表的第一行的数据的标识列的值。

Increment：

定义标识列的增量。

所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。

⑧ROWGUIDCOL：

定义该列是一个行全局唯一的标识列。

在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。

column_constraint：

定义与列相关联的约束。

⑨table_constraint：

定义对表的约束。

⑩ON{filegroup|DEFAULT}：

定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。

[TEXTIMAGE_ON{filegroup|DEFAULT}]：

如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。

3.修改表结构的SQL语句是ALTERTABLE,修改的内容包括：

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

简单语法格式:

altertable<表名>

[add<列名><数据类型>[<列级完整性约束>]]//增加新列

[drop<完整性约束名>]//删除约束

[dropcolumn<列名>]//删除列

[altercolumn<列名><数据类型>[<列级完整性约束>]];//修改列定义

其中列级完整性约束包括:

·空值约束NOTNULL和NULL

·主关键字约束PRIMARYKEY

·唯一性约束UNIQUE

·参照完整性约束FOREIGNKEY

一般语法格式:

ALTERTABLEtable

{[ALTERCOLUMNcolumn_name{new_data_type[NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL}]

|ADD{[]|column_nameAScomputed_column_expression}[,…n]

|ADD{}[,…n]

|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]

|{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,…n]

|{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}

参数说明：

①ALTERCOLUMN：

修改已经存在的列的属性。

②{ADD|DROP}ROWGUIDCOL：

将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。

③ADD{[]|column_nameAScomputed_column_expression}[,…n]：

增加新的列。

④ADD{}[,…n]:

定义新的表约束。

⑤DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]：

删除约束或删除列。

⑥{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,…n]}：

使所有约束或者指定的约束有效或失效。

例：

使cnst_example表中的约束salary_cap失效。

ALTERTABLEcnst_exampleNOCHECKCONSTRAINTsalary_cap

⑦{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}：

使所有或部分触发器有效或无效。

例：

使用T_SQL语句对学生表进行各种修改。

①向表中添加新的字段：

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

ALTERTABLEstudentaddclasschar（10）

②删除表中的旧列：

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

ALTERTABLEstudentDROPcolumnclass

③更改表中的约束：

删除某列的约束：

将SC表中的外码约束FK_sc_sno删除。

ALTERTABLEscDROPFK_sc_sno

将Student表中的主码约束PK_student_sno删除。

ALTERTABLEstudentDROPPK_student_sno

为某列添加约束：

为Student表的“Ssex”字段创建一个缺省约束，缺省值为’男’。

ALTERTABLEstudentADDconstraintdef_ssexDEFAULT'男'FORssex

更改Student表中的“sno”字段的宽度为10及非空约束。

ALTERTABLEstudentALTERCOLUMNsnochar（10）NOTNULL

为Student表添加一个主码约束PK_student_sno。

ALTERTABLEstudentADDCONSTRAINTPK_student_snoPRIMARYKEY（sno）

为SC表添加一个外码约束FK_sc_sno。

ALTERTABLEscADDCONSTRAINTFK_sc_snoFOREIGNKEY（sno）REFERENCESstudent（sno）

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

ALTERTABLEstudentADDgradeintConstraintch_gradecheck（grade<100）

Execsp_helpstudent

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

AltertablestudentAddbirthdayDATETIMENULLCONSTRAINTch_birthdayCHECK（birthday

⑥添加具有默认值的可为空的列：

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

AltertablestudentAddmatriculationdaysmalldatetimeNULLConstraintadddatefltDefaultgetdate（）

4.建立索引的SQL语句是CREATEINDEX,语法格式:

CREATE[UNIQUE][CLUSTERED]INDEX<索引名>

ON<表名>（<列名>[ASC|DESC][,<列名>[ASC|DESC]…]）

其中UNIQUE指出是唯一索引,CLUSTERED指出是聚集索引。

实验三SQL数据查询

一、实验目的

⏹熟练掌握使用T-SQL语句进行单表查询，尤其要熟练掌握GROUPBY子句、HAVING子句和集函数；

⏹熟练掌握使用SQL标准语句和T-SQL扩展语句进行连接查询。

⏹熟练掌握使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

二、实验内容

完成如下单表查询：

1