SQL Server实训报告.docx
《SQL Server实训报告.docx》由会员分享，可在线阅读，更多相关《SQL Server实训报告.docx（24页珍藏版）》请在冰豆网上搜索。
SQLServer实训报告
《数据库设计与实现
（SQL）实训》报告
（2011-2012学年第一学期）
系别：
班级：
姓名：
学号：
指导教师：
设计时间：
二〇一一年十二月三十日
实训项目：
教学管理系统数据库设计
一、实训目的和要求
1、通过实训掌握本学期所学数据库开发的相关知识；
2、掌握数据库、数据表的创建及修改；
3、掌握基本查询、连接查询、嵌套查询的使用；
4、掌握变量的定义、函数的使用和简单程序设计；
5、掌握索引、视图、存储过程和触发器的创建；
6、掌握数据库的维护及帐户的创建。
二、实训内容
为了有效的管理资源，并方便的完成教学信息检索操作，很多学校都有自己的教学管理系统，本次实训主要完成一个基于实际需求的教学管理系统的设计，主要完成后台数据库部分的内容。
包括学生表、教师表、授课任务表、课程表、选课表的设计与数据输入，外键关系的建立，课程、成绩等信息的查询操作。
四、实训步骤
实训项目一数据库、数据表的创建及修改
■任务一、创建一个名为教学管理的数据库，数据文件存放在“D：
\教学管理”路径下。
它由5MB主要数据文件（教学管理1.mdf和1MB日志文件（教学管理_log.ldf）组成。
并且主要数据文件以2MB增长速度增长，其最大数据文件大小为15MB，事物日志文件以1MB增长速度增长，其最大数据文件大小为10MB。
）
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
createdatabase教学管理
on
（name=教学管理1,
filename='c:
\教学管理1.mdf',
size=5,
maxsize=15,
filegrowth=2）
logon
（name=教学管理_log,
filename='c:
\教学管理_log.ldf',
size=1,
maxsize=10,
filegrowth=1）
■任务二、为教学管理数据库增加一个次要数据文件（教学管理2.ndf），该文件大小为4MB，以10%增长速度增长，最大增长到10MB
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
alterdatabase教学管理
addfile
（name=教学管理,
filename='c:
\教学管理2.ndf',
size=4,
maxsize=10,
filegrowth=10%）
■任务三、将“教学管理”数据库中主要数据文件“教学管理1.mdf”大小扩充为20MB。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
alterdatabase教学管理
modifyfile
（name=教学管理1,
size=20）
■任务五、创建教师表。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
createtable教师
（教师名char（4）notnullPRIMARYKEY,
教师姓名varchar（10）,
性别char
（2）default'男',
职称varchar（10）,
所在部门varchar（30）,
登录密码varchar（30）default'teacher'）
实训项目二数据查询的实现
■任务一、对学生表进行学号、姓名、性别、出生日期、专业的查询，结果按姓名字段升序排序。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,姓名,性别,出生日期,专业
from学生表
orderby姓名asc
■任务二、为结果集制定姓名，查询学生表中的学生及姓名列，要求查询结果中姓名列的名称为“学生姓名”。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,姓名as学生姓名
from学生表
■任务三、查询所有学生的学号、姓名、性别和年龄。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,姓名,性别,year（getdate（））-year（出生日期）as年龄
from学生表
■任务四、显示学生的专业情况，要求：
不显示重复的专业。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
selectdistinct专业
from学生表
■任务五、显示前3名较年长的学生信息
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
selecttop3学号,姓名,出生日期
from学生表
orderby出生日期
■任务六、查询不是计算机网络技术专业的女同学的信息。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select*
from学生表
where专业<>'计算机网络技术'and性别='女'
■任务七、使用IN关键字查询成绩是88、87、75的同学信息。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select*
from选课表
where成绩IN（88,87,75）
orderby成绩
■任务八、查询所有姓李的老师的信息。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select*
from教师
where教师姓名like'李%'
■任务九、计算选课表中的成绩总分、平均分、最高分、最低分。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
selectSUM（成绩）as总分,AVG（成绩）AS平均分,
MAX（成绩）AS最高分,MIN（成绩）AS最低分
from选课表
■任务十、按学生分组计算选课表中的成绩总分、平均分、最高分、最低分。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,SUM（成绩）as总分,AVG（成绩）AS平均分,
MAX（成绩）AS最高分,MIN（成绩）AS最低分
from选课表
groupby学号
■任务十一、按学生分组，求出学生的总分，要求生成汇总行和明细行。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,成绩
from选课表
orderby学号
computeSUM（成绩）by学号
■任务十二、使用连接查询，查询教师所担任的课程号、课程名和课程性质。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select教师号,授课任务表.课程号,课程名,课程性质
from授课任务表join课程表on（授课任务表.课程号=课程表.课程号）
■任务十三、查询没有选课的学生信息。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,姓名,性别
from学生表
where学号notin（selectdistinct学号from选课表）
■任务十四、查询选课表中低于平均分的学生的学号。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
selectdistinct学号as低于平均分的学生
from选课表
where成绩<（selectavg（成绩）from选课表）
■任务十五、查询08网络1班学生的平均成绩。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,avg（成绩）as平均成绩
from选课表
groupby学号
having学号in（select学号from学生表where班级='08网络1班'）
实训项目三数据库、数据表的创建及修改
■任务一、显示数据库服务器名称和数据库同时连接的最大用户数。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
select@@servernameas数据库服务器名称
select@@max_connectionsas数据库同时连接的最大用户数
■任务二、定义两个局部变量@v1，@v2并赋值，然后输出变量的值。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
declare@v1char（12）,@v2char（20）
set@v1='Transcat-SQL'
set@v2=@v1+'程序设计'
select@v1as'变量@v1的值',@v2as'变量@v2的值'
■任务三、将教师表中姓名字段和人员职称字段连接在一列显示。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select教师姓名+职称as学院教师
from教师
where职称isnotnull
■任务四、用left函数返回课程名左3个字符。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
selectleft（课程名,3）as课程名左三个字符
from课程表
■任务五、使用LTRIM函数和RTRIM函数删除字符串中的空格。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
declare@v1varchar（20）,@v2varchar（20）
set@v1='Transcat-SQL'
set@v2='程序设计'
select@v1+@v2as去除空格前
selectRTRIM（@v1）+LTRIM（@v2）as去除空格后
■任务六、使用REPLACE函数将“飞速发展的时代”中的“时代”替换为“信息时代”。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
declare@string1varchar（30）
declare@string2varchar（20）
declare@string3varchar（40）
set@string1='飞速发展的时代'
set@string2='时代'
set@string3='信息时代'
selectreplace（@string1,@string2,@string3）as替代后
■任务七、查询“大学英语”课程上课人数，如果人数多于40则显示分班上课，否则显示单班上课。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
declare@stcountint
select@stcount=count（*）
from选课表join授课任务表on选课表.授课编号=授课任务表.授课编号
join课程表on授课任务表.课程号=课程表.课程号
where课程名='大学英语'
IF@stcount>40
begin
print'选择该课程的人数有'+str（@stcount）+'人'
print'进行分班上课'
end
else
begin
print'选择该课程的人数有'+str（@stcount）+'人'
print'进行分班上课'
end
go
■任务八、使用CASE语句对学生按性别分类。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
select学号,姓名,性别=
case性别
when'男'then'该生是男同学'
when'女'then'该生是女同学'
end
from学生表
实训项目四索引、视图、存储过程、触发器创建
■任务一、对学生表创建一个复合索引，使用姓名字段和出生日期字段。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
createINDEXI_student
on学生表（姓名,出生日期）
with
PAD_INDEX,
FILLFACTOR=40
■任务二、使用SQL语句创建一个视图，命名为V_选课。
要求基表的数据来源为：
学生表、选课表、授课任务表、教师、课程表。
选择的字段为：
学生表中的学生号、姓名和班级，课程表中的课程号和课程名字段，授课任务表中的授课学期字段，教师表中的教师号、教师姓名和所在部门字段。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
createviewview_选课表
as
select学生表.学号,学生表.姓名,学生表.班级,
课程表.课程号,课程表.课程名,选课表.成绩,
授课任务表.授课学期,教师.教师号,教师.教师姓名
from授课任务表innerjoin
教师on授课任务表.教师号=教师.教师号innerjoin
课程表on授课任务表.课程号=课程表.课程号innerjoin
选课表on授课任务表.授课编号=选课表.授课编号innerjoin
学生表on选课表.学号=学生表.学号
■任务三、建立一个用户存储过程，实现在教学管理数据库中查询所有学生的学号、姓名、课程名、学分和成绩的功能，并执行该存储过程。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
IFEXISTS（SELECTNAMEFROMsysobjects
wherename='st_score'andtype='p'）
DROPPROCEDUREst_score
GO
createPROCEDUREst_score
as
select学生表.学号,学生表.姓名,课程表.课程名,课程表.学分,选课表.成绩
from学生表join选课表on选课表.学号=学生表.学号
join授课任务表on授课任务表.授课编号=选课表.授课编号
join课程表on授课任务表.课程号=课程表.课程号
go
存储过程st_score创建后，执行该存储过程只需在查询窗口中输入：
executest_score
■任务四、建立一个用户存储过程，实现在教学管理数据库中查询某人指定课程的成绩和学分的功能，并执行该存储过程。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
IFEXISTS（SELECTNAMEFROMsysobjects
wherename='query_stscore'andtype='p'）
DROPPROCEDUREquery_stscore5
GO
createPROCEDUREquery_stscore5
@namechar（8）,@cnamechar（16）
as
select学生表.学号,学生表.姓名,课程表.课程名,课程表.学分,选课表.成绩
from学生表join选课表on选课表.学号=学生表.学号
join授课任务表on授课任务表.授课编号=选课表.授课编号
join课程表on授课任务表.课程号=课程表.课程号
where学生表.姓名=@nameand课程表.课程名=@cname
go
存储过程query_stscore创建后，执行该存储过程只需在查询窗口中输入：
executequery_stscore‘张珊’,’大学英语’
■任务五、建立一个用户存储过程，用于实现计算指定学生的总分的功能，并执行该存储过程。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
IFEXISTS（SELECTNAMEFROMsysobjects
wherename='query_sum'andtype='p'）
DROPPROCEDUREquery_sum
GO
createPROCEDUREquery_sum
@namechar（8）,@totalfloatoutput
as
select@total=SUM（选课表.成绩）
from学生表join选课表on选课表.学号=学生表.学号
where学生表.姓名=@name
groupby选课表.学号
go
存储过程query_sum创建后，执行该存储过程只需在查询窗口中输入：
declare@tfloat
executequery_sum‘张珊’,@toutput
select‘张珊’as姓名,@tas查询学生的总分
■任务六、在教学管理数据库中创建一个删除类型的触发器nodelete，当在课程表中删除记录时触发该触发器，显示不允许删除的数据的提示信息。
并验证该触发器的功能。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
go
IFEXISTS（SELECTNAMEFROMsysobjects
wherename='nodelete1'andtype='tr'）
DROPPROCEDUREnodelete1
GO
createTRIGGERnodelete1
ON课程表
INSTEADOFDELETE
AS
PRINT'insteadof触发器开始执行'
PRINT'本表中不允许删除数据'
触发器nodelete创建后，验证该触发器只需在查询窗口中输入：
use教学管理
deletefrom课程表where课程号=’08005’
实训项目五数据库维护及其他管理
■任务二、创建一个新登录账户，名称为t_login，密码为123，登录后默认的数据库是教学管理数据库，使用默认语言为us_english。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
sp_addlogin't_login1','123','教学管理','us_english'
■任务三、一非事务方式和事务方式执行SQL程序，实现的功能是将选课表中所有记录的成绩设置为60，然后将选课编号为7的选课记录成绩再加60，分析两者执行结果。
非事务方式：
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
update选课表set成绩=60
update选课表set成绩=成绩+60
where选课编号=7
事务方式：
use教学管理
update选课表set成绩=60
update选课表set成绩=成绩+60
if@@error<>0--如果出现错误则回滚操作
rollbacktran
else--如果没有错误则提交事务
committran
■任务四、通过执行SQL语句来查看数据库中的锁信息。
启动SQLServer2005，单击“新建查询”，在弹出窗口中输入如下语句：
use教学管理
begintran
update选课表set成绩=60
execsp_lock
rollback
五、分析与讨论
1、记下在调试过程中所发现的错误、系统给出的出错信息和对策。
分析讨论对策成功或失败的原因。
2、总结数据库开发及维护的过程及注意点。
升级会员 