数据库试验答案

1、计算机与信息学院 数据库 实验报告专 业 班 级 学生姓名及学号 课程教学班号 任 课 教 师 实验指导教师 实验地点 2012 2013 学年第 二 学期实验1 使用向导创建和删除数据库一、 实验目的1 熟悉SQL Server 中SQL Server Management Studio的环境2 了解SQL Server 数据库的逻辑结构和物理结构3 掌握使用向导创建和删除数据库的方法二、 实验要求1. 熟练使用SSMS进行数据库的创建和删除操作。2. 完成实验报告。三、 实验内容设有一学籍管理系统，其数据库名为“EDUC”。初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按

2、5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存放路径为“E:sql_data”。日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“E:sql_data”。四、实验步骤1使用SQL Server Management Studio（简称SSMS）创建数据库。（1）启动SSMS 在开始菜单中：所有程序SQL Server 2005 SQL Server Management Studio单击“连接”按钮，便可以进入

3、【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。（2）建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。2. 使用向导删除上面建立的数据库。用SSMS删除建立的数据库EDUC。5、 实验总结 经过本次试验，对于SQL SERVER有了一个初步的认识，也对于在sql server上创建数据库有了一定的了解。实验2 使用SQL语句创建和删除数据库1、 实验目的1. 了解SQL Server 2005数据库的逻辑结构和物理结构。2. 掌握使用SQL 语

4、句创建和删除数据库。二、实验要求1. 熟练使用查询分析器进行数据库的创建和删除操作。2. 完成实验报告。三、实验内容 用SQL语句去创建和删除数据库。4、 实验步骤 1.创建数据库userdb1。2. 根据步骤1的sql语句，写出创建实验1中数据库EDUC的sql语句，并建立数据库EDUC.create database EDUCon(name=EDUC_data,-数据文件的逻辑名称,注意不能与日志逻辑同名filename=E:sql_datastudent_data.mdf ,-物理名称，注意路径必须存在size=10,-数据初始长度为Mmaxsize=50,-最大长度为Mfilegrow

5、th=5%)-数据文件每次增长Mlog on( name=EDUC_log, filename=E:sql_datastudent_log.ldf , size=2 , maxsize=5 , filegrowth=1) 3.用SQL语句删除步骤1建立的数据库userdb1。drop database userdb1；五、实验总结 掌握了用SQL语句创建和删除数据库，对SQL Server都有了进一步的认识和了解，发现其实SQL语句还是挺好用的。实验三 分别用向导和SQL语句创建和删除表1、 实验目的1了解表的结构特点。2了解SQL Server的基本数据类型。3学会使用T-SQL语句创建表。

6、二实验要求1. 完成SQL语句创建和删除基本表。2. 完成实验报告。 三.实验内容在数据库EDUC中，创建如下几个表： 表3.1 class表（班级信息表）字段名称类 型允许空值主 键说 明ClsNOChar(6)NOT NULL是班号ClsNameVarchar(16)NOT NULL班名DirectorVarchar(10)NULL辅导员SpecialtyVarchar(30)NULL专业表3.2 student表（学生信息表）字段名称类 型允许空值主 键说 明SnoChar(8)NOT NULL是学号SnameVarchar(10)NOT NULL姓名SexChar(2)性别：男、女Cl

7、sNOChar(6)NULL班级的编号，（外键）参照表ClassSaddrVarchar(20)住址Sagenumeric(3, 0)年龄，大于10，但小于30岁HeightDecimal(4,2)身高表3.3 course表（课程信息表）字段名称类 型允许空值主 键说 明CnoChar(4)NOT NULL是主键CnameVarchar(16)NOT NULL课程的名称Cpnochar(4)NULL先修课程的课程号（外键）,参照cnoCcreditTinyint学分表3.4 sc表（学生选课成绩表）字段名称类 型允许空值主 键说 明SnoChar(8)NOT NULL是学号，参照Studen

8、t，与Cno组成主键CNOChar(4)NOT NULL是课程号，参照CoursegradeNumeric(4,1)NULL成绩四实验步骤1. 打开查询窗口，输入创建基本表的sql语句。（1） student表：create table student(sno char(8) NOT NULL primary key ,-学号sname varchar(10) NOT NULL ,-姓名sex char(2) check( sex=女 or sex=男),-性别：男、女clsno char(6) ,-班级编号，（外键）参照表classforeign key (clsno) references

9、 class(clsno),sadrr varchar(20) ,-住址sage numeric(3,0),-年龄，大于，但小于岁height decimal(4,2)-身高)（2） class表create table class(clsno char(6) NOT NULL primary key ,-班号clsname varchar(16) NOT NULL ,-班名director varchar(10) NULL,-辅导员specialty varchar(30) NULL -专业)（3） course表create table course(cno char(4) NOT NUL

10、L primary key ,-主键cname varchar(16) NOT NULL ,-课程名称cpno char(4) ,-先修课程的课程号（外键）,参照cnoccredit tinyint -学分)（4） sc表create table sc(sno char(8) NOT NULL,-学号，参照Student，与Cno组成主键cno char(4) NOT NULL,-课程号，参照Coursegrade numeric(4,1) NULL,-成绩primary key(sno ,cno),foreign key(sno) references student(sno),foreig

11、n key(cno) references course(cno),)五、实验总结 通过本次试验，基本掌握了用sql语言创建数据库表的基本方法。实验四 数据更新操作一. 实验目的1.熟悉使用UPDATE/INSERT/DELETE语句进行表操作； 2.能将这些更新操作应用于实际操作中去；二. 实验要求1 完成下面的实验内容，并提交实验报告；2 在实验报告中附上相应的代码；三. 实验内容(1) 向表（Student）中插入数据SnoSnameSsexClsNOSaddrSageHeight20090101王军男CS01下关40#201.7620090102李杰男CS01江边路96#221.722

12、0090306王彤女MT04中央路94#191.6519940107吴杪女PH08莲化小区74#181.60插入数据之后使用命令：Select * from Student; 检查插入数据的正确性insert into studentvalues (20090101,王军,男,cs01,下关#,20,1.76);insert into studentvalues (20090102,李杰,男,cs01,江边路#,22,1.72);insert into studentvalues (20090306,王彤,女,MT04,中央路#,19,1.65);insert into studentvalu

13、es (19940107,吴杪,女,PH08,莲化小区#,18,1.60);select * from student;（2）向表（Class）中插入数据ClsNOClsNameDirectorSpecialtyCS01计算机一班王宁计算机应用CS02计算机二班王宁计算机应用MT04数学四班陈晨数学PH08物理八班葛格物理插入数据之后使用命令：Select * from Class; 检查插入数据的正确性insert into classvalues(cs01,计算机一班,王宁,计算机应用);insert into classvalues(cs02,计算机二班,王宁,计算机应用);insert

14、 into classvalues(MT04,数学四班,陈晨,数学);insert into classvalues(PH08,物理八班,葛格,物理);select * from class;（3）向表（Course ）中插入数据CnoCnameCpnoCredit0001高等数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select * from Course; 检查插入数据的正确性insert into coursevalues(0001,高等数学,NULL,6);insert into coursevalues(0003,计算机基础,0001,3)

15、;insert into coursevalues(0007,物理,0001,4);select * from course;（4）向表（SC ）中插入数据SNOCNOGrade20090101000190200901010007862009010200018720090102000376200903060001872009030600039319940106000785insert into scvalues(20090101,0001,90);insert into scvalues(20090101,0007,86);insert into scvalues(20090102,0001,

16、87);insert into scvalues(20090102,0003,76);insert into scvalues(20090306,0001,87);insert into scvalues(20090306,0003,93);insert into scvalues(19940107,0007,85);select * from sc;(5).对于student表，将所有班级号为CS01的，并且年龄小于20岁的学生的班级号改为CS02。update studentset clsno=cs02where clsno=cs01andsage20 andclsno=cs02;inse

17、rt into student(sno,sname,ssex,sage,clsno)values(20071101,张三,男,19,cs01)(7).对于student表，将年龄最小的学生的家庭地址去掉。update studentset sadrr=nullwhere sage=(select MIN(s.sage) from student s); (8).对于student表，将平均年龄最小的一个班级编号改为GL01 由于给定的class表中数据不足，因而添加数据以用来满足要求。 insert into classvalues(gl01,计算机班,王,计算应用); update stud

18、ent set student.clsno=gl01 where student.clsno= (select t1.clsno from (select s1.clsno,avg(s1.sage) a from student s1 group by s1.clsno) t1,(select MIN(t2.a1) m from (select s.clsno,avg(s.sage) a1 from student s group by s.clsno) t2 ) t3 where t1.a=t3.m); 五、实验总结通过本次试验，懂得了用sql语言来插入数据，以及一些简单的数据更新操作，也对

19、sql server有了更多的了解。在实验的过程中，也遇到许多的问题，并在实验的过程中解决。比方说，在往表中插入数据时，先插的student表的数据，结果就发现出现错误，原来是student表中的clsno是class表的外码，需要先插入class表，student表内容才能插进去；还有在数据更新的过程中，比方说“对于student表，将平均年龄最小的一个班级编号改为GL01”，由于class表中没有班号位“gl01”，因而出错，这是没有违反完整性约束条件，在插入一组班号为“gl01”时，该更新操作就能正确的运行。实验五 创建和删除索引一. 实验目的1 了解索引的类型和应用。2 学会用SQL语

20、句对表创建和删除索引二. 实验要求1. 了解索引类型并比较各类索引的不同之处2.完成索引的创建和删除，并提交实验报告。三. 实验内容 1. 用SQL语句分别建立以下索引(1) 在student表的Sname列上建立普通降序索引Stusname。(2) 在course表的Cname列上建立唯一索引Coucname。(3) 在sc表的Sno(升序), Cno(升序)和grade (降序)三列上建立一个普通索引SCno。2. 用SQL语句删除索引 Student表的Stusname索引。四、实验步骤1. 用SQL语句分别建立以下索引（1）在student表的sname列上建立普通降序索引Stusna

21、me。 create index stusname on student (sname desc); （2）在course表的cname列上建立唯一索引Coucname。 create unique index coucname on course(cname); (3) 在sc表的Sno(升序), Cno(升序)和grade (降序)三列上建立一个普通索引SCno。 create index scno on sc(sno,cno,grade desc) 2. 删除索引删除Student表的Stusname索引。 drop index stusname on student;五、实验总结 通过

22、本次试验，学会了用sql语言为基本表创建索引和删除索引。实验六 数据查询 一 实验目的1观察查询结果, 体会SELECT语句实际应用；2要求学生能够在查询分析器中使用SELECT语句进行简单查询。3. 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二 实验要求1. 完成简单查询和连接查询操作，并验收实验结果提交实验报告三 实验内容所有的查询全部用Transact-SQL语句实现。此部分查询包括投影、选择条件表达、数据排序、使用临时表等。对EDUC数据库实现以下查询：1) 查询计算机应用专业的学生学号和姓名；2) 查询选修了课程的学生学号；3) 查询选修课程0001 且成绩在809

23、0 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；4) 查询计算机应用和数学专业的姓“张”的学生的信息。5) 查询“0001”课程的成绩高于张三的学生学号和成绩；6) 查询没有选修“0002”课程的学生姓名；四 实验步骤打开查询窗口，输入SQL查询语句，点击“执行”按钮，查看查询结果。1） 查询计算机应用专业的学生学号和姓名； select sno,sname from student,class where student.clsno=class.clsno and class.specialty=计算机应用; 2） 查询选修了课程的学生学号； select sno from sc

24、where sno is not null; 3） 查询选修课程0001 且成绩在8090 之间的学生学号和成绩，并将成绩乘以系数0.75 输出； select sno,grade*0.75 from sc where cno=0001and grade between 80 and 90; 4） 查询计算机应用和数学专业的姓“张”的学生的信息。select *from student,classwhere sname like张_and student.clsno=class.clsno and (specialty=计算机应用 or specialty=数学);5） 查询“0001”课程的

25、成绩高于张三的学生学号和成绩；select student.sno,gradefrom student,scwhere cno=0001and student.sno=sc.sno and grade in (select grade from student,sc where sname=王军and student.sno=sc.sno )6） 查询没有选修“0002”课程的学生姓名；select distinct snamefrom student,scwhere student.sno=sc.sno and cno!=0002 五、实验总结通过本次试验，复习用sql语言处理数据查询的一些

26、方法，更好的掌握数据的查询。实验七 使用聚集函数的SELECT语句一 实验目的1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL语言的嵌套查询语句的理解。二 实验要求1.在实验之前做好准备。2.完成实验，并验收实验结果提交实验报告。三 实验内容在数据库EDUC中用SQL语句实现如下查询：1)求学生的总人数。2)求选修了课程的学生人数。3)求课程的课程号和选修该课程的人数。4)求选修课程超过2 门课的学生学号。四. 实验步骤1) 求学生的总人数.select COUNT(*)from student; 2) 求选修了课

27、程的学生人数。select COUNT(distinct sno) from sc; 3) 求课程的课程号和选修该课程的人数。 select COUNT(cno) from scgroup by cno;4)求选修课程超过2 门课的学生学号。select snofrom scgroup by snohaving COUNT (*)1;5、 实验总结 通过本次试验对聚集函数的SELECT语句使用更加熟悉。 实验八 视图的定义与使用一 实验目的1. 熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；2. 熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联

28、系；3.学习灵活熟练的进行视图的操作，认识视图的作用。二实验要求1. 在实验开始之前做好准备工作。2. 实验之后提交实验报告，思考视图和基本表的区别三实验内容1.定义视图在EDUC数据库中，已Student、Course 和SC表为基础完成一下视图定义:1) 将Student, Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_SC_G；2) 定义一个反映学生出生年份的视图V_YEAR；3) 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G；4) 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。2.使用视图1) 查询以上所建的视图结果。2)

29、查询平均成绩为90分及以上的学生学号、姓名和成绩；3) 查询课程成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；4) 查询1994年出生的学生学号和姓名。四 实验步骤1.定义视图1) 将Student, Course 和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_SC_G.create view v_sc_gas select student.sno,sname,o,cname,grade from sc,student,course where sc.sno=student.sno o=o; select *from v_sc_

30、g;drop view v_sc_g;2) 定义一个反映学生出生年份的视图V_YEAR.create view v_year asselect sno,sname,2013-sage agefrom student;select *from v_year;3) 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G.create view v_avg_s_gasselect student.sno,COUNT(cno) cnum,avg(grade) avg_agefrom sc,student where sc.sno=student.snogroup by student.sno;

31、4) 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。create view v_avg_c_g as select cno,count(*) as cnum,avg(grade) avg_gradefrom scgroup by cno; select *from v_avg_c_g;2.使用视图1） 查询以上所建的视图结果。2） 查询平均成绩为90分及以上的学生学号、姓名和成绩；select student.sno,sname,avg_agefrom v_avg_s_g s,studentwhere s.sno=student.sno and s.avg_age=90;3) .

32、查询科目成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；select student.sno,sname,cno,grade,avg_agefrom sc a,student,v_avg_s_gwhere a.sno=student.sno and Student.sno=v_avg_s_g.sno and not exists ( select * from sc b ,v_avg_c_g where a.Grade v_avg_c_g.avg_grade and a.Cno=b.Cno )4） 查询1994年出生的学生学号和姓名。select sname,snofrom v_year

33、where age=1994;5、 实验总结视图的使用很简单，有时使查询变得更加简单，和表的使用很像。视图就是一种很特殊的表。实验九 触发器的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQL Server触发器的创建及使用方法，加深SQL触发器的理解。通过对数据的更新操作体会其触发器的作用。二、实验要求1. 在实验之前做好准备2. 试验之后提交实验报告，并验收实验结果三、实验内容1. 在班级表class中增加班级人数（c_total）字段2. 创建INSERT触发器t_inst_stu3. 创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级

34、编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。4. 创建UPDATE触发器t_update_stu。5. 体会触发器的作用。四、实验步骤1. 在班级表class中增加班级人数（c_total）字段，默认值是0；并手动统计人数作为该字段的值；alter table class add c_total int NOT NULL DEFAULT 0- alter table class drop column c_total -删除一个字段2. 创建INSERT触发器t_inst_stu：在学生表(student)中每新增一名学生，若其班级编号非空，则将班级表（cla

35、ss）中相应班级的人数（c_total）自动加1。create trigger dbo.t_inst_stu on dbo.student for insert as begin declare cls char(6) select cls=ClsNo from inserted if exists(select * from student where student.ClsNo=cls and student.ClsNo is not null ) print 开始查找并将班级人数加1. update class set c_total=c_total+1 where ClsNo=cls

36、print 班级人数已增加！End3. 创建DELETE触发器t_dele_stu：在学生表(student)中删除一名学生，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动减1。create trigger dbo.t_dele_stuon dbo.studentfor deleteas begindeclare cls char(6)select cls=clsno from deletedif exists(select *from studentwhere student.clsno=cls andstudent.clsno is not null )p

37、rint 开始查找并将班级人数减.update classset c_total=c_total-1where class.clsno=clsprint 班级人数已减少！End4. 创建UPDATE触发器t_update_stu:当某学生所在班号发生变化时（即调到另一班级后），将其原先所在班级的人数（c_total）减1，将新调入的班级班级的人数（c_total）加1。create trigger t_update_stu on student for update as begin declare oldClsNO char(8),newClsNO char(8);select oldCls

38、NO from deleted;select newClsNO from inserted;if oldClsNO is not null update class set c_total=c_total+1 where ClsNO=oldClsNO; if newClsNO is not nullupdate class set c_total=c_total-1 where ClsNO=newClsNO; End5. 触发器的作用：1) 查看班级表（class）2）对学生表(student)分别插入（INSERT）、删除（DELETE）和修改（UPDATE）元组insert into st

39、udentvalues(20102680,古鑫,男,cs01,合工大,20,1.70);delete from studentwhere sname=古鑫;3）再次查看班级表（class），检查其数据的变化情况，体会触发器的作用。select *from class;五.实验总结 对触发器的创建把握的不是太好，还是需要多加锻炼。实验十 存储过程的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQL Server存储过程的创建及使用方法，加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。二、实验要求1. 在实验之前做好准备2. 试验之后提交实验报告，并验收实验结果三、实验内容1、

40、创建一个不带参数的存储过程p_stu_info1，实现对满足要求的学生基本信息的查询。2、创建一个带有参数的存储过程p_stu_info2，实现对满足要求的学生基本信息的查询。3、创建一个存储过程p_stu_info3,根据输入的学号，查询某学生的基本信息。4、创建一个存储过程p_stu_grade,根据输入的学号，返回其选课及其成绩。5.使用SQL语句分别执行p_stu_info1、p_stu_info2、p_stu_info3和p_stu_grade，并将结果显示出来。四、实验步骤1.建立不带参数的存储过程p_stu_info1（选择所有年龄21岁的男同学）。create procedure p_stu_infolas beginselect distinct *from studentwhere sage20 and sex=男End执行语句：exec p_stu_info12.建立带有参数的存储过程p_stu_info2，根据传递的参数只显示与指定学号的学生同龄的所有同学（如：200711