总的来说，通过此次实习我得以将过去所学的知识综合融汇，进一步加深了对数据库的认识，更加熟练的掌握了数据库设计的一般过程及其具体环节。
参考文献
[1] 王珊 萨师煊.数据库系统概论（第四版）.北京：高等教育出版社.2005
[2] 詹英.数据库技术与应用-SQL Server 2005 教程.北京.清华大学出版社.2008
[3] 刘大玮 马传宝 孙颖洁.SQL Server 数据库项目案例导航.北京.清华大学出版社.2005
[4] 夏邦贵 刘凡馨.SQL Server 数据库开发经典案例精解.北京.机械工业出版社.2006 附录
use SportManager --创建表
create table R ( )
create table S ( )
create table E (
11
re_no varchar(6) primary key, re_name varchar(20) not null,
re_sex char(2) check(re_sex in('男','女')), re_age int,
re_event varchar(2) not null, re_title varchar(20),
re_add varchar(30) not null,
constraint CK_RENO check(re_no like
constraint FK_R_E foreign key(re_event) references E(ev_no)
'2[0-9]0-9[0-9][0-9][0-9][0-9]'),
sp_no varchar(6) primary key, sp_name varchar(20) not null,
sp_sex char(2) not null check(re_sex in('男','女')), sp_age int check(sp_age between 18 and 35), sp_event varchar(2) not null, team varchar(2) not null,
constraint FK_S_S foreign key(sp_event) references E(ev_no), constraint FK_S_T foreign key(team) references T(t_no),
constraint CK_SPNO check(sp_no like '1[0-9][0-9][0-9][0-9][0-9]')
ev_no varchar(20) primary key check(ev_no like '[0-9][0-9]'), ev_name varchar(20) not null,
ev_place varchar(30) not null default '校本部' )
create table G ( )
create table P ( )
create table T ( ) --索引
create unique
create nonclustered
index sp_event_index
on S(sp_event) index ev_name_index
on E(ev_name)
t_no varchar(2) primary key, t_name varchar(20),
t_add varchar(20) not null, coach varchar(20) not null,
constraint CK_T_NO check(t_no like '[0-9][0-9]') sp_no varchar(6) primary key, punish varchar(50) not null,
constraint FK_P_S foreign key(sp_no) references S(sp_no) sp_no varchar(6), ev_no varchar(2), score int not null,
constraint PK_G primary key (sp_no,ev_no),
constraint FK_G_SP foreign key(sp_no) references S(sp_no), constraint FK_G_EV foreign key(ev_no) references E(ev_no)
--简单查询
--查询102001号运动员01项目的成绩 select @score=score from G
where sp_no=’01’ and ev_no=’102001’
12
-查询103002号运动员的姓名、教练和比赛项目
select sp_name,ev_name,coach from S,T,E
where sp_no=’103002’ and sp_event=ev_no and team=t_no
--自定义数据类型
CREATE TYPE NUM FROM char(6) NULL
--查询某位运动员的基本信息 内嵌表值函数
create function selectSporter(@num varchar(6)) returns table as return( )
select * from selectSporter('101001')
--查询某位裁判的基本信息 内嵌表值函数
create function selectReferee(@num varchar(6)) returns table as
return(
select * from R where re_no=@num )
select * from selectReferee('201001')
--查询某运动员在某项目取得的成绩 标量函数
create function selectSpGain(@num varchar(6),@evno varchar(2)) returns int as
begin
declare @score int
select @score=score from G
where sp_no=@num and ev_no=@evno return @score
select * from S where sp_no=@num
end
select dbo.selectSpGain('101001','01') --或者
declare @gain int
exec @gain = dbo.selectSpGain '101001','01' select @gain as 成绩
--查询某运动员的姓名、教练和比赛项目 多语句表值函数 create function selectSpInfor(@num varchar(6))
13
returns @infor table(姓名varchar(20),教练varchar(20),项目varchar(20)) as
begin
insert into @infor
select sp_name,ev_name,coach from S,T,E
where sp_no=@num and sp_event=ev_no and team=t_no return
end
select * from selectSpInfor('101001')
--查询某项赛事的地点 存储过程
create procedure selectEvent @evno varchar(2),@place varchar(30) output as
begin
select @place=ev_place from E where ev_no=@evno end
declare @where varchar(30)
exec selectEvent '01' ,@where output select @where as 地点
--查询所有处罚信息
create procedure selectPunish as
begin
select * from P end
exec selectPunish
--创建视图
--创建裁判与赛事的视图
create view referee_event as
select re_name,ev_name,ev_place from R join E on re_event=ev_no
select * from referee_event
--创建运动员和队伍的视图 create view sp_team as
14
select sp_no, sp_name,t_name from S,T where team=t_no
select * from sp_team --触发器
--不允许对处罚表进行修改或删除
create trigger trigger_punish on P
for update,delete as begin
rollback tran end
--如果修改运动员基本信息表中的编号将成绩表的编号也修改 create trigger tri_sporter on S
after update as
begin
update G
set sp_no=(select sp_no from inserted)
where sp_no in (select sp_no from deleted) end
--授权
exec sp_addlogin 'liusensen','111','SportManager'
exec sp_grantlogin 'zhangxiukun' go
exec sp_grantdbaccess 'zhangxiukun','zxk' go
exec sp_addrole '教师' go
exec sp_addrolemember '教师','zxk'
grant select,update,delete on S to 教师
with grant option
--备份
exec sp_addumpdevice 'disk','Spmdata','e:\\backup\\Spmdata.bak'
exec sp_addumpdevice 'disk','Spmlog','e:\\backup\\Spmlog.bak'
15
backup database SportManager to Spmdata
backup log SportManager to Spmlog
16