《数据库系统及应用实验与课程设计指导实验报告.doc》由会员分享，可在线阅读，更多相关《数据库系统及应用实验与课程设计指导实验报告.doc（62页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、实验一 SQL Server 2008 基本服务和信息一 实验目的 要求学生查看SQL Server 2008联机丛书的内容，了解SQL 的环境及基本操作，了解SQL的基本信息。二 实验内容查看书内容，环境并掌握其基本操作，服务器管理和注册查看SQL的目录结构，利用SQL的基本系统视图查看相关信息。三 实验指导select * from sys.serverswhere server_id=0select * from sys.databasesselect * from sys.filegroupsselect * from sys.master_filesselect * from sys
2、.database_filesselect * from sys.data_spacescreate table test( id int not null, name char(100) null, constraint pk_test primary key clustered ( id asc)create nonclustered index ix_test on test(name)select *from sys.objectswhere type_desc=user_table and name=testselect * from sys.objectswhere type_de
3、sc=user_table and name=testor parent_object_id in( select object_id from sys.objectswhere type_desc=user_table and name=test)select * from sys.columnswhere object_id =12select * from sys.indexeswhere object_id=select * from sys.partitionswhere object_id=select * from sys.allocation_unitsselect * fro
4、m sys.allocation_units u,sys.partitions pwhere u.type in (1,3) and u.container_id=p.hobt_id and p.object_id=33union allselect * from sys.allocation_units u,sys.partitions pwhere u.type = 2 and u.container_id=p.partition_id and p.object_id=33select * from sys.dm_db_partition_statswhere object_id=33se
5、lect * from sys.index_columnswhere object_id=11select * from sys.database_principalsselect * from sys.types实验二 数据库的创建和管理一 实验目的 要求学生熟练的使用SQL创建和管理数据库，并学会使用SQL查询分析器接受和运行结果分析。二 实验内容创建数据库,查看和修改数据库的属性,修改数据库名称,删除数据库.三 实验指导-数据库的创建create database educ on primary( name= student_data, filename =C:Program Files
6、Microsoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAstudent_data.mdf, size=10MB, maxsize=50MB, filegrowth=1MB)log on( name =student_log, filename=C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAstudent_log.ldf, size=2MB, maxsize=5MB, filegrowth =1%)use educ go select database_id,is_read_o
7、nly,collation_name,compatibility_levelfrom sys.databases where name = educ gouse educ go select databasepropertyex(educ,isautoshrink) gouse jxgl go select object_name(id) as 数据表名from sysobjectswhere xtype = uand objectproperty(id ,ismsshipped)=0gouse educgoexec sp_spaceusedgouse jxglgoalter database
8、 jxgladd file( name =xs_data, filename=C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAjxglsysxs_data.ndf, size =3MB, maxsize =10MB)gouse jxglgo alter database jxgladd log file( name =xs_log, filename=,filegrowth=10%)gouse educgoalter database educremove file xs_loggogoalter database
9、educmodify name=xjglgogoexec sp_renamedb xjgl,educgo实验三 数据表的创建和管理/Dept_info数据表的创建use educgocreate table Dept_info( dno char(4) primary key, dname char(16) not null, d_chair char(8), d_address varchar(50), tel char(12)GO/class_info数据表的创建use educgocreate table class_info( classno char(4) primary key,
10、dname char(16) not null, monitor char(8), instructor char(8), tel char(12), dno char(4), foreign key(dno) references Dept_info(dno)GO/student_info数据表的创建use educgocreate table Student_info( sno char(8) primary key, sname char(8) not null, sex char(2), s_native varchar(50), birthday smalldatetime, cla
11、ssno char(4), entime smalldatetime, home varchar(50), tel char(12), dno char(4), foreign key(dno) references Dept_info(dno), foreign key(classno) references class_info(classno),)GOuse educgoinsert into class_info(classno,dname,monitor,instructor,tel)values(S13,王小明,王大明,大的,3)gouse educgoinsert into sc
12、(sno,cno,garde) values(s13,c12,88)gouse educgoinsert int sc(sno,cno) values(ss3,d9)gocreate table s( sno char(9) primary key, sname char(8), sex char(2), age smallint, sdept varchar(50)实验四 数据库约束实验一 实验目的1. 理解数据库完整性约束的概念2. 掌握SQL的完整性约束实验技术3. 了解SQL的违反完整性约束的处理措施二 实验内容1. 理解域完整性,实体完整性,参照完整性和用户定义完整性的意义2. 定义
13、和管理主键约束,外键约束,唯一性约束.3. 定义和管理检查约束,默认值约束,允许空值约束.create table s( sno char(9) primary key, sname char(8), sex char(2), age smallint, sdept varchar(50) -X*Y use educgoselect identity(int ,1,1) as #id,*into #tmp from s-SELECT IDENTITY(int,1,1) as PID,empID*1 as empID,empName INTO #T FROM sselect * from #tm
14、pgo -X*Y use educgoalter table swith nocheck add constraint ck_s_deptcheck(sdept in(cs,ma,is),constraint ck_s_age check(age=15 and age=15 and age=15)or(sdept=maand sdept=28;-查询选修了课程或者课程的学生select snofrom scwhere cno=1union select snofrom scwhere cno=2;-查询选修了课程或者课程的学生 X*Y7select *from scwhere c=01unio
15、n select *from scwhere c=02;-查询计算机系的学生与年龄不大于岁的学生的交集select *from studentwhere ssex=男intersect -except 此时是求的差集 X*Yselect *from studentwhere sage=19;实验六 游标操作一 实验目的 使学生加深对游标概念的理解，掌握游标的定义，使用方法，以及使用游标查询，修改和删除数据的方法。二 实验内容1. 利用游标逐行显示所查询的数据块的内容2. 利用游标显示指定行的数据内容3. 利用游标修改和删除指定的数据元组三 实验指导-实验 游标操作 -6.1 逐条显示use e
16、ducgodeclare cs_cursor scroll cursorforselect s,sname,sage,ssexfrom student-for read onlyopen cs_cursorfetch from cs_cursorgo-显示指定行数据use educgodeclare cs_cursor scroll cursorforselect s,sname ,sage,ssexfrom studentwhere s=02open cs_cursorfetch first from cs_cursorgofetch last from cs_cursorfetch pri
17、or from cs_cursor-显示从游标开始的第二条记录fetch absolute 2 from cs_cursor-关闭游标close cs_cursordeallocate cs_cursor-用游标修改数据 X*Yuse educgodeclare cs_cursor scroll cursorforselect sname,ssexfrom studentwhere ssex=男for update of snameopen cs_cursorfetch absolute 2 from cs_cursorupdate student set sname=玩玩吧where cur
18、rent of cs_cursorfetch absolute 2 from cs_cursorgo-用游标删除数据 X*Yuse educgodeclare cs_cursor scroll cursorforselect sname,ssex from studentopen cs_cursorfetch absolute 2 from cs_cursordelete from student where current of cs_cursorgo-用游标遍历数据use educgodeclare cs_cursor scroll cursorforselect student.s,sn
19、ame,sage from student,sc where student.s=sc.sopen cs_cursordeclare s char(9)declare sname char(8)declare sage intfetch next from cs_cursor into s,sname,sagewhile fetch_status=0begin print s+str(sage) fetch next from cs_cursor into s,sname,sageendclose cs_cursordeallocate cs_cursorgo-用游标备份数据库use educ
20、godeclare name varchar(50) -数据库名declare path varchar(256) -文件备份路径declare fileName varchar(256) -备份文件名declare fileDate varchar(20) -用户数据文件set path=C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAD:educselect fileDate=convert(varchar(20),getdate(),112)declare db_cursor cursor forselect
21、namefrom educ.dbo.sysdatebaseswhere name not in(master,model,msdb,tempdb)open db_cursor into namewhile fetch_status=0begin set filename=path+name+_+fileDate+.bak backup datebase name to disk =fileName fetch next from cs_cursor into nameendclose cs_cursordeallocate cs_cursorgo实验七 存储过程的创建与应用一 实验目的 使学生
22、理解存储过程的概念,掌握存储过程的创建和执行,并掌握存储过程的查看,修改和删除.二 实验内容a) 创建存储过程.b) 修改存储过程.c) 调用存储过程d) 删除存储过程.三 实验指导-实验七存储过程的创建与应用-7.1创建存储过程use educgocreate procedure s_ageasselect student.s,sname,ssex,sagefrom student join sc on student.s=sc.s join course on sc.s=course.cgo-调用存储过程use educgoexec s_agego-7.2 创建存储过程，要求输入名字，能从
23、SC表中查到该学生的平均成绩use educgocreate procedure proc_exp s_name char(20)asselect avg(score) as 平均成绩from student join sc on student.s=sc.s and sname=s_namego-调用存储过程,求“钱电”平均成绩use educgoexec proc_exp 钱电go-四，存储过程的删除drop procedure proc_add-7.7use educgodrop procedure proc_expdrop procedure proc_addgo-7.3- creat
24、e a table name is s_info, into the name,output the courses and avguse educgocreate procedure s_info s_name char(8)asdeclare s_count intdeclare s_avg realselect s_count=count(c),s_avg=avg(score)from student join sc on student.s=sc.s and sname=s_nameprint s_name +the student all have+str(s_count) +cou
25、rses.And the average is :+str(s_avg)go-7.6存储过程的调用exec procedure_name-1添加信息 X*Yuse educ gocreate procedure proc_add(s varchar(10),c varchar(10),score int) asinsert into scvalues(s,c,score)go-调用proc_adduse educgoexec proc_add02,01,84exec proc_add02,02,78go-调用存储过程s-infouse educgoexec s_info 钱电go-7.4-查看
26、数据表的索引信息-创建use educ gocreate proc table_info table varchar(30)asselect table_name = sysobjects.name,index_name = sysindexes.name,index_id =indidfrom sysindexes inner join sysobjects on sysobjects.id =sysindexes.idwhere sysobjects.name=tablego-调用use educgoexec table_info sgo-7.5存储过程的修改use educgoalter
27、 procedure proc_exp s_name char(20)asdeclare savg intselect savg=avg(score)from student join sc on student.s=sc.s and student.sname=s_nameif savg60 print this student score is qualified,the score is:+convert(char(2),savg)+scores else print this student score is not qualified,the score is:+convert(ch
28、ar(2),savg)+scoresgo-调用use educgoexec proc_exp 钱电go-四，存储过程的删除drop procedure proc_add-7.7use educgodrop procedure proc_expdrop procedure proc_addgo实验八 使用触发器实现数据完整性一 实验目的 使学生理解触发器实现数据完整性的重要性,掌握触发器实现数据完整性的方法,掌握用触发器实现参照完整性的方法,并理解触发器与约束的不同.二 实验内容1. 为表建立触发器,实现域完整性,并激活触发器进行验证.2. 为表建立级联更新的触发器,实现参照完整性,并激活触发器
29、进行验证.3. 比较约束与触发器的执行顺序.三 实验指导use educgodelete stu where s=4-查询数据select * from stuselect * from s_backupgo-返回表的索引和分区信息 X*Yuse mastergodeclare db_id intdeclare object_id intset db_id=DB_ID(AdventureWorks2008.Person.Address)if db_id is nullprint NInvalid databaseelse if object_id is null print NInvalid
30、object else select * from sys.dm_db_index_operational_stats(db_id,object_id,null,null)go-实验触发器-8.1 插入记录 X*Yuse educgocreate trigger tri_insert_student on stufor insertasdeclare sage tinyintselect sage=stu.sage from stuif sage not between 15 and 30 rollback transactiongo-8.2删除触发器drop trigger tri_inse
31、rt_studrop trigger tgr_s_deleteuse educgo -delete删除类型触发器 X*Ycreate trigger tgr_s_deleteon stu for delete -删除触发asprint备份数据中if(object_id(s_backup,U) is not null) -存在S_back，直接插入数据 insert into s_backup select s,sname from deleted else -不存在stu_backup,创建后再插入select * into s_backup from deletedprint 备份数据成功！
32、go-8.5.4 查看触发器的触发事件 X*Yuse educgoselect te.*from sys.trigger_events te join sys.triggers ton t.object_id =te.object_idwhere t.parent_class=0 and t.name=tgr_valid_datagouse educ -X*Ygoinsert into stuvalues(15,王晓霞,19,女)goinsert into stu -X*Yvalues(16,邵庆国,13,男)go-8.2删除触发器drop trigger tri_insert_studrop
33、 trigger tgr_s_deleteuse educgo -delete删除类型触发器 X*Ycreate trigger tgr_s_deleteon stu for delete -删除触发asprint备份数据中if(object_id(s_backup,U) is not null) -存在S_back，直接插入数据 insert into s_backup select s,sname from deleted else -不存在stu_backup,创建后再插入select * into s_backup from deletedprint 备份数据成功！go-验证是否存在，
34、有则删除，无则不执行X*Yuse mastergoif object_id(dbo.AWBuildVersion,U) is not nulldrop table dbo.AWBuildVersiongo-返回表的索引和分区信息 X*Yuse mastergodeclare db_id intdeclare object_id intset db_id=DB_ID(AdventureWorks2008.Person.Address)if db_id is nullprint NInvalid databaseelse if object_id is null print NInvalid ob
35、ject else select * from sys.dm_db_index_operational_stats(db_id,object_id,null,null)go-8.3删除记录 X*Yuse educgocreate trigger delete_sc_s on stuinstead of deleteasdeclare s_no char(9)select s_no=s from deleteddelete from scwhere s=s_nodelete from stuwhere s=s_nogo-8.4 UPDATE触发器 X*Yuse educgocreate trig
36、ger tgr_s_updateon stufor updateasdeclare oldName char(8),newName char(8)-更新前的数据 X*Y select oldName=sname from deletedif(exists(select * from stu where sname like%+oldName+%) begin -更新后的数据 select newName=sname from inserted update stu set sname=replace(sname,oldName,newName) where sname like %+oldNa
37、me+% print级联修改数据成功！ endelse print无须修改STU表!go-先查询再修改 X*Yuse educgoselect * from stu order by sselect * from stuupdate stu set sname=乌兰where sname=吴兰go-8.5 触发器的其他操作-8.5.1 消息函数raiserror的应用drop trigger tgr_messageuse educgocreate trigger tgr_messageon stuafter insert,updateas raiserror(tgr_message 触发器被触
38、发,16,10)go-验证X*Yuse educgoupdate stu set ssex=女 where sname = 钱电select * from stu order by s-8.5.3 查询创建触发器的信息 X*Yselect * from sys.triggersselect * from sys.objects where type =TR实验九 视图和索引及数据库关系图一 实验目的 使学生掌握SQL中视图的创建,查看,修改和删除方法,掌握索引的创建和删除方法以及数据库关系图的实现方法,加深对视图和SQL数据库关系图作用的理解.二 实验内容1. 创建,查看,修改和删除视图2. 创建,删除索引文件3. 创建数据库关系图.三 实验指导本部分利用数据库EDUC的表Student,sc ,course