数据库上机实验指导

数据库原理实验指导书

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

一、实验目的：

(1) 掌握数据库模式设计，依据实际要求设计表结构，建立表的

关系; 比较SQL命令方式和可视化环境管理器方式设计的异

同点。

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

语言的查询语句的理解。熟练掌握简单表的数据查询、数据

排序和数据连接查询的操作方法。

二、实验内容：

1、分别用sql查询分析器和企业管理器创建表，并设定相应的约束。

要求：在学生管理数据库XSGL中创建如下三个表。

创建名为student(学生信息)的表，表中的各列要求如下：

字段名称字段类型大小说明

Sno Char10主键

Sname Char8

Sex Char2默认值为男，只能

输入男和女

Sage Int

Sdept Char20

创建名为course（课程信息）的表，表中的各列要求如下：

字段名称字段类型大小说明

Cno Char10主键

Cname Char30唯一性

Ccredit Real

创建名为score（学生成绩）的表，表中的各列要求如下：

字段名称字段类型大小取值范围说明

主键

Sno Char10数据来自学生信

息表

主键

Cno Char10数据来自课程信

息表

Grade Real0--100

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

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 https://www.360docs.net/doc/a318253679.html,o=https://www.360docs.net/doc/a318253679.html,o

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 https://www.360docs.net/doc/a318253679.html,o=https://www.360docs.net/doc/a318253679.html,o

and https://www.360docs.net/doc/a318253679.html,o=3 and Grade>80

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

use student;

select https://www.360docs.net/doc/a318253679.html,o as '课程号',https://www.360docs.net/doc/a318253679.html,ame as '课程',https://www.360docs.net/doc/a318253679.html,ame as '先行课'

from Course a,Course b

where a.Cpno=https://www.360docs.net/doc/a318253679.html,o

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

三、实验方法

将查询需求用SQL语言表示；在SQL Server查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为Standard

Execute(标准执行)或Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修

改，直到正确为止。

实验2 数据库的复杂查询实验1．实验目的

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

2．实验内容

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

具体完成以下例题。将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。

1）求选修了数据库的学号和姓名

use student;

select Sname,Cname

from SC,Student,Course

where https://www.360docs.net/doc/a318253679.html,o=https://www.360docs.net/doc/a318253679.html,o 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 https://www.360docs.net/doc/a318253679.html,o=https://www.360docs.net/doc/a318253679.html,o

)

)

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

3．实验步骤

将查询需求用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

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

（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 授予学生表的增、删、改、查权限。

操作步骤：选择“学生选课”数据库中的“表”节点，选择“学

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