数据库系统原理及应用答案

数据库系统原理及应用实验指导书

（本科）

福建工程学院计算机与信息科学系

目录

实验一数据定义语言 (2)

实验二SQL Sever中的单表查询 (5)

实验三SQL Serve中的连接查询 (8)

实验四SQL Serve的数据更新、视图 (11)

实验五数据控制（完整性与安全性） (16)

实验六语法元素与流程控制 (19)

实验七存储过程与用户自定义函数 (23)

实验八触发器 (27)

实验一数据定义语言

一、实验目的

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\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数据库的两个文件，进行备份，后面的实验要用到这个数据库。

三、实验总结

通过本次实验大致熟悉了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 JNAME

FROM P,J,SPJ

WHERE 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，请用两种方法实现。

第一种：

SELECT DISTINCT SNO

FROM P,SPJ

WHERE COLOR='红'AND P.PNO=SPJ.PNO AND JNO='J1';

第二种：

SELECT SNO

FROM SPJ

WHERE PNO IN

(SELECT PNO

FROM P

WHERE COLOR ='红'

)AND JNO='J1'

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

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

SELECT*

FROM S

WHERE SNO IN

(SELECT SNO

FROM SPJ

WHERE JNO='J1'

);

(5)查询使用北京供应商供应零件的工程信息。

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

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

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

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

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

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

词的子查询实现。

SELECT JNO

FROM J

WHERE EXISTS

(SELECT*

FROM SPJ

WHERE JNO=J.JNO AND SNO='S1')

(12)（可选）查询使用了全部零件的工程号JNO，用带EXISTS谓词的子查询实现。

--不会

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

FROM J

WHERE EXISTS

(SELECT*

FROM SPJ

WHERE JNO=J.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）一个学生：张红，女，200215135，信息系，20岁

2）一个选课记录：200215135，1，成绩未定

3）算机系所有学生的年龄加1岁

4）删除所有200215130学生的选课记录

5）删除所有姓张的同学的信息

6）行设计案例对学生－课程数据库的数据更新，并观察是否有违反数据的完整性约束。

（二）视图

1）创建一个学生成绩统计视图，包括学号，姓名，选课门数，平均分，最高分，最低分

2）利用上题所建视图实现：

①查询成绩高于自己平均成绩的选课记录

②查询每个同学获得最高分的选课记录

3）请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。

建立该视图：

针对该视图完成下列查询：

①找出三建工程项目使用的各种零件代码及其数量；

②找出供应商S1的供应情况；

四、实验小结

删除姓张的全部信息时忽视了关系的完整性，没意识到表级完整性约束条件，导致不能直接在Student表中删除记录。先把SC表中的有关姓张的学生先给删除掉，解除关联，再从student表中删除相关信息。

对于group by的使用仍然不是很清楚。如以上创建视图的时候为什么要

group by student.Sno，sname~~等等

实验五数据控制（完整性与安全性）

一、实验目的：

1.通过实验加深对数据库完整性与数据库安全性的理解；

2.掌握SQLServer对数据进行安全性控制的方法

二、实验内容和要求

(一)数据库完整性

某公司使用数据库进行内部管理：表Employees存储雇员的代号（整型，标识位，种子1000001，增量为1，主键）、身份证号码（18个字符）、名字（最长20个字符）和工资等信息；表Departments存储部门的部门号（2个字符，唯一，主键）、部门名称（30个字符）等信息；表Work每一行表示某雇员在某部门工作过及其开始工作时间和备注。

请写出创建这三个表的SQL语句，要保证：工资的值大于0，身份证号码唯一，开始工作时间非空，默认值为当前时间。

--创建表Employees

CREATE 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)

)

--创建表Departments

CREATE TABLE Departments

(Dno CHAR(2)PRIMARY KEY,

Dname CHAR(30)UNIQUE

)

--创建表Work

CREATE 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 操作，为什么？

（提示：废除权限是删除已授予的权限，并不妨碍用户、组或角色从更高级别继承已授予的权限。因此，如果废除用户查看表的权限，不一定能防止用户查看该表，因为已将查看该表的权限授予了用户所属的角色。）

可以select 因为s1从超级用户那里也被被授予select的权限

5) 切换回sa用户的查询窗口，输入下面代码并执行

deny select on student to u1

请问该代码含义？

取消u1的查询权限

再切换至s1的查询窗口，能不能对student 表进行查询操作？为什么？

不能，因为角色u1select已经被回收了，这条命令是把s1从超级用户那里获得的select权限也回收了，所以不能进行查询操作了。

(三)请完成以下操作,并记录完整语句。在实验过程注意验证语句的执行效果。

(1)需要为学生－课程数据库，创建一用户，该用户以“SQL Server身份验证”方式登录SQL Server 服务器的账号为学生自己的姓名，密码为“123”。

exec sp_addlogin'xiezhenjia','123'

use studb

(2)给该登录用户映射到数据库用户user2上，并赋予该用户对student表的sno、sname两列的查询权限，对sc表的所有操作权限及对course的查询权限。

（请先执行revoke select on student from public

Revoke all on sc from public

注：新建用户自动被加入到public角色中）

revoke select on student from public

Revoke all on sc from public

exec sp_grantdbaccess'xiezhenjia','user2'

grant select(sno,sname)

on student

to user2

grant select,update,delete,insert

on sc

to user2

grant select

on course

to user2

(3)请分析、验证数据库用户user2的权限。

1）、对student的sno以及sname有查询权限

2）、对sc表有所有权限

3）、对course有查询权限

(4)用户user2授予对course表的插入操作权限，并允许将此权限授予其它用户grant insert

on course

to user2

with grant option

(5)收回授予user2对sc表的所有权限

revoke select,insert,delete,update

on sc

from user2

(6)创建一个角色R1

create role r1

(7)然后使用GRANT语句，使角色R1拥有SC表的SELECT、UPDATE、INSERT grant select,update,insert

on sc

to r1

(8)将这个角色授予user2。使它具有角色R1所包含的全部权限。

grant r1

to user2

三、实验要求：

1)写出正确的Transact-SQL命令。

2)无须记录正确的查询结果。要求记录实验过程中发生的有学习意义的错误及错误信息。

四、实验小结

刚开始在机房里面做的时候附加进来的数据库属性是只读的，不知道是什么情况。就是没有把错误记录下来。通过实验对数据的安全性更为熟悉。

实验六语法元素与流程控制

一、实验目的：

1)掌握Transact-SQL局部变量的声明、赋值和使用方法以及全局变量的运用方法，掌握Transact-SQL的局部变量的作用域概念。

2)掌握Transact-SQL的控制流语言的使用方法。

3)掌握与控制流语言语句一起使用的其它Transact-SQL 语句：

CASE、/*...*/（注释）、--（注释）、PRINT。

二、实验内容和要求

1、使用局部变量、全局变量

(1)定义一个int的整型变量，为其赋值45，并显示变量的值。

declare @var int

set @var=45

select @var 变量

go

(2)定义一个长度为20的可变长度型字符变量，为其赋值“Welcome to FJUT”，并显示变量的值。

declare @var1 varchar(20)

set @var1='Welcome to FJUT'

select @var1 字符变量

go

(3)在批处理中声明两个局部变量＠ssex和@sdept并对它们赋值，对Student表进行查询，要求两局部变量在SELECT 语句的WHERE 子句中被使用，查询的要求是性别为‘女’或所在系为‘IS’系的那些学生。

declare @ssex char(2),@sdept char(20)

set @ssex='女'

set @sdept='IS'

select*from student

where ssex=@ssex or sdept=@sdept

go

(4)查询当前数据库服务器名。

select@@SERVERNAME

结果：

(5)查询当前数据库管理系统版本。

select@@VERSION