创建数据库的相关知识由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“数据库相关知识”。
create database PeronalBlog
/*创建文章内容表（id，标题，内容，关键字）*/
create table TitleInfo（Tid varchar(20)primary key,Ttile varchar(20)not null,Ttext varchar(8000)not null,Tkey varchar(20)not null)
insert intoTitleInfo values('112401','我爱家乡','我的家乡在四处','四川')
/*创建文章信息表（文章id，文章标题，文章关键字，文章创建日期，文章浏览次数，文章类型，文章是否隐藏）*/
create table ArticleDetail(tid varchar(20)primary key,tTitle varchar(20)not null,tkey varchar(20)not null,tDate date not null,tText varchar(8000)not null,tLookNum int ,tType varchar(20)not null,tIsSee varchar(4)not null)
/*创建浏览信息表(id，浏览内容，浏览数量)*/
create table searchInfo(stId varchar(20)primary key,stTName varchar(100)not null,stDate date not null)
insert into searchInfovalues('112401','我爱家乡','2014-05-01')
/*创建留言信息表（id，留言者姓名，留言日期，留言内容，留言是否回复，回复内容）*/
create table liuyanInfo(liId varchar(20)primary key,liName varchar(20),liDate date not null,liText varchar(100)not null,liTName varchar(50)not null,liIsResponse varchar(2)not null,liResponseText varchar(100))
insert into liuyanInfovalues('112401','songyan','2014-04-04','想你了，亲！','我爱家乡','是','me too!')
/*创建评论信息表（id，评论者姓名，评论日期，评论是否审核，评论内容，评论文章id，评论文章名字）*/
create table pinglunInfo(plId varchar(20)primary key,plName varchar(20),plDate date not null,plIsCheck varchar(2),plText varchar(20)not null,plTitleId varchar(20)not null,plTName varchar(20)not null)
insert into pinglunInfovalues('112401','songyan','2014-04-04','是','good','112401','我爱家乡')
/*创建系统信息表*/
create table systemInfo(slId varchar(20)primary key,slLoginName varchar(20)not null,slPaword varchar(20)not null ,slBlogName varchar(20)not null,slBlogTheme varchar(20)not null,slOwner varchar(10)not null,slCreateDate date not null,slBlogDetail varchar(100),slBlogJianjie varchar(50))
insert into systemInfovalues('112401','owner','123456','回不到的昨天','浪漫回忆,向往未来','黄圆','2014-01-01','我的博客是我生活的写照,也是我们新一代人梦的展现..','回望过去,展望明天!')
/*个人信息*/
Create table personInfo(pid varchar(20)primary key,pname varchar(20)not null,page int not null,psex varchar(2)not null,pXingzuo varchar(6)not null,pBornPlace varchar(20),pShcool varchar(20)not null,pXueli varchar(10)not null,pIntrest varchar(20)not null,pEmail varchar(50)not null,pNum varchar(20))
insert into personInfo values('112401','黄圆',18,'女','双鱼','四川','琼州学院','本科','看相亲节目','123456@yuan','10')
/*创建用户信息*/
create table userInfo(uid varchar(20)primary key,name varchar(20)not null,paword varchar(20)not null)
insert into userInfo values('112401','seeker','123')
/*好友表*/
create table friends(fid varchar(20)primary key,fname varchar(20)not null,fdate date not null,)
insert into friendsvalues('112401','songyan','2014-05-01')
/*---创建存储过程
--------------------*/
/*输入文章id查看文章类别信息*/
create procedure queryType @ttid varchar(20)
as
select ttTpyeid,tpname,tpKey,tpDetail
from titleType tp join ttTpye tt on tt.ttTypeid=tp.tpid where ttTpyeid=@ttid and tpid=@ttid
/*输入文章id，查看留言信息*/
create procedure querLiuyanInfo@tid varchar(20)
as
select
tTitle,tKey,tDate,tType,tIsSee,liName,liDate,LiText,LiIsResponse,LIResponseText
fromtitle a joinliuyanInfo b on a.tid=b.liId
where a.tid=@tid and b.liId=@tid
/*输入文章id，查看浏览信息*/
create procedure querSearchInfo@tid varchar(20)
as
select tTitle,tKey,tDate,tType,tIsSee, stText,stNum
fromtitle a joinserachInfo b on a.tid=b.stId
where a.tid=@tid and b.stId=@tid
/*输入文章id，查看评论信息*/
create procedure querPinglunInfo@tid varchar(20)
as
select tTitle,tKey,tDate,tType,tIsSee,plName,plDate,plIsCheck
fromtitle a joinpinglunInfo b on a.tid=b.plId
where a.tid=@tid and b.plId=@tid
/*增加博文*/
create procedure insertAr @tid varchar(20),@tid1 varchar(20), @Title varchar(20),@text varchar(8000),@key varchar(20),@tpName varchar(10),@date date,@num int,@isSeevarchar(4)
as
begin
begin
insert into ArticleInfo values(@tid,@Title,@text,@key)
end
begin
insert into ArticleDetail values(@tid1,@Title,@key,@date,@num,@tpName, @isSee)
end
End
select COUNT(plId)
from pinglunInfo
whereplTName in
（selecttTitle
from ArticleDetail)
/*-------------------------创建登录名和权限设定
--------------------*/
create login owner with paword='123456'
create login seeker with paword='123'
create user owner from login owner
create user seeker1from login seeker
grant select,delete,insert,update on liuyanInfo to owner
grant select,delete,insert,update on pinglunInfo to owner
grant select,delete,insert,update on searchInfoto owner
grant select,delete,insert,update on friendsto owner
grant select,delete,insert,update on personInfoto owner
grant select,update,insert,delete on ArticleDetail to owner
grant select,update,insert,delete on userInfo to owner
grant selecton liuyanInfo to seeker1
grant selecton pinglunInfo to seeker1
grant selecton searchInfoto seeker1
grant selecton friendsto seeker
1grant selecton personInfoto seeker1
grant selecton ArticleDetail to seeker1
112401 songyan 2014-05-01
112402 xuezeng 2014-05-01
112403 lina 2014-05-01
112404 qiushan 2014-06-01
112405 liuyan 2014-07-02
112406 huyiyi 2014-08-09
201407 zhenglihui 2014-09-09
201408 liping 2014-05-15
201409 sunzhouhong 2014-06-15
201410 sunshuai 2014-07-07
112401 songyan 2014-04-04 想你了，亲！我爱家乡 是 me too!112402 xuezeng 2014-05-05 踩一脚 我爱家乡 是 回踩！112403 qiushan 2014-05-05 呵呵 我爱家乡 是 呵呵。112404 liuyan 2014-05-06 也许天灾就在眼前 我爱家乡 是 去去去、、、112405 酸牛奶 2014-06-05 交个朋友吧 我爱家乡 是 饿@。你是、、112406 春天的敬意 2014-08-09 交个朋友吧 我爱家乡 是 您是？ 112407 吹吧 2014-09-09 我的爱人呢 我爱家乡 否 无
