web-3g-bula(布啦微博)-数据库设计

ylbtech-DatabaseDesgin:web-3g-bula(布啦微博)-数据库设计

 

1.A，数据库关系图

 

1.B，数据库设计脚本

 /App_Data/sql-basic.sql

-- =============================================
-- ylb: 仿布啦微博
-- url: http://bula.cn
-- devloper：ylb,tech
-- author: YuanBo
-- date: 11:11 2011-07-05
-- siteType:微博
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
    SELECT name 
        FROM sys.databases 
        WHERE name = N'BuLa'
)
DROP DATABASE BuLa
GO

CREATE DATABASE BuLa
GO
use BuLa

go
-- =============================================
-- 1,用户表
-- =============================================
create table Users
(
UserID int identity(200,1) primary key,    --用户ID
Username varchar(30) not null,                --昵称
Userpass varchar(30) not null,                --密码
Sex varchar(30) check(Sex='male' or Sex='female'),    --性别
HeadImageUrl varchar(30) default('list_face.jpg'),            --头像Url

Province varchar(30),            --省份
City varchar(30),                ---市
County varchar(30),                --县
Years int default(2011),        --年
Months int default(2),        --月

Days int default(15),        --日
Signature varchar(500)        --个性签名
)

go
-- =============================================
-- 2_1,关注表
-- =============================================
create table Attending
(
UserID int foreign key references Users(UserID),    --用户ID【FK】
AttendingID int foreign key references Users(UserID)    --用户ID【FK】
)

go
-- =============================================
-- 2_2,被关注表
-- =============================================
create table Attended
(
AttendedID int foreign key references Users(UserID),    --用户ID【FK】
UserID int foreign key references Users(UserID)        --用户ID【FK】
)

go
-- =============================================
-- 3,信息表
-- =============================================
create table Msg
(
MsgID int identity primary key,    --信息ID【PK】
Content varchar(150),            --内容
HeadImageUrl varchar(150),        --头像地址
Username varchar(150),            --昵称
Pubdate datetime default(getdate()),    --发布时间

UserID int foreign key references Users(UserID),    --用户ID【FK】
BaseID int default(0)        --信息ID（基类）0：基类，
)

go
-- =============================================
-- 4,私信息表
-- =============================================
create table SiMsg
(
MsgID int identity primary key,    --信息ID【PK】
Content varchar(150),            --内容
HeadImageUrl varchar(150),        --头像地址
Username varchar(150),            --昵称
Pubdate datetime default(getdate()),    --发布时间

SendID int foreign key references Users(UserID),    --发送用户ID【FK】
JieShouID int foreign key references Users(UserID)    --接收用户ID【FK】
)

go
print '布啦微博数据创建成功！'

1.C，功能实现代码

 /App_Data/select/1,Users.sql

use BuLa
go
-- =============================================
-- ylb:1,对用户表的操作
-- =============================================

go
--1,注册
--select * from Users
--select Username,Userpass,Sex,Province,City from Users
--insert into Users(Username,Userpass,Sex,Province,City) values()
select @@identity

go
--2,登录
select count(*) from Users where UserID=2000 and Userpass='123'

go
--3,修改个人资料[先回填，后修改]
select Username,Sex,City,Years,Months,Days,Signature from Users where UserID=2000
Update Users set Username='tome' where UserID=20000

go
--4,修改头像
update Users set HeadImageUrl='' where UserID=2000

go
--5,修改密码
--a)bool=查原密码（方法：2,登录）
--b)bool=ture：去修改；bool=false：原密码有误
update Users set Userpass='abc' where UserID=20000


go
--6,获取个人信息

select UserID,Username,Sex,HeadImageUrl,Years,Signature from Users where UserID=200


go
--7,查自定义
select UserID,Username,HeadImageUrl from Users where UserID=200

go
--8,查RepliedBula个人信息
select UserID,Username,Sex,HeadImageUrl,Province,Years from Users where UserID=200

go
--9,关注数量
--9_1,我关注的人数
select COUNT(*) from Attending where UserID=201
--9_2,关注我的人数
select COUNT(*) from Attended where UserID=201

go
--10,验证昵称是否存在
select count(*) from Users where Username='sunshine'

 /App_Data/select/2,Attend.sql

use Bula
go
-- =============================================
-- 2,对关注表和被关注表的操作
-- =============================================
select * from Users
select * from Attending
select * from Attended

go
--1,添加关注
----p:200用户关注了201用户
--a)向关注表插入一条信息(我的视角)
insert into Attending(UserID,AttendingID) values(200,201)
--b)向被关注表插入一条信息（关注者的视角）
insert into Attended(AttendedID,UserID) values(200,201)


insert into Attending(UserID,AttendingID) values(200,202)
--b)向被关注表插入一条信息（关注者的视角）
insert into Attended(AttendedID,UserID) values(200,202)

go
--2，取消关注
--a)关注表
delete Attending where UserID=200 and AttendingID=201
--b)被关注表
delete Attended where AttendedID=200 and UserID=201



go
--3,我关注的人

select UserID,Username,Sex,HeadImageUrl,Years,Signature,Province from Users 
where UserID in(select AttendingID from Attending where UserID=200)


go
--4,关注我的人

select UserID,Username,Sex,HeadImageUrl,Years,Signature,Province from Users 
where UserID in(select AttendedID from Attended where userID=201)


go
--5,关注我的人的数量统计
select COUNT(*) from Attended where UserID=201

go
--6,我和别人之间的关系
select COUNT(*) from Attending where UserID=200 and AttendingID=201

 /App_Data/select/3,Msg.sql

use BuLa
go
-- =============================================
-- 3,对信息表的操作方法
-- =============================================

select * from Msg
go
--1,发布一条信息
select Content,HeadImageUrl,Username,UserID,BaseID from Msg
--insert into Msg(Content,HeadImageUrl,Username,UserID,BaseID) values()

go
--2,查“我的布啦”
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg where UserID=200
order by MsgID desc

select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg where UserID=200 and BaseID=0
order by MsgID desc


go
--3,查“布啦圈”
select AttendingID from Attending where UserID=200

select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg 
where UserID in(select AttendingID from Attending where UserID=200) or UserID=200 and BaseID=0
order by MsgID desc

go
--4,获取一条信息，根据msgID
select Content,Pubdate from Msg where MsgID=22

go
--5,获取所有回复，根据msgID
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg where BaseID=2

go
--6,查询回复的数量
select COUNT(*) from Msg where BaseID=2

go
--7,查看我的发布的布啦数量
select COUNT(*) from Msg where UserID=200 and BaseID=0
select * from Msg

go
--8,热门布啦(BulaSquare.aspx)【规则：当前布啦回复最多的布啦信息】
--a）分析
 select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID from Msg

--b)
select BaseID,COUNT(*) from Msg
where BaseID!=0
group by BaseID
--c)结论
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg
where MsgID in(select top 5 BaseID from Msg
where BaseID!=0 and DateDiff(dd,pubdate,getdate())=0 
group by BaseID)
order by MsgID desc


go
--9,随便看看（public.aspx）
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg where BaseID=0
order by MsgID desc

go
--10,布啦回复(Personal/Replied.aspx)
--a)我发布的布啦
select * from Msg
where UserID=200 and BaseID=0
go
--b)确定有回复的布啦
select * from Msg
where BaseID=1
--c)结论
select * from Msg 
where BaseID in(select MsgID from Msg where UserID=200 and BaseID=0)


--d)
select * from Msg m1 inner join Msg m2
on m1.BaseID=m2.MsgID
where m1.BaseID!=0

--f)总结论
select * from Msg m1 inner join Msg m2
on m1.BaseID=m2.MsgID
where m1.BaseID!=0 and m2.UserID=200

 /App_Data/select/4,BulaSearching.sql

use BuLa
go
-- =============================================
-- Bula搜索操作
-- =============================================

go
-- 1,Bula搜索：
-- 类型:搜话题、搜昵称、搜布啦号
-- 1_1,搜话题
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID,BaseID from Msg where Content like '%你们%'
order by MsgID desc

go
-- 1_2,搜昵称
select * from Users where Username like '%x%'

go
-- 1_3,搜布啦号
select * from Users where UserID like '%2%'

 /App_Data/select/5,Ranking.sql

use BuLa
go
-- =============================================
-- 5，排行榜
-- =============================================

go
-- 1,布啦数量排行榜
-- 1_分析过程
--a,
select UserID,Username,HeadImageUrl,Signature from Users

--b,
select top 20 UserID,COUNT(*) from Msg
where BaseID=0
group by UserID
order by COUNT(*) desc

go
--c,结果
select UserID,Username,HeadImageUrl,Signature from Users
where UserID in
(select top 20 UserID from Msg
where BaseID=0
group by UserID
order by COUNT(*) desc
)


go
--2,被关注排行榜
select * from Attended
--a,分析
select UserID,COUNT(*) from Attended
group by UserID
order by COUNT(*) desc

--b,结果
select top 20 UserID,Username,HeadImageUrl,Signature from Users
where UserID in
(select top 20 UserID from Attended
group by UserID
order by COUNT(*) desc
)

go
--3,被回复数排行榜
--a,分析
select UserID,COUNT(*) from Msg
where BaseID!=0
group by UserID
order by COUNT(*) desc
--b,结果
select top 20 UserID,Username,HeadImageUrl,Signature from Users
where UserID in
(select top 20 UserID from Msg
where BaseID!=0
group by UserID
order by COUNT(*) desc)

,6

 /App_Data/select/6,BulaKe.sql

use BuLa
go
-- =============================================
-- ylb:Bulake对布啦客推荐
-- =============================================

go
--1,热门布啦客推荐
--随机方式推荐10位用户
select top 10 UserID,Username,HeadImageUrl,Province from Users order by NEWID()


go
--2,热门布啦客推荐(Reg2.aspx)
select top 12 UserID,Username,Sex,HeadImageUrl,Signature from Users order by NEWID()


go
--3,热门布啦客推荐(BulaSquare.aspx)--附加字段content

select top 6 u.UserID,u.Username,u.HeadImageUrl,m.MsgID,m.Content from Users u 
inner join Msg m 
on u.UserID=m.UserID
where m.BaseID=0
order by m.MsgID desc
go

 /App_Data/select/7,Reconmendation.sql

use BuLa
go
-- =============================================
-- ylb:recomendation操作
-- =============================================
go
--一、热门回复
--1，今日回复最多的
--分析
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID from Msg 

--a，查今天的布啦
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID from Msg
 where DateDiff(dd,pubdate,getdate())=0 
 
--b，查布啦每条布啦回帖的数量，并按降序排列
select COUNT(*),BaseID from Msg 
where BaseID!=0 and DateDiff(dd,pubdate,getdate())=0 
group by BaseID
order by COUNT(*) desc


--结果
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID from Msg
where DateDiff(dd,pubdate,getdate())=0  and MsgID in(
select top 10 BaseID from Msg 
where BaseID!=0 and DateDiff(dd,pubdate,getdate())=0 
group by BaseID
order by COUNT(*) desc)



go
--2,一周回复最多的
select MsgID,Content,HeadImageUrl,Username,Pubdate,UserID from Msg
where DateDiff(WEEK,pubdate,getdate())=0  and MsgID in(
select top 10 BaseID from Msg 
where BaseID!=0 and DateDiff(WEEK,pubdate,getdate())=0 
group by BaseID
order by COUNT(*) desc)

 /App_Data/select/8,SiMsg.sql

use BuLa
go
-- =============================================
-- 4,对私信表的操作
-- =============================================

go
--1,发私信
insert into SiMsg(Content,HeadImageUrl,Username,SendID,JieShouID) values('ylb给晓梅发的私信','tt','sunshine',200,201)

--select MsgID,Content,HeadImageUrl,Username,Pubdate,SendID,JieShouID from SiMsg
--查
go
--2，收到私信
select MsgID,Content,HeadImageUrl,Username,Pubdate,SendID,JieShouID from SiMsg 
where JieShouID=201
go


--3,发的私信
select MsgID,Content,u.HeadImageUrl,u.Username,Pubdate,JieShouID,SendID from SiMsg sm
inner join Users u on sm.JieShouID=u.UserID
where SendID=200
go
--4，我的关注（我给关注好友发的私信）
select MsgID,Content,u.HeadImageUrl,u.Username,Pubdate,SendID,JieShouID from SiMsg sm
inner join Users u on sm.SendID=u.UserID
where SendID=200 and JieShouID in(select AttendingID from Attending where UserID=200)

go
--5,删除私信,根据MsgID
delete SiMsg where MsgID=1

作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。