SQL Server实训报告.docx

1、SQL Server实训报告数据库设计与实现（SQL）实训报告 （2011-2012学年第一学期） 系 别：班 级：姓 名：学 号：指导教师：设计时间：二一一年十二月三十日 实训项目：教学管理系统数据库设计一、 实训目的和要求1、 通过实训掌握本学期所学数据库开发的相关知识；2、 掌握数据库、数据表的创建及修改；3、 掌握基本查询、连接查询、嵌套查询的使用；4、 掌握变量的定义、函数的使用和简单程序设计；5、 掌握索引、视图、存储过程和触发器的创建；6、 掌握数据库的维护及帐户的创建。二、实训内容 为了有效的管理资源，并方便的完成教学信息检索操作，很多学校都有自己的教学管理系统，本次实训主要完

2、成一个基于实际需求的教学管理系统的设计，主要完成后台数据库部分的内容。包括学生表、教师表、授课任务表、课程表、选课表的设计与数据输入，外键关系的建立，课程、成绩等信息的查询操作。四、实训步骤实训项目一 数据库、数据表的创建及修改任务一、创建一个名为教学管理的数据库，数据文件存放在“D：教学管理”路径下。它由5MB主要数据文件（教学管理1.mdf和1MB日志文件（教学管理_log.ldf）组成。并且主要数据文件以2MB增长速度增长，其最大数据文件大小为15MB，事物日志文件以1MB增长速度增长，其最大数据文件大小为10MB。）启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入

3、如下语句：create database 教学管理on(name=教学管理1,filename=c:教学管理1.mdf,size=5,maxsize=15,filegrowth=2)log on(name=教学管理_log,filename=c:教学管理_log.ldf,size=1,maxsize=10,filegrowth=1)任务二、为教学管理数据库增加一个次要数据文件（教学管理2.ndf），该文件大小为4MB，以10%增长速度增长，最大增长到10 MB启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：alter database 教学管理add file(

4、name=教学管理,filename=c:教学管理2.ndf,size=4,maxsize=10,filegrowth=10%)任务三、将“教学管理”数据库中主要数据文件“教学管理1.mdf”大小扩充为20 MB。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：alter database 教学管理modify file(name=教学管理1,size=20)任务五、创建教师表。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理create table 教师(教师名 char (4) not null PRIMARY

5、 KEY,教师姓名 varchar(10),性别 char(2) default 男,职称 varchar(10),所在部门 varchar(30),登录密码 varchar(30) default teacher)实训项目二 数据查询的实现任务一、对学生表进行学号、姓名、性别、出生日期、专业的查询，结果按姓名字段升序排序。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,姓名,性别,出生日期,专业from 学生表order by 姓名 asc任务二、为结果集制定姓名，查询学生表中的学生及姓名列，要求查询结果中姓名列的名称为“学

6、生姓名”。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,姓名 as 学生姓名from 学生表任务三、查询所有学生的学号、姓名、性别和年龄。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,姓名,性别,year(getdate()-year(出生日期)as 年龄from 学生表任务四、显示学生的专业情况，要求：不显示重复的专业。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select distinct 专业fr

7、om 学生表任务五、显示前3名较年长的学生信息启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select top 3 学号,姓名,出生日期from 学生表order by 出生日期任务六、查询不是计算机网络技术专业的女同学的信息。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select *from 学生表where 专业计算机网络技术and 性别=女任务七、使用IN关键字查询成绩是88、87、75的同学信息。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use

8、 教学管理select *from 选课表where 成绩 IN (88,87,75)order by 成绩任务八、查询所有姓李的老师的信息。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select *from 教师where 教师姓名 like 李%任务九、计算选课表中的成绩总分、平均分、最高分、最低分。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select SUM(成绩) as 总分,AVG(成绩)AS 平均分,MAX(成绩)AS 最高分,MIN(成绩)AS 最低分from 选课表任务

9、十、按学生分组计算选课表中的成绩总分、平均分、最高分、最低分。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号, SUM(成绩) as 总分,AVG(成绩)AS 平均分,MAX(成绩)AS 最高分,MIN(成绩)AS 最低分from 选课表group by 学号任务十一、按学生分组，求出学生的总分，要求生成汇总行和明细行。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,成绩from 选课表order by 学号compute SUM(成绩)by 学号任务十二、使

10、用连接查询，查询教师所担任的课程号、课程名和课程性质。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 教师号,授课任务表.课程号,课程名,课程性质from 授课任务表 join 课程表 on (授课任务表.课程号=课程表.课程号)任务十三、查询没有选课的学生信息。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,姓名,性别from 学生表 where 学号 not in (select distinct 学号 from 选课表)任务十四、查询选课表中低于平均分的学生的

11、学号。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select distinct 学号 as 低于平均分的学生from 选课表 where 成绩 40beginprint选择该课程的人数有+str(stcount)+人print 进行分班上课endelsebeginprint选择该课程的人数有+str(stcount)+人print 进行分班上课endgo任务八、使用CASE语句对学生按性别分类。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理select 学号,姓名,性别=case 性别whe

12、n 男 then 该生是男同学when 女 then 该生是女同学endfrom 学生表实训项目四 索引、视图、存储过程、触发器创建任务一、对学生表创建一个复合索引，使用姓名字段和出生日期字段。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理create INDEX I_studenton 学生表(姓名,出生日期)withPAD_INDEX,FILLFACTOR=40任务二、使用SQL语句创建一个视图，命名为 V_ 选课。要求基表的数据来源为：学生表、选课表、授课任务表、教师、课程表。选择的字段为：学生表中的学生号、姓名和班级，课程表中的课程号和

13、课程名字段，授课任务表中的授课学期字段，教师表中的教师号、教师姓名和所在部门字段。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理gocreate view view_选课表asselect 学生表.学号,学生表.姓名,学生表.班级,课程表.课程号,课程表.课程名,选课表.成绩,授课任务表.授课学期,教师.教师号,教师.教师姓名from 授课任务表 inner join教师 on 授课任务表.教师号=教师.教师号 inner join课程表 on 授课任务表.课程号=课程表.课程号 inner join选课表 on 授课任务表.授课编号=选课表.

14、授课编号 inner join学生表 on 选课表.学号=学生表.学号任务三、建立一个用户存储过程，实现在教学管理数据库中查询所有学生的学号、姓名、课程名、学分和成绩的功能，并执行该存储过程。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理goIF EXISTS(SELECT NAME FROM sysobjects where name=st_score and type=p)DROP PROCEDURE st_scoreGOcreate PROCEDURE st_scoreasselect 学生表.学号,学生表.姓名,课程表.课程名,课程表.

15、学分,选课表.成绩from 学生表 join 选课表 on 选课表.学号=学生表.学号join 授课任务表 on 授课任务表.授课编号=选课表.授课编号join 课程表 on 授课任务表.课程号=课程表.课程号go存储过程st_score创建后，执行该存储过程只需在查询窗口中输入：execute st_score任务四、建立一个用户存储过程，实现在教学管理数据库中查询某人指定课程的成绩和学分的功能，并执行该存储过程。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理goIF EXISTS(SELECT NAME FROM sysobjectswhe

16、re name=query_stscore and type=p)DROP PROCEDURE query_stscore5GOcreate PROCEDURE query_stscore5name char(8),cname char(16)asselect 学生表.学号,学生表.姓名,课程表.课程名,课程表.学分,选课表.成绩from 学生表 join 选课表 on 选课表.学号=学生表.学号 join 授课任务表 on 授课任务表.授课编号=选课表.授课编号 join 课程表 on 授课任务表.课程号=课程表.课程号where 学生表.姓名 = name and 课程表.课程名 = cn

17、amego存储过程query_stscore创建后，执行该存储过程只需在查询窗口中输入：execute query_stscore 张珊 ,大学英语任务五、建立一个用户存储过程，用于实现计算指定学生的总分的功能，并执行该存储过程。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理goIF EXISTS(SELECT NAME FROM sysobjectswhere name=query_sum and type=p)DROP PROCEDURE query_sumGOcreate PROCEDURE query_sumname char(8),t

18、otal float outputasselect total=SUM(选课表.成绩)from 学生表 join 选课表 on 选课表.学号=学生表.学号 where 学生表.姓名 = namegroup by 选课表.学号go存储过程 query_sum创建后，执行该存储过程只需在查询窗口中输入：declare t floatexecute query_sum 张珊,t outputselect 张珊 as 姓名,t as 查询学生的总分任务六、在教学管理数据库中创建一个删除类型的触发器nodelete，当在课程表中删除记录时触发该触发器，显示不允许删除的数据的提示信息。并验证该触发器的功能

19、。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理goIF EXISTS(SELECT NAME FROM sysobjectswhere name=nodelete1 and type=tr)DROP PROCEDURE nodelete1GOcreate TRIGGER nodelete1ON 课程表INSTEAD OF DELETEASPRINT instead of 触发器开始执行PRINT 本表中不允许删除数据触发器nodelete创建后，验证该触发器只需在查询窗口中输入：use 教学管理delete from 课程表 where 课程

20、号=08005 实训项目五 数据库维护及其他管理任务二、创建一个新登录账户，名称为t_login，密码为123，登录后默认的数据库是教学管理数据库，使用默认语言为us_english。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：sp_addlogin t_login1,123,教学管理,us_english任务三、一非事务方式和事务方式执行SQL程序，实现的功能是将选课表中所有记录的成绩设置为60，然后将选课编号为7的选课记录成绩再加60，分析两者执行结果。非事务方式：启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教

21、学管理update 选课表 set 成绩=60update 选课表 set 成绩=成绩+60where 选课编号=7事务方式：use 教学管理update 选课表 set 成绩=60update 选课表 set 成绩=成绩+60if error 0 -如果出现错误则回滚操作rollback tranelse -如果没有错误则提交事务commit tran任务四、通过执行SQL语句来查看数据库中的锁信息。启动SQL Server 2005，单击“新建查询”，在弹出窗口中输入如下语句：use 教学管理begin tranupdate 选课表 set 成绩 =60exec sp_lockrollback五、分析与讨论1、记下在调试过程中所发现的错误、系统给出的出错信息和对策。分析讨论对策成功或失败的原因。2、总结数据库开发及维护的过程及注意点。