数据库系统原理及应用实验全套.docx

1、数据库系统原理及应用实验全套数据库系统原理及应用实 验 指 导 书（本科）福建工程学院 计算机与信息科学系计算机软件与理论教研室 浅诺制作 QQ:405790115 2012年5月实验一 数据定义语言一、实验目的1. 熟悉SQL Server2000/2005查询分析器。2. 掌握SQL语言的DDL语言，在SQL Server2000/2005环境下采用Transact-SQL实现表的定义、删除与修改，掌握索引的建立与删除方法。3. 掌握SQL Server2000/2005实现完整性的六种约束。二、实验内容1. 启动SQL Server2000/2005查询分析器，并连接服务器。2. 创建数

2、据库: （请先在D盘下创建DB文件夹）1）在SQL Server2000中建立一个StuDB数据库： 有一个数据文件：逻辑名为StuData，文件名为“d:dbStuDat.mdf”，文件初始大小为5MB，文件的最大大小不受限制，文件的增长率为2MB；有一个日志文件，逻辑名为StuLog，文件名为“d:dbStuLog.ldf”，文件初始大小为5MB，文件的最大大小为10MB，文件的增长率为10% 2）刷新管理器查看是否创建成功，右击StuDB查看它的属性。3. 设置StuDB为当前数据库。4. 在StuDB数据库中作如下操作：设有如下关系表S： S(CLASS,SNO, NAME, SEX,

3、 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)

4、 对表S，按年龄降序建索引（索引名为inxage）；(7) 删除S表的inxage索引；(8) 删除S表；5. 在StuDB数据库中，（1）按照数据库系统概论(第四版)P82页的学生课程数据库创建STUDENT、COURSE和SC三张表，每一张表都必须有主码约束，合理使用列级完整性约束和表级完整性。并输入相关数据。（2）将StuDB数据库分离，在D盘下创建DB文件夹下找到StuDB数据库的两个文件，进行备份，后面的实验要用到这个数据库。3、实验总结通过本次实验大致熟悉了SQL Server2000/2005/2008查询分析器.对SQL Server2000/2005实现完整性的六种约束有所了

5、解，但是对表的关联还不是很明白，我会去看书加深理解。实验二 SQL Sever中的单表查询一、实验目的：1. 掌握SQL语言的DML子语言，在SQL Server2000/2005环境下采用Transact-SQL的SELECT语句熟练掌握各种单表查询方法。二、实验内容：(一) 附加上次实验备份的StuDB数据库和SPJ数据库。(二) 按照数据库系统概论P82页中的学生课程数据库及数据实现以下查询：1.查询所有课程的课号，课程名。2. 查询计算机系年龄不到21岁的所有男生的姓名和出生年份。3. 查询已被学生所选课程的课程号。4. 查询所有有成绩的学生学号和课程号，并为各列分别取相应的中文别名。

6、(三) 按照数据库系统概论P75页中的SPJ数据库及数据实现以下查询：1.查询地点在北京、上海的供应商代码和供应商名字。2.查询供应商名中包含方这个汉字的供应商信息。3.查询零件名以刀字结尾，包含三个汉字的零件信息。4.查询使用了供应商S1所供应零件的工程项目数。5.查询各个供应商所供应的零件的总数，要求对查询结果降序显示。6.查询供应了3个以上项目的供应商代码和供应的项目总数。加上group by直接用COUNT（JNO）3结果一致三、实验要求：1. 根据实验内容认真写好实验报告，记录每个步骤正确的Transact-SQL命令。2. 无须记录正确的查询结果。但要求记录实验过程中发生的有学习意

7、义的错误及错误信息。4、实验小结通过本次实验，基本上掌握了SQL Sever中的单表查询，但对group by 语句不是很了解。还有where子句与having短语的区别，作用对象不同，where作用于基本表或视图，having作用于组，这里的组怎么理解？实验三 SQL Serve中的连接查询一、 实验目的：1. 掌握采用Transact-SQL实现连接查询。2. 掌握等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。二、 实验内容：按照数据库系统概论P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询(1) 查询使用红色零件的工程名称。-(1) 查询使用红色零件的工

8、程名称。SELECT DISTINCT JNAMEFROM P,J,SPJWHERE COLOR=红AND P.PNO=SPJ.PNO AND J.JNO=SPJ.JNO;(2) 查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。-(2) 查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。SELECT DISTINCT *FROM J LEFT OUTER JOIN SPJ ON(J.JNO=SPJ.JNO);(3) 查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。-(3) 查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。第一

9、种：SELECT DISTINCT SNOFROM P,SPJWHERE COLOR=红 AND P.PNO=SPJ.PNO AND JNO=J1;第二种：SELECT SNOFROM SPJWHERE PNO IN (SELECT PNO FROM P WHERE COLOR = 红 )AND JNO=J1(4) 求供应工程J1零件的供应商的完整信息。-(4)求供应工程J1零件的供应商的完整信息。SELECT *FROM SWHERE SNO IN (SELECT SNO FROM SPJ WHERE JNO=J1 );(5) 查询使用北京供应商供应零件的工程信息。(6) 查询选修3号课程且

10、成绩在85分以上的所有学生。(7) 查询先行课的学分为4的课程信息。(8) 查询课程与其间接先行课的名称。(9) 查询其他系中比计算机科学系所有学生年龄都小的学生完整信息，用两种方法实现。(10) 查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。(11) （可选）查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现。SELECT JNOFROM JWHERE EXISTS (SELECT * FROM SPJ WHERE JNO=J.JNO AND SNO=S1)(12) （可选）查询使用了全部零件的工程号JNO，用带EXISTS谓词

11、的子查询实现。-不会(13) （可选）查询所有使用了P3零件的工程号JNO，用带EXISTS谓词的子查询实现。SELECT JNOFROM JWHERE EXISTS (SELECT * FROM SPJ WHERE JNO=J.JNO AND PNO=P3)三、实验要求：1. 写出正确的Transact-SQL命令。2. 无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。四、实验小结通过实验大致掌握了等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。实验过程中最纠结的就是12题，使用了全部零件就是说pno从p1到p6在同一个工程中都有，那么这该怎么表示

12、呢？实验四 SQL Serve的数据更新、视图 实验时间：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）删除所有

13、姓张的同学的信息6） 行设计案例对学生课程数据库的数据更新，并观察是否有违反数据的完整性约束。（二）视图1）创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分2）利用上题所建视图实现：查询成绩高于自己平均成绩的选课记录查询每个同学获得最高分的选课记录3）请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。 建立该视图： 针对该视图完成下列查询：找出三建工程项目使用的各种零件代码及其数量； 找出供应商S1的供应情况； 4、实验小结删除姓张的全部信息时忽视了关系的完整性，没意识到表级完整性约束条件，导致不能直接在Stud

14、ent表中删除记录。先把SC表中的有关姓张的学生先给删除掉，解除关联，再从student表中删除相关信息。对于group by的使用仍然不是很清楚。如以上创建视图的时候为什么要group by student.Sno，sname等等实验五 数据控制（完整性与安全性）一、实验目的：1. 通过实验加深对数据库完整性与数据库安全性的理解；2. 掌握SQLServer对数据进行安全性控制的方法二、实验内容和要求(一)数据库完整性某公司使用数据库进行内部管理：表Employees存储雇员的代号（整型，标识位，种子1000001，增量为1，主键）、身份证号码（18个字符）、名字（最长20个字符）和工资等信

15、息；表Departments存储部门的部门号（2个字符，唯一，主键）、部门名称（30个字符）等信息；表Work每一行表示某雇员在某部门工作过及其开始工作时间和备注。请写出创建这三个表的SQL语句，要保证：工资的值大于0，身份证号码唯一，开始工作时间非空，默认值为当前时间。-创建表EmployeesCREATE TABLE Employees(Eno INT IDENTITY(1000001,1)PRIMARY KEY, EID CHAR(18)CHECK(Len(Ltrim(RTRIM(EID)=18)UNIQUE, Ename Char(20)not null, Egz int CHECK(

16、Egz0)-创建表DepartmentsCREATE TABLE Departments(Dno CHAR(2)PRIMARY KEY, Dname CHAR(30)UNIQUE)-创建表WorkCREATE TABLE Work(Eno INT, Dname CHAR(30), WorkTime datetime default getdate() not null, Memo char(100),PRIMARY KEY(Eno,Dname),FOREIGN KEY(Eno)REFERENCES Employees(Eno),FOREIGN KEY(Dname)REFERENCES Depa

17、rtments(Dname)(2) 以sa登录查询分析器，输入下列代码并执行第1行 exec sp_addlogin s1,123第2行 use StuDB 第3行 exec sp_grantdbaccess s1,u1第4行 grant select,insert,update on student to public第5行 grant all on student to u1第6行 revoke select on student to u1问：1）第1行创建了一个名为s1登录帐户,请问123的含义是什么？ 账户s1的密码2）第3行将登录帐户s1映射到数据库用户u1上，它将作为哪个数据库的

18、用户？ StuDB数据库3）请解释4到6行的含义第4行 grant select,insert,update on student to public -把在student表上查询，插入修改的权限给所有用户第5行 grant all on student to u1 -在student上的全部权限给u1第6行 revoke select on student to u1 -回收u1对student表的查询权限4）在查询分析器中以s1帐户连接服务器，能否对学生课程数据库的student表进行select操作，为什么？（提示：废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的

19、权限。因此，如果废除用户查看表的权限，不一定能防止用户查看该表，因为已将查看该表的权限授予了用户所属的角色。）可以select 因为s1从超级用户那里也被被授予select的权限5) 切换回sa用户的查询窗口，输入下面代码并执行deny select on student to u1请问该代码含义？取消u1的查询权限再切换至s1的查询窗口，能不能对student 表进行查询操作？为什么？不能，因为角色u1select已经被回收了，这条命令是把s1从超级用户那里获得的select权限也回收了，所以不能进行查询操作了。(三)请完成以下操作,并记录完整语句。在实验过程注意验证语句的执行效果。(1)需

20、要为学生课程数据库，创建一用户，该用户以“SQL Server身份验证”方式登录SQL Server 服务器的账号为学生自己的姓名，密码为“123”。exec sp_addlogin xiezhenjia , 123use studb(2)给该登录用户映射到数据库用户user2上，并赋予该用户对student表的sno、sname两列的查询权限，对sc表的所有操作权限及对course的查询权限。（请先执行revoke select on student from public Revoke all on sc from public注：新建用户自动被加入到public角色中）revoke se

21、lect on student from publicRevoke all on sc from publicexec sp_grantdbaccess xiezhenjia,user2grant select(sno,sname)on studentto user2grant select,update,delete,inserton scto user2grant selecton courseto user2(3)请分析、验证数据库用户user2的权限。1）、对student的sno以及sname有查询权限2）、对sc表有所有权限3）、对course有查询权限(4)用户user2授予对c

22、ourse表的插入操作权限，并允许将此权限授予其它用户grant inserton courseto user2with grant option(5)收回授予user2对sc表的所有权限revoke select,insert,delete,updateon scfrom user2(6)创建一个角色 R1create role r1(7)然后使用GRANT语句，使角色R1拥有SC表的SELECT、UPDATE、INSERTgrant select,update,inserton scto r1(8)将这个角色授予user2。使它具有角色R1所包含的全部权限。grant r1to user2

23、三、实验要求：1) 写出正确的Transact-SQL命令。2) 无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。四、实验小结刚开始在机房里面做的时候附加进来的数据库属性是只读的，不知道是什么情况。就是没有把错误记录下来。通过实验对数据的安全性更为熟悉。实验六 语法元素与流程控制一、实验目的：1) 掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运用方法，掌握Transact-SQL的局部变量的作用域概念。2) 掌握Transact-SQL的控制流语言的使用方法。3) 掌握与控制流语言语句一起使用的其它 Transact-SQL 语句：CAS

24、E、/*.*/（注释）、-（注释）、PRINT。二、实验内容和要求1、使用局部变量、全局变量(1)定义一个int的整型变量，为其赋值45，并显示变量的值。declare var int set var=45select var 变量go(2)定义一个长度为20的可变长度型字符变量，为其赋值“Welcome to FJUT”， 并显示变量的值。declare var1 varchar(20)set var1=Welcome to FJUTselect var1 字符变量go(3)在批处理中声明两个局部变量ssex和sdept并对它们赋值，对Student表进行查询，要求两局部变量在SELECT

25、语句的 WHERE 子句中被使用，查询的要求是性别为女或所在系为IS系的那些学生。declare ssex char(2),sdept char(20)set ssex=女set sdept=ISselect * from studentwhere ssex=ssex or sdept=sdeptgo(4)查询当前数据库服务器名。select SERVERNAME结果：(5)查询当前数据库管理系统版本。select VERSION(6)在student表中执行所有学生年龄加1岁语句，并用 ROWCOUNT 来检测是否有发生更改的行。update studentSET sage=sage+1;s

26、elect ROWCOUNT2 函数的使用(1) 数学函数的使用分别用函数求出-3的绝对值，16的平方根，5的三次方。declare var1 int,var2 real,var3 intset var1=Abs(-3)set var2=Sqrt(16)set var3=Power(5,3)select var1 -3的绝对值,var2 16的平方根,var3 5的三次方go(2) 字符串函数的使用1）分别将字符串china、MACHINE转换成大写、小写字母；select upper(china)select lower(MACHINE)2）去掉字符串 machine 左边的空格，右边与pr

27、ess连接起来；select ltrim( machine )+press(3) 日期、时间函数的使用1）返回系统当前日期并以整数形式返回当前日期的年份、月份、日；select year(getdate() 年,month(getdate() 月,day(getdate() 日2）返回给定日期“2006-2-21”与当前日期相差的天数。select datediff(day,2006-2-21,getdate() (4) 系统函数与元数据函数的使用显示正在使用的用户名select USER_name()3编写较复杂的Transact-SQL程序1) 在 SELECT 语句中，CASE 搜索函数

28、允许根据比较值在结果集内对值进行替换。要求仿照数据库概论的sc表，根据学生的成绩范围将成绩（grade 列）显示为文本注释。（要求：60分，文本注释为“不及格”，6070，“及格”，7080，“中”，8090，“良”，90100，“优”）。selectcase when grade=60 and Grade=70 and Grade=80 and Grade=90 then 优endfrom sc2) 用TransactSQL语言编写程序计算1100之间所有能被7整除的数的总和。declare sum int,i intset sum=0set i=1while i=100begin if i

29、 % 7=0 begin set sum=sum+i; end set i=i+1;endprint sum3) 自选实例，实践并掌握Transact-SQL以下控制流语言的使用方法：BEGIN.END、WHILE、BREAK、CONTINUE、IF.ELSE、WAITFOR4) 自选实例，实践并掌握以下Transact-SQL语句：/*.*/（注释）、-（注释）、PRINT-我的实例（求水仙花数）declare a int,b int,k int,n intset n=99while n999begin set n=n+1 set a=n/100/*求百位*/ set b=n/10-a*10-求十位 set k=n%10/*求个位*/ if(na*a*a+b*b*b+k*k*k) continue/*不是水仙花数,退出当前循环