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

数据库系统原理及应用实验指导书（本科）福建工程学院计算机与信息科学系计算机软件与理论教研室浅诺制作2012年5月目录实验一数据定义语言 (1)实验二 SQL Sever中的单表查询 (5)实验三 SQL Serve中的连接查询 (8)实验四 SQL Serve的数据更新、视图 (12)实验五数据控制（完整性与安全性） (17)实验六语法元素与流程控制 (22)实验七存储过程与用户自定义函数 (28)实验八触发器 (34)实验一数据定义语言一、实验目的1.熟悉SQL Server2000/2005查询分析器。

2.掌握SQL语言的DDL语言，在SQL Server2000/2005环境下采用Transact-SQL实现表的定义、删除与修改，掌握索引的建立与删除方法。

3.掌握SQL Server2000/2005实现完整性的六种约束。

二、实验内容1.启动SQL Server2000/2005查询分析器，并连接服务器。

2.创建数据库: （请先在D盘下创建DB文件夹）1）在SQL Server2000中建立一个StuDB数据库：有一个数据文件：逻辑名为StuData，文件名为“d:\db\”，文件初始大小为5MB，文件的最大大小不受限制，文件的增长率为2MB；有一个日志文件，逻辑名为StuLog，文件名为“d:\db\”，文件初始大小为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数据库的两个文件，进行备份，后面的实验要用到这个数据库。

三、实验总结通过本次实验大致熟悉了SQL Server2000/2005/2008查询分析器.对SQL Server2000/2005实现完整性的六种约束有所了解，但是对表的关联还不是很明白，我会去看书加深理解。

实验二 SQL Sever中的单表查询一、实验目的：1.掌握SQL语言的DML子语言，在SQL Server2000/2005环境下采用Transact-SQL的SELECT语句熟练掌握各种单表查询方法。

二、实验内容：(一) 附加上次实验备份的StuDB数据库和SPJ数据库。

(二) 按照《数据库系统概论》P82页中的学生课程数据库及数据实现以下查询：1.查询所有课程的课号，课程名。

2.查询计算机系年龄不到21岁的所有男生的姓名和出生年份。

3.查询已被学生所选课程的课程号。

4.查询所有有成绩的学生学号和课程号，并为各列分别取相应的中文别名。

(三) 按照《数据库系统概论》P75页中的SPJ数据库及数据实现以下查询：1.查询地点在‘北京’、‘上海’的供应商代码和供应商名字。

2.查询供应商名中包含‘方’这个汉字的供应商信息。

3.查询零件名以‘刀’字结尾，包含三个汉字的零件信息。

4.查询使用了供应商S1所供应零件的工程项目数。

5.查询各个供应商所供应的零件的总数，要求对查询结果降序显示。

6.查询供应了3个以上项目的供应商代码和供应的项目总数。

加上group by直接用COUNT（JNO）>3结果一致三、实验要求：1.根据实验内容认真写好实验报告，记录每个步骤正确的Transact-SQL命令。

2.无须记录正确的查询结果。

但要求记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结通过本次实验，基本上掌握了SQL Sever中的单表查询，但对group by 语句不是很了解。

还有where子句与having短语的区别，作用对象不同，where作用于基本表或视图，having 作用于组，这里的组怎么理解实验三 SQL Serve中的连接查询一、实验目的：1.掌握采用Transact-SQL实现连接查询。

2.掌握等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。

二、实验内容：按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下查询(1)查询使用红色零件的工程名称。

--(1) 查询使用红色零件的工程名称。

SELECT DISTINCT JNAMEFROM P,J,SPJWHERE COLOR='红'AND=AND=;(2)查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。

--(2) 查询每个工程的信息及相应的供应信息（包括列出尚未被供应零件的那些工程）。

SELECT DISTINCT*FROM J LEFT OUTER JOIN SPJ ON=;(3)查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。

--(3) 查询供应工程J1红色零件的供应商号SNO，请用两种方法实现。

第一种：SELECT DISTINCT SNOFROM P,SPJWHERE COLOR='红'AND=AND JNO='J1';第二种：SELECT SNOFROM SPJWHERE PNO IN(SELECT PNOFROM PWHERE COLOR ='红')AND JNO='J1'(4)求供应工程J1零件的供应商的完整信息。

--(4)求供应工程J1零件的供应商的完整信息。

SELECT*FROM SWHERE SNO IN(SELECT SNOFROM SPJWHERE JNO='J1');(5)查询使用北京供应商供应零件的工程信息。

(6)查询选修3号课程且成绩在85分以上的所有学生。

(7)查询先行课的学分为4的课程信息。

(8)查询课程与其间接先行课的名称。

(9)查询其他系中比计算机科学系所有学生年龄都小的学生完整信息，用两种方法实现。

(10)查询其他系中比计算机科学系某一学生年龄大的学生姓名与年龄，用两种方法实现。

(11)（可选）查询至少用了供应商S1所供应的所有零件的工程号JNO，用带EXISTS谓词的子查询实现。

SELECT JNOFROM JWHERE EXISTS(SELECT*FROM SPJWHERE JNO=AND SNO='S1')(12)（可选）查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。

--不会(13)（可选）查询所有使用了P3零件的工程号JNO，用带EXISTS谓词的子查询实现。

SELECT JNOFROM JWHERE EXISTS(SELECT*FROM SPJWHERE JNO=AND PNO='P3')三、实验要求：1.写出正确的Transact-SQL命令。

2.无须记录正确的查询结果。

要求记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结通过实验大致掌握了等值连接（自然连接）、自身连接、外连接和复合条件连接的查询方法。

实验过程中最纠结的就是12题，使用了全部零件就是说pno从p1到p6在同一个工程中都有，那么这该怎么表示呢实验四 SQL Serve的数据更新、视图实验时间：2012-3-27实验地点：c2-101一、实验目的：1.掌握采用Transact-SQL实现嵌套查询。

2.掌握采用Transact-SQL实现数据更新。

3.掌握采用Transact-SQL实现视图的定义、删除、查询与更新。

二、实验内容按照《数据库系统概论》P82页中的学生课程数据库和P75页中的SPJ数据库完成以下操作：（一）数据更新1）一个学生：张红，女，5，信息系，20岁2）一个选课记录：5，1，成绩未定3）算机系所有学生的年龄加1岁4）删除所有0学生的选课记录5）删除所有姓张的同学的信息6）行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约束。

（二）视图1）创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分2）利用上题所建视图实现：①查询成绩高于自己平均成绩的选课记录②查询每个同学获得最高分的选课记录3）请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。

建立该视图：针对该视图完成下列查询：①找出三建工程项目使用的各种零件代码及其数量；②找出供应商S1的供应情况；四、实验小结删除姓张的全部信息时忽视了关系的完整性，没意识到表级完整性约束条件，导致不能直接在Student表中删除记录。

先把SC表中的有关姓张的学生先给删除掉，解除关联，再从student表中删除相关信息。

对于group by的使用仍然不是很清楚。

如以上创建视图的时候为什么要group by ，sname~~等等实验五数据控制（完整性与安全性）一、实验目的：1.通过实验加深对数据库完整性与数据库安全性的理解；2.掌握SQLServer对数据进行安全性控制的方法二、实验内容和要求(一)数据库完整性某公司使用数据库进行内部管理：表Employees存储雇员的代号（整型，标识位，种子1000001，增量为1，主键）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表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(Egz>0))--创建表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 Departments(Dname))(二)以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上，它将作为哪个数据库的用户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操作，为什么（提示：废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。