《数据库原理及应用》实验报告

《数据库原理及应用》

实验报告

2011～2012学年第二学期

班 级 姓 名 学 号 指导教师

实验一 数据库的描述和定义

一、实验学时 4学时。 二、实验类型 演示、设计类型。 三、实验目的

熟悉和掌握数据库的创建和连接方法；熟悉和掌握数据表的建立、修改和删除；加深对表的实体完整性、参照完整性和用户自定义完整性的理解。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境；

2、复习有关表的建立、修改和删除的SQL语言命令。 六、实验方法及步骤

1、介绍有关SQL Server 2000/2005的图形界面工作环境。 2、创建数据库或连接已建立的数据库。 3、在当前数据库上建立新表。 4、定义表的结构：

（1）用SQL命令形式；

（2）用SQL Server 2000/2005提供的企业管理器以交互方式进行。 5、进一步定义表的主码，外码及在表上建立索引。 6、修改表的结构。 7、删除表的定义。 七、实验内容

在以下实验中，使用课本P82的学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。

1、创建学生-课程数据库 2、创建学生关系表Student

学号 Sno 姓名 Sname 课程名 Cname 课程号 Cno 性别 Ssex 先行课 Cpno 年龄 Sage 所在系 Sdept 学分 Ccredit 成绩 Grade 3、创建课程关系Course 课程号 Cno 4、创建选课关系表SC

学号 Sno 5、在表Student上增加“Birthday”属性列，数据类型为DATETIME。 6、删除表Student的“Sage” 属性列。

1 / 25

8、在表Student上，按“Sno” 属性列的唯一值方式建立索引。 9、在表SC上，按“Grade” 属性列的多值方式建立索引。 八、实验结果

请写出上述各个实验内容的SQL语句。 1、 2、

3、

4、

5、

6、

7、

8、 9、 2 / 25

九、思考题

1、定义表结构是对数据模型的哪个要素进行描述，对主属性有什么要求？ 答：

2、修改表结构时，能否修改已定义属性的参数？新增加的属性列能否定义为非空？为什么？ 答：

成绩 指导教师签名

3 / 25

实验二 数据库中的数据查询

一、实验学时 6学时。 二、实验类型 综合类型。 三、实验目的

熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式；加深理解关系运算的各种操作（尤其是关系的选择，投影，连接和除运算）。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生-课程数据库；

3、复习对表中数据查询的SQL语言命令。 六、实验方法及步骤

1、在表Student、Course、SC上进行简单查询； 2、在表Student、Course、SC上进行连接查询；

3、在表Student、Course、SC上进行嵌套查询； 4、使用聚合函数的查询； 5、对数据的分组查询； 6、对数据的排序查询。 七、实验内容

在表Student、Course、SC上完成以下查询： 1、查询学生的基本信息。

2、查询“CS”系学生的基本信息。

3、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名。 4、找出最大年龄。

5、找出“CS”系年龄最大的学生，显示其学号、姓名。 6、找出各系年龄最大的学生，显示其学号、姓名。 7、统计“CS”系学生的人数。

8、统计各系学生的人数，结果按升序排列。

9、按系统计各系学生的平均年龄，结果按降序排列。 10、查询每门课程的课程名。

11、查询无先修课的课程的课程名和学分数。 12、统计无先修课的课程的学分总数。

13、统计每位学生选修课程的门数、学分及其平均成绩。

4 / 25

14、统计选修每门课程的学生人数及各门课程的平均成绩。

15、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。 16、查询选修了“1”或“2”号课程的学生学号和姓名。

17、查询选修了“1”和“2”号课程的学生学号和姓名。

18、查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩。 19、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）。 20、查询没有选修课程的学生的基本信息。

21、查询选修了3门以上课程的学生学号。

22、查询选修课程成绩至少有一门在80分以上的学生学号。 23、查询选修课程成绩均在80分以上的学生学号。 24、查询选修课程平均成绩在80分以上的学生学号。 八、实验结果

请写出上述各个实验内容的SQL语句。 1、 2、 3、

4、 5、

6、

7、 8、 9、

5 / 25

10、 11、

12、 13、 14、

15、

16、 17、

18、

19、

20、

21、

6 / 25

22、 23、

24、

成绩

指导教师签名

7 / 25

实验三 数据库中的数据更新

一、实验学时 2学时。 二、实验类型 综合、设计类型。 三、实验目的

熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；加深理解表的定义对数据更新的作用。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生-课程数据库；

3、复习对表中数据的插入、修改和删除的SQL语言命令。 六、实验方法及步骤

1、将数据插入当前数据库的表Student、Course、SC中； （1）用SQL命令形式；

（2）用SQL Server提供的企业管理器以交互方式进行。

2、将以上插入的数据分别以.SQL文件和.txt文件的形式保存在磁盘上； 3、修改表Student、Course、SC中的数据；

（1）用SQL命令形式

（2）用SQL Server提供的企业管理器以交互方式进行 4、删除表Student、Course、SC中的数据。 （1）用SQL命令形式；

（2）用SQL Server提供的企业管理器以交互方式进行。 七、实验内容

1、将表Student、Course、SC中的数据分别以.SQL文件或.txt文件的形式保存在磁盘上。 2、 如果要在表SC中插入某个学生的选课信息（如：学号为“200215121”，课程号为“5”，成绩待定），应如何进行？

3、求各系学生的平均成绩，并把结果存入数据库。

4、将“CS”系全体学生的成绩置零。

5、删除“CS”系全体学生的选课记录。 6、删除学号为“S1”的相关信息。

7、将学号为“S1”的学生的学号修改为“S001”。

8、把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S_GRADE（SNO，AVG_GRADE）。

8 / 25

9、把选修了课程名为“数据结构”的学生的各门课成绩提高10%。

10、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%。 11、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉。 八、实验结果

请写出上述各个实验内容的SQL语句。 1、

（1）将表Student的数据以.SQL文件形式保存在磁盘上

（2）将表Student的数据以.txt文件形式保存在磁盘上 2、

3、 4、 5、 6、 7、 8、 9、

9 / 25

10、

11、

九、实验思考

1、如果在表Student 和SC 表上建立了外码联系时，要删除学号为‘S2’的学生记录，应如何进行？如果在表Student 和SC 表上未建立外码联系，情况会是怎样的？

答：（1）当表Student 和SC 表上建立了外码联系时（写出相应的SQL语句）

（2）当表Student 和SC 表上未建立外码联系时（写出相应的SQL语句）

2、在进行数据更新时，可能产生破坏数据完整性的情况，因此，在数据更新（尤其是多表更新）时，应注意什么问题？

答：

成绩 指导教师签名

10 / 25

实验四 视图的定义和使用

一、实验学时 2学时。 二、实验类型 综合、设计类型。 三、实验目的

熟悉和掌握对数据表中视图的定义操作和SQL命令的使用；熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；学习灵活熟练的进行视图的操作，认识视图的作用。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生-课程数据库；

3、复习有关视图操作的SQL语言命令。 六、实验方法及步骤 1、视图的定义； 2、视图的查询； 3、视图的修改。 七、实验内容

以Student表、Course表、SC表为基础完成以下视图定义及使用。

1、定义“IS”系学生基本情况视图V_IS 并查询结果。

2、将Student、Course、SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果。

3、将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果。 4、定义一个反映学生出生年份的视图V_YEAR并查询结果。

5、将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果。 6、将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果。

7、查询平均成绩为90分以上的学生学号、姓名和成绩。

8、查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩。 9、按系统计各系平均成绩在80分以上的人数，结果按降序排列。

10、通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,“S4_MMM”并查询结果。

11、通过视图V_IS，新增加一个学生记录 ('S12','YAN XI','男',19,'IS')，并查询结果。

12、通过视图V_IS，新增加一个学生记录 ('S13','XAN XI',19,'MA')，并查询结果。

11 / 25

13、通过视图V_IS，删除学号为“S12”和“S13”的学生信息，并查询结果。 八、实验结果

请写出上述各个实验内容的SQL语句。 1、 2、

3、

4、 5、

6、 7、 8、

12 / 25

9、 10、 11、 12、

13、

九、实验思考

1、使用视图有什么好处？ 答：

2、要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？ 答：

3、要通过视图V_AVG_S_G，将学号为“S12”的平均成绩改为90分，是否可以实现？ 答：

成绩 指导教师签名

13 / 25

实验五 数据控制（安全性部分）

一、实验学时 2学时 二、实验类型 演示、设计类型。 三、实验目的

SQL的数据控制通过DCL(数据控制语言) 实现。DCL通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。

本次实验了解DCL语言的GRANT和REVOKE语句对数据库存取权限的控制，学会SQL Server 2000/2005的查询分析器中用DCL语言对数据库存取权限进行设定。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生课程数据库；

3、复习有关DCL语言的GRANT和REVOKE语句命令 （1）授予对象权限

启动数据库服务软件SQL Server 2000/2005的查询分析器，用GRANT语句对数据库存取权限进行授权操作，语法格式如下：

GRANT <系统特权组> To <用户组>|PUBLIC 注：PUBLIC指数据库的所有用户。 （2）回收对象权限

用REVOKE语句将DBA(数据库管理员)、DBO(建库用户)授与其它用户对数据库的操作权收回，语法格式如下：

REVOKE <权限组>|ALL PRIVILEGES [ON <对象名>] FROM <用户组名>|PUBLIC

注：ON用于被回收特权的对象，ALL PRIVILEGES指回收所有特权，PUBLIC指数据库的所有用户。 4、建立用户U1、U2、U3、U4、U5、U6、U7。 在SQL Server中建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。（在SYSTEM（即DBA）与七个Connect用户之间进行授权。） 首先，使用系统存储过程sp_addlogin创建新的 SQL Server 登录。例如，为用户 U1创建SQL Server登录，密码为123。

EXEC sp_addlogin 'U1', '123'; 然后，使用系统存储过程sp_adduser向当前数据库中添加新的用户。例如，使用现有的SQL Server登录名'U1'向当前数据库添加用户U1。

EXEC sp_adduser 'U1';

注：上面的EXEC可省略。可以采用同样的方法建立剩下的用户。 六、实验内容

14 / 25

（一）授予权限

1、把查询Student表的权限授给用户U1。

2、把对Student表和Course表的全部操作权限授予用户U2和U3。 3、把对表SC的查询权限授予所有用户。

4、把查询Student表和修改学生学号的权限授给用户U4。

5、把对表SC的INSERT权限授予U5，并允许U5将此权限再授予其他用户。 6、用户U5将对表SC的INSERT权限授予U6,并允许将权限转授给其他用户。

7、用户U6将对表SC的INSERT权限授予U7。

8、在执行完上面七个语句之后，分别以不同用户的身份登录数据库，进行相关操作，检查系统是否许可。

（1）U4更新Student表的学生学号

（2）U7向SC表中插入一条数据：('200215124','4',88)。

（二）回收权限

将授予的权限部分收回，检查回收后，该用户是否真正丧失了对数据的相应权限。 1、收回用户U4修改学生学号的权限。 2、收回所有用户对表SC的查询权限。 3、收回用户U5对SC表的INSERT权限。 七、实验结果

请写出上述各个实验内容的SQL语句。 （一）授予权限 1、 2、

3、 4、 5、 6、

7、 8、（1）

（2）

（二）回收权限 1、

15 / 25

2、收回所有用户对表SC的查询权限。

3、收回用户U5对SC表的INSERT权限。 成绩

指导教师签名

16 / 25

实验六 数据控制（完整性部分）

一、实验学时 2学时 二、实验类型 演示、设计 三、实验目的

熟悉通过SQL对数据进行完整性控制，包括3类完整性、CHECK短语、CONSTRAIN子句、触发器。因为完整性约束绝大部分是在定义表结构时进行的，因此可能需要多次定义表，如果表名发生重复的话，可以先将旧表删除后再建立。

四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生-课程数据库；

3、复习有关DCL语言3类完整性、CHECK短语、CONSTRAIN子句、触发器。 六、实验内容

使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。 （一）实体完整性

1、定义表Student，并将其中的Sno属性定义为主码。 2、定义表SC，将其中的属性Sno,Cno定义为主码。 （二）参照完整性

定义表SC，其中Sno参照表Student的主码Sno，Cno参照表Course的主码Cno。 （三）用户定义完整性

1、列值非空

在定义SC表时，Sno、Cno和Grade属性都不允许取空值（在不特别声明的情况下，非码属性的值是允许取空值的）。

2、列值唯一

建立部门表DEPT，要求部门名称Dname取值唯一，部门编号Deptno属性为主码。

3、CHECK短语指定列值应该满足的条件

定义表Student，属性Ssex的值只允许取“男”或“女”；定义表SC，属性Grade的值定义在0－100之间。

4、定义表Student，要求当学生性别为男时，其名字不能以Ms.打头。

5、CONSTRAINT完整性约束命名子句

（1）定义表Student，要求学号在90000－99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。要求全部用约束命名子句实现。

（2）修改表Student中的完整性限制，去掉对性别的限制，并将年龄的限制由小于30改为小于40。

17 / 25

（四）触发器

1、定义表TAB,并在其上定义触发器TRI，在对TAB的插入和更新前检查，如果插入或更新的值在100－1000之间的话，将值置为50；如果值大于1000的话，则给出新值不允许大于1000的提示。

（1）向表TAB插入数据（150）。

（2）对表中的数据进行更新，将20更新为1500，是否能正常执行，系统会出现什么情况？ 2、删除触发器TRI 七、实验结果

请写出上述各个实验内容的SQL语句。 （一）实体完整性 1、

2、

（二）参照完整性

（三）用户定义完整性 1、 2、

18 / 25

3、 4、

5、（1） （2）

（四）触发器1、

19 / 25

（1）

（2） 2、 成绩

指导教师签名

20 / 25

实验七 存储过程

一、实验学时 2学时 二、实验类型 演示、设计 三、实验目的

1、熟悉使用存储过程来进行数据库应用程序的设计； 2、掌握用户存储过程的创建、执行和删除操作。 四、需用仪器、设备

奔腾及以上微机；安装了Windows 2000/xp操作系统和SQL Server 2000/2005。 五、实验准备

1、熟悉SQL Server 2000/2005工作环境； 2、连接到学生-课程数据库； 3、熟悉使用建立存储过程命令。 六、实验内容

（一）根据SC表和Course表统计离散数学的成绩分布情况，即按照各分数段统计人数 1、创建存储过程

（1）创建需要的表结构

因为存储过程在执行后在客户端并没有返回值，因此需要建立一个表存放执行结果，并返回到客户端显示。

根据实验要求，我们要统计选修了离散数学的学生的成绩分布，因此我们建立表其中第一列division显示成绩分段划分，第二列number显示的是成绩在该分数段的分数。

（2）编写存储过程 2、执行存储过程

（二）统计SC表中任意一门课的平均成绩 1、创建存储过程 （1）创建需要的表结构

根据实验要求，要统计任意一门课的平均成绩，因此要建立表Avggrade，第一列cname显示统计的课程名称，第二列avgg显示选修了该课程的学生平均成绩。

（2）编写存储过程 2、执行存储过程

（三）在SC表中将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。 1、创建存储过程 根据实验要求，在本实验中存储过程的执行不需要在客户端返回结果，因此我们不建立相应的表结构来存放存储过程的执行结果，直接编写存储过程。

（1）修改表SC的结构

21 / 25

由于之前的成绩值为整数型，而修改后的结果为字符型，因此在SC中先增加列newgrade，存放换算后的成绩。

（2）创建存储过程 2、执行存储过程 （四）删除存储过程

存储过程一旦建立，则将被保存在数据库中，便于用户随时、反复调用执行。如果不需要该存储过程的话，可以将其删除。

七、实验结果

请写出上述各个实验内容的SQL语句。

（一）统计离散数学的成绩分布情况，即按照各分数段统计人数 1、创建存储过程 （1）创建需要的表结构

（2）编写存储过程

22 / 25

2、执行存储过程

（二）统计任意一门课的平均成绩 1、创建存储过程 （1）创建需要的表结构

（2）编写存储过程

23 / 25

2、执行存储过程

（三）在表SC中将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。 1、创建存储过程 （1）修改表SC的结构

（2）创建存储过程

24 / 25

2、执行存储过程

（四）删除存储过程

删除以上建立的三个存储过程。1、 2、 3、 成绩

指导教师签名

25 / 25

2、执行存储过程

（四）删除存储过程

删除以上建立的三个存储过程。1、 2、 3、 成绩

指导教师签名

25 / 25

本文来源：https://www.bwwdw.com/article/w75r.html