forum.sql

这个是使用JSP、JDBC技术构架一个关于JAVA语言的BBS讨论区

/*创建forum数据库*/
create database forum
use forum

/*创建用户表*/

create table forumuser
(
	username varchar(100),
	nickname varchar(100),
	password varchar(100),
	sex      varchar(20),
	email    varchar(100),
	age      varchar(40),
	address  varchar(200),
	comment  varchar(2000),
	register datetime,
	postcount int,
	status   varchar(100),
	usertype varchar(100),
	expr     int,
	id int not null identity(1,1)
)

/*初始化三个用户javalover,Rainbow,SYSOP*/

insert into forumuser 
(username,nickname,password,sex,email,age,address,comment,register,postcount,status,usertype,expr)
values
(
	'javalover',
	'影落寒潭',
	'java',
	'男',
	'[e-mail]',
	'20',
	'Peking University',
	'小楼一夜听春雨,深巷明朝卖杏花。',
	getdate(),
	0,
	'unknown',
	'normal',
	5
)	

insert into forumuser 
(username,nickname,password,sex,email,age,address,comment,register,postcount,status,usertype,expr)
values
(
	'Rainbow',
	'小楼一夜听春雨',
	'java',
	'女',
	'[e-mail]',
	'18',
	'Pking University',
	'鸳鸯独宿何曾惯，化作西楼一缕云。',
	getdate(),
	0,
	'unknown',
	'normal',
	5
)	

insert into forumuser 
(username,nickname,password,sex,email,age,address,comment,register,postcount,status,usertype,expr)
values
(
	'SYSOP',
	'小楼一夜听春雨',
	'java',
	'男',
	'[e-mail]',
	'20',
	'Pking University',
	'鸳鸯独宿何曾惯，化作西楼一缕云。',
	getdate(),
	0,
	'unknown',
	'SYSOP',
	5
)	

/*创建板块表,保存板块的信息*/

create table forumgroup
(
	groupname varchar(100),
	groupenname varchar(100),
	createtime datetime,
	adminusername varchar(100),
	adminuserid   int,
	comment    varchar(2000),
)


/*创建三个板块J2EE,JSP/Servlet,SYSOP*/

insert into forumgroup
(
	groupname,groupenname,createtime,adminusername,adminuserid,comment
)
values
(
	'J2EE',
	'j2ee',
	getdate(),
	'javalover',
	1,
	'讨论JMS,JNDI,JTS,JTA,JAXP,EJB等J2EE技术'
)


insert into forumgroup
(
	groupname,groupenname,createtime,adminusername,adminuserid,comment
)
values
(
	'JSP/Servlet',
	'jsp',
	getdate(),
	'Rainbow',
	2,
	'讨论JSP/Servlet技术'
)

insert into forumgroup
(
	groupname,groupenname,createtime,adminusername,adminuserid,comment
)
values
(
	'SYSOP',
	'SYSOP',
	getdate(),
	'SYSOP',
	3,
	'版主之家'
)

/*创建数据表,用于保存文章的信息*/
create table data 
(
	topic varchar(200),
	groupname varchar(200),
	groupenname varchar(200),	
	author varchar(200),
	password varchar(200),
	content  text,
	posttime datetime,
	modifytime datetime,
	ip varchar(200),
	ref int default -1,
	replycount int default 0,
	ifre varchar(20) default 'false',
	id int not null identity(1,1),
	mark varchar(50) default 'N' 	
)

/*创建精华区表,用于保存精华区文章的信息*/

create table datafavourite 
(
	topic varchar(200),
	groupname varchar(200),
	groupenname varchar(200),	
	author varchar(200),
	password varchar(200),
	content  text,
	posttime datetime,
	modifytime datetime,
	ip varchar(200),
	ref int default -1,
	replycount int default 0,
	ifre varchar(20) default 'false',
	id int not null identity(1,1),
	mark varchar(50) default 'N' 	
)