《数据库原理》
实 验 报 告
学号: 541013100256 姓名: 张浩鹏 专业班级: 软件工程（java技术）
java10-02班
指导教师： 宋胜利
郑州轻工业学院软件
时间：2012年12月12日星期三
目 录
一、实验内容及要求 ..................................................................................... 错误！未定义书签。 二、实验目的 ................................................................................................................................... 4 三、Microsoft SQL Server 2008实验 ............................................................................................. 5
实验一 数据库创建 ................................................................................................................. 5 实验二 创建表 ......................................................................................................................... 8 实验三 创建数据完整性 ....................................................................................................... 12 实验四 数据完整性实验 ....................................................................................................... 18 实验五 索引 ........................................................................................................................... 26 实验六 更新数据 ................................................................................................................... 32 实验七 Sql 查询语句 ........................................................................................................... 34 实验八 视图 ........................................................................................................................... 47 实验九 安全性控制实验 ....................................................................................................... 54 实验十 存储过程 ................................................................................................................... 55 实验十一 触发器 ................................................................................................................... 59 实验十二 恢复技术 ............................................................................................................... 59 实验十三 事务 ....................................................................................................................... 66 实验十四 锁 ........................................................................................................................... 68 四、实验体会 ................................................................................................................................. 71
2
一、实验内容及要求
实验一 数据库创建
要求：1 创建数据库 2 修改数据库 3 删除数据库
实验二 创建表
要求：1 创建表 2 修改表结构 3 删除表
实验三 创建数据完整性
要求：1 能建立完整性约束 2 修改完整性约束 3 删除完整性约束
实验四 数据完整性实验
要求：记录实验中遇到的问题，并写出原因。
实验五 索引
要求：建索引
实验六 更新数据
要求：更新数据
实验七 Sql 查询语句
要求：Sql查询语句
实验八 视图
要求：建视图
实验九 安全性控制实验
要求：会安全性控制
实验十 存储过程
要求：建存储过程
实验十一 触发器
要求：建触发器
实验十二 恢复技术
要求：会数据库恢复技术
实验十三 事务
要求：会处理事务
实验十四 锁
要求：会建立锁
3
二、实验目的
实验一 数据库创建
1掌握利用SQL语言进行数据库的创建、维护。 2 sp_helpdb 命令
实验二 创建表
1 掌握利用SQL语言创建表的方法。 2 sp_help 命令
实验三 创建数据完整性
1掌握创建数据完整性约束的命令。 2 掌握完整性约束的修改、删除。
实验四 数据完整性实验
1理解实体完整性、参照完整性、用户自定义完整性的作用 2 特别掌握外码的作用。
实验五 索引
掌握索引的建立、删除的方法。
实验六 更新数据
掌握insert,update ,delete 语句的使用。
实验七 Sql 查询语句
掌握 Select 查询语句。
实验八 视图
掌握视图的建立、使用。
实验九 安全性控制实验
掌握Sql-server 的授权机制.
实验十 存储过程
掌握存储过程的概念、编程及使用
实验十一 触发器
了解触发器的机制及编程设计、使用
实验十二 恢复技术
1 掌握数据库的备份及恢复的方法。 2 了解备份方案的设定
实验十三 事务
1 掌握并理解事务
实验十四 锁
1 理解锁的概念及锁的作用
4
三、实验步骤及运行结果
实验一 数据库创建
一 建立school 数据库
1 Create database school;
刷新左边的数据库即可看到建立的school数据库
2 用sp-helpdb查询数据库school的信息
5
二 删除School数据库
1 使用查询分析器删除数据库 school DROP DATABASE school
刷新后可看到已创建的数据库school已被删除
2 使用SQL-Server 的企业管理器删除数据库 school 。
三 create Database 深入研究
6
1 建立school数据库,要求数据库存储在D:\\sqldata文件夹下，初始大小为5MB ,增量为 1MB。
CREATE DATABASE school ON( Name = ‘school_dat’,
Filename =’ D:\\sqldata \\school.mdf’, SIZE = 5,
FILEGROWTH = 1 )
刷新数据库可看到school数据库
7
实验二 创建表
一 写出使用 Create Table 语句创建表 student, SC,course 的SQL语句。
学生表、课程表、选课表属于数据库 School ，其各自得数据结构如下： 学生 student (Sno,Sname,Ssex,Sage,Sdept)
序号 列名 含义 数据类型 长度 1 Sno 6 学号 字符型(char) 2 Sname 8 姓名 字符型(varchar) 3 Ssex 2 性别 字符型(char) 4 Sage 年龄 整数 (smallint) 5 sdept 15 系科 字符型(varchar)
课程表 course(Cno,Cname,Cpno,Ccredit)
序号 列名 含义 数据类型 长度 1 Cno 4 课程号 字符型(char) 2 cname 20 课程名 字符型(varchar) 3 Cpno 4 先修课 字符型(char) 4 Ccredit 学分 短整数 (tinyint) 学生选课 SC(Sno,Cno,Grade)
序号 列名 含义 数据类型 长度 1 Sno 4 学号 字符型(char) 2 Cno 6 课程名 字符型(char) 3 Grade 12,1 成绩 小数(decimal)
二 要执行的Sql语句。
create table student ( Sno char(6) , Sname char(10) , Ssex char(2) , Sage smallint , Sdept char(10) , )
create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int, )
create table SC( Sno char(6), Cno char(4) , Grade int )
8
四 利用 sql 语句表结构修改
Alter table student add address varchar(60); Alter table student add inDate datetime; 使用sp_help进行查看：
9
alter table student alter column address varchar(50); 使用sp_help查看结果：
alter table student drop column inDate;
分别删除表sc、student、course
drop table sc;
drop table student;
drop table course;
10
11
实验三 创建数据完整性
一 写出带有完整性约束的 Create Table 命令建立表student、course、SC 。要求：
1 Student表的主码：Sno student 的约束：
? 姓名不可为空，且唯一
? 性别 不能为空且取值范围为{男，女} ? 年龄大于16岁
? Sdept 默认为 ?JSJ? 系
2 Course表的主码：Cno course 的约束：
? Ccredit 取值范围{ 0 ,1,2,3,4,5 }
? 课程表的每一行的 Cno 与 cpno 不可相同
3 SC表的主码：Sno，Cno 。主码名为 PK_SC
Sc的外码：
? 外码：SC 表的Sno 参照表 student 的Sno ? 外码：SC 表的Cno 参照表 course 的Cno
4 把上述创建表的sql 语句的脚本存储到文件 createSchool.sql 。 create table student ( Sno char(6) ,
Sname char(10) not null unique ,
Ssex char(2) check (ssex='男' or ssex='女') , Sage smallint check(sage>16) ,
Sdept char(10) not null default 'JSJ' ,
primary key (Sno) )
create table course( Cno char(4) , Cname char(16) , Cpno char(4) ,
Ccredit int check (Ccredit >=0 and Ccredit<=5),
check( Cno<>Cpno) , --约束 primary key (Cno) )
create table SC( Sno char(6), Cno char(4) ,
Grade int check(grade<=100) ,
constraint pk_sc primary key (Sno,Cno), foreign key (Sno) references student(Sno) , foreign key (Cno) references course(Cno) , )
12
二 使用 SP_HELP 查看表 student 的主码名，约束名，并记录。
记录结果为：
主码名：PK__student__7C8480AE(sno)
约束名：CK__student__sage__7F60ED59([sage]>(16))
CK__student__ssex__7E6CC920 ([ssex]='男' OR [ssex]='女') DF__student__sdept__00551192 ('JSJ') PK__student__7C8480AE （sno） UQ__student__7D78A4E7（sname）
1、删除sc的主码
alter table sc drop pk_sc;
13
删除sc表参照表course表的外码的名字。 先找到course表的外码名 sp_help course;
school.dbo.sc: FK__sc__cno__08EA5793 alter table sc drop FK__sc__cno__08EA5793;
14
添加sc表的主码
alter table sc add constraint pk_sc primary key(sno,cno);
添加sc表的cno的外码
alter table sc add foreign key (cno) references course(cno);
15
加自定义约束：表sc的成绩只能在0-100分之间。
alter table sc add check(grade>=0 and grade<=100);
四 使用 Sql-Server 企业管理器完成：
1 删除 SC 表参照 course 表的外码。
2 建立 SC 表的Cno 的外码 ，参照表Course的Cno. *使该外码具有级联修改的功能。
3 删除表 SC的成绩只能在 0 – 100分之间的约束.
3加自定义约束：表 SC的成绩只能在 0 – 100分之间。
16
五 使用
select * from student 查看信息 select * from course 查看信息 select * from SC 查看信息
17
实验四 数据完整性实验
1、向student表中输入数据
insert into student (
sno,sname,ssex,sage,sdept)
values ('3001','赵达','男','20','SX');
插入第二条记录：
insert into student (
sno,sname,ssex,sage,sdept)
values ('3002','杨丽','女','21','JSJ');
插入第三条记录：
insert into student (
sno,sname,ssex,sage,sdept)
18
values ('3001','李寅','女','21','SX');
发现消息框提示错误：第三行不能输入，student的主码为sno，因此，sno列的值不能重复。
用select语句对表student进行查询，发现只插入了前两条记录，由于第三条记录违反了主码唯一性，所以插入失败。截图如下：
insert into sc( sno,cno,grade)
values('3001','1081','79');
截图如下：
19
由上图可知有错，原因是：sc表的主码为：sno,cno,主码名为：PK_SC，违反了PRIMARY KEY唯一性，因此不能插入重复值，提示错误。 用户自定义完整性约束： Student表数据的输入： 插入第一条记录：
insert into student(
sno,sname,ssex,sage,sdept)
values('3005','赵达','男','14','SX');
截图如下：
20
由上图可知出错，原因：违反了用户自定义约束：年龄规定为大于16，而这里插入的是14（<16），所以插入失败，提示错误。 插入第二条记录：
insert into student(
sno,sname,ssex,sage,sdept)
values('3006','杨丽','南','21','JSJ');
截图如下：
由上图可知插入失败，原因：违反了用户自定义完整性约束：性别不能为空且取值范围为{男，女}，
而此处的性别为南不等于男，所以插入失败，提示错误。 用select语句查询插入的记录数：
select * from student;
截图如下：
21
Course表数据的输入： 第一条记录：
insert into course(
cno,cname,cpno,ccredit)
values('1085','c++','','9');
截图如下：
由上图可知插入失败，原因是：违反了course表的自定义约束：ccredit取值范围{0,1,2,3,4,5}，而这里插入的ccredit为6，不属于ccredit规定的取值范围，所以插入失败，给出提示信息。
22
第二条记录：
insert into course(
cno,cname,cpno,ccredit)
values('1086','语文','1086','3');
截图如下：
由上图可知插入失败，原因：违反了表course的自定义约束：课程表的每一行的cno与cpno不可相同，而这里插入的cno:1086=cpno:1086，所以插入失败，给出提示信息。 用select语句查询看成功插入了几条语句：
select * from course;
截图如下：
23
由上图可知成功插入了一条记录。 Sc表数据的输入：
insert into sc( sno,cno,grade)
values('3002','1081','128');
截图如下：
由上图可知插入失败，原因：grade不能大于100分，而这里是128（>100），所以插入失败。
在sc表中添加新行：
24
insert into sc( sno,cno,grade)
values('4001','1066','76');
添加失败，原因：cno是外码，参照course表的cno,但在course表中没有1066的课程号。
25
实验五 索引
1、创建索引
建student的索引：为姓名建立索引，索引名：ix_student_sname 为系科建立索引，索引名：ix_student_sdept
create index ix_student_sname on student(sname);
创建索引成功。
create index ix_student_sdept on student(sdept);
26
创建索引成功。 Sc的索引
为课程号建立索引：ix_sc_cno create index ix_sc_cno on sc(cno);
创建索引成功。 Course的索引
为课程名建立唯一性索引：ix_course_cname
create unique index ix_course_cname on course(cname);
27
创建索引成功。
用sp_help分别查看表student,sc,course所创建的索引。 sp_help student;
sp_help sc;
28
sp_help course;
删除course表的索引ix_course_cname
drop index course.ix_course_cname;
29
把索引ix_student_sname修改为唯一性索引。 步骤：
1、先删除索引ix_student_sname：
drop index student.ix_student_sname;
1、建唯一性索引ix_student_sname：
create unique index ix_student_sname on student(sname);
30
31
实验六 更新数据
一 insert
1 写出把下述学生的信息添加到student表中的命令。 学号 姓名 性别 年龄 系科 4001 20 SX 赵茵 男 4002 21 杨华 女 Insert into student (sno,sname,ssex,sage,sdept) values (?4001 ?,?赵茵?,?男?,20,? SX?) Insert into student (sno,sname,ssex,sage) values (?4002 ?,?杨华?,?女?,21)
2 批量插入数据
1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade 。 CREATE TABLE sc_name ( Sno char(6) , Sname varchar(20), Ssex char(2) , Cno char(4) , Grade int )
2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name(Sno,Sname,Ssex,Cno,Grade) select student.Sno,Sname,Ssex,Cno,Grade from student,SC where student.Sno = SC.Sno and Sdept = 'SX' 3） 察看 sc_name 表的数据 select * from sc_name
32
二 Update
1 修改3001 学生的系科为: JSJ
Update student set Sdept = 'JSJ' where Sno = '3001'; 2 把陈小明的年龄加1岁，性别改为女。
Update student set sage=sage+1 , ssex=?女? where sname=?陈小明? 3 修改杨华的1081课程的成绩为 93 分
update SC set Grade = 93 where Cno = '1081' and Sno in( select Sno from student where Sname = '杨华' )
4 把“数据库原理”课的成绩减去1分
update SC set Grade = Grade-1 where Cno in(
select Cno from course where Cname = '数据库原理' )
三 Delete
1 删除所有 JSJ 系的男生 delete from student where sdept=?JSJ? 2 删除“数据库原理”的课的选课纪录
delete from SC where Cno in (select Cno from course where Cname = '数据库原理')
33
实验七 Sql 查询语句
一 单表
1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 select Sno,Sname,Sage from student
where Sage between 19 and 21 and Ssex = '女' order by Sage desc; 2查询姓名中第2个字为“明”字的学生学号、性别。
select COUNT(*) from student where Sname like '_明%'; 3查询 1081课程没有成绩的学生学号、课程号
select Sno,Cno from SC where Grade is null and Cno = '1081' 4查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列
select Sno,Sname from student where Sdept in ('JSJ','SX','WL') order by Sdept,Sno
5按10分制查询学生的sno,cno,10分制成绩
（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） select Sno , Cno , Grade/10.0+1 as level from SC
6查询 student 表中的学生共分布在那几个系中。（distinct） select distinct Sdept from student
7查询3001号学生1081，1082课程的成绩。
Select Grade from SC where Sno='3001' and (Cno='1081' or Cno='1082')
二 统计
1查询姓名中有“明”字的学生人数。
select COUNT(*) from student where Sname like '%明%'; 2计算‘JSJ’系的平均年龄及最大年龄。
Select avg(Sage) , max(Sage) from student Where Sdept='JSJ'
34
3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 select Cno,sum(Grade),avg(Grade),max(Grade),min(Grade) from SC group by Cno
order by avg(Grade) desc
4 计算 1081,1082 课程的平均分。
Select Cno , avg(Grade) from SC where Cno in ('1081','1082') Group by cno
35
5 查询平均分大于80分的学生学号及平均分 select SC.Sno , avg(Grade) from SC group by SC.Sno
having avg(Grade)>80
6 统计选修课程超过 2 门的学生学号
select Sno from SC group by Sno having count(*)>2
7 统计有10位成绩大于85分以上的课程号。 Select Cno from SC where Grade>80
36
group by Cno having count(*) =2
8 统计平均分不及格的学生学号 select Sno from SC
group by Sno having avg(Grade)<60
9 统计有大于两门课不及格的学生学号 select Sno from SC where Grade<60 group by Sno having COUNT(*)>2
三 连接
1查询 JSJ 系的学生选修的课程号
select Cno from student,SC where student.Sno = SC.Sno and Sdept = 'JSJ'
37
2查询选修1081 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）
a: select Sname from Student,SC where student.Sno = SC.Sno and Cno = '1081' b: select Sname from student where Sno in (select Sno from SC where Cno = '1081')
3查询数据库原理不及格的学生学号及成绩 select sno,grade from sc ,course
where sc.cno=course.cno and cname=’数据库原理’
4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） a: select Sname from student,SC,course
38
where student.Sno = SC.Sno and SC.Cno = course.Cno and Grade>80 and Cname = '数据库原理'
b:select Sname from student where Sno in(select Sno from SC where Grade>80 and Cno in(select Cno from course where Cname = '数据库原理') )
5查询平均分不及格的学生的学号，姓名,平均分。
select student.Sno,MAX(Sname),AVG(Grade) as avggrade from SC,student where student.Sno=SC.Sno group by student.Sno having AVG(Grade)<80
39
6查询女学生平均分高于75分的学生姓名。
A: select Sname from student where Ssex ='?' and Sno in( select Sno from SC group by Sno having AVG(Grade)>75 )
B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75
7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)
select student.Sno,Sname,Cno,Grade from student left join SC ON student.Sno = SC.Sno and Ssex = '男'
40
四 嵌套、相关及其他
1 查询平均分不及格的学生人数
select COUNT(*) from student where Sno in(
select Sno from SC group by Sno having AVG(Grade)<60 )
2 查询没有选修1002 课程的学生的学生姓名
41
select Sname from student where Sno not in( select Sno from SC where Cno = '3001' )
3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）
a: select top 1 Sno,avg(Grade) from SC group by Sno order by avg(Grade) desc B: select Sno,avg(Grade) from SC group by Sno
having avg(Grade) = (select top 1 avg(Grade) from SC group by Sno order by avg(Grade) desc ) c: select Sno,avg(Grade) from SC group by Sno
having avg(Grade) >=all ( select avg(Grade) from SC group by Sno )
42
*4 查询没有选修1001，1002课程的学生姓名。
Select Sname from student where not exists (
Select * from course where Cno in ('3001','3002') and
Not exists ( select * from SC where Sno=student.Sno and Cno=course.Cno ) )
5 查询1002课程第一名的学生学号（2种方法）
select top 3 Sno from SC group by Sno order by avg(Grade) desc
6 查询平均分前三名的学生学号
select top 3 sno from sc group by sno order by avg(grade) desc
43
7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
a: select * from student where Sdept='JSJ' and Sage>19 b: select * from student where sdept='JSJ' except select * from student where sage<19
8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 select student.Sno,Sname from student,SC where Cno='1081' and Grade>90 union select Sno,Sname from student where Sno in( select Sno from SC group by Sno having AVG(Grade)>85 )
44
9 查询每门课程成绩都高于该门课程平均分的学生学号 select Sno from student where Sno not in ( select Sno from SC X where Grade < (
select avg(Grade) from SC where Cno=X.Cno ) )
select sno from student where sno not in (
select sno from sc X where grade < (
select avg(grade) from sc where cno=X.cno ) )
10 查询大于本系科平均年龄的学生姓名 select Sname from student X where Sage > ( select avg(Sage) from student y where Sdept=x.Sdept )
45
46
实验八 视图
建立学生学号、姓名、性别、课程号、成绩的视图v_sc，并且查看v_sc中的数据。
create view v_sc(sno,sname,ssex,cno,grade) as(select student.sno,sname,ssex,cno,grade from student,sc
where student.sno=sc.sno );
创建视图成功。
select * from v_sc;
47
查询成功,共有14条记录.
建立学生学号、姓名、出生年月的视图v_age，并且查看v_age中的数据。
create view v_age(sno,sname,sbirth)
as select sno,sname,2008-sage from student;
创建成功。
select * from v_age;
48
查询成功,共有12条记录.
建立’JSJ’系的学生学号、姓名、性别、年龄的视图V_JSJ
create view V_JSJ(sno,sname,ssex,sage) as select sno,sname,ssex,sage from student where sdept='JSJ';
建立视图成功。
建立每门课程的平均分的视图v_average
create view v_average(cno,grade1)
49
as select cno,avg(grade) from sc group by cno;
建立视图成功。
将视图V_JSJ中赵顺的年龄改为21岁.
update V_JSJ set sage=sage+1
where sname='赵顺';
查询成功.
50