微博数据库

USE master;
GO
IF DB_ID (N'MicroblogDB') IS NOT NULL

--N '.... '代表是Unicode   字符串

/*

删除数据库 数据库名一般放在master的sysdatabases中。
   方法一 用exists(存在)
   use master
   go

  if exists(select * from sysdatabases where name = '数据库名')
     drop database 数据库名
  go

  方法二 db_id存在sqlserver2000以后的版本
  use master
   go

  if db_id(N'数据库名') is not
    drop database 数据库名
   go

*/

DROP DATABASE MicroblogDB;
GO
-- Get the SQL Server data path.
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

 -- Execute the CREATE DATABASE statement.
EXECUTE ('CREATE DATABASE MicroblogDB
ON
    (
    NAME = MicroblogDB_data
    ,FILENAME = ''' + @data_path + 'MicroblogDB_data.mdf''
    ,SIZE = 10MB
    ,MAXSIZE = 50MB
    ,FILEGROWTH = 15%
    )
LOG ON
    (
    NAME = Microblog_log
    ,FILENAME = ''' + @data_path + 'MicroblogDB_log.ldf''
    ,SIZE = 5MB
    ,MAXSIZE = 25MB
    ,FILEGROWTH = 5MB
    )'
);
GO

USE MicroblogDB;
GO

CREATE TABLE tbUser--用户表
(
 UserID nvarchar(64)PRIMARY KEY,--用户ID,主键
 Password nvarchar(64),--密码
 Question nvarchar(64),--密码问题
 Answer nvarchar(64),--密码问题答案
 NickName nvarchar(64),--昵称
 TrueName nvarchar(64),--真实姓名
 Sex nvarchar(8) check (sex in ('男','女')),--性别
 Province nvarchar(16),--省份
 City nvarchar(16),--市
 BlogAddress nvarchar(64),--博客地址
 Email nvarchar(64),--电子邮件
 QQ nvarchar(32),--QQ
 MSN nvarchar(64),--MSN
 SelfIntroduction nvarchar(1024),--自我介绍
)
ALTER   TABLE   tbUser   ADD   Regdate   datetime  default   NULL  --给表添加一个字段
INSERT INTO tbUser
(
 UserID,
 Password,
 Question,
 Answer,
 NickName,
 TrueName,
 Sex,
 Province,
 City,
 BlogAddress,
 Email,
 QQ,
 MSN,
 SelfIntroduction
)
VALUES
(
 'lianqidi'/* UserID */,
 '123456'/* Password */,
 '我叫什么？'/* Question */,
 '连齐俤？'/* Answer */,
 '独钓寒江雪'/* NickName */,
 '连齐俤'/* TrueName */,
 '男'/* Sex */,
 '福建省'/* Province */,
 '福州市'/* City */,
 'http://user.qzone.qq.com/89745607/'/* BlogAddress */,
 'lianqidi@sina.com'/* Email */,
 '89745607'/* QQ */,
 'lianqidi@hotmail.com'/* MSN */,
 '很好很强大？'/* SelfIntroduction */
)
select getdate()
update tbUser set Regdate = getdate() where UserID = 'lin'
SELECT * FROM tbUser
DELETE  FROM tbUser WHERE  UserID = 'lin'
DROP TABLE tbUser;
SELECT top 15 U.UserID,I.ImageName FROM tbUser as U left join tbUserImage as I on(U.UserID=I.UserID)Order By Regdate DESC
CREATE TABLE tbUserImage --用户图片表
(
 UserID nvarchar(64)primary key,
 ImageName nvarchar(64),
 FOREIGN KEY (UserID) REFERENCES tbUser(UserID)on delete cascade on update cascade
)
drop table tbUserImage
select * from tbUserImage
Insert Into tbUserImage(UserID, ImageName)values('lianqidi','123456.jpg')
DELETE FROM tbUserImage WHERE UserID = 'lianqidi'
SELECT TOP 3 *, NewID() as random  from tbUser order by random
SELECT TOP 5 *, NewID() as random  from tbUserImage order by random

CREATE TABLE tbMicroblog--微博表
(
 MicroblogID int identity(1,1) primary key,--微博编号
 UserID nvarchar(64),--微博主人
 Content nvarchar(300),--微博内容
 Pubdate datetime,--发布时间
 FOREIGN KEY (UserID) REFERENCES tbUser(UserID)on delete cascade on update cascade
)

INSERT INTO tbMicroblog(UserID,Content,Pubdate)VALUES('lianqidi','你好',getdate())
--取出最新发表的微博和作者照片文件名
select top 20 M.UserID, M.MicroblogID,M.Content,M.Pubdate,I.ImageName From tbMicroblog as M left join tbUserImage as I on(M.UserID = I.UserID) Order By Pubdate DESC 
--取出最新发表的微博和作者照片文件名(作者为我关注的人)
select M.UserID, M.MicroblogID,M.Content,M.Pubdate,I.ImageName From tbMicroblog as M left join tbUserImage as I on(M.UserID=I.UserID) WHERE M.UserID in(select distinct R.FocusID from tbUser as U left join tbRelation as R on ('lianqidi'=R.UserID)) Order By Pubdate DESC
select distinct R.FocusID from tbUser as U left join tbRelation as R on ('lianqidi'=R.UserID)
SELECT Content,Pubdate FROM tbMicroblog WHERE UserID = 'lianqidi' Order By Pubdate DESC
SELECT * FROM tbMicroblog;
DELETE FROM tbMicroblog WHERE UserID = 'lianqidi'
DROP TABLE tbMicroblog;

CREATE TABLE tbReview    --评论表
(
 ReviewID int identity(1,1) primary key,  --评论ID，由数据库自动增长
 UserID nvarchar(64),      --评论用户ID
 MicroblogID int,       --评论微博编号
 ReviewContent nvarchar(300),    --评论内容
 Pubdate datetime,       --评论时间
 FOREIGN KEY (MicroblogID) REFERENCES tbMicroblog(MicroblogID)on delete cascade on update cascade
)
insert into tbReview(UserID,MicroblogID,ReviewContent,Pubdate) values('lianqidi',1,'你好',getdate())
DROP TABLE tbReview
SELECT * FROM tbReview

CREATE TABLE tbSms --短信表
(
 SmsID int identity(1,1) primary key, --短信编号
 Content nvarchar(2048),     --短信内容
 SendID nvarchar(64),     --发送账号
 ReceiveID nvarchar(64),    --接受账号
 SendTime datetime,     --发送时间
 HasRead  nvarchar(8) not null default 'no', --是否阅读
 ReadTime datetime     --阅读时间
 FOREIGN KEY (SendID) REFERENCES tbUser(UserID) on delete cascade on update cascade
)
SELECT * FROM tbSms
INSERT INTO tbSms(Content,SendID,ReceiveID,SendTime,HasRead,ReadTime) values('你好。','lianqidi','123','2010-03-26 13:35:50.140','no','2010-03-26 13:35:50.140')
DROP TABLE tbSms
select getdate()

/* 此表已废弃
CREATE TABLE tbRelation--用户关系表
(
 RelationID int identity(1,1) primary key,--主键，关系编号
 UserID nvarchar(64),--用户ID
 FansID nvarchar(64) ,--粉丝
 FocusID nvarchar(64) ,--关注
 FOREIGN KEY (UserID) REFERENCES tbUser(UserID) on delete cascade on update cascade
)
SELECT * FROM tbRelation
INSERT INTO tbRelation(UserID,FocusID)values('lianqidi','王建伟')
Insert into tbRelation(UserID,FansID)values('','')
DROP table tbRelation
select count(*) from tbRelation where UserID = 'lianqidi' AND FansID <> ''
*/
CREATE TABLE tbAttention  --关注表
(
 AttentionID int identity(1,1) primary key,--主键，关注编号
 MyID nvarchar(64),  --我的ID
 YourID nvarchar(64), --你的ID
 AttenTime datetime,  --关注时间
 FOREIGN KEY (MyID) REFERENCES tbUser(UserID) on delete cascade on update cascade
)
SELECT * FROM tbAttention WHERE MyID = 'lianqidi'
DELETE FROM tbAttention WHERE YourID = 'lianqidi'
INSERT INTO tbAttention(MyID,YourID,AttenTime)VALUES('lin','lianqidi',getdate())
SELECT count(*) FROM tbAttention WHERE YourID = 'lianqidi'
SELECT Content,Pubdate FROM tbMicroblog WHERE UserID = 'lianqidi' Order By Pubdate DESC
select UserID, MicroblogID,Content,PubDate From tbMicroblog Where UserID in(select distinct FocusID From tbUser as U join tbRelation as R on 'lianqidi' = R.UserID) or UserID = 'lianqidi'
select M.UserID, M.MicroblogID,M.Content,M.Pubdate,I.ImageName From tbMicroblog as M left join tbUserImage as I on(M.UserID=I.UserID) WHERE M.UserID in(select YourID from tbAttention where MyID = 'lianqidi') Order By Pubdate DESC

CREATE TABLE tbManager --管理员表
(
 ManagerID nvarchar(64) PRIMARY KEY,--管理员编号
 Password nvarchar(64),--管理员密码
)

 

转载于:https://www.cnblogs.com/xgcblog/archive/2011/08/23/2150635.html