SQL2000实验指导书

1、SQL server 2000课程实验一、本实验课在培养实验能力中的地位及作用数据库技术是一个理论和实际紧密联系的技术，而SQL server 2000则是数据库具体应用的一个数据库开发系统。在学习过程中，除了解SQL server系统的设计环境及各种工具的使用方法、数据库系统结构、数据库应用方法等知识外，通过实验对相关技术加深认识，切实加强学生的实践能力，是教学中一个重要且必要的环节。并为其它信息系统专业课程奠定基础。数据库技术是一门理论与实践相结合的课程，只有通过上机实践才能真正领会主教材中介绍的知识。二、应达到的实验能力标准本实验的教学目标是使学生掌握如何使用SQL server 200

2、0 设计数据库系统，了解数据库设计及相关操作的基本概念与方法，进而学会建立与组织、操作数据库。上机实验的主要目标如下:(1)通过上机操作，加深对数据库系统理论知识的理解。(2)通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术。(3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。三、实验主要内容实验一 SQL server2000的安装及SQL server 2000常用工具的使用（学时 2）要求：掌握SQL server 2000的安装方法 掌握SQL server 2000常用工具的使用方法实验二 数据库及表的创建和管理（学时 2）要求：掌握SQL serve

3、r 2000数据库的创建及使用方法 掌握SQL server 2000数据库表的创建及使用方法实验三 约束的创建及使用 （学时 2）要求： 能用TSQL语言及企业管理器创建约束 实验四 表中数据的查询及维护（学时 2） 要求：掌握表中数据的查询方法及操作方法实验五TransactSQL程序设计 （学时 2）要求：掌握TransactSQL程序设计的方法要求：掌握用户自定义函数的创建方法 实验六 SQL Server视图及索引的创建及使用（学时 2）要求：掌握视图的创建及调用 掌握索引的创建及调用实验七 SQL Server 的存储过程 （学时 2）要求：掌握存储过程的创建及调用实验八 SQL

4、Server 的触发器 （学时 2）要求：学会触发器的创建及使用实验九 SQL Server的安全性管理及代理服务 （学时 2）要求：通过实验理解SQL Server的安全性管理及代理服务实验十 数据库的备份及恢复 （学时 2） 要求：学会如何备份及恢复数据库 实验十一 SQL Server数据转换 （学时 2） 要求：掌握数据的导入导出及DTS设计器的使用四、实验成绩考核方法实验学时共16，分8次完成。实验成绩占课程平时总成绩中占32%，每次实验以4分计，独立完成实验内容达到实验目的为4分，完成实验内容但对实验内容理解不清为3分，其他情况酌情定分。五、实验教材、参考资料1 数据库技术实验指导

5、书自编指导书 2 SQL SERVER数据库设计与应用案例训练教程 魏茂林等 电子工业出版社3 SQL Server数据库应用系统开发技术实验指导及习题解答 朱如龙等编著 机械工业出版社六、实验项目汇总表序号内容实验学时实验一SQL server 2000的安装及SQL server 2000常用工具的使用2实验二数据库及表的创建和管理2实验三约束的创建及使用2实验四表中数据的查询及维护2实验五TransactSQL程序设计2实验六SQL Server视图及索引的创建及使用2实验七SQL Server 的存储过程2实验八SQL Server 的触发器2实验九SQL Server的安全性管理及代

6、理服务2 实验十数据库的备份及恢复2实验十一SQL Server数据转换2实验附录：上机实验一1练习安装SQL SERVER 2000及卸载SQL SERVER 2000。2. 管理器创建一个新的SQL Server服务器组，作为Microsoft SQL Servers的子对象，名称为 “NewGroup”3. 在计算机上再安装一个SQL Server命名实例，实例名为 “people”。 使用企业管理器在NewGroup服务器组下创建一个新的SQL Server注册，使她连接people实例。上机实验二1 使用企业管理器创建名为student的数据库，并设置数据库主文件名为syudent_

7、data,大小为10MB;日志文件为student_log,大小为2MB.其他参数取默认值。步骤：a. b. c. d. 2. 使用TransactSQL语言创建一个student1数据库，主文件逻辑名为student1_data，物理文件名为student1.mdf,为10MB,增长速度为10%;数据库的日志文件逻辑名为syudent1_log, 物理文件名为student1.ldf,初始化大小为1MB,最大尺寸为5MB, 增长速度为1 MB；文件存放路径为c:data文件夹下。Create database student1On primry(name=student1_data, fil

8、ename=c:datastudent1.mdf, size=10,maxsize=unlimited, filegrowth=10%)log on(name=student1_log, filename=c:datastudent1.ldf, size=1,maxsize=5, filegrowth=1)3. 在student数据库中创建一个名为pupil的表，要求：(sno char(6) not null,sname char(10) not null,ssex char(2) not null,birthday datetime not null, polity char(20)步骤：

9、a. b.c.保存并命名为pupil即可。上机实验三1.将pupil表的sno设置为主键。 2 为sname字段设置惟一性约束。步骤：3 为ssex和birthday设置检查性约束，要求ssex只能为男或女，birthday应该大于1987-1-1。步骤：4 为polity字段设置默认约束，值为群众。5 再创建一个学生选课表sc(sno char(6) not null,cno char(10) not null,grade real).为sc表创建外键约束，把sc表的sno和pupil表的sno关联起来，在这两个表之间创建一种制约关系。步骤： 最后保存该关系图即可。上机实验四1. 已知一个名

10、为pupil的表：(sno char(6) not null,sname char(10) not null,ssex char(2) not null,birthday datetime not null, polity char(20),sdept char(20) not null)a.利用insert语句向其中插入一条新的记录：（0007，张三，男，1982-3-21，团员,计算机系）insert into pupilvalues(0007，张三，男，1982-3-21，团员，计算机系)b.利用update语句将编号为0004的学生polity改为党员：update pupilset

11、polity=党员where sno=0004a. 利用delete语句将186-1-1以后出生的女同学记录删除。Delete from pupilWhere polity>1986-1-1 and ssex=女2. 针对pupil、sc、course三张表完成以下查询：a. 查询所有学生的基本信息，并按出生日期升序排列select * from pupil order by polityb. 查询女同学的信息和女同学的人数select * from pupil where ssex=女compute count(sno)c. 查询所有男同学的年龄select year(getdate(

12、)-year(birthday) as 年龄 from pupild. 所有选课学生的姓名、选修课程名及成绩select sname,cname,gradefrom pupil,sc,coursewhere pupil.sno=sc.sno and o=oe. 不及格学生的姓名select distinct snamefrom pupil,sc,coursewhere pupil.sno=sc.sno and o=o and grade<60f. 查询刘姓学生的信息select * from pupil where sname like 刘%g. 查询polity为团员或党员的学生信息s

13、elect * from pupil where polity in(团员, 党员)h. 查询各门课程的选课人数select cno ,count(sno) from sc group by cnoi. 查询缺少成绩的学生的学号及课程号select sno,cno from sc where grade is null j. 查询与刘成同一个系的学生情况select * from pupil where sdept in(select sdept from pupil where sname= 刘成)k. 查询选修了课程名为MIS的学生的学号和姓名select sno,sname from p

14、upil where sno in(select sno from sc where cno in(select cno from course wnere cname=MIS) 上机实验五1 编写一个用户自定义函数fun_avggrade,要求根据输入的班级号和课程号，求此班级此课程的平均分。Create function fun_avggrade(classnumber as char(10),coursenumber as char(10)returns realbegindeclare avgresult as decimalselect avgresult=avg(grade)fro

15、m scwhere substring(sno,1,len(sno)-2)= classnumber and cno=coursenumberreturn avgresultend2. 编写一个用户自定义函数,完成以下功能：根据两个输入参数(成绩上限和成绩下限)，求sc数据表中满足输入条件的学生人数。Create function fun_sumren(maxline real,minline real)returns intbegindeclare personcount as intselect personcount=(select count(distinct sno) from sc

16、)from scwhere grade<=maxline real and grade>=minlinereturn personcountend 上机实验六1.如何通过企业管理器和Transact_SQL语句对视图进行创建、修改和删除？通过企业管理器：创建步骤：最后保存该视图即可。修改步骤：进入该界面重新修改该视图即可。删除步骤：通过Transact_SQL语句：创建：create view 视图名 with encryption as select_statement with check option修改：利用 alter view 视图名删除：利用 drop view 视图

17、名2 通过Transact_SQL语句创建一个视图，计算各个班级的各门课程的平均分。Create view v_avggradeAsSelect substring(pupil.sno,1,len(pupil.sno)-2) as 班级， Cname as 课程名称， Avg(grade) as 平均分From pupil,sc,gradeWhere pupil.sno=sc.sno and o=oGroup by substring(pupil.sno,1,len(pupil.sno)-2), Cname3. 通过Transact_SQL语句创建一个视图，显示高等数学未过的学生的信息。Cre

18、ate view v_gradenotpassAsSelect pupil.sno,sname,cname,gradeFrom pupil,sc,courseWhere pupil.sno=sc.sno and o=o and grade<60 And cname= 高等数学4. 通过Transact_SQL语句创建一个视图，查询的数据为99521班学生的考试成绩。Create view v_grade99521AsSelect pupil.sno,sname,cname,gradeFrom pupil,sc,courseWhere pupil.sno=sc.sno and o=o an

19、d left(pupil.sno,5)=99521 5. 如何通过企业管理器创建索引？步骤：点击新建按钮点击确定点击关闭6. 如何删除索引？ 选中该索引点击删除即可或利用 drop index 表名. 索引名完成删除上机实验七1. 创建一个存储过程stugradeinfo,查询班级、学号、姓名、性别、课程名称、分数create procedure stugradeinfoasselect 班级=substring(pupil.sno,1,len(pupil.sno)-2), pupil.sno as 学号， sname as 姓名，ssex as 性别，cname as课程名称,grade a

20、s 分数from pupil,sc,coursewhere pupil.sno=sc.sno and o=o2. 利用企业管理器创建一个存储过程stu_info，根据传入的编号，查询某学生的基本信息。 代码如下：create procedure stu_infostunum char(10)asif not exists(select * from pupil where sno=stunum) print 查无此人！else begin select sno as 编号， sname as 姓名， ssex as 性别， birthday as 出生日期， polity as 政治面貌fro

21、m pupil where sno=stunumend3. 创建一个存储过程stu_age,根据输入的学生姓名，计算该学生的年龄。Create procedure stu_agestuname char(10)asif not exists(select * from pupil where sname=stuname) print 查无此人！else begin declare age int select age=floor(datediff(day,birthday,getdate()/365) from pupil where sname=stuname end4. 创建一个存储过程s

22、tu_grade,根据输入的学生编号，返回其选课及其成绩。Create procedure stu_gradestunum char(10)if not exists(select * from pupil where sname=stuname) print 查无此人！else begin if not exists(select * from pupil,sc where pupil.sno=sc.sno) print 无此学生的选课信息！ else select sname,cname,grade from pupil,course,sc where pupil.sno=sc.sno a

23、nd course=cno=o end上机实验八1 创建一个insert触发器,当在pupil表中插入一条新记录时，给出你已经插入了一条新记录！的提示信息。步骤：程序代码如下：create trigger stuinsert on pupilfor insertasdeclare msg char(50)set msg= 你已经插入了一条新记录！print msg2.创建一个insert触发器,当在pupil表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。 程序代码如下：create trigger stuinsert1 on pupilfor insertasdeclare s

24、tunum char(10)set stunum=(select sno from inserted )if stunum is null begin print 学号不能为空！ rollback transaction endelse begin declare num int set num=(select count(*) from pupil where sno in (select sno from inserted) if num>1 begin print 已经有相同的学号，请重新确认！ rollback transaction end else print 数据录入成功！

25、 end3.创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0-100之间。 程序代码如下： create trigger scinsert on sc for insert as declare gradevalue real set gradevalue=(select grade from inserted) print 触发器开始工作 if not exists(select sno from inserted where sno in(select sno from pupil) begin print 无此学生的编号，请重新确认！ rollback transaction endelse begin if not exists(select cno from inserted where cno in(select cno from course) begin print 无此课程的编号，请重新确认！ rollback transaction end else begin if gradevalue<0 or gradevalue>100 begin print 分数有误，请重新确认！ rollback transaction end else print