bbs基本建表语句

create database test1

--bbs论坛会员基本信息表
CREATE TABLE bbsuser(
username VARCHAR(20)  PRIMARY KEY,
[password] VARCHAR(20)  NOT NULL,
nickname VARCHAR(20)  NOT NULL,
sex  VARCHAR(2)  NOT NULL,
birthyear VARCHAR(4)  NOT NULL,
birthmonth VARCHAR(4)  NOT NULL,
birthday VARCHAR(4)  NOT NULL,
e_mail  VARCHAR(50)  NOT NULL,
mobile  VARCHAR(20)   NULL,
signature VARCHAR(250)  NULL,
usergrade VARCHAR(20)   default '普通会员',
userscore numeric(10)    NULL
);

--论坛版块基本信息表
CREATE TABLE sort(
id   numeric(10)  PRIMARY KEY,
sortname VARCHAR(20)  NOT NULL ,
master  VARCHAR(20)   foreign key REFERENCES bbsuser(username) ON DELETE CASCADE
);
--论坛主贴信息表
CREATE TABLE topic(
id   numeric(10)  PRIMARY KEY,
topicname  VARCHAR(20)  NOT NULL ,
topiccontent VARCHAR(2000) NULL,
owner  VARCHAR(20)  NOT NULL
   foreign key  REFERENCES bbsuser(username) ON DELETE CASCADE,
time  DATEtime   NOT NULL,
sortid   numeric(10)   NOT NULL
   foreign key  REFERENCES sort(id)
);
--论坛回帖信息表
CREATE TABLE responses(
id   numeric(10)   PRIMARY KEY,
title   VARCHAR(20)  NOT NULL ,
content  VARCHAR(2000) NULL,
owner  VARCHAR(20)  NOT NULL
   REFERENCES bbsuser(username) ON DELETE CASCADE,
time  DATEtime   NOT NULL,
topicid  numeric(10)   NOT NULL
   REFERENCES topic(id)
);

insert into bbsuser values('mike','111','大奔','男','1970','12','10','[e-mail]','1391230896','管理员','管理员',10);
insert into bbsuser values('robert','222','小龙女','女','1982','12','25','[e-mail]','1352345646','努力吧','版主',20);
insert into bbsuser values('jenney','333','大虾','男','1979','08','12','[e-mail]','1372342566','我爱struts','普通会员',30);
insert into bbsuser values('peter','444','侠女','女','1981','01','10','[e-mail]','1391231758','分层好','普通会员',30);

insert into sort values(1,'Struts','mike');
insert into sort values(2,'Eclipse','jenney');
insert into sort values(3,'accp','peter');


select * from bbsuser
select * from  sort
select * from topic
select * from  responses
select * from dengji

create table dengji
(
dengji_id    varchar(20),
dengji_low   int,
dengji_heigh int,
dengji_name  varchar(40)
)
--等级表
insert into dengji values('d001',0,10,'小虾米')

insert into dengji values('d001',10,30,'大虾米')

insert into dengji values('d001',30,50,'大侠')
--跨行查询
select * from bbsuser as b,dengji as d
where  b.userscore>=d.dengji_low and b.userscore<d.dengji_heigh


insert into topic values(1,'分手在雨天','你好毒!','mike',getdate(),2)



select sort.sortname as '版块信息',count(topic.id) as'主贴数量',topic.topicname as '主贴名',topic.owner as '发帖人' ,topic.time as '发帖时间',sort.sortname as '版主' from sort,topic
where sort.id=topic.id  group by sort.sortname,topic.time,topic.topicname,topic.owner order by topic.time desc