oooo.sql

照片管理系统的数据库

use image

create table userid(
uid char(10) primary key,
uname varchar(20) not null,
pwd varchar(16) not null,
email varchar(20) not null
)
go


 drop table ablum

create table ablum(
uid char(10) not null references userid(uid),
photoid  char(10) primary key,
pname varchar(20) not null default'我的秀图',
mshu varchar(50) ,
)
go

drop table imag

create table imag(
photoid char(10) references ablum(photoid),
imageid char(10) not null,
photo image ,
iname varchar(20) not null,
primary key(photoid,imageid)
)



alter table imag
add                                                                                                                                                                                                 



--lect uid,uname,pwd,email,photoid,pname,mshu,imageid,iname from userid join ablum on  userid.uid=ablum.uid join imag on imag.photoid=ablum.photoid


--lect userid.uid,pwd,email,ablum.photoid,pname,mshu,imageid,iname from userid join ablum on  userid.uid=ablum.uid join imag on imag.photoid=ablum.photoid
         --创建全局视图
select * from v_all

drop view v_all


--创建全局视图
create view v_all
as
select userid.uid,uname,pwd,email,ablum.photoid,pname,mshu,imageid,iname,photo from userid join ablum on  userid.uid=ablum.uid join imag on imag.photoid=ablum.photoid

--查看会员总数
use image

select count(*) 会员总数 from userid
go

select * from userid


--图片总数
select count(*) 图片总数 from  imag 
go



--创建用户表视图
create view v_userid
as
select * from userid
go

--创建用户视图
select * from v_userid



--创建相册视图
create view v_album
as
select * from ablum
go


--创建照片视图
create view v_image
as
select * from imag
go











--INSERT 触发器


create trigger u_insert on userid
after insert 
as
declare @u_uid char(10)
select @u_uid=uid  from inserted
insert into ablum(uid,photoid) select @u_uid ,uid from userid


insert into userid values('07118701','chan','07118701','[e-mail]')


select * from userid


select * from v_all


select * from ablum






--添加用户视图存储过程
create proc proc_userid
@uid char(10),@uname varchar(20),@pwd varchar(16),@email varchar(20)
as
if not exists(select * from userid where uid=@uid)
 insert into userid(uid,uname,pwd,email) values(@uid,@uname,@pwd,@email)
else
print'用户名已存在，请换一个重试。'










--检验上面存储器的功能 （插入已存在值检验结果）
exec proc_userid '07118701','07118701','趁','[e-mail]'






select * from userid




delete  function dbo.ano






--创建自定义函数 返回用户像册现有数量（或下标）

create function ano(@uid char(10))
returns int
as
begin
declare @no int
select @no=count(pname) from ablum where uid=@uid
return @no
end
go



--验证函数体

select * from dbo.ano ('07118701')









drop proc proc_ablum




--添加像册存储过程
create proc proc_ablum
@uid char(10),@pname varchar(20),@mshu varchar(50)
as
if not exists(select * from ablum where uid=@uid and pname = @pname)
  insert into ablum(uid,photoid,pname,mshu) values(@uid,dbo.ano(@uid)+1,@pname,@mshu) 
else
print '用户名或相册名重复，请重新填写！'
go






--验证相册存储过程
exec proc_ablum '07118701','我的美丽','最美丽的自己在那时！~'
go






--DELECT 用户表删除触发器

create trigger del_userid on userid
after delete
as
declare @uid char(10)
select @uid=uid from deleted
delete from ablum where ablum.uid=@uid


--验证
delete from userid where uid='07118701'





--查询最终结果
select * from userid


select * from ablum