数据库系统原理及应用实验全套.docx
《数据库系统原理及应用实验全套.docx》由会员分享，可在线阅读，更多相关《数据库系统原理及应用实验全套.docx（39页珍藏版）》请在冰豆网上搜索。
数据库系统原理及应用实验全套
数据库系统原理及应用
实验指导书
（本科）
福建工程学院计算机与信息科学系
计算机软件与理论教研室
浅诺制作QQ:
405790115
2012年5月
实验一数据定义语言
一、实验目的
1.熟悉SQLServer2000/2005查询分析器。
2.掌握SQL语言的DDL语言，在SQLServer2000/2005环境下采用Transact-SQL实现表的定义、删除与修改，掌握索引的建立与删除方法。
3.掌握SQLServer2000/2005实现完整性的六种约束。
二、实验内容
1.启动SQLServer2000/2005查询分析器，并连接服务器。
2.创建数据库:
（请先在D盘下创建DB文件夹）
1）在SQLServer2000中建立一个StuDB数据库：
有一个数据文件：
逻辑名为StuData，文件名为“d:
\db\StuDat.mdf”，文件初始大小为5MB，文件的最大大小不受限制，文件的增长率为2MB；
有一个日志文件，逻辑名为StuLog，文件名为“d:
\db\StuLog.ldf”，文件初始大小为5MB，文件的最大大小为10MB，文件的增长率为10%
2）刷新管理器查看是否创建成功，右击StuDB查看它的属性。
3.设置StuDB为当前数据库。
4.在StuDB数据库中作如下操作：
设有如下关系表S：
S（CLASS,SNO,NAME,SEX,AGE），
其中：
CLASS为班号，char（5）；SNO为座号，char
（2）；NAME为姓名，char（10），设姓名的取值唯一；SEX为性别，char
（2）；AGE为年龄，int，表中主码为班号+座号。
写出实现下列功能的SQL语句。
（1）创建表S；
（2）刷新管理器查看表是否创建成功；
（3）右击表S插入3个记录：
95031班25号李明，男性，21岁；
95101班10号王丽，女性，20岁；
95031班座号为30，名为郑和的学生记录；
（4）将年龄的数据类型改为smallint；
（5）向S表添加“入学时间（comedate）”列，其数据类型为日期型（datetime）；
（6）对表S，按年龄降序建索引（索引名为inxage）；
（7）删除S表的inxage索引；
（8）删除S表；
5.在StuDB数据库中，
（1）按照《数据库系统概论》（第四版）P82页的学生－课程数据库创建STUDENT、COURSE和SC三张表，每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。
并输入相关数据。
（2）将StuDB数据库分离，在D盘下创建DB文件夹下找到StuDB数据库的两个文件，进行备份，后面的实验要用到这个数据库。
3、实验总结
通过本次实验大致熟悉了SQLServer2000/2005/2008查询分析器.对SQLServer2000/2005实现完整性的六种约束有所了解，但是对表的关联还不是很明白，我会去看书加深理解。
实验二SQLSever中的单表查询
一、实验目的：
1.掌握SQL语言的DML子语言，在SQLServer2000/2005环境下采用Transact-SQL的SELECT语句熟练掌握各种单表查询方法。
二、实验内容：
（一）附加上次实验备份的StuDB数据库和SPJ数据库。
（二）按照《数据库系统概论》P82页中的学生课程数据库及数据实现以下查询：
1.查询所有课程的课号，课程名。
2.查询计算机系年龄不到21岁的所有男生的姓名和出生年份。
3.查询已被学生所选课程的课程号。
4.查询所有有成绩的学生学号和课程号，并为各列分别取相应的中文别名。
（三）按照《数据库系统概论》P75页中的SPJ数据库及数据实现以下查询：
1.查询地点在‘北京’、‘上海’的供应商代码和供应商名字。
2.查询供应商名中包含‘方’这个汉字的供应商信息。
3.查询零件名以‘刀’字结尾，包含三个汉字的零件信息。
4.查询使用了供应商S1所供应零件的工程项目数。
5.查询各个供应商所供应的零件的总数，要求对查询结果降序显示。
6.查询供应了3个以上项目的供应商代码和供应的项目总数。
加上groupby
直接用COUNT（JNO）>3结果一致
三、实验要求：
1.根据实验内容认真写好实验报告，记录每个步骤正确的Transact-SQL命令。
2.无须记录正确的查询结果。
但要求记录实验过程中发生的有学习意义的错误及错误信息。
4、实验小结
通过本次实验，基本上掌握了SQLSever中的单表查询，但对groupby语句不是很了解。
还有where子句与having短语的区别，作用对象不同，where作用于基本表或视图，having作用于组，这里的组怎么理解？
实验三SQLServe中的连接查询
一、实验目的：
1.掌握采用Transact-SQL实现连接查询。
2.掌握等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。
二、实验内容：
按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询
（1）查询使用红色零件的工程名称。
--
（1）查询使用红色零件的工程名称。
SELECTDISTINCTJNAME
FROMP,J,SPJ
WHERECOLOR='红'ANDP.PNO=SPJ.PNOANDJ.JNO=SPJ.JNO;
（2）查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。
--
（2）查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。
SELECTDISTINCT*
FROMJLEFTOUTERJOINSPJON（J.JNO=SPJ.JNO）;
（3）查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。
--（3）查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。
第一种：
SELECTDISTINCTSNO
FROMP,SPJ
WHERECOLOR='红'ANDP.PNO=SPJ.PNOANDJNO='J1';
第二种：
SELECTSNO
FROMSPJ
WHEREPNOIN
（SELECTPNO
FROMP
WHERECOLOR='红'
）ANDJNO='J1'
（4）求供应工程J1零件的供应商的完整信息。
--（4）求供应工程J1零件的供应商的完整信息。
SELECT*
FROMS
WHERESNOIN
（SELECTSNO
FROMSPJ
WHEREJNO='J1'
）;
（5）查询使用北京供应商供应零件的工程信息。
（6）查询选修3号课程且成绩在85分以上的所有学生。
（7）查询先行课的学分为4的课程信息。
（8）查询课程与其间接先行课的名称。
（9）查询其他系中比计算机科学系所有学生年龄都小的学生完整信息，用两种方法实现。
（10）查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。
（11）（可选）查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现。
SELECTJNO
FROMJ
WHEREEXISTS
（SELECT*
FROMSPJ
WHEREJNO=J.JNOANDSNO='S1'）
（12）（可选）查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。
--不会
（13）（可选）查询所有使用了P3零件的工程号JNO，用带EXISTS谓词的子查询实现。
SELECTJNO
FROMJ
WHEREEXISTS
（SELECT*
FROMSPJ
WHEREJNO=J.JNOANDPNO='P3'）
三、实验要求：
1.写出正确的Transact-SQL命令。
2.无须记录正确的查询结果。
要求记录实验过程中发生的有学习意义的错误及错误信息。
四、实验小结
通过实验大致掌握了等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。
实验过程中最纠结的就是12题，使用了全部零件就是说pno从p1到p6在同一个工程中
都有，那么这该怎么表示呢？
实验四SQLServe的数据更新、视图
实验时间：
2012-3-27
实验地点：
c2-101
一、实验目的：
1.掌握采用Transact-SQL实现嵌套查询。
2.掌握采用Transact-SQL实现数据更新。
3.掌握采用Transact-SQL实现视图的定义、删除、查询与更新。
二、实验内容
按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下操作：
（一）数据更新
1）一个学生：
张红，女，200215135，信息系，20岁
2）一个选课记录：
200215135，1，成绩未定
3）算机系所有学生的年龄加1岁
4）删除所有200215130学生的选课记录
5）删除所有姓张的同学的信息
6）行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约束。
（二）视图
1）创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分
2）利用上题所建视图实现：
①查询成绩高于自己平均成绩的选课记录
②查询每个同学获得最高分的选课记录
3）请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。
建立该视图：
针对该视图完成下列查询：
①找出三建工程项目使用的各种零件代码及其数量；
②找出供应商S1的供应情况；
4、实验小结
删除姓张的全部信息时忽视了关系的完整性，没意识到表级完整性约束条件，
导致不能直接在Student表中删除记录。
先把SC表中的有关姓张的学生先给
删除掉，解除关联，再从student表中删除相关信息。
对于groupby的使用仍然不是很清楚。
如以上创建视图的时候为什么要
groupbystudent.Sno，sname~~等等
实验五数据控制（完整性与安全性）
一、实验目的：
1.通过实验加深对数据库完整性与数据库安全性的理解；
2.掌握SQLServer对数据进行安全性控制的方法
二、实验内容和要求
（一）数据库完整性
某公司使用数据库进行内部管理：
表Employees存储雇员的代号（整型，标识位，种子1000001，增量为1，主键）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表Departments存储部门的部门号（2个字符，唯一，主键）、部门名称（30个字符）等信息；表Work每一行表示某雇员在某部门工作过及其开始工作时间和备注。
请写出创建这三个表的SQL语句，要保证：
工资的值大于0，身份证号码唯一，开始工作时间非空，默认值为当前时间。
--创建表Employees
CREATETABLEEmployees
（EnoINTIDENTITY（1000001,1）PRIMARYKEY,
EIDCHAR（18）CHECK（Len（Ltrim（RTRIM（EID）））=18）UNIQUE,
EnameChar（20）notnull,
EgzintCHECK（Egz>0）
）
--创建表Departments
CREATETABLEDepartments
（DnoCHAR
（2）PRIMARYKEY,
DnameCHAR（30）UNIQUE
）
--创建表Work
CREATETABLEWork
（EnoINT,
DnameCHAR（30）,
WorkTimedatetimedefaultgetdate（）notnull,
Memochar（100）,
PRIMARYKEY（Eno,Dname）,
FOREIGNKEY（Eno）REFERENCESEmployees（Eno）,
FOREIGNKEY（Dname）REFERENCESDepartments（Dname）
）
（2）以sa登录查询分析器，输入下列代码并执行
第1行execsp_addlogin's1','123'
第2行useStuDB
第3行execsp_grantdbaccess's1','u1'
第4行grantselect,insert,updateonstudenttopublic
第5行grantallonstudenttou1
第6行revokeselectonstudenttou1
问：
1）第1行创建了一个名为s1登录帐户,请问'123'的含义是什么？
账户s1的密码
2）第3行将登录帐户s1映射到数据库用户u1上，它将作为哪个数据库的用户？
StuDB数据库
3）请解释4到6行的含义
第4行grantselect,insert,updateonstudenttopublic
--把在student表上查询，插入修改的权限给所有用户
第5行grantallonstudenttou1
--在student上的全部权限给u1
第6行revokeselectonstudenttou1
--回收u1对student表的查询权限
4）在查询分析器中以s1帐户连接服务器，能否对学生－课程数据库的student表进行select操作，为什么？
（提示：
废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。
因此，如果废除用户查看表的权限，不一定能防止用户查看该表，因为已将查看该表的权限授予了用户所属的角色。
）
可以select因为s1从超级用户那里也被被授予select的权限
5）切换回sa用户的查询窗口，输入下面代码并执行
denyselectonstudenttou1
请问该代码含义？
取消u1的查询权限
再切换至s1的查询窗口，能不能对student表进行查询操作？
为什么？
不能，因为角色u1select已经被回收了，这条命令是把s1从超级用户那里获得的select权限也回收了，所以不能进行查询操作了。
（三）请完成以下操作,并记录完整语句。
在实验过程注意验证语句的执行效果。
（1）需要为学生－课程数据库，创建一用户，该用户以“SQLServer身份验证”方式登录SQLServer服务器的账号为学生自己的姓名，密码为“123”。
execsp_addlogin'xiezhenjia','123'
usestudb
（2）给该登录用户映射到数据库用户user2上，并赋予该用户对student表的sno、sname两列的查询权限，对sc表的所有操作权限及对course的查询权限。
（请先执行revokeselectonstudentfrompublic
Revokeallonscfrompublic
注：
新建用户自动被加入到public角色中）
revokeselectonstudentfrompublic
Revokeallonscfrompublic
execsp_grantdbaccess'xiezhenjia','user2'
grantselect（sno,sname）
onstudent
touser2
grantselect,update,delete,insert
onsc
touser2
grantselect
oncourse
touser2
（3）请分析、验证数据库用户user2的权限。
1）、对student的sno以及sname有查询权限
2）、对sc表有所有权限
3）、对course有查询权限
（4）用户user2授予对course表的插入操作权限，并允许将此权限授予其它用户
grantinsert
oncourse
touser2
withgrantoption
（5）收回授予user2对sc表的所有权限
revokeselect,insert,delete,update
onsc
fromuser2
（6）创建一个角色R1
createroler1
（7）然后使用GRANT语句，使角色R1拥有SC表的SELECT、UPDATE、INSERT
grantselect,update,insert
onsc
tor1
（8）将这个角色授予user2。
使它具有角色R1所包含的全部权限。
grantr1
touser2
三、实验要求：
1）写出正确的Transact-SQL命令。
2）无须记录正确的查询结果。
要求记录实验过程中发生的有学习意义的错误及错误信息。
四、实验小结
刚开始在机房里面做的时候附加进来的数据库属性是只读的，不知道是什么情况。
就是没有把错误记录下来。
通过实验对数据的安全性更为熟悉。
实验六语法元素与流程控制
一、实验目的：
1）掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运用方法，掌握Transact-SQL的局部变量的作用域概念。
2）掌握Transact-SQL的控制流语言的使用方法。
3）掌握与控制流语言语句一起使用的其它Transact-SQL语句：
CASE、/*...*/（注释）、--（注释）、PRINT。
二、实验内容和要求
1、使用局部变量、全局变量
（1）定义一个int的整型变量，为其赋值45，并显示变量的值。
declare@varint
set@var=45
select@var变量
go
（2）定义一个长度为20的可变长度型字符变量，为其赋值“WelcometoFJUT”，并显示变量的值。
declare@var1varchar（20）
set@var1='WelcometoFJUT'
select@var1字符变量
go
（3）在批处理中声明两个局部变量＠ssex和@sdept并对它们赋值，对Student表进行查询，要求两局部变量在SELECT语句的WHERE子句中被使用，查询的要求是性别为‘女’或所在系为‘IS’系的那些学生。
declare@ssexchar
（2）,@sdeptchar（20）
set@ssex='女'
set@sdept='IS'
select*fromstudent
wheressex=@ssexorsdept=@sdept
go
（4）查询当前数据库服务器名。
select@@SERVERNAME
结果：
（5）查询当前数据库管理系统版本。
select@@VERSION
（6）在student表中执行所有学生年龄加1岁语句，并用@@ROWCOUNT来检测是否有发生更改的行。
updatestudent
SETsage=sage+1;
select@@ROWCOUNT
2．函数的使用
（1）数学函数的使用
分别用函数求出-3的绝对值，16的平方根，5的三次方。
declare@var1int,@var2real,@var3int
set@var1=Abs（-3）
set@var2=Sqrt（16）
set@var3=Power（5,3）
select@var1'-3的绝对值',@var2'16的平方根',@var3'5的三次方'
go
（2）字符串函数的使用
1）分别将字符串'china'、'MACHINE'转换成大写、小写字母；
selectupper（'china'）
selectlower（'MACHINE'）
2）去掉字符串'machine'左边的空格，右边与'press'连接起来；
selectltrim（'machine'）+'press'
（3）日期、时间函数的使用
1）返回系统当前日期并以整数形式返回当前日期的年份、月份、日；
selectyear（getdate（））年,month（getdate（））月,day（getdate（））日
2）返回给定日期“2006-2-21”与当前日期相差的天数。
selectdatediff（day,'2006-2-21',getdate（））
（4）系统函数与元数据函数的使用
显示正在使用的用户名
selectUSER_name（）
3．编写较复杂的Transact-SQL程序
1）在SELECT语句中，CASE搜索函数允许根据比较值在结果集内对值进行替换。
要求仿照《数据库概论》的sc表，根据学生的成绩范围将成绩（grade列）显示为文本注释。
（要求：
＜60分，文本注释为“不及格”，60－70，“及格”，70－80，“中”，80－90，“良”，90－100，“优”）。
select
case
whengrade<60then'不及格'
whenGrade>=60andGrade<70then'及格'
whenGrade>=70andGrade<80then'中'
whenGrade>=80andGrade<90then'良'
whenGrade>=90then'优'
end
fromsc
2）用Transact－SQL语言编写程序计算1～100之间所有能被7整除的数的总和。
declare@sumint,@iint
set@sum=0
set@i=1
while@i<=100
begin
if@i%7=0
begin
set@sum=@sum+@i;
end
set@i=@i+1;
end
print@sum
3）自选实例，实践并掌握Transact-SQL以下控制流语言的使用方法：
BEGIN...END、WHILE、BREAK、CONTINUE、IF...ELSE、WAITFOR
4）自选实例，实践并掌握以下Transact-SQL语句：
/*...*/（注释）、--（注释）、PRINT
--我的实例（求水仙花数）
declare@aint,@bint,@kint,@nint
set@n=99
while@n<999
begin
set@n=@n+1
set@a=@n/100/*求百位*/
set@b=@n/10-@a*10--求十位
set@k=@n%10/*求个位*/
if（@n<>@a*@a*@a+@b*@b*@b+@k*@k*@k）continue/*不是水仙花数,退出当前循环
升级会员 