微博数据库
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),--管理员密码
)
