数据库上机实验指导

数据库原理实验指导书

实验1 数据库模式设计及建立

一、实验目的：

(1) 掌握数据库模式设计，依据实际要求设计表结构，建立表的关系; 比较SQL命

令方式和可视化环境管理器方式设计的异同点。

(2) 掌握SQL Server查询分析器的使用方法，加深对SQL和SQL语言的查询语句

的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

二、实验内容：

1、分别用sql查询分析器和企业管理器创建表，并设定相应的约束。 要求：在学生管理数据库XSGL中创建如下三个表。 创建名为student(学生信息)的表，表中的各列要求如下： 字段名称 Sno Sname Sex Sage Sdept 字段类型 Char Char Char Int Char 大小 10 8 2 20 说明 主键 默认值为男，只能输入男和女 创建名为course（课程信息）的表，表中的各列要求如下： 字段名称 Cno Cname Ccredit 字段类型 Char Char Real 大小 10 30 说明 主键 唯一性 创建名为score（学生成绩）的表，表中的各列要求如下： 字段名称 Sno Cno Grade

2、创建score –>student表，以及score –>course表的外部键。

字段类型 Char Char Real 大小 10 10 取值范围 数据来自学生信息表 数据来自课程信息表 0--100 说明 主键 主键 3、增加、修改和删除字段，要求：

1) 给student表增加一个memo(备注)字段，类型为varchar(200)。 2) 将memo字段的类型修改为varchar(300)。

3) 删除memo 字段。

4、 简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。

具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。

1) 求数学系学生的学号和姓名 2) 求选修了课程的学生学号

3) 求选修课程号为‘C1’的学生号和成绩，并要求对查询结果按成绩的降序排列，

如果成绩相同按学号的升序排列。 use student;

select Student.Sno,Grade from SC,Student

where Cno='3' and SC.Sno=Student.Sno order by Grade asc ,student.Sno desc

4) 求选修课程号为‘3’且成绩在80~90之间的学生学号和成绩，并成绩乘以0.8

输出。

use student;

select distinct Student.Sno,Grade*0.8 from SC,Student

where Cno='3' and student.Sno=SC.Sno and grade>80 and grade<90

5) 求数学系或计算机系姓张的学生的信息。

use student;

select Sno,Sname,Sage,Sdept from Student

where Sname like '张%'and (Sdept='MA'or Sdept='CS')

6) 求缺少了成绩的学生的学号和课程号

use student; select Sno from SC

where Grade is NULL

5、 连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内

连接、左连接、右连接和自连接等。

1） 查询每个学生的情况以及他所选修的课程

use student;

select Student.Sno,Ssex,Sname,Sage,Grade

from Student left JOIN SC ON (Student.Sno=SC.Sno)

2） 求学生的学号、姓名、选修的课程及成绩

use student;

select Student.Sno as '学号',Sname as '姓名', Cname as '选修的课程',Grade as '得分' from SC,Student,Course

where Student.Sno=SC.Sno and SC.Cno=Course.Cno

3） 求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩

use student;

select Student.Sno as '学号',Sname as '姓名', Cname as '选修的课程',Grade as '得分' from SC,Student,Course

where Student.Sno=SC.Sno and SC.Cno=Course.Cno and SC.Cno=3 and Grade>80

4） 查询每一门课程的间接先行课（即先行课的先行课）

use student;

select a.Cno as '课程号',a.Cname as '课程',b.Cname as '先行课' from Course a,Course b where a.Cpno=b.Cno

注意：如果要是修改为NULL 可按Ctrl+0

三、实验方法

将查询需求用SQL语言表示；在SQL Server查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard Execute(标准执行)或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。

实验2 数据库的复杂查询实验

一．实验目的

本实验的目的是使学生进一步掌握SQL Server查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合的操作方法。

二．实验内容

1、在SQL Server查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。

具体完成以下例题。将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。 1） 求选修了数据库的学号和姓名

use student;

select Sname,Cname from SC,Student,Course

where SC.Cno=Course.Cno and Student.Sno=SC.Sno and Cname='信息系统'

2） 求3课程的成绩高于刘晨的学生学号和成绩 use student;

select Sname,Student.Sno,Grade from SC,Student

where Student.Sno=SC.Sno

and Cno='3'and Grade>( select Grade from SC,Student

where Student.Sno=SC.sno

and Sname like'刘晨' and Cno='3')

3） 求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者

的学生）

use student;

select distinct Sname,Sno,Sdept,Sage from Student

where Sdept<>'CS' and Sage<( select max(Sage) from Student where Sdept='CS' )

4） 求其他系中比计算机系学生年龄都小的学生 use student;

select Sname,Sage from Student where Sage>any( select Sage from Student where Sdept='CS' ) and Sdept<>'CS'

5） 求选修了C2课程的学生姓名 use student;

select distinct Student.Sno from Student,SC where exists( select * from SC

where Student.Sno=SC.Sno and Cno='3')

6） 求没有选修3课程的学生姓名 use student;

select distinct Student.Sno from Student,SC where not exists( select * from SC

where Student.Sno=SC.Sno and Cno='3')

7） 查询选修了全部课程的学生姓名 use student; select Sno from SC

group by Sno having count(*)>=3

8） 求至少选修了学号为“95002”的学生所选修全部课程的学生学号和姓名 use student;

select Sname,Sno from Student where not exists(

select *

from SC SCY

where SCY.Sno='95002' and not exists (

select *

from SC SCZ

where SCZ.Sno=Student.Sno and SCZ.Cno=SCY.Cno ) )

2、进行分组查询，包括分组条件表达、选择组条件表达的方法；进行函数查询，包括统计函数和分组统计函数的使用方法；进行集合查询，包括并操作UNION、交操作INTERSECT和差操作MINUS的使用方法。

具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。 1） 求学生的总人数 use student;

select count(distinct Sno) from Student

2） 求选修了课程的学生人数 use student;

select count(distinct Sno) from SC

3） 求课程和选修了该课程的学生人数 use student;

select Cno,count(*) from SC

group by Cno

4） 求选修超过2门课的学生学号 use student;

select Cno,count(*) from SC

group by Cno having count(*)>2

5） 查询计算机科学系的学生及年龄不大于20岁的学生 use student;

select Sno,Sname from Student

where Sdept='CS' and Sage<20

6） 查询计算机科学系的学生与年龄不大于20岁的学生的交集 use student;

select Sno,Sname from Student

where Sdept='CS' and Sage<20

7） 查询计算机科学系的学生与年龄不大于20岁的学生的差集 use student;

select Sno,Sname from Student

where Sdept='CS' and Sage>20

8） 查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的交集 use student; select Sno from SC

where Cno='1' and Sno in (select Sno from SC

where Cno='2')

9） 查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的差集 use student;

select student.Sno,Sname from SC,Student

where student.sno=sc.sno and Cno='1' and sc.Sno not in (select Sno from SC

where Cno='2') 三．实验步骤

将查询需求用SQL语言表示：在SQL Server查询分析器的输入区中输入SQL查询语句：设置查询分析器的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。

实验3 数据库的更新和视图定义及维护实验

一、实验目的

本实验的目的是要求学生熟练掌握使用SQL 和通过SQL Server企业管理器向数据库输入数据、修改数据和删除数据的操作，同时使学生掌握视图的定义与维护操作，加深对视图在关系数据库中的作用的理解。

二、实验内容

1、具体完成以下例题。将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。

1） 将一新学生记录(学号：95050；姓名：陈冬；性别：男；年龄：18；所在系：计算

机科学系)插入学生表中。

insert

into student (sno,sname,ssex,sdept,sage) values('95007','陈东','男','is',18)

2） 插入一条选课记录(‘95020’,’C1’)。 insert into SC

values('95007','2',NULL)

3） 对每一个系，求学生的平均年龄，并把结果存入数据库。对于这道题，首先要在数

据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。

CREATE TABLE Deptage（所在系 CHAR（20），平均年龄SMALLINT）; 然后对数据库的学生表按所在系分组求平均年龄，再把所在系和平均年龄存入新表

中。 use student;

create table av_age(sdept char(15), age int);

insert into av_age(sdept,age) select Sdept,avg(Sage) from student group by Sdept

3） 将学生95001的年龄改为22岁。

use student; update student set Sage=22

where Sno=95001

4） 将所有学生的年龄增加一岁。 use student; update student set Sage=Sage+1

5） 将计算机科学系全体学生的成绩置零。 use student; update SC set Grade=0

where 'CS'=(select Sdept from student

where student.Sno=SC.Sno)

6） 删除学号为95006的学生记录 use student;

delete from student where Sno='95006'

7） 删除所有的学生选课记录 use student; delete from SC

8） 删除计算机科学系所有学生的选课记录 use student delete from SC

where 'CS'=(select Sdept from student

where student.Sno=SC.Sno ) 2、具体完成以下例题。

1） 建立信息系学生的视图 create view CS_stu as

select Sname,Sdept from student

where Sdept='CS'

2） 建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息

系的学生。

create view IS_stu as

select Sname,Sdept from student where Sdept='IS' with check option

3） 建立信息系选修了’C1’号课程的学生视图 create view IS_student as

select Student.Sno,Sname,Sdept from student,SC

where Sdept='IS' and student.Sno=SC.Sno with check option

4） 建立信息系选修了’C1’号课程且成绩在90分以上的学生视图 create view IS_student as

select Student.Sno,Sname,Sdept,Grade from student,SC

where Sdept='IS' and student.Sno=SC.Sno and Grade>90 with check option

5） 定义一个反映学生出生年份的视图 create view BIR_stu(Sno,Sname,Sbir) as

select Sno,Sname,2011-Sage from student

三、实验步骤

将查询需求用SQL语言表示；在SQL查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard Execute（标准执行）或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。

实验四 存储过程和触发器的使用

一、实验目的：

本实验要求学生应了解存储过程和事务的作用，基本掌握存储过程和事务的创建和使用方法，要求学生了解触发器的知识与作用，基本掌握触发器的创建方法。要求学生在已建好的学生管理数据库中创建3个分别用于插入、删除和更新的触发器。 二、实验内容： 1、存储过程例题

（1）创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。 create procedure proc_1 as begin

select * from student end

exec proc_1

（2）创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时决定。然后调用该存储过程。

create procedure proc_2(@num int,@name varchar(20),@sex varchar(20),@age int,@dept varchar(20)) as begin

insert into student

values(@num,@name,@sex,@age,@dept) end

exec proc_2 95008,李冰,男,19,CS

（3）创建一个名为“proc_3”的存储过程，用于删除学生表中指定学号的记录，具体学号由调用时决定。然后调用该存储过程。 create procedure proc_3(@num varchar) as begin delete

from student

where Sno=@num end

exec proc_3 95008

（4）修改存储过程“proc_4”，用于查询不小于指定成绩的学生的基本信息，具体成绩由调用时决定。\\

create procedure proc_5(@grade varchar) as begin

select sname from student,SC

where SC.Sno=student.Sno and Grade>@grade end

exec proc_6 50 2、触发器例题

（1） 创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自

动显示学生表的所有信息。

create trigger trig_1 on student for insert as begin

select * from student end

insert into student

values(95008,'李冰','男','18','CS')

（2） 创建一个名为“trig_2”的触发器，当试图向学生表添加、修改或删除记

录时，该触发器自动显示如下信息：“对不起，你无权进行更新操作！”。 create trigger trig_2 on student for insert,update,delete as

begin

print '对不起，你无权进行更新操作！' roll back end

（3） 创建一个名为“trig_3”的触发器，当向学生表删除记录时，该触发器自

动删除成绩表中与之相关的所有记录。 create trigger trig_3 on student for delete as begin delete from SC

where Sno in(select Sno from deleted) end

（4）创建一个名为“trig_4”的触发器，当向成绩表添加记录时，该触发器自动显示与该记录相关的学生的学号、姓名和班级。

实验五 安全管理

一、实验目的

本实验要求学生掌握SQL Server2000三级安全体系及验证模式的设置，掌握SQL Server2000服务器登录帐号及数据库用户帐号的管理，掌握创建用户自定义数据库角色并为其授权，掌握为数据库用户授权的方法 二、实验内容

1、 创建SQL Server 服务器登录账号

操作步骤：展开SQL Server 服务器，右击安全性节点下的“登录”，选择“新建登录“，在弹出的“登录属性”对话框中，选择一个windows用户，单击“确定”按钮，就可以创建一个登录账号。

2、 创建数据库用户：为“学生选课”数据库添加用户:zhang。

操作步骤：选择“学生选课”数据库，右击“用户”节点，选择“新建数据库用户”，在弹出的“数据库用户属性—新建用户”对话框中，在“登录名”右边的下拉列表框中选择一个登录名，可以在“用户名”后的文本框输入数据库用户名。数据库用户名可以和选中的登录名相同，也可以不同。

3、 创建数据库角色：为“学生选课”数据库创建一用户自定义数据库角色

s_c_reader,该角色的权限是可以查询学生表及选课成绩表的信息。 操作步骤： 展开“学生选课”数据库，右击“角色”节点，选择“新建数据库角色”，弹出的“数据库用户角色—新建角色”对话框，在“名称”下面的文本框输入数据库角色的名称，单击“确定”按钮即可。 可以将数据库用户添加到数据库角色中。

为数据库角色授权：选中一个具体的数据库角色，单击鼠标右键，选择“属性”，在弹出的“数据库角色属性”对话框中，单击“权限”按钮，在弹出的对话框中设置数据库角色的权限。

4、 数据库对象操作权限的授予和回收：为数据库用户zhang 授予学生表的

增、删、改、查权限。

操作步骤： 选择“学生选课”数据库中的“表”节点，选择“学生”表，单击鼠标右键，选择“所有任务”中的“管理权限”命令，在弹出的对话框中设置各个数据库用户的操作权限。

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