数据库应用实验

实验报告（总四次）

目录

实验一 .......................................................错误!未定义书签。

第二章 T-SQL语言基础.....................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容和步骤......................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。

第三章数据库及其管理.....................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容和步骤......................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。实验二 .......................................................错误!未定义书签。

第四章数据表与表数据.....................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容和步骤......................................错误!未定义书签。

第五章数据查询...........................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容和步骤......................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。实验三 .......................................................错误!未定义书签。

第六章试图及其应用.......................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容............................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。

第七章索引及其应用.......................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容............................................错误!未定义书签。实验四 .......................................................错误!未定义书签。

第八章存储过程与触发器...................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容............................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。

第九章事务与游标.........................................错误!未定义书签。

1 实验目的............................................错误!未定义书签。

2 实验内容............................................错误!未定义书签。

3 实验小结............................................错误!未定义书签。

实验一

第二章 T-SQL语言基础

1 实验目的

1）学会定义数据类型，声明局部变量的方法；

2）学会批处理和流程控制语句的使用方法。

2 实验内容和步骤

（1）使用系统存储过程，用户自定义数据类型

自定义一个地址（address）数据类型。

EXEC sp_addtype address, ‘ varchar(80)’, ‘not null’

创建两个自定义类型stud_name 和 postalcode.

Use student

Go

Exec sp_addtype stud_name , ‘ varchar(8)’ , ‘null’

Exec sp_addtype postalcode , ‘ char(6) ’ , ‘ not null ’

Go

（2）使用DECLARE语句声明局部变量，并使用SELECT或SER语句给变量赋值

定义一个整型局部变量和一个可变长字符型局部变量并赋值。（略）

（3）使用流程控制语句编写程序

编写计算n！（20）的T-SQL语句，并显示计算结果。

输出字符串“School”中每一个字符的ASCII值和字符。

设置在五分钟后进行一次查询操作。

使用CASE语句，输出教师基本信息表（teacher_info）中的所有教师所在的年龄段（每6年划分一个段），并说明对应教师的职称。

use student

go

select tech_title'职称',areaofage=(case

when age>18 and age<=24 then'18-24'

when age>24 and age<=30 then'24-30'

when age>30 and age<=36 then'30-36'

when age>36 and age<=42 then'30-36'

when age>42 and age<=48 then'42-48'

end)

from teacher_info

go

3 实验小结

本次实验我学习了定义数据类型、声明局部变量的方法，熟悉了批处理和流程控制语句的

使用方法。

第三章数据库及其管理

1 实验目的

1）了解系统数据库的作用；

2）学会使用对象资源管理器创建用户数据库；

3）学会使用SQL编辑器创建数据库；

4）学会压缩和扩充数据库；

5）学会查看和修改数据库选项；

6）学会给数据库改名和删除数据库。

2 实验内容和步骤

1）分别对象资源管理器、SQL编辑器和模板资源管理器创建一个数据库。

（略）

使用T-SQL语句创建数据库：

2）分别使用对象资源管理器和T-SQL编辑器将数据库student的初始分配空间大小扩充到40MB。

3）分别使用对象资源管理器和T-SQL编辑器将数据库student数据库的空间压缩至最小空间量。

4）分别使用对象资源管理器和T-SQL编辑器将student数据库重新设置为只读状态。

5）分别使用对象资源管理器和T-SQL编辑器将student数据库改名为scholastic。

6）分别使用对象资源管理器和T-SQL编辑器删除scholastic数据库。

drop database scholastic

7）采用系统存储过程分离和附加数据库。

分离数据库scholastic

附加数据库

3 实验小结

通过本次实验，我了解系统数据库的作用，学会了使用对象资源管理器创建用户数据库和使用SQL编辑器创建数据库，学会了压缩和扩充数据库，并且学会查看和修改数据库选项以及数据库改名和删除数据库。

实验二

第四章数据表与表数据

1 实验目的

1）了解SQL Server的数据类型；

2）了解数据表的结构特征；

3）学会使用对象资源管理器和T-SQL语句创建数据表；

4）学会在对象资源管理器中对数据表进行插入、修改和删除数据的操作；

5）学会使用T-SQL语句对数据表进行插入、修改和删除数据的操作；

6）学会定义约束、使用默认和规则。

2 实验内容和步骤

（1）使用对象资源管理器创建表

在对象资源管理器中分别创建学生信息表（ stud_info）、教师基本信息表（teacher_info）、学生成绩表（stud_grade）、课程信息表（lesson_info）、课程安排表（teach_schedule）、院系代码表（dept_code）、专业代码表（specialty_code）、教研室信息表（staffroom_info）和教师信息表（classroom_info）。

（2）删除表和创建表

（3）分别使用对象资源管理器和T-SQL语句修改表结构

1)假设在创建数据表lesson_info时错将课程学分course_mark设计为char(2)，现欲修改为

decimal(3,1)。

2)向stud_info 表增加“入学时间”列，其数据为日期型

（4）建立约束

1）为student 数据库的stud_info表中的mark列建立check约束

2）使用T-SQL语句创建表 stud_info，设置stud_id为PRIMARY KEY 约束。

（略）

3）为表stud_info的telcode列建立UNIQUE约束。

4）为stud_grade表中的stud_id列建立FOREIGN KEY约束。

独立实践：为表 stud_info的zipcode列建立DEFAULT约束。

（5）向数据库student的表中插入数据

（略）

（6）修改数据库student 的表数据

1）在学生成绩表中，将课程号“01”的成绩在原来基础上增加10%。

结果：

2）将所有低于60分的成绩置空。

3）将赵明同学的入学成绩更新成与学号为“03”的孔荣同学的入学成绩相同。

（7）删除数据库student的表数据

1）删除学号为“01”的学生记录。

第五章数据查询

1 实验目的

1）掌握SELECT语句的基本语句；

2）学会使用ORDER BY 子句进行排序，使用GROUP BY子句进行统计；

3）学会数据汇总、连接查询、嵌套查询的方法。

2 实验内容和步骤

（1）SELECT语句的基本使用

1）查询stud_info为“04”的学生的地址和电话。

结果：

2）查询stud_info表中性别为“女”的学生地址和电话。使用AS子句将结果中指定目标列的名称分别指定为汉字标榜。

3）查询所有电话号码好友“3460”的教师所担任课程的编码。

4）在学生基本信息表（stud_info）中查询出生日期从“01-01-1987”到“12-31-1987”的学生的学号、姓名、家庭住址和邮政编码。

（2）子查询的使用

1）查找计算机工程系中所有担任“计算机专业英语”的教师编号名和姓名。

结果：

2）查询计算机工程系计算机应用教研室的教师编号、姓名和其课程号。

（略）

3）查询计算机工程系参与了“多媒体技术”课程考试的学生学号、姓名和考试成绩。

（3）连接查询的使用

1）写出课程安排表（teach_schedule）与课程信息表（lesson_info）等值内连接的SQL语句。

2）写出教师信息表（teacher_info）与课程安排表（ teach_schedule）右外连接的SQL 语句。

（4）数据汇总

1）查询计算机工程系全体教师的平均工资。

结果：

2）查询计算机工程系学生的入学成绩的最高分和最低分。

3）统计计算机工程系担任“多媒体技术”课程教学的教师的工资总和。

结果：

（5）GROUP BY、ORDER BY子句的使用

1）按职称统计各个教研室的教师人数。

结果：

2）将计算机工程系的学生情况按出生日期的先后排列。

3 实验小结

本次实验我掌握了SELECT语句的基本语句；学会了使用ORDER BY 子句进行排序，使用GROUP BY子句进行统计；并练习了数据汇总、连接查询、嵌套查询的方式。

实验三

第六章试图及其应用

1 实验目的

1）理解视图的概念

2）学会利用对象资源管理器和SQL编辑器创建视图的方法

3）学会查询、更新、删除视图的方法

2 实验内容

（1）创建视图

1）将表 stud_info中所有女生记录定义为一个视图（stud_info_female），在SQL编辑器窗口中输入和执行语句，并通过对象资源管理器来显示其结果。

2）对表stud_info定义反映学生出生日期的视图（stud_info_birthday）。

3）将表stud_grade中学生的学号及其平均成绩定义一个视图（stud_grade_average）。

（2）使用视图

1）通过视图stud_info_female查询所有女生的记录信息。

select * from stud_info_female

2）通过查询视图stud_info_birthday查看学生的年龄。

Select stud_id,name,2011-year(birthday) as stud_age from stud_info_birthday

3）向视图stud_info_female中插入一条记录。

insert into stud_info_female

values('04',N'张燕','12/06/1986',N'女',N'北京海淀区','0','100081',565)

4）将张燕从管理工程系转到计算机工程系。

update stud_info_female

set stud_id=SUBSTRING(stud_id,1,2)+'01'+SUBSTRING(stud_id,5,6)

where name=N'张燕'

5）把张燕从表 stud_info中删除。

delete from stud_info_female where name=N'张燕'

3 实验小结

本次实验中我理解了视图的概念，也学会了利用对象资源管理器和SQL编辑器创建视图的方法，并熟悉了查询、更新、删除视图的方法

第七章索引及其应用

1 实验目的

1）学会使用对象资源管理器或SQL编辑器创建索引

2）学会创建唯一、聚集、非聚集索引以及复合索引

3）学会查看和修改索引选项，以及给索引改名和删除索引

2 实验内容

（1）建立索引

对student数据库的stud_info学生信息表中的学号stud_id列建立一个名为xh_index的索引。create index xh_index on stud_info(stud_id)

（2）创建一个复合索引

为stud_info学生信息表创建一个基于“入学成绩，学号”组合列的非聚集、复合索引cj_xh_index。

create index cj_xh_index on stud_info(stud_id,mark)

（3）创建一个聚集、复合索引

为学生成绩表（stud_grade）创建一个基于“学号，课程号”组合列的聚集复合索引。

use student

go

create clustered index xhkc_index on stud_grade(stud_id,course_id)

（4）创建一个唯一聚集、复合索引

为课程表（lesson_info）创建一个基于“课程号，课程类型”组合的唯一、聚集、复合索引kc_lx_index。

use student

go

create unique clustered index kc_lx_index on lesson_info(course_id,course_type)

with

pad_index,fillfactor=80,ignore_dup_key

（5）在teacher_info表上创建基于telephone列的非聚集索引，其填充因子为60.

use student

go

create index ch_index on teacher_info(telephone)

with fillfactor=60

实验四

第八章存储过程与触发器

1 实验目的

1）理解存储过程和触发器的作用

2）学会使用对象资源管理器和SQL编辑器创建存储过程

3）学会使用对象资源管理器和SQL编辑器创建触发器

4）学会存储过程和触发器的管理方法

2 实验内容

（1）创建和执行存储过程

1）针对教师基本信息表teacher_info，创建一个名称为teacher_select的存储过程，执行存储过程将从数据表teacher_info中根据编号检索某一教师的姓名和职称。

use student

go

create procedure teacher_select

(@no char(6),@nam nvarchar(4)output,@title nchar(5)output)

as

select@nam=name,@title=tech_title from teacher_info where teacher_id=@no

go

执行：

declare@nam varchar(6)

declare@title char(10)

execute teacher_select'010104',@nam output,@title output

select'姓名：',@nam,'职称：',@title

2）建立一个插入教师记录的存储过程teacher_insert。

（略）

3）建立一个修改教师记录的存储过程teacher_update，并执行。

（略）

4）建立一个删除教师信息表中记录的存储过程teacher_delete，并执行之。

use student

go

create procedure teacher_delete

(@no char(6))

as

delete from teacher_info where teacher_id=@no

go

执行：

exec teacher_delete'010119'

（2）创建触发器

1）使用CREATE TRIGGER 语句创建一个触发器teacher_tri1，当表teacher_info中插入一条记录时，自动显示表teacher_info的记录。

use student

go

create trigger teacher_tri1

on teacher_info

for insert

as

select*from teacher_info

执行：

insert into teacher_info

values('010119',N'聂晓基',N'男',40,N'讲师','038',,'08')

2）使用系统存储过程sp_helptext查看触发器teacher_tri1的定义文本信息。

exec sp_helptext teacher_tri1

3）在数据库student中的teacher_info 表上建立delete触发器teacher_tri2。

use student

go

create trigger teacher_tri2

on teacher_info

for delete

as

if((select count(*)from teach_schedule inner join deleted

on=>0)

begin

raiserror('you can not delete the teacher_info with the order record.

\the transaction will be cancelled',10,1)

rollback transaction

end

执行：

delete from teacher_info where teacher_id='010101'

独立实践：编写一个触发器，在对student数据库中的数据表stud_info执行插入、更新和删除三种操作后作出相应提示。

use student

go

create trigger teacher_tri3

on stud_info

for insert,update,delete

as

if((select count(*)from inserted inner join deleted

on=>0)

print'update successfully'

if((select count(*)from deleted)>0)

print'delete successfully'

if((select count(*)from inserted)>0)

print'insert successfully'

3 实验小结

本次试验我理解了存储过程和触发器的作用，并熟悉了使用对象资源管理器和SQL编辑器创建存储过程和使用对象资源管理器和SQL编辑器创建触发器，与此同时，我还练习了存储过程和触发器的管理方法。

第九章事务与游标

1 实验目的

1）了解SQL Server 程序设计的方法。

2）学会批处理、流程控制、事务处理、锁、游标的使用方法。

2 实验内容

（1）事务处理

运用事务处理将student数据库中课程信息表lesson_info中的多媒体技术课程编号course_id 由06修改为06.

use student

go

begin transaction student_tran

update lesson_info set course_id='06'

where course_id='06'

save tran stud_savepoint

update teach_schedule set course_id='06'

where course_id='06'

update stud_grade set course_id='06'

where course_id='06'

update teacher_info set course_id='06'

where course_id='06'

if(@@ERROR=1)

rollback tran stud_savepoint

else

commit tran student_tran

（2）油表的使用

1）定义一个游标stud_cursor，逐行读取stud_info表中的数据

（略）

2）定义一个游标stud_cursor，删除stud_info中第一行数据

（略）

3）定义一个游标stud_cursor，更新表stud_info中的数据

use student

go

select*from stud_info

go

declare stud_cursor cursor

for select*from stud_info

open stud_cursor

fetch next from stud_cursor

update stud_info

set mark=567

where CURRENT of stud_cursor

close stud_cursor

deallocate stud_cursor

go

select*from stud_info

独立实践：定义一个游标stud_sursor，在student数据库中使用使用游标逐行查询stud_info 表输出所有的电话号码，最后删除stud_info表中最后一行数据。

use student

go

declare@stud_name nvarchar(4),@tel_code char(12)

declare stud_cursor cursor

for select name,telcode from stud_info

open stud_cursor

fetch next from stud_cursor into@stud_name,@tel_code

while(@@FETCH_STATUS=0)

begin

select@stud_name'姓名',@tel_code'电话'

fetch next from stud_cursor into@stud_name,@tel_code

end

delete from stud_info where name=@stud_name and telcode=@tel_code

close stud_cursor

deallocate stud_cursor

go

select*from stud_info

3 实验小结

通过本次对事务处理和声明游标的实验，我进一步地熟悉了SQL Server 程序设计的方法，而且还巩固了对批处理、流程控制、事务处理、锁、游标的使用，并从中注意到了容易出错的问题，使我受益匪浅。