一个完整的数据库示例说明.docx
- 文档编号:4921156
- 上传时间:2022-12-11
- 格式:DOCX
- 页数:15
- 大小:294.40KB
一个完整的数据库示例说明.docx
《一个完整的数据库示例说明.docx》由会员分享,可在线阅读,更多相关《一个完整的数据库示例说明.docx(15页珍藏版)》请在冰豆网上搜索。
一个完整的数据库示例说明
一、表的结构及完整性约束
新建一个数据库jxsk,包括S、C、SC、T、TC五个表,结构如下:
C表:
S表:
SC表:
T表:
TC表:
二、安全性控制及视图机制
1、三类角色:
depart、teacher、student
depart的权限:
teacher的权限:
student的权限:
2、有2个院系用户:
d_jsj,d_xx,同属于depart角色。
有1个教师用户:
t,属于teacher角色。
有一个学生用户:
s,属于student角色。
3、创建计算机系教师视图t_view_jsj、计算机系学生视图s_view_jsj,并授予d_jsj用户在这两个视图上的select、delete、update、insert权限。
计算机系教师视图t_view_jsj:
createviewt_view_jsj
as
selecttno,tn,sex,age,prof,sal,comm,dept
fromt
wheredept='计算机'
withcheckoption
授予d_jsj用户在计算机系教师视图t_view_jsj上的select、delete、update、insert权限:
grantselect,update,delete,insertont_view_jsjtod_jsj
计算机系学生视图t_view_jsj:
createviews_view_jsj
as
selectsno,sn,sex,age,dept,resume,native
froms
wheredept='计算机'
withcheckoption
授予d_jsj用户在计算机系学生视图s_view_jsj上的select、delete、update、insert权限:
grantselect,update,delete,insertons_view_jsjtod_jsj
……
4、创建一个视图,显示学号,姓名,院系,课程名,成绩。
createviewscore_view(学号,姓名,院系,课程名,成绩)
as
selects.sno,sn,dept,cn,score
froms,sc,c
wheres.sno=sc.snoando=o
三、完整性控制--触发器、规则
1、要求当删除C表中某课程信息时,同时删除SC和TC中与此课程相关的记录。
createtriggerc_delete_triggeronc
afterdelete
as
deletefromsc
wherecnoin
(selectcnofromdeleted)
deletefromtc
wherecnoin
(selectcnofromdeleted)
go
2、为T创建一触发器,当职称从“讲师”晋升为“副教授”时,岗位津贴自动增加500元,从“副教授”晋升为“教授”时,岗位津贴自动增加900元。
createtriggert_update_triggeront
afterupdate
as
ifupdate(prof)
begin
declare@prof_oldchar(10),@prof_newchar(10)
select@prof_old=proffromdeleted
select@prof_new=proffrominserted
if@prof_old='讲师'and@prof_new='副教授'
updatetsetcomm=comm+500wheretno=(selecttnofrominserted)
if@prof_old='副教授'and@prof_new='教授'
updatetsetcomm=comm+900wheretno=(selecttnofrominserted)
end
3、创建一个规则sexrule,指定变量@sex的取值只能为'男'或'女'
createrulesexrule
as@sexin('男','女')
绑定T表的sex、S表的sex到sexrule规则:
execsp_bindrule'sexrule','s.sex'
execsp_bindrule'sexrule','t.sex'
四、索引
1、索引的分类:
●聚集索引:
primarykey自动创建聚集索引
●非聚集索引
2、使用索引的准则:
1)适合建索引的属性列
●主码所在的属性列
●外码所在的列或在连接查询中经常使用的属性列
●按关键字的范围值进行搜索的属性列
●按关键字的排序顺序访问的属性列
2)不适合建索引的属性列
●在查询中很少涉及的属性列
●包含较少的唯一值
●更新性能比查询性能更重要的属性列
●有text、ntext、image数据类型定义的属性列
3、为s表在dept属性列上创建索引
createindexs_dept_indexons(dept)
……
五、自定义数据类型、自定义函数
1、自定义数据类型Idnum:
学号、教师编号都是char(6),notnull。
execsp_addtypeIdnum,'char(6)','notnull'
2、自定一个标量函数,用于查询某个同学某门课程的成绩。
createfunctionscore_fun(@snamechar(8),@cnamechar(10))
returnstinyint
as
begin
declare@cjtinyint
select@cj=score
froms,sc,c
wheres.sno=sc.snoando=oand
sn=@snameandcn=@cname
return@cj
end
使用该函数,查询'李忘'选修'计算机基础'的成绩。
selectdbo.score_fun('李忘','计算机基础')
3、创建一个单语句表值函数。
createfunctioncname_score(@cnamechar(10))
returnstable
as
return
(selectsn,score
froms,sc,c
wheres.sno=sc.snoando=oandcn=@cname)
go
使用该函数,查询选修了“计算机基础”的学生姓名、成绩。
selectsn姓名,score成绩
fromcname_score('计算机基础')
4、创建一个多语句表值函数,根据教师姓名查询该教师所讲授课程名、学生人数、平均成绩、最高成绩、最低成绩。
createfunctiontname_fun(@tnamechar(10))
returns@tname_score_tabtable(cnchar(10),rsint,pjfnumeric(6,1),
zgfnumeric(6,1),zdfnumeric(6,1))
as
begin
insertinto@tname_score_tab
selectcn,count(*),avg(score),max(score),min(score)
fromsc,tc,t,c
whereo=oandt.tno=tc.tnoando=oandtn=@tname
groupbycn
return
end
使用该函数,查询‘徐红霞’教师所授课信息。
select*fromtname_fun('徐红霞')
六、存储过程和游标
1、利用课程名查询选修该课程的学生姓名、系别、成绩。
createprocedurecn_score_pro
@cnamechar(10)
as
selectsn姓名,dept系别,score成绩
froms,sc,c
wheres.sno=sc.snoando=oandcn=@cname
go
使用该存储过程,查询选修“计算机基础”的相关信息。
execcn_score_pro'计算机基础'
2、统计某门课程成绩为60以下,60~80(含60、80分),80以上各有多少人。
createprocedurecn_tjscore_pro
@cnamechar(10),@n1intoutput,@n2intoutput,@n3intoutput
as
declare@cjtinyint
select@n1=0,@n2=0,@n3=0
declareccursorfor
selectscore
fromsc,c
whereo=oandcn=@cname
openc
fetchnextfromcinto@cj
while@@fetch_status=0
begin
if@cj<60
set@n1=@n1+1
else
if@cj<=80
set@n2=@n2+1
else
set@n3=@n3+1
fetchnextfromcinto@cj
end
closec
deallocatec
go
使用该存储过程,查看“计算机基础”的相关成绩统计信息。
declare@n1tinyint,@n2tinyint,@n3tinyint
execcn_tjscore_pro'计算机基础',@n1output,@n2output,@n3output
select@n1'0--60人数',@n2'60--80人数',@n3'80--100人数'
3、利用课程名查询选修该课程的学生姓名、系别、成绩。
(使用输出参数)
创建存储过程
createproceduretn_scoretj_cursor_pro
@cnamechar(10),@ccursorvaryingoutput
as
set@c=cursorfor
selectsn,dept,score
fromsc,s,c
whereo=oandsc.sno=s.snoandcn=@cname
open@c
go
使用该存储过程,查看“计算机基础”的相关信息
declare@myccursor,@snchar(10),@deptchar(10),@scoretinyint
--调用过程tn_scoretj_cursor_pro
exectn_scoretj_cursor_pro'计算机基础',@mycoutput
--打印表头
print'姓名系别成绩'
fetchnextfrom@mycinto@sn,@dept,@score
while@@fetch_status=0
begin
print@sn++@dept+''+cast(@scoreaschar(5))
fetchnextfrom@mycinto@sn,@dept,@score
end
close@myc
deallocate@myc
七、事务设计
1、创建一个事务程序,要求:
学生“王蕾”打算选修“图像处理”课程,根据规定,此门课程选修的人数最多为30人,该生是否可以选修此门课程,给出结果提示。
begintransaction
declare@numtinyint,@cnochar(5),@snochar(6)
select@cno=cnofromcwherecn='图像处理'
select@sno=snofromswheresn='王蕾'
select@num=count(*)fromscwherecno=@cno
if@num<30
begin
insertintosc(sno,cno)values(@sno,@cno)
commit
print'王蕾同学选修图像处理课程注册成功!
'
end
else
begin
rollbacktransaction
print'选修图像处理课程的人数已满,王蕾同学不能再选修此课程'
end
go
2、将教师“王平”讲授的“计算机网络”转给“徐红霞”,将“微机原理”转给“田丰”讲授。
begintransaction
declare@cnochar(5),@tno1char(6),@tno2char(6)
select@tno1=tnofromtwheretn='王平'
select@tno2=tnofromtwheretn='徐红霞'
select@cno=cnofromcwherecn='计算机网络'
updatetcsettno=@tno2wheretno=@tno1andcno=@cno
select@tno1=tnofromtwheretn='王平'
select@tno2=tnofromtwheretn='田丰'
select@cno=cnofromcwherecn='微机原理'
updatetcsettno=@tno2wheretno=@tno1andcno=@cno
go
commit
go
八、数据库备份和恢复
1、数据库备份的方式:
●完全数据库备份
●执行差异备份
●执行日志备份
●执行文件/文件组备份
2、恢复数据库的方法
●从完全数据库备份中恢复
●从差异备份中恢复
●从日志备份中恢复
●从文件或文件组备份中恢复
●直接拷贝文件的备份和恢复
3、完全数据库备份与简单恢复
完全备份:
backupdatabasejxsk
todisk='e:
\beifen\fullbackup_jxsk'
withinit
go
恢复:
restoredatabasejxsk
fromdisk='e:
\beifen\fullbackup_jxsk'
withreplace
…………
九、报表*
打开“SQLServerBusinessIntelligenceDevelopmentStudio”,使用报表设计器实现的一个简单报表
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 一个 完整 数据库 示例 说明