数据库实验指导书.docx
《数据库实验指导书.docx》由会员分享，可在线阅读，更多相关《数据库实验指导书.docx（40页珍藏版）》请在冰豆网上搜索。
数据库实验指导书
数据库原理实验
指
导
书
实验一
实验项目名称：
SQLServer2000系统的配置及基本操作（2课时，验证）
实验主要内容及方法熟悉实验环境
实验要求：
（1）了解SQLServer2000的启动，熟悉如何在SQLServer2000图形环境下建立数据库和表。
（2）了解SQLServer2000系统的相关服务及常用工具。
实验设备：
装有SQL SERVER 2000的电脑
实验步骤：
1、启动和停止SQLServer
a.用服务管理器启动和停止SQLServer
选择菜单“程序|MicrosoftSQLServer|服务管理器”。
弹出“SQLServer服务管理器”，选择服务器（如：
LWHNOTE）和服务选项（如：
SQLServer），若没有启动，按下
，即可启动SQLServer，若已经启动，按下
，即可停止SQLServer。
启动SQLServer后，在任务栏的右边会显示正在运行的SQLServer，即：
b.自动启动SQLServer
使用手动启动弹出“SQLServer服务管理器”后，选择复选框“当启动OS时自动启动服务”后，下次开机时系统即可自动启动SQLServer。
c.用操作系统控制面板中的服务管理程序启动和停止SQLServer
选择菜单“程序|管理工具|服务”。
弹出“服务”对话框。
鼠标右击要启动的服务器名称，即：
MSSQLSERVER，弹出菜单后，选择“启动”菜单项，就可启动SQLServer。
类似的方法，可以停止SQLServer。
2、使用企业管理器
a.启动企业管理器
在SQLServer中，企业管理器（EnterpriceManager）是最重要最常用的管理工具。
b.启动和停止SQLServer
在企业管理器中所要启动的服务器上点鼠标右击，从快捷菜单中选择“连接”。
即可启动并连接。
类似的方法，可以停止SQLServer。
说明：
企业管理器是一个对初学者来说最重要的管理工具，在以后的内容会经常使用。
c.浏览master和pubs数据库的结构
3、使用查询分析器
a.启动查询分析器
在SQLServer中，查询分析器也是最常用的管理工具之一。
选择“”菜单后，弹出“连接到SQLServer服务器”对话框。
用户输入SQLServer服务器的名称和身份验证后，即可进入查询分析器。
注：
身份验证可以使用“Windows身份验证”或“SQLServer身份验证”。
注：
以后很多命令操作均可在查询分析器中完成，如：
各种查询命令实验、SQL文件编写、存储过程和触发器文件的编写等。
b.从表中查询数据：
首先选择当前使用的数据库为pubs，然后在查询分析器的查询窗口中输入SQL语句
Select*fromtitles
点击
按钮，执行该SQL语句，观察执行结果。
c.练习T-SQL中的函数
如求200的平方根
在查询分析器的查询窗口中输入
selectsqrt（200）
go
观察执行结果。
实验二
实验项目名称sql数据定义语言的应用（2课时，验证）
实验主要内容及方法数据库、数据表、视图、索引的定义、删除及修改
实验要求：
（1）了解并掌握SQL查询分析器及企业管理器的使用;
（2）掌握数据库、基本表、索引的定义、删除与修改。
实验目的：
熟练掌握库、表的定义、删除与修改，为后继学习作准备。
实验设备：
装有SQL SERVER 2000的电脑
实验步骤：
表sc
Sno
Cno
Grade
S01
S01
S02
S02
S02
S03
S03
S04
…
C01
C03
C01
C02
C03
C01
C02
C03
…
92
84
90
94
82
72
90
75
…
表Courses
Cno
Cname
Pre_Cno
Credits
C01
C02
C03
C04
C05
C06
C07
英语
数据结构
数据库
DB_设计
C++
网络原理
操作系统
C05
C02
C03
C07
C05
4
2
2
3
3
3
3
表Students
Sno
Sname
Ssex
Sage
Sdept
S01
S02
S03
S04
S05
S06
S07
S08
S09
S10
王平
刘华
范君
李伟
黄河
长江
文涛
杨丽
欧浩
刘洪
男
女
女
男
男
男
男
女
男
男
21
19
18
19
18
20
17
18
21
19
信息
信息
计算机
计算机
数学
数学
信息
历史
中文
历史
对于如下给定的数据表格进行实验
表中数据可以自行给定。
一、定义数据库
1.用企业管理器创建数据库
创建一个名称为Test的数据库，数据文件的初始大小设为5MB，文件增长增量设为2MB，文件增长方式设为自动增长，文件的增长上限设为50MB；日志文件的初始大小为10MB，文件增长增量为1MB，文件的增长限制设为100MB。
具体创建步骤如下：
1）进入SQLServer企业管理器
2）展开目录树，在“数据库”选项上单击右键，选择“新建数据库”，如图所示：
该窗口有3个选项卡：
常规、数据文件和事务日志。
输入数据库名称：
Test，点击数据文件选项卡，如图所示：
在数据文件中，你可以设定数据文件的名称，所在目录以及文件的大小等信息。
完成后点击事务日志选项卡，如图所示：
事务日志中，你可以设定日志文件的名称，所在目录以及文件的大小等信息。
接受系统的缺省值，点击确定按钮，完成数据库的新建。
2.用T-SQL语言创建数据库
CREATEDATABASEdatabase_name
其他配置可以采用默认设置。
[练习]：
按以上要求，在查询分析器中用T-SQL语言创建数据库“Test”，写出SQL语句。
3.查看并修改数据库的属性
（1）进入企业管理器，鼠标右键单击数据库“Test”，选择“属性”，查看选项。
将数据库Test数据文件的初始大小改为20MB，最大值改为80MB，数据增长改为5％。
二、定义基本表
例1建立表1.1所示的学生表Students，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。
这里要求Sno和Sname不能为空值，且取值唯一。
CREATETABLEStudents/*列级完整性约束条件*/
（SnoCHAR（5）NOTNULL,/*Sno不能为空值*/
SnameCHAR（20）NOTNULL,/*Sname不能为空值*/
SsexCHAR
（2）,
SageINT,
SdeptCHAR（15）,
CONSTRAINTun_SnoUNIQUE（Sno）,/*Sno取值唯一的约束*/
CONSTRAINTun_SnameUNIQUE（Sname））;/*Sname取值唯一的约束*/
说明：
在MicrosoftSQLServer2000的查询分析器（QueryAnalyzer）中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。
通常，SQLServer2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQLServer2000会给出错误信息提示。
例2建立表1.2所示的课程表Courses，其属性名意义分别为Cno-课程号,Cname-课程名,Pre_Cno-先修课程号,Credits-学分。
CREATETABLECourses
（CnoCHAR（5）NOTNULL,/*Cno不能为空值*/
CnameCHAR（20）NOTNULL,/*Cname不能为空值*/
Pre_CnoCHAR（5）,
CreditsINT,
CONSTRAINTun_CnoUNIQUE（Cno））;/*Cno取值唯一的约束*/
例3建立表1.3所示的成绩表Sc。
其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。
CREATETABLESc
（SnoCHAR（5）NOTNULL,/*Sno不能为空值*/
CnoCHAR（5）NOTNULL,/*Cno不能为空值*/
GradeINT,
CONSTRAINTSno_CnoUNIQUE（Sno,Cno））;/*Sno+Cno取值唯一的约束*/
三、修改基本表
例4向基本表Students中增加“入学时间”属性列，其属性名为Sentrancedate，数据类型为DATETIME型。
ALTERTABLEStudentsADDSentrancedateDATETIME;例5将Sage（年龄）的数据类型改为SMALLINT型。
ALTERTABLEStudentsALTERCOLUMNSageSMALLINT;
例6删除Sname（姓名）必须取唯一值的约束。
ALTERTABLEStudentsDROPCONSTRAINTun_Sname;
注意：
SQLServer2000增加了删除属性的命令。
比如，删除属性列Sentrancedate的命令为：
ALTERTABLEStudentsDROPCOLUMNSentrancedate;
说明：
为了保证后面例子能够顺利运行，请大家一定将属性列Sentrancedate从Students表中删除。
为了调试SQL语句方便，这里没有在表Sc中增加参照完整性约束，甚至没有定义主键。
四、删除基本表
例7删除Students表。
DROPTABLEStudents;
说明：
此表删除后，请立即用例1将其建立起来，以便后面的例子使用。
五、建立索引
例8为学生选课数据库中的Students，Courses，Sc三个表建立索引。
其中Students表按Sno（学号）升序建唯一索引，Courses表按Cno（课程号）升序建唯一索引，Sc表按Sno（学号）升序和Cno（课程号）号降序建唯一索引。
其语句为：
CREATEUNIQUEINDEXStu_SnoONStudents（Sno）;
CREATEUNIQUEINDEXCou_CnoONCourses（Cno）;
CREATEUNIQUEINDEXRep_ScnoONSc（SnoASC,CnoDESC）;
例9在基本表Students的Sname（姓名）和Sno（学号）列上建立一个聚簇索引，而且Students中的物理记录将按照Sname值和Sno值的升序存放。
其语句为：
CREATECLUSTEREDINDEXStu_Sname_SnoONStudents（Sname,Sno）;
六、删除索引
例10删除基本表Sc上的Rep_SCno索引。
DROPINDEXSc.Rep_Scno;
七、建立视图
例11建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。
CREATEVIEWC_Student
AS
SELECTSno,Sname,Sage,Sdept
FROMStudents
WHERESdept=’数学’
WITHCHECKOPTION
例12建立学生的学号（Sno）、姓名（Sname）、选修课程名（Cname）及成绩（Grade）的视图。
本视图由三个基本表的连接操作导出，其SQL语句如下：
CREATEVIEWStudent_CR
AS
SELECTStudents.Sno,Sname,Cname,Grade
FROMStudents,Sc,Courses
WHEREStudents.Sno=Sc.SnoANDSc.Cno=Courses.Cno
例13定义一个反映学生出生年份的视图。
CREATEVIEWStudent_birth（Sno,Sname,Sbirth）
ASSELECTSno,Sname,1996-Sage
FROMStudents
八、删除视图
例14删除视图Student_CR。
DROPVIEWStudent_CR;
实验三
实验项目名称sql数据操纵语言的应用（6课时，综合）
实验主要内容及方法数据的各种操纵方法
实验要求：
（1）掌握sql数据插入、修改、删除和查询语句的一般格式。
（2）掌握sql数据插入、修改、删除和查询使用方法。
实验目的：
熟练掌握四大语句的使用。
实验设备：
装有SQL SERVER 2000的电脑
实验步骤：
一、插入数据
1）采用insert语句将实验二给定的表格中数据插入数据库。
2）在数据库中定义一个关系History_Student，其关系模式与Students完全一样，试将关系Students中的所有元组插入到关系History_Student中去。
二、修改数据
1）将学号为“S03”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。
2）将所有学生的年龄增加1岁。
即要修改多个元组的值。
3）将数学系所有学生的成绩置零。
注意：
由于学生所在系的信息在Students表中，而学习成绩在Sc表中，因此，可以将SELECT子查询作为WHERE子句的条件表达式。
三、删除数据
1）删除学号为“S04”的学生选修的课号为“C02”的记录。
2）删除所有学生的选课记录。
3）删除数学系所有学生的选课记录。
四、查询数据
1、无条件查询
1）查询全体学生的详细记录。
2）查询全体学生的姓名（Sname）、学号（Sno）、所在系（Sdept）。
3）查询全体学生的姓名（Sname）、出生年份及学号（Sno）。
提示：
由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。
4）查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。
2、条件查询
1）查询数学系全体学生的学号（Sno）和姓名（Sname）。
2）查询所有年龄在18-22岁（包括18岁和22岁）之间的学生姓名（Sname）及年龄（Sage）。
3）查询年龄不在18-22岁之间的学生姓名（Sname）及年龄（Sage）。
4）查询自动化系、数学和计算机系学生的学号（Sno）、姓名（Sname）和性别（Ssex）。
5）查询既不是信息系、数学系、也不是计算机系的学生的姓名（Sname）和性别（Ssex）。
6）查询所有姓刘的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。
7）查询课程名为“DB_设计”的课程号（Cno）和学分（Credits）。
8）假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
试查询缺少成绩的学生的学号（Sno）和相应的课程号（Cno）。
9）查询所有有成绩的学生学号（Sno）和课程号（Cno）。
3、查询结果排序
1）查询选修了C03号课程的学生的学号（Sno）和成绩（Grade），并按成绩降序排列。
2）查询全体学生情况，查询结果按所在系的系名（Sdpet）升序排列，同一系中的学生按年龄（Sage）降序排列。
4、集函数的使用
1）查询学生总人数。
2）查询选修了课程的学生人数。
3）计算选修C01号课程的学生平均成绩。
4）查询选修C01号课程的学生最高分数。
5、查询结果分组
1）求各个课程号（Cno）及相应的选课人数。
2）查询选修了3门或3门以上课程的学生学号（Sno）。
6、不同表之间的连接查询
1）查询每个学生及其选修课程的情况。
提示：
本查询实际上是涉及Students与Sc两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的。
2）查询每个学生的学号（Sno）、姓名（Sname）、选修的课程名（Cname）及成绩（Grade）。
7、自身连接
1）查询每一门课的间接先修课（即先修课的先修课）。
在Courses表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。
要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。
这就需要要将Courses表与其自身连接。
为方便连接运算，这里为Courses表取两个别名分别为A，B。
8、带谓词IN的嵌套查询
1）查询选修了编号为“C02”的课程的学生姓名（Sname）和所在系（Sdept）。
2）查询与“李伟”在同一个系学习的学生学号（Sno）、姓名（Sname）和系名（Sdept）。
3）查询选修了课程名为“数据结构”的学生学号（Sno）和姓名（Sname）。
9、带有比较运算符的嵌套查询
1）将例32改为带有比较运算符的嵌套查询。
提示：
由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN。
10、带谓词ANY或ALL的嵌套查询
1）查询非自动化系的不超过自动化系所有学生的年龄的学生姓名和年龄。
11、带谓词EXISTS的嵌套查询
1）查询所有选修了编号为“C01”课程的学生姓名（Sname）和所在系（Sdept）。
2）查询与“李伟”在同一个系学习的学生学号（Sno）、姓名（Sname）和系名（Sdept）。
3）查询选修了所有课程的学生姓名（Sname）和所在系。
实验四
实验项目名称sql的数据控制语言及数据库的保护（2课时，验证）
实验主要内容及方法数据控制语言，与用户、权限管理
实验要求：
（1）掌握用户的创建，删除，权限的授予、收回。
（2）掌握数据库的备份和还原。
实验目的：
熟练掌握授权与撤权。
实验设备：
装有SQL SERVER 2000的电脑
实验步骤：
一、用户管理
1.用企业管理器管理SQLServer登录
A.增加一个WindowsNT用户或用户组
选择WindowsNT用户或用户组
设置登录用户的服务器角色为系统管理员
设置登录用户可访问的数据库为DBS。
一个登录用户可以访问很多个数据库，可以在这里指定，指定后在相应的数据库中建立了相应的数据库用户。
按下“确定”，即可建立一个WindowsNT用户，然后，可以在企业管理器中检验登录用户是否已经建立。
B.增加一个SQLServer用户
如：
增加一个SQLServer用户，用户名为“lwh”，密码为“lwhpass”。
设置登录用户的常规属性
C.修改用户
如：
修改SQLServer用户lwh的属性。
D.删除用户
如：
删除SQLServer用户lwh。
2、用查询分析器管理SQLServer登录
A.增加一个WindowsNT用户或用户组
如：
增加一个WindowsNT用户为“LWHNOTE\User”，授予登录访问权。
取消WindowsNT用户“LWHNOTE\User”的登录访问权。
B.增加一个SQLServer用户
如：
增加一个SQLServer用户，用户名为“lwh”，密码为“lwhpass”。
C.删除用户
如：
删除WindowsNT用户“LWHNOTE\User”。
如：
删除SQLServer用户lwh。
3.用企业管理器管理数据库用户
A.增加一个数据库用户
如：
给数据库dbs增加一个dbuser用户。
B.删除一个数据库用户
如：
删除数据库dbs中的数据库用户dbuser。
4、用查询分析器管理数据库用户
A.增加一个数据库用户
如：
给数据库dbs增加一个dbuser用户。
B.删除一个数据库用户
如：
删除数据库dbs中的数据库用户dbuser。
二、权限管理
1.用企业管理器管理权限
在SQLServer中通过两种途径可实现对语句权限和对象权限的管理，从而实现对用户权限的设定。
这两种途径分别为面向单一用户和面向数据库对象两种权限设置。
A.面向单一用户的权限设置
为某一用户设置其对当前数据库所有对象的访问权限。
如：
设置数据库dbs的dbo用户的权限。
B.面向数据库对象的权限设置
为某一数据库对象设置当前数据库所有用户对其的访问权限。
如：
设置数据库dbs中数据表Course的权限。
2、用查询分析器管理权限
利用企业管理器建立U1,U2,U3,U4,U5用户，启动查询分析器，用GRANT语句对数据库存取权限进行授权操作。
（1）利用查询分析器对这些用户进行授权.并加以验证.
a.把查询Student表的权限授给用户U1。
GRANTSELECT
ONStudents
TOU1
b.把对表和Course表的全部操作权限授予用户U2和U3
GRANTALLPRIVILIGES
ONCourse
TOU2,U3;
c.把对表Sc的查询权限授予所有用户。
GRANTSELECT
ONSC
TOPUBLIC;
d.把查询Student表和修改学生学号的权限授给用户U4
GRANTUPDATE（Sno）,SELECT
ONStudents
TOU4;
e.把对表SC的INSERT权限授予U5用户,并允许将此权限再授予其他用户
GRANTINSERT
ONTABLESC
TOU5
WITHGRANTOPTION;
（2）利用查询分析器对这些用户进行收权.并加以验证.
a.把用户U4修改学生学号的权限收回
REVOKEUPDATE（Sno）
ONStudents
FROMU4;
b.收回所有用户对表sc的查询权限
REVOKESELECT
ONSC
FROMPUBLIC;
c.把拥护U5对SC表的INSERT权限收回
REVOKEINSERT
ONSC
FROMU5
CASCADE
三、数据库备份
1.用企业管理器进行备份
在SQLServer中无论是数据库备份，还是事务日志备份、差异备份、文件或文件组备份都执行相同的步骤。
如：
完全备份数据库DBS到一个磁盘文件d:
\SQL_DBs\DBS_BAK。
选择数据库备份方式，然后按“添加”按钮，指定备份文件。
说明：
选择不同的备份方式，可以实现完全数据库备份、差异数据库备份、事务日志备份等。
指定备份文件
最后，按“确定”按钮，即可完成备份。
2、用查询分析器进行备份
（可选内容，请参照其他参考资料）
四.数据库还原
1.用企业管理器进行恢复
如：
完全备
升级会员 