1、合肥师范学院 实验报告册 2014 / 2015 学年 第1 学期系 别 计算机学院实验课程 数据库原理专 业 班 级 姓 名 学 号 指导教师 李宜兵实验名称课时实验报告成绩实验（一）数据库基本操作2实验（二）SQL语句6实验（三）数据库完整性与安全性实验4实验（四）数据库编程4备注：实验一 数据库基本操作一、 实验目的1. 熟悉MS SQL SERVER运行界面，掌握服务器的基本操作。2. 掌握界面操作方法完成用户数据库建立、备份和还原。3. 建立两个实验用的数据库，使用企业管理器和查询分析器对数据库和表进行基本操作。二、 实验预习内容在认真阅读教材及实验指导书的基础上，上机前请预习以下内
2、容，并在空白处填写相应的步骤或命令。1. 熟悉SQL SERVER 2000 的运行环境，练习服务器基本操作：打开、停止、关闭。2. 使用SQL SERVER 2000 中的企业管理器完成以下任务。数据库名称：STC表：STU（sno char(9), sname varchar(50), ssex char(2) , sage int, sdept char(2) ）； COURSE（ cno char(3), cname varchar(50), cpno char(3), credit int ）； SC（sno char(9), cno char(3), grade int ）；说明：
3、以上为表结构，以sno char（9）为例，说明sno属性设置为字符类型，宽度为9，int指整型数据。 1） 建立数据库STC，分别建立以上三张表，并完成数据录入。（表结构及数据参见教材）2） 分析并建立各表的主码，用下划线在上面表结构中标出主码。create table stu(sno char(9) primary key,sname varchar(50),ssex char(2),sage int,sdept char(2) );select* from coursecreate table course( cno char(3) primary key,cname varchar(5
4、0),cpno char(3),credit int ,foreign key (cpno) references course(cno);create table sc(sno char(9),cno char(3),grade int,primary key(sno,cno),foreign key (sno) references stu(sno),foreign key (cno) references course(cno);insertinto stuvalues(95001,李勇,男,20,cs);Stu表：Course表：Sc表：3） 建立各表之间的关联，请简述操作步骤。建立关
5、联：右击SC表，设计表-在任意一个空白处右击-关系-表和列规范-选择主键表course对应的主码cno和外键表sc对应的cno以及主键表stu对应的主码sno和外键表sc对应的sno确定即可 4） 参考实验指导书的【第5章 数据库的备份和还原】，使用企业管理器对数据库STC进行备份，并尝试在个人电脑与机房电脑上进行还原，请简述备份、还原操作的步骤。 右击STC数据库-任务备份备份到(添加到你要备份到的地方)-确定。 右击STC数据库-任务还原-数据库-（从原数据库或源设备）-选择路径-确定。3. 在SQL SERVER 2000 的查询分析器中使用SQL语言完成以下任务。参考实验指导书数据库系
6、统实验指导教程【实验1.1 数据定义】，建立数据库SCHOOL，分别建立students、teachers、courses、choices四张表，表结构见实验指导书P236 【附录A】。1） 用SQL语言创建数据库、建表。建表时为各表建立关键字、设置外码，数据暂不录入，请写出对应的SQL命令。create table students(sid char(10) primary key,sname char(30) not null,email char(30),grade int);create table teachers(tid char (10) primary key,tname ch
7、ar(30) not null,email char(30),salary int,);create table courses(cid char(10) primary key,cname char(30) not null,hour int,);create table choices(no int primary key,sid char(10) not null,tid char(10) not null,cid char(10) not null,score int ,foreign key (sid) references students(sid),foreign key (ti
8、d) references teachers(tid),foreign key (cid) references courses(cid),);2） 为students表、courses建立按主键増序排列的索引，请写出相应的SQL命令。 create index s on students(sid asc) create index c on courses(cid asc)3） 删除course上的索引，请写出相应的SQL命令。 drop index c on courses4） 在SCHOOL数据库中的students表中增加一个“出生日期”字段，类型为日期时间型，请写出对应的SQL命令。
9、 Alter table students add “出生日期” datetime5） 删除students中的“出生日期”字段，请写出对应的SQL命令。Alter table students drop clumn 出生日期6） 删除SCHOOL数据库中的students表，请写出对应的SQL命令。 drop table students cascade4. 使用企业管理器创建数据库SCHOOL，实现内容与实验预习内容3完全相同。5. 使用企业管理器，将SCHOOL数据库分离出MS SQL SERVER，请简述步骤。点开数据库-找到school数据库-(右击)所有任务-分离数据库-确定。6.
10、 使用企业管理器，使用【数据库/所有任务/附加数据库】将SCHOOL数据库添加进SQL SERVER，并查看数据。7. 使用查询分析器，删除数据库STC，请写出对应的SQL命令。Drop database stc8. 结束本次实验三、 实验课后训练1. 使用企业管理器练习数据的导入导出。1） 将SCHOOL数据库students表中的数据导出到STC数据库对应的stu表中；2） 向STC数据库courses表导入SCHOOL数据库courses表中的数据；3） 自行练习实验指导书【实验5.3 SQL SERVER数据库的导入与导出】部分；2. 自行练习实验指导书【实验1.1数据定义】部分。4、
11、 实验报告1. 简述本次实验所用到的SQL命令及其功能。命令：creat database 功能：建立数据库命令： Creat table （ 列级完整性约束条件）， 功能：定义基本表命令：alter table add 完整性约束-增加新列和新的完整性约束条件 Drop -删除指定的完整性约束条件 alter column ；-修改原有的列定义，包括列名和数据类型。功能：修改基本表2. 在MS SQL SERVER中数据库的“分离|添加”与“备份|还原”是相同的功能吗？答：在MS SQL SERVER中数据库的“分离|添加”与“备份|还原”的功能是不相同的。分离之后的数据库在用户不更改路径的
12、情况下会分离在系统默认的路径上，且分离之后在数据库的下属栏中找不到分离的数据库，除非重新按指定路径添加。而备份的数据库还会显示在数据库的下属栏中。3. 实验总结（实验过程中出现的问题、解决方法、结果如何或其它） 本次试验过程中遇到了很多问题，因为是第一次使用这个软件，有好多东西都不知道在哪，比如，不知道如何建立关联，如何备份还原数据库等，经过询问同学才知道。对书上的知识不是很了解，不知道如何建立索引和删除索引，在看书之后才知道如何做。经过这次实验，以后做实验要好好预习。实验二SQL语句一、 实验目的1. 熟悉SQL的数据查询语言，能使用SQL进行单表查询、连接查询、嵌套查询、集合查询和统计查询
13、，能理解空值的处理；2. 熟悉数据库的数据更新操作，能使用SQL语句对数据库进行数据的插入、更新、删除操作；3. 熟悉SQL支持的有关视图的操作，能创建、查询及取消视图；4. 了解NULL在数据库中的特殊含义，掌握使用SQL进行与空值相关的操作；二、 实验预习内容在认真阅读教材及实验指导书【实验1.2 数据查询】、【实验1.3 数据更新】、【实验1.4 视图】和【实验1.6 空值和空集的处理】的基础上，上机前请预习以下内容，并在空白处填写相应的步骤或命令。1. 使用SCHOOL数据库，在SQL SERVER 2000 的查询分析器中使用SQL语言完成以下操作。请在空白处填写相应的SQL命令。1
14、） 查询年级为2001的所有学生的名称，按编号顺序排列；select sname from students where grade=2001 order by sid2） 查询所有课程名称中含有data的课程编号；select cid from courses where cname like %data%3） 统计所有老师的平均工资；select AVG(salary) 平均工资from teachers4） 查询至少选了3门课的学生编号；select sidfrom choicesgroup by sid having count(*)=35） 查询学号为80009026的学生的姓名、所
15、选课名及成绩；select sname,cname,scorefrom students,courses,choiceswhere students.sid=80009026 and choices.sid=students.sidand choices.cid =courses.cid6） 查询没有学生选的课程编号；select cid from courseswhere cid not in (select cid from choices )7） 查询既选了C+又选了Java课程的学生编号；select sidfrom choices,courseswhere choices.cid=c
16、ourses.cid and cname=c+and sid in(select sidfrom choices,courseswhere choices.cid=courses.cid and cname=java)8） 查询选了C+但没选Java课程的学生编号；select sidfrom choices,courseswhere choices.cid=courses.cid and cname=c+and sid not in(select sidfrom choices,courseswhere choices.cid=courses.cid and cname=java)9） 向S
17、TUDENTS表中插入“LiMing”的个人信息（编号：700045678，名字：LiMing，Email：LX，年级：1992）；insert into studentsvalues (700045678,liming,LX,1992)10） 将“LiMing”的年级改为2002；update studentsset grade=2002where sname=liming11） 删除所有选了Java课程的学生选课记录；delete from choices where cid=(select cid from courseswhere cname=java)12） 求出每门课的课程号、选课
18、人数，结果存入数据库表T1中。create table T1(cid char(10) primary key,renshu int)insertinto t1select cid,count(distinct sid)from choicesgroup by cid;13） 查询所有选课记录的成绩并换算为五分制（注意NULL的情况）；select distinct score,score/20 五分制from choices;14） 查询成绩小于0的选课记录，统计总数、平均分、最大值和最小值（注意查询结果中NULL的情况）；select sid, cidfrom choiceswhere s
19、core0select distinct count(*)总数,avg(score) 平均值,max(score)最大值,min(score)最小值from choices15） 按成绩顺序排序显示CHOICES表中所有记录。（使用ORDER BY排序，注意NULL的情况）；select *from choicesorder by score;16） 创建视图V1，显示学生姓名、所选课名称、任课教师名；create view v1 (sname,cname,tname)asselect sname,cname,tnamefrom teachers,choices ,students,cours
20、eswhere choices.cid=courses.cid and choices.sid=students.sid and choices.tid=teachers.tid17） 取消V1视图；drop view v12. 使用STC数据库，在SQL SERVER 2000 的查询分析器中使用SQL语言完成以下操作。请在空白处填写相应的SQL命令或其它内容。1） 创建视图V2，显示计算机（CS）系学生信息；（结合下面的b)小题，V2视图创建2次，分别使用/不使用WITH CHECK OPTION选项）（1）不加WITH CHECK OPTION（2）加WITH CHECK OPTIONc
21、reate view v2asselect *from stuwhere sdept=cscreate view v2asselect *from stuwhere sdept=csWITH CHECK OPTION2） 插入元组（99999，张三，20岁，男，PH）到V2中，运行结果如何？（观察WITH CHECK OPTION对结果的影响）；（1）不加WITH CHECK OPTION的结果（2）加WITH CHECK OPTION的结果insertinto v2values(99999,张三,男,20,PH)insertinto v2values(99999,张三,男,20,PH)3）
22、在V2基础上，查询所有计算机系女生信息;select * from V2where ssex=女3. 结束本次实验三、 实验课后训练1. 掌握SQL SERVER中流控制语句及其它常用编程语句；1） 使用查询分析器，编写代码，输出乘法口诀表。2. 自行练习实验指导书P24【实验1.2 数据查询】1.2.5自我实践部分；3. 自行练习实验指导书P30【实验1.3 数据更新】1.3.5自我实践部分；4. 自行练习实验指导书P36【实验1.4 视图】1.4.5自我实践部分；5. 自行练习实验指导书P49【实验1.6 空值和空集的处理】1.6.5自我实践部分；四、实验报告1. SQL SERVER中变
23、量声明的命令是什么？输出命令是什么？Declare variable_name /局部变量名称Datatype /数据类型2. SQL SERVER中实现分支和循环的语句分别是什么？分支：case when 条件1 then 结果1 when 条件2 then 结果2 else 其他结果 end循环： while (条件-只能是表达式) 语句或语句块 break-强制退出3. 在SQL SERVER里使用ORDER BY排序时，NULL值如何处理？使用GROUP BY分组时，NULL值又如何处理的？在使用ORDER BY 进行排序时，有ASC（升序）和DESC（降序）两种方式。无论采用哪种方式
24、，NULL值总被当作最小值处理。即在按升序排列时，取空值的元组将最先显示，按降序排时，取空值的元组将会最后显示。 使用GROUP BY 进行排序，取空值的项不是被忽略，而是将NULL看成一个取值，在处理的时候把所有取空值的项都看成一样，因而形成一个分组。4. 实验总结（实验过程中出现的问题、解决方法、结果如何或其它）有好多次运行都是有检查完成但是执行错误，经检查才知道是上面的数据库默认是master，而不是要执行的数据库。由于粗心导致的字母拼写错误，导致执行结果出错。不会使用嵌套语句，经过分析书上的程序段，略微懂了一点。向表中插入记录，应该用insert into 表名 values（列属性）
25、.创建视图用create view (视图名) as from 表名 。经过试验验证，明白with check option 可以自动筛选不符合条件的记录。实验三数据库完整性与安全性一、 实验目的1. 理解实体完整性、参照完整性、用户自定义完整性的概念，学习三类完整性的实现，了解违反完整性的结果；2. 掌握MS SQL SERVER中的有关用户登录认证及管理方法，熟练使用自主存取控制进行权限管理；二、 实验预习内容在认真阅读教材及实验指导书【2.1 实体完整性】、【2.2 参照完整性】、【2.3 用户自定义完整性】、【2.4 触发器】、【2.5 综合案例】和【3.1 用户标识与鉴别】、【3.2
26、 自主存取控制】、【3.3 视图机制在自主存取控制上的应用】、【3.4 Public角色在安全性中的应用】、【3.8 综合案例】的基础上，上机前请预习以下内容，并在空白处填写相应的步骤或命令。1. 使用SCHOOL数据库1） 创建一张新表CLASS，包括Class_id（varchar（4），Name（varchar（10），Department（varchar（20）三个属性列，其中Class_id为主码； create table class(Class_id varchar(4) primary key,Name varchar(10),Department varchar(20)2）
27、执行两次相同的插入操作（0001，01csc,cs），结果如何；insert into CLASSvalues(0001,01CSC,CS)第一次插入：第二次插入：2. 使用SQL命令创建一张学生互助表，要求：包括学生编号、学生姓名、学生帮助对象的编号，每个学生有且仅有一个帮助对象，帮助的对象必须是已存在的学生。create table 学生互助表(sid varchar(10) primary key, sname varchar(30), s_id varchar(10) unique, foreign key (s_id) references 学生互助表(sid);3. 使用STC数据
28、库，修改设置，完成以下2种不同的要求，请写出对应的语句或简述操作过程：1） 当更新、删除STU表中记录时，若SC表中有此学生的记录，则拒绝；Alter table scAdd constraint c1 foreign key(sno) references stu(sno)On update no actionOn delete no action2） 当更新、删除STU表中记录时，若SC表中有此学生的记录，则自动更新或删除；Alter table scAdd constraint c2 foreign key(sno) references stu(sno)On update cascad
29、eOn delete cascade4. 使用SQL命令完成以下任务：1） 创建Worker表（表结构见指导书P73）create table worker(number char(5),name char(8) constraint U1 unique,sex char (1),sage int constraint U2 check (sage=28),deapartment char (20),constraint PK_worker primary key(number)2） 定义约束U1、U2，其中U1规定Name字段取值唯一，U2规定sage字段上限是28；3） 插入一条合法记录；
30、insertinto workervalues(123,小明,m,20,cs)4） 插入一条违反U2约束的记录，简述观察到的结果如何？并分析原因；insertinto workervalues(123,小明,m,29,cs)原因：sage的值必须=0);7） 创建规则rule_sex，规定更新或插入的值只能是M或F，并绑定到Worker的sex字段；Go Create Rule rule_sex as value in(F,M)Goexec sp_bindrule rule_sex,Worker.sex;8） 插入2条记录，一条满足规则rule_sex，一条违反规则，观察结果。Insert i
31、nto Workervalues (00005,小李,m,15,科技部)Insert into Workervalues (00005,小李,a,15,科技部)5. 使用查询分析器创建触发器并测试，请写出相应的语句：1） 为Worker表创建触发器T1，当插入或更新表中数据时，保证所操作记录的sage大于0；create trigger t1on workerfor insert,updateasif(select sage from inserted)1rollback transaction2） 为Worker表创建触发器T2，禁止删除编号为00001的记录；create trigger
32、t2on workerfor deleteasif(select number from deleted)= 00001rollback transaction3） 为Worker表创建触发器T3，要求更新一个记录时，表中记录的sage要比老记录的sage的值大。create trigger T3on worker for updateas if(select sage from inserted)安全性2） 建立名为“LiYong”的登录用户、数据库用户，默认访问SCHOOL数据库，密码为“abcd”；exec sp_addlogin liyong,abcd,schoolexec sp_gr
33、antdbaccess liyong3） 取消“LiYong”用户；exec sp_revokedbaccess liyongexec sp_droplogin liyong7. 先以sa帐号登录，要实现如下表的权限管理，请按先后顺序写出相应的SQL语句（假设所有用户尚未定义，其密码均为123），并加以必要的说明。编号授权用户名被授权用户名数据库对象名允许的操作类型能否转授权1SAU1StudentsSELECT不能2SAU2StudentsALL不能3SAPUBLICChoicesSELECT不能4SAU3StudentsSELECT能5U3U4StudentsSELECT能6U4U5Stu
34、dentsSELECT不能7SAU6ChoicesINSERT能8SAU7Students.snameUPDATE不能exec sp_addlogin u1,123,schoolexec sp_grantdbaccess u1把select 权限授给U1Grant selectOn studentsTo u1;把所有权限授给U2Grant all privilegesOn studentsTo U2把choices表的select 权限授给publicGrant selectOn choicesTo public把select 权限授给U3Grant selectOn studentsTo u
35、3With grant option;把select 权限授给U4,以u3登陆Grant selectOn studentsTo u4With grant option;把select 权限授给U5,以u4登陆Grant selectOn studentsTo u5With grant option;把choices表的insert 权限授给u6Grant insertOn choicesTo u6With grant option;把Students.sname的权限授给u7Grant update(sname),selectOn studentsTo u7验证：update student
36、sset sname=asdwhere sid=7000456788. 创建在SCHOOL数据库中choices上的视图CS_View，授权给计算机系讲授课程号为10010的数据库用户“LiYong”，让其具有视图上的SELECT权限；create view cs_viewasselect * from choiceswhere cid=10010grant select on cs_viewto liyong9. 对视图CS_View上的score属性列的UPDATE权限授予用户“LiYong”，可以修改学生成绩，但不能修改其它属性列的值；grant update on dbo.cs_vie
37、w(score)to liyongselect * from cs_viewupdate cs_view set score=90where no=50002494010. 假设SCHOOL数据库中，每个学生的登录名都为自己在STUDENTS表中的sid，实现每个学生只能查询自己选课信息，不能查询别人的成绩，也不能修改自己的成绩。create view v1asselect * from choiceswhere sid=current_userwith check optiongrant select on v1 to public11. 结束本次实验三、 实验课后训练1. 自行练习实验指导
38、书P81【实验2.5 综合案例】综合案例1；2. 自行练习实验指导书P122【实验3.8 综合案例】综合案例；四、实验报告1. 授权给public与授权给指定用户有什么区别？实际应用中，哪个更安全些？ 授权给public是授给本地数据库中的所有用户，实际应用中授权给指定用户更安全些。 用户名public指系统的所有当前用户和将来的用户，因此，对pubilc的授权隐含着对所有当前用户和将来用户的授权。在默认情况下，被授权的用户无权把此权限授予其他用户，但SQL允许使用授予权限来指定权限的接受者可以进一步把权限授予其他用户，如果被收回权限的用户已经把权限授予了其他用户，权限的收回会更加复杂 数据库
39、中的每个用户都属于 public 数据库角色。如果想让数据库中的每个用户都能有某个特定的权限，则将该权限指派给 public 角色。如果没有给用户专门授予对某个对象的权限，他们就使用指派给 public 角色的权限2. SQL SERVER中的角色有什么作用？ 数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。使用角色来管理数据库权限可以简化授权的过程。SQL Server 提供了两种类型的数据库角色：固定的数据库角色和用户自定义的数据库角色。服务器角色是指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQl Server管理职能的用户划分为不同的用户
40、组，每一组所具有的管理SQL Server 的权限都是Sql Server内置的，即不能对其进行添加、修改和删除，只能向其中加入用户或者其他角色。数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。3. 实验总结（实验过程中出现的问题、解决方法、结果如何或其它） 在本次实验中，遇到了不少问题，在本次试验中主要讲述了数据库的安全性与完整性，数据库的安全性和完整性对数据库至关重要！其中数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。数据库的完整性是指数据的正确性和
41、相容性。数据库的安全性主要通过数据库的安全控制实现，安全控制的方法和手段有很多，最重要的是存取控制技术、视图技术和审计技术。实验四数据库编程一、 实验目的1. 掌握ODBC的配置；2. 能编写简单的存储过程和函数，并调用；二、 实验预习内容上机前请预习以下内容，并在空白处填写相应的步骤或命令。1. 配置ODBC，使用VFP中的表单，查询SCHOOL数据库中STUDENTS表中所有记录。2. 编写存储过程并调用，请写出相应命令；1） 在查询分析器中，编写存储过程usp_get_stuinfo ，使用一个名为xm能够传送进存储过程的参数。允许以学生的姓名查询该生的基本信息；create proce
42、dure usp_get_stuinfo xm char(30)asselect * from studentswhere sname = xm2） 在查询分析器中调用存储过程查询“赵飞”同学的基本信息的语句；Exec usp_get_stuinfo 赵飞；exec usp_get_stuinfo xm = 赵飞；3. 编写函数并调用，请写出相应命令；1） 在查询分析器中，定义内联表值函数Fun，用于返回各职称的教师基本信息；第一步： alter table teachers add iden char(30)第二步： update teachers set iden=教授 where tid=200003125create function teacherinformation(id char(30)returns table as return( select * from teachers where iden=id )2） 在查询分析器中，使用Fun进行选择以获得“教授”职称的教师基本信息；select * from teacherinformation(教授)3） 在查询分析器中，编写标量函数Func，用于返回两个整数中的
