SQL数据库系统实验报告(含代码、截图)

数据库系统 实验报告

专 业 班 级 学生姓名及学号 课程教学班号 任 课 教 师 实验指导教师 实

验

地

点

2012 ~2013 学年第 二 学期

实验一 使用向导创建和删除数据库

一． 实验目的

1． 熟悉SQL Server 中SQL Server Management Studio的环境 2． 了解SQL Server 数据库的逻辑结构和物理结构 3． 掌握使用向导创建和删除数据库的方法 二． 实验要求

1. 熟练使用SSMS进行数据库的创建和删除操作。 2. 完成实验报告。 三．实验内容

设有一学籍管理系统，其数据库名为“EDUC”。

初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长； 日志文件初始为2MB，最大可增长到5MB，按1MB增长。

数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存

放路径为“E:\\sql_data”(注意：此文件名必须已经建立的前提下才可以此操作)。 日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“E:\\sql_data”。 四．实验步骤

1．使用SQL Server Management Studio（简称SSMS）创建数据库。 （1）启动SSMS

在开始菜单中：所有程序－SQL Server 2005 －SQL Server Management Studio

单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。

（2）建立数据库

在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。

2. 使用向导删除上面建立的数据库。

用SSMS删除建立的数据库EDUC。 3、数据库的分离

将刚建好的数据库分离出来，即点击新建的EDUC——任务——分离，将删除连接和更新打一个钩，然后点击确定。如图所示：

4、数据分离出来之后可以附加进去 。

即右击数据库——附加——点击添加按钮，

找到数据库文件.mdf所存放的路径，然后点击确定，即可以将我们刚所创建的文件添加回去。

五．实验总结

通过本次实验，我熟悉了SQL Server 中SQL Server Management Studio的环境，了解了SQL Server 数据库的逻辑结构和物理结构，掌握使用向导创建和删除数据库的方法。加深了对数据库的认识和理解。

实验二 使用SQL语句创建和删除数据库

一. 实验目的：

1. 了解SQL Server 2005数据库的逻辑结构和物理结构。 2. 掌握使用SQL 语句创建、删除和修改数据库。 二. 实验要求

1. 熟练使用查询分析器进行数据库的创建、删除和修改操作。 2. 完成实验报告。 三. 实验内容

使用SQL语句创建数据库userdb1。 四．实验步骤

1．创建数据库userdb1的SQL语句，

create database userdb1 on

(name=userdb1_data,

filename='g:\\xygl\%userdb1.mdf' , size=5, maxsize=10, filegrowth=1) log on

( name=userdb1_log,

filename='g:\\xygl\%userdb1.ldf ' , size=2 , maxsize=5 , filegrowth=1)

执行上述语句建立数据库userdb1

2. 根据步骤1的sql语句，写出创建实验1中数据库EDUC的sql语句，并建立数据库EDUC.

初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长； 日志文件初始为2MB，最大可增长到5MB，按1MB增长。 create database EDUC on

实验四 数据更新操作

一. 实验目的

1． 熟悉使用UPDATE/INSERT/DELETE语句进行表操作； 2． 能将这些更新操作应用于实际操作中去； 二. 实验要求

1． 完成下面的实验内容，并提交实验报告； 2． 在实验报告中附上相应的代码； 三. 实验内容

（1）向表（Class）中插入数据

ClsNO CS01 CS02 MT04 PH08 GL01 ClsName 计算机一班 计算机二班 数学四班 物理八班 地理一班 Director 张宁 王宁 陈晨 葛格 张四 Specialty 计算机应用 计算机应用 数学 物理 应用地理

插入数据之后使用命令：Select * from Class; 检查插入数据的正确性

(2) 向表（Student）中插入数据 Sno 20090101 20090102 20090306 19940107 Sname 王军 李杰 王彤 吴杪 Ssex ClsNO 男 男 女 女 CS01 CS01 MT04 PH08 Saddr 下关40# 江边路96# 中央路94# 莲化小区74# Sage 20 22 19 18 Height 1.76 1.72 1.65 1.60

插入数据之后使用命令：Select * from Student; 检查插入数据的正确性

（3）向表（Course ）中插入数据 Cno 0001 0003 0007

Cname 高等数学 计算机基础 物理 Cpno Null 0001 0001 Credit 6 3 4 插入数据之后使用命令：Select * from Course; 检查插入数据的正确性

（4）向表（SC ）中插入数据

SNO 20090101 20090101 20090102 20090102 20090306 20090306 19940107 20090306

（5） 对于student表，将所有班级号为‘CS01’的，并且年龄小于20岁的学生的班级

号改为‘CS02’。 （6） 对于student表，删掉所有年龄不小于20岁，并且专业号为‘CS02’的学生的记录。

对于student表，插入一条新记录，它的具体信息为，学号：20071101、姓名：张三、性别：男、年龄：19、班级编号：‘CS01’。 （7） 对于student表，将年龄最小的学生的家庭地址去掉。 （8） 对于student表，将平均年龄最小的一个班级编号改为‘GL01’

四、实验步骤：

1.向class表插入数据如下：

insert

into class(clsno,clsname,director,specialty) values('cs01','计算机一班'，'张宁'，'计算机应用'); insert

into class(clsno,clsname,director,specialty) values('cs02','计算机二班'，'王宁'，'计算机应用'); insert

into class(clsno,clsname,director,specialty) values('mt04','数学四班'，'陈晨'，'数学'); insert

into class(clsno,clsname,director,specialty) values('ph08','物理八班'，'葛格'，'物理'); insert

into class(clsno,clsname,director,specialty) values('gl01','地理一班'，'张四'，'应用地理');

CNO 0001 0007 0001 0003 0001 0003 0007 0007 Grade 90 86 87 76 87 93 85 90

2.向Student表插入数据如下：

insert

into student(sno,sname,scsex,clsno,saddr,sage，height)

values('20090101','王军'，'男'，'cs01','下关40#'，'20','1.76'); insert

into student(sno,sname,scsex,clsno,saddr,sage,height)

values('20090102','李杰'，'男'，'cs01','江边路96#'，'22','1.72'); insert

into student(sno,sname,scsex,clsno,saddr,sage,height)

values('20090306','王彤'，'女'，'mt04','中央路94#'，'19','1.65'); insert

into student(sno,sname,scsex,clsno,saddr,sage,height)

values('19940107','吴杪'，'女'，'ph08','莲化小区74#'，'18','1.60');

3. 向course表插入数据如下：

insert

into course(cno,cname,cpno,ccredit) values('0001','高等数学'，'null'，'6'); insert

into course(cno,cname,cpno,ccredit) values('0003','计算机基础'，'0001'，'3'); insert

into course(cno,cname,cpno,ccredit) values('0007','物理'，'0001'，'4');

4.向表SC 中插入数据

insert

into sc(sno,cno,grade)

values('20090101','0001'，'90'); insert

into sc(sno,cno,grade)

values('20090101','0007'，'86'); insert

into sc(sno,cno,grade)

values('20090102','0001'，'87'); insert

into sc(sno,cno,grade)

values('20090102','0003'，'76'); insert

into sc(sno,cno,grade)

values('20090306','0001'，'87'); insert

into sc(sno,cno,grade)

values('20090306','0003'，'93'); insert

into sc(sno,cno,grade)

values('19940107','0007'，'85'); insert

into sc(sno,cno,grade)

values('20090306','0007'，'90');

5.对于student表，将所有班级号为‘CS01’的，并且年龄不大于20岁的学生的班级号改为‘CS02’

update student set clsno = 'cs02'

where clsno = 'cs01' and sage<20;

6. 对于student表，删掉所有年龄不小于20岁，并且专业号为‘CS02’的学生的记录。

如果建表的时候没写级联删除，就在删除前先添加级联删除语句

delete from student

where clsno = 'cs02' and sage >= 20;

对于student表，插入一条新记录，它的具体信息为，学号：20071101、姓名：张三、性别：

男、年龄：19、班级编号：‘CS01’。

insert

into student(sno,sname,scsex,clsno,saddr,sage，height) values('20071101','张三'，'男'，'cs01',null，'19',null);

7.对于student表，将年龄最小的学生的家庭地址去掉。

update student set saddr = 'null' where sage in

(select min(sage) from student)

8.对于student表，将平均年龄最小的一个班级编号改为‘GL01’

update student set clsno = 'gl01' where sage <=

(select min(avg(sage)) from student

group by student.clsno);

9、 对于student表，将学号为19940107的学生的姓名该成“吴用”；

update student set sname = '吴用' where sno = '19940107';

10、对于sc表，将学号为20090101且的成绩为86的学生的成绩改为96

update sc set grade = '96'

实验七 使用聚集函数的SELECT语句

一． 实验目的

1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL语言的嵌套查询语句的理解。 二． 实验要求c

1.在实验之前做好准备。

2.完成实验，并验收实验结果提交实验报告。 三． 实验内容

在数据库EDUC中用SQL语句实现如下查询：

1)求学生的总人数。

2)求选修了课程的学生人数。

3)求课程的课程号和选修该课程的人数。 4)求选修课程超过2 门课的学生学号。

四. 实验步骤 1) 求学生的总人数.

select count(*) from student;

2) 求选修了课程的学生人数。

select count(distinct sno) from sc;

3)求课程的课程号和选修该课程的人数。

select cno,count(sno) from sc group by cno;

4)求选修课程超过2 门课的学生学号

select sno from sc group by sno having count(*)>2;

五、实验总结

通过本次实验，我了解了SELECT语句的GROUP BY和ORDER BY子句的作用。掌握了统计函数和

分组统计函数的使用方法。熟悉了查询分析器的运行环境。

实验八 视图的定义与使用

一． 实验目的

1. 熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；

2. 熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更

新的区别与联系；

3. 学习灵活熟练的进行视图的操作，认识视图的作用。 二．实验要求

1. 在实验开始之前做好准备工作。

2. 实验之后提交实验报告，思考视图和基本表的区别 三．实验内容 1.定义视图

在EDUC数据库中，已Student、Course 和SC表为基础完成一下视图定义:

1） 将Student, Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图

V_SC_G；

2） 定义一个反映学生出生年份的视图V_YEAR；

3） 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G； 4） 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G； 5） 在原来的V_SC_G中增加一个属性列班级号 2.使用视图

1) 查询以上所建的视图结果。

2) 查询平均成绩为90分及以上的学生学号、姓名和成绩；

3) 查询科目成绩大于平均成绩的学生学号、课程号、成绩和平均成绩； 4) 查询1994年出生的学生学号和姓名。

5) 查询班级号为CS01选修了0001号课程的学生学号，姓名和成绩 四． 实验步骤 1.定义视图

1) 将Student, Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图

V_SC_G.

create view v_sc_g(sno,sname,cno,cname,grade) as

select sc.sno,sname,sc.cno,cname,grade from student,course,sc

where student.sno = sc.sno and sc.cno = course.cno;

2)定义一个反映学生出生年份的视图V_YEAR.

create view v_year(sname,year)

as

select sname,2013-sage from student;

3)将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G. create view v_avg_s_g(sno,cnono,avggrade)

as

select sno,count(cno),avg(grade) from sc group by sno;

4)将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。

create view v_avg_c_g(cno,cnono,avggrade) as

select sc.cno,count(sc.cno),avg(grade) from sc,course

where course.cno = sc.cno group by sc.cno;

5)在原来的V_SC_G中增加一个属性列班级号

2.使用视图

1)查询以上所建的视图结果。

2)查询平均成绩为90分及以上的学生学号、姓名和成绩；

select v_sc_g.sno,sname,grade from v_sc_g where sno in

(select v_avg_s_g.sno from v_avg_s_g

where v_sc_g.sno = v_avg_s_g.sno and v_avg_s_g.avggrade>90);

3)查询科目成绩大于其科目平均成绩的学生学号、课程号、成绩和平均成绩；

select sc.sno ,sc.cno,sc.grade,savggrade from v_avg_c_g,sc,v_avg_s_g

where sc.cno = v_avg_c_g.cno and sc.grade > v_avg_c_g.avggrade and sc.sno=v_avg_s_g.sno;

4)查询1994年出生的学生学号和姓名。 select sno,student.sname

from student,v_year

where student.sname = v_year.sname and year = '1994';

5)查询班级号为CS01选修了0001号课程的学生学号，姓名和成绩 select student.sno,v_sc_g.sname,v_sc_g.grade

from v_sc_g,student

where student.sno = v_sc_g.sno and v_sc_g.cno = '0001' and student.clsno = 'cs01';

五、实验总结

通过本次实验，我掌握了对数据表中视图的查询操作和SQL命令的使用；熟悉了对数据表中视图的更新操作和SQL命令的使用，体会到了视图的作用。

实验九 触发器的创建与使用

一、实验目的

本实验的目的是使学生进一步掌握SQL Server触发器的创建及使用方法，加深SQL触发器的理解。通过对数据的更新操作体会其触发器的作用。 二、实验要求

1. 在实验之前做好准备

2. 试验之后提交实验报告，并验收实验结果 三、实验内容

1. 在班级表class中增加班级人数（c_total）字段 Alter table class

2. 创建INSERT触发器t_inst_stu

3. 创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。 4. 创建UPDATE触发器t_update_stu。 5. 体会触发器的作用。

四、实验步骤

1. 在班级表class中增加班级人数（c_total）字段，默认值是0； 并手动统计人数作为该字段的值；

alter table class

add c_total int default 0;

update class set c_total = '3'

where class.clsno = 'cs01'; update class set c_total = '1'

where class.clsno = 'gl01'; update class set c_total = '1'

where class.clsno = 'mt04';

2. 创建INSERT触发器t_inst_stu：在学生表(student)中每新增一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动加1。

create trigger update_stu after insert on student for each row begin

if(:new.clsno is not null)then update class

set c_total = c_total + 1 where :new.clsno = class.clsno; end if; end;

insert

into student(sno,sname,scsex,clsno,saddr,sage，height) values('20071102','李四'，'男'，'cs01',null，'17',null);

3. 创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。

create trigger t_dele_stu after delete on student for each row begin

if(:old.clsno is not null)then update class

set c_total = c_total - 1 where :old.clsno = class.clsno; end if; end;

4. 创建UPDATE触发器t_update_stu:当某学生所在班号发生变化时（即调到另一班级后），将其原先所在班级的人数（c_total）减1，将新调入的班级班级的人数（c_total）加1。

create trigger t_update_stu after update on student for each row begin

if(:old.clsno <> :new.clsno)then update class

set c_total=c_total-1

where :old.clsno = class.clsno; update class

set c_total=c_total+1

where :new.clsno = class.clsno; end if; end;

五、实验总结

通过本次实验，我了解了触发器的相关原理，掌握触发器的定义，理解了触发器的使用方法及其特点。

实验十 存储过程的创建与使用

一、实验目的

本实验的目的是使学生进一步掌握SQL Server存储过程的创建及使用方法，加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。 二、实验要求

1. 在实验之前做好准备

2. 试验之后提交实验报告，并验收实验结果 三、实验内容

1、创建一个不带参数的存储过程p_stu_info1，实现对满足要求的学生基本信息的查询。 2、创建一个带有参数的存储过程p_stu_info2，实现对满足要求的学生基本信息的查询。 3、创建一个存储过程p_stu_info3,根据输入的学号，查询某学生的基本信息。 4、创建一个存储过程p_stu_grade,根据输入的学号，返回其选课及其成绩。

5.使用SQL语句分别执行p_stu_info1、p_stu_info2、p_stu_info3和p_stu_grade，并将结果显示出来。 四、实验步骤

1.建立不带参数的存储过程p_stu_info1（选择所有年龄<21岁的男同学）。

create procedure p_stu_info1 as begin

select * from student where sage<21 and ssex='男'

end

执行语句：exec p_stu_info1

2.建立带有参数的存储过程p_stu_info2，根据传递的参数只显示与指定学号的学生同龄的所有同学（如：20071101，自己可以选别的学号，下同）。

create procedure p_stu_info2 @sno char(8) as begin

declare @sage numeric(3,0)

select @sage=sage from student where sno=@sno select * from student where sage=@sage end 执行：

exec procedure '20071101'

3.创建一个存储过程p_stu_info3，根据传入的学号（如：20071101），查询某学生的基本

信息。

create procedure sc_avg @sno char(10),@avg int output AS begin

select @avg=avg(grade) from sc where sno=@sno group by sno; print('ok ! 学号：' + @sno+' 的数据已处理！'); end; declare

@tmp char(10),@avg_out int set @tmp='20090001'

exec sc_avg @tmp,@avg_out output

4. 创建一个存储过程p_stu_grade,根据输入的学生学号（如：20071101），返回其选课及其成绩。

create procedure p_getgrade(sid sc.sno%type, cid sc.cno%type) is

res number; begin

res:=get_grade(sid,cid); case

when res>=0 then

dbms_output.put_line('课程成绩 = ' || res); when res=-1 then

dbms_output.put_line('指定的数据不存在' ); else

dbms_output.put_line(sqlcode || '-------' || sqlerrm); end case; end;

5.使用SQL语句分别执行p_stu_info1、p_stu_info2、p_stu_info3和p_stu_grade，并将结果显示出来。

五、实验总结

通过本次实验，我了解了存储过程的相关知识，掌握存储过程的定义，理解了存储过程的调用及其参数的使用方法。

实验十一 用户管理操作

一、实验目的

本实验的目的是使学生进一步掌握SQL Server用户角色及权限的使用方法，加深SQL用户管理的理解。SQL Server的特定数据库都有自己的用户和角色（用户组），该数据库只能由它的用户或角色访问，其他用户无权访问该数据库数据，数据库系统可以通过创建和管理特定的数据库的用户和角色来保证数据库不被非法用户访问。

二、实验要求

1.实现对SQL Server的用户和角色管理 2.设置和管理数据操作权限

三、实验内容

1、创建登录帐号

2、创建用户 3、创建角色 4、授权

5、验证授权操作

四、实验步骤（贴图仅供参考） 1. 创建登录帐号

--添加登录cxp,设置密码为123

2、创建角色并给角色授权

给创建角色崔小平，并授予其对学生表的查询、插入、更新姓名列的权利 删除角色：（用崔平测试）

grant update,select,insert on student(sname) to 崔小平;

create role 崔小平;

3.授权- 将角色所拥有的权限授予所创建的用户 grant 崔小平

to zdl;

4、验证授权操作

修改数据库属性为混合登录模式后重新启动数据库，用cxp登录账号登录后，进行验

证；

五、实验总结

通过本次实验，我了解了用户及角色权限的相关知识，掌握了用户管理的定义，理解 了用户和角色的创建和分配。

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