SQL上机操作实验

1、 PAGE PAGE 56SQL server2000上機操作实验一、本实验课在培养实验能力中的地位及作用SQL server2000SQL serverSQL server2000是一门实践性很强的课程，只有通过上机实践才能真正领会主教材中介绍的知识。二、应达到的实验能力标准本实验的教学目标是使学生掌握如何使用 SQL server2000 数据库开发系数据库。上机实验的主要目标如下:通过上机操作，加深对数据库系统理论知识的理解。DBMS，(3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。三、实验主要内容试验一SQL server2000的安装及SQL server2000常用

2、工具的使用（学时 4）要求：掌握 SQL server2000 的安装方法掌握 SQL server2000 常用工具的使用方法试验二 数据库及表的创建和管理（学时 4）SQL server2000SQL server2000试验三 约束的创建及使用（学时 2）要求： 能用 TSQL 语言及企业管理器创建约束试验四 表中数据的查询及维护（学时 4）要求：掌握表中数据的查询方法及操作方法试验五 TransactSQL 程序设计（学时 2）TransactSQL要求：掌握用户自定义函数的创建方法试验六 SQL Server 视图及索引的创建及使用（学时 4）要求：掌握视图的创建及调用掌握索引的创建

3、及调用试验七 SQL Server 的存储过程（学时 4）要求：掌握存储过程的创建及调用试验八SQL Server 的触发器（学时 4）要求：学会触发器的创建及使用试验九SQL Server的安全性管理及代理服务（学时 4）要求：通过实验理解 SQL Server 的安全性管理及代理服务试验十 数据库的备份及恢复（学时 2）要求：学会如何备份及恢复数据实验十一SQL Server数据转换（学时 4）要求：掌握数据的导入导出及 DTS 设计器的使用四、实验成绩考核方法实验成绩在课程总成绩中占 20%，每次实验以百分记分，独立完成实验100600.2五、实验教材、参考资料SQL Server学习与

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

5、rver 的安全性管理及代理服务4实验十数据库的备份及恢复2实验十一SQL Server 数据转换4实验附录：上机实验一SQL SERVER 2000及卸载SQL SERVER 。SQL Server Microsoft SQL Servers“NewGroup”在计算机上再安装一个 SQL Server 命名实例，实例名为“people”NewGroup SQL Serverpeople 实例。上机实验二student 主文件名为 syudent_data,大小为 10MB;日志文件为student_log,大小为 2MB.其他参数取默认值。步骤：a.b.c.d.使用TransactSQL语

6、言创建一个student1student1_datastudent1.mdf,10MB,10%;数据库的日志文件逻辑名为syudent1_log, 名为student1.ldf,1MB,5MB, 1 MBc:data文件夹下。Create database student1 On primry (name=student1_data,filename=c:datastudent1.mdf, size=10,maxsize=unlimited, filegrowth=10%)log on (name=student1_log,filename=c:datastudent1.ldf, size=1

7、,maxsize=5, filegrowth=1)在student 数据库中创建一个名为pupil 的表，要求：(sno char(6) not null,sname char(10) not null,ssex char(2) not null,birthday datetime not null, polity char(20)步骤：a.b.c.保存并命名为 pupil 即可。上机实验三将pupil sno 设置为主键。为sname 步骤：为ssex 和birthday ssex birthday 应该大于1987-1-1步骤：为polity sc(snochar(6)notnull,cn

8、ochar(10) not null,grade real).sc sc sno pupil sno 联起来，在这两个表之间创建一种制约关系。步骤：最后保存该关系图即可。上机实验四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)insert 语句向其中插入一条新的记录：00071982-3-21, ）insert into pupilvalues00071

9、982-3-21 计算机系)利用update polity 改为update pupilset polity= 党员where sno= 0004a. delete Delete from pupilWhere polity1986-1-1 and ssex= 女针对pupil、sc、course三张表完成以下查询：查询所有学生的基本信息，并按出生日期升序排列select * from pupil order by polityselect * from pupil where ssex= 女compute count(sno)查询所有男同学的年龄select year(getdate()-y

10、ear(birthday) as 年龄 from pupil所有选课学生的姓名、选修课程名及成绩select sname,cname,grade from pupil,sc,coursewhere pupil.sno=sc.sno and o=o不及格学生的姓名select distinct from pupil,sc,coursewhere pupil.sno=sc.sno and o=o and grade60查询刘姓学生的信息select * from pupil where sname like 刘%polity为团员或党员的学生信息select * from pupil where

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

12、e cno in(select cno from course wnerecname=MIS)上机实验五编写一个用户自定义函数fun_avggrade,的班级号和课程号，求此班级此课程的平均分。Create function fun_avggrade(classnumber as char(10),coursenumber as char(10) returns realbegindeclare avgresultselect avgresult=avg(grade) from scwhere substring(sno,1,len(sno)-2)= classnumber and cno=c

13、oursenumberreturn avgresult end,完成以下功能：根据两个输入参数(成绩上限和成绩下限)sc 入条件的学生人数。Create function fun_sumren (maxline real,minline real) returns intbegindeclare personcount as intselect personcount=(select count(distinct sno) from sc) from scwhere grade=minline return personcountend上机实验六Transact_SQL改和删除？通过企业管理器

14、： 创建步骤：最后保存该视图即可。修改步骤：进入该界面重新修改该视图即可。删除步骤：通过Transact_SQL 创建：create view 视图名with encryption as select_statement with check alter view 视图名drop view 视图名通过Transact_SQL的各门课程的平均分。Create view v_avggrade AsSelect substring(pupil.sno,1,len(pupil.sno)-2) as 班级，Cname as 课程名称，Avg(grade) as 平均分From pupil,sc,grad

15、eWhere pupil.sno=sc.sno and o=oGroup by substring(pupil.sno,1,len(pupil.sno)-2), CnameTransact_SQL 过的学生的信息。Create view v_gradenotpass AsSelect pupil.sno,sname,cname,grade From pupil,sc,courseWhere pupil.sno=sc.sno and o=o and And cname=高等数学Transact_SQL 99521班学生的考试成绩。Create view v_grade99521 AsSelect

16、 pupil.sno,sname,cname,grade From pupil,sc,courseWhere pupil.sno=sc.sno and o=o and left(pupil.sno,5)= 99521步骤：点击新建按钮点击确定点击关闭如何删除索引？选中该索引点击删除即可或利用 drop index 表名. 索引名完成删除上机实验七创建一个存储过程 stugradeinfo,课程名称、分数create procedure stugradeinfo asselect 班级=substring(pupil.sno,1,len(pupil.sno)-2),pupil.sno as 学号

17、，sname as 姓名，ssex as 性 别 ， cname as 课程名称grade as 分 数frompupil,sc,coursewhere pupil.sno=sc.sno and o=o利用企业管理器创建一个存储过程stu_info查询某学生的基本信息。代码如下：create procedure stu_info stunum char(10)asif not exists(select * from pupil where sno=stunum) print 查无此人！ elsebeginselect sno as 编 号 ， sname as 姓名ssex as性别，bir

18、thday as出生日期，polity as 政治面貌from pupilwhere sno=stunum endstu_age,根据输入的学生姓名，计算该学生的年龄。Create procedure stu_age stuname char(10)asif not exists(select * from pupil where sname=stuname) print 查无此人！ elsebegindeclare age intselect age=floor(datediff(day,birthday,getdate()/365) from pupilwhere sname=stunam

19、eendstu_grade,课及其成绩。Create procedure stu_grade stunum char(10)if not exists(select * from pupil where sname=stuname) print 查无此人！ elsebeginif not exists(select * from pupil,sc where pupil.sno=sc.sno) print 无此学生的选课信息！elseselect sname,cname,grade from pupil,course,scwhere pupil.sno=sc.sno and course=cn

20、o=oend上机实验八insert ,当在pupil ！的提示信息。步骤：程序代码如下：create trigger stuinsert on pupil for insertasdeclare msg char(50)set msg=！print msginsert 触发器,pupil 允许在学号中出现重复的编号或出现空值。程序代码如下：create trigger stuinsert1 on pupil for insertasdeclare stunum char(10)set stunum=(select sno from inserted ) if stunum is nullbeg

21、inprint ！rollback transaction endelsebegindeclare num intset num=(select count(*) from pupil where sno in (select sno from inserted)if num1 beginprint ！rollback transaction endelseprint ！endinsert 触发器,sc表中插入一条新记录时，sno 和cno 必须是已经存在的学号和课程号，且grade 应该在0100之间。程序代码如下： create trigger scinsert on for insert

22、asdeclare gradevalue realset gradevalue=(select grade from inserted) print触发器开始工作if not exists(select sno from insertedwhere sno in(select sno from pupil) beginprint ！rollback transactionend elsebeginif not exists(select cno from inserted where cno in(select cno from course)beginprint ！rollback transaction endels