数据库试验答案

计算机与信息学院数据库实验报告专业班级学生姓名及学号课程教学班号任课教师实验指导教师实验地点20122013学年第 二 学期实验1使用向导创建和删除数据库实验目的1. 熟悉 SQL Server 中 SQL Server Management Studio 的环境2. 了解SQL Server数据库的逻辑结构和物理结构3. 掌握使用向导创建和删除数据库的方法实验要求1. 熟练使用SSMS进行数据库的创建和删除操作。2. 完成实验报告。实验内容设有一学籍管理系统，其数据库名为“EDUC。初始大小为10MB,最大为50MB数据库自动增长，增长方式是按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在开始菜单中：所有程序 SQLServer 2005 SQLServer ManagementStudio 单击连 接”按钮，便可以进入 【SQLServer Management Studio】窗口。如果身份验证选择的是 混 合模式”，则要输入sa的密码。（2）建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC12. 使用向导删除上面建立的数据库。用SSMS删除建立的数据库 EDUC五、实验总结经过本次试验，对于 SQL SERVER有了一个初步的认识，也对于在sql server上创建数据库有了一定的了解。实验2使用SQL语句创建和删除数据库一、实验目的1. 了解SQL Server 2005数据库的逻辑结构和物理结构。2. 掌握使用SQL语句创建和删除数据库。二、实验要求1. 熟练使用查询分析器进行数据库的创建和删除操作。2. 完成实验报告。三、实验内容用SQL语句去创建和删除数据库。四、实验步骤1.创建数据库userdb1。2.根据步骤1的sql语句，写出创建实验1中数据库EDUC勺sql语句，并建立数据库EDUC. create database EDUCon（n ame= EDUC_data，-数据文件的逻辑名称，注意不能与日志逻辑同名filename =E:sql_datastudent_data.mdf ,-物理名称，注意路径必须存在 size=10,-数据初始长度为Mmaxsize=50,-最大长度为Mfilegrowth =5%）-数据文件每次增长Mlog on（name=EDUC_log ,filename =E:sql_datastudent_log.ldf ,size=2 ,maxsize=5 ,filegrowth =1）3.用SQL语句删除步骤1建立的数据库userdbl。drop databaseuserdb1;五、实验总结掌握了用SQL语句创建和删除数据库，对SQL Server都有了进一步的认识和了解，发现其实SQL语句还是挺好用的。实验三分别用向导和SQL语句创建和删除表一、实验目的1. 了解表的结构特点。2. 了解SQL Server的基本数据类型。3. 学会使用T-SQL语句创建表。二. 实验要求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是学号Sn ameVarchar(10)NOT NULL姓名SexChar(2)性别：男、女ClsNOChar(6)NULL班级的编号，（外键）参照表ClassSaddrVarchar(20)住址Sagenu meric(3, 0)年龄，大于10,但小于30岁HeightDecimal(4,2)身高表3.3 course 表（课程信息表）字段名称类型允许空值主键说明CnoChar(4)NOT NULL是主键四.实验步骤1.打开查询窗口，输入创建基本表的sql语句。(1)student 表：create table student(snochar(8) NOT NULL primary key，-学号sname varchar(10) NOT NULL，-姓名sex char(2) check( sex=女or sex=男),-性别：男、女 clsno char(6)，-班级编号，(外键)参照表 class foreign key (clsno) references 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 NULL primary key，-主键 cname varchar(16) NOT NULL ，-课程名称cpno char(4),-先修课程的课程号(外键)，参照cnoccredit tinyint -学分)(4) sc 表create table sc(snochar(8) NOT NULL,-学号，参照 Student,与Cno组成主键 cno char(4) NOT NULL,-课程号，参照 Coursegrade numeric(4,1) NULL,-成绩primary key (sno ,cno),foreign key(sno) referencesstudent(sno),foreign key(cno) references course(cno),)五、实验总结通过本次试验，基本掌握了用sql语言创建数据库表的基本方法。实验四数据更新操作一. 实验目的1.熟悉使用 UPDATE/INSERT/DELET语句进行表操作；2. 能将这些更新操作应用于实际操作中去；二. 实验要求1. 完成下面的实验内容，并提交实验报告；2 .在实验报告中附上相应的代码；三. 实验内容(1)向表(Student )中插入数据SnoSn ameSsexClsNOSaddrSageHeight王军男CS01下关40#201.76李杰男CS01江边路96#221.7220090306王彤女MT04中央路94#191.65吴杪女PH08莲化小区74#181.60插入数据之后使用命令：Select * from Stude nt;检查插入数据的正确性insert into studentvalues （,王军,男,cs01,下关 #,20,1.76）;insert into studentvalues （,李杰,男,cs01,江边路 #,22,1.72）;insert into studentvalues （,王彤,女,MT04，中央路 #,19,1.65）; insert into studentvalues （,吴杪,女,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 into classvalues(MT04，数学四班,陈晨,数学); insert into classvalues(PH08，物理八班,葛格,物理); select * from class;(3)向表(Course )中插入数据CnoCn ameCpnoCredit0001高等数学Null60003计算机基础000130007物理00014插入数据之后使用命令：Select * from Course;检查插入数据的正确性insert into coursevalues(0001,高等数学,NULL, 6);insert into coursevalues(0003，计算机基础：0001,3);insert into coursevalues(0007,物理,0001,4);select * from course;(4)向表(SC )中插入数据SNOCNOGrade00019020090101000786000187000376200903060001872009030600039319940106000785insert into sc values(,0001,90); insert into sc values(,0007,86); insert into sc values(,0001,87); insert into sc values(,0003,76); insert into sc values(,0001,87); insert into sc values(,0003,93); insert into sc values(,0007,85); select * from sc;(5) .对于student表，将所有班级号为CS01的，并且年龄小于20岁的学生的班级号改为CS02。update studentset clsno =cs02where clsno=cs01andsage20 andclsno=cs02：insert into student(sno,sname,ssex,sageclsno)values(，张三，男,19,cs01)(7) .对于student表，将年龄最小的学生的家庭地址去掉。update studentset sadrr= nullwhere sage=(select MIN (s.sagqfrom student s);(8) .对于student表，将平均年龄最小的一个班级编号改为GL01由于给定的class表中数据不足，因而添加数据以用来满足要求。insert into classvalues(gl01,计算机班，王,计算应用);update stude ntset stude nt.cls no=gl01where stude nt.cls no=(select t1 .cls nofrom (select s1.clsno,avg(s1.sag a from student s1 group by s1.clsno) t1 ,(selectMIN (t2.a1) m from(select s.clsno ,avg(s.sag a1 from student s group by s.clsno) t2 ) t3where t1.a=t3.m);五、实验总结通过本次试验，懂得了用sql语言来插入数据，以及一些简单的数据更新操作，也对sqlserver有了更多的了解。在实验的过程中，也遇到许多的问题，并在实验的过程中解决。比方说，在往表中插入数据时，先插的student表的数据，结果就发现出现错误，原来是student表中的clsno是class表的外码，需要先插入class表，student表内容才能插进去；还有在数据更新的过程中，比方说“对于student表，将平均年龄最小的一个班级编号改为GL01”，由于class表中没有班号位“ gl01 ”，因而出错，这是没有违反完整性约束条件，在插入一组班号为“gl01 ”时，该更新操作就能正确的运行。实验五创建和删除索引一. 实验目的1. 了解索引的类型和应用。2. 学会用SQL语句对表创建和删除索引二. 实验要求1. 了解索引类型并比较各类索引的不同之处2. 完成索引的创建和删除，并提交实验报告。三. 实验内容1. 用SQL语句分别建立以下索引在stude nt表的Sn ame列上建立普通降序索引Stus name。(2) 在course表的Cname列上建立唯一索引Coucname(3) 在sc表的Sno(升序),Cno(升序)和grade (降序)三列上建立一个普通索引SCno2. 用SQL语句删除索引Stude nt 表的 Stus name 索引。四. 实验步骤1. 用SQL语句分别建立以下索引(1 )在stude nt表的sn ame列上建立普通降序索引Stus name。create index stusnameon student (sname des;(2)在course表的cname列上建立唯一索引Coucnamecreate unique index coucnameon course(c name); 在sc表的Sno(升序),Cno(升序)和grade (降序)三列上建立一个普通索引SC nocreate index scnoon sc(sno,cno,grade desc)2. 删除索引删除Stude nt表的Stus name索引。drop index stusname on student;五. 实验总结通过本次试验，学会了用sql语言为基本表创建索引和删除索引。实验六数据查询一. 实验目的1. 观察查询结果，体会SELECT语句实际应用；2. 要求学生能够在查询分析器中使用SELECT语句进行简单查询。3. 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二. 实验要求1. 完成简单查询和连接查询操作，并验收实验结果提交实验报告三. 实验内容所有的查询全部用 Transact-SQL语句实现。此部分查询包括投影、选择条件表达、数 据排序、使用临时表等。对EDUC数据库实现以下查询：1）查询计算机应用专业的学生学号和姓名；2）查询选修了课程的学生学号；3）查询选修课程0001且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.75输出；4）查询计算机应用和数学专业的姓“张”的学生的信息。5）查询“ 0001 ”课程的成绩高于张三的学生学号和成绩；6）查询没有选修“ 0002”课程的学生姓名；四. 实验步骤打开查询窗口，输入SQL查询语句，点击“执行”按钮，查看查询结果。1）查询计算机应用专业的学生学号和姓名；select sno,sn amefrom student,classwhere student.clsno=classclsno andclass.specialty=计算机应用;2）查询选修了课程的学生学号；select snofrom scwhere sno is not null;3） 查询选修课程0001且成绩在80- 90之间的学生学号和成绩，并将成绩乘以系数0.75 输出；select sno ,grade* 0.75from scwhere cno=0001andgrade between 80 and 90;4）查询计算机应用和数学专业的姓“张”的学生的信息。select *from student,classwhere sname like 张 _andstudent.clsno=class.clsno and（specialty=计算机应用orspecialty = 数学）;5）查询“ 0001 ”课程的成绩高于张三的学生学号和成绩；select stude nt.s no ,gradefrom student,scwhere cno=0001andstudent.sno=sc.sno andgrade in （select gradefrom student,scwhere sname=王军andstude nt.s no =scs no）6）查询没有选修“ 0002 ”课程的学生姓名；select distinct snamefrom student,scwhere student.sno=sc.sno and cno!=0002五、实验总结通过本次试验，复习用sql语言处理数据查询的一些方法，更好的掌握数据的查询。实验七使用聚集函数的SELECT语句一. 实验目的1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。2. 进一步掌握SQL Server查询分析器的使用，加深对SQL语言的嵌套查询语句的理解。二. 实验要求1. 在实验之前做好准备。2. 完成实验，并验收实验结果提交实验报告。三. 实验内容在数据库EDUC中用SQL语句实现如下查询：1）求学生的总人数。2）求选修了课程的学生人数。3）求课程的课程号和选修该课程的人数。4）求选修课程超过2门课的学生学号。四. 实验步骤1）求学生的总人数.select COUNT （*）from student;2）求选修了课程的学生人数。select COUNT （distinet sno）from sc;3）求课程的课程号和选修该课程的人数。select COUNT （cno）from scgroup by cno;4）求选修课程超过 2门课的学生学号。select s nofrom scgroup by snohaving COUNT （*）1;五、实验总结通过本次试验对聚集函数的SELECT语句使用更加熟悉。实验八 视图的定义与使用一. 实验目的1. 熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；2. 熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系；3. 学习灵活熟练的进行视图的操作，认识视图的作用。二实验要求1. 在实验开始之前做好准备工作。2. 实验之后提交实验报告，思考视图和基本表的区别三实验内容1. 定义视图在EDUC数据库中，已 Student、Course和SC表为基础完成一下视图定义：1）将Student. Course和SC表中学生的学号，姓名，课程号，课程名，成绩定 义为视图v_sc_g2）定义一个反映学生出生年份的视图V_YEAR3）将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_；4）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G2. 使用视图1）查询以上所建的视图结果。2）查询平均成绩为90分及以上的学生学号、姓名和成绩；3）查询课程成绩大于平均成绩的学生学号、课程号、成绩和平均成绩；4）查询1994年出生的学生学号和姓名。四. 实验步骤1. 定义视图1）将Student. Course和SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图v_sc_g.create view v_sc_gasselect stude nt.s no,sn ame,sc.c no,cn ame,gradefrom sc,student,coursewhere sc.sno=student.sno andsc.c no=course.c no;select * from v_sc_g;drop view v_sc_g;2）定义一个反映学生岀生年份的视图 V_YEAR.create view v_yearas selects no,sn ame,2013-sageagefrom student; select * from v_year;V_AVG_S_G.3)将各位学生选修课程的门数及平均成绩定义为视图create view v_avg_s_gasselectstudent.sno,COUNT (eno) cnum,avg(grade) avg from sc,studentwhere sc.sno=student.snogroup by student.sno;4)将各门课程的选修人数及平均成绩定义为视图create view v_avg_c_gasselect c no ,co un t(*)as cnum,avg(grade) avg_gradefrom scgroup by eno;select * from v_avg_c_g;2.使用视图1) 查询以上所建的视图结果。2) 查询平均成绩为 90分及以上的学生学号、ageV_AVG_C_G姓名和成绩;select stude nt.s no,sn ame,avg_agefrom v_avg_s_g s,studentwhere s.sno=student.sno ands.avg_age= 90;3).查询科目成绩大于平均成绩的学生学号、课程号、成绩和平均成绩;select stude nt.s no, sn ame,c no ,grade, avg_age from sc a,student,v_avg_s_gwhere a.sno=student.sno andStude nt.s no=v_avg_s_g .snoandnot exists (select *from scb ,v_avg_c_gwhere a.Grade v_avg_c_g .avg_grade and a.Cno =b.Cno)HAP-PC (SQL Server 10.50.1600 * 容库j库一i加澤快黑EDUCa U舞库关粟图日L表=Fj select student. ano, snarr.e * cnof gr且de, fse a,studentrv avg s a where a.3no=studenD.sna and SrudenT：. sno=v avq 3 g. sna andnor exists (select *rart ss b f v avg c gwhere a.Graded v_avg_c_g.avg_grad& and a.Cno=b.Ono_j3dbo.dss dbo.course dbo.sc dbo.student二结果j消息+：El lJI视圈+垂统祝图snosname eno grade avgage1_ ； 20090101 王军hnn r irBiirmiRiiiivi irnir#220090306 王彤0001900DD03 93.0BS.OODOOO30.0000004）查询1994年岀生的学生学号和姓名。selects names no from v_yearwhere age=1994：五、实验总结于种很特殊的表。视图的使用很简单，有时使查询变得更加简单，和表的使用很像。视图就是实验九触发器的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQL Server触发器的创建及使用方法，加深SQL触发器的理解。通过对数据的更新操作体会其触发器的作用。二、实验要求1. 在实验之前做好准备2. 试验之后提交实验报告，并验收实验结果三、实验内容1. 在班级表class中增加班级人数（c_total ）字段2. 创建 INSERT触发器 t_inst_stu3. 创建DELETE触发器t_dele_stu :在学生表（student）中删除一名学生，若其班级编号非空，则将班级表（class ）中相应班级的人数（c_total ）自动减1。4. 创建 UPDATE虫发器 t_update_stu 。5. 体会触发器的作用。四、实验步骤1. 在班级表class中增加班级人数（c_total ）字段，默认值是 0;并手动统计人数作为该字段的值；alter table class addc_total int NOT NULL DEFAULT 0-alter table class dropcolum nc_total -删除一个字段2. 创建INSERT触发器t_inst_stu :在学生表（student）中每新增一名学生，若其班级编号非空，则将班级表(class )中相应班级的人数(c_total )自动加1。create trigger dbo . t_inst_stuon dbo . studentfor in sertasbegindeclarecls char (6)selectcls =ClsNo from in sertedif exists( select * fromstudentwherestudent. ClsNo =cls and student . ClsNo is not null )print开始查找并将班级人数加1.update class set c_total =c_total +1 where ClsNo =clsprint 班级人数已增加！ ！End3. 创建DELETE触发器t_dele_stu :在学生表(student)中删除一名学生，若其班级编号非 空，则将班级表(class )中相应班级的人数(c_total )自动减1。create trigger dbo .t_dele_stuon dbo .studentfor deleteas beg indeclare cls char(6)select cls=clsno from deletedif exists(select * from studentwhere student.clsno= cls andstudent.clsno is not null )print 开始查找并将班级人数减.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_stuon studentfor updateas beg indeclare oldClsNO char(8),newClsNO char(8);select oldClsNO from deleted;select newClsNO from inserted;if oldClsNO is not nullupdate classset c_total=c_total +1where ClsNO =oldClsNO ;if newClsNO is not nullupdate classset c_total=c_total -1where CIsNO =newClsNO ;End5.触发器的作用：1）查看班级表（class ）2）对学生表（student）分别插入（INSERT、删除（DELETE和修改（UPDATE元组 insert into studentvalues（，古鑫,男,cs01,合工大,20,1.70）;delete from studentwhere sname=古鑫;3）再次查看班级表（class ），检查其数据的变化情况，体会触发器的作用。select * from class;五实验总结 对触发器的创建把握的不是太好，还是需要多加锻炼。实验十存储过程的创建与使用一、实验目的本实验的目的是使学生进一步掌握SQLServer存储过程的创建及使用方法，加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。二、实验要求1. 在实验之前做好准备2. 试验之后提交实验报告，并验收实验结果三、实验内容1、 创建一个不带参数的存储过程p_stu_ infol ，实现对满足要求的学生基本信息的查询。2、 创建一个带有参数的存储过程p_stu_ info2 ，实现对满足要求的学生基本信息的查询。3、 创建一个存储过程p_stu_ in fo3,根据输入的学号，查询某学生的基本信息。4、 创建一个存储过程p_stu_grade,根据输入的学号，返回其选课及其成绩。5、使用 SQL语句分别执行 p_stunfo1 、p_stunfo2 、p_stunfo3和 p_stu_grade，并将结果显示出来。四、实验步骤1. 建立不带参数的存储过程p_stu_info1(选择所有年龄21岁的男同学)。create procedure p_stu_infolas beg inselect distinet *from studentwhere sage20 andsex=男End执行语句：exec p_stunfo12. 建立带有参数的存储过程p_stu_info2 ，根据传递的参数只显示与指定学号的学生同龄的所有同学(如：，自己可以选别的学号，下同 )。如：create procedurep_stunfo2sno char ( 8)asbegindeclare age numeric ( 3, 0 )select age=sage from stude nt where sno =s noselect * from student where sage =ageendexec p_stunfo23. 创建一个存储过程p_stu_info3 ，根据传入的学号(如：)，查询某学生的基本信息。createprocedure p_stu_info3sno char(8)as beg inselect*from student where sno=sno end exec p_stu_i nfo34.创建一个存储过程p_stu_grade,根据输入的学生学号（如：），返回其选课及其成绩。createprocedure p_stu_gradesno char（10）asbeginselect * from v_sc_gwhere sno=snoEndexec p_stu_grade五、实验总结这次实验，了解了存储过程的意义，掌握了存储过程的调用及其参数的使用方法。但是对这一块掌握的还不是太好，以后会多加练习的。实验一一用户管理操作一、实验目的本实验的目的是使学生进一步掌握SQL Server用户角色及权限的使用方法，加深SQL用户管理的理解。SQLServer的特定数据库都有自己的用户和角色（用户组），该数据库只能由它的用户或角色访问，其他用户无权访问该数据库数据，数据库系统可以通过创建和管理特定的数据库的用户和角色来保证数据库不被非法用户访问。二、实验要求1. 实现对SQL Server的用户和角色管理2. 设置和管理数据操作权限三、实验内容1、创建登录帐号2、创建用户3、创建角色4、授权5、验证授权操作四、 实验步骤（贴图仅供参考）1. 创建登录帐号-添加登录cxp,设置密码为123,默认数据库为 EDUCuse EDUCgoexecsp_addlogin cxp,123,EDUC;2、创建用户use EDUCgoexecsp_addlogin cxp1,123 ,EDUC;create user p for log in cxpl with default_schema=dbo;此时用户CXP没有一些操作的权限，要用创建的登录账号进入进行验证，如下：use EDUCgoselect * from student;3. 创建角色用create语句进行创建use EDUCgocreate role 李超;4. 给角色授权：grant update(sno),selecton studentto李超；5. 删除角色：use EDUCgodrop role 李超;6. 验证授权操作用cxp登录账号登录后，进行验证；Select *from student;五实验总结通过本次试验，加强了对数据库用户的建立和用户权限的一些基本知识，也对创建角 色有了深刻的认识。